Here is the initial data model that Neophytos worked out from our conversations on IRC. It is just a first draft, but maybe there is something here to work with. The APIs still need to be worked on and I think that is very important to work out the operations it will need to perform.
Let's use this to continue this discussion.
Dave
------------------
-- OBJECT TYPES --
------------------
select acs_object_type__create_type (
'category', -- object_type
'Category', -- pretty_name
'Categories', -- pretty_plural
'acs_object', -- supertype
'categories', -- table_name
'category_id', -- id_column
null, -- package_name
'f', -- abstract_p
null, -- type_extension_table
null -- name_method
);
------------
-- TABLES --
------------
create table categories (
category_id integer
constraint categories_category_id_fk
references acs_objects(object_id)
on delete cascade
constraint categories_category_id_pk
primary key,
pretty_name varchar(200)
constraint categories_pretty_name_nn
not null,
parent_id integer default 0,
tree_sortkey varbit,
constraint categories_category_object_un
unique (parent_id, category_id)
);
create table object_category_map (
object_id integer
constraint ocm_object_id_nn
not null
constraint ocm_object_id_fk
references acs_objects(object_id)
on delete cascade,
object_type varchar(100)
constraint ocm_object_type_nn
not null
constraint ocm_object_type_fk
references acs_object_types(object_type)
on delete cascade,
category_id integer
constraint ocm_category_id_nn
not null
constraint ocm_category_id_fk
references categories(category_id)
on delete cascade
);
-------------------------------
-- One Root to Bind Them All --
-------------------------------
insert into categories (
category_id,
pretty_name,
parent_id,
tree_sortkey
) values (
0,
'One Root to Bind Them All',
0,
int_to_tree_key(0)
);
------------------------------------------------
-- Add Foreign Key Constraint on 'parent_id' --
------------------------------------------------
alter table categories add constraint categories_parent_id_fk
foreign key (parent_id) references categories (category_id);
-------------
-- INDICES --
-------------
create index cat_parent_category_idx on categories (parent_id, category_id);
create index cat_tree_sortkey_idx on categories (tree_sortkey);
--------------
-- TRIGGERS --
--------------
create function categories_insert_tr () returns opaque as '
declare
v_parent_sk varbit default null;
v_max_value integer;
begin
select max(tree_leaf_key_to_int(tree_sortkey)) into v_max_value
from categories
where parent_id = new.parent_id;
select tree_sortkey into v_parent_sk
from categories
where category_id = new.parent_id;
new.tree_sortkey := tree_next_key(v_parent_sk, v_max_value);
return new;
end;' language 'plpgsql';
create trigger categories_insert_tr before insert on categories
for each row execute procedure categories_insert_tr ();
create function categories_update_tr () returns opaque as '
declare
v_parent_sk varbit default null;
v_max_value integer;
v_parent_id integer;
v_rec record;
clr_keys_p boolean default ''t'';
begin
if (new.category_id = old.category_id) and
(new.parent_id = old.parent_id) then
return new;
end if;
for v_rec in select category_id
from categories
where tree_sortkey between new.tree_sortkey and tree_right(new.tree_sortkey)
order by tree_sortkey
loop
if clr_keys_p then
update categories set tree_sortkey = null
where tree_sortkey between new.tree_sortkey and tree_right(new.tree_sortkey);
clr_keys_p := ''f'';
end if;
select parent_id into v_parent_id
from categories
where category_id = v_rec.category_id;
select max(tree_leaf_key_to_int(tree_sortkey)) into v_max_value
from categories
where parent_id = v_parent_id;
select tree_sortkey into v_parent_sk
from categories
where category_id = v_parent_id;
update categories
set tree_sortkey = tree_next_key(v_parent_sk, v_max_value)
where category_id = v_rec.category_id;
end loop;
return new;
end;' language 'plpgsql';
create trigger categories_update_tr after update on categories
for each row execute procedure categories_update_tr ();
--------------
-- Packages --
--------------
create function category__new(varchar,integer)
returns integer as '
declare
p_pretty_name alias for $1;
p_parent_id alias for $2;
v_category_id integer;
begin
v_category_id := acs_object__new(
null,
''category'',
now(),
null,
null,
null
);
insert into categories (
category_id,
pretty_name,
parent_id
) values (
v_category_id,
p_pretty_name,
p_parent_id
);
return v_category_id;
end;' language 'plpgsql';
create function category__get_name(integer)
returns varchar as '
declare
p_category_id alias for $1;
v_pretty_name varchar;
begin
select pretty_name into v_pretty_name
from categories
where category_id = p_category_id;
return v_pretty_name;
end;' language 'plpgsql';
create function category__delete(integer)
returns integer as '
declare
p_category_id alias for $1;
begin
delete from categories
where category_id = p_category_id;
return 0;
end;' language 'plpgsql';
create function object_category_map__new(integer,varchar,integer)
returns integer as '
declare
p_object_id alias for $1;
p_object_type alias for $2;
p_category_id alias for $3;
begin
insert into object_category_map (
object_id,
object_type,
category_id
) values (
p_object_id,
p_object_type,
p_category_id
);
returns 0;
end;' language 'plpgsql';
create function object_category_map__new(integer,integer)
returns integer as '
declare
p_object_id alias for $1;
p_category_id alias for $2;
v_object_type varchar;
begin
select object_type into v_object_type
from acs_objects
where object_id = object_id;
perform object_category_map__new(p_object_id,v_object_type,p_category_id);
return 0;
end;' language 'plpgsql';
create function object_category_map__delete(integer,integer)
returns integer as '
declare
p_object_id alias for $1;
p_category_id alias for $2;
begin
delete from object_category_map
where object_id = p_object_id
and category_id = p_category_id;
return 0;
end;' language 'plpgsql';
