I have some tables to model assemblies (of "stuff"). Each assembly
can have several associated tests and resources used by the tests.
The resources, once defined, can be reused when creating new tests
on the same assemblies.
The tables are defined like so:
CREATE TABLE assemblies (
assembly VARCHAR(15) NOT NULL,
title VARCHAR(255) NOT NULL,
CONSTRAINT assemblies _pk PRIMARY KEY (assembly)
);
CREATE TABLE tests (
id INTEGER GENERATED ALWAYS AS IDENTITY,
assembly VARCHAR(15) NOT NULL,
title VARCHAR(255) NOT NULL,
CONSTRAINT tests_pk PRIMARY KEY (id),
CONSTRAINT tests_1 UNIQUE (assembly, title),
CONSTRAINT tests_2 FOREIGN KEY (assembly)
REFERENCES assemblies(assembly)
ON DELETE CASCADE
);
CREATE TABLE resources (
id INTEGER GENERATED ALWAYS AS IDENTITY,
name VARCHAR(256) NOT NULL,
assembly VARCHAR(15) NOT NULL,
refcount INTEGER DEFAULT 0,
CONSTRAINT resources_pk PRIMARY KEY (id),
CONSTRAINT resources_1 UNIQUE (name,assembly)
);
CREATE TABLE resource_usage (
resid INTEGER NOT NULL,
testid INTEGER NOT NULL,
CONSTRAINT resusage_1 FOREIGN KEY (resid)
REFERENCES resources(id)
ON DELETE CASCADE
);
The idea is that when a test is modified, the resources it
used to use are deleted from resource_usage and the resources
it now uses are added to resource_usage. I have triggers to
maintain the reference count (refcount) in resources:
CREATE TRIGGER delete_usage
AFTER DELETE ON resource_usage
REFERENCING OLD AS del
FOR EACH ROW MODE DB2SQL
UPDATE resources
SET refcount=refcount-1
WHERE id=del.resid;
CREATE TRIGGER insert_usage
AFTER INSERT ON resource_usage
REFERENCING NEW AS ins
FOR EACH ROW MODE DB2SQL
UPDATE resources
SET refcount=refcount+1
WHERE id=ins.resid;
Deleting a test fires a trigger to delete all the associated
resource usages:
CREATE TRIGGER delete_test
AFTER DELETE ON tests
REFERENCING OLD AS del
FOR EACH ROW MODE DB2SQL
DELETE FROM resource_usage
WHERE xmlid=del.id
Another trigger deletes resources when they are no longer
referenced:
CREATE TRIGGER delete_resource
AFTER UPDATE ON resources
REFERENCING OLD AS upd
FOR EACH ROW MODE DB2SQL
DELETE FROM resources
WHERE id=upd.id
AND refcount=0;
Now, what happens is that if I delete a test, all works fine.
The associated resource_usage records get deleted by the delete_test
trigger, which in turn decrements the resource refcounts, which in
turn triggers resource deletion if any resources have a reference
count of 0.
If however I delete an assembly, what I get is this:
java.sql.SQLException : An attempt was made to put a data value of type
'java.lang.String' into a data value of type 'INTEGER'.
According to the Derby log file, the last thing that happens is this:
Executing prepared statement: DELETE FROM resource_usage
WHERE xmlid=CAST
(org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getOldRow().getObject(1)
AS INTEGER)
Now, getObject(1) in all the tables except "assemblies" will return an
integer. What I think is happening is that when I say "delete from
assemblies where assembly=?", the assembly deletion cascades to delete
the associated tests, and this in turn fires the delete_test trigger;
however, the "REFERENCING OLD AS del" clause refers to the original
"delete from assemblies" query, not to the cascaded test deletion.
Can anyone:
(a) confirm my reasoning?
(b) suggest a workaround?
Many thanks,
----------------------------------------------------------------------
John English | mailto:[email protected]
Senior Lecturer | http://www.it.bton.ac.uk/staff/je
School of Computing & MIS | "Those who don't know their history
University of Brighton | are condemned to relive it" (Santayana)
----------------------------------------------------------------------
