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;