Forum OpenACS Q&A: A starting work to manage translations and locations (addresses)

Collapse
1: A starting work to manage translations and locations (addresses)
Posted by Domingo Alvarez duarte on 05/13/01 02:01 AM 
Sorry but this is to start discussion or have some critics.

This code works almost fine.
----
-- $Id: areas_and_languages,v 1.1 2001/05/12 10:01:21 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,
    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,
    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,'Root',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,
    area_type_id	integer references dad_areas_type,
    name		varchar(100) not null,
    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)
);

insert into dad_areas values(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;
	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;
	    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;
		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;
		    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;
			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;
				if v_area_rec.level_6 < 1 then v_area_rec.level_6 := 1; 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, 
	    p_area_type_id,
	    p_name,
	    p_zip,
	    p_translation_id
	);
    end if;
    return v_new_area_id;
end;' language 'plpgsql';

--
-- This view doesn't work but the query works fine without the comments with a proper value
--
create view dad_area_tree as
    select a1.*
    from dad_areas as a1, dad_areas as a2
    where  -- a2.area_id = @some_area_id@ and 
    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
    );


insert into dad_areas_type values(1,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);

----
Collapse
2: Response to A starting work to manage translations and locations (addresses) (response to 1)
Posted by Don Baccus on 05/13/01 02:35 AM
Exactly how is the view not working?  It looks like it should...

I have a locations table for my birdnotes.net database which serves a similar purpose.  Rather than a fixed number of parent nodes within the table of locations, I use a separate table with two fields, i.e.  "smaller" and "bigger".  This works quite well with proper indexing on the locations table and the elements of the hierarchy table (named "contains" in my datamodel).

Note that by coincidence this is similar to the hierarchy table used by acs_objects.

If I want to return the set of all locations in Oregon, I just do this:

select * from locales l, contains c
where c.bigger = :locale_id_for_oregon and c.smaller = l.locale_id;

My locations database is complicated by the fact that in real life geographical regions don't decompose into a tree, but rather a DAG.

Collapse
3: Response to A starting work to manage translations and locations (addresses) (response to 1)
Posted by Domingo Alvarez duarte on 05/13/01 10:36 AM 
  
Now I found a way for the view to work as I expect :  
  
---  
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  
    );  
  
---  
  
Now I can ask like this to get whole tree:  
  
select * from dad_area_tree where seed = 13; -- 13 means any validy area_id  
  
PS.: To : Don Baccus answer  
What do you mean with  "DAG" ?  
  
Where I can have a look at the data model you mentioned ?
Collapse
4: Response to A starting work to manage translations and locations (addresses) (response to 1)
Posted by Don Baccus on 05/13/01 04:01 PM
DAG = Directed Acyclic Graph. Trees are a subset of this class of graph.

The datamodel's very simple, as described above, it's all the queries to do exactly what I need that are hairy. The datamodel's roughly just something like: 

create table locales (
    locale_id integer primary key
);

create table contains (
     smaller integer references locales,
     bigger integer references locales
);

create index smaller_idx on contains(smaller);
create index bigger_idx on contains(bigger);
I may have a "primary key(bigger, smaller)" to enforce uniqueness and then just a separate index on "smaller", I don't remember offhand. I need indexed access on both fields, the composite index/primary key only gets me indexed access on "bigger".