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==_--