Examples with PgConstraint org.jooq.meta.postgres.pg_catalog.tables.PgConstraint used on opensource projects

Example 1 with PgConstraint

use of org.jooq.meta.postgres.pg_catalog.tables.PgConstraint in project jOOQ by jOOQ.

the class PostgresDatabase method keys.

private ResultQuery<Record6<String, String, String, String, String, Integer>> keys(List<String> schemas, Field<String> constraintType) {
    KeyColumnUsage k = KEY_COLUMN_USAGE.as("k");
    PgConstraint c = PG_CONSTRAINT.as("c");
    return create().select(k.TABLE_CATALOG, k.TABLE_SCHEMA, k.TABLE_NAME, k.CONSTRAINT_NAME, k.COLUMN_NAME, k.ORDINAL_POSITION).from(c).join(k).on(k.TABLE_SCHEMA.eq(c.pgClass().pgNamespace().NSPNAME)).and(k.TABLE_NAME.eq(c.pgClass().RELNAME)).and(k.CONSTRAINT_SCHEMA.eq(c.pgNamespace().NSPNAME)).and(k.CONSTRAINT_NAME.eq(c.CONNAME)).where(c.CONTYPE.eq(constraintType)).and(c.pgNamespace().NSPNAME.in(schemas)).orderBy(k.TABLE_SCHEMA.asc(), k.TABLE_NAME.asc(), k.CONSTRAINT_NAME.asc(), k.ORDINAL_POSITION.asc());
}

Example 2 with PgConstraint

use of org.jooq.meta.postgres.pg_catalog.tables.PgConstraint in project jOOQ by jOOQ.

the class PostgresDatabase method getDomains0.

@Override
protected List<DomainDefinition> getDomains0() throws SQLException {
    List<DomainDefinition> result = new ArrayList<>();
    if (existAll(PG_CONSTRAINT, PG_TYPE)) {
        PgNamespace n = PG_NAMESPACE.as("n");
        PgConstraint c = PG_CONSTRAINT.as("c");
        PgType d = PG_TYPE.as("d");
        PgType b = PG_TYPE.as("b");
        Field<String[]> src = field(name("domains", "src"), String[].class);
        Field<String> constraintDef = field("pg_get_constraintdef({0})", VARCHAR, c.OID);
        for (Record record : create().withRecursive("domains", "domain_id", "base_id", "typbasetype", "src").as(select(d.OID, d.OID, d.TYPBASETYPE, when(c.OID.isNotNull(), array(constraintDef))).from(d).join(n).on(n.OID.eq(d.TYPNAMESPACE)).leftJoin(c).on(d.OID.eq(c.CONTYPID)).where(d.TYPTYPE.eq("d")).and(n.NSPNAME.in(getInputSchemata())).unionAll(select(field(name("domains", "domain_id"), Long.class), d.OID, d.TYPBASETYPE, decode().when(c.CONBIN.isNull(), src).otherwise(arrayAppend(src, constraintDef))).from(name("domains")).join(d).on(field(name("domains", d.TYPBASETYPE.getName())).eq(d.OID)).leftJoin(c).on(d.OID.eq(c.CONTYPID)))).select(n.NSPNAME, d.TYPNAME, d.TYPNOTNULL, d.TYPDEFAULT, b.TYPNAME, // See https://github.com/postgres/postgres/blob/master/src/backend/catalog/information_schema.sql
        field("information_schema._pg_char_max_length({0}, {1})", INTEGER, d.TYPBASETYPE, d.TYPTYPMOD).as(DOMAINS.CHARACTER_MAXIMUM_LENGTH), field("information_schema._pg_numeric_precision({0}, {1})", INTEGER, d.TYPBASETYPE, d.TYPTYPMOD).as(DOMAINS.NUMERIC_PRECISION), field("information_schema._pg_numeric_scale({0}, {1})", INTEGER, d.TYPBASETYPE, d.TYPTYPMOD).as(DOMAINS.NUMERIC_SCALE), src).from(d).join(name("domains")).on(field(name("domains", "typbasetype")).eq(0)).and(field(name("domains", "domain_id")).eq(d.OID)).join(b).on(field(name("domains", "base_id")).eq(b.OID)).join(n).on(n.OID.eq(d.TYPNAMESPACE)).where(d.TYPTYPE.eq("d")).and(n.NSPNAME.in(getInputSchemata())).orderBy(n.NSPNAME, d.TYPNAME)) {
            SchemaDefinition schema = getSchema(record.get(n.NSPNAME));
            DataTypeDefinition baseType = new DefaultDataTypeDefinition(this, schema, record.get(b.TYPNAME), record.get(DOMAINS.CHARACTER_MAXIMUM_LENGTH), record.get(DOMAINS.NUMERIC_PRECISION), record.get(DOMAINS.NUMERIC_SCALE), !record.get(d.TYPNOTNULL, boolean.class), record.get(d.TYPDEFAULT), name(record.get(n.NSPNAME), record.get(b.TYPNAME)));
            DefaultDomainDefinition domain = new DefaultDomainDefinition(schema, record.get(d.TYPNAME), baseType);
            domain.addCheckClause(record.get(src));
            result.add(domain);
        }
    }
    return result;
}

Example 3 with PgConstraint

use of org.jooq.meta.postgres.pg_catalog.tables.PgConstraint in project jOOQ by jOOQ.

the class PostgresDatabase method getIndexes0.

@Override
protected List<IndexDefinition> getIndexes0() throws SQLException {
    List<IndexDefinition> result = new ArrayList<>();
    PgIndex i = PG_INDEX.as("i");
    PgClass trel = PG_CLASS.as("trel");
    PgConstraint c = PG_CONSTRAINT.as("c");
    indexLoop: for (Record6<String, String, String, Boolean, String[], Integer[]> record : create().select(trel.pgNamespace().NSPNAME, trel.RELNAME, i.indexClass().RELNAME, i.INDISUNIQUE, array(select(field("pg_get_indexdef({0}, k + 1, true)", String.class, i.INDEXRELID)).from("generate_subscripts({0}, 1) as k", i.INDKEY).orderBy(field("k"))).as("columns"), field("{0}::int[]", Integer[].class, i.INDOPTION).as("asc_or_desc")).from(i).join(trel).on(trel.OID.eq(i.INDRELID)).where(trel.pgNamespace().NSPNAME.in(getInputSchemata())).and(getIncludeSystemIndexes() ? noCondition() : row(trel.pgNamespace().NSPNAME, i.indexClass().RELNAME).notIn(select(c.pgNamespace().NSPNAME, c.CONNAME).from(c))).orderBy(1, 2, 3)) {
        final SchemaDefinition tableSchema = getSchema(record.get(trel.pgNamespace().NSPNAME));
        if (tableSchema == null)
            continue indexLoop;
        final String indexName = record.get(i.indexClass().RELNAME);
        final String tableName = record.get(trel.RELNAME);
        final String[] columns = record.value5();
        final Integer[] options = record.value6();
        final TableDefinition table = getTable(tableSchema, tableName);
        if (table == null)
            continue indexLoop;
        final boolean unique = record.get(i.INDISUNIQUE);
        for (int k = 0; k < columns.length; k++) // the column expression, because it might be quoted
        if (table.getColumn(columns[k]) == null && table.getColumn(columns[k] = tryParseColumnName(columns[k])) == null)
            continue indexLoop;
        else // columns without options
        if (k >= options.length)
            continue indexLoop;
        result.add(new AbstractIndexDefinition(tableSchema, indexName, table, unique) {

            List<IndexColumnDefinition> indexColumns = new ArrayList<>();

            {
                for (int ordinal = 0; ordinal < columns.length; ordinal++) {
                    ColumnDefinition column = table.getColumn(columns[ordinal]);
                    // [#6307] Some background info on this bitwise operation here:
                    // https://stackoverflow.com/a/18128104/521799
                    SortOrder order = (options[ordinal] & 1) == 1 ? SortOrder.DESC : SortOrder.ASC;
                    indexColumns.add(new DefaultIndexColumnDefinition(this, column, order, ordinal + 1));
                }
            }

            @Override
            protected List<IndexColumnDefinition> getIndexColumns0() {
                return indexColumns;
            }
        });
    }
    return result;
}

Example 4 with PgConstraint

use of org.jooq.meta.postgres.pg_catalog.tables.PgConstraint in project jOOQ by jOOQ.

the class PostgresDatabase method loadCheckConstraints.

@Override
protected void loadCheckConstraints(DefaultRelations relations) throws SQLException {
    CheckConstraints cc = CHECK_CONSTRAINTS.as("cc");
    // [#10940] [#10992] Workaround for issue caused by re-using implicit join paths
    PgConstraint pc = PG_CONSTRAINT.as("pc");
    for (Record record : create().select(pc.pgClass().pgNamespace().NSPNAME, pc.pgClass().RELNAME, pc.CONNAME.as(cc.CONSTRAINT_NAME), replace(field("pg_get_constraintdef({0}.oid)", VARCHAR, pc), inline("CHECK "), inline("")).as(cc.CHECK_CLAUSE)).from(pc).where(pc.pgClass().pgNamespace().NSPNAME.in(getInputSchemata())).and(pc.CONTYPE.eq(inline("c"))).unionAll(getIncludeSystemCheckConstraints() ? select(pc.pgClass().pgNamespace().NSPNAME, pc.pgClass().RELNAME, cc.CONSTRAINT_NAME, cc.CHECK_CLAUSE).from(pc).join(cc).on(pc.CONNAME.eq(cc.CONSTRAINT_NAME)).and(pc.pgNamespace().NSPNAME.eq(cc.CONSTRAINT_NAME)).where(pc.pgNamespace().NSPNAME.in(getInputSchemata())).and(row(pc.pgClass().pgNamespace().NSPNAME, pc.pgClass().RELNAME, cc.CONSTRAINT_NAME).notIn(select(pc.pgClass().pgNamespace().NSPNAME, pc.pgClass().RELNAME, pc.CONNAME).from(pc).where(pc.CONTYPE.eq(inline("c"))))) : select(inline(""), inline(""), inline(""), inline("")).where(falseCondition())).orderBy(1, 2, 3)) {
        SchemaDefinition schema = getSchema(record.get(pc.pgClass().pgNamespace().NSPNAME));
        TableDefinition table = getTable(schema, record.get(pc.pgClass().RELNAME));
        if (table != null) {
            relations.addCheckConstraint(table, new DefaultCheckConstraintDefinition(schema, table, record.get(cc.CONSTRAINT_NAME), record.get(cc.CHECK_CLAUSE)));
        }
    }
}