acs_rel_types represents a type of
relationship between objects. For example, the following DML
statement:
insert into acs_rel_types
(rel_type,
object_type_one, role_one, min_n_rels_one, max_n_rels_one,
object_type_two, role_two, min_n_rels_two, max_n_rels_two)
values
(''employment'',
''person'', ''employee'', 0, null,
''company'', ''employer'', 0, null)
A person may be the employee of zero or more companies, and a company may be the employer of zero or more people.'; create or replace package acs_rel_type as procedure create_role ( role in acs_rel_roles.role%TYPE, pretty_name in acs_rel_roles.pretty_name%TYPE default null, pretty_plural in acs_rel_roles.pretty_plural%TYPE default null ); procedure drop_role ( role in acs_rel_roles.role%TYPE ); function role_pretty_name ( role in acs_rel_roles.role%TYPE ) return acs_rel_roles.pretty_name%TYPE; function role_pretty_plural ( role in acs_rel_roles.role%TYPE ) return acs_rel_roles.pretty_plural%TYPE; procedure create_type ( rel_type in acs_rel_types.rel_type%TYPE, pretty_name in acs_object_types.pretty_name%TYPE, pretty_plural in acs_object_types.pretty_plural%TYPE, supertype in acs_object_types.supertype%TYPE default 'relationship', table_name in acs_object_types.table_name%TYPE, id_column in acs_object_types.id_column%TYPE, package_name in acs_object_types.package_name%TYPE, abstract_p in acs_object_types.abstract_p%TYPE default 'f', type_extension_table in acs_object_types.type_extension_table%TYPE default null, name_method in acs_object_types.name_method%TYPE default null, object_type_one in acs_rel_types.object_type_one%TYPE, role_one in acs_rel_types.role_one%TYPE default null, min_n_rels_one in acs_rel_types.min_n_rels_one%TYPE, max_n_rels_one in acs_rel_types.max_n_rels_one%TYPE, object_type_two in acs_rel_types.object_type_two%TYPE, role_two in acs_rel_types.role_two%TYPE default null, min_n_rels_two in acs_rel_types.min_n_rels_two%TYPE, max_n_rels_two in acs_rel_types.max_n_rels_two%TYPE ); procedure drop_type ( rel_type in acs_rel_types.rel_type%TYPE, cascade_p in char default 'f' ); end acs_rel_type; / show errors create or replace package body acs_rel_type as procedure create_role ( role in acs_rel_roles.role%TYPE, pretty_name in acs_rel_roles.pretty_name%TYPE default null, pretty_plural in acs_rel_roles.pretty_plural%TYPE default null ) is begin insert into acs_rel_roles (role, pretty_name, pretty_plural) values (create_role.role, nvl(create_role.pretty_name,create_role.role), nvl(create_role.pretty_plural,create_role.role)); end; procedure drop_role ( role in acs_rel_roles.role%TYPE ) is begin delete from acs_rel_roles where role = drop_role.role; end; function role_pretty_name ( role in acs_rel_roles.role%TYPE ) return acs_rel_roles.pretty_name%TYPE is v_pretty_name acs_rel_roles.pretty_name%TYPE; begin select r.pretty_name into v_pretty_name from acs_rel_roles r where r.role = role_pretty_name.role; return v_pretty_name; end role_pretty_name; function role_pretty_plural ( role in acs_rel_roles.role%TYPE ) return acs_rel_roles.pretty_plural%TYPE is v_pretty_plural acs_rel_roles.pretty_plural%TYPE; begin select r.pretty_plural into v_pretty_plural from acs_rel_roles r where r.role = role_pretty_plural.role; return v_pretty_plural; end role_pretty_plural; procedure create_type ( rel_type in acs_rel_types.rel_type%TYPE, pretty_name in acs_object_types.pretty_name%TYPE, pretty_plural in acs_object_types.pretty_plural%TYPE, supertype in acs_object_types.supertype%TYPE default 'relationship', table_name in acs_object_types.table_name%TYPE, id_column in acs_object_types.id_column%TYPE, package_name in acs_object_types.package_name%TYPE, abstract_p in acs_object_types.abstract_p%TYPE default 'f', type_extension_table in acs_object_types.type_extension_table%TYPE default null, name_method in acs_object_types.name_method%TYPE default null, object_type_one in acs_rel_types.object_type_one%TYPE, role_one in acs_rel_types.role_one%TYPE default null, min_n_rels_one in acs_rel_types.min_n_rels_one%TYPE, max_n_rels_one in acs_rel_types.max_n_rels_one%TYPE, object_type_two in acs_rel_types.object_type_two%TYPE, role_two in acs_rel_types.role_two%TYPE default null, min_n_rels_two in acs_rel_types.min_n_rels_two%TYPE, max_n_rels_two in acs_rel_types.max_n_rels_two%TYPE ) is begin acs_object_type.create_type( object_type => rel_type, pretty_name => pretty_name, pretty_plural => pretty_plural, supertype => supertype, table_name => table_name, id_column => id_column, package_name => package_name, abstract_p => abstract_p, type_extension_table => type_extension_table, name_method => name_method ); insert into acs_rel_types (rel_type, object_type_one, role_one, min_n_rels_one, max_n_rels_one, object_type_two, role_two, min_n_rels_two, max_n_rels_two) values (create_type.rel_type, create_type.object_type_one, create_type.role_one, create_type.min_n_rels_one, create_type.max_n_rels_one, create_type.object_type_two, create_type.role_two, create_type.min_n_rels_two, create_type.max_n_rels_two); end; procedure drop_type ( rel_type in acs_rel_types.rel_type%TYPE, cascade_p in char default 'f' ) is begin -- XXX do cascade_p delete from acs_rel_types where acs_rel_types.rel_type = acs_rel_type.drop_type.rel_type; acs_object_type.drop_type(acs_rel_type.drop_type.rel_type, acs_rel_type.drop_type.cascade_p); end; end acs_rel_type; / show errors begin acs_rel_type.create_type ( rel_type => 'relationship', pretty_name => 'Relationship', pretty_plural => 'Relationships', supertype => 'acs_object', table_name => 'acs_rels', id_column => 'rel_id', package_name => 'acs_rel', type_extension_table => 'acs_rel_types', object_type_one => 'acs_object', min_n_rels_one => 0, max_n_rels_one => null, object_type_two => 'acs_object', min_n_rels_two => 0, max_n_rels_two => null ); commit; end; / show errors -------------------------------------- -- OPERATIONAL LEVEL: RELATIONSHIPS -- -------------------------------------- create sequence acs_rel_id_seq; create table acs_rels ( rel_id integer constraint acs_rels_rel_id_nn not null constraint acs_rels_rel_id_fk references acs_objects (object_id) on delete cascade constraint acs_rels_rel_id_pk primary key, rel_type varchar(100) constraint acs_rels_rel_type_nn not null constraint acs_rels_rel_type_fk references acs_rel_types (rel_type), object_id_one integer constraint acs_rels_object_id_one_nn not null constraint acs_rels_object_id_one_fk references acs_objects (object_id) on delete cascade, object_id_two integer constraint acs_rels_object_id_two_nn not null constraint acs_rels_object_id_two_fk references acs_objects (object_id) on delete cascade, constraint acs_object_rels_un unique (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); comment on table acs_rels is ' 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. '; ---------------------------- -- Application Data Links -- ---------------------------- create sequence acs_data_links_seq start with 1; create table acs_data_links ( rel_id integer constraint acs_data_links_rel_id_nn not null constraint acs_data_links_rel_id_pk primary key, object_id_one integer not null constraint acs_data_links_obj_one_fk references acs_objects (object_id) on delete cascade, object_id_two integer not null constraint acs_data_links_obj_two_fk references acs_objects (object_id) on delete cascade, constraint acs_data_links_un unique (object_id_one, object_id_two) ); create index acs_data_links_id_one_idx on acs_data_links (object_id_one); create index acs_data_links_id_two_idx on acs_data_links (object_id_two); -------------- -- TRIGGERS -- -------------- -- added by oumi@arsdigita.com - Jan 11, 2001 create or replace trigger acs_rels_in_tr before insert or update on acs_rels for each row declare dummy integer; target_object_type_one acs_object_types.object_type%TYPE; target_object_type_two acs_object_types.object_type%TYPE; actual_object_type_one acs_object_types.object_type%TYPE; actual_object_type_two acs_object_types.object_type%TYPE; begin -- validate that the relation being added is between objects of the -- correct object_type. If no rows are returned by this query, -- then the types are wrong and we should return an error. select 1 into dummy from acs_rel_types rt, acs_objects o1, acs_objects o2 where exists (select 1 from acs_object_types t where t.object_type = o1.object_type connect by prior t.object_type = t.supertype start with t.object_type = rt.object_type_one) and exists (select 1 from acs_object_types t where t.object_type = o2.object_type connect by prior t.object_type = t.supertype start with t.object_type = rt.object_type_two) and rt.rel_type = :new.rel_type and o1.object_id = :new.object_id_one and o2.object_id = :new.object_id_two; exception when NO_DATA_FOUND then -- At least one of the object types must have been wrong. -- Get all the object type information and print it out. select rt.object_type_one, rt.object_type_two, o1.object_type, o2.object_type into target_object_type_one, target_object_type_two, actual_object_type_one, actual_object_type_two from acs_rel_types rt, acs_objects o1, acs_objects o2 where rt.rel_type = :new.rel_type and o1.object_id = :new.object_id_one and o2.object_id = :new.object_id_two; raise_application_error (-20001, :new.rel_type || ' violation: Invalid object types. ' || 'Object ' || :new.object_id_one || ' (' || actual_object_type_one || ') ' || 'must be of type ' || target_object_type_one || '. ' || 'Object ' || :new.object_id_two || ' (' || actual_object_type_two || ') ' || 'must be of type ' || target_object_type_two || '.'); end; / show errors create or replace package acs_rel as function new ( rel_id in acs_rels.rel_id%TYPE default null, rel_type in acs_rels.rel_type%TYPE default 'relationship', object_id_one in acs_rels.object_id_one%TYPE, object_id_two in acs_rels.object_id_two%TYPE, context_id in acs_objects.context_id%TYPE default null, creation_user in acs_objects.creation_user%TYPE default null, creation_ip in acs_objects.creation_ip%TYPE default null ) return acs_rels.rel_id%TYPE; procedure del ( rel_id in acs_rels.rel_id%TYPE ); end; / show errors create or replace package body acs_rel as function new ( rel_id in acs_rels.rel_id%TYPE default null, rel_type in acs_rels.rel_type%TYPE default 'relationship', object_id_one in acs_rels.object_id_one%TYPE, object_id_two in acs_rels.object_id_two%TYPE, context_id in acs_objects.context_id%TYPE default null, creation_user in acs_objects.creation_user%TYPE default null, creation_ip in acs_objects.creation_ip%TYPE default null ) return acs_rels.rel_id%TYPE is v_rel_id acs_rels.rel_id%TYPE; begin -- XXX This should check that object_id_one and object_id_two are -- of the appropriate types. v_rel_id := acs_object.new ( object_id => rel_id, object_type => rel_type, title => rel_type || ': ' || object_id_one || ' - ' || object_id_two, context_id => context_id, creation_user => creation_user, creation_ip => creation_ip ); insert into acs_rels (rel_id, rel_type, object_id_one, object_id_two) values (v_rel_id, new.rel_type, new.object_id_one, new.object_id_two); return v_rel_id; end; procedure del ( rel_id in acs_rels.rel_id%TYPE ) is begin acs_object.del(rel_id); end; end; / show errors ----------- -- VIEWS -- ----------- -- These views are handy for metadata driven UI -- View: rel_types_valid_obj_one_types -- -- Question: Given rel_type :rel_type, -- -- What are all the valid object_types for object_id_one of -- a relation of type :rel_type -- -- Answer: select object_type -- from rel_types_valid_obj__one_types -- where rel_type = :rel_type -- create or replace view rel_types_valid_obj_one_types as select rt.rel_type, th.object_type from acs_rel_types rt, (select object_type, ancestor_type from acs_object_type_supertype_map UNION ALL select object_type, object_type as ancestor_type from acs_object_types) th where rt.object_type_one = th.ancestor_type; -- View: rel_types_valid_obj_two_types -- -- Question: Given rel_type :rel_type, -- -- What are all the valid object_types for object_id_two of -- a relation of type :rel_type -- -- Answer: select object_type -- from rel_types_valid_obj_two_types -- where rel_type = :rel_type -- create or replace view rel_types_valid_obj_two_types as select rt.rel_type, th.object_type from acs_rel_types rt, (select object_type, ancestor_type from acs_object_type_supertype_map UNION ALL select object_type, object_type as ancestor_type from acs_object_types) th where rt.object_type_two = th.ancestor_type;