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 );
        }
    }

}

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