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.