-- 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.11 2018/11/01 08:43:43 gustafn 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 CREATE OR REPLACE 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 OR REPLACE 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_revision_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 timestamptz default current_timestamp, end_when timestamptz default current_timestamp + interval ''20 years'' )'; 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 timestamptz default current_timestamp 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 timestamptz )'; 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