Subject: recursive functions and cursors
From: [email-masked] (Jonathan Leffler)
Date: 13 Jun 1995 12:33:04 -0400
Newsgroups: comp.databases.informix

>From: [email-masked] (Sivagurn Ramanathan)
>Date: Tue, 13 Jun 95 9:58:41 CDT
>X-Informix-List-Id: <list.6556>
>
>[email-masked] wrote:
>>
>> I have a table that contains a hierarchy of items where where each record
>> references another record as its parent.
>>
>> [...]
>>
>> I'm trying to use a cursor in a recursive function but keep running into
>> an error.  The program compiles ok but returns the following error when ran.
>>
>> $ fglgo prog.4go
>> A
>> B
>> Program stopped at "prog.4gl", line number 16.
>> SQL statement error number -400.
>> Fetch attempted on unopen cursor.
>>
>> # prog.4gl
>> 1  database mydata
>> 2
>> 3  main
>> 4     call showitem(A)
>> 5  end main
>> 6
>> 7  function showitem(itemid)
>> 8     define itemid like tableA.itemid,
>> 9        childid like tableA.itemid
>> 10
>> 11   display itemid
>> 12
>> 13   declare child_cur cursor for
>> 14      select itemid from tableA where parent = itemid
>> 15
>> 16   foreach child_cur into childid
>> 17      call showitem(childid)
>> 18   end foreach
>> 19
>> 20 end function
>>
>> Does anyone have a suggestion?
>
>  Folks correct me if I am wrong.
>
>  I think 4GL as such does not support recursion.

I4GL does support recursion.  What doesn't support recursion is ESQL/C.
When you are not using cursors, I4GL supports recursion in ordinary
functions.  Reports cannot be used recursively reliably, though there is
nothing in the language or compilers to stop you doing:

REPORT x(...) ... OUTPUT TO REPORT x(...) ... END REPORT

However, in the code above, the function showitem() is called for a first
time.  It opens the cursor with the FOREACH loop, fetches a row of data and
calls itself.  This re-opens the cursor (doing a close and then an open),
and, for sake of argument, reads 2 rows and then closes the cursor, and
returns to the first invocation.  The cursor to which the first invocation
has been closed twice, now.  So the next fetch returns the error, quite
correctly.  Remember, cursors are like global variables -- there is only
one of them, even when the function is recursive.  In fact, if you look at
the generated C code, you'll see that the cursors are indeed represented by
global (or file static) variables.

>  But there is a work around
>  for your problem which I think is about one of the ways to get around it.
>
>  DO NOT use the foreach .... end foreach approach. Instead open the cursor
>  out side of the function showitem() and inside the function have a while
>  loop to fetch the rows.
>  [sample deleted]

This would not work, I'm sorry to report.  To get the child data, you have
to re-open the cursor.  And the code given doesn't re-open the cursor.  The
original code did a depth-first traversal of the tree.  It is quite
difficult to simulate this directly in SQL.  You can simulate breadth-first
traversal and stuff the data into a temporary table, and then retrieve the
data from the temporary table.

I attach a shell archive containing an SQL-based set of scripts which do
various sorts of hierarchical data analysis -- but you should be aware that
some of the techniques are not pretty but are effective.  Also the file
"alternatives" itself contains two shell archives where alternatives are
discussed.

Also, you can use stored procedures recursively to achieve the required
effect, so it may be easiest to adapt the code above into a stored
procedure, and then use that from I4GL.

Note that if you have an upper bound on the depth of the hierarchy, you can
write code to simulate an array of cursors and do the recursion manually.
It is rather ghastly (British understatement for perfectly revolting) but
will work until the upper bound is exceeded.

Finally, the data in the example program will be returned in a
indeterminate order because no sort criteria are specified on the SELECT
statement.  If the data happens to be inserted in order, it will work --
the test data probably was inserted in order -- but after a few random
updates, deletes and insertions, it will produce the data in any old order.

I hope this makes it through the mailers...

Yours,
Jonathan Leffler ([email-masked]) #include <disclaimer.h>

:	"@(#)shar.sh	1.9"
#! /bin/sh
#
#	This is a shell archive.
#	Remove everything above this line and run sh on the resulting file.
#	If this archive is complete, you will see this message at the end:
#	"All files extracted"
#
#	Created: Tue Jun 13 08:57:34 PDT 1995 by johnl at Informix Software Ltd.
#	Files archived in this archive:
#	README
#	Makefile
#	alternatives
#	bom.sql
#	hier.sql
#	hier1.ace
#	hier1.sh
#	hier2.ace
#	hier2.sh
#	partexp1.sh
#	partexp2.sh
#	partexp3.sh
#	partexp4.sh
#	pp.1.sh
#	pp.sql
#
#--------------------
if [ -f README -a "$1" != "-c" ]
then echo shar: README already exists
else
echo 'x - README (2039 characters)'
sed -e 's/^X//' >README <<'SHAR-EOF'
XExamples in Hierarchical Data Structures
X========================================
X
XThe accompanying shell scripts, ACE report and SQL files can be used to
Xdemonstrate how to handle hierarchical data structures (eg Bill of
XMaterials or Organisation Chart) in SQL.
X
XThe Bill of Materials (BoM) solutions (partexp1..partexp4) illustrate
Xthe use of Breadth-First-Search.  The Organisation Chart (OC) solution
X(hier2) illustrates Depth-First-Search.  Note that neither solution is
Xneat and tidy, though the BoM is simply iterative whereas the OC
Xsolution requires a recursively defined table structure.
X
XThe solutions use my program SQLCMD as an SQL command interpreter.  The
Xequivalent effect can more or less be achieved using either ISQL or
XDBACCESS, but it is not as easy, and some cases would require the output
Xfrom ISQL/DBACCESS to be reformatted to retain the data one row per
Xline.  (Where the command is of the form sqlcmd -d dbase -e "SQL stmt",
Xuse echo "SQL stmt" | isql dbase -; where the command is of the form
Xsqlcmd -d dbase -f file.sql, use isql dbase file; where the command is
Xof the form sqlcmd -d dbase file1.sql file2.sql, use cat file1.sql
Xfile2.sql | isql dbase -; when -D is used, set DBDELIMITER to the
Xargument value.  When -F unload is used, change the last SELECT
Xstatement into an unload statement.)  Contact me for source to SQLCMD.
X
XThe Makefile has the targets all, dbase and runit.  All makes the
Xrequisite files, dbase creates and loads the database, and runit runs
Xthe various programs.  A sample output is included called runit.log.
X
XNote that both sets of data have random number sequences.  The OC code
Xwas initially developed with a neat and tidy (non-random) sequence for
Xthe identifiers, and a neat and tidy solution was the result.  When the
Xnumbers were randomised, that solution fell to bits, so the current
Xhier2 solution was developed.  Beware when adapting the code of this
Xpotential problem; simple sample data may easily mislead you.
X
XJonathan Leffer
XInformix Software
X@(#)README	1.1 93/03/03
SHAR-EOF
chmod 444 README
if [ `wc -c <README` -ne 2039 ]
then echo shar: README unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f Makefile -a "$1" != "-c" ]
then echo shar: Makefile already exists
else
echo 'x - Makefile (607 characters)'
sed -e 's/^X//' >Makefile <<'SHAR-EOF'
X#	@(#)Makefile	1.1 93/03/03
X#
X#	Makefile for Parts Explosion examples
X
X.SUFFIXES: .ace .arc
X
X.ace.arc:
X	${ACEPREP} ${AP_FLAGS} $*
X
X.sh:
X	rm -f $@
X	cp $< $@
X	chmod 555 $@
X
XFILES.sql = bom.sql hier.sql
XFILES.ace = hier2.ace
XFILES.sh  = partexp1.sh partexp2.sh partexp3.sh partexp4.sh hier1.sh hier2.sh
XFILES     = ${FILES.sh:.sh=}
X
Xall:	${FILES.sql} ${FILES} ${FILES.ace}
X
Xdbase:	.dbase
X
X.dbase:	${FILES.sql}
X	sqlcmd -e "drop database parts" 2>/dev/null || true
X	sqlcmd -e "create database parts"
X	sqlcmd -d parts ${FILES.sql}
X	touch $@
X
Xrunit:	dbase all
X	partexp1
X	partexp2
X	partexp3
X	partexp4
X	hier1
X	hier2
SHAR-EOF
chmod 444 Makefile
if [ `wc -c <Makefile` -ne 607 ]
then echo shar: Makefile unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f alternatives -a "$1" != "-c" ]
then echo shar: alternatives already exists
else
echo 'x - alternatives (19132 characters)'
sed -e 's/^X//' >alternatives <<'SHAR-EOF'
XDate: Tue, 15 Jun 93 13:40:23 PDT
XFrom: cortesi@godzilla (David Cortesi)
XTo: tech@godzilla
XSubject: Re: Tech - Oracle's "connect by", how do we match it?
X
X> From proberts@moose  Mon Jun 14 21:15:06 1993
X>
X>  Do we have, or do we plan to have, anything functionally similar to
X>  Oracle's SQL*Plus's CONNECT BY ?  This is an extension to SQL that
X>  allows you traverse an entire tree in a single query, the parent-
X>  child relations in the tree being specified in a table.  It yields
X>  a sort of solution to the "parts explosion" problem, frequently
X>  quoted as something SQL "can't do".
X>
X>  I think we can certainly write a 4GL program to do the same thing,
X>  and perhaps procedures would also work ...
X
XA 4GL solution is shown in chapter 28 of 4GL BY EXAMPLE.
X
XBut the stored procedure idea is a good one.  The following stored
Xprocedure generates the data for a parts explosion, using the database
Xin chapter 28 of 4GL BY EXAMPLE.  It has been tested on a 6.0 engine.
X
XIn order to demo this, you have to create three files.  Cut and save
Xas indicated below.  Then set up to run dbaccess on 5.01 or 6.0, and do:
X
X	dbaccess - < recurse.sql
X
XDave Cortesi
X	p.s. perhaps somebody will capture this for tech info?
X#! /bin/sh
X# This is a shell archive.  Remove anything before this line, then feed it
X# into a shell via "sh file" or similar.  To overwrite existing files,
X# type "sh file -c".
X# The tool that generated this appeared in the comp.sources.unix newsgroup;
X# send mail to [email-masked] if you want that tool.
X# If this archive is complete, you will see the following message at the end:
X#		"End of shell archive."
X# Contents:  recurse.sql ex28pa.unl ex28pt.unl
X# Wrapped by johnl@godzilla on Tue Nov 16 10:05:37 1993
XPATH=/bin:/usr/bin:/usr/ucb ; export PATH
Xif test -f 'recurse.sql' -a "${1}" != "-c" ; then
X  echo shar: Will not clobber existing file \"'recurse.sql'\"
Xelse
Xecho shar: Extracting \"'recurse.sql'\" \(1893 characters\)
Xsed "s/^X//" >'recurse.sql' <<'END_OF_FILE'
XXcreate database recurse;
XX
XXcreate table parts (partnum integer, descr char(40));
XX
XXcreate table partree(parent integer, child integer, used integer);
XX
XXload from "ex28pa.unl" insert into parts; -- file follows this one
XX
XXload from "ex28pt.unl" insert into partree; -- file follows that one
XX
XX-- recursive procedure to do a parts explosion
XX
XXcreate procedure explode(partno integer, usage integer, level smallint)
XX        returning       -- info on "partno" and its children
XX                integer         -- partno or one of its children
XX,               char(40)        -- its description
XX,               integer         -- its use count
XX,               smallint        -- its level, >= input level
XX;
XXdefine  pn      integer;        -- current partnum
XXdefine  pu      integer;        -- its use count
XXdefine  cn      integer;        -- child partnum
XXdefine  cu      integer;        -- child use count
XXdefine  cl      smallint;       -- child level
XXdefine  de      char(40);       -- description
XX
XX        let de = null;
XX        select descr into de from parts where partnum = partno;
XX        if de is null then -- no such part, stop now
XX                return null, null, null, level;
XX        end if;
XX	-- return info on this part
XX        return partno, de, usage, level WITH RESUME;
XX	-- return info on all children of this part
XX        foreach
XX                select child, used
XX                into pn, pu
XX                from partree
XX                where parent = partno
XX
XX                foreach
XX                        execute procedure explode(pn,pu,level+1)
XX                        into cn,de,cu,cl
XX
XX                        return cn, de, cu, cl WITH RESUME;
XX
XX                end foreach; -- child at this level
XX
XX        end foreach; -- child of partno
XXend procedure;
XX
XX-- Demonstrate operation of above procedure
XX
XXexecute procedure explode(76566,1,0);
XX
XXclose database;
XX-- end of "recurse.sql"
XEND_OF_FILE
Xif test 1893 -ne `wc -c <'recurse.sql'`; then
X    echo shar: \"'recurse.sql'\" unpacked with wrong size!
Xfi
X# end of 'recurse.sql'
Xfi
Xif test -f 'ex28pa.unl' -a "${1}" != "-c" ; then
X  echo shar: Will not clobber existing file \"'ex28pa.unl'\"
Xelse
Xecho shar: Extracting \"'ex28pa.unl'\" \(2491 characters\)
Xsed "s/^X//" >'ex28pa.unl' <<'END_OF_FILE'
XX76540|speed nut #8|
XX76541|speed nut #2|
XX76542|plastic end cap|
XX76543|stove bolt|
XX76544|flat washer|
XX76545|nut|
XX76546|bracket mount kit|
XX76547|9-in rubber wheel|
XX76548|5-in rubber wheel|
XX76549|wagon handle assembly|
XX76550|wagon handle|
XX76551|handle clevis pin|
XX76552|swivel fixed plate|
XX76553|swivel bolt|
XX76554|swivel bolt nut|
XX76555|lock washer|
XX76556|swivel mount kit|
XX76557|axle bracket left|
XX76558|axle bracket right|
XX76559|wheel pair assembly|
XX76560|18-in axle rod|
XX76561|18-in fixed axle|
XX76562|18-in swivel plate|
XX76563|18-in swivelling axle truck|
XX76564|large wagon body pan|
XX76565|red wagon decal kit|
XX76566|large red wagon|
XX76567|14-in axle rod|
XX76568|14-in fixed axle|
XX76569|14-in swivel plate|
XX76570|14-in swivelling axle truck|
XX76571|small wagon body pan|
XX76572|small red wagon|
XX76573|12-in axle rod|
XX76574|12-in fixed axle|
XX76575|12-in swivel plate|
XX76576|12-in swivelling axle truck|
XX76577|tot wagon decal kit|
XX76578|pull-me tot wagon|
XX76579|tot wagon body pan|
XX76580|2-in axle rod|
XX76581|scooter wheel kit|
XX76582|toddler scooter decal kit|
XX76583|short scooter body|
XX76584|short scooter handle|
XX76585|plastic handle grip|
XX76586|toddler scooter|
XX76587|mom's best grocery cart|
XX76588|grocery basket assy|
XX76589|caddy pan|
XX76590|grocery basket|
XX76591|caddy top ring|
XX76592|dad's fun golf caddy|
XX76593|caddy handle|
XX76594|basket rim trim|
XX76595|caddy base|
XX-------------------------------save this as ex28pt.unl -----------------
XX76546|76543|2|
XX76546|76544|2|
XX76546|76545|2|
XX76549|76550|1|
XX76549|76551|1|
XX76549|76540|1|
XX76549|76542|1|
XX76556|76552|1|
XX76556|76553|1|
XX76556|76554|1|
XX76556|76555|1|
XX76556|76546|2|
XX76559|76557|1|
XX76559|76558|1|
XX76559|76546|2|
XX76559|76547|2|
XX76559|76541|2|
XX76559|76542|2|
XX76561|76560|1|
XX76561|76559|1|
XX76563|76562|1|
XX76563|76561|1|
XX76563|76556|1|
XX76566|76549|1|
XX76566|76564|1|
XX76566|76565|1|
XX76566|76561|1|
XX76566|76563|1|
XX76568|76567|1|
XX76568|76559|1|
XX76570|76569|1|
XX76570|76568|1|
XX76570|76556|1|
XX76572|76570|1|
XX76572|76549|1|
XX76572|76565|1|
XX76572|76568|1|
XX76572|76571|1|
XX76574|76573|1|
XX76574|76559|1|
XX76576|76575|1|
XX76576|76574|1|
XX76576|76556|1|
XX76578|76577|1|
XX76578|76576|1|
XX76578|76574|1|
XX76578|76579|1|
XX76578|76549|1|
XX76581|76580|1|
XX76581|76540|2|
XX76581|76548|2|
XX76581|76544|2|
XX76581|76542|2|
XX76586|76581|2|
XX76586|76585|2|
XX76586|76582|1|
XX76586|76583|1|
XX76586|76584|1|
XX76587|76588|1|
XX76587|76568|1|
XX76587|76585|1|
XX76587|76595|1|
XX76587|76593|1|
XX76588|76590|1|
XX76588|76594|1|
XX76592|76593|1|
XX76592|76595|1|
XX76592|76591|1|
XX76592|76585|1|
XX76592|76568|1|
XEND_OF_FILE
Xif test 2491 -ne `wc -c <'ex28pa.unl'`; then
X    echo shar: \"'ex28pa.unl'\" unpacked with wrong size!
Xfi
X# end of 'ex28pa.unl'
Xfi
Xif test -f 'ex28pt.unl' -a "${1}" != "-c" ; then
X  echo shar: Will not clobber existing file \"'ex28pt.unl'\"
Xelse
Xecho shar: Extracting \"'ex28pt.unl'\" \(1050 characters\)
Xsed "s/^X//" >'ex28pt.unl' <<'END_OF_FILE'
XX76546|76543|2|
XX76546|76544|2|
XX76546|76545|2|
XX76549|76550|1|
XX76549|76551|1|
XX76549|76540|1|
XX76549|76542|1|
XX76556|76552|1|
XX76556|76553|1|
XX76556|76554|1|
XX76556|76555|1|
XX76556|76546|2|
XX76559|76557|1|
XX76559|76558|1|
XX76559|76546|2|
XX76559|76547|2|
XX76559|76541|2|
XX76559|76542|2|
XX76561|76560|1|
XX76561|76559|1|
XX76563|76562|1|
XX76563|76561|1|
XX76563|76556|1|
XX76566|76549|1|
XX76566|76564|1|
XX76566|76565|1|
XX76566|76561|1|
XX76566|76563|1|
XX76568|76567|1|
XX76568|76559|1|
XX76570|76569|1|
XX76570|76568|1|
XX76570|76556|1|
XX76572|76570|1|
XX76572|76549|1|
XX76572|76565|1|
XX76572|76568|1|
XX76572|76571|1|
XX76574|76573|1|
XX76574|76559|1|
XX76576|76575|1|
XX76576|76574|1|
XX76576|76556|1|
XX76578|76577|1|
XX76578|76576|1|
XX76578|76574|1|
XX76578|76579|1|
XX76578|76549|1|
XX76581|76580|1|
XX76581|76540|2|
XX76581|76548|2|
XX76581|76544|2|
XX76581|76542|2|
XX76586|76581|2|
XX76586|76585|2|
XX76586|76582|1|
XX76586|76583|1|
XX76586|76584|1|
XX76587|76588|1|
XX76587|76568|1|
XX76587|76585|1|
XX76587|76595|1|
XX76587|76593|1|
XX76588|76590|1|
XX76588|76594|1|
XX76592|76593|1|
XX76592|76595|1|
XX76592|76591|1|
XX76592|76585|1|
XX76592|76568|1|
XEND_OF_FILE
Xif test 1050 -ne `wc -c <'ex28pt.unl'`; then
X    echo shar: \"'ex28pt.unl'\" unpacked with wrong size!
Xfi
X# end of 'ex28pt.unl'
Xfi
Xecho shar: End of shell archive.
Xexit 0
X
XFrom: hermannd@infmuc (Hermann Daeubler)
XSubject: ESQL/C and stored procedure example
XTo: tech@infmuc
XDate: Wed, 20 May 92 11:07:10 MET DST
X
XHello,
X
Xa little example which shows the use of a stored procedure in ESQL/C.
X
XYou need :   ESQL/C  5.0
X             OnLine  5.0
X             Bourne shell
X
XTake the following sh script and start it. It generates all .ec files
Xand stored procedure sources ... and starts the program.
X
XImportant :
X===========
XTo run the script several times I included a drop database statement.
XSo be sure that you have no database with the name  "spl_test" !!!!!!!
X( if there is no database spl_test you get an error -329 from the
X  drop database statement -> ignore it )
X
XWhat shows the example ?
X
Xit shows a way to solve the part explosion problem. You have one table
Xand in this table you have parts and sub parts ( part A  consists of
Xpart A1 and part A2 .... ).
XNow you want a list of all parts and their sub parts :
X
Xpart A
X  part A1
X  part A2
Xpart B
X  part B1
X    part B1a
X    part B1b
X  part B2
X  part B3
X.
X
XTo make a simple solution I included a field in the table which shows if
Xthe part is a sub part ( "T" = top,  "S" = sub ). In the list above
Xpart A and part B have type "T" the rest has type "S". I used it because
Xif there is no flag it is too difficult to find the parts which are not
Xsub parts with a select.
XTo solve the problem that you don't know how many sub parts are available
Xfor a "top" part I used a recursively calling procedure.
X
XIt is a quick and dirty example, so there could be better solutions !
X
XHermann the German
X( Support - Informix Germany )
X
X#! /bin/sh
X# This is a shell archive.  Remove anything before this line, then feed it
X# into a shell via "sh file" or similar.  To overwrite existing files,
X# type "sh file -c".
X# The tool that generated this appeared in the comp.sources.unix newsgroup;
X# send mail to [email-masked] if you want that tool.
X# If this archive is complete, you will see the following message at the end:
X#		"End of shell archive."
X# Contents:  createdb.ec createpr.ec partslist.ec runit.sh
X#   sub_parts.prc
X# Wrapped by johnl@godzilla on Tue Nov 16 10:23:05 1993
XPATH=/bin:/usr/bin:/usr/ucb ; export PATH
Xif test -f 'createdb.ec' -a "${1}" != "-c" ; then
X  echo shar: Will not clobber existing file \"'createdb.ec'\"
Xelse
Xecho shar: Extracting \"'createdb.ec'\" \(1685 characters\)
Xsed "s/^X//" >'createdb.ec' <<'END_OF_FILE'
XX#include <stdio.h>
XX$include sqlca;
XX$include sqlda;
XX
XXmain()
XX{
XX
XX   $drop database spl_test;
XX   sql_error();
XX
XX   $create database spl_test;
XX   sql_error();
XX
XX   $create table parts ( part_nr      integer,
XX                         part_name    char(30),
XX                         sub_part_nr  integer,
XX                         top_or_not   char(1) );
XX   sql_error();
XX
XX   $insert into parts values(  1,"Part1",        10,"T");
XX   sql_error();
XX   $insert into parts values(100,"Part2",       110,"T");
XX   sql_error();
XX   $insert into parts values(200,"Part3",      NULL,"T");
XX   sql_error();
XX   $insert into parts values(300,"Part4",       310,"T");
XX   sql_error();
XX   $insert into parts values( 10,"Part1-1",    NULL,"S");
XX   sql_error();
XX   $insert into parts values( 10,"Part1-2",    NULL,"S");
XX   sql_error();
XX   $insert into parts values( 10,"Part1-3",      20,"S");
XX   sql_error();
XX   $insert into parts values(110,"Part2-1",    NULL,"S");
XX   sql_error();
XX   $insert into parts values(110,"Part2-2",    NULL,"S");
XX   sql_error();
XX   $insert into parts values(310,"Part4-1",     320,"S");
XX   sql_error();
XX   $insert into parts values(310,"Part4-2",    NULL,"S");
XX   sql_error();
XX   $insert into parts values( 20,"Part1-3-a",  NULL,"S");
XX   sql_error();
XX   $insert into parts values( 20,"Part1-3-b",  NULL,"S");
XX   sql_error();
XX   $insert into parts values(320,"Part4-1-a",   330,"S");
XX   sql_error();
XX   $insert into parts values(330,"Part4-1-a.1",NULL,"S");
XX   sql_error();
XX
XX   $close database;
XX   sql_error();
XX}
XX
XXsql_error()
XX{
XX  char err_str[300];
XX
XX  if( sqlca.sqlcode != 0 )
XX    {
XX	rgetmsg(sqlca.sqlcode,err_str,255);
XX	fprintf(stderr,"error  %d : %s\n",sqlca.sqlcode,err_str);
XX    }
XX}
XEND_OF_FILE
Xif test 1685 -ne `wc -c <'createdb.ec'`; then
X    echo shar: \"'createdb.ec'\" unpacked with wrong size!
Xfi
X# end of 'createdb.ec'
Xfi
Xif test -f 'createpr.ec' -a "${1}" != "-c" ; then
X  echo shar: Will not clobber existing file \"'createpr.ec'\"
Xelse
Xecho shar: Extracting \"'createpr.ec'\" \(373 characters\)
Xsed "s/^X//" >'createpr.ec' <<'END_OF_FILE'
XX#include <stdio.h>
XX$include sqlda;
XX$include sqlca;
XX
XXmain()
XX{
XX   $database spl_test;
XX   sql_error();
XX
XX   $create procedure from "sub_parts.prc";
XX   sql_error();
XX
XX   $close database;
XX   sql_error();
XX}
XX
XXsql_error()
XX{
XX  char err_str[300];
XX
XX  if( sqlca.sqlcode != 0 )
XX    {
XX	rgetmsg(sqlca.sqlcode,err_str,255);
XX	fprintf(stderr,"error  %d : %s\n",sqlca.sqlcode,err_str);
XX    }
XX}
XEND_OF_FILE
Xif test 373 -ne `wc -c <'createpr.ec'`; then
X    echo shar: \"'createpr.ec'\" unpacked with wrong size!
Xfi
X# end of 'createpr.ec'
Xfi
Xif test -f 'partslist.ec' -a "${1}" != "-c" ; then
X  echo shar: Will not clobber existing file \"'partslist.ec'\"
Xelse
Xecho shar: Extracting \"'partslist.ec'\" \(1596 characters\)
Xsed "s/^X//" >'partslist.ec' <<'END_OF_FILE'
XX#include <stdio.h>
XX$include sqlda;
XX$include sqlca;
XX
XX#define LENMARGIN  81
XX$define LEFTMARGIN  4;
XX
XX$long  part_nr;
XX$char  part_name[31];
XX$long  sub_part_nr;
XX$char  top_or_not;
XX
XX$int  nr_of_blanks;
XX$char sub_part_name[31];
XX
XXchar left_margin[LENMARGIN];
XX
XXmain()
XX{
XX  int i;
XX
XX  for(i=0;i<LENMARGIN-1;i++)
XX    left_margin[i]=' ';
XX  left_margin[i]='\0';
XX
XX  $database spl_test;
XX  sql_error();
XX
XX  /* searching for all parts which are not sub parts */
XX  $declare top_cur cursor for select * from parts where top_or_not = "T";
XX  sql_error();
XX
XX  /* searching for all sub parts */
XX  $declare part_cur cursor for
XX    execute procedure sub_parts($sub_part_nr,LEFTMARGIN);
XX  sql_error();
XX
XX  $open top_cur;
XX  sql_error();
XX
XX  $fetch top_cur into $part_nr,$part_name,$sub_part_nr,$top_or_not;
XX
XX  while( sqlca.sqlcode != SQLNOTFOUND )
XX    {
XX         printf("%s\n",part_name);
XX         $open part_cur;
XX         sql_error();
XX
XX         $fetch part_cur into $sub_part_name,$nr_of_blanks;
XX
XX         while( sqlca.sqlcode != SQLNOTFOUND )
XX           {
XX              printf("%s%s\n",&left_margin[LENMARGIN-1-nr_of_blanks],
XX                     sub_part_name);
XX              $fetch part_cur into $sub_part_name,$nr_of_blanks;
XX           }
XX
XX         $close part_cur;
XX         sql_error();
XX
XX         $fetch top_cur into $part_nr,$part_name,$sub_part_nr,$top_or_not;
XX    }
XX
XX  $close top_cur;
XX  sql_error();
XX
XX  $close database;
XX  sql_error();
XX}
XX
XXsql_error()
XX{
XX  char err_str[300];
XX
XX  if( sqlca.sqlcode != 0 )
XX    {
XX      rgetmsg(sqlca.sqlcode,err_str,255);
XX      fprintf(stderr,"error  %d : %s\n",sqlca.sqlcode,err_str);
XX    }
XX}
XEND_OF_FILE
Xif test 1596 -ne `wc -c <'partslist.ec'`; then
X    echo shar: \"'partslist.ec'\" unpacked with wrong size!
Xfi
X# end of 'partslist.ec'
Xfi
Xif test -f 'runit.sh' -a "${1}" != "-c" ; then
X  echo shar: Will not clobber existing file \"'runit.sh'\"
Xelse
Xecho shar: Extracting \"'runit.sh'\" \(320 characters\)
Xsed "s/^X//" >'runit.sh' <<'END_OF_FILE'
XXecho "compiling createdb.ec"
XXesql createdb.ec -o createdb
XXecho "creating the testdatabase"
XX./createdb
XX
XXecho "compiling createpr.ec"
XXesql createpr.ec -o createpr
XXecho "creating the stored procedure"
XX./createpr
XX
XXecho "compiling partslist.ec"
XXesql partslist.ec -o partslist
XXecho
XXecho "executing partslist"
XXecho
XX./partslist
XEND_OF_FILE
Xif test 320 -ne `wc -c <'runit.sh'`; then
X    echo shar: \"'runit.sh'\" unpacked with wrong size!
Xfi
X# end of 'runit.sh'
Xfi
Xif test -f 'sub_parts.prc' -a "${1}" != "-c" ; then
X  echo shar: Will not clobber existing file \"'sub_parts.prc'\"
Xelse
Xecho shar: Extracting \"'sub_parts.prc'\" \(599 characters\)
Xsed "s/^X//" >'sub_parts.prc' <<'END_OF_FILE'
XXcreate procedure sub_parts(p_nr int,nr_of_blanks int)
XX  returning char(30),int;
XX
XX  define p_name char(30);
XX  define sub_p_nr, h_nr_of_blanks int;
XX
XX  foreach
XX    select part_name,sub_part_nr into p_name,sub_p_nr
XX      from parts where part_nr = p_nr
XX
XX    if sub_p_nr is not null then
XX      return p_name,nr_of_blanks with resume;
XX
XX      foreach execute procedure
XX        sub_parts(sub_p_nr,nr_of_blanks+4) into p_name, h_nr_of_blanks
XX
XX        return p_name, h_nr_of_blanks with resume;
XX      end foreach
XX    else
XX      return p_name,nr_of_blanks with resume;
XX    end if
XX  end foreach;
XX
XXend procedure
XEND_OF_FILE
Xif test 599 -ne `wc -c <'sub_parts.prc'`; then
X    echo shar: \"'sub_parts.prc'\" unpacked with wrong size!
Xfi
X# end of 'sub_parts.prc'
Xfi
Xecho shar: End of shell archive.
Xexit 0
X
XFrom: johnl (Jonathan Leffler)
XDate: Thu Mar 4 12:01:43 1993
XTo: cwilson@atlanta
XSubject: Re: Hierarchical Structures in SQL Databases
X
XHere's my stuff.
X
XAbout depth of search/hierarchy.
X
XIt depends on whether you are doing a Parts Explosion (Bill of Materials or
XBoM) or Organisation Chart type search.
X
XIn the BoM solution, there is a simple iterative solution which does,
Xroughly:
X
X	while (select count(*) from ...) > 0
X		do another scan
X	end while
X
Xwhere the select condition counts the incompletely expanded parts.  This is
Xquite simple because the table being used has the same structure on every
Xiteration.  This means that you do not have to know exactly how deep your
Xsearch goes before you do it.
X
XIn the Organisation Chart solution, the table structure changes
Xiteratively, which is a confounded nuisance (believe me!).  Although an
Xiterative solution could be provided, it would have to include a
Xdynamically constructed create temp table statement as well as dynamically
Xconstructed select statements, so it is considerably more complex.  To
Xavoid the iterative solution, you could write out the expansion long-hand,
Xbut then you would have to ensure that the number of written out iterations
Xwas sufficient for the deepest search the code would be asked to do.  I
Xsuspect that few hierarchies (even in government circles) would extend to
X10 deep; 20 would be utterly exceptional.  However, if the hierarchy was
Xonly 4 deep, the cost of doing the extra 16 iterations would be
Xnon-negligible.  If you look at the hier* code in the shell archive,
Xyou'll see what I mean, I think.  If not, come back to me on it.
X
XYours,
XJonathan Leffler (johnl@obelix)
X
X
SHAR-EOF
chmod 664 alternatives
if [ `wc -c <alternatives` -ne 19132 ]
then echo shar: alternatives unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f bom.sql -a "$1" != "-c" ]
then echo shar: bom.sql already exists
else
echo 'x - bom.sql (4772 characters)'
sed -e 's/^X//' >bom.sql <<'SHAR-EOF'
X-- @(#)bom.sql	1.3 93/02/27
X-- Parts explosion (schema and data)
X
XCREATE TABLE parts
X(
X	pno		CHAR(3) NOT NULL
X			PRIMARY KEY CONSTRAINT pk_part,
X	pname	VARCHAR(30) NOT NULL
X);
X
XINSERT INTO parts VALUES("P02", "Pump Vanes");
XINSERT INTO parts VALUES("P10", "Pump Assembly");
XINSERT INTO parts VALUES("P11", "Pump Rotor");
XINSERT INTO parts VALUES("P12", "Pump Housing A");
XINSERT INTO parts VALUES("P13", "Pump Seals");
XINSERT INTO parts VALUES("P14", "Pump Housing B");
XINSERT INTO parts VALUES("P16", "Annealed flange 5D");
XINSERT INTO parts VALUES("P17", "Base Assembly P");
XINSERT INTO parts VALUES("P18", "Baseplate R3");
XINSERT INTO parts VALUES("P19", "3mm D x 70mm Rotor Shaft");
XINSERT INTO parts VALUES("P21", "Mounting Plate R3");
XINSERT INTO parts VALUES("P23", "Vane Springs");
XINSERT INTO parts VALUES("P24", "4mm x M1.5 Bolt");
XINSERT INTO parts VALUES("P26", "3mm x M1.2 Bolt");
XINSERT INTO parts VALUES("P27", "Base Bearing");
XINSERT INTO parts VALUES("P28", "Bearing Outer Case");
XINSERT INTO parts VALUES("P29", "Bearing Inner Case");
XINSERT INTO parts VALUES("P30", "0.35mm Ball Bearings");
XINSERT INTO parts VALUES("P32", "6mm D x 37mm Connecting hose");
XINSERT INTO parts VALUES("P33", "6mm Jubilee clip");
XINSERT INTO parts VALUES("P34", "3mm x M1.5 Bolt");
XINSERT INTO parts VALUES("P35", "M1.5 Nut");
XINSERT INTO parts VALUES("P36", "6mm Jubilee clip body");
XINSERT INTO parts VALUES("P07", "6mm D x 37mm Hose Assembly");
XINSERT INTO parts VALUES("P68", "12mm D x 30T Cog (3mm bore)");
XINSERT INTO parts VALUES("P39", "47mm D x 150T Cog (6mm bore)");
XINSERT INTO parts VALUES("P40", "2mm x M1.2 Grub Screw");
XINSERT INTO parts VALUES("P41", "Bearing Cage");
XINSERT INTO parts VALUES("P45", "6mm shakeproof washer");
XINSERT INTO parts VALUES("P47", "Annealed flange 6B");
XINSERT INTO parts VALUES("P49", "Pump Sub-assembly");
XINSERT INTO parts VALUES("P55", "13mm x M2.5 Bolt");
XINSERT INTO parts VALUES("P56", "Baseplate type A");
XINSERT INTO parts VALUES("P62", "Widget Sub-assembly A");
XINSERT INTO parts VALUES("P71", "Widget Assembly");
XINSERT INTO parts VALUES("P72", "Pump Vane Housing");
XINSERT INTO parts VALUES("P74", "25mm x M6 Bolt");
XINSERT INTO parts VALUES("P85", "1.5mm Shakeproof Washer");
XINSERT INTO parts VALUES("P88", "Baseplate type B");
XINSERT INTO parts VALUES("P93", "Widget Sub-assembly B");
X
XCREATE TABLE bom 
X(
X    p1		CHAR(3) NOT NULL
X			REFERENCES parts  CONSTRAINT f1_bom,
X    p2		CHAR(3) NOT NULL
X			REFERENCES parts  CONSTRAINT f2_bom,
X    qty		INTEGER NOT NULL,
X    PRIMARY KEY (p1,p2) CONSTRAINT pk_bom
X);
X
XINSERT INTO bom VALUES("P71", "P62", 2);		-- Widget Assembly
XINSERT INTO bom VALUES("P71", "P93", 1);
XINSERT INTO bom VALUES("P71", "P74", 4);
XINSERT INTO bom VALUES("P71", "P45", 3);
XINSERT INTO bom VALUES("P71", "P49", 1);
XINSERT INTO bom VALUES("P62", "P74", 2);		-- Widget Sub-assembly A
XINSERT INTO bom VALUES("P62", "P56", 1);
XINSERT INTO bom VALUES("P62", "P47", 2);
XINSERT INTO bom VALUES("P93", "P74", 6);		-- Widget Sub-assembly B
XINSERT INTO bom VALUES("P93", "P45", 6);
XINSERT INTO bom VALUES("P93", "P47", 3);
XINSERT INTO bom VALUES("P93", "P88", 1);
XINSERT INTO bom VALUES("P49", "P74", 10);		-- Pump Sub-assembly
XINSERT INTO bom VALUES("P49", "P45", 10);
XINSERT INTO bom VALUES("P49", "P10", 2);
XINSERT INTO bom VALUES("P49", "P07", 2);
XINSERT INTO bom VALUES("P49", "P39", 1);
XINSERT INTO bom VALUES("P49", "P40", 3);
XINSERT INTO bom VALUES("P10", "P11", 1);		-- Pump Assembly
XINSERT INTO bom VALUES("P10", "P12", 1);
XINSERT INTO bom VALUES("P10", "P13", 2);
XINSERT INTO bom VALUES("P10", "P14", 1);
XINSERT INTO bom VALUES("P10", "P55", 16);
XINSERT INTO bom VALUES("P10", "P16", 2);
XINSERT INTO bom VALUES("P10", "P17", 1);
XINSERT INTO bom VALUES("P10", "P18", 1);
XINSERT INTO bom VALUES("P10", "P07", 2);
XINSERT INTO bom VALUES("P10", "P68", 1);
XINSERT INTO bom VALUES("P10", "P40", 1);
XINSERT INTO bom VALUES("P11", "P19", 1);		-- Rotor
XINSERT INTO bom VALUES("P11", "P02", 4);
XINSERT INTO bom VALUES("P11", "P72", 1);
XINSERT INTO bom VALUES("P11", "P23", 8);
XINSERT INTO bom VALUES("P11", "P24", 8);
XINSERT INTO bom VALUES("P11", "P85", 8);
XINSERT INTO bom VALUES("P11", "P26", 4);
XINSERT INTO bom VALUES("P18", "P21", 2);		-- Baseplate R3
XINSERT INTO bom VALUES("P18", "P24", 4);
XINSERT INTO bom VALUES("P18", "P26", 4);
XINSERT INTO bom VALUES("P18", "P27", 1);
XINSERT INTO bom VALUES("P27", "P28", 1);		-- Base Bearing R3
XINSERT INTO bom VALUES("P27", "P29", 1);
XINSERT INTO bom VALUES("P27", "P30", 12);
XINSERT INTO bom VALUES("P27", "P41", 1);
XINSERT INTO bom VALUES("P07", "P32", 1);		-- Hose Assembly
XINSERT INTO bom VALUES("P07", "P33", 2);
XINSERT INTO bom VALUES("P33", "P34", 1);		-- Jubilee Clip
XINSERT INTO bom VALUES("P33", "P35", 1);
XINSERT INTO bom VALUES("P33", "P36", 1);
SHAR-EOF
chmod 444 bom.sql
if [ `wc -c <bom.sql` -ne 4772 ]
then echo shar: bom.sql unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f hier.sql -a "$1" != "-c" ]
then echo shar: hier.sql already exists
else
echo 'x - hier.sql (2459 characters)'
sed -e 's/^X//' >hier.sql <<'SHAR-EOF'
X-- @(#)hier.sql	1.2 93/02/27
X-- @(#)Table and sample data for illustrating hierarchical data
X
XCREATE TABLE hierarchy
X(
X	body	INTEGER  NOT NULL,
X	name	CHAR(25) NOT NULL,
X	boss	INTEGER  NOT NULL
X);
X
XINSERT INTO hierarchy VALUES (12, "Sales Manager 3", 25);
XINSERT INTO hierarchy VALUES (13, "Sales Person 1", 12);
XINSERT INTO hierarchy VALUES (14, "Sales Person 2", 50);
XINSERT INTO hierarchy VALUES (16, "Sales Person 4", 61);
XINSERT INTO hierarchy VALUES (18, "Sales Person 6", 50);
XINSERT INTO hierarchy VALUES (19, "Sales Person 7", 12);
XINSERT INTO hierarchy VALUES (21, "Junior Accountant 2", 39);
XINSERT INTO hierarchy VALUES (24, "Junior Accountant 5", 48);
XINSERT INTO hierarchy VALUES (25, "Sales Director", 71);
XINSERT INTO hierarchy VALUES (26, "Junior Accountant 7", 48);
XINSERT INTO hierarchy VALUES (27, "Junior Accountant 8", 39);
XINSERT INTO hierarchy VALUES (28, "Junior Accountant 9", 39);
XINSERT INTO hierarchy VALUES (29, "Accountancy Clerk 1", 72);
XINSERT INTO hierarchy VALUES (30, "Accountancy Clerk 2", 24);
XINSERT INTO hierarchy VALUES (31, "Technical Director", 71);
XINSERT INTO hierarchy VALUES (32, "Accountancy Clerk 4", 90);
XINSERT INTO hierarchy VALUES (37, "Accountant 1", 66);
XINSERT INTO hierarchy VALUES (38, "Development Manager", 31);
XINSERT INTO hierarchy VALUES (39, "Accountant 3", 66);
XINSERT INTO hierarchy VALUES (44, "Marketing Director", 71);
XINSERT INTO hierarchy VALUES (45, "Junior Accountant 6", 37);
XINSERT INTO hierarchy VALUES (48, "Accountant 2", 66);
XINSERT INTO hierarchy VALUES (50, "Sales Manager 1", 25);
XINSERT INTO hierarchy VALUES (53, "Junior Accountant 4", 48);
XINSERT INTO hierarchy VALUES (57, "Sales Person 5", 12);
XINSERT INTO hierarchy VALUES (61, "Sales Manager 2", 25);
XINSERT INTO hierarchy VALUES (62, "Development Engineer 2", 38);
XINSERT INTO hierarchy VALUES (63, "Accountancy Clerk 5", 90);
XINSERT INTO hierarchy VALUES (64, "Development Engineer 1", 38);
XINSERT INTO hierarchy VALUES (66, "Chief Accountant", 92);
XINSERT INTO hierarchy VALUES (71, "President", 0);
XINSERT INTO hierarchy VALUES (72, "Junior Accountant 3", 37);
XINSERT INTO hierarchy VALUES (82, "Accountancy Clerk 3", 45);
XINSERT INTO hierarchy VALUES (85, "Sales Person 3", 61);
XINSERT INTO hierarchy VALUES (90, "Junior Accountant 1", 37);
XINSERT INTO hierarchy VALUES (92, "Finance Director", 71);
XINSERT INTO hierarchy VALUES (94, "Marketing Executive", 44);
X
XCREATE UNIQUE INDEX pk_hierarchy ON hierarchy(body);
SHAR-EOF
chmod 444 hier.sql
if [ `wc -c <hier.sql` -ne 2459 ]
then echo shar: hier.sql unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f hier1.ace -a "$1" != "-c" ]
then echo shar: hier1.ace already exists
else
echo 'x - hier1.ace (3114 characters)'
sed -e 's/^X//' >hier1.ace <<'SHAR-EOF'
X-- @(#)hier1.ace	1.1 93/02/27
X-- @(#)Illustrating the Depth-First-Search (DFS) Technique for Hierarchies
X
X-- This illustrates how a Breadth-First-Search (BFS) Technique can be
X-- persuaded to return data so that it can be processed in DFS order.
X-- There is an inductive procedure for generating the Nth temporary
X-- table for N > 1.  The N = 0 table is produced completely differently,
X-- and the N = 1 method is slightly different from the N > 1 method.
X-- Note that an extra column is added to the temporary table every time.
X-- Note the trick of inserting level before b0 in the ORDER BY clause.
X--
X-- More work is needed to investigate whether a surreptitious use of
X-- SERIAL columns will allow the number of extra columns to be limited.
X--
X-- Note that you have to pre-determine how many iterations of SELECTION
X-- are necessary.  At the start of each iteration, you could use the
X-- statement: SELECT COUNT(*) FROM tN, hierarchy h WHERE tN.b0 = h.boss;
X-- If the answer is zero, no more iterations are required.
X
XDATABASE parts END
X
XDEFINE
X    variable n INTEGER
XEND
X
XOUTPUT
X    TOP    MARGIN 0
X    BOTTOM MARGIN 0
X    LEFT   MARGIN 0
XEND
X
X-- N = 0
XSELECT body b0, 0 level
X    FROM hierarchy
X    WHERE boss = 0				-- Choose your own criteria here!
X    INTO TEMP t0;
X
X--SELECT "T0" tag, t0.*, h.name
X--  FROM t0, hierarchy h
X--  WHERE t0.b0 = h.body
X--  ORDER BY level, b0;
X
X-- N = 1
XSELECT t0.b0 b1, t0.b0 b0, t0.level level
X    FROM t0
XUNION
XSELECT t0.b0, h.body, (t0.level + 1)
X    FROM hierarchy h, t0
X    WHERE t0.b0 = h.boss
X    INTO TEMP t1;
X
X--SELECT "T1" tag, t1.*, h.name
X--  FROM t1, hierarchy h
X--  WHERE t1.b0 = h.body
X--  ORDER BY b1, level, b0;
X
X-- N = 2: Start inductive procedure!
XSELECT t1.b1 b2, t1.b0 b1, t1.b0, t1.level
X    FROM t1
XUNION
XSELECT t1.b1, t1.b0, h.body, (t1.level + 1)
X    FROM hierarchy h, t1
X    WHERE t1.b0 = h.boss
X      AND t1.b1 != t1.b0
X    INTO TEMP t2;
X
X--SELECT "T2" tag, t2.*, h.name
X--  FROM t2, hierarchy h
X--  WHERE t2.b0 = h.body
X--  ORDER BY b2, b1, level, b0;
X
X-- N = 3
XSELECT b2 b3, b1 b2, b0 b1, b0, level
X    FROM t2
XUNION
XSELECT b2, b1, b0, body, (level + 1) level
X    FROM hierarchy h, t2
X    WHERE t2.b0 = h.boss
X      AND t2.b1 != t2.b0
X    INTO TEMP t3;
X
X--SELECT "T3" tag, t3.*, h.name
X--  FROM t3, hierarchy h
X--  WHERE t3.b0 = h.body
X--  ORDER BY b3, b2, b1, level, b0;
X
X-- N = 4
XSELECT b3 b4, b2 b3, b1 b2, b0 b1, b0, level
X    FROM t3
XUNION
XSELECT b3, b2, b1, b0, body, (level + 1) level
X    FROM hierarchy h, t3
X    WHERE t3.b0 = h.boss
X      AND t3.b1 != t3.b0
X    INTO TEMP t4;
X
X--SELECT "T4" tag, t4.*, h.name
X--  FROM t4, hierarchy h
X--  WHERE t4.b0 = h.body
X--  ORDER BY b4, b3, b2, b1, level, b0;
X
X-- N = 5
XSELECT b4 b5, b3 b4, b2 b3, b1 b2, b0 b1, b0, level
X    FROM t4
XUNION
XSELECT b4, b3, b2, b1, b0, body, (level + 1) level
X    FROM hierarchy h, t4
X    WHERE t4.b0 = h.boss
X      AND t4.b1 != t4.b0
X    INTO TEMP t5;
X
XSELECT "T5" tag, t5.*, h.name
X    FROM t5, hierarchy h
X    WHERE t5.b0 = h.body
X    ORDER BY b5, b4, b3, b2, b1, level, b0
X
XEND
X
XFORMAT
X
XON EVERY ROW
X    LET n = 3 * level + 1
X    PRINT COLUMN n, name CLIPPED
X
XEND
SHAR-EOF
chmod 444 hier1.ace
if [ `wc -c <hier1.ace` -ne 3114 ]
then echo shar: hier1.ace unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f hier1.sh -a "$1" != "-c" ]
then echo shar: hier1.sh already exists
else
echo 'x - hier1.sh (4462 characters)'
sed -e 's/^X//' >hier1.sh <<'SHAR-EOF'
X:	"@(#)hier1.sh	1.1 93/02/27"
X#
X# @(#)Query sequence to demonstrate Depth-First-Search Techniques.
X
X# Sorting the results is a major problem!
X# Given:
X# RANGE h IS hierarchy
X# FORALL h (h.boss < h.body)
X# then: the sort can be done on the basis of body number.
X#
X# However, if the official body numbers are allocated arbitrarily, then
X# the technique used near the bottom of this file allocates new sequence
X# numbers which satisfy the criterion above.  It works, but it is very
X# messy, and it could not be used in an ACE report.
X
Xcat <<'EOF' |
X
XSELECT body b0, 0 level
X	FROM hierarchy
X	WHERE boss = 0
X	INTO TEMP t0;
X
XSELECT "T0" tag, t0.*, h.name
X	FROM t0, hierarchy h
X	WHERE t0.b0 = h.body
X	ORDER BY level, b0;
X
X-- Start inductive procedure!
XSELECT t0.b0 b1, t0.b0 b0, t0.level level
X	FROM t0
XUNION
XSELECT t0.b0, h.body, (t0.level + 1)
X	FROM hierarchy h, t0
X	WHERE t0.b0 = h.boss
X	INTO TEMP t1;
X
XSELECT "T1" tag, t1.*, h.name
X	FROM t1, hierarchy h
X	WHERE t1.b0 = h.body
X	ORDER BY b1, level, b0;
X
XSELECT t1.b1 b2, t1.b0 b1, t1.b0, t1.level
X	FROM t1
XUNION
XSELECT t1.b1, t1.b0, h.body, (t1.level + 1)
X	FROM hierarchy h, t1
X	WHERE t1.b0 = h.boss
X	  AND t1.b1 != t1.b0
X	INTO TEMP t2;
X
XSELECT "T2" tag, t2.*, h.name
X	FROM t2, hierarchy h
X	WHERE t2.b0 = h.body
X	ORDER BY b2, b1, level, b0;
X
XSELECT b2 b3, b1 b2, b0 b1, b0, level
X	FROM t2
XUNION
XSELECT b2, b1, b0, body, (level + 1) level
X	FROM hierarchy h, t2
X	WHERE t2.b0 = h.boss
X	  AND t2.b1 != t2.b0
X	INTO TEMP t3;
X
XSELECT "T3" tag, t3.*, h.name
X	FROM t3, hierarchy h
X	WHERE t3.b0 = h.body
X	ORDER BY b3, b2, b1, level, b0;
X
XSELECT b3 b4, b2 b3, b1 b2, b0 b1, b0, level
X	FROM t3
XUNION
XSELECT b3, b2, b1, b0, body, (level + 1) level
X	FROM hierarchy h, t3
X	WHERE t3.b0 = h.boss
X	  AND t3.b1 != t3.b0
X	INTO TEMP t4;
X
XSELECT "T4" tag, t4.*, h.name
X	FROM t4, hierarchy h
X	WHERE t4.b0 = h.body
X	ORDER BY b4, b3, b2, b1, level, b0;
X
XSELECT b4 b5, b3 b4, b2 b3, b1 b2, b0 b1, b0, level
X	FROM t4
XUNION
XSELECT b4, b3, b2, b1, b0, body, (level + 1) level
X	FROM hierarchy h, t4
X	WHERE t4.b0 = h.boss
X	  AND t4.b1 != t4.b0
X	INTO TEMP t5;
X
XSELECT "T5" tag, t5.*, h.name
X	FROM t5, hierarchy h
X	WHERE t5.b0 = h.body
X	ORDER BY b5, b4, b3, b2, b1, level, b0;
X
X-- ********************************
X-- **    The fun starts here!    **
X-- ** This cannot be done in ACE **
X-- ********************************
XCREATE TEMP TABLE t6
X(
X	seq		SERIAL,
X	b5		INTEGER,
X	b4		INTEGER,
X	b3		INTEGER,
X	b2		INTEGER,
X	b1		INTEGER,
X	b0		INTEGER,
X	level	INTEGER
X);
X
X-- Ensure that all the level 0 people have smaller numbers sequence numbers
X-- than the level 1 people, etc.
XINSERT INTO t6
X	SELECT 0, b5, b4, b3, b2, b1, b0, level
X		FROM t5
X		WHERE level = 0;
XINSERT INTO t6
X	SELECT 0, b5, b4, b3, b2, b1, b0, level
X		FROM t5
X		WHERE level = 1;
XINSERT INTO t6
X	SELECT 0, b5, b4, b3, b2, b1, b0, level
X		FROM t5
X		WHERE level = 2;
XINSERT INTO t6
X	SELECT 0, b5, b4, b3, b2, b1, b0, level
X		FROM t5
X		WHERE level = 3;
XINSERT INTO t6
X	SELECT 0, b5, b4, b3, b2, b1, b0, level
X		FROM t5
X		WHERE level = 4;
XINSERT INTO t6
X	SELECT 0, b5, b4, b3, b2, b1, b0, level
X		FROM t5
X		WHERE level = 5;
X
XSELECT "T6" tag, t6.*, h.name
X	FROM t6, hierarchy h
X	WHERE t6.b0 = h.body
X	ORDER BY level, seq, b5, b4, b3, b2, b1, b0;
X
X-- Now, remap the body number columns to use the newly allocated
X-- sequence numbers.  Note that the self-join of t6r and t6a could be
X-- eliminated and t6a would supply the values labelled body and level
X-- instead.  However, the supplied solution is more symmetric, and
X-- performance isn't an issue when proving the technology!
X
XSELECT	t6f.seq b5,
X		t6e.seq b4,
X		t6d.seq b3,
X		t6c.seq b2,
X		t6b.seq b1,
X		t6a.seq b0,
X		t6r.b0  body,
X		t6r.level
X	FROM
X		t6	t6r,		-- Reference
X		t6	t6a,		-- Self-join for b0
X		t6	t6b,		-- Self-join for b1
X		t6	t6c,		-- Self-join for b2
X		t6	t6d,		-- Self-join for b3
X		t6	t6e,		-- Self-join for b4
X		t6	t6f			-- Self-join for b5
X	WHERE t6r.b5 = t6f.b0
X	  AND t6r.b4 = t6e.b0
X	  AND t6r.b3 = t6d.b0
X	  AND t6r.b2 = t6c.b0
X	  AND t6r.b1 = t6b.b0
X	  AND t6r.b0 = t6a.b0
XINTO TEMP T7;
X
X-- Now we can sequence the data correctly (adequately) by sorting on
X-- all the re-allocated body number columns.
X
XSELECT "T7" tag, t7.*, h.name
X	FROM t7, hierarchy h
X	WHERE t7.body = h.body
X	ORDER BY b5, b4, b3, b2, b1, b0;
XEOF
X
Xsqlcmd -D'	' -d parts -f - |
Xawk -F'	' '#
X{
X	# Print table name and body numbers in 4-character fields
X	printf("%-2s  ", $1);
X	for (i = 2; i < NF; i++)
X		printf("%2d  ", $i);
X	printf("%s\n",$NF);
X}'
SHAR-EOF
chmod 444 hier1.sh
if [ `wc -c <hier1.sh` -ne 4462 ]
then echo shar: hier1.sh unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f hier2.ace -a "$1" != "-c" ]
then echo shar: hier2.ace already exists
else
echo 'x - hier2.ace (499 characters)'
sed -e 's/^X//' >hier2.ace <<'SHAR-EOF'
X-- @(#)hier2.ace	1.2 93/03/03
X-- @(#)Print Formatting for Hierarchical (Breadth-First-Search) Selection
X
XDATABASE ASCII END
X
XDEFINE
X    VARIABLE n INTEGER
X	ASCII
X		b6		INTEGER,
X		b5		INTEGER,
X		b4		INTEGER,
X		b3		INTEGER,
X		b2		INTEGER,
X		b1		INTEGER,
X		b0		INTEGER,
X		level	INTEGER,
X		name	CHAR(30)
XEND
X
XOUTPUT
X    TOP    MARGIN 0
X    BOTTOM MARGIN 0
X    LEFT   MARGIN 0
XEND
X
XREAD "hier2.out" DELIMITER "|"
X
XEND
X
XFORMAT
X
XON EVERY ROW
X    LET n = 3 * level + 1
X    PRINT COLUMN n, name CLIPPED
X
XEND
SHAR-EOF
chmod 444 hier2.ace
if [ `wc -c <hier2.ace` -ne 499 ]
then echo shar: hier2.ace unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f hier2.sh -a "$1" != "-c" ]
then echo shar: hier2.sh already exists
else
echo 'x - hier2.sh (3576 characters)'
sed -e 's/^X//' >hier2.sh <<'SHAR-EOF'
X:	"@(#)hier2.sh	1.1 93/03/03"
X#
X# @(#)Query sequence to demonstrate Depth-First-Search Techniques.
X
X# Sorting the results is a major problem!
X# Given:
X# RANGE h IS hierarchy
X# FORALL h (h.boss < h.body)
X# then: the sort can be done on the basis of body number.
X#
X# However, if the official body numbers are allocated arbitrarily, then
X# the technique used near the bottom of this file allocates new sequence
X# numbers which satisfy the criterion above.  It works, but it is very
X# messy, and it could not be used in an ACE report.
X
Xcat <<'EOF' |
X
XSELECT body b0, 0 level
X	FROM hierarchy
X	WHERE boss = 0
XINTO TEMP t0;
X
X-- Start inductive procedure!
XSELECT t0.b0 b1, t0.b0 b0, t0.level level
X	FROM t0
XUNION
XSELECT t0.b0, h.body, (t0.level + 1)
X	FROM hierarchy h, t0
X	WHERE t0.b0 = h.boss
XINTO TEMP t1;
X
XSELECT t1.b1 b2, t1.b0 b1, t1.b0, t1.level
X	FROM t1
XUNION
XSELECT t1.b1, t1.b0, h.body, (t1.level + 1)
X	FROM hierarchy h, t1
X	WHERE t1.b0 = h.boss
X	  AND t1.b1 != t1.b0
XINTO TEMP t2;
X
XSELECT b2 b3, b1 b2, b0 b1, b0, level
X	FROM t2
XUNION
XSELECT b2, b1, b0, body, (level + 1) level
X	FROM hierarchy h, t2
X	WHERE t2.b0 = h.boss
X	  AND t2.b1 != t2.b0
XINTO TEMP t3;
X
XSELECT b3 b4, b2 b3, b1 b2, b0 b1, b0, level
X	FROM t3
XUNION
XSELECT b3, b2, b1, b0, body, (level + 1) level
X	FROM hierarchy h, t3
X	WHERE t3.b0 = h.boss
X	  AND t3.b1 != t3.b0
XINTO TEMP t4;
X
XSELECT b4 b5, b3 b4, b2 b3, b1 b2, b0 b1, b0, level
X	FROM t4
XUNION
XSELECT b4, b3, b2, b1, b0, body, (level + 1) level
X	FROM hierarchy h, t4
X	WHERE t4.b0 = h.boss
X	  AND t4.b1 != t4.b0
XINTO TEMP t5;
X
X-- ********************************
X-- **    The fun starts here!    **
X-- ** This cannot be done in ACE **
X-- ********************************
XCREATE TEMP TABLE t6
X(
X	seq		SERIAL,
X	b5		INTEGER,
X	b4		INTEGER,
X	b3		INTEGER,
X	b2		INTEGER,
X	b1		INTEGER,
X	b0		INTEGER,
X	level	INTEGER
X);
X
X-- Ensure that all the level 0 people have smaller numbers sequence numbers
X-- than the level 1 people, etc.
XINSERT INTO t6
X	SELECT 0, b5, b4, b3, b2, b1, b0, level
X		FROM t5
X		WHERE level = 0;
XINSERT INTO t6
X	SELECT 0, b5, b4, b3, b2, b1, b0, level
X		FROM t5
X		WHERE level = 1;
XINSERT INTO t6
X	SELECT 0, b5, b4, b3, b2, b1, b0, level
X		FROM t5
X		WHERE level = 2;
XINSERT INTO t6
X	SELECT 0, b5, b4, b3, b2, b1, b0, level
X		FROM t5
X		WHERE level = 3;
XINSERT INTO t6
X	SELECT 0, b5, b4, b3, b2, b1, b0, level
X		FROM t5
X		WHERE level = 4;
XINSERT INTO t6
X	SELECT 0, b5, b4, b3, b2, b1, b0, level
X		FROM t5
X		WHERE level = 5;
X
X-- Now, remap the body number columns to use the newly allocated
X-- sequence numbers.  Note that the self-join of t6r and t6a could be
X-- eliminated and t6a would supply the values labelled body and level
X-- instead.  However, the supplied solution is more symmetric, and
X-- performance isn't an issue when proving the technology!
X
XSELECT	t6f.seq b5,
X		t6e.seq b4,
X		t6d.seq b3,
X		t6c.seq b2,
X		t6b.seq b1,
X		t6a.seq b0,
X		t6r.b0  body,
X		t6r.level
X	FROM
X		t6	t6r,		-- Reference
X		t6	t6a,		-- Self-join for b0
X		t6	t6b,		-- Self-join for b1
X		t6	t6c,		-- Self-join for b2
X		t6	t6d,		-- Self-join for b3
X		t6	t6e,		-- Self-join for b4
X		t6	t6f			-- Self-join for b5
X	WHERE t6r.b5 = t6f.b0
X	  AND t6r.b4 = t6e.b0
X	  AND t6r.b3 = t6d.b0
X	  AND t6r.b2 = t6c.b0
X	  AND t6r.b1 = t6b.b0
X	  AND t6r.b0 = t6a.b0
XINTO TEMP T7;
X
X-- Now we can sequence the data correctly (adequately) by sorting on
X-- all the re-allocated body number columns.
X
XSELECT t7.*, h.name
X	FROM t7, hierarchy h
X	WHERE t7.body = h.body
X	ORDER BY b5, b4, b3, b2, b1, b0;
XEOF
X
Xsqlcmd -F unload -D'|' -d parts -f - >hier2.out
X
Xsaceprep -q hier2 && sacego -q hier2
SHAR-EOF
chmod 444 hier2.sh
if [ `wc -c <hier2.sh` -ne 3576 ]
then echo shar: hier2.sh unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f partexp1.sh -a "$1" != "-c" ]
then echo shar: partexp1.sh already exists
else
echo 'x - partexp1.sh (1619 characters)'
sed -e 's/^X//' >partexp1.sh <<'SHAR-EOF'
X#!/bin/ksh
X#
X#	"@(#)partexp1.sh	1.1 93/02/27"
X#
X#	Run parts explosion.
X
X# This parts explosion produces the picking list of elementary
X# components needed to build 1 unit of any particular assembly.
X
Xtmp=${TMPDIR:-/tmp}/mk2.$$
Xtrap "rm -f ${tmp}.*; exit 1" 1 2 3 13 15
Xp1=${tmp}.1
Xp2=${tmp}.2
Xp3=${tmp}.3
Xp4=${tmp}.4
Xp5=${tmp}.5
X
Xcat >$p1 <<'EOF'
XSELECT *
X	FROM bom
X	INTO TEMP t1;
X
XSELECT "Number of parts not expanded", COUNT(*)
X	FROM t1, bom
X	WHERE t1.p2 = bom.p1;
XEOF
X
Xcat >$p2 <<'EOF'
XSELECT p1, p2, qty
X	FROM t1
X	WHERE p2 NOT IN (SELECT p1 FROM bom)
XUNION ALL
XSELECT t1.p1, bom.p2, SUM(bom.qty * t1.qty) qty
X	FROM bom, t1
X	WHERE t1.p2 = bom.p1
X	GROUP BY 1, 2
XINTO TEMP t0;
X
XDELETE FROM t1;
X
XINSERT INTO t1
X	SELECT p1, p2, SUM(qty) qty
X		FROM t0
X		GROUP BY p1, p2;
X
XDROP TABLE t0;
X
XSELECT "Number of parts not expanded", COUNT(*)
X	FROM t1, bom
X	WHERE t1.p2 = bom.p1;
XEOF
X
Xcat >$p3 <<'EOF'
XSELECT t1.qty, p1.pname p1, p2.pname p2
X	FROM parts p1, parts p2, t1
X	WHERE t1.p1 = p1.pno
X	  AND t1.p2 = p2.pno
X	ORDER BY p1, qty;
XEOF
X
Xcat >$p4 <<'EOF'
XSELECT "--", COUNT(*)
X	FROM t1, bom
X	WHERE t1.p2 = bom.p1;
XEOF
X
Xiterations="-e 'SET EXPLAIN ON'"
Xiterations=""
X((i=1))
Xwhile ((i<10))
Xdo
X	echo "`date`: Level $i"
X	{
X	echo "\nLevel $i"
X	eval sqlcmd "-D'	'" -d parts $p1 $iterations $p4 $p3
X	} >$p5
X	grep "^--[ 	]*0$" $p5 >/dev/null && break
X	iterations="$iterations $p2"
X	((i=i+1))
Xdone
Xecho "`date`: Done"
X
Xcat $p5 |
Xawk -F'	' '#
X/^--/	{ next; }
X		{
X			if (NF != 3)
X				print;
X			else
X			{
X				if ($2 != old)
X				{
X					print "";
X					old = $2;
X				}
X				printf("%4d  %-32s %-32s\n", $1, $2, $3);
X			}
X		}'
X
Xrm -f ${tmp}.*
SHAR-EOF
chmod 444 partexp1.sh
if [ `wc -c <partexp1.sh` -ne 1619 ]
then echo shar: partexp1.sh unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f partexp2.sh -a "$1" != "-c" ]
then echo shar: partexp2.sh already exists
else
echo 'x - partexp2.sh (1907 characters)'
sed -e 's/^X//' >partexp2.sh <<'SHAR-EOF'
X#!/bin/ksh
X#
X#	"@(#)partexp2.sh	1.1 93/02/27"
X#
X#	Run parts explosion Mk II
X
X# This parts explosion produces the picking list of elementary
X# components needed to build 1 unit of any particular assembly.
X# It lists part names as well as part numbers.
X
Xtmp=${TMPDIR:-/tmp}/mk2.$$
Xtrap "rm -f ${tmp}.*; exit 1" 1 2 3 13 15
Xp1=${tmp}.1
Xp2=${tmp}.2
Xp3=${tmp}.3
Xp4=${tmp}.4
Xp5=${tmp}.5
X
Xcat >$p1 <<'EOF'
X
XSELECT *
X	FROM bom
X	INTO TEMP t1;
X
XSELECT "Parts unexpanded", COUNT(*)
X	FROM t1, bom
X	WHERE t1.p2 = bom.p1;
X
XEOF
X
Xcat >$p2 <<'EOF'
X
XSELECT p1, p2, qty
X	FROM t1
X	WHERE p2 NOT IN (SELECT p1 FROM bom)
XUNION ALL
XSELECT t1.p1, bom.p2, SUM(bom.qty * t1.qty) qty
X	FROM bom, t1
X	WHERE t1.p2 = bom.p1
X	GROUP BY 1, 2
XINTO TEMP t0;
X
XDROP TABLE t1;
X
XSELECT p1, p2, SUM(qty) qty
X	FROM t0
X	GROUP BY p1, p2
X	INTO TEMP t1;
X
XDROP TABLE t0;
X
XSELECT "Parts unexpanded", COUNT(*)
X	FROM t1, bom
X	WHERE t1.p2 = bom.p1;
X
XEOF
X
Xcat >$p3 <<'EOF'
XSELECT t1.qty, p1.pno p1n, p1.pname p1,
X		p2.pno p2n, p2.pname p2
X	FROM parts p1, parts p2, t1
X	WHERE t1.p1 = p1.pno
X	  AND t1.p2 = p2.pno
X	ORDER BY p1, qty, p2;
XEOF
X
Xcat >$p4 <<'EOF'
XSELECT "--", COUNT(*)
X	FROM t1, bom
X	WHERE t1.p2 = bom.p1
XEOF
X
Xiterations="-e 'SET EXPLAIN ON'"
Xiterations=""
X((i=1))
Xwhile ((i<10))
Xdo
X	echo "`date`: Level $i"
X	{
X	echo "\nLevel $i"
X	eval sqlcmd "-D'	'" -d parts $p1 $iterations $p4 $p3
X	} >$p5
X	grep "^--[ 	]*0$" $p5 >/dev/null && break
X	iterations="$iterations $p2"
X	((i=i+1))
Xdone
Xecho "`date`: Done"
X
Xcat $p5 |
Xawk -F'	' '#
X/^--/	{
X			printf("\n");
X			printf("%3s %-32s %-37s\n",
X					"Qty", "Assembly", "Component");
X			printf("%3s %-32s %-37s\n",
X					"---", "--------", "---------");
X			next;
X		}
X		{
X			if (NF != 5)
X				print;
X			else
X			{
X				if ($2 != old)
X				{
X					print "";
X					old = $2;
X				}
X				else
X				{
X					$2 = "";
X					$3 = "";
X				}
X				printf("%3d %-3s %-28s %-3s %-28s\n",
X						$1, $2, $3, $4, $5);
X			}
X		}'
X
Xrm -f ${tmp}.*
SHAR-EOF
chmod 444 partexp2.sh
if [ `wc -c <partexp2.sh` -ne 1907 ]
then echo shar: partexp2.sh unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f partexp3.sh -a "$1" != "-c" ]
then echo shar: partexp3.sh already exists
else
echo 'x - partexp3.sh (2304 characters)'
sed -e 's/^X//' >partexp3.sh <<'SHAR-EOF'
X#!/bin/ksh
X#
X#	"@(#)partexp3.sh	1.2 93/02/27"
X#
X#	Run parts explosion Mk II
X
X# This parts explosion produces the picking list of components needed
X# to build 1 unit of any particular assembly, but the list is layered
X# so that you get the information about the components needed for the
X# assemblies at each level.
X
Xtmp=${TMPDIR:-/tmp}/mk2.$$
Xtrap "rm -f ${tmp}.*; exit 1" 1 2 3 13 15
Xp1=${tmp}.1
Xp2=${tmp}.2
Xp3=${tmp}.3
Xp4=${tmp}.4
Xp5=${tmp}.5
X
Xcat >$p1 <<'EOF'
XSELECT *, 1 level
X	FROM bom
X	INTO TEMP t1;
X
XSELECT "Parts unexpanded", COUNT(*)
X	FROM t1, bom
X	WHERE t1.p2 = bom.p1
X	  AND t1.level = (SELECT MAX(level) FROM t1);
XEOF
X
Xcat >$p2 <<'EOF'
XSELECT p1, p2, qty, level
X	FROM t1
XUNION ALL
XSELECT t1.p1, bom.p2, SUM(bom.qty * t1.qty) qty, t1.level + 1 level
X	FROM bom, t1
X	WHERE t1.p2 = bom.p1
X	  AND level = (SELECT MAX(level) FROM t1)
X	GROUP BY 1, 2, 4
XINTO TEMP t0;
X
XDELETE FROM t1;
X
XINSERT INTO t1
X	SELECT p1, p2, SUM(qty) qty, level
X		FROM t0
X		GROUP BY p1, p2, level;
X
XDROP TABLE t0;
X
XUPDATE STATISTICS FOR TABLE t1;
X
XSELECT "Parts unexpanded", COUNT(*)
X	FROM t1, bom
X	WHERE t1.p2 = bom.p1
X	  AND t1.level = (SELECT MAX(level) FROM t1);
XEOF
X
Xcat >$p3 <<'EOF'
XSELECT t1.qty, p1.pno p1n, p1.pname p1, p2.pno p2n, p2.pname p2, t1.level
X	FROM parts p1, parts p2, t1
X	WHERE t1.p1 = p1.pno
X	  AND t1.p2 = p2.pno
X	ORDER BY p1, level, qty, p2;
XEOF
X
Xcat >$p4 <<'EOF'
XSELECT "--", COUNT(*)
X	FROM t1, bom
X	WHERE t1.p2 = bom.p1
X	  AND t1.level = (SELECT MAX(level) FROM t1);
XEOF
X
Xiterations="-e 'SET EXPLAIN ON'"
Xiterations=""
X((i=1))
Xwhile ((i<10))
Xdo
X	echo "`date`: Level $i"
X	{
X	echo "\nLevel $i"
X	eval sqlcmd "-D'	'" -d parts $p1 $iterations $p4 $p3
X	} > $p5
X	grep "^--[ 	]*0$" $p5 >/dev/null && break
X	iterations="$iterations $p2"
X	((i=i+1))
Xdone
Xecho "`date`: Done"
X
Xcat $p5 |
Xawk -F'	' '#
X/^--/	{
X			printf("\n");
X			printf("%5s %5s %-32s %-32s\n",
X					"Level", "Qty", "Assembly", "Component");
X			printf("%5s %5s %-32s %-32s\n",
X					"-----", "---", "--------", "---------");
X			next;
X		}
X		{
X			if (NF != 6)
X				print;
X			else
X			{
X				if ($2 != old2)
X				{
X					print "";
X					print "";
X					old2 = $2;
X					old6 = $6;
X				}
X				else if ($6 != old6)
X				{
X					print "";
X					old6 = $6;
X				}
X				printf("%5d %5d %-4s %-27s %-4s %-27s\n",
X						$6, $1, $2, $3, $4, $5);
X			}
X		}'
X
Xrm -f ${tmp}.*
SHAR-EOF
chmod 444 partexp3.sh
if [ `wc -c <partexp3.sh` -ne 2304 ]
then echo shar: partexp3.sh unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f partexp4.sh -a "$1" != "-c" ]
then echo shar: partexp4.sh already exists
else
echo 'x - partexp4.sh (2367 characters)'
sed -e 's/^X//' >partexp4.sh <<'SHAR-EOF'
X#!/bin/ksh
X#
X#	"@(#)partexp4.sh	1.1 93/02/27"
X#
X#	Run parts explosion Mk II
X
X# This parts explosion produces the picking list of components needed
X# to build 1 unit of any particular assembly, but the list is layered
X# so that you get the information about the components needed for the
X# assemblies at each level, and a record of which sub-assembly each
X# set of components is destined for.
X
Xtmp=${TMPDIR:-/tmp}/mk2.$$
Xtrap "rm -f ${tmp}.*; exit 1" 1 2 3 13 15
Xp1=${tmp}.1
Xp2=${tmp}.2
Xp3=${tmp}.3
Xp4=${tmp}.4
Xp5=${tmp}.5
X
Xcat >$p1 <<'EOF'
XSELECT p1, p1 expof, p2, qty, 1 level
X	FROM bom
X	INTO TEMP t1;
X
XSELECT "Parts unexpanded", COUNT(*)
X	FROM t1, bom
X	WHERE t1.p2 = bom.p1
X	  AND t1.level = (SELECT MAX(level) FROM t1);
XEOF
X
Xcat >$p2 <<'EOF'
XSELECT p1, expof, p2, qty, level
X	FROM t1
XUNION ALL
XSELECT t1.p1, t1.p2 expof, bom.p2, SUM(bom.qty * t1.qty) qty, t1.level + 1 level
X	FROM bom, t1
X	WHERE t1.p2 = bom.p1
X	  AND level = (SELECT MAX(level) FROM t1)
X	GROUP BY 1, 2, 3, 5
XINTO TEMP t0;
X
XDELETE FROM t1;
X
XINSERT INTO t1
X	SELECT p1, expof, p2, SUM(qty) qty, level
X		FROM t0
X		GROUP BY p1, expof, p2, level;
X
XDROP TABLE t0;
X
XUPDATE STATISTICS FOR TABLE t1;
X
XSELECT "Parts unexpanded", COUNT(*)
X	FROM t1, bom
X	WHERE t1.p2 = bom.p1
X	  AND t1.level = (SELECT MAX(level) FROM t1);
XEOF
X
Xcat >$p3 <<'EOF'
XSELECT t1.qty, p1.pno p1n, p1.pname p1,
X		p2.pno p2n, p2.pname p2, t1.level, t1.expof
X	FROM parts p1, parts p2, t1
X	WHERE t1.p1 = p1.pno
X	  AND t1.p2 = p2.pno
X	ORDER BY p1, level, expof, qty, p2;
XEOF
X
Xcat >$p4 <<'EOF'
XSELECT "--", COUNT(*)
X	FROM t1, bom
X	WHERE t1.p2 = bom.p1
X	  AND t1.level = (SELECT MAX(level) FROM t1);
XEOF
X
Xiterations="-e 'SET EXPLAIN ON'"
Xiterations=""
X((i=1))
Xwhile ((i<10))
Xdo
X	{
X	echo "\nLevel $i"
X	eval sqlcmd "-D'	'" -d parts $p1 $iterations $p4 $p3
X	} >$p5
X	grep "^--[ 	]*0$" $p5 >/dev/null && cat $p5 && break
X	iterations="$iterations $p2"
X	((i=i+1))
Xdone |
X
Xawk -F'	' '#
X/^--/	{
X			printf("\n");
X			printf("%3s %3s %-32s %-37s\n",
X					"Lvl", "Qty", "Assembly", "Component");
X			printf("%3s %3s %-32s %-37s\n",
X					"---", "---", "--------", "---------");
X			next;
X		}
X		{
X			if (NF != 7)
X				print;
X			else
X			{
X				if ($2 != old)
X				{
X					print "";
X					old = $2;
X				}
X				else
X				{
X					$2 = "";
X					$3 = "";
X				}
X				printf("%3d %3d %-3s %-28s %-3s %-3s %-28s\n",
X						$6, $1, $2, $3, $7, $4, $5);
X			}
X		}'
X
Xrm -f ${tmp}.*
SHAR-EOF
chmod 444 partexp4.sh
if [ `wc -c <partexp4.sh` -ne 2367 ]
then echo shar: partexp4.sh unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f pp.1.sh -a "$1" != "-c" ]
then echo shar: pp.1.sh already exists
else
echo 'x - pp.1.sh (774 characters)'
sed -e 's/^X//' >pp.1.sh <<'SHAR-EOF'
X-- @(#)pp.1.sh	1.1 93/02/27
X-- @(#)Transitive closure of PP
X
XSELECT *
X	FROM pp
X	INTO TEMP t1;
X
X-- Start repeat (Cycle A)
XSELECT major_p, minor_p
X	FROM t1
XUNION
XSELECT t1.major_p, pp.minor_p
X	FROM pp, t1
X	WHERE t1.minor_p = pp.major_p
XINTO TEMP t0;
X
XDROP TABLE t1;
X
X-- This count does not change when sufficient iterations have been done
XSELECT "List size", COUNT(*) FROM t0;
X-- End repeat (Cycle A)
X
X-- Start repeat (Cycle B)
XSELECT major_p, minor_p
X	FROM t0
XUNION
XSELECT t0.major_p, pp.minor_p
X	FROM pp, t0
X	WHERE t0.minor_p = pp.major_p
XINTO TEMP t1;
X
XDROP TABLE t0;
X
X-- This count does not change when sufficient iterations have been done
XSELECT "List size", COUNT(*) FROM t1;
X-- End repeat (Cycle B)
X
XHEADINGS ON;
XDELIM "\t";
XSELECT * FROM t1 ORDER BY major_p, minor_p;
SHAR-EOF
chmod 444 pp.1.sh
if [ `wc -c <pp.1.sh` -ne 774 ]
then echo shar: pp.1.sh unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f pp.sql -a "$1" != "-c" ]
then echo shar: pp.sql already exists
else
echo 'x - pp.sql (761 characters)'
sed -e 's/^X//' >pp.sql <<'SHAR-EOF'
X-- @(#)pp.sql	1.1 93/02/27
X-- @(#)Table and Data for Part Explosion Experiments
X
XCREATE TABLE pp
X(
X	major_p	CHAR(2) NOT NULL,
X	minor_p CHAR(2) NOT NULL
X);
X
XINSERT INTO pp (major_p, minor_p) VALUES ("P1", "P2");
XINSERT INTO pp (major_p, minor_p) VALUES ("P1", "P3");
XINSERT INTO pp (major_p, minor_p) VALUES ("P1", "P4");
XINSERT INTO pp (major_p, minor_p) VALUES ("P2", "P3");
XINSERT INTO pp (major_p, minor_p) VALUES ("P2", "P5");
XINSERT INTO pp (major_p, minor_p) VALUES ("P3", "P5");
XINSERT INTO pp (major_p, minor_p) VALUES ("P3", "P6");
XINSERT INTO pp (major_p, minor_p) VALUES ("P4", "P3");
XINSERT INTO pp (major_p, minor_p) VALUES ("P4", "P6");
XINSERT INTO pp (major_p, minor_p) VALUES ("P7", "P8");
XINSERT INTO pp (major_p, minor_p) VALUES ("P7", "P9");
SHAR-EOF
chmod 444 pp.sql
if [ `wc -c <pp.sql` -ne 761 ]
then echo shar: pp.sql unpacked with wrong size
fi
# end of overwriting check
fi
echo All files extracted
exit 0