Listing 1: create.table
#!/bin/sh
#-----------------------------------------------------------------------------#
#- Author of this Program: William Genosa -#
#- Program Name and Release: @(#)create.table 1.7 -#
#- File Name Used by SCCS: s.create.table -#
#- Tested on Sybase Versions: 4.2 and 4.92 for AIX -#
#- Last Date of Modification: 94/07/18 -#
#- -#
#- Description: -#
#- This program will retrieve table information from the master database and -#
#- generate the sql required to re-create the table passed as the first -#
#- augment to this script. -#
#- -#
#- This program is broken into six sections. -#
#- -#
#- Section 1 - Set up environment variables. -#
#- Section 2 - Make sure we have a table name as the first augment. -#
#- Section 3 - Creates a temporary script to add correct syntax. -#
#- Section 4 - Retrieve the table information from the database. -#
#- Section 5 - Executes the script for syntax and output formatting. -#
#- Section 6 - Clean up temporary files used. -#
#- -#
#- This program is run in the bourne shell to remain compatable across unix -#
#- platforms. Final output of this program will appear as shown below, once -#
#- it has been checked into sccs. If you do not wish to use sccs, then omit -#
#- section of code which creates the header. -#
#- -#
#- /******************************************************************* -#
#- Program Name and Release: @(#)syssegments.tab 1.1 -#
#- File Name Used by SCCS: s.syssegments.tab -#
#- Last Date of Modification: 94/07/18 -#
#- *******************************************************************/ -#
#- CREATE TABLE syssegments ( -#
#- segment smallint Not Null, -#
#- name sysname Not Null, -#
#- status intn Null -#
#- ) -#
#- ON system -#
#- go -#
#- /******************************* END ******************************/ -#
#------------------------------ Section 1 ------------------------------------#
#- In this section we assign our environment variables. Because this program -#
#- may be one of several programs which is to be executed using the same -#
#- variable assignments, provisions have been made for variable assignments -#
#- to be done within this program or via an external file. The script -#
#- generates sql that would re-create the table passed as the first augment. -#
#- Other programs which could create segments, indexes, rules, triggers, -#
#- stored procedures, defaults, and databases may read from the same CONFIG -#
#- file for variable assignment. Then only one CONFIG file would need -#
#- modification instead of editing each program individually. Only variables -#
#- which would be considered global are to be kept in the file assigned to -#
#- the variable CONFIG. Notice the use of the dot command for exporting -#
#- variables in the CONFIG file. -#
#-----------------------------------------------------------------------------#
CONFIG=/home/bill/.config #- Global variable configuration file. -#
if [ -f "${CONFIG}" ] #- If the global variable configuration -#
then #- exists, then use it. Otherwise set up -#
. ${CONFIG} #- the variables locally in this program. -#
else
SYBASE=/home/sybase #- The sybase directory structure location. -#
DSQUERY=CLIENT #- Locates the server in interface file. -#
LOGIN=sa #- Sybase sa login. -#
PASSWORD=guessme #- The password for sa. -#
SERVER=CLIENT #- The Sybase SQL Server to access. -#
DATABASE=accounts #- The database to access. -#
export SYBASE DSQUERY LOGIN PASSWORD SERVER DATABASE
fi
PROGRAM=/tmp/program
QUERYOUT=/tmp/queryout
SQL=$1.tab
export PROGRAM QUERYOUT SQL
#------------------------------ Section 2 ------------------------------------#
#- Make sure a table name has been passed as the first augment. -#
#-----------------------------------------------------------------------------#
if [ "$#" -ne "1" ]
then
echo "usage $0: <tablename>"
exit
fi
#------------------------------ Section 3 ------------------------------------#
#- This section creates a PROGRAM which will be executed in section 5. This -#
#- script will perform two tasks. One task will be to place commas on each -#
#- line where syntax requires them. The second task is to format the output -#
#- for proper appearance and style. The script takes input from the file -#
#- saved in the variable QUERYOUT. That file is populated in section 4. The -#
#- number of lines in QUERYOUT is calculated and assigned to the variable -#
#- LINES. We need to know how many lines there are so we can place the -#
#- commas in the right places. The fifth line up from the bottom will not -#
#- require a comma. This script reads each line of QUERYOUT testing for the -#
#- word Null and testing for the fifth line from bottom. The chmod sets -#
#- execute permission on the script. This script is only needed in section 5 -#
#- and is removed in section 6. The input file QUERYOUT, will be in the -#
#- format below. -#
#- -#
#- /******************************************************************* -#
#- Program Name and Release: ?W? -#
#- File Name Used by SCCS: ?F? ?Y? -#
#- Last Date of Modification: ?D? ?Q? -#
#- *******************************************************************/ -#
#- CREATE TABLE syssegments ( -#
#- segment smallint Not Null -#
#- name sysname Not Null -#
#- status intn Null -#
#- ) -#
#- ON system -#
#- go -#
#- /******************************* END ******************************/ -#
#- -#
#-----------------------------------------------------------------------------#
echo "QUERYOUT=/tmp/queryout
LINES=\`cat \${QUERYOUT} | wc -l\`
COUNT=1
LAST=\`echo \"\${LINES} - 5\" | bc\`
while read line
do
if echo \"\${line}\" | grep -v grep | grep Null >/dev/null
then
if [ \"\${COUNT}\" -eq \"\${LAST}\" ]
then
echo \"\${line}\" | \\
awk '{
if ( NF == 3 ) {
printf ( \"%21s%15s%15s\\\n\", \$1, \$2, \$3 )
}
if ( NF == 4 ) {
printf ( \"%21s%15s%10s %s\\\n\", \$1, \$2, \$3, \$4 )
}
}'
COUNT=\`echo \"\${COUNT} +1\" | bc\`
else
echo \"\${line}\" | \\
awk '{
if ( NF == 3 ) {
printf ( \"%21s%15s%15s,\\\n\", \$1, \$2, \$3 )
}
if ( NF == 4 ) {
printf ( \"%21s%15s%10s %s,\\\n\", \$1, \$2, \$3, \$4 )
}
}'
COUNT=\`echo \"\${COUNT} +1\" | bc\`
fi
else
echo \"\${line}\"
COUNT=\`echo \"\${COUNT} +1\" | bc\`
fi
done < \${QUERYOUT}" > ${PROGRAM}
chmod 700 ${PROGRAM}
#------------------------------ Section 4 ------------------------------------#
#- The query below is actually made up of two queries, and inner query and -#
#- an outer query. The inner query is required to retrieve the object id of -#
#- the table named in $1, the first augment. The outer query joins the -#
#- tables SYSCOLUMNS, SYSTYPES, SYSINDEXES, and SYSSEGMENTS. The tables -#
#- SYSCOLUMNS and SYSTYPES define column information such as the name, -#
#- datatype, data length, and whether or not that column can contain a Null. -#
#- The tables SYSINDEXES and SYSSEGMENTS define the segment that this table -#
#- should be placed on. The id of SYSCOLUMNS is joined with the id in -#
#- SYSINDEXES where the object is either a table or a clustered index. The -#
#- segment of SYSINDEXES is joined with the segment from SYSSEGMENTS, and -#
#- usertype of SYSCOLUMNS is joined with the usertype of SYSTYPES. The -#
#- output is ordered by column id so this script will create the columns in -#
#- the same order as the original table. This query is performed using the -#
#- client isql. The output of the query is piped into awk for processing. -#
#- Awk creates the header along with sccs keyword identifiers and the sql -#
#- create command. Once all the columns retrived from the database have -#
#- been processed, segment information is appended to the end of the script -#
#- along with the keyword go, which is the sql terminator. -#
#------------------------------------------------------------------------------
echo "select Column_name = c.name,
Data_Type = t.name,
Length = c.length,
Nulls = convert(bit, (c.status & 8)),
Segment = s.name
from ${DATABASE}..syscolumns c,
${DATABASE}..systypes t,
${DATABASE}..sysindexes i,
${DATABASE}..syssegments s
where c.id = (select id from ${DATABASE}..sysobjects where name = '$1')
and c.usertype = t.usertype
and i.segment = s.segment
and i.id = c.id
and i.indid < 2
order by c.colid
go" | isql -U${LOGIN} -P${PASSWORD} -S${SERVER} | \
awk '
BEGIN {
print "/*******************************************************************"
print "Program Name and Release: ?W?"
print "File Name Used by SCCS: ?F? ?Y?"
print "Last Date of Modification: ?D? ?Q?"
print "*******************************************************************/"
print "CREATE TABLE '$1' (" }
{ # Begin awk
if ( $4 == 0 && $2 ~ /char/ ) {
printf ( "%21s\t%10s%s\t%10s\n", $1, $2,"("$3")", "Not Null" )
}
else {
if ( $4 == 0 ) {
printf ( "%21s\t%13s\t%10s\n", $1, $2, "Not Null" )
}
}
if ( $4 == 1 && $2 == "char" ) {
printf ( "%21s\t%10s%s\t%10s\n", $1, $2, "("$3")", "Null" )
}
else {
if ( $4 == 1 ) {
printf ( "%21s\t%13s\t%10s\n", $1, $2, "Null" )
}
}
if ( NF == 1 ) { SEGMENT = $1 }
} END {
printf ( "%s\n%s %s\n%s\n", ")", "ON", SEGMENT, "go" )
print "/******************************* END ******************************/\n"
}' > ${QUERYOUT}
#------------------------------ Section 5 ------------------------------------#
#- The output file assigned to the variable SQL is truncated and the script -#
#- created in section 3 and saved in the variable PROGRAM is now executed. -#
#- The output of PROGRAM is filtered to replace question marks with percent -#
#- signs. These percent signs are used for the sccs keyword identifiers. The -#
#- idea here is to prevent the expansion of sccs keywords when this script -#
#- create.table, is checked into sccs. When a table not is placed on a -#
#- specific segment it is automatically placed on the default segment. The -#
#- keyword default, is implied and will cause an error if specified within -#
#- the create table statement. -#
#-----------------------------------------------------------------------------#
> ${SQL}
${PROGRAM} | sed "s/?/%/g" | grep -v "ON default" > ${SQL}
#------------------------------ Section 6 ------------------------------------#
#- The files assigned to PROGRAM and QUERYOUT are no longer needed. System -#
#- Administrators should appreciate the removal of files no longer needed. -#
#-----------------------------------------------------------------------------#
rm -f ${QUERYOUT}
rm -f ${PROGRAM}
#----------------------------------END----------------------------------------#
|