Examples with Table org.hsqldb_voltpatches.Table used on opensource projects

Example 56 with Table

use of org.hsqldb_voltpatches.Table in project voltdb by VoltDB.

the class DatabaseInformationMain method SYSTEM_CROSSREFERENCE.

/**
     * Retrieves a <code>Table</code> object describing, for each
     * accessible referencing and referenced table, how the referencing
     * tables import, for the purposes of referential integrity,
     * the columns of the referenced tables.<p>
     *
     * Each row is a foreign key column description with the following
     * columns: <p>
     *
     * <pre class="SqlCodeExample">
     * PKTABLE_CAT   VARCHAR   referenced table catalog
     * PKTABLE_SCHEM VARCHAR   referenced table schema
     * PKTABLE_NAME  VARCHAR   referenced table name
     * PKCOLUMN_NAME VARCHAR   referenced column name
     * FKTABLE_CAT   VARCHAR   referencing table catalog
     * FKTABLE_SCHEM VARCHAR   referencing table schema
     * FKTABLE_NAME  VARCHAR   referencing table name
     * FKCOLUMN_NAME VARCHAR   referencing column
     * KEY_SEQ       SMALLINT  sequence number within foreign key
     * UPDATE_RULE   SMALLINT
     *    { Cascade | Set Null | Set Default | Restrict (No Action)}?
     * DELETE_RULE   SMALLINT
     *    { Cascade | Set Null | Set Default | Restrict (No Action)}?
     * FK_NAME       VARCHAR   foreign key constraint name
     * PK_NAME       VARCHAR   primary key or unique constraint name
     * DEFERRABILITY SMALLINT
     *    { initially deferred | initially immediate | not deferrable }
     * </pre> <p>
     *
     * @return a <code>Table</code> object describing how accessible tables
     *      import other accessible tables' primary key and/or unique
     *      constraint columns
     */
final Table SYSTEM_CROSSREFERENCE() {
    Table t = sysTables[SYSTEM_CROSSREFERENCE];
    if (t == null) {
        t = createBlankTable(sysTableHsqlNames[SYSTEM_CROSSREFERENCE]);
        addColumn(t, "PKTABLE_CAT", SQL_IDENTIFIER);
        addColumn(t, "PKTABLE_SCHEM", SQL_IDENTIFIER);
        // not null
        addColumn(t, "PKTABLE_NAME", SQL_IDENTIFIER);
        // not null
        addColumn(t, "PKCOLUMN_NAME", SQL_IDENTIFIER);
        addColumn(t, "FKTABLE_CAT", SQL_IDENTIFIER);
        addColumn(t, "FKTABLE_SCHEM", SQL_IDENTIFIER);
        // not null
        addColumn(t, "FKTABLE_NAME", SQL_IDENTIFIER);
        // not null
        addColumn(t, "FKCOLUMN_NAME", SQL_IDENTIFIER);
        // not null
        addColumn(t, "KEY_SEQ", Type.SQL_SMALLINT);
        // not null
        addColumn(t, "UPDATE_RULE", Type.SQL_SMALLINT);
        // not null
        addColumn(t, "DELETE_RULE", Type.SQL_SMALLINT);
        addColumn(t, "FK_NAME", SQL_IDENTIFIER);
        addColumn(t, "PK_NAME", SQL_IDENTIFIER);
        // not null
        addColumn(t, "DEFERRABILITY", Type.SQL_SMALLINT);
        // order: FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, and KEY_SEQ
        // added for unique: FK_NAME
        // false PK, as FKTABLE_CAT, FKTABLE_SCHEM and/or FK_NAME
        // may be null
        HsqlName name = HsqlNameManager.newInfoSchemaObjectName(sysTableHsqlNames[SYSTEM_CROSSREFERENCE].name, false, SchemaObject.INDEX);
        t.createPrimaryKey(name, new int[] { 4, 5, 6, 8, 11 }, false);
        return t;
    }
    PersistentStore store = database.persistentStoreCollection.getStore(t);
    // calculated column values
    String pkTableCatalog;
    String pkTableSchema;
    String pkTableName;
    String pkColumnName;
    String fkTableCatalog;
    String fkTableSchema;
    String fkTableName;
    String fkColumnName;
    Integer keySequence;
    Integer updateRule;
    Integer deleteRule;
    String fkName;
    String pkName;
    Integer deferrability;
    // Intermediate holders
    Iterator tables;
    Table table;
    Table fkTable;
    Table pkTable;
    int columnCount;
    int[] mainCols;
    int[] refCols;
    Constraint[] constraints;
    Constraint constraint;
    int constraintCount;
    HsqlArrayList fkConstraintsList;
    Object[] row;
    DITableInfo pkInfo;
    DITableInfo fkInfo;
    // column number mappings
    final int ipk_table_cat = 0;
    final int ipk_table_schem = 1;
    final int ipk_table_name = 2;
    final int ipk_column_name = 3;
    final int ifk_table_cat = 4;
    final int ifk_table_schem = 5;
    final int ifk_table_name = 6;
    final int ifk_column_name = 7;
    final int ikey_seq = 8;
    final int iupdate_rule = 9;
    final int idelete_rule = 10;
    final int ifk_name = 11;
    final int ipk_name = 12;
    final int ideferrability = 13;
    tables = database.schemaManager.databaseObjectIterator(SchemaObject.TABLE);
    pkInfo = new DITableInfo();
    fkInfo = new DITableInfo();
    // We must consider all the constraints in all the user tables, since
    // this is where reference relationships are recorded.  However, we
    // are only concerned with Constraint.FOREIGN_KEY constraints here
    // because their corresponing Constraint.MAIN entries are essentially
    // duplicate data recorded in the referenced rather than the
    // referencing table.  Also, we skip constraints where either
    // the referenced, referencing or both tables are not accessible
    // relative to the session of the calling context
    fkConstraintsList = new HsqlArrayList();
    while (tables.hasNext()) {
        table = (Table) tables.next();
        if (table.isView() || !isAccessibleTable(table)) {
            continue;
        }
        constraints = table.getConstraints();
        constraintCount = constraints.length;
        for (int i = 0; i < constraintCount; i++) {
            constraint = (Constraint) constraints[i];
            if (constraint.getConstraintType() == Constraint.FOREIGN_KEY && isAccessibleTable(constraint.getRef())) {
                fkConstraintsList.add(constraint);
            }
        }
    }
    // Do it.
    for (int i = 0; i < fkConstraintsList.size(); i++) {
        constraint = (Constraint) fkConstraintsList.get(i);
        pkTable = constraint.getMain();
        pkInfo.setTable(pkTable);
        pkTableName = pkInfo.getName();
        fkTable = constraint.getRef();
        fkInfo.setTable(fkTable);
        fkTableName = fkInfo.getName();
        pkTableCatalog = pkTable.getCatalogName().name;
        pkTableSchema = pkTable.getSchemaName().name;
        fkTableCatalog = fkTable.getCatalogName().name;
        fkTableSchema = fkTable.getSchemaName().name;
        mainCols = constraint.getMainColumns();
        refCols = constraint.getRefColumns();
        columnCount = refCols.length;
        fkName = constraint.getRefName().name;
        pkName = constraint.getMainName().name;
        deferrability = ValuePool.getInt(constraint.getDeferability());
        //pkName = constraint.getMainIndex().getName().name;
        deleteRule = ValuePool.getInt(constraint.getDeleteAction());
        updateRule = ValuePool.getInt(constraint.getUpdateAction());
        for (int j = 0; j < columnCount; j++) {
            keySequence = ValuePool.getInt(j + 1);
            pkColumnName = pkInfo.getColName(mainCols[j]);
            fkColumnName = fkInfo.getColName(refCols[j]);
            row = t.getEmptyRowData();
            row[ipk_table_cat] = pkTableCatalog;
            row[ipk_table_schem] = pkTableSchema;
            row[ipk_table_name] = pkTableName;
            row[ipk_column_name] = pkColumnName;
            row[ifk_table_cat] = fkTableCatalog;
            row[ifk_table_schem] = fkTableSchema;
            row[ifk_table_name] = fkTableName;
            row[ifk_column_name] = fkColumnName;
            row[ikey_seq] = keySequence;
            row[iupdate_rule] = updateRule;
            row[idelete_rule] = deleteRule;
            row[ifk_name] = fkName;
            row[ipk_name] = pkName;
            row[ideferrability] = deferrability;
            t.insertSys(store, row);
        }
    }
    return t;
}

Example 57 with Table

use of org.hsqldb_voltpatches.Table in project voltdb by VoltDB.

the class DatabaseInformationMain method SYSTEM_TYPEINFO.

/**
     * Retrieves a <code>Table</code> object describing the
     * result expected by the JDBC DatabaseMetaData interface implementation
     * for system-defined SQL types supported as table columns.
     *
     * <pre class="SqlCodeExample">
     * TYPE_NAME          VARCHAR   the canonical name for DDL statements.
     * DATA_TYPE          SMALLINT  data type code from DITypes.
     * PRECISION          INTEGER   max column size.
     *                              number => max precision.
     *                              character => max characters.
     *                              datetime => max chars incl. frac. component.
     * LITERAL_PREFIX     VARCHAR   char(s) prefixing literal of this type.
     * LITERAL_SUFFIX     VARCHAR   char(s) terminating literal of this type.
     * CREATE_PARAMS      VARCHAR   Localized syntax-order list of domain
     *                              create parameter keywords.
     *                              - for human consumption only
     * NULLABLE           SMALLINT  {No Nulls | Nullable | Unknown}
     * CASE_SENSITIVE     BOOLEAN   case-sensitive in collations/comparisons?
     * SEARCHABLE         SMALLINT  {None | Char (Only WHERE .. LIKE) |
     *                               Basic (Except WHERE .. LIKE) |
     *                               Searchable (All forms)}
     * UNSIGNED_ATTRIBUTE BOOLEAN   {TRUE  (unsigned) | FALSE (signed) |
     *                               NULL (non-numeric or not applicable)}
     * FIXED_PREC_SCALE   BOOLEAN   {TRUE (fixed) | FALSE (variable) |
     *                               NULL (non-numeric or not applicable)}
     * AUTO_INCREMENT     BOOLEAN   automatic unique value generated for
     *                              inserts and updates when no value or
     *                              NULL specified?
     * LOCAL_TYPE_NAME    VARCHAR   localized name of data type;
     *                              - NULL if not supported.
     *                              - for human consuption only
     * MINIMUM_SCALE      SMALLINT  minimum scale supported.
     * MAXIMUM_SCALE      SMALLINT  maximum scale supported.
     * SQL_DATA_TYPE      INTEGER   value expected in SQL CLI SQL_DESC_TYPE
     *                              field of the SQLDA.
     * SQL_DATETIME_SUB   INTEGER   SQL CLI datetime/interval subcode.
     * NUM_PREC_RADIX     INTEGER   numeric base w.r.t # of digits reported in
     *                              PRECISION column (typically 10).
     * TYPE_SUB           INTEGER   From DITypes:
     *                              {TYPE_SUB_DEFAULT | TYPE_SUB_IDENTITY |
     *                               TYPE_SUB_IGNORECASE}
     * </pre> <p>
     *
     * @return a <code>Table</code> object describing the
     *      system-defined SQL types supported as table columns
     */
final Table SYSTEM_TYPEINFO() {
    Table t = sysTables[SYSTEM_TYPEINFO];
    if (t == null) {
        t = createBlankTable(sysTableHsqlNames[SYSTEM_TYPEINFO]);
        //-------------------------------------------
        // required by JDBC:
        // ------------------------------------------
        addColumn(t, "TYPE_NAME", SQL_IDENTIFIER);
        addColumn(t, "DATA_TYPE", Type.SQL_SMALLINT);
        addColumn(t, "PRECISION", Type.SQL_INTEGER);
        addColumn(t, "LITERAL_PREFIX", CHARACTER_DATA);
        addColumn(t, "LITERAL_SUFFIX", CHARACTER_DATA);
        addColumn(t, "CREATE_PARAMS", CHARACTER_DATA);
        addColumn(t, "NULLABLE", Type.SQL_SMALLINT);
        addColumn(t, "CASE_SENSITIVE", Type.SQL_BOOLEAN);
        addColumn(t, "SEARCHABLE", Type.SQL_INTEGER);
        addColumn(t, "UNSIGNED_ATTRIBUTE", Type.SQL_BOOLEAN);
        addColumn(t, "FIXED_PREC_SCALE", Type.SQL_BOOLEAN);
        addColumn(t, "AUTO_INCREMENT", Type.SQL_BOOLEAN);
        addColumn(t, "LOCAL_TYPE_NAME", SQL_IDENTIFIER);
        addColumn(t, "MINIMUM_SCALE", Type.SQL_SMALLINT);
        addColumn(t, "MAXIMUM_SCALE", Type.SQL_SMALLINT);
        addColumn(t, "SQL_DATA_TYPE", Type.SQL_INTEGER);
        addColumn(t, "SQL_DATETIME_SUB", Type.SQL_INTEGER);
        addColumn(t, "NUM_PREC_RADIX", Type.SQL_INTEGER);
        //-------------------------------------------
        // for JDBC sort contract:
        //-------------------------------------------
        addColumn(t, "TYPE_SUB", Type.SQL_INTEGER);
        // order: DATA_TYPE, TYPE_SUB
        // true PK
        HsqlName name = HsqlNameManager.newInfoSchemaObjectName(sysTableHsqlNames[SYSTEM_TYPEINFO].name, false, SchemaObject.INDEX);
        t.createPrimaryKey(name, new int[] { 1, 18 }, true);
        return t;
    }
    PersistentStore store = database.persistentStoreCollection.getStore(t);
    Session sys = database.sessionManager.newSysSession(SqlInvariants.INFORMATION_SCHEMA_HSQLNAME, session.getUser());
    Result rs = sys.executeDirectStatement("select TYPE_NAME, DATA_TYPE, PRECISION, LITERAL_PREFIX," + "LITERAL_SUFFIX, CREATE_PARAMS, NULLABLE, CASE_SENSITIVE," + "SEARCHABLE," + "UNSIGNED_ATTRIBUTE, FIXED_PREC_SCALE, AUTO_INCREMENT, LOCAL_TYPE_NAME, MINIMUM_SCALE, " + "MAXIMUM_SCALE, SQL_DATA_TYPE, SQL_DATETIME_SUB, NUM_PREC_RADIX, TYPE_SUB " + "from INFORMATION_SCHEMA.SYSTEM_ALLTYPEINFO  where AS_TAB_COL = true;");
    t.insertSys(store, rs);
    sys.close();
    return t;
}

Example 58 with Table

use of org.hsqldb_voltpatches.Table in project voltdb by VoltDB.

the class DatabaseInformationFull method ROUTINE_SEQUENCE_USAGE.

Table ROUTINE_SEQUENCE_USAGE() {
    Table t = sysTables[ROUTINE_SEQUENCE_USAGE];
    if (t == null) {
        t = createBlankTable(sysTableHsqlNames[ROUTINE_SEQUENCE_USAGE]);
        addColumn(t, "SPECIFIC_CATALOG", SQL_IDENTIFIER);
        addColumn(t, "SPECIFIC_SCHEMA", SQL_IDENTIFIER);
        addColumn(t, "SPECIFIC_NAME", SQL_IDENTIFIER);
        addColumn(t, "SEQUENCE_CATALOG", SQL_IDENTIFIER);
        addColumn(t, "SEQUENCE_SCHEMA", SQL_IDENTIFIER);
        addColumn(t, "SEQUENCE_NAME", SQL_IDENTIFIER);
        HsqlName name = HsqlNameManager.newInfoSchemaObjectName(sysTableHsqlNames[ROUTINE_SEQUENCE_USAGE].name, false, SchemaObject.INDEX);
        t.createPrimaryKey(name, new int[] { 0, 1, 2, 3, 4, 5 }, false);
        return t;
    }
    // column number mappings
    final int specific_catalog = 0;
    final int specific_schema = 1;
    final int specific_name = 2;
    final int sequence_catalog = 3;
    final int sequence_schema = 4;
    final int sequence_name = 5;
    //
    PersistentStore store = database.persistentStoreCollection.getStore(t);
    Iterator it;
    Object[] row;
    it = database.schemaManager.databaseObjectIterator(SchemaObject.ROUTINE);
    while (it.hasNext()) {
        RoutineSchema routine = (RoutineSchema) it.next();
        if (!session.getGrantee().isAccessible(routine)) {
            continue;
        }
        Routine[] specifics = routine.getSpecificRoutines();
        for (int m = 0; m < specifics.length; m++) {
            OrderedHashSet set = specifics[m].getReferences();
            for (int i = 0; i < set.size(); i++) {
                HsqlName refName = (HsqlName) set.get(i);
                if (refName.type != SchemaObject.SEQUENCE) {
                    continue;
                }
                if (!session.getGrantee().isAccessible(refName)) {
                    continue;
                }
                row = t.getEmptyRowData();
                row[specific_catalog] = database.getCatalogName().name;
                row[specific_schema] = specifics[m].getSchemaName().name;
                row[specific_name] = specifics[m].getName().name;
                row[sequence_catalog] = database.getCatalogName().name;
                row[sequence_schema] = refName.schema.name;
                row[sequence_name] = refName.name;
                try {
                    t.insertSys(store, row);
                } catch (HsqlException e) {
                }
            }
        }
    }
    return t;
}

Example 59 with Table

use of org.hsqldb_voltpatches.Table in project voltdb by VoltDB.

the class DatabaseInformationFull method ROLE_COLUMN_GRANTS.

Table ROLE_COLUMN_GRANTS() {
    Table t = sysTables[ROLE_COLUMN_GRANTS];
    if (t == null) {
        t = createBlankTable(sysTableHsqlNames[ROLE_COLUMN_GRANTS]);
        // not null
        addColumn(t, "GRANTOR", SQL_IDENTIFIER);
        // not null
        addColumn(t, "GRANTEE", SQL_IDENTIFIER);
        addColumn(t, "TABLE_CATALOG", SQL_IDENTIFIER);
        addColumn(t, "TABLE_SCHEMA", SQL_IDENTIFIER);
        // not null
        addColumn(t, "TABLE_NAME", SQL_IDENTIFIER);
        // not null
        addColumn(t, "COLUMN_NAME", SQL_IDENTIFIER);
        // not null
        addColumn(t, "PRIVILEGE_TYPE", CHARACTER_DATA);
        // not null
        addColumn(t, "IS_GRANTABLE", YES_OR_NO);
        // order: COLUMN_NAME, PRIVILEGE
        // for unique: GRANTEE, GRANTOR, TABLE_NAME, TABLE_SCHEMA, TABLE_CAT
        // false PK, as TABLE_SCHEMA and/or TABLE_CAT may be null
        HsqlName name = HsqlNameManager.newInfoSchemaObjectName(sysTableHsqlNames[ROLE_COLUMN_GRANTS].name, false, SchemaObject.INDEX);
        t.createPrimaryKey(name, new int[] { 5, 6, 1, 0, 4, 3, 2 }, false);
        return t;
    }
    PersistentStore store = database.persistentStoreCollection.getStore(t);
    Session sys = database.sessionManager.newSysSession(SqlInvariants.INFORMATION_SCHEMA_HSQLNAME, session.getUser());
    Result rs = sys.executeDirectStatement("SELECT GRANTOR, GRANTEE, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE, IS_GRANTABLE " + "FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES " + "JOIN INFORMATION_SCHEMA.APPLICABLE_ROLES ON GRANTEE = ROLE_NAME;");
    t.insertSys(store, rs);
    sys.close();
    return t;
}

Example 60 with Table

use of org.hsqldb_voltpatches.Table in project voltdb by VoltDB.

the class DatabaseInformationFull method SQL_PACKAGES.

Table SQL_PACKAGES() {
    Table t = sysTables[SQL_PACKAGES];
    if (t == null) {
        t = createBlankTable(sysTableHsqlNames[SQL_PACKAGES]);
        addColumn(t, "ID", CHARACTER_DATA);
        addColumn(t, "NAME", CHARACTER_DATA);
        addColumn(t, "IS_SUPPORTED", YES_OR_NO);
        addColumn(t, "IS_VERIFIED_BY", CHARACTER_DATA);
        addColumn(t, "COMMENTS", CHARACTER_DATA);
        HsqlName name = HsqlNameManager.newInfoSchemaObjectName(sysTableHsqlNames[SQL_PACKAGES].name, false, SchemaObject.INDEX);
        t.createPrimaryKey(name, new int[] { 0 }, false);
        return t;
    }
    PersistentStore store = database.persistentStoreCollection.getStore(t);
    Session sys = database.sessionManager.newSysSession(SqlInvariants.INFORMATION_SCHEMA_HSQLNAME, session.getUser());
    String sql = (String) statementMap.get("/*sql_packages*/");
    Result rs = sys.executeDirectStatement(sql);
    t.insertSys(store, rs);
    return t;
}