Examples with SQLBuilder org.apache.sis.internal.metadata.sql.SQLBuilder used on opensource projects

Example 1 with SQLBuilder

use of org.apache.sis.internal.metadata.sql.SQLBuilder in project sis by apache.

the class MetadataSource method search.

/**
 * Searches for the given metadata in the database. If such metadata is found, then its
 * identifier (primary key) is returned. Otherwise this method returns {@code null}.
 *
 * @param  table     the table where to search.
 * @param  columns   the table columns as given by {@link #getExistingColumns(String)}, or {@code null}.
 * @param  metadata  a map view of the metadata to search for.
 * @param  stmt      the statement to use for executing the query.
 * @param  helper    an helper class for creating the SQL query.
 * @return the identifier of the given metadata, or {@code null} if none.
 * @throws SQLException if an error occurred while searching in the database.
 */
final String search(final String table, Set<String> columns, final Map<String, Object> metadata, final Statement stmt, final SQLBuilder helper) throws SQLException {
    assert Thread.holdsLock(this);
    helper.clear();
    for (final Map.Entry<String, Object> entry : metadata.entrySet()) {
        /*
             * Gets the value and the column where this value is stored. If the value is non-null,
             * then the column must exist otherwise the metadata will be considered as not found.
             */
        Object value = extractFromCollection(entry.getValue());
        final String column = entry.getKey();
        if (columns == null) {
            columns = getExistingColumns(table);
        }
        if (!columns.contains(column)) {
            if (value != null) {
                // The column was mandatory for the searched metadata.
                return null;
            } else {
                // Do not include a non-existent column in the SQL query.
                continue;
            }
        }
        /*
             * Tests if the value is another metadata, in which case we will invoke this method recursively.
             * Note that if a metadata dependency is not found, we can stop the whole process immediately.
             */
        if (value != null) {
            if (value instanceof CodeList<?>) {
                value = Types.getCodeName((CodeList<?>) value);
            } else if (value instanceof Enum<?>) {
                value = ((Enum<?>) value).name();
            } else {
                String dependency = proxy(value);
                if (dependency != null) {
                    value = dependency;
                } else {
                    final Class<?> type = value.getClass();
                    if (standard.isMetadata(type)) {
                        dependency = search(getTableName(standard.getInterface(type)), null, asValueMap(value), stmt, new SQLBuilder(helper));
                        if (dependency == null) {
                            // Dependency not found.
                            return null;
                        }
                        value = dependency;
                    }
                }
            }
        }
        /*
             * Builds the SQL statement with the resolved value.
             */
        if (helper.isEmpty()) {
            helper.append("SELECT ").append(ID_COLUMN).append(" FROM ").appendIdentifier(schema, table).append(" WHERE ");
        } else {
            helper.append(" AND ");
        }
        helper.appendIdentifier(column).appendCondition(value);
    }
    /*
         * The SQL statement is ready, with metadata dependency (if any) resolved. We can now execute it.
         * If more than one record is found, the identifier of the first one will be selected add a warning
         * will be logged.
         */
    String identifier = null;
    try (ResultSet rs = stmt.executeQuery(helper.toString())) {
        while (rs.next()) {
            final String candidate = rs.getString(1);
            if (candidate != null) {
                if (identifier == null) {
                    identifier = candidate;
                } else if (!identifier.equals(candidate)) {
                    warning(MetadataSource.class, "search", Errors.getResources((Locale) null).getLogRecord(Level.WARNING, Errors.Keys.DuplicatedElement_1, candidate));
                    break;
                }
            }
        }
    }
    return identifier;
}

Example 2 with SQLBuilder

use of org.apache.sis.internal.metadata.sql.SQLBuilder in project sis by apache.

the class MetadataWriter method add.

/**
 * Implementation of the {@link #add(Object)} method. This method invokes itself recursively,
 * and maintains a map of metadata inserted up to date in order to avoid infinite recursivity.
 *
 * @param  stmt      the statement to use for inserting data.
 * @param  metadata  the metadata object to add.
 * @param  done      the metadata objects already added, mapped to their primary keys.
 * @param  parent    the primary key of the parent, or {@code null} if there is no parent.
 *                   This identifier shall not contain {@linkplain #isReservedChar(int) reserved characters}.
 * @return the identifier (primary key) of the metadata just added.
 * @throws SQLException if an exception occurred while reading or writing the database.
 * @throws ClassCastException if the metadata object does not implement a metadata interface
 *         of the expected package.
 */
private String add(final Statement stmt, final Object metadata, final Map<Object, String> done, final String parent) throws ClassCastException, SQLException {
    final SQLBuilder helper = helper();
    /*
         * Take a snapshot of the metadata content. We do that in order to protect ourself against
         * concurrent changes in the metadata object. This protection is needed because we need to
         * perform multiple passes on the same metadata.
         */
    final Map<String, Object> asValueMap = asValueMap(metadata);
    final Map<String, Object> asSingletons = new LinkedHashMap<>();
    for (final Map.Entry<String, Object> entry : asValueMap.entrySet()) {
        asSingletons.put(entry.getKey(), extractFromCollection(entry.getValue()));
    }
    /*
         * Search the database for an existing metadata.
         */
    final Class<?> implementationType = metadata.getClass();
    final Class<?> interfaceType = standard.getInterface(implementationType);
    final String table = getTableName(interfaceType);
    final Set<String> columns = getExistingColumns(table);
    String identifier = search(table, columns, asSingletons, stmt, helper);
    if (identifier != null) {
        if (done.put(metadata, identifier) != null) {
            throw new AssertionError(metadata);
        }
        return identifier;
    }
    /*
         * Trim the null values or empty collections. We perform this operation only after the check
         * for existing entries, in order to take in account null values when checking existing entries.
         */
    if (columnCreationPolicy != ValueExistencePolicy.ALL) {
        for (final Iterator<Object> it = asSingletons.values().iterator(); it.hasNext(); ) {
            if (it.next() == null) {
                it.remove();
            }
        }
    }
    /*
         * Process to the table creation if it does not already exists. If the table has parents, they will be
         * created first. The later will work only for database supporting table inheritance, like PostgreSQL.
         * For other kind of database engine, we can not store metadata having parent interfaces.
         */
    Boolean isChildTable = createTable(stmt, interfaceType, table, columns);
    if (isChildTable == null) {
        isChildTable = isChildTable(interfaceType);
    }
    /*
         * Add missing columns if there is any. If columns are added, we will keep trace of foreigner keys in
         * this process but will not create the constraints now because the foreigner tables may not exist yet.
         * They will be created later by recursive calls to this method a little bit below.
         */
    Map<String, Class<?>> colTypes = null, colTables = null;
    final Map<String, FKey> foreigners = new LinkedHashMap<>();
    for (final String column : asSingletons.keySet()) {
        if (!columns.contains(column)) {
            if (colTypes == null) {
                colTypes = standard.asTypeMap(implementationType, NAME_POLICY, TypeValuePolicy.ELEMENT_TYPE);
                colTables = standard.asTypeMap(implementationType, NAME_POLICY, TypeValuePolicy.DECLARING_INTERFACE);
            }
            /*
                 * We have found a column to add. Check if the column actually needs to be added to the parent table
                 * (if such parent exists). In most case, the answer is "no" and 'addTo' is equals to 'table'.
                 */
            String addTo = table;
            if (helper.dialect.isTableInheritanceSupported) {
                @SuppressWarnings("null") final Class<?> declaring = colTables.get(column);
                if (!interfaceType.isAssignableFrom(declaring)) {
                    addTo = getTableName(declaring);
                }
            }
            /*
                 * Determine the column data type. We infer that type from the method return value, not from the
                 * actual value for in the given metadata object, since the value type for the same property may
                 * be different in future calls to this method.
                 */
            int maxLength = maximumValueLength;
            Class<?> rt = colTypes.get(column);
            final boolean isCodeList = CodeList.class.isAssignableFrom(rt);
            if (isCodeList || standard.isMetadata(rt)) {
                /*
                     * Found a reference to an other metadata. Remind that column for creating a foreign key
                     * constraint later, except if the return type is an abstract CodeList or Enum (in which
                     * case the reference could be to any CodeList or Enum table). Abstract CodeList or Enum
                     * may happen when the concrete class is not yet available in the GeoAPI version that we
                     * are using.
                     */
                if (!isCodeList || !Modifier.isAbstract(rt.getModifiers())) {
                    if (foreigners.put(column, new FKey(addTo, rt, null)) != null) {
                        // Should never happen.
                        throw new AssertionError(column);
                    }
                }
                // For forcing VARCHAR type.
                rt = null;
                maxLength = maximumIdentifierLength;
            } else if (rt.isEnum()) {
                maxLength = maximumIdentifierLength;
            }
            stmt.executeUpdate(helper.createColumn(schema(), addTo, column, rt, maxLength));
            columns.add(column);
        }
    }
    /*
         * Get the identifier for the new metadata. If no identifier is proposed, we will try to recycle
         * the identifier of the parent.  For example in ISO 19115, Contact (which contains phone number,
         * etc.) is associated only to Responsibility. So it make sense to use the Responsibility ID for
         * the contact info.
         */
    identifier = nonEmpty(removeReservedChars(suggestIdentifier(metadata, asValueMap), null));
    if (identifier == null) {
        identifier = parent;
        if (identifier == null) {
            /*
                 * Arbitrarily pickup the first non-metadata attribute.
                 * Fallback on "unknown" if none are found.
                 */
            identifier = "unknown";
            for (final Object value : asSingletons.values()) {
                if (value != null && !standard.isMetadata(value.getClass())) {
                    identifier = abbreviation(value.toString());
                    break;
                }
            }
        }
    }
    /*
         * If the record to add is located in a child table, we need to prepend the child table name
         * in the identifier in order to allow MetadataSource to locate the right table to query.
         */
    final int minimalIdentifierLength;
    if (isChildTable) {
        identifier = TYPE_OPEN + table + TYPE_CLOSE + identifier;
        minimalIdentifierLength = table.length() + 2;
    } else {
        minimalIdentifierLength = 0;
    }
    /*
         * Check for key collision. We will add a suffix if there is one. Note that the final identifier must be
         * found before we put its value in the map, otherwise cyclic references (if any) will use the wrong value.
         *
         * First, we trim the identifier (primary key) to the maximal length. Then, the loop removes at most four
         * additional characters if the identifier is still too long. After that point, if the identifier still too
         * long, we will let the database driver produces its own SQLException.
         */
    try (IdentifierGenerator idCheck = new IdentifierGenerator(this, schema(), table, ID_COLUMN, helper)) {
        for (int i = 0; i < MINIMAL_LIMIT - 1; i++) {
            final int maxLength = maximumIdentifierLength - i;
            if (maxLength < minimalIdentifierLength)
                break;
            if (identifier.length() > maxLength) {
                identifier = identifier.substring(0, maxLength);
            }
            identifier = idCheck.identifier(identifier);
            if (identifier.length() <= maximumIdentifierLength) {
                break;
            }
        }
    }
    if (done.put(metadata, identifier) != null) {
        throw new AssertionError(metadata);
    }
    /*
         * Process all dependencies now. This block may invoke this method recursively.
         * Once a dependency has been added to the database, the corresponding value in
         * the 'asMap' HashMap is replaced by the identifier of the dependency we just added.
         */
    Map<String, FKey> referencedTables = null;
    for (final Map.Entry<String, Object> entry : asSingletons.entrySet()) {
        Object value = entry.getValue();
        final Class<?> type = value.getClass();
        if (CodeList.class.isAssignableFrom(type)) {
            value = addCode(stmt, (CodeList<?>) value);
        } else if (type.isEnum()) {
            value = ((Enum<?>) value).name();
        } else if (standard.isMetadata(type)) {
            String dependency = proxy(value);
            if (dependency == null) {
                dependency = done.get(value);
                if (dependency == null) {
                    dependency = add(stmt, value, done, identifier);
                    // Really identity comparison.
                    assert done.get(value) == dependency;
                    if (!helper.dialect.isIndexInheritanceSupported) {
                        /*
                             * In a classical object-oriented model, the foreigner key constraints declared in the
                             * parent table would take in account the records in the child table and we would have
                             * nothing special to do. However PostgreSQL 9.1 does not yet inherit index. So if we
                             * detect that a column references some records in two different tables, then we must
                             * suppress the foreigner key constraint.
                             */
                        final String column = entry.getKey();
                        final Class<?> targetType = standard.getInterface(value.getClass());
                        FKey fkey = foreigners.get(column);
                        if (fkey != null && !targetType.isAssignableFrom(fkey.tableType)) {
                            /*
                                 * The foreigner key constraint does not yet exist, so we can
                                 * change the target table. Set the target to the child table.
                                 */
                            fkey.tableType = targetType;
                        }
                        if (fkey == null) {
                            /*
                                 * The foreigner key constraint may already exist. Get a list of all foreigner keys for
                                 * the current table, then verify if the existing constraint references the right table.
                                 */
                            if (referencedTables == null) {
                                referencedTables = new HashMap<>();
                                try (ResultSet rs = stmt.getConnection().getMetaData().getImportedKeys(catalog, schema(), table)) {
                                    while (rs.next()) {
                                        if ((schema() == null || schema().equals(rs.getString("PKTABLE_SCHEM"))) && (catalog == null || catalog.equals(rs.getString("PKTABLE_CAT")))) {
                                            referencedTables.put(rs.getString("FKCOLUMN_NAME"), new FKey(rs.getString("PKTABLE_NAME"), null, rs.getString("FK_NAME")));
                                        }
                                    }
                                }
                            }
                            fkey = referencedTables.remove(column);
                            if (fkey != null && !fkey.tableName.equals(getTableName(targetType))) {
                                /*
                                     * The existing foreigner key constraint doesn't reference the right table.
                                     * We have no other choice than removing it...
                                     */
                                stmt.executeUpdate(helper.clear().append("ALTER TABLE ").appendIdentifier(schema(), table).append(" DROP CONSTRAINT ").appendIdentifier(fkey.keyName).toString());
                                warning(MetadataWriter.class, "add", Messages.getResources(null).getLogRecord(Level.WARNING, Messages.Keys.DroppedForeignerKey_1, table + '.' + column + " ⇒ " + fkey.tableName + '.' + ID_COLUMN));
                            }
                        }
                    }
                }
            }
            value = dependency;
        }
        entry.setValue(value);
    }
    /*
         * Now that all dependencies have been inserted in the database, we can setup the foreigner key constraints
         * if there is any. Note that we deferred the foreigner key creations not because of the missing rows,
         * but because of missing tables (since new tables may be created in the process of inserting dependencies).
         */
    if (!foreigners.isEmpty()) {
        for (final Map.Entry<String, FKey> entry : foreigners.entrySet()) {
            final FKey fkey = entry.getValue();
            Class<?> rt = fkey.tableType;
            final boolean isCodeList = CodeList.class.isAssignableFrom(rt);
            final String primaryKey;
            if (isCodeList) {
                primaryKey = CODE_COLUMN;
            } else {
                primaryKey = ID_COLUMN;
                rt = standard.getInterface(rt);
            }
            final String column = entry.getKey();
            final String target = getTableName(rt);
            stmt.executeUpdate(helper.createForeignKey(// Source (schema.table.column)
            schema(), // Source (schema.table.column)
            fkey.tableName, // Source (schema.table.column)
            column, // Target (table.column)
            target, // Target (table.column)
            primaryKey, // CASCADE if metadata, RESTRICT if CodeList or Enum.
            !isCodeList));
            /*
                 * In a classical object-oriented model, the constraint would be inherited by child tables.
                 * However this is not yet supported as of PostgreSQL 9.6. If inheritance is not supported,
                 * then we have to repeat the constraint creation in child tables.
                 */
            if (!helper.dialect.isIndexInheritanceSupported && !table.equals(fkey.tableName)) {
                stmt.executeUpdate(helper.createForeignKey(schema(), table, column, target, primaryKey, !isCodeList));
            }
        }
    }
    /*
         * Create the SQL statement which will insert the data.
         */
    helper.clear().append("INSERT INTO ").appendIdentifier(schema(), table).append(" (").append(ID_COLUMN);
    for (final String column : asSingletons.keySet()) {
        helper.append(", ").appendIdentifier(column);
    }
    helper.append(") VALUES (").appendValue(identifier);
    for (final Object value : asSingletons.values()) {
        helper.append(", ").appendValue(value);
    }
    final String sql = helper.append(')').toString();
    if (stmt.executeUpdate(sql) != 1) {
        throw new SQLException(Errors.format(Errors.Keys.DatabaseUpdateFailure_3, 0, table, identifier));
    }
    return identifier;
}

Example 3 with SQLBuilder

use of org.apache.sis.internal.metadata.sql.SQLBuilder in project sis by apache.

the class IdentifierGeneratorTest method testSequence.

/**
 * Tests the creation of identifiers with sequence numbers.
 *
 * @throws Exception if an error occurred while reading or writing in the temporary database.
 */
@Test
public void testSequence() throws Exception {
    final DataSource ds = TestDatabase.create("IdentifierGenerator");
    try {
        final MetadataSource source = new MetadataSource(MetadataStandard.ISO_19115, ds, null, null);
        synchronized (source) {
            stmt = source.connection().createStatement();
            stmt.executeUpdate("CREATE TABLE \"" + TABLE + "\" (ID VARCHAR(6) NOT NULL PRIMARY KEY)");
            generator = new IdentifierGenerator(source, null, TABLE, "ID", new SQLBuilder(source.connection().getMetaData(), false));
            /*
                 * Actual tests.
                 */
            addRecords("TD", 324);
            removeAndAddRecords("TD");
            addRecords("OT", 30);
            /*
                 * Cleaning.
                 */
            stmt.executeUpdate("DROP TABLE \"" + TABLE + '"');
            stmt.close();
            generator.close();
            source.close();
        }
    } finally {
        TestDatabase.drop(ds);
    }
}

Example 4 with SQLBuilder

use of org.apache.sis.internal.metadata.sql.SQLBuilder in project sis by apache.

the class MetadataSource method readColumn.

/**
 * Invoked by {@link MetadataProxy} for fetching an attribute value from a table.
 *
 * @param  info      the interface type (together with cached information).
 *                   This is mapped to the table name in the database.
 * @param  method    the method invoked. This is mapped to the column name in the database.
 * @param  toSearch  contains the identifier and preferred index of the record to search.
 * @return the value of the requested attribute.
 * @throws SQLException if the SQL query failed.
 * @throws MetadataStoreException if a value was not found or can not be converted to the expected type.
 */
final Object readColumn(final LookupInfo info, final Method method, final Dispatcher toSearch) throws SQLException, MetadataStoreException {
    /*
         * If the identifier is prefixed with a table name as in "{CI_Organisation}identifier",
         * the name between bracket is a subtype of the given 'type' argument.
         */
    final Class<?> type = subType(info.getMetadataType(), toSearch.identifier);
    final Class<?> returnType = Interim.getReturnType(method);
    final boolean wantCollection = Collection.class.isAssignableFrom(returnType);
    final Class<?> elementType = wantCollection ? Classes.boundOfParameterizedProperty(method) : returnType;
    final boolean isMetadata = standard.isMetadata(elementType);
    final String tableName = getTableName(type);
    final String columnName = info.asNameMap(standard).get(method.getName());
    final boolean isArray;
    Object value;
    synchronized (this) {
        if (!getExistingColumns(tableName).contains(columnName)) {
            value = null;
            isArray = false;
        } else {
            /*
                 * Prepares the statement and executes the SQL query in this synchronized block.
                 * Note that the usage of 'result' must stay inside this synchronized block
                 * because we can not assume that JDBC connections are thread-safe.
                 */
            CachedStatement result = take(type, Byte.toUnsignedInt(toSearch.preferredIndex));
            if (result == null) {
                final SQLBuilder helper = helper();
                final String query = helper.clear().append("SELECT * FROM ").appendIdentifier(schema, tableName).append(" WHERE ").append(ID_COLUMN).append("=?").toString();
                result = new CachedStatement(type, connection().prepareStatement(query), listeners);
            }
            value = result.getValue(toSearch.identifier, columnName);
            isArray = (value instanceof java.sql.Array);
            if (isArray) {
                final java.sql.Array array = (java.sql.Array) value;
                value = array.getArray();
                array.free();
            }
            toSearch.preferredIndex = (byte) recycle(result, Byte.toUnsignedInt(toSearch.preferredIndex));
        }
    }
    /*
         * If the value is an array and the return type is anything except an array of primitive type, ensure
         * that the value is converted in an array of type Object[]. In this process, resolve foreigner keys.
         */
    if (isArray && (wantCollection || !elementType.isPrimitive())) {
        final Object[] values = new Object[Array.getLength(value)];
        for (int i = 0; i < values.length; i++) {
            Object element = Array.get(value, i);
            if (element != null) {
                if (isMetadata) {
                    element = lookup(elementType, element.toString());
                } else
                    try {
                        element = info.convert(elementType, element);
                    } catch (UnconvertibleObjectException e) {
                        throw new MetadataStoreException(Errors.format(Errors.Keys.IllegalPropertyValueClass_3, columnName + '[' + i + ']', elementType, element.getClass()), e);
                    }
            }
            values[i] = element;
        }
        // Now a Java array.
        value = values;
        if (wantCollection) {
            value = specialize(UnmodifiableArrayList.wrap(values), returnType, elementType);
        }
    }
    /*
         * Now converts the value to its final type. To be strict, we should convert null values into empty collections
         * if the return type is a collection type. But we leave this task to the caller (which is the Dispatcher class)
         * for making easier to detect when a value is absent, for allowing Dispatcher to manage its cache.
         */
    if (value != null) {
        if (isMetadata) {
            value = lookup(elementType, value.toString());
        } else
            try {
                value = info.convert(elementType, value);
            } catch (UnconvertibleObjectException e) {
                throw new MetadataStoreException(Errors.format(Errors.Keys.IllegalPropertyValueClass_3, columnName, elementType, value.getClass()), e);
            }
        if (wantCollection) {
            if (Set.class.isAssignableFrom(returnType)) {
                return Collections.singleton(value);
            } else {
                return Collections.singletonList(value);
            }
        }
    }
    return value;
}

Example 5 with SQLBuilder

use of org.apache.sis.internal.metadata.sql.SQLBuilder in project sis by apache.

the class MetadataWriter method createTable.

/**
 * Creates a table for the given type, if the table does not already exists.
 * This method may call itself recursively for creating parent tables, if they do not exist neither.
 * This method opportunistically computes the same return value than {@link #isChildTable(Class)}.
 *
 * @param  stmt     the statement to use for creating tables.
 * @param  type     the interface class.
 * @param  table    the name of the table (should be consistent with the type).
 * @param  columns  the existing columns, as an empty set if the table does not exist yet.
 * @return the value that {@code isChildTable(type)} would return, or {@code null} if undetermined.
 * @throws SQLException if an error occurred while creating the table.
 */
private Boolean createTable(final Statement stmt, final Class<?> type, final String table, final Set<String> columns) throws SQLException {
    Boolean isChildTable = null;
    if (columns.isEmpty()) {
        isChildTable = Boolean.FALSE;
        StringBuilder inherits = null;
        for (final Class<?> candidate : getParentTypes(type)) {
            if (standard.isMetadata(candidate)) {
                isChildTable = Boolean.TRUE;
                final SQLBuilder helper = helper();
                if (helper.dialect.isTableInheritanceSupported) {
                    final String parent = getTableName(candidate);
                    createTable(stmt, candidate, parent, getExistingColumns(parent));
                    if (inherits == null) {
                        helper.clear().append("CREATE TABLE ").appendIdentifier(schema(), table);
                        if (!helper.dialect.isIndexInheritanceSupported) {
                            /*
                                 * In a classical object-oriented model, the new child table would inherit the index from
                                 * its parent table. However this is not yet the case as of PostgreSQL 9.6. If the index is
                                 * not inherited, then we have to repeat the primary key creation in every child tables.
                                 */
                            helper.append("(CONSTRAINT ").appendIdentifier(table + "_pkey").append(" PRIMARY KEY (").append(ID_COLUMN).append(")) ");
                        }
                        inherits = new StringBuilder(helper.append(" INHERITS (").toString());
                    } else {
                        inherits.append(", ");
                    }
                    inherits.append(helper.clear().appendIdentifier(schema(), parent));
                }
            }
        }
        final String sql;
        if (inherits != null) {
            sql = inherits.append(')').toString();
        } else {
            sql = createTable(table, ID_COLUMN);
        }
        stmt.executeUpdate(sql);
        columns.add(ID_COLUMN);
    }
    return isChildTable;
}