S H Q L version 1.3 Shql is a program that reads SQL commands interactively and executes those commands by creating and manipulating Unix files. This program requires a bourne shell that understands functions, as well as awk, grep, cut, sort, uniq, join, wc, and sed. This script can be invoked with the command shql [-q] {database name} A directory must be created for the database before you may use it. This directory will house all data files for a single database. All datafiles are created with mode 666 ('rw-rw-rw-'), so create the directory with 777 ('rwxrwxrwx') if you want the database to be sharable, and 700 ('rwx------') to be private. Of course, multiple databases are possible. A database called 'mydb' may be created as a directory $HOME/mydb, $HOME/shql/mydb, ./mydb, or as $SHQL_ROOT/mydb, where $SHQL_ROOT is defined below. The -q option turns off the display of headings so the output of shql can be used by other programs by caputuring all lines that begin the pipe symbol. The program is patterned after Ingres's interactive sql terminal monitor program. Terminal monitor commands begin with either a forward or backward-slash. Forward slashes may appear at the end of a commend line. Back-slashes are accepted for compatability. The /g is the 'go' command, /p is print, and /q is quit. Try 'help commands' for a full list. Because of this, if you need a slash as the second to last caracter on a line, you should add a space between the slash and the last character. To get started, invoke shql with a database name. Use the directory name you created above. Type shql mydb if the directory you created was 'mydb'. Once shql starts up, you should see the database name displayed, and then a '*'. At this point, the most valuable thing is to type help, * help * /g You may then go on. The command 'help syntax' displays syntax for all SQL operations, and 'help commands' displays all shql workspace commands. Try the demo. Shql can execute only one operation at a time, but operations can be spread over several lines. Shql operations are allow 'select' operations on multiple tables. Table names are read from left to write in select's 'from' section, so the tables should be ordered with the most central tables first. In two-table joins, it doesn't matter. In three table joins, if you join table A-to-B and B-to-C, B must not be the last table in the from clause, because shql will not be able to join tables A-C. If you get the message 'Join not found, try reordering tables', this is probably the problem. Also qualified field names are not understood, like tablename.fieldname, so if you are joining my_id in table A with my_id in table B, just say 'my_id = my_id'. Views can also be used to create multi-table selects. Subselects are implemented, but must be the last operand of a 'where' clause, most useful with 'in'. In most cases, commas are optional. NULLs are not implemented. Aggregates like AVG() are implemented, but not with GROUP BY. When INSERTing strings that contain the characters !,*,=,>,<,(, or ), spaces or backslashes may be added during the insert. This is a side-effect of the string manipulation needed to properly parse the command parameters. This SQL is type-less, so specify just the column width when creating tables. This is used only for display purposes. Shql is case-sensitive, and expects SQL key words to be in lower case. Commands can be piped into shql. The table data files are tab delimited, so awk scripts can be used to generate reports directly from the tables. To operate on non-shql data files, create a dummy table with the proper fields, then copy your file into your shql data directory, replacing your delimiters with tabs, then run shql on the table, and convert the table back to its original format. Grave accents (`) may be used to execute unix command from with shql. Environment variables may also be used. See the demo for an example, i.e. "cat demo.shql | shql mydb". If you have comments, suggestions, or bug reports contact: Bruce Momjian, root@candle.pha.pa.us -----BEGIN PGP PUBLIC KEY BLOCK----- Version: 2.6.1 mQBtAy6pceUAAAEDAM9YKKJiqG7AzjLIyvqmDHvjVnmCG0QlhNZm3EdpcbIZBEUJ 41zWuGhvusiC99MeXy43kxSF4pJLFWhLDYRk1unVvz5y3B+xyERhig3h/AWWRaxH J4HzFdeKgclqllqhVQAFEbQlQnJ1Y2UgTW9tamlhbiA8cm9vdEBjYW5kbGUucGhh LnBhLnVzPg== =S8mw -----END PGP PUBLIC KEY BLOCK-----