Index: openacs-4/packages/acs-content-repository/sql/oracle/upgrade/upgrade-5.2.0a1-5.2.0a2.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/oracle/upgrade/upgrade-5.2.0a1-5.2.0a2.sql,v diff -u -r1.1.2.3 -r1.1.2.4 --- openacs-4/packages/acs-content-repository/sql/oracle/upgrade/upgrade-5.2.0a1-5.2.0a2.sql 5 Jul 2005 21:51:35 -0000 1.1.2.3 +++ openacs-4/packages/acs-content-repository/sql/oracle/upgrade/upgrade-5.2.0a1-5.2.0a2.sql 6 Jul 2005 20:33:44 -0000 1.1.2.4 @@ -1,3 +1,757 @@ +create or replace package content_revision +as + +function new ( + --/** Create a new revision for an item. + -- @author Karl Goldstein + -- @param title The revised title for the item + -- @param description A short description of this revision, 4000 characters maximum + -- @param publish_date Publication date. + -- @param mime_type The revised mime type of the item, defaults to 'text/plain' + -- @param nls_language The revised language of the item, for use with Intermedia searching + -- @param data The blob which contains the body of the revision + -- @param item_id The id of the item being revised + -- @param revision_id The id of the new revision. A new id will be allocated by default + -- @param creation_date As in acs_object.new + -- @param creation_ip As in acs_object.new + -- @param creation_user As in acs_object.new + -- @return The id of the newly created revision + -- @see {acs_object.new}, {content_item.new} + --*/ + title in cr_revisions.title%TYPE, + description in cr_revisions.description%TYPE default null, + publish_date in cr_revisions.publish_date%TYPE default sysdate, + mime_type in cr_revisions.mime_type%TYPE default 'text/plain', + nls_language in cr_revisions.nls_language%TYPE default null, + data in cr_revisions.content%TYPE, + item_id in cr_items.item_id%TYPE, + revision_id in cr_revisions.revision_id%TYPE default null, + creation_date in acs_objects.creation_date%TYPE + default sysdate, + creation_user in acs_objects.creation_user%TYPE + default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + filename in cr_revisions.filename%TYPE default null, + package_id in acs_objects.package_id%TYPE default null +) return cr_revisions.revision_id%TYPE; + +function new ( + title in cr_revisions.title%TYPE, + description in cr_revisions.description%TYPE default null, + publish_date in cr_revisions.publish_date%TYPE default sysdate, + mime_type in cr_revisions.mime_type%TYPE default 'text/plain', + nls_language in cr_revisions.nls_language%TYPE default null, + text in varchar2 default null, + item_id in cr_items.item_id%TYPE, + revision_id in cr_revisions.revision_id%TYPE default null, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + package_id in acs_objects.package_id%TYPE default null, + filename in cr_revisions.filename%TYPE default null +) return cr_revisions.revision_id%TYPE; + +function copy ( + --/** Creates a new copy of a revision, including all attributes and content + -- and content, returning the ID of the new revision + -- @author Karl Goldstein, Michael Pih + -- @param revision_id The id of the revision to copy + -- @param copy_id The id of the new copy (default null) + -- @param target_item_id The id of the item which will own the copied revision. If null, the item that holds the original revision will own the copied revision. Defaults to null. + -- @param creation_user The id of the creation user + -- @param creation_ip The IP address of the creation user (default null) + -- @return The id of the new revision + -- @see {content_revision.new} + --*/ + revision_id in cr_revisions.revision_id%TYPE, + copy_id in cr_revisions.revision_id%TYPE default null, + target_item_id in cr_items.item_id%TYPE default null, + creation_user in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null +) return cr_revisions.revision_id%TYPE; + +procedure del ( + --/** Deletes the revision. + -- @author Karl Goldstein + -- @param revision_id The id of the revision to delete + -- @see {content_revision.new}, {acs_object.delete} + --*/ + revision_id in cr_revisions.revision_id%TYPE +); + +function get_number ( + --/** Return the revision number of the specified revision, according to + -- the chronological + -- order in which revisions have been added for this item. + -- @author Karl Goldstein + -- @param revision_id The id the revision + -- @return The number of the revision + -- @see {content_revision.new} + --*/ + revision_id in cr_revisions.revision_id%TYPE +) return number; + +function revision_name ( + --/** Return a pretty string 'revision x of y' + --*/ + revision_id in cr_revisions.revision_id%TYPE +) return varchar2; + +procedure index_attributes( + --/** Generates an XML document for insertion into cr_revision_attributes, + -- which is indexed by Intermedia for searching attributes. + -- @author Karl Goldstein + -- @param revision_id The id of the revision to index + -- @see {content_revision.new} + --*/ + revision_id IN cr_revisions.revision_id%TYPE +); + +function export_xml ( + revision_id IN cr_revisions.revision_id%TYPE +) return cr_xml_docs.doc_id%TYPE; + +function write_xml ( + revision_id IN number, + clob_loc IN clob +) return number as language + java +name + 'com.arsdigita.content.XMLExchange.exportRevision( + java.lang.Integer, oracle.sql.CLOB + ) return int'; + +function import_xml ( + item_id IN cr_items.item_id%TYPE, + revision_id IN cr_revisions.revision_id%TYPE, + doc_id IN number +) return cr_revisions.revision_id%TYPE; + +function read_xml ( + item_id IN number, + revision_id IN number, + clob_loc IN clob +) return number as language + java +name + 'com.arsdigita.content.XMLExchange.importRevision( + java.lang.Integer, java.lang.Integer, oracle.sql.CLOB + ) return int'; + +procedure to_html ( + --/** Converts a revision uploaded as a binary document to html + -- @author Karl Goldstein + -- @param revision_id The id of the revision to index + --*/ + revision_id IN cr_revisions.revision_id%TYPE +); + +procedure replace( + revision_id number, search varchar2, replace varchar2) +as language + java +name + 'com.arsdigita.content.Regexp.replace( + int, java.lang.String, java.lang.String + )'; + +function is_live ( + -- /** Determine if the revision is live + -- @author Karl Goldstein, Stanislav Freidin + -- @param revision_id The id of the revision to check + -- @return 't' if the revision is live, 'f' otherwise + -- @see {content_revision.is_latest} + --*/ + revision_id in cr_revisions.revision_id%TYPE +) return varchar2; + +function is_latest ( + -- /** Determine if the revision is the latest revision + -- @author Karl Goldstein, Stanislav Freidin + -- @param revision_id The id of the revision to check + -- @return 't' if the revision is the latest revision for its item, 'f' otherwise + -- @see {content_revision.is_live} + --*/ + revision_id in cr_revisions.revision_id%TYPE +) return varchar2; + +procedure to_temporary_clob ( + revision_id in cr_revisions.revision_id%TYPE +); + +procedure content_copy ( + -- /** Copies the content of the specified revision to the content + -- of another revision + -- @author Michael Pih + -- @param revision_id The id of the revision with the content to be copied + -- @param revision_id The id of the revision to be updated, defaults to the + -- latest revision of the item with which the source revision is + -- associated. + --*/ + revision_id in cr_revisions.revision_id%TYPE, + revision_id_dest in cr_revisions.revision_id%TYPE default null +); + +end content_revision; +/ +show errors + +create or replace package body content_revision +as + +function new ( + title in cr_revisions.title%TYPE, + description in cr_revisions.description%TYPE default null, + publish_date in cr_revisions.publish_date%TYPE default sysdate, + mime_type in cr_revisions.mime_type%TYPE default 'text/plain', + nls_language in cr_revisions.nls_language%TYPE default null, + data in cr_revisions.content%TYPE, + item_id in cr_items.item_id%TYPE, + revision_id in cr_revisions.revision_id%TYPE default null, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + filename in cr_revisions.filename%TYPE default null, + package_id in acs_objects.package_id%TYPE default null + +) return cr_revisions.revision_id%TYPE is + + v_revision_id integer; + v_package_id acs_objects.package_id%TYPE; + v_content_type acs_object_types.object_type%TYPE; + +begin + + v_content_type := content_item.get_content_type(item_id); + + if package_id is null then + v_package_id := acs_object.package_id(item_id); + else + v_package_id := package_id; + end if; + + v_revision_id := acs_object.new( + object_id => revision_id, + object_type => v_content_type, + title => title, + package_id => v_package_id, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + context_id => item_id + ); + + insert into cr_revisions ( + revision_id, title, description, mime_type, publish_date, + nls_language, content, item_id, filename + ) values ( + v_revision_id, title, description, mime_type, publish_date, + nls_language, data, item_id, filename + ); + + return v_revision_id; + +end new; + +function new ( + title in cr_revisions.title%TYPE, + description in cr_revisions.description%TYPE default null, + publish_date in cr_revisions.publish_date%TYPE default sysdate, + mime_type in cr_revisions.mime_type%TYPE default 'text/plain', + nls_language in cr_revisions.nls_language%TYPE default null, + text in varchar2 default null, + item_id in cr_items.item_id%TYPE, + revision_id in cr_revisions.revision_id%TYPE default null, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + package_id in acs_objects.package_id%TYPE default null, + filename in cr_revisions.filename%TYPE default null +) return cr_revisions.revision_id%TYPE is + + v_revision_id integer; + blob_loc cr_revisions.content%TYPE; + +begin + + blob_loc := empty_blob(); + + v_revision_id := content_revision.new( + title => title, + description => description, + publish_date => publish_date, + mime_type => mime_type, + nls_language => nls_language, + data => blob_loc, + item_id => item_id, + revision_id => revision_id, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + package_id => package_id, + filename => filename + ); + + select + content into blob_loc + from + cr_revisions + where + revision_id = v_revision_id + for update; + + string_to_blob(text, blob_loc); + + return v_revision_id; + +end new; + +procedure copy_attributes ( + content_type in acs_object_types.object_type%TYPE, + revision_id in cr_revisions.revision_id%TYPE, + copy_id in cr_revisions.revision_id%TYPE +) is + + v_table_name acs_object_types.table_name%TYPE; + v_id_column acs_object_types.id_column%TYPE; + + cursor attr_cur is + select + attribute_name + from + acs_attributes + where + object_type = copy_attributes.content_type; + + cols varchar2(2000) := ''; + +begin + + select table_name, id_column into v_table_name, v_id_column + from acs_object_types where object_type = copy_attributes.content_type; + + for attr_rec in attr_cur loop + cols := cols || ', ' || attr_rec.attribute_name; + end loop; + + execute immediate 'insert into ' || v_table_name || + ' ( ' || v_id_column || cols || ' ) ( select ' || copy_id || cols || + ' from ' || v_table_name || ' where ' || v_id_column || ' = ' || + revision_id || ')'; + +end copy_attributes; + +function copy ( + revision_id in cr_revisions.revision_id%TYPE, + copy_id in cr_revisions.revision_id%TYPE default null, + target_item_id in cr_items.item_id%TYPE default null, + creation_user in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null +) return cr_revisions.revision_id%TYPE +is + v_copy_id cr_revisions.revision_id%TYPE; + v_target_item_id cr_items.item_id%TYPE; + + -- get the content_type and supertypes + cursor type_cur is + select + object_type + from + acs_object_types + where + object_type ^= 'acs_object' + and + object_type ^= 'content_revision' + connect by + prior supertype = object_type + start with + object_type = ( + select object_type from acs_objects where object_id = copy.revision_id + ) + order by + level desc; + +begin + -- use the specified item_id or the item_id of the original revision + -- if none is specified + if target_item_id is null then + select item_id into v_target_item_id from cr_revisions + where revision_id = copy.revision_id; + else + v_target_item_id := target_item_id; + end if; + + -- use the copy_id or generate a new copy_id if none is specified + -- the copy_id is a revision_id + if copy_id is null then + select acs_object_id_seq.nextval into v_copy_id from dual; + else + v_copy_id := copy_id; + end if; + + -- create the basic object + insert into acs_objects ( + object_id, object_type, context_id, security_inherit_p, + creation_user, creation_date, creation_ip, + last_modified, modifying_user, modifying_ip, + title, package_id + ) ( select + v_copy_id, object_type, v_target_item_id, security_inherit_p, + copy.creation_user, sysdate, copy.creation_ip, + sysdate, copy.creation_user, copy.creation_ip, + title, package_id from + acs_objects where object_id = copy.revision_id + ); + + -- create the basic revision (using v_target_item_id) + insert into cr_revisions ( + revision_id, title, description, publish_date, mime_type, + nls_language, content, item_id, content_length + ) ( select + v_copy_id, title, description, publish_date, mime_type, nls_language, + content, v_target_item_id, content_length + from + cr_revisions + where + revision_id = copy.revision_id + ); + + -- iterate over the ancestor types and copy attributes + for type_rec in type_cur loop + copy_attributes(type_rec.object_type, copy.revision_id, v_copy_id); + end loop; + + return v_copy_id; +end copy; + +procedure del ( + revision_id in cr_revisions.revision_id%TYPE +) is + v_item_id cr_items.item_id%TYPE; + v_latest_revision cr_revisions.revision_id%TYPE; + v_live_revision cr_revisions.revision_id%TYPE; + +begin + + -- Get item id and latest/live revisions + select item_id into v_item_id from cr_revisions + where revision_id = content_revision.del.revision_id; + + select + latest_revision, live_revision + into + v_latest_revision, v_live_revision + from + cr_items + where + item_id = v_item_id; + + -- Recalculate latest revision + if v_latest_revision = content_revision.del.revision_id then + declare + cursor c_revision_cur is + select r.revision_id 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 <> content_revision.del.revision_id + order by o.creation_date desc; + begin + open c_revision_cur; + fetch c_revision_cur into v_latest_revision; + if c_revision_cur%NOTFOUND then + v_latest_revision := null; + end if; + close c_revision_cur; + + update cr_items set latest_revision = v_latest_revision + where item_id = v_item_id; + end; + end if; + + -- Clear live revision + if v_live_revision = content_revision.del.revision_id then + update cr_items set live_revision = null + where item_id = v_item_id; + end if; + + -- Clear the audit + delete from cr_item_publish_audit + where old_revision = content_revision.del.revision_id + or new_revision = content_revision.del.revision_id; + + -- Delete the revision + acs_object.del(revision_id); + +end del; + +function get_number ( + revision_id in cr_revisions.revision_id%TYPE +) return number is + + cursor rev_cur is + select + revision_id + from + cr_revisions r, acs_objects o + where + item_id = (select item_id from cr_revisions + where revision_id = get_number.revision_id) + and + o.object_id = r.revision_id + order by + o.creation_date; + + v_number integer; + v_revision cr_revisions.revision_id%TYPE; + +begin + + open rev_cur; + loop + + fetch rev_cur into v_revision; + + if v_revision = get_number.revision_id then + v_number := rev_cur%ROWCOUNT; + exit; + end if; + + end loop; + close rev_cur; + + return v_number; + +end get_number; + +function revision_name( + revision_id IN cr_revisions.revision_id%TYPE +) return varchar2 is + + v_text varchar2(500); + v_sql varchar2(500); + +begin + + v_sql := 'select ''Revision '' || content_revision.get_number(r.revision_id) || '' of '' || (select count(*) from cr_revisions where item_id = r.item_id) || '' for item: '' || content_item.get_title(item_id) + from cr_revisions r + where r.revision_id = ' || revision_name.revision_id; + + execute immediate v_sql into v_text; + + return v_text; + +end revision_name; + +procedure index_attributes( + revision_id IN cr_revisions.revision_id%TYPE +) is + + clob_loc clob; + v_revision_id cr_revisions.revision_id%TYPE; + +begin + + insert into cr_revision_attributes ( + revision_id, attributes + ) values ( + revision_id, empty_clob() + ) returning attributes into clob_loc; + + v_revision_id := write_xml(revision_id, clob_loc); + +end index_attributes; + +function import_xml ( + item_id IN cr_items.item_id%TYPE, + revision_id IN cr_revisions.revision_id%TYPE, + doc_id IN number +) return cr_revisions.revision_id%TYPE is + + clob_loc clob; + v_revision_id cr_revisions.revision_id%TYPE; + +begin + + select doc into clob_loc from cr_xml_docs where doc_id = import_xml.doc_id; + v_revision_id := read_xml(item_id, revision_id, clob_loc); + + return v_revision_id; + +end import_xml; + +function export_xml ( + revision_id IN cr_revisions.revision_id%TYPE +) return cr_xml_docs.doc_id%TYPE is + + clob_loc clob; + v_doc_id cr_xml_docs.doc_id%TYPE; + v_revision_id cr_revisions.revision_id%TYPE; + +begin + + 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; + + v_revision_id := write_xml(revision_id, clob_loc); + + return v_doc_id; + +end export_xml; + +procedure to_html ( + revision_id IN cr_revisions.revision_id%TYPE +) is + + tmp_clob clob; + blob_loc blob; + +begin + + ctx_doc.filter('cr_doc_filter_index', revision_id, tmp_clob, false); + + select + content into blob_loc + from + cr_revisions + where + revision_id = to_html.revision_id + for update; + + clob_to_blob(tmp_clob, blob_loc); + + dbms_lob.freetemporary(tmp_clob); + +end to_html; + +function is_live ( + revision_id in cr_revisions.revision_id%TYPE +) return varchar2 +is + v_ret varchar2(1); +begin + + select 't' into v_ret from cr_items + where live_revision = is_live.revision_id; + + return v_ret; + +exception when no_data_found then + return 'f'; +end is_live; + +function is_latest ( + revision_id in cr_revisions.revision_id%TYPE +) return varchar2 +is + v_ret varchar2(1); +begin + + select 't' into v_ret from cr_items + where latest_revision = is_latest.revision_id; + + return v_ret; + +exception when no_data_found then + return 'f'; +end is_latest; + +procedure to_temporary_clob ( + revision_id in cr_revisions.revision_id%TYPE +) is + b blob; + c clob; + +begin + + insert into cr_content_text ( + revision_id, content + ) values ( + revision_id, empty_clob() + ) returning content into c; + + select content into b from cr_revisions + where revision_id = to_temporary_clob.revision_id; + + blob_to_clob(b, c); + +end to_temporary_clob; + + + + +-- revision_id is the revision with the content that is to be copied +procedure content_copy ( + revision_id in cr_revisions.revision_id%TYPE, + revision_id_dest in cr_revisions.revision_id%TYPE default null +) is + v_item_id cr_items.item_id%TYPE; + v_content_length integer; + v_revision_id_dest cr_revisions.revision_id%TYPE; + v_filename cr_revisions.filename%TYPE; + v_content blob; +begin + + select + content_length, item_id + into + v_content_length, v_item_id + from + cr_revisions + where + revision_id = content_copy.revision_id; + + -- get the destination revision + if content_copy.revision_id_dest is null then + select + latest_revision into v_revision_id_dest + from + cr_items + where + item_id = v_item_id; + else + v_revision_id_dest := content_copy.revision_id_dest; + end if; + + + -- only copy the content if the source content is not null + if v_content_length is not null and v_content_length > 0 then + + /* The internal LOB types - BLOB, CLOB, and NCLOB - use copy semantics, as + opposed to the reference semantics which apply to BFILEs. + When a BLOB, CLOB, or NCLOB is copied from one row to another row in + the same table or in a different table, the actual LOB value is + copied, not just the LOB locator. */ + + select + filename, content_length + into + v_filename, v_content_length + from + cr_revisions + where + revision_id = content_copy.revision_id; + + -- need to update the file name after the copy, + -- if this content item is in CR file storage. The file name is based + -- off of the item_id and revision_id and it will be invalid for the + -- copied revision. + + update cr_revisions + set content = (select content from cr_revisions where revision_id = content_copy.revision_id), + filename = v_filename, + content_length = v_content_length + where revision_id = v_revision_id_dest; + end if; + +end content_copy; + + + +end content_revision; +/ +show errors + create or replace package content_type AUTHID CURRENT_USER as --/** This package is used to manipulate content types and attributes -- @@ -1205,3 +1959,6 @@ end; / + +-- content_item must be explicitly recompiled on Oracle 8 for some reason. +alter package content_item compile body;