Hello,
this is more a general database question rather than a SA one, but maybe
someone has already achieve this with SA. I know there's a small chapter
about this already in the SA docs (1), but as far as I understood, this
is about the "Adjacency List" model and not about the "Nested Set"
model. This article (2) explains about both approches and the "Nested
Set" model would suite better my needs I think.
Maybe this should need some attention to implement in SA some API to
handle nodes (insert, move, remove) of herachical trees in SA the
"Nested Set" way.
Right now I have a playlist management application that is XML-based. So
a playlist has many nodes that either represent media elements ("video",
"image") or classification elements ("group"):
<main>
<video>intro.avi</video>
<group datein="20070701" dateout="20070731">
<video>foo.avi</video>
<video>bar.avi</video>
</group>
<group>
<video>weather_intro.avi</video>
<image duration="5" start="0000" stop="1159">morning.jpg</image>
<image duration="5" start="1200" stop="2359">afternoon.jpg</image>
<video>brought_to_you_by.avi</video>
<media_list pick_style="random" pick_many="1">
<video>brand_A.avi</video>
<video>brand_B.avi</video>
<video dateout="20071231">brand_C.avi</video>
</media_list>
</group>
<video>outro.avi</video>
</main>
You can understand that the playlist has the ability to store different
types of medias (images, videos), some group elements used for
classification, can apply some scheduling data to any elements as
attributes.
So I'm looking foward to integrate this hierarchical tree in a flat
database.
Basicly, I'd have a "playlist" to hold the main playlist:
playlist_table = Table('playlists', meta,
Column('id', Integer, primary_key=True),
Column('name', Unicode(20), nullable=False),
Column('description', Unicode(40)),
)
Then an "nodes" table to store every type of nodes (group, video, image)
and some scheduling attributes.
nodes_table = Table('nodes', meta,
Column('id', Integer, primary_key=True),
Column('typeof', Unicode(20), nullable=False),
Column('foreign_id', Integer),
Column('lft', Integer, nullable=False), # For "Nested Set" model
Column('rgt', Integer, nullable=False), # For "Nested Set" model
Column('scheduled_from', DateTime),
Column('scheduled_until', DateTime),
Column('id_playlist', None, ForeignKey('playlists.id')),
)
Every row would store "group", "video" or "image" in the 'typeof' field
to identify the type of node. And the id of the media would be in the
'foreign_id' field. 'typeof' and 'foreign_id' are the two field I don't
feel clean about.
Lets finish with the "videos" table for the example:
videos_table = Table('videos', meta,
Column('id', Integer, primary_key=True),
Column('file_name', Unicode(255), nullable=False),
Column('category', Unicode(255), nullable=False),
Column('duration', Integer),
Column('id_codec', None, ForeignKey('codecs.id')),
)
As the "nodes" table would need to point to different tables to find the
corresponding media, I couldn't have a foreign key for a natural
relation. Or I would need to have as many foreign keys in the "nodes"
table (id_video, id_image, ...) and only fill in the nedded column. But
this is not efficient.
The whole idea is about browing down my tree through python objects
attributes like SA already does so well when two tables are related to
each other, but returning a different type of object depending on the
kind of node I hit.
class Node(object):
...
class Video(Node):
...
class Image(Node):
...
Any ideas one how I could deal with that would be greatly appreciated. I
hope this is clear enough to not scare people from reading it. At least
if you got there, it's a good point. :)
ps: I just felt on the docs about "Concrete Inheritance, Polymorphic"
(3), maybe this could be something to dig for ?
(1)
http://www.sqlalchemy.org/docs/documentation.html#advdatamapping_selfreferential
(2)
http://www.vbmysql.com/articles/database-design/managing-hierarchical-data-in-mysql/
(3)http://www.sqlalchemy.org/docs/documentation.html#advdatamapping_inheritance_concrete
Regards,
--
Alexandre CONRAD
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
