Examples with RowDescriptor org.firebirdsql.gds.ng.fields.RowDescriptor used on opensource projects

Example 6 with RowDescriptor

use of org.firebirdsql.gds.ng.fields.RowDescriptor in project jaybird by FirebirdSQL.

the class FBDatabaseMetaData method getColumns.

// @formatter:on
/**
 * Retrieves a description of table columns available in
 * the specified catalog.
 *
 * <P>Only column descriptions matching the catalog, schema, table
 * and column name criteria are returned.  They are ordered by
 * <code>TABLE_CAT</code>,<code>TABLE_SCHEM</code>,
 * <code>TABLE_NAME</code>, and <code>ORDINAL_POSITION</code>.
 *
 * <P>Each column description has the following columns:
 *  <OL>
 *  <LI><B>TABLE_CAT</B> String => table catalog (may be <code>null</code>)
 *  <LI><B>TABLE_SCHEM</B> String => table schema (may be <code>null</code>)
 *  <LI><B>TABLE_NAME</B> String => table name
 *  <LI><B>COLUMN_NAME</B> String => column name
 *  <LI><B>DATA_TYPE</B> int => SQL type from java.sql.Types
 *  <LI><B>TYPE_NAME</B> String => Data source dependent type name,
 *  for a UDT the type name is fully qualified
 *  <LI><B>COLUMN_SIZE</B> int => column size.
 *  <LI><B>BUFFER_LENGTH</B> is not used.
 *  <LI><B>DECIMAL_DIGITS</B> int => the number of fractional digits. Null is returned for data types where
 * DECIMAL_DIGITS is not applicable.
 *  <LI><B>NUM_PREC_RADIX</B> int => Radix (typically either 10 or 2)
 *  <LI><B>NULLABLE</B> int => is NULL allowed.
 *      <UL>
 *      <LI> columnNoNulls - might not allow <code>NULL</code> values
 *      <LI> columnNullable - definitely allows <code>NULL</code> values
 *      <LI> columnNullableUnknown - nullability unknown
 *      </UL>
 *  <LI><B>REMARKS</B> String => comment describing column (may be <code>null</code>)
 *  <LI><B>COLUMN_DEF</B> String => default value for the column, which should be interpreted as a string when the value is enclosed in single quotes (may be <code>null</code>)
 *  <LI><B>SQL_DATA_TYPE</B> int => unused
 *  <LI><B>SQL_DATETIME_SUB</B> int => unused
 *  <LI><B>CHAR_OCTET_LENGTH</B> int => for char types the
 *       maximum number of bytes in the column
 *  <LI><B>ORDINAL_POSITION</B> int => index of column in table
 *      (starting at 1)
 *  <LI><B>IS_NULLABLE</B> String  => ISO rules are used to determine the nullability for a column.
 *       <UL>
 *       <LI> YES           --- if the column can include NULLs
 *       <LI> NO            --- if the column cannot include NULLs
 *       <LI> empty string  --- if the nullability for the
 * column is unknown
 *       </UL>
 *  <LI><B>SCOPE_CATALOG</B> String => catalog of table that is the scope
 *      of a reference attribute (<code>null</code> if DATA_TYPE isn't REF)
 *  <LI><B>SCOPE_SCHEMA</B> String => schema of table that is the scope
 *      of a reference attribute (<code>null</code> if the DATA_TYPE isn't REF)
 *  <LI><B>SCOPE_TABLE</B> String => table name that this the scope
 *      of a reference attribute (<code>null</code> if the DATA_TYPE isn't REF)
 *  <LI><B>SOURCE_DATA_TYPE</B> short => source type of a distinct type or user-generated
 *      Ref type, SQL type from java.sql.Types (<code>null</code> if DATA_TYPE
 *      isn't DISTINCT or user-generated REF)
 *   <LI><B>IS_AUTOINCREMENT</B> String  => Indicates whether this column is auto incremented
 *       <UL>
 *       <LI> YES           --- if the column is auto incremented
 *       <LI> NO            --- if the column is not auto incremented
 *       <LI> empty string  --- if it cannot be determined whether the column is auto incremented
 *       </UL>
 *   <LI><B>IS_GENERATEDCOLUMN</B> String  => Indicates whether this is a generated column
 *       <UL>
 *       <LI> YES           --- if this a generated column
 *       <LI> NO            --- if this not a generated column
 *       <LI> empty string  --- if it cannot be determined whether this is a generated column
 *       </UL>
 *  <LI><B>JB_IS_IDENTITY</B> String  => Indicates whether this column is an identity column (<b>NOTE: Jaybird specific column; retrieve by name!</b>).
 *      There is subtle difference with the meaning of {@code IS_AUTOINCREMENT}. This column indicates if the column
 *      is a true identity column.
 *       <UL>
 *       <LI> YES           --- if the column is an identity column
 *       <LI> NO            --- if the column is not an identity column
 *       </UL>
 *  <LI><B>JB_IDENTITY_TYPE</B> String  => Type of identity column (<b>NOTE: Jaybird specific column; retrieve by name!</b>)
 *       <UL>
 *       <LI> ALWAYS        --- for a GENERATED ALWAYS AS IDENTITY column (not yet supported in Firebird 3!)
 *       <LI> BY DEFAULT    --- for a GENERATED BY DEFAULT AS IDENTITY column
 *       <LI> null          --- if the column is not an identity type (or the identity type is unknown)
 *       </UL>
 *  </OL>
 *
 * <p>The COLUMN_SIZE column specifies the column size for the given column.
 * For numeric data, this is the maximum precision.  For character data, this is the length in characters.
 * For datetime datatypes, this is the length in characters of the String representation (assuming the
 * maximum allowed precision of the fractional seconds component). For binary data, this is the length in bytes.  For the ROWID datatype,
 * this is the length in bytes. Null is returned for data types where the
 * column size is not applicable.
 *
 * @param catalog a catalog name; must match the catalog name as it
 *        is stored in the database; "" retrieves those without a catalog;
 *        <code>null</code> means that the catalog name should not be used to narrow
 *        the search
 * @param schemaPattern a schema name pattern; must match the schema name
 *        as it is stored in the database; "" retrieves those without a schema;
 *        <code>null</code> means that the schema name should not be used to narrow
 *        the search
 * @param tableNamePattern a table name pattern; must match the
 *        table name as it is stored in the database
 * @param columnNamePattern a column name pattern; must match the column
 *        name as it is stored in the database
 * @return <code>ResultSet</code> - each row is a column description
 * @exception SQLException if a database access error occurs
 * @see #getSearchStringEscape
 */
public ResultSet getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern) throws SQLException {
    final RowDescriptor rowDescriptor = new RowDescriptorBuilder(26, datatypeCoder).at(0).simple(SQL_VARYING, OBJECT_NAME_LENGTH, "TABLE_CAT", "COLUMNINFO").addField().at(1).simple(SQL_VARYING, OBJECT_NAME_LENGTH, "TABLE_SCHEM", "COLUMNINFO").addField().at(2).simple(SQL_VARYING, OBJECT_NAME_LENGTH, "TABLE_NAME", "COLUMNINFO").addField().at(3).simple(SQL_VARYING, OBJECT_NAME_LENGTH, "COLUMN_NAME", "COLUMNINFO").addField().at(4).simple(SQL_LONG, 0, "DATA_TYPE", "COLUMNINFO").addField().at(5).simple(SQL_VARYING | 1, 31, "TYPE_NAME", "COLUMNINFO").addField().at(6).simple(SQL_LONG, 0, "COLUMN_SIZE", "COLUMNINFO").addField().at(7).simple(SQL_LONG, 0, "BUFFER_LENGTH", "COLUMNINFO").addField().at(8).simple(SQL_LONG, 0, "DECIMAL_DIGITS", "COLUMNINFO").addField().at(9).simple(SQL_LONG, 0, "NUM_PREC_RADIX", "COLUMNINFO").addField().at(10).simple(SQL_LONG, 0, "NULLABLE", "COLUMNINFO").addField().at(11).simple(SQL_VARYING | 1, Integer.MAX_VALUE, "REMARKS", "COLUMNINFO").addField().at(12).simple(SQL_VARYING | 1, 31, "COLUMN_DEF", "COLUMNINFO").addField().at(13).simple(SQL_LONG, 0, "SQL_DATA_TYPE", "COLUMNINFO").addField().at(14).simple(SQL_LONG, 0, "SQL_DATETIME_SUB", "COLUMNINFO").addField().at(15).simple(SQL_LONG, 0, "CHAR_OCTET_LENGTH", "COLUMNINFO").addField().at(16).simple(SQL_LONG, 0, "ORDINAL_POSITION", "COLUMNINFO").addField().at(17).simple(SQL_VARYING, 3, "IS_NULLABLE", "COLUMNINFO").addField().at(18).simple(SQL_VARYING, OBJECT_NAME_LENGTH, getScopeCatalogColumnName(), "COLUMNINFO").addField().at(19).simple(SQL_VARYING, OBJECT_NAME_LENGTH, "SCOPE_SCHEMA", "COLUMNINFO").addField().at(20).simple(SQL_VARYING, OBJECT_NAME_LENGTH, "SCOPE_TABLE", "COLUMNINFO").addField().at(21).simple(SQL_SHORT, 0, "SOURCE_DATA_TYPE", "COLUMNINFO").addField().at(22).simple(SQL_VARYING, 3, "IS_AUTOINCREMENT", "COLUMNINFO").addField().at(23).simple(SQL_VARYING, 3, "IS_GENERATEDCOLUMN", "COLUMNINFO").addField().at(24).simple(SQL_VARYING, 3, "JB_IS_IDENTITY", "COLUMNINFO").addField().at(25).simple(SQL_VARYING, 10, "JB_IDENTITY_TYPE", "COLUMNINFO").addField().toRowDescriptor();
    Clause tableClause = new Clause("RF.RDB$RELATION_NAME", tableNamePattern);
    Clause columnClause = new Clause("RF.RDB$FIELD_NAME", columnNamePattern);
    String sql = hasIdentityColumns() ? GET_COLUMNS_3_0_START : GET_COLUMNS_START;
    sql += tableClause.getCondition();
    sql += columnClause.getCondition();
    sql += GET_COLUMNS_END;
    List<String> params = new ArrayList<>(2);
    if (tableClause.hasCondition()) {
        params.add(tableClause.getValue());
    }
    if (columnClause.hasCondition()) {
        params.add(columnClause.getValue());
    }
    try (ResultSet rs = doQuery(sql, params)) {
        if (!rs.next()) {
            return new FBResultSet(rowDescriptor, Collections.<RowValue>emptyList());
        }
        final List<RowValue> rows = new ArrayList<>();
        final RowValueBuilder valueBuilder = new RowValueBuilder(rowDescriptor);
        do {
            final short fieldType = rs.getShort("FIELD_TYPE");
            final short fieldSubType = rs.getShort("FIELD_SUB_TYPE");
            final short fieldScale = rs.getShort("FIELD_SCALE");
            final int characterSetId = rs.getInt("RDB$CHARACTER_SET_ID");
            final int dataType = getDataType(fieldType, fieldSubType, fieldScale, characterSetId);
            valueBuilder.at(2).set(getBytes(rs.getString("RELATION_NAME"))).at(3).set(getBytes(rs.getString("FIELD_NAME"))).at(4).set(createInt(dataType)).at(5).set(getBytes(getDataTypeName(fieldType, fieldSubType, fieldScale))).at(9).set(RADIX_TEN);
            switch(dataType) {
                case Types.DECIMAL:
                case Types.NUMERIC:
                    valueBuilder.at(6).set(createInt(rs.getShort("FIELD_PRECISION"))).at(8).set(createInt(fieldScale * (-1)));
                    break;
                case Types.CHAR:
                case Types.VARCHAR:
                case Types.BINARY:
                case Types.VARBINARY:
                    valueBuilder.at(15).set(createInt(rs.getShort("FIELD_LENGTH")));
                    short charLen = rs.getShort("CHAR_LEN");
                    if (!rs.wasNull()) {
                        valueBuilder.at(6).set(createInt(charLen));
                    } else {
                        valueBuilder.at(6).set(valueBuilder.get(15));
                    }
                    break;
                case Types.FLOAT:
                    valueBuilder.at(6).set(FLOAT_PRECISION);
                    break;
                case Types.DOUBLE:
                    valueBuilder.at(6).set(DOUBLE_PRECISION);
                    break;
                case Types.BIGINT:
                    valueBuilder.at(6).set(BIGINT_PRECISION).at(8).set(INT_ZERO);
                    break;
                case Types.INTEGER:
                    valueBuilder.at(6).set(INTEGER_PRECISION).at(8).set(INT_ZERO);
                    break;
                case Types.SMALLINT:
                    valueBuilder.at(6).set(SMALLINT_PRECISION).at(8).set(INT_ZERO);
                    break;
                case Types.DATE:
                    valueBuilder.at(6).set(DATE_PRECISION);
                    break;
                case Types.TIME:
                    valueBuilder.at(6).set(TIME_PRECISION);
                    break;
                case Types.TIMESTAMP:
                    valueBuilder.at(6).set(TIMESTAMP_PRECISION);
                    break;
                case Types.BOOLEAN:
                    valueBuilder.at(6).set(BOOLEAN_PRECISION).at(9).set(RADIX_BINARY);
                    break;
                case JaybirdTypeCodes.DECFLOAT:
                    switch(fieldType) {
                        case dec16_type:
                            valueBuilder.at(6).set(DECFLOAT_16_PRECISION);
                            break;
                        case dec34_type:
                            valueBuilder.at(6).set(DECFLOAT_34_PRECISION);
                            break;
                    }
                    break;
            }
            final short nullFlag = rs.getShort("NULL_FLAG");
            final short sourceNullFlag = rs.getShort("SOURCE_NULL_FLAG");
            valueBuilder.at(10).set(nullFlag == 1 || sourceNullFlag == 1 ? COLUMN_NO_NULLS : COLUMN_NULLABLE).at(11).set(getBytes(rs.getString("REMARKS")));
            String column_def = rs.getString("DEFAULT_SOURCE");
            if (column_def == null) {
                column_def = rs.getString("DOMAIN_DEFAULT_SOURCE");
            }
            if (column_def != null) {
                // TODO This looks suspicious (what if it contains default)
                int defaultPos = column_def.toUpperCase().indexOf("DEFAULT");
                if (defaultPos >= 0)
                    column_def = column_def.substring(7).trim();
                valueBuilder.at(12).set(getBytes(column_def));
            }
            valueBuilder.at(16).set(createInt(rs.getInt("FIELD_POSITION"))).at(17).set(nullFlag == 1 || sourceNullFlag == 1 ? NO_BYTES : YES_BYTES);
            final boolean isIdentity = Objects.equals("YES", rs.getString("IS_IDENTITY"));
            if (isIdentity) {
                // identity column is an autoincrement for sure
                valueBuilder.at(22).set(YES_BYTES);
            } else {
                switch(dataType) {
                    case Types.INTEGER:
                    case Types.TINYINT:
                    case Types.BIGINT:
                    case Types.SMALLINT:
                        // Could be autoincrement by trigger, but we simply don't know
                        valueBuilder.at(22).set(EMPTY_STRING_BYTES);
                        break;
                    case Types.NUMERIC:
                    case Types.DECIMAL:
                        if (fieldScale == 0) {
                            // Could be autoincrement by trigger, but we simply don't know
                            valueBuilder.at(22).set(EMPTY_STRING_BYTES);
                        } else {
                            // Scaled NUMERIC/DECIMAL: definitely not autoincrement
                            valueBuilder.at(22).set(NO_BYTES);
                        }
                        break;
                    default:
                        // All other types are never autoincrement
                        valueBuilder.at(22).set(NO_BYTES);
                }
            }
            // Retrieving COMPUTED_BLR to check if it was NULL or not
            rs.getString("COMPUTED_BLR");
            // consider identity columns to be generated columns
            boolean isGenerated = !rs.wasNull() || isIdentity;
            valueBuilder.at(23).set(isGenerated ? YES_BYTES : NO_BYTES);
            valueBuilder.at(24).set(isIdentity ? YES_BYTES : NO_BYTES);
            valueBuilder.at(25).set(getBytes(rs.getString("JB_IDENTITY_TYPE")));
            rows.add(valueBuilder.toRowValue(true));
        } while (rs.next());
        return new FBResultSet(rowDescriptor, rows);
    }
}

Example 7 with RowDescriptor

use of org.firebirdsql.gds.ng.fields.RowDescriptor in project jaybird by FirebirdSQL.

the class FBDatabaseMetaData method getBestRowIdentifier.

// @formatter:on
@Override
public ResultSet getBestRowIdentifier(String catalog, String schema, String table, int scope, boolean nullable) throws SQLException {
    // TODO Handling of scope is wrong
    final RowDescriptor rowDescriptor = new RowDescriptorBuilder(8, datatypeCoder).at(0).simple(SQL_SHORT, 0, "SCOPE", "ROWIDENTIFIER").addField().at(1).simple(SQL_VARYING, OBJECT_NAME_LENGTH, "COLUMN_NAME", "ROWIDENTIFIER").addField().at(2).simple(SQL_SHORT, 0, "DATA_TYPE", "ROWIDENTIFIER").addField().at(3).simple(SQL_VARYING, 31, "TYPE_NAME", "ROWIDENTIFIER").addField().at(4).simple(SQL_LONG, 0, "COLUMN_SIZE", "ROWIDENTIFIER").addField().at(5).simple(SQL_LONG, 0, "BUFFER_LENGTH", "ROWIDENTIFIER").addField().at(6).simple(SQL_SHORT, 0, "DECIMAL_DIGITS", "ROWIDENTIFIER").addField().at(7).simple(SQL_SHORT, 0, "PSEUDO_COLUMN", "ROWIDENTIFIER").addField().toRowDescriptor();
    List<RowValue> rows;
    final RowValueBuilder rowValueBuilder = new RowValueBuilder(rowDescriptor);
    // Check if table exists, need to escape as getTables takes a pattern
    String quoteLikeTable = escapeWildcards(table);
    try (ResultSet tables = getTables(catalog, schema, quoteLikeTable, null)) {
        if (!tables.next()) {
            return new FBResultSet(rowDescriptor, Collections.<RowValue>emptyList());
        }
        rows = getPrimaryKeyIdentifier(tables.getString(3), scope, rowValueBuilder);
    }
    // if no primary key exists, add RDB$DB_KEY as pseudo-column
    if (rows.size() == 0) {
        rows.add(rowValueBuilder.at(0).set(createShort(scope)).at(1).set(getBytes("RDB$DB_KEY")).at(2).set(createShort(Types.ROWID)).at(3).set(getBytes(getDataTypeName(char_type, 0, CS_BINARY))).at(4).set(// TODO Consider querying RDB$RELATIONS for the actual size of the DB_KEY
        createInt(8)).at(6).set(createShort(0)).at(7).set(createShort(bestRowPseudo)).toRowValue(true));
    }
    return new FBResultSet(rowDescriptor, rows);
}

Example 8 with RowDescriptor

use of org.firebirdsql.gds.ng.fields.RowDescriptor in project jaybird by FirebirdSQL.

the class FBDatabaseMetaData method getExportedKeys.

/**
 * Gets a description of the foreign key columns that reference a
 * table's primary key columns (the foreign keys exported by a
 * table).  They are ordered by FKTABLE_CAT, FKTABLE_SCHEM,
 * FKTABLE_NAME, and KEY_SEQ.
 *
 * <P>Each foreign key column description has the following columns:
 *  <OL>
 *  <LI><B>PKTABLE_CAT</B> String => primary key table catalog (may be null)
 *  <LI><B>PKTABLE_SCHEM</B> String => primary key table schema (may be null)
 *  <LI><B>PKTABLE_NAME</B> String => primary key table name
 *  <LI><B>PKCOLUMN_NAME</B> String => primary key column name
 *  <LI><B>FKTABLE_CAT</B> String => foreign key table catalog (may be null)
 *      being exported (may be null)
 *  <LI><B>FKTABLE_SCHEM</B> String => foreign key table schema (may be null)
 *      being exported (may be null)
 *  <LI><B>FKTABLE_NAME</B> String => foreign key table name
 *      being exported
 *  <LI><B>FKCOLUMN_NAME</B> String => foreign key column name
 *      being exported
 *  <LI><B>KEY_SEQ</B> short => sequence number within foreign key
 *  <LI><B>UPDATE_RULE</B> short => What happens to
 *       foreign key when primary is updated:
 *      <UL>
 *      <LI> importedNoAction - do not allow update of primary
 *               key if it has been imported
 *      <LI> importedKeyCascade - change imported key to agree
 *               with primary key update
 *      <LI> importedKeySetNull - change imported key to NULL if
 *               its primary key has been updated
 *      <LI> importedKeySetDefault - change imported key to default values
 *               if its primary key has been updated
 *      <LI> importedKeyRestrict - same as importedKeyNoAction
 *                                 (for ODBC 2.x compatibility)
 *      </UL>
 *  <LI><B>DELETE_RULE</B> short => What happens to
 *      the foreign key when primary is deleted.
 *      <UL>
 *      <LI> importedKeyNoAction - do not allow delete of primary
 *               key if it has been imported
 *      <LI> importedKeyCascade - delete rows that import a deleted key
 *      <LI> importedKeySetNull - change imported key to NULL if
 *               its primary key has been deleted
 *      <LI> importedKeyRestrict - same as importedKeyNoAction
 *                                 (for ODBC 2.x compatibility)
 *      <LI> importedKeySetDefault - change imported key to default if
 *               its primary key has been deleted
 *      </UL>
 *  <LI><B>FK_NAME</B> String => foreign key name (may be null)
 *  <LI><B>PK_NAME</B> String => primary key name (may be null)
 *  <LI><B>DEFERRABILITY</B> short => can the evaluation of foreign key
 *      constraints be deferred until commit
 *      <UL>
 *      <LI> importedKeyInitiallyDeferred - see SQL92 for definition
 *      <LI> importedKeyInitiallyImmediate - see SQL92 for definition
 *      <LI> importedKeyNotDeferrable - see SQL92 for definition
 *      </UL>
 *  </OL>
 *
 * @param catalog a catalog name; "" retrieves those without a
 * catalog; null means drop catalog name from the selection criteria
 * @param schema a schema name; "" retrieves those
 * without a schema
 * @param table a table name
 * @return <code>ResultSet</code> - each row is a foreign key column description
 * @exception SQLException if a database access error occurs
 * @see #getImportedKeys
 */
public ResultSet getExportedKeys(String catalog, String schema, String table) throws SQLException {
    final RowDescriptor rowDescriptor = new RowDescriptorBuilder(14, datatypeCoder).at(0).simple(SQL_VARYING, OBJECT_NAME_LENGTH, "PKTABLE_CAT", "COLUMNINFO").addField().at(1).simple(SQL_VARYING, OBJECT_NAME_LENGTH, "PKTABLE_SCHEM", "COLUMNINFO").addField().at(2).simple(SQL_VARYING, OBJECT_NAME_LENGTH, "PKTABLE_NAME", "COLUMNINFO").addField().at(3).simple(SQL_VARYING, OBJECT_NAME_LENGTH, "PKCOLUMN_NAME", "COLUMNINFO").addField().at(4).simple(SQL_VARYING, OBJECT_NAME_LENGTH, "FKTABLE_CAT", "COLUMNINFO").addField().at(5).simple(SQL_VARYING, OBJECT_NAME_LENGTH, "FKTABLE_SCHEM", "COLUMNINFO").addField().at(6).simple(SQL_VARYING, OBJECT_NAME_LENGTH, "FKTABLE_NAME", "COLUMNINFO").addField().at(7).simple(SQL_VARYING, OBJECT_NAME_LENGTH, "FKCOLUMN_NAME", "COLUMNINFO").addField().at(8).simple(SQL_SHORT, 0, "KEY_SEQ", "COLUMNINFO").addField().at(9).simple(SQL_SHORT, 0, "UPDATE_RULE", "COLUMNINFO").addField().at(10).simple(SQL_SHORT, 0, "DELETE_RULE", "COLUMNINFO").addField().at(11).simple(SQL_VARYING, OBJECT_NAME_LENGTH, "FK_NAME", "COLUMNINFO").addField().at(12).simple(SQL_VARYING, OBJECT_NAME_LENGTH, "PK_NAME", "COLUMNINFO").addField().at(13).simple(SQL_SHORT, 0, "DEFERRABILITY", "COLUMNINFO").addField().toRowDescriptor();
    List<String> params = Collections.singletonList(table);
    try (ResultSet rs = doQuery(GET_EXPORTED_KEYS, params)) {
        // if nothing found, return an empty result set
        if (!rs.next()) {
            return new FBResultSet(rowDescriptor, Collections.<RowValue>emptyList());
        }
        List<RowValue> rows = new ArrayList<>();
        final RowValueBuilder valueBuilder = new RowValueBuilder(rowDescriptor);
        do {
            rows.add(valueBuilder.at(2).set(getBytes(rs.getString("PKTABLE_NAME"))).at(3).set(getBytes(rs.getString("PKCOLUMN_NAME"))).at(6).set(getBytes(rs.getString("FKTABLE_NAME"))).at(7).set(getBytes(rs.getString("FKCOLUMN_NAME"))).at(8).set(createShort(rs.getShort("KEY_SEQ"))).at(9).set(mapAction(rs.getString("UPDATE_RULE"))).at(10).set(mapAction(rs.getString("DELETE_RULE"))).at(11).set(getBytes(rs.getString("FK_NAME"))).at(12).set(getBytes(rs.getString("PK_NAME"))).at(13).set(IMPORTED_KEY_NOT_DEFERRABLE).toRowValue(true));
        } while (rs.next());
        return new FBResultSet(rowDescriptor, rows);
    }
}

Example 9 with RowDescriptor

use of org.firebirdsql.gds.ng.fields.RowDescriptor in project jaybird by FirebirdSQL.

the class FBDatabaseMetaData method getIndexInfo.

/**
 * Gets a description of a table's indices and statistics. They are
 * ordered by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.
 *
 * <P>Each index column description has the following columns:
 *  <OL>
 *  <LI><B>TABLE_CAT</B> String => table catalog (may be null)
 *  <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
 *  <LI><B>TABLE_NAME</B> String => table name
 *  <LI><B>NON_UNIQUE</B> boolean => Can index values be non-unique?
 *      false when TYPE is tableIndexStatistic
 *  <LI><B>INDEX_QUALIFIER</B> String => index catalog (may be null);
 *      null when TYPE is tableIndexStatistic
 *  <LI><B>INDEX_NAME</B> String => index name; null when TYPE is
 *      tableIndexStatistic
 *  <LI><B>TYPE</B> short => index type:
 *      <UL>
 *      <LI> tableIndexStatistic - this identifies table statistics that are
 *           returned in conjuction with a table's index descriptions
 *      <LI> tableIndexClustered - this is a clustered index
 *      <LI> tableIndexHashed - this is a hashed index
 *      <LI> tableIndexOther - this is some other style of index
 *      </UL>
 *  <LI><B>ORDINAL_POSITION</B> short => column sequence number
 *      within index; zero when TYPE is tableIndexStatistic
 *  <LI><B>COLUMN_NAME</B> String => column name; null when TYPE is
 *      tableIndexStatistic
 *  <LI><B>ASC_OR_DESC</B> String => column sort sequence, "A" => ascending,
 *      "D" => descending, may be null if sort sequence is not supported;
 *      null when TYPE is tableIndexStatistic
 *  <LI><B>CARDINALITY</B> int => When TYPE is tableIndexStatistic, then
 *      this is the number of rows in the table; otherwise, it is the
 *      number of unique values in the index.
 *  <LI><B>PAGES</B> int => When TYPE is  tableIndexStatisic then
 *      this is the number of pages used for the table, otherwise it
 *      is the number of pages used for the current index.
 *  <LI><B>FILTER_CONDITION</B> String => Filter condition, if any.
 *      (may be null)
 *  </OL>
 *
 * @param catalog a catalog name; "" retrieves those without a
 * catalog; null means drop catalog name from the selection criteria
 * @param schema a schema name; "" retrieves those without a schema
 * @param table a table name
 * @param unique when true, return only indices for unique values;
 *     when false, return indices regardless of whether unique or not
 * @param approximate when true, result is allowed to reflect approximate
 *     or out of data values; when false, results are requested to be
 *     accurate
 * @return <code>ResultSet</code> - each row is an index column description
 * @exception SQLException if a database access error occurs
 */
public ResultSet getIndexInfo(String catalog, String schema, String table, boolean unique, boolean approximate) throws SQLException {
    final RowDescriptor rowDescriptor = new RowDescriptorBuilder(13, datatypeCoder).at(0).simple(SQL_VARYING, OBJECT_NAME_LENGTH, "TABLE_CAT", "INDEXINFO").addField().at(1).simple(SQL_VARYING, OBJECT_NAME_LENGTH, "TABLE_SCHEM", "INDEXINFO").addField().at(2).simple(SQL_VARYING, OBJECT_NAME_LENGTH, "TABLE_NAME", "INDEXINFO").addField().at(3).simple(SQL_TEXT, 1, "NON_UNIQUE", "INDEXINFO").addField().at(4).simple(SQL_VARYING, OBJECT_NAME_LENGTH, "INDEX_QUALIFIER", "INDEXINFO").addField().at(5).simple(SQL_VARYING, OBJECT_NAME_LENGTH, "INDEX_NAME", "INDEXINFO").addField().at(6).simple(SQL_SHORT, 0, "TYPE", "INDEXINFO").addField().at(7).simple(SQL_SHORT, 0, "ORDINAL_POSITION", "INDEXINFO").addField().at(8).simple(SQL_VARYING, Integer.MAX_VALUE, "COLUMN_NAME", "INDEXINFO").addField().at(9).simple(SQL_VARYING, 31, "ASC_OR_DESC", "INDEXINFO").addField().at(10).simple(SQL_LONG, 0, "CARDINALITY", "INDEXINFO").addField().at(11).simple(SQL_LONG, 0, "PAGES", "INDEXINFO").addField().at(12).simple(SQL_VARYING, 31, "FILTER_CONDITION", "INDEXINFO").addField().toRowDescriptor();
    List<String> params = Collections.singletonList(table);
    try (ResultSet rs = doQuery(GET_INDEX_INFO, params)) {
        if (!rs.next()) {
            return new FBResultSet(rowDescriptor, Collections.<RowValue>emptyList());
        }
        final List<RowValue> rows = new ArrayList<>();
        final RowValueBuilder valueBuilder = new RowValueBuilder(rowDescriptor);
        do {
            final boolean isNotUnique = rs.getInt("UNIQUE_FLAG") == 0;
            if (unique && isNotUnique) {
                // Skip indices that are not unique, as requested
                continue;
            }
            valueBuilder.at(2).set(getBytes(rs.getString("TABLE_NAME"))).at(3).set(isNotUnique ? TRUE_BYTES : FALSE_BYTES).at(5).set(getBytes(rs.getString("INDEX_NAME"))).at(6).set(TABLE_INDEX_OTHER);
            String columnName = rs.getString("COLUMN_NAME");
            if (rs.wasNull()) {
                valueBuilder.at(7).set(SHORT_ONE);
                String expressionSource = rs.getString("EXPRESSION_SOURCE");
                if (expressionSource != null) {
                    valueBuilder.at(8).set(getBytes(expressionSource));
                }
            } else {
                valueBuilder.at(7).set(createShort(rs.getShort("ORDINAL_POSITION"))).at(8).set(getBytes(columnName));
            }
            int ascOrDesc = rs.getInt("ASC_OR_DESC");
            if (ascOrDesc == 0) {
                valueBuilder.at(9).set(ASC_BYTES);
            } else if (ascOrDesc == 1) {
                valueBuilder.at(9).set(DESC_BYTES);
            }
            // NOTE: We are setting CARDINALITY and PAGES to NULL as we don't have this info; might contravene JDBC spec
            // TODO index 10: use 1 / RDB$STATISTICS for approximation of CARDINALITY?
            // TODO index 11: query RDB$PAGES for PAGES information?
            rows.add(valueBuilder.toRowValue(true));
        } while (rs.next());
        return new FBResultSet(rowDescriptor, rows);
    }
}

Example 10 with RowDescriptor

use of org.firebirdsql.gds.ng.fields.RowDescriptor in project jaybird by FirebirdSQL.

the class FBDatabaseMetaData method getTableTypes.

/**
 * Gets the table types available in this database.  The results
 * are ordered by table type.
 *
 * <P>The table type is:
 *  <OL>
 *  <LI><B>TABLE_TYPE</B> String => table type.  Typical types are "TABLE",
 *          "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY",
 *          "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
 *  </OL>
 *
 * @return <code>ResultSet</code> - each row has a single String column that is a
 * table type
 * @exception SQLException if a database access error occurs
 */
public ResultSet getTableTypes() throws SQLException {
    final RowDescriptor rowDescriptor = new RowDescriptorBuilder(1, datatypeCoder).at(0).simple(SQL_VARYING, 31, "TABLE_TYPE", "TABLETYPES").addField().toRowDescriptor();
    final String[] types = hasGlobalTemporaryTables() ? ALL_TYPES_2_5 : ALL_TYPES_2_1;
    final List<RowValue> rows = new ArrayList<>(types.length);
    for (String type : types) {
        rows.add(RowValue.of(rowDescriptor, getBytes(type)));
    }
    return new FBResultSet(rowDescriptor, rows);
}