APPENDIX L dbschema with extents


The following ESQL\C program is an Informix schema generator for OnLine. The functionality is similar to the 'dbschema' utility with the following differences:


Content-Type: text/plain; charset="us-ascii"
Content-Disposition: attachment; filename="TBSCHEMA.EC"

/**************************************************************************** 
 * MODULE 
 *   tbschema.ec 
 * 
 * SYNOPSIS
 *   tbschema [-int] <database> [<table 1>...]
 *
 *   Options : 
 *              i - display indexes only.
 *              n - do not print dbspace information.
 *              t - print only the tables found; omitting indexes.
 *
 *    <database>  - valid OnLine database. 
 *
 *    <table 1>   - optional table names separated by spaces. Wild cards are 
 *                  allowed but must be quoted to protect them from the shell.
 *                  If no table name(s) are given, all the tables will be
 *                  printed.
 *
 * FUNCTIONS
 *    main(); 
 *    check_db();
 *    process_usr(); 
 *    process_tbl(); 
 *    process_col(); 
 *    process_con(); 
 *    process_idx(); 
 *    process_view(); 
 *    process_syn(); 
 *    get_col_type(); 
 *    get_blobspace(); 
 *    get_col_name(); 
 *    get_tu_name(); 
 *    get_db_priv(); 
 *    bld_dbspace(); 
 *    chk_sql_err(); 
 *    clip(); 
 *    usage(); 
 *    get_options(); 
 *    signal_handler(); 
 *
 * DESCRIPTION
 *    The following ESQL\C program is an Informix schema generator for OnLine.
 *    The functionality is similar to the 'dbschema' utility with the following 
 *    differences:
 *
 *        - displays the dbspace location of the table.
 *
 *        - displays the extent and next extent sizes.
 *
 *        - displays the table locking level (row or page).
 *
 *        - NOW, does display table, column, or owner permissions.
 *
 * NOTES
 *    The utility executes 'tbstat -d' to gather the dbspace information.
 *
 *    The following datatypes are supported:
 *
 *        CHAR        SMALLINT       INTEGER      FLOAT     VARCHAR
 *        DECIMAL     SMALLFLOAT     SERIAL       DATE      MONEY
 *        TEXT        BYTE           DATETIME     INTERVAL  UNIQUE CONSTRAINT
 *
 *    To compile, enter 'esql -o tbschema tbschema.ec' or
 *                      'c4gl -o tbschema tbschema.ec'
 *
 *
 * DATE
 *   March 14 1991
 *
 * REVISIONS
 *   August 29, 1994 Albert E. Whale - Updated the output to include owner
 #                                     name in the table & index schema.
 ****************************************************************************/

#include <stdio.h>
#include <signal.h>
#include <decimal.h>
#include <sys/types.h>

$include sqlca;
$include sqltypes;

/* Calculates the 'dbspace' number from table partition. */

#define part_dbs( part_id ) ( (int)( ( (part_id) >> 24 ) & 0xff ) )

/* Calculates the VARCHAR datatype minimum and maximum sizes from the . */
/* 'collength' column values stored in 'syscolumns'.                    */

#define vcmin( size ) ( ( ( size ) >> 8 ) & 0x00ff )
#define vcmax( size ) ( ( size ) & 0x00ff )

/* Calculates the DECIMAL & MONEY datatype minimum and maximum sizes    */
/* from the 'collength' column values stored in 'syscolumns'.           */

#define dcmax( size ) ( ( ( size ) >> 8 ) & 0x00ff )
#define dcmin( size ) ( ( size ) & 0x00ff )

/* Calculates the DATETIME & INTERVAL datatype start and end time units */
/* from the 'collength' column values stored in 'syscolumns'.           */

#define tuend( qual ) ( ( qual ) & 0xf )  
#define tustart( qual ) ( ( ( qual ) >>4 ) & 0xf )

/* Defines the time units for DATATIME and INTERVAL datatypes.          */

#define TU_YEAR          0
#define TU_MONTH         2
#define TU_DAY           4
#define TU_HOUR          6
#define TU_MINUTE        8
#define TU_SECOND       10
#define TU_FRAC1        11
#define TU_FRAC2        12
#define TU_FRAC3        13
#define TU_FRAC4        14
#define TU_FRAC5        15

void process_usr();
void process_tbl();
void process_col();
void process_con();
void process_idx();
void process_view();
void process_syn();
void get_col_type();
void get_col_name(); 
void chk_sql_err(); 
void clip(); 
void usage();

extern char *calloc();
extern char *realloc();
extern char *optarg;
extern int   optind, opterr;

char       **dbs;
int          idx_only, tbl_only, no_dbs;

/**************/
/* Structures */
/**************/

$struct sys_tbls 
{                                          /* Table Information      */
    char   tablename[19];
    char   owner[9];
    long   tabid;
    char   tabtype[2];
    char   locklevel[2];
    int    fextsize;
    int    nextsize;
    long   partnum;
} tbls;

$struct sys_cols 
{                                          /* Column Information     */
    char   colname[19];
    long   tabid;
    int    colno;
    int    coltype;
    int    collength;
    int    colmin;
} cols;

$struct sys_idxs 
{                                          /* Index Information     */
    char   idxname[19];
    char   owner[9];
    long   tabid;
    char   idxtype[2];
    char   clustered[2];
    int    col[16];
} idxs;

$struct sys_cons 
{                                          /* Constraint Information */
    char   conname[19];
    int    col[16];
} cons;

$char sql_txt[1024];


/******************************************************************************
 *  NAME         
 *      main()
 * 
 *  DESCRIPTION 
 *      Calls a routine to parse the command line arguments, verifies a valid
 *      database, and controls the generation of a schema for each table
 *      selected.  
 *
 *  FORMAL INPUTS
 *      argc  -  integer representing the number of command line arguments.
 *      argv  -  command line arguments.
 *
 *  FORMAL OUTPUTS
 *      0     -  status indicating proper execution.
 *      1     -  status indicating an error has occurred.
 *
 *****************************************************************************/

main( argc, argv )
int   argc;
char *argv[];
{
    $char    where_cl[64];
    int      signal_handler(); 

    idx_only = 0;
    tbl_only = 0;
    no_dbs   = 0;

    get_options( argc,argv );              /* Process the command arguments. */

                                           /* Set the interrupt signals.     */
    signal( SIGHUP,  signal_handler );     /*   Hangup                       */
    signal( SIGINT,  signal_handler );     /*   Interrupt                    */
    signal( SIGQUIT, signal_handler );     /*   Quit                         */
    signal( SIGFPE,  signal_handler );     /*   Floating point exception     */
    signal( SIGBUS,  signal_handler );     /*   Bus error                    */
    signal( SIGSEGV, signal_handler );     /*   Segmentation violation       */
    signal( SIGTERM, signal_handler );     /*   Software terminate           */
 
                                           /* Validate the database and      */
    if ( check_db( argv[optind++] ) ) {    /* process the selected tables.   */
        if ( optind < argc ) {
            int i = 0;

            for ( i = optind; i < argc; i++ ) {
                sprintf( where_cl, "WHERE (tabname MATCHES \"%s\") ", argv[i] );
                strcat( where_cl,  "AND (tabid >= 100) AND (tabtype = \"T\")" );
                process_tbl( where_cl );
            }
        }
        else {
            strcpy( where_cl, "WHERE (tabid >= 100) AND (tabtype = \"T\") " );
            process_usr();
            process_tbl( where_cl );
            process_view();
            process_syn();
        }
    }
    exit(0);
}

/******************************************************************************
 *  NAME         
 *      check_db()
 * 
 *  DESCRIPTION 
 *      Checks for a valid OnLine database name and gathers the dbspace 
 *      information.
 *
 *  FORMAL INPUTS
 *      dbname - database name.
 *
 *  FORMAL OUTPUTS
 *      <none>
 *
 *****************************************************************************/
int check_db( dbname )
char dbname[];
{
    int    bld_dbspace();
    $long  part_id;

    sprintf( sql_txt, "database %s", dbname );
    $PREPARE opn_db FROM $sql_txt;     
    $EXECUTE opn_db;

    if ( sqlca.sqlcode != 0 || sqlca.sqlwarn.sqlwarn3 != 'W' ) {
        printf( "OnLine database <%s> not found.\n", dbname );
        return( 0 );
    }
                                      /* Get the 'dbspace' information.     */
    if ( !no_dbs ) {
        if ( !bld_dbspace() ) {
            fprintf( stderr, "ERROR: Cannot execute 'tbstat -d' command.\n" );
            exit(1);
        }
    }                                 /* Select the partition number and    */
                                      /* display the database name.         */

    $SELECT partnum INTO $part_id FROM systables WHERE ( tabid = 1 );

    if ( no_dbs )
        printf( "%s;\n\n", sql_txt );
    else 
        printf("%s;   { in %s }\n\n", sql_txt, dbs[part_dbs(part_id)-1] );

    return( 1 );
}

/******************************************************************************
 *  NAME         
 *      process_usr()
 * 
 *  DESCRIPTION 
 *      Selects and displays the appropriate information from 'sysusers'.
 *
 *  FORMAL INPUTS
 *      <none>
 *
 *  FORMAL OUTPUTS
 *      <none>
 *
 *****************************************************************************/

void process_usr()
{
    $char usr_nam[9], usr_typ[2];
    $int  priority_num;

    char  *get_db_priv();

    $DECLARE usr_curs CURSOR FOR
         SELECT username, usertype, priority
           INTO $usr_nam, $usr_typ, $priority_num
           FROM sysusers;

    $OPEN usr_curs;
    $FETCH usr_curs;
    chk_sql_err( __LINE__ );

    while ( sqlca.sqlcode != SQLNOTFOUND ) {
        clip( usr_nam );
        printf( "grant %-12s to %s;", get_db_priv( usr_typ ), usr_nam );

        if ( priority_num == 9 ) 
            printf( "          { owner of database }\n" );
        else
            printf( "\n" );

        $FETCH NEXT usr_curs;
        chk_sql_err( __LINE__ );

        if ( sqlca.sqlcode == SQLNOTFOUND )
            printf( "\n" );
    }
    $CLOSE usr_curs;
}

/******************************************************************************
 *  NAME         
 *      process_tbl()
 * 
 *  DESCRIPTION 
 *      Selects the appropriate information from 'systables' and calls the
 *      necessary routines for column and index information.
 *
 *  FORMAL INPUTS
 *      where_cl  -  WHERE clause filter for SELECTing systables data.
 *
 *  FORMAL OUTPUTS
 *      <none>
 *
 *****************************************************************************/

void process_tbl( where_cl )
char *where_cl;
{
    char     db_txt[25];
    char     lck_txt[25];

    strcpy( db_txt, " " );
    lck_txt[0] = '\0';

    strcpy( sql_txt, "SELECT tabname, owner, tabid, tabtype, locklevel, " );
    strcat( sql_txt,        "fextsize, nextsize, partnum " );
    strcat( sql_txt, "FROM systables ");
    strcat( sql_txt,  where_cl );
    strcat( sql_txt," ORDER BY tabname");

    $PREPARE tab_stmt FROM $sql_txt;
    $DECLARE tab_curs CURSOR FOR tab_stmt;
    $OPEN tab_curs;

    $FETCH tab_curs 
      INTO $tbls.tablename, $tbls.owner, $tbls.tabid, $tbls.tabtype,
           $tbls.locklevel, $tbls.fextsize, $tbls.nextsize, $tbls.partnum;
    chk_sql_err( __LINE__ );

    if ( sqlca.sqlcode == SQLNOTFOUND )
        printf("{ Table not found : %s }\n", where_cl );

    while ( sqlca.sqlcode != SQLNOTFOUND ) {
        if (!no_dbs) 
            sprintf( db_txt, " in %s ", dbs[part_dbs(tbls.partnum)-1] );

        clip( tbls.tablename );

        if ( !idx_only ) {
	    clip( tbls.owner );
            printf( "create table ""%s"".%s (\n", tbls.owner, 
		    tbls.tablename );
            process_col( tbls.tabid );

            if ( !strcmp( tbls.locklevel, "R" )) {
                strcpy( lck_txt, "lock mode row" );
                printf( ")%sextent size %d next size %d %s;\n\n",
                        db_txt, tbls.fextsize, tbls.nextsize, lck_txt );
            }
            else {
                strcpy( lck_txt, "lock mode page" );
                printf( ")%sextent size %d next size %d %s;\n\n",
                        db_txt, tbls.fextsize, tbls.nextsize, lck_txt );
            }
        }
        if ( !tbl_only )
            process_idx( tbls.tabid, tbls.owner, tbls.tablename );

        $FETCH NEXT tab_curs 
          INTO $tbls.tablename, $tbls.owner, $tbls.tabid, $tbls.tabtype, 
               $tbls.locklevel, $tbls.fextsize, $tbls.nextsize, $tbls.partnum;
        chk_sql_err( __LINE__ );
    }
    $CLOSE tab_curs;
}

/******************************************************************************
 *  NAME         
 *      process_col()
 * 
 *  DESCRIPTION 
 *      Selects and displays the appropriate information from 'syscolumns'.
 *
 *  FORMAL INPUTS
 *      table_id  -  integer representing the table id from 'systables'.
 *
 *  FORMAL OUTPUTS
 *      <none>
 *
 *****************************************************************************/

void process_col( table_id )
$int table_id;
{
    int  exit_loop=0;
    char col_typ[41], null_ok[9];
    
    col_typ[0] = '\0';
    null_ok[0] = '\0';

    $DECLARE col_curs CURSOR FOR
         SELECT colname, colno, coltype, collength, colmin
           INTO $cols.colname, $cols.colno, $cols.coltype, $cols.collength,
                $cols.colmin
           FROM syscolumns 
          WHERE ( tabid = $table_id );

    $OPEN col_curs;
    $FETCH col_curs;
    chk_sql_err( __LINE__ );

    while ( sqlca.sqlcode != SQLNOTFOUND && !exit_loop ) {
        get_col_type( cols.coltype, cols.collength, col_typ, cols.colmin, 
                      null_ok );

        if ( null_ok[0] )
            printf( "%4s%-25s%-20s%10s", "", cols.colname, col_typ, null_ok);
        else
            printf( "%4s%-25s%s", "", cols.colname, col_typ );

        $FETCH NEXT col_curs;
        chk_sql_err( __LINE__ );

        if ( sqlca.sqlcode == SQLNOTFOUND ) {
            process_con( table_id );
            exit_loop=1;
        }
        else 
            printf( ",\n" );
    }
    $CLOSE col_curs;
}

/******************************************************************************
 *  NAME         
 *      process_con()
 * 
 *  DESCRIPTION 
 *      Selects and displays the all constraint information from 
 *      'sysconstraints' for a given table id.
 *
 *  FORMAL INPUTS
 *      table_id    -  integer representing the table id from 'systables'.
 *
 *  FORMAL OUTPUTS
 *      <none>
 *
/*****************************************************************************/

void process_con( table_id )
$int table_id;
{
    $char colname[19];
    
    $DECLARE con_curs CURSOR FOR 
         SELECT constrname, part1, part2, part3, part4, part5, part6, part7, 
                part8, part9, part10, part11, part12, part13, part14, part15, 
                part16
           INTO $cons.conname, $cons.col[0], $cons.col[1], $cons.col[2], 
                $cons.col[3], $cons.col[4], $cons.col[5], $cons.col[6], 
                $cons.col[7], $cons.col[8], $cons.col[9], $cons.col[10], 
                $cons.col[11], $cons.col[12], $cons.col[13], $cons.col[14], 
                $cons.col[15]
           FROM sysconstraints, sysindexes 
          WHERE ( sysconstraints.tabid = $table_id ) AND
                ( sysconstraints.idxname = sysindexes.idxname ) AND
                ( sysconstraints.tabid = sysindexes.tabid );

    $OPEN con_curs;
    $FETCH con_curs;
    chk_sql_err( __LINE__ );

    if ( sqlca.sqlcode == SQLNOTFOUND )
        printf( "\n" );
    else
        printf( ",\n" );

    while ( sqlca.sqlcode != SQLNOTFOUND ) {
        int i = 0;
        clip( cons.conname );

        while ( cons.col[i] ) {
            get_col_name( table_id, abs(cons.col[i]), colname );
            clip( colname );

            if ( i == 0 )
                printf( "    unique ( %s", colname );
            else
                printf( "%13s%s", "", colname );

            i++;
            if ( cons.col[i] )
                printf( ",\n" );
            else
                printf( " )\n%29s%-22s%s", "", "constraint", cons.conname );
        }
        $FETCH NEXT con_curs;

        if ( sqlca.sqlcode == SQLNOTFOUND )
            printf( "\n" );
        else 
            printf( ",\n" );
    }
    $CLOSE con_curs;
}

/******************************************************************************
 *  NAME         
 *      process_idx()
 * 
 *  DESCRIPTION 
 *      Selects and displays the all index information from 'sysindexes' for
 *      a given table id.
 *
 *  FORMAL INPUTS
 *      table_id    -  integer representing the table id from 'systables'.
 *      tablename   -  table name for the processed table.
 *
 *  FORMAL OUTPUTS
 *      <none>
 *
 *****************************************************************************/

void process_idx( table_id, owner, tablename )
$int table_id;
char *owner;
char *tablename;
{
    int i = 0;
    char  uniq[8], clust[9], desc_flg[6];
    $char colname[19];
    
    $DECLARE idx_curs CURSOR FOR 
         SELECT idxname, owner, idxtype, clustered, part1, part2, part3, part4, 
                part5, part6, part7, part8, part9, part10, part11, part12, 
                part13, part14, part15, part16
           INTO $idxs.idxname, $idxs.owner, $idxs.idxtype, $idxs.clustered,
                $idxs.col[0], $idxs.col[1], $idxs.col[2], $idxs.col[3], 
                $idxs.col[4], $idxs.col[5], $idxs.col[6], $idxs.col[7], 
                $idxs.col[8], $idxs.col[9], $idxs.col[10], $idxs.col[11],
                $idxs.col[12], $idxs.col[13], $idxs.col[14], $idxs.col[15]
           FROM sysindexes 
          WHERE ( tabid = $table_id );

    $OPEN idx_curs;
    $FETCH idx_curs;
    chk_sql_err( __LINE__ );

    while ( sqlca.sqlcode != SQLNOTFOUND ) {
        if ( idxs.idxname[0] != ' ' ) {
            int i = 0;
            clip( tablename );
            clip( idxs.idxname );
    
            if ( idxs.idxtype[0] == 'U' )
                strcpy( uniq, " unique" );
            else
                uniq[0] = '\0';

            if ( idxs.clustered[0] == 'C' )
                strcpy( clust, " cluster" );
            else
                clust[0] = '\0';
    
	    clip( owner );
	    clip( idxs.owner );

            printf( "create%s%s index ""%s"".%s on ""%s"".%s (\n",
                     uniq, clust, idxs.owner, idxs.idxname, owner, tablename );
    
            while ( idxs.col[i] ) {
                desc_flg[0] = '\0';
                get_col_name( table_id, abs(idxs.col[i]), colname );
                clip( colname );
    
                if ( idxs.col[i] < 0 )
                    strcpy( desc_flg," desc" );
   
                printf( "    %s%s", colname, desc_flg );
                i++;
                if ( idxs.col[i] )
                    printf( ",\n" );
                else
                    printf( "\n" );
            }
            printf( ");\n\n" );
        }
        $FETCH NEXT idx_curs;
        chk_sql_err( __LINE__ );
    }
    $CLOSE idx_curs;
}

/******************************************************************************
 *  NAME         
 *      process_view()
 * 
 *  DESCRIPTION 
 *      Selects and displays the appropriate information from 'sysviews'.
 *
 *  FORMAL INPUTS
 *      <none>
 *
 *  FORMAL OUTPUTS
 *      <none>
 *
 *****************************************************************************/

void process_view()
{
    $char view_txt[65];
    $int  tab_id;

     int  prev_tab_id;

    $DECLARE view_curs CURSOR FOR
        SELECT sysviews.viewtext, sysviews.tabid
        INTO   $view_txt, $tab_id
        FROM   sysviews
        WHERE  ( sysviews.tabid > 0 );

    $OPEN view_curs;
    $FETCH view_curs;
    chk_sql_err( __LINE__ );
 
    prev_tab_id = tab_id;

    while ( sqlca.sqlcode != SQLNOTFOUND ) {
        printf( "%s", view_txt );

        $FETCH NEXT view_curs;
        chk_sql_err( __LINE__ );

        if ( prev_tab_id != tab_id || sqlca.sqlcode == SQLNOTFOUND ) {
            prev_tab_id = tab_id;
            printf( "\n\n" );
        }
    }
    $CLOSE view_curs;
}

/******************************************************************************
 *  NAME         
 *      process_syn()
 * 
 *  DESCRIPTION 
 *      Selects and displays the appropriate information from 'syssyntable'.
 *
 *  FORMAL INPUTS
 *      <none>
 *
 *  FORMAL OUTPUTS
 *      <none>
 *
 *****************************************************************************/

void process_syn()
{
    $char serv_nam[19], db_nam[19], rmt_tab_nam[19], own_nam[10], syn_nam[19],
          tab_nam [19];
     char serv_db [41], own_tab[41];

    $DECLARE syn_curs CURSOR FOR
        SELECT syssyntable.servername, syssyntable.dbname, syssyntable.tabname,
               syssyntable.owner, a.tabname, b.tabname
        INTO   $serv_nam, $db_nam, $rmt_tab_nam, $own_nam, $syn_nam, $tab_nam
        FROM   syssyntable, systables a, outer systables b
        WHERE  ( a.tabid = syssyntable.tabid ) 
        AND    ( b.tabid = syssyntable.btabid );

    $OPEN syn_curs;
    $FETCH syn_curs;
    chk_sql_err( __LINE__ );

    while ( sqlca.sqlcode != SQLNOTFOUND ) {
        serv_db[0] = '\0';

        if ( rmt_tab_nam[0] == ' ' )       /* indicates local database table  */
            strcpy( own_tab, tab_nam );
        else {                             /* otherwise remote database table */
            clip( db_nam ); 
            clip( own_nam ); 
            clip( tab_nam );
            clip( rmt_tab_nam );

            if ( serv_nam[0] != ' ' ) {
                clip( serv_nam );
                sprintf( serv_db, "%s@%s", db_nam, serv_nam );
            }
            else
                sprintf( serv_db, "%s", db_nam );
            
            sprintf( own_tab, ":\"%s\".%s", own_nam, rmt_tab_nam );
        }
        clip( syn_nam );
        clip( own_tab );

        printf( "create synonym %-18s for %s%s;\n", syn_nam, serv_db, own_tab );

        $FETCH NEXT syn_curs;
        chk_sql_err( __LINE__ );
    }
    $CLOSE syn_curs;
}

/******************************************************************************
 *  NAME         
 *      get_col_typ()
 * 
 *  DESCRIPTION 
 *      Determines the column datatype and 'not null' status.
 *
 *  FORMAL INPUTS
 *      typ  -  integer representing the column type from 'syscolumns'.
 *      len  -  integer representing the column length from 'syscolumns'.
 *
 *  FORMAL OUTPUTS
 *      str_typ -  column type string name.
 *      nulls   -  string holding 'not null' indicator.
 *
 *****************************************************************************/

void get_col_type( typ, len, str_typ, col_min, nulls )
int  typ, len, col_min;
char *str_typ, *nulls;
{
    char *get_tu_nam(), *get_blobspace();

    if (( typ-256 ) >= 0 ) {                /* Disallow NULLS    */
        strcpy( nulls, "not null" );
        typ -= 256;
    }
    else
        nulls[0] = '\0';

    switch( typ ) {
        case SQLCHAR:
            sprintf( str_typ, "char(%d)", len );
            break;
        case SQLSMINT:
            strcpy( str_typ, "smallint" );
            break;
        case SQLINT:
            strcpy( str_typ, "integer" );
            break;
        case SQLFLOAT:
            strcpy( str_typ, "float" );
            break;
        case SQLSMFLOAT:
            strcpy( str_typ, "smallfloat" );
            break;
        case SQLDECIMAL:
            if ( dcmin(len) == 255 ) 
                sprintf( str_typ, "decimal(%d)", dcmax(len) );
            else 
                sprintf( str_typ, "decimal(%d,%d)", dcmax(len), dcmin(len) );
            break;
        case SQLSERIAL:
            strcpy( str_typ, "serial" );
            break;
        case SQLDATE:
            strcpy( str_typ, "date" );
            break;
        case SQLMONEY:
            if ( dcmin(len) == 255 ) 
                sprintf( str_typ, "money(%d)", dcmax(len) );
            else 
                sprintf( str_typ, "money(%d,%d)", dcmax(len), dcmin(len) );
            break;
        case SQLDTIME:
            sprintf( str_typ, "datetime %s to %s", get_tu_nam(0,len), 
                                                   get_tu_nam(1,len) );
            break;
        case SQLBYTES:
            sprintf( str_typ, "byte %s", get_blobspace( col_min ) );
            break;
        case SQLTEXT:
            sprintf( str_typ, "text %s", get_blobspace( col_min ) );
            break;
        case SQLVCHAR:
            if ( vcmin(len)  == 0 )
                sprintf( str_typ, "varchar(%d)", vcmax(len) );
            else
                sprintf( str_typ, "varchar(%d,%d)", vcmax(len), vcmin(len) );
            break;
        case SQLINTERVAL:
            sprintf( str_typ, "interval %s to %s", get_tu_nam(0,len), 
                                                   get_tu_nam(1,len) );
            break;
        default:                                /* Datatype not Supported */
            sprintf( str_typ, "{ type %d unsupported }", typ );
    }
}

/******************************************************************************
 *  NAME         
 *      char *get_blobspace()
 * 
 *  DESCRIPTION 
 *     Determines the blobspace name based on the 'colmin' column within
 *     'syscolumns' for the TEXT and BYTE datatypes.
 *
 *  FORMAL INPUTS
 *     col_min   -  'colmin' value within syscolumns.
 *
 *  FORMAL OUTPUTS
 *     <none>  
 *
 *****************************************************************************/

char *get_blobspace( col_min )
int col_min;
{
    static char tmp_nam[19];
    char *blob_nam;

    if ( col_min == -1 )
        blob_nam = "in table";
    else
        if ( col_min >= 1 ) {
            sprintf( tmp_nam, "in %s", dbs[col_min-1] );
            blob_nam = tmp_nam;
        }
        else 
            blob_nam = "{ in unknown blobspace }"; 

    return( blob_nam );
}

/******************************************************************************
 *  NAME         
 *      char *get_tu_nam()
 * 
 *  DESCRIPTION 
 *     Determines the time unit name based on the 'collength' column within
 *     'syscolumns' for the DATETIME and INTERVAL datatypes.
 *
 *  FORMAL INPUTS
 *     typ   -  specifies starting (0) or ending (1) type.
 *     qual  -  'collength' value from 'syscolumns'.
 *
 *  FORMAL OUTPUTS
 *     <none>  
 *
 *****************************************************************************/

char *get_tu_nam ( typ, qual )
short typ, qual;
{
    char *nam;

    if ( typ == 0 )                   /* starting time unit */
       qual = tustart( qual );
    else                              /* ending time unit   */
       qual = tuend( qual );

    switch( qual ) {
        case TU_YEAR:   nam = "year"        ; break;
        case TU_MONTH:  nam = "month"       ; break;
        case TU_DAY:    nam = "day"         ; break;
        case TU_HOUR:   nam = "hour"        ; break;
        case TU_MINUTE: nam = "minute"      ; break;
        case TU_SECOND: nam = "second"      ; break;
        case TU_FRAC1:  nam = "fraction(1)" ; break;
        case TU_FRAC2:  nam = "fraction(2)" ; break;
        case TU_FRAC3:  nam = "fraction(3)" ; break;
        case TU_FRAC4:  nam = "fraction(4)" ; break;
        case TU_FRAC5:  nam = "fraction(5)" ; break;
        default:        nam = "{ unknown }" ;
    }
    return( nam );
}

/******************************************************************************
 *  NAME         
 *      get_col_name()
 * 
 *  DESCRIPTION 
 *      Selects the column name from 'syscolumns' based on the table id and
 *      column number.
 *
 *  FORMAL INPUTS
 *      table_id  -  integer representing the table id from 'systables'.
 *      col_no    -  integer representing the column number from 'syscolumns'.
 *
 *  FORMAL OUTPUTS
 *      colname   -  string holding the column name.
 *
/*****************************************************************************/

void get_col_name ( table_id, col_no, colname )
$int   table_id, col_no;
$char *colname;
{
    $SELECT colname 
       INTO $colname 
       FROM syscolumns 
      WHERE ( tabid = $table_id and colno = $col_no );

    chk_sql_err( __LINE__ );
}

/******************************************************************************
 *  NAME         
 *      char *get_db_priv()
 * 
 *  DESCRIPTION 
 *     Determines the database priviledge based on the 'usertype' column within
 *     'sysusers'. 
 *
 *  FORMAL INPUTS
 *     user_type   -  'usertype' value within sysusers.
 *
 *  FORMAL OUTPUTS
 *     <none>  
 *
 *****************************************************************************/

char *get_db_priv( user_type )
char *user_type;
{
    char *db_priv, ch;

    ch = *user_type;

    switch( ch ) {
        case 'D' :  db_priv = "dba";          break;
        case 'C' :  db_priv = "connect";      break;
        case 'R' :  db_priv = "resource";     break;
        default  :  db_priv = "{ unknown }";
    }
    return( db_priv );
}

/******************************************************************************
 *  NAME         
 *      bld_dbspace()
 * 
 *  DESCRIPTION 
 *      Allocates memory dynamically to hold the dbspace names for the current
 *      OnLine system.  The dbspace names are extracted from the 'tbstat -d' 
 *      output.  
 *
 *  FORMAL INPUTS
 *      <none>
 *
 *  FORMAL OUTPUTS
 *      <none>
 *
 *****************************************************************************/

int bld_dbspace()
{
    FILE     *tbstat, *popen();
    char      jnk[19], owner[19], dbs_nam[19], blob_nam[19], buf[81];
    int       elem_cnt, sts=1, i=1, cont_loop=1;    
    unsigned  dbs_cnt=1;

    if (( tbstat = popen( "tbstat -d", "r" )) == (FILE *)NULL ) 
        sts--;
    else
    {
        dbs = (char **)calloc( dbs_cnt, sizeof( char * ) );

        while( fgets( buf, 81, tbstat ) != (char *)NULL && cont_loop ) {
            elem_cnt = sscanf( buf, "%s %s %s %s %s %s %s %s %s\n", 
                                     jnk, jnk, jnk, jnk, jnk, jnk, 
                                     owner, dbs_nam, blob_nam );
            if ( i > 5 ) {
                if ( elem_cnt != 4 ) {

                    if ( strcmp( owner, "B" ) == 0 ) 
                        strcpy( dbs_nam, blob_nam );

                    dbs = (char **) realloc( (char *)dbs, 
                                             (dbs_cnt+1)*sizeof( char * ) );

                    dbs[dbs_cnt-1] = (char *) calloc( strlen( dbs_nam )+1,
                                                      sizeof( char ) );
                    strcpy( dbs[dbs_cnt-1], dbs_nam );
                    dbs_cnt++;
                }
                else
                    cont_loop--;
            }
            else
                i++;
        }
        pclose( tbstat );
    }
    return( sts );
} 

/******************************************************************************
 *  NAME         
 *      chk_sql_err()
 * 
 *  DESCRIPTION 
 *      Displays the SQL error number and line number.
 *
 *  FORMAL INPUTS
 *      ln_no  -  integer representing line number of the SQL statement.
 *
 *  FORMAL OUTPUTS
 *      <none>
 *
 *****************************************************************************/

void chk_sql_err( ln_no )
int ln_no;
{
    if( sqlca.sqlcode < 0 ) {
        fprintf( stderr, "ERROR: %d at line # <%d>\n", sqlca.sqlcode, ln_no );
        exit(1);
    }
}

/******************************************************************************
 *  NAME         
 *      clip()
 * 
 *  DESCRIPTION 
 *      Removes all trailing spaces from a string.
 *
 *  FORMAL INPUTS
 *      s  -  character string.
 *
 *  FORMAL OUTPUTS
 *      s  -  clipped character string.
 *
 *****************************************************************************/

void clip( s )
char *s;
{
    while ( *s && *s++ != ' ' );
    if (*s) {
        *s--;
        (*s) = '\0';
    }
}

/******************************************************************************
 *  NAME         
 *      usage()
 * 
 *  DESCRIPTION 
 *      Displays a usage message.  The routine is called when an error is
 *      encountered with the command line arguments.
 *
 *  FORMAL INPUTS
 *      <none>
 *
 *  FORMAL OUTPUTS
 *      <none>
 *
 *****************************************************************************/

void usage()
{
    fprintf(stderr,"Usage: tbschema [-int] <database> [<table 1>...]\n\n");
    fprintf(stderr,"       Options : \n");
    fprintf(stderr,"                 i - display indexes only.\n");
    fprintf(stderr,"                 n - do not print dbspace information.\n");
    fprintf(stderr,"                 t - print only the tables found; ");
    fprintf(stderr,"omitting indexes.\n\n");
    fprintf(stderr,"       <database>  - valid OnLine database\n\n");
    fprintf(stderr,"       <table 1>   - optional table names separated by ");
    fprintf(stderr,"spaces. Wild cards are \n");
    fprintf(stderr,"                     allowed but must be quoted to ");
    fprintf(stderr,"protect them from the shell.\n");
    fprintf(stderr,"                     If no table name(s) are given, all ");
    fprintf(stderr,"the tables will be\n" );
    fprintf(stderr,"                     printed.\n");
    exit(1);
}

/******************************************************************************
 *  NAME         
 *      get_options()
 * 
 *  DESCRIPTION 
 *      Parses the command line arguments.
 *
 *  FORMAL INPUTS
 *      cnt   -  integer representing the number of command line arguments.
 *      args  -  command line arguments.
 *
 *  FORMAL OUTPUTS
 *      <none>
 *
 *****************************************************************************/

int get_options( cnt, args )
int   cnt;
char *args[];
{
    int c;

    if (( cnt == 1 ) || ( cnt == 2 && args[1][0] == '-' ))
        usage();

    if ( args[1][0] == '-' ) {
        opterr = 0;                          /* Disable getopt() reporting. */
    
        while (( c = getopt(cnt,args,"int")) != -1 ) {
            switch( c ) {
                case 'i':                    /* Index only option. */
                    idx_only = 1;
                    if ( tbl_only )
                        usage();
                    break;
                case 'n':                   /* No index option. */
                    no_dbs = 1;
                    break;
                case 't':                   /* Table only option. */
                    tbl_only = 1;
                    if ( idx_only ) 
                        usage();
                    break;
                case '?':                   /* Display usage. */
                    usage();
            }
        }
    }
    return( 1 );
}

/******************************************************************************
 *  NAME         
 *      signal_handler()
 * 
 *  DESCRIPTION 
 *      Gracefully terminates the 'sqlturbo' process upon receiving an 
 *      interrupt signal.
 *
 *  FORMAL INPUTS
 *      <none>
 *
 *  FORMAL OUTPUTS
 *      <none>
 *
 *****************************************************************************/

int signal_handler()	
{
    sqlbreak();           /* Wake the back-end process.      */
    sqlexit();            /* Terminate the back-end process. */

    printf( "\nInterrupt signal received.\n" );
    exit(1);        
}


--=====================_781291392==_
Content-Type: text/plain; charset="us-ascii"

Albert E. Whale              aewhale@access.hky.com
----------------------------------------------------
Systems/Database Consultant
Executive's Monitor, Inc.
1789 S. Braddock Ave, Ste 220
Pittsburgh, PA  15218-1868
(412)243-4600  Fax: (412)243-4830

--=====================_781291392==_--