-- 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.47 2011/07/07 10:46:02 gustafn 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

-- DRB: BLOB issues make it impractical to use package_instantiate_object to create
-- new revisions that contain binary data so a higher-level Tcl API is required rather
-- than the standard package_instantiate_object.  So we don't bother calling define_function_args
-- here.



-- added

--
-- procedure content_revision__new/12
--
CREATE OR REPLACE FUNCTION content_revision__new(
   new__title varchar,
   new__description varchar,       -- default null
   new__publish_date timestamptz,  -- default now()
   new__mime_type varchar,         -- default 'text/plain'
   new__nls_language varchar,      -- default null
   new__data integer,
   new__item_id integer,
   new__revision_id integer,       -- default null
   new__creation_date timestamptz, -- default now()
   new__creation_user integer,     -- default null
   new__creation_ip varchar,       -- default null
   new__package_id integer         -- default null

) RETURNS integer AS $$
DECLARE
  -- lob id 
  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;



--
-- procedure content_revision__new/11
--
CREATE OR REPLACE FUNCTION content_revision__new(
   new__title varchar,
   new__description varchar,       -- default null
   new__publish_date timestamptz,  -- default now()
   new__mime_type varchar,         -- default 'text/plain'
   new__nls_language varchar,      -- default null
   new__data integer,
   new__item_id integer,
   new__revision_id integer,       -- default null
   new__creation_date timestamptz, -- default now()
   new__creation_user integer,     -- default null
   new__creation_ip varchar        -- default null

) RETURNS integer AS $$
DECLARE
  -- lob id 
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;



--
-- procedure content_revision__new/7
--
CREATE OR REPLACE FUNCTION content_revision__new(
   new__title varchar,
   new__description varchar,      -- default null
   new__publish_date timestamptz, -- default now()
   new__mime_type varchar,        -- default 'text/plain'
   new__text text,                -- default ' '
   new__item_id integer,
   new__package_id integer        -- default null

) RETURNS integer AS $$
DECLARE
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;



--
-- procedure content_revision__new/6
--
CREATE OR REPLACE FUNCTION content_revision__new(
   new__title varchar,
   new__description varchar,      -- default null
   new__publish_date timestamptz, -- default now()
   new__mime_type varchar,        -- default 'text/plain'
   new__text text,                -- default ' '
   new__item_id integer

) RETURNS integer AS $$
DECLARE
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;



--
-- procedure content_revision__new/11
--
CREATE OR REPLACE FUNCTION content_revision__new(
   new__title varchar,
   new__description varchar,       -- default null
   new__publish_date timestamptz,  -- default now()
   new__mime_type varchar,         -- default 'text/plain'
   new__nls_language varchar,      -- default null
   new__text text,                 -- default ' '
   new__item_id integer,
   new__revision_id integer,       -- default null
   new__creation_date timestamptz, -- default now()
   new__creation_user integer,     -- default null
   new__creation_ip varchar        -- default null

) RETURNS integer AS $$
DECLARE
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;



-- added

--
-- procedure content_revision__new/12
--
CREATE OR REPLACE FUNCTION content_revision__new(
   new__title varchar,
   new__description varchar,       -- default null
   new__publish_date timestamptz,  -- default now()
   new__mime_type varchar,         -- default 'text/plain'
   new__nls_language varchar,      -- default null
   new__text text,                 -- default ' '
   new__item_id integer,
   new__revision_id integer,       -- default null
   new__creation_date timestamptz, -- default now()
   new__creation_user integer,     -- default null
   new__creation_ip varchar,       -- default null
   new__content_length integer     -- default null

) RETURNS integer AS $$
DECLARE
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


-- added
select define_function_args('content_revision__new','title,description;null,publish_date;now(),mime_type;text/plain,nls_language;null,text; ,item_id,revision_id;null,creation_date;now(),creation_user;null,creation_ip;null,content_length;null,package_id;null');

--
-- procedure content_revision__new/13
--
CREATE OR REPLACE FUNCTION content_revision__new(
   new__title varchar,
   new__description varchar,       -- default null
   new__publish_date timestamptz,  -- default now()
   new__mime_type varchar,         -- default 'text/plain'
   new__nls_language varchar,      -- default null
   new__text text,                 -- default ' '
   new__item_id integer,
   new__revision_id integer,       -- default null
   new__creation_date timestamptz, -- default now()
   new__creation_user integer,     -- default null
   new__creation_ip varchar,       -- default null
   new__content_length integer,    -- default null
   new__package_id integer         -- default null

) RETURNS integer AS $$
DECLARE
  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;


-- procedure copy_attributes
select define_function_args('content_revision__copy_attributes','content_type,revision_id,copy_id');


--
-- procedure content_revision__copy_attributes/3
--
CREATE OR REPLACE FUNCTION content_revision__copy_attributes(
   copy_attributes__content_type varchar,
   copy_attributes__revision_id integer,
   copy_attributes__copy_id integer
) RETURNS integer AS $$
DECLARE
  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

  if copy_attributes__content_type is null or copy_attributes__revision_id is null or copy_attributes__copy_id is null then 
     raise exception 'content_revision__copy_attributes called with null % % %',copy_attributes__content_type,copy_attributes__revision_id, copy_attributes__copy_id;
  end if;

  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 || '(' || v_id_column || cols || ')' || ' select ' || copy_attributes__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

-- old define_function_args('content_revision__copy','revision_id,copy_id,target_item_id,creation_user,creation_ip')
-- new
select define_function_args('content_revision__copy','revision_id,copy_id;null,target_item_id;null,creation_user;null,creation_ip;null');



--
-- procedure content_revision__copy/5
--
CREATE OR REPLACE FUNCTION content_revision__copy(
   copy__revision_id integer,
   copy__copy_id integer,        -- default null
   copy__target_item_id integer, -- default null
   copy__creation_user integer,  -- default null
   copy__creation_ip varchar     -- default null

) RETURNS integer AS $$
DECLARE
  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 nextval('t_acs_object_id_seq') into v_copy_id from dual;
  else
    v_copy_id := copy__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 as object_id,
         object_type,
         v_target_item_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,
         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 
      select 
        v_copy_id as revision_id, 
        v_target_item_id as item_id, 
        title, 
        description, 
        publish_date, 
        mime_type, 
        nls_language, 
        lob,
	content,
        content_length
      from 
        cr_revisions 
      where
        revision_id = copy__revision_id;

  -- 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, acs_objects o
                  where ot2.object_type <> 'acs_object'                       
                    and ot2.object_type <> 'content_revision'
                    and o.object_id = copy__revision_id 
                    and ot1.object_type = o.object_type 
                    and ot1.tree_sortkey between ot2.tree_sortkey and tree_right(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
select define_function_args('content_revision__del','revision_id');


--
-- procedure content_revision__del/1
--
CREATE OR REPLACE FUNCTION content_revision__del(
   delete__revision_id integer
) RETURNS integer AS $$
DECLARE
  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;
      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;    
  end if; 
 
  -- 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;

select define_function_args('content_revision__delete','revision_id');



--
-- procedure content_revision__delete/1
--
CREATE OR REPLACE FUNCTION content_revision__delete(
   delete__revision_id integer
) RETURNS integer AS $$
DECLARE
BEGIN
        PERFORM content_revision__del(delete__revision_id);
  return 0; 
END;
$$ LANGUAGE plpgsql;

-- function get_number
select define_function_args('content_revision__get_number','revision_id');


--
-- procedure content_revision__get_number/1
--
CREATE OR REPLACE FUNCTION content_revision__get_number(
   get_number__revision_id integer
) RETURNS integer AS $$
DECLARE
  v_revision                         cr_revisions.revision_id%TYPE;
  v_row_count                        integer default 0;
  rev_cur                            record;
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
    v_row_count := v_row_count + 1;
    if rev_cur.revision_id = get_number__revision_id then 
       return v_row_count;
       exit;
    end if;
  end LOOP;

  return null;
 
END;
$$ LANGUAGE plpgsql stable strict;

select define_function_args('content_revision__revision_name','revision_id');


--
-- procedure content_revision__revision_name/1
--
CREATE OR REPLACE FUNCTION content_revision__revision_name(
   p_revision_id integer
) RETURNS text AS $$
DECLARE
BEGIN
        return 'Revision ' || content_revision__get_number(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 = p_revision_id;
END;
$$ LANGUAGE plpgsql stable strict;

-- procedure to_html
select define_function_args('content_revision__to_html','revision_id');


--
-- procedure content_revision__to_html/1
--
CREATE OR REPLACE FUNCTION content_revision__to_html(
   to_html__revision_id integer
) RETURNS integer AS $$
DECLARE
  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
select define_function_args('content_revision__is_live','revision_id');


--
-- procedure content_revision__is_live/1
--
CREATE OR REPLACE FUNCTION content_revision__is_live(
   is_live__revision_id integer
) RETURNS boolean AS $$
DECLARE
BEGIN

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

END;
$$ LANGUAGE plpgsql strict;


-- function is_latest
select define_function_args('content_revision__is_latest','revision_id');


--
-- procedure content_revision__is_latest/1
--
CREATE OR REPLACE FUNCTION content_revision__is_latest(
   is_latest__revision_id integer
) RETURNS boolean AS $$
DECLARE
BEGIN

  return count(*) > 0 from cr_items
    where latest_revision = is_latest__revision_id;
 
END;
$$ LANGUAGE plpgsql stable;


-- procedure to_temporary_clob


-- added
select define_function_args('content_revision__to_temporary_clob','revision_id');

--
-- procedure content_revision__to_temporary_clob/1
--
CREATE OR REPLACE FUNCTION content_revision__to_temporary_clob(
   to_temporary_clob__revision_id integer
) RETURNS integer AS $$
DECLARE
  -- 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

-- old define_function_args('content_revision__content_copy','revision_id,revision_id_dest')
-- new
select define_function_args('content_revision__content_copy','revision_id,revision_id_dest;null');



--
-- procedure content_revision__content_copy/2
--
CREATE OR REPLACE FUNCTION content_revision__content_copy(
   content_copy__revision_id integer,
   content_copy__revision_id_dest integer -- default null

) RETURNS integer AS $$
DECLARE
  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_storage_type                       varchar;
BEGIN
  if content_copy__revision_id is null then 
	raise exception 'content_revision__content_copy attempt to copy a null revision_id';
  end if;

  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();

	PERFORM lob_copy(v_lob, v_new_lob);

        update cr_revisions
           set content = null,
               content_length = v_content_length,
               lob = v_new_lob
         where revision_id = v_revision_id_dest;
	-- this call has to be before the above instruction,
	-- because lob references the v_new_lob 
	--        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
select define_function_args('content_revision__get_content','revision_id');


--
-- procedure content_revision__get_content/1
--
CREATE OR REPLACE FUNCTION content_revision__get_content(
   get_content__revision_id integer
) RETURNS text AS $$
DECLARE
  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 stable strict;

-- show errors

-- Trigger to maintain latest_revision in cr_items

CREATE OR REPLACE FUNCTION cr_revision_latest_tr () RETURNS trigger 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