On 03/14/2013 19:56, Michael Bayer wrote:
On Mar 12, 2013, at 5:13 AM, Julien Cigar <jci...@ulb.ac.be> 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.
just curious: why would you stay away from the PickleType?
In the future I would like to use the new JSON column type in PostgreSQL
9.1+, but for now we're still on 9.0.x and I thought a bytea +
PickleType would be better/faster than a TEXT + json.dumps() .. ?
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.
Sorry to not have been more accurate on this. It's a webapp based on
CherryPy, the workflow is pretty simple: the user can select, through a
<select>, which mappers should be joined for a container, and it's saved
in a:
'polymorphic_children' : orm.relationship(
ContentType,
secondary = table['folder_polymorphic_loading']
)
When the user selects mappers in the <select> box above, there is an
ajax call that retrieve which columns are available as possible default
ORDER BY clause for the container (this is the .__order__ stuff). This
is saved in the default_order property (bytea + PickleType)
Once everything is saved and that the user browse the container, those
entities are joined thanks to orm.with_polymorphic(). The webapp checks
if the default_order property is set (which is the one for which I use
atm a bytea + PickleType). So I have to maintain a kind of
"bidirectional check" between the polymorphic_children and default_order
properties, to avoid that I end's up with a query that use a column from
a table which is not joined (because, for example, the user decides that
this table should not be joined automatically anymore) in the ORDER BY
clause.
Anyway, I ended up with two @validates : http://pastie.org/6553052 and
it seems to work well :) But I'm always open to better solutions!
Thanks,
Julien
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
