Examples with DataTable org.openforis.collect.relational.model.DataTable used on opensource projects

Example 11 with DataTable

use of org.openforis.collect.relational.model.DataTable in project collect by openforis.

the class JooqDatabaseExporter method findAncestorFKByColumnName.

private Map<String, BigInteger> findAncestorFKByColumnName(RelationalSchema schema, DataTable table, int recordId, int parentId) {
    Map<String, BigInteger> result = new HashMap<String, BigInteger>();
    DataTable parentTable = table.getParent();
    List<DataAncestorFKColumn> parentAncestorFKColumns = new ArrayList<DataAncestorFKColumn>(parentTable.getAncestorFKColumns());
    List<Field<?>> parentAncestorColumns = toFields(parentAncestorFKColumns);
    BigInteger parentPKValue = getTableArtificialPK(recordId, parentTable.getNodeDefinition(), parentId);
    DataPrimaryKeyColumn parentPKColumn = parentTable.getPrimaryKeyColumn();
    QueryCreator queryCreator = new QueryCreator(dsl, schema.getName());
    SelectConditionStep<Record> selectAncestorFKs = dsl.select(parentAncestorColumns).from(queryCreator.getJooqTable(parentTable)).where(field(parentPKColumn.getName()).eq(parentPKValue));
    Record record = selectAncestorFKs.fetchOne();
    for (int i = 0; i < parentAncestorColumns.size(); i++) {
        Field<?> parentAncestorField = parentAncestorColumns.get(i);
        DataAncestorFKColumn parentColumn = parentAncestorFKColumns.get(i);
        int ancestorDefinitionId = parentColumn.getAncestorDefinitionId();
        DataAncestorFKColumn column = table.getAncestorFKColumn(ancestorDefinitionId);
        BigInteger ancestorPK = record.getValue(parentAncestorField, BigInteger.class);
        result.put(column.getName(), ancestorPK);
    }
    result.put(table.getParentFKColumn().getName(), parentPKValue);
    return result;
}

Example 12 with DataTable

use of org.openforis.collect.relational.model.DataTable in project collect by openforis.

the class JooqRelationalSchemaCreator method addFKIndexes.

private void addFKIndexes(RelationalSchema schema, CollectDSLContext dsl) {
    for (Table<?> table : schema.getTables()) {
        if (table instanceof DataTable) {
            org.jooq.Table<Record> jooqTable = jooqTable(schema, table, !dsl.isSchemaLess());
            int idxCount = 1;
            for (ReferentialConstraint referentialConstraint : table.getReferentialContraints()) {
                String idxName = String.format("%s_%d_idx", table.getName(), idxCount);
                dsl.createIndex(idxName).on(jooqTable, toJooqFields(referentialConstraint.getColumns())).execute();
                idxCount++;
            }
        }
    }
}

Example 13 with DataTable

use of org.openforis.collect.relational.model.DataTable 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 14 with DataTable

use of org.openforis.collect.relational.model.DataTable in project collect by openforis.

the class MondrianCubeGenerator method generateDimension.

/*	private List<Dimension> generatePredefinedDimensions() {
		List<Dimension> dimensions = new ArrayList<Dimension>();
		//Slope category
		{
			Dimension d = new Dimension("Slope category");
			d.foreignKey = "slope_id";
			d.highCardinality = "false";
			Hierarchy h = new Hierarchy();
			h.table = new Table("slope_category");
			Level l = new Level("Slope_category");
			l.table = "slope_category";
			l.column = "slope_id";
			l.nameColumn = "slope_caption";
			l.type = "String";
			l.levelType = "Regular";
			l.uniqueMembers = "false";
			h.levels.add(l);
			d.hierarchy = h;
			dimensions.add(d);
		}
		//Initial Land Use
		{
			Dimension d = new Dimension("Initial Land Use");
			d.foreignKey = "dynamics_id";
			d.highCardinality = "false";
			Hierarchy h = new Hierarchy();
			h.table = new Table("dynamics_category");
			Level l = new Level("Initial_land_use");
			l.table = "dynamics_category";
			l.column = "dynamics_id";
			l.nameColumn = "dynamics_caption";
			l.type = "String";
			l.levelType = "Regular";
			l.uniqueMembers = "false";
			l.hideMemberIf = "Never";
			h.levels.add(l);
			d.hierarchy = h;
			dimensions.add(d);
		}
		//Elevation Range
		{
			Dimension d = new Dimension("Elevation range");
			d.foreignKey = "elevation_id";
			d.highCardinality = "false";
			Hierarchy h = new Hierarchy();
			h.table = new Table("elevation_category");
			Level l = new Level("Elevation_range");
			l.table = "elevation_category";
			l.column = "elevation_id";
			l.nameColumn = "elevation_caption";
			l.type = "String";
			l.levelType = "Regular";
			l.uniqueMembers = "false";
			l.hideMemberIf = "Never";
			h.levels.add(l);
			d.hierarchy = h;
			dimensions.add(d);
		}
		return dimensions;
	}
*/
private Dimension generateDimension(NodeDefinition nodeDef, EntityDefinition rootEntityDef) {
    String attrLabel = extractFailsafeLabel(nodeDef);
    Dimension dimension = new Dimension(attrLabel);
    Hierarchy hierarchy = dimension.hierarchy;
    if (nodeDef instanceof CodeAttributeDefinition) {
        CodeAttributeDefinition codeDef = (CodeAttributeDefinition) nodeDef;
        DataTable dataTable = nodeDef.isMultiple() ? rdbSchema.getDataTable(nodeDef) : rdbSchema.getDataTable(nodeDef.getParentDefinition());
        CodeTable codeListTable = rdbSchema.getCodeListTable(codeDef);
        String codeListTableName = codeListTable.getName();
        String codeFKColumnName = DataTables.getCodeFKColumnName(rdbConfig, dataTable, codeDef);
        String rootEntityIdColumnName = getRootEntityIdColumnName(rootEntityDef);
        if (nodeDef.isMultiple()) {
            dimension.foreignKey = rootEntityIdColumnName;
            hierarchy.primaryKey = rootEntityIdColumnName;
            hierarchy.primaryKeyTable = dataTable.getName();
            Join join = new Join(null);
            join.leftKey = codeFKColumnName;
            join.rightKey = CodeListTables.getIdColumnName(rdbConfig, codeListTableName);
            join.tables = Arrays.asList(new Table(dataTable.getName()), new Table(codeListTableName));
            hierarchy.join = join;
        } else {
            dimension.foreignKey = codeFKColumnName;
            hierarchy.table = new Table(codeListTableName);
        }
    }
    if (nodeDef instanceof DateAttributeDefinition) {
        dimension.type = "";
        hierarchy.type = "TimeDimension";
        hierarchy.allMemberName = "attrLabel";
        String[] levelNames = new String[] { "Year", "Month", "Day" };
        for (String levelName : levelNames) {
            Level level = new Level(attrLabel + " - " + levelName);
            level.column = nodeDef.getName() + "_" + levelName.toLowerCase(Locale.ENGLISH);
            level.levelType = String.format("Time%ss", levelName);
            level.type = "Numeric";
            // Avoids grouping the values of the sane day over multiple months!!!
            level.uniqueMembers = "false";
            hierarchy.levels.add(level);
        }
    } else if (nodeDef instanceof CoordinateAttributeDefinition) {
        dimension.type = "";
        hierarchy.type = "StandardDimension";
        Level level = new Level(attrLabel + " - Latitude");
        level.column = nodeDef.getName() + "_y";
        hierarchy.levels.add(level);
        Level level2 = new Level(attrLabel + " - Longitude");
        level2.column = nodeDef.getName() + "_x";
        hierarchy.levels.add(level2);
    } else {
        List<Level> levels = generateLevel(nodeDef);
        hierarchy.levels.addAll(levels);
    }
    return dimension;
}

Example 15 with DataTable

use of org.openforis.collect.relational.model.DataTable in project collect by openforis.

the class MondrianCubeGenerator method generateCube.

private Cube generateCube() {
    Cube cube = new Cube("Collect Earth Plot");
    EntityDefinition rootEntityDef = survey.getSchema().getFirstRootEntityDefinition();
    Table table = new Table(rootEntityDef.getName());
    cube.table = table;
    List<NodeDefinition> children = rootEntityDef.getChildDefinitions();
    for (NodeDefinition nodeDef : children) {
        if (!survey.getAnnotations().isIncludedInDataExport(nodeDef)) {
            continue;
        }
        String nodeName = nodeDef.getName();
        if (nodeDef instanceof AttributeDefinition) {
            Dimension dimension = generateDimension(nodeDef, rootEntityDef);
            if (nodeDef instanceof KeyAttributeDefinition && ((KeyAttributeDefinition) nodeDef).isKey()) {
                Measure measure = new Measure(rootEntityDef.getName() + "_count");
                measure.column = "_" + rootEntityDef.getName() + "_" + nodeName;
                measure.caption = StringEscapeUtils.escapeHtml4(extractFailsafeLabel(rootEntityDef) + " Count");
                measure.aggregator = "distinct count";
                measure.datatype = "Integer";
                cube.measures.add(measure);
            } else if (nodeDef instanceof NumberAttributeDefinition) {
                for (String aggregator : MEASURE_AGGREGATORS) {
                    Measure measure = new Measure(nodeName + "_" + aggregator);
                    measure.column = nodeName;
                    measure.caption = StringEscapeUtils.escapeHtml4(extractFailsafeLabel(nodeDef) + " " + aggregator);
                    measure.aggregator = aggregator;
                    measure.datatype = "Numeric";
                    measure.formatString = "#.##";
                    cube.measures.add(measure);
                }
            }
            cube.dimensions.add(dimension);
        } else {
            String rootEntityIdColumnName = getRootEntityIdColumnName(rootEntityDef);
            String entityName = nodeName;
            String entityLabel = extractFailsafeLabel(nodeDef);
            for (NodeDefinition childDef : ((EntityDefinition) nodeDef).getChildDefinitions()) {
                String childLabel = extractReportingLabel(childDef);
                if (childLabel == null) {
                    childLabel = extractFailsafeLabel(childDef);
                    if (!childLabel.startsWith(entityLabel)) {
                        childLabel = entityLabel + " " + childLabel;
                    }
                }
                Dimension dimension = new Dimension(childLabel);
                Hierarchy hierarchy = new Hierarchy(childLabel);
                if (nodeDef.isMultiple()) {
                    dimension.foreignKey = rootEntityIdColumnName;
                    hierarchy.primaryKey = rootEntityIdColumnName;
                    hierarchy.primaryKeyTable = entityName;
                    if (childDef instanceof CodeAttributeDefinition) {
                        CodeAttributeDefinition codeAttr = (CodeAttributeDefinition) childDef;
                        Join join = new Join(null);
                        DataTable dataTable = rdbSchema.getDataTable(nodeDef);
                        CodeValueFKColumn foreignKeyCodeColumn = dataTable.getForeignKeyCodeColumn(codeAttr);
                        join.leftKey = foreignKeyCodeColumn.getName();
                        CodeTable codeListTable = rdbSchema.getCodeListTable(codeAttr);
                        join.rightKey = CodeListTables.getIdColumnName(rdbConfig, codeListTable.getName());
                        ;
                        join.tables = Arrays.asList(new Table(entityName), new Table(codeListTable.getName()));
                        hierarchy.join = join;
                    } else {
                        hierarchy.table = new Table(entityName);
                    }
                    hierarchy.levels.addAll(generateLevel(childDef));
                    dimension.hierarchy = hierarchy;
                } else {
                    dimension = generateDimension(childDef, rootEntityDef);
                }
                cube.dimensions.add(dimension);
            }
        }
    }
    // add predefined dimensions
    // DEPRECATED 07/08/2015 : From now on all the operations to calculate the aspect,elevation,slope and initial land use class are made through Calculated Members
    // cube.dimensions.addAll(generatePredefinedDimensions());
    // add predefined measures
    // Add the measures AFTER the 1st measure, which shouyld be Plot Count
    cube.measures.addAll(1, generatePredefinedMeasures());
    return cube;
}