use of org.molgenis.emx2.Table in project molgenis-emx2 by molgenis.
the class LinkedDataFragmentsApi method ttlTable.
private static String ttlTable(Request request, Response response) {
Table table = getTable(request);
StringWriter sw = new StringWriter();
LinkedDataService.getTtlForTable(table, new PrintWriter(sw));
return sw.getBuffer().toString();
}
use of org.molgenis.emx2.Table in project molgenis-emx2 by molgenis.
the class SqlColumnRefBackExecutor method createRefBackColumnConstraints.
// will create a dummy array column matching the toColumn we will link to
// will create a before insert trigger to update all REF instances in the other table that needs
// updating
static void createRefBackColumnConstraints(DSLContext jooq, Column ref) {
try {
// get ref table
validateColumn(ref);
String columNames = ref.getReferences().stream().map(Reference::getName).collect(Collectors.joining(","));
// get the via column which is also in the 'toTable'
String refBackColumnName = ref.getRefBack();
if (refBackColumnName == null) {
throw new MolgenisException("Create column failed: Create of REFBACK column '" + ref.getQualifiedName() + "' failed because refBack was not set.");
}
// create the trigger so that insert/update/delete on REFBACK column updates the
// relationship
Column refBack = ref.getRefBackColumn();
if (refBack == null) {
throw new MolgenisException("Set refBack on column '" + ref.getTableName() + "." + ref.getName() + "'failed: refBack column '" + ref.getRefBack() + "'not found");
}
if (refBack.isRef()) {
createTriggerForRef(jooq, ref, true);
createTriggerForRef(jooq, ref, false);
} else if (refBack.isRefArray()) {
createTriggerForRefArray(jooq, ref);
} else {
throw new MolgenisException("Create column failed: Create of REFBACK column(s) '" + ref.getTableName() + "." + getNames(ref) + "' failed because refBack '" + ref.getRefBack() + "' was not of type REF, REF_ARRAY");
}
} catch (DataAccessException dae) {
throw new SqlMolgenisException("REFBACK column(s) '" + getNames(ref) + "' could not be created in table '" + ref.getTableName() + "'", dae);
}
}
use of org.molgenis.emx2.Table in project molgenis-emx2 by molgenis.
the class SqlColumnRefBackExecutor method createTriggerForRef.
private static void createTriggerForRef(DSLContext jooq, Column column, boolean isUpdateTrigger) {
// check if any refBack array has non-existing pkey
// remove refs from other table if not any more in refBack array
// update refs from other table to new identifier ( automatic via cascade , nothing to
// do here)
// add refs from other table if new in refBack array
String schemaName = column.getTable().getSchema().getName();
String triggerName = refBackUpdateTriggerName(column) + (isUpdateTrigger ? "_UPDATE" : "_INSERT");
List<Reference> columns = column.getReferences();
// begin
String sql = "CREATE FUNCTION {0}() RETURNS trigger AS $BODY$ " + "\nDECLARE my_row RECORD;" + "\nBEGIN";
// add check if the refs actually exist
sql += "\n\t-- raise error for first refColumn value that does not in refTable key values " + "\n\tFOR my_row IN SELECT {1} FROM newtab EXCEPT (SELECT {2} FROM {3}) LOOP" + "\n\t\tRAISE EXCEPTION USING ERRCODE='23503', " + "\n\t\tMESSAGE = 'insert on table '||{4}||' violates foreign key constraint for refback column(s)'," + "\n\t\tDETAIL = 'Key ('||{5}||')=('|| {6} ||') is not present in table '||{7}||', column '||{8};" + "\n\tEND LOOP;";
// in case of update, we should also remove the references not in the 'old'
if (isUpdateTrigger) {
sql += "\n\t-- remove ref to 'oldtable'.key if not anymore in refarray" + "\n\tFOR my_row IN SELECT {13},{1} FROM oldtab EXCEPT (SELECT {13},{1} FROM newtab) LOOP" + "\n\t\tUPDATE {3} set {9} WHERE {12};" + "\n\tEND LOOP;";
sql += "\n\t-- set to ref to 'newtable'.key if in refBack values list" + "\n\tFOR my_row IN SELECT {13},{1} FROM newtab EXCEPT (SELECT {13},{1} FROM oldtab) LOOP" + "\n\t\tUPDATE {3} set {11} WHERE {12};" + "\n\tEND LOOP;";
} else {
// in case of insert
sql += "\n\t-- set to ref to 'newtable'.key if in refBack values list" + "\n\tFOR my_row IN SELECT {13},{1} FROM newtab LOOP" + "\n\t\tUPDATE {3} set {11} WHERE {12};" + "\n\tEND LOOP;";
}
// end
sql += "\n\tRETURN NEW;" + "\nEND; $BODY$ LANGUAGE plpgsql;";
RowCountQuery q = jooq.query(sql, // 0 function name
name(schemaName, triggerName), // 1 selection of unnested inputs
keyword(columns.stream().map(r -> "unnest(" + name(r.getName()) + ") as " + name(r.getName())).collect(Collectors.joining(","))), // 2 foreign key column names refBack refers to
keyword(columns.stream().map(r -> name(r.getRefTo()).toString()).collect(Collectors.joining(","))), // 3 refTable
table(name(schemaName, column.getRefTableName())), // 4 inline string of table for debug message
inline(column.getTable().getTableName()), // 5 inline columns
keyword(columns.stream().map(r -> inline(r.getName()).toString()).collect(Collectors.joining("||','||"))), // 6 concat of the error column values
keyword(columns.stream().map(r -> "COALESCE(my_row." + name(r.getRefTo()).toString() + ",'NULL')").collect(Collectors.joining("||','||"))), // 7 inline refTable
inline(column.getRefTable().getTableName()), // 8 inline toColumns
keyword(columns.stream().map(r -> inline(r.getRefTo()).toString()).collect(Collectors.joining("||','||"))), // 9 set refBack to null
keyword(column.getRefBackColumn().getReferences().stream().map(r -> name(r.getName()) + "=NULL").collect(Collectors.joining(","))), // 10 where references old key and not new key
keyword(column.getRefBackColumn().getReferences().stream().map(r -> name(r.getName()) + "=OLD." + name(r.getRefTo())).collect(Collectors.joining(" AND "))), // 11 set to point to this.key(s)
keyword(column.getRefBackColumn().getReferences().stream().map(r -> name(r.getName()) + "=my_row." + name(r.getRefTo())).collect(Collectors.joining(","))), // 12 where reftable.key=refback
keyword(columns.stream().map(r -> name(r.getRefTo()) + "=my_row." + name(r.getName())).collect(Collectors.joining(" AND "))), // 13 keys of this table
keyword(column.getRefBackColumn().getReferences().stream().map(r -> name(r.getRefTo()).toString()).collect(Collectors.joining(","))), // 14 where this keys
keyword(column.getRefBackColumn().getReferences().stream().map(Reference::getRefTo).map(s -> name(s) + "=NEW." + name(s)).collect(Collectors.joining(" AND "))));
// System.out.println("sql: " + q.getSQL());
q.execute();
String trigger = isUpdateTrigger ? "CREATE TRIGGER {0} " + "\n\tAFTER UPDATE ON {2}" + "\n\tREFERENCING NEW TABLE AS newtab OLD TABLE AS oldtab" + "\n\tEXECUTE PROCEDURE {3}()" : "CREATE TRIGGER {0} " + "\n\tAFTER INSERT ON {2}" + "\n\tREFERENCING NEW TABLE AS newtab" + "\n\tEXECUTE PROCEDURE {3}()";
jooq.execute(trigger, // 0 name of the trigger
name(triggerName), // 1 the columns of the refBack that should be set to trigger the trigger
keyword(columns.stream().map(r -> name(r.getName()).toString()).collect(Collectors.joining(","))), // name of the table
name(schemaName, column.getTable().getTableName()), // reference to the trigger function
name(schemaName, triggerName));
}
use of org.molgenis.emx2.Table in project molgenis-emx2 by molgenis.
the class CatalogueSiteMapTest method buildSiteMap.
@Test
public void buildSiteMap() {
Schema schema = mock(Schema.class);
Table table = mock(Table.class);
Query query = mock(Query.class);
List<Row> row = Collections.singletonList(new Row("pid", "my-cohort-pid"));
when(schema.getTable("Cohorts")).thenReturn(table);
when(table.select(any())).thenReturn(query);
when(query.retrieveRows()).thenReturn(row);
CatalogueSiteMap catalogueSiteMap = new CatalogueSiteMap(schema, "https://my/base/url");
String expected = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n" + "<urlset xmlns=\"http://www.sitemaps.org/schemas/sitemap/0.9\" >\n" + " <url>\n" + " <loc>https://my/base/url/ssr-catalogue/Cohorts/my-cohort-pid</loc>\n" + " </url>\n" + "</urlset>";
assertEquals(expected, catalogueSiteMap.buildSiteMap());
}
use of org.molgenis.emx2.Table in project molgenis-emx2 by molgenis.
the class Column method getReferences.
/**
* will return self in case of single, and multiple in case of composite key wrapper
*/
public List<Reference> getReferences() {
// no ref
if (getRefTable() == null) {
return new ArrayList<>();
}
List<Column> pkeys = getRefTable().getPrimaryKeyColumns();
List<Reference> refColumns = new ArrayList<>();
// check if primary key exists
if (pkeys.size() == 0) {
throw new MolgenisException("Error in column '" + getName() + "': Reference to " + getRefTableName() + " fails because that table has no primary key");
}
// create the refs
Column refLink = getRefLinkColumn();
for (Column keyPart : pkeys) {
if (keyPart.isReference()) {
for (Reference ref : keyPart.getReferences()) {
ColumnType type = ref.getPrimitiveType();
if (!isRef()) {
type = getArrayType(type);
}
List<String> path = ref.getPath();
path.add(0, keyPart.getName());
String name = null;
if (refLink != null) {
for (Reference overlap : refLink.getReferences()) {
if (overlap.getTargetTable().equals(ref.getTargetTable()) && overlap.getTargetColumn().equals(ref.getTargetColumn())) {
name = overlap.getName();
}
}
}
if (name == null) {
name = getName();
if (pkeys.size() > 1) {
name += COMPOSITE_REF_SEPARATOR + ref.getName();
}
}
refColumns.add(new Reference(this, name, ref.getName(), getColumnType(), type, keyPart.getColumnType().isArray(), ref.getTargetTable(), ref.getTargetColumn(), ref.isRequired() || this.isRequired(), path));
}
} else {
ColumnType type = keyPart.getColumnType();
// all but ref is array
if (!isRef()) {
type = getArrayType(type);
}
// create the ref
String name = getName();
if (pkeys.size() > 1) {
name += COMPOSITE_REF_SEPARATOR + keyPart.getName();
}
refColumns.add(new Reference(this, name, keyPart.getName(), getColumnType(), type, getColumnType().isArray(), getRefTableName(), keyPart.getName(), keyPart.isRequired() || this.isRequired(), new ArrayList<>(List.of(keyPart.getName()))));
}
}
// clean up in case only one
if (refColumns.stream().filter(r -> r.getName().startsWith(getName())).count() == 1) {
refColumns = refColumns.stream().map(r -> {
if (r.getName().startsWith(getName()))
r.setName(getName());
return r;
}).collect(Collectors.toList());
}
// remove duplicates
HashSet<Object> seen = new HashSet<>();
refColumns.removeIf(e -> !seen.add(e.getName()));
return refColumns;
}
Aggregations