(*************************************************************************** 
 * System      : DBPL database system 
 * Level       : SQLUtil
 *               Interface to relational DBMS (INGRES, ORACLE)
 *               via embedded SQL
 * Modulename  : SQLUtil: The module is written in SQL embedded in C
 * Version     : 0.0 
 * Author      : Kazimierz Subieta
 * Last Update : 21-JAN-1992 
 * 
 * Arbeitsbereich DBIS
 * Universitaet Hamburg
 * Schlueterstrasse 70
 * D-2000 Hamburg 13, FRG
 * This software is subject to a license agreement
 *************************************************************************** 
 * Updates : 
 ***************************************************************************)
DEFINITION FOR C MODULE SQLUtil;

FROM SYSTEM IMPORT
  ADDRESS;
IMPORT Base;

CONST
  (* Maximal number of attributes. It is necessary, in particular, for 
  ** determining the size of tables for typing information of DBPL and
  ** for determing the size of SQLDA area. INGRES allows max 128 attributes,
  ** but for most applications this seems to be too much. *)
  maxattrnbr = 36;

PROCEDURE SQLmyTest;

(*------------------------------------------------------------------*)
PROCEDURE SQLconnect( mydatabase: ARRAY OF CHAR ): INTEGER;

PROCEDURE SQLcommit;

PROCEDURE SQLundo;

PROCEDURE SQLdisconnect;

(*------------------------------------------------------------------
** Test if the table of the given name exists in open INGRES database *)

PROCEDURE SQLExistsTable( relname : ARRAY OF CHAR ) : INTEGER;

(*------------------------------------------------------------------
** SQLprepIns, SQLinsNext, and SQLcloseScan allow to insert a collection of
** DBPL tuples into INGRES table. SQLprepIns (prepare) should issued as 
** first; then as many SQLinsNext as dbpltuples to be inserted; the
** process should be closed by SQLcloseScan. It is a possible to nest
** inserts from different tables, and to combine inserts with delete and
** fetch, but the "stack principle" should be obeyed. The meaning of
** parameters is the following:
**    relname    : pointer string with name of INGRES relation
**    attributes : pointer to table of pointers to names of attributes
**    offsets    : pointer to table of integers being offsets in
**                 dbpltuple for subsequent fields.
**    attrnbr    : number of attributes
**    dbpltuple  : tuple in the dbpl format to be inserted into INGRES
** attributes, attrnbr, and offsets can be received by procedure
** AttrNamesOffsets. *)

PROCEDURE SQLprepIns(       relname : ADDRESS;
                     VAR attributes : ARRAY OF ADDRESS;
                        VAR offsets : ARRAY OF INTEGER;
		            attrnbr : INTEGER );

PROCEDURE SQLinsNext(      dbpltuple : ADDRESS ): INTEGER;
(* The procedure returns 1 if successfully inserted and 0 if a tuple
** with the same primary key is present in the relation, or iff error. *)


PROCEDURE SQLcloseScan;

(*------------------------------------------------------------------
** SQLprepDel, SQLdelNext, and SQLcloseScan allow to delete a collection of
** DBPL tuples from INGRES table. SQLprepDel (prepare) should issued as 
** first; then as many SQLdelNext as dbpltuples to be deleted; the
** process should be closed by SQLcloseScan. It is possible to nest delete
** from different tables, and to combine delete with fetch and with
** insert, but the "stack principle" should be obeyed. The meaning of
** parameters is the following:
**    relname    : pointer string with name of INGRES relation
**    attributes : pointer to table of pointers to names of attributes
**    offsets    : pointer to table of integers being offsets in
**                 dbpltuple for subsequent fields.
**    attrnbr    : number of attributes
**    dbpltuple  : tuple in the dbpl format to be deleted into INGRES
** attributes, attrnbr, and offsets can be received by procedure
** AttrNamesOffsets.
**
** SQLprepDelKeys prepares the delete loop according to primary keys of
** the relation, similarly as SQLprepDel does.
** The procedure asssumes comparison of an DBPL tuple and an INGRES tuple 
** according to primary keys, and delete the INGRES tuple matching the
** keys. keyoffsets and keynbr can be received from procedure 
** PrimaryKeyOffsets. Application rules are the same as for SQLprepDel,
** SQLdelNext, and SQLcloseScan. Additional paremeters are the following:
**    keyoffsets : pointer to table of integers being offsets of keys in
**                 dbpltuple.
**        keynbr : the number of keys. *)

PROCEDURE SQLprepDel(       relname : ADDRESS;
                     VAR attributes : ARRAY OF ADDRESS;
                        VAR offsets : ARRAY OF INTEGER;
		            attrnbr : INTEGER );

PROCEDURE SQLprepDelKeys(   relname : ADDRESS;
                     VAR attributes : ARRAY OF ADDRESS;
                        VAR offsets : ARRAY OF INTEGER;
		            attrnbr : INTEGER;
	             VAR keyoffsets : ARRAY OF INTEGER;
                             keynbr : INTEGER );

PROCEDURE SQLdelNext(     dbpltuple : ADDRESS ): INTEGER;
(* The procedure returns 1 if successfully deleted and 0 if the tuple
** was not present in the relation or iff error. *)

(*------------------------------------------------------------------
** SQLprepFet, SQLfetNext, SQLcloseScan allow subsequent fetching
** tuples from INGRES table into dbpltuple. SQLprepFet (prepare)
** should be issued as a first, then arbitrary number of SQLfetNext,
** and the process should be finished by SQLcloseScan. 
** It is a possible to nest fetching from different 
** tables, and to combine fetching with insert and delete, but the "stack 
** principle" should be obeyed. The meaning of parameters is the
** following:
**    relname    : pointer string with name of INGRES relation
**    attributes : pointer to table of pointers to names of attributes
**    offsets    : pointer to table of integers being offsets in
**                 dbpltuple for subsequent fields.
**    attrnbr    : number of attributes
**    dbpltuple  : tuple in the dbpl format to be inserted into INGRES
** attributes, attrnbr, and offsets can be received by procedure
** AttrNamesOffsets. SQLfetNext returns 1 if the tuple is successfully
** fetched, and 0 if there is no next tuple. The process of fetching
** need not to be continued till the end of table; in any moment
** it can be finished by SQLcloseScan. 
**
** Procedures SQLprepSel, SQLfetNext, SQLcloseScan allow subsequent 
** fetching tuples determined by arbitrary SQL 'select' statement
** from INGRES database into dbpltuple. Application rules are
** the same as for SQLprepFet, SQLfetNext, SQLcloseScan.
**    sqlquery   : pointer to string with SQL select query.
*)

PROCEDURE SQLprepFet(       relname : ADDRESS;
                     VAR attributes : ARRAY OF ADDRESS;
                        VAR offsets : ARRAY OF INTEGER;
			    attrnbr : INTEGER );

PROCEDURE SQLprepSel(      sqlquery : ADDRESS;
                        VAR offsets : ARRAY OF INTEGER );

PROCEDURE SQLfetNext(     dbpltuple : ADDRESS ): INTEGER;

(*------------------------------------------------------------------
** SQLdelete deletes tuples from a INGRES table according to
** arbitrary SQL query. For external unification, the query should
** be given in the form of the 'select' statement; the procedure
** automatically changes 'select *' into 'delete'.
**    sqlquery   : pointer to string with SQL select query. *)

PROCEDURE SQLdelete(       sqlquery : ADDRESS );

(*------------------------------------------------------------------
** Insert through SQL 'select' query; relname is the name of relation *)

PROCEDURE SQLinsert(       relname : ADDRESS;
                          sqlquery : ADDRESS );

(*------------------------------------------------------------------
** Clear (make empty) the given INGRES relation *)

PROCEDURE SQLclear( relname : ADDRESS );

(*------------------------------------------------------------------
** SQLcreateTable creates INGRES table named relname, according to the
** SQL 'select' statement.
**    relname    : pointer string with name of INGRES relation
**    sqlquery   : pointer to string with SQL select query. *)

PROCEDURE SQLcreateTable(  relname : ADDRESS;
                          sqlquery : ADDRESS );

(*--------------------------------------------------------------------
** SQLCreateDbplTable creates INGRES table according to type information
** from DBPL. It is thus fully compatible with the corresponding DBPL
** relation.
**    relname    : pointer string with name of INGRES/DBPL relation
**    attributes : pointer to table of pointers to names of attributes
**    stypes     : DBPL types: 0 - integer, 1 - string, 2 - real
**    sizes      : sizes of attribute values
**    attrnbr    : number of attributes.
** The procedure returns 1 if success and 0 otherwise *)

PROCEDURE SQLCreateDbplTable(         relname : ADDRESS;
			      VAR attributess : ARRAY OF ADDRESS;
			           VAR stypes : ARRAY OF INTEGER;
			 	    VAR sizes : ARRAY OF Base.Bytesize;
				      attrnbr : INTEGER ): INTEGER; 

(*--------------------------------------------------------------------
** SQLAttrTypes for the given relation 'relname' returns all information
** concerning the type: names of attributes, types of attributes, 
** sizes of attributes, and the attribute number. The function has
** value 0 iff error or no such relation, and 1 otherwise.
** attrnbr should be filled in before the call by maximal anticipated
** number of columns; if the return attrnbr is 0, it means that
** the anticipated number was too low *)

PROCEDURE SQLAttrTypes(       relname : ARRAY OF CHAR;
                       VAR attributes : ARRAY OF ADDRESS;
                            VAR types : ARRAY OF INTEGER;
                            VAR sizes : ARRAY OF INTEGER;
                          VAR attrnbr : INTEGER ): INTEGER;

(*--------------------------------------------------------------------
** SQLTypeOf for the type being number returns its string name *)

PROCEDURE SQLTypeOf( type: INTEGER ): ADDRESS;

(*----------------------------------------------------------------------
** SQLdropTable removes the table from INGRES database. Returns 1 iff
** success and 0 otherwise *)

PROCEDURE SQLdropTable( relname : ADDRESS ): INTEGER;

(*-----------------------------------------------------------------------
** SQLevalExists evaluates (incorrect) SQL query of the form
**                   [NOT] EXISTS <select statement>
** Such queries may appear during generation of SQL queries from DBPL 
** predicates. The method of evaluation assumes a dummy relation Rdummy
** inside INGRES, containing exactly one tuple. The above
** query is converted to the (correct) form
**        SELECT * FROM Rdummy WHERE [NOT] EXISTS <select statement>
** We hope the INGRES SQL machine can process this query more efficiently
** than we can do that inside DBPL. Alternatively, we can send to INGRES
** only <select statement> and process EXISTS inside DBPL, but in this
** case INGRES must retrieve all tuples, while we require only one. The
** procedure returns 1 if this statement returns a tuple, and 0 if no
** tuple is returned (or if error). *)

PROCEDURE SQLevalExists( sqlquery: ADDRESS ): INTEGER;


(*-----------------------------------------------------------------------
** SQLcard returns the cardinality of relation created by the sqlquery,
** which is given as a standard 'select' statement (possibly, with any
** projection list).
** SQLcardRel returns the cardinality of the given relation.
** The return is the number of tuples or -1 iff error. *)

PROCEDURE SQLcard(   sqlquery : ADDRESS ): INTEGER;

PROCEDURE SQLcardRel( relname : ADDRESS ): INTEGER;

(*----------------------------------------------------------------------
** SQLcontains returns 1 iff rel1name relation projected on primary keys
** contains rel2name relation projected on primary keys.
** It returns 0 if rel1[keys] does not contain rel2[keys], or -1 iff error *)

PROCEDURE SQLcontains(     rel1name : ADDRESS;
                           rel2name : ADDRESS;
                        VAR offsets : ARRAY OF INTEGER;
	             VAR keyoffsets : ARRAY OF INTEGER;
                             keynbr : INTEGER )          : INTEGER;

(*-----------------------------------------------------------------------
** Check if the given dbpltuple belongs to the given INGRES relation.
** The method assumes generation of select statement based on primary keys.
**    dbpltuple  : tuple in the dbpl format to be checked
**    relname    : pointer string with name of INGRES relation
**    attributes : pointer to table of pointers to names of attributes
**    offsets    : pointer to table of integers being offsets in
**                 dbpltuple for subsequent fields.
**    attrnbr    : number of attributes
**    keyoffsets : pointer to table of integers being offsets of keys in
**                 dbpltuple.
**        keynbr : the number of keys. 
** attributes, offsets and attrnbr can be received by AttrNamesOffsets.
** keyoffsets and keynbr can be received by PrimaryKeyOffsets.
** Out: 1 if belongs, 0 if does not belong, -1 if error. *)


PROCEDURE SQLbelongs(     dbpltuple : ADDRESS;
                            relname : ADDRESS;
                     VAR attributes : ARRAY OF ADDRESS;
                        VAR offsets : ARRAY OF INTEGER;
	             VAR keyoffsets : ARRAY OF INTEGER;
                             keynbr : INTEGER ): INTEGER;

(* The same as above, but the argument is sqlquery *)

PROCEDURE SQLtupleInQuery(dbpltuple : ADDRESS;
                           sqlquery : ADDRESS;
                     VAR attributes : ARRAY OF ADDRESS;
                        VAR offsets : ARRAY OF INTEGER;
	             VAR keyoffsets : ARRAY OF INTEGER;
                             keynbr : INTEGER ): INTEGER;

(*------------------------------------------------------------------------
** The input for the procedure is the dbpltuple filled in with primary keys.
** The procedure checks if the tuple with these keys exists in the
** INGRES relation, fills in the tuple with attributes, and returns 1.
** If the tuple does not exist, the procedure returns 0. *)

PROCEDURE SQLgetTuple(    dbpltuple : ADDRESS;
                            relname : ADDRESS;
                     VAR attributes : ARRAY OF ADDRESS;
                        VAR offsets : ARRAY OF INTEGER;
	             VAR keyoffsets : ARRAY OF INTEGER;
                             keynbr : INTEGER ): INTEGER;

(*------------------------------------------------------------------------
** The input for the procedure is the dbpltuple filled in with primary keys.
** The procedure checks if the tuple with these keys exists in the
** INGRES relation. If so, it returns 1, otherwise 0. *)

PROCEDURE SQLkeyBelong(   dbpltuple : ADDRESS;
                            relname : ADDRESS;
                     VAR attributes : ARRAY OF ADDRESS;
                        VAR offsets : ARRAY OF INTEGER;
	             VAR keyoffsets : ARRAY OF INTEGER;
                             keynbr : INTEGER ): INTEGER;
(*------------------------------------------------------------------------
** Procedures SQLprepUpdRel, SQLprepUpdSel, SQLfetNext, SQLupdNext, and
** SQLcloseScan form a consistent set for processing tuples retrieved
** from INGRES. The usage: first, SQLprepUpdRel (or SQLprepUpdSel)
** opens the scan through the given relation (or through the given 'select'
** statement). The INGRES cursor is located below the set to be retrieved.
** Then SQLfetNext moves the cursor to the first tuple to be retrieved, and
** fetches the tuple to DBPL. The tuple can be processed by any DBPL
** capabilities. We can overwrite by it the original INGRES tuple through
** procedure SQLupdNext; the procedure may be issued 0, 1 or more times
** in one loop of SQLfetNext, and it does not change the cursor position.
** Next SQLfetNext moves the cursor to the next tuple and retrieves it.
** Again, the tuple can be processed and then flushed to INGRES through
** SQLupdNext. The whole process must be finished by SQLcloseScan.
** Note that changing primary keys in the tuple may lead to random
** effects, thus is not recommended. There is no restriction about
** nesting of the above loops. Not also that in general, this loop is
** less efficient than SQLprepFet, SQLfetNext, SQLcloseScan (SQLprepSel,
** SQLfetNext, SQLcloseScan), thus if there is no updating it is not
** recomended (although it should work properly also for retrieval).
** The typical usage:
**
**              SQLprepUpdSel( ADR(sqlquery), offsets );
**              WHILE SQLfetNext( ADR(dbpltuple) ) = 1 DO
**                < processing of dbpltuple >
**                ErrorHandler.Assert( SQLupdNext( ADR(dbpltuple) ) = 1 );
**              END;
**              SQLcloseScan;
*)

PROCEDURE SQLprepUpdRel(    relname : ADDRESS;
                        VAR offsets : ARRAY OF INTEGER );

PROCEDURE SQLprepUpdSel(   sqlquery : ADDRESS;
                        VAR offsets : ARRAY OF INTEGER );

(* Returns 1 if success or 0 if error *)
PROCEDURE SQLupdNext(      dbpltuple: ADDRESS ): INTEGER;

(*==================================================================*)
END SQLUtil.