Re: [Modeling-users] Execute arbitrary SQL within an EditingContext

[Matt G. <ma...@po...>]

Sebastien Bigaret wrote:

>Matt Goodall <ma...@po...> wrote:
>  
>
>>Hmm, I don't actually have a use case at the moment ;-). In my somewhat
>>limited experience with ORMs, they can often handle the majority of queries
>>but there are times when it is easier or faster to directly access the
>>database. Obviously, it's important for the direct access to be part of the
>>ORM's transaction.
>>    
>>
>
>Okay, so you meant both queries and updates. To summarize the current
>situation:
>
>  - raw sql *queries* are not officially supported, nor documented.
>    However, there is a test case in test_EditingContext_Global.py you'd
>    probably want to refer to: test_999_customSQLQuery(), at:
>    http://cvs.sourceforge.net/cgi-bin/viewcvs.cgi/modeling/ProjectModeling/Modeling/tests/test_EditingContext_Global.py?rev=1.27&content-type=text/vnd.viewcvs-markup
>
>    You were right, the dbContext is one of the objects you need to
>    access --you'll notice that this is far from a user friendly API,
>    which would probably be EC.fetchSQL(), as already discussed earlier
>    here.
>
Ah, I had a feeling the DatabaseContext would be registered for the 
model somewhere, since that is how it is defined in the XML/Py file. I 
didn't think to look in the DatabaseContext module itself. And you're 
right, it's not user friendly ;-). EC.fetchSQL() would be a lot more 
pleasant.

>>Here are a few use cases that might warrant direct database access:
>>
>>    * batch updates
>>    
>>
>
>My _personal_ point of view here is that, if you do not want to pay for
>the extra payload of manipulating real objects, such batches should
>probably be made w/ the appropriate raw python adaptor. But I might have
>misunderstood your point.
>
In this case you are probably right. I would be unlikely to manipulate 
object via Modeling during a large batch update.

>>    * maintaining a hierarchy of objects stored as a nested set
>>    
>>
>Could you elaborate on that?
>
Nested sets are a reasonably common way of describing a hierarchy of 
objects using a relational model. Here's a link to one article about it, 
http://www.intelligententerprise.com/001020/celko.shtml, but there are 
plenty of others.

The nested set example in the article is somewhat simplified since 
everything is in one table. It is quite reasonable to have the left and 
right node information in one table (nested set), the object's "real" 
data in another table (object) and a foreign key to connect them. In 
this configuration it makes more sense to me to maintain the nested set 
table with direct SQL but let the ORM handle the object.

- Matt

-- 
Matt Goodall, Pollenation Internet Ltd
w: http://www.pollenationinternet.com
e: ma...@po...