Example 1 with DbFkInfo
use of org.folio.cql2pgjson.model.DbFkInfo in project raml-module-builder by folio-org.
the class CQL2PgJSON method pgSubQuery.
private String pgSubQuery(CQLTermNode node, List<DbFkInfo> fks, boolean childToParent) throws QueryValidationException {
String currentTableName = dbTable.getTableName();
Table targetTable = null;
StringBuilder sb = new StringBuilder();
if (childToParent) {
// child to parent
targetTable = DbSchemaUtils.getTable(dbSchema, fks.get(fks.size() - 1).getTargetTable());
for (DbFkInfo fk : fks) {
sb.append(currentTableName).append('.').append(fk.getField().replace(".", "_")).append(" IN ( SELECT id FROM ").append(fk.getTargetTable()).append(" WHERE ");
currentTableName = fk.getTargetTable();
}
} else {
// parent to child
targetTable = DbSchemaUtils.getTable(dbSchema, fks.get(0).getTable());
for (int i = fks.size() - 1; i >= 0; i--) {
DbFkInfo fk = fks.get(i);
sb.append(currentTableName).append(".id IN ( SELECT ").append(fks.get(i).getField()).append(" FROM ").append(fk.getTable()).append(" WHERE ");
currentTableName = fk.getTable();
}
}
String[] foreignTarget = node.getIndex().split("\\.", 2);
sb.append(indexNodeForForeignTable(node, targetTable, foreignTarget));
for (int i = 0; i < fks.size(); i++) {
sb.append(')');
}
return sb.toString();
}Example 2 with DbFkInfo
use of org.folio.cql2pgjson.model.DbFkInfo in project raml-module-builder by folio-org.
the class DbSchemaUtils method findForeignKeys.
/**
* Find foreign keys info about foreignKeys via its "fieldName" or "targetPath" property.
*/
private static List<DbFkInfo> findForeignKeys(Schema dbSchema, Table table, ForeignKeys foreignKeys) {
// join one table with a second table only?
if (foreignKeys.getFieldName() != null) {
List<DbFkInfo> list = new ArrayList<>();
list.add(new DbFkInfo(table.getTableName(), foreignKeys.getFieldName(), foreignKeys.getTargetTable()));
return list;
}
// join with several tables using a path?
List<String> targetPath = foreignKeys.getTargetPath();
if (targetPath == null || targetPath.isEmpty()) {
return Collections.emptyList();
}
List<DbFkInfo> list = new ArrayList<>();
for (String fieldName : targetPath) {
DbFkInfo dbFkInfo = getForeignKey(table, fieldName);
list.add(dbFkInfo);
table = getTable(dbSchema, dbFkInfo.getTargetTable());
if (table == null) {
throw new IllegalStateException("table not found for tableName=" + dbFkInfo.getTargetTable() + ", targetPath=" + targetPath);
}
}
return list;
}Example 3 with DbFkInfo
use of org.folio.cql2pgjson.model.DbFkInfo in project raml-module-builder by folio-org.
the class DbSchemaUtilsTest method testFindForeignKeys.
@Test
public void testFindForeignKeys() throws Exception {
// pathA: f -> e -> d -> c -> b -> a
// pathB: f -> e -> c -> b -> a
Schema dbSchema = schema("templates/db_scripts/foreignKeyPath.json");
// child->parent with targetAlias
List<DbFkInfo> list = DbSchemaUtils.findForeignKeysFromSourceTableToTargetAlias(dbSchema, "f", "a");
assertEquals("f", list.get(0).getTable());
assertEquals("e", list.get(1).getTable());
assertEquals("c", list.get(2).getTable());
assertEquals("b", list.get(3).getTable());
// child->parent with targetAlias
list = DbSchemaUtils.findForeignKeysFromSourceTableToTargetAlias(dbSchema, "f", "bAlias");
assertEquals("f", list.get(0).getTable());
assertEquals("e", list.get(1).getTable());
assertEquals("c", list.get(2).getTable());
// parent->child with sourceAlias
list = DbSchemaUtils.findForeignKeysFromSourceAliasToTargetTable(dbSchema, "eAlias", "b");
assertEquals("e", list.get(0).getTable());
assertEquals("d", list.get(1).getTable());
assertEquals("c", list.get(2).getTable());
// parent->child with sourceAlias
list = DbSchemaUtils.findForeignKeysFromSourceAliasToTargetTable(dbSchema, "e2Alias", "b");
assertEquals("e", list.get(0).getTable());
assertEquals("c", list.get(1).getTable());
list = DbSchemaUtils.findForeignKeysFromSourceTableToTargetAlias(dbSchema, "nonexistingTable", "a");
assertThat(list, is(empty()));
}