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.2 -r1.3
--- openacs-4/packages/acs-content-repository/sql/postgresql/content-revision.sql	28 Mar 2001 02:15:10 -0000	1.2
+++ openacs-4/packages/acs-content-repository/sql/postgresql/content-revision.sql	29 Mar 2001 01:21:03 -0000	1.3
@@ -42,12 +42,16 @@
       new__item_id
   );
 
+  -- binary data is stored in cr_revisions using Don's lob hack.
+  -- This routine only inserts the lob id.  It would need to be followed by 
+  -- ns_pg blob_dml from within a tcl script to actually insert the lob data.
+
   insert into cr_revisions (
     revision_id, title, description, mime_type, publish_date,
-    nls_language, content, item_id
+    nls_language, lob, item_id, storage_type
   ) values (
     v_revision_id, new__title, new__description, new__mime_type, 
-    new__publish_date, new__nls_language, new__data, new__item_id     
+    new__publish_date, new__nls_language, new__data, new__item_id, ''lob''     
   );
 
   return v_revision_id;
@@ -59,46 +63,42 @@
 create function content_revision__new (varchar,varchar,timestamp,varchar,varchar,text,integer,integer,timestamp,integer,varchar)
 returns integer as '
 declare
-  title                  alias for $1;  
-  description            alias for $2;  
-  publish_date           alias for $3;  
-  mime_type              alias for $4;  
-  nls_language           alias for $5;  
-  text                   alias for $6;  
-  item_id                alias for $7;  
-  revision_id            alias for $8;  
-  creation_date          alias for $9;  
-  creation_user          alias for $10; 
-  creation_ip            alias for $11; 
-  v_revision_id          integer;       
-  blob_loc               cr_revisions.content%TYPE;
+  new__title                  alias for $1;  
+  new__description            alias for $2;  
+  new__publish_date           alias for $3;  
+  new__mime_type              alias for $4;  
+  new__nls_language           alias for $5;  
+  new__text                   alias for $6;  
+  new__item_id                alias for $7;  
+  new__revision_id            alias for $8;  
+  new__creation_date          alias for $9;  
+  new__creation_user          alias for $10; 
+  new__creation_ip            alias for $11; 
+  v_revision_id               integer;       
+  v_content_type              acs_object_types.object_type%TYPE;
 begin
 
-  blob_loc := empty_blob();
+  v_content_type := content_item__get_content_type(new__item_id);
 
-  v_revision_id := content_revision__new(
-      title,
-      description,
-      publish_date,
-      mime_type,
-      nls_language,
-      blob_loc,
-      item_id, 
-      revision_id,
-      creation_date,
-      creation_user,
-      creation_ip
+  v_revision_id := acs_object__new(
+      new__revision_id,
+      v_content_type, 
+      new__creation_date, 
+      new__creation_user, 
+      new__creation_ip, 
+      new__item_id
   );
 
-  select 
-    content into blob_loc
-  from 
-    cr_revisions 
-  where 
-    revision_id = v_revision_id
-  for update;
+  -- text data is stored directly in cr_revisions using text datatype.
 
-  PERFORM string_to_blob(text, blob_loc);
+  insert into cr_revisions (
+    revision_id, title, description, mime_type, publish_date,
+    nls_language, content, item_id, storage_type, content_length
+  ) values (
+    v_revision_id, new__title, new__description, new__mime_type, 
+    new__publish_date, new__nls_language, new__text, new__item_id, ''text'',
+    length(new__text)
+  );
 
   return v_revision_id;
  
@@ -369,15 +369,15 @@
 returns integer as '
 declare
   revision_id            alias for $1;  
-  clob_loc               clob;          
+  clob_loc               text;          
   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 (v_doc_id, empty_clob());
+    values (v_doc_id, '''');
 
   -- FIXME: need a way to deal with this xml call.
   v_revision_id := write_xml(revision_id, clob_loc);  
@@ -436,10 +436,9 @@
 
 -- function is_latest
 create function content_revision__is_latest (integer)
-returns varchar as '
+returns boolean as '
 declare
   is_latest__revision_id            alias for $1;  
-  v_ret                             varchar(1);    
 begin
 
   select 1 from cr_items
@@ -459,10 +458,15 @@
 returns integer as '
 declare
   to_temporary_clob__revision_id            alias for $1;  
-  b                                         blob;          
-  c                                         text;          
+  -- b                                         blob;          
+  -- c                                         text;          
 begin
-  -- FIXME
+  -- FIXME:  I can't find an instance in the 4.2 beta code where this
+  --         is used so I'm not worrying about porting it for now.
+  --         DCW - 2001-03-28.
+
+  raise EXCEPTION ''not implemented content_revision.to_temporary_clob'';
+/*
   insert into cr_content_text (
     revision_id, content
   ) values (
@@ -473,7 +477,7 @@
     where revision_id = to_temporary_clob__revision_id;
 
   PERFORM blob_to_clob(b, c);
-
+*/
   return 0; 
 end;' language 'plpgsql';
 
@@ -487,12 +491,16 @@
   lobs                                 blob;          
   lobd                                 blob;          
   v_item_id                            cr_items.item_id%TYPE;
-  v_content_length                     integer;       
+  v_content_length                     cr_revisions.content_length%TYPE;
   v_revision_id_dest                   cr_revisions.revision_id%TYPE;
+  v_content                            cr_revisions.content%TYPE;
+  v_lob                                cr_revisions.lob%TYPE;
+  v_new_lob                            cr_revisions.lob%TYPE;
+  v_content_type                       cr_revisions.content_type%TYPE;
 begin
   -- FIXME
   select
-    dbms_lob.getlength( content ), item_id
+    content_length, item_id
   into
     v_content_length, v_item_id
   from
@@ -521,17 +529,63 @@
        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. */
-    update cr_revisions
-      set content = ( select content from cr_revisions
-                        where revision_id = content_copy__revision_id )
-      where revision_id = v_revision_id_dest;
+
+    select content, content_length, lob, storage_type 
+      into v_content, v_content_length, v_lob, v_storage_type
+      from cr_revisions where revision_id = content_copy__revision_id;
+
+    if v_storage_type = ''lob'' then
+       v_new_lob := empty_lob();
+       PERFORM lob_copy(v_lob, v_new_lob);
+
+        update cr_revisions
+           set content = v_content,
+               content_length = v_content_length,
+               storage_type = ''lob''
+               lob = v_new_lob
+         where revision_id = v_revision_id_dest;
+    else 
+        update cr_revisions
+           set content = v_content,
+               content_length = v_content_length,
+               storage_type = ''text'',
+               lob = null
+         where revision_id = v_revision_id_dest;
+    end if;
+
   end if;
 
   return 0; 
 end;' language 'plpgsql';
 
 
+-- procedure content_copy
+create function content_revision__get_content (integer)
+returns text as '
+declare
+  get_content__revision_id            alias for $1;  
+  v_storage_type                      varchar;
+  v_lob_id
+  v_data                              text;
+begin
+       select storage_type, lob, 
+         into v_storage_type, v_lob_id
+         from cr_revisions
+        where revision_id = get_content__revision_id;
+        
+        if v_storage_type = ''lob'' then
+           return lob_get_data (v_lob_id);
+        else 
+           select content into v_data
+             from cr_revisions
+            where revision_id = get_content_revision_id;
 
+           return v_data;
+        end if;
+
+end;' language 'plpgsql';
+
+
 -- show errors
 
 -- Trigger to maintain latest_revision in cr_items
