On Mar 15, 2013, at 3:19 PM, Julien Cigar <jci...@ulb.ac.be> wrote:
> 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() .. ?
you don't need PG's JSON type, just a text one like this one:
http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#marshal-json-strings
why JSON and not pickle, basically would you prefer your dictionary of strings
to look like:
"(dp0\nS'username'\np1\nS'some user'\np2\nsS'full name'\np3\nS'user b.
user'\np4\nsS'address'\np5\nS'123 anywhere street'\np6\ns."
or
'{"username": "some user", "full name": "user b. user", "address": "123
anywhere street"}'
?
plus would you prefer your data to have code injection vulnerabilities or not.
>
>> 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.
>
>
--
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.
