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 ---