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

Example 86 with Table

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

the class DatabaseInformationFull method APPLICABLE_ROLES.

/**
     * APPLICABLE_ROLES<p>
     *
     * <b>Function</b><p>
     *
     * Identifies the applicable roles for the current user.<p>
     *
     * <b>Definition</b><p>
     *
     * <pre class="SqlCodeExample">
     * CREATE RECURSIVE VIEW APPLICABLE_ROLES ( GRANTEE, ROLE_NAME, IS_GRANTABLE ) AS
     *      ( ( SELECT GRANTEE, ROLE_NAME, IS_GRANTABLE
     *            FROM DEFINITION_SCHEMA.ROLE_AUTHORIZATION_DESCRIPTORS
     *           WHERE ( GRANTEE IN ( CURRENT_USER, 'PUBLIC' )
     *                OR GRANTEE IN ( SELECT ROLE_NAME
     *                                  FROM ENABLED_ROLES ) ) )
     *      UNION
     *      ( SELECT RAD.GRANTEE, RAD.ROLE_NAME, RAD.IS_GRANTABLE
     *          FROM DEFINITION_SCHEMA.ROLE_AUTHORIZATION_DESCRIPTORS RAD
     *          JOIN APPLICABLE_ROLES R
     *            ON RAD.GRANTEE = R.ROLE_NAME ) );
     *
     * GRANT SELECT ON TABLE APPLICABLE_ROLES
     *    TO PUBLIC WITH GRANT OPTION;
     * </pre>
     */
Table APPLICABLE_ROLES() {
    Table t = sysTables[APPLICABLE_ROLES];
    if (t == null) {
        t = createBlankTable(sysTableHsqlNames[APPLICABLE_ROLES]);
        addColumn(t, "GRANTEE", SQL_IDENTIFIER);
        addColumn(t, "ROLE_NAME", SQL_IDENTIFIER);
        addColumn(t, "IS_GRANTABLE", SQL_IDENTIFIER);
        HsqlName name = HsqlNameManager.newInfoSchemaObjectName(sysTableHsqlNames[APPLICABLE_ROLES].name, false, SchemaObject.INDEX);
        t.createPrimaryKey(name, new int[] { 0, 1, 2 }, false);
        return t;
    }
    insertRoles(t, session.getGrantee(), session.isAdmin());
    return t;
}

Example 87 with Table

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

the class DatabaseInformationFull method USER_DEFINED_TYPES.

Table USER_DEFINED_TYPES() {
    Table t = sysTables[USER_DEFINED_TYPES];
    if (t == null) {
        t = createBlankTable(sysTableHsqlNames[USER_DEFINED_TYPES]);
        addColumn(t, "USER_DEFINED_TYPE_CATALOG", SQL_IDENTIFIER);
        addColumn(t, "USER_DEFINED_TYPE_SCHEMA", SQL_IDENTIFIER);
        addColumn(t, "USER_DEFINED_TYPE_NAME", SQL_IDENTIFIER);
        addColumn(t, "USER_DEFINED_TYPE_CATEGORY", SQL_IDENTIFIER);
        addColumn(t, "IS_INSTANTIABLE", YES_OR_NO);
        addColumn(t, "IS_FINAL", YES_OR_NO);
        addColumn(t, "ORDERING_FORM", SQL_IDENTIFIER);
        addColumn(t, "ORDERING_CATEGORY", SQL_IDENTIFIER);
        addColumn(t, "ORDERING_ROUTINE_CATALOG", SQL_IDENTIFIER);
        addColumn(t, "ORDERING_ROUTINE_SCHEMA", SQL_IDENTIFIER);
        addColumn(t, "ORDERING_ROUTINE_NAME", SQL_IDENTIFIER);
        addColumn(t, "REFERENCE_TYPE", SQL_IDENTIFIER);
        addColumn(t, "DATA_TYPE", CHARACTER_DATA);
        addColumn(t, "CHARACTER_MAXIMUM_LENGTH", CARDINAL_NUMBER);
        addColumn(t, "CHARACTER_OCTET_LENGTH", CARDINAL_NUMBER);
        addColumn(t, "CHARACTER_SET_CATALOG", SQL_IDENTIFIER);
        addColumn(t, "CHARACTER_SET_SCHEMA", SQL_IDENTIFIER);
        addColumn(t, "CHARACTER_SET_NAME", SQL_IDENTIFIER);
        addColumn(t, "COLLATION_CATALOG", SQL_IDENTIFIER);
        addColumn(t, "COLLATION_SCHEMA", SQL_IDENTIFIER);
        addColumn(t, "COLLATION_NAME", SQL_IDENTIFIER);
        addColumn(t, "NUMERIC_PRECISION", CARDINAL_NUMBER);
        addColumn(t, "NUMERIC_PRECISION_RADIX", CARDINAL_NUMBER);
        addColumn(t, "NUMERIC_SCALE", CARDINAL_NUMBER);
        addColumn(t, "DATETIME_PRECISION", CARDINAL_NUMBER);
        addColumn(t, "INTERVAL_TYPE", CHARACTER_DATA);
        addColumn(t, "INTERVAL_PRECISION", CARDINAL_NUMBER);
        addColumn(t, "SOURCE_DTD_IDENTIFIER", CHARACTER_DATA);
        addColumn(t, "REF_DTD_IDENTIFIER", CHARACTER_DATA);
        addColumn(t, "DECLARED_DATA_TYPE", CHARACTER_DATA);
        addColumn(t, "DECLARED_NUMERIC_PRECISION", CARDINAL_NUMBER);
        addColumn(t, "DECLARED_NUMERIC_SCALE", CARDINAL_NUMBER);
        addColumn(t, "EXTERNAL_NAME", CHARACTER_DATA);
        addColumn(t, "EXTERNAL_LANGUAGE", CHARACTER_DATA);
        addColumn(t, "JAVA_INTERFACE", CHARACTER_DATA);
        HsqlName name = HsqlNameManager.newInfoSchemaObjectName(sysTableHsqlNames[USER_DEFINED_TYPES].name, false, SchemaObject.INDEX);
        t.createPrimaryKey(name, new int[] { 0, 1, 2, 4, 5, 6 }, false);
        return t;
    }
    PersistentStore store = database.persistentStoreCollection.getStore(t);
    final int user_defined_type_catalog = 0;
    final int user_defined_type_schema = 1;
    final int user_defined_type_name = 2;
    final int user_defined_type_category = 3;
    final int is_instantiable = 4;
    final int is_final = 5;
    final int ordering_form = 6;
    final int ordering_category = 7;
    final int ordering_routine_catalog = 8;
    final int ordering_routine_schema = 9;
    final int ordering_routine_name = 10;
    final int reference_type = 11;
    final int data_type = 12;
    final int character_maximum_length = 13;
    final int character_octet_length = 14;
    final int character_set_catalog = 15;
    final int character_set_schema = 16;
    final int character_set_name = 17;
    final int collation_catalog = 18;
    final int collation_schema = 19;
    final int collation_name = 20;
    final int numeric_precision = 21;
    final int numeric_precision_radix = 22;
    final int numeric_scale = 23;
    final int datetime_precision = 24;
    final int interval_type = 25;
    final int interval_precision = 26;
    final int source_dtd_identifier = 27;
    final int ref_dtd_identifier = 28;
    final int declared_data_type = 29;
    final int declared_numeric_precision = 30;
    final int declared_numeric_scale = 31;
    Iterator it = database.schemaManager.databaseObjectIterator(SchemaObject.DOMAIN);
    while (it.hasNext()) {
        Type distinct = (Type) it.next();
        if (!distinct.isDistinctType()) {
            continue;
        }
        Object[] data = t.getEmptyRowData();
        data[user_defined_type_catalog] = database.getCatalogName().name;
        data[user_defined_type_schema] = distinct.getSchemaName().name;
        data[user_defined_type_name] = distinct.getName().name;
        data[data_type] = distinct.getFullNameString();
        data[declared_data_type] = distinct.getFullNameString();
        data[user_defined_type_category] = "DISTINCT";
        data[is_instantiable] = "YES";
        data[is_final] = "YES";
        data[ordering_form] = "FULL";
        data[source_dtd_identifier] = distinct.getFullNameString();
        if (distinct.isCharacterType()) {
            data[character_maximum_length] = ValuePool.getLong(distinct.precision);
            data[character_octet_length] = ValuePool.getLong(distinct.precision * 2);
            data[character_set_catalog] = database.getCatalogName().name;
            data[character_set_schema] = ((CharacterType) distinct).getCharacterSet().getSchemaName().name;
            data[character_set_name] = ((CharacterType) distinct).getCharacterSet().getName().name;
            data[collation_catalog] = database.getCatalogName().name;
            data[collation_schema] = ((CharacterType) distinct).getCollation().getSchemaName().name;
            data[collation_name] = ((CharacterType) distinct).getCollation().getName().name;
        } else if (distinct.isNumberType()) {
            data[numeric_precision] = ValuePool.getLong(((NumberType) distinct).getPrecision());
            data[declared_numeric_precision] = ValuePool.getLong(((NumberType) distinct).getPrecision());
            if (distinct.typeCode != Types.SQL_DOUBLE) {
                data[numeric_scale] = ValuePool.getLong(distinct.scale);
                data[declared_numeric_scale] = ValuePool.getLong(distinct.scale);
            }
            data[numeric_precision_radix] = ValuePool.getLong(((NumberType) distinct).getPrecisionRadix());
        } else if (distinct.isBooleanType()) {
        } else if (distinct.isDateTimeType()) {
            data[datetime_precision] = ValuePool.getLong(distinct.scale);
        } else if (distinct.isIntervalType()) {
            data[interval_precision] = ValuePool.getLong(distinct.precision);
            data[interval_type] = distinct.getFullNameString();
            data[datetime_precision] = ValuePool.getLong(distinct.scale);
        } else if (distinct.isBinaryType()) {
            data[character_maximum_length] = ValuePool.getLong(distinct.precision);
            data[character_octet_length] = ValuePool.getLong(distinct.precision);
        } else if (distinct.isBitType()) {
            data[character_maximum_length] = ValuePool.getLong(distinct.precision);
            data[character_octet_length] = ValuePool.getLong(distinct.precision);
        }
    }
    return t;
}

Example 88 with Table

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

the class DatabaseInformationFull method CONSTRAINT_TABLE_USAGE.

/**
     * The CONSTRAINT_TABLE_USAGE view has one row for each table identified by a
     * <table name> simply contained in a <table reference>
     * contained in the <search condition> of a check constraint,
     * domain constraint, or assertion. It has one row for each table
     * containing / referenced by each PRIMARY KEY, UNIQUE and FOREIGN KEY
     * constraint<p>
     *
     * <b>Definition:</b> <p>
     *
     * <pre class="SqlCodeExample">
     *      CONSTRAINT_CATALOG      VARCHAR
     *      CONSTRAINT_SCHEMA       VARCHAR
     *      CONSTRAINT_NAME         VARCHAR
     *      TABLE_CATALOG           VARCHAR
     *      TABLE_SCHEMA            VARCHAR
     *      TABLE_NAME              VARCHAR
     * </pre>
     *
     * <b>Description:</b> <p>
     *
     * <ol>
     * <li> The values of CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, and
     *      CONSTRAINT_NAME are the catalog name, unqualified schema name,
     *       and qualified identifier, respectively, of the constraint being
     *      described. <p>
     *
     * <li> The values of TABLE_CATALOG, TABLE_SCHEMA, and TABLE_NAME are the
     *      catalog name, unqualified schema name, and qualified identifier,
     *      respectively, of a table identified by a &lt;table name&gt;
     *      simply contained in a &lt;table reference&gt; contained in the
     *      *lt;search condition&gt; of the constraint being described, or
     *      its columns.
     * </ol>
     *
     * @return Table
     */
Table CONSTRAINT_TABLE_USAGE() {
    Table t = sysTables[CONSTRAINT_TABLE_USAGE];
    if (t == null) {
        t = createBlankTable(sysTableHsqlNames[CONSTRAINT_TABLE_USAGE]);
        addColumn(t, "CONSTRAINT_CATALOG", SQL_IDENTIFIER);
        addColumn(t, "CONSTRAINT_SCHEMA", SQL_IDENTIFIER);
        // not null
        addColumn(t, "CONSTRAINT_NAME", SQL_IDENTIFIER);
        addColumn(t, "TABLE_CATALOG", SQL_IDENTIFIER);
        addColumn(t, "TABLE_SCHEMA", SQL_IDENTIFIER);
        // not null
        addColumn(t, "TABLE_NAME", SQL_IDENTIFIER);
        HsqlName name = HsqlNameManager.newInfoSchemaObjectName(sysTableHsqlNames[CONSTRAINT_TABLE_USAGE].name, false, SchemaObject.INDEX);
        t.createPrimaryKey(name, new int[] { 0, 1, 2, 3, 4, 5 }, 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 DISTINCT CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, " + "CONSTRAINT_NAME, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME " + "from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE");
    t.insertSys(store, rs);
    sys.close();
    return t;
}

Example 89 with Table

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

the class DatabaseInformationFull method SYSTEM_SESSIONINFO.

/**
     * Retrieves a <code>Table</code> object describing attributes
     * for the calling session context.<p>
     *
     * The rows report the following {key,value} pairs:<p>
     *
     * <pre class="SqlCodeExample">
     * KEY (VARCHAR)       VALUE (VARCHAR)
     * ------------------- ---------------
     * SESSION_ID          the id of the calling session
     * AUTOCOMMIT          YES: session is in autocommit mode, else NO
     * USER                the name of user connected in the calling session
     * (was READ_ONLY)
     * SESSION_READONLY    TRUE: session is in read-only mode, else FALSE
     * (new)
     * DATABASE_READONLY   TRUE: database is in read-only mode, else FALSE
     * MAXROWS             the MAXROWS setting in the calling session
     * DATABASE            the name of the database
     * IDENTITY            the last identity value used by calling session
     * </pre>
     *
     * <b>Note:</b>  This table <em>may</em> become deprecated in a future
     * release, as the information it reports now duplicates information
     * reported in the newer SYSTEM_SESSIONS and SYSTEM_PROPERTIES
     * tables. <p>
     *
     * @return a <code>Table</code> object describing the
     *        attributes of the connection associated
     *        with the current execution context
     */
Table SYSTEM_SESSIONINFO() {
    Table t = sysTables[SYSTEM_SESSIONINFO];
    if (t == null) {
        t = createBlankTable(sysTableHsqlNames[SYSTEM_SESSIONINFO]);
        // not null
        addColumn(t, "KEY", CHARACTER_DATA);
        // not null
        addColumn(t, "VALUE", CHARACTER_DATA);
        HsqlName name = HsqlNameManager.newInfoSchemaObjectName(sysTableHsqlNames[SYSTEM_SESSIONINFO].name, false, SchemaObject.INDEX);
        t.createPrimaryKey(name, new int[] { 0 }, true);
        return t;
    }
    PersistentStore store = database.persistentStoreCollection.getStore(t);
    Object[] row;
    row = t.getEmptyRowData();
    row[0] = "SESSION_ID";
    row[1] = String.valueOf(session.getId());
    t.insertSys(store, row);
    row = t.getEmptyRowData();
    row[0] = "AUTOCOMMIT";
    row[1] = session.isAutoCommit() ? "TRUE" : "FALSE";
    t.insertSys(store, row);
    row = t.getEmptyRowData();
    row[0] = "USER";
    row[1] = session.getUsername();
    t.insertSys(store, row);
    row = t.getEmptyRowData();
    row[0] = "SESSION_READONLY";
    row[1] = session.isReadOnlyDefault() ? "TRUE" : "FALSE";
    t.insertSys(store, row);
    row = t.getEmptyRowData();
    row[0] = "DATABASE_READONLY";
    row[1] = database.isReadOnly() ? "TRUE" : "FALSE";
    t.insertSys(store, row);
    // fredt - value set by SET MAXROWS in SQL, not Statement.setMaxRows()
    row = t.getEmptyRowData();
    row[0] = "MAXROWS";
    row[1] = String.valueOf(session.getSQLMaxRows());
    t.insertSys(store, row);
    row = t.getEmptyRowData();
    row[0] = "DATABASE";
    row[1] = database.getURI();
    t.insertSys(store, row);
    row = t.getEmptyRowData();
    row[0] = "IDENTITY";
    row[1] = String.valueOf(session.getLastIdentity());
    t.insertSys(store, row);
    row = t.getEmptyRowData();
    row[0] = "SCHEMA";
    row[1] = String.valueOf(session.getSchemaName(null));
    t.insertSys(store, row);
    return t;
}

Example 90 with Table

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

the class DatabaseInformationFull method VIEWS.

/**
     * The VIEWS view contains one row for each VIEW definition. <p>
     *
     * Each row is a description of the query expression that defines its view,
     * with the following columns:
     *
     * <pre class="SqlCodeExample">
     * TABLE_CATALOG    VARCHAR     name of view's defining catalog.
     * TABLE_SCHEMA     VARCHAR     name of view's defining schema.
     * TABLE_NAME       VARCHAR     the simple name of the view.
     * VIEW_DEFINITION  VARCHAR     the character representation of the
     *                              &lt;query expression&gt; contained in the
     *                              corresponding &lt;view descriptor&gt;.
     * CHECK_OPTION     VARCHAR     {"CASCADED" | "LOCAL" | "NONE"}
     * IS_UPDATABLE     VARCHAR     {"YES" | "NO"}
     * INSERTABLE_INTO VARCHAR      {"YES" | "NO"}
     * IS_TRIGGER_UPDATABLE        VARCHAR  {"YES" | "NO"}
     * IS_TRIGGER_DELETEABLE       VARCHAR  {"YES" | "NO"}
     * IS_TRIGGER_INSERTABLE_INTO  VARCHAR  {"YES" | "NO"}
     * </pre> <p>
     *
     * @return a tabular description of the text source of all
     *        <code>View</code> objects accessible to
     *        the user.
     */
Table VIEWS() {
    Table t = sysTables[VIEWS];
    if (t == null) {
        t = createBlankTable(sysTableHsqlNames[VIEWS]);
        addColumn(t, "TABLE_CATALOG", SQL_IDENTIFIER);
        addColumn(t, "TABLE_SCHEMA", SQL_IDENTIFIER);
        // not null
        addColumn(t, "TABLE_NAME", SQL_IDENTIFIER);
        // not null
        addColumn(t, "VIEW_DEFINITION", CHARACTER_DATA);
        // not null
        addColumn(t, "CHECK_OPTION", CHARACTER_DATA);
        // not null
        addColumn(t, "IS_UPDATABLE", YES_OR_NO);
        // not null
        addColumn(t, "INSERTABLE_INTO", YES_OR_NO);
        // not null
        addColumn(t, "IS_TRIGGER_UPDATABLE", YES_OR_NO);
        // not null
        addColumn(t, "IS_TRIGGER_DELETABLE", YES_OR_NO);
        // not null
        addColumn(t, "IS_TRIGGER_INSERTABLE_INTO", YES_OR_NO);
        // order TABLE_NAME
        // added for unique: TABLE_SCHEMA, TABLE_CATALOG
        // false PK, as TABLE_SCHEMA and/or TABLE_CATALOG may be null
        HsqlName name = HsqlNameManager.newInfoSchemaObjectName(sysTableHsqlNames[VIEWS].name, false, SchemaObject.INDEX);
        t.createPrimaryKey(name, new int[] { 1, 2, 0 }, false);
        return t;
    }
    PersistentStore store = database.persistentStoreCollection.getStore(t);
    Iterator tables;
    Table table;
    Object[] row;
    final int table_catalog = 0;
    final int table_schema = 1;
    final int table_name = 2;
    final int view_definition = 3;
    final int check_option = 4;
    final int is_updatable = 5;
    final int insertable_into = 6;
    final int is_trigger_updatable = 7;
    final int is_trigger_deletable = 8;
    final int is_trigger_insertable_into = 9;
    tables = allTables();
    while (tables.hasNext()) {
        table = (Table) tables.next();
        if ((table.getSchemaName() != SqlInvariants.INFORMATION_SCHEMA_HSQLNAME && !table.isView()) || !isAccessibleTable(table)) {
            continue;
        }
        row = t.getEmptyRowData();
        row[table_catalog] = database.getCatalogName().name;
        row[table_schema] = table.getSchemaName().name;
        row[table_name] = table.getName().name;
        String check = Tokens.T_NONE;
        if (table instanceof View) {
            if (session.getGrantee().isFullyAccessibleByRole(table)) {
                row[view_definition] = ((View) table).getStatement();
            }
            switch(((View) table).getCheckOption()) {
                case SchemaObject.ViewCheckModes.CHECK_NONE:
                    break;
                case SchemaObject.ViewCheckModes.CHECK_LOCAL:
                    check = Tokens.T_LOCAL;
                    break;
                case SchemaObject.ViewCheckModes.CHECK_CASCADE:
                    check = Tokens.T_CASCADED;
                    break;
            }
        }
        row[check_option] = check;
        row[is_updatable] = table.isUpdatable() ? Tokens.T_YES : Tokens.T_NO;
        row[insertable_into] = table.isInsertable() ? Tokens.T_YES : Tokens.T_NO;
        // only applies to INSTEAD OF triggers
        row[is_trigger_updatable] = null;
        row[is_trigger_deletable] = null;
        row[is_trigger_insertable_into] = null;
        t.insertSys(store, row);
    }
    return t;
}