-- Data model to support content repository of the ArsDigita
-- Community System
-- Copyright (C) 1999-2000 ArsDigita Corporation
-- Author: Stanislav Freidin (sfreidin@arsdigita.com)
-- 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
select define_function_args ('content_keyword__get_heading','keyword_id');
--
-- procedure content_keyword__get_heading/1
--
CREATE OR REPLACE FUNCTION content_keyword__get_heading(
get_heading__keyword_id integer
) RETURNS text AS $$
DECLARE
v_heading text;
BEGIN
select heading into v_heading from cr_keywords
where keyword_id = get_heading__keyword_id;
return v_heading;
END;
$$ LANGUAGE plpgsql stable strict;
-- function get_description
select define_function_args ('content_keyword__get_description','keyword_id');
--
-- procedure content_keyword__get_description/1
--
CREATE OR REPLACE FUNCTION content_keyword__get_description(
get_description__keyword_id integer
) RETURNS text AS $$
DECLARE
v_description text;
BEGIN
select description into v_description from cr_keywords
where keyword_id = get_description__keyword_id;
return v_description;
END;
$$ LANGUAGE plpgsql stable strict;
-- procedure set_heading
select define_function_args ('content_keyword__set_heading','keyword_id,heading');
--
-- procedure content_keyword__set_heading/2
--
CREATE OR REPLACE FUNCTION content_keyword__set_heading(
set_heading__keyword_id integer,
set_heading__heading varchar
) RETURNS integer AS $$
DECLARE
BEGIN
update cr_keywords set
heading = set_heading__heading
where
keyword_id = set_heading__keyword_id;
update acs_objects
set title = set_heading__heading
where object_id = set_heading__keyword_id;
return 0;
END;
$$ LANGUAGE plpgsql;
-- procedure set_description
select define_function_args ('content_keyword__set_description','keyword_id,description');
--
-- procedure content_keyword__set_description/2
--
CREATE OR REPLACE FUNCTION content_keyword__set_description(
set_description__keyword_id integer,
set_description__description varchar
) RETURNS integer AS $$
DECLARE
BEGIN
update cr_keywords set
description = set_description__description
where
keyword_id = set_description__keyword_id;
return 0;
END;
$$ LANGUAGE plpgsql;
-- function is_leaf
select define_function_args ('content_keyword__is_leaf','keyword_id');
--
-- procedure content_keyword__is_leaf/1
--
CREATE OR REPLACE FUNCTION content_keyword__is_leaf(
is_leaf__keyword_id integer
) RETURNS boolean AS $$
DECLARE
BEGIN
return
count(*) = 0
from
cr_keywords k
where
k.parent_id = is_leaf__keyword_id;
END;
$$ LANGUAGE plpgsql stable;
--
-- procedure content_keyword__new/9
--
select define_function_args('content_keyword__new','heading,description;null,parent_id;null,keyword_id;null,creation_date;now,creation_user;null,creation_ip;null,object_type;content_keyword,package_id;null');
CREATE OR REPLACE FUNCTION content_keyword__new(
new__heading varchar,
new__description varchar, -- default null
new__parent_id integer, -- default null
new__keyword_id integer, -- default null
new__creation_date timestamptz, -- default now() -- default 'now'
new__creation_user integer, -- default null
new__creation_ip varchar, -- default null
new__object_type varchar, -- default 'content_keyword'
new__package_id integer -- default null
) RETURNS integer AS $$
DECLARE
v_id integer;
v_package_id acs_objects.package_id%TYPE;
BEGIN
if new__package_id is null then
v_package_id := acs_object__package_id(new__parent_id);
else
v_package_id := new__package_id;
end if;
v_id := acs_object__new (new__keyword_id,
new__object_type,
new__creation_date,
new__creation_user,
new__creation_ip,
new__parent_id,
't',
new__heading,
v_package_id
);
insert into cr_keywords
(heading, description, keyword_id, parent_id)
values
(new__heading, new__description, v_id, new__parent_id);
return v_id;
END;
$$ LANGUAGE plpgsql;
--
-- procedure content_keyword__new/8
--
CREATE OR REPLACE FUNCTION content_keyword__new(
new__heading varchar,
new__description varchar, -- default null
new__parent_id integer, -- default null
new__keyword_id integer, -- default null
new__creation_date timestamptz, -- default now() -- default 'now'
new__creation_user integer, -- default null
new__creation_ip varchar, -- default null
new__object_type varchar -- default 'content_keyword'
) RETURNS integer AS $$
DECLARE
BEGIN
return content_keyword__new(new__heading,
new__description,
new__parent_id,
new__keyword_id,
new__creation_date,
new__creation_user,
new__creation_ip,
new__object_type,
null
);
END;
$$ LANGUAGE plpgsql;
-- procedure delete
select define_function_args ('content_keyword__del','keyword_id');
--
-- procedure content_keyword__del/1
--
CREATE OR REPLACE FUNCTION content_keyword__del(
delete__keyword_id integer
) RETURNS integer AS $$
DECLARE
v_rec record;
BEGIN
for v_rec in select item_id from cr_item_keyword_map
where keyword_id = delete__keyword_id LOOP
PERFORM content_keyword__item_unassign(v_rec.item_id, delete__keyword_id);
end LOOP;
PERFORM acs_object__delete(delete__keyword_id);
return 0;
END;
$$ LANGUAGE plpgsql;
-- added
select define_function_args('content_keyword__delete','keyword_id');
--
-- procedure content_keyword__delete/1
--
CREATE OR REPLACE FUNCTION content_keyword__delete(
delete__keyword_id integer
) RETURNS integer AS $$
DECLARE
v_rec record;
BEGIN
perform content_keyword__del(delete__keyword_id);
return 0;
END;
$$ LANGUAGE plpgsql;
-- procedure item_assign
select define_function_args ('content_keyword__item_assign','item_id,keyword_id,context_id;null,creation_user;null,creation_ip;null');
--
-- procedure content_keyword__item_assign/5
--
CREATE OR REPLACE FUNCTION content_keyword__item_assign(
item_assign__item_id integer,
item_assign__keyword_id integer,
item_assign__context_id integer, -- default null
item_assign__creation_user integer, -- default null
item_assign__creation_ip varchar -- default null
) RETURNS integer AS $$
DECLARE
exists_p boolean;
BEGIN
-- Do nothing if the keyword is assigned already
select count(*) > 0 into exists_p from dual
where exists (select 1 from cr_item_keyword_map
where item_id = item_assign__item_id
and keyword_id = item_assign__keyword_id);
if NOT exists_p then
insert into cr_item_keyword_map (
item_id, keyword_id
) values (
item_assign__item_id, item_assign__keyword_id
);
end if;
return 0;
END;
$$ LANGUAGE plpgsql;
-- procedure item_unassign
select define_function_args ('content_keyword__item_unassign','item_id,keyword_id');
--
-- procedure content_keyword__item_unassign/2
--
CREATE OR REPLACE FUNCTION content_keyword__item_unassign(
item_unassign__item_id integer,
item_unassign__keyword_id integer
) RETURNS integer AS $$
DECLARE
BEGIN
delete from cr_item_keyword_map
where item_id = item_unassign__item_id
and keyword_id = item_unassign__keyword_id;
return 0;
END;
$$ LANGUAGE plpgsql;
-- function is_assigned
select define_function_args ('content_keyword__is_assigned','item_id,keyword_id,recurse;none');
--
-- procedure content_keyword__is_assigned/3
--
CREATE OR REPLACE FUNCTION content_keyword__is_assigned(
is_assigned__item_id integer,
is_assigned__keyword_id integer,
is_assigned__recurse varchar -- default 'none'
) RETURNS boolean AS $$
DECLARE
v_ret boolean;
v_is_assigned__recurse varchar;
BEGIN
if is_assigned__recurse is null then
v_is_assigned__recurse := 'none';
else
v_is_assigned__recurse := is_assigned__recurse;
end if;
-- Look for an exact match
if v_is_assigned__recurse = 'none' then
return count(*) > 0 from cr_item_keyword_map
where item_id = is_assigned__item_id
and keyword_id = is_assigned__keyword_id;
end if;
-- Look from specific to general
if v_is_assigned__recurse = 'up' then
return count(*) > 0
where exists (select 1
from (select keyword_id from cr_keywords c, cr_keywords c2
where c2.keyword_id = is_assigned__keyword_id
and c.tree_sortkey between c2.tree_sortkey and tree_right(c2.tree_sortkey)) t,
cr_item_keyword_map m
where t.keyword_id = m.keyword_id
and m.item_id = is_assigned__item_id);
end if;
if v_is_assigned__recurse = 'down' then
return count(*) > 0
where exists (select 1
from (select k2.keyword_id
from cr_keywords k1, cr_keywords k2
where k1.keyword_id = is_assigned__keyword_id
and k1.tree_sortkey between k2.tree_sortkey and tree_right(k2.tree_sortkey)) t,
cr_item_keyword_map m
where t.keyword_id = m.keyword_id
and m.item_id = is_assigned__item_id);
end if;
-- Tried none, up and down - must be an invalid parameter
raise EXCEPTION '-20000: The recurse parameter to content_keyword.is_assigned should be ''none'', ''up'' or ''down''';
return null;
END;
$$ LANGUAGE plpgsql stable;
-- function get_path
select define_function_args ('content_keyword__get_path','keyword_id');
--
-- procedure content_keyword__get_path/1
--
CREATE OR REPLACE FUNCTION content_keyword__get_path(
get_path__keyword_id integer
) RETURNS text AS $$
DECLARE
v_path text default '';
v_is_found boolean default 'f';
v_heading cr_keywords.heading%TYPE;
v_rec record;
BEGIN
-- select
-- heading
-- from (
-- select
-- heading, level as tree_level
-- from cr_keywords
-- connect by prior parent_id = keyword_id
-- start with keyword_id = get_path.keyword_id) k
-- order by
-- tree_level desc
for v_rec in select heading
from (select k2.heading, tree_level(k2.tree_sortkey) as tree_level
from cr_keywords k1, cr_keywords k2
where k1.keyword_id = get_path__keyword_id
and k1.tree_sortkey between k2.tree_sortkey and tree_right(k2.tree_sortkey)) k
order by tree_level desc
LOOP
v_heading := v_rec.heading;
v_is_found := 't';
v_path := v_path || '/' || v_heading;
end LOOP;
if v_is_found = 'f' then
return null;
else
return v_path;
end if;
END;
$$ LANGUAGE plpgsql stable strict;
-- Ensure that the context_id in acs_objects is always set to the
-- parent_id in cr_keywords
CREATE OR REPLACE FUNCTION cr_keywords_update_tr () RETURNS trigger AS $$
BEGIN
if old.parent_id <> new.parent_id then
update acs_objects set context_id = new.parent_id
where object_id = new.keyword_id;
end if;
return new;
END;
$$ LANGUAGE plpgsql;
create trigger cr_keywords_update_tr after update on cr_keywords
for each row execute procedure cr_keywords_update_tr ();
-- show errors
OpenACS – The Toolkit for Online Communities : ACS API Browser : ACS Content Repository 5.10.1 : content-keyword.sql
Begin main content
