Hi there,
I'm trying to make a table for annotating other tables' rows, perhaps even
in relation to a user. I haven't been able to find similar examples of this
elsewhere so I'd like a sanity check that I'm not doing something
horrendously wrong. This is in SQLAlchemy 0.7 by the way.
Here's an example:
class Tag(BaseModel):
__tablename__ = 'tag'
id = Column(types.Integer, primary_key=True)
table_name = Column(types.String(64))
user_id = Column(types.Integer)
row_id = Column(types.Integer)
name = Column(types.String, nullable=False)
Index('tag_backref_idx', Tag.table_name, Tag.user_id, Tag.row_id, Tag.name,
unique=True)
Index('tag_name_idx', Tag.table_name, Tag.user_id, Tag.name)
So say I have a table called *user*, I want to be able to apply a tag to a
row in it as Tag(table_name='foo', user_id=None, row_id=1, name='sometag').
The semantic is that user_id=None means it's a "global" (aka. system) tag,
whereas a tag from a user would have that user_id filled respectively.
On the Foo object, I can apply a relation like this (I'm doing this via a
mixin class in my code):
@declared_attr
def tags(cls):
return orm.relationship(Tag,
primaryjoin=and_(
Tag.table_name==cls.__tablename__,
Tag.user_id==None,
Tag.row_id==cls.id,
),
foreign_keys=[Tag.table_name, Tag.user_id, Tag.row_id],
)
So now I can do things like...
f = Session.query(Foo).get(1)
f.tags # == [list of global Tag objects applied on object f]
The goal is to be able to apply a "tag" on the system level (user_id=None)
or on the user level for any row of any table (in reality there will be a
couple of tables that will never have tags, but 90% will).
My questions:
1. Can we use the polymorphic_on stuff to make this better integrated?
2. Is there a way to make a "dynamic" ForeignKey reference such that I
can associate a tag to an object that hasn't been committed (ie. no `id`
yet)?
Example:
f = Foo()
t = Tag(name='blah', magic_triple_column_property=f)
Session.add(f)
Session.add(t)
Session.commit()
3. Am I crazy for trying to do this? Is there a better/simpler/more
rational way to do something like this?
Thanks in advance!
- Andrey
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
