sqlobject-discuss

[SQLObject] One to 'a lot' relationship

[Robert L. <ro...@le...>]

What is the best (read fastest) way to handle a 1-many relationship in 
SQLObject where the many is thousands of rows? The current 0.5.2 release 
issues an individual select for every row on the many side, which is a 
significant bottleneck. Is the SVN version better in this regard or do I 
need to code up something specific for this situation? If SVN is the way 
to go, is there a specific upgrade strategy I need to follow or can I 
just uninstall 0.5.2 and install the SVN version w/o requiring db changes?


Robert

[so many questions, so few answers:-(]

Re: [SQLObject] One to 'a lot' relationship

[Ian B. <ia...@co...>]

Robert Leftwich wrote:
> What is the best (read fastest) way to handle a 1-many relationship in 
> SQLObject where the many is thousands of rows? The current 0.5.2 release 
> issues an individual select for every row on the many side, which is a 
> significant bottleneck. Is the SVN version better in this regard or do I 
> need to code up something specific for this situation? If SVN is the way 
> to go, is there a specific upgrade strategy I need to follow or can I 
> just uninstall 0.5.2 and install the SVN version w/o requiring db changes?

SVN is significantly better, though I actually thought 0.5.2 also did 
this properly.  Anyway, it's supposed to fetch all the columns on any 
select.

There's no database changes necessary to upgrade, but the main module 
has changed names from SQLObject to sqlobject, and MyClass(id) is now 
MyClass.get(id), and MyClass.new(**kw) is now MyClass(**kw).

-- 
Ian Bicking  /  ia...@co...  / http://blog.ianbicking.org

Re: [SQLObject] One to 'a lot' relationship

[Robert L. <ro...@le...>]

(oops, did not send to the list, apologies for the direct email Ian)

Ian Bicking wrote:
> 
> SVN is significantly better, though I actually thought 0.5.2 also did 
> this properly.  Anyway, it's supposed to fetch all the columns on any 
> select.
> 
> There's no database changes necessary to upgrade, but the main module 
> has changed names from SQLObject to sqlobject, and MyClass(id) is now 
> MyClass.get(id), and MyClass.new(**kw) is now MyClass(**kw).
> 
To add to the list of changes:

The connection logic is deprecated, e.g. you now need to use :

   from sqlobject.postgres import builder;
   PostgresConnection = builder();
   conn = PostgresConnection(...)

or

   connectionForURI("postgres://...")

I'm using the former as I can't seem to get the latter to work atm.

The names of the MultipleJoin properties now have an 's' appended to
them (I'm not sure if they are fully pluralised, i.e. is address now
addresses?). Also the capitalisation seems to have changed so that for a
joined table ABCUnit the property is now aBCUnits instead of the old
abcUnit - this seems a little counter intuitive, is it possible to
revert back or select the old naming scheme in some way.

In addition the rev206 SVN release still issues individual selects for
every row in the joined table, e.g.

It will issue :

SELECT id FROM address WHERE person_id = 299

and then for every returned id it will issue:

SELECT * FROM address WHERE id = 488
...
(3500 times)

where * is the name of every column in the address table.

The culprit seems to be

class FileConnection(DBConnection):

     ...

     def _SO_selectJoin(self, soClass, column, value):
         results = []
         # @@: seems lame I need to do this...
         value = int(value)
         for id in self._allIDs(soClass._table):
             d = self._fetchDict(soClass._table, id)
             if d[column] == value:
                 results.append((id,))
         return results


which overrides the default _SO_selectJoin in DBConnection.

Am I doing something wrong?

Robert

[SQLObject] connection document on wiki

[Charles B. <li...@st...>]

Hi,

I've been revisiting the concept of connections and caching in SQLObject to
get a few of the nuances straight in my head.  In the process I tried to jot
these concepts down in writing and posted them on the SQLObject Wiki site
here:
http://wiki.sqlobject.org/connections.html

This process lead me to two big questions on the topic:

1.  Is there a way to manually close a connection?
2.  Is there a way to manually expire the cache of a connection?

In a prior mailing list thread I think I read that objects are not coupled
with connections, so maybe question 2 is worded incorrectly.  (this is
specifically asked with the method of passing a connection to a object in
mind).

On a more general note I'd be very interested to hear details on how others
deal with connections in their SQLObject applications, especially
multi-process applications.

Thanks in advance,
-Charles.

Re: [SQLObject] connection document on wiki

[Ian B. <ia...@co...>]

Charles Brandt wrote:
> Hi,
> 
> I've been revisiting the concept of connections and caching in SQLObject to
> get a few of the nuances straight in my head.  In the process I tried to jot
> these concepts down in writing and posted them on the SQLObject Wiki site
> here:
> http://wiki.sqlobject.org/connections.html
> 
> This process lead me to two big questions on the topic:
> 
> 1.  Is there a way to manually close a connection?

Hmm... no.  And since SQLObject does pooling it's not trivial.

> 2.  Is there a way to manually expire the cache of a connection?

connection.cache.expireAll() -- that won't removed the cached objects, 
but it will only hold weak references.

> In a prior mailing list thread I think I read that objects are not coupled
> with connections, so maybe question 2 is worded incorrectly.  (this is
> specifically asked with the method of passing a connection to a object in
> mind).

They are coupled rather strongly to connections.

> On a more general note I'd be very interested to hear details on how others
> deal with connections in their SQLObject applications, especially
> multi-process applications.

Yes, that's still a bit of an open issue.  It's hard, because it really 
depends on how accurate you want to be -- you can be very accurate by 
checking each time an attribute is accessed, but that's really 
inefficient, and may give you an inconsistent view.  I'd be interested 
in hearing other people's thoughts as well.

-- 
Ian Bicking  /  ia...@co...  / http://blog.ianbicking.org

Re: [SQLObject] connection document on wiki

[Charles B. <li...@st...>]

> > 1.  Is there a way to manually close a connection?
>
> Hmm... no.  And since SQLObject does pooling it's not trivial.

I'm more interested in removing and closing connection objects themselves
(i.e. all connections in a specific connection object's pool) vs a single
specific connection in a connection object's pool.   This would be
especially useful when the connection object is only passed into objects
when they are fetched (vs defined in the object definition).

When I use connection objects this way, they are effectively a pool of one
connection used for fetching/updating one object.  Granted this is not as
efficient as keeping a pool of connections (or a connection per thread) but
it is handy for a quick test or rare use situations.  (even then it is
impractical without a close function though).

I'm not sure if looking at this higher level would simplify the problem to a
more trival one. My work around is to re-use connections as intended. :)

> > 2.  Is there a way to manually expire the cache of a connection?
>
> connection.cache.expireAll() -- that won't removed the cached objects,
> but it will only hold weak references.

I think cache in "connection.cache.expireAll()" is actually a CacheSet
object, and expireAll() is a method of CacheFactory.  That lead me to try
the following:

for key in connection.cache.caches.keys():
     connection.cache.caches[key].expireAll()

but got:

  File
"/System/Library/Frameworks/Python.framework/Versions/2.3/lib/python2.3/site
-packages/SQLObject/Cache.py", line 151, in expireAll
    self.expiredCache[key] = ref(obj)
NameError: global name 'obj' is not defined

should obj in this case actually be value from the previous line of:
     for key, value in self.cache.items():
?

I can file a bug report if so.

In the mean time I'm giving the method posted by Eddie Corns in July a
closer look:
http://thread.gmane.org/gmane.comp.python.sqlobject/1630

> > On a more general note I'd be very interested to hear details on how
others
> > deal with connections in their SQLObject applications, especially
> > multi-process applications.
>
> Yes, that's still a bit of an open issue.  It's hard, because it really
> depends on how accurate you want to be -- you can be very accurate by
> checking each time an attribute is accessed, but that's really
> inefficient, and may give you an inconsistent view.  I'd be interested
> in hearing other people's thoughts as well.

My goal with these questions is to end up with an approach that caches
fetched data for a specific page request, and then flushes the cache at the
end of the request.  In practice this might not save much over checking the
database each time, but its the best compromise I've come up with so far.


Thanks Ian!

-Charles.

Re: [SQLObject] connection document on wiki

[Michael W. <li...@mi...>]

> I think cache in "connection.cache.expireAll()" is actually a CacheSet
> object, and expireAll() is a method of CacheFactory.  That lead me to t=
ry
> the following:
>
> for key in connection.cache.caches.keys():
>      connection.cache.caches[key].expireAll()

Hmnn... that's almost exactly what I have done.

import gc
def expire_all():
    c =3D get_connection()
    for k in c.cache.caches.keys():
        c.cache.caches[k].expireAll()
    gc.collect()

Ah, thought this looked familiar:

"The SQLObject code has a bug in expireAll =96 ref(obj) instead of
ref(value) =96 and the method isn=92t called from anywhere in the code ei=
ther,
at least not in the svn checkout I am looking at."

http://mikewatkins.net/categories/technical/2004-07-14-1.html