#!/bin/sh
#
#  This shell script unpacks itself and creates DBREF utility sources.
#  Give this file permissions executable and type in its name.
#
sed -e 's/^X//' >READ.ME <<'SHAR-EOF'
X
X
X	File:  READ.ME
X
X	Dear Colleagues,
X
X	The DBREF utility analyzes Referential Integrity Rules and 
X	forms load/unload/delete scripts in accordance with these rules.
X	The algorithm used is described in theory.txt file and coded
X	in omega.4gl file. It also displays all referential constraints
X	and/or all the erroneous constraints in the following format:
X
X	Foreign table.column	Primary table.column	Constraint Name
X	---------------------------------------------------------------
X	f_tabname.f_colname	p_tabname.p_colname	constr_name
X	.			.			.
X	
X	Only the references causing "cycling" of the RI "forest" are listed.
X
X	Files are used:
X	  READ.ME       - you are reading it now
X	  theory.txt    - the algorithm description
X	  Makefile      - make command file to compile utility
X	  dbref.man     - manual page
X	  globals.4gl   - 4GL globals
X	  dbref.4gl     - main module
X	  input.4gl     - system catalog reading module
X	  omega.4gl     - the algorithm code
X	  output.4gl    - SQL scripts and output files creation module
X	  getopt.4gl    - command line parsing routine written by John Leffler
X
X	To compile utility type in:
X	  make          - to get RDS version
X	  make rds      - to get RDS version
X	  make dbref    - to get executable version
X	  make exe      - to get executable version
X	  make pack     - to get wraped package in file dbref.sh
X
X	It is very convenient to use this utility during the development
X	phase of the project, when the database architecture changes often 
X	(since INFORMIX doesn't check constraints for their logic correctness,
X	improperly built references may result in errors during unloading and
X	recovering procedures), and during the testing phase, when testers 
X	have to reload the test database due to errors in software logic 
X	in order to bring the test database to the previous state, or to
X	populate the database with manually prepared testing data.
X	
X	If you will use DBREF utility and find any bugs, please let me know.
X
X						Sincerely,
X						Pavel Kazenin,
X						E-mail: pavel@cso.spb.su
X						ph/fax: 7-812-316-3324
SHAR-EOF
sed -e 's/^X//' >theory.txt <<'SHAR-EOF'
X{******************************************************************************
X
X  Project:      DBREF utility design
X
X  Version:      @(#)theory.txt	2.1 9/12/95
X
X  Title:        theory.txt
X
X  Abstract:     ASCII text file
X
X  Functional Description:
X		Referential Tree Analyzing Algorithm description
X
X  Author:       Pavel Kazenin (pavel@cso.spb.su), 17-MAR-1995
X
X  Revision History:
X
X  --------Name-------- -----Revision Date-----
X  --------------------------Revision Description--------------------------
X  Pavel Kazenin        11-SEP-1995
X  - Turned to version 2.1
X  - Added "cutting off topmost tables" alogrithm description
X*******************************************************************************}
X
XDealing with multiple referential integrity rules is a real head-ache
Xfor any DBA. The algorithm below solves Referential Tree Problem.
XIn other words, it answers the question: can the database be reloaded using 
Xunload/load statements or wrong built references don't let do it and, 
Xif the answer is YES, what the proper loading order is.
X
XThe first step of the algorithm is to build Referential Rules Matrix [A].
X[A] matrix is a square NxN matrix, where N is total number of user defined 
Xtables ( systables.tabid > 99 ). The members of [A] matrix are assigned 
Xaccording to the following rule ( i,j represent TabID ):
X
X
X		 | 0, if tables i,j don't refer each other 
X	A(i,j) = |
X		 | 1, if table i referes table j ( i -> j )
X
XNote that in INFORMIX SQL implementation of the alogrithm no need to insert
Xinto [A] table all possible pairs (i,j). Instead, we insert actually existing 
Xreferences.
X
XAlso create [l] vector listing all user defined tables and thear hierarchy
Xlevels in the Referential Integrity Tree. Initially level members are NULLs. 
X
XThe second step of the algorithm is populating level members of the [l] vector
Xwith data. In DBREF Utility the following algorithm is used:
X
X	    #
X	    # Mark "roots"
X	    #
X	    assign level members value '1' for tables which don't refer to
X		      any others tables ( stand-alone or lookup tables )
X
X	    #
X	    # Slice Referential Tree on layers
X	    #
X	    for ( p_level = 2; 
X		  all tables processed; 
X		  p_level = p_level + 1 ) {
X		assign p_level to all [l] vector members
X		which do refer to tables with level less than p_level
X		  and don't refer to tables with NULL level }
X
XAfter the algorithm processed, sorted by level in ascending order [l] vector
Xmembers give us proper loading order. If there are any non-assigned level
Xmembers in [l] vector (level is still NULL), it means that respective tables 
Xare out of Referential Integrity Tree and such database cannot be reloaded 
Xusing load/unload statements. In this case we need to get exact information
Xon which referential constraints caused referential violations. For that we 
Xhave to separate the "topmost" tables from the "mesh". The algorithm is the 
Xsame. Only difference is that we apply it to matrix [A']: A'(i,j) = A(j,i)
Xand decrement p_level every step instead of to increment it.
X
XExample 1. No Referential Integrity Tree violations:
X
X			    (j)+---------------+
X			    10 | 0  0  0  0  0 |
X			     9 | 0  0  0  0  1 |
X		[A] matrix:  8 | 1  1  0  1  0 |
X			     7 | 0  0  0  0  1 |
X			     6 | 0  1  0  0  1 |
X			       +---------------+
X				 6  7  8  9 10 (i)
X
X		+---------------------------------------+
X   [l] vector:  | 1  2  3  4  5  6  7  8  9 10 11 12 13 |  (i)
X		| 1  1  1  1  1  2  3  1  2  4  1  1  1 |  (level)
X		+---------------------------------------+
X
XThe proper order in our example is:
X
X		  1, 2, 3, 4, 5, 11, 12, 13, 8, 6, 9, 7, 10
X
XExample 2. Bad chain  7 -> 6 -> 7
X
X			    (j)+---------------+
X			    10 | 0  0  0  0  0 |
X			     9 | 0  0  0  0  1 |
X		[A] matrix:  8 | 1  1  0  1  0 |
X			     7 | 1  0  0  0  1 |
X			     6 | 0  1  0  0  1 |
X			       +---------------+
X				 6  7  8  9 10 (i)
X
X		+---------------------------------------+
X   [l] vector:  | 1  2  3  4  5  6  7  8  9 10 11 12 13 |  (i)
X		| 1  1  1  1  1  N  N  1  2 -1  1  1  1 |  (level)
X		+---------------------------------------+
X
XSo, tables 6 and 7 cause Referential Integrity hierarchy violations.
SHAR-EOF
sed -e 's/^X//' >dbref.man <<'SHAR-EOF'
X	dbref (INFORMIX-OnLine)                                     Release 2
X
X	Name    dbref	\- Referential Integrity Analyzer 
X
X	Syntax       
X	
X	   dbref  [-eloru] [-d dbname] [-t [tabname]]
X
X	   The meanings of the available options are:
X
X            -e   Creates file containing list of bad RI constraints (LoadDB.err)
X	    -l   Creates database "load" script (LoadDB.sql)
X            -o   Redirects the list of RI constraints to file (LoadDB.lst)
X            -r   Creates database "delete" script (DeleteDB.sql)
X            -u   Creates database "unload" script (UnloadDB.sql)
X	    -d   Investigates database `dbname' ($DBNAME by default)
X            -t   Displays list of all RI constraints for table `tabname'
X                 or for all tables if `tabname' omitted. 
X
X	Description
X
X	     DBREF (DataBase REFerences analyzer) analyzes the Referential
X	   Integrity Rules. It reads them from "informix".sysreferences
X	   and "informix".sysconstraints Informix System catalog tables.
X	   Then it tries to order the Referential Integrity "Forest". 
X	     DBREF generates INFORMIX SQL scripts needed to reload a database 
X	   with Referential Constraints according to RI rules, outputs list of
X           existing RI constraints, and in case of RI violations creates list
X           of the RI constraints causing these violations in the format:
X
X	   Foreign table.column    Primary table.column    Constraint Name
X	   ---------------------------------------------------------------
X	   f_tabname.f_colname     p_tabname.p_colname     constr_name
X           
X	Compatibility
X
X	      DBREF utility was tested on the following equipment:
X
X		 Computer Model:    IBM PS/2 Model 95A
X		 Operating System:  SCO UNIX, release 3.0.0
X		 Filesystems:       AFS, EAFS
X		 Informix Engine:   Informix On-Line v.5.01
X		 Database Log Mode: ANSI
X
X	Diagnostics
X
X	   Exit status is:
X	      0, if no RI violations are found;
X	      1, if Informix System Error occured ( no CONNECT perms, etc );
X	      2, if syntax errors;
X	      3, if any RI violations are found;
X
X	See also
X
X	   dbexport (INFORMIX), dbimport (INFORMIX), dbload (INFORMIX)
X
X	Author
X
X	   Pavel Kazenin,   <pavel@cso.spb.su>
SHAR-EOF
sed -e 's/^X//' >Makefile <<'SHAR-EOF'
X#==========================================================================
X#
X#  Project:     DBREF utility design
X#
X#  Version:     @(#)Makefile	2.1 9/12/95
X#
X#  Title:       Makefile
X#
X#  Abstract:    Make command file
X#
X#  Functional Description:
X#               Makefile used to compile DBREF utility
X#
X#  Environment: Informix SQL/4GL
X#
X#  Author:      Pavel Kazenin (pavel@cso.spb.su) , 18-MAR-1995
X#
X#  Revision History:
X#
X#  --------Name-------- -----Revision Date-----
X#  --------------------------Revision Description--------------------------
X#  Pavel Kazenin        11-SEP-1995
X#  - Turned to version 2.1
X#  - Added command line parsing module - getopt, written by John Leffler
X#==========================================================================
X
X.SUFFIXES:
X.SUFFIXES:	.o .4go .4gl .4gl~ .frm .per .per~
X
XGET	=	get
XGFLAGS	=	-p
X
XSOURCES	=	READ.ME \
X		theory.txt \
X		dbref.man \
X		Makefile \
X		globals.4gl \
X		dbref.4gl \
X		input.4gl \
X		omega.4gl \
X		output.4gl \
X		getopt.4gl
X
XGL4	=	globals.4go \
X		dbref.4go \
X		input.4go \
X		omega.4go \
X		output.4go \
X		getopt.4go
X
XGO4	=	globals.o \
X		dbref.o \
X		input.o \
X		omega.o \
X		output.o \
X		getopt.o
X
XPERS	=
X
XGLOBALS	=	globals.4gl
X
XGI4	=	dbref.4gi
X
XGL4EXEC	=	dbref
X
X# RDS IS DEFAULT 
Xrds:		$(GI4)
Xexe:		$(GL4EXEC)
Xpack:		dbref.sh
X
X$(GI4):		$(GL4) $(PERS)
X		cat $(GL4) > $@
X
X$(GL4EXEC):	$(GO4) $(PERS)
X		c4gl -o $@ $(GO4)
X		strip $@
X
Xdbref.sh:	$(SOURCES)
X		@grep '^#%' Makefile | sed 's/^#%//' > packer
X		@chmod +x packer
X		@grep '^#!' Makefile | sed 's/^#!//' > dbref.sh
X		@packer $(SOURCES) >> dbref.sh
X		@chmod +x dbref.sh
X		@rm -f packer
X
X# precompile
X.4gl~.4gl:
X	$(GET) $(GFLAGS) s.$*.4gl > $*.4gl
X
X# precompile
X.4gl.4go:
X	fglpc -s -a $<
X
X# precompile
X.4gl.o:
X	c4gl -c $<
X
X# precompile
X.per~.per:
X	$(GET) $(GFLAGS) s.$*.per > $*.per
X
X# precompile
X.per.frm:
X	form4gl -s $*
X
X#
Xclean:
X	rm -f *.err
X	rm -f *.4ge
X	rm -f *.4go
X	rm -f *.4gi
X	rm -f *.frm
X
Xcclean:
X	rm -f *.ec
X	rm -f *.ic
X	rm -f *.c
X	rm -f *.o
X	rm -f *.a
X
X#
X#  Specific source-file dependencies
X#
X$(GL4): $(GLOBALS)
X$(GO4): $(GLOBALS)
X
X#
X# This is dbref.sh header to be executed
X#
X#!:
X#!#!/bin/sh
X#!#
X#!#  This shell script unpacks itself and creates DBREF utility sources.
X#!#  Give this file permissions executable and type in its name.
X#!#
X
X#
X# This is packer script ( Extraction from shar routine )
X#
X#%:
X#%#!/bin/sh
X#%for file in $*
X#%do
X#%   echo "sed -e 's/^X//' >$file <<'SHAR-EOF'"
X#%   sed -e 's/^/X/' $file
X#%   echo "SHAR-EOF"
X#%done
SHAR-EOF
sed -e 's/^X//' >globals.4gl <<'SHAR-EOF'
X{******************************************************************************
X
X  Project:      DBREF utility design
X
X  Version:      @(#)globals.4gl	2.1 9/12/95
X
X  Title:        globals.4gl   
X
X  Abstract:     4GL program file
X
X  Functional Description:
X		Dbref package globals definitions
X
X  Environment:  Informix SQL/4GL
X
X  Author:       Pavel Kazenin (pavel@cso.spb.su), 20-MAR-1995
X
X  Revision History:
X
X  --------Name-------- -----Revision Date-----
X  --------------------------Revision Description--------------------------
X  Pavel Kazenin        11-SEP-1995
X  - Turned to version 2.1
X  - Added exit_code - code returning
X  - Added args record - arguments passed out from command line
X*******************************************************************************}
X
Xglobals
X   
X   define dbname                    char(18),   # Database Name
X	  tablename		    char(18),	# Table Name
X	  logon                     char(8),    # loged in user name
X	  SccsID                    char(64),   # SCCS string for what(1)
X	  exit_code                 integer,    # code returning
X	  args          record
X	     d_flag		smallint,	# Database name indicator
X	     l_flag             smallint,       # 'load' script wanted
X	     u_flag             smallint,       # 'unload' script wanted
X	     r_flag             smallint,       # 'delete' script wanted
X	     e_flag             smallint,       # bad constraints list wanted
X	     t_flag             smallint,       # table investigating
X	     o_flag		smallint	# display | to file
X			end record,
X
X	#
X	# The following globals are taken from getopt.4gl 
X	#
X	optind  INTEGER,        { Where we've got through list }
X	argnum  INTEGER,        { Corresponds to ARGC }
X	argstr  CHAR(80),       { Current argument }
X	arglen  INTEGER,        { Length of current argument }
X	chridx  INTEGER,        { How far through argstr we have analysed }
X	no_opt  INTEGER         { Has `--' argument been read? }
X	     
Xend globals
SHAR-EOF
sed -e 's/^X//' >dbref.4gl <<'SHAR-EOF'
X{******************************************************************************
X
X  Project:      DBREF utility design
X
X  Version:      @(#)dbref.4gl	2.3 9/14/95 
X  
X  Title:        dbref.4gl   
X
X  Abstract:     4GL program file
X
X  Functional Description:
X		DBREF utility main module
X
X  Environment:  Informix SQL/4GL
X
X  Author:       Pavel Kazenin (pavel@cso.spb.su), 20-MAR-1995
X
X  Revision History:
X
X  --------Name-------- -----Revision Date-----
X  --------------------------Revision Description--------------------------
X  Pavel Kazenin        11-SEP-1995
X  - Turned to version 2.1
X  - Added argument line parsing using getopt() written by John Leffler
X  - Added RI constraints IDs in a_matrix table layout
X*******************************************************************************}
X
Xglobals "globals.4gl"
X
Xmain
X
Xdefine  optval           char(1),
X	optarg           char(80),
X	exec_str         char(40)
X
X   #
X   # Assign SCCS ID
X   #
X   let SccsID = "@(#)dbref.4gl	2.3 9/14/95"
X
X   #
X   # Command line parsing using getopt() function written by John Leffler
X   #
X   call getopt( "elorud:t:" ) returning optval, optarg
X   while optval is not null
X       case optval
X	 when "l" let args.l_flag = 1
X	 when "u" let args.u_flag = 1
X	 when "r" let args.r_flag = 1
X	 when "e" let args.e_flag = 1
X	 when "o" let args.o_flag = 1
X	 when "d"
X	  let args.d_flag = 1
X	  let dbname = optarg clipped
X	 when "t" 
X	  let args.t_flag = 1
X	  let tablename = optarg clipped
X	 otherwise  call usage()
X       end case
X       call getopt( "elorud:t:" ) returning optval, optarg
X   end while
X
X   #
X   # Getting Database Connection
X   #
X   if args.d_flag != 1 then let dbname = fgl_getenv("DBNAME") end if
X
X   if dbname = "                  " then
X      display "Neither $DBNAME nor -d option have not been assigned, bye-bye..."
X      exit program( 2 )
X   else
X      let exec_str = "database ", dbname clipped
X      prepare exec_p from exec_str
X      execute exec_p
X   end if
X
X   #
X   # Getting loged in user name
X   #
X   select user
X     into logon
X	from "informix".systables
X   where "informix".systables.tabid = 1
X
X   #
X   # Temporary tables creation ( [A] matrix, [l] and [t] vectors )
X   #
X   create temp table a_matrix
X	  (
X		a_i             integer,        # [A] matrix i index (tabid)
X		a_j             integer,        # [A] matrix j index (tabid)
X		a_i_constr      integer,        # foreign RI constraint ID
X		a_j_constr      integer         # primary RI constraint ID
X	  )  with no log
X   create index a_matrix0 on a_matrix ( a_i )
X   create index a_matrix1 on a_matrix ( a_j )
X
X   create temp table l_vector
X	  (
X		l_i     integer,                # [l] vector i index (tabid)
X		level   integer                 # table level in tree
X	  )  with no log
X   create index l_vector0 on l_vector ( l_i )
X   create index l_vector1 on l_vector ( level )
X
X   create temp table t_vector                   # to avoid error #360
X	  (
X		t_i     integer,                # [t] vector i index (tabid)
X		level   integer                 # table level in tree
X	  )  with no log
X   create index t_vector0 on t_vector ( t_i )
X   create index t_vector1 on t_vector ( level )
X
X   #
X   # [A] matrix creation using sysreference 
X   # and sysconstrains tables ( file input.4gl )
X   #
X   call input() 
X
X   #
X   # Sorting algorithm call ( file omega.4gl )
X   #
X   call omega()
X
X   #
X   # [l] vector analyzing and sql scripts generation
X   # using l_vector table ( file output.4gl )
X   #
X   call output()
X
X   #
X   # Return to shell with code {0|1}
X   #
X   call return_code()
X
Xend main
X
X#
X# Assign exit_code = {0, if RI rules are OK | 1, if RI violation }
X#
Xfunction return_code()
X
X   select unique 1 from l_vector where level is null
X   if status = NOTFOUND then let exit_code = 0 else let exit_code = 3 end if
X   exit program( exit_code )
X
Xend function
X
X#
X# Fuction usage() displays dbref usage and exits program with code -1
X#
Xfunction usage()
X
Xdisplay ""
Xdisplay "usage: dbref [-eloru] [-d dbname] [-t [tabname]]"
Xdisplay ""
Xdisplay "The meanings of the available options are:"
Xdisplay ""
Xdisplay " -e   Creates file containing list of bad RI constraints (LoadDB.err)"
Xdisplay " -l   Creates database \"load\" script (LoadDB.sql)"
Xdisplay " -o   Redirects the list of RI constraints to file (LoadDB.lst)"
Xdisplay " -r   Creates database \"delete\" script (DeleteDB.sql)"
Xdisplay " -u   Creates database \"unload\" script (UnloadDB.sql)"
Xdisplay " -d   Investigates database \`dbname\' ($DBNAME by default)"
Xdisplay " -t   Displays list of all RI constraints for table \`tabname\'"
Xdisplay "      or for all tables if \`tabname\' omitted."
Xdisplay ""
Xdisplay ""
Xdisplay "Exit status is:"
Xdisplay ""
Xdisplay "  0, if no RI violations are found;"
Xdisplay "  1, if Informix System Error occured ( no CONNECT perms, etc );"
Xdisplay "  2, if command line syntax errors;"
Xdisplay "  3, if any RI violations are found;"
Xdisplay ""
X
X	exit program( 2 )
X
Xend function
SHAR-EOF
sed -e 's/^X//' >input.4gl <<'SHAR-EOF'
X{******************************************************************************
X
X  Project:      DBREF utility design
X
X  Version:      @(#)input.4gl	2.1 9/12/95
X
X  Title:        input.4gl   
X
X  Abstract:     4GL program file
X
X  Functional Description:
X		Initial a_matrix and l_vector tables data creation
X
X  Environment:  Informix SQL/4GL
X
X  Author:       Pavel Kazenin (pavel@cso.spb.su), 20-MAR-1995
X
X  Revision History:
X
X  --------Name-------- -----Revision Date-----
X  --------------------------Revision Description--------------------------
X  Pavel Kazenin        11-SEP-1995
X  - Turned to version 2.1
X*******************************************************************************}
X
Xglobals "globals.4gl"
X
X#
X# Function input() populates a_matrix and l_vector tables with
X# data from sysreferences and sysconstraints tables.
X#
Xfunction input()
X   define foreign_id, primary_id, i, j          integer
X
X	#
X	# Assign SCCS ID
X	#
X	let SccsID = "@(#)input.4gl	2.1 9/12/95"
X
X	#
X	# Insert referential dependencies into a_matrix table
X	#
X	declare get_reference cursor for
X	  select constrid, primary, ptabid
X	    from "informix".sysreferences
X
X	foreach get_reference
X	  into foreign_id, primary_id, j
X
X	  select tabid
X	    into i
X	      from "informix".sysconstraints
X	  where "informix".sysconstraints.constrid = foreign_id
X	  
X	  insert into a_matrix values ( i, j, foreign_id, primary_id )
X
X	end foreach
X	free get_reference
X
X	#
X	# Insert TabIDs of all existing user-defined
X	# tables into l_vector
X	#
X	initialize j to null
X	declare get_table_id cursor for
X	   select tabid
X	     from "informix".systables
X		where "informix".systables.tabid > 99
X
X	foreach get_table_id into i
X	   insert into l_vector values ( i, j )
X	   insert into t_vector values ( i, j )
X	end foreach
X	free get_table_id
Xend function
SHAR-EOF
sed -e 's/^X//' >omega.4gl <<'SHAR-EOF'
X{******************************************************************************
X
X  Project:      DBREF utility design
X
X  Version:      @(#)omega.4gl	2.1 9/12/95
X
X  Title:        omega.4gl   
X
X  Abstract:     4GL program file
X
X  Functional Description:
X		Sorting algorithm implementation
X
X  Environment:  Informix SQL/4GL
X
X  Author:       Pavel Kazenin (pavel@cso.spb.su), 19-MAR-1995
X
X  Revision History:
X
X  --------Name-------- -----Revision Date-----
X  --------------------------Revision Description--------------------------
X  Pavel Kazenin        11-SEP-1995
X  - Turned to version 2.1
X  - Added cutting off the "topmost" tables in RI "forest"
X*******************************************************************************}
X
Xglobals "globals.4gl"
X
X#
X# function omega() provides algorithm described in theory.txt file
X#
Xfunction omega()
Xdefine  p_level,
X	loop            integer
X	
X	#
X	# Assign SCCS ID
X	#
X	let SccsID = "@(#)omega.4gl	2.1 9/12/95"
X
X	#
X	# Mark "roots"
X	#
X	let p_level = 1
X
X	update l_vector set level = p_level
X	  where l_i not in ( select unique a_i from a_matrix )
X	    and level is null
X
X	update t_vector set level = p_level
X	  where t_i not in ( select unique a_i from a_matrix )
X	    and level is null
X	
X	#
X	# Slice Referential Integrity Tree on layers
X	#
X	let loop = 1
X	while loop 
X	  let p_level = p_level + 1
X
X	  update t_vector set level = p_level
X	    where t_i in
X	       ( select unique a.a_i from a_matrix a
X		  where a.a_j in
X		     ( select l.l_i from l_vector l
X			where l.level < p_level ) )
X	      and t_i not in
X	       ( select unique a.a_i from a_matrix a
X		  where a.a_j in
X		     ( select l.l_i from l_vector l
X			where l.level is null ) )
X	      and level is null
X
X	  update l_vector set level = p_level
X		where l_i in
X		   ( select t.t_i from t_vector t
X			where t.level = p_level )
X
X	  select count(*) into loop
X	    from t_vector
X		where level = p_level
X
X	end while
X
X	#
X	# Perform further RI Tree analyzing - cut off all the "topmost"
X	# tables to separate the "mesh"
X	#
X	# Mark "anti-roots"
X	#
X	let p_level = -1
X
X	update l_vector set level = p_level
X	  where l_i not in ( select unique a_j from a_matrix )
X	    and level is null
X
X	update t_vector set level = p_level
X	  where t_i not in ( select unique a_j from a_matrix )
X	    and level is null
X	
X	#
X	# Slice Referential Integrity Tree on anti-layers
X	#
X	let loop = 1
X	while loop 
X	  let p_level = p_level - 1
X
X	  update t_vector set level = p_level
X	    where t_i in
X	       ( select unique a.a_j from a_matrix a
X		  where a.a_i in
X		     ( select l.l_i from l_vector l
X			where l.level > p_level ) )
X	      and t_i not in
X	       ( select unique a.a_j from a_matrix a
X		  where a.a_i in
X		     ( select l.l_i from l_vector l
X			where l.level is null ) )
X	      and level is null
X
X	  update l_vector set level = p_level
X		where l_i in
X		   ( select t.t_i from t_vector t
X			where t.level = p_level )
X
X	  select count(*) into loop
X	    from t_vector
X		where level = p_level
X
X	end while
Xend function
SHAR-EOF
sed -e 's/^X//' >output.4gl <<'SHAR-EOF'
X{******************************************************************************
X
X  Project:      DBREF utility design
X
X  Version:      @(#)output.4gl	2.4 9/18/95
X
X  Title:        output.4gl   
X
X  Abstract:     4GL program file
X
X  Functional Description:
X		Output diagnostic and sql files generation
X
X  Environment:  Informix SQL/4GL
X
X  Author:       Pavel Kazenin (pavel@cso.spb.su), 20-MAR-1995
X
X  Revision History:
X
X  --------Name-------- -----Revision Date-----
X  --------------------------Revision Description--------------------------
X  Pavel Kazenin        11-SEP-1995
X  - Turned to version 2.1
X  - Added "DeleteDB.sql" script creation
X  - Added "LoadDB.err" and "LoadDB.lst" files creation
X  Pavel Kazenin        18-SEP-1995
X  - Fixed bug in get_colname() in case of multiple referential constraint
X*******************************************************************************}
X
Xglobals "globals.4gl"
X
X#
X# This function determines which output file needed to be created
X#
Xfunction output()
X
X	#
X	# Assign SCCS ID
X	#
X	let SccsID = "@(#)output.4gl	2.4 9/18/95"
X
X	#
X	# Create output files ( if needed )
X	#
X	if args.l_flag = 1 then  call output_load_sql()		end if
X	if args.u_flag = 1 then  call output_unload_sql()	end if
X	if args.r_flag = 1 then  call output_delete_sql()	end if
X	if args.e_flag = 1 then  call output_errors_list()	end if
X	if args.t_flag = 1 then  call output_constr_list()	end if
X	
Xend function
X
X#
X# Create LoadDB.sql script
X#
Xfunction output_load_sql()
X	start report rp_load_sql to "LoadDB.sql"
X	output to report rp_load_sql()
X	finish report rp_load_sql
Xend function
X
X#
X# Create UnloadDB.sql script
X#
Xfunction output_unload_sql()
X	start report rp_unload_sql to "UnloadDB.sql"
X	output to report rp_unload_sql()
X	finish report rp_unload_sql
Xend function
X
X#
X# Create DeleteDB.sql script
X#
Xfunction output_delete_sql()
X	start report rp_delete_sql to "DeleteDB.sql"
X	output to report rp_delete_sql()
X	finish report rp_delete_sql
Xend function
X
X#
X# Create errors chains description
X#
Xfunction output_errors_list()
X	start report rp_err_list to "LoadDB.err"
X	output to report rp_err_list()
X	finish report rp_err_list
Xend function
X
X#
X# Create list of references for all or certain table
X#
Xfunction output_constr_list()
Xdefine f_tabname	char(18)
X
X	if args.o_flag = 1 then 
X	   start report rp_con_list to "LoadDB.lst"
X	else
X	   start report rp_con_list 
X	end if
X	if tablename = "                  " then
X	   declare curs_tabname cursor for
X	      select tabname
X		 from "informix".systables
X		    where "informix".systables.tabid > 99
X	      order by tabname
X	   foreach curs_tabname into f_tabname
X	      output to report rp_con_list( f_tabname )
X	   end foreach
X	else
X	   output to report rp_con_list( tablename )
X	end if
X	finish report rp_con_list
X
Xend function
X
X#
X# LoadDB.sql file generator
X#
Xreport rp_load_sql()
X
Xdefine  li, p_level     integer,
X	n_nodes         integer,
X	tab_name        char(18),
X	file_name       char(14),
X	tab_owner       char(8)
X
Xoutput
X	left margin     0
X	right margin    0
X	top margin      0
X	bottom margin   0
X	page length     24
X
Xformat
X
Xfirst page header
X	
X	select count(*) into n_nodes from l_vector
X
X	print "{"
X	print "LoadDB.sql generated by DBREF utility"
X	print "-------------------------------------"
X	print "Created by:       ", logon
X	print "Created on:       ", current year to second
X	print "Database:         ", dbname
X	print "Number of tables: ", n_nodes using "<<<<<<<<<<"
X	print "}"
X
Xon every row
X    select unique 1 from l_vector where level is null
X
X    if status = NOTFOUND then
X
X	declare get_sql cursor for
X	  select l_i, level
X	    from l_vector
X	  order by level, l_i
X
X	foreach get_sql into li, p_level
X
X	  select tabname, owner 
X	    into tab_name, tab_owner
X	      from "informix".systables
X		 where "informix".systables.tabid = li
X
X	  let file_name = trunc_name( tab_name, li )
X
X	  print column 00, "load from",
X		column 12, file_name clipped,
X		column 28, "insert into",
X		column 41, "\"", tab_owner clipped, "\".", tab_name clipped, ";"
X
X	end foreach
X	free get_sql
X
X    else
X	  print "{"
X	  print "DBREF detected Referential Integrity violations,",
X		" see LoadDB.err for details"
X	  print "}"
X    end if
X
Xend report
X
X#
X# UnloadDB.sql file generator
X#
Xreport rp_unload_sql()
X
Xdefine  li, p_level     integer,
X	n_nodes         integer,
X	tab_name        char(18),
X	file_name       char(14),
X	tab_owner       char(8)
X
Xoutput
X	left margin     0
X	right margin    0
X	top margin      0
X	bottom margin   0
X	page length     24
X
Xformat
X
Xfirst page header
X	
X	select count(*) into n_nodes from l_vector
X
X	print "{"
X	print "UnloadDB.sql generated by DBREF utility"
X	print "---------------------------------------"
X	print "Created by:       ", logon
X	print "Created on:       ", current year to second
X	print "Database:         ", dbname
X	print "Number of tables: ", n_nodes using "<<<<<<<<<<"
X	print "}"
X
Xon every row
X    select unique 1 from l_vector where level is null
X
X    if status = NOTFOUND then
X
X	declare get_sql_unl cursor for
X	  select l_i, level
X	    from l_vector
X	  order by level desc, l_i desc
X
X	foreach get_sql_unl into li, p_level
X
X	  select tabname, owner 
X	    into tab_name, tab_owner
X	      from "informix".systables
X		 where "informix".systables.tabid = li
X
X	  let file_name = trunc_name( tab_name, li )
X
X	  print column 00, "unload to",
X		column 12, file_name clipped,
X		column 28, "select * from",
X		column 43, "\"", tab_owner clipped, "\".", tab_name clipped, ";"
X
X	end foreach
X	free get_sql_unl
X
X    else
X	  print "{"
X	  print "DBREF detected Referential Integrity violations,",
X		" see LoadDB.err for details"
X	  print "}"
X    end if
X
Xend report
X
X#
X# DeleteDB.sql file generator
X#
Xreport rp_delete_sql()
X
Xdefine  li, p_level     integer,
X	n_nodes         integer,
X	tab_name        char(18),
X	file_name       char(14),
X	tab_owner       char(8)
X
Xoutput
X	left margin     0
X	right margin    0
X	top margin      0
X	bottom margin   0
X	page length     24
X
Xformat
X
Xfirst page header
X	
X	select count(*) into n_nodes from l_vector
X
X	print "{"
X	print "DeleteDB.sql generated by DBREF utility"
X	print "---------------------------------------"
X	print "Created by:       ", logon
X	print "Created on:       ", current year to second
X	print "Database:         ", dbname
X	print "Number of tables: ", n_nodes using "<<<<<<<<<<"
X	print "}"
X
Xon every row
X    select unique 1 from l_vector where level is null
X
X    if status = NOTFOUND then
X
X	declare get_sql_del cursor for
X	  select l_i, level
X	    from l_vector
X	  order by level desc, l_i desc
X
X	foreach get_sql_del into li, p_level
X
X	  select tabname, owner 
X	    into tab_name, tab_owner
X	      from "informix".systables
X		 where "informix".systables.tabid = li
X
X	  let file_name = trunc_name( tab_name, li )
X
X	  print column 00, "delete from",
X		column 14, "\"", tab_owner clipped, "\".", tab_name clipped,
X		column 45, "where 1=1;"
X
X	end foreach
X	free get_sql_del
X
X    else
X	  print "{"
X	  print "DBREF detected Referential Integrity violations,",
X		" see LoadDB.err for details"
X	  print "}"
X    end if
X
Xend report
X
X#
X# LoadDB.err file generator
X#
Xreport rp_err_list()
X
Xdefine  con_owner	char(8),
X	con_name	char(18),
X	f_tabid, p_tabid	integer,
X	f_constr, p_constr	integer,
X	f_tabname, p_tabname	char(18),
X	f_colname, p_colname	array[16] of char(18),
X	n_ref, i		smallint
X
Xoutput
X	left margin     0
X	right margin    0
X	top margin      0
X	bottom margin   0
X	page length     24
X
Xformat
X
Xfirst page header
X	
X	print "{"
X	print "LoadDB.err generated by DBREF utility"
X	print "-------------------------------------"
X	print "Created by:       ", logon
X	print "Created on:       ", current year to second
X	print "Database:         ", dbname
X	print "}"
X	print "{"
X	print "Some of the Referential Integrity Rules",
X	      " listed below cause RI violation:"
X	print
X	print column 00, "Foreign table.column",
X	      column 30, "Primary table.column",
X	      column 60, "Constraint Name"
X	print
X"-----------------------------------------------------------------------------"
X
Xon every row
X    select unique 1 from l_vector where level is null
X
X    if status != NOTFOUND then
X
X	declare get_err_constr cursor for
X	  select * from a_matrix
X	     where a_i in ( select l_i from l_vector where level is null )
X	       and a_j in ( select l_i from l_vector where level is null )
X
X
X	foreach get_err_constr into f_tabid, p_tabid, f_constr, p_constr
X
X	  call get_colname( p_tabid, p_constr ) returning 
X	   p_colname[1],  p_colname[2],  p_colname[3],  p_colname[4], 
X	   p_colname[5],  p_colname[6],  p_colname[7],  p_colname[8],
X	   p_colname[9],  p_colname[10], p_colname[11], p_colname[12],
X	   p_colname[13], p_colname[14], p_colname[15], p_colname[16], n_ref
X
X	  call get_colname( f_tabid, f_constr ) returning 
X	   f_colname[1],  f_colname[2],  f_colname[3],  f_colname[4], 
X	   f_colname[5],  f_colname[6],  f_colname[7],  f_colname[8],
X	   f_colname[9],  f_colname[10], f_colname[11], f_colname[12],
X	   f_colname[13], f_colname[14], f_colname[15], f_colname[16], n_ref
X
X	  select constrname into con_name
X	 	from "informix".sysconstraints
X		   where "informix".sysconstraints.constrid = f_constr
X	  select tabname into p_tabname
X		from "informix".systables
X		   where "informix".systables.tabid = p_tabid
X	  select tabname into f_tabname
X		from "informix".systables
X		   where "informix".systables.tabid = f_tabid
X
X	  for i = 1 to n_ref
X	      print column 00, f_tabname clipped, ".", f_colname[i] clipped,
X		    column 30, p_tabname clipped, ".", p_colname[i] clipped,
X	  	    column 60, con_name clipped
X	  end for
X	
X	end foreach
X	free get_err_constr
X
X    else
X	  print "DBREF did not detect any Referential Integrity violations"
X    end if
X
Xon last row
X	print "}"
X
Xend report
X
X#
X# List of references generator
X#
Xreport rp_con_list( f_tabname )
X
Xdefine  con_owner	char(8),
X	con_name	char(18),
X	f_tabid, p_tabid	integer,
X	f_constr, p_constr	integer,
X	f_tabname, p_tabname	char(18),
X	f_colname, p_colname	array[16] of char(18),
X	n_ref, i		smallint
X
Xoutput
X	left margin     0
X	right margin    0
X	top margin      0
X	bottom margin   0
X	page length     24
X
Xformat
X
Xfirst page header
X	
X	print "{"
X	print "LoadDB.lst generated by DBREF utility"
X	print "-------------------------------------"
X	print "Created by:       ", logon
X	print "Created on:       ", current year to second
X	print "Database:         ", dbname
X	if tablename = "                  " then
X	print "Foreign table:    All tables"
X	else
X	print "Foreign table:    ", tablename 
X	end if
X	print "}"
X	print "{"
X	print "DBREF detected the following referential constraints:"
X	print
X	print column 00, "Foreign table.column",
X	      column 30, "Primary table.column",
X	      column 60, "Constraint Name"
X	print
X"-----------------------------------------------------------------------------"
X
Xon every row
X	declare curs_constr cursor for
X	  select * from a_matrix
X	    where a_i = ( select tabid from "informix".systables 
X			     where "informix".systables.tabname = f_tabname )
X
X	foreach curs_constr into f_tabid, p_tabid, f_constr, p_constr
X
X	  call get_colname( p_tabid, p_constr ) returning 
X	   p_colname[1],  p_colname[2],  p_colname[3],  p_colname[4], 
X	   p_colname[5],  p_colname[6],  p_colname[7],  p_colname[8],
X	   p_colname[9],  p_colname[10], p_colname[11], p_colname[12],
X	   p_colname[13], p_colname[14], p_colname[15], p_colname[16], n_ref
X
X	  call get_colname( f_tabid, f_constr ) returning 
X	   f_colname[1],  f_colname[2],  f_colname[3],  f_colname[4], 
X	   f_colname[5],  f_colname[6],  f_colname[7],  f_colname[8],
X	   f_colname[9],  f_colname[10], f_colname[11], f_colname[12],
X	   f_colname[13], f_colname[14], f_colname[15], f_colname[16], n_ref
X
X	  select constrname into con_name
X	 	from "informix".sysconstraints
X		   where "informix".sysconstraints.constrid = f_constr
X	  select tabname into p_tabname
X		from "informix".systables
X		   where "informix".systables.tabid = p_tabid
X
X	  for i = 1 to n_ref
X	     print column 00, f_tabname clipped, ".", f_colname[i] clipped,
X		   column 30, p_tabname clipped, ".", p_colname[i] clipped,
X	  	   column 60, con_name clipped
X	  end for
X	
X	end foreach
X	free curs_constr
X
Xon last row
X	print "}"
X
Xend report
X
X#
X# Function trunc_name() returns dbexport/dbimport 
X# compartible flat file name
X#
Xfunction trunc_name( tbname, tbid )
Xdefine  tbname  char(18),
X	tbid    integer,
X	fname   char(14),
X	i       integer
X
X	let fname = "          .unl"
X	let fname[8,10] = tbid using "&&&"
X	let fname[1,7] = tbname[1,7] clipped
X	for i =1 to 7
X	  if fname[i,i] = " " then
X	     let fname[i,i] = "_"
X	  end if
X	end for
X
X	return fname
Xend function
X
X#
X# Function get_colname() returns set of column names assosiated with pair
X# ( tab_id, constr_id )
X#
Xfunction get_colname( tab_id, constr_id )
Xdefine  tab_id          integer,
X	constr_id       integer,
X	cn		array[16] of char(18),
X	ci		array[16] of integer,
X	i		smallint
X
X	select part1, part2, part3, part4, part5, part6, part7, part8,
X	       part9, part10,part11,part12,part13,part14,part15,part16
X	    into ci[1], ci[2], ci[3], ci[4], ci[5], ci[6], ci[7], ci[8],
X		 ci[9], ci[10],ci[11],ci[12],ci[13],ci[14],ci[15],ci[16]
X	     from "informix".sysindexes
X		where "informix".sysindexes.idxname = (
X	    select idxname
X		from "informix".sysconstraints
X		  where "informix".sysconstraints.constrid = constr_id )
X
X	for i = 1 to 16
X	   if ci[i] = 0 then exit for end if
X	   select colname into cn[i]
X	      from "informix".syscolumns
X	         where "informix".syscolumns.tabid = tab_id
X		   and "informix".syscolumns.colno = ci[i]
X	end for
X	let i = i - 1
X
X	return cn[1], cn[2], cn[3], cn[4], cn[5], cn[6], cn[7], cn[8],
X	       cn[9], cn[10],cn[11],cn[12],cn[13],cn[14],cn[15],cn[16], i
X
Xend function
SHAR-EOF
sed -e 's/^X//' >getopt.4gl <<'SHAR-EOF'
X{
X	@(#)getopt.4gl  1.4 90/12/17
X	@(#)JLSS Informix Tools: General Library
X	@(#)Simulate GETOPT(3)
X	@(#)Author: JL
X}
X
X{
XSimulate GETOPT(3) in I4GL
X
XOperation:
XWhen called with an argument such as "abcf:t:x", the function scans
Xthe command line argument list for the flags -a, -b -c or -x, or options -f and
X-t.  The flags are boolean values; the options have a text string associated
Xwith them.  This can be attached to the option argument (e.g. -ffilename) or in
Xthe next argument (-f filename).
X
XDifferences from GETOPT(3):
X1.  Function returns optval (CHAR(1)) containing the flag letter and optarg
X    (CHAR(80)) containing option string.  GETOPT(3) just returns the flag letter
X    and the global variable optarg points to the option string.
X3.  The optval returned for no more arguments is NULL.
X3.  The optval returned for a non-option (filename) argument is "-".
X}
X
XGLOBALS  "globals.4gl"
X
XFUNCTION getopt(optstr)
X
X	DEFINE
X		optstr  CHAR(20),   { List of options as for GETOPT(3) }
X		optarg  CHAR(80),   { Returned option value }
X		optval  CHAR(1),    { Returned option flag }
X		sccs    CHAR(70),
X		idx     INTEGER,
X		i       INTEGER
X
X	{ Initialisation }
X	IF optind <= 0 THEN
X		LET optind = 1
X		LET argnum = NUM_ARGS()
X		LET chridx = 1
X		LET arglen = 0
X		LET no_opt = FALSE
X		LET sccs   = "@(#)getopt.4gl	1.4 90/12/17"
X	END IF
X
X	LET optarg = NULL
X	LET optval = NULL
X
X	WHILE TRUE
X
X		{ Step to next argument }
X		IF chridx > arglen THEN
X			IF optind > argnum THEN
X				EXIT WHILE
X			END IF
X			LET argstr = ARG_VAL(optind)
X			LET arglen = LENGTH(argstr)
X			LET optind = optind + 1
X			LET chridx = 2
X		END IF
X
X		IF argstr[1] != "-" OR no_opt = TRUE OR arglen = 1 THEN
X			{ Non-option argument }
X			LET optval = "-"
X			LET optarg = argstr
X			LET arglen = 0
X			EXIT WHILE
X		END IF
X
X		IF argstr[2] == "-" THEN
X			{ '--' argument.  All following arguments are file name arguments }
X			LET no_opt = TRUE
X			LET arglen = 0
X			CONTINUE WHILE
X		END IF
X
X		LET optval = argstr[chridx]
X		LET chridx = chridx + 1
X		LET idx = instring(optstr, optval)
X		IF idx = 0 THEN
X			{ Unknown option }
X			LET optval = "?"
X			EXIT WHILE
X		END IF
X
X		IF optstr[idx+1] != ":" THEN
X			{ Flag-only option }
X			EXIT WHILE
X		END IF
X
X		IF arglen <= chridx THEN
X			{ Need to use next argument for value }
X			LET optarg = ARG_VAL(optind)
X			LET optind = optind + 1
X			LET arglen = 0  { Use next argument next time round }
X			EXIT WHILE
X		END IF
X
X		{ Value of option attached to current argument }
X		LET optarg = argstr[chridx,arglen]
X		LET arglen = 0  { Use next argument next time round }
X		EXIT WHILE
X
X	END WHILE
X
X	RETURN optval, optarg
X
XEND FUNCTION {getopt}
X
X{
X	@(#)instring.4gl        1.1 90/07/19
X	@(#)JLSS Informix Tools: General Library
X	@(#)Find index of character in string
X	@(#)Author: JL
X}
X
XFUNCTION instring(str, c)
X
X	DEFINE
X		str             CHAR(80),
X		c               CHAR(1),
X		sccs    CHAR(1),
X		l               INTEGER,
X		i               INTEGER
X
X	LET l = length(str)
X
X	FOR i = 1 TO l
X		IF str[i] = c THEN
X			RETURN i
X		END IF
X	END FOR
X
X	RETURN 0
X
X	LET sccs = "@(#)instring.4gl	1.1 90/07/19"
X
XEND FUNCTION {instring}
X
X{ included in dbref 9/12/95 }
SHAR-EOF