-- 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.50.2.1 2021/06/18 13:57:52 hectorr 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.
-- function new
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');
--
-- content_revision__new/13
--
-- We can't use for the last two arguments "default null", since
-- otherwise calls with provided package_id but no content_length
-- would lead to a wrong interpretation of the package_id as
-- content_length.
--
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 content_revision__new/11 content_revision__new/12
--
-- text/file version
--
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__package_id integer default null
) RETURNS integer AS $$
DECLARE
BEGIN
raise NOTICE 'content_revision__new/12 is deprecated, call content_revision__new/13 instead';
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, -- content_length
new__package_id
);
END
$$ LANGUAGE plpgsql;
--
-- procedure content_revision__new/11 content_revision__new/12
--
-- lob version
--
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
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/6 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
raise NOTICE 'content_revision__new/7 is deprecated, call content_revision__new/13 instead';
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 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
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;
--
-- Delete a content revisions
--
-- procedure content_revision__del/1
--
select define_function_args('content_revision__del','revision_id');
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;
BEGIN
--
-- Get item_id and the latest revision
--
select item_id
into v_item_id
from cr_revisions
where revision_id = delete__revision_id;
select latest_revision
into v_latest_revision
from cr_items
where item_id = v_item_id;
--
-- Recalculate latest revision in case it was deleted
--
if v_latest_revision = delete__revision_id then
select r.revision_id
into v_latest_revision
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 limit 1;
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;
--
-- 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,
r.revision_id
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 cr_items.storage_type%TYPE;
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 cr_items.storage_type%TYPE;
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;
--
-- Trigger to maintain latest_revision in cr_items
--
CREATE OR REPLACE FUNCTION cr_revision_latest_tr () RETURNS trigger AS $$
DECLARE
v_content_type cr_items.content_type%TYPE;
BEGIN
select content_type from cr_items into v_content_type where item_id = new.item_id;
--
-- Don't set the latest revision via trigger, since other means in
-- the xotcl-core frame work take care for it. This is not the most
-- general solution, but improves the situation for busy sites.
--
if substring(v_content_type,1,2) != '::' then
update cr_items set latest_revision = new.revision_id
where item_id = new.item_id;
end if;
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
OpenACS – The Toolkit for Online Communities : ACS API Browser : ACS Content Repository 5.10.1 : content-revision.sql
Begin main content
