Derby
  1. Derby
  2. DERBY-5577

Write a table function to expose the contents of SYSCONGLOMERATES.DESCRIPTOR

    Details

    • Type: Improvement Improvement
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Tools
    • Labels:
      None
    • Urgency:
      Normal
    • Bug behavior facts:
      Seen in production

      Description

      The Derby metadata does not make it easy to list out the columns in an index. We should write a table function which cracks open the IndexDescriptor stored in the SYSCONGLOMERATES table.

        Activity

        Hide
        Rick Hillegas added a comment -

        Attaching IndexColumnVTI.java. This table function cracks open SYSCONGLOMERATES.DESCRIPTOR.

        Before using the table function, you must register it:

        create function indexColumns()
        returns table
        (
        conglomerateid char( 36 ),
        isUnique boolean,
        isUniqueWithDuplicateNulls boolean,
        positionInIndex int,
        baseColumnPosition int,
        isColumnAscending boolean
        )
        language java parameter style derby_jdbc_result_set reads sql data
        external name 'IndexColumnVTI.indexColumns';

        Then you can join it with the Derby metadata. Here is a query which lists all of the columns in all of the indexes in the database:

        select t.tableName, cong.conglomerateName indexName, col.columnName, ic.positionInIndex
        from sys.systables t, sys.sysconglomerates cong, sys.syscolumns col, table( indexColumns() ) ic
        where t.tableID = cong.tableID
        and t.tableID = col.referenceID
        and cong.conglomerateID = ic.conglomerateID
        and ic.baseColumnPosition = col.columnNumber
        order by t.tableName, cong.conglomerateName, ic.positionInIndex;

        Show
        Rick Hillegas added a comment - Attaching IndexColumnVTI.java. This table function cracks open SYSCONGLOMERATES.DESCRIPTOR. Before using the table function, you must register it: create function indexColumns() returns table ( conglomerateid char( 36 ), isUnique boolean, isUniqueWithDuplicateNulls boolean, positionInIndex int, baseColumnPosition int, isColumnAscending boolean ) language java parameter style derby_jdbc_result_set reads sql data external name 'IndexColumnVTI.indexColumns'; Then you can join it with the Derby metadata. Here is a query which lists all of the columns in all of the indexes in the database: select t.tableName, cong.conglomerateName indexName, col.columnName, ic.positionInIndex from sys.systables t, sys.sysconglomerates cong, sys.syscolumns col, table( indexColumns() ) ic where t.tableID = cong.tableID and t.tableID = col.referenceID and cong.conglomerateID = ic.conglomerateID and ic.baseColumnPosition = col.columnNumber order by t.tableName, cong.conglomerateName, ic.positionInIndex;

          People

          • Assignee:
            Unassigned
            Reporter:
            Rick Hillegas
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:

              Development