Index: openacs-4/packages/acs-kernel/sql/postgresql/rel-constraints-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/rel-constraints-create.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/acs-kernel/sql/postgresql/rel-constraints-create.sql 14 Mar 2001 04:39:10 -0000 1.1 +++ openacs-4/packages/acs-kernel/sql/postgresql/rel-constraints-create.sql 14 Mar 2001 05:56:26 -0000 1.2 @@ -61,14 +61,90 @@ required_rel_segment integer not null constraint rc_required_rel_segment references rel_segments (segment_id), + tree_sortkey varchar(4000), constraint rel_constraints_uq unique (rel_segment, rel_side, required_rel_segment) ); -- required_rel_segment has a foreign key reference - create an index create index rel_constraint_req_rel_seg_idx on rel_constraints(required_rel_segment); +create index rel_constraint_tree_skey_idx on rel_constraints (tree_sortkey); +create function rel_constraints_insert_tr () returns opaque as ' +declare + v_parent_sk varchar; + max_key varchar; +begin + select max(tree_sortkey) into max_key + from rel_constraints + where required_rel_segment = new.required_rel_segment; + select coalesce(max(tree_sortkey),'''') into v_parent_sk + from rel_constraints + where rel_segment = new.required_rel_segment; + + new.tree_sortkey := v_parent_sk || ''/'' || tree_next_key(max_key); + + return new; + +end;' language 'plpgsql'; + +create trigger rel_constraints_insert_tr before insert +on rel_constraints for each row +execute procedure rel_constraints_insert_tr (); + +create function rel_constraints_update_tr () returns opaque as ' +declare + v_parent_sk varchar; + max_key varchar; + v_rec record; + clr_keys_p boolean default ''t''; +begin + if new.rel_segment = old.rel_segment and + new.required_rel_segment = old.required_rel_segment then + + return new; + + end if; + + for v_rec in select rel_segment + from rel_constraints + where tree_sortkey like new.tree_sortkey || ''%'' + order by tree_sortkey + LOOP + if clr_keys_p then + update rel_constraints set tree_sortkey = null + where tree_sortkey like new.tree_sortkey || ''%''; + clr_keys_p := ''f''; + end if; + + select max(tree_sortkey) into max_key + from rel_constraints + where required_rel_segment = (select required_rel_segment + from rel_constraints + where rel_segment = v_rec.rel_segment); + + select coalesce(max(tree_sortkey),'''') into v_parent_sk + from rel_constraints + where rel_segment = (select required_rel_segment + from rel_constraints + where rel_segment = v_rec.rel_segment); + + update rel_constraints + set tree_sortkey = v_parent_sk || ''/'' || tree_next_key(max_key) + where rel_segment = v_rec.rel_segment; + + end LOOP; + + return new; + +end;' language 'plpgsql'; + +create trigger rel_constraints_update_tr after update +on rel_constraints +for each row +execute procedure rel_constraints_update_tr (); + comment on table rel_constraints is ' Defines relational constraints. The relational constraints system is intended to support applications in modelling and applying @@ -107,24 +183,24 @@ -- from the constraints and the associated relations of rel_segment -- where the relation's container_id (i.e., object_id_one) is not in the -- relational segment required_rel_segment. -/* -create view rel_constraints_violated_one as -select constrained_rels.* -from (select rel_constraints.constraint_id, rel_constraints.constraint_name, - r.rel_id, r.container_id, r.party_id, r.rel_type, - rel_constraints.rel_segment, - rel_constraints.rel_side, - rel_constraints.required_rel_segment - from rel_constraints, rel_segment_party_map r - where rel_constraints.rel_side = 'one' - and rel_constraints.rel_segment = r.segment_id - ) constrained_rels, - rel_segment_party_map rspm -where rspm.segment_id(+) = constrained_rels.required_rel_segment - and rspm.party_id(+) = constrained_rels.container_id - and rspm.party_id is null; -*/ +-- create view rel_constraints_violated_one as +-- select constrained_rels.* +-- from (select rel_constraints.constraint_id, rel_constraints.constraint_name, +-- r.rel_id, r.container_id, r.party_id, r.rel_type, +-- rel_constraints.rel_segment, +-- rel_constraints.rel_side, +-- rel_constraints.required_rel_segment +-- from rel_constraints, rel_segment_party_map r +-- where rel_constraints.rel_side = 'one' +-- and rel_constraints.rel_segment = r.segment_id +-- ) constrained_rels, +-- rel_segment_party_map rspm +-- where rspm.segment_id(+) = constrained_rels.required_rel_segment +-- and rspm.party_id(+) = constrained_rels.container_id +-- and rspm.party_id is null; + + create view rel_constraints_violated_one as select constrained_rels.* from (select rel_constraints.constraint_id, rel_constraints.constraint_name, @@ -170,24 +246,23 @@ -- from the constraints and the associated relations of rel_segment -- where the relation's party_id (i.e., object_id_two) is not in the -- relational segment required_rel_segment. -/* -create view rel_constraints_violated_two as -select constrained_rels.* -from (select rel_constraints.constraint_id, rel_constraints.constraint_name, - r.rel_id, r.container_id, r.party_id, r.rel_type, - rel_constraints.rel_segment, - rel_constraints.rel_side, - rel_constraints.required_rel_segment - from rel_constraints, rel_segment_party_map r - where rel_constraints.rel_side = 'two' - and rel_constraints.rel_segment = r.segment_id - ) constrained_rels, - rel_segment_party_map rspm -where rspm.segment_id(+) = constrained_rels.required_rel_segment - and rspm.party_id(+) = constrained_rels.party_id - and rspm.party_id is null; -*/ +-- create view rel_constraints_violated_two as +-- select constrained_rels.* +-- from (select rel_constraints.constraint_id, rel_constraints.constraint_name, +-- r.rel_id, r.container_id, r.party_id, r.rel_type, +-- rel_constraints.rel_segment, +-- rel_constraints.rel_side, +-- rel_constraints.required_rel_segment +-- from rel_constraints, rel_segment_party_map r +-- where rel_constraints.rel_side = 'two' +-- and rel_constraints.rel_segment = r.segment_id +-- ) constrained_rels, +-- rel_segment_party_map rspm +-- where rspm.segment_id(+) = constrained_rels.required_rel_segment +-- and rspm.party_id(+) = constrained_rels.party_id +-- and rspm.party_id is null; + create view rel_constraints_violated_two as select constrained_rels.* from (select rel_constraints.constraint_id, rel_constraints.constraint_name, @@ -536,16 +611,29 @@ -- and rel_side = :rel_side -- -- +-- create view rc_segment_required_seg_map as +-- select rc.rel_segment, rc.rel_side, rc_required.required_rel_segment +-- from rel_constraints rc, rel_constraints rc_required +-- where rc.rel_segment in ( +-- select rel_segment +-- from rel_constraints +-- start with rel_segment = rc_required.rel_segment +-- connect by required_rel_segment = prior rel_segment +-- and prior rel_side = 'two' +-- ); + create view rc_segment_required_seg_map as select rc.rel_segment, rc.rel_side, rc_required.required_rel_segment from rel_constraints rc, rel_constraints rc_required where rc.rel_segment in ( select rel_segment - from rel_constraints - start with rel_segment = rc_required.rel_segment - connect by required_rel_segment = prior rel_segment - and prior rel_side = 'two' - ); + from rel_constraints + where tree_sortkey + like + (select tree_sortkey || '%' + from rel_constraints + where rel_segment = rc_required.rel_segment + and rel_side = 'two')); -- View: rc_segment_dependency_levels -- @@ -576,14 +664,27 @@ -- example query above (see "Answer:"). I could have embeded that logic into -- this view, but that would unnecessarily degrade performance. -- +-- create view rc_segment_dependency_levels as +-- select rel_segment as segment_id, +-- max(tree_level) as dependency_level +-- from (select rel_segment, level as tree_level +-- from rel_constraints +-- connect by required_rel_segment = prior rel_segment +-- and prior rel_side = 'two') +-- group by rel_segment; + +-- FIXME: need to verify this against acs classic to see if this is correct. +-- It seems correct, but it might be bogus. +-- DCW 2001-03-14. + create view rc_segment_dependency_levels as select rel_segment as segment_id, max(tree_level) as dependency_level - from (select rel_segment, level as tree_level - from rel_constraints - connect by required_rel_segment = prior rel_segment - and prior rel_side = 'two') - group by rel_segment; + from (select rc1.rel_segment, tree_level(rc1.tree_sortkey) as tree_level + from rel_constraints rc1, rel_constraints rc2 + where rc1.tree_sortkey like rc2.tree_sortkey || '%' + and rc2.rel_side = 'two') + group by segment_id; -------------- -- PACKAGES --