text: -- 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.4 2015/12/04 13:49:58 cvs 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 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
  
  