Thread: How to store directory like structures?
Hello! I want to store some structure like: CREATE TABLE node (nodeid SERIAL PRIMARY KEY,parent INT REFERENCES node(nodeid) ON UPDATE CASCADE ON DELETE CASCADE,label TEXT,UNIQUE(parent, label),...data ... ); The label is used to map a node to a directory like strukture, so i can have a function directory_for(nodeid) which gives me /root_label/parent_label/parent_label/my_label (root labels have NULL as parent) The problem is the ammount of queries when i've got deep nodes, and I often have to query if a node is "in path" of another node. Is there a good solution to build directory-tree like datastruktures? I found, http://www.sai.msu.su/~megera/postgres/gist/ltree/ which seems to do what i want, but I've no idea (and probalbly no chance) to get that running on my system ;-( Thanks, AXEL.
On Sun, 3 Apr 2005, Axel Straschil wrote: > Hello! > > I want to store some structure like: > > CREATE TABLE node > ( > nodeid SERIAL PRIMARY KEY, > parent INT REFERENCES node(nodeid) > ON UPDATE CASCADE ON DELETE CASCADE, > label TEXT, > UNIQUE (parent, label), > ... > data > ... > ); > > The label is used to map a node to a directory like strukture, so i can > have a function directory_for(nodeid) which gives me > /root_label/parent_label/parent_label/my_label (root labels have NULL as parent) > > The problem is the ammount of queries when i've got deep nodes, and I > often have to query if a node is "in path" of another node. > > Is there a good solution to build directory-tree like datastruktures? > I found, http://www.sai.msu.su/~megera/postgres/gist/ltree/ which seems > to do what i want, but I've no idea (and probalbly no chance) to get > that running on my system ;-( what's a problem with ltree ? > > Thanks, > AXEL. > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Hello! >> I found, http://www.sai.msu.su/~megera/postgres/gist/ltree/ which seems > what's a problem with ltree ? I think ltree would be exactly what I need, the Problem ist that I've got absolutly no Idea how to get that thing into a running Pg 7.4 under Gentoo and a Pg 7.3 under Fedora? Is there a possibility to use ltree without using the original sources from postgresql and keep using gentoo's portage and fedora's rpm-version of postgres? Thanks, AXEL. -- "Aber naja, ich bin eher der Forentyp." Wolfibolfi's outing in http://www.informatik-forum.at/showpost.php?p=206342&postcount=10
Use the ltree datatype !It's made specifically for this purpose.http://www.sai.msu.su/~megera/postgres/gist/ On Sun, 03 Apr 2005 12:13:48 +0200, Axel Straschil <axel@straschil.com> wrote: > Hello! > > I want to store some structure like: > > CREATE TABLE node > ( > nodeid SERIAL PRIMARY KEY, > parent INT REFERENCES node(nodeid) > ON UPDATE CASCADE ON DELETE CASCADE, > label TEXT, > UNIQUE (parent, label), > ... > data > ... > ); > > The label is used to map a node to a directory like strukture, so i can > have a function directory_for(nodeid) which gives me > /root_label/parent_label/parent_label/my_label (root labels have NULL as > parent) > > The problem is the ammount of queries when i've got deep nodes, and I > often have to query if a node is "in path" of another node. > > Is there a good solution to build directory-tree like datastruktures? > I found, http://www.sai.msu.su/~megera/postgres/gist/ltree/ which seems > to do what i want, but I've no idea (and probalbly no chance) to get > that running on my system ;-( > > Thanks, > AXEL. > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
On gentoo (at least on my box) it's installed by default in 8.0, I believe it was installed by default, too, on 7.4.X On Sun, 03 Apr 2005 19:26:03 +0200, Axel Straschil <axel@straschil.com> wrote: > Hello! > >>> I found, http://www.sai.msu.su/~megera/postgres/gist/ltree/ which seems >> what's a problem with ltree ? > > I think ltree would be exactly what I need, the Problem ist that I've > got absolutly no Idea how to get that thing into a running Pg 7.4 under > Gentoo and a Pg 7.3 under Fedora? > > Is there a possibility to use ltree without using the original sources > from postgresql and keep using gentoo's portage and fedora's rpm-version > of postgres? > > Thanks, AXEL.
On Sun, 3 Apr 2005, Axel Straschil wrote: > Hello! > >>> I found, http://www.sai.msu.su/~megera/postgres/gist/ltree/ which seems >> what's a problem with ltree ? > > I think ltree would be exactly what I need, the Problem ist that I've > got absolutly no Idea how to get that thing into a running Pg 7.4 under > Gentoo and a Pg 7.3 under Fedora? > > Is there a possibility to use ltree without using the original sources > from postgresql and keep using gentoo's portage and fedora's rpm-version > of postgres? I have no experience with those beasts, but what's wrong just untar source, configure and compile by hand ? > > Thanks, AXEL. > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Hello! > I have no experience with those beasts, but what's wrong just untar source, > configure and compile by hand ? + Testing and maybe reinstall all stuff depending on posgressql on that server every release you want to go with ;-) Lg, AXEL. -- "Aber naja, ich bin eher der Forentyp." Wolfibolfi's outing in http://www.informatik-forum.at/showpost.php?p=206342&postcount=10
Hello! > On gentoo (at least on my box) it's installed by default in 8.0, I > believe it was installed by default, too, on 7.4.X Tried with gentoo just under 7.4.x and 8.0.1: axel=# CREATE TABLE test ( path ltree); ERROR: type "ltree" does not exist Any idea? Thanks, AXEL. -- "Aber naja, ich bin eher der Forentyp." Wolfibolfi's outing in http://www.informatik-forum.at/showpost.php?p=206342&postcount=10
On Sun, 3 Apr 2005, Axel Straschil wrote:
> Hello!
>
>> On gentoo (at least on my box) it's installed by default in 8.0, I
>> believe it was installed by default, too, on 7.4.X
>
> Tried with gentoo just under 7.4.x and 8.0.1:
>
> axel=# CREATE TABLE test ( path ltree);
> ERROR: type "ltree" does not exist
>
> Any idea?
you need to load ltree into your database !
psql yourdb < ltree.sql
use 'locate ltree.sql' to find if ltree is installed
>
> Thanks,
> AXEL.
>
Regards, Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
> you need to load ltree into your database ! > psql yourdb < ltree.sql > > use 'locate ltree.sql' to find if ltree is installed Yeah, I remember now having to do that, but the binary module definitely was here without having to do anything besides "emerge postgresql" : peufeu@nyuu peufeu $ locate ltree.so /usr/lib/postgresql/ltree.so peufeu@nyuu peufeu $ locate ltree.sql /usr/share/postgresql/contrib/ltree.sql So just : psql yourdb < /usr/share/postgresql/contrib/ltree.sql And you should be OK.Really this module is amazing. You can put a trigger on the table so that, for instance, the path is created automatically from the element name and the parent_id, that kind of things. I remember search was also really fast and you can use complex queries without CONNECT BY...
Hello! > psql yourdb < /usr/share/postgresql/contrib/ltree.sql *STRIKE* ... I'm a lucky guy now ;-) Thanks to all! Lg, AXEL. -- "Aber naja, ich bin eher der Forentyp." Wolfibolfi's outing in http://www.informatik-forum.at/showpost.php?p=206342&postcount=10
Hello! > psql yourdb < /usr/share/postgresql/contrib/ltree.sql *STRIKE*, thanks, works perfektyl now! Lg, AXEL. -- "Aber naja, ich bin eher der Forentyp." Wolfibolfi's outing in http://www.informatik-forum.at/showpost.php?p=206342&postcount=10