The APEX QuickSQL utility has the "Tags Framework" flag.
Anyone has a detailed explanation about when and how to use it?
The tag framework is a mechanism to support the need of your application to record "tags" against tables.
eg If you were building a bug database for your application for developers to use, as they record a bug, they would probably record some useful tags about it. The framework gives you a solid foundation to implement that. It will generate the following
-- tag framework
create table tags (
id number not null primary key,
tag varchar2(255) not null enable,
content_pk number,
content_table varchar2(128),
created timestamp with local time zone not null,
created_by varchar2(255) not null,
updated timestamp with local time zone,
updated_by varchar2(255) )
;
create or replace trigger tags_biu
before insert or update on tags
for each row
begin
if inserting then
if :new.id is null then
:new.id := to_number(sys_guid(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
end if;
:new.created := localtimestamp;
:new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end if;
if updating then
:new.created := localtimestamp;
:new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end if;
end tags_biu;
/
create table tags_tsum (
tag varchar2(255),
content_table varchar2(128),
tag_count number,
constraint tags_tspk primary key (tag,content_table) )
;
create table tags_sum (
tag varchar2(255),
tag_count number,
constraint tags_spk primary key (tag) )
;
create or replace procedure tags_sync (
p_new_tags in varchar2,
p_old_tags in varchar2,
p_content_table in varchar2,
p_content_pk in number )
as
type tags is table of varchar2(255) index by varchar2(255);
type tag_values is table of varchar2(32767) index by binary_integer;
l_new_tags_a tags;
l_old_tags_a tags;
l_new_tags tag_values;
l_old_tags tag_values;
l_merge_tags tag_values;
l_dummy_tag varchar2(255);
i integer;
function string_to_table (
str in varchar2,
sep in varchar2 default ':')
return tag_values
is
temp tag_values;
l_str varchar2(32767) := str;
pos pls_integer;
i pls_integer := 1;
l_sep_length pls_integer := length(sep);
begin
if str is null or sep is null then
return temp;
end if;
if substr( l_str, 1, l_sep_length ) = sep then
l_str := substr( l_str, l_sep_length + 1 );
end if;
if substr( l_str, length( l_str ) - l_sep_length + 1 ) = sep then
l_str := substr( l_str, 1, length( l_str ) - l_sep_length );
end if;
loop
pos := instr( l_str, sep );
exit when nvl(pos,0) = 0;
temp(i) := substr( l_str, 1, pos-1 );
l_str := substr( l_str, pos + l_sep_length );
i := i + 1;
end loop;
temp(i) := trim(l_str);
return temp;
exception when others then return temp;
end;
begin
l_old_tags := string_to_table(p_old_tags,',');
l_new_tags := string_to_table(p_new_tags,',');
if l_old_tags.count > 0 then --do inserts and deletes
--build the associative arrays
for i in 1..l_old_tags.count loop
l_old_tags_a(l_old_tags(i)) := l_old_tags(i);
end loop;
for i in 1..l_new_tags.count loop
l_new_tags_a(l_new_tags(i)) := l_new_tags(i);
end loop;
--do the inserts
for i in 1..l_new_tags.count loop
begin
l_dummy_tag := l_old_tags_a(l_new_tags(i));
exception when no_data_found then
insert into tags (tag, content_pk, content_table )
values (trim(l_new_tags(i)), p_content_pk, p_content_table );
l_merge_tags(l_merge_tags.count + 1) := l_new_tags(i);
end;
end loop;
--do the deletes
for i in 1..l_old_tags.count loop
begin
l_dummy_tag := l_new_tags_a(l_old_tags(i));
exception when no_data_found then
delete from tags where content_pk = p_content_pk and tag = l_old_tags(i);
l_merge_tags(l_merge_tags.count + 1) := l_old_tags(i);
end;
end loop;
else --just do inserts
if l_new_tags.exists(1) then
for i in 1..l_new_tags.count loop
insert into tags (tag, content_pk, content_table )
values (trim(l_new_tags(i)), p_content_pk, p_content_table );
l_merge_tags(l_merge_tags.count + 1) := l_new_tags(i);
end loop;
end if;
end if;
for i in 1..l_merge_tags.count loop
merge into tags_tsum s
using (select count(*) tag_count
from tags
where tag = l_merge_tags(i) and content_table = p_content_table ) t
on (s.tag = l_merge_tags(i) and s.content_table = p_content_table )
when not matched then insert (tag, content_table, tag_count)
values (trim(l_merge_tags(i)), p_content_table, t.tag_count)
when matched then update set s.tag_count = t.tag_count;
merge into tags_sum s
using (select sum(tag_count) tag_count
from tags_tsum
where tag = l_merge_tags(i) ) t
on (s.tag = l_merge_tags(i) )
when not matched then insert (tag, tag_count)
values (trim(l_merge_tags(i)), t.tag_count)
when matched then update set s.tag_count = t.tag_count;
end loop;
end tags_sync;
/
Notice the content_pk/content_table columns - basically we're centralising tag management from whatever application tables you want to add the concept to.