Examples with Column org.molgenis.emx2.Column used on opensource projects

Example 26 with Column

use of org.molgenis.emx2.Column 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));
}

Example 27 with Column

use of org.molgenis.emx2.Column 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);
    }
}

Example 28 with Column

use of org.molgenis.emx2.Column in project molgenis-emx2 by molgenis.

the class SqlTable method insertBatch.

private static int insertBatch(SqlTable table, List<Row> rows, boolean updateOnConflict, Set<String> updateColumns) {
    boolean inherit = table.getMetadata().getInherit() != null;
    if (inherit) {
        SqlTable inheritedTable = table.getInheritedTable();
        inheritedTable.insertBatch(inheritedTable, rows, updateOnConflict, updateColumns);
    }
    // get metadata
    Set<Column> columns = table.getColumnsToBeUpdated(updateColumns);
    // check that columns exist for validation
    checkForMissingVariablesColumns(columns);
    List<Column> allColumns = table.getMetadata().getMutationColumns();
    List<Field> insertFields = columns.stream().map(c -> c.getJooqField()).collect(Collectors.toList());
    if (!inherit) {
        insertFields.add(field(name(MG_INSERTEDBY)));
        insertFields.add(field(name(MG_INSERTEDON)));
        insertFields.add(field(name(MG_UPDATEDBY)));
        insertFields.add(field(name(MG_UPDATEDON)));
    }
    // define the insert step
    InsertValuesStepN<org.jooq.Record> step = table.getJooq().insertInto(table.getJooqTable(), insertFields.toArray(new Field[0]));
    // add all the rows as steps
    String user = table.getSchema().getDatabase().getActiveUser();
    if (user == null) {
        user = ADMIN_USER;
    }
    LocalDateTime now = LocalDateTime.now();
    for (Row row : rows) {
        // get values
        Map values = SqlTypeUtils.getValuesAsMap(row, columns);
        if (!inherit) {
            values.put(MG_INSERTEDBY, user);
            values.put(MG_INSERTEDON, now);
            values.put(MG_UPDATEDBY, user);
            values.put(MG_UPDATEDON, now);
        }
        // when insert, we should include all columns, not only 'updateColumns'
        if (!row.isDraft()) {
            checkRequired(row, allColumns);
            checkValidation(values, columns);
        }
        step.values(values.values());
    }
    // optionally, add conflict clause
    if (updateOnConflict) {
        InsertOnDuplicateSetStep<org.jooq.Record> step2 = step.onConflict(table.getMetadata().getPrimaryKeyFields().toArray(new Field[0])).doUpdate();
        for (Column column : columns) {
            step2.set(column.getJooqField(), (Object) field(unquotedName("excluded.\"" + column.getName() + "\"")));
        }
        if (!inherit) {
            step2.set(field(name(MG_UPDATEDBY)), user);
            step2.set(field(name(MG_UPDATEDON)), now);
        }
    }
    return step.execute();
}

Example 29 with Column

use of org.molgenis.emx2.Column in project molgenis-emx2 by molgenis.

the class SqlTable method getWhereConditionForBatchDelete.

private Condition getWhereConditionForBatchDelete(Collection<Row> rows) {
    List<Condition> conditions = new ArrayList<>();
    for (Row r : rows) {
        List<Condition> rowCondition = new ArrayList<>();
        if (getMetadata().getPrimaryKeys().isEmpty()) {
            // when no key, use all columns as id
            for (Column keyPart : getMetadata().getStoredColumns()) {
                rowCondition.add(getColumnCondition(r, keyPart));
            }
        } else {
            for (Column keyPart : getMetadata().getPrimaryKeyColumns()) {
                rowCondition.add(getColumnCondition(r, keyPart));
            }
        }
        conditions.add(and(rowCondition));
    }
    return or(conditions);
}

Example 30 with Column

use of org.molgenis.emx2.Column in project molgenis-emx2 by molgenis.

the class TestSettings method testTableSettings.

@Test
public void testTableSettings() {
    database.tx(// prevent side effect of user changes on other tests using tx
    db -> {
        Schema s = db.dropCreateSchema("testTableSettings");
        // set roles
        // viewer should only be able to see, not change
        // editor should be able to set values
        s.addMember("testtablesettingsviewer", VIEWER.toString());
        s.addMember("testtablesettingseditor", EDITOR.toString());
        s.create(table("test").add(column("test")));
        db.setActiveUser("testtablesettingsviewer");
        try {
            Table t = db.getSchema("testTableSettings").getTable("test");
            t.getMetadata().setSetting("key", "value");
            fail("viewers should not be able to change schema settings");
        } catch (Exception e) {
        // failed correctly
        }
        db.setActiveUser("testtablesettingseditor");
        try {
            Table t = db.getSchema("testTableSettings").getTable("test");
            t.getMetadata().setSetting("key", "value");
        } catch (Exception e) {
            e.printStackTrace();
            fail("managers should  be able to change schema settings");
        }
        db.clearCache();
        List<Setting> test = db.getSchema("testTableSettings").getTable("test").getMetadata().getSettings();
        assertEquals(1, test.size());
        assertEquals("key", test.get(0).key());
        assertEquals("value", test.get(0).value());
        assertEquals("key", db.getSchema("testTableSettings").getTable("test").getMetadata().getSettings().get(0).key());
        assertEquals("value", db.getSchema("testTableSettings").getTable("test").getMetadata().getSettings().get(0).value());
        db.clearActiveUser();
    });
}