-- 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.1.1 2002/07/09 17:34:56 rmello 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 -- FIXME: drop constraint doesn't work on postgresql 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\\\'\\\' end as 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 boolean default \\\'\\\'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 text default \\\'\\\' not null )''; -- 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 column old_status varchar(40)''; execute ''alter table cr_item_publish_audit add column 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) default \\\'\\\' not null )''; 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