Search in sources :

Example 26 with TableDefinition

use of org.jooq.meta.TableDefinition in project jOOQ by jOOQ.

the class XMLDatabase method loadForeignKeys.

@Override
protected void loadForeignKeys(DefaultRelations relations) {
    for (ReferentialConstraint fk : info().getReferentialConstraints()) {
        if (getInputSchemata().contains(fk.getConstraintSchema())) {
            for (KeyColumnUsage usage : info().getKeyColumnUsages()) {
                if (StringUtils.equals(defaultIfNull(fk.getConstraintCatalog(), ""), defaultIfNull(usage.getConstraintCatalog(), "")) && StringUtils.equals(defaultIfNull(fk.getConstraintSchema(), ""), defaultIfNull(usage.getConstraintSchema(), "")) && StringUtils.equals(defaultIfNull(fk.getConstraintName(), ""), defaultIfNull(usage.getConstraintName(), ""))) {
                    SchemaDefinition foreignKeySchema = getSchema(fk.getConstraintSchema());
                    SchemaDefinition uniqueKeySchema = getSchema(fk.getUniqueConstraintSchema());
                    String foreignKey = usage.getConstraintName();
                    String foreignKeyTableName = usage.getTableName();
                    String foreignKeyColumn = usage.getColumnName();
                    String uniqueKey = fk.getUniqueConstraintName();
                    TableConstraint fktc = tableConstraint(fk.getConstraintCatalog(), fk.getConstraintSchema(), fk.getConstraintName());
                    TableConstraint uktc = tableConstraint(fk.getUniqueConstraintCatalog(), fk.getUniqueConstraintSchema(), fk.getUniqueConstraintName());
                    if (fktc != null && uktc != null) {
                        TableDefinition foreignKeyTable = getTable(foreignKeySchema, foreignKeyTableName);
                        TableDefinition uniqueKeyTable = getTable(uniqueKeySchema, uktc.getTableName());
                        if (foreignKeyTable != null && uniqueKeyTable != null)
                            relations.addForeignKey(foreignKey, foreignKeyTable, foreignKeyTable.getColumn(foreignKeyColumn), uniqueKey, uniqueKeyTable, !FALSE.equals(fktc.isEnforced()));
                    }
                }
            }
        }
    }
}
Also used : KeyColumnUsage(org.jooq.util.xml.jaxb.KeyColumnUsage) SchemaDefinition(org.jooq.meta.SchemaDefinition) ReferentialConstraint(org.jooq.util.xml.jaxb.ReferentialConstraint) TableDefinition(org.jooq.meta.TableDefinition) TableConstraint(org.jooq.util.xml.jaxb.TableConstraint)

Example 27 with TableDefinition

use of org.jooq.meta.TableDefinition in project jOOQ by jOOQ.

the class MySQLDatabase method getTables0.

@Override
protected List<TableDefinition> getTables0() throws SQLException {
    List<TableDefinition> result = new ArrayList<>();
    for (Record record : create().select(TABLES.TABLE_SCHEMA, TABLES.TABLE_NAME, TABLES.TABLE_COMMENT, when(TABLES.TABLE_TYPE.eq(inline("VIEW")), inline(TableType.VIEW.name())).else_(inline(TableType.TABLE.name())).as("table_type"), when(VIEWS.VIEW_DEFINITION.lower().like(inline("create%")), VIEWS.VIEW_DEFINITION).else_(inline("create view `").concat(TABLES.TABLE_NAME).concat("` as ").concat(VIEWS.VIEW_DEFINITION)).as(VIEWS.VIEW_DEFINITION)).from(TABLES).leftJoin(VIEWS).on(TABLES.TABLE_SCHEMA.eq(VIEWS.TABLE_SCHEMA)).and(TABLES.TABLE_NAME.eq(VIEWS.TABLE_NAME)).where(TABLES.TABLE_SCHEMA.in(workaroundFor5213(getInputSchemata()))).and(TABLES.TABLE_TYPE.ne(inline("SEQUENCE"))).orderBy(TABLES.TABLE_SCHEMA, TABLES.TABLE_NAME)) {
        SchemaDefinition schema = getSchema(record.get(TABLES.TABLE_SCHEMA));
        String name = record.get(TABLES.TABLE_NAME);
        String comment = record.get(TABLES.TABLE_COMMENT);
        TableType tableType = record.get("table_type", TableType.class);
        String source = record.get(VIEWS.VIEW_DEFINITION);
        MySQLTableDefinition table = new MySQLTableDefinition(schema, name, comment, tableType, source);
        result.add(table);
    }
    return result;
}
Also used : SchemaDefinition(org.jooq.meta.SchemaDefinition) TableType(org.jooq.TableOptions.TableType) ArrayList(java.util.ArrayList) TableDefinition(org.jooq.meta.TableDefinition) Record(org.jooq.Record)

Example 28 with TableDefinition

use of org.jooq.meta.TableDefinition in project jOOQ by jOOQ.

the class MySQLDatabase method loadUniqueKeys.

@Override
protected void loadUniqueKeys(DefaultRelations relations) throws SQLException {
    for (Record record : uniqueKeys(getInputSchemata())) {
        SchemaDefinition schema = getSchema(record.get(STATISTICS.TABLE_SCHEMA));
        String constraintName = record.get(STATISTICS.INDEX_NAME);
        String tableName = record.get(STATISTICS.TABLE_NAME);
        String columnName = record.get(STATISTICS.COLUMN_NAME);
        String key = getKeyName(tableName, constraintName);
        TableDefinition table = getTable(schema, tableName);
        if (table != null)
            relations.addUniqueKey(key, table, table.getColumn(columnName));
    }
}
Also used : SchemaDefinition(org.jooq.meta.SchemaDefinition) TableDefinition(org.jooq.meta.TableDefinition) Record(org.jooq.Record)

Example 29 with TableDefinition

use of org.jooq.meta.TableDefinition in project jOOQ by jOOQ.

the class PostgresDatabase method getTables0.

@Override
protected List<TableDefinition> getTables0() throws SQLException {
    List<TableDefinition> result = new ArrayList<>();
    Map<Name, PostgresTableDefinition> map = new HashMap<>();
    Select<Record6<String, String, String, String, String, String>> empty = select(inline(""), inline(""), inline(""), inline(""), inline(""), inline("")).where(falseCondition());
    for (Record record : create().select().from(select(TABLES.TABLE_SCHEMA, TABLES.TABLE_NAME, TABLES.TABLE_NAME.as("specific_name"), PG_DESCRIPTION.DESCRIPTION, when(TABLES.TABLE_TYPE.eq(inline("VIEW")), inline(TableType.VIEW.name())).else_(inline(TableType.TABLE.name())).as("table_type"), VIEWS.VIEW_DEFINITION).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(PG_NAMESPACE.OID)).leftJoin(PG_DESCRIPTION).on(PG_DESCRIPTION.OBJOID.eq(PG_CLASS.OID)).and(PG_DESCRIPTION.OBJSUBID.eq(0)).leftJoin(VIEWS).on(TABLES.TABLE_SCHEMA.eq(VIEWS.TABLE_SCHEMA)).and(TABLES.TABLE_NAME.eq(VIEWS.TABLE_NAME)).where(TABLES.TABLE_SCHEMA.in(getInputSchemata())).and(canUseTupleInPredicates() ? 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(PG_NAMESPACE.OID)).where(PG_CLASS.RELKIND.eq(inline("m")))) : noCondition()).unionAll(select(field("{0}::varchar", PG_NAMESPACE.NSPNAME.getDataType(), PG_NAMESPACE.NSPNAME), field("{0}::varchar", PG_CLASS.RELNAME.getDataType(), PG_CLASS.RELNAME), field("{0}::varchar", PG_CLASS.RELNAME.getDataType(), PG_CLASS.RELNAME), PG_DESCRIPTION.DESCRIPTION, inline(TableType.MATERIALIZED_VIEW.name()).as("table_type"), inline("")).from(PG_CLASS).join(PG_NAMESPACE).on(PG_CLASS.RELNAMESPACE.eq(PG_NAMESPACE.OID)).leftOuterJoin(PG_DESCRIPTION).on(PG_DESCRIPTION.OBJOID.eq(PG_CLASS.OID)).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(""), inline(TableType.FUNCTION.name()).as("table_type"), inline("")).from(ROUTINES).join(PG_NAMESPACE).on(ROUTINES.SPECIFIC_SCHEMA.eq(PG_NAMESPACE.NSPNAME)).join(PG_PROC).on(PG_PROC.PRONAMESPACE.eq(PG_NAMESPACE.OID)).and(PG_PROC.PRONAME.concat("_").concat(PG_PROC.OID).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);
        String comment = record.get(PG_DESCRIPTION.DESCRIPTION, String.class);
        TableType tableType = record.get("table_type", TableType.class);
        String source = record.get(VIEWS.VIEW_DEFINITION);
        if (source != null && !source.toLowerCase().startsWith("create"))
            source = "create view \"" + name + "\" as " + source;
        switch(tableType) {
            case FUNCTION:
                result.add(new PostgresTableValuedFunction(schema, name, record.get(ROUTINES.SPECIFIC_NAME), comment));
                break;
            case MATERIALIZED_VIEW:
                result.add(new PostgresMaterializedViewDefinition(schema, name, comment));
                break;
            default:
                PostgresTableDefinition t = new PostgresTableDefinition(schema, name, comment, tableType, source);
                result.add(t);
                map.put(name(schema.getName(), name), t);
                break;
        }
    }
    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(cn.OID)).join(i).on(i.INHRELID.eq(ct.OID)).join(pt).on(i.INHPARENT.eq(pt.OID)).join(pn).on(pt.RELNAMESPACE.eq(pn.OID)).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;
}
Also used : SchemaDefinition(org.jooq.meta.SchemaDefinition) TableType(org.jooq.TableOptions.TableType) HashMap(java.util.HashMap) ArrayList(java.util.ArrayList) Name(org.jooq.Name) BigInteger(java.math.BigInteger) PgInherits(org.jooq.meta.postgres.pg_catalog.tables.PgInherits) TableDefinition(org.jooq.meta.TableDefinition) PgClass(org.jooq.meta.postgres.pg_catalog.tables.PgClass) Record6(org.jooq.Record6) Record(org.jooq.Record) PgNamespace(org.jooq.meta.postgres.pg_catalog.tables.PgNamespace)

Example 30 with TableDefinition

use of org.jooq.meta.TableDefinition 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;
}
Also used : SchemaDefinition(org.jooq.meta.SchemaDefinition) ArrayList(java.util.ArrayList) SortOrder(org.jooq.SortOrder) PgConstraint(org.jooq.meta.postgres.pg_catalog.tables.PgConstraint) DefaultIndexColumnDefinition(org.jooq.meta.DefaultIndexColumnDefinition) ColumnDefinition(org.jooq.meta.ColumnDefinition) IndexColumnDefinition(org.jooq.meta.IndexColumnDefinition) BigInteger(java.math.BigInteger) DefaultIndexColumnDefinition(org.jooq.meta.DefaultIndexColumnDefinition) IndexColumnDefinition(org.jooq.meta.IndexColumnDefinition) AbstractIndexDefinition(org.jooq.meta.AbstractIndexDefinition) IndexDefinition(org.jooq.meta.IndexDefinition) PgConstraint(org.jooq.meta.postgres.pg_catalog.tables.PgConstraint) PgIndex(org.jooq.meta.postgres.pg_catalog.tables.PgIndex) AbstractIndexDefinition(org.jooq.meta.AbstractIndexDefinition) DefaultIndexColumnDefinition(org.jooq.meta.DefaultIndexColumnDefinition) PgClass(org.jooq.meta.postgres.pg_catalog.tables.PgClass) TableDefinition(org.jooq.meta.TableDefinition) Record6(org.jooq.Record6) Records.intoList(org.jooq.Records.intoList) Arrays.asList(java.util.Arrays.asList) ArrayList(java.util.ArrayList) List(java.util.List) Collectors.toList(java.util.stream.Collectors.toList)

Aggregations

TableDefinition (org.jooq.meta.TableDefinition)67 SchemaDefinition (org.jooq.meta.SchemaDefinition)47 Record (org.jooq.Record)44 ArrayList (java.util.ArrayList)23 IndexColumnDefinition (org.jooq.meta.IndexColumnDefinition)16 IndexDefinition (org.jooq.meta.IndexDefinition)14 List (java.util.List)12 ColumnDefinition (org.jooq.meta.ColumnDefinition)12 IOException (java.io.IOException)10 DefaultIndexColumnDefinition (org.jooq.meta.DefaultIndexColumnDefinition)10 Result (org.jooq.Result)9 CatalogDefinition (org.jooq.meta.CatalogDefinition)9 EnumDefinition (org.jooq.meta.EnumDefinition)9 RoutineDefinition (org.jooq.meta.RoutineDefinition)9 Arrays.asList (java.util.Arrays.asList)8 TableType (org.jooq.TableOptions.TableType)8 AbstractIndexDefinition (org.jooq.meta.AbstractIndexDefinition)8 DefaultCheckConstraintDefinition (org.jooq.meta.DefaultCheckConstraintDefinition)8 PackageDefinition (org.jooq.meta.PackageDefinition)8 SequenceDefinition (org.jooq.meta.SequenceDefinition)8