Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql,v diff -u -N -r1.10 -r1.11 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql 9 Apr 2001 04:55:14 -0000 1.10 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql 17 Apr 2001 04:10:06 -0000 1.11 @@ -128,7 +128,7 @@ child_type varchar(100) constraint cr_type_children_child_fk references acs_object_types, - relation_tag varchar(100) default '' not null, + relation_tag varchar(100), min_n integer, max_n integer, constraint cr_type_children_pk @@ -147,7 +147,7 @@ target_type varchar(100) constraint cr_type_relations_child_fk references acs_object_types, - relation_tag varchar(100) default '' not null, + relation_tag varchar(100), min_n integer, max_n integer, constraint cr_type_relations_pk Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-extlink.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-extlink.sql,v diff -u -N -r1.3 -r1.4 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-extlink.sql 2 Apr 2001 05:35:29 -0000 1.3 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-extlink.sql 17 Apr 2001 04:10:06 -0000 1.4 @@ -29,13 +29,13 @@ v_name cr_items.name%TYPE; begin - if new__label is null then + if new__label is null or new__label = '''' then v_label := new__url; else v_label := new__label; end if; - if new__name is null then + if new__name is null or new__name = '''' then select acs_object_id_seq.nextval into v_extlink_id from dual; v_name := ''link'' || v_extlink_id; else Index: openacs-4/packages/acs-subsite/sql/postgresql/acs-subsite-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/sql/postgresql/acs-subsite-create.sql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/sql/postgresql/acs-subsite-create.sql 12 Apr 2001 04:00:44 -0000 1.1 +++ openacs-4/packages/acs-subsite/sql/postgresql/acs-subsite-create.sql 17 Apr 2001 04:10:06 -0000 1.2 @@ -7,23 +7,41 @@ -- @cvs-id $Id$ -- -@@ attribute -@@ portraits -@@ application-groups-create -@@ subsite-callbacks-create +\i attribute.sql +\i portraits.sql +\i application-groups-create.sql +\i user-profiles-create.sql +\i subsite-callbacks-create.sql -- This view lets us avoid using acs_object.name to get party_names. -- -create or replace view party_names +-- create or replace view party_names +-- as +-- select p.party_id, +-- decode(groups.group_id, +-- null, decode(persons.person_id, +-- null, p.email, +-- persons.first_names || ' ' || persons.last_name), +-- groups.group_name) as party_name +-- from parties p, +-- groups, +-- persons +-- where p.party_id = groups.group_id(+) +-- and p.party_id = persons.person_id(+); + +create view party_names as select p.party_id, - decode(groups.group_id, - null, decode(persons.person_id, - null, p.email, - persons.first_names || ' ' || persons.last_name), - groups.group_name) as party_name -from parties p, - groups, - persons -where p.party_id = groups.group_id(+) - and p.party_id = persons.person_id(+); + (case + when groups.group_id is null then + (case + when persons.person_id is null then + p.email + else + persons.first_names || ' ' || persons.last_name + end) + else + groups.group_name + end) as party_name +from ((parties p left outer join groups on p.party_id = groups.group_id) + left outer join persons on p.party_id = persons.person_id); Index: openacs-4/packages/acs-subsite/sql/postgresql/acs-subsite-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/sql/postgresql/acs-subsite-drop.sql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/sql/postgresql/acs-subsite-drop.sql 12 Apr 2001 04:00:44 -0000 1.1 +++ openacs-4/packages/acs-subsite/sql/postgresql/acs-subsite-drop.sql 17 Apr 2001 04:10:06 -0000 1.2 @@ -5,8 +5,10 @@ -- @creation-date (Sat Aug 26 17:56:07 2000) -- @cvs-id $Id$ -@@ subsite-group-callbacks-drop -@@ application-groups-drop -@@ user-profiles-drop -@@ attributes-drop -@@ portraits-drop +\i subsite-callbacks-drop.sql +\i user-profiles-drop.sql +\i application-groups-drop.sql +\i portraits-drop.sql +\i attributes-drop.sql + +drop view party_names; Index: openacs-4/packages/acs-subsite/sql/postgresql/application-groups-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/sql/postgresql/application-groups-create.sql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/sql/postgresql/application-groups-create.sql 12 Apr 2001 04:00:44 -0000 1.1 +++ openacs-4/packages/acs-subsite/sql/postgresql/application-groups-create.sql 17 Apr 2001 04:10:06 -0000 1.2 @@ -10,148 +10,236 @@ -- APPLICATION GROUPS -- ------------------------ -begin - acs_object_type.create_type ( - supertype => 'group', - object_type => 'application_group', - pretty_name => 'Application Group', - pretty_plural => 'Application Groups', - table_name => 'application_groups', - id_column => 'group_id', - package_name => 'application_group', - type_extension_table => 'group_types', - name_method => 'acs_group.name' - ); -end; -/ -show errors +-- begin +-- acs_object_type.create_type ( +-- supertype => 'group', +-- object_type => 'application_group', +-- pretty_name => 'Application Group', +-- pretty_plural => 'Application Groups', +-- table_name => 'application_groups', +-- id_column => 'group_id', +-- package_name => 'application_group', +-- type_extension_table => 'group_types', +-- name_method => 'acs_group.name' +-- ); +-- end; +-- / +-- show errors +select acs_object_type__create_type ( + 'application_group', + 'Application Group', + 'Application Groups', + 'group', + 'application_groups', + 'group_id', + 'application_group', + 'f', + 'group_types', + 'acs_group__name' +); + + + create table application_groups ( - group_id constraint app_groups_group_id_fk + group_id integer constraint app_groups_group_id_fk references groups (group_id) constraint app_groups_group_id_pk primary key, - package_id constraint app_groups_package_id_fk + package_id integer constraint app_groups_package_id_fk references apm_packages, constraint app_groups_package_id_un unique (package_id) ); -create or replace package application_group -is +-- create or replace package application_group +-- is - function new ( - group_id in application_groups.group_id%TYPE default null, - object_type in acs_objects.object_type%TYPE - default 'application_group', - creation_date in acs_objects.creation_date%TYPE - default sysdate, - creation_user in acs_objects.creation_user%TYPE - default null, - creation_ip in acs_objects.creation_ip%TYPE default null, - email in parties.email%TYPE default null, - url in parties.url%TYPE default null, - group_name in groups.group_name%TYPE, - package_id in application_groups.package_id%TYPE, - context_id in acs_objects.context_id%TYPE default null - ) return application_groups.group_id%TYPE; +-- function new ( +-- group_id in application_groups.group_id%TYPE default null, +-- object_type in acs_objects.object_type%TYPE +-- default 'application_group', +-- creation_date in acs_objects.creation_date%TYPE +-- default sysdate, +-- creation_user in acs_objects.creation_user%TYPE +-- default null, +-- creation_ip in acs_objects.creation_ip%TYPE default null, +-- email in parties.email%TYPE default null, +-- url in parties.url%TYPE default null, +-- group_name in groups.group_name%TYPE, +-- package_id in application_groups.package_id%TYPE, +-- context_id in acs_objects.context_id%TYPE default null +-- ) return application_groups.group_id%TYPE; - procedure delete ( - group_id in application_groups.group_id%TYPE - ); +-- procedure delete ( +-- group_id in application_groups.group_id%TYPE +-- ); - function group_id_from_package_id ( - package_id in application_groups.group_id%TYPE, - no_complain_p in char default 'f' - ) return char; +-- function group_id_from_package_id ( +-- package_id in application_groups.group_id%TYPE, +-- no_complain_p in char default 'f' +-- ) return char; -end application_group; -/ -show errors +-- end application_group; +-- / +-- show errors -create or replace package body application_group -is +-- create or replace package body application_group +-- is - function new ( - group_id in application_groups.group_id%TYPE default null, - object_type in acs_objects.object_type%TYPE - default 'application_group', - creation_date in acs_objects.creation_date%TYPE - default sysdate, - creation_user in acs_objects.creation_user%TYPE - default null, - creation_ip in acs_objects.creation_ip%TYPE default null, - email in parties.email%TYPE default null, - url in parties.url%TYPE default null, - group_name in groups.group_name%TYPE, - package_id in application_groups.package_id%TYPE, - context_id in acs_objects.context_id%TYPE default null - ) - return application_groups.group_id%TYPE - is - v_group_id application_groups.group_id%TYPE; - begin - v_group_id := acs_group.new ( - group_id => group_id, - object_type => object_type, - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip, - email => email, - url => url, - group_name => group_name, - context_id => context_id - ); +-- function new ( +-- group_id in application_groups.group_id%TYPE default null, +-- object_type in acs_objects.object_type%TYPE +-- default 'application_group', +-- creation_date in acs_objects.creation_date%TYPE +-- default sysdate, +-- creation_user in acs_objects.creation_user%TYPE +-- default null, +-- creation_ip in acs_objects.creation_ip%TYPE default null, +-- email in parties.email%TYPE default null, +-- url in parties.url%TYPE default null, +-- group_name in groups.group_name%TYPE, +-- package_id in application_groups.package_id%TYPE, +-- context_id in acs_objects.context_id%TYPE default null +-- ) +-- return application_groups.group_id%TYPE +-- is +-- v_group_id application_groups.group_id%TYPE; +-- begin +-- v_group_id := acs_group.new ( +-- group_id => group_id, +-- object_type => object_type, +-- creation_date => creation_date, +-- creation_user => creation_user, +-- creation_ip => creation_ip, +-- email => email, +-- url => url, +-- group_name => group_name, +-- context_id => context_id +-- ); +-- insert into application_groups (group_id, package_id) +-- values (v_group_id, package_id); + +-- return v_group_id; +-- end new; + +create function application_group__new(integer,varchar,timestamp,integer,varchar,varchar,varchar,varchar,integer,integer) +returns integer as ' +declare + new__group_id alias for $1; + new__object_type alias for $2; -- default ''application_group'', + new__creation_date alias for $3; -- default sysdate, + new__creation_user alias for $4; -- default null, + new__creation_ip alias for $5; -- default null, + new__email alias for $6; -- default null, + new__url alias for $7; -- default null, + new__group_name alias for $8; + new__package_id alias for $9; + new__context_id alias for $10; -- default null + v_group_id application_groups.group_id%TYPE; +begin + v_group_id := acs_group__new ( + new__group_id, + new__object_type, + new__creation_date, + new__creation_user, + new__creation_ip, + new__email, + new__url, + new__group_name, + null, + new__context_id + ); + insert into application_groups (group_id, package_id) - values (v_group_id, package_id); + values (v_group_id, new__package_id); return v_group_id; - end new; +end;' language 'plpgsql'; - procedure delete ( - group_id in application_groups.group_id%TYPE - ) - is - begin +-- procedure delete ( +-- group_id in application_groups.group_id%TYPE +-- ) +-- is +-- begin - acs_group.delete(group_id); +-- acs_group.delete(group_id); - end delete; +-- end delete; - function group_id_from_package_id ( - package_id in application_groups.group_id%TYPE, - no_complain_p in char default 'f' - ) return char - is - v_group_id application_groups.group_id%TYPE; - begin +create function application_group__delete(integer) +returns integer as ' +declare + group_id alias for $1; +begin + PERFORM acs_group__delete(group_id); - select group_id - into v_group_id - from application_groups - where package_id = group_id_from_package_id.package_id; + return 0; +end;' language 'plpgsql'; - return v_group_id; +-- function group_id_from_package_id ( +-- package_id in application_groups.group_id%TYPE, +-- no_complain_p in char default 'f' +-- ) return char +-- is +-- v_group_id application_groups.group_id%TYPE; +-- begin - exception when no_data_found then +-- select group_id +-- into v_group_id +-- from application_groups +-- where package_id = group_id_from_package_id.package_id; - if no_complain_p != 't' then - raise_application_error(-20000, 'No group_id found for package ' || - package_id || ' (' || acs_object.name(package_id) || ').' ); - end if; +-- return v_group_id; - return null; +-- exception when no_data_found then - end group_id_from_package_id; +-- if no_complain_p != 't' then +-- raise_application_error(-20000, 'No group_id found for package ' || +-- package_id || ' (' || acs_object.name(package_id) || ').' ); +-- end if; -end application_group; -/ -show errors +-- return null; +-- end group_id_from_package_id; + +create function application_group__group_id_from_package_id(integer,boolean) +returns integer as ' +declare + group_id_from_package_id__package_id alias for $1; + group_id_from_package_id__no_complain_p alias for $2; -- default ''f'' + v_group_id application_groups.group_id%TYPE; + v_object_name varchar; +begin + + select group_id + into v_group_id + from application_groups + where package_id = group_id_from_package_id__package_id; + +-- TODO: does this shortcut the exception in Oracle? +-- return v_group_id; + + if not found then + if group_id_from_package_id__no_complain_p != ''t'' then + v_object_name := acs_object__name(group_id_from_package_id__package_id); + raise EXCEPTION ''-20000: No group_id found for package % (%)'', group_id_from_package_id__package_id, v_object_name; + end if; + return null; + else + return v_group_id; + end if; + +end;' language 'plpgsql'; + +-- end application_group; +-- / +-- show errors + insert into group_type_rels (group_rel_type_id, group_type, rel_type) values @@ -166,22 +254,50 @@ -- Views -- ----------- -create or replace view application_group_element_map as +-- create or replace view application_group_element_map as +-- select g.package_id, g.group_id, +-- m.element_id, m.container_id, m.rel_id, m.rel_type, m.ancestor_rel_type +-- from application_groups g, +-- group_element_map m +-- where g.group_id = m.group_id; + +create view application_group_element_map as select g.package_id, g.group_id, m.element_id, m.container_id, m.rel_id, m.rel_type, m.ancestor_rel_type from application_groups g, group_element_map m where g.group_id = m.group_id; -create or replace view app_group_distinct_element_map as +-- create or replace view app_group_distinct_element_map as +-- select distinct package_id, group_id, element_id +-- from application_group_element_map; + +create view app_group_distinct_element_map as select distinct package_id, group_id, element_id from application_group_element_map; -create or replace view app_group_distinct_rel_map as +-- create or replace view app_group_distinct_rel_map as +-- select distinct package_id, group_id, rel_id, rel_type, ancestor_rel_type +-- from application_group_element_map; + +create view app_group_distinct_rel_map as select distinct package_id, group_id, rel_id, rel_type, ancestor_rel_type from application_group_element_map; -create or replace view application_group_segments as +-- create or replace view application_group_segments as +-- select g.package_id, s.segment_id, s.group_id, s.rel_type, s.segment_name +-- from application_groups g, +-- group_element_map m, +-- rel_segments s +-- where g.group_id = m.group_id +-- and m.element_id = s.group_id +-- UNION ALL +-- select g.package_id, s.segment_id, s.group_id, s.rel_type, s.segment_name +-- from application_groups g, +-- rel_segments s +-- where g.group_id = s.group_id; + +create view application_group_segments as select g.package_id, s.segment_id, s.group_id, s.rel_type, s.segment_name from application_groups g, group_element_map m, Index: openacs-4/packages/acs-subsite/sql/postgresql/application-groups-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/sql/postgresql/application-groups-drop.sql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/sql/postgresql/application-groups-drop.sql 12 Apr 2001 04:00:44 -0000 1.1 +++ openacs-4/packages/acs-subsite/sql/postgresql/application-groups-drop.sql 17 Apr 2001 04:10:06 -0000 1.2 @@ -8,16 +8,12 @@ delete from group_type_rels where rel_type = 'application_group'; -drop table application_groups; -drop package application_group; +drop view application_group_segments; +drop view app_group_distinct_rel_map; +drop view app_group_distinct_element_map; +drop view application_group_element_map; -begin - acs_object_type.drop_type('application_group'); -end; -/ -show errors +select drop_package('application_group'); +select acs_object_type__drop_type('application_group', 'f'); -drop view application_group_element_map; -drop view application_users; -drop view registered_users_for_package_id; -drop view cc_users_for_package_id; \ No newline at end of file +drop table application_groups; Index: openacs-4/packages/acs-subsite/sql/postgresql/attribute.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/sql/postgresql/Attic/attribute.sql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/sql/postgresql/attribute.sql 12 Apr 2001 04:00:44 -0000 1.1 +++ openacs-4/packages/acs-subsite/sql/postgresql/attribute.sql 17 Apr 2001 04:10:06 -0000 1.2 @@ -10,21 +10,37 @@ -- @cvs-id $Id$ -- -declare - result varchar2(10); -begin - result := acs_attribute.create_attribute ( - object_type => 'person', - attribute_name => 'bio', - datatype => 'string', - pretty_name => 'Biography', - pretty_plural => 'Biographies', - min_n_values => 0, - max_n_values => 1, - storage => 'generic' - ); +-- declare +-- result varchar2(10); +-- begin +-- result := acs_attribute.create_attribute ( +-- object_type => 'person', +-- attribute_name => 'bio', +-- datatype => 'string', +-- pretty_name => 'Biography', +-- pretty_plural => 'Biographies', +-- min_n_values => 0, +-- max_n_values => 1, +-- storage => 'generic' +-- ); - commit; -end; -/ -show errors +-- commit; +-- end; +-- / +-- show errors + +select acs_attribute__create_attribute ( + 'person', + 'bio', + 'string', + 'Biography', + 'Biographies', + null, + null, + null, + 0, + 1, + null, + 'generic', + 'f' +); Index: openacs-4/packages/acs-subsite/sql/postgresql/attributes-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/sql/postgresql/Attic/attributes-drop.sql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/sql/postgresql/attributes-drop.sql 12 Apr 2001 04:00:44 -0000 1.1 +++ openacs-4/packages/acs-subsite/sql/postgresql/attributes-drop.sql 17 Apr 2001 04:10:06 -0000 1.2 @@ -7,21 +7,4 @@ -- -- -declare - result varchar2(10); -begin - result := acs_attribute.create_attribute ( - object_type => 'person', - attribute_name => 'bio', - datatype => 'string', - pretty_name => 'Biography', - pretty_plural => 'Biographies', - min_n_values => 0, - max_n_values => 1, - storage => 'generic' - ); - - commit; -end; -/ -show errors +select acs_attribute__drop_attribute('person','bio'); Index: openacs-4/packages/acs-subsite/sql/postgresql/portraits-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/sql/postgresql/portraits-drop.sql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/sql/postgresql/portraits-drop.sql 12 Apr 2001 04:00:44 -0000 1.1 +++ openacs-4/packages/acs-subsite/sql/postgresql/portraits-drop.sql 17 Apr 2001 04:10:06 -0000 1.2 @@ -6,11 +6,8 @@ -- @cvs-id $Id$ -- -drop table user_portraits; -drop package user_portrait_rel; +select acs_rel_type__drop_type('user_portrait_rel', 'f'); +select acs_rel_type__drop_role('portrait'); +select acs_rel_type__drop_role('user'); -begin - acs_rel_type.drop_type('user_portrait_rel'); -end; -/ -show errors +drop table user_portraits; Index: openacs-4/packages/acs-subsite/sql/postgresql/portraits.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/sql/postgresql/portraits.sql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/sql/postgresql/portraits.sql 12 Apr 2001 04:00:44 -0000 1.1 +++ openacs-4/packages/acs-subsite/sql/postgresql/portraits.sql 17 Apr 2001 04:10:06 -0000 1.2 @@ -6,33 +6,64 @@ -- $Id$ create table user_portraits ( - user_id constraint user_portraits_user_id_fk + user_id integer constraint user_portraits_user_id_fk references users constraint user_portraits_pk primary key ); +-- begin +-- acs_rel_type.create_role('user', 'User', 'Users'); +-- acs_rel_type.create_role('portrait', 'Portrait', 'Portraits'); + +-- acs_rel_type.create_type ( +-- rel_type => 'user_portrait_rel', +-- pretty_name => 'User Portrait', +-- pretty_plural => 'User Portraits', +-- object_type_one => 'user', +-- role_one => 'user', +-- table_name => 'user_portraits', +-- id_column => 'user_id', +-- package_name => 'user_portrait_rel', +-- min_n_rels_one => 1, +-- max_n_rels_one => 1, +-- object_type_two => 'content_item', +-- min_n_rels_two => 0, +-- max_n_rels_two => 1 +-- ); + +-- commit; +-- end; +-- / +-- show errors + +create function inline_0 () +returns integer as ' begin - acs_rel_type.create_role('user', 'User', 'Users'); - acs_rel_type.create_role('portrait', 'Portrait', 'Portraits'); + PERFORM acs_rel_type__create_role(''user'', ''User'', ''Users''); + PERFORM acs_rel_type__create_role(''portrait'', ''Portrait'', ''Portraits''); - acs_rel_type.create_type ( - rel_type => 'user_portrait_rel', - pretty_name => 'User Portrait', - pretty_plural => 'User Portraits', - object_type_one => 'user', - role_one => 'user', - table_name => 'user_portraits', - id_column => 'user_id', - package_name => 'user_portrait_rel', - min_n_rels_one => 1, - max_n_rels_one => 1, - object_type_two => 'content_item', - min_n_rels_two => 0, - max_n_rels_two => 1 + PERFORM acs_rel_type__create_type ( + ''user_portrait_rel'', + ''User Portrait'', + ''User Portraits'', + ''relationship'', + ''user_portraits'', + ''user_id'', + ''user_portrait_rel'', + ''user'', + ''user'', + 1, + 1, + ''content_item'', + null, + 0, + 1 ); - commit; -end; -/ -show errors + return 0; +end;' language 'plpgsql'; + +select inline_0 (); + +drop function inline_0 (); Index: openacs-4/packages/acs-subsite/sql/postgresql/subsite-callbacks-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/sql/postgresql/subsite-callbacks-create.sql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/sql/postgresql/subsite-callbacks-create.sql 12 Apr 2001 04:00:44 -0000 1.1 +++ openacs-4/packages/acs-subsite/sql/postgresql/subsite-callbacks-create.sql 17 Apr 2001 04:10:06 -0000 1.2 @@ -79,100 +79,157 @@ '; -create or replace package subsite_callback as +-- create or replace package subsite_callback as - function new ( - --/** Registers a new callback. If the same callback exists as - -- defined in the unique constraint on the table, does - -- nothing but returns the existing callback_id. - -- - -- @author Michael Bryzek (mbryzek@arsdigita.com) - -- @creation-date 2001-02-20 - -- - --*/ - callback_id IN subsite_callbacks.callback_id%TYPE default null, - event_type IN subsite_callbacks.event_type%TYPE, - object_type IN subsite_callbacks.object_type%TYPE, - callback IN subsite_callbacks.callback%TYPE, - callback_type IN subsite_callbacks.callback_type%TYPE, - sort_order IN subsite_callbacks.sort_order%TYPE default null - ) return subsite_callbacks.callback_id%TYPE; +-- function new ( +-- --/** Registers a new callback. If the same callback exists as +-- -- defined in the unique constraint on the table, does +-- -- nothing but returns the existing callback_id. +-- -- +-- -- @author Michael Bryzek (mbryzek@arsdigita.com) +-- -- @creation-date 2001-02-20 +-- -- +-- --*/ +-- callback_id IN subsite_callbacks.callback_id%TYPE default null, +-- event_type IN subsite_callbacks.event_type%TYPE, +-- object_type IN subsite_callbacks.object_type%TYPE, +-- callback IN subsite_callbacks.callback%TYPE, +-- callback_type IN subsite_callbacks.callback_type%TYPE, +-- sort_order IN subsite_callbacks.sort_order%TYPE default null +-- ) return subsite_callbacks.callback_id%TYPE; - procedure delete ( - --/** Deletes the specified callback - -- - -- @author Michael Bryzek (mbryzek@arsdigita.com) - -- @creation-date 2001-02-20 - -- - --*/ +-- procedure delete ( +-- --/** Deletes the specified callback +-- -- +-- -- @author Michael Bryzek (mbryzek@arsdigita.com) +-- -- @creation-date 2001-02-20 +-- -- +-- --*/ - callback_id IN subsite_callbacks.callback_id%TYPE - ); +-- callback_id IN subsite_callbacks.callback_id%TYPE +-- ); -end subsite_callback; -/ -show errors; +-- end subsite_callback; +-- / +-- show errors; -create or replace package body subsite_callback as +-- create or replace package body subsite_callback as - function new ( - callback_id IN subsite_callbacks.callback_id%TYPE default null, - event_type IN subsite_callbacks.event_type%TYPE, - object_type IN subsite_callbacks.object_type%TYPE, - callback IN subsite_callbacks.callback%TYPE, - callback_type IN subsite_callbacks.callback_type%TYPE, - sort_order IN subsite_callbacks.sort_order%TYPE default null - ) return subsite_callbacks.callback_id%TYPE - IS - v_callback_id subsite_callbacks.callback_id%TYPE; - v_sort_order subsite_callbacks.sort_order%TYPE; - BEGIN +-- function new ( +-- callback_id IN subsite_callbacks.callback_id%TYPE default null, +-- event_type IN subsite_callbacks.event_type%TYPE, +-- object_type IN subsite_callbacks.object_type%TYPE, +-- callback IN subsite_callbacks.callback%TYPE, +-- callback_type IN subsite_callbacks.callback_type%TYPE, +-- sort_order IN subsite_callbacks.sort_order%TYPE default null +-- ) return subsite_callbacks.callback_id%TYPE +-- IS +-- v_callback_id subsite_callbacks.callback_id%TYPE; +-- v_sort_order subsite_callbacks.sort_order%TYPE; +-- BEGIN - if new.callback_id is null then - select acs_object_id_seq.nextval into v_callback_id from dual; +-- if new.callback_id is null then +-- select acs_object_id_seq.nextval into v_callback_id from dual; +-- else +-- v_callback_id := new.callback_id; +-- end if; + +-- if new.sort_order is null then +-- -- Make this the next event for this object_type/event_type combination +-- select nvl(max(sort_order),0) + 1 into v_sort_order +-- from subsite_callbacks +-- where object_type = new.object_type +-- and event_type = new.event_type; +-- else +-- v_sort_order := new.sort_order; +-- end if; + +-- begin +-- insert into subsite_callbacks +-- (callback_id, event_type, object_type, callback, callback_type, sort_order) +-- values +-- (v_callback_id, new.event_type, new.object_type, new.callback, new.callback_type, v_sort_order); +-- exception when dup_val_on_index then +-- select callback_id into v_callback_id +-- from subsite_callbacks +-- where event_type = new.event_type +-- and object_type = new.object_type +-- and callback_type = new.callback_type +-- and callback = new.callback; +-- end; +-- return v_callback_id; + +-- END new; + +create function subsite_callback__new(integer,varchar,varchar,varchar,varchar,integer) +returns integer as ' +declare + new__callback_id alias for $1; -- default null, + new__event_type alias for $2; + new__object_type alias for $3; + new__callback alias for $4; + new__callback_type alias for $5; + new__sort_order alias for $6; -- default null + v_callback_id subsite_callbacks.callback_id%TYPE; + v_sort_order subsite_callbacks.sort_order%TYPE; +begin + + if new__callback_id is null then + select acs_object_id_seq.nextval into v_callback_id; else - v_callback_id := new.callback_id; + v_callback_id := new__callback_id; end if; - if new.sort_order is null then + if new__sort_order is null then -- Make this the next event for this object_type/event_type combination - select nvl(max(sort_order),0) + 1 into v_sort_order + select coalesce(max(sort_order),0) + 1 into v_sort_order from subsite_callbacks - where object_type = new.object_type - and event_type = new.event_type; + where object_type = new__object_type + and event_type = new__event_type; else - v_sort_order := new.sort_order; + v_sort_order := new__sort_order; end if; - begin +-- begin insert into subsite_callbacks (callback_id, event_type, object_type, callback, callback_type, sort_order) values - (v_callback_id, new.event_type, new.object_type, new.callback, new.callback_type, v_sort_order); - exception when dup_val_on_index then - select callback_id into v_callback_id - from subsite_callbacks - where event_type = new.event_type - and object_type = new.object_type - and callback_type = new.callback_type - and callback = new.callback; - end; + (v_callback_id, new__event_type, new__object_type, new__callback, new__callback_type, v_sort_order); + +-- TODO: Can we do this properly? +-- If not, could move select before insert +-- exception when dup_val_on_index then +-- select callback_id into v_callback_id +-- from subsite_callbacks +-- where event_type = new__event_type +-- and object_type = new__object_type +-- and callback_type = new__callback_type +-- and callback = new__callback; +-- end; return v_callback_id; - END new; +end;' language 'plpgsql'; +-- procedure delete ( +-- callback_id IN subsite_callbacks.callback_id%TYPE +-- ) +-- is +-- begin +-- delete from subsite_callbacks where callback_id=subsite_callback.delete.callback_id; +-- end delete; - procedure delete ( - callback_id IN subsite_callbacks.callback_id%TYPE - ) - is - begin - delete from subsite_callbacks where callback_id=subsite_callback.delete.callback_id; - end delete; +create function subsite_callback__delete(integer) +returns integer as ' +declare + delete__callback_id alias for $1; +begin + delete from subsite_callbacks where callback_id = delete__callback_id; + return 0; +end;' language 'plpgsql'; -end subsite_callback; -/ -show errors; +-- end subsite_callback; +-- / +-- show errors; Index: openacs-4/packages/acs-subsite/sql/postgresql/subsite-callbacks-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/sql/postgresql/subsite-callbacks-drop.sql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/sql/postgresql/subsite-callbacks-drop.sql 12 Apr 2001 04:00:44 -0000 1.1 +++ openacs-4/packages/acs-subsite/sql/postgresql/subsite-callbacks-drop.sql 17 Apr 2001 04:10:06 -0000 1.2 @@ -12,5 +12,5 @@ -- License. Full text of the license is available from the GNU Project: -- http://www.fsf.org/copyleft/gpl.html -drop package subsite_callback; +select drop_package('subsite_callback'); drop table subsite_callbacks; Index: openacs-4/packages/acs-subsite/sql/postgresql/user-profiles-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/sql/postgresql/user-profiles-create.sql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/sql/postgresql/user-profiles-create.sql 12 Apr 2001 04:00:44 -0000 1.1 +++ openacs-4/packages/acs-subsite/sql/postgresql/user-profiles-create.sql 17 Apr 2001 04:10:06 -0000 1.2 @@ -10,114 +10,185 @@ -- APPLICATION USER PROFILES -- ------------------------------- -begin +-- begin - -- the 'user' role should already exist from the portraits stuff. - -- acs_rel_type.create_role('user', - -- 'Registered User', 'Registered Users'); +-- -- the 'user' role should already exist from the portraits stuff. +-- -- acs_rel_type.create_role('user', +-- -- 'Registered User', 'Registered Users'); - acs_rel_type.create_role('application', - 'Application Group', 'Application Group'); +-- acs_rel_type.create_role('application', +-- 'Application Group', 'Application Group'); - acs_rel_type.create_type( - rel_type => 'user_profile', - pretty_name => 'User Profile', - pretty_plural => 'User Profiles', - supertype => 'membership_rel', - table_name => 'user_profiles', - id_column => 'profile_id', - package_name => 'user_profile', - abstract_p => 'f', - object_type_one => 'application_group', - role_one => 'application', - min_n_rels_one => 0, - max_n_rels_one => null, - object_type_two => 'user', - role_two => 'user', - min_n_rels_two => 0, - max_n_rels_two => null +-- acs_rel_type.create_type( +-- rel_type => 'user_profile', +-- pretty_name => 'User Profile', +-- pretty_plural => 'User Profiles', +-- supertype => 'membership_rel', +-- table_name => 'user_profiles', +-- id_column => 'profile_id', +-- package_name => 'user_profile', +-- abstract_p => 'f', +-- object_type_one => 'application_group', +-- role_one => 'application', +-- min_n_rels_one => 0, +-- max_n_rels_one => null, +-- object_type_two => 'user', +-- role_two => 'user', +-- min_n_rels_two => 0, +-- max_n_rels_two => null +-- ); + +-- end; +-- / +-- show errors + +create function inline_0 () +returns integer as ' +begin + -- the ''user'' role should already exist from the portraits stuff. + -- acs_rel_type.create_role(''user'', + -- ''Registered User'', ''Registered Users''); + + PERFORM acs_rel_type__create_role(''application'', ''Application Group'', ''Application Group''); + + PERFORM acs_rel_type__create_type ( + ''user_profile'', + ''User Profile'', + ''User Profiles'', + ''membership_rel'', + ''user_profiles'', + ''profile_id'', + ''user_profile'', + ''application_group'', + ''application'', + 0, + null, + ''user'', + ''user'', + 0, + null ); -end; -/ -show errors + return 0; +end;' language 'plpgsql'; +select inline_0 (); +drop function inline_0 (); + create table user_profiles ( - profile_id constraint user_profiles_profile_id_fk + profile_id integer constraint user_profiles_profile_id_fk references membership_rels (rel_id) constraint user_profiles_profile_id_pk primary key ); -create or replace package user_profile -as +-- create or replace package user_profile +-- as - function new ( - profile_id in user_profiles.profile_id%TYPE default null, - rel_type in acs_rels.rel_type%TYPE default 'user_profile', - object_id_one in acs_rels.object_id_one%TYPE, - object_id_two in acs_rels.object_id_two%TYPE, - member_state in membership_rels.member_state%TYPE default null, - creation_user in acs_objects.creation_user%TYPE default null, - creation_ip in acs_objects.creation_ip%TYPE default null - ) return user_profiles.profile_id%TYPE; +-- function new ( +-- profile_id in user_profiles.profile_id%TYPE default null, +-- rel_type in acs_rels.rel_type%TYPE default 'user_profile', +-- object_id_one in acs_rels.object_id_one%TYPE, +-- object_id_two in acs_rels.object_id_two%TYPE, +-- member_state in membership_rels.member_state%TYPE default null, +-- creation_user in acs_objects.creation_user%TYPE default null, +-- creation_ip in acs_objects.creation_ip%TYPE default null +-- ) return user_profiles.profile_id%TYPE; - procedure delete ( - profile_id in user_profiles.profile_id%TYPE - ); +-- procedure delete ( +-- profile_id in user_profiles.profile_id%TYPE +-- ); -end user_profile; -/ -show errors +-- end user_profile; +-- / +-- show errors -create or replace package body user_profile -as +-- create or replace package body user_profile +-- as - function new ( - profile_id in user_profiles.profile_id%TYPE default null, - rel_type in acs_rels.rel_type%TYPE default 'user_profile', - object_id_one in acs_rels.object_id_one%TYPE, - object_id_two in acs_rels.object_id_two%TYPE, - member_state in membership_rels.member_state%TYPE default null, - creation_user in acs_objects.creation_user%TYPE default null, - creation_ip in acs_objects.creation_ip%TYPE default null - ) return user_profiles.profile_id%TYPE - is - v_profile_id integer; - begin +-- function new ( +-- profile_id in user_profiles.profile_id%TYPE default null, +-- rel_type in acs_rels.rel_type%TYPE default 'user_profile', +-- object_id_one in acs_rels.object_id_one%TYPE, +-- object_id_two in acs_rels.object_id_two%TYPE, +-- member_state in membership_rels.member_state%TYPE default null, +-- creation_user in acs_objects.creation_user%TYPE default null, +-- creation_ip in acs_objects.creation_ip%TYPE default null +-- ) return user_profiles.profile_id%TYPE +-- is +-- v_profile_id integer; +-- begin - v_profile_id := membership_rel.new ( - rel_id => profile_id, - rel_type => rel_type, - object_id_one => object_id_one, - object_id_two => object_id_two, - member_state => member_state, - creation_user => creation_user, - creation_ip => creation_ip +-- v_profile_id := membership_rel.new ( +-- rel_id => profile_id, +-- rel_type => rel_type, +-- object_id_one => object_id_one, +-- object_id_two => object_id_two, +-- member_state => member_state, +-- creation_user => creation_user, +-- creation_ip => creation_ip +-- ); + +-- insert into user_profiles (profile_id) values (v_profile_id); + +-- return v_profile_id; +-- end new; + +create function user_profile__new(integer,varchar,integer,integer,varchar,integer,varchar) +returns integer as ' +declare + new__profile_id alias for $1; -- default null, + new__rel_type alias for $2; -- default ''user_profile'', + new__object_id_one alias for $3; + new__object_id_two alias for $4; + new__member_state alias for $5; -- default null, + new__creation_user alias for $6; -- default null, + new__creation_ip alias for $7; -- default null + v_profile_id integer; +begin + v_profile_id := membership_rel__new ( + new__profile_id, + new__rel_type, + new__object_id_one, + new__object_id_two, + new__member_state, + new__creation_user, + new__creation_ip ); insert into user_profiles (profile_id) values (v_profile_id); return v_profile_id; - end new; +end;' language 'plpgsql'; - procedure delete ( - profile_id in user_profiles.profile_id%TYPE - ) - is - begin +-- procedure delete ( +-- profile_id in user_profiles.profile_id%TYPE +-- ) +-- is +-- begin - membership_rel.delete(profile_id); +-- membership_rel.delete(profile_id); - end delete; +-- end delete; -end user_profile; -/ -show errors +create function user_profile__delete(integer) +returns integer as ' +declare + profile_id alias for $1; +begin + PERFORM membership_rel__delete(profile_id); + + return 0; +end;' language 'plpgsql'; + +-- end user_profile; +-- / +-- show errors + insert into group_type_rels (group_rel_type_id, group_type, rel_type) values @@ -126,7 +197,7 @@ -- This view is extremely fast, but for some reason its not so blaxing fast -- when used in the registered_users_of_package_id view below. -create or replace view application_users as +create view application_users as select ag.package_id, gem.element_id as user_id from user_profiles up, group_element_map gem, @@ -137,13 +208,13 @@ -- create the generalized versions of the registered_users and cc_users views: -create or replace view registered_users_of_package_id as +create view registered_users_of_package_id as select u.*, au.package_id from application_users au, registered_users u where au.user_id = u.user_id; -create or replace view cc_users_of_package_id as +create view cc_users_of_package_id as select u.*, au.package_id from application_users au, cc_users u Index: openacs-4/packages/acs-subsite/sql/postgresql/user-profiles-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/sql/postgresql/user-profiles-drop.sql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-subsite/sql/postgresql/user-profiles-drop.sql 12 Apr 2001 04:00:44 -0000 1.1 +++ openacs-4/packages/acs-subsite/sql/postgresql/user-profiles-drop.sql 17 Apr 2001 04:10:06 -0000 1.2 @@ -6,12 +6,15 @@ -- @cvs-id $Id$ -- +drop view cc_users_of_package_id; +drop view registered_users_of_package_id; +drop view application_users; + +select acs_rel_type__drop_type('user_profile', 'f'); +select acs_rel_type__drop_role('application'); + +select drop_package('user_profile'); + drop table user_profiles; -drop package user_profile; -begin - acs_rel_type.drop_type('user_profile'); -end; -/ -show errors