-- 
-- packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.3.0b2-5.3.0b3.sql
-- 
-- @author Dave Bauer (dave@thedesignexperience.org)
-- @creation-date 2007-01-10
-- @cvs-id $Id: upgrade-5.3.0b2-5.3.0b3.sql,v 1.2 2007/05/14 20:30:19 donb Exp $
--


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';