Delta maintenances of Transitive Closure by Active Rules

1. Delta maintenances of Transitive Closure by Active Rules CS240A Homework Solution Carlo Zaniolo, UCLA CS Department

2. Non-Recursive delta rules for arc(X,Y) If only one new arc d+arc(A,B) is inserted d+closr(A, B) <- d+arc(A, B). d+closr(A, Z) <- d+arc(A,B), closr(B,Z). d+closr(X, B) <- closr(X,A), d+arc(A,B). d+closr(X, Z) <- closr(X,A), d+arc(A,B),closr(A,Z). CREATE TRIGGER POSDELTA AFTER INSERT ON ARC FOR EACH ROW INSERT INTO CLOSR SELECT VALUES( NEW.FROM, NEW.TO) UNION SELECT NEW.FROM, CLOSR.TO FROM CLOSR WHERE NEW.TO=CLOSR.FROM AND (NEW.FROM, CLOSR.TO) NOT IN (SELECT * FROM CLOSR) UNION SELECT CLOSR.FROM, NEW.T FROM CLOSR WHERE CLOSR.TO=NEW.FROM AND (CLOSR.FROM, NEW.TO) NOT IN (SELECT * FROM CLOSR) UNION SELECT C1.FROM, C2.TO FROM CLOSR AS C1, CLOSR AS C2 WHERE C1.TO=NEW.FROM AND NEW.TO=C2.FROM AND (C1.FROM, C2.TO) NOT IN (SELECT * FROM CLOSR) With active rules, we can use the ‘for each row’ option to obtainthe same effect as ‘only one new arc’ The `not in' conditions should be omitted for trees. The only minor disadvantage of these rules is that if temp contains tuples at the time CLOSR is created we must first populate with a recursive SQL query

3. Logical rules for negative deltas d-closr(A, B) <- d-arc(A, B). d-closr(A, Z) <- d-arc(A,B), closr(B,Z). d-closr(X, B) <- closr(X,A), d-arc(A,B). d-closr(X, Z) <- closr(X,A), d-arc(A,B), closr(B,Z). CREATE TRIGGER NEGDELTA AFTER DELETE FROM ARC FOR EACH ROW DELETE FROM CLOSR WHERE (CLOSR.FROM, CLOSR.TO) IN (SELECT OLD.FROM, CLOSR.TO FROM CLOSR WHERE OLD.TO=CLOSR.FROM) OR (CLOSR.FROM, CLOSR.TO) IN (SELECT CLOSR.FROM, OLD.TO FROM CLOSR WHERE CLOSR.TO=OLD.FROM) OR (CLSR.FROM, CLOSR.TO) IN (SELECT C1.FROM, C2.TO FROM CLOSR AS C1, CLOSR AS C2 WHERE C1.TO=OLD.FROM AND C2.FROM =OLD.TO) Nothing else is needed for a tree or a forest of trees.

4. Reinserting is harder Here too me must save the tuples deleted by NEGDELTA in a temporary table. We can reuse the NEG3 rule CREATE TRIGGER NEWNEG3 AFTER DELETE FROM CLOSR FOR EACH STATEMENT INSERT INTO TEMP SELECT * FROM OLD-TABLE The logical reinsert rules: But these are triggered by an insertion into ARC---which will also trigger POS1!*? To get around that problem we store the deleted tuple in another table called DARC CREATE TRIGGER NEG2POS AFTER DELETE FROM ARC FOR EACH STATEMENT INSERT INTO DARC SELECT * FROM NEW-TABLE d+closr(A, B) <- d+arc(A, B), temp(A, B). d+closr(A, Z) <- d+arc(A,B), closr(B,Z), temp(A,Z). d+closr(X, B) <- closr(X,A), d+arc(A,B), temp(X,B). d+closr(X, Z) < -closr(X,A), d+arc(A,B),closr(B,Z), temp(X,Z).

5. Reinsert rules for negative deltas This must follow NEWNEG3 CREATE TRIGGER NEWREINSERT AFTER INSERT ON DARC FOR EACH ROW INSERT INTO CLOSR SELECT NEW.FROM, CLOSR.TO FROM CLOSR, TEMP WHERE NEW.TO=CLOSR.FROM AND (NEW.FROM, CLOSR.TO) IN (SELECT * FROM TEMP) AND (NEW.FROM, CLOSR.TO) NOT IN (SELECT * FROM CLOSR) UNION SELECT CLOSR.FROM, NEW.TO FROM CLOSR WHERE CLOSR.TO=NEW.FROM AND (CLOSR.FROM, NEW.TO) IN (SELECT * FROM TEMP) AND (CLOSR.FROM, NEW.TO) NOT IN (SELECT * FROM CLOSR) UNION SELECT C1.FROM, C2.TO FROM CLOSR AS C1, CLOSR AS C2 WHERE C1.TO=NEW.FROM AND NEW.TO=C2.FROM AND (C1.FROM, C2.TO) IN (SELECT * FROM TEMP) AND (C1.FROM, C2.TO) NOT IN (SELECT * FROM CLOSR)

6. Cleaning up at the end The next rule follows NEWREINSERT. CREATE TRIGGER CLEANUP-TEMP AFTER INSERT ON CLOSRDELETE * FROM TEMP Also cleanup DARC Comments ? Large active rules applications are no fun …*!