modeling-users

[Modeling-users] Execute arbitrary SQL within an EditingContext

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

Hi,

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?

Thanks, Matt

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

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

[Sebastien B. <sbi...@us...>]

        Hi Matt,

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?
>=20
> 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?

  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 ;)

-- S=E9bastien.

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...

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

[Sebastien B. <sbi...@us...>]

Matt Goodall <ma...@po...> wrote:
> 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 ;-).

  Fair enough!

> 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 queri=
es
> 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 t=
he
> 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=3D1.27&content-type=3Dtex=
t/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.

  - speaking of sql *updates* which should be bounded to the
    EditingContext transaction (i.e. updates that should be atomically
    done along with the changes that ec.saveChanges() does): this is not
    supported for the moment being.

    The reasons is: there is no way for now to bind a method to be
    executed when an EC saves its changes. It's not *that* difficult,
    however, and if there's a need for such a thing then I'll activate
    the delegation stuff around EditingContext and then all that you'll
    need to do is to code a given method in the EC's delegate.

    (for the curious, the support for delegation is done in module
     Modeling.delegation, whose documentation is here:
http://modeling.sf.net/API/Modeling-API/public/Modeling.delegation-module.h=
tml
     Delegates are another TODO item for which there is no need [users'
     request] for the moment being)

  Now you must understand that updating directly db-rows with raw sql is
  very risky and should be handled with great care: the ORM has no way
  to know that some of the objects it already manages are being directly
  changed in the db. If you don't care, you can face very strange
  behaviour, such as objects' properties being overriden without notice,
  or exceptions if you're using optimistic locking (not implemented
  yet), etc.

    Note that I don't say that's impossible, just because I know how
    this could be done. Just a quick hint: we will need a fetch to be
    able to 'refresh' the already fetched objects (another TODO item).

> Here are a few use cases that might warrant direct database access:
>=20
>     * 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.

>     * maintaining a hierarchy of objects stored as a nested set

Could you elaborate on that?

>     * reporting queries, which can often be highly complex joins

If the query interface is not efficient enough for what you need,
fetchSQL() will probably be handy there. Now go and lobby for this TODO
item ;)  BTW this was discussed some weeks ago, look in the archives for
the thread named "Summary objects and Proxy objects".

  Last, as you probably already noticed it, I usually make my best to
enhance the framework and implement particular features when there's a
strong user request; however most of the time I need use-cases because
it makes things a lot easier, and it also helps in delimiting the area
of the to-be-implemented feature (remember this is all done on my spare
time ;)

  I hope this makes things clearer. If you need further informations and
  clarifications, feel free to ask for more here.

        Regards,

-- S=E9bastien.

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...

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

[Sebastien B. <sbi...@us...>]

Matt Goodall <ma...@po...> writes:
> >>    * 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 obje=
cts
> using a relational model. Here's a link to one article about it,
> http://www.intelligententerprise.com/001020/celko.shtml, but there are pl=
enty
> of others.
>=20
> The nested set example in the article is somewhat simplified since everyt=
hing
> 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 b=
ut
> let the ORM handle the object.

Wow, I remember having applied this to trees when I was doing some
discrete math., but didn't ever apply it to data structures. Nice,
indeed, thanks for the link, now I have two more things, formerly
separated, connected in my brain ;)

  Roaming around the web to read more about this, I've found this
  article you may find interseting as well:=20
  http://www.dbazine.com/tropashko4.html

        Regards,

-- S=E9bastien.