[dais-wg] More on Updates based on DAIS Factory Results

[Norman Paton]

This message expands and makes more precise the previously posted 
proposal on how
database updates can be carried out based on result collections.  This 
seeks to
clarify the proposal, and takes into account feedback from conference 
calls  and some
offline feedback.  Comments/corrections continue to be invited.

The DAIS specifications support Factory operations, which return 
identifiers for
the results of queries.  However, there is a lack of symmetry in the 
handling of
such results in the specs, in that their identifiers cannot currently be 
provided
as inputs to update operations.  This note comments on options for 
extending this
capability.

The proposal here does not involve reflecting back changes made to a 
query result -
that is, issues associated with pushing updates back through views do 
not arise.  In
terms of transactions, if no transactions specification is being used in 
conjunction
with DAIS, then each of the operations described below would run as a 
single transaction
over the underlying database.

1. Relational

In the relational model, data access and manipulation requests are directed
at data services using the SQLAccess operation.  Additional operations could
be defined on the relational data access service or on the SQLRowSet that
in some way push the contents of the row set to the relational data access
service.

1.1 Insert

The following operation could be defined:

SQLAccess::Insert(TableName, FieldMapping, RowsetIdentifier) -> 
SQLExecuteResponse

Given the name of a table, insert tuples derived from the rowset based 
on FieldMapping
into the given table.

Faults:
- Unable to access Rowset.
- Invalid FieldMapping.

The semantics might most easily be assumed to be those of the existing 
SQL clause:

insert into TableName [(FieldName1 [, FieldName2, ...])] SelectClause

where the SelectClause is assumed to range over the tuples in the 
RowSet.  The
SQLExecuteResponse provides access to the SQLCommunicationsArea, and 
thus is able
to provide access to a wide range of error conditions.

The FieldMapping would contains a list of pairs, associating each named 
field in
TableName with corresponding fields in the SQLRowSetSchema; it can be 
optional if
the names in the two settings have a 1:1 mapping.  As an example, if the 
TableName
is Person, with fields Forename and Surname, and the Rowset contains 
tuples with
fields Firstname, Lastname and Age, then if the mapping is as follows:

{<Forename, Firstname>, <Surname, Lastname>} then the following SQL 
defines the
semantics of the update.

insert into Person (Forename, Surname)
select Firstname, Lastname
from <TableFromRowset>


1.2 Delete

The following operation could be defined:

SQLAccess::Delete(TableName, FieldMapping, RowsetIdentifier) -> 
SQLExecuteResponse

Given the name of a table, delete tuples in the given table that match 
the tuples
in the referenced Rowset in the fields provided in the FieldMapping.

Faults:
- Unable to access Rowset.
- Invalid FieldMapping.

The semantics might most easily be assumed to be those of the existing 
SQL clause:

delete from TableName
using <TableFromRowset>
WHERE <condition>

where <TableFromRowset> contains the tuples in the table referenced by the
RowsetIdentifier and the condition is obtained as follows.  For every pair
of names

<TableField, RowsetField>

in FieldMapping, a condition is inserted into a conjunction in the  
WHERE clause
of the form

TableName.TableField = <TableFromRowset>.RowsetField

SQLExecuteResponse provides access to the SQLCommunicationsArea, and 
thus is able to
provide access to a wide range of error conditions.

The FieldMapping could be optional if the names in table TableName are 
identical to
those in the result set identified by RowsetIdentifier, in wich case all 
tuples in
TableName that are identical to tuples in the Rowset will be deleted.

As an example, if the TableName is Person, with fields Forename and 
Surname, and the
Rowset contains tuples with fields Firstname, Lastname and Age, then if 
the mapping
is as follows:

{<Forename, Firstname>, <Surname, Lastname>} then the following SQL 
defines the
semantics of the update.

insert from TableName
using <TableFromRowset>
where TableName.Forename = <TableFromRowset>.Firstname and
      TableName.Surname = <TableFromRowset>.Lastname


1.3 Merge

It would also be possible to wrap the semantics of the SQL Merge 
operation in a similar
way, thereby allowing the updating of a stored table with reference to a 
rowset.

The following operation could be defined:

SQLAccess::Merge(TableName, IdentificationFieldMapping, UpdateFieldMapping,
          InsertFieldMapping, RowsetIdentifier) -> SQLExecuteResponse

Given the name of a table, delete tuples in the given table that match 
the tuples
in the referenced Rowset in the fields provided in the 
IdentificationFieldMapping
to replace existing values for the fields described using 
UpdateFieldMapping.  If
no tuple in the database matches a tuple in the Rowset, then the tuple 
from the
Rowset is inserted into the database.

Faults:
- Unable to access Rowset.
- Invalid IdentificationFieldMapping.
- Invalid UpdateFieldMapping.

The semantics might most easily be assumed to be those of the existing 
SQL clause:

merge INTO TableName
USING <TableFromRowset>
ON <match_condition>
WHEN MATCHED THEN <update_clause>
WHEN NOT MATCHED THEN <insert_clause>;

where <TableFromRowset> contains the tuples in the table referenced by the
RowsetIdentifier and the condition is obtained as follows:

a) <match_condition>  For every pair of names

<TableField, RowsetField>

in IdentificationFieldMapping, a condition is inserted into a 
conjunction of the form

TableName.TableField = <TableFromRowset>.RowsetField

b) <update_clause>  For every pair of names

<TableField, RowsetField>

in UpdateFieldMapping, an update clause is added of the form

update set TableName.TableField = <TableFromRowset>.RowsetField

c) <insert_clause>  a single insert clause is constructed of the form

insert (TableFieldName1 [, TableFieldName2, ...])
values (RowsetFieldName1 [, RowsetFieldName2, ...])

where the ith value in each of the insert and values clauses is drawn 
from the ith
entry in the InsertFieldMapping, which is of the same form as the above 
mappings


SQLExecuteResponse provides access to the SQLCommunicationsArea, and 
thus is able to
provide access to a wide range of error conditions.

The InsertFieldMapping could be optional if the names in table TableName 
are identical to
those in the result set identified by RowsetIdentifier.

As an example, if the TableName is Person, with fields Forename, Surname 
and City,
and the Rowset contains tuples with fields Firstname, Lastname and 
Location, then if
the mappings are as follows:

IdentificationFieldMapping = {<Forename, Firstname>, <Surname, Lastname>}
UpdateFieldMapping = {<City, Location>}
InsertFieldMapping = {<Forename, Firstname>, <Surname, Lastname>, <City, 
Location>}

then the following SQL defines the semantics of the merge.

merge INTO TableName
USING <TableFromRowset>
ON TableName.Forename = <TableFromRowset>.Firstname and
   TableName.Surname = <TableFromRowset>.Lastname
WHEN MATCHED THEN
   update set TableName.City = <TableFromRowset>.Location>
WHEN NOT MATCHED THEN
   insert (Forename, Surname, City)
   values (Firstname, Lastname, Location)


2. XML

Unlike the WS-DAIR specification, the WS-DAIX spec contains top-level 
update operations,
namely:

XMLCollectionAccess::AddDocuments([Name, Collection, Data]) -> 
ResponseDocument
XMLCollectionAccess::RemoveDocuments([Name], Collection) -> ResponseDocument
XMLCollectionAccess::BulkLoad([Collections],[Name, Collection, Data]) -> 
ResponseDocument

Where the above operations take Data as a parameter (for insertion only 
- deletes are
by document name, which seems more natural than by document matching), 
one could
envisage variants in which an XMLSequenceIdentifier is passed as a 
parameter.


Anyway, these are some initial thoughts on how the named results of DAIS 
factory operations
might be able to be used as inputs to other DAIS operations; comments 
invited.

Norman