Examples with SQLVendor org.sql.generation.api.vendor.SQLVendor used on opensource projects

Example 6 with SQLVendor

use of org.sql.generation.api.vendor.SQLVendor in project qi4j-sdk by Qi4j.

the class AbstractSQLIndexing method indexEntities.

@Override
public void indexEntities(Iterable<EntityState> changedStates) throws SQLException {
    final String schemaName = this._state.schemaName().get();
    final SQLVendor vendor = this.descriptor.metaInfo(SQLVendor.class);
    Connection connectionTest = AbstractSQLStartup.CONNECTION_FOR_REINDEXING.get();
    boolean connectionFromStartupWasNull = connectionTest == null;
    boolean wasAutoCommit = false;
    boolean wasReadOnly = false;
    if (connectionFromStartupWasNull) {
        connectionTest = this._dataSource.getConnection();
    } else {
        wasAutoCommit = connectionTest.getAutoCommit();
        wasReadOnly = connectionTest.isReadOnly();
    }
    final Connection connection = connectionTest;
    PreparedStatement updateEntityTablePS = null;
    PreparedStatement removeEntityPS = null;
    PreparedStatement insertToPropertyQNamesPS = null;
    PreparedStatement clearEntityDataPS = null;
    Lazy<PreparedStatement, SQLException> queryEntityPKPS = new Lazy<>(new LazyInit<PreparedStatement, SQLException>() {

        @Override
        public PreparedStatement create() throws SQLException {
            return connection.prepareStatement(vendor.toString(createQueryEntityPkByIdentityStatement(schemaName, vendor)));
        }
    });
    Lazy<PreparedStatement, SQLException> insertToEntityTableAutoGenerated = new Lazy<>(new LazyInit<PreparedStatement, SQLException>() {

        @Override
        public PreparedStatement create() throws SQLException {
            return connection.prepareStatement(createInsertStatementWithAutoGeneratedIDForEntitiesTable(schemaName, ENTITY_TABLE_NAME, vendor).toString());
        }
    });
    Lazy<PreparedStatement, SQLException> insertToEntityTypeTablePS = new Lazy<>(new LazyInit<PreparedStatement, SQLException>() {

        @Override
        public PreparedStatement create() throws SQLException {
            return connection.prepareStatement(createInsertEntityTypeStatement(schemaName, vendor).toString());
        }
    });
    Map<QualifiedName, PreparedStatement> qNameInsertPSs = new HashMap<>();
    try {
        connection.setAutoCommit(false);
        connection.setReadOnly(false);
        // TODO cache all queries.
        updateEntityTablePS = connection.prepareStatement(this.createUpdateEntityTableStatement(schemaName, vendor).toString());
        removeEntityPS = connection.prepareStatement(this.createDeleteFromEntityTableStatement(schemaName, vendor).toString());
        insertToPropertyQNamesPS = connection.prepareStatement(vendor.toString(this.createInsertStatement(schemaName, DBNames.ALL_QNAMES_TABLE_NAME, AMOUNT_OF_COLUMNS_IN_ALL_QNAMES_TABLE, vendor)));
        clearEntityDataPS = connection.prepareStatement(this.createClearEntityDataStatement(schemaName, vendor).toString());
        Map<Long, EntityState> statesByPK = new HashMap<>();
        Map<Long, Integer> qNamePKs = new HashMap<>();
        Iterable<EntityState> relatedStates = Iterables.filter(new Specification<EntityState>() {

            @Override
            public boolean satisfiedBy(EntityState item) {
                return item.entityDescriptor().queryable();
            }
        }, Iterables.map(SQLCompatEntityStateWrapper.WRAP, changedStates));
        for (EntityState eState : relatedStates) {
            EntityStatus status = eState.status();
            Long pk = null;
            boolean needToInsert = status.equals(EntityStatus.NEW);
            if (!needToInsert) {
                if (status.equals(EntityStatus.UPDATED)) {
                    pk = this.findEntityPK(eState, queryEntityPKPS);
                    if (pk == null) {
                        // Happens when reindexing
                        needToInsert = true;
                    } else {
                        // TODO if multiple applications with different application model use
                        // indexing, need to sync type-table.
                        this.updateEntityInfoAndProperties(connection, qNameInsertPSs, insertToPropertyQNamesPS, clearEntityDataPS, updateEntityTablePS, eState, pk, qNamePKs);
                    }
                } else if (status.equals(EntityStatus.REMOVED)) {
                    this.removeEntity(eState, removeEntityPS);
                } else {
                // TODO possibly handle LOADED state somehow
                // throw new
                // UnsupportedOperationException("Did not understand what to do with state [id = "
                // +
                // eState.identity().identity() + ", status = " + status + "].");
                }
            }
            if (needToInsert) {
                pk = this.getPKFromAutoGeneratedIDInsert(eState, insertToEntityTableAutoGenerated.getValue(), vendor, connection);
                this.insertPropertyType(connection, insertToEntityTypeTablePS.getValue(), eState, pk);
                this.insertProperties(connection, qNameInsertPSs, insertToPropertyQNamesPS, eState, pk, qNamePKs);
            }
            if (pk != null) {
                statesByPK.put(pk, eState);
            }
        }
        removeEntityPS.executeBatch();
        updateEntityTablePS.executeBatch();
        clearEntityDataPS.executeBatch();
        if (insertToEntityTypeTablePS.hasValue()) {
            insertToEntityTypeTablePS.getValue().executeBatch();
        }
        for (Map.Entry<Long, EntityState> entry : statesByPK.entrySet()) {
            EntityState eState = entry.getValue();
            Long pk = entry.getKey();
            this.insertAssoAndManyAssoQNames(qNameInsertPSs, insertToPropertyQNamesPS, eState, qNamePKs.get(pk), pk);
        }
        insertToPropertyQNamesPS.executeBatch();
        for (PreparedStatement ps : qNameInsertPSs.values()) {
            ps.executeBatch();
        }
        connection.commit();
    } catch (SQLException sqle) {
        SQLUtil.rollbackQuietly(connection);
        throw sqle;
    } finally {
        try {
            if (queryEntityPKPS.hasValue()) {
                SQLUtil.closeQuietly(queryEntityPKPS.getValue());
            }
            if (insertToEntityTableAutoGenerated.hasValue()) {
                SQLUtil.closeQuietly(insertToEntityTableAutoGenerated.getValue());
            }
            SQLUtil.closeQuietly(updateEntityTablePS);
            SQLUtil.closeQuietly(removeEntityPS);
            SQLUtil.closeQuietly(insertToPropertyQNamesPS);
            SQLUtil.closeQuietly(clearEntityDataPS);
            for (PreparedStatement ps : qNameInsertPSs.values()) {
                SQLUtil.closeQuietly(ps);
            }
        } finally {
            if (connectionFromStartupWasNull) {
                SQLUtil.closeQuietly(connection);
            } else {
                connection.setReadOnly(wasReadOnly);
                connection.setAutoCommit(wasAutoCommit);
            }
        }
    }
}

Example 7 with SQLVendor

use of org.sql.generation.api.vendor.SQLVendor in project qi4j-sdk by Qi4j.

the class AbstractSQLStartup method createSchemaAndRequiredTables.

private void createSchemaAndRequiredTables(Connection connection, String schemaName, Map<String, Long> tablePKs) throws SQLException {
    boolean schemaFound = false;
    ResultSet rs = connection.getMetaData().getSchemas();
    try {
        while (rs.next() && !schemaFound) {
            schemaFound = rs.getString(1).equals(schemaName);
        }
    } finally {
        SQLUtil.closeQuietly(rs);
    }
    SQLVendor vendor = this._vendor;
    DefinitionFactory d = vendor.getDefinitionFactory();
    TableReferenceFactory t = vendor.getTableReferenceFactory();
    Statement stmt = connection.createStatement();
    // @formatter:off
    try {
        if (!schemaFound) {
            stmt.execute(vendor.toString(d.createSchemaDefinitionBuilder().setSchemaName(schemaName).createExpression()));
            LOGGER.debug("Database schema created");
        }
        this.testRequiredCapabilities(connection);
        LOGGER.debug("Underlying database fullfill required capabilities");
        stmt.execute(vendor.toString(d.createTableDefinitionBuilder().setTableName(t.tableName(schemaName, USED_CLASSES_TABLE_NAME)).setTableContentsSource(d.createTableElementListBuilder().addTableElement(d.createColumnDefinition(USED_CLASSES_TABLE_PK_COLUMN_NAME, this._primitiveTypes.get(Integer.class), false)).addTableElement(d.createColumnDefinition(USED_CLASSES_TABLE_CLASS_NAME_COLUMN_NAME, this._primitiveTypes.get(String.class), false)).addTableElement(d.createTableConstraintDefinition(d.createUniqueConstraintBuilder().setUniqueness(UniqueSpecification.PRIMARY_KEY).addColumns(USED_CLASSES_TABLE_PK_COLUMN_NAME).createExpression())).addTableElement(d.createTableConstraintDefinition(d.createUniqueConstraintBuilder().setUniqueness(UniqueSpecification.UNIQUE).addColumns(USED_CLASSES_TABLE_CLASS_NAME_COLUMN_NAME).createExpression())).createExpression()).createExpression()));
        tablePKs.put(USED_CLASSES_TABLE_NAME, 0L);
        stmt.execute(vendor.toString(d.createTableDefinitionBuilder().setTableName(t.tableName(schemaName, ENTITY_TYPES_TABLE_NAME)).setTableContentsSource(d.createTableElementListBuilder().addTableElement(d.createColumnDefinition(ENTITY_TYPES_TABLE_PK_COLUMN_NAME, this._primitiveTypes.get(ENTITY_TYPE_PK_TYPE), false)).addTableElement(d.createColumnDefinition(ENTITY_TYPES_TABLE_TYPE_NAME_COLUMN_NAME, this._primitiveTypes.get(String.class), false)).addTableElement(d.createTableConstraintDefinition(d.createUniqueConstraintBuilder().setUniqueness(UniqueSpecification.PRIMARY_KEY).addColumns(ENTITY_TYPES_TABLE_PK_COLUMN_NAME).createExpression())).addTableElement(d.createTableConstraintDefinition(d.createUniqueConstraintBuilder().setUniqueness(UniqueSpecification.UNIQUE).addColumns(ENTITY_TYPES_TABLE_TYPE_NAME_COLUMN_NAME).createExpression())).createExpression()).createExpression()));
        tablePKs.put(ENTITY_TYPES_TABLE_NAME, 0L);
        stmt.execute(vendor.toString(d.createTableDefinitionBuilder().setTableName(t.tableName(schemaName, ENTITY_TABLE_NAME)).setTableContentsSource(d.createTableElementListBuilder().addTableElement(d.createColumnDefinition(ENTITY_TABLE_PK_COLUMN_NAME, this._primitiveTypes.get(ENTITY_PK_TYPE), false, AutoGenerationPolicy.BY_DEFAULT)).addTableElement(d.createColumnDefinition(ENTITY_TABLE_IDENTITY_COLUMN_NAME, this._primitiveTypes.get(String.class), false)).addTableElement(d.createColumnDefinition(ENTITY_TABLE_MODIFIED_COLUMN_NAME, this._primitiveTypes.get(Date.class), false)).addTableElement(d.createColumnDefinition(ENTITY_TABLE_VERSION_COLUMN_NAME, this._primitiveTypes.get(String.class), false)).addTableElement(d.createColumnDefinition(ENTITY_TABLE_APPLICATION_VERSION_COLUMN_NAME, this._primitiveTypes.get(String.class), false)).addTableElement(d.createTableConstraintDefinition(d.createUniqueConstraintBuilder().setUniqueness(UniqueSpecification.PRIMARY_KEY).addColumns(ENTITY_TABLE_PK_COLUMN_NAME).createExpression())).addTableElement(d.createTableConstraintDefinition(d.createUniqueConstraintBuilder().setUniqueness(UniqueSpecification.UNIQUE).addColumns(ENTITY_TABLE_IDENTITY_COLUMN_NAME).createExpression())).createExpression()).createExpression()));
        tablePKs.put(ENTITY_TABLE_NAME, 0L);
        stmt.execute(d.createTableDefinitionBuilder().setTableName(t.tableName(schemaName, ENTITY_TYPES_JOIN_TABLE_NAME)).setTableContentsSource(d.createTableElementListBuilder().addTableElement(d.createColumnDefinition(ENTITY_TABLE_PK_COLUMN_NAME, this._primitiveTypes.get(ENTITY_PK_TYPE), false)).addTableElement(d.createColumnDefinition(ENTITY_TYPES_TABLE_PK_COLUMN_NAME, this._primitiveTypes.get(ENTITY_TYPE_PK_TYPE), false)).addTableElement(d.createTableConstraintDefinition(d.createUniqueConstraintBuilder().setUniqueness(UniqueSpecification.PRIMARY_KEY).addColumns(ENTITY_TABLE_PK_COLUMN_NAME, ENTITY_TYPES_TABLE_PK_COLUMN_NAME).createExpression())).addTableElement(d.createTableConstraintDefinition(d.createForeignKeyConstraintBuilder().addSourceColumns(ENTITY_TABLE_PK_COLUMN_NAME).setTargetTableName(t.tableName(schemaName, ENTITY_TABLE_NAME)).addTargetColumns(ENTITY_TABLE_PK_COLUMN_NAME).setOnDelete(ReferentialAction.CASCADE).setOnUpdate(ReferentialAction.CASCADE).createExpression(), ConstraintCharacteristics.INITIALLY_DEFERRED_DEFERRABLE)).addTableElement(d.createTableConstraintDefinition(d.createForeignKeyConstraintBuilder().addSourceColumns(ENTITY_TYPES_TABLE_PK_COLUMN_NAME).setTargetTableName(t.tableName(schemaName, ENTITY_TYPES_TABLE_NAME)).addTargetColumns(ENTITY_TYPES_TABLE_PK_COLUMN_NAME).setOnDelete(ReferentialAction.RESTRICT).setOnDelete(ReferentialAction.CASCADE).createExpression(), ConstraintCharacteristics.NOT_DEFERRABLE)).createExpression()).createExpression().toString());
        stmt.execute(vendor.toString(d.createTableDefinitionBuilder().setTableName(t.tableName(schemaName, ENUM_LOOKUP_TABLE_NAME)).setTableContentsSource(d.createTableElementListBuilder().addTableElement(d.createColumnDefinition(ENUM_LOOKUP_TABLE_PK_COLUMN_NAME, this._primitiveTypes.get(Integer.class), false)).addTableElement(d.createColumnDefinition(ENUM_LOOKUP_TABLE_ENUM_VALUE_NAME, this._primitiveTypes.get(String.class), false)).addTableElement(d.createTableConstraintDefinition(d.createUniqueConstraintBuilder().setUniqueness(UniqueSpecification.PRIMARY_KEY).addColumns(ENUM_LOOKUP_TABLE_PK_COLUMN_NAME).createExpression())).createExpression()).createExpression()));
        tablePKs.put(ENUM_LOOKUP_TABLE_NAME, 0L);
        stmt.execute(vendor.toString(d.createTableDefinitionBuilder().setTableName(t.tableName(schemaName, USED_QNAMES_TABLE_NAME)).setTableContentsSource(d.createTableElementListBuilder().addTableElement(d.createColumnDefinition(USED_QNAMES_TABLE_QNAME_COLUMN_NAME, this._primitiveTypes.get(String.class), false)).addTableElement(d.createColumnDefinition(USED_QNAMES_TABLE_TABLE_NAME_COLUMN_NAME, this._primitiveTypes.get(String.class), false)).addTableElement(d.createTableConstraintDefinition(d.createUniqueConstraintBuilder().setUniqueness(UniqueSpecification.PRIMARY_KEY).addColumns(USED_QNAMES_TABLE_QNAME_COLUMN_NAME, USED_QNAMES_TABLE_TABLE_NAME_COLUMN_NAME).createExpression())).createExpression()).createExpression()));
        stmt.execute(vendor.toString(d.createTableDefinitionBuilder().setTableName(t.tableName(schemaName, ALL_QNAMES_TABLE_NAME)).setTableContentsSource(d.createTableElementListBuilder().addTableElement(d.createColumnDefinition(ALL_QNAMES_TABLE_PK_COLUMN_NAME, this._primitiveTypes.get(Integer.class), false)).addTableElement(d.createColumnDefinition(ENTITY_TABLE_PK_COLUMN_NAME, this._primitiveTypes.get(ENTITY_PK_TYPE), false)).addTableElement(d.createTableConstraintDefinition(d.createUniqueConstraintBuilder().setUniqueness(UniqueSpecification.PRIMARY_KEY).addColumns(ALL_QNAMES_TABLE_PK_COLUMN_NAME, ENTITY_TABLE_PK_COLUMN_NAME).createExpression())).addTableElement(d.createTableConstraintDefinition(d.createForeignKeyConstraintBuilder().addSourceColumns(ENTITY_TABLE_PK_COLUMN_NAME).setTargetTableName(t.tableName(schemaName, ENTITY_TABLE_NAME)).addTargetColumns(ENTITY_TABLE_PK_COLUMN_NAME).setOnUpdate(ReferentialAction.CASCADE).setOnDelete(ReferentialAction.CASCADE).createExpression(), ConstraintCharacteristics.INITIALLY_DEFERRED_DEFERRABLE)).createExpression()).createExpression()));
        tablePKs.put(ALL_QNAMES_TABLE_NAME, 0L);
        stmt.execute(vendor.toString(d.createTableDefinitionBuilder().setTableName(t.tableName(schemaName, APP_VERSION_TABLE_NAME)).setTableContentsSource(d.createTableElementListBuilder().addTableElement(d.createColumnDefinition(APP_VERSION_PK_COLUMN_NAME, this._primitiveTypes.get(String.class), false)).addTableElement(d.createTableConstraintDefinition(d.createUniqueConstraintBuilder().setUniqueness(UniqueSpecification.PRIMARY_KEY).addColumns(APP_VERSION_PK_COLUMN_NAME).createExpression())).createExpression()).createExpression()));
        ModificationFactory m = vendor.getModificationFactory();
        PreparedStatement ps = connection.prepareStatement(vendor.toString(m.insert().setTableName(t.tableName(schemaName, APP_VERSION_TABLE_NAME)).setColumnSource(m.columnSourceByValues().addValues(vendor.getLiteralFactory().param()).createExpression()).createExpression()));
        ps.setString(1, this._app.version());
        ps.execute();
    // TODO INDICES!!!!
    } finally {
        SQLUtil.closeQuietly(stmt);
    }
    // @formatter:on
    LOGGER.debug("Indexing SQL database tables created");
}

Example 8 with SQLVendor

use of org.sql.generation.api.vendor.SQLVendor in project qi4j-sdk by Qi4j.

the class AbstractSQLStartup method getNextPK.

protected Long getNextPK(Statement stmt, String schemaName, String columnName, String tableName, Long defaultPK) throws SQLException {
    ResultSet rs = null;
    Long result = defaultPK;
    try {
        SQLVendor vendor = this._vendor;
        QueryFactory q = vendor.getQueryFactory();
        // Let's cheat a bit on SQL functions, so we won't need to use heavy query builder.
        // Also, currently there are no arithmetic statements
        rs = stmt.executeQuery(vendor.toString(q.simpleQueryBuilder().select("COUNT(" + columnName + ")", "MAX(" + columnName + ") + 1").from(vendor.getTableReferenceFactory().tableName(schemaName, tableName)).createExpression()));
        if (rs.next()) {
            Long count = rs.getLong(1);
            if (count > 0) {
                result = rs.getLong(2);
            }
        }
    } finally {
        SQLUtil.closeQuietly(rs);
    }
    return result;
}

Example 9 with SQLVendor

use of org.sql.generation.api.vendor.SQLVendor in project qi4j-sdk by Qi4j.

the class AbstractSQLQuerying method constructQuery.

@Override
public //
String constructQuery(//
Class<?> resultType, //
Specification<Composite> whereClause, //
OrderBy[] orderBySegments, //
Integer firstResult, //
Integer maxResults, //
Map<String, Object> variables, //
List<Object> values, //
List<Integer> valueSQLTypes, //
Boolean countOnly) throws EntityFinderException {
    SQLVendor vendor = this.descriptor.metaInfo(SQLVendor.class);
    QueryFactory q = vendor.getQueryFactory();
    TableReferenceFactory t = vendor.getTableReferenceFactory();
    LiteralFactory l = vendor.getLiteralFactory();
    ColumnsFactory c = vendor.getColumnsFactory();
    ColumnReference mainColumn = c.colName(TABLE_NAME_PREFIX + "0", DBNames.ENTITY_TABLE_IDENTITY_COLUMN_NAME);
    if (countOnly) {
        mainColumn = c.colExp(l.func(SQLFunctions.COUNT, mainColumn));
    }
    QueryBuilder innerBuilder = this.processBooleanExpression(whereClause, false, vendor, this.createTypeCondition(resultType, vendor), variables, values, valueSQLTypes);
    QuerySpecificationBuilder mainQuery = q.querySpecificationBuilder();
    mainQuery.getSelect().addUnnamedColumns(mainColumn);
    mainQuery.getFrom().addTableReferences(t.tableBuilder(t.table(q.createQuery(innerBuilder.createExpression()), t.tableAlias(TABLE_NAME_PREFIX + "0"))));
    this.processOrderBySegments(orderBySegments, vendor, mainQuery);
    QueryExpression finalMainQuery = this.finalizeQuery(vendor, mainQuery, resultType, whereClause, orderBySegments, firstResult, maxResults, variables, values, valueSQLTypes, countOnly);
    String result = vendor.toString(finalMainQuery);
    LOGGER.info("SQL query:\n" + result);
    return result;
}