Examples with PgNamespace org.jooq.util.postgres.pg_catalog.tables.PgNamespace used on opensource projects

Example 1 with PgNamespace

use of org.jooq.util.postgres.pg_catalog.tables.PgNamespace in project jOOQ by jOOQ.

the class PostgresDatabase method getTables0.

@Override
protected List<TableDefinition> getTables0() throws SQLException {
    List<TableDefinition> result = new ArrayList<TableDefinition>();
    Map<Name, PostgresTableDefinition> map = new HashMap<Name, PostgresTableDefinition>();
    Select<Record6<String, String, String, Boolean, Boolean, String>> empty = select(inline(""), inline(""), inline(""), inline(false), inline(false), inline("")).where(falseCondition());
    for (Record record : create().select().from(select(TABLES.TABLE_SCHEMA, TABLES.TABLE_NAME, TABLES.TABLE_NAME.as("specific_name"), inline(false).as("table_valued_function"), inline(false).as("materialized_view"), PG_DESCRIPTION.DESCRIPTION).from(TABLES).join(PG_NAMESPACE).on(TABLES.TABLE_SCHEMA.eq(PG_NAMESPACE.NSPNAME)).join(PG_CLASS).on(PG_CLASS.RELNAME.eq(TABLES.TABLE_NAME)).and(PG_CLASS.RELNAMESPACE.eq(oid(PG_NAMESPACE))).leftOuterJoin(PG_DESCRIPTION).on(PG_DESCRIPTION.OBJOID.eq(oid(PG_CLASS))).and(PG_DESCRIPTION.OBJSUBID.eq(0)).where(TABLES.TABLE_SCHEMA.in(getInputSchemata())).and(row(TABLES.TABLE_SCHEMA, TABLES.TABLE_NAME).notIn(select(PG_NAMESPACE.NSPNAME, PG_CLASS.RELNAME).from(PG_CLASS).join(PG_NAMESPACE).on(PG_CLASS.RELNAMESPACE.eq(oid(PG_NAMESPACE))).where(PG_CLASS.RELKIND.eq(inline("m"))))).unionAll(select(PG_NAMESPACE.NSPNAME, PG_CLASS.RELNAME, PG_CLASS.RELNAME, inline(false).as("table_valued_function"), inline(true).as("materialized_view"), PG_DESCRIPTION.DESCRIPTION).from(PG_CLASS).join(PG_NAMESPACE).on(PG_CLASS.RELNAMESPACE.eq(oid(PG_NAMESPACE))).leftOuterJoin(PG_DESCRIPTION).on(PG_DESCRIPTION.OBJOID.eq(oid(PG_CLASS))).and(PG_DESCRIPTION.OBJSUBID.eq(0)).where(PG_NAMESPACE.NSPNAME.in(getInputSchemata())).and(PG_CLASS.RELKIND.eq(inline("m")))).unionAll(tableValuedFunctions() ? select(ROUTINES.ROUTINE_SCHEMA, ROUTINES.ROUTINE_NAME, ROUTINES.SPECIFIC_NAME, inline(true).as("table_valued_function"), inline(false).as("materialized_view"), inline("")).from(ROUTINES).join(PG_NAMESPACE).on(ROUTINES.SPECIFIC_SCHEMA.eq(PG_NAMESPACE.NSPNAME)).join(PG_PROC).on(PG_PROC.PRONAMESPACE.eq(oid(PG_NAMESPACE))).and(PG_PROC.PRONAME.concat("_").concat(oid(PG_PROC)).eq(ROUTINES.SPECIFIC_NAME)).where(ROUTINES.ROUTINE_SCHEMA.in(getInputSchemata())).and(PG_PROC.PRORETSET) : empty).asTable("tables")).orderBy(1, 2).fetch()) {
        SchemaDefinition schema = getSchema(record.get(TABLES.TABLE_SCHEMA));
        String name = record.get(TABLES.TABLE_NAME);
        boolean tableValuedFunction = record.get("table_valued_function", boolean.class);
        boolean materializedView = record.get("materialized_view", boolean.class);
        String comment = record.get(PG_DESCRIPTION.DESCRIPTION, String.class);
        if (tableValuedFunction) {
            result.add(new PostgresTableValuedFunction(schema, name, record.get(ROUTINES.SPECIFIC_NAME), comment));
        } else if (materializedView) {
            result.add(new PostgresMaterializedViewDefinition(schema, name, comment));
        } else {
            PostgresTableDefinition t = new PostgresTableDefinition(schema, name, comment);
            result.add(t);
            map.put(name(schema.getName(), name), t);
        }
    }
    PgClass ct = PG_CLASS.as("ct");
    PgNamespace cn = PG_NAMESPACE.as("cn");
    PgInherits i = PG_INHERITS.as("i");
    PgClass pt = PG_CLASS.as("pt");
    PgNamespace pn = PG_NAMESPACE.as("pn");
    // don't execute the following query:
    if (is84()) {
        for (Record5<String, String, String, String, Integer> inheritance : create().select(cn.NSPNAME, ct.RELNAME, pn.NSPNAME, pt.RELNAME, max(i.INHSEQNO).over().partitionBy(i.INHRELID).as("m")).from(ct).join(cn).on(ct.RELNAMESPACE.eq(oid(cn))).join(i).on(i.INHRELID.eq(oid(ct))).join(pt).on(i.INHPARENT.eq(oid(pt))).join(pn).on(pt.RELNAMESPACE.eq(oid(pn))).where(cn.NSPNAME.in(getInputSchemata())).and(pn.NSPNAME.in(getInputSchemata())).fetch()) {
            Name child = name(inheritance.value1(), inheritance.value2());
            Name parent = name(inheritance.value3(), inheritance.value4());
            if (inheritance.value5() > 1) {
                log.info("Multiple inheritance", "Multiple inheritance is not supported by jOOQ: " + child + " inherits from " + parent);
            } else {
                PostgresTableDefinition childTable = map.get(child);
                PostgresTableDefinition parentTable = map.get(parent);
                if (childTable != null && parentTable != null) {
                    childTable.setParentTable(parentTable);
                    parentTable.getChildTables().add(childTable);
                }
            }
        }
    }
    return result;
}

Example 2 with PgNamespace

use of org.jooq.util.postgres.pg_catalog.tables.PgNamespace in project jOOQ by jOOQ.

the class PostgresTableValuedFunction method getElements0.

@Override
public List<ColumnDefinition> getElements0() throws SQLException {
    List<ColumnDefinition> result = new ArrayList<ColumnDefinition>();
    Routines r = ROUTINES;
    Parameters p = PARAMETERS;
    PgNamespace pg_n = PG_NAMESPACE;
    PgProc pg_p = PG_PROC;
    Columns c = COLUMNS;
    for (Record record : create().select(p.PARAMETER_NAME, rowNumber().over(partitionBy(p.SPECIFIC_NAME).orderBy(p.ORDINAL_POSITION)).as(p.ORDINAL_POSITION), p.DATA_TYPE, p.CHARACTER_MAXIMUM_LENGTH, p.NUMERIC_PRECISION, p.NUMERIC_SCALE, inline("true").as(c.IS_NULLABLE), (((PostgresDatabase) getDatabase()).is94() ? PARAMETERS.PARAMETER_DEFAULT : inline((String) null)).as(c.COLUMN_DEFAULT), p.UDT_SCHEMA, p.UDT_NAME).from(r).join(p).on(row(r.SPECIFIC_CATALOG, r.SPECIFIC_SCHEMA, r.SPECIFIC_NAME).eq(p.SPECIFIC_CATALOG, p.SPECIFIC_SCHEMA, p.SPECIFIC_NAME)).join(pg_n).on(r.SPECIFIC_SCHEMA.eq(pg_n.NSPNAME)).join(pg_p).on(pg_p.PRONAMESPACE.eq(oid(pg_n))).and(pg_p.PRONAME.eq(r.ROUTINE_NAME)).where(r.SPECIFIC_NAME.eq(specificName)).and(p.PARAMETER_MODE.ne("IN")).and(pg_p.PRORETSET).unionAll(// from INFORMATION_SCHEMA.TABLES
    select(nvl(c.COLUMN_NAME, getName()).as(c.COLUMN_NAME), // Type inference doesn't seem to be possible here with Java 8... ?
    nvl(c.ORDINAL_POSITION, DSL.<Integer>inline(1)).as(c.ORDINAL_POSITION), nvl(c.DATA_TYPE, r.DATA_TYPE).as(c.DATA_TYPE), nvl(c.CHARACTER_MAXIMUM_LENGTH, r.CHARACTER_MAXIMUM_LENGTH).as(c.CHARACTER_MAXIMUM_LENGTH), nvl(c.NUMERIC_PRECISION, r.NUMERIC_PRECISION).as(c.NUMERIC_PRECISION), nvl(c.NUMERIC_SCALE, r.NUMERIC_SCALE).as(c.NUMERIC_SCALE), nvl(c.IS_NULLABLE, "true").as(c.IS_NULLABLE), nvl(c.COLUMN_DEFAULT, inline((String) null)).as(c.COLUMN_DEFAULT), nvl(c.UDT_SCHEMA, inline((String) null)).as(c.UDT_SCHEMA), nvl(c.UDT_NAME, r.UDT_NAME).as(c.UDT_NAME)).from(r).leftOuterJoin(c).on(row(r.TYPE_UDT_CATALOG, r.TYPE_UDT_SCHEMA, r.TYPE_UDT_NAME).eq(c.TABLE_CATALOG, c.TABLE_SCHEMA, c.TABLE_NAME)).join(pg_n).on(r.SPECIFIC_SCHEMA.eq(pg_n.NSPNAME)).join(pg_p).on(pg_p.PRONAMESPACE.eq(oid(pg_n))).and(pg_p.PRONAME.concat("_").concat(oid(pg_p)).eq(r.SPECIFIC_NAME)).where(r.SPECIFIC_NAME.eq(specificName)).and(row(r.SPECIFIC_CATALOG, r.SPECIFIC_SCHEMA, r.SPECIFIC_NAME).notIn(select(p.SPECIFIC_CATALOG, p.SPECIFIC_SCHEMA, p.SPECIFIC_NAME).from(p).where(p.PARAMETER_MODE.eq("OUT")))).and(pg_p.PRORETSET)).orderBy(2)) {
        SchemaDefinition typeSchema = null;
        String schemaName = record.get(p.UDT_SCHEMA);
        if (schemaName != null)
            typeSchema = getDatabase().getSchema(schemaName);
        DataTypeDefinition type = new DefaultDataTypeDefinition(getDatabase(), typeSchema, record.get(p.DATA_TYPE), record.get(p.CHARACTER_MAXIMUM_LENGTH), record.get(p.NUMERIC_PRECISION), record.get(p.NUMERIC_SCALE), record.get(c.IS_NULLABLE, boolean.class), record.get(c.COLUMN_DEFAULT), name(record.get(p.UDT_SCHEMA), record.get(p.UDT_NAME)));
        ColumnDefinition column = new DefaultColumnDefinition(getDatabase().getTable(getSchema(), getName()), record.get(p.PARAMETER_NAME), record.get(p.ORDINAL_POSITION, int.class), type, defaultString(record.get(c.COLUMN_DEFAULT)).startsWith("nextval"), null);
        result.add(column);
    }
    return result;
}

Example 3 with PgNamespace

use of org.jooq.util.postgres.pg_catalog.tables.PgNamespace in project jOOQ by jOOQ.

the class PostgresDatabase method getDomains0.

@SuppressWarnings("unchecked")
@Override
protected List<DomainDefinition> getDomains0() throws SQLException {
    List<DomainDefinition> result = new ArrayList<DomainDefinition>();
    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);
        for (Record record : create().withRecursive("domains", "domain_id", "base_id", "typbasetype", "src").as(select(oid(d), oid(d), d.TYPBASETYPE, array(c.CONSRC)).from(d).join(n).on(oid(n).eq(d.TYPNAMESPACE)).leftJoin(c).on(oid(d).eq(c.CONTYPID)).where(d.TYPTYPE.eq("d")).and(n.NSPNAME.in(getInputSchemata())).unionAll(select(field(name("domains", "domain_id"), Long.class), oid(d), d.TYPBASETYPE, decode().when(c.CONSRC.isNull(), src).otherwise(arrayAppend(src, c.CONSRC))).from(name("domains")).join(d).on(field(name("domains", d.TYPBASETYPE.getName())).eq(oid(d))).leftJoin(c).on(oid(d).eq(c.CONTYPID)))).select(n.NSPNAME, d.TYPNAME, d.TYPNOTNULL, d.TYPDEFAULT, b.TYPNAME, b.TYPLEN, src).from(d).join(name("domains")).on(field(name("domains", "typbasetype")).eq(0)).and(field(name("domains", "domain_id")).eq(oid(d))).join(b).on(field(name("domains", "base_id")).eq(oid(b))).join(n).on(oid(n).eq(d.TYPNAMESPACE)).where(d.TYPTYPE.eq("d")).and(n.NSPNAME.in(getInputSchemata()))) {
            SchemaDefinition schema = getSchema(record.get(n.NSPNAME));
            DataTypeDefinition baseType = new DefaultDataTypeDefinition(this, schema, record.get(b.TYPNAME), record.get(b.TYPLEN), record.get(b.TYPLEN), // ?
            0, !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 4 with PgNamespace

use of org.jooq.util.postgres.pg_catalog.tables.PgNamespace in project jOOQ by jOOQ.

the class PostgresMaterializedViewDefinition method getElements0.

@Override
public List<ColumnDefinition> getElements0() throws SQLException {
    List<ColumnDefinition> result = new ArrayList<ColumnDefinition>();
    Columns col = COLUMNS;
    PgAttribute a = PG_ATTRIBUTE.as("a");
    PgAttrdef ad = PG_ATTRDEF.as("ad");
    PgType t = PG_TYPE.as("t");
    PgType bt = PG_TYPE.as("bt");
    PgClass c = PG_CLASS.as("c");
    PgCollation co = PG_COLLATION.as("co");
    PgNamespace nt = PG_NAMESPACE.as("nt");
    PgNamespace nc = PG_NAMESPACE.as("nc");
    PgNamespace nbt = PG_NAMESPACE.as("nbt");
    PgNamespace nco = PG_NAMESPACE.as("nco");
    for (Record record : create().select(field("({0})::information_schema.sql_identifier", col.COLUMN_NAME.getDataType(), a.ATTNAME).as(col.COLUMN_NAME), field("({0})::information_schema.cardinal_number", col.ORDINAL_POSITION.getDataType(), a.ATTNUM).as(col.ORDINAL_POSITION), field("({0})::information_schema.character_data", col.DATA_TYPE.getDataType(), when(t.TYPTYPE.eq(inline("d")), when(bt.TYPELEM.ne(inline(0L)).and(bt.TYPLEN.eq(inline((short) -1))), inline("ARRAY")).when(nbt.NSPNAME.eq(inline("pg_catalog")), field("format_type({0}, NULL::integer)", String.class, t.TYPBASETYPE)).otherwise(inline("USER-DEFINED"))).otherwise(when(t.TYPELEM.ne(inline(0L)).and(t.TYPLEN.eq(inline((short) -1))), inline("ARRAY")).when(nt.NSPNAME.eq(inline("pg_catalog")), field("format_type({0}, NULL::integer)", String.class, a.ATTTYPID)).otherwise(inline("USER-DEFINED")))).as(col.DATA_TYPE), field("(information_schema._pg_char_max_length(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*)))::information_schema.cardinal_number", col.CHARACTER_MAXIMUM_LENGTH.getDataType()).as(col.CHARACTER_MAXIMUM_LENGTH), field("(information_schema._pg_numeric_precision(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*)))::information_schema.cardinal_number", col.NUMERIC_PRECISION.getDataType()).as(col.NUMERIC_PRECISION), field("(information_schema._pg_numeric_scale(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*)))::information_schema.cardinal_number", col.NUMERIC_SCALE.getDataType()).as(col.NUMERIC_SCALE), field("({0})::information_schema.yes_or_no", col.IS_NULLABLE.getDataType(), when(condition(a.ATTNOTNULL).or(t.TYPTYPE.eq(inline("d")).and(t.TYPNOTNULL)), inline("NO")).otherwise(inline("YES"))).as(col.IS_NULLABLE), field("(pg_get_expr({0}, {1}))::information_schema.character_data", col.COLUMN_DEFAULT.getDataType(), ad.ADBIN, ad.ADRELID).as(col.COLUMN_DEFAULT), field("({0})::information_schema.sql_identifier", col.UDT_SCHEMA.getDataType(), nvl(nbt.NSPNAME, nt.NSPNAME)).as(col.UDT_SCHEMA), field("({0})::information_schema.sql_identifier", col.UDT_NAME.getDataType(), nvl(bt.TYPNAME, t.TYPNAME)).as(col.UDT_NAME), PG_DESCRIPTION.DESCRIPTION).from(a.leftJoin(ad).on(a.ATTRELID.eq(ad.ADRELID)).and(a.ATTNUM.eq(ad.ADNUM)).join(c.join(nc).on(c.RELNAMESPACE.eq(oid(nc)))).on(a.ATTRELID.eq(oid(c))).join(t.join(nt).on(t.TYPNAMESPACE.eq(oid(nt)))).on(a.ATTTYPID.eq(oid(t)))).leftJoin(bt.join(nbt).on(bt.TYPNAMESPACE.eq(oid(nbt)))).on(t.TYPTYPE.eq(inline("d")).and(t.TYPBASETYPE.eq(oid(bt)))).leftJoin(co.join(nco).on(co.COLLNAMESPACE.eq(oid(nco)))).on(a.ATTCOLLATION.eq(oid(co)).and(nco.NSPNAME.ne(inline("pg_catalog")).or(co.COLLNAME.ne(inline("default"))))).leftJoin(PG_DESCRIPTION).on(PG_DESCRIPTION.OBJOID.eq(oid(c))).and(PG_DESCRIPTION.OBJSUBID.eq(a.ATTNUM.coerce(PG_DESCRIPTION.OBJSUBID))).where(not(condition("pg_is_other_temp_schema({0})", oid(nc))).and(a.ATTNUM.gt(inline((short) 0))).and(not(a.ATTISDROPPED)).and(c.RELKIND.eq(inline("m"))).and(nc.NSPNAME.in(getSchema().getName())).and(c.RELNAME.eq(getName()))).orderBy(a.ATTNUM)) {
        SchemaDefinition typeSchema = null;
        String schemaName = record.get(COLUMNS.UDT_SCHEMA);
        if (schemaName != null)
            typeSchema = getDatabase().getSchema(schemaName);
        DataTypeDefinition type = new DefaultDataTypeDefinition(getDatabase(), typeSchema, record.get(COLUMNS.DATA_TYPE), record.get(COLUMNS.CHARACTER_MAXIMUM_LENGTH), record.get(COLUMNS.NUMERIC_PRECISION), record.get(COLUMNS.NUMERIC_SCALE), record.get(COLUMNS.IS_NULLABLE, boolean.class), record.get(COLUMNS.COLUMN_DEFAULT), name(record.get(COLUMNS.UDT_SCHEMA), record.get(COLUMNS.UDT_NAME)));
        ColumnDefinition column = new DefaultColumnDefinition(getDatabase().getTable(getSchema(), getName()), record.get(COLUMNS.COLUMN_NAME), record.get(COLUMNS.ORDINAL_POSITION, int.class), type, defaultString(record.get(COLUMNS.COLUMN_DEFAULT)).startsWith("nextval"), record.get(PG_DESCRIPTION.DESCRIPTION));
        result.add(column);
    }
    return result;
}