Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-update.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-update.sql,v diff -u -r1.8 -r1.9 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-update.sql 4 Jun 2006 00:45:23 -0000 1.8 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-update.sql 7 Jul 2011 10:46:02 -0000 1.9 @@ -14,197 +14,198 @@ --set serveroutput on -- FIXME: drop constraint doesn't work on postgresql -create function inline_0 () -returns integer as ' -begin +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''; + 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'; +END; +$$ LANGUAGE plpgsql; -- select inline_0 (); drop function inline_0 (); -create function inline_1 () returns integer as ' -begin +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'' + set privilege = 'cm_admin' where - privilege = ''cm_write'' + privilege = 'cm_write' and - child_privilege = ''cm_item_workflow''; + child_privilege = 'cm_item_workflow'; - if not table_exists(''cr_doc_filter'') then + if not table_exists('cr_doc_filter') then - raise NOTICE ''Creating CR_DOC_FILTER table for converting - documents to HTML''; + raise NOTICE 'Creating CR_DOC_FILTER table for converting + documents to HTML'; - execute ''create table cr_doc_filter ( + execute 'create table cr_doc_filter ( revision_id integer primary key, content integer - )''; + )'; - -- execute ''create index cr_doc_filter_index + -- execute 'create index cr_doc_filter_index -- on cr_doc_filter ( content ) indextype is ctxsys.context - -- parameters (''''FILTER content_filter_pref'''' )''; + -- parameters (''FILTER content_filter_pref'' )'; end if; - if not table_exists(''cr_content_text'') then + if not table_exists('cr_content_text') then - raise NOTICE ''Creating CR_CONTENT_TEXT table''; + raise NOTICE 'Creating CR_CONTENT_TEXT table'; - execute ''create table cr_content_text ( + 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 + 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.''; + 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 + 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, + 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''; + on i.item_id = s.symlink_id'; - execute ''alter table cr_folders add + execute 'alter table cr_folders add has_child_folders boolean - default \\\'\\\'f\\\'\\\'''; + default ''''f'''''; - execute ''update cr_folders f set has_child_folders = - coalesce((select \\\'\\\'t\\\'\\\' from dual where exists + 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\\\'\\\')''; + and f_child.folder_id = r_child.resolved_id)), ''''f'''')'; end if; - if not column_exists(''cr_keywords'', ''parent_id'') then + 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''; + raise NOTICE 'Adding PARENT_ID column to CR_KEYWORDS and updating the parent id from the context id'; - execute ''alter table cr_keywords add + execute 'alter table cr_keywords add parent_id integer constraint cr_keywords_hier - references cr_keywords''; + references cr_keywords'; - execute ''update cr_keywords set parent_id = ( + execute 'update cr_keywords set parent_id = ( select context_id from acs_objects - where object_id = keyword_id)''; + where object_id = keyword_id)'; end if; - if not table_exists(''cr_text'') then + if not table_exists('cr_text') then - raise NOTICE ''Creating CR_TEXT table for incoming text submissions...''; + raise NOTICE 'Creating CR_TEXT table for incoming text submissions...'; - execute ''create table cr_text ( text text default \\\'\\\' not null )''; + 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)''; + execute 'insert into cr_text values (NULL)'; end if; - if not column_exists(''cr_items'', ''publish_status'') then + if not column_exists('cr_items', 'publish_status') then - raise NOTICE ''Adding PUBLISH_STATUS column to CR_ITEMS for tracking deployment status...''; + raise NOTICE 'Adding PUBLISH_STATUS column to CR_ITEMS for tracking deployment status...'; - execute ''alter table cr_items add + execute 'alter table cr_items add publish_status varchar(40) constraint cr_items_pub_status_chk check (publish_status in - (\\\'\\\'production\\\'\\\', \\\'\\\'ready\\\'\\\', \\\'\\\'live\\\'\\\', \\\'\\\'expired\\\'\\\'))''; + (''''production'''', ''''ready'''', ''''live'''', ''''expired''''))'; - execute ''update cr_items set publish_status = \\\'\\\'live\\\'\\\' - where live_revision is not null''; + 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)''; + 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 + if not column_exists('cr_items', 'latest_revision') then - raise NOTICE ''Adding LATEST_REVISION column to CR_ITEMS for tracking revision status...''; + raise NOTICE 'Adding LATEST_REVISION column to CR_ITEMS for tracking revision status...'; - execute ''alter table cr_items add + execute 'alter table cr_items add latest_revision integer constraint cr_items_latest_fk - references cr_revisions''; + references cr_revisions'; - execute ''update cr_items + execute 'update cr_items set latest_revision = - content_item__get_latest_revision(item_id)''; + content_item__get_latest_revision(item_id)'; end if; - if not table_exists(''cr_release_periods'') then + if not table_exists('cr_release_periods') then - raise NOTICE ''Creating CR_RELEASE_PERIODS table for scheduled publishing...''; + raise NOTICE 'Creating CR_RELEASE_PERIODS table for scheduled publishing...'; - execute '' + 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_when timestamptz default current_timestamp + interval ''20 years'' + )'; end if; - if not table_exists(''cr_scheduled_release_log'') then + if not table_exists('cr_scheduled_release_log') then - raise NOTICE ''Creating CR_SCHEDULED_RELEASE_LOG table for auditing of scheduled publishing...''; + raise NOTICE 'Creating CR_SCHEDULED_RELEASE_LOG table for auditing of scheduled publishing...'; - execute '' + 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 - )''; + err_msg varchar(500) default '''' not null + )'; end if; - if not table_exists(''cr_scheduled_release_job'') then + if not table_exists('cr_scheduled_release_job') then - raise NOTICE ''Creating CR_SCHEDULED_RELEASE_JOB table for tracking database job for scheduled publishing...''; + raise NOTICE 'Creating CR_SCHEDULED_RELEASE_JOB table for tracking database job for scheduled publishing...'; - execute '' + execute ' create table cr_scheduled_release_job ( job_id integer, last_exec timestamptz - )''; + )'; - execute '' - insert into cr_scheduled_release_job values (NULL, now())''; + execute ' + insert into cr_scheduled_release_job values (NULL, now())'; end if; return null; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- select inline_1 ();