PageRenderTime 236ms CodeModel.GetById 24ms RepoModel.GetById 0ms app.codeStats 0ms

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

https://github.com/iuri/PWTI
SQL | 211 lines | 140 code | 58 blank | 13 comment | 12 complexity | 955ae8ab40c4db07d66daad0496d286d MD5 | raw file
  1. -- Data model to support content repository of the ArsDigita Community
  2. -- System. This file contains DDL patches to the basic data model
  3. -- that were incorporated after the code freeze. It makes it easier for
  4. -- existing users to update their data models.
  5. -- Copyright (C) 1999-2000 ArsDigita Corporation
  6. -- Authors: Karl Goldstein (karlg@arsdigita.com)
  7. -- $Id: content-update.sql,v 1.2 2008/06/07 20:28:47 donb Exp $
  8. -- This is free software distributed under the terms of the GNU Public
  9. -- License. Full text of the license is available from the GNU Project:
  10. -- http://www.fsf.org/copyleft/gpl.html
  11. set serveroutput on
  12. begin
  13. -- altering the constraint on cr_type_template_map
  14. dbms_output.put_line('Altering constraint on cr_type_template_map...');
  15. execute immediate 'alter table cr_type_template_map drop constraint cr_type_template_map_pk';
  16. execute immediate 'alter table cr_type_template_map add constraint cr_type_template_map_pk primary key (content_type, template_id, use_context)';
  17. execute immediate 'analyze table cr_type_template_map compute statistics for all indexes';
  18. end;
  19. /
  20. show errors
  21. begin
  22. -- Set the workflow permission as child of admin
  23. update acs_privilege_hierarchy
  24. set privilege = 'cm_admin'
  25. where
  26. privilege = 'cm_write'
  27. and
  28. child_privilege = 'cm_item_workflow';
  29. if not table_exists('cr_content_text') then
  30. dbms_output.put_line('Creating CR_CONTENT_TEXT table');
  31. execute immediate 'create global temporary table cr_content_text (
  32. revision_id integer primary key,
  33. content CLOB
  34. ) on commit delete rows';
  35. end if;
  36. if not column_exists('cr_folders', 'has_child_folders') then
  37. dbms_output.put_line('Adding HAS_CHILD_FOLDERS column to CR_FOLDERS' ||
  38. ' and updating the column based on selection criteria.');
  39. execute immediate 'create or replace view cr_resolved_items as
  40. select
  41. i.parent_id, i.item_id, i.name,
  42. decode(s.target_id, NULL, ''f'', ''t'') is_symlink,
  43. nvl(s.target_id, i.item_id) resolved_id, s.label
  44. from
  45. cr_items i, cr_symlinks s
  46. where
  47. i.item_id = s.symlink_id (+)';
  48. execute immediate 'alter table cr_folders add
  49. has_child_folders char(1)
  50. default ''f''
  51. constraint cr_folder_child_chk
  52. check (has_child_folders in (''t'',''f''))';
  53. execute immediate 'update cr_folders f set has_child_folders =
  54. nvl((select ''t'' from dual where exists
  55. (select 1 from cr_folders f_child, cr_resolved_items r_child
  56. where r_child.parent_id = f.folder_id
  57. and f_child.folder_id = r_child.resolved_id)), ''f'')';
  58. end if;
  59. if not column_exists('cr_keywords', 'parent_id') then
  60. dbms_output.put_line('Adding PARENT_ID column to CR_KEYWORDS' ||
  61. ' and updating the parent id from the context id');
  62. execute immediate 'alter table cr_keywords add
  63. parent_id integer
  64. constraint cr_keywords_hier
  65. references cr_keywords';
  66. execute immediate 'update cr_keywords set parent_id = (
  67. select context_id from acs_objects
  68. where object_id = keyword_id)';
  69. end if;
  70. if not table_exists('cr_text') then
  71. dbms_output.put_line('Creating CR_TEXT table ' ||
  72. 'for incoming text submissions...');
  73. execute immediate 'create table cr_text ( text varchar2(4000) )';
  74. -- For some reason a simple insert statement throws an error but this works
  75. execute immediate 'insert into cr_text values (NULL)';
  76. end if;
  77. if not column_exists('cr_items', 'publish_status') then
  78. dbms_output.put_line('Adding PUBLISH_STATUS column to CR_ITEMS ' ||
  79. 'for tracking deployment status...');
  80. execute immediate 'alter table cr_items add
  81. publish_status varchar2(40)
  82. constraint cr_items_pub_status_chk
  83. check (publish_status in
  84. (''production'', ''ready'', ''live'', ''expired''))';
  85. execute immediate 'update cr_items set publish_status = ''live''
  86. where live_revision is not null';
  87. execute immediate 'alter table cr_item_publish_audit add
  88. old_status varchar2(40)';
  89. execute immediate 'alter table cr_item_publish_audit add
  90. new_status varchar2(40)';
  91. end if;
  92. if not column_exists('cr_items', 'latest_revision') then
  93. dbms_output.put_line('Adding LATEST_REVISION column to CR_ITEMS ' ||
  94. 'for tracking revision status...');
  95. execute immediate 'alter table cr_items add
  96. latest_revision integer
  97. constraint cr_items_latest_fk
  98. references cr_revisions';
  99. execute immediate 'update cr_items
  100. set latest_revision =
  101. content_item.get_latest_revision(item_id)';
  102. end if;
  103. if not table_exists('cr_release_periods') then
  104. dbms_output.put_line('Creating CR_RELEASE_PERIODS table ' ||
  105. 'for scheduled publishing...');
  106. execute immediate '
  107. create table cr_release_periods (
  108. item_id integer
  109. constraint cr_release_periods_fk
  110. references cr_items
  111. constraint cr_release_periods_pk
  112. primary key,
  113. start_when date default sysdate,
  114. end_when date default sysdate + (365 * 20)
  115. )';
  116. end if;
  117. if not table_exists('cr_scheduled_release_log') then
  118. dbms_output.put_line('Creating CR_SCHEDULED_RELEASE_LOG table ' ||
  119. 'for auditing of scheduled publishing...');
  120. execute immediate '
  121. create table cr_scheduled_release_log (
  122. exec_date date default sysdate not null,
  123. items_released integer not null,
  124. items_expired integer not null,
  125. err_num integer,
  126. err_msg varchar2(500)
  127. )';
  128. end if;
  129. if not table_exists('cr_scheduled_release_job') then
  130. dbms_output.put_line('Creating CR_SCHEDULED_RELEASE_JOB table ' ||
  131. 'for tracking database job for scheduled publishing...');
  132. execute immediate '
  133. create table cr_scheduled_release_job (
  134. job_id integer,
  135. last_exec date
  136. )';
  137. execute immediate '
  138. insert into cr_scheduled_release_job values (NULL, sysdate)';
  139. end if;
  140. end;
  141. /
  142. show errors
  143. create or replace trigger cr_text_tr
  144. before insert on cr_text
  145. for each row
  146. begin
  147. raise_application_error(-20000,
  148. 'Inserts are not allowed into cr_text.'
  149. );
  150. end;
  151. /
  152. show errors
  153. @@ content-schedule.sql