Example: Displaying database schema information

This example demonstrates how to navigate the system tables of an UltraLiteJ database to examine the schema information. The data for each row of the tables is also displayed.

package ianywhere.ultralitej.demo;
import ianywhere.ultralitej.*;

/** Sample program to dump schema of a database.
 * This sample extracts schema information into a set of data structures
 * (TableArray, OptionArray) before dumping out the meta data so as to
 * provide for future schema information lookup.
 */
public class DumpSchema
{
    /** Mainline.
     * @param args program arguments (not used)
     */
    public static void main( String[] args )
    {
        try {
            Configuration config = DatabaseManager.createConfigurationFile( "Sales.ulj" );
            Connection conn = DatabaseManager.connect( config );

            Demo.display(
                    TableSchema.SYS_TABLES
                    + " table_flags are:\nTableSchema.TABLE_IS_SYSTEM(0x"
                    + Integer.toHexString( ((int)TABLE_FLAG_SYSTEM) & 0xffff )
                    + "),\nTableSchema.TABLE_IS_NOSYNC(0x"
                    + Integer.toHexString( ((int)TABLE_FLAG_NO_SYNC) & 0xffff )
                    + ")"
                );
            getSchema( conn );
            dumpSchema( conn );

        } catch( ULjException exc ) {
            Demo.displayException( exc );
        }
    }

    // Some constants for metadata
    private static String SQL_SELECT_TABLE_COLS =
            "SELECT T.table_id, T.table_name, T.table_flags,"
            + " C.column_id, C.column_name, C.column_flags,"
            + " C.column_domain, C.column_length, C.column_default"
            + " FROM " + TableSchema.SYS_TABLES + " T"
            + " JOIN " + TableSchema.SYS_COLUMNS + " C"
            + " ON T.table_id = C.table_id"
            + " ORDER BY T.table_id"
            ;
    private static final int TABLE_ID = 1;
    private static final int TABLE_NAME = 2;
    private static final int TABLE_FLAGS = 3;
    private static final int COLUMN_ID = 4;
    private static final int COLUMN_NAME = 5;
    private static final int COLUMN_FLAGS = 6;
    private static final int COLUMN_DOMAIN_TYPE = 7;
    private static final int COLUMN_DOMAIN_LENGTH = 8;
    private static final int COLUMN_DEFAULT = 9;

    private static final int TABLE_FLAG_SYSTEM = TableSchema.TABLE_IS_SYSTEM;
    private static final int TABLE_FLAG_NO_SYNC = TableSchema.TABLE_IS_NOSYNC;

    private static final int COLUMN_FLAG_IN_PRIMARY_INDEX = 0x01;
    private static final int COLUMN_FLAG_IS_NULLABLE = 0x02;

    private static String SQL_SELECT_INDEX_COLS =
            "SELECT I.table_id, I.index_id, I.index_name, I.index_flags,"
            + " X.\"order\", X.column_id, X.index_column_flags"
            + " FROM " + TableSchema.SYS_INDEX_COLUMNS + " X"
            + " JOIN " + TableSchema.SYS_INDEXES + " I"
            + " ON I.table_id = X.table_id AND I.index_id = X.index_id"
            + " ORDER BY X.table_id, X.index_id, X.\"order\""
            ;
    private static final int INDEX_TABLE_ID = 1;
    private static final int INDEX_ID = 2;
    private static final int INDEX_NAME = 3;
    private static final int INDEX_FLAGS = 4;
    private static final int INDEX_COLUMN_ORDER = 5;
    private static final int INDEX_COLUMN_COLUMN_ID = 6;
    private static final int INDEX_COLUMN_FLAGS = 7;

    private static final int INDEX_COLUMN_FLAG_FORWARD = 1;
    
    private static final int INDEX_FLAG_UNIQUE_KEY = 0x01;
    private static final int INDEX_FLAG_UNIQUE_INDEX = 0x02;
    private static final int INDEX_FLAG_PERSISTENT = 0x04;
    private static final int INDEX_FLAG_PRIMARY_INDEX = 0x08;

    private static String SQL_SELECT_OPTIONS =
            "SELECT name, value FROM " + TableSchema.SYS_INTERNAL
            + " ORDER BY name"
            ;
    private static final int OPTION_NAME = 1;
    private static final int OPTION_VALUE = 2;

    // Metadata:
    private static TableArray tables = new TableArray();
    private static OptionArray options = new OptionArray();

    /**
     * Extracts the schema of a database
     */
    private static void getSchema( Connection conn ) throws ULjException
    {
        PreparedStatement stmt = conn.prepareStatement(
                SQL_SELECT_TABLE_COLS
            );
        ResultSet cursor = stmt.executeQuery();
        Table table = null;
        int last_table_id = -1;
        for( ; cursor.next(); ) {
            int table_id = cursor.getInt( TABLE_ID );
            if( table_id != last_table_id ) {
                String table_name = cursor.getString( TABLE_NAME );
                int table_flags = cursor.getInt( TABLE_FLAGS );
                table = new Table( table_id, table_name, table_flags );
                tables.append( table );
                last_table_id = table_id;
            }
            int column_id = cursor.getInt( COLUMN_ID );
            String column_name = cursor.getString( COLUMN_NAME );
            int column_flags = cursor.getInt( COLUMN_FLAGS );
            int column_domain = cursor.getInt( COLUMN_DOMAIN_TYPE );
            int column_length = cursor.getInt( COLUMN_DOMAIN_LENGTH );
            int column_default = cursor.getInt( COLUMN_DEFAULT );
            Column column = new Column(
                    conn, column_id, column_name, column_flags,
                    column_domain, column_length, column_default
                );
            table.addColumn( column );
        }
        cursor.close();
        stmt.close();

        // read indexes
        stmt = conn.prepareStatement( SQL_SELECT_INDEX_COLS ); 
        cursor = stmt.executeQuery();
        int last_index_id = -1;
        Index index = null;
        last_table_id = -1;
        for( ; cursor.next(); ) {
            int table_id = cursor.getInt( INDEX_TABLE_ID );
            int index_id = cursor.getInt( INDEX_ID );
            if( last_table_id != table_id || last_index_id != index_id ) {
                String index_name = cursor.getString( INDEX_NAME );
                int index_flags = cursor.getInt( INDEX_FLAGS );
                index = new Index( index_id, index_name, index_flags );
                table = findTable( table_id );
                table.addIndex( index );
                last_index_id = index_id;
                last_table_id = table_id;
            }
            int order = cursor.getInt( INDEX_COLUMN_ORDER );
            int column_id = cursor.getInt( INDEX_COLUMN_COLUMN_ID );
            int index_column_flags = cursor.getInt( INDEX_COLUMN_FLAGS );
            IndexColumn index_column = new IndexColumn( order, column_id, index_column_flags );
            index.addColumn( index_column );
        }
        cursor.close();
        stmt.close();

        // read database options
        stmt = conn.prepareStatement( SQL_SELECT_OPTIONS );
        cursor = stmt.executeQuery();
        for( ; cursor.next(); ) {
            String option_name = cursor.getString( OPTION_NAME );
            String option_value = cursor.getString( OPTION_VALUE );
            Option option = new Option( option_name, option_value );
            options.append( option );
        }
        cursor.close();
        stmt.close();
    }

    /** Dump the schema of a database
     */
    private static void dumpSchema( Connection conn ) throws ULjException
    {
        // Display the metadata options
        Demo.display( "\nMetadata options:\n" );
        for( int opt_no = 0; opt_no < options.count(); ++ opt_no ) {
            Option option = options.elementAt( opt_no );
            option.display();
        }
        // Display the metadata tables
        Demo.display( "\nMetadata tables:" );
        for( int table_no = 0; table_no < tables.count(); ++ table_no ) {
            Table table = tables.elementAt( table_no );
            table.display( table_no );
        }
        // Display the rows for non-system tables.
        for( int table_no = 0; table_no < tables.count(); ++ table_no ) {
            Table table = tables.elementAt( table_no );
            if( 0 == ( table.getFlags() & TABLE_FLAG_SYSTEM ) ) {
                Demo.display( "\nRows for table: ", table.getName(), "\n" );
                Index index = table.getIndex( 0 );
                PreparedStatement stmt = conn.prepareStatement(
                        "SELECT * FROM \"" + table.getName() + "\""
                    );
                ResultSet cursor = stmt.executeQuery();
                int column_count = table.getColumnCount();
                int row_count = 0;
                for( ; cursor.next(); ) {
                    StringBuffer buf = new StringBuffer();
                    buf.append( "Row[" );
                    buf.append( Integer.toString( ++row_count ) );
                    buf.append( "]:" );
                    char joiner = ' ';
                    for( int col_no = 1; col_no <= column_count; ++col_no ) {
                        String value = cursor.isNull( col_no )
                                     ? "<NULL>"
                                     : cursor.getString( col_no );
                        buf.append( joiner );
                        buf.append( value );
                        joiner = ',';
                    }
                    Demo.display( buf.toString() );
                }
                cursor.close();
                stmt.close();
            }
        }
    }

    /** Find a table.
     */
    private static Table findTable( int table_id )
    {
        Table retn = null;
        for( int i = tables.count(); i > 0; ) {
            Table table = tables.elementAt( --i );
            if( table_id == table.getId() ) {
                retn = table;
                break;
            }
        }
        return retn;
    }
    
    /** Representation of a column.
     */
    private static class Column
    {
        private int _column_id;
        private String _column_name;
        private int _column_flags;
        private Domain _domain;
        private int _column_default;

        Column( Connection conn, int column_id, String column_name, int column_flags, int column_domain, int column_length, int column_default )
            throws ULjException
        {
            _column_id = column_id;
            _column_name = column_name;
            _column_flags = column_flags;
            _column_default = column_default;
            int scale = 0;
            switch( column_domain ) {
              case Domain.NUMERIC :
                scale = column_length >> 8;
                column_length &= 255;
                _domain = conn.createDomain( Domain.NUMERIC, column_length, scale );
                break;
              case Domain.VARCHAR :
              case Domain.BINARY :
                _domain = conn.createDomain( column_domain, column_length );
                break;
              default :
                _domain = conn.createDomain( column_domain );
                break;
            }
        }

        String getName()
        {
            return _column_name;
        }

        void display()
        {
            StringBuffer buf = new StringBuffer();
            buf.append( "  \"" );
            buf.append( _column_name );
            buf.append( "\"\t" );
            buf.append( _domain.getName() );
            switch( _domain.getType() ) {
              case Domain.NUMERIC :
                buf.append( '(' );
                buf.append( Integer.toString( _domain.getPrecision() ) );
                buf.append( ',' );
                buf.append( Integer.toString( _domain.getScale() ) );
                buf.append( ')' );
                break;
              case Domain.VARCHAR :
              case Domain.BINARY :
                buf.append( '(' );
                buf.append( Integer.toString( _domain.getSize() ) );
                buf.append( ')' );
                break;
            }
            if( 0 != ( _column_flags & COLUMN_FLAG_IS_NULLABLE ) ) {
                buf.append( " NULL" );
            } else {
                buf.append( " NOT NULL" );
            }
            switch( _column_default ) {
                case ColumnSchema.COLUMN_DEFAULT_NONE:
                default:
                    break;
                case ColumnSchema.COLUMN_DEFAULT_AUTOINC:
                    buf.append( " DEFAULT AUTOINCREMENT" );
                    break;
                case ColumnSchema.COLUMN_DEFAULT_GLOBAL_AUTOINC:
                    buf.append( " DEFAULT GLOBAL AUTOINCREMENT" );
                    break;
                case ColumnSchema.COLUMN_DEFAULT_CURRENT_DATE:
                    buf.append( " DEFAULT CURRENT DATE" );
                    break;
                case ColumnSchema.COLUMN_DEFAULT_CURRENT_TIME:
                    buf.append( " DEFAULT CURRENT TIME" );
                    break;
                case ColumnSchema.COLUMN_DEFAULT_CURRENT_TIMESTAMP:
                    buf.append( " DEFAULT CURRENT TIMESTAMP" );
                    break;
                case ColumnSchema.COLUMN_DEFAULT_UNIQUE_ID:
                    buf.append( " DEFAULT NEWID()" );
                    break;
            }
            buf.append( ", /* column_id=" );
            buf.append( Integer.toString( _column_id ) );
            buf.append( " column_flags=" );
            int c = 0;
            if( 0 != ( _column_flags & COLUMN_FLAG_IN_PRIMARY_INDEX ) ) {
                buf.append( "IN_PRIMARY_INDEX" );
                c++;
            }
            if( 0 != ( _column_flags & COLUMN_FLAG_IS_NULLABLE ) ) {
                if( c > 0 ) {
                    buf.append( "," );
                }
                buf.append( "NULLABLE" );
            }
            buf.append( " */" );
            Demo.display( buf.toString() );
        }
    }

    /** Representation of Index schema.
     */
    private static class Index
    {
        private String _index_name;
        private int _index_id;
        private int _index_flags;
        private IndexColumnArray _columns;

        Index( int index_id, String index_name, int index_flags )
        {
            _index_id = index_id;
            _index_name = index_name;
            _index_flags = index_flags;
            _columns = new IndexColumnArray();
        }

        void addColumn( IndexColumn column )
        {
            _columns.append( column );
        }

        void display( Table table, boolean constraints )
        {
            StringBuffer buf = new StringBuffer();
            String flags = "";
            String indent = "  ";
            if( 0 != ( _index_flags & INDEX_FLAG_PRIMARY_INDEX ) ) {
                if( !constraints )      return;
                buf.append( "  CONSTRAINT \"" );
                buf.append( _index_name );
                buf.append( "\" PRIMARY KEY (" );
                flags = "PRIMARY_KEY,UNIQUE_KEY";
            } else if( 0 != ( _index_flags & INDEX_FLAG_UNIQUE_KEY ) ) {
                if( !constraints )      return;
                buf.append( "  CONSTRAINT \"" );
                buf.append( _index_name );
                buf.append( "\" UNIQUE (" );
                flags = "UNIQUE_KEY";
            } else {
                if( constraints )       return;
                if( 0 != ( _index_flags & INDEX_FLAG_UNIQUE_INDEX ) ) {
                    buf.append( "UNIQUE " );
                    flags = "UNIQUE_INDEX";
                }
                indent = "";
                buf.append( "INDEX \"" );
                buf.append( _index_name );
                buf.append( "\" ON \"" );
                buf.append( table.getName() );
                buf.append( "\" (" );
            }
            buf.append( "\n" );
            buf.append( indent );
            buf.append( "  /* index_id=" );
            buf.append( Integer.toString( _index_id ) );
            buf.append( " index_flags=" );
            buf.append( flags );
            if( 0 != ( _index_flags & INDEX_FLAG_PERSISTENT ) ) {
                buf.append( ",PERSISTENT" );
            }
            buf.append( " */" );
            Demo.display( buf.toString() );
            int bounds = _columns.count();
            for( int col_no = 0; col_no < bounds; ++ col_no ) {
                IndexColumn column = _columns.elementAt( col_no );
                column.display( table, indent, col_no + 1 < bounds );
            }
            Demo.display( indent + ")" );
        }

        String getName()
        {
            return _index_name;
        }
    }

    /** Representation of IndexColumn schema.
     */
    private static class IndexColumn
    {
        private int _index_column_id;
        private int _index_column_column_id;
        private int _index_column_flags;

        IndexColumn( int index_column_id, int index_column_column_id, int index_column_flags )
        {
            _index_column_id = index_column_id;
            _index_column_column_id = index_column_column_id;
            _index_column_flags = index_column_flags;
        }

        void display( Table table, String indent, boolean notlast )
        {
            StringBuffer buf = new StringBuffer( indent );
            buf.append( "  \"" );
            Column column = table.getColumn( _index_column_column_id );
            buf.append( column.getName() );
            if( 0 != ( _index_column_flags & INDEX_COLUMN_FLAG_FORWARD ) ) {
                buf.append( "\" ASC" );
            } else {
                buf.append( "\" DESC" );
            }
            if( notlast ) {
                buf.append( "," );
            }
            Demo.display( buf.toString() );
        }
    }

    /** Representation of a database Option.
     */
    private static class Option
    {
        private String _option_name;
        private String _option_value;

        Option( String name, String value )
        {
            _option_name = name;
            _option_value = value;
        }

        void display()
        {
            StringBuffer buf = new StringBuffer();
            buf.append( "Option[ " );
            buf.append( _option_name );
            buf.append( " ] = '" );
            buf.append( _option_value );
            buf.append( "'" );
            Demo.display( buf.toString() );
        }

    }

    /** Representation of Table schema.
     */
    private static class Table
    {
        private String _table_name;
        private int _table_id;
        private int _table_flags;
        private ColumnArray _columns;
        private IndexArray _indexes;
        
        Table( int table_id, String table_name, int table_flags )
        {
            _table_name = table_name;
            _table_id = table_id;
            _table_flags = table_flags;
            _columns = new ColumnArray();
            _indexes = new IndexArray();
        }

        void addColumn( Column column )
        {
            _columns.append( column );
        }

        void addIndex( Index index )
        {
            _indexes.append( index );
        }

        Column getColumn( int id )
        {
            return _columns.elementAt( id );
        }

        int getColumnCount()
        {
            return _columns.count();
        }

        int getFlags()
        {
            return _table_flags;
        }

        Index getIndex( int id )
        {
            return _indexes.elementAt( id );
        }

        String getName()
        {
            return _table_name;
        }

        void display( int logical_number )
        {
            StringBuffer str = new StringBuffer();
            str.append( "\nTABLE \"" );
            str.append( _table_name );
            str.append( "\" /* table_id=" );
            str.append( Integer.toString( _table_id ) );
            str.append( " table_flags=" );
            if( 0 == _table_flags ) {
                str.append( "0" );
            } else {
                int c = 0;
                if( 0 != ( _table_flags & TABLE_FLAG_SYSTEM ) ) {
                    str.append( "SYSTEM" );
                    c++;
                }
                if( 0 != ( _table_flags & TABLE_FLAG_NO_SYNC ) ) {
                    if( c > 0 ) {
                        str.append( "," );
                    }
                    str.append( "NO_SYNC" );
                    c++;
                }
            }
            str.append( " */ (" );
            Demo.display( str.toString() );
            int bound = _columns.count();
            for( int col_no = 0; col_no < bound; ++col_no ) {
                Column column = _columns.elementAt( col_no );
                column.display();
            }
            bound = _indexes.count();
            for( int idx_no = 0; idx_no < bound; ++idx_no ) {
                Index index = _indexes.elementAt( idx_no );
                index.display( this, true );
            }
            Demo.display( ")" );
            for( int idx_no = 0; idx_no < bound; ++idx_no ) {
                Index index = _indexes.elementAt( idx_no );
                index.display( this, false );
            }
        }

        int getId()
        {
            return _table_id;
        }
    }

    /** Simple adjustable array of objects.
     */
    private static class ObjArray
    {
        private Object[] _array = new Object[ 10 ];
        private int _used = 0;

        void append( Object str )
        {
            if( _used >= _array.length ) {
                Object[] new_array = new Object[ _used * 2 ];
                for( int i = _used; i > 0; ) {
                    --i;
                    new_array[ i ] = _array[ i ];
                }
                _array = new_array;
            }
            _array[ _used++ ] = str;
        }

        int count()
        {
            return _used;
        }

        Object getElementAt( int position )
        {
            return _array[ position ];
        }
    }

    /** Simple adjustable array of Strings.
     */
    private static class StrArray
        extends ObjArray
    {
        String elementAt( int position )
        {
            return (String)getElementAt( position );
        }
    }

    /** Simple adjustable array of Table objects.
     */
    private static class TableArray
        extends ObjArray
    {
        Table elementAt( int position )
        {
            return (Table)getElementAt( position );
        }
    }

    /** Simple adjustable array of Column objects.
     */
    private static class ColumnArray
        extends ObjArray
    {
        Column elementAt( int position )
        {
            return (Column)getElementAt( position );
        }
    }

    /** Simple adjustable array of Index objects.
     */
    private static class IndexArray
        extends ObjArray
    {
        Index elementAt( int position )
        {
            return (Index)getElementAt( position );
        }
    }

    /** Simple adjustable array of IndexColumn objects.
     */
    private static class IndexColumnArray
        extends ObjArray
    {
        IndexColumn elementAt( int position )
        {
            return (IndexColumn)getElementAt( position );
        }
    }

    /** Simple adjustable array of Option objects.
     */
    private static class OptionArray
        extends ObjArray
    {
        Option elementAt( int position )
        {
            return (Option)getElementAt( position );
        }
    }

}

The partial output of the application is shown below.

Metadata options:

Option[ date_format ] = 'YYYY-MM-DD'
Option[ date_order ] = 'YMD'
Option[ global_database_id ] = '0'
Option[ nearest_century ] = '50'
Option[ precision ] = '30'
Option[ scale ] = '6'
Option[ time_format ] = 'HH:NN:SS.SSS'
Option[ timestamp_format ] = 'YYYY-MM-DD HH:NN:SS.SSS'
Option[ timestamp_increment ] = '1'

Metadata tables:

Table[0] name = "systable"  id = 0 flags = 0xc000,SYSTEM,NO_SYNC
  column[0 ]: name = "table_id" flags = 0x1,IN-PRIMARY-INDEX domain = INTEGER
  column[1 ]: name = "table_name" flags = 0x0 domain = VARCHAR(128)
  column[2 ]: name = "table_flags" flags = 0x0 domain = UNSIGNED-SHORT
  column[3 ]: name = "table_data" flags = 0x0 domain = INTEGER
  column[4 ]: name = "table_autoinc" flags = 0x0 domain = BIG
  index[0 ]: name = "primary" flags = 0xf,UNIQUE-KEY,UNIQUE-INDEX,PERSISTENT,PRIMARY-INDEX
    key[0 ]: name = "table_id" flags = 0x1,FORWARD

Table[1] name = "syscolumn"  id = 1 flags = 0xc000,SYSTEM,NO_SYNC
  column[0 ]: name = "table_id" flags = 0x1,IN-PRIMARY-INDEX domain = INTEGER
  column[1 ]: name = "column_id" flags = 0x1,IN-PRIMARY-INDEX domain = INTEGER
  column[2 ]: name = "column_name" flags = 0x0 domain = VARCHAR(128)
  column[3 ]: name = "column_flags" flags = 0x0 domain = TINY
  column[4 ]: name = "column_domain" flags = 0x0 domain = TINY
  column[5 ]: name = "column_length" flags = 0x0 domain = INTEGER
  column[6 ]: name = "column_default" flags = 0x0 domain = TINY
  index[0 ]: name = "primary" flags = 0xf,UNIQUE-KEY,UNIQUE-INDEX,PERSISTENT,PRIMARY-INDEX
    key[0 ]: name = "table_id" flags = 0x1,FORWARD
    key[1 ]: name = "column_id" flags = 0x1,FORWARD

Table[2] name = "sysindex"  id = 2 flags = 0xc000,SYSTEM,NO_SYNC
  column[0 ]: name = "table_id" flags = 0x1,IN-PRIMARY-INDEX domain = INTEGER
  column[1 ]: name = "index_id" flags = 0x1,IN-PRIMARY-INDEX domain = INTEGER
  column[2 ]: name = "index_name" flags = 0x0 domain = VARCHAR(128)
  column[3 ]: name = "index_flags" flags = 0x0 domain = TINY
  column[4 ]: name = "index_data" flags = 0x0 domain = INTEGER
  index[0 ]: name = "primary" flags = 0xf,UNIQUE-KEY,UNIQUE-INDEX,PERSISTENT,PRIMARY-INDEX
    key[0 ]: name = "table_id" flags = 0x1,FORWARD
    key[1 ]: name = "index_id" flags = 0x1,FORWARD

Table[3] name = "sysindexcolumn"  id = 3 flags = 0xc000,SYSTEM,NO_SYNC
  column[0 ]: name = "table_id" flags = 0x1,IN-PRIMARY-INDEX domain = INTEGER
  column[1 ]: name = "index_id" flags = 0x1,IN-PRIMARY-INDEX domain = INTEGER
  column[2 ]: name = "order" flags = 0x1,IN-PRIMARY-INDEX domain = INTEGER
  column[3 ]: name = "column_id" flags = 0x0 domain = INTEGER
  column[4 ]: name = "index_column_flags" flags = 0x0 domain = TINY
  index[0 ]: name = "primary" flags = 0xf,UNIQUE-KEY,UNIQUE-INDEX,PERSISTENT,PRIMARY-INDEX
    key[0 ]: name = "table_id" flags = 0x1,FORWARD
    key[1 ]: name = "index_id" flags = 0x1,FORWARD
    key[2 ]: name = "order" flags = 0x1,FORWARD

Table[4] name = "sysinternal"  id = 4 flags = 0xc000,SYSTEM,NO_SYNC
  column[0 ]: name = "name" flags = 0x1,IN-PRIMARY-INDEX domain = VARCHAR(128)
  column[1 ]: name = "value" flags = 0x0 domain = VARCHAR(128)
  index[0 ]: name = "primary" flags = 0xf,UNIQUE-KEY,UNIQUE-INDEX,PERSISTENT,PRIMARY-INDEX
    key[0 ]: name = "name" flags = 0x1,FORWARD

Table[5] name = "syspublications"  id = 5 flags = 0xc000,SYSTEM,NO_SYNC
  column[0 ]: name = "publication_id" flags = 0x1,IN-PRIMARY-INDEX domain = INTEGER
  column[1 ]: name = "publication_name" flags = 0x0 domain = VARCHAR(128)
  column[2 ]: name = "download_timestamp" flags = 0x0 domain = TIMESTAMP
  column[3 ]: name = "last_sync_sent" flags = 0x0 domain = INTEGER
  column[4 ]: name = "last_sync_confirmed" flags = 0x0 domain = INTEGER
  index[0 ]: name = "primary" flags = 0xf,UNIQUE-KEY,UNIQUE-INDEX,PERSISTENT,PRIMARY-INDEX
    key[0 ]: name = "publication_id" flags = 0x1,FORWARD

Table[6] name = "sysarticles"  id = 6 flags = 0xc000,SYSTEM,NO_SYNC
  column[0 ]: name = "publication_id" flags = 0x1,IN-PRIMARY-INDEX domain = INTEGER
  column[1 ]: name = "table_id" flags = 0x1,IN-PRIMARY-INDEX domain = INTEGER
  index[0 ]: name = "primary" flags = 0xf,UNIQUE-KEY,UNIQUE-INDEX,PERSISTENT,PRIMARY-INDEX
    key[0 ]: name = "publication_id" flags = 0x1,FORWARD
    key[1 ]: name = "table_id" flags = 0x1,FORWARD

Table[7] name = "sysforeignkey"  id = 7 flags = 0xc000,SYSTEM,NO_SYNC
  column[0 ]: name = "table_id" flags = 0x1,IN-PRIMARY-INDEX domain = INTEGER
  column[1 ]: name = "foreign_table_id" flags = 0x0 domain = INTEGER
  column[2 ]: name = "foreign_key_id" flags = 0x1,IN-PRIMARY-INDEX domain = INTEGER
  column[3 ]: name = "name" flags = 0x0 domain = VARCHAR(128)
  column[4 ]: name = "index_name" flags = 0x0 domain = VARCHAR(128)
  index[0 ]: name = "primary" flags = 0xf,UNIQUE-KEY,UNIQUE-INDEX,PERSISTENT,PRIMARY-INDEX
    key[0 ]: name = "table_id" flags = 0x1,FORWARD
    key[1 ]: name = "foreign_key_id" flags = 0x1,FORWARD

Table[8] name = "sysfkcol"  id = 8 flags = 0xc000,SYSTEM,NO_SYNC
  column[0 ]: name = "table_id" flags = 0x1,IN-PRIMARY-INDEX domain = INTEGER
  column[1 ]: name = "foreign_key_id" flags = 0x1,IN-PRIMARY-INDEX domain = INTEGER
  column[2 ]: name = "item_no" flags = 0x1,IN-PRIMARY-INDEX domain = SHORT
  column[3 ]: name = "column_id" flags = 0x0 domain = INTEGER
  column[4 ]: name = "foreign_column_id" flags = 0x0 domain = INTEGER
  index[0 ]: name = "primary" flags = 0xf,UNIQUE-KEY,UNIQUE-INDEX,PERSISTENT,PRIMARY-INDEX
    key[0 ]: name = "table_id" flags = 0x1,FORWARD
    key[1 ]: name = "foreign_key_id" flags = 0x1,FORWARD
    key[2 ]: name = "item_no" flags = 0x1,FORWARD