-- Data model to support content repository of the ArsDigita
-- Community System

-- Copyright (C) 1999-2000 ArsDigita Corporation
-- Author: Karl Goldstein (karlg@arsdigita.com)

-- $Id: content-revision.sql,v 1.19 2001/05/17 03:59:04 danw 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 body content_revision

-- function new
create function content_revision__new (varchar,varchar,timestamp,varchar,varchar,integer,integer,integer,timestamp,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
  v_revision_id               integer;       
  v_content_type              acs_object_types.object_type%TYPE;
begin

  v_content_type := content_item__get_content_type(new__item_id);

  v_revision_id := acs_object__new(
      new__revision_id,
      v_content_type, 
      new__creation_date, 
      new__creation_user, 
      new__creation_ip, 
      new__item_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 function content_revision__new(varchar,varchar,timestamp,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
               );

end;' language 'plpgsql';

create function content_revision__new (varchar,varchar,timestamp,varchar,varchar,text,integer,integer,timestamp,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
                                     );
end;' language 'plpgsql';

-- function new
create function content_revision__new (varchar,varchar,timestamp,varchar,varchar,text,integer,integer,timestamp,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
  v_revision_id               integer;       
  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);

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


-- procedure copy_attributes
create function content_revision__copy_attributes (varchar,integer,integer)
returns integer as '
declare
  copy_attributes__content_type           alias for $1;  
  copy_attributes__revision_id            alias for $2;  
  copy_attributes__copy_id                alias for $3;  
  v_table_name                            acs_object_types.table_name%TYPE;
  v_id_column                             acs_object_types.id_column%TYPE;
  cols                                    varchar default ''''; 
  attr_rec                                record;
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 select
                    attribute_name
                  from
                    acs_attributes
                  where
                    object_type = copy_attributes__content_type 
  LOOP
    cols := cols || '', '' || attr_rec.attribute_name;
  end loop;

  execute ''insert into '' || v_table_name || '' select '' || copy_id || 
          '' as '' || v_id_column || cols || '' from '' || 
          v_table_name || '' where '' || v_id_column || '' = '' || 
          copy_attributes__revision_id;
  
  return 0; 
end;' language 'plpgsql';


-- function copy
create function content_revision__copy (integer,integer,integer,integer,varchar)
returns integer as '
declare
  copy__revision_id            alias for $1;  
  copy__copy_id                alias for $2;  -- default null  
  copy__target_item_id         alias for $3;  -- default null
  copy__creation_user          alias for $4;  -- default null
  copy__creation_ip            alias for $5;  -- default null
  v_copy_id                    cr_revisions.revision_id%TYPE;
  v_target_item_id             cr_items.item_id%TYPE;
  type_rec                     record;
begin
  -- use the specified item_id or the item_id of the original revision 
  --   if none is specified
  if copy__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 := copy__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__copy_id is null then
    select acs_object_id_seq.nextval into v_copy_id from dual;
  else
    v_copy_id := copy__copy_id;
  end if;

  -- create the basic object
  insert into acs_objects 
       select 
         v_copy_id as object_id, 
         object_type, 
         context_id, 
         security_inherit_p, 
         copy__creation_user as creation_user, 
         now() as creation_date, 
         copy__creation_ip as creation_ip,
         now() as last_modified, 
         copy__creation_user as modifying_user, 
         copy__creation_ip as modifying_ip 
       from
         acs_objects 
       where 
         object_id = copy__revision_id;
  
  -- create the basic revision (using v_target_item_id)
  insert into cr_revisions 
      select 
        v_copy_id as revision_id, title, description, publish_date, 
        mime_type, nls_language, 
	content, v_target_item_id as item_id
      from 
        cr_revisions 
      where
        revision_id = copy__revision_id;

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

  -- iterate over the ancestor types and copy attributes
  for type_rec in select                                                
                    ot2.object_type, tree_level(ot2.tree_sortkey) as level
                  from                                                
                    acs_object_types ot1, acs_object_types ot2
                  where                                               
                    ot2.object_type <> ''acs_object''                       
                  and                                                 
                    ot2.object_type <> ''content_revision''
                  and 
                    ot1.object_type = (select object_type 
                                         from acs_objects 
                                        where object_id = copy__revision_id)
                  and 
                    ot2.tree_sortkey <= ot1.tree_sortkey
                  and 
                    ot1.tree_sortkey like (ot2.tree_sortkey || ''%'')
                  order by
                    level desc
  LOOP
    PERFORM content_revision__copy_attributes(type_rec.object_type, 
                                              copy__revision_id, v_copy_id);
  end loop;

  return v_copy_id;
 
end;' language 'plpgsql';


-- procedure delete
create function content_revision__delete (integer)
returns integer as '
declare
  delete__revision_id    alias for $1;  
  v_item_id              cr_items.item_id%TYPE;
  v_latest_revision      cr_revisions.revision_id%TYPE;
  v_live_revision        cr_revisions.revision_id%TYPE;
  v_rec                  record;                                      
begin

  -- Get item id and latest/live revisions
  select item_id into v_item_id from cr_revisions 
    where revision_id = delete__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 = delete__revision_id then
      for v_rec in 
          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 <> delete__revision_id
        order by o.creation_date desc 
      LOOP

          v_latest_revision := v_rec.revision_id;
          exit;
      end LOOP;
  end if; 

  if NOT FOUND then
     v_latest_revision := null;        
  end if;

  update cr_items set latest_revision = v_latest_revision
  where item_id = v_item_id;
 
  -- Clear live revision
  if v_live_revision = delete__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 = delete__revision_id
       or new_revision = delete__revision_id;

  -- Delete the revision
  PERFORM acs_object__delete(delete__revision_id);

  return 0; 
end;' language 'plpgsql';


-- function get_number
create function content_revision__get_number (integer)
returns number as '
declare
  get_number__revision_id            alias for $1;  
  v_number                           integer;       
  v_revision                         cr_revisions.revision_id%TYPE;
  row_count                          integer default 0;
begin
  for rev_cur in 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
  LOOP
    row_count := row_count + 1;
    if v_revision = get_number__revision_id then 
       v_number := row_count;
       exit;
    end if;
  end LOOP;

  return v_number;
 
end;' language 'plpgsql';


-- procedure index_attributes
create function content_revision__index_attributes (integer)
returns integer as '
declare
  index_attributes__revision_id            alias for $1;  
  clob_loc                                 text;          
  v_revision_id                            cr_revisions.revision_id%TYPE;
begin

  insert into cr_revision_attributes 
    select index_attributes__revision_id as revision_id, 
           clob_loc as attributes 

  -- FIXME: need to find a way to deal with these xml calls
  v_revision_id := write_xml(revision_id, clob_loc);  

  return 0; 
end;' language 'plpgsql';


-- function import_xml
create function content_revision__import_xml (integer,integer,numeric)
returns integer as '
declare
  import_xml__item_id                alias for $1;  
  import_xml__revision_id            alias for $2;  
  import_xml__doc_id                 alias for $3;  
  clob_loc                           text;          
  v_revision_id                      cr_revisions.revision_id%TYPE;
begin

  -- FIXME: need a way to deal with this xml call.
  select doc into clob_loc from cr_xml_docs where doc_id = import_xml__doc_id;
  v_revision_id := read_xml(import_xml__item_id, import_xml__revision_id, 
                            clob_loc);  

  return v_revision_id;
 
end;' language 'plpgsql';


-- function export_xml
create function content_revision__export_xml (integer)
returns integer as '
declare
  revision_id            alias for $1;  
  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, '''');

  -- FIXME: need a way to deal with this xml call.
  v_revision_id := write_xml(revision_id, clob_loc);  

  return v_doc_id;
 
end;' language 'plpgsql';


-- procedure to_html
create function content_revision__to_html (integer)
returns integer as '
declare
  to_html__revision_id            alias for $1;  
  tmp_clob                        text;          
  blob_loc                        integer;          
begin

  -- FIXME
  -- ctx_doc.filter(''cr_doc_filter_index'', revision_id, tmp_clob);

  select 
    content into blob_loc
  from 
    cr_revisions 
  where 
    revision_id = to_html__revision_id
  for update;

 PERFORM clob_to_blob(tmp_clob, blob_loc);

 PERFORM dbms_lob__freetemporary(tmp_clob);

 return 0; 
end;' language 'plpgsql';


-- function is_live
create function content_revision__is_live (integer)
returns boolean as '
declare
  is_live__revision_id            alias for $1;  
begin

  return count(*) > 0 from cr_items
   where live_revision = is_live__revision_id;

end;' language 'plpgsql';


-- function is_latest
create function content_revision__is_latest (integer)
returns boolean as '
declare
  is_latest__revision_id            alias for $1;  
begin

  return count(*) > 0 from cr_items
    where latest_revision = is_latest__revision_id;
 
end;' language 'plpgsql';


-- procedure to_temporary_clob
create function content_revision__to_temporary_clob (integer)
returns integer as '
declare
  to_temporary_clob__revision_id            alias for $1;  
  -- b                                         blob;          
  -- c                                         text;          
begin
  -- FIXME:  I cannot find an instance in the 4.2 beta code where this
  --         is used so I am 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 (
    revision_id, empty_clob()
  ) returning content into c;

  select content into b from cr_revisions 
    where revision_id = to_temporary_clob__revision_id;

  PERFORM blob_to_clob(b, c);
*/
  return 0; 
end;' language 'plpgsql';


-- procedure content_copy
create function content_revision__content_copy (integer,integer)
returns integer as '
declare
  content_copy__revision_id            alias for $1;  
  content_copy__revision_id_dest       alias for $2;  -- default null  
  lobs                                 blob;          
  lobd                                 blob;          
  v_item_id                            cr_items.item_id%TYPE;
  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

  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 r.content, r.content_length, r.lob, i.storage_type 
      into v_content, v_content_length, v_lob, v_storage_type
      from cr_revisions r, cr_items i 
     where r.item_id = i.item_id 
       and r.revision_id = content_copy__revision_id

    if v_storage_type = ''lob'' then
        v_new_lob := empty_lob();

        update cr_revisions
           set content = null,
               content_length = v_content_length,
               lob = v_new_lob
         where revision_id = v_revision_id_dest;
        PERFORM lob_copy(v_lob, v_new_lob);
    else 
        -- this will work for both file and text types... well sort of.
        -- this really just creates a reference to the first file which is
        -- wrong since, the item_id, revision_id uniquely describes the 
        -- location of the file in the content repository file system.  
        -- after copy is called, the content attribute needs to be updated 
        -- with the new relative file path:

        -- update cr_revisions
        -- set content = ''[cr_create_content_file $item_id $revision_id [cr_fs_path]$old_rel_path]''
        -- where revision_id = :revision_id
        
        -- old_rel_path is the content attribute value of the content revision
        -- that is being copied.
        update cr_revisions
           set content = v_content,
               content_length = v_content_length,
               lob = null
         where revision_id = v_revision_id_dest;
    end if;

  end if;

  return 0; 
end;' language 'plpgsql';


-- procedure content__get_content
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                            integer;
  v_data                              text;
begin
       select i.storage_type, r.lob, 
         into v_storage_type, v_lob_id
         from cr_items i, cr_revisions r
        where i.item_id = r.item_id 
          and r.revision_id = get_content__revision_id;
        
        if v_storage_type = ''lob'' then
           return v_lob_id::text;
        else 
           return content
             from cr_revisions
            where revision_id = get_content_revision_id;
        end if;

end;' language 'plpgsql';


-- show errors

-- Trigger to maintain latest_revision in cr_items

create function cr_revision_latest_tr () returns opaque as '
begin
  update cr_items set latest_revision = new.revision_id
  where item_id = new.item_id;
  return new;
end;' language 'plpgsql';

create trigger cr_revision_latest_tr after insert on cr_revisions
for each row execute procedure cr_revision_latest_tr ();

-- show errors