Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-extlink.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-extlink.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-extlink.sql 25 Mar 2001 21:18:51 -0000 1.1 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-extlink.sql 28 Mar 2001 02:15:10 -0000 1.2 @@ -43,16 +43,16 @@ end if; v_extlink_id := content_item__new( - name => v_name, - parent_id => content_extlink.new__parent_id, - item_id => content_extlink.new__extlink_id, + v_name, + new__parent_id, + new__extlink_id, null, - creation_date => content_extlink.new__creation_date, - creation_user => content_extlink.new__creation_user, + new__creation_date, + new__creation_user, null, - creation_ip => content_extlink.new__creation_ip, + new__creation_ip, ''content_item'', - content_type => ''content_extlink'', + ''content_extlink'', null, null, ''text/plain'', Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-folder.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-folder.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-folder.sql 25 Mar 2001 22:11:59 -0000 1.1 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-folder.sql 28 Mar 2001 02:15:10 -0000 1.2 @@ -36,7 +36,7 @@ end if; -- parent_id = 0 means that this is a mount point - if new__parent_id ^= 0 and + if new__parent_id != 0 and content_folder__is_registered(new__parent_id,''content_folder'') = ''f'' then raise EXCEPTION ''-20000: This folder does not allow subfolders to be created''; @@ -101,14 +101,15 @@ -- check if the folder contains any items - select count(*) into v_count from cr_items where parent_id = folder_id; + select count(*) into v_count from cr_items + where parent_id = delete__folder_id; if v_count > 0 then - raise EXCEPTION ''-20000: Folder ID % (%) cannot be deleted because it is not empty.'', folder_id, content_item__get_path(delete__folder_id); + raise EXCEPTION ''-20000: Folder ID % (%) cannot be deleted because it is not empty.'', delete__folder_id, content_item__get_path(delete__folder_id); end if; PERFORM content_folder__unregister_content_type( - content_folder.delete__folder_id, + delete__folder_id, ''content_revision'', ''t'' ); @@ -193,7 +194,7 @@ or folder_id = move__folder_id; - if v_valid_folders_p ^= 2 then + if v_valid_folders_p != 2 then raise ''-20000: content_folder.move - Not valid folder(s)''; end if; @@ -210,7 +211,7 @@ raise EXCEPTION ''-20000: content_folder.move - Destination folder is subfolder''; end if; - if content_folder__is_registered(move__target_folder_id,''content_folder'') ^= ''t'' then + if content_folder__is_registered(move__target_folder_id,''content_folder'') != ''t'' then raise EXCEPTION ''-20000: content_folder.move - Destination folder does not allow subfolders''; end if; @@ -280,11 +281,12 @@ or copy__folder_id = content_template__get_root_folder() or copy__target_folder_id = copy__folder_id or v_current_folder_id = copy__target_folder_id then + v_valid_folders_p := 0; end if; if v_valid_folders_p = 2 then - if content_folder__is_sub_folder(copy__folder_id, copy__target_folder_id) ^= ''t'' then + if content_folder__is_sub_folder(copy__folder_id, copy__target_folder_id) != ''t'' then -- get the source folder info select @@ -351,10 +353,9 @@ -- function is_folder create function content_folder__is_folder (integer) -returns char as ' +returns boolean as ' declare item_id alias for $1; - v_folder_p varchar(1) begin select 1 from cr_folders @@ -382,6 +383,7 @@ if is_sub_folder__folder_id = content_item__get_root_folder() or is_sub_folder__folder_id = content_template__get_root_folder() then + v_sub_folder_p := ''t''; end if; @@ -398,7 +400,7 @@ exit when v_parent_id = is_sub_folder__folder_id; end LOOP; - if v_parent_id ^= 0 then + if v_parent_id != 0 then v_sub_folder_p := ''t''; end if; @@ -428,13 +430,13 @@ -- procedure register_content_type -create function content_folder__register_content_type (integer,varchar,varchar) +create function content_folder__register_content_type (integer,varchar,boolean) returns integer as ' declare register_content_type__folder_id alias for $1; register_content_type__content_type alias for $2; register_content_type__include_subtypes alias for $3; - v_is_registered varchar(100); + v_is_registered varchar(100); begin if register_content_type__include_subtypes = ''f'' then @@ -465,7 +467,7 @@ from acs_object_types where - object_type ^= ''acs_object'' + object_type <> ''acs_object'' and not exists (select 1 from cr_folder_type_map where folder_id = register_content_type__folder_id @@ -482,7 +484,7 @@ -- procedure unregister_content_type -create function content_folder__unregister_content_type (integer,varchar,varchar) +create function content_folder__unregister_content_type (integer,varchar,boolean) returns integer as ' declare unregister_content_type__folder_id alias for $1; @@ -499,7 +501,7 @@ where folder_id = unregister_content_type__folder_id and content_type in (select object_type from acs_object_types - where object_type ^= ''acs_object'' + where object_type <> ''acs_object'' connect by prior object_type = supertype start with object_type = unregister_content_type__content_type); @@ -541,7 +543,7 @@ from acs_object_types where - object_type ^= 'acs_object' + object_type <> 'acs_object' connect by prior object_type = supertype start with @@ -594,7 +596,7 @@ -- if the folder is a symlink, resolve it if content_symlink__is_symlink(get_index_page__folder_id) = ''t'' then - v_folder_id := content_symlink.resolve(get_index_page__folder_id); + v_folder_id := content_symlink__resolve(get_index_page__folder_id); else v_folder_id := get_index_page__folder_id; end if; @@ -613,7 +615,7 @@ ''content_folder'') = ''f'' and content_item__is_subclass( - content_item__get_content_type(content_symlink.resolve(item_id)), + content_item__get_content_type(content_symlink__resolve(item_id)), ''content_template'') = ''f''; if NOT FOUND then Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql 25 Mar 2001 19:47:11 -0000 1.1 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql 28 Mar 2001 02:15:10 -0000 1.2 @@ -39,7 +39,7 @@ item_id = get_root_folder__item_id; if NOT FOUND then - raise EXCEPTION '' -20000: Could not find a root folder for item ID %. Either the item does not exist or its parent value is corrupted.'', item_id; + raise EXCEPTION '' -20000: Could not find a root folder for item ID %. Either the item does not exist or its parent value is corrupted.'', get_root_folder__item_id; end if; end if; @@ -92,14 +92,14 @@ -- place the item in the context of the pages folder if no -- context specified - if parent_id is null then + if new__parent_id is null then v_parent_id := content_item_globals.c_root_folder_id; else v_parent_id := new__parent_id; end if; -- Determine context_id - if context_id is null then + if new__context_id is null then v_context_id := v_parent_id; else v_context_id := new__context_id; @@ -108,14 +108,14 @@ if v_parent_id = 0 or content_folder__is_folder(v_parent_id) = ''t'' then - if v_parent_id ^= 0 and + if v_parent_id != 0 and content_folder__is_registered( v_parent_id, new__content_type, ''f'') = ''f'' then raise EXCEPTION ''-20000: This item\\\\'s content type % is not registered to this folder %'', new__content_type, v_parent_id; end if; - else if v_parent_id ^= 0 then + else if v_parent_id != 0 then select object_type into v_parent_type from acs_objects where object_id = v_parent_id; @@ -124,8 +124,8 @@ raise EXCEPTION ''-20000: Invalid parent ID % specified in content_item.new'', v_parent_id; end if; - if is_subclass(v_parent_type, ''content_item'') = ''t'' and - is_valid_child(v_parent_id, new__content_type) = ''f'' then + if content_item__is_subclass(v_parent_type, ''content_item'') = ''t'' and + content_item__is_valid_child(v_parent_id, new__content_type) = ''f'' then raise EXCEPTION ''-20000: This item\\\\'s content type % is not allowed in this container %'', new__content_type, v_parent_id); end if; @@ -156,7 +156,7 @@ ); -- if the parent is not a folder, insert into cr_child_rels - if v_parent_id ^= 0 and + if v_parent_id != 0 and content_folder__is_folder(v_parent_id) = ''f'' and content_item__is_valid_child(v_parent_id, new__content_type) = ''t'' then @@ -208,11 +208,11 @@ null, new__creation_date, new__creation_user, - content_item.new__creation_ip, + new__creation_ip, ); - else if content_item.new__title is not null or - content_item.new__text is not null then + else if new__title is not null or + new__text is not null then v_revision_id := content_revision__new( v_title, @@ -277,7 +277,7 @@ -- function is_publishable create function content_item__is_publishable (integer) -returns char as ' +returns boolean as ' declare is_publishable__item_id alias for $1; v_child_count integer; @@ -371,10 +371,10 @@ where workflow_key = ''publishing_wf'' and - object_id = is_publishable.item_id; + object_id = is_publishable__item_id; LOOP - if v_pub_wf.state ^= ''finished'' then + if v_pub_wf.state != ''finished'' then return ''f''; end if; end loop; @@ -390,7 +390,7 @@ -- function is_valid_child create function content_item__is_valid_child (integer,varchar) -returns char as ' +returns boolean as ' declare is_valid_child__item_id alias for $1; is_valid_child__content_type alias for $2; @@ -620,7 +620,7 @@ get_id__resolve_index alias for $3; v_item_path varchar; v_root_folder_id cr_items.item_id%TYPE; - parent_id integer; + get_id__parent_id integer; child_id integer; start_pos integer default 1; end_pos integer; @@ -639,10 +639,10 @@ -- Remove leading, trailing spaces, leading slashes v_item_path := rtrim(ltrim(trim(get_id__item_path), ''/''), ''/''); - parent_id := v_root_folder_id; + get_id__parent_id := v_root_folder_id; -- if parent_id is a symlink, resolve it - parent_id := content_symlink__resolve(parent_id); + get_id__parent_id := content_symlink__resolve(get_id__parent_id); LOOP -- FIXME: this use of instr in oracle code seems incorrect. @@ -664,12 +664,16 @@ and name = item_name; + if NOT FOUND then + return null; + end if; + exit when end_pos = 0; - parent_id := child_id; + get_id__parent_id := child_id; -- if parent_id is a symlink, resolve it - parent_id := content_symlink__resolve(parent_id); + get_id__parent_id := content_symlink__resolve(get_id__parent_id); start_pos := end_pos + 1; v_item_path := substr(v_item_path, start_pos); @@ -792,8 +796,8 @@ create function content_item__get_virtual_path (integer,integer) returns varchar as ' declare - get_virtual_path_item_id alias for $1; - get_virtual_path_root_folder_id alias for $2; + get_virtual_path__item_id alias for $1; + get_virtual_path__root_folder_id alias for $2; v_path varchar; v_item_id cr_items.item_id%TYPE; v_is_folder boolean; @@ -835,11 +839,13 @@ select content into blob_loc from cr_revisions where revision_id = v_revision; + + if NOT FOUND then + raise EXCEPTION ''-20000: No live revision for content item % in content_item.write_to_file.'', item_id; + end if; PERFORM blob_to_file(root_path || content_item__get_path(item_id), blob_loc); - raise EXCEPTION ''-20000: No live revision for content item% in content_item.write_to_file.'', item_id; - return 0; end;' language 'plpgsql'; @@ -855,12 +861,11 @@ begin -- register template if it is not already registered - insert into cr_item_template_map ( - template_id, item_id, use_context - ) select - register_template__template_id, - register_template__item_id, - register_template__use_context + insert into cr_item_template_map + select + register_template__template_id as template_id, + register_template__item_id as item_id, + register_template__use_context as use_context from dual where @@ -888,18 +893,19 @@ begin - if use_context is null and template_id is null then + if unregister_template__use_context is null and + unregister_template__template_id is null then delete from cr_item_template_map where item_id = unregister_template__item_id; - else if use_context is null then + else if unregister_template__use_context is null then delete from cr_item_template_map where template_id = unregister_template__template_id and item_id = unregister_template__item_id; - else if template_id is null then + else if unregister_template__template_id is null then delete from cr_item_template_map where item_id = unregister_template__item_id @@ -1404,11 +1410,11 @@ if v_content_type = ''content_folder'' then select label into v_title from cr_folders where folder_id = get_title__item_id; - elsif v_content_type = ''content_symlink'' then + else if v_content_type = ''content_symlink'' then select label into v_title from cr_symlinks where symlink_id = get_title__item_id; else - if is_live then + if get_title__is_live then select title into v_title from @@ -1427,7 +1433,7 @@ and r.revision_id = i.latest_revision; end if; - end if; + end if; end if; return v_title; @@ -1475,7 +1481,7 @@ -- function is_subclass create function content_item__is_subclass (varchar,varchar) -returns char as ' +returns boolean as ' declare is_subclass__object_type alias for $1; is_subclass__supertype alias for $2; @@ -1538,7 +1544,7 @@ raise EXCEPTION ''-20000: There is no registered relation type matching this item relation.''; end if; - if relate__item_id ^= relate__object_id then + if relate__item_id != relate__object_id then -- check that these two items are not related already --dbms_output.put_line( ''checking if the items are already related...''); @@ -1569,8 +1575,12 @@ if v_exists <> 1 then --dbms_output.put_line( ''creating new relationship...''); v_rel_id := acs_object__new( - object_type => relation_type, - context_id => item_id + null, + relate__relation_type, + now(), + null, + null, + relate__item_id ); insert into cr_item_rels ( rel_id, item_id, related_object_id, order_n, relation_tag Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-keyword.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-keyword.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-keyword.sql 25 Mar 2001 22:47:14 -0000 1.1 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-keyword.sql 28 Mar 2001 02:15:10 -0000 1.2 @@ -258,7 +258,7 @@ end if; -- Tried none, up and down - must be an invalid parameter - raise EXCEPTION ''-20000: The recurse parameter to content_keyword.is_assigned should be \\\\'none\\\\', \\\\'up\\\\' or \\\\'down\\\\'''; + raise EXCEPTION ''-20000: The recurse parameter to content_keyword.is_assigned should be \\\'none\\\', \\\'up\\\' or \\\'down\\\'''; return null; end;' language 'plpgsql'; Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-perms.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/Attic/content-perms.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-perms.sql 27 Mar 2001 02:02:31 -0000 1.1 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-perms.sql 28 Mar 2001 02:15:10 -0000 1.2 @@ -84,22 +84,30 @@ if inherit_permissions__child_creator_id is not null then -- Grant cm_write and cm_perm to the child creator - if content_permission.permission_p ( - child_object_id, child_creator_id, ''cm_perm'' + if content_permission__permission_p ( + inherit_permissions__child_object_id, + inherit_permissions__child_creator_id, + ''cm_perm'' ) != ''t'' then -- Turn off inheritance and grant permission update acs_objects set security_inherit_p = ''f'' where object_id = inherit_permissions__child_object_id; PERFORM acs_permission__grant_permission ( - child_object_id, child_creator_id, ''cm_perm'' + inherit_permissions__child_object_id, + inherit_permissions__child_creator_id, + ''cm_perm'' ); end if; if content_permission__permission_p ( - child_object_id, child_creator_id, ''cm_write'' + inherit_permissions__child_object_id, + inherit_permissions__child_creator_id, + ''cm_write'' ) != ''t'' then PERFORM acs_permission__grant_permission ( - child_object_id, child_creator_id, ''cm_write'' + inherit_permissions__child_object_id, + inherit_permissions__child_creator_id, + ''cm_write'' ); end if; end if; @@ -179,7 +187,9 @@ -- If the permission is already granted, do nothing if content_permission__permission_p ( - object_id, grantee_id, privilege + grant_permission_h__object_id, + grant_permission_h__grantee_id, + grant_permission_h__privilege ) = ''t'' then return null; end if; @@ -188,17 +198,21 @@ update acs_objects set security_inherit_p = ''f'' where object_id = grant_permission_h__object_id; - PERFORM acs_permission__grant_permission(object_id, grantee_id, privilege); + PERFORM acs_permission__grant_permission(grant_permission_h__object_id, + grant_permission_h__grantee_id, + grant_permission_h__privilege); -- Revoke the children - they are no longer relevant for v_rec in select descendant from acs_privilege_descendant_map where privilege = grant_permission_h__privilege and descendant <> grant_permission_h__privilege; LOOP - PERFORM acs_permission__revoke_permission(grant_permission_h__object_id, - grant_permission_h__grantee_id, - v_rec.descendant); + PERFORM acs_permission__revoke_permission( + grant_permission_h__object_id, + grant_permission_h__grantee_id, + v_rec.descendant + ); end LOOP; return 0; @@ -231,10 +245,12 @@ and content_item__is_subclass (o.object_type, grant_permission__object_type) = ''t'' LOOP - -- Grant the parent and revoke the children, since we don''t need them + -- Grant the parent and revoke the children, since we do not need them -- anymore PERFORM content_permission__grant_permission_h ( - v_rec.object_id, recepient_id, privilege + v_rec.object_id, + grant_permission__recepient_id, + grant_permission__privilege ); exit when grant_permission__is_recursive = ''f''; end loop; @@ -300,13 +316,15 @@ content_item__is_subclass(o.object_type, revoke_permission__object_type) = ''t'' LOOP PERFORM content_permission__revoke_permission_h ( - v_rec.object_id, revoke_permission__revokee_id, revoke_permission__privilege + v_rec.object_id, + revoke_permission__revokee_id, + revoke_permission__privilege ); exit when revoke_permission__is_recursive = ''f''; end loop; - return 0; + return 0; end;' language 'plpgsql'; Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-revision.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-revision.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-revision.sql 25 Mar 2001 20:47:43 -0000 1.1 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-revision.sql 28 Mar 2001 02:15:10 -0000 1.2 @@ -131,10 +131,10 @@ cols := cols || '', '' || attr_rec.attribute_name; end loop; - execute ''insert into '' || v_table_name || - '' ( '' || v_id_column || cols || '' ) ( select '' || copy_id || cols || - '' from '' || v_table_name || '' where '' || v_id_column || '' = '' || - copy_attributes__revision_id || '')''; + execute ''insert into '' || v_table_name || '' select '' || copy_id || + '' as '' || v_id_column || cols || '' from '' || + v_table_name || '' where '' || v_id_column || '' = '' || + copy_attributes__revision_id; return 0; end;' language 'plpgsql'; @@ -205,9 +205,9 @@ from acs_object_types where - object_type ^= 'acs_object' + object_type <> ''acs_object'' and - object_type ^= 'content_revision' + object_type <> ''content_revision'' connect by prior supertype = object_type start with @@ -252,20 +252,26 @@ -- Recalculate latest revision if v_latest_revision = delete__revision_id then - select r.revision_id into v_latest_revision - from cr_revisions r, acs_objects o - where o.object_id = r.revision_id - and r.item_id = v_item_id - and r.revision_id <> delete__revision_id - order by o.creation_date desc; + for v_rec in + select r.revision_id into v_latest_revision + from cr_revisions r, acs_objects o + where o.object_id = r.revision_id + and r.item_id = v_item_id + and r.revision_id <> delete__revision_id + order by o.creation_date desc + LOOP - if NOT FOUND then - v_latest_revision := null; - end if; - - update cr_items set latest_revision = v_latest_revision - where item_id = v_item_id; + v_latest_revision := v_rec.revision_id; + exit; + end LOOP; end if; + + if NOT FOUND then + v_latest_revision := null; + end if; + + update cr_items set latest_revision = v_latest_revision + where item_id = v_item_id; -- Clear live revision if v_live_revision = delete__revision_id then @@ -289,10 +295,10 @@ create function content_revision__get_number (integer) returns number as ' declare - revision_id alias for $1; - v_number integer; - v_revision cr_revisions.revision_id%TYPE; - row_count integer default 0; + get_number__revision_id alias for $1; + v_number integer; + v_revision cr_revisions.revision_id%TYPE; + row_count integer default 0; begin for rev_cur in select revision_id @@ -308,7 +314,7 @@ LOOP row_count := row_count + 1; if v_revision = get_number__revision_id then - v_number := rowcount; + v_number := row_count; exit; end if; end LOOP; @@ -322,13 +328,14 @@ create function content_revision__index_attributes (integer) returns integer as ' declare - content_revision__revision_id alias for $1; + index_attributes__revision_id alias for $1; clob_loc text; v_revision_id cr_revisions.revision_id%TYPE; begin insert into cr_revision_attributes - select revision_id + select index_attributes__revision_id as revision_id, + clob_loc as attributes -- FIXME: need to find a way to deal with these xml calls v_revision_id := write_xml(revision_id, clob_loc); @@ -359,17 +366,19 @@ -- function export_xml create function content_revision__export_xml (integer) -returns as ' +returns integer as ' declare revision_id alias for $1; clob_loc clob; v_doc_id cr_xml_docs.doc_id%TYPE; v_revision_id cr_revisions.revision_id%TYPE; begin + v_doc_id := cr_xml_doc_seq.nextval; + insert into cr_xml_docs (doc_id, doc) - values (cr_xml_doc_seq.nextval, empty_clob()) - returning doc_id, doc into v_doc_id, clob_loc; + values (v_doc_id, empty_clob()); + -- FIXME: need a way to deal with this xml call. v_revision_id := write_xml(revision_id, clob_loc); @@ -382,9 +391,9 @@ create function content_revision__to_html (integer) returns integer as ' declare - revision_id alias for $1; - tmp_clob text; - blob_loc integer; + to_html__revision_id alias for $1; + tmp_clob text; + blob_loc integer; begin -- what is this? FIXME @@ -400,7 +409,7 @@ PERFORM clob_to_blob(tmp_clob, blob_loc); - PERFORM dbms_lob.freetemporary(tmp_clob); + PERFORM dbms_lob__freetemporary(tmp_clob); return 0; end;' language 'plpgsql'; Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-schedule.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-schedule.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-schedule.sql 26 Mar 2001 05:36:37 -0000 1.1 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-schedule.sql 28 Mar 2001 02:15:10 -0000 1.2 @@ -38,10 +38,10 @@ exec__items_expired integer default 0; exec__err_num integer; -- sqlcode exec__err_msg varchar; -- substr(sqlerrm, 1, 500); - exec__item_rec record; + item_rec record; begin - select exec__last_exec into last_exec from cr_scheduled_release_job; + select last_exec into exec__last_exec from cr_scheduled_release_job; for item_rec in select p.item_id, live_revision @@ -100,11 +100,8 @@ return 0; end;' language 'plpgsql'; -select inline_0 (); -drop function inline_0 (); - -- show errors -- initialize the scheduled publication job @@ -117,27 +114,27 @@ begin select job into v_job_id from user_jobs - where what = 'cr_scheduled_release_exec; + where what = ''cr_scheduled_release_exec;''; if NOT FOUND then - dbms_output.put_line(' - Submitting job to process scheduled updates to live content...'); + raise NOTICE '' + Submitting job to process scheduled updates to live content...''; - dbms_job.submit( + dbms_job__submit( job => v_job_id, - what => 'cr_scheduled_release_exec;', - next_date => sysdate, - interval => 'sysdate + ' || (interval/24/60) + what => ''cr_scheduled_release_exec;'', + next_date => now(), + interval => ''now() + '' || (interval/24/60) ); update cr_scheduled_release_job set job_id = v_job_id; else - dbms_job.change( + dbms_job__change( job => v_job_id, - what => 'cr_scheduled_release_exec;', - next_date => sysdate, - interval => 'sysdate + ' || (interval/24/60) + what => ''cr_scheduled_release_exec;'', + next_date => now(), + interval => ''now() + '' || (interval/24/60) ); end if; Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-symlink.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-symlink.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-symlink.sql 25 Mar 2001 21:11:23 -0000 1.1 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-symlink.sql 28 Mar 2001 02:15:10 -0000 1.2 @@ -55,19 +55,19 @@ -- PASSED ALL CHECKS -- -- Select default name if the name is null - if name is null then + if new__name is null then select - ''symlink_to_'' || name into v_name + ''symlink_to_'' || new__name into v_name from cr_items where - item_id = target_id; + item_id = new__target_id; if NOT FOUND then v_name := null; end if; else - v_name := name; + v_name := new__name; end if; -- Select default label if the label is null @@ -146,15 +146,15 @@ create function content_symlink__copy (integer,integer,integer,varchar) returns integer as ' declare - copy_symlink_id alias for $1; - copy_target_folder_id alias for $2; - copy_creation_user alias for $3; - copy_creation_ip alias for $4; - v_current_folder_id cr_folders.folder_id%TYPE; - v_name cr_items.name%TYPE; - v_target_id cr_items.item_id%TYPE; - v_label cr_symlinks.label%TYPE; - v_symlink_id cr_symlinks.symlink_id%TYPE; + copy__symlink_id alias for $1; + copy__target_folder_id alias for $2; + copy__creation_user alias for $3; + copy__creation_ip alias for $4; + v_current_folder_id cr_folders.folder_id%TYPE; + v_name cr_items.name%TYPE; + v_target_id cr_items.item_id%TYPE; + v_label cr_symlinks.label%TYPE; + v_symlink_id cr_symlinks.symlink_id%TYPE; begin if content_folder__is_folder(copy__target_folder_id) = ''t'' then @@ -168,7 +168,7 @@ item_id = copy__symlink_id; -- can''t copy to the same folder - if copy__target_folder_id ^= v_current_folder_id then + if copy__target_folder_id != v_current_folder_id then select i.name, content_symlink__resolve(i.item_id), s.label @@ -267,6 +267,4 @@ case when s.target_id is NULL then 'f' else 't' end as is_symlink, coalesce(s.target_id, i.item_id) as resolved_id, s.label from - cr_items i left outer join cr_symlinks s - where - i.item_id = s.symlink_id; + cr_items i left outer join cr_symlinks s on (i.item_id = s.symlink_id); Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-type.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-type.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-type.sql 25 Mar 2001 15:58:30 -0000 1.1 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-type.sql 28 Mar 2001 02:15:10 -0000 1.2 @@ -29,13 +29,13 @@ and use_context = new.use_context and - template_id ^= new.template_id + template_id <> new.template_id and is_default = ''t''; end if; - return; return new; + end;' language 'plpgsql'; create trigger cr_type_template_map_tr before insert on cr_type_template_map @@ -88,7 +88,7 @@ create_type__name_method ); - PERFORM refresh_view(create_type__content_type); + PERFORM content_type__refresh_view(create_type__content_type); return 0; end;' language 'plpgsql'; @@ -270,6 +270,7 @@ PERFORM acs_attribute__drop_attribute(drop_attribute__content_type, drop_attribute__attribute_name); + -- FIXME: postgresql does not support drop column. -- Drop the column if neccessary if drop_attribute__drop_column then execute ''alter table '' || v_table || '' drop column '' || @@ -280,7 +281,7 @@ -- v_table || ''.'' || attribute_name || '' in content_type.drop_attribute''); end if; - PERFORM content_type__refresh_view(content_type); + PERFORM content_type__refresh_view(drop_attribute__content_type); return 0; end;' language 'plpgsql'; @@ -358,7 +359,7 @@ -- any given content_type/use_context pair update cr_type_template_map set is_default = ''f'' - where template_id ^= set_default_template__template_id + where template_id <> set_default_template__template_id and content_type = set_default_template__content_type and use_context = set_default_template__use_context and is_default = ''t''; @@ -472,6 +473,10 @@ -- FIXME: need to look at this in more detail. This probably can't be made -- to work reliably in postgresql. + +-- Create or replace a trigger on insert for simplifying addition of +-- revisions for any content type + -- procedure refresh_trigger create function content_type__refresh_trigger (varchar) returns integer as ' @@ -491,13 +496,13 @@ tr_text := '' -create function '' || v_table_name || ''t() returns opaque as \\\\' +create function '' || v_table_name || ''t() returns opaque as \\\' declare new_revision_id integer; begin if new.item_id is null then - raise EXCEPTION \\\\'\\\\'-20000: item_id is required when inserting into %i \\\\'\\\\', v_table_name; + raise EXCEPTION \\\'\\\'-20000: item_id is required when inserting into %i \\\'\\\', v_table_name; end if; if new.text is not null then @@ -541,9 +546,9 @@ from acs_object_types where - object_type ^= ''acs_object'' + object_type <> ''acs_object'' and - object_type ^= ''content_revision'' + object_type <> ''content_revision'' connect by prior supertype = object_type start with @@ -557,7 +562,7 @@ -- end building the trigger code tr_text := tr_text || '' -end;\\\\' language \\\\'plpgsql\\\\'; +end;\\\' language \\\'plpgsql\\\'; create trigger '' || v_table_name || ''t before insert on '' || v_table_name || ''i for each row execute procedure '' || v_table_name || ''t()''; -- (Re)create the trigger @@ -833,11 +838,11 @@ create function content_type__is_content_type (varchar) returns boolean as ' declare - object_type alias for $1; - v_is_content_type boolean + is_content_type__object_type alias for $1; + v_is_content_type boolean begin - if object_type = ''content_revision'' then + if is_content_type__object_type = ''content_revision'' then v_is_content_type := ''t''; @@ -866,7 +871,7 @@ -- get the default template select - template_id into rotate_template__v_template_id + template_id into v_template_id from cr_type_template_map where @@ -907,7 +912,7 @@ end if; -- register the new template as the default template of the content type - if v_template_id ^= rotate_template__template_id then + if v_template_id != rotate_template__template_id then content_type__register_template( rotate_template__v_content_type, rotate_template__template_id, 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.1 -r1.2 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-update.sql 26 Mar 2001 05:20:15 -0000 1.1 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-update.sql 28 Mar 2001 02:15:10 -0000 1.2 @@ -13,7 +13,7 @@ -- http://www.fsf.org/copyleft/gpl.html --set serveroutput on - +-- FIXME: drop constraint doesn't work on postgresql create function inline_0 () returns integer as ' begin @@ -76,23 +76,23 @@ 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\\\\'\\\\' 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''; execute ''alter table cr_folders add has_child_folders char(1) - default \\\\'\\\\'f\\\\'\\\\' + default \\\'\\\'f\\\'\\\' constraint cr_folder_child_chk - check (has_child_folders in (\\\\'\\\\'t\\\\'\\\\',\\\\'\\\\'f\\\\'\\\\'))''; + check (has_child_folders in (\\\'\\\'t\\\'\\\',\\\'\\\'f\\\'\\\'))''; execute ''update cr_folders f set has_child_folders = - coalesce((select \\\\'\\\\'t\\\\'\\\\' from dual where exists + 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; @@ -131,14 +131,14 @@ 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\\\\'\\\\' + execute ''update cr_items set publish_status = \\\'\\\'live\\\'\\\' where live_revision is not null''; - execute ''alter table cr_item_publish_audit add + execute ''alter table cr_item_publish_audit add column old_status varchar(40)''; - execute ''alter table cr_item_publish_audit add + execute ''alter table cr_item_publish_audit add column new_status varchar(40)''; end if; Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-util.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-util.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-util.sql 25 Mar 2001 01:20:55 -0000 1.1 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-util.sql 28 Mar 2001 02:15:10 -0000 1.2 @@ -21,7 +21,7 @@ select count(*) > 0 into v_exists from pg_class - where upper(relname) = upper(table_exists__table_name); + where relname = lower(table_exists__table_name); return v_exists; @@ -43,9 +43,9 @@ select count(*) > 0 into v_exists from pg_class c, pg_attribute a - where upper(c.relname) = = upper(column_exists__table_name) + where c.relname = = lower(column_exists__table_name) and c.oid = a.attrelid - and upper(a.attname) = upper(column_exists__column_name); + and a.attname = lower(column_exists__column_name); return v_exists; Index: openacs-4/packages/acs-content-repository/sql/postgresql/doc-package.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/doc-package.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/acs-content-repository/sql/postgresql/doc-package.sql 27 Mar 2001 02:02:31 -0000 1.1 +++ openacs-4/packages/acs-content-repository/sql/postgresql/doc-package.sql 28 Mar 2001 02:15:10 -0000 1.2 @@ -25,51 +25,12 @@ declare proc_name alias for $1; package_name alias for $2; - v_line integer; - v_result varchar(4000); - v_text varchar(4000); - v_started varchar(1); - v_newline varchar(10) - '; - - cursor v_package_cur - select line, - where lower(name) - and type - order by - + v_function_name varchar; begin - - v_result := ''''; - v_started := ''f''; + v_function_name := package_name || ''__'' proc_name; - open v_package_cur; - loop - fetch v_package_cur into v_line, v_text; - exit when v_package_cur%NOTFOUND; - - -- Look for the function header - if v_started = ''f'' then - if lower(v_text) like ''%function%'' || lower(proc_name) || ''%'' then - v_started := ''t''; - elsif lower(v_text) like ''%procedure%'' || lower(proc_name) || ''%'' then - v_started := ''t''; - end if; - end if; - - -- Process the header - if v_started = ''t'' then - v_result := v_result || v_text; - if v_text like ''%;%'' then - close v_package_cur; - return v_result; - end if; - end if; - end loop; + return get_func_header(v_function_name); - -- Return unfinished result - return v_result; - end;' language 'plpgsql'; @@ -78,51 +39,9 @@ returns varchar as ' declare package_name alias for $1; - v_line integer; - v_result varchar(4000); - v_text varchar(4000); - v_started varchar(1); - v_newline varchar(10) - '; - - cursor v_package_cur - select line, - where lower(name) - and type - order by - begin - - v_result := ''''; - v_started := ''f''; - open v_package_cur; - loop - fetch v_package_cur into v_line, v_text; - exit when v_package_cur%NOTFOUND; - - -- Look for the function header - if v_started = ''f'' then - if v_text like ''--%'' then - v_started := ''t''; - end if; - end if; - - -- Process the header - if v_started = ''t'' then - - if v_text not like ''--%'' then - close v_package_cur; - return v_result; - end if; - - v_result := v_result || v_text; - end if; - end loop; - - -- Return unfinished result - return v_result; - + return ''NOT IMPLEMENTED FOR POSTGRESQL''; end;' language 'plpgsql';