final Table SYSTEM_CROSSREFERENCE()

in HSQL/src/org/hsqldb1/DatabaseInformationMain.java [1159:1334]
129 lines of code
9 McCabe index (conditional complexity)

    final Table SYSTEM_CROSSREFERENCE() throws HsqlException {

        Table t = sysTables[SYSTEM_CROSSREFERENCE];

        if (t == null) {
            t = createBlankTable(sysTableHsqlNames[SYSTEM_CROSSREFERENCE]);

            addColumn(t, "PKTABLE_CAT", Types.VARCHAR);
            addColumn(t, "PKTABLE_SCHEM", Types.VARCHAR);
            addColumn(t, "PKTABLE_NAME", Types.VARCHAR, false);      // not null
            addColumn(t, "PKCOLUMN_NAME", Types.VARCHAR, false);     // not null
            addColumn(t, "FKTABLE_CAT", Types.VARCHAR);
            addColumn(t, "FKTABLE_SCHEM", Types.VARCHAR);
            addColumn(t, "FKTABLE_NAME", Types.VARCHAR, false);      // not null
            addColumn(t, "FKCOLUMN_NAME", Types.VARCHAR, false);     // not null
            addColumn(t, "KEY_SEQ", Types.SMALLINT, false);          // not null
            addColumn(t, "UPDATE_RULE", Types.SMALLINT, false);      // not null
            addColumn(t, "DELETE_RULE", Types.SMALLINT, false);      // not null
            addColumn(t, "FK_NAME", Types.VARCHAR);
            addColumn(t, "PK_NAME", Types.VARCHAR);
            addColumn(t, "DEFERRABILITY", Types.SMALLINT, false);    // not null

            // 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
            t.createPrimaryKey(null, new int[] {
                4, 5, 6, 8, 11
            }, false);

            return 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.allTablesIterator();
        pkInfo = new DITableInfo();
        fkInfo = new DITableInfo();

        // the only deferrability rule currently supported by hsqldb is:
        deferrability = ValuePool.getInt(Constraint.NOT_DEFERRABLE);

        // 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 (!isAccessibleTable(table)) {
                continue;
            }

            constraints     = table.getConstraints();
            constraintCount = constraints.length;

            for (int i = 0; i < constraintCount; i++) {
                constraint = (Constraint) constraints[i];

                if (constraint.getType() == Constraint.FOREIGN_KEY
                        && isAccessibleTable(constraint.getRef())) {
                    fkConstraintsList.add(constraint);
                }
            }
        }

        // Now that we have all of the desired constraints, we need to
        // process them, generating one row in our ouput table for each
        // imported/exported column pair of each 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 = ns.getCatalogName(pkTable);
            pkTableSchema  = pkTable.getSchemaName();
            fkTableCatalog = ns.getCatalogName(fkTable);
            fkTableSchema  = fkTable.getSchemaName();
            mainCols       = constraint.getMainColumns();
            refCols        = constraint.getRefColumns();
            columnCount    = refCols.length;
            fkName         = constraint.getFkName();
            pkName         = constraint.getPkName();

            //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(row);
            }
        }

        t.setDataReadOnly(true);

        return t;
    }