Index: openacs-4/packages/acs-kernel/acs-kernel.info =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/acs-kernel.info,v diff -u -N -r1.136.2.12 -r1.136.2.13 --- openacs-4/packages/acs-kernel/acs-kernel.info 24 Jun 2016 15:44:37 -0000 1.136.2.12 +++ openacs-4/packages/acs-kernel/acs-kernel.info 1 Jul 2016 04:44:18 -0000 1.136.2.13 @@ -9,15 +9,15 @@ f t - + OpenACS Core Team Routines and data models providing the foundation for OpenACS-based Web services. 2015-10-04 OpenACS The OpenACS kernel contains the core datamodel create and drop scripts for such things as objects, groups, partiies and the supporting PL/SQL and PL/pgSQL procedures. 3 - + Index: openacs-4/packages/acs-kernel/sql/postgresql/postgresql.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/postgresql.sql,v diff -u -N -r1.48.2.3 -r1.48.2.4 --- openacs-4/packages/acs-kernel/sql/postgresql/postgresql.sql 26 May 2016 19:27:04 -0000 1.48.2.3 +++ openacs-4/packages/acs-kernel/sql/postgresql/postgresql.sql 1 Jul 2016 04:44:17 -0000 1.48.2.4 @@ -249,60 +249,37 @@ -- -- procedure get_func_definition/2 -- + CREATE OR REPLACE FUNCTION get_func_definition( fname varchar, args oidvector -) RETURNS text AS $$ +) RETURNS text AS $PROC$ DECLARE - nargs integer default 0; - v_pos integer; v_funcdef text default ''; v_args varchar; - v_one_arg varchar; - v_one_type varchar; v_nargs integer; v_src text; v_rettype varchar; BEGIN - select proargtypes, pronargs, number_src(prosrc), + select pg_get_function_arguments(oid), pronargs, prosrc, -- was number_src(prosrc) (select typname from pg_type where oid = p.prorettype::integer) into v_args, v_nargs, v_src, v_rettype from pg_proc p where proname = fname::name and proargtypes = args; - v_funcdef := v_funcdef || ' -create or replace function ' || fname || '('; + v_funcdef := + E'--\n-- ' || fname || '/' || v_nargs || E'\n--' + || E'\ncreate or replace function ' || fname || E'(\n ' + || replace(v_args, ', ', E',\n ') + || E'\n) returns ' || v_rettype + || E' as $$\n' || v_src || '$$ language plpgsql;'; - v_pos := position(' ' in v_args); - - while nargs < v_nargs loop - nargs := nargs + 1; - if nargs = v_nargs then - v_one_arg := v_args; - v_args := ''; - else - v_one_arg := substr(v_args, 1, v_pos - 1); - v_args := substr(v_args, v_pos + 1); - v_pos := position(' ' in v_args); - end if; - select case when nargs = 1 - then typname - else ',' || typname - end into v_one_type - from pg_type - where oid = v_one_arg::integer; - v_funcdef := v_funcdef || v_one_type; - end loop; - v_funcdef := v_funcdef || ') returns ' || v_rettype || E' as ''\n' || v_src || ''' language ''plpgsql'';'; - return v_funcdef; - END; -$$ LANGUAGE plpgsql stable strict; +$PROC$ LANGUAGE plpgsql stable strict; - -- -- procedure get_func_header/2 -- Index: openacs-4/packages/acs-kernel/sql/postgresql/rel-segments-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/rel-segments-create.sql,v diff -u -N -r1.16 -r1.16.2.1 --- openacs-4/packages/acs-kernel/sql/postgresql/rel-segments-create.sql 15 May 2015 14:26:25 -0000 1.16 +++ openacs-4/packages/acs-kernel/sql/postgresql/rel-segments-create.sql 1 Jul 2016 04:44:17 -0000 1.16.2.1 @@ -170,21 +170,28 @@ constraint party_member_member_fk references parties on delete cascade, - tag integer - constraint party_member_tag_nn - not null, + originating_rel_id integer + constraint party_member_rel_id_fk + references acs_rels + on delete cascade, constraint party_approved_member_map_pk - primary key (party_id, member_id, tag) + primary key (party_id, member_id, originating_rel_id) ); -- Need this to speed referential integrity create index party_member_member_idx on party_approved_member_map(member_id); +create index party_member_party_idx on party_approved_member_map(party_id); +create index party_member_originating_idx on party_approved_member_map(originating_rel_id); -- Helper functions to maintain the materialized party_approved_member_map. +-- +-- Create an "identity relationship" +-- +select acs_object__new(-10, 'relationship') from dual; +insert into acs_rels (rel_id, rel_type, object_id_one, object_id_two) values (-10, 'relationship', 0, 0); --- added select define_function_args('party_approved_member__add_one','party_id,member_id,rel_id'); -- @@ -199,7 +206,7 @@ BEGIN insert into party_approved_member_map - (party_id, member_id, tag) + (party_id, member_id, originating_rel_id) values (p_party_id, p_member_id, p_rel_id); @@ -210,7 +217,6 @@ --- added select define_function_args('party_approved_member__add','party_id,member_id,rel_id,rel_type'); -- @@ -248,7 +254,6 @@ --- added select define_function_args('party_approved_member__remove_one','party_id,member_id,rel_id'); -- @@ -265,7 +270,7 @@ delete from party_approved_member_map where party_id = p_party_id and member_id = p_member_id - and tag = p_rel_id; + and originating_rel_id = p_rel_id; return 1; @@ -275,7 +280,6 @@ --- added select define_function_args('party_approved_member__remove','party_id,member_id,rel_id,rel_type'); -- @@ -320,9 +324,9 @@ BEGIN insert into party_approved_member_map - (party_id, member_id, tag) + (party_id, member_id, originating_rel_id) values - (new.party_id, new.party_id, 0); + (new.party_id, new.party_id, -10); return new; @@ -357,7 +361,7 @@ BEGIN insert into party_approved_member_map - (party_id, member_id, tag) + (party_id, member_id, originating_rel_id) select new.segment_id, element_id, rel_id from group_element_index where group_id = new.group_id Index: openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.9.1d6-5.9.1d7.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.9.1d6-5.9.1d7.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.9.1d6-5.9.1d7.sql 1 Jul 2016 04:44:17 -0000 1.1.2.1 @@ -0,0 +1,156 @@ + +-- +-- Use a better name for attribute "tag" in party_approved_member_map +-- +alter table party_approved_member_map rename tag to originating_rel_id; + +-- +-- Create an "identity relationship" +-- +select acs_object__new(-10, 'relationship') from dual; +insert into acs_rels (rel_id, rel_type, object_id_one, object_id_two) values (-10, 'relationship', 0, 0); + +-- +-- Use the new identity relation instead of value "0" +-- +update party_approved_member_map +set originating_rel_id = -10 +where originating_rel_id = 0; + +-- +-- Make sure, there are no leftovers in the old "tag" attribute, which +-- did not have a foreign key defined +-- +delete from party_approved_member_map +where originating_rel_id in +(select originating_rel_id from party_approved_member_map +except select rel_id from acs_rels); + +-- +-- Add a foreign key +-- +alter table party_approved_member_map +ADD CONSTRAINT party_member_rel_id_fk foreign key (originating_rel_id) +references acs_rels on delete cascade; + +-- speed up referential integrity +create index party_member_party_idx on party_approved_member_map(party_id); +create index party_member_originating_idx on party_approved_member_map(originating_rel_id); + + +-- +-- Redefine the stored procedures/functions referring to the attribute +-- "tag". +-- + +-- +-- procedure party_approved_member__add_one/3 +-- +CREATE OR REPLACE FUNCTION party_approved_member__add_one( + p_party_id integer, + p_member_id integer, + p_rel_id integer +) RETURNS integer AS $$ +DECLARE +BEGIN + + insert into party_approved_member_map + (party_id, member_id, originating_rel_id) + values + (p_party_id, p_member_id, p_rel_id); + + return 1; + +END; +$$ LANGUAGE plpgsql; + +-- +-- procedure party_approved_member__remove_one/3 +-- +CREATE OR REPLACE FUNCTION party_approved_member__remove_one( + p_party_id integer, + p_member_id integer, + p_rel_id integer +) RETURNS integer AS $$ +DECLARE +BEGIN + + delete from party_approved_member_map + where party_id = p_party_id + and member_id = p_member_id + and originating_rel_id = p_rel_id; + + return 1; + +END; +$$ LANGUAGE plpgsql; + + +-- Triggers to maintain party_approved_member_map when parties are created or +-- destroyed. These don't call the above helper functions because we're just +-- creating the identity row for the party. + +CREATE OR REPLACE FUNCTION parties_in_tr () RETURNS trigger AS $$ +BEGIN + + insert into party_approved_member_map + (party_id, member_id, originating_rel_id) + values + (new.party_id, new.party_id, -10); + + return new; + +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION rel_segments_in_tr () RETURNS trigger AS $$ +BEGIN + + insert into party_approved_member_map + (party_id, member_id, originating_rel_id) + select new.segment_id, element_id, rel_id + from group_element_index + where group_id = new.group_id + and rel_type = new.rel_type; + + return new; + +END; +$$ LANGUAGE plpgsql; + +-- +-- Improve get_func_definition() to return SQL function/procedure +-- definitions with argument names and defaults +-- + +-- +-- procedure get_func_definition/2 +-- +CREATE OR REPLACE FUNCTION get_func_definition( + fname varchar, + args oidvector +) RETURNS text AS $PROC$ +DECLARE + v_funcdef text default ''; + v_args varchar; + v_nargs integer; + v_src text; + v_rettype varchar; +BEGIN + select pg_get_function_arguments(oid), pronargs, prosrc, -- was number_src(prosrc) + (select typname from pg_type where oid = p.prorettype::integer) + into v_args, v_nargs, v_src, v_rettype + from pg_proc p + where proname = fname::name + and proargtypes = args; + + v_funcdef := + E'--\n-- ' || fname || '/' || v_nargs || E'\n--' + || E'\ncreate or replace function ' || fname || E'(\n ' + || replace(v_args, ', ', E',\n ') + || E'\n) returns ' || v_rettype + || E' as $$\n' || v_src || '$$ language plpgsql;'; + + return v_funcdef; +END; +$PROC$ LANGUAGE plpgsql stable strict;