From af137@torfree.net  Mon Jun  9 22:48:04 1997
Received: by noc.ntua.gr with ESMTP
	id WAA28011 ; Mon, 9 Jun 1997 22:48:03 +0300 (EET DST)
Received: by achilles.noc.ntua.gr via NTUAnet with SMTP
	id WAA02787 ; Mon, 9 Jun 1997 22:49:22 +0300 (EET DST)
Received: from localhost by queen.torfree.net
	(/\==/\ Smail3.1.28.1 #28.6; 16-jun-94)
	via sendmail with stdio id 
	for Y.Adamopoulos@noc.ntua.gr; Mon, 9 Jun 97 15:35 EDT
Date: Mon, 9 Jun 1997 15:35:47 -0400 (EDT)
From: Al Aab 
Subject: a sed sql interpreter (fwd)
Message-ID: 
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Status: O


bravo simon !
how is autumn in australia ?

keep unlurking.
yours is the first sql message that ever arrived at seders.
keep'em coming, mate !

=-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
al aab, seders moderator                                      sed u soon 
               it is not zat we do not see the  s o l u t i o n          
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+

---------- Forwarded message ----------
Date: Mon, 9 Jun 1997 15:38:29 +1000
From: Simon Taylor 
To: Al Aab 
Cc: david.bath@salmat.com.au
Subject: a sed sql interpreter

Al,

I have attached a sed-driven sql interpreter that I am writing
as part of an exercise to delve into sed and RE's a little
further than I have in the past. (The sedders grab-bag www
site, and Greg Ubbens tutorials, were a great resource).

The program currently allows you to list the tables in its
database, describe the columns in a table, select data from 
a table, drop a table and create a table. Additionally there
are help and debug commands.

Before it is really of any use as a sed demo, however, I need 
to finish the insert and update statements (which are not
present in this version), and to add a "delete from" 
statement. I also need to modify the select statement so
that it will select individual columns from a table, and
maybe even support a where clause, who knows...

The 'attached' files are:

    sql.sed:	This is the sed script and will function
			stand-alone, however it needs to be run via
			the "sedql" shell script in order for
			database changes to be permanent.

    sedql:		The unix shell script that rebuilds sql.sed
			each time it is run, so that new tables,
			(and ultimately new data) are remembered
			between invocations of the interpreter.

    demo:		The output of a sample session.

I would appreciate comments and test results, good and bad (!)
from any seders who can help. So far I have tested the 
interpreter on SCO Open Server 5 and Linux only. 

Sorry for this overlong email message, I can send the files
as separate attachments if required.

Regards,


Simon Taylor

--- Attachment 1: "demo" ---
Script started on Mon Jun  9 14:38:07 1997
[simon@SCO_SV]/home/simon/sed-> sedql
sedql: A sed based sql interpreter
list;

List of known tables
--------------------
sqlsed
sport
city

desc sqlsed

Name		Column Type
---------------------------
revision	char (10)
revision	char (20)
date		char (30)

desc sport;

Name		Column Type
---------------------------
name		char (10) not null
desc		char (30)

desc city;

Name		Column Type
---------------------------
city		char (20) not null
state		char (20)
country		char (30)
population	char (10)

select * from city;

Melbourne VIC Australia
Melbourne FL USA
Denver CO USA
Dunedin  Scotland
Dunedin  New Zealand

select * from 
sport;

cricket england
baseball america
golf scotland

select * from     	  sqlsed;

$Revision  1.4 $ $Date  1997/06/09 04 35 47 $

select * from badtable;

Unkown table...

list;

List of known tables
--------------------
sqlsed
sport
city

help

Available commands are:

  create table ... ;
  debug
  desc
  drop table ... ;
  exit
  list | list tables
  quit
  select ... ;

(Note that the commands that are terminated with a ';' may be entered
over any number of lines).
Enter help followed by the command for more info, ie: help create table

help create table

create table tablename ...;
    This is a subset of the SQL create table statement. The syntax is:
        create table tablename
        (
        column datatype [ , column-n datatype ]
        );
    ie:
    create table service_group (
        service_group        char(20) not null,
        cost                 char(8),
        concessional_cost    char(8)
    );

    Note that the following rules apply:

    1. Object names (tables, columns etc) can be from 1 to
       30 characters long, must start with a letter a-z, followed
       by zero or more of 'a-z0-9$_'. No uppercase letters are allowed.

    2. The only datatype accepted at present is "char(n)", 
       ie: char(25). The qualifier "not null" may be applied also.

create table library
(
author char (20),
publisher char (20),
title char(30)
);

Table created...
list;

List of known tables
--------------------
sqlsed
sport
city
library

desc library;

Name		Column Type
---------------------------
author		char(20)
publisher	char(20)
title		char(30)

select * from library;


drop table city;

Table dropped...

list;

List of known tables
--------------------
sqlsed
sport
library

select * from sport;

cricket england
baseball america
golf scotland

debug

~sqlsed=1#revision:char (10)#revision:char (20)#date:char (30)~sqlsed=2\
$Revision: 1.4 $:$Date: 1997/06/09 04:35:47 $#~sqlsed=3~sport=1#name:ch\
ar (10) not null#desc:char (30)~sport=2cricket:england#baseball:america\
#golf:scotland#~sport=3~library=1#author:char(20)#publisher:char(20)#ti\
tle:char(30)~library=2~library=3$

badcommand;

Unknown command...

quit;

[simon@SCO_SV]/home/simon/sed-> exit

script done on Mon Jun  9 14:42:07 1997


--- Attachment 2: "sql.sed" ---
#!/bin/sed -nf
# name:       $RCSfile: sql.sed,v $
# process:    A simple SQL engine - implemented in sed
# author:     Simon Taylor
# revision:   $Id: sql.sed,v 1.4 1997/06/09 04:35:47 simon Exp simon $

#
# Load the hold area with our database when we process line 1
# The tables are delimited as follows (the < and > chars are not 
# actually used):
#   ~table_name=1~table_name=2
~table_name=3 # # The
is defined as: # #column_name:data_type # # The
is defined as: # data for column 1: ..data for column n# # 1{ h s;.*;~sqlsed=1#revision:char (10)#revision:char (20)#date:char (30)~sqlsed=2$Revision: 1.4 $:$Date: 1997/06/09 04:35:47 $#~sqlsed=3~sport=1#name:char (10) not null#desc:char (30)~sport=2cricket:england#baseball:america#golf:scotland#~sport=3~city=1#c ity:char (20) not null#state:char (20)#country:char (30)#population:char (10)~city=2Melbourne:VIC:Australia#Melbourne:FL:USA#Denver:CO:USA#Dunedin::S cotland#Dunedin::New Zealand~city=3; x } # # # Process a quit or exit command. The action here is first to write # the hold area database out to the sql.db file, then to quit sed. # /^[ ]*quit/{ g w sql.db q } /^[ ]*exit/{ g w sql.db q } # # Process the debug command. This lets the user display the holdspace # memory # /^[ ]*debug[ ;]*/{ g;l;b } # # Process the "list" (or "list tables") command. This simply lists the # table names. # /^[ ]*list\([ ]*tables\)\{0,1\}[ ;]*/{ i\ List of known tables\ -------------------- g :list s/~\([a-z][a-z0-9\$_]\{0,30\}\)=1.*\1=3/\1\ / t list p b } # # The Drop table command: # drop table table_name; # /^[ ]*drop[ ]\{1,\}table/{ :drop /;/!{ N b drop } s/\n/@/g s/drop// s/table// s/[ @;]*//g G s/\n// # First ensure that the table is known /^\([a-z][a-z0-9\$_]\{0,30\}\)~.*\1=1/!{ b drop_err } # Remove the tablename, the table definition and any data from the # patternspace s/^\([a-z][a-z0-9\$_]\{0,30\}\)\(.*\)~\1=1.*~\1=3/\2/ h c\ Table dropped... b :drop_err c\ Unkown table... b } # # The Create table command: # create table table_name ( column char(n) [, column char(n) ]* ); # # An object name (ie: a table or column name) is defined as: # [a-z][a-z0-9\$_]\{0,30\} # Note that we use the 'N' command to read in as many lines as are # required before we encounter the ';' that terminates the statement. # Once this is done, we replace all embedded newlines with the '@' # character so that pattern matching across the whole statement may # proceed. # needs to handle not null /^[ ]*create[ ]\{1,\}table/{ :ct /;/!{ N b ct } s/\n/@/g /^[ ]*create[ ]\{1,\}table[ @]\{1,\}\([a-z][a-z0-9\$_]\{0,30\}\)[ @]*([ @]*\([a-z][a-z0-9\$_]\{0,30\}\)[ ]\{1,\}\(char[ ]*([0-9]\{1,3\})\)\(,[ @]*\([a-z][a-z0-9\$_]\{0,30\}\)[ ]\{1,\}\(char[ ]*([0-9]\{1,3\})\)[ @]*\)*[ @]*)[ @]*;/{ # Having isolated the statement, now strip away all but the text that # we need to describe the table internally s/@*//g s/create// s/table// s/;// s/([ ]*/#/ s/,[ ]*/#/g s/[ ]char*/:char/g s/)$// s/[ ]*//g s/\([a-z][a-z0-9\$_]\{0,30\}\)#\(.*\)/~\1=1#\2~\1=2~\1=3/ # Append the hold area with our pattern, exchange the hold and # pattern areas and then remove the embedded newline caused by # the append. The 'h' command then replaces the hold area with # the pattern space. Our new table is now in memory H x s/\ // h c\ Table created... b } c\ Invalid create table statement... b } # # The 'desc table_name' command lists the table description # /^[ ]*desc[ ]\{1,\}[a-z][a-z0-9\$_]\{0,30\}[ ;]*/{ G s/[ ]*desc[ ]\{1,\}\([a-z][a-z0-9\$_]\{0,30\}\)[ ;]*.*~\1=1\([^~]*\)~\1=2.*/Name Column Type\ ---------------------------\2/ s/\(#\)\([a-z0-9\$_]\{1,7\}\):/\ \2 /g s/\(#\)\([a-z0-9\$_]\{8,\}\):/\ \2 /g s/#/\ / p i\ b } # # Process the SQL 'select' command. At present this is restricted to # a simple 'select * from table'. # # select * from table_name; # # An object name (ie: a table or column name) is defined as: # [a-z][a-z0-9\$_]\{0,30\} # Note that we use the 'N' command to read in as many lines as are # required before we encounter the ';' that terminates the statement. # Once this is done, we replace all embedded newlines with the '@' # character so that pattern matching across the whole statement may # proceed. # /^[ ]*select/{ :sel /;/!{ N b sel } # First ensure that the tablename is known G s/\n/@/g /\([a-z][a-z0-9\$_]\{0,30\}\);@.*\1=1/!{ b sel_err } s/^[ ]*select[ @]\{1,\}\*[ @]\{1,\}from[ @]\{1,\}\([a-z][a-z0-9\$_]\{0,30\}\)[ @]*;.*~\1=2\([^~]*\)~\1=3.*/\2/ s/:/ /g s/#/\ /g p b :sel_err c\ Unkown table... b } # # Process the 'help' command. Here we simply output a summary of the # sql.sed commands so that the user knows at least the command names. # He or she can then get more detailed info by entering 'help command' # /^[ ]*help[ ]*[;]\{0,1\}$/{ c\ Available commands are:\ \ create table ... ;\ debug\ desc\ drop table ... ;\ exit\ list | list tables\ quit\ select ... ;\ \ (Note that the commands that are terminated with a ';' may be entered\ over any number of lines).\ Enter help followed by the command for more info, ie: help create table\ b } # # Process the 'help' sub-commands. # /[ ]*help[ ]\{1,\}\([a-z][a-z ]*\)[;]\{0,1\}/{ /create table/{ c\ create table tablename ...;\ This is a subset of the SQL create table statement. The syntax is:\ create table tablename\ (\ column datatype [ , column-n datatype ]\ );\ ie:\ create table service_group (\ service_group char(20) not null,\ cost char(8),\ concessional_cost char(8)\ );\ \ Note that the following rules apply:\ \ 1. Object names (tables, columns etc) can be from 1 to\ 30 characters long, must start with a letter a-z, followed\ by zero or more of 'a-z0-9$_'. No uppercase letters are allowed.\ \ 2. The only datatype accepted at present is "char(n)", \ ie: char(25). The qualifier "not null" may be applied also.\ b } /debug/{ c\ debug\ Displays the contents of the hold area - this is the raw\ table descriptions and any table data.\ b } /desc/{ c\ desc tablename\ Lists the column names and their datatypes for 'tablename'.\ b } /drop table/{ c\ drop table tablename;\ Removes the table (and any data in it) from the database\ b } /exit/{ c\ exit | quit\ Writes the database to sql.db and exits sql.sed.\ b } /list/{ c\ list | list tables\ Provides a simple list of all the tables in the database.\ b } /quit/{ c\ quit | exit\ Writes the database to sql.db and exits sql.sed.\ b } /select/{ c\ select ...;\ This is indeed a subset of the SQL select statement! At this\ revision of sql.sed the syntax is:\ select * from tablename;\ b } } # # When processing the last line of the file (or we see a quit or exit # when running iteractively), write the new database out to the # sql.db file. # ${ g w sql.db } # # If we reach this far, we may have a syntax error... # /./{ c\ Unknown command... } #--------------------------------------------------------------------------- -- # $Log: sql.sed,v $ # Revision 1.4 1997/06/09 04:35:47 simon # Removed a bug in the drop table statement that was removing more than # the table requested. # # Revision 1.3 1997/06/08 12:33:54 simon # Amended object name recognised by the desc command. # # Revision 1.2 1997/06/08 11:00:58 simon # Added the 'drop table' statement, made the select statement function # over one or more lines, added checking for invalid tablename in the # select and drop table commands. Modified database now written to the # sql.db file when the program exits. Added the table 'sqlsed' to the # database to describe the program itself. # # Revision 1.1 1997/06/07 14:05:37 simon # Many amendments to produce the first workable prototype. Added more # detailed help, the "create table", "list" and "debug" statements. # # Revision 1.0 1997/06/04 05:18:46 simon # Initial revision # --- Attachment 3: "sedql" --- #!/bin/sh # name: $RCSfile: sedql,v $ # process: The shell wrapper for the sed SQL script 'sql.sed' # Assumes a unix environment for this wrapper script only. # (and there's nothing here that can't easily be reproduced # on any OS where sed is found...) # author: Simon Taylor # revision: $Id: sedql,v 1.1 1997/06/09 04:26:59 simon Exp $ # Make backup copies of sql.sed and its database file sql.db if [ ! -r sql.sed ] then echo "\nsedql: This script must be run from the directory where" echo "the \"sql.sed\" file resides\n" exit 1 fi /bin/cp sql.sed sql.sed.bu if [ -r sql.db ] then /bin/mv sql.db sql.db.bu fi # Run sql.sed echo "sedql: A sed based sql interpreter" sql.sed # Remake sql.sed with the new database file. sed -e ' /^1{$/{ :db /#/!{ N b db } s/\n/@/g s/.*/1{\ h\ s;.*;/ r sql.db a\ ;\ x\ }\ # b } ' sql.sed | sed -e ' /s\;\.\*\;/{ N;N;s/\n//g;b }' > sql.sed.new /bin/mv sql.sed.new sql.sed chmod +x sql.sed #--------------------------------------------------------------------------- -- # $Log: sedql,v $ # Revision 1.1 1997/06/09 04:26:59 simon # Ensure that the sql.db file is present before attempting to mv it. # # Revision 1.0 1997/06/08 12:23:47 simon # Initial revision # --- end email ---