Index: openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.3.0b1-5.3.0b2.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.3.0b1-5.3.0b2.sql,v
diff -u -r1.1.2.2 -r1.1.2.3
--- openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.3.0b1-5.3.0b2.sql	3 Jan 2007 21:06:24 -0000	1.1.2.2
+++ openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.3.0b1-5.3.0b2.sql	10 Jan 2007 17:23:30 -0000	1.1.2.3
@@ -6,4 +6,284 @@
 -- @cvs-id $Id$
 --
 
-update cr_revisions set content = '<html><body>@text;noquote@</body></html>' where revision_id = (select live_revision from cr_items ci, cr_type_template_map cm where cm.content_type = 'content_revision' and cm.use_context = 'public' and cm.is_default = 't' and ci.item_id=cm.template_id);
\ No newline at end of file
+update cr_revisions set content = '<html><body>@text;noquote@</body></html>' where revision_id = (select live_revision from cr_items ci, cr_type_template_map cm where cm.content_type = 'content_revision' and cm.use_context = 'public' and cm.is_default = 't' and ci.item_id=cm.template_id);
+
+create or replace function content_revision__new (varchar,varchar,timestamptz,varchar,varchar,integer,integer,integer,timestamptz,integer,varchar,integer)
+returns integer as '
+declare
+  new__title                  alias for $1;  
+  new__description            alias for $2;  -- default null 
+  new__publish_date           alias for $3;  -- default now()
+  new__mime_type              alias for $4;  -- default ''text/plain''
+  new__nls_language           alias for $5;  -- default null
+  -- lob id 
+  new__data                   alias for $6;  
+  new__item_id                alias for $7;  
+  new__revision_id            alias for $8;  -- default null
+  new__creation_date          alias for $9;  -- default now()
+  new__creation_user          alias for $10; -- default null
+  new__creation_ip            alias for $11; -- default null
+  new__package_id             alias for $12; -- default null  
+  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(new__item_id);
+
+  if new__package_id is null then
+    v_package_id := acs_object__package_id(new__item_id);
+  else
+    v_package_id := new__package_id;
+  end if;
+
+  v_revision_id := acs_object__new(
+      new__revision_id,
+      v_content_type, 
+      new__creation_date, 
+      new__creation_user, 
+      new__creation_ip, 
+      new__item_id,
+      ''t'',
+      new__title,
+      v_package_id
+  );
+
+  -- binary data is stored in cr_revisions using Dons 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.
+
+  -- After the lob data is inserted, the content_length needs to be updated 
+  -- as well.
+  -- DanW, 2001-05-10.
+
+  insert into cr_revisions (
+    revision_id, title, description, mime_type, publish_date,
+    nls_language, lob, item_id, content_length
+  ) values (
+    v_revision_id, new__title, new__description,
+    new__mime_type, 
+    new__publish_date, new__nls_language, new__data, 
+    new__item_id, 0
+  );
+
+  return v_revision_id;
+
+end;' language 'plpgsql';
+
+create or replace function content_revision__new (varchar,varchar,timestamptz,varchar,varchar,integer,integer,integer,timestamptz,integer,varchar)
+returns integer as '
+declare
+  new__title                  alias for $1;  
+  new__description            alias for $2;  -- default null 
+  new__publish_date           alias for $3;  -- default now()
+  new__mime_type              alias for $4;  -- default ''text/plain''
+  new__nls_language           alias for $5;  -- default null
+  -- lob id 
+  new__data                   alias for $6;  
+  new__item_id                alias for $7;  
+  new__revision_id            alias for $8;  -- default null
+  new__creation_date          alias for $9;  -- default now()
+  new__creation_user          alias for $10; -- default null
+  new__creation_ip            alias for $11; -- default null
+begin
+        return content_revision__new(new__title,
+                                     new__description,
+                                     new__publish_date,
+                                     new__mime_type,
+                                     new__nls_language,
+                                     new__data,
+                                     new__item_id,
+                                     new__revision_id,
+                                     new__creation_date,
+                                     new__creation_user,
+                                     new__creation_ip,
+                                     null
+               );
+end;' language 'plpgsql';
+
+create or replace function content_revision__new(varchar,varchar,timestamptz,varchar,text,integer,integer) returns integer as '
+declare
+        new__title              alias for $1;
+        new__description        alias for $2;  -- default null
+        new__publish_date       alias for $3;  -- default now()
+        new__mime_type          alias for $4;  -- default ''text/plain''
+        new__text               alias for $5;  -- default '' ''
+        new__item_id            alias for $6;
+        new__package_id         alias for $7;  -- default null
+begin
+        return content_revision__new(new__title,
+                                     new__description,
+                                     new__publish_date,
+                                     new__mime_type,
+                                     null,
+                                     new__text,
+                                     new__item_id,
+                                     null,
+                                     now(),
+                                     null,
+                                     null,
+                                     null,
+                                     new__package_id
+               );
+
+end;' language 'plpgsql';
+
+create or replace function content_revision__new(varchar,varchar,timestamptz,varchar,text,integer) returns integer as '
+declare
+        new__title              alias for $1;
+        new__description        alias for $2;  -- default null
+        new__publish_date       alias for $3;  -- default now()
+        new__mime_type          alias for $4;  -- default ''text/plain''
+        new__text               alias for $5;  -- default '' ''
+        new__item_id            alias for $6;
+begin
+        return content_revision__new(new__title,
+                                     new__description,
+                                     new__publish_date,
+                                     new__mime_type,
+                                     null,
+                                     new__text,
+                                     new__item_id,
+                                     null,
+                                     now(),
+                                     null,
+                                     null,
+                                     null,
+                                     null
+               );
+
+end;' language 'plpgsql';
+
+create or replace function content_revision__new (varchar,varchar,timestamptz,varchar,varchar,text,integer,integer,timestamptz,integer,varchar)
+returns integer as '
+declare
+  new__title                  alias for $1;  
+  new__description            alias for $2;  -- default null  
+  new__publish_date           alias for $3;  -- default now()
+  new__mime_type              alias for $4;  -- default ''text/plain''
+  new__nls_language           alias for $5;  -- default null
+  new__text                   alias for $6;  -- default '' ''
+  new__item_id                alias for $7;  
+  new__revision_id            alias for $8;  -- default null
+  new__creation_date          alias for $9;  -- default now()
+  new__creation_user          alias for $10; -- default null
+  new__creation_ip            alias for $11; -- default null
+begin
+        return content_revision__new(new__title,
+                                     new__description,
+                                     new__publish_date,
+                                     new__mime_type,
+                                     new__nls_language,
+                                     new__text,
+                                     new__item_id,
+                                     new__revision_id,
+                                     new__creation_date,
+                                     new__creation_user,
+                                     new__creation_ip,
+                                     null,
+                                     null
+                                     );
+end;' language 'plpgsql';
+
+create or replace function content_revision__new (varchar,varchar,timestamptz,varchar,varchar,text,integer,integer,timestamptz,integer,varchar,integer)
+returns integer as '
+declare
+  new__title                  alias for $1;  
+  new__description            alias for $2;  -- default null  
+  new__publish_date           alias for $3;  -- default now()
+  new__mime_type              alias for $4;  -- default ''text/plain''
+  new__nls_language           alias for $5;  -- default null
+  new__text                   alias for $6;  -- default '' ''
+  new__item_id                alias for $7;  
+  new__revision_id            alias for $8;  -- default null
+  new__creation_date          alias for $9;  -- default now()
+  new__creation_user          alias for $10; -- default null
+  new__creation_ip            alias for $11; -- default null
+  new__content_length         alias for $12; -- default null
+begin
+        return content_revision__new(new__title,
+                                     new__description,
+                                     new__publish_date,
+                                     new__mime_type,
+                                     new__nls_language,
+                                     new__text,
+                                     new__item_id,
+                                     new__revision_id,
+                                     new__creation_date,
+                                     new__creation_user,
+                                     new__creation_ip,
+                                     new__content_length,
+                                     null
+                                     );
+end;' language 'plpgsql';
+
+-- function new
+create or replace function content_revision__new (varchar,varchar,timestamptz,varchar,varchar,text,integer,integer,timestamptz,integer,varchar,integer,integer)
+returns integer as '
+declare
+  new__title                  alias for $1;  
+  new__description            alias for $2;  -- default null  
+  new__publish_date           alias for $3;  -- default now()
+  new__mime_type              alias for $4;  -- default ''text/plain''
+  new__nls_language           alias for $5;  -- default null
+  new__text                   alias for $6;  -- default '' ''
+  new__item_id                alias for $7;  
+  new__revision_id            alias for $8;  -- default null
+  new__creation_date          alias for $9;  -- default now()
+  new__creation_user          alias for $10; -- default null
+  new__creation_ip            alias for $11; -- default null
+  new__content_length         alias for $12; -- default null
+  new__package_id             alias for $13; -- default null  
+  v_revision_id               integer;       
+  v_package_id                acs_objects.package_id%TYPE;
+  v_content_type              acs_object_types.object_type%TYPE;
+  v_storage_type              cr_items.storage_type%TYPE;
+  v_length                    cr_revisions.content_length%TYPE;
+begin
+
+  v_content_type := content_item__get_content_type(new__item_id);
+
+  if new__package_id is null then
+    v_package_id := acs_object__package_id(new__item_id);
+  else
+    v_package_id := new__package_id;
+  end if;
+
+  v_revision_id := acs_object__new(
+      new__revision_id,
+      v_content_type, 
+      new__creation_date, 
+      new__creation_user, 
+      new__creation_ip, 
+      new__item_id,
+      ''t'',
+      new__title,
+      v_package_id
+  );
+
+  select storage_type into v_storage_type
+    from cr_items
+   where item_id = new__item_id;
+
+  if v_storage_type = ''text'' then 
+     v_length := length(new__text);
+  else
+     v_length := coalesce(new__content_length,0);
+  end if;
+
+  -- text data is stored directly in cr_revisions using text datatype.
+
+  insert into cr_revisions (
+    revision_id, title, description, mime_type, publish_date,
+    nls_language, content, item_id, content_length
+  ) values (
+    v_revision_id, new__title, new__description,
+     new__mime_type, 
+    new__publish_date, new__nls_language, 
+    new__text, new__item_id, v_length
+  );
+
+  return v_revision_id;
+ 
+end;' language 'plpgsql';
