Thank you very much for the source,
I also learned the term of vertical paradigm and stumble upon the
dictlike.py example in the sqlalchemy source ...
even if yours remains simpler
On Aug 18, 5:05 pm, Conor <conor.edward.da...@gmail.com> wrote:
> On 08/17/2010 11:32 AM, yota wrote:
>
>
>
> > Hello,
>
> > sqlalchemy seems to be the proper tool for my needs but I can't figure
> > out how to design my project or set the ORM properly.
>
> > Let's say, I build a music database, storing tracks and their
> > associated metadata in an sql-like database defined as such :
>
> > TRACK_TABLE ( ident *, url , duration )
> > METADATA_TABLE ( track_ident *, field_name *, field_content )
>
> > (track_ident, field_name) being the primary key for METADATA_TABLE...
>
> > intuitively, a meaningful object for a metadata set would be a
> > dictionary-like object. The name and number of fields being unknown in
> > advance but stored in the METADATA_TABLE as one row per field.
>
> > Here is the question : how may I use the ORM to map
> > a table :
> > 123 / title / waka waka
> > 123 / artist / shakira
> > 123 / featuring / my sister
>
> > to an object like :
> > metadata.title = "waka waka"
> > metadata.artist = "shakira"
> > metadata.featuring = "my sister"
>
> > or a dictionary like :
> > metadata = { "title" : "waka waka", "artist" : "shakira",
> > "featuring" : "my sister" }
>
> > or whatsoever ...
>
> > the solution might also be in rewrite of the sql schema :)
>
> > thanks for your advices
>
> SQLAlchemy supports for this via the attribute_mapped_collection and
> association_proxy classes:
>
> from sqlalchemy.ext.associationproxy import association_proxy
> from sqlalchemy.orm.collections import attribute_mapped_collection
>
> def create_metadata(field_name, field_content):
> return TrackMetaData(field_name=field_name, field_content=field_content)
>
> class Track(Base):
> __tablename__ = "track"
>
> id = Column(Integer, primary_key=True)
> [...]
>
> field2metadata = relationship(TrackMetaData,
> backref="track",
>
> collection_class=attribute_mapped_collection("field_name"))
> field2content = association_proxy("field2metadata",
> "field_content",
> creator=create_metadata)
>
> # I'm only naming this class TrackMetaData to prevent confusion with
> sqlalchemy.MetaData.
> class TrackMetaData(Base):
> __tablename__ = "metadata"
>
> track_id = Column(Integer, ForeignKey("track.id"), primary_key=True)
> field_name = Column(Unicode(...), primary_key=True)
> field_content = Column(Unicode(...), nullable=False)
>
> In this way you can access field2content like a dictionary:
>
> artist = track.field2content[u"artist"]
> track.field2content[u"title"] = u"waka waka"
>
> Note that association_proxy does not supply a comparator yet, so if you
> want to join/query on metadata then you need to use the field2metadata
> relationship:
>
> # Find all tracks by artist Shakira.
> q = Session.query(Track)
> q = q.filter(Track.field2metadata.any(and_(TrackMetaData.field_name ==
> u"artist",
> TrackMetaData.field_content =
> u"Shakira")))
> tracks = q.all()
>
> # Load all tracks, eagerloading their metadata.
> q = Session.query(Track)
> q = q.options(joinedload(Track.field2metadata))
> tracks = q.all()
>
> -Conor
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
