h a l f b a k e r y
The leaning tower of Piezo
idea:
add, search, annotate, link, view, overview, recent, by name, best, random
meta:
news, help, about, links, report a problem
account:
Browse anonymously,
or get an account
and write.
Login
Create account.
|
|
|
SQL for trees
UDA in SQL as a mean to work with recursive hierarchies | |
The problem: unlimited depth tree represented as (id, pid) relation is hard to use in RDBMS.
The solution:
First you need a logical description of your UDA (user defined aggregation) functions recursivly traversing unlimited depth tree by one traverse pattern per function.
Second, you need an
index optimizing of and used by such UDA functions.
Third, you need such UDA implementation.
I've got a prototype already.
[link]
|
| |
Ooooo a prototype! I suppose you're going to use your swell new database to destroy a major North American city if we don't meet your demands, aren't you? |
|
| |
[panserg] you might have to wait a while for the more technical users here to make worthwhile comments. I wish I knew enough about DBs to comment but, alas, I don't. |
|
| |
Firstly, [panserg] claims to have baked this idea.
Secondly, there's no description or indication as to how or why this method is better. |
|
| |
Slaked Quick Lime is not good
for trees. It makes them drop
their leaves prematurely and
gives them that ghostly look as
they stand there bare in the
moonlight. |
|
| |
That's right neelandan, but it also masks those annoying dead fish odors. |
|
| |
I'm working with trees in SQL right now, and it's an ABSOLUTE BITCH. I wish there was something better. Are you telling me you have such a thing? |
|
| |
I've found that you can do trees in SQL by adding two extra columns to the table, sort of like a tree index. This stores the tree "path" and a "depth" of the placement. When you pull out the statement, you need to sort by the "path" and monitor the "depth". If the depth changes, move in or out. The path is really just used for sorting (so that should include your sort index). You could also add another column that stores the actual IDs so you can easily look up parents, siblings, delete whole branch, yada... |
|
| |
The real trick is saving this data and converting it in your host application. Here's a sample tree... |
|
| |
ID, name, description, sort, depth, tree, path
1, 'parent', '', 'a', 1, '/a', '/1'
2, 'child', '', 'a', '/a/a', '/1/2'
3, 'another child', 'b', '/a/b', '1/3'
etc... |
|
| |
I also have done some stuff with joining tables, returning the ID from multiple tables and then monitor those field, indenting and stuff when they change. I agree that if some software function existed where you can define that, it would be really cool. |
|
| |
Can I check out that prototype? I could send you something as well. |
|
| |
What I'm saying is, SQL, SchmeQL. This application is right up OO's alley. Forget the relational model here. Recursive hierarchies are a snap with an OODBMS. |
|
| |
Representing a tree structure in SQL isn't the problem, [jkichline]. The problem is navigating it in any reasonable way, because in SQL that gets incredibly clumsy. |
|
| |
It's even worse when you have a lattice, a many-to-many tree that must be represented with a separate table. |
|
| |
Oracle SQL has a thingie called CONNECT BY
(look it up in the manual). It sorta can do
what you want (it screws up if you have a
loop, which you wouldn't have in a tree,
but still...). Of course, I vote for this
feature; I've been in a few situations where
it would have been very welcome. |
|
| |
This idea is most disappointing. I was hoping for some sort of database query performed by vegetables. |
|
| |