On Mar 12, 2013, at 5:13 AM, Julien Cigar <[email protected]> wrote:
> Hello,
>
> I have written a CMS which is, among other, based on the joined load
> inheritance feature of SQLAlchemy.
>
> It is quite simple: the user is able to add "objects" in "containers" and can
> select the default polymorphic loading for a "container". In gross it can
> dynamically select which tables will be joined. For that I'm using the new
> orm.with_polymorphic() stuff of version 0.8.
>
> It works well, and now I would also be able to let the user to set a default
> ORDER BY, ... for a "container".
> Basically I would like to be able to select a default ORDER BY from the
> orm.with_polymorphic() join above.
>
> For the moment I'm using a "bytea" (I'm using PostgreSQL) column, named
> "default_order" which is mapped in a "PickleType" on the SQLAlchemy side.
> This column is just a serialized list which looks like:
>
> [{'column': 'starts', 'polymorphic_identity': 5, 'weight': 1, 'order':
> 'desc'},
> {'column': 'weight', 'polymorphic_identity': None, 'weight': 2, 'order':
> 'asc'},
> {'column': 'last_update', 'polymorphic_identity': None, 'weight': 3, 'order':
> 'asc'}]
>
> So it tells which column from which primary mapper should be used and in
> which order.
> I'm using the following code http://pastie.org/6459613 to transform this in
> an ORDER BY clause.
>
> Not all columns should be selectable, so I used something like this in my
> mapped classes:
>
> Content.__order__ = [Content.weight, Content.last_update]
> Event.__order__ = [Event.starts, Event.ends]
> File.__order__ = [File.file_size]
>
> I need some advices on how would you do to maintain the "consistency" of
> everything. By consistency I mean that I would like to avoid cases where the
> user select a column that doesn't exist or is not allowed, a
> polymorphic_identity that doesn't exist, maintain the synchronization between
> the polymorphic loading of a container and the serialized list, etc
>
> Would you create a custom type based on PickleType or a simple @validates()
> or .. ?
well I'd stay away from PickleType like the plague and at least use a JSON
column instead.
I can't answer the bigger question here since it's dependent on the flow of
your application. For example, i don't see how you're about to "select" some
rows, but then there's an object already loaded in memory where you can pull
out these various database-persisted attributes that describe other SQL
queries. Then @validates, that's used for persisting data, so I guess this
means users are persisting objects that describe SQL queries ? Sure, if
someone tries to say "set_order_by('somecol')", and you want to check
Content.__order__, @validates seems the most expedient approach. But if this
is a GUI, typically you are not allowing invalid selections from the GUI in the
first place, so something would be scanning those __order__ collections ahead
of time.... its kind of a highly specific application design question.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
