Index: openacs-4/packages/acs-kernel/sql/postgresql/test/rel-constraints-test.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/test/rel-constraints-test.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/acs-kernel/sql/postgresql/test/rel-constraints-test.sql 22 Mar 2001 00:21:19 -0000 1.1 +++ openacs-4/packages/acs-kernel/sql/postgresql/test/rel-constraints-test.sql 23 Mar 2001 00:21:50 -0000 1.2 @@ -19,30 +19,41 @@ uname varchar(100) ); +create table groups_test_segs ( + seg_id integer, + sorder integer, + sname varchar(100) +); + create function rel_constraint_dump_views() returns integer as ' +declare r record; str varchar; begin - raise NOTICE ''Contents of view ''rel_constraints_violated_one'':''; + raise NOTICE ''Contents of view rel_constraints_violated_one:''; + str := rpad(''constraint_id'', 20) || rpad(''rel_id'', 20) || + rpad(''name(container_id)'',20) || + rpad(''name(party_id)'',20); + raise NOTICE ''%'', str; for r in select * from rel_constraints_violated_one LOOP - str := rpad(r.constraint_id, 10) || - rpad(r.rel_id, 10) || + str := rpad(r.constraint_id, 20) || + rpad(r.rel_id, 20) || 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'':''; + 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) || + str := rpad(r.constraint_id, 20) || + rpad(r.rel_id, 20) || rpad(acs_object__name(r.container_id), 20) || rpad(acs_object__name(r.party_id), 20); @@ -53,13 +64,13 @@ end;' language 'plpgsql'; -create function rel_constraint_test_check (integer, boolean) +create function rel_constraint_test_check (integer, char) returns integer as ' declare - rel_id alias for $1; + v_rel_id alias for $1; expect_violation_p alias for $2; v_violation_msg varchar(4000); - v_violation_p boolean; + v_violation_p char; v_object_id_one integer; v_object_id_two integer; v_rel_type acs_rels.rel_type%TYPE; @@ -68,22 +79,23 @@ v_violation_p := ''f''; - v_violation_msg := rel_constraint__violation(rel_id); + v_violation_msg := rel_constraint__violation(v_rel_id); if v_violation_msg is not null then v_violation_p := ''t''; end if; - if v_violation_p != expect_violation_p then + if v_violation_p::char != expect_violation_p::char 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; + where rel_id = v_rel_id; - str := ''Relation '' || acs_object_name(rel_id) || - '' ('' || rel_id || '')'' || - '' failed (violation_p = '' || v_violation_p || ''). '' || + str := ''Relation '' || acs_object__name(v_rel_id) || + '' ('' || v_rel_id || '')'' || + '' failed (violation_p = '' || v_violation_p::varchar + || ''). '' || ''Rel info: type = '' || v_rel_type || '', object one = '' || acs_object__name(v_object_id_one) || @@ -99,13 +111,15 @@ raise NOTICE ''Violation Message:''; raise NOTICE ''%'', v_violation_msg; + else + raise NOTICE ''passed %'', v_rel_id; end if; return null; end;' language 'plpgsql'; --- creates blah_member_rel and yippe_member_rel relationships +-- creates blah_member_rel and yippie_member_rel relationships \i rel-segments-test-types-create.sql @@ -130,6 +144,14 @@ reg_users integer; + rel_a integer; + rel_b integer; + rel_c integer; + rel_d integer; + rel_e integer; + rel_f integer; + rel_g integer; + rel_id integer; side_one_constraint integer; @@ -175,6 +197,8 @@ stacy := acs_user__new(''stacy@asdf.com'',''Stacy'', ''Smith'',''assword'',''p''); + reg_users := acs__magic_object_id(''registered_users''); + 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''); @@ -183,10 +207,10 @@ 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''); + insert into groups_test_users values (reg_users,9,''reg_users''); -- 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); @@ -197,17 +221,17 @@ 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'', + ''Yippie: side 2 must be a blah of A'', rel_segment__get_or_new(reg_users, - ''yippe_member_rel'', + ''yippie_member_rel'', null), ''two'', rel_segment__get_or_new(A, @@ -219,13 +243,12 @@ ); - side_one_constraint := rel_constraint__new( null, ''rel_constraint'', - ''Yippe: side 1 must be a component of E'', + ''Yippie: side 1 must be a component of E'', rel_segment__get_or_new(reg_users, - ''yippe_member_rel'', + ''yippie_member_rel'', null), ''one'', rel_segment__get_or_new(E, @@ -236,55 +259,156 @@ null ); + insert into groups_test_segs values (side_two_constraint,1,''side_two_constraint''); + insert into groups_test_segs values (side_one_constraint,2,''side_one_constraint''); 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. + -- a yippie of F. - rel_id := blah_member_rel__new(null, ''blah_member_rel'', A, joe); + rel_a := blah_member_rel__new(null, ''blah_member_rel'', A, joe); - PERFORM rel_constraint_test_check(rel_id, ''f''); + rel_b := yippie_member_rel__new(null, ''yippie_member_rel'', F, joe); - 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); + rel_c := blah_member_rel__new(null, ''blah_member_rel'', B, jane); - PERFORM rel_constraint_test_check(rel_id, ''f''); + rel_d := yippie_member_rel__new(null, ''yippie_member_rel'', F, jane); - 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 + -- G is not a component of F, therefore no one can be a yippie 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); + rel_e := yippie_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 yippie of F. + rel_f := yippie_member_rel__new(null, ''yippie_member_rel'', F, betty); - -- 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); + -- make sven be a regular member of A. Sven cannot be a yippie of F. + rel_id := membership_rel__new(A, sven); + rel_g := yippie_member_rel__new(null, ''yippie_member_rel'', F, sven); - PERFORM rel_constraint_test_check(rel_id, ''t''); + insert into groups_test_segs values (rel_a,3,''a''); + insert into groups_test_segs values (rel_b,4,''b''); + insert into groups_test_segs values (rel_c,5,''c''); + insert into groups_test_segs values (rel_d,6,''d''); + insert into groups_test_segs values (rel_e,7,''e''); + insert into groups_test_segs values (rel_f,8,''f''); + insert into groups_test_segs values (rel_g,9,''g''); - -- 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); + return null; - PERFORM rel_constraint_test_check(rel_id, ''t''); +end;' language 'plpgsql'; +create function check_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_a integer; + rel_b integer; + rel_c integer; + rel_d integer; + rel_e integer; + rel_f integer; + rel_g integer; + + rel_id integer; + + side_one_constraint integer; + side_two_constraint integer; + + v_count integer; + + r record; +begin + select group_id into A from groups_test_groups where gname = ''A''; + select group_id into B from groups_test_groups where gname = ''B''; + select group_id into C from groups_test_groups where gname = ''C''; + select group_id into D from groups_test_groups where gname = ''D''; + select group_id into E from groups_test_groups where gname = ''E''; + select group_id into F from groups_test_groups where gname = ''F''; + select group_id into G from groups_test_groups where gname = ''G''; + + select user_id into joe from groups_test_users where uname = ''joe''; + select user_id into jane from groups_test_users where uname = ''jane''; + select user_id into bob from groups_test_users where uname = ''bob''; + select user_id into betty from groups_test_users where uname = ''betty''; + select user_id into jack from groups_test_users where uname = ''jack''; + select user_id into jill from groups_test_users where uname = ''jill''; + select user_id into sven from groups_test_users where uname = ''sven''; + select user_id into stacy from groups_test_users where uname = ''stacy''; + select user_id into reg_users from groups_test_users where uname = ''reg_users''; + + select seg_id into side_one_constraint + from groups_test_segs + where sname = ''side_one_constraint''; + + select seg_id into side_two_constraint + from groups_test_segs + where sname = ''side_two_constraint''; + + select seg_id into rel_a from groups_test_segs where sname = ''a''; + select seg_id into rel_b from groups_test_segs where sname = ''b''; + select seg_id into rel_c from groups_test_segs where sname = ''c''; + select seg_id into rel_d from groups_test_segs where sname = ''d''; + select seg_id into rel_e from groups_test_segs where sname = ''e''; + select seg_id into rel_f from groups_test_segs where sname = ''f''; + select seg_id into rel_g from groups_test_segs where sname = ''g''; + + -- 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 yippie of F. + + PERFORM rel_constraint_test_check(rel_a, ''f''); + + PERFORM rel_constraint_test_check(rel_b, ''f''); + + -- do constraints respect group hierarchy? If so, this will be legal: + PERFORM rel_constraint_test_check(rel_c, ''f''); + + PERFORM rel_constraint_test_check(rel_d, ''f''); + + -- ILLEGAL MEMBERSHIPS: + + -- G is not a component of F, therefore no one can be a yippie of G + -- This should violated 2 constraints (object one and object two are both + -- invalid). + PERFORM rel_constraint_test_check(rel_e, ''t''); + + -- betty is not a blah of A, therefore she cannot be a yippie of F. + PERFORM rel_constraint_test_check(rel_f, ''t''); + + -- make sven be a regular member of A. Sven cannot be a yippie of F. + PERFORM rel_constraint_test_check(rel_g, ''t''); + -- TEST THE VIEWS (there should be 4 violated constraints, -- 1 side one violation and 3 side two violations. @@ -306,7 +430,6 @@ PERFORM rel_constraint_dump_views(); end if; - -- Remove the constraints PERFORM rel_constraint__delete(side_one_constraint); PERFORM rel_constraint__delete(side_two_constraint); @@ -316,14 +439,14 @@ 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); + for r in select * from yippie_member_rels LOOP + PERFORM yippie_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'')); + PERFORM rel_segment__delete(rel_segment__get(reg_users,''yippie_member_rel'')); -- Remove the test groups. PERFORM acs_group__delete(G); @@ -348,13 +471,16 @@ end;' language 'plpgsql'; - select test_rel_constraints(); +select check_rel_constraints(); + drop function rel_constraint_dump_views(); -drop function rel_constraint_test_check (integer, boolean); +drop function rel_constraint_test_check (integer, char); drop function test_rel_constraints(); +drop function check_rel_constraints(); drop table groups_test_groups; drop table groups_test_users; +drop table groups_test_segs; \i rel-segments-test-types-drop.sql