Search in sources :

Example 11 with org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Column

use of org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$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));
}
Also used : Column(org.molgenis.emx2.Column) SqlColumnExecutor.validateColumn(org.molgenis.emx2.sql.SqlColumnExecutor.validateColumn) DataAccessException(org.jooq.exception.DataAccessException) List(java.util.List) DSL(org.jooq.impl.DSL) MolgenisException(org.molgenis.emx2.MolgenisException) Reference(org.molgenis.emx2.Reference) RowCountQuery(org.jooq.RowCountQuery) DSLContext(org.jooq.DSLContext) Collectors(java.util.stream.Collectors) RowCountQuery(org.jooq.RowCountQuery) Reference(org.molgenis.emx2.Reference)

Example 12 with org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Column

use of org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Column in project molgenis-emx2 by molgenis.

the class EvaluateExpressionsTest method testCheckValidationColumnsFailure.

@Test
public void testCheckValidationColumnsFailure() {
    Collection<Column> columns = new ArrayList<>();
    Column column1 = new Column("columnName1");
    column1.setValidation("{columnName2}");
    columns.add(column1);
    try {
        checkForMissingVariablesColumns(columns);
    } catch (MolgenisException exception) {
        String expectedError = "Validation failed: columns [columnName2] not provided";
        assertEquals(expectedError, exception.getMessage());
    }
}
Also used : Column(org.molgenis.emx2.Column) ArrayList(java.util.ArrayList) MolgenisException(org.molgenis.emx2.MolgenisException) Test(org.junit.Test)

Example 13 with org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Column

use of org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Column in project molgenis-emx2 by molgenis.

the class EvaluateExpressionsTest method testCheckValidationTurnToBoolIsFalse.

@Test
public void testCheckValidationTurnToBoolIsFalse() {
    Map<String, Object> values = new HashMap<>();
    Collection<Column> columns = new ArrayList<>();
    String validation = "false";
    Column column = new Column("name");
    column.setValidation(validation);
    columns.add(column);
    try {
        checkValidation(values, columns);
    } catch (MolgenisException exception) {
        assertEquals("false. Values provided: {}", exception.getMessage());
    }
}
Also used : HashMap(java.util.HashMap) Column(org.molgenis.emx2.Column) ArrayList(java.util.ArrayList) MolgenisException(org.molgenis.emx2.MolgenisException) Test(org.junit.Test)

Example 14 with org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Column

use of org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Column in project molgenis-emx2 by molgenis.

the class Mapper method map.

public static TableMetadata map(Class<?> klazz) {
    TableMetadata t = new TableMetadata(klazz.getSimpleName());
    Field[] fields = klazz.getDeclaredFields();
    for (Field f : fields) {
        if (!f.getName().contains("jacoco")) {
            Column col = column(f.getName()).setType(TypeUtils.typeOf(f.getType()));
            if (f.isAnnotationPresent(ColumnAnnotation.class)) {
                ColumnAnnotation cm = f.getAnnotation(ColumnAnnotation.class);
                col.setRequired(cm.required());
                col.setDescription(cm.description());
            }
            t.add(col);
        }
    }
    return t;
}
Also used : TableMetadata(org.molgenis.emx2.TableMetadata) Field(java.lang.reflect.Field) Column(org.molgenis.emx2.Column)

Example 15 with org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Column

use of org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Column in project java-bigtable by googleapis.

the class DefaultRowAdapter method createRowFromProto.

/**
 * Helper to convert a proto Row to a model Row.
 *
 * <p>For internal use only.
 */
@InternalApi
public Row createRowFromProto(com.google.bigtable.v2.Row row) {
    RowBuilder<Row> builder = createRowBuilder();
    builder.startRow(row.getKey());
    for (Family family : row.getFamiliesList()) {
        for (Column column : family.getColumnsList()) {
            for (Cell cell : column.getCellsList()) {
                builder.startCell(family.getName(), column.getQualifier(), cell.getTimestampMicros(), cell.getLabelsList(), cell.getValue().size());
                builder.cellValue(cell.getValue());
                builder.finishCell();
            }
        }
    }
    return builder.finishRow();
}
Also used : Column(com.google.bigtable.v2.Column) Family(com.google.bigtable.v2.Family) Cell(com.google.bigtable.v2.Cell) InternalApi(com.google.api.core.InternalApi)

Aggregations

org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Column (org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Column)21 ArrayList (java.util.ArrayList)19 org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Bigint (org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Bigint)16 org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Smallint (org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Smallint)16 HashMap (java.util.HashMap)14 org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Tinyint (org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Tinyint)13 List (java.util.List)11 Column (org.molgenis.emx2.Column)11 Column (com.google.bigtable.v2.Column)9 org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Integer (org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Integer)9 Family (com.google.bigtable.v2.Family)8 org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Decimal (org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Decimal)8 org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Enum (org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Enum)8 org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Table (org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Table)8 Test (org.junit.Test)8 org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Boolean (org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Boolean)7 org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Char (org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Char)7 org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Double (org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Double)7 org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Float (org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Float)7 org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Named (org.jaxdb.www.ddlx_0_5.xLygluGCXAA.$Named)7