Listing 1: Sybaccess
1 #-----------------------------------------------------------------------
2 # Sybaccess
3 # Author of this Program: Ed Schaefer
4 # Program Name and Release: Sybaccess
5 # Last Date of Modification: 01/27/95
6 # Description:
7 # This program serves as a menu system to Sybases's Interactive SQL.
8 # Among the options are choosing and displaying a current database,
9 # executing SQL queries, displaying current table statistics, and
10 # performing certain administration functions
11 #
12 trap "" 2 15 # No user interrupt allowed
13 SYBASE=/usr/sybase; export SYBASE
14 CONFIG=./config; export CONFIG # configuration file
15 GO=\;; export GO
16 CLEAR=`tput clear`; export CLEAR
17 PAGER=pg; export PAGER
18 EDITOR=vi; export EDITOR
19 TMP=/tmp; export TMP
20 TEE=tee; export TEE
21 PR="pr -t -8h"; export PR
22 TABLE=TEM; export TABLE
23 edit_file=$TMP/edit_file.$$ # Temporary files
24 run_file=$TMP/run_file.$$
25 out_file=output.$$
26
27 while [ "$#" -gt 0 ]
28 do # set the command line arguments
29 case $1 in
30 -D) shift;DATABASE=$1 # Database Name
31 shift
32 ;;
33 -D*) DATABASE=`echo $1|cut -c3-`
34 shift
35 ;;
36 -U) shift;USER=$1 # User Name
37 shift
38 ;;
39 -U*) USER=`echo $1|cut -c3-`
40 shift
41 ;;
42 -P) shift;PASSWORD=$1 # User Password
43 shift
44 ;;
45 -P*) PASSWORD=`echo $1|cut -c3-`
46 shift
47 ;;
48 -S) shift;SERVER=$1 # Optional Server
49 shift
50 ;;
51 -S*) SERVER=`echo $1|cut -c3-`
52 shift
53 ;;
54 -I) shift;SYBASE=$1
55 shift
56 ;;
57 -I*) SYBASE=`echo $1|cut -c3-` # Sybase directory location
58 shift
59 ;;
60 *) echo "Illegal Command-line Option"
61 exit
62 ;;
63 esac
64 done
65 if [ x"$USER" = x ] # Check for valid defaults
66 then # if undefined, get USER from config file
67 if [ -r "$CONFIG" ]
68 then # does configuration file exist
69 USER=`grep USER $CONFIG|cut -d : -f2`; export USER;
70 fi
71 if [ x"$USER" = x ]
72 then # USER still undefined?
73 echo "User is undefined; Exiting!"
74 exit
75 fi
76 fi
77 if [ y"$PASSWORD" = y ]
78 then # if undefined, get PASSWORD from config file
79 if [ -r "$CONFIG" ]
80 then # does configuration file exist
81 PASSWORD=`grep PASSWORD $CONFIG|cut -d : -f2`; export PASSWORD;
82 fi
83 if [ y"$PASSWORD" = y ]
84 then # PASSWORD still undefined?
85 echo "Password is undefined; Exiting!"
86 exit
87 fi
88 fi
89 if [ z"$DATABASE" = z ]
90 then # if undefined, get DATABASE from config file
91 if [ -r "$CONFIG" ]
92 then # does configuration file exist
93 DATABASE=`grep DATABASE $CONFIG|cut -d : -f2`; export DATABASE;
94 fi
95 fi
96 # if return value is non-null, some error messsage occurred
97 # so terminate
98 arg_check=`$SYBASE/bin/isql -c$GO -U$USER -P$PASSWORD -S$SERVER << !@!
99 !@!` # terminate isql
100 if [ "$arg_check" != "" ]
101 then # returned an error message
102 echo "Sybase Login failed! Illegal User or Password. Exiting!"
103 sleep 2
104 exit
105 fi
106
107 # Function Declaration Section
108 # This function displays the Sybaccess Main Menu
109 display_main ()
110 {
111 echo "
112
113
114 SYBACCESS MAIN MENU
115
116 D. Database select - Select the current database
117 Q. Query language - Enter the query language menu
118 T. Table information - Enter the table information menu
119 A. Administration - Perform administration functions
120 E. Exit program - Leave this program
121
122 Enter a choice \c"
123 }
124
125 # This function displays the current database
126 display_db () {
127 echo "-------------------------" $DATABASE "-------------------------"
128 }
129 # This function attempts to open a database by echoing the command
130 # 'use database' to ISQL. If the return value is not null,
131 # an error message occured and the database was not
132 # opened.
133 open_db () {
134 if [ x"$DATABASE" = x ]
135 then
136 DATABASE="NO DATABASE"
137 else # check whether the database can be opened
138 db_name=`echo "use $DATABASE\n$GO" \
139 | $SYBASE/bin/isql -c$GO -U$USER -P$PASSWORD -S$SERVER`
140 if [ "$db_name" != "" ]
141 then # returned an error message, Could NOT open database
142 echo "Can NOT open database"
143 sleep 2
144 DATABASE="NO DATABASE"
145 fi
146 fi
147 }
148
149 # This function echos the string 'use database', the contents of the edit
150 # file and the GO command to a file. The resulting file is piped to ISQL
151 # with the results being sent to an output file and to the pager utility.
152 run_edit () {
153 if [ -f "$edit_file" -a -s "$edit_file" ]
154 then
155 echo "use $DATABASE\n$GO" > $run_file
156 cat $edit_file >> $run_file
157 echo "\n$GO" >> $run_file
158 cat $run_file|$SYBASE/bin/isql -c$GO -U$USER -P$PASSWORD -S$SERVER|
159 $TEE $out_file |$PAGER
160 else
161 echo "Nothing in buffer to run"
162 sleep 1
163 fi
164 }
165
166 # This function displays the Administration Menu.
167 display_admin () {
168 echo "
169
170
171 ADMINISTRATION MENU
172
173 C. Choose - choose a current table
174 U. Unload - Unload table: $TABLE
175 L. Load - Load table: $TABLE
176 S. Update Statistics
177 E. Return - return to main menu
178
179 Enter a choice \c"
180 }
181
182 # This function displays the Table Menu.
183 display_table () {
184 echo "
185
186
187 TABLE MENU
188
189 C. Choose - choose a current table
190 N. No of records in: $TABLE
191 I. Indexes on $TABLE
192 S. Columns on $TABLE
193 E. Return - return to main menu
194
195 Enter a choice \c"
196 }
197 # This function displays the Query Menu.
198 display_sql () {
199 echo "
200
202 QUERY MENU
202
203 N. New - create new SQL statements in run file
204 M. Modify - modify SQL statements in run file
205 V. View - view the SQL statements in run file
206 L. Load - load the SQL statements into an external file
207 R. Run - run SQL statments
208 S. Save - save the SQL statements to an external file
209 E. Return - return to main menu
210
211 Enter a choice \c"
212 }
213 # This function removes the old edit file and calls EDITOR
214 # (normally vi) to create a new edit file.
215 new_edit () {
216 if [ -f $edit_file ]
217 then
218 rm $edit_file
219 fi
220 $EDITOR $edit_file
221 }
222
223 # This function modifies an existing edit file.
224 modify_edit () {
225 $EDITOR $edit_file
226 }
227
228 # This function views an existing edit file.
229 view_edit () {
230 if [ -f "$edit_file" -a -s "$edit_file" ]
231 then
232 $PAGER $edit_file
233 else
234 echo "Nothing in buffer to view"
235 sleep 1
236 fi
237 }
238
239 # This function prompts for an existing file. The contents of this
240 # file then becomes the new edit file.
241 load_edit () {
242 echo "Enter file name to load: \c"
243 read load_name
244 if [ -r "$load_name" ]
245 then
246 cp $load_name $edit_file
247 echo "Loading "$load_name" into edit file"
248 sleep 2
249 else
250 echo "File name: "$load_name" does NOT exist!"
251 sleep 3
252 fi
253 }
254
255 # This function prompts for a save file name. The contents of
256 # edit file then becomes this save file.
257 save_edit () {
258 if [ -f "$edit_file" -a -s "$edit_file" ]
259 then
260 echo "Enter file name: \c"
261 read file_name
262 if [ -r "$file_name" ]
263 then
264 echo "File name exists!"
265 else
266 cp $edit_file $file_name
267 echo "File copied!"
268 fi
269 sleep 1
270 else
271 echo "Nothing in edit file to save"
272 sleep 1
273 fi
274 }
275 # This function:
276 # 1) Prompts for a valid database if there isn't and continues
277 # only if a valid database is entered
278 # 2) Displays the Administration Menu and prompts for a choice.
279 # The choices are select another TABLE, update statistics for the
280 # database, load a TABLE from ascii file, $TABLE.unl, and unload TABLE
281 # to ascii file, $TABLE.unl.
282 do_admin () {
283 while true
284 do
285 if [ "$DATABASE" = "NO DATABASE" ]
286 then # you need a valid database to query
287 echo "Database select: \c"
288 read DATABASE
289 open_db
290 fi
291 if [ "$DATABASE" = "NO DATABASE" ]
292 then
293 return
294 fi
295 echo $CLEAR
296 display_db
297 display_admin
298 read q_choice
299 case $q_choice
300 in
301 C|c) select_table;;
302 U|u) echo "Unloading "$TABLE
303 tab_name=$TMP/$TABLE.unl
304 $SYBASE/bin/bcp $DATABASE..$TABLE out $tab_name \
305 -U$USER -P$PASSWORD -S$SERVER -c -t\|
306 sleep 3;;
307 L|l) echo "Loading "$TABLE
308 tab_name=$TMP/$TABLE.unl
309 $SYBASE/bin/bcp $DATABASE..$TABLE in $tab_name \
310 -U$USER -P$PASSWORD -S$SERVER -c -t\|
311 sleep 3;;
312 S|s) echo "Updating Statistics - Please wait"
313 do_statistics;;
314 E|e) return;;
315 *) echo "Bad Choice!"
316 sleep 2;;
317 esac
318 done
319 }
320 # This function:
321 # 1) Prompts for a valid database if there isn't and
322 # continues only if a valid database is entered
323 # 2) Displays the Table Menu and prompts for a choice. The
324 # choices are select another TABLE, find out how many records are in
325 # the table, and call the create.table and create.index shell scripts
326 # to display the table structure and columns respectively.
327 do_table () {
328 while true
329 do
330 if [ "$DATABASE" = "NO DATABASE" ]
331 then # you need a valid database to query
332 echo "Database select: \c"
333 read DATABASE
334 open_db
335 fi
336 if [ "$DATABASE" = "NO DATABASE" ]
337 then
338 return
339 fi
340 echo $CLEAR
341 display_db
342 display_table
343 read q_choice
344 case $q_choice
345 in
346 C|c) select_table;;
347 N|n) cnt_table;;
348 I|i) echo "Processing "$TABLE " index "
349 ./create.index -I$SYBASE -D$DATABASE -U$USER \
350 -P$PASSWORD -T$TABLE;;
351 S|s) echo "Processing "$TABLE " columns "
352 ./create.table -I$SYBASE -D$DATABASE -U$USER \
353 -P$PASSWORD -T$TABLE;;
354 E|e) return;;
355 *) echo "Bad Choice!"
356 sleep 2;;
357 esac
358 done
359 }
360 # This function creates update statistics logic for each user type
361 # table in $DATABASE and echos it to the run_file. The contents of
362 # run_file are then executed.
363 do_statistics () {
364 echo "use $DATABASE\n$GO" > $run_file
365 echo "set nocount on \
366 select \"update statistics \" + name
367 from $DATABASE..sysobjects
368 where type = 'U'\n$GO" \
369 |$SYBASE/bin/isql -c$GO -U$USER -P$PASSWORD -S$SERVER | \
370 grep -v "^ name $" | \
371 grep -v "^ ------------------------------ $" | \
372 grep -v "^$" >> $run_file
373 echo "\n$GO" >> $run_file
374 cat $run_file|$SYBASE/bin/isql -c$GO -U$USER -P$PASSWORD -S$SERVER
375 echo "statistics updated for DATABASE: "$DATABASE
376 sleep 2
377 }
378 # This function displays the Query Menu and prompts the user
379 # to enter an edit command for the edit file or run the edit file.
380 do_query () {
381 while true
382 do
383 if [ "$DATABASE" = "NO DATABASE" ]
384 then # you need a valid database to query
385 echo "Database select: \c"
386 read DATABASE
387 open_db
388 fi
389 if [ "$DATABASE" = "NO DATABASE" ]
390 then
391 return
392 fi
393 echo $CLEAR
394 display_db
395 display_sql
396 read q_choice
397 case $q_choice
398 in
399 N|n) new_edit;;
400 M|m) modify_edit;;
401 V|v) view_edit;;
402 L|l) load_edit;;
403 R|r) run_edit;;
404 S|s) save_edit;;
405 E|e) return;;
406 *) echo "Bad Choice!"
407 sleep 2;;
408 esac
409 done
410 }
411 # This function displays all the active user tables in $DATABASE
412 # to the screen and prompts the user to enter a valid table.
413 select_table () {
414 echo $CLEAR
415 echo " TABLES"
416 echo "set nocount on \
417 select name from $DATABASE..sysobjects\n$GO" \
418 |$SYBASE/bin/isql -c$GO -U$USER -P$PASSWORD -S$SERVER | \
419 grep -v "^ name $" | \
420 grep -v "^ ------------------------------ $" | \
421 grep -v "^$" | \
422 grep -v "^ sys*"| # elliminate the system tables
423 $PR|$PAGER
424 echo "
425 Select table: \c"
426 read TABLE
427 }
428
429 # This function determines how many records are in $TABLE.
430 cnt_table () {
431 echo $CLEAR
432 echo "set nocount on \
433 select 'Number of records in $TABLE: ', count(*)
434 from $DATABASE..$TABLE\n$GO" \
435 |$SYBASE/bin/isql -c$GO -U$USER -P$PASSWORD -S$SERVER | \
436 grep -v "^ name $" | \
437 grep -v "^ ------------------------------ $" | \
438 grep -v "^$" |
439 $PAGER
440 }
441
442 # This function displays the active databases in the Sybase
443 # instance by looking at the master's sysdatabases table. The user
444 # is then prompted to enter a valid database. The open_db function
445 # determines if the entered string is correct.
446 select_database () {
447 echo $CLEAR
448 for db_name in `echo "set nocount on
449 \n$GO
450 select name
451 from master..sysdatabases where name != 'master' and name != 'model'
452 and name != 'sybsystemprocs' and name != 'tempdb'\n$GO" \
453 | $SYBASE/bin/isql -c$GO -U$USER -P$PASSWORD -S$SERVER | \
454 grep -v "^ name $" | \
455 grep -v "^ ------------------------------ $" | \
456 grep -v "^$"
457 `
458 do
459 echo $db_name
460 done
461 echo "
462 Database select: \c"
463 read DATABASE
464 open_db
465 }
466 # End Function Declaration Section
467
468 # Main Processing Section
469 open_db # open the database
470 while true
471 do
472 echo $CLEAR
473 display_db
474 display_main
475
476 read main_choice
477 case $main_choice
478 in
479 D|d) select_database;;
480 Q|q) do_query;;
481 T|t) do_table;;
482 A|a) do_admin;;
483 E|e) echo "Leaving SYBACCESS"
484 sleep 1
485 exit;;
486 *) echo "Bad Choice!"
487 sleep 1;;
488 esac
489 done
490
491 # clean up temp files
492 if [ -f "$edit_file" ]
493 then
494 rm $edit_file
495 fi
496 if [ -f "$run_file" ]
497 then
498 rm $run_file
499 fi
|