Index: openacs-4/packages/cronjob/sql/oracle/upgrade/upgrade-0.1d-0.2d1.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/cronjob/sql/oracle/upgrade/upgrade-0.1d-0.2d1.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/cronjob/sql/oracle/upgrade/upgrade-0.1d-0.2d1.sql 8 Oct 2003 16:43:41 -0000 1.1 @@ -0,0 +1,262 @@ +-- +-- Cronjob in sql +-- @author tom jackson +-- @creation-date 22 Sept 2001 +-- @cvs-id $Id: upgrade-0.1d-0.2d1.sql,v 1.1 2003/10/08 16:43:41 mohanp Exp $ +-- + +create or replace package cronjob +as + function cronjob_p ( + cronjob_id in cronjobs.cronjob_id%TYPE + ) return char; + + function new ( + cronjob_id in cronjobs.cronjob_id%TYPE default null, + user_id in cronjobs.user_id%TYPE, + description in cronjobs.description%TYPE, + approved_p in cronjobs.approved_p%TYPE default 'f', + disabled_p in cronjobs.disabled_p%TYPE, + minute in cronjobs.minute%TYPE default '0', + hr in cronjobs.hr%TYPE default '0', + mon in cronjobs.mon%TYPE default '0', + day in cronjobs.day%TYPE default '0', + dayofweek in cronjobs.dayofweek%TYPE default '0', + run_sql in cronjobs.run_sql%TYPE default null, + run_tcl in cronjobs.run_tcl%TYPE default null, + email in cronjobs.email%TYPE default null, + object_type in acs_objects.object_type%TYPE default 'cronjob', + 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, + context_id in acs_objects.context_id%TYPE default null + ) return cronjobs.cronjob_id%TYPE; + + procedure del ( + cronjob_id in cronjobs.cronjob_id%TYPE + ); + procedure set_attrs ( + cronjob_id in cronjobs.cronjob_id%TYPE, + user_id in cronjobs.user_id%TYPE default null, + description in cronjobs.description%TYPE default null, + approved_p in cronjobs.approved_p%TYPE default null, + disabled_p in cronjobs.disabled_p%TYPE default null, + minute in cronjobs.minute%TYPE default null, + hr in cronjobs.hr%TYPE default null, + mon in cronjobs.mon%TYPE default null, + day in cronjobs.day%TYPE default null, + dayofweek in cronjobs.dayofweek%TYPE default null, + run_sql in cronjobs.run_sql%TYPE default null, + run_tcl in cronjobs.run_tcl%TYPE default null, + email in cronjobs.email%TYPE default null + ); + procedure reset_attr ( + cronjob_id in cronjobs.cronjob_id%TYPE, + column_name in varchar + ); +end cronjob; +/ +show errors; + +create or replace package body cronjob +as + function cronjob_p ( + cronjob_id in cronjobs.cronjob_id%TYPE + ) return char + is + -- declare vars here + v_check_cronjob_id integer; + begin + select count(cronjob_id) into v_check_cronjob_id + from cronjobs + where cronjob_id = cronjob_p.cronjob_id; + if v_check_cronjob_id = 1 then + return 't'; + else + return 'f'; + end if; + end cronjob_p; + + -- Context ID May need adjustment to reflect security/access model. + function new ( + cronjob_id in cronjobs.cronjob_id%TYPE default null, + user_id in cronjobs.user_id%TYPE, + description in cronjobs.description%TYPE, + approved_p in cronjobs.approved_p%TYPE default 'f', + disabled_p in cronjobs.disabled_p%TYPE, + minute in cronjobs.minute%TYPE default '0', + hr in cronjobs.hr%TYPE default '0', + mon in cronjobs.mon%TYPE default '0', + day in cronjobs.day%TYPE default '0', + dayofweek in cronjobs.dayofweek%TYPE default '0', + run_sql in cronjobs.run_sql%TYPE default null, + run_tcl in cronjobs.run_tcl%TYPE default null, + email in cronjobs.email%TYPE default null, + object_type in acs_objects.object_type%TYPE default 'cronjob', + 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, + context_id in acs_objects.context_id%TYPE default null + ) return cronjobs.cronjob_id%TYPE + is + v_cronjob_id integer; + begin + v_cronjob_id := acs_object.new ( + object_id => cronjob_id, + object_type => object_type, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + context_id => context_id + ); + + insert into cronjobs + ( cronjob_id, user_id, description, approved_p, disabled_p, minute, hr, mon, day, dayofweek, run_sql, run_tcl, email ) + values + ( v_cronjob_id, user_id, description, approved_p, disabled_p, minute, hr, mon, day, dayofweek, run_sql, run_tcl, email ); + + return v_cronjob_id; + end new; + + procedure del ( + cronjob_id in cronjobs.cronjob_id%TYPE + ) + is + begin + if cronjob_p(cronjob.del.cronjob_id) = 'f' then + return; + end if; + + delete from cronjobs + where cronjob_id = cronjob.del.cronjob_id; + + acs_object.del(cronjob_id); + end del; + + procedure set_attrs ( + cronjob_id in cronjobs.cronjob_id%TYPE, + user_id in cronjobs.user_id%TYPE default null, + description in cronjobs.description%TYPE default null, + approved_p in cronjobs.approved_p%TYPE default null, + disabled_p in cronjobs.disabled_p%TYPE default null, + minute in cronjobs.minute%TYPE default null, + hr in cronjobs.hr%TYPE default null, + mon in cronjobs.mon%TYPE default null, + day in cronjobs.day%TYPE default null, + dayofweek in cronjobs.dayofweek%TYPE default null, + run_sql in cronjobs.run_sql%TYPE default null, + run_tcl in cronjobs.run_tcl%TYPE default null, + email in cronjobs.email%TYPE default null + ) + is + -- declared vars here + begin + if cronjob_p(cronjob.set_attrs.cronjob_id) = 'f' then + return; + end if; + + -- modify parts that are not null + + if user_id is not null then + update cronjobs set user_id = set_attrs.user_id + where cronjob_id = set_attrs.cronjob_id; + end if; + if description is not null then + update cronjobs set description = set_attrs.description + where cronjob_id = set_attrs.cronjob_id; + end if; + if approved_p is not null then + update cronjobs set approved_p = set_attrs.approved_p + where cronjob_id = set_attrs.cronjob_id; + end if; + if disabled_p is not null then + update cronjobs set disabled_p = set_attrs.disabled_p + where cronjob_id = set_attrs.cronjob_id; + end if; + if minute is not null then + update cronjobs set minute = set_attrs.minute + where cronjob_id = set_attrs.cronjob_id; + end if; + if hr is not null then + update cronjobs set hr = set_attrs.hr + where cronjob_id = set_attrs.cronjob_id; + end if; + if mon is not null then + update cronjobs set mon = set_attrs.mon + where cronjob_id = set_attrs.cronjob_id; + end if; + if day is not null then + update cronjobs set day = set_attrs.day + where cronjob_id = set_attrs.cronjob_id; + end if; + if dayofweek is not null then + update cronjobs set dayofweek = set_attrs.dayofweek + where cronjob_id = set_attrs.cronjob_id; + end if; + if run_sql is not null then + update cronjobs set run_sql = set_attrs.run_sql + where cronjob_id = set_attrs.cronjob_id; + end if; + if run_tcl is not null then + update cronjobs set run_tcl = set_attrs.run_tcl + where cronjob_id = set_attrs.cronjob_id; + end if; + if email is not null then + update cronjobs set email = set_attrs.email + where cronjob_id = set_attrs.cronjob_id; + end if; + + end set_attrs; + procedure reset_attr ( + cronjob_id in cronjobs.cronjob_id%TYPE, + column_name in varchar + ) + is + -- declared vars here + begin + if cronjob_p(cronjob.reset_attr.cronjob_id) = 'f' then + return; + end if; + + if column_name = 'approved_p' then + update cronjobs set approved_p = 'f' + where cronjob_id = reset_attr.cronjob_id; + end if; + if column_name = 'minute' then + update cronjobs set minute = '0' + where cronjob_id = reset_attr.cronjob_id; + end if; + if column_name = 'hr' then + update cronjobs set hr = '0' + where cronjob_id = reset_attr.cronjob_id; + end if; + if column_name = 'mon' then + update cronjobs set mon = '0' + where cronjob_id = reset_attr.cronjob_id; + end if; + if column_name = 'day' then + update cronjobs set day = '0' + where cronjob_id = reset_attr.cronjob_id; + end if; + if column_name = 'dayofweek' then + update cronjobs set dayofweek = '0' + where cronjob_id = reset_attr.cronjob_id; + end if; + if column_name = 'run_sql' then + update cronjobs set run_sql = NULL + where cronjob_id = reset_attr.cronjob_id; + end if; + if column_name = 'run_tcl' then + update cronjobs set run_tcl = NULL + where cronjob_id = reset_attr.cronjob_id; + end if; + if column_name = 'email' then + update cronjobs set email = NULL + where cronjob_id = reset_attr.cronjob_id; + end if; + + end reset_attr; + +end cronjob; +/ +show errors; Index: openacs-4/packages/download/sql/oracle/upgrade/upgrade-4.1-5.0d1.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/download/sql/oracle/upgrade/upgrade-4.1-5.0d1.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/download/sql/oracle/upgrade/upgrade-4.1-5.0d1.sql 8 Oct 2003 16:45:37 -0000 1.1 @@ -0,0 +1,152 @@ +--Defines the following packages: +-- +-- Download Repository +-- -new +-- -delete +-- -edit +-- -new_archive_type (no need for proc) +-- -delete_archive_type (no need for proc) +-- -edit_archive_type (no need for proc) +-- -new_reason (no need for proc) +-- -delete_reason (no need for proc) +-- -edit_reason (no need for proc) +-- -new_metadata (no need for proc) +-- -delete_metadata (no need for proc) +-- -edit_metadata (no need for proc) +-- -new_metadata_choice (no need for proc) +-- -delete_metadata_choice (no need for proc) +-- -edit_metadata_choice (no need for proc) +-- +-- +-- Download Archive +-- -new +-- -delete +-- -edit +-- -new_revision +-- -approve_revision +-- -set_metadata_value +-- -downloaded_by + +create or replace package download_rep as + + function new ( + repository_id in acs_objects.object_id%TYPE, + title in cr_revisions.title%TYPE, + description in cr_revisions.description%TYPE, + help_text in varchar2 default null, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE default null, + parent_id in cr_items.parent_id%TYPE default null, + context_id in acs_objects.context_id%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null + ) return download_repository.repository_id%TYPE; + + procedure edit ( + repository_id in acs_objects.object_id%TYPE, + title in cr_revisions.title%TYPE, + description in cr_revisions.description%TYPE, + help_text in varchar2 default null, + last_modified in acs_objects.last_modified%TYPE default sysdate, + modifying_user in acs_objects.modifying_user%TYPE default null, + modifying_ip in acs_objects.modifying_ip%TYPE default null + ); + + procedure del ( + repository_id in acs_objects.object_id%TYPE + ); + +end download_rep; +/ +show errors + + +create or replace package body download_rep as + + function new ( + repository_id in acs_objects.object_id%TYPE, + title in cr_revisions.title%TYPE, + description in cr_revisions.description%TYPE, + help_text in varchar2 default null, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE default null, + parent_id in cr_items.parent_id%TYPE default null, + context_id in acs_objects.context_id%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null + ) return download_repository.repository_id%TYPE + is + v_name cr_items.name%TYPE; + v_repository_id integer; + begin + v_name := 'download_repository' || repository_id; + v_repository_id := content_item.new ( + content_type => 'cr_download_rep', + item_id => new.repository_id, + name => v_name, + parent_id => new.parent_id, + context_id => new.context_id, + title => new.title, + description => new.description, + text => new.help_text, + creation_date => new.creation_date, + creation_user => new.creation_user, + creation_ip => new.creation_ip, + is_live => 't' + ); + + insert into download_repository + (repository_id) + values + (new.repository_id); + + return v_repository_id; + end new; + + procedure edit ( + repository_id in acs_objects.object_id%TYPE, + title in cr_revisions.title%TYPE, + description in cr_revisions.description%TYPE, + help_text in varchar2 default null, + last_modified in acs_objects.last_modified%TYPE default sysdate, + modifying_user in acs_objects.modifying_user%TYPE default null, + modifying_ip in acs_objects.modifying_ip%TYPE default null + ) + is + v_revision_id integer; + begin + v_revision_id := content_revision.new ( + item_id => edit.repository_id, + title => edit.title, + description => edit.description, + text => edit.help_text, + creation_date => edit.last_modified, + creation_user => edit.modifying_user, + creation_ip => edit.modifying_ip + ); + content_item.set_live_revision(v_revision_id); + + + update acs_objects set + last_modified = edit.last_modified, + modifying_user = edit.modifying_user, + modifying_ip = edit.modifying_ip + where object_id = edit.repository_id; + + end edit; + + + procedure del ( + repository_id in acs_objects.object_id%TYPE + ) + is + begin + update acs_objects set context_id = null where context_id = download_rep.del.repository_id; + + delete from download_repository + where repository_id = download_rep.del.repository_id; + + acs_object.del(repository_id); + end; + +end download_rep; +/ +show errors Index: openacs-4/packages/news-aggregator/sql/oracle/upgrade/upgrade-0.1d-0.2d.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news-aggregator/sql/oracle/upgrade/Attic/upgrade-0.1d-0.2d.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news-aggregator/sql/oracle/upgrade/upgrade-0.1d-0.2d.sql 8 Oct 2003 16:48:40 -0000 1.1 @@ -0,0 +1,121 @@ +-- +-- news-aggregator-package-create.sql +-- +-- @author Simon Carstensen +-- +-- @cvs-id $Id: news-aggregator-package-create.sql +-- + +create or replace package na_source +as + + function new ( + source_id in na_sources.source_id%TYPE default null, + package_id in na_sources.package_id%TYPE, + feed_url in na_sources.feed_url%TYPE default null, + link in na_sources.link%TYPE default null, + title in na_sources.title%TYPE default null, + description in na_sources.description%TYPE default null, + creation_user in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null + ) return na_sources.source_id%TYPE; + + procedure del ( + source_id in na_sources.source_id%TYPE + ); + + function title ( + source_id in na_sources.source_id%TYPE + ) return na_sources.title%TYPE; + +end na_source; +/ +show errors + +create or replace package body na_source +as + + function new ( + source_id in na_sources.source_id%TYPE default null, + package_id in na_sources.package_id%TYPE, + feed_url in na_sources.feed_url%TYPE default null, + link in na_sources.link%TYPE default null, + title in na_sources.title%TYPE default null, + description in na_sources.description%TYPE default null, + creation_user in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null + ) return na_sources.source_id%TYPE + is + v_source_id na_sources.source_id%TYPE; + begin + + v_source_id := acs_object.new( + object_id => na_source.new.source_id, + object_type => 'na_source', + creation_date => sysdate, + creation_user => na_source.new.creation_user, + creation_ip => na_source.new.creation_ip, + context_id => na_source.new.package_id + ); + + insert into na_sources ( + source_id, + package_id, + owner_id, + feed_url, + link, + title, + description, + updates, + last_scanned, + last_modified + ) values ( + v_source_id, + na_source.new.package_id, + na_source.new.creation_user, + na_source.new.feed_url, + na_source.new.link, + na_source.new.title, + na_source.new.description, + '0', + sysdate, + sysdate + ); + + return v_source_id; + + end new; + + procedure del ( + source_id in na_sources.source_id%TYPE + ) + is + begin + + delete + from na_sources + where source_id = na_source.del.source_id; + + acs_object.del(na_source.del.source_id); + + end del; + + function title ( + source_id in na_sources.source_id%TYPE + ) return na_sources.title%TYPE + is + v_title na_sources.title%TYPE; + begin + + select title + into v_title + from na_sources + where source_id = na_source.title.source_id; + + return v_title; + + end title; + +end na_source; +/ +show errors Index: openacs-4/packages/notes/sql/oracle/upgrade/upgrade-0.11d-0.12d.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/notes/sql/oracle/upgrade/upgrade-0.11d-0.12d.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/notes/sql/oracle/upgrade/upgrade-0.11d-0.12d.sql 8 Oct 2003 16:50:04 -0000 1.1 @@ -0,0 +1,107 @@ +-- +-- packages/notes/sql/notes-create.sql +-- +-- @author rhs@mit.edu +-- @creation-date 2000-10-22 +-- @cvs-id $Id: upgrade-0.11d-0.12d.sql,v 1.1 2003/10/08 16:50:04 mohanp Exp $ +-- + +create or replace package note +as + function new ( + note_id in notes.note_id%TYPE default null, + owner_id in notes.owner_id%TYPE default null, + title in notes.title%TYPE, + body in notes.body%TYPE, + object_type in acs_object_types.object_type%TYPE + default 'note', + 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, + context_id in acs_objects.context_id%TYPE default null + ) return notes.note_id%TYPE; + + procedure del ( + note_id in notes.note_id%TYPE + ); + + function name ( + note_id in notes.note_id%TYPE + ) return notes.title%TYPE; +end note; +/ +show errors + +create or replace package body note +as + function new ( + note_id in notes.note_id%TYPE default null, + owner_id in notes.owner_id%TYPE default null, + title in notes.title%TYPE, + body in notes.body%TYPE, + object_type in acs_object_types.object_type%TYPE + default 'note', + 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, + context_id in acs_objects.context_id%TYPE default null + ) return notes.note_id%TYPE + is + v_note_id integer; + begin + v_note_id := acs_object.new ( + object_id => note_id, + object_type => object_type, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + context_id => context_id + ); + + insert into notes + (note_id, owner_id, title, body) + values + (v_note_id, owner_id, title, body); + + acs_permission.grant_permission( + object_id => v_note_id, + grantee_id => owner_id, + privilege => 'admin' + ); + + return v_note_id; + end new; + + procedure del ( + note_id in notes.note_id%TYPE + ) + is + begin + delete from acs_permissions + where object_id = note.del.note_id; + + delete from notes + where note_id = note.del.note_id; + + acs_object.del(note_id); + end del; + + function name ( + note_id in notes.note_id%TYPE + ) return notes.title%TYPE + is + v_note_name notes.title%TYPE; + begin + select title into v_note_name + from notes + where note_id = name.note_id; + + return v_note_name; + end name; +end note; +/ +show errors; Index: openacs-4/packages/organizations/sql/oracle/upgrade/upgrade-0.1d-0.2d.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/organizations/sql/oracle/upgrade/upgrade-0.1d-0.2d.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/organizations/sql/oracle/upgrade/upgrade-0.1d-0.2d.sql 8 Oct 2003 16:51:05 -0000 1.1 @@ -0,0 +1,104 @@ +-- packages/organization/sql/oracle/organization-plsql.sql +-- +-- @author Jon Griffin +-- @creation-date 13 March 2003 +-- @cvs-id $Id: upgrade-0.1d-0.2d.sql,v 1.1 2003/10/08 16:51:05 mohanp Exp $ + +create or replace package organization +as + function new ( + p_organization_id in organizations.organization_id%TYPE default null, + p_legal_name in organizations.legal_name%TYPE, + p_name in organizations.name%TYPE, + p_notes in organizations.notes%TYPE default null, + p_reg_number in organizations.reg_number%TYPE default null, + p_email in parties.email%TYPE default null, + p_url in parties.url%TYPE default null, + p_object_type in acs_objects.object_type%TYPE default 'organization', + p_creation_date in acs_objects.creation_date%TYPE default sysdate, + p_creation_user in acs_objects.creation_user%TYPE default null, + p_creation_ip in acs_objects.creation_ip%TYPE default null, + p_context_id in acs_objects.context_id%TYPE default null + ) return organizations.organization_id%TYPE; + + procedure del ( + p_organization_id in organizations.organization_id%TYPE + ); + +end organization; +/ +show errors + +create or replace package body organization +as + function new ( + p_organization_id in organizations.organization_id%TYPE default null, + p_legal_name in organizations.legal_name%TYPE, + p_name in organizations.name%TYPE, + p_notes in organizations.notes%TYPE default null, + p_reg_number in organizations.reg_number%TYPE default null, + p_email in parties.email%TYPE default null, + p_url in parties.url%TYPE default null, + p_object_type in acs_objects.object_type%TYPE default 'organization', + p_creation_date in acs_objects.creation_date%TYPE default sysdate, + p_creation_user in acs_objects.creation_user%TYPE default null, + p_creation_ip in acs_objects.creation_ip%TYPE default null, + p_context_id in acs_objects.context_id%TYPE default null + ) return organizations.organization_id%TYPE + is + v_organization_id organizations.organization_id%TYPE; + begin + v_organization_id := party.new ( + party_id => null, + object_type => p_object_type, + creation_user => p_creation_user, + creation_ip => p_creation_ip, + email => p_email, + url => p_url, + context_id => p_context_id + ); + + + insert into organizations ( + legal_name, + name, + notes, + organization_id, + reg_number + ) + values ( + p_legal_name, + p_name, + p_notes, + v_organization_id, + p_reg_number + ); + + acs_permission.grant_permission ( + object_id => v_organization_id, + grantee_id => p_creation_user, + privilege => 'admin' + ); + return v_organization_id; +end new; + + -- + -- + procedure del ( + p_organization_id in organizations.organization_id%TYPE + ) + is + begin + delete from acs_permissions + where object_id = organization.del.p_organization_id; + + delete from organizations + where organization_id = organization.del.p_organization_id; + + party.del(organization.del.p_organization_id); + + end del; + +end organization; +/ +show errors Index: openacs-4/packages/press/sql/oracle/upgrade/upgrade-4.0.1-5.0d1.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/press/sql/oracle/upgrade/upgrade-4.0.1-5.0d1.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/press/sql/oracle/upgrade/upgrade-4.0.1-5.0d1.sql 8 Oct 2003 16:51:55 -0000 1.1 @@ -0,0 +1,544 @@ +-- /packages/press/sql/press-create.sql +-- +-- @author sarah@arsdigita.com +-- @author stefan@arsdigita.com +-- @author ron@arsdigita.com +-- +-- @created 2000-11-15 +-- +-- $Id: upgrade-4.0.1-5.0d1.sql,v 1.1 2003/10/08 16:51:55 mohanp Exp $ + + +-- *** PACKAGE PRESS, plsql to create content_item *** +create or replace package press +as + function new ( + name in cr_items.name%TYPE, + publication_name in cr_press.publication_name%TYPE, + publication_link in cr_press.publication_link%TYPE default null, + publication_date in cr_press.publication_date%TYPE, + publication_date_desc in cr_press.publication_date_desc%TYPE default null, + article_link in cr_press.article_link%TYPE default null, + article_pages in cr_press.article_pages%TYPE default null, + article_abstract_html_p in cr_press.article_abstract_html_p%TYPE, + approval_user in cr_press.approval_user%TYPE default null, + approval_date in cr_press.approval_date%TYPE default null, + approval_ip in cr_press.approval_ip%TYPE default null, + release_date in cr_press.release_date%TYPE default null, + archive_date in cr_press.archive_date%TYPE default null, + package_id in cr_press.package_id%TYPE default null, + parent_id in acs_objects.context_id%TYPE default null, + item_id in cr_items.item_id%TYPE default null, + locale in cr_items.locale%TYPE default null, + item_subtype in acs_object_types.object_type%TYPE default 'content_item', + content_type in acs_object_types.object_type%TYPE default 'press', + title in cr_revisions.title%TYPE default null, + description in cr_revisions.description%TYPE default null, + mime_type in cr_revisions.mime_type%TYPE default 'text/plain', + template_id in press_templates.template_id%TYPE default 1, + nls_language in cr_revisions.nls_language%TYPE default null, + text in varchar2 default null, + relation_tag in cr_child_rels.relation_tag%TYPE default null, + is_live_p in varchar2 default 'f', + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_ip in acs_objects.creation_ip%TYPE default null, + creation_user in acs_objects.creation_user%TYPE default null + ) return cr_press.press_id%TYPE; + + procedure del ( + item_id in cr_items.item_id%TYPE + ); + + procedure make_permanent ( + item_id in cr_items.item_id%TYPE + ); + + procedure archive ( + item_id in cr_items.item_id%TYPE, + archive_date in cr_press.archive_date%TYPE default sysdate + ); + + -- administrative procs + procedure approve ( + press_id in cr_press.press_id%TYPE, + approve_p in varchar2 default 't' + ); + + procedure approve_release( + revision_id in cr_revisions.revision_id%TYPE, + release_date in cr_press.release_date%TYPE, + archive_date in cr_press.archive_date%TYPE + ); + + + procedure set_active_revision( + revision_id in cr_revisions.revision_id%TYPE + ); + + + function is_live ( + press_id in cr_press.press_id%TYPE + ) return varchar; + + function status ( + press_id in cr_press.press_id%TYPE + ) return varchar; + +end press; +/ +show errors + + +create or replace package body press +as function new ( + name in cr_items.name%TYPE, + publication_name in cr_press.publication_name%TYPE, + publication_link in cr_press.publication_link%TYPE default null, + publication_date in cr_press.publication_date%TYPE, + publication_date_desc in cr_press.publication_date_desc%TYPE default null, + article_link in cr_press.article_link%TYPE default null, + article_pages in cr_press.article_pages%TYPE default null, + article_abstract_html_p in cr_press.article_abstract_html_p%TYPE, + approval_user in cr_press.approval_user%TYPE default null, + approval_date in cr_press.approval_date%TYPE default null, + approval_ip in cr_press.approval_ip%TYPE default null, + release_date in cr_press.release_date%TYPE default null, + archive_date in cr_press.archive_date%TYPE default null, + package_id in cr_press.package_id%TYPE default null, + parent_id in acs_objects.context_id%TYPE default null, + item_id in cr_items.item_id%TYPE default null, + locale in cr_items.locale%TYPE default null, + item_subtype in acs_object_types.object_type%TYPE default 'content_item', + content_type in acs_object_types.object_type%TYPE default 'press', + title in cr_revisions.title%TYPE default null, + description in cr_revisions.description%TYPE default null, + mime_type in cr_revisions.mime_type%TYPE default 'text/plain', + template_id in press_templates.template_id%TYPE default 1, + nls_language in cr_revisions.nls_language%TYPE default null, + text in varchar2 default null, + relation_tag in cr_child_rels.relation_tag%TYPE default null, + is_live_p in varchar2 default 'f', + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_ip in acs_objects.creation_ip%TYPE default null, + creation_user in acs_objects.creation_user%TYPE default null + ) return cr_press.press_id%TYPE + is + v_press_id integer; + v_item_id integer; + v_revision_id integer; + begin + v_item_id := content_item.new( + name => name, + parent_id => parent_id, + item_id => item_id, + locale => locale, + item_subtype => item_subtype, + content_type => content_type, + mime_type => mime_type, + nls_language => nls_language, + relation_tag => relation_tag, + creation_date => creation_date, + creation_ip => creation_ip, + creation_user => creation_user + ); + + v_revision_id := content_revision.new( + title => title, + description => description, + mime_type => mime_type, + nls_language => nls_language, + text => text, + item_id => v_item_id, + creation_date => creation_date, + creation_ip => creation_ip, + creation_user => creation_user + ); + + insert into cr_press + (press_id, + package_id, + publication_name, + publication_date, + publication_date_desc, + publication_link, + article_link, + article_abstract_html_p, + article_pages, + template_id, + approval_user, + approval_date, + approval_ip, + release_date, + archive_date) + values + (v_revision_id, + package_id, + publication_name, + publication_date, + publication_date_desc, + publication_link, + article_link, + article_abstract_html_p, + article_pages, + template_id, + approval_user, + approval_date, + approval_ip, + release_date, + archive_date); + + -- make this revision live when immediately approved + + if is_live_p = 't' then + content_item.set_live_revision (v_revision_id); + end if; + + return v_revision_id; + end new; + + -- deletes a press item along with all its revisions + + procedure del ( + item_id in cr_items.item_id%TYPE + ) + is + begin + delete + from cr_press + where press_id in (select revision_id + from cr_revisions + where item_id = press.del.item_id); + + content_item.del( item_id => press.del.item_id ); + end del; + + -- make a press item permanent by nulling the archive_date + -- this only applies to the currently active revision + procedure make_permanent ( + item_id in cr_items.item_id%TYPE + ) + is + begin + update cr_press + set archive_date = null + where press_id = content_item.get_live_revision(press.make_permanent.item_id); + end make_permanent; + + + -- archive a press item by setting cr_press.release_date to sysdate + -- this only applies to the currently active revision + + procedure archive ( + item_id in cr_items.item_id%TYPE, + archive_date in cr_press.archive_date%TYPE default sysdate + ) + is + begin + update cr_press + set archive_date = press.archive.archive_date + where press_id = content_item.get_live_revision(press.archive.item_id); + end archive; + + -- approve/unapprove currently active revision + + procedure approve ( + press_id in cr_press.press_id%TYPE, + approve_p in varchar2 default 't' + ) + is + v_item_id cr_items.item_id%TYPE; + begin + if press.approve.approve_p = 't' then + -- approve revision + content_item.set_live_revision ( + revision_id => press_id + ); + -- set approval_date for revision + update cr_press + set approval_date = sysdate + where press_id = press.approve.press_id; + else + -- get item_id for revision that is being unapproved + select item_id into v_item_id + from cr_revisions + where revision_id = press_id; + -- unapprove revision + -- does not mean to knock out active revision + -- content_item.unset_live_revision ( + -- item_id => v_item_id + -- ); + -- null approval_date for revision + update cr_press + set approval_date = null, + release_date = null + where press_id = press.approve.press_id; + end if; + end approve; + + + procedure approve_release( + revision_id in cr_revisions.revision_id%TYPE, + release_date in cr_press.release_date%TYPE, + archive_date in cr_press.archive_date%TYPE + ) + is + begin + update cr_press + set release_date = press.approve_release.release_date, + archive_date = press.approve_release.archive_date + where press_id = press.approve_release.revision_id; + end approve_release; + + + procedure set_active_revision( + revision_id in cr_revisions.revision_id%TYPE + ) is + v_press_item_p char; + -- could be used to check if really a 'press' item + begin + content_item.set_live_revision ( + revision_id => press.set_active_revision.revision_id + ); + end set_active_revision; + + + function is_live ( + press_id in cr_press.press_id%TYPE + ) return varchar + is + v_item_id cr_items.item_id%TYPE; + begin + select item_id into v_item_id + from cr_revisions + where revision_id = press.is_live.press_id; + -- use get_live_revision + if content_item.get_live_revision(v_item_id) = press.is_live.press_id then + return 't'; + else + return 'f'; + end if; + end is_live; + + + -- the status function returns information on the pulish or archive status + -- it does not make any checks on the order of release_date and archive_date + function status ( + press_id in cr_press.press_id%TYPE + ) return varchar + is + v_archive_date date; + v_release_date date; + begin + -- populate variables + select + archive_date, release_date + into + v_archive_date, v_release_date + from cr_press + where + press_id = press.status.press_id; + + -- if release_date is not null the item is approved, otherwise it is not + -- archive_date can be null + if v_release_date is not null then + if v_release_date - sysdate > 0 then + -- to be published (2 cases) + if v_archive_date is null then + return 'going live in ' || + round(to_char(v_release_date - sysdate),0) || ' days'; + else + return 'going live in ' || + round(to_char(v_release_date - sysdate),1) || ' days' || + ', archived in ' || round(to_char(v_archive_date - sysdate),0) || ' days'; + end if; + else + -- already released or even archived (3 cases) + if v_archive_date is null then + return 'live, permanent'; + else + if v_archive_date - sysdate > 0 then + return 'live, archived in ' || + round(to_char(v_archive_date - sysdate),0) || ' days'; + else + return 'archived'; + end if; + end if; + end if; + else + return 'unapproved'; + end if; + end status; + +end press; +/ +show errors + + +-- *** PACKAGE PRESS_REVISION, plsql to update press items + +-- press_revision: the basic idea here is to create a new press_revision +-- in both, cr_revision and cr_press for each edit, i.e. you can't edit a press revision after +-- upload, so that all changes are audited. +-- +-- plsql to create revisions of press items +create or replace package press_revision + as + function new ( + title in cr_revisions.title%TYPE, + description in cr_revisions.description%TYPE default null, + publish_date in cr_revisions.publish_date%TYPE + default sysdate, + mime_type in cr_revisions.mime_type%TYPE + default 'text/plain', + nls_language in cr_revisions.nls_language%TYPE + default null, + text in varchar2, + item_id in cr_items.item_id%TYPE, + 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, + package_id in cr_press.package_id%TYPE default null, + publication_name in cr_press.publication_name%TYPE, + publication_link in cr_press.publication_link%TYPE default null, + publication_date in cr_press.publication_date%TYPE, + publication_date_desc in cr_press.publication_date_desc%TYPE + default null, + article_link in cr_press.article_link%TYPE default null, + article_pages in cr_press.article_pages%TYPE default null, + article_abstract_html_p in cr_press.article_abstract_html_p%TYPE, + approval_user in cr_press.approval_user%TYPE default null, + approval_date in cr_press.approval_date%TYPE default null, + approval_ip in cr_press.approval_ip%TYPE + default null, + release_date in cr_press.release_date%TYPE + default sysdate, + archive_date in cr_press.archive_date%TYPE default null, + template_id in press_templates.template_id%TYPE default 1, + make_active_revision_p in varchar2 default 'f' + ) return cr_revisions.revision_id%TYPE; + + procedure del ( + revision_id in cr_revisions.revision_id%TYPE + ); +end press_revision; +/ +show errors + + +create or replace package body press_revision + as + function new ( + title in cr_revisions.title%TYPE, + description in cr_revisions.description%TYPE default null, + publish_date in cr_revisions.publish_date%TYPE + default sysdate, + mime_type in cr_revisions.mime_type%TYPE + default 'text/plain', + nls_language in cr_revisions.nls_language%TYPE + default null, + text in varchar2, + item_id in cr_items.item_id%TYPE, + 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, + package_id in cr_press.package_id%TYPE default null, + publication_name in cr_press.publication_name%TYPE, + publication_link in cr_press.publication_link%TYPE default null, + publication_date in cr_press.publication_date%TYPE, + publication_date_desc in cr_press.publication_date_desc%TYPE + default null, + article_link in cr_press.article_link%TYPE default null, + article_pages in cr_press.article_pages%TYPE default null, + article_abstract_html_p in cr_press.article_abstract_html_p%TYPE, + approval_user in cr_press.approval_user%TYPE default null, + approval_date in cr_press.approval_date%TYPE default null, + approval_ip in cr_press.approval_ip%TYPE + default null, + release_date in cr_press.release_date%TYPE + default sysdate, + archive_date in cr_press.archive_date%TYPE default null, + template_id in press_templates.template_id%TYPE default 1, + make_active_revision_p in varchar2 default 'f' + ) return cr_revisions.revision_id%TYPE + is + v_revision_id integer; + begin + + -- create revision + + v_revision_id := content_revision.new( + title => title, + description => description, + publish_date => publish_date, + mime_type => mime_type, + nls_language => nls_language, + text => text, + item_id => item_id, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip + ); + + -- create new press entry to go with new revision + + insert into cr_press + (press_id, + package_id, + publication_name, + publication_date, + template_id, + publication_date_desc, + publication_link, + article_link, + article_pages, + article_abstract_html_p, + approval_user, + approval_date, + approval_ip, + release_date, + archive_date) + values + (v_revision_id, + package_id, + publication_name, + publication_date, + template_id, + publication_date_desc, + publication_link, + article_link, + article_pages, + article_abstract_html_p, + approval_user, + approval_date, + approval_ip, + release_date, + archive_date); + + -- make active revision if indicated + + if make_active_revision_p = 't' then + press.set_active_revision(v_revision_id); + end if; + + return v_revision_id; + end new; + + procedure del ( + revision_id in cr_revisions.revision_id%TYPE + ) + is + begin + + delete from cr_press where press_id = press_revision.del.revision_id; + + content_revision.del(press_revision.del.revision_id); + + end del; +end press_revision; +/ +show errors + Index: openacs-4/packages/profile-provider/sql/oracle/upgrade/upgrade-0.1d-0.2d.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/profile-provider/sql/oracle/upgrade/upgrade-0.1d-0.2d.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/profile-provider/sql/oracle/upgrade/upgrade-0.1d-0.2d.sql 8 Oct 2003 16:52:56 -0000 1.1 @@ -0,0 +1,87 @@ +-- +-- Copyright (C) 2001, 2002 MIT +-- +-- This file is part of dotLRN. +-- +-- dotLRN is free software; you can redistribute it and/or modify it under the +-- terms of the GNU General Public License as published by the Free Software +-- Foundation; either version 2 of the License, or (at your option) any later +-- version. +-- +-- dotLRN is distributed in the hope that it will be useful, but WITHOUT ANY +-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS +-- FOR A PARTICULAR PURPOSE. See the GNU General Public License for more +-- details. +-- + +create or replace package profiled_group +as + function new ( + group_id in profiled_groups.group_id%TYPE default null, + profile_provider in profiled_groups.profile_provider%TYPE, + group_name in groups.group_name%TYPE, + join_policy in groups.join_policy%TYPE default null, + email in parties.email%TYPE default null, + url in parties.url%TYPE default null, + object_type in acs_objects.object_type%TYPE default 'profiled_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 + ) return profiled_groups.group_id%TYPE; + + procedure del ( + group_id in profiled_groups.group_id%TYPE + ); +end profiled_group; +/ +show errors +create or replace package body profiled_group +as + function new ( + group_id in profiled_groups.group_id%TYPE default null, + profile_provider in profiled_groups.profile_provider%TYPE, + group_name in groups.group_name%TYPE, + join_policy in groups.join_policy%TYPE default null, + email in parties.email%TYPE default null, + url in parties.url%TYPE default null, + object_type in acs_objects.object_type%TYPE default 'profiled_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 + ) return profiled_groups.group_id%TYPE is + v_group_id profiled_groups.group_id%TYPE; + begin + v_group_id := acs_group.new( + group_id => profiled_group.new.group_id, + group_name => profiled_group.new.group_name, + join_policy => profiled_group.new.join_policy, + email => profiled_group.new.email, + url => profiled_group.new.url, + object_type => profiled_group.new.object_type, + creation_date => profiled_group.new.creation_date, + creation_user => profiled_group.new.creation_user, + creation_ip => profiled_group.new.creation_ip + ); + + insert + into profiled_groups + (group_id, profile_provider) + values + (v_group_id, profiled_group.new.profile_provider); + + return v_group_id; + end new; + + procedure del ( + group_id in profiled_groups.group_id%TYPE + ) is + begin + delete + from profiled_groups + where profiled_groups.group_id = profiled_group.del.group_id; + + acs_group.del(group_id); + end del; +end profiled_group; +/ +show errors