sqlobject-discuss — All things SQLObject-related

Re: [SQLObject] SQLObject Speed ?

[Stef T. <st...@um...>]

Oleg Broytmann wrote:
> On Wed, Jul 22, 2009 at 02:26:57PM -0400, Stef Telford wrote:
>   
>>>> yes. evals appear to be  a 'bad' thing here :\
>>>>         
>>>    Well, those evals are in sqlmeta.addColumn and .addJoin methods, so they
>>> work once for every column in the table, but that's all. After the class
>>> has been created and populated - whatever you do with rows (class instances)
>>> - those evals are not executed.
>>>
>>>       
>> Ah. hrm. *rubs chin* perhaps it's not the evals then. It seems that the  
>> instantiations get .. well .. 'slower' over time.
>>     
>
>    Curiouser and curiouser. IWBN to find where the slowness is *near the
> end* of the loop - i.e. when instantiation becomes really slow.
>
>   
It could be purely a 'feeling' .. I don't have any numbers to back it up 
and I am not entirely sure -how- to benchmark that. This machine does 
have 8gb of ram in it, and a fairly beefy quad core. I have never seen 
any process get near memory exhaustion, which I could believe the calls 
to 'malloc' could slow down but.. yes.

>>    Ordered by: standard name
>>
>>    ncalls  tottime  percall  cumtime  percall filename:lineno(function)
>>     40000    0.063    0.000    0.141    0.000 <string>:1(<lambda>)
>>     
>
>    Can you sort by ncalls and cumtime columns, cut the first 100-200 lines
> and post two tables here?
>
>   
surely, please find the output attached at the bottom. It maybe also 
worth changing the Decimal call to using gmpy or .. something that's 
faster than Decimal perhaps ? I did notice a fair few cycles are spent 
in Decimal.__new__ .. I don't think that's the -entire- story, but it 
should help I would think.
>>         1    0.000    0.000   85.863   85.863 <string>:1(<module>)
>>     
>
>    This is AFAIU the entire script? :)
>
>   
Yes indeed.. that's the way I read the cProfile output as well :D

Regards
Stef

Re: [SQLObject] SQLObject Speed ?

[Oleg B. <ph...@ph...>]

On Wed, Jul 22, 2009 at 02:26:57PM -0400, Stef Telford wrote:
>>> yes. evals appear to be  a 'bad' thing here :\
>>
>>    Well, those evals are in sqlmeta.addColumn and .addJoin methods, so they
>> work once for every column in the table, but that's all. After the class
>> has been created and populated - whatever you do with rows (class instances)
>> - those evals are not executed.
>>
> Ah. hrm. *rubs chin* perhaps it's not the evals then. It seems that the  
> instantiations get .. well .. 'slower' over time.

   Curiouser and curiouser. IWBN to find where the slowness is *near the
end* of the loop - i.e. when instantiation becomes really slow.

> I should stress that  
> the amount of instantiations can reach into thousands easily for me. I  
> know this maybe a niche problem and for 10-30 objects the speed is good.

   Still I'd resolve the issue to at least some extent.

> I have attached the python -m cProfile output, and limited the run to  
> 'only' (?!) 40,000 objects. Hopefully this may make some more sense to  
> you. Gprof is.. definitely.. urm.. quirky :)

>    Ordered by: standard name
> 
>    ncalls  tottime  percall  cumtime  percall filename:lineno(function)
>     40000    0.063    0.000    0.141    0.000 <string>:1(<lambda>)

   Can you sort by ncalls and cumtime columns, cut the first 100-200 lines
and post two tables here?

>         1    0.000    0.000   85.863   85.863 <string>:1(<module>)

   This is AFAIU the entire script? :)

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] SQLObject Speed ?

[Oleg B. <ph...@ph...>]

On Wed, Jul 22, 2009 at 01:27:41PM -0400, Stef Telford wrote:
>>> Hello Oleg, Everyone,
>>>    Sorry for the delay in replying.. "real life" sort of got in the 
>>> way. Deepest apologies for that.
>>
>>    No problem. I understand the issue and feel your pain, sure.
>>
> *chuckles* this is kind of the problem with OS projects. We use them  
> almost 100% here at work but try to spend any -actual- time on improving  
> them and.. well.. yes.

   Well, I have a lot of job, too; and another problem is that my
mother-in-law is very ill so my wife and me have to sit beside her days and
nights.

> actually.. I -did- remark them out. As I said, all the evals do (I  
> -believe-) is to add the lambda into the python dictionary with a  
> specific name for getattr to find

   Not for getattr - for property... but that's a minor technical detail.

> I have read in numerous places  
> that python's eval is hysterically slow
[skip]
> yes. evals appear to be  
> a 'bad' thing here :\

   Well, those evals are in sqlmeta.addColumn and .addJoin methods, so they
work once for every column in the table, but that's all. After the class
has been created and populated - whatever you do with rows (class instances)
- those evals are not executed.
   It could be that the functions the evals produced are slow - you've
commented out all those evals and functions. That again require
experimentation. And what is worse - those functions are hard to "fix"
without much architectural changes.

>>    Perhaps those evals
>> could be replaced with functools.partial
>>
> functools partial ? isn't this another name for decorators ?

   No, not at all - it is pure functional approach to a completely
different problem.

>>> (ps. I -do- have a large PNG of the system during a single 'page   
>>> impression'. It's about 7mb .. I can send it offlist if your 
>>> interested ?)
>>
>>    A PNG of the system? What's that? I don't understand...
>>
> It's a PNG of the profiling of a single 'page'. I profiled it and then  
> passed it through gprof2dot (  
> http://code.google.com/p/jrfonseca/wiki/Gprof2Dot ) let me know if you  
> want it.. it -is- a VERY large PNG though :)

   Ah, I see - gprof. I have never used it so I doubt I can understand the
results.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] SQLObject Speed ?

[Stef T. <st...@um...>]

Hello again Oleg, Everyone

Oleg Broytmann wrote:
> Hi!
>
> On Wed, Jul 22, 2009 at 11:19:36AM -0400, Stef Telford wrote:
>   
>> Hello Oleg, Everyone,
>>    Sorry for the delay in replying.. "real life" sort of got in the way. 
>> Deepest apologies for that.
>>     
>
>    No problem. I understand the issue and feel your pain, sure.
>
>   
*chuckles* this is kind of the problem with OS projects. We use them 
almost 100% here at work but try to spend any -actual- time on improving 
them and.. well.. yes.
>>    When I benchmarked the SQLObject page of 7000+ objects (give or take  
>> a few hundred), I got the 80-90 seconds. With plain python objects, I  
>> got the 0.3 seconds (obviously a lot less going on, so, not surprising  
>> there). However, when I took out the evals inside sqlobject/main.py, I  
>> got the page speed to around 15-20 seconds.
>>     
>
>    Aha, that's the results! Thank you!
>
>    In what way have you "took out the evals"? You cannot just remove them so
> you probably replaced them? with what?
>
>   
actually.. I -did- remark them out. As I said, all the evals do (I 
-believe-) is to add the lambda into the python dictionary with a 
specific name for getattr to find (I am probably horribly mangling 
pythonic terms here, and I apologise). I have read in numerous places 
that python's eval is hysterically slow, and I think that this is 
somewhat borne out. I know that (obviously) the code is missing so it's 
not a straight 1<->1 in terms of execution, but, yes. evals appear to be 
a 'bad' thing here :\
>>    So, this raises the question, is it perhaps better to -not- use  
>> eval(..) and instead tinker with the objects __attributes__ or __dict__  
>> ? I am not -that- conversant/knowledgeable in pythons black magic of  
>> object meta programming but.. I definitely think that if we can get away  
>> from calling 'evals' when doing object instantiation that would be a  
>> HUGE performance win.
>>     
>
>    That requires some thinking and experimenting. Perhaps those evals
> could be replaced with functools.partial - that requires python 2.5, so it
> can be implemented after SQLObject 0.11 (which I'm gonna release RSN).
>
>   
functools partial ? isn't this another name for decorators ? I tend to 
get rather lost in python black magic as I said (sorry :). This said, 
stating python 2.5 as a 'baseline' is not a -bad- idea. It's been out in 
one form or another since sep 2006.. that's not exactly cutting edge ;)

I am still wondering though, can't we simply look into the python 
object's dictionary and place functions into there ? if there is an 
existing _SO_get_Blah and a user defined _SO_get_Blah then can't python 
rename the existing one and then include a call at the end to the newly 
placed one onto the end ? I know, this is monkey patching but.. isn't 
that what we are doing with the evals anyway ?
>>    And, in the process of putting my money where my mouth is, I am more  
>> than willing to donate $200 bounty for the fix, either to the person or  
>> charity of their choosing.
>>     
>
>    Wow, what a generous offer! Thank you very much!
>
>   
Not a problem. I -do- use SQLObject day to day, and I figure that since 
my python skills would probably offend everyone should I try, then the 
least I can do is put my money where my mouth is :)
>> (ps. I -do- have a large PNG of the system during a single 'page  
>> impression'. It's about 7mb .. I can send it offlist if your interested 
>> ?)
>>     
>
>    A PNG of the system? What's that? I don't understand...
>
>   
It's a PNG of the profiling of a single 'page'. I profiled it and then 
passed it through gprof2dot ( 
http://code.google.com/p/jrfonseca/wiki/Gprof2Dot ) let me know if you 
want it.. it -is- a VERY large PNG though :)
> Oleg.
>   
Regards
Stef

Re: [SQLObject] SQLObject Speed ?

[Oleg B. <ph...@ph...>]

Hi!

On Wed, Jul 22, 2009 at 11:19:36AM -0400, Stef Telford wrote:
> Hello Oleg, Everyone,
>    Sorry for the delay in replying.. "real life" sort of got in the way. 
> Deepest apologies for that.

   No problem. I understand the issue and feel your pain, sure.

>    When I benchmarked the SQLObject page of 7000+ objects (give or take  
> a few hundred), I got the 80-90 seconds. With plain python objects, I  
> got the 0.3 seconds (obviously a lot less going on, so, not surprising  
> there). However, when I took out the evals inside sqlobject/main.py, I  
> got the page speed to around 15-20 seconds.

   Aha, that's the results! Thank you!

   In what way have you "took out the evals"? You cannot just remove them so
you probably replaced them? with what?

>    So, this raises the question, is it perhaps better to -not- use  
> eval(..) and instead tinker with the objects __attributes__ or __dict__  
> ? I am not -that- conversant/knowledgeable in pythons black magic of  
> object meta programming but.. I definitely think that if we can get away  
> from calling 'evals' when doing object instantiation that would be a  
> HUGE performance win.

   That requires some thinking and experimenting. Perhaps those evals
could be replaced with functools.partial - that requires python 2.5, so it
can be implemented after SQLObject 0.11 (which I'm gonna release RSN).

>    And, in the process of putting my money where my mouth is, I am more  
> than willing to donate $200 bounty for the fix, either to the person or  
> charity of their choosing.

   Wow, what a generous offer! Thank you very much!

> (ps. I -do- have a large PNG of the system during a single 'page  
> impression'. It's about 7mb .. I can send it offlist if your interested 
> ?)

   A PNG of the system? What's that? I don't understand...

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] SQLObject Speed ?

[Stef T. <st...@um...>]

Oleg Broytmann wrote:
> On Fri, May 22, 2009 at 12:52:52PM -0400, Stef Telford wrote:
>   
>> I am merely wondering where things 'slow 
>> down' so much and if there is anything we can do about it :(
>>     
>
>    Can you run your test program under the python profiler and find out the
> slow spot(s)?
>
> Oleg.
>   
Hello Oleg, Everyone,
    Sorry for the delay in replying.. "real life" sort of got in the 
way. Deepest apologies for that.

    When I benchmarked the SQLObject page of 7000+ objects (give or take 
a few hundred), I got the 80-90 seconds. With plain python objects, I 
got the 0.3 seconds (obviously a lot less going on, so, not surprising 
there). However, when I took out the evals inside sqlobject/main.py, I 
got the page speed to around 15-20 seconds.

    So, this raises the question, is it perhaps better to -not- use 
eval(..) and instead tinker with the objects __attributes__ or __dict__ 
? I am not -that- conversant/knowledgeable in pythons black magic of 
object meta programming but.. I definitely think that if we can get away 
from calling 'evals' when doing object instantiation that would be a 
HUGE performance win.

    And, in the process of putting my money where my mouth is, I am more 
than willing to donate $200 bounty for the fix, either to the person or 
charity of their choosing.

    Seriously :)
   
    Regards and thanks
    Stef
(ps. I -do- have a large PNG of the system during a single 'page 
impression'. It's about 7mb .. I can send it offlist if your interested ?)

Re: [SQLObject] Unicode in mysql issue

[Oleg B. <ph...@ph...>]

On Mon, Jun 29, 2009 at 10:36:55PM +0400, Alex wrote:
> How to retreive unicode data from a mysql table with SQLObject?

   Add 'use_unicode=1' to your DB URI; this will replace all StringCol with
UnicodeCol. Or declare your columns:

class Foo(SQLObject):
   bar = UnicodeCol(dbEncoding='utf-8', length=2**16+1)

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] Unicode in mysql issue

[Alex <xk...@gm...>]

Hi all,
How to retreive unicode data from a mysql table with SQLObject?
When I run this code:
class Foo(SQLObject):
    class sqlmeta:
        fromDatabase = True
print Foo.select(Foo.q.id==55)[0].bar
...I get:
"?????????? ????????????? ???????? ?? ?????????? ?????? ? ???"
bar's type is mediumtext and collation is utf8_general_ci.

Bests,
Alex

Re: [SQLObject] Unicode w/ sqlobject

[Oleg B. <ph...@ph...>]

On Sun, Jun 28, 2009 at 02:31:53PM -0400, Markos Kapes wrote:
> Just assumed using the sqlmeta fromDatabase=True formatted columns right 
> (they are typed as utf-8 in the database).
> Why doesn't this work, I wonder?

   Because nobody has implemented that feature. Wanna work on it?

> UnicodeCol  
> works, but the one column is going to be a mighty big text chunk  
> sometimes..... Will sqlobject truncate ever?

   SQLObject - no. Database - yes, they could. Depends on the column's
type.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Unicode w/ sqlobject

[Markos K. <mk...@gm...>]

Just assumed using the sqlmeta fromDatabase=True formatted columns  
right (they are typed as utf-8 in the database).
Why doesn't this work, I wonder? Should I file it as a bug? UnicodeCol  
works, but the one column is going to be a mighty big text chunk  
sometimes..... Will sqlobject truncate ever?
Thanks,
--Marko

On Jun 28, 2009, at 3:21 AM, Imri Goldberg wrote:

> This might be a stupid question, but you didn't include the full  
> source of PrimaryDef, so I have to ask.
> How did you define the column? If you set a StringCol instead of a  
> UnicodeCol, that might explain it.
>
> Also, noe that your second example,
>     "PrimaryDef(lemma=u"λήμμα῾῾).encode("utf-8")"
> does the encoding only after trying to add to the table. Maybe you  
> intended to write
>     "PrimaryDef(lemma=u"λήμμα῾῾.encode("utf-8"))"
> instead?
>
> Cheers,
> Imri
>
>
>
> On Sun, Jun 28, 2009 at 4:22 AM, Markos Kapes <mk...@gm...>  
> wrote:
> OK, so I have a simple object:
> class PrimaryDef(SQLObject):
>     _connection=sqlobject.connectionForURI("mysql://
> infoshopkeeper@localhost/lexiko?
> use_unicode=1&charset=utf8&sqlobject_encoding=utf-8")
>     class sqlmeta:
>         fromDatabase=True
> If I try to add an ascii record, it works.
> If I try to add a unicode record
>        PrimaryDef(lemma=u"λήμμα῾῾)
>                or even
>        PrimaryDef(lemma=u"λήμμα῾῾).encode("utf-8")
> I get the standard
> /opt/local/Library/Frameworks/Python.framework/Versions/2.5/lib/
> python2.5/site-packages/sqlobject/dbconnection.pyc in _insertSQL(self,
> table, names, values)
>     383         return ("INSERT INTO %s (%s) VALUES (%s)" %
>     384                 (table, ', '.join(names),
> --> 385                  ', '.join([self.sqlrepr(v) for v in  
> values])))
>     386
>     387     def transaction(self):
>
> UnicodeDecodeError: 'ascii' codec can't decode byte 0xce in position
> 1: ordinal not in range(128)
>
> So far so good, I'm still thinking that the connection isn't getting
> my  option keys to know it's supposed to be unicode an then chokes at
> sqlrepr, but if I just do
>        PrimaryDef._connection.sqlrepr(u"λήμμα῾῾)
> it gives me
>        "'\xce\xbb\xce\xae\xce\xbc\xce\xbc\xce\xb1'"
>
> What am I missing here? the table is utf8, the dbencoding is utf8, I'm
> even on OS X, which is utf8.... what gives?
> Thanks for any help.
> --Marko
>
>
> ------------------------------------------------------------------------------
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>
>
>
> -- 
> Imri Goldberg
> --------------------------------------
> www.algorithm.co.il/blogs/
> --------------------------------------
> -- insert signature here ----

Re: [SQLObject] Unicode w/ sqlobject

[Imri G. <lor...@gm...>]

This might be a stupid question, but you didn't include the full source of
PrimaryDef, so I have to ask.
How did you define the column? If you set a StringCol instead of a
UnicodeCol, that might explain it.

Also, noe that your second example,
    "PrimaryDef(lemma=u"λήμμα῾῾).encode("utf-8")"
does the encoding only after trying to add to the table. Maybe you intended
to write
    "PrimaryDef(lemma=u"λήμμα῾῾.encode("utf-8"))"
instead?

Cheers,
Imri



On Sun, Jun 28, 2009 at 4:22 AM, Markos Kapes <mk...@gm...> wrote:

> OK, so I have a simple object:
> class PrimaryDef(SQLObject):
>     _connection=sqlobject.connectionForURI("mysql://
> infoshopkeeper@localhost/lexiko?
> use_unicode=1&charset=utf8&sqlobject_encoding=utf-8")
>     class sqlmeta:
>         fromDatabase=True
> If I try to add an ascii record, it works.
> If I try to add a unicode record
>        PrimaryDef(lemma=u"λήμμα῾῾)
>                or even
>        PrimaryDef(lemma=u"λήμμα῾῾).encode("utf-8")
> I get the standard
> /opt/local/Library/Frameworks/Python.framework/Versions/2.5/lib/
> python2.5/site-packages/sqlobject/dbconnection.pyc in _insertSQL(self,
> table, names, values)
>     383         return ("INSERT INTO %s (%s) VALUES (%s)" %
>     384                 (table, ', '.join(names),
> --> 385                  ', '.join([self.sqlrepr(v) for v in values])))
>     386
>     387     def transaction(self):
>
> UnicodeDecodeError: 'ascii' codec can't decode byte 0xce in position
> 1: ordinal not in range(128)
>
> So far so good, I'm still thinking that the connection isn't getting
> my  option keys to know it's supposed to be unicode an then chokes at
> sqlrepr, but if I just do
>        PrimaryDef._connection.sqlrepr(u"λήμμα῾῾)
> it gives me
>        "'\xce\xbb\xce\xae\xce\xbc\xce\xbc\xce\xb1'"
>
> What am I missing here? the table is utf8, the dbencoding is utf8, I'm
> even on OS X, which is utf8.... what gives?
> Thanks for any help.
> --Marko
>
>
>
> ------------------------------------------------------------------------------
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>



-- 
Imri Goldberg
--------------------------------------
www.algorithm.co.il/blogs/
--------------------------------------
-- insert signature here ----

[SQLObject] Unicode w/ sqlobject

[Markos K. <mk...@gm...>]

OK, so I have a simple object:
class PrimaryDef(SQLObject):
     _connection=sqlobject.connectionForURI("mysql:// 
infoshopkeeper@localhost/lexiko? 
use_unicode=1&charset=utf8&sqlobject_encoding=utf-8")
     class sqlmeta:
         fromDatabase=True
If I try to add an ascii record, it works.
If I try to add a unicode record
	PrimaryDef(lemma=u"λήμμα῾῾)
		or even
	PrimaryDef(lemma=u"λήμμα῾῾).encode("utf-8")
I get the standard
/opt/local/Library/Frameworks/Python.framework/Versions/2.5/lib/ 
python2.5/site-packages/sqlobject/dbconnection.pyc in _insertSQL(self,  
table, names, values)
     383         return ("INSERT INTO %s (%s) VALUES (%s)" %
     384                 (table, ', '.join(names),
--> 385                  ', '.join([self.sqlrepr(v) for v in values])))
     386
     387     def transaction(self):

UnicodeDecodeError: 'ascii' codec can't decode byte 0xce in position  
1: ordinal not in range(128)

So far so good, I'm still thinking that the connection isn't getting  
my  option keys to know it's supposed to be unicode an then chokes at  
sqlrepr, but if I just do
	PrimaryDef._connection.sqlrepr(u"λήμμα῾῾)
it gives me
	"'\xce\xbb\xce\xae\xce\xbc\xce\xbc\xce\xb1'"

What am I missing here? the table is utf8, the dbencoding is utf8, I'm  
even on OS X, which is utf8.... what gives?
Thanks for any help.
--Marko

Re: [SQLObject] Using __connection__ in a multi-thread application

[Oleg B. <ph...@ph...>]

On Wed, Jun 24, 2009 at 01:50:29PM +0400, Alex wrote:
> how can I predefine connection for each thread without
> the necessity to pass the connection explicitly on every
> instantiation?

from sqlobject import sqlhub
sqlhub.threadConnection = connectionForURI(connection_string)

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] Using __connection__ in a multi-thread application

[Alex <xk...@gm...>]

Is it thread safe to define __connections__ on the startup of a server
for the later use in multiple threads?

...
__connection__ = connectionForURI(connection_string)

class Person(SQLObject):
    ...

class MultithreadAppUsingPerson:
    ...
    persons = Person.select()
    ...

aMultithreadAppUsingPerson.run()

If it's not ok, how can I predefine connection for each thread without
the necessity to pass the connection explicitly on every
instantiation? I would really like to workaround the straightforward
code-consuming approach.

Re: [SQLObject] Quoting reserved column names...

[Dan P. <da...@ag...>]

On 22 Jun 2009, at 17:20, Oleg Broytmann wrote:

> On Thu, Jun 11, 2009 at 11:50:09AM +0300, Dan Pascu wrote:
>> On 11 Jun 2009, at 11:23, jonhattan wrote:
>>> Sam's Lists escribi?:
>>>> Is there a way to force SQLObject to quote it properly?
>>>>
>>> you can force the name of the column in the database this way:
>>>
>>> condition = StringCol(dbName='_condition')
>>
>> Or simply quote it and you do not need to change anything:
>>
>> condition = StringCol(dbName='`condition`')
>
>   But you have to remember this may cause problems when SQLObject uses
> dbName to construct a complex name like sequence name (in Postgres).


That quoting is only valid and necessary in mysql. IMO all this  
quoting of table and column names should be done internally by the  
specific database driver according to the specific database  
requirements. This manual quoting is just a workaround.

--
Dan

Re: [SQLObject] Trouble with cascade

[Oleg B. <ph...@ph...>]

Hello. Sorry for the late answer. I was on vacation. (It is strange nobody
answered the question.)

On Mon, Jun 15, 2009 at 06:56:06PM +0300, Imri Goldberg wrote:
> I had some trouble with cascade='null' not being acted on when using
> deleteMany instead of destroySelf.
[skip]
> The output is:
> [<Abc 2 field1='goodbye' finalID=1>, <Abc 4 field1='shmidt' finalID=None>]
> I would expect that both objects would have finalID set to None.

   .deleteMany() sens DELETE query to the backend, so for ON DELETE
constraints it requires the backend properly implements cascading. It seems
SQLite doesn't implement it. I ran you program verbatim on Postgtres and
both finalID were None.
   .destroySelf() implements cascading itself, without relying on a
backend.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] MyTable.select(False).filter(False).count() crashes!

[Oleg B. <ph...@ph...>]

On Mon, Jun 22, 2009 at 06:30:24PM +0400, Oleg Broytmann wrote:
> On Thu, Jun 11, 2009 at 07:26:59PM +0000, Matthew Wilson wrote:
> >     >>> sr = MyTable.select(false) # obviously empty
> >     >>> sr.filter(False).count()
> >     TypeError: 'bool' object is unsubscriptable
> 
>    Select clause is expected to be either SQLExpression or a string, not
> bool.

   Well, I have to explain this a bit better. If SQLObject got an
SQLExpression or a string like 'test=1' it construct a query string like
this: SELECT * FROM table WHERE test=1.
   There are two special cases for the clause - it can be None or the
string 'all'. In this case SQLObject doesn't add WHERE at all or adds a
trivial clause like '1=1'.
   But what SQLObject should do in case of bool? What SQL query should be
constructed from MyTable.select(False)? Currently SQLObject constructs
WHERE 0, but it is only a happy accident. Boolean values as a clause are
not currently supported.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Call a function in a schema

[Oleg B. <ph...@ph...>]

On Thu, Jun 11, 2009 at 11:30:38AM +0200, Christian Schwartze wrote:
> Is it possible to call functions with func() in a postgresql schema other than the public one? I am using SQLObject 0.10.
> Thanks a lot for your help!

   getattr(func, 'schema.function()')

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] MyTable.select(False).filter(False).count() crashes!

[Oleg B. <ph...@ph...>]

On Thu, Jun 11, 2009 at 07:26:59PM +0000, Matthew Wilson wrote:
>     >>> sr = MyTable.select(false) # obviously empty
>     >>> sr.filter(False).count()
>     TypeError: 'bool' object is unsubscriptable

   Select clause is expected to be either SQLExpression or a string, not
bool.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Quoting reserved column names...

[Oleg B. <ph...@ph...>]

On Thu, Jun 11, 2009 at 11:50:09AM +0300, Dan Pascu wrote:
> On 11 Jun 2009, at 11:23, jonhattan wrote:
> > Sam's Lists escribi?:
> >> Is there a way to force SQLObject to quote it properly?
> >>
> > you can force the name of the column in the database this way:
> >
> > condition = StringCol(dbName='_condition')
> 
> Or simply quote it and you do not need to change anything:
> 
> condition = StringCol(dbName='`condition`')

   But you have to remember this may cause problems when SQLObject uses
dbName to construct a complex name like sequence name (in Postgres).

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] longId=True handling...

[Oleg B. <ph...@ph...>]

On Wed, Jun 10, 2009 at 06:06:16PM -0700, Sam's Lists wrote:
> class sqlmeta:
>         style = MixedCaseStyle(longID=True)
> 
> It appears that under the really old versions of TurboGears one could
> refer to this EventQueueID as just .id and everything would work the
> way you want it to.  (This appears to only be done in the app under
> kid templates).
> 
> But now, under TG 1.0.8/the latest version of SQLObject that doesn't work.
>  Either I have to rename the
> column to id or I have to refer to it as EventQueueID.

   Well, you can always give the name of the column:

class MyTable(SQLObject):
   class sqlmeta:
      style = MixedCaseStyle(longID=True)
      idName = 'id' # or 'MyTableID' or whatever.

> How can I make sqlObject seemlessly let me refer to columns named in
> the long style just using id?

class Test(SQLObject):
    class sqlmeta:
        style = MixedCaseStyle(longID=True)

Test.createTable()

test = Test()
print test.id

   Output (SQLObject 0.10):

  1/Query   :  CREATE TABLE Test (
    id INTEGER PRIMARY KEY
)
 1/QueryR  :  CREATE TABLE Test (
    id INTEGER PRIMARY KEY
)
 1/Query   :  CREATE TABLE Test (
    id INTEGER PRIMARY KEY
)
 1/QueryR  :  CREATE TABLE Test (
    id INTEGER PRIMARY KEY
)
 2/QueryIns:  INSERT INTO Test VALUES (NULL)
 2/QueryR  :  INSERT INTO Test VALUES (NULL)
 3/QueryOne:  SELECT NULL FROM Test WHERE ((Test.id) = (1))
 3/QueryR  :  SELECT NULL FROM Test WHERE ((Test.id) = (1))
 1/Query   :  CREATE TABLE Test (
    id INTEGER PRIMARY KEY
)
 1/QueryR  :  CREATE TABLE Test (
    id INTEGER PRIMARY KEY
)
 2/QueryIns:  INSERT INTO Test VALUES (NULL)
 2/QueryR  :  INSERT INTO Test VALUES (NULL)
 3/QueryOne:  SELECT NULL FROM Test WHERE ((Test.id) = (1))
 3/QueryR  :  SELECT NULL FROM Test WHERE ((Test.id) = (1))

   No problem so far - id is id, not TestId. What am I doing wrong?

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] transactions; cache sync behavior; sqlite3 relatet?

[Oleg B. <ph...@ph...>]

On Sat, Jun 06, 2009 at 12:20:50PM +0200, Herwig Hochleitner wrote:
> I intended to illustrate following behavior:
>
> Two instances of the same class with same id; one pulled via a  
> connection (with cache=False); one pulled via a transaction createt from  
> that same connection;
> --> Commits to the transaction _do_ invalidate the instance pulled via  
> connection (thanks to cache=False);
> --> Changes to the connection instance _don't_ invalidate the  
> transaction instance;
>
> This asymmetric behavior seems strange to me

   AFAIU it is meaningless from the transaction point of view. Changes
outside a transaction (regardless of the backend and SQLObject) must not
creep inside the transaction, right?
   Actually, in SQLite one cannot even change the DB outside a transaction
if there is an open transaction. Let's see. Start sqlite3 test.db and
initialize a table:

sqlite> CREATE TABLE test (id integer PRIMARY KEY, test VARCHAR (255));
sqlite> INSERT INTO test (test) VALUES ('test1');
sqlite> INSERT INTO test (test) VALUES ('test2');
sqlite> SELECT * FROM test;
1|test1
2|test2

   Now start another sqlite3, open the same DB test.db and start a
transaction:

sqlite> BEGIN;
sqlite> SELECT * FROM test;
1|test1
2|test2

   Back to the first connection:

sqlite> INSERT INTO test (test) VALUES ('test3');
SQL error: database is locked
sqlite> UPDATE test SET test='test3';
SQL error: database is locked

   No way, see? Now the second transactioned connection:

sqlite> COMMIT;

   Back to the first connection:

sqlite> INSERT INTO test (test) VALUES ('test3');

   And again the second transactioned connection:

sqlite> SELECT * FROM test;
1|test1
2|test2
3|test3

   Let us see how Postgres handles this. First createdb test and start two
psql test.

test=# CREATE TABLE test (id serial, test VARCHAR(255));
NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for serial column "test.id"
CREATE TABLE
test=# INSERT INTO test (test) VALUES ('test1');
INSERT 0 1
test=# INSERT INTO test (test) VALUES ('test2');
INSERT 0 1
test=# SELECT * FROM test;
 id | test  
----+-------
  1 | test1
  2 | test2
(2 rows)

   Second connection:

test=# BEGIN;
BEGIN
test=# SELECT * FROM test;
 id | test  
----+-------
  1 | test1
  2 | test2
(2 rows)

   First connection:

test=# INSERT INTO test (test) VALUES ('test3');
INSERT 0 1

   Second transactioned connection:

test=# SELECT * FROM test;
 id | test  
----+-------
  1 | test1
  2 | test2
  3 | test3
(3 rows)

   The new row is visible. Back to the first connection:

test=# UPDATE test SET test='test4';
UPDATE 3

   Second transactioned connection:

test=# SELECT * FROM test;
 id | test  
----+-------
  1 | test4
  2 | test4
  3 | test4
(3 rows)

   The change is visible inside the transaction in Read Committed Isolation
Level. Let's try Serializable Isolation Level. Second transactioned connection:

test=# COMMIT;
ROLLBACK
test=# BEGIN;
BEGIN
test=# SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET
test=# SELECT * FROM test;
 id | test  
----+-------
  1 | test4
  2 | test4
  3 | test4
(3 rows)

   First connection:

test=# INSERT INTO test (test) VALUES ('test5');
INSERT 0 1

   Second transactioned connection:

test=# SELECT * FROM test;
 id | test  
----+-------
  1 | test4
  2 | test4
  3 | test4
(3 rows)

   No changes, see? First connection:

test=# UPDATE test SET test='test6';
UPDATE 4
test=# SELECT * FROM test;
 id | test  
----+-------
  1 | test6
  2 | test6
  3 | test6
  4 | test6
(4 rows)

   Second transactioned connection:

test=# SELECT * FROM test;
 id | test  
----+-------
  1 | test4
  2 | test4
  3 | test4
(3 rows)

   Still no changes. Data added or changed outside a transaction is not
visible in the transaction in Serializable Isolation Level. So SQLObject
has to be very clever to manipulate caches inside and outside transactions.

> Can I evade manually syncing all the transactions.

   Well, the simplest (not the best) way would be, probably, to turn off
caching completely:

class MyTable(SQLObject):
   class sqlmeta:
      cacheValues = False

> what's the  
> best way to invalidate a transaction's cache for a given instance/all  
> instances of given class?

   MyTable.sqlmeta.expireAll() or MyTable.sqlmeta.expireAll(transaction)

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] Trouble with cascade

[Imri G. <lor...@gm...>]

Hi
I had some trouble with cascade='null' not being acted on when using
deleteMany instead of destroySelf.
I managed to reproduce it with a simple example. When running the following
code:

from sqlobject import *

class Abc(SQLObject):
    field1 = StringCol()
    final = ForeignKey('Abc', cascade='null')
    sources = MultipleJoin('Abc', joinColumn = 'finalID')

def main():
    conn = connectionForURI('sqlite:/:memory:')
    sqlhub.processConnection = conn
    #Abc._connection.debug = True
    Abc.createTable()

    a1 = Abc(field1='hello', final = None)
    a2 = Abc(field1='goodbye', final = a1)
    a3 = Abc(field1='gram', final = None)
    a4 = Abc(field1='shmidt', final = a3)
    a3.destroySelf()
    Abc.deleteMany(Abc.q.field1=='hello')
    print list(Abc.select())

if __name__ == '__main__':
    main()

The output is:
[<Abc 2 field1='goodbye' finalID=1>, <Abc 4 field1='shmidt' finalID=None>]
I would expect that both objects would have finalID set to None.

I'd appreciate any help with this.

Cheers,
Imri

-- 
Imri Goldberg
--------------------------------------
www.algorithm.co.il/blogs/
--------------------------------------
-- insert signature here ----

[SQLObject] MyTable.select(False).filter(False).count() crashes!

[Matthew W. <ma...@tp...>]

I get a weird error when I try to filter a selectResults object after
it is already empty.

It can be reproduced like this:

    >>> sr = MyTable.select(false) # obviously empty
    >>> sr.filter(False).count()
    TypeError: 'bool' object is unsubscriptable

This is a weird one.  I build a lot of dynamic queries and I don't
check as I add filters if I have an empty select results.

Is this a bug?  If so, I can try to fix.

Matt

Re: [SQLObject] FW: longId=True handling...

[Joe L. <joe...@gm...>]

This issue seems to be caused by a change in SQLObject.
The change in behaviour seems to be due to a change made in SQLObject in
main.py, where the ‘idName’ attribute was taken out of a list of unshared
attributes.  This caused the sqlmeta class to share the idName value with
its containing class, so they both end up with ‘id’ even when longID is set
to true in your own class.  If you add it back in:
_unshared_attributes = ['table', 'columns', 'childName', 'idName']
then it should work as before...however, I'm not sure if this will cause
other problems (so far it seems to work).

It seems that the longID feature is broken unless this little change is
made.  OR, are we misunderstanding the "new" way to make longID work
properly?

By the way, here is a link to a change that seems to have triggered this
change in behaviour:
http://www.mail-archive.com/sql...@li.../msg02159.html

Joe

>
>
> *From:* Sam's Lists [mailto:sam...@gm...]
> *Sent:* Wednesday, June 10, 2009 9:06 PM
> *To:* sqlobject-discuss
> *Subject:* [SQLObject] longId=True handling...
>
>
>
> I posted this on the TurboGears mailing list but now I'm posting it here in
> hopes for more help.
>
> Essentially I am upgrading from SQLObject 0.7.1dev_r1653 and MySQL 4.x
> something to MySQL 5 something and SQLObject 0.10.6
>
> I have this very complicated legacy TurboGears application that I'm
> tasked with upgrading to 1.0.8 which has laid stagnant since the days
> of early .99 series.
>
> The original author used the following a lot in his table definitions:
>
> class sqlmeta:
>         style = MixedCaseStyle(longID=True)
>
> What this means is that instead of the id column being named 'id' it
> takes on the name of the table...so if the table is called EventQueue,
> the id column name is EventQueueID.
>
> So far so good....but here's where it gets weird.
>
> It appears that under the really old versions of TurboGears one could
> refer to this EventQueueID as just .id and everything would work the
> way you want it to.  (This appears to only be done in the app under
> kid templates).
>
> But now, under TG 1.0.8/the latest version of SQLObject that doesn't work.
>  Either I have to rename the
> column to id or I have to refer to it as EventQueueID.
>
> How can I make sqlObject seemlessly let me refer to columns named in
> the long style just using id?
>
> Thanks
>
>
>