Thanks Sebastian,
This is indeed very useful and worth pursuing. Also, in the future, we may
add support for ON UPDATE / DELETE SET NULL and SET DEFAULT. First, I have
reviewed your changes to various classes and incorporated your changes to
all but Parser.java and Table.java. I have allowed for future addition of
the SET NULL or SET DEFAULT actions. These changes are in the CVS hsqldb-dev
module.
Continuing this work, the checkUpdateCascade() method should be made
recursive, following the pattern of checkDeleteCascade(). To simplify the
development, you can at first write two methods, one that checks and one
that updates and merge the two together once you've got it to work.
checkUpdateCascade(..., update==false) should perform two tests, one similar
to checkDeleteCascade( , delete == false), the other test against any
different FK constraint not in the same tree. In the example below, if you
start the check on a row in table 1 and it goes on to check table3, an
additional test must be made to make sure the change in table3 conforms to
the other FK that references table2.
table1 table 2
table3 (FK references table1, FK references table2)
In order to optimise the work, the signature of checkUpdateCascade() may
have to change to include a column list for the changed columns against the
FK indexs involved. Otherwise changes to non-FK columns will fire an
unnecessary test.
Please comment and let us know how you're making progress. I should be able
to help further with the recursive method.
Fred Toussi
-------------------------------
Sebastian Kloska wrote:
I've worked on the hsqldb_1_7_2_ALPHA_D
code trying to implement 'ON UPDATE CASCADE'
It seems to begin to work:
Ok I've checked it in a 'threetable situation'
with no recursion.
In short the changes are as follows:
1) The parser now understands the 'ON UPDATE CASCADE'
option and initializes a modified TempConstraint
and Constraint object.
2) Constraint and TempConstraint now carry two booleans
uConstraint and dConstraint to reflect if the
foreign key supports 'on delete' (and/or) 'on update'.
3) Constraint has no isCascade method anymore but
isDeleteCascade and isUpdateCascade.Calls to
the old method have been modified.
4) Constraint.findFkRef now has an additional
boolean flag telling the method under which
circumstances to throw an exception.
5) Parser.processUpdate now calls the new method
table.checkUpdateCascade on all rows it likes
to insert into the table with the new row and the
old row data.
6) table.checkUpdateCascade follows a similar policy
like checkDeleteCascade. If it finds a reference
without an 'on update cascade' it throws an exception
via findFkRef. Otherwis it remembers the referenced
rows replacing the old column data with the new column
data from the master row. deletes the old row and after
deleting all rows inserts them once agin with the
updated data.
This is all still very flaky and I have only tested it on
three very simple tables like:
creat table a(
a int primary key,
b int,
);
create index aidx on a(a,b);
create table b(
a int,
b int,
foreign key(a)
references a(a)
on update cascade);
create table c(
a int,
b int,
foreign key(a,b)
references a(a,b)
on update cascade)
The old code is almost untouche. So
e.g. checkUpdate is still called etc but
I wanted to get this out to ask if something
like this is usefull anyway befor I start
to work on it further
Cheers
Sebastian
-------------------------------------------------------
This sf.net email is sponsored by:
Access Your PC Securely with GoToMyPC. Try Free Now
https://www.gotomypc.com/s/OSND/DD
_______________________________________________
hsqldb-developers mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
