Index: openacs-4/packages/acs-content-repository/sql/oracle/upgrade/upgrade-5.4.0d1-5.4.0d2.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/oracle/upgrade/upgrade-5.4.0d1-5.4.0d2.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-content-repository/sql/oracle/upgrade/upgrade-5.4.0d1-5.4.0d2.sql 17 Sep 2007 13:00:40 -0000 1.1 @@ -0,0 +1,3 @@ +-- Introduces some changes from Quest +CREATE UNIQUE INDEX CR_CHILD_RELS_kids_IDx ON CR_CHILD_RELS(CHILD_ID); +@ ../content-image.sql \ No newline at end of file Index: openacs-4/packages/acs-content-repository/sql/oracle/upgrade/upgrade-5.4.0d3-5.4.0d4.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/oracle/upgrade/Attic/upgrade-5.4.0d3-5.4.0d4.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-content-repository/sql/oracle/upgrade/upgrade-5.4.0d3-5.4.0d4.sql 17 Sep 2007 13:00:40 -0000 1.1 @@ -0,0 +1,134 @@ +create or replace function blob_to_string (blob_loc IN BLOB) +RETURN VARCHAR2 +IS +v_blength number; +v_ret varchar(32767); +v_amount binary_integer:=10000; +v_offset integer:=1; +v_buffer raw(20000); +BEGIN + v_blength:=dbms_lob.getlength(blob_loc); + IF v_blength=0 THEN + return v_ret; + ELSE + + if v_blengthacs_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 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 +); + +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 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 + +-- Trigger to maintain latest_revision in cr_items + +create or replace trigger cr_revision_latest_tr +after insert on cr_revisions for each row +begin + update cr_items set latest_revision = :new.revision_id + where item_id = :new.item_id; +end cr_revision_latest_tr; +/ +show errors + Index: openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.4.0d6-5.4.0d7.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.4.0d6-5.4.0d7.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.4.0d6-5.4.0d7.sql 17 Sep 2007 13:00:41 -0000 1.1 @@ -0,0 +1,9 @@ +-- +-- packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.4.0d6-5.4.0d7.sql +-- +-- @author Malte Sussdorff (malte.sussdorff@cognovis.de) +-- @creation-date 2007-09-07 +-- @cvs-id $Id: upgrade-5.4.0d6-5.4.0d7.sql,v 1.1 2007/09/17 13:00:41 maltes Exp $ +-- + +select define_function_args('content_type__drop_type','content_type,drop_children_p;f,drop_table_p;f,drop_objects_p;f'); \ No newline at end of file