Conference heron::euro_swas_ai

253.0. "OPS5 (TIN) SQL interface - Any Comments?" by HERON::ROACH (TANSTAAFL !) Mon Nov 19 1990 11:53

Printed by: Pat Roach                                    Document Number: 013899
--------------------------------------------------------------------------------

                  I N T E R O F F I C E   M E M O R A N D U M

                                        Date:     15-Nov-1990 09:45pm CET
                                        From:     LEHTO
                                                  LEHTO@GUESS@HERON@MRGATE@HUGHI
                                        Dept:      
                                        Tel No:    

TO:  KANE@A1NSTC
TO:  ROACH@A1NSTC


Subject: OPS5 (TIN) SQL interface


   Attached is a draft of a proposed TIN SQL interface functionality.
Obviously there are still questions, but we want feedback now while working 
on prototypes to answer some of our own questions.  

   Please review this draft and provide us with your comments. 

   Thanks you,
   Jon



								  DRAFT
								11/15/90
		Proposed OPS/TINfoil SQL interface
		==================================


Introduction
------------

   This interface will allow a TIN programmer access to a SQL compliant database
though a set of function calls.  Using SQL, these routines will allow movement
of data between TIN working memory and the database, in both directions.

   The TIN SQL interface functions will include capability to read data from a
SQL database and form WMEs from this data, and the reverse operations, within
the context of an implicit or explicit SQL transaction.  These functions will
utilize the TINfoil call interface.

  The following built-in routines will be provided:

	FETCH	  - make TIN WMEs from selected SQL database records
	STORE	  - insert SQL database records from TIN WMEs
	DELETE	  - delete selected database records
	START	  - explicitly start an SQL transaction
	COMMIT	  - end transaction, commit changes to database
	ROLLBACK  - end transaction, rollback database changes

   Additional functionality has been suggested, and will be considered as time
and resources permit:

	ATTACH	  - bind or open an SQL database at runtime, multiple db's?
	DETACH	  - close an SQL database at runtime
	INIT_DB	  - define string edit and debug qualifiers, other?
	UPDATE	  - modify the contents of selected database records
	CREATE	  - create SQL tables, fields, etc.
	FIND	  - find selected DB records
	CALL_SQL  - perform execution of arbitrary SQL command



Data Mapping
------------

   A TIN WME-class would typically be mapped one-to-one to a corresponding SQL
table, and the WME-class attributes are mapped to the corresponding fields
within the table.  (Exceptions to this could be achieved easily through user
defined views of the database, or by using more complex SQL RSE expressions, for
example in form 1 or 3 of the fetch function).

	TINFoil					SQL

(WME-CLASS car_part			create table car_part
	^part_name	symbol			(part_name	char (10),
	^list_price	float			 list_price	double,
	^quantity	integer)		 quantity	integer);

[Note: avoid hyphen in table names, etc, as SQL requires underscore character.]


   Not all attributes need be defined as fields in the table, and not all
fields need be defined as attributes of the WME-CLASS. Any non-corresponding
fields/attributes will be left untouched [behavior tbd???].

[??? DBKEY attr requirement tbd???  Some of the functions discussed below would
require use of dbkeys, and furthermore that the WME-class definitions used would
contain a dbkey attribute (which could be part of the base WME-class definition,
so that user TIN code would not have to worry about defining this attribute).
Name of this attribute is tbd, but something like $DBKEY of type address?]

[??? Remember to comment in actual spec on "missing" DB data values, ie field
defined, but data not there]

[??? How are errors handled/reported to user ???]
   - return status
   - make of a wme? interface or user defined?

[??? Would alias-ing be applicable ???]

[??? Should views be allowed; map multiple records into one wme. ???]






Interface functions:
--------------------

FETCH

   Makes TIN WMEs from selected SQL database records.


Format:

   Form 1:  (fetch wme-class [SQL-RSE])

   Form 2:  (fetch (TIN-CE-expr))

   Form 3:  (fetch ^attr-name [SQL-RSE])


Arguments:

   For form 1:

	wme-class: name of WME-class corresponding to SQL table from which data
			is to be fetched.

	SQL-RSE: optional SQL record selection expression used to select only
			specific records from the SQL database.  Default RSE
			corresponds to "SELECT * FROM WME-class".

   For form 2:

	TIN-CE-expr: a TIN condition element expression which specifies the
			WME-class to be fetched and [optional] attribute-value
			tests to be satisfied by DB records to be fetched; note
			that the inner parentheses are required part of this
			syntax.

   For form 3:

	^attr-name: fetch operation(s) similar to that in form #1 above will be
			performed on all SQL tables containing this attribute
			name.

	SQL-RSE: optional SQL record selection expression used to select only
			specific records from the SQL database.   Default RSE
                        corresponds to "SELECT * FROM all-tables-with-attr".


Description:

   Select the appropriate records from the SQL (ie Rdb) database, as specified
by the arguments to the call, then fetch these DB records and form WMEs from
them.

   Forms 1 and 2 of the fetch function will typically be used to fetch data from
the database into one or more WMEs of a single WME-class.  Form 1 requires the
use of SQL syntax, but does offer the user more flexibility in his selection
criteria.  Form 2 provides the user with a simpler, TIN-like selection syntax.

   Form 3 of the fetch gives the user the capability of fetching data from
multiple SQL tables into WMEs of multiple WME-classes.  Optionally, form 2 may
also be used to provide this functionality by omitting WME-class from the TIN-CE
expression (although this is not correct TIN CE syntax).
   Also, form 3 could be used as a fetch via dbkey by specifying the
dbkey attribute.

Example:

   Form 1:  (fetch CAR-PART |WHERE part-name = '01-0000AA'|)

   Form 2:  (fetch (CAR-PART ^part-name |'01-0000AA'|))

   Form 3:  (fetch ^part-name |WHERE part-name = '01-0000AA'|)
            (fetch ^$DBKEY <dbkey>) ;must have previously fetched the record...





 STORE

   Inserts SQL database records from TIN WMEs.
   Valid in context of a READ WRITE transaction only. (explicit)

Format:

   Form 1:  (store <WME-variable>)

   Form 2:  (store (WME-expr))


Arguments:

   <WME-variable>: WME element identifier, WME-ID bound on LHS of rule.

   (WME-expr): explicit statement of WME to be stored, as in a make command.


Description:

   Inserts contents of specified WME into SQL database table.  Form 1 uses the
WME-ID bound on LHS of rule to identify the WME to be stored, while form 2 just
specifies the actual WME to be stored, as in the form of a TIN MAKE command.

[???Does either store return and save (in WME) the dbkey value???]
[???Does form 2 make and keep in WM the stored WME, or use it temporarily
and then remove it after store completed???]


Example:

   (store <MY_PART>)

   (store (CAR_PART ^part_number |"01-1234AB"| ^quantity  456))




 DELETE

   Deletes selected data records from database.
   Valid in context of a READ WRITE transaction only. (explicit)
   [??? Should the WME also be deleted from working memory ???]

Format:

   Form 1:  (delete wme-class [SQL-RSE])

   Form 2:  (delete (TIN-CE-expr))

   Form 3:  (delete ^attr-name [SQL-RSE])

   Form 4:  (delete <WME-variable>)


Arguments:

   For form 1:

	wme-class: name of WME-class corresponding to SQL table from which data
			is to be deleted.

	SQL-RSE: optional SQL record selection expression used to select only
			specific records from the SQL database.  Default RSE
			corresponds to "SELECT * FROM WME-class".

   For form 2:

	TIN-CE-expr: a TIN condition element expression which specifies the
			WME-class to be deleted and [optional] attribute-value
			tests to be satisfied by DB records to be deleted; note
			that the inner parentheses are required part of this
			syntax.

   For form 3:

	^attr-name: delete operation(s) similar to that in form #1 above will be
			performed on all SQL tables containing this attribute
			name.

	SQL-RSE: optional SQL record selection expression used to select only
			specific records from the SQL database.   Default RSE
                        corresponds to "SELECT * FROM all-tables-with-attr".

   For form 4:

	<WME-variable>: WME element identifier, WME-ID bound on LHS of rule.


Description:

   Select the appropriate records from the SQL (ie Rdb) database, as specified
by the arguments to the call, then delete these DB records.

   Forms 1 and 2 of the delete function will typically be used to delete data
records from a single database table.  Form 1 requires the use of SQL syntax,
but does offer the user more flexibility in his selection criteria.  Form 2
provides the user with a simpler, TIN-like selection syntax.

   Form 3 of the delete gives the user the capability of deleting data from
multiple SQL tables. 

   Form 4 allows a single DB record to be deleted; the record to be deleted is
selected via the dbkey attribute value of the matched WME (e.g. the WME was
formed by a fetch from the database).


Example:

   Form 1:  (delete CAR-PART |WHERE part-name = '01-0000AA'|)

   Form 2:  (delete (CAR-PART ^part-name |'01-0000AA'|))

   Form 3:  (delete ^part-name |WHERE part-name = '01-0000AA'|)

   Form 4:  (delete <MY_PART>)





 START_TRANSACTION

   Starts the specified type of SQL transaction explicitly.

Format:

   (start_transaction SQL-txn-options)


Arguments:

   SQL-txn-options: a statement of required SQL transaction options.

Description:

   This function executes a SQL "SET TRANSACTION" command to start a transaction
with the specified options.  These options can include READ ONLY versus READ
WRITE transaction, or to obtain locks on specified tables.


Example:

   (start_transaction |READ WRITE RESERVING CAR-PARTS FOR PROTECTED WRITE|)




 COMMIT and ROLLBACK

   Used to commit or rollback the SQL transaction.

Format:

   (commit)

   (rollback)

Arguments:

   None.


Description:

   Both commands complete the current SQL transaction.  Commit makes permanent
any changes made to the database during this transaction, while rollback undoes
all these changes, if any.

Example:

   (commit)

   (rollback)

--------------------------------------------------------------------------




Open issues...


  - Multiple databases...

  - Use of logicals or flags for debugging during development...

  - Default behavior for setting unmapped(non-corresponding) fields/attributes..

  - How does the mapping work? Dependent on sequencing through field names, 
	WME-class definitions or both; function dependent...

  - How does alias-ing, views, multiple relations and DB's fit into the mapping
	to a WME?...

  - Should the interface provide explicit/implicit use of DBKEYs...

  - Should Fetch and Store return the DBKEYs...

  - Should TIN provide a base WME-class with DBKEY defined...

  - Should errors be returned only as a status value? Returned as a compound
	value? Returned as a TIN or user defined WME?...

  - Should functions have side effects...
	DELETE also deletes the WME in working memory?

Printed by: Pat Roach                                    Document Number: 013904
--------------------------------------------------------------------------------

                  I N T E R O F F I C E   M E M O R A N D U M

                                        Date:     16-Nov-1990 02:40pm CET
                                        From:     ORCIUCH
                                                  ORCIUCH@YIPPEE@HERON@MRGATE@HUGHI
                                        Dept:      
                                        Tel No:    

TO:  ROACH@A1NSTC


Subject: TINFOIL - Any comments?

From:	GUESS::WALTHER      "Beatrice Walther DTN 291-8235" 12-NOV-1990 15:29:14.22
To:	YIPPEE::ORCIUCH
CC:	WALTHER
Subj:	RE: TINFOIL

Ed,
We would very much welcome you field testing TINfoil. Right now, we are still
in the process of finalizing the contents and schedule of TINfoil, and
are working on obtaining the approval to productize TINfoil as DEC OPS5 V4.0,
with possible Mips/ULTRIX support.

It is our goal that the product and internal version (to be delivered to CSDG)
be the same, however, this is not yet confirmed, pending the productization
approval. Would you be field testing TINfoil with external customers, or with
the intent of making it available as a product, or would you field test 
strictly for internal applications?

				Thanks,		Beatrice

    
    Both prospects (SQL and availability on ULTRIX/RISC) would make
    DEC OPS5 a very valuable product. Availability across all platforms 
    is particularly important: we are currently limited to DDS (NEXPERT OBJECT)
    or CMP (Interface Prolog) if VMS/ULTRIX availability comes into play.
    
    Dirk