Examples with HsqlProperties org.hsqldb_voltpatches.persist.HsqlProperties used on opensource projects

Example 6 with HsqlProperties

use of org.hsqldb_voltpatches.persist.HsqlProperties in project voltdb by VoltDB.

the class DatabaseInformationMain method SYSTEM_BESTROWIDENTIFIER.

/**
     * Retrieves a <code>Table</code> object describing the optimal
     * set of visible columns that uniquely identifies a row
     * for each accessible table defined within this database. <p>
     *
     * Each row describes a single column of the best row indentifier column
     * set for a particular table.  Each row has the following
     * columns: <p>
     *
     * <pre class="SqlCodeExample">
     * SCOPE          SMALLINT  scope of applicability
     * COLUMN_NAME    VARCHAR   simple name of the column
     * DATA_TYPE      SMALLINT  SQL data type from Types
     * TYPE_NAME      VARCHAR   canonical type name
     * COLUMN_SIZE    INTEGER   precision
     * BUFFER_LENGTH  INTEGER   transfer size in bytes, if definitely known
     * DECIMAL_DIGITS SMALLINT  scale  - fixed # of decimal digits
     * PSEUDO_COLUMN  SMALLINT  is this a pseudo column like an Oracle ROWID?
     * TABLE_CAT      VARCHAR   table catalog
     * TABLE_SCHEM    VARCHAR   simple name of table schema
     * TABLE_NAME     VARCHAR   simple table name
     * NULLABLE       SMALLINT  is column nullable?
     * IN_KEY         BOOLEAN   column belongs to a primary or alternate key?
     * </pre> <p>
     *
     * <b>Notes:</b><p>
     *
     * <code>JDBCDatabaseMetaData.getBestRowIdentifier</code> uses its
     * nullable parameter to filter the rows of this table in the following
     * manner: <p>
     *
     * If the nullable parameter is <code>false</code>, then rows are reported
     * only if, in addition to satisfying the other specified filter values,
     * the IN_KEY column value is TRUE. If the nullable parameter is
     * <code>true</code>, then the IN_KEY column value is ignored. <p>
     *
     * There is not yet infrastructure in place to make some of the ranking
     * descisions described below, and it is anticipated that mechanisms
     * upon which cost descisions could be based will change significantly over
     * the next few releases.  Hence, in the interest of simplicity and of not
     * making overly complex dependency on features that will almost certainly
     * change significantly in the near future, the current implementation,
     * while perfectly adequate for all but the most demanding or exacting
     * purposes, is actually sub-optimal in the strictest sense. <p>
     *
     * A description of the current implementation follows: <p>
     *
     * <b>DEFINTIONS:</b>  <p>
     *
     * <b>Alternate key</b> <p>
     *
     *  <UL>
     *   <LI> An attribute of a table that, by virtue of its having a set of
     *        columns that are both the full set of columns participating in a
     *        unique constraint or index and are all not null, yeilds the same
     *        selectability characteristic that would obtained by declaring a
     *        primary key on those same columns.
     *  </UL> <p>
     *
     * <b>Column set performance ranking</b> <p>
     *
     *  <UL>
     *  <LI> The ranking of the expected average performance w.r.t a subset of
     *       a table's columns used to select and/or compare rows, as taken in
     *       relation to all other distinct candidate subsets under
     *       consideration. This can be estimated by comparing each cadidate
     *       subset in terms of total column count, relative peformance of
     *       comparisons amongst the domains of the columns and differences
     *       in other costs involved in the execution plans generated using
     *       each subset under consideration for row selection/comparison.
     *  </UL> <p>
     *
     *
     * <b>Rules:</b> <p>
     *
     * Given the above definitions, the rules currently in effect for reporting
     * best row identifier are as follows, in order of precedence: <p>
     *
     * <OL>
     * <LI> if the table under consideration has a primary key contraint, then
     *      the columns of the primary key are reported, with no consideration
     *      given to the column set performance ranking over the set of
     *      candidate keys. Each row has its IN_KEY column set to TRUE.
     *
     * <LI> if 1.) does not hold, then if there exits one or more alternate
     *      keys, then the columns of the alternate key with the lowest column
     *      count are reported, with no consideration given to the column set
     *      performance ranking over the set of candidate keys. If there
     *      exists a tie for lowest column count, then the columns of the
     *      first such key encountered are reported.
     *      Each row has its IN_KEY column set to TRUE.
     *
     * <LI> if both 1.) and 2.) do not hold, then, if possible, a unique
     *      contraint/index is selected from the set of unique
     *      contraints/indices containing at least one column having
     *      a not null constraint, with no consideration given to the
     *      column set performance ranking over the set of all such
     *      candidate column sets. If there exists a tie for lowest non-zero
     *      count of columns having a not null constraint, then the columns
     *      of the first such encountered candidate set are reported. Each
     *      row has its IN_KEY column set to FALSE. <p>
     *
     * <LI> Finally, if the set of candidate column sets in 3.) is the empty,
     *      then no column set is reported for the table under consideration.
     * </OL> <p>
     *
     * The scope reported for a best row identifier column set is determined
     * thus: <p>
     *
     * <OL>
     * <LI> if the database containing the table under consideration is in
     *      read-only mode or the table under consideration is GLOBAL TEMPORARY
     *      (a TEMP or TEMP TEXT table, in HSQLDB parlance), then the scope
     *      is reported as
     *      <code>java.sql.DatabaseMetaData.bestRowSession</code>.
     *
     * <LI> if 1.) does not hold, then the scope is reported as
     *      <code>java.sql.DatabaseMetaData.bestRowTemporary</code>.
     * </OL> <p>
     *
     * @return a <code>Table</code> object describing the optimal
     * set of visible columns that uniquely identifies a row
     * for each accessible table defined within this database
     */
final Table SYSTEM_BESTROWIDENTIFIER() {
    Table t = sysTables[SYSTEM_BESTROWIDENTIFIER];
    if (t == null) {
        t = createBlankTable(sysTableHsqlNames[SYSTEM_BESTROWIDENTIFIER]);
        // not null
        addColumn(t, "SCOPE", Type.SQL_SMALLINT);
        // not null
        addColumn(t, "COLUMN_NAME", SQL_IDENTIFIER);
        // not null
        addColumn(t, "DATA_TYPE", Type.SQL_SMALLINT);
        // not null
        addColumn(t, "TYPE_NAME", SQL_IDENTIFIER);
        addColumn(t, "COLUMN_SIZE", Type.SQL_INTEGER);
        addColumn(t, "BUFFER_LENGTH", Type.SQL_INTEGER);
        addColumn(t, "DECIMAL_DIGITS", Type.SQL_SMALLINT);
        // not null
        addColumn(t, "PSEUDO_COLUMN", Type.SQL_SMALLINT);
        addColumn(t, "TABLE_CAT", SQL_IDENTIFIER);
        addColumn(t, "TABLE_SCHEM", SQL_IDENTIFIER);
        // not null
        addColumn(t, "TABLE_NAME", SQL_IDENTIFIER);
        // not null
        addColumn(t, "NULLABLE", Type.SQL_SMALLINT);
        // not null
        addColumn(t, "IN_KEY", Type.SQL_BOOLEAN);
        // order: SCOPE
        // for unique:  TABLE_CAT, TABLE_SCHEM, TABLE_NAME, COLUMN_NAME
        // false PK, as TABLE_CAT and/or TABLE_SCHEM may be null
        HsqlName name = HsqlNameManager.newInfoSchemaObjectName(sysTableHsqlNames[SYSTEM_BESTROWIDENTIFIER].name, false, SchemaObject.INDEX);
        t.createPrimaryKey(name, new int[] { 0, 8, 9, 10, 1 }, false);
        return t;
    }
    PersistentStore store = database.persistentStoreCollection.getStore(t);
    // calculated column values
    // { temp, transaction, session }
    Integer scope;
    Integer pseudo;
    //-------------------------------------------
    // required for restriction of results via
    // DatabaseMetaData filter parameters, but
    // not actually required to be included in
    // DatabaseMetaData.getBestRowIdentifier()
    // result set
    //-------------------------------------------
    // table calalog
    String tableCatalog;
    // table schema
    String tableSchema;
    // table name
    String tableName;
    // column participates in PK or AK?
    Boolean inKey;
    //-------------------------------------------
    /**
         * @todo -  Maybe include: - backing index (constraint) name?
         *       - column sequence in index (constraint)?
         */
    //-------------------------------------------
    // Intermediate holders
    Iterator tables;
    Table table;
    DITableInfo ti;
    int[] cols;
    Object[] row;
    HsqlProperties p;
    // Column number mappings
    final int iscope = 0;
    final int icolumn_name = 1;
    final int idata_type = 2;
    final int itype_name = 3;
    final int icolumn_size = 4;
    final int ibuffer_length = 5;
    final int idecimal_digits = 6;
    final int ipseudo_column = 7;
    final int itable_cat = 8;
    final int itable_schem = 9;
    final int itable_name = 10;
    final int inullable = 11;
    final int iinKey = 12;
    // Initialization
    ti = new DITableInfo();
    tables = database.schemaManager.databaseObjectIterator(SchemaObject.TABLE);
    // Do it.
    while (tables.hasNext()) {
        table = (Table) tables.next();
        /** @todo - requires access to the actual columns */
        if (table.isView() || !isAccessibleTable(table)) {
            continue;
        }
        cols = table.getBestRowIdentifiers();
        if (cols == null) {
            continue;
        }
        ti.setTable(table);
        inKey = ValuePool.getBoolean(table.isBestRowIdentifiersStrict());
        tableCatalog = table.getCatalogName().name;
        tableSchema = table.getSchemaName().name;
        tableName = table.getName().name;
        Type[] types = table.getColumnTypes();
        scope = ti.getBRIScope();
        pseudo = ti.getBRIPseudo();
        for (int i = 0; i < cols.length; i++) {
            ColumnSchema column = table.getColumn(i);
            row = t.getEmptyRowData();
            row[iscope] = scope;
            row[icolumn_name] = column.getName().name;
            row[idata_type] = ValuePool.getInt(types[i].getJDBCTypeCode());
            row[itype_name] = types[i].getNameString();
            row[icolumn_size] = types[i].getJDBCPrecision();
            row[ibuffer_length] = null;
            row[idecimal_digits] = types[i].getJDBCScale();
            row[ipseudo_column] = pseudo;
            row[itable_cat] = tableCatalog;
            row[itable_schem] = tableSchema;
            row[itable_name] = tableName;
            row[inullable] = column.getNullability();
            row[iinKey] = inKey;
            t.insertSys(store, row);
        }
    }
    return t;
}