Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-update.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-update.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-update.sql 26 Mar 2001 05:20:15 -0000 1.1 @@ -0,0 +1,218 @@ +-- Data model to support content repository of the ArsDigita Community +-- System. This file contains DDL patches to the basic data model +-- that were incorporated after the code freeze. It makes it easier for +-- existing users to update their data models. + +-- Copyright (C) 1999-2000 ArsDigita Corporation +-- Authors: Karl Goldstein (karlg@arsdigita.com) + +-- $Id: content-update.sql,v 1.1 2001/03/26 05:20:15 danw Exp $ + +-- This is free software distributed under the terms of the GNU Public +-- License. Full text of the license is available from the GNU Project: +-- http://www.fsf.org/copyleft/gpl.html + +--set serveroutput on + +create function inline_0 () +returns integer as ' +begin + -- altering the constraint on cr_type_template_map + raise NOTICE ''Altering constraint on cr_type_template_map...''; + execute ''alter table cr_type_template_map drop constraint cr_type_template_map_pk''; + execute ''alter table cr_type_template_map add constraint cr_type_template_map_pk primary key (content_type, template_id, use_context)''; + execute ''VACUUM ANALYZE cr_type_template_map''; + + return 0; +end;' language 'plpgsql'; + +select inline_0 (); + +drop function inline_0 (); + +create function inline_1 () returns integer as ' +begin + + -- Set the workflow permission as child of admin + update acs_privilege_hierarchy + set privilege = ''cm_admin'' + where + privilege = ''cm_write'' + and + child_privilege = ''cm_item_workflow''; + + if not table_exists(''cr_doc_filter'') then + + raise NOTICE ''Creating CR_DOC_FILTER table for converting + documents to HTML''; + + execute ''create table cr_doc_filter ( + revision_id integer primary key, + content integer + )''; + + -- execute ''create index cr_doc_filter_index + -- on cr_doc_filter ( content ) indextype is ctxsys.context + -- parameters (''''FILTER content_filter_pref'''' )''; + + end if; + + if not table_exists(''cr_content_text'') then + + raise NOTICE ''Creating CR_CONTENT_TEXT table''; + + execute ''create table cr_content_text ( + revision_id integer primary key, + content text + )''; + + end if; + + + if not column_exists(''cr_folders'', ''has_child_folders'') then + + raise NOTICE ''Adding HAS_CHILD_FOLDERS column to CR_FOLDERS and updating the column based on selection criteria.''; + + execute ''create view cr_resolved_items as + select + i.parent_id, i.item_id, i.name, + case s.target_id is NULL then \\\\'\\\\'f\\\\'\\\\' else \\\\'\\\\'t\\\\'\\\\' is_symlink, + coalesce(s.target_id, i.item_id) resolved_id, s.label + from + cr_items i left outer join cr_symlinks s + on i.item_id = s.symlink_id''; + + execute ''alter table cr_folders add + has_child_folders char(1) + default \\\\'\\\\'f\\\\'\\\\' + constraint cr_folder_child_chk + check (has_child_folders in (\\\\'\\\\'t\\\\'\\\\',\\\\'\\\\'f\\\\'\\\\'))''; + + execute ''update cr_folders f set has_child_folders = + coalesce((select \\\\'\\\\'t\\\\'\\\\' from dual where exists + (select 1 from cr_folders f_child, cr_resolved_items r_child + where r_child.parent_id = f.folder_id + and f_child.folder_id = r_child.resolved_id)), \\\\'\\\\'f\\\\'\\\\')''; + end if; + + + + if not column_exists(''cr_keywords'', ''parent_id'') then + + raise NOTICE ''Adding PARENT_ID column to CR_KEYWORDS and updating the parent id from the context id''; + + execute ''alter table cr_keywords add + parent_id integer + constraint cr_keywords_hier + references cr_keywords''; + + execute ''update cr_keywords set parent_id = ( + select context_id from acs_objects + where object_id = keyword_id)''; + + end if; + + if not table_exists(''cr_text'') then + + raise NOTICE ''Creating CR_TEXT table for incoming text submissions...''; + + execute ''create table cr_text ( text varchar(4000) )''; + + -- For some reason a simple insert statement throws an error but this works + execute ''insert into cr_text values (NULL)''; + + end if; + + if not column_exists(''cr_items'', ''publish_status'') then + + raise NOTICE ''Adding PUBLISH_STATUS column to CR_ITEMS for tracking deployment status...''; + + execute ''alter table cr_items add + publish_status varchar(40) + constraint cr_items_pub_status_chk + check (publish_status in + (\\\\'\\\\'production\\\\'\\\\', \\\\'\\\\'ready\\\\'\\\\', \\\\'\\\\'live\\\\'\\\\', \\\\'\\\\'expired\\\\'\\\\'))''; + + execute ''update cr_items set publish_status = \\\\'\\\\'live\\\\'\\\\' + where live_revision is not null''; + + execute ''alter table cr_item_publish_audit add + old_status varchar(40)''; + execute ''alter table cr_item_publish_audit add + new_status varchar(40)''; + + end if; + + if not column_exists(''cr_items'', ''latest_revision'') then + + raise NOTICE ''Adding LATEST_REVISION column to CR_ITEMS for tracking revision status...''; + + execute ''alter table cr_items add + latest_revision integer + constraint cr_items_latest_fk + references cr_revisions''; + + execute ''update cr_items + set latest_revision = + content_item__get_latest_revision(item_id)''; + + end if; + + if not table_exists(''cr_release_periods'') then + + raise NOTICE ''Creating CR_RELEASE_PERIODS table for scheduled publishing...''; + + execute '' + create table cr_release_periods ( + item_id integer + constraint cr_release_periods_fk + references cr_items + constraint cr_release_periods_pk + primary key, + start_when timestamp default now(), + end_when timestamp default now() + (365 * 20) + )''; + + end if; + + if not table_exists(''cr_scheduled_release_log'') then + + raise NOTICE ''Creating CR_SCHEDULED_RELEASE_LOG table for auditing of scheduled publishing...''; + + execute '' + create table cr_scheduled_release_log ( + exec_date timestamp default now() not null, + items_released integer not null, + items_expired integer not null, + err_num integer, + err_msg varchar(500) + )''; + + end if; + + if not table_exists(''cr_scheduled_release_job'') then + + raise NOTICE ''Creating CR_SCHEDULED_RELEASE_JOB table for tracking database job for scheduled publishing...''; + + execute '' + create table cr_scheduled_release_job ( + job_id integer, + last_exec timestamp + )''; + + execute '' + insert into cr_scheduled_release_job values (NULL, now())''; + + end if; + + return null; +end;' language 'plpgsql'; + +select inline_1 (); + +drop function inline_1 (); + +-- show errors + +\i content-schedule.sql +