home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
OS/2 Shareware BBS: 18 REXX
/
18-REXX.zip
/
dbschm.zip
/
DBSCHEMA.CMD
Wrap
OS/2 REXX Batch file
|
1993-09-22
|
16KB
|
513 lines
/**
*** ╔════════════════════════════════════════════════════════════════════╗
*** ║ ║
*** ║ DbSchema v1.1 ║
*** ║ -- Create REXX statements to assist rebuilding a db ║
*** ║ ║
*** ║ ────────────────────────────────────────────────────────────────── ║
*** ║ ║
*** ║ Copyright (c) 1993, Hilbert Computing ║
*** ║ ║
*** ║ ────────────────────────────────────────────────────────────────── ║
*** ║ ║
*** ║ Send any comments to: ║
*** ║ ║
*** ║ Hilbert Computing ║
*** ║ 1022 N. Cooper ║
*** ║ Olathe, KS 66061 ║
*** ║ ║
*** ║ CIS: [73457,365] ║
*** ║ BBS: 913-829-2450 ║
*** ║ ║
*** ╚════════════════════════════════════════════════════════════════════╝
**/
arg inputdb .
if (inputdb = '') | (inputdb = '?') then
do
say "You must specify a database name."
say
say "Usage: DBSCHEMA dbname"
exit
end
/* Initialize */
call LoadFunctions
call StartDatabase inputdb
call WriteHeader
call WriteLoadFunctions
call WriteLoadDatabase inputdb
/* Find out the tables in this database */
call GetTableList
/* Generate the create table statements for each table */
do i = 1 to TableList.0
call Write "/*" left('─── 'TableList.name.i' ', 70, '─') "*/"
call Write
call Write "stmt = 'CREATE TABLE" TableList.Creator.i"."TableList.name.i "',"
call Write " '(',"
call MakeColumns TableList.name.i TableList.Creator.i
/* See if there is a primary key associated with this table and print it */
/* out if there is. */
call MakePrimaryKey TableList.name.i TableList.Creator.i
call Write " ')'"
call WriteExecuteAndCommit
/* Create all of the indexes on this table */
call MakeIndex TableList.name.i
end
call Write "call sqldbs 'stop using database'"
call Write "exit"
exit
/**
*** ┌───────────────────────────────────────────────────────────────────────┐
*** │ Test │
*** └───────────────────────────────────────────────────────────────────────┘
**/
Test: procedure
/**
*** This will stub into the routines to test them.
**/
parse arg Field
say Field
Start = 1
Found = pos("'", Field, Start)
do while Found > 0
Field = insert("'", Field, Found)
Start = Found + 2
Found = pos("'", Field, Start)
end
say Field
return
/**
*** ╔═══════════════════════════════════════════════════════════════════════╗
*** ║ Application Functions ║
*** ╚═══════════════════════════════════════════════════════════════════════╝
**/
GetTableList: procedure expose TableList.
/**
*** This will issue an SQL SELECT statement to get the list of the tables
*** in this database. The list is returned in the TableList global
*** stem variable in standard REXX format.
***
**/
TableList. = ''
SqlQuery = "select name, creator",
"from sysibm.systables",
"where creator <> 'SYSIBM' and creator <> 'QRWSYS'"
call Sql 'PREPARE s1 INTO :sqlda FROM :SqlQuery'
if result <> 0 then
return
call Sql 'DECLARE c1 CURSOR FOR s1'
call Sql 'OPEN c1'
call Sql 'FETCH c1 USING DESCRIPTOR :sqlda'
i = 0
do while (sqlca.sqlcode = 0)
i = i + 1
TableList.name.i = strip(sqlda.1.sqldata)
TableList.creator.i = strip(sqlda.2.sqldata)
call SqlExec 'FETCH c1 USING DESCRIPTOR :sqlda'
end
TableList.0 = i
call SQLEXEC 'CLOSE c1'
return
MakeColumns: procedure
/**
*** This will issue query the SQLDA for a select on the table and
*** generate the code to create the table.
**/
arg Name Creator .
SqlQuery = "select * from" Name
call Sql 'PREPARE s1 INTO :sqlda FROM :SqlQuery'
if result <> 0 then
return result
do i = 1 to sqlda.sqld
Column = strip(sqlda.i.sqlname)
Type = SqlType(sqlda.i.sqltype, sqlda.i.sqllen)
if i = sqlda.sqld then
call Write " '"left(Column,24) Type"',"
else
call Write " '"left(Column,24) Type",',"
end
return
MakePrimaryKey: procedure
/**
*** This routine will issue a select statement to see if this table has
*** a primary key. If so, it will write the keywords as part of the
*** CREATE TABLE command.
***
*** NOTE: I expected the creator to be the creator of the table, but
*** these appear to be created by SYSIBM, so the SELECT statement
*** accounts for both possibilities.
**/
arg TableName Creator
SqlQuery = "select colnames from sysibm.sysindexes",
"where tbname = '"TableName"'",
"and ((creator = '"Creator"') or (creator='SYSIBM'))",
"and uniquerule = 'P'"
/* "and ((creator = '"Creator"') or (creator='SYSIBM'))", */
/* "and creator = '"Creator"'", */
call Sql 'PREPARE s1 INTO :sqlda FROM :SqlQuery'
if result <> 0 then
return
call Sql 'DECLARE c1 CURSOR FOR s1'
call Sql 'OPEN c1'
call Sql 'FETCH c1 USING DESCRIPTOR :sqlda'
i = 0
do while (sqlca.sqlcode = 0)
i = i + 1
PrimaryKey = substr(strip(sqlda.1.sqldata),2) /* Remove leading '+' */
PrimaryKey = translate(PrimaryKey,',','+') /* Change '+' to ',' */
call SqlExec 'FETCH c1 USING DESCRIPTOR :sqlda'
end
call SQLEXEC 'CLOSE c1'
if i = 0 then
return
if i > 1 then
say "There are more than one primary key for table '"TableName"'."
call Write " ', PRIMARY KEY("PrimaryKey")',"
return
MakeIndex: procedure
/**
*** This routine will issue a select statement to see if this table has
*** a primary key. If so, it will write the keywords as part of the
*** CREATE TABLE command.
**/
arg TableName
SqlQuery = "select name, creator, colnames, uniquerule, colcount",
"from sysibm.sysindexes",
"where tbname = '"TableName"' and uniquerule <> 'P'"
call Sql 'PREPARE s1 INTO :sqlda FROM :SqlQuery'
if result <> 0 then
return
call Sql 'DECLARE c1 CURSOR FOR s1'
call Sql 'OPEN c1'
call Sql 'FETCH c1 USING DESCRIPTOR :sqlda'
i = 0
do while (sqlca.sqlcode = 0)
i = i + 1
Name = strip(sqlda.1.sqldata)
Creator = strip(sqlda.2.sqldata)
ColNames = strip(sqlda.3.sqldata)
Unique = strip(sqlda.4.sqldata)
Count = strip(sqlda.5.sqldata)
Columns = DecodeColumnNames(ColNames, Count)
/* Determine if this is a unique column */
if Unique = 'U' then
UniqueKeyword = " UNIQUE"
else
UniqueKeyword = ""
call Write "stmt = 'CREATE"UniqueKeyword "INDEX',"
call Write " '"Creator"."Name "ON" TableName"',"
call Write " '"Columns"'"
call Write
call WriteExecuteAndCommit
call SqlExec 'FETCH c1 USING DESCRIPTOR :sqlda'
end
call SQLEXEC 'CLOSE c1'
return
SqlType: procedure
arg SqlType, SqlSize
select
when SqlType = 384 then DataType = "DATE NOT NULL"
when SqlType = 385 then DataType = "DATE"
when SqlType = 388 then DataType = "TIME NOT NULL"
when SqlType = 389 then DataType = "TIME"
when SqlType = 392 then DataType = "TIMESTAMP NOT NULL"
when SqlType = 393 then DataType = "TIMESTAMP"
when SqlType = 448 then DataType = "VARCHAR("SqlSize") NOT NULL"
when SqlType = 449 then DataType = "VARCHAR("SqlSize")"
when SqlType = 452 then DataType = "CHAR("SqlSize") NOT NULL"
when SqlType = 453 then DataType = "CHAR("SqlSize")"
when SqlType = 456 then DataType = "LONG VARCHAR("SqlSize") NOT NULL"
when SqlType = 457 then DataType = "LONG VARCHAR("SqlSize")"
when SqlType = 464 then DataType = "VARGRAPH("SqlSize") NOT NULL"
when SqlType = 465 then DataType = "VARGRAPH("SqlSize")"
when SqlType = 468 then DataType = "GRAPH("SqlSize") NOT NULL"
when SqlType = 469 then DataType = "GRAPH("SqlSize")"
when SqlType = 472 then DataType = "LONGVARG("SqlSize") NOT NULL"
when SqlType = 473 then DataType = "LONGVARG("SqlSize")"
when SqlType = 480 then DataType = "FLOAT("SqlSize") NOT NULL"
when SqlType = 481 then DataType = "FLOAT("SqlSize")"
when SqlType = 484 then DataType = "DECIMAL("SqlSize") NOT NULL"
when SqlType = 485 then DataType = "DECIMAL("SqlSize")"
when SqlType = 496 then DataType = "INTEGER NOT NULL"
when SqlType = 497 then DataType = "INTEGER"
when SqlType = 500 then DataType = "SMALLINT NOT NULL"
when SqlType = 501 then DataType = "SMALLINT"
otherwise
DataType = "UNKNOWN["SqlType"]"
end /* select */
return DataType
DecodeColumnNames: procedure
/**
*** This will convert the COLNAMES column in SYSINDEXES to the syntax
*** required by the CREATE INDEX command.
**/
arg Names, Count
Columns = '('
/* Check for ascending or descending key */
if left(Names,1) = '-' then
order = 'DESC'
else
order = 'ASC'
Names = substr(Names,2)
do (Count - 1)
psn = verify(Names, '-+', 'Match')
NextColumn = left(Names, (psn-1))
Names = substr(Names, psn)
Columns = Columns NextColumn order','
/* Check for ascending or descending key */
if left(Names,1) = '-' then
order = 'DESC'
else
order = 'ASC'
Names = substr(Names,2)
end
Columns = Columns Names order ')'
return Columns
/**
*** ╔═══════════════════════════════════════════════════════════════════════╗
*** ║ Database Manager Functions ║
*** ╚═══════════════════════════════════════════════════════════════════════╝
**/
StartDatabase: procedure
/**
*** This will start the Database Manager and open the database with
*** the name that was passed
**/
arg Database
call sqldbs 'start database manager'
if Database <> '' then
call sqldbs 'start using database' Database
return
Sql:
/**
*** This will issue the SqlExec API call and check the return codes and
*** results. It will terminate on error.
**/
arg SqlCommand
call SqlExec SqlCommand
if result <> 0 then
say "Return code ["result"] from SQLEXEC. You are probably out-of-storage."
else
select
when SQLCA.SQLCODE = 0 then nop /* Ok */
when SQLCA.SQLCODE = 100 then nop /* Not found. */
otherwise
say "SQL code ("SQLCA.SQLCODE"):" SQLCA.SQLMSG
end /* select */
return SQLCA.SQLCODE
/**
*** ╔═══════════════════════════════════════════════════════════════════════╗
*** ║ Misc Functions ║
*** ╚═══════════════════════════════════════════════════════════════════════╝
**/
Write: procedure
/**
*** This will write the line to the command file
**/
parse arg line
say line
return
WriteHeader: procedure
/**
*** Write the static header information
**/
call Write "/** ╔═══════════════════════════════════════════════════════════════════════╗"
call Write "*** ║ ║"
call Write "*** ║ This code was generated by the DBSCHEMA v1.1 command. ║"
call Write "*** ║ ║"
call Write "*** ╚═══════════════════════════════════════════════════════════════════════╝"
call Write "**/"
call Write
return
WriteLoadFunctions: procedure
/**
*** Write the code to load the DLLs to the generated REXX file
**/
call Write "/* Load the Database Manager DLL */"
call Write
call Write "if RxFuncQuery('SQLEXEC') <> 0 then "
call Write " do "
call Write " RCode = RxFuncAdd('SQLEXEC','SQLAR','SQLEXEC') "
call Write " if RCode <> 0 then "
call Write " call Error 1001,0,RCode "
call Write " end "
call Write " "
call Write "if RxFuncQuery('SQLDBS') <> 0 then "
call Write " do "
call Write " RCode = RxFuncAdd('SQLDBS', 'SQLAR', 'SQLDBS') "
call Write " if RCode <> 0 then "
call Write " call Error 1001,0,RCode "
call Write " end "
call Write
return
WriteLoadDatabase: procedure
/**
*** This will write the statements to start the database manager
*** and start using the database
**/
parse arg Database
call Write "/* Start the Database Manager and start using the database */"
call Write
call Write "call sqldbs 'start database manager'"
call Write "call sqldbs 'start using database" Database"'"
call Write
return
WriteExecuteAndCommit: procedure
/**
*** This will write the statements for the EXECUTE IMMEDIATE and
*** COMMIT SQL.
**/
call Write "call sqlexec 'EXECUTE IMMEDIATE :stmt'"
call Write "if sqlca.sqlcode = 0 then"
call Write " say 'Ok.'"
call Write "else"
call Write " say 'SQL message: ['sqlca.sqlcode']' sqlca.sqlmsg"
call Write
call Write "SqlCommit = 'COMMIT'"
call Write "call SqlExec 'EXECUTE IMMEDIATE :SqlCommit'"
call Write
return
LoadFunctions: procedure
/**
*** This will load the REXX entry points for manipulating the OS/2 ES
*** Database Manager
**/
if RxFuncQuery('SQLEXEC') <> 0 then
do
RCode = RxFuncAdd('SQLEXEC','SQLAR','SQLEXEC')
if RCode <> 0 then
do
say "Return code ["RCode"] from RxFuncAdd for SQLEXEC"
exit RCode
end
end
if RxFuncQuery('SQLDBS') <> 0 then
do
RCode = RxFuncAdd('SQLDBS', 'SQLAR', 'SQLDBS')
if RCode <> 0 then
do
say "Return code ["RCode"] from RxFuncAdd for SQLEXEC"
exit RCode
end
end
if RxFuncQuery('SysLoadFuncs') <> 0 then
do
RCode = RxFuncAdd('SysLoadFuncs', 'RexxUtil', 'SysLoadFuncs')
if RCode <> 0 then
do
say "Return code ["RCode"] from RxFuncAdd for SysLoadFuncs"
exit RCode
end
end
/* Load the rest of the entry points */
call SysLoadFuncs
return