Examples with CollectDSLContext org.openforis.collect.persistence.jooq.CollectDSLContext used on opensource projects

Example 6 with CollectDSLContext

use of org.openforis.collect.persistence.jooq.CollectDSLContext in project collect by openforis.

the class SurveyWorkDao method insert.

@Transactional
public void insert(CollectSurvey survey) throws SurveyImportException {
    String idml = marshalSurvey(survey);
    CollectDSLContext dsl = dsl();
    int surveyId = dsl.nextId(OFC_SURVEY_WORK.ID, OFC_SURVEY_WORK_ID_SEQ);
    dsl().insertInto(OFC_SURVEY_WORK).set(OFC_SURVEY_WORK.ID, surveyId).set(OFC_SURVEY_WORK.NAME, survey.getName()).set(OFC_SURVEY_WORK.URI, survey.getUri()).set(OFC_SURVEY.TARGET, survey.getTarget().getCode()).set(OFC_SURVEY.COLLECT_VERSION, survey.getCollectVersion().toString()).set(OFC_SURVEY.DATE_CREATED, toTimestamp(survey.getCreationDate())).set(OFC_SURVEY.DATE_MODIFIED, toTimestamp(survey.getModifiedDate())).set(OFC_SURVEY_WORK.IDML, DSL.val(idml, SQLDataType.CLOB)).execute();
    survey.setId(surveyId);
}

Example 7 with CollectDSLContext

use of org.openforis.collect.persistence.jooq.CollectDSLContext in project collect by openforis.

the class JooqRelationalSchemaCreator method createRelationalSchema.

@Override
public void createRelationalSchema(RelationalSchema schema, Connection conn) throws CollectRdbException {
    CollectDSLContext dsl = new CollectDSLContext(conn);
    for (Table<?> table : schema.getTables()) {
        org.jooq.Table<Record> jooqTable = jooqTable(schema, table, !dsl.isSchemaLess());
        CreateTableAsStep<Record> createTableStep = dsl.createTable(jooqTable);
        Query createTableFinalQuery = (Query) createTableStep;
        for (Column<?> column : table.getColumns()) {
            DataType<?> dataType = dsl.getDataType(column.getType().getJavaType());
            Integer length = column.getLength();
            if (length != null) {
                dataType.length(length);
            }
            createTableFinalQuery = createTableStep.column(column.getName(), dataType);
        }
        createTableFinalQuery.execute();
    }
    createDataTableViews(schema, conn);
}

Example 8 with CollectDSLContext

use of org.openforis.collect.persistence.jooq.CollectDSLContext in project collect by openforis.

the class JooqRelationalSchemaCreator method createDataTableView.

private void createDataTableView(RelationalSchema schema, DataTable dataTable, Connection conn) {
    CollectDSLContext dsl = new CollectDSLContext(conn);
    List<Field<?>> fields = new ArrayList<Field<?>>();
    List<TableLike<?>> tables = new ArrayList<TableLike<?>>();
    List<Condition> conditions = new ArrayList<Condition>();
    DataTable currentTable = dataTable;
    while (currentTable != null) {
        org.jooq.Table<Record> currentJooqTable = jooqTable(schema, currentTable, !dsl.isSchemaLess());
        tables.add(currentJooqTable);
        List<Column<?>> columns = currentTable.getColumns();
        for (Column<?> column : columns) {
            if (!(column instanceof DataAncestorFKColumn)) {
                fields.add(field(name(currentJooqTable.getName(), column.getName())));
            }
        }
        // add parent table join condition
        DataTable parentTable = currentTable.getParent();
        if (parentTable != null) {
            // names are duplicate, use the table name as prefix in the join condition
            Condition parentTableJoinCondition = field(currentJooqTable.getName() + "." + currentTable.getParentFKColumn().getName()).eq(field(parentTable.getName() + "." + parentTable.getPrimaryKeyColumn().getName()));
            conditions.add(parentTableJoinCondition);
        }
        currentTable = parentTable;
    }
    Select<?> select = dsl.select(fields).from(tables).where(conditions);
    Name name;
    if (dsl.isSchemaLess()) {
        name = name(dataTable.getName() + "_view");
    } else {
        name = name(schema.getName(), dataTable.getName() + "_view");
    }
    dsl.createView(DSL.table(name), fields.toArray(new Field[fields.size()])).as(select).execute();
}

Example 9 with CollectDSLContext

use of org.openforis.collect.persistence.jooq.CollectDSLContext in project collect by openforis.

the class JooqRelationalSchemaCreator method addIndexes.

@Override
public void addIndexes(RelationalSchema schema, Connection conn) {
    CollectDSLContext dsl = new CollectDSLContext(conn);
    if (dsl.isSQLite()) {
        addCodeListsCodeIndexes(schema, dsl);
        addPKIndexes(schema, dsl);
        addFKIndexes(schema, dsl);
    } else {
    // for other DBMS, indexes are already created together with PK and FK constraints
    }
}

Example 10 with CollectDSLContext

use of org.openforis.collect.persistence.jooq.CollectDSLContext in project collect by openforis.

the class SQLRelationalSchemaCreator method createRelationalSchema.

@Override
public void createRelationalSchema(RelationalSchema schema, Connection conn) {
    CollectDSLContext dsl = new CollectDSLContext(conn);
    RdbDialect rdbDialect = getRdbDialect(dsl);
    Writer writer = new StringWriter();
    SqlSchemaWriter schemaWriter = new SqlSchemaWriter(writer, schema, rdbDialect);
    try {
        schemaWriter.write();
        String sql = writer.toString();
        Statement stmt = conn.createStatement();
        stmt.executeUpdate(sql);
    } catch (Throwable e) {
        throw new RuntimeException(String.format("Error generating schema on db for rdb schema %s", schema.getName()), e);
    }
}