Well I've part of this before, I'm repeating here the actual working
version here again for comments and sugestion.
I have something like this working in coldfusion and I think it works
quite well and could be used here for openacs.
--------
-- $Id: areas_and_languages,v 1.7 2001/05/13 22:54:33 mingo Exp $
--
-- Areas and language data model
--
create sequence dad_languages_seq;
create table dad_languages(
lang_id integer not null default nextval('dad_languages_seq') primary key,
name varchar(50) not null,
short_name varchar(10),
native_name varchar(50),
translation_id integer
);
create table dad_translations(
translation_id integer not null,
lang_id integer not null references dad_languages,
content text,
primary key (translation_id,lang_id)
);
create sequence dad_areas_type_seq;
create table dad_areas_type(
area_type_id integer not null default nextval('dad_areas_type__seq') primary key,
country_id integer default 0,
area_group_id integer not null default 0,
name varchar(50) not null,
translation_id integer -- references dad_translations( translation_id ) match partial
);
insert into dad_areas_type values(0,0,0,'Root',null);
insert into dad_areas_type values(1,0,0,'Country',null);
insert into dad_areas_type values(2,0,0,'Region',null);
insert into dad_areas_type values(3,0,0,'SubRegion',null);
insert into dad_areas_type values(4,0,0,'City',null);
insert into dad_areas_type values(5,0,0,'District',null);
insert into dad_areas_type values(6,0,0,'Street',null);
insert into dad_areas_type values(7,0,0,'Building',null);
create sequence dad_areas_seq;
create table dad_areas(
area_id integer not null default nextval('dad_areas_seq') primary key,
level_1 integer not null default 0,
level_2 integer not null default 0,
level_3 integer not null default 0,
level_4 integer not null default 0,
level_5 integer not null default 0,
level_6 integer not null default 0,
level_7 integer not null default 0,
area_type_id integer references dad_areas_type,
name varchar(100) not null,
short_name varchar(10),
zip varchar(50),
translation_id integer, -- references dad_tranlations(tranlation_id) match partial,
constraint dad_unique_area unique(level_1,level_2,level_3,level_4,level_5,level_6,level_7)
);
insert into dad_areas values(0,0,0,0,0,0,0,0,0,'World',null,null);
create function dad_add_area_in(
integer, -- area_id
integer, -- area_type_id
varchar, -- name
varchar, -- zip
integer --translation_id
) returns integer as '
declare
p_area_id alias for $1;
p_area_type_id alias for $2;
p_name alias for $3;
p_zip alias for $4;
p_translation_id alias for $5;
v_new_area_id integer;
v_area_rec dad_areas%ROWTYPE;
begin
v_new_area_id := -1;
select into v_area_rec * from dad_areas where area_id = p_area_id;
if found then
if v_area_rec.level_1 = 0 then
select into v_area_rec.level_1 max(level_1) +1
from dad_areas
where level_2 = 0;
if v_area_rec.level_1 < 1 then v_area_rec.level_1 := 1; end if;
v_area_rec.level_2 := 0;
v_area_rec.level_3 := 0;
v_area_rec.level_4 := 0;
v_area_rec.level_5 := 0;
v_area_rec.level_6 := 0;
v_area_rec.level_7 := 0;
else if v_area_rec.level_2 = 0 then
select into v_area_rec.level_2 max(level_2) +1
from dad_areas
where level_1 = v_area_rec.level_1
and level_3 = 0;
if v_area_rec.level_2 < 1 then v_area_rec.level_2 := 1; end if;
v_area_rec.level_3 := 0;
v_area_rec.level_4 := 0;
v_area_rec.level_5 := 0;
v_area_rec.level_6 := 0;
v_area_rec.level_7 := 0;
else if v_area_rec.level_3 = 0 then
select into v_area_rec.level_3 max(level_3) +1
from dad_areas
where level_1 = v_area_rec.level_1
and level_2 = v_area_rec.level_2
and level_4 = 0;
if v_area_rec.level_3 < 1 then v_area_rec.level_3 := 1; end if;
v_area_rec.level_4 := 0;
v_area_rec.level_5 := 0;
v_area_rec.level_6 := 0;
v_area_rec.level_7 := 0;
else if v_area_rec.level_4 = 0 then
select into v_area_rec.level_4 max(level_4) +1
from dad_areas
where level_1 = v_area_rec.level_1
and level_2 = v_area_rec.level_2
and level_3 = v_area_rec.level_3
and level_5 = 0;
if v_area_rec.level_4 < 1 then v_area_rec.level_4 := 1; end if;
v_area_rec.level_5 := 0;
v_area_rec.level_6 := 0;
v_area_rec.level_7 := 0;
else if v_area_rec.level_5 = 0 then
select into v_area_rec.level_5 max(level_5) +1
from dad_areas
where level_1 = v_area_rec.level_1
and level_2 = v_area_rec.level_2
and level_3 = v_area_rec.level_3
and level_4 = v_area_rec.level_4
and level_6 = 0;
if v_area_rec.level_5 < 1 then v_area_rec.level_5 := 1; end if;
v_area_rec.level_6 := 0;
v_area_rec.level_7 := 0;
else if v_area_rec.level_6 = 0 then
select into v_area_rec.level_6 max(level_6) +1
from dad_areas
where level_1 = v_area_rec.level_1
and level_2 = v_area_rec.level_2
and level_3 = v_area_rec.level_3
and level_4 = v_area_rec.level_4
and level_5 = v_area_rec.level_5
and level_7 = 0;
if v_area_rec.level_6 < 1 then v_area_rec.level_6 := 1; end if;
v_area_rec.level_7 := 0;
else if v_area_rec.level_7 = 0 then
select into v_area_rec.level_7 max(level_7) +1
from dad_areas
where level_1 = v_area_rec.level_1
and level_2 = v_area_rec.level_2
and level_3 = v_area_rec.level_3
and level_4 = v_area_rec.level_4
and level_5 = v_area_rec.level_5
and level_6 = v_area_rec.level_6;
if v_area_rec.level_7 < 1 then v_area_rec.level_7 := 1; end if;
end if;
end if;
end if;
end if;
end if;
end if;
end if;
select into v_new_area_id nextval(''dad_areas_seq'');
insert into dad_areas values(
v_new_area_id,
v_area_rec.level_1,
v_area_rec.level_2,
v_area_rec.level_3,
v_area_rec.level_4,
v_area_rec.level_5,
v_area_rec.level_6,
v_area_rec.level_7,
p_area_type_id,
p_name,
p_zip,
p_translation_id
);
end if;
return v_new_area_id;
end;' language 'plpgsql';
create view dad_area_tree as
select a2.area_id as seed, a1.*
from dad_areas as a1, dad_areas as a2
where
a1.area_id = a2.area_id
or
(
a1.level_1 = a2.level_1
and a1.level_2 = 0
)
or
(
a1.level_1 = a2.level_1
and a1.level_2 = a2.level_2
and a1.level_3 = 0
)
or
(
a1.level_1 = a2.level_1
and a1.level_2 = a2.level_2
and a1.level_3 = a2.level_3
and a1.level_4 = 0
)
or
(
a1.level_1 = a2.level_1
and a1.level_2 = a2.level_2
and a1.level_3 = a2.level_3
and a1.level_4 = a2.level_4
and a1.level_5 = 0
)
or
(
a1.level_1 = a2.level_1
and a1.level_2 = a2.level_2
and a1.level_3 = a2.level_3
and a1.level_4 = a2.level_4
and a1.level_5 = a2.level_5
and a1.level_6 = 0
)
or
(
a1.level_1 = a2.level_1
and a1.level_2 = a2.level_2
and a1.level_3 = a2.level_3
and a1.level_4 = a2.level_4
and a1.level_5 = a2.level_5
and a1.level_6 = a2.level_6
and a1.level_7 = 0
) limit 7;
create view dad_area_leafs as
select a2.area_id as root, a1.*
from dad_areas as a1, dad_areas as a2
where 1=1
and
case when a2.level_1 = 0 then
(a1.level_2 = 0)
else
case when a2.level_2 = 0 then
((a1.level_1 = a2.level_2) and (a1.level_3 = 0))
else
case when a2.level_3 = 0 then
(
(a1.level_1 = a2.level_1)
and (a1.level_2 = a2.level_2)
and (a1.level_4 = 0)
)
else
case when a2.level_4 = 0 then
(
(a1.level_1 = a2.level_1)
and (a1.level_2 = a2.level_2)
and (a1.level_3 = a2.level_3)
and (a1.level_5 = 0)
)
else
case when a2.level_5 = 0 then
(
(a1.level_1 = a2.level_1)
and (a1.level_2 = a2.level_2)
and (a1.level_3 = a2.level_3)
and (a1.level_4 = a2.level_4)
and (a1.level_6 = 0)
)
else
case when a2.level_6 = 0 then
(
(a1.level_1 = a2.level_1)
and (a1.level_2 = a2.level_2)
and (a1.level_3 = a2.level_3)
and (a1.level_4 = a2.level_4)
and (a1.level_5 = a2.level_5)
and (a1.level_6 = 0)
)
else
(
(a1.level_1 = a2.level_1)
and (a1.level_2 = a2.level_2)
and (a1.level_3 = a2.level_3)
and (a1.level_4 = a2.level_4)
and (a1.level_5 = a2.level_5)
and (a1.level_6 = a2.level_6)
)
end
end
end
end
end
end;
/*
create function dad_populate_areas( integer )
returns integer as '
declare
v_x alias for $1;
v_r1 integer;
v_r2 integer;
v_r3 integer;
v_r4 integer;
v_r5 integer;
v_r6 integer;
v_r7 integer;
begin
for v_1 in 1..v_x loop
-- RAISE NOTICE ''v_1 is %'',v_1;
select into v_r1 dad_add_area_in(0,1,''level_1-'' || cast(v_1 as char) ,null,null);
for v_2 in 1..v_x loop
-- RAISE NOTICE ''v_2 is %'',v_2;
select into v_r2 dad_add_area_in(v_r1,2,''level_2-'' || cast(v_2 as char) ,null,null);
for v_3 in 1..v_x loop
-- RAISE NOTICE ''v_3 is %'',v_3;
select into v_r3 dad_add_area_in(v_r2,3,''level_3-'' || cast(v_3 as char) ,null,null);
for v_4 in 1..v_x loop
-- RAISE NOTICE ''v_4 is %'',v_4;
select into v_r4 dad_add_area_in(v_r3,4,''level_4-'' || cast(v_4 as char) ,null,null);
for v_5 in 1..v_x loop
-- RAISE NOTICE ''v_5 is %'',v_5;
select into v_r5 dad_add_area_in(v_r4,5,''level_5-'' || cast(v_5 as char) ,null,null);
for v_6 in 1..v_x loop
-- RAISE NOTICE ''v_6 is %'',v_6;
select into v_r6 dad_add_area_in(v_r5,6,''level_6-'' || cast(v_6 as char) ,null,null);
for v_7 in 1..v_x loop
-- RAISE NOTICE ''v_7 is %'',v_7;
select into v_r7 dad_add_area_in(v_r6,7,''level_7-'' || cast(v_7 as char) ,null,null);
end loop;
end loop;
end loop;
end loop;
end loop;
end loop;
end loop;
return 0;
end;' language 'plpgsql';
-- insert into dad_areas_type values(1,0,0,'calle',0);
select dad_add_area_in(0,1,'Espana',null,null);
select dad_add_area_in(0,1,'Brasil',null,null);
select dad_add_area_in(0,1,'Portugal',null,null);
select dad_add_area_in(1,1,'Andalucia',null,null);
select dad_add_area_in(4,1,'Malaga',null,null);
select dad_add_area_in(5,1,'Malaga ciudad',null,null);
select dad_add_area_in(6,1,'Centro',null,null);
select dad_add_area_in(7,1,'Larios',null,null);
select dad_add_area_in(1,1,'Extremadura',null,null);
select dad_add_area_in(4,1,'Cadiz',null,null);
select dad_add_area_in(5,1,'Fuengirola',null,null);
select dad_add_area_in(6,1,'La Paz',null,null);
select dad_add_area_in(7,1,'Juan Miguel',null,null);
select dad_populate_areas(4);
*/
--------
