Index: openacs-4/packages/acs-content-repository/sql/oracle/upgrade/upgrade-5.4.0d5-5.4.0d6.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/oracle/upgrade/Attic/upgrade-5.4.0d5-5.4.0d6.sql,v
diff -u -N
--- openacs-4/packages/acs-content-repository/sql/oracle/upgrade/upgrade-5.4.0d5-5.4.0d6.sql	17 Sep 2007 13:00:40 -0000	1.1
+++ /dev/null	1 Jan 1970 00:00:00 -0000
@@ -1,670 +0,0 @@
--- Data model to support content repository of the ArsDigita
--- Community System
-
--- Copyright (C) 1999-2000 ArsDigita Corporation
--- Author: Karl Goldstein (karlg@arsdigita.com)
-
--- $Id: upgrade-5.4.0d5-5.4.0d6.sql,v 1.1 2007/09/17 13:00:40 maltes Exp $
-
--- This is free software distributed under the terms of the GNU Public
--- License.  Full text of the license is available from the GNU Project:
--- http://www.fsf.org/copyleft/gpl.html
-
-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 <tt>acs_object.new</tt>
-  --    @param creation_ip   As in <tt>acs_object.new</tt>
-  --    @param creation_user As in <tt>acs_object.new</tt>
-  --    @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
-
