-- -- The acs_rels table is essentially a generic mapping table for -- acs_objects. Once we come up with a way to associate attributes with -- relationship types, we could replace many of the ACS 3.x mapping -- tables like user_content_map, user_group_map, and -- user_group_type_modules_map with this one table. Much application -- logic consists of asking questions like "Does object X have a -- relationship of type Y to object Z?" where all that differs is -- X, Y, and Z. Thus, the value of consolidating many mapping tables -- into one is that we can provide a generic API for defining and -- querying relationships. In addition, we may need to design a way to -- enable "type_specific" storage for relationships (i.e., foreign key -- columns for one-to-many relationships and custom mapping tables for -- many-to-many relationships), instead of only supporting "generic" -- storage in the acs_rels table. This would parallel what we do with -- acs_attributes. -- CREATE TABLE acs_rels ( rel_id integer PRIMARY KEY NOT NULL, rel_type varchar(100) NOT NULL, object_id_one integer NOT NULL, object_id_two integer NOT NULL, ON DELETE CASCADE ON DELETE CASCADE ON DELETE CASCADE ON DELETE CASCADE ON DELETE CASCADE, CONSTRAINT acs_rels_object_id_two_fk REFERENCES acs_objects (), CONSTRAINT acs_rels_pk REFERENCES acs_objects (), CONSTRAINT acs_rels_rel_type_fk REFERENCES acs_objects (), CONSTRAINT acs_rels_object_id_one_fk REFERENCES acs_objects (), CONSTRAINT acs_rels_rel_id_fk REFERENCES acs_objects () ON DELETE CASCADE ON DELETE CASCADE ON DELETE CASCADE ON DELETE CASCADE ON DELETE CASCADE, CONSTRAINT acs_rels_pk REFERENCES acs_objects (), CONSTRAINT acs_rels_object_id_one_fk REFERENCES acs_objects (), CONSTRAINT acs_rels_object_id_two_fk REFERENCES acs_objects (), CONSTRAINT acs_rels_rel_id_fk REFERENCES acs_objects (), CONSTRAINT acs_rels_rel_type_fk REFERENCES acs_objects (), CONSTRAINT acs_rels_rel_type_fk REFERENCES acs_objects (), CONSTRAINT acs_rels_rel_id_fk REFERENCES acs_objects (), CONSTRAINT acs_rels_pk REFERENCES acs_objects (), CONSTRAINT acs_rels_object_id_two_fk REFERENCES acs_objects (), CONSTRAINT acs_rels_object_id_one_fk REFERENCES acs_objects (), CONSTRAINT acs_rels_rel_type_fk REFERENCES acs_rel_types (), CONSTRAINT acs_rels_object_id_one_fk REFERENCES acs_rel_types (), CONSTRAINT acs_rels_object_id_two_fk REFERENCES acs_rel_types (), CONSTRAINT acs_rels_pk REFERENCES acs_rel_types (), CONSTRAINT acs_rels_rel_id_fk REFERENCES acs_rel_types () ); CREATE UNIQUE INDEX acs_object_rels_un ON acs_rels (rel_type, object_id_one, object_id_two); CREATE INDEX acs_rels_object_id_one_idx ON acs_rels (object_id_one); CREATE INDEX acs_rels_object_id_two_idx ON acs_rels (object_id_two); CREATE TRIGGER acs_rels_in_tr BEFORE INSERT OR UPDATE FOR EACH ROW EXECUTE PROCEDURE acs_rels_in_tr (disabled) CREATE TRIGGER RI_ConstraintTrigger_c_340524 AFTER INSERT FOR EACH ROW EXECUTE PROCEDURE RI_FKey_check_ins (disabled) CREATE TRIGGER RI_ConstraintTrigger_c_340525 AFTER UPDATE FOR EACH ROW EXECUTE PROCEDURE RI_FKey_check_upd (disabled) CREATE TRIGGER RI_ConstraintTrigger_c_340529 AFTER INSERT FOR EACH ROW EXECUTE PROCEDURE RI_FKey_check_ins (disabled) CREATE TRIGGER RI_ConstraintTrigger_c_340530 AFTER UPDATE FOR EACH ROW EXECUTE PROCEDURE RI_FKey_check_upd (disabled) CREATE TRIGGER RI_ConstraintTrigger_c_340534 AFTER INSERT FOR EACH ROW EXECUTE PROCEDURE RI_FKey_check_ins (disabled) CREATE TRIGGER RI_ConstraintTrigger_c_340535 AFTER UPDATE FOR EACH ROW EXECUTE PROCEDURE RI_FKey_check_upd (disabled) CREATE TRIGGER RI_ConstraintTrigger_c_340539 AFTER INSERT FOR EACH ROW EXECUTE PROCEDURE RI_FKey_check_ins (disabled) CREATE TRIGGER RI_ConstraintTrigger_c_340540 AFTER UPDATE FOR EACH ROW EXECUTE PROCEDURE RI_FKey_check_upd (disabled) CREATE TRIGGER RI_ConstraintTrigger_a_341207 AFTER DELETE FOR EACH ROW EXECUTE PROCEDURE RI_FKey_noaction_del (disabled) CREATE TRIGGER RI_ConstraintTrigger_a_341208 AFTER UPDATE FOR EACH ROW EXECUTE PROCEDURE RI_FKey_noaction_upd (disabled) CREATE TRIGGER RI_ConstraintTrigger_a_342492 AFTER DELETE FOR EACH ROW EXECUTE PROCEDURE RI_FKey_noaction_del (disabled) CREATE TRIGGER RI_ConstraintTrigger_a_342493 AFTER UPDATE FOR EACH ROW EXECUTE PROCEDURE RI_FKey_noaction_upd (disabled) CREATE TRIGGER RI_ConstraintTrigger_a_342602 AFTER DELETE FOR EACH ROW EXECUTE PROCEDURE RI_FKey_noaction_del (disabled) CREATE TRIGGER RI_ConstraintTrigger_a_342603 AFTER UPDATE FOR EACH ROW EXECUTE PROCEDURE RI_FKey_noaction_upd (disabled) CREATE TRIGGER RI_ConstraintTrigger_a_342802 AFTER DELETE FOR EACH ROW EXECUTE PROCEDURE RI_FKey_cascade_del (disabled) CREATE TRIGGER RI_ConstraintTrigger_a_342803 AFTER UPDATE FOR EACH ROW EXECUTE PROCEDURE RI_FKey_noaction_upd (disabled) -- Tables with foreign keys that refer to acs_rels: --composition_rels(acs_rels_object_id_one_fk) --composition_rels(acs_rels_object_id_two_fk) --composition_rels(acs_rels_pk) --composition_rels(acs_rels_rel_id_fk) --composition_rels(acs_rels_rel_type_fk) --group_element_index(acs_rels_object_id_one_fk) --group_element_index(acs_rels_object_id_two_fk) --group_element_index(acs_rels_pk) --group_element_index(acs_rels_rel_id_fk) --group_element_index(acs_rels_rel_type_fk) --membership_rels(acs_rels_object_id_one_fk) --membership_rels(acs_rels_object_id_two_fk) --membership_rels(acs_rels_pk) --membership_rels(acs_rels_rel_id_fk) --membership_rels(acs_rels_rel_type_fk) --party_approved_member_map(acs_rels_object_id_one_fk) --party_approved_member_map(acs_rels_object_id_two_fk) --party_approved_member_map(acs_rels_pk) --party_approved_member_map(acs_rels_rel_id_fk) --party_approved_member_map(acs_rels_rel_type_fk) -- Table size: 1,310,720 bytes -- Table rows: 21,698
Begin main content
