-- -- packages/acs-kernel/sql/test/rel-constraints-test.sql -- -- @author oumi@arsdigita.com -- @creation-date 2000-12-02 -- @cvs-id rel-constraints-test.sql,v 1.1.4.1 2001/01/12 23:06:33 oumi Exp -- -- set serveroutput on create table groups_test_groups ( group_id integer, sorder integer, gname varchar(100) ); create table groups_test_users ( user_id integer, sorder integer, uname varchar(100) ); create function rel_constraint_dump_views() returns integer as ' r record; str varchar; begin raise NOTICE ''Contents of view ''rel_constraints_violated_one'':''; for r in select * from rel_constraints_violated_one LOOP str := rpad(r.constraint_id, 10) || rpad(r.rel_id, 10) || rpad(acs_object__name(r.container_id), 20) || rpad(acs_object__name(r.party_id), 20); raise NOTICE ''%'', str; end LOOP; raise NOTICE ''Contents of view ''rel_constraints_violated_two'':''; for r in select * from rel_constraints_violated_two LOOP str := rpad(r.constraint_id, 10) || rpad(r.rel_id, 10) || rpad(acs_object__name(r.container_id), 20) || rpad(acs_object__name(r.party_id), 20); raise NOTICE ''%'', str; end loop; return null; end;' language 'plpgsql'; create function rel_constraint_test_check (integer, boolean) returns integer as ' declare rel_id alias for $1; expect_violation_p alias for $2; v_violation_msg varchar(4000); v_violation_p boolean; v_object_id_one integer; v_object_id_two integer; v_rel_type acs_rels.rel_type%TYPE; str varchar; begin v_violation_p := ''f''; v_violation_msg := rel_constraint__violation(rel_id); if v_violation_msg is not null then v_violation_p := ''t''; end if; if v_violation_p != expect_violation_p then select object_id_one, object_id_two, rel_type into v_object_id_one, v_object_id_two, v_rel_type from acs_rels where rel_id = rel_constraint_test_check.rel_id; str := ''Relation '' || acs_object_name(rel_id) || '' ('' || rel_id || '')'' || '' failed (violation_p = '' || v_violation_p || ''). '' || ''Rel info: type = '' || v_rel_type || '', object one = '' || acs_object__name(v_object_id_one) || '' ('' || v_object_id_one || '')'' || '', object two = '' || acs_object__name(v_object_id_two) || '' ('' || v_object_id_two || '').''; PERFORM acs_log__error(''rel_constraint_test_check'', str); raise NOTICE ''%'', str; raise NOTICE ''Violation Message:''; raise NOTICE ''%'', v_violation_msg; end if; return null; end;' language 'plpgsql'; -- creates blah_member_rel and yippe_member_rel relationships \i rel-segments-test-types-create.sql create function test_rel_constraints() returns integer as ' declare A integer; B integer; C integer; D integer; E integer; F integer; G integer; joe integer; jane integer; bob integer; betty integer; jack integer; jill integer; sven integer; stacy integer; reg_users integer; rel_id integer; side_one_constraint integer; side_two_constraint integer; v_count integer; r record; begin -- Create the test groups. A := acs_group__new(''A''); B := acs_group__new(''B''); C := acs_group__new(''C''); D := acs_group__new(''D''); E := acs_group__new(''E''); F := acs_group__new(''F''); G := acs_group__new(''G''); insert into groups_test_groups values (A,1,''A''); insert into groups_test_groups values (B,2,''B''); insert into groups_test_groups values (C,3,''C''); insert into groups_test_groups values (D,4,''D''); insert into groups_test_groups values (E,5,''E''); insert into groups_test_groups values (F,6,''F''); insert into groups_test_groups values (G,7,''G''); -- Create the test members. joe := acs_user__new(''joe@asdf.com'',''Joe'', ''Smith'',''assword'',''p''); jane := acs_user__new(''jane@asdf.com'',''Jane'', ''Smith'',''assword'',''p''); bob := acs_user__new(''bob@asdf.com'',''Bob'', ''Smith'',''assword'',''p''); betty := acs_user__new(''betty@asdf.com'',''Betty'', ''Smith'',''assword'',''p''); jack := acs_user__new(''jack@asdf.com'',''Jack'', ''Smith'',''assword'',''p''); jill := acs_user__new(''jill@asdf.com'',''Jill'', ''Smith'',''assword'',''p''); sven := acs_user__new(''sven@asdf.com'',''Sven'', ''Smith'',''assword'',''p''); stacy := acs_user__new(''stacy@asdf.com'',''Stacy'', ''Smith'',''assword'',''p''); insert into groups_test_users values (joe,1,''joe''); insert into groups_test_users values (jane,2,''jane''); insert into groups_test_users values (bob,3,''bob''); insert into groups_test_users values (betty,4,''betty''); insert into groups_test_users values (jack,5,''jack''); insert into groups_test_users values (jill,6,''jill''); insert into groups_test_users values (sven,7,''sven''); insert into groups_test_users values (stacy,8,''stacy''); -- Make a couple of compositions. reg_users := acs__magic_object_id(''registered_users''); rel_id := composition_rel__new(A, B); rel_id := composition_rel__new(A, C); rel_id := composition_rel__new(A, D); rel_id := composition_rel__new(E, A); rel_id := composition_rel__new(E, F); rel_id := composition_rel__new(reg_users, E); rel_id := composition_rel__new(reg_users, G); -- define a few segments. -- define a few relational constraints. side_two_constraint := rel_constraint__new( null, ''rel_constraint'', ''Yippe: side 2 must be a blah of A'', rel_segment__get_or_new(reg_users, ''yippe_member_rel'', null), ''two'', rel_segment__get_or_new(A, ''blah_member_rel'', null), null, null, null ); side_one_constraint := rel_constraint__new( null, ''rel_constraint'', ''Yippe: side 1 must be a component of E'', rel_segment__get_or_new(reg_users, ''yippe_member_rel'', null), ''one'', rel_segment__get_or_new(E, ''composition_rel'', null), null, null, null ); delete from acs_logs; -- Make a couple of memberships. -- LEGAL MEMBERSHIPS: -- textbook case: -- joe is a blah of A, and F is component of E, so its legal to make joe -- a yippe of F. rel_id := blah_member_rel__new(null, ''blah_member_rel'', A, joe); PERFORM rel_constraint_test_check(rel_id, ''f''); rel_id := yippe_member_rel__new(null, ''yippie_member_rel'', F, joe); PERFORM rel_constraint_test_check(rel_id, ''f''); -- do constraints respect group hierarchy? If so, this will be legal: rel_id := blah_member_rel__new(null, ''blah_member_rel'', B, jane); PERFORM rel_constraint_test_check(rel_id, ''f''); rel_id := yippe_member_rel__new(null, ''yippie_member_rel'', F, jane); PERFORM rel_constraint_test_check(rel_id, ''f''); -- ILLEGAL MEMBERSHIPS: -- G is not a component of F, therefore no one can be a yippe of G -- This should violated 2 constraints (object one and object two are both -- invalid). rel_id := yippe_member_rel__new(null, ''yippie_member_rel'', G, bob); PERFORM rel_constraint_test_check(rel_id, ''t''); -- betty is not a blah of A, therefore she cannot be a yippe of F. rel_id := yippe_member_rel__new(null, ''yippie_member_rel'', F, betty); PERFORM rel_constraint_test_check(rel_id, ''t''); -- make sven be a regular member of A. Sven cannot be a yippe of F. rel_id := membership_rel__new(A, sven); rel_id := yippe_member_rel__new(null, ''yippie_member_rel'', F, sven); PERFORM rel_constraint_test_check(rel_id, ''t''); -- TEST THE VIEWS (there should be 4 violated constraints, -- 1 side one violation and 3 side two violations. select count(*) into v_count from rel_constraints_violated_one; if v_count != 1 then raise NOTICE ''rel_constraints_violated_one should have 1 row. Found % rows.'', v_count; PERFORM rel_constraint_dump_views(); end if; select count(*) into v_count from rel_constraints_violated_two; if v_count != 3 then raise NOTICE ''rel_constraints_violated_two should have 2 rows. Found % rows.'', v_count; PERFORM rel_constraint_dump_views(); end if; -- Remove the constraints PERFORM rel_constraint__delete(side_one_constraint); PERFORM rel_constraint__delete(side_two_constraint); -- Remove the test memebership relations for r in select * from blah_member_rels LOOP PERFORM blah_member_rel__delete(r.rel_id); end loop; for r in select * from yippe_member_rels LOOP PERFORM yippe_member_rel__delete(r.rel_id); end loop; -- Remove the test segments. PERFORM rel_segment__delete(rel_segment__get(A,''blah_member_rel'')); PERFORM rel_segment__delete(rel_segment__get(E,''composition_rel'')); PERFORM rel_segment__delete(rel_segment__get(reg_users,''yippe_member_rel'')); -- Remove the test groups. PERFORM acs_group__delete(G); PERFORM acs_group__delete(F); PERFORM acs_group__delete(E); PERFORM acs_group__delete(D); PERFORM acs_group__delete(C); PERFORM acs_group__delete(B); PERFORM acs_group__delete(A); -- Remove the test members. PERFORM acs_user__delete(joe); PERFORM acs_user__delete(jane); PERFORM acs_user__delete(bob); PERFORM acs_user__delete(betty); PERFORM acs_user__delete(jack); PERFORM acs_user__delete(jill); PERFORM acs_user__delete(sven); PERFORM acs_user__delete(stacy); return null; end;' language 'plpgsql'; select test_rel_constraints(); drop function rel_constraint_dump_views(); drop function rel_constraint_test_check (integer, boolean); drop function test_rel_constraints(); drop table groups_test_groups; drop table groups_test_users; \i rel-segments-test-types-drop.sql select log_level, log_key, message from acs_logs where log_key = 'error';