@Override protected void loadForeignKeys(DefaultRelations relations) throws SQLException { // [#3520] PostgreSQL INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS contains incomplete information about foreign keys // The (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME) tuple is non-unique, in case two tables share the // same CONSTRAINT_NAME. // The JDBC driver implements this correctly through the pg_catalog, although the sorting and column name casing is wrong, too. Result<Record> result = create() .fetch(getConnection().getMetaData().getExportedKeys(null, null, null)) .sortAsc("key_seq") .sortAsc("fk_name") .sortAsc("fktable_name") .sortAsc("fktable_schem"); for (Record record : result) { SchemaDefinition foreignKeySchema = getSchema(record.get("fktable_schem", String.class)); SchemaDefinition uniqueKeySchema = getSchema(record.get("pktable_schem", String.class)); String foreignKey = record.get("fk_name", String.class); String foreignKeyTable = record.get("fktable_name", String.class); String foreignKeyColumn = record.get("fkcolumn_name", String.class); String uniqueKey = record.get("pk_name", String.class); TableDefinition referencingTable = getTable(foreignKeySchema, foreignKeyTable); if (referencingTable != null) { // [#986] Add the table name as a namespace prefix to the key // name. In Postgres, foreign key names are only unique per table ColumnDefinition referencingColumn = referencingTable.getColumn(foreignKeyColumn); relations.addForeignKey(foreignKeyTable + "__" + foreignKey, uniqueKey, referencingColumn, uniqueKeySchema); } } }
result.sortAsc(4); return createPrimaryKey(result, 3);
public List<Pizza> findOrderByIdAsc() { List<Result<Record>> values = dslContext.selectDistinct() .from(PIZZA) .innerJoin(BASE).on(BASE.ID.eq(PIZZA.BASE_ID)) .innerJoin(PIZZA_TOPPINGS).on(PIZZA_TOPPINGS.PIZZA_ID.eq(PIZZA.ID)) .innerJoin(TOPPING).on(TOPPING.ID.eq(PIZZA_TOPPINGS.TOPPINGS_ID)) .orderBy(PIZZA.ID.asc()) .fetch() .intoGroups(PIZZA.fields()) .values() .stream() .collect(Collectors.toList()); if (values.isEmpty()) { return Collections.emptyList(); } return values.stream() .map(r -> { Pizza pizza = r.into(PIZZA.ID, PIZZA.NAME, PIZZA.PRICE).get(0).into(Pizza.class); pizza.setBase(r.into(BASE.ID, BASE.NAME).get(0).into(Base.class)); pizza.setToppings(r.sortAsc(TOPPING.ID).into(Topping.class)); return pizza; }) .collect(Collectors.toList()); }
@Override public final UniqueKey<Record> getPrimaryKey() { SQLDialect family = configuration.family(); final String schema = getSchema() == null ? null : getSchema().getName(); Result<Record> result = meta(new MetaFunction() { @Override public Result<Record> run(DatabaseMetaData meta) throws SQLException { ResultSet rs; if (!inverseSchemaCatalog) rs = meta.getPrimaryKeys(null, schema, getName()); // [#2760] MySQL JDBC confuses "catalog" and "schema" else rs = meta.getPrimaryKeys(schema, null, getName()); return ctx.fetch( rs, String.class, // TABLE_CAT String.class, // TABLE_SCHEM String.class, // TABLE_NAME String.class, // COLUMN_NAME int.class, // KEY_SEQ String.class // PK_NAME ); } }); // Sort by KEY_SEQ result.sortAsc(4); return createPrimaryKey(result, 3); }