/packages/acs-content-repository/sql/oracle/content-update.sql

-- 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.2 2008/06/07 20:28:47 donb 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

begin
  -- altering the constraint on cr_type_template_map
  dbms_output.put_line('Altering constraint on cr_type_template_map...');
  execute immediate 'alter table cr_type_template_map drop constraint cr_type_template_map_pk';
  execute immediate 'alter table cr_type_template_map add constraint cr_type_template_map_pk primary key (content_type, template_id, use_context)';
  execute immediate 'analyze table cr_type_template_map compute statistics for all indexes';
end;
/
show errors

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_content_text') then

    dbms_output.put_line('Creating CR_CONTENT_TEXT table');

    execute immediate 'create global temporary table cr_content_text (
      revision_id        integer primary key,
      content            CLOB
    ) on commit delete rows';

  end if;


  if not column_exists('cr_folders', 'has_child_folders') then

    dbms_output.put_line('Adding HAS_CHILD_FOLDERS column to CR_FOLDERS' || 
      ' and updating the column based on selection criteria.');

    execute immediate 'create or replace view cr_resolved_items as
       select
         i.parent_id, i.item_id, i.name, 
         decode(s.target_id, NULL, ''f'', ''t'') is_symlink,
         nvl(s.target_id, i.item_id) resolved_id, s.label
       from
         cr_items i, cr_symlinks s
       where
         i.item_id = s.symlink_id (+)';

    execute immediate '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 immediate 'update cr_folders f set has_child_folders =
      nvl((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

    dbms_output.put_line('Adding PARENT_ID column to CR_KEYWORDS' || 
      ' and updating the parent id from the context id');

    execute immediate 'alter table cr_keywords add 
       parent_id      integer 
                      constraint cr_keywords_hier
                      references cr_keywords';

    execute immediate '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

    dbms_output.put_line('Creating CR_TEXT table ' ||
     'for incoming text submissions...');

    execute immediate 'create table cr_text ( text varchar2(4000) )';

    -- For some reason a simple insert statement throws an error but this works
    execute immediate 'insert into cr_text values (NULL)';

  end if;

  if not column_exists('cr_items', 'publish_status') then

    dbms_output.put_line('Adding PUBLISH_STATUS column to CR_ITEMS ' ||
     'for tracking deployment status...');

    execute immediate 'alter table cr_items add 
      publish_status varchar2(40) 
                     constraint cr_items_pub_status_chk
                     check (publish_status in 
                       (''production'', ''ready'', ''live'', ''expired''))';

    execute immediate 'update cr_items set publish_status = ''live''
                         where live_revision is not null';

    execute immediate 'alter table cr_item_publish_audit add
      old_status varchar2(40)';
    execute immediate 'alter table cr_item_publish_audit add
      new_status varchar2(40)';

  end if;

  if not column_exists('cr_items', 'latest_revision') then

    dbms_output.put_line('Adding LATEST_REVISION column to CR_ITEMS ' ||
     'for tracking revision status...');

    execute immediate 'alter table cr_items add 
      latest_revision integer
                      constraint cr_items_latest_fk
                      references cr_revisions';

    execute immediate 'update cr_items 
                       set latest_revision = 
                         content_item.get_latest_revision(item_id)';

  end if;

  if not table_exists('cr_release_periods') then

    dbms_output.put_line('Creating CR_RELEASE_PERIODS table ' ||
     'for scheduled publishing...');

    execute immediate '
      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	   date default sysdate,
	end_when	   date default sysdate + (365 * 20)
      )';

  end if;

  if not table_exists('cr_scheduled_release_log') then

    dbms_output.put_line('Creating CR_SCHEDULED_RELEASE_LOG table ' ||
     'for auditing of scheduled publishing...');

    execute immediate '
      create table cr_scheduled_release_log (
	exec_date        date default sysdate not null,
	items_released   integer not null,
	items_expired    integer not null,
	err_num          integer,
	err_msg          varchar2(500)
      )';

  end if;

  if not table_exists('cr_scheduled_release_job') then

    dbms_output.put_line('Creating CR_SCHEDULED_RELEASE_JOB table ' ||
     'for tracking database job for scheduled publishing...');

    execute immediate '
      create table cr_scheduled_release_job (
        job_id     integer,
        last_exec  date
      )';

    execute immediate '
      insert into cr_scheduled_release_job values (NULL, sysdate)';

  end if;

end;
/
show errors

create or replace trigger cr_text_tr
before insert on cr_text
for each row
begin

   raise_application_error(-20000,
        'Inserts are not allowed into cr_text.'
      );
end;
/
show errors

@@ content-schedule.sql