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

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

Sebastien Bigaret wrote:

>Matt Goodall <ma...@po...> wrote:
>  
>
>>I realise this is sometimes a contentious issue for O/R mapping but is
>>there any way to execute arbitrary SQL within a transaction managed by
>>and EditingContext?
>>
>>I've had a quick look through the API but cannot see any way of
>>getting hold of the underlying connection. It looks like I need to
>>obtain a DatabaseContext for my model but how do I do that?
>>    
>>
>
>As far as I can read between the lines, you want to modify some data
>when an EC is saving changes, right?
>
Or to put it another way ... I want to run some arbitrary, manually 
written SQL within the same transaction as any updates to objects 
managed by Modeling.

>  Could you be more explicit about what you'd like to do? There are
>  numerous use-cases for such things, most of which have major drawbacks
>  (and that's why it's considered contentious within ORMs, mainly). I'd
>  prefer to discuss a particular use-case rather than discourse on
>  generic cases that probably won't match your case ;)
>  
>
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.

Here are a few use cases that might warrant direct database access:

    * batch updates
    * maintaining a hierarchy of objects stored as a nested set
    * reporting queries, which can often be highly complex joins


I admit I'm fairly new to Modeling so I don't know its full potential. 
However, I'm a little wary of using an ORM which blocks we when either 
it can't handle a particularly complex use case or a hand crafted query 
would improve a bottleneck in the database access layer. I guess I'm 
looking for an insurance policy ;-).

Cheers, Matt

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