use of org.hsqldb_voltpatches.lib.Iterator 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;
}
use of org.hsqldb_voltpatches.lib.Iterator 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;
}
use of org.hsqldb_voltpatches.lib.Iterator in project voltdb by VoltDB.
the class DatabaseInformationMain method COLUMN_PRIVILEGES.
// -----------------------------------------------------------------------------
// SQL SCHEMATA VIEWS
// limited to views used in JDBC DatabaseMetaData
/**
* Retrieves a <code>Table</code> object describing the visible
* access rights for all visible columns of all accessible
* tables defined within this database.<p>
*
* Each row is a column privilege description with the following
* columns: <p>
*
* <pre class="SqlCodeExample">
* TABLE_CAT VARCHAR table catalog
* TABLE_SCHEM VARCHAR table schema
* TABLE_NAME VARCHAR table name
* COLUMN_NAME VARCHAR column name
* GRANTOR VARCHAR grantor of access
* GRANTEE VARCHAR grantee of access
* PRIVILEGE VARCHAR name of access
* IS_GRANTABLE VARCHAR grantable?: "YES" - grant to others, else "NO"
* </pre>
*
* <b>Note:</b> From 1.9.0, HSQLDB supports column level
* privileges. <p>
*
* @return a <code>Table</code> object describing the visible
* access rights for all visible columns of
* all accessible tables defined within this
* database
*/
final Table COLUMN_PRIVILEGES() {
Table t = sysTables[COLUMN_PRIVILEGES];
if (t == null) {
t = createBlankTable(sysTableHsqlNames[COLUMN_PRIVILEGES]);
// 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);
HsqlName name = HsqlNameManager.newInfoSchemaObjectName(sysTableHsqlNames[COLUMN_PRIVILEGES].name, false, SchemaObject.INDEX);
t.createPrimaryKey(name, new int[] { 2, 3, 4, 5, 6, 1, 0 }, false);
return t;
}
PersistentStore store = database.persistentStoreCollection.getStore(t);
// calculated column values
String tableCatalog;
String tableSchema;
String tableName;
Grantee granteeObject;
// intermediate holders
User user;
Iterator tables;
Table table;
Object[] row;
// column number mappings
final int grantor = 0;
final int grantee = 1;
final int table_catalog = 2;
final int table_schema = 3;
final int table_name = 4;
final int column_name = 5;
final int privilege_type = 6;
final int is_grantable = 7;
// enumerations
OrderedHashSet grantees = session.getGrantee().getGranteeAndAllRolesWithPublic();
// Initialization
tables = allTables();
while (tables.hasNext()) {
table = (Table) tables.next();
tableName = table.getName().name;
tableCatalog = database.getCatalogName().name;
tableSchema = table.getSchemaName().name;
for (int i = 0; i < grantees.size(); i++) {
granteeObject = (Grantee) grantees.get(i);
OrderedHashSet rights = granteeObject.getAllDirectPrivileges(table);
OrderedHashSet grants = granteeObject.getAllGrantedPrivileges(table);
if (!grants.isEmpty()) {
grants.addAll(rights);
rights = grants;
}
for (int j = 0; j < rights.size(); j++) {
Right right = (Right) rights.get(j);
Right grantableRight = right.getGrantableRights();
for (int k = 0; k < Right.privilegeTypes.length; k++) {
OrderedHashSet columnList = right.getColumnsForPrivilege(table, Right.privilegeTypes[k]);
OrderedHashSet grantableList = grantableRight.getColumnsForPrivilege(table, Right.privilegeTypes[k]);
for (int l = 0; l < columnList.size(); l++) {
HsqlName fullName = ((HsqlName) columnList.get(l));
row = t.getEmptyRowData();
row[grantor] = right.getGrantor().getName().name;
row[grantee] = right.getGrantee().getName().name;
row[table_catalog] = tableCatalog;
row[table_schema] = tableSchema;
row[table_name] = tableName;
row[column_name] = fullName.name;
row[privilege_type] = Right.privilegeNames[k];
row[is_grantable] = right.getGrantee() == table.getOwner() || grantableList.contains(fullName) ? "YES" : "NO";
try {
t.insertSys(store, row);
} catch (HsqlException e) {
}
}
}
}
}
}
return t;
}
use of org.hsqldb_voltpatches.lib.Iterator in project voltdb by VoltDB.
the class DatabaseInformationMain method TABLE_PRIVILEGES.
/*
WHERE ( GRANTEE IN ( 'PUBLIC', CURRENT_USER )
OR GRANTEE IN ( SELECT ROLE_NAME FROM ENABLED_ROLES )
OR GRANTOR = CURRENT_USER
OR GRANTOR IN ( SELECT ROLE_NAME FROM ENABLED_ROLES ) )
*/
/**
* The TABLE_PRIVILEGES view has one row for each visible access
* right for each accessible table definied within this database. <p>
*
* Each row is a table privilege description with the following columns: <p>
*
* <pre class="SqlCodeExample">
* GRANTOR VARCHAR grantor of access
* GRANTEE VARCHAR grantee of access
* TABLE_CATALOG VARCHAR table catalog
* TABLE_SCHEMA VARCHAR table schema
* TABLE_NAME VARCHAR table name
* PRIVILEGE_TYPE VARCHAR { "SELECT" | "INSERT" | "UPDATE" | "DELETE" | "REFERENCES" | "TRIGGER" }
* IS_GRANTABLE VARCHAR { "YES" | "NO" }
* WITH_HIERARCHY { "YES" | "NO" }
* </pre>
*
* @return a <code>Table</code> object describing the visible
* access rights for each accessible table
* defined within this database
*/
final Table TABLE_PRIVILEGES() {
Table t = sysTables[TABLE_PRIVILEGES];
if (t == null) {
t = createBlankTable(sysTableHsqlNames[TABLE_PRIVILEGES]);
// 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, "PRIVILEGE_TYPE", CHARACTER_DATA);
// not null
addColumn(t, "IS_GRANTABLE", YES_OR_NO);
addColumn(t, "WITH_HIERARCHY", YES_OR_NO);
//
HsqlName name = HsqlNameManager.newInfoSchemaObjectName(sysTableHsqlNames[SEQUENCES].name, false, SchemaObject.INDEX);
t.createPrimaryKey(name, new int[] { 0, 1, 2, 3, 4, 5, 6 }, false);
return t;
}
PersistentStore store = database.persistentStoreCollection.getStore(t);
// calculated column values
String tableCatalog;
String tableSchema;
String tableName;
Grantee granteeObject;
String privilege;
// intermediate holders
Iterator tables;
Table table;
Object[] row;
// column number mappings
final int grantor = 0;
final int grantee = 1;
final int table_catalog = 2;
final int table_schema = 3;
final int table_name = 4;
final int privilege_type = 5;
final int is_grantable = 6;
final int with_hierarchy = 7;
OrderedHashSet grantees = session.getGrantee().getGranteeAndAllRolesWithPublic();
tables = allTables();
while (tables.hasNext()) {
table = (Table) tables.next();
tableName = table.getName().name;
tableCatalog = table.getCatalogName().name;
tableSchema = table.getSchemaName().name;
for (int i = 0; i < grantees.size(); i++) {
granteeObject = (Grantee) grantees.get(i);
OrderedHashSet rights = granteeObject.getAllDirectPrivileges(table);
OrderedHashSet grants = granteeObject.getAllGrantedPrivileges(table);
if (!grants.isEmpty()) {
grants.addAll(rights);
rights = grants;
}
for (int j = 0; j < rights.size(); j++) {
Right right = (Right) rights.get(j);
Right grantableRight = right.getGrantableRights();
for (int k = 0; k < Right.privilegeTypes.length; k++) {
if (!right.canAccess(Right.privilegeTypes[k])) {
continue;
}
privilege = Right.privilegeNames[k];
row = t.getEmptyRowData();
row[grantor] = right.getGrantor().getName().name;
row[grantee] = right.getGrantee().getName().name;
row[table_catalog] = tableCatalog;
row[table_schema] = tableSchema;
row[table_name] = tableName;
row[privilege_type] = privilege;
row[is_grantable] = right.getGrantee() == table.getOwner() || grantableRight.canAccess(Right.privilegeTypes[k]) ? "YES" : "NO";
row[with_hierarchy] = "NO";
try {
t.insertSys(store, row);
} catch (HsqlException e) {
}
}
}
}
}
return t;
}
use of org.hsqldb_voltpatches.lib.Iterator in project voltdb by VoltDB.
the class DatabaseInformationFull method VIEW_ROUTINE_USAGE.
/**
* The VIEW_ROUTINE_USAGE table has one row for each SQL-invoked
* routine identified as the subject routine of either a <routine
* invocation>, a <method reference>, a <method invocation>,
* or a <static method invocation> contained in a <view
* definition>. <p>
*
* <b>Definition</b><p>
*
* <pre class="SqlCodeExample">
* CREATE TABLE VIEW_ROUTINE_USAGE (
* TABLE_CATALOG VARCHAR NULL,
* TABLE_SCHEMA VARCHAR NULL,
* TABLE_NAME VARCHAR NOT NULL,
* SPECIFIC_CATALOG VARCHAR NULL,
* SPECIFIC_SCHEMA VARCHAR NULL,
* SPECIFIC_NAME VARCHAR NOT NULL,
* UNIQUE( TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,
* SPECIFIC_CATALOG, SPECIFIC_SCHEMA,
* SPECIFIC_NAME )
* )
* </pre>
*
* <b>Description</b><p>
*
* <ol>
* <li> The values of TABLE_CATALOG, TABLE_SCHEMA, and TABLE_NAME are the
* catalog name, unqualified schema name, and qualified identifier,
* respectively, of the viewed table being described. <p>
*
* <li> The values of SPECIFIC_CATALOG, SPECIFIC_SCHEMA, and SPECIFIC_NAME are
* the catalog name, unqualified schema name, and qualified identifier,
* respectively, of the specific name of R. <p>
* </ol>
*
* @return Table
*/
Table VIEW_ROUTINE_USAGE() {
Table t = sysTables[VIEW_ROUTINE_USAGE];
if (t == null) {
t = createBlankTable(sysTableHsqlNames[VIEW_ROUTINE_USAGE]);
addColumn(t, "VIEW_CATALOG", SQL_IDENTIFIER);
addColumn(t, "VIEW_SCHEMA", SQL_IDENTIFIER);
addColumn(t, "VIEW_NAME", SQL_IDENTIFIER);
addColumn(t, "SPECIFIC_CATALOG", SQL_IDENTIFIER);
addColumn(t, "SPECIFIC_SCHEMA", SQL_IDENTIFIER);
addColumn(t, "SPECIFIC_NAME", SQL_IDENTIFIER);
HsqlName name = HsqlNameManager.newInfoSchemaObjectName(sysTableHsqlNames[VIEW_ROUTINE_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);
// Intermediate holders
Iterator tables;
Table table;
Object[] row;
// Column number mappings
final int view_catalog = 0;
final int view_schema = 1;
final int view_name = 2;
final int specific_catalog = 3;
final int specific_schema = 4;
final int specific_name = 5;
// Initialization
tables = database.schemaManager.databaseObjectIterator(SchemaObject.TABLE);
// Do it.
while (tables.hasNext()) {
table = (Table) tables.next();
if (table.isView() && session.getGrantee().isFullyAccessibleByRole(table)) {
// $FALL-THROUGH$
} else {
continue;
}
OrderedHashSet set = table.getReferences();
for (int i = 0; i < set.size(); i++) {
HsqlName refName = (HsqlName) set.get(i);
if (!session.getGrantee().isFullyAccessibleByRole(refName)) {
continue;
}
row = t.getEmptyRowData();
row[view_catalog] = database.getCatalogName().name;
row[view_schema] = table.getSchemaName().name;
row[view_name] = table.getName().name;
row[specific_catalog] = database.getCatalogName().name;
row[specific_schema] = refName.schema.name;
row[specific_name] = refName.name;
try {
t.insertSys(store, row);
} catch (HsqlException e) {
}
}
}
return t;
}
Aggregations