Forum OpenACS Development: Response to Replacement for places package.
Posted by
Domingo Alvarez duarte
on 05/24/01 09:11 PM
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); */ --------