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 -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 -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 -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 -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 -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 -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 -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 -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 -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 -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 -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 -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 -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 -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