Re: perl create database with folder system (Tree in SQL)

Please edit your post with <code> tags to make your intention clearer.

My first idea was that you want to implement a database just using the file system.

But now I think you are asking about how to implement tree structures in an SQL table.

This is speculation and we are primarily a Perl board, but table like this should do:

id  parent  value 
1   0       animal
2   1       cat
3   2       lion
4   2       tiger
5   4       Siegfried
6   4       Roy
[download]

For instance the path structure /animal/cat/tiger/Roy is represented in the former table.

There are different ways how to query this, like recursively or using self joins.

You can also use a view based on self join if the tree's depth is fixed in order to visualize the path structure.

Cheers Rolf
_{(addicted to the Perl Programming Language and ☆☆☆☆ :)

Wikisyntax for the Monastery}

Comment on Re: perl create database with folder system (Tree in SQL) Select or Download Code

Replies are listed 'Best First'.
Re^2: perl create database with folder system (Tree in SQL) by choroba (Cardinal) on Dec 02, 2017 at 15:17 UTC
It's a good idea to use the pre-order indices as ids; if you also store the maximal descendant id for each node (easily generated in pre-order by using the node's id for leaves, and using the last assigned id for non-leaves) you can easily check for descendants: A is B's descendant iff `B.id <= A.id <= B.mdi`. ($q=q:Sq=~/;[c](.)(.)/;chr(-\|\|-\|5+lengthSq)`"S\|oS2"`map{chr \|+ord }map{substrSq`S_+\|`\|}3E\|-\|`7**2-3:)=~y+S\|`+$1,++print+eval$q,q,a, [download]	[reply] [d/l] [select]
Re^3: perl create database with folder system (Tree in SQL) (edit) by LanX (Saint) on Dec 02, 2017 at 15:36 UTC
I think imposing order into numerical ° IDs works only for static structures. If the max depth is fixed I can easily create a performant view where each column represents a level. SQL views on self joins are even updatetable in mysql. edit Of course there are attempts to use a path like strings as ID, ie `0_1` would be a parent of `0_1_15` , but this becomes a can of worms if you need to move branches ... Cheers Rolf _{(addicted to the Perl Programming Language and ☆☆☆☆ :) Wikisyntax for the Monastery} °) integers to be more precise	[reply] [d/l] [select]