by (Use mono-spaced font for best result)
By Kolbjorn Aamboe, University of Oslo Library, Norway.
Implementing table addition/inheritance in SQL using LEFT OUTER JOIN.
=====================================================================
We have a Table A and want to add data in two different and
complementary ways. We want to express as follows: (a, b and c's are
tupples/instances/objects and A, B, and C are Relations/ Tables/
Classes). Where there is a b there always is one and only one a for
that b. Where there is a c there always is one and only one a for
that c.
(A) <------------------ (B)
^
|
+--------------------- (C)
meaning (in a more Object Oriented Jargon): A is the SUPER-class, and
B and C are SUB-classes for A. Some also say that B and C INHERIT
some of their properties from A. A, B and C need some attributes to
be proper behaving classes but we don't draw theme here. It suffice
to say that A have a Primary/Preferred key, and a Secondary
key to make the a's more available to humans. System Assigned
Surrogate key as known from for instance Codd's RT/M paper is used as
primary keys. b's and c's use the primary key of a's to identify
which a's they are additional information for. The following SQL
statements define our data base:
In Watcom SQL:
==============
Create Table A (
KEY_1 INTEGER NOT NULL,
Key_2_1 CHAR (32) NOT NULL,
Key_2_2 INTEGER NOT NULL,
:
Comment CHAR (32000) );
Create Table B(
KEY_1 INTEGER NOT NULL,
Title CHAR (255) NOT NULL);
Create Table C (
KEY_1 INTEGER NOT NULL,
day DATE );
// *** Index Division for the Data Model
Create Unique Index NAV_I2_A on A (Key_2_1,Key_2_2,);
// *** Uniqueness Constraints
Alter Table A Add Primary Key (KEY_1);
Alter Table A Add Unique (Key_2_1,Key_2_2);
Alter Table B Add Primary Key (KEY_1);
Alter Table C Add Primary Key (KEY_1);
// *** Referential Subset Constraints
Alter table B Add Foreign Key A_B(KEY_1) References A (KEY_1);
Alter table C Add Foreign Key A_C (KEY_1) References A (KEY_1);
SELECT, UPDATE, INSERT, DELETE from a Client
============================================
To SELECT from A, B and C into the same PowerBuilder DataWindow:
----------------------------------------------------------------
SELECT
"A"."KEY_1",
"A"."Key_2_1",
"A"."Key_2_2",
"A"."Comment",
"B"."KEY_1",
"B"."Title",
"C"."KEY_1",
"C"."Day"
FROM
{oj "A" LEFT OUTER JOIN "B" ON "A"."KEY_1" = "B"."KEY_1"},
{oj "A" LEFT OUTER JOIN "C" ON "A"."KEY_1" = "C"."KEY_1"}
ORDER BY
"A"."Key_2_1" ASC,
"A"."Key_2_2" ASC,
PowerBuilder DataWindow Painter clarify what LEFT OUTER JOIN is:
-----------------------------------------------------------------
{oj "A" LEFT OUTER JOIN "B" ON "A"."KEY_1" = "B"."KEY_1"}, meaning:
A.KEY_1 = B.KEY_1 and rows from A that have no B
{oj "A" LEFT OUTER JOIN "C" ON "A"."KEY_1" = "C"."KEY_1"}, meaning:
A.KEY_1 = C.KEY_1 and rows from A that have no C
In the graphic select browser LEFT OUTER JOIN is shown as (sketch)
-------+ +----------....
A | | B
-------+ +----+ +----------....
KEY_1 |------o| = |--------- |KEY_1
| +----+ |
: :
To update a data base entails UPDATEs, INSERTs and DELETEs. What the
Client software of choice is allowed or able to do is
constrained by the different Uniqueness and Subset constraints
already implemented above by the RDBMS of choice. These constraints
are not automatically implemented in the client. To minimise the number
of updates that the RDBMS refuse to perform, it is wise to
implement a copy of the same constraint in the client. In principle
it should be possible to make the same program that perform the
NIAM grouping into Databases designs to perform an automatic
generation of user interfaces. Such a tool must for obvious reasons be
integrated closely with the client design program or otherwise
the graphical changes of the client will not feed back to the model
and consequently a change of the model can not be integrated with the
old clients designs. It is also worth hoping for the same tools
ability to change both database designs and consequently the old
data.
SELECT
======
In the mean time we have to implement things more or less by hand and
hope that client design tools like PowerBuilder 4.0 is able to do
part of the work for us. Using the SQL SELECT statement above we can
easily populate a so called DataWindow with A rows with additional
corresponding data from B and C rows. A Window in PowerBuilder is
like any window on a Macintosh or Windows program. Such a Window
may contain several DataWindows that may or may not be dependent on
each others contents. Such dependencies must be explicitly coded (as
RowFocusChanged events for the master to change the Detail
DataWindow)even though they should be obvious from the NIAM (data)
model and consequently from the RDBMS constraints. Another quirk of
the PowerBuilder DataWindow is its less than half-hearted ability to
update more than one table from a DataWindow. If you read the
documentation and believe what's written you may be able to
decipher that it is in deed POSSIBLE to update several tables from
the same DataWindow. I'm sorry to say, but it is quite an advantage
that these tables are not interrelated too closely for the multiple
update to work at it's best. What is meant by that will be addressed as follows:
UPDATE
======
How to update all modified rows.
--------------------------------
In the Update Criteria Dialogue box of the DataWindow of choice you
can set which of the tables to update per default when updates are
required, we chose table A since it is the super class vs. B and C.
To have a tupple/object you at least have to have the required fields
of A populated. If you are able to decipher the less than half a
meter of documentation correctly you will be able to first update all
the changed columns of A without resetting the flags for changes in
possibly empty B and C columns: We assume that our DataWindow is
named dw_1:
if dw_1. Update(TRUE, FALSE) < 0 then
ROLLBACK;
Success = FALSE
end if
if Success then ...
-will do that for you. We don't want to COMMIT yet because things may
get sour later on. Then comes all the convoluted code to turn off the
update criteria for A. (A is implicit here)
dw_1.Modify("KEY_1.Update = No")
dw_1.Modify("Key_2_1.Update = No")
dw_1.Modify("Key_2_2.Update = No")
dw_1.Modify("Comment.Update = No")
We don't want the primary key of A to be used in the WHERE statement
anymore:
dw_1.Modify("KEY_1.Key = No")
Then we want to set B to be the next table to update:
dw_1.Modify("DataWindow.Table.UpdateTable = ~"B~"")
Remember that typos here make for no feed-back what so ever ;-(
Then we tell which attributes from B to be updated if they have
their changed flags enabled (Since the KEY_1 have the same name
as in A the system has supplied a more explicit name B_KEY_1:
dw_1.Modify("B_KEY_1.Update = Yes")
dw_1.Modify("B_KEY_1.Key = Yes")
dw_1.Modify("B_Title.Update = Yes")
Then we are ready for a new update along the same lines as the
previous one
if dw_1.Update(TRUE, FALSE) < 0 then
ROLLBACK;
Success = FALSE
end if
if Success then ?
Since we have another table that may have changed columns/attributes
we still don't want to COMMIT since there are still things that may
stumble... Now we change Update Criteria using the convoluted scripts
once again:
dw_1.Modify("B_KEY_1.Update = No")
dw_1.Modify("B_KEY_1.Key = No")
dw_1.Modify("B_Title.Update = No")
dw_1.Modify("DataWindow.Table.UpdateTable = ~"C~"")
dw_1.Modify("C_KEY_1.Update = Yes")
dw_1.Modify("C_KEY_1.Key = Yes")
dw_1.Modify("C_Day.Update = Yes")
Then we are ready for the finale where we choose to forget all Update
flags if things are successful otherwise the update flags will still
be around:
if dw_1.Update() > 0 then
COMMIT;
else
ROLLBACK;
end if
Then we have to set Update Criteria back so another quest for update
behave the same way:
dw_1.Modify("C_KEY_1.Update = No")
dw_1.Modify("C_KEY_1.Key = No")
dw_1.Modify("C_Day.Update = No")
dw_1.Modify("DataWindow.Table.UpdateTable = ~"A~"")
dw_1.Modify("KEY_1.Update = Yes")
dw_1.Modify("Key_2_1.Update = Yes")
dw_1.Modify("Key_2_2.Update = Yes")
dw_1.Modify("Comment.Update = Yes")
As long as you need to update without any INSERTions or DELETions
things will be well. But try to make an INSERT to be handled by
this mechanism and you will get into problems with NULL values for
the B and C columns that are not populated.
INSERT
======
insertion must be performed in a, b, c order since B and C is
dependent on A.
Insertion could have been done through the same mechanism as updates
if it where not for a simple need. A's secondary key can be system
assigned as the next key in the row of keys already used. New objects
must be INSERTed and COMMITted one at a time.
DELETE
======
Deletion must be performed in the opposite order; delete b's or c's
(that are dependent on a's), then a's are deletet as there are then
no objects depending on them.
Since deletion is performed in another order than updates the en-mass
update mechanism are deemed un-useful for deletion.
PROBLEMS
========
LEFT OUTER JOIN is not part of contemporary Standard SQL (SQL-92?)
according to Elmasri/Navathe: Fundamentals of Database Systems
pp.736. It's actually quite a disaster that such a tool for table
evolution is not part of the standard but have to be searched for
on a implementation basis.
Questions:
==========
Q1:
Is there any chance that the next PowerBuilder could solve this problem
more implicit?
Q2:
Is there any chance that SQL3 insert and delete will be more powerful by
infering more from interdependencies and constraints?
................................................................
Universitetsbiblioteket i Oslo/Bibliografisk avd.
The University of Oslo library/Bibliographic dept.
N-0242 Oslo, Norway
Kolbj|rn H. Aamb|
University Librarian
Phone: +47 22 85 91 36
Fax: +47 22 85 90 50
E-mail: kolbjorn.aa...@ub.uio.no
http://www.nbo.uio.no/html/nansen/nanseneng.html
http://www.nbo.uio.no/html/krigstr/krigstr.html
................................................................
There is No Way like Norway
................................................................
AMEN, ALI AKBAR, SHALOM, HIL, Carriage Return!