sqlobject-discuss — All things SQLObject-related

Re: [SQLObject] Problem Creating the "Same" ForeignKey Twice

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

First, are you using CVS?  There's a bug in 0.4 related to this.  CVS 
will be 0.5 once I package it up.

On Tuesday, October 14, 2003, at 12:58 PM, Brad Bollenbach wrote:
> Hi,
>
> I have a class like this:
>
> class Refund(SQLObject):
>     """A transaction where somebody was given some or all of
>     their money back for previous Purchase."""
>
>     ...[snip overriding of new and a couple class variables]...
>
>     transactionID = ForeignKey('Transaction')
>     refundedTransactionID = ForeignKey('Transaction')
>
> The first FK pointing to Transaction, represents the transaction that 
> took place to do this refund. The second FK, also pointing to 
> Transaction (refundedTransactionID), represents the transaction for 
> which the refund was done. So, I need to have two FK's pointing to the 
> same relation, each one, of course, pointing to a different row in the 
> foreign relation (the transaction table.)
>
> When I create the refund table with only one of the ForeignKey()s 
> present, it works fine, but if I try to create it (calling 
> createTable) with both defined as above, I get:
>
> Traceback (most recent call last):
>   File "lib/merchant.py", line 252, in ?
>     class Refund(SQLObject):
>   File "../SQLObject/SQLObject.py", line 233, in __new__
>   File "../SQLObject/SQLObject.py", line 503, in addColumn
>   File "../SQLObject/SQLObject.py", line 102, in addNeedSet
>   File "../SQLObject/SQLObject.py", line 404, in __new__
>   File "../SQLObject/SQLObject.py", line 662, in _init
>   File "../SQLObject/DBConnection.py", line 254, in _SO_selectOne
>   File "../SQLObject/SQLBuilder.py", line 126, in sqlRepr
> ValueError: Unknown SQL builtin type: <class 
> 'SQLObject.SQLObject.MetaSQLObject'> for <class > '__main__.Transaction'>
>
> Is there a known restriction/reason for why I can't have two FK's in A 
> that both point to B, or is this a bug?
>
> --
> Brad Bollenbach
> BBnet.ca
>
>
>
> -------------------------------------------------------
> This SF.net email is sponsored by: SF.net Giveback Program.
> SourceForge.net hosts over 70,000 Open Source Projects.
> See the people who have HELPED US provide better services:
> Click here: http://sourceforge.net/supporters.php
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>
--
Ian Bicking | ia...@co... | http://blog.ianbicking.org

Re: [SQLObject] Overriding new()..._SO_new()?

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

On Tuesday, October 14, 2003, at 12:49 PM, Brad Bollenbach wrote:
> On Friday, October 10, 2003, at 05:08 PM, Ian Bicking wrote:
>
>> On Friday, October 10, 2003, at 03:18 PM, Brad Bollenbach wrote:
>>> On Friday, October 10, 2003, at 11:23 AM, Ian Bicking wrote:
>>>>> Am I missing a way in which this can be done, or should SQLObject 
>>>>> have an _SO_new?
>>>>
>>>> You can override new() just like it is.  (Though number 1 can 
>>>> probably be handled with a column default)
>>>
>>> Then what method do I call to access the "original" new once I've 
>>> done the overriding? When overriding columns, I'd just use the 
>>> _SO_'s, but when overriding new, it's unclear (to me, at least.)
>>
>> _SO_get_colName is an exception, because the SQLObject class doesn't 
>> implement methods for your columns.  But SQLObject implements new(), 
>> so you can call SQLObject.new(cls, **kw) (or use super()).
>
> I've hacked this in, but I still don't quite agree with this 
> implementation-detail-reasoning, for a few reasons:
>
> 1. I'm calling SQLObject.new, instead of TableName.new. The "one class 
> for one table" metaphor starts losing its balance -- I'm used to being 
> able to look at the class on which new was called to see the name of 
> the table in which the new() row will be created. That's no longer 
> applicable here.

Sure you can access the table name and all that stuff.  
SQLObject.new(cls, **kw) calls the SQLObject.new *method*, but the 
*class* (cls) is still your TableName class.

> 2. I have to pass the class instance to the SQLObject.new call, which 
> is not the way I'm used to being able to call new.

That's just the way Python works, this is how you call a superclass.  
Or use super(cls).new(**kw), which should work fine too (except maybe 
in some early versions of 2.2 that are buggy).  The two are equivalent.

> 3. Conceptually, I expect to think of new() as a "method created at 
> runtime" too -- and therefore, provided with an _SO_ method like each 
> column's property methods -- because (conceptually) it wouldn't know 
> how to go about creating my columns until it knows what columns I've 
> defined. The implementation detail, however, breaks the metaphor, > IMHO.

The only methods created at runtime are the column and join methods, 
because (from the SQLObject class's perspective) there are an unknown 
number of these methods, with unknown names.  These created methods are 
all very small, and point to methods defined in the SQLObject class.

> I'm either being reasonable, picky, stubborn, or all of the above, but 
> in the discussion we had on IRC, Sidnei seemed to think _SO_new would 
> be useful too.

Yep, picky and stubborn  ;)

You could argue that SQLObject shouldn't use inheritance, and that the 
mechanics of persistence should be put in a separate object.  This 
isn't unreasonable, and maybe it could happen at some time -- maybe Col 
and its subclasses would turn into descriptors (akin to property), and 
you'd define the mapping in some prescribed instance variable (and that 
mapping would subclass from some SQLObject class).  But cls._SO_new vs. 
SQLObject.new is only a trivial side effect of subclassing.

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

[SQLObject] Problem Creating the "Same" ForeignKey Twice

[Brad B. <br...@bb...>]

Hi,

I have a class like this:

class Refund(SQLObject):
     """A transaction where somebody was given some or all of
     their money back for previous Purchase."""

     ...[snip overriding of new and a couple class variables]...

     transactionID = ForeignKey('Transaction')
     refundedTransactionID = ForeignKey('Transaction')

The first FK pointing to Transaction, represents the transaction that 
took place to do this refund. The second FK, also pointing to 
Transaction (refundedTransactionID), represents the transaction for 
which the refund was done. So, I need to have two FK's pointing to the 
same relation, each one, of course, pointing to a different row in the 
foreign relation (the transaction table.)

When I create the refund table with only one of the ForeignKey()s 
present, it works fine, but if I try to create it (calling createTable) 
with both defined as above, I get:

Traceback (most recent call last):
   File "lib/merchant.py", line 252, in ?
     class Refund(SQLObject):
   File "../SQLObject/SQLObject.py", line 233, in __new__
   File "../SQLObject/SQLObject.py", line 503, in addColumn
   File "../SQLObject/SQLObject.py", line 102, in addNeedSet
   File "../SQLObject/SQLObject.py", line 404, in __new__
   File "../SQLObject/SQLObject.py", line 662, in _init
   File "../SQLObject/DBConnection.py", line 254, in _SO_selectOne
   File "../SQLObject/SQLBuilder.py", line 126, in sqlRepr
ValueError: Unknown SQL builtin type: <class 
'SQLObject.SQLObject.MetaSQLObject'> for <class '__main__.Transaction'>

Is there a known restriction/reason for why I can't have two FK's in A 
that both point to B, or is this a bug?

--
Brad Bollenbach
BBnet.ca

Re: [SQLObject] Overriding new()..._SO_new()?

[Brad B. <br...@bb...>]

On Friday, October 10, 2003, at 05:08 PM, Ian Bicking wrote:

> On Friday, October 10, 2003, at 03:18 PM, Brad Bollenbach wrote:
>> On Friday, October 10, 2003, at 11:23 AM, Ian Bicking wrote:
>>>> Am I missing a way in which this can be done, or should SQLObject 
>>>> have an _SO_new?
>>>
>>> You can override new() just like it is.  (Though number 1 can 
>>> probably be handled with a column default)
>>
>> Then what method do I call to access the "original" new once I've 
>> done the overriding? When overriding columns, I'd just use the 
>> _SO_'s, but when overriding new, it's unclear (to me, at least.)
>
> _SO_get_colName is an exception, because the SQLObject class doesn't 
> implement methods for your columns.  But SQLObject implements new(), 
> so you can call SQLObject.new(cls, **kw) (or use super()).

I've hacked this in, but I still don't quite agree with this 
implementation-detail-reasoning, for a few reasons:

1. I'm calling SQLObject.new, instead of TableName.new. The "one class 
for one table" metaphor starts losing its balance -- I'm used to being 
able to look at the class on which new was called to see the name of 
the table in which the new() row will be created. That's no longer 
applicable here.

2. I have to pass the class instance to the SQLObject.new call, which 
is not the way I'm used to being able to call new.

3. Conceptually, I expect to think of new() as a "method created at 
runtime" too -- and therefore, provided with an _SO_ method like each 
column's property methods -- because (conceptually) it wouldn't know 
how to go about creating my columns until it knows what columns I've 
defined. The implementation detail, however, breaks the metaphor, IMHO.

I'm either being reasonable, picky, stubborn, or all of the above, but 
in the discussion we had on IRC, Sidnei seemed to think _SO_new would 
be useful too.

Thoughts?

--
Brad Bollenbach
BBnet.ca

Re: [SQLObject] Overriding new()..._SO_new()?

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

On Friday, October 10, 2003, at 03:18 PM, Brad Bollenbach wrote:
> On Friday, October 10, 2003, at 11:23 AM, Ian Bicking wrote:
>>> Am I missing a way in which this can be done, or should SQLObject 
>>> have an _SO_new?
>>
>> You can override new() just like it is.  (Though number 1 can 
>> probably be handled with a column default)
>
> Then what method do I call to access the "original" new once I've done 
> the overriding? When overriding columns, I'd just use the _SO_'s, but 
> when overriding new, it's unclear (to me, at least.)

_SO_get_colName is an exception, because the SQLObject class doesn't 
implement methods for your columns.  But SQLObject implements new(), so 
you can call SQLObject.new(cls, **kw) (or use super()).

 > Also, I don't see how using a column default will be able to default 
a DateTimeCol
 > to be the time at which the row was created, but in any case, I still 
want to
 > override new for at least the problem of wanting to instantiate a 
Transaction when
 > a Refund/Purchase/ChargeBack/whatever is instantiated.

You can use SQLBuilder.const.NOW(), which will put 'NOW()' into the 
insert, or you can have a default value of DateTime.now -- functions 
and methods will be called when an object is inserted, so if the 
default is DateTime.now then DateTime.now() will be inserted.

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

Re: [SQLObject] Overriding new()..._SO_new()?

[Brad B. <br...@bb...>]

On Friday, October 10, 2003, at 04:18 PM, Brad Bollenbach wrote:

> On Friday, October 10, 2003, at 11:23 AM, Ian Bicking wrote:
>>> Am I missing a way in which this can be done, or should SQLObject 
>>> have an _SO_new?
>>
>> You can override new() just like it is.  (Though number 1 can 
>> probably be handled with a column default)
>
> Then what method do I call to access the "original" new once I've done 
> the overriding? When overriding columns, I'd just use the _SO_'s, but 
> when overriding new, it's unclear (to me, at least.)

Sidnei pointed out that I could do:

super(Transaction, cls).new()

which, as it turns out, Does The Right Thing. But I wonder if those are 
the kind of semantics one would want to use (if "one" is "me", the 
answer is "No" because There Is No Superclass(TM), so it seems like a 
misleading hackaround.)

Just my $0.02 CAD.

--
Brad Bollenbach
BBnet.ca

Re: [SQLObject] Overriding new()..._SO_new()?

[Brad B. <br...@bb...>]

On Friday, October 10, 2003, at 11:23 AM, Ian Bicking wrote:
>> Am I missing a way in which this can be done, or should SQLObject 
>> have an _SO_new?
>
> You can override new() just like it is.  (Though number 1 can probably 
> be handled with a column default)

Then what method do I call to access the "original" new once I've done 
the overriding? When overriding columns, I'd just use the _SO_'s, but 
when overriding new, it's unclear (to me, at least.)

Also, I don't see how using a column default will be able to default a 
DateTimeCol to be the time at which the row was created, but in any 
case, I still want to override new for at least the problem of wanting 
to instantiate a Transaction when a Refund/Purchase/ChargeBack/whatever 
is instantiated.

--
Brad Bollenbach
BBnet.ca

Re: [SQLObject] Overriding new()..._SO_new()?

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

On Friday, October 10, 2003, at 10:12 AM, Brad Bollenbach wrote:
> Hi,
>
> I would have thought it to be pretty common to want to customize what 
> happens when a new SQLObject is created.
>
> In my case, I have to use cases at the moment:
>
> 1. I want the default of a date column in my Transaction object to be 
> the time at which the object was created.
>
> 2. I want my other types of transactions (Purchase, Refund, 
> ChargeBack, ServiceCharge, etc, etc) to be smart enough to accept all 
> the arguments (in their new() method) of both their respective tables, 
> and the transaction table, so that each one is smart enough to 
> transparently add the necessary row to the transaction table.
>
> In both cases, I need to customize the behaviour of creating a new 
> SQLObject.
 >
> Am I missing a way in which this can be done, or should SQLObject have 
> an _SO_new?

You can override new() just like it is.  (Though number 1 can probably 
be handled with a column default)

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

[SQLObject] Overriding new()..._SO_new()?

[Brad B. <br...@bb...>]

Hi,

I would have thought it to be pretty common to want to customize what 
happens when a new SQLObject is created.

In my case, I have to use cases at the moment:

1. I want the default of a date column in my Transaction object to be 
the time at which the object was created.

2. I want my other types of transactions (Purchase, Refund, ChargeBack, 
ServiceCharge, etc, etc) to be smart enough to accept all the arguments 
(in their new() method) of both their respective tables, and the 
transaction table, so that each one is smart enough to transparently 
add the necessary row to the transaction table.

In both cases, I need to customize the behaviour of creating a new 
SQLObject.

Am I missing a way in which this can be done, or should SQLObject have 
an _SO_new?

--
Brad Bollenbach
BBnet.ca

Re: [SQLObject] SQLite threadsafety?

[Luke O. <lu...@me...>]

To clarify my situation:

Mistyped, this is PySQLite 0.4.3.
SQLObject 0.4 and from CVS yesterday both show this problem for me.
Not using transactions, this is with the autocommit conn.commit() in
DBAPI.releaseConnection() only.

It wasn't so much the threadsafety=1 issue, but that combined with the comment:
  # uses only one connection for sqlite - supports multiple
  # cursors per connection
in DBConnection.py , SQLiteConnection.__init__, and the subsequent
makeConnection(self): return self._conn that got me thinking.

Specific errors:

    return self._runWithConnection(self._queryOne, s)
  File "SQLObject\DBConnection.py", line 69, in _runWithConnection
    val = meth(conn, *args)
  File "SQLObject\DBConnection.py", line 139, in _queryOne
    c.execute(s)
  File "C:\Python22\Lib\site-packages\sqlite\main.py", line 237, in execute
    self.con._begin()
  File "C:\Python22\Lib\site-packages\sqlite\main.py", line 508, in _begin
    self.db.execute("BEGIN")
  DatabaseError: cannot start a transaction within a transaction

followed from then on by:

  File "C:\Python22\Lib\site-packages\sqlite\main.py", line 532, in commit
    self.db.execute("COMMIT")
  DatabaseError: cannot commit - no transaction is active

Reading the pysqlite, these happen because of the connection-level
inTransaction flag.

I tried quickly getting around this by simply removing the assumption in
__init__, and in makeConnection returning a new connection each time, but
these same errors keeps happening.

Next, tried to turn on the pysqlite autocommit functionality
(sqlite.connect(self.filename, autocommit=1)), and commented out the
conn.commit() in releaseConnection(), and it now works for me. But not sure if
I'm just masking another problem, and don't have time right now to
generalize/cleanup this hack if it's actually a solution.

- Luke

Re: [SQLObject] SQLite threadsafety?

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

On Wednesday, October 8, 2003, at 03:50 PM, Luke Opperman wrote:
> Hello -
>
> Finally had need to use SQLObject with something besides Postgres, 
> this time
> SQLite (also, MSAccess vi mx.ODBC, but meh).
>
> Quick question though: running into problems with multiple threads 
> (Webware
> servlets) hitting the db at the same time. This is on a single-user 
> install
> hence SQLite, but some of the pages make multiple requests to Webware 
> at once.
>
> Looking through it all, the pysqlite.sf.net webpage and my copy of the 
> code
> (rev1.17) both show a threadsafety of 1, and only supports one 
> concurrent
> cursor/query per connection. The SQLObject DBConnection code though 
> assumes
> better threadsafety, sharing a single connection. Is there a newer 
> version of
> SQLite that I'm just not finding, or is the SQLObject code wrong?

MySQL is also 1, and some Postgres adapters.  SQLObject should work 
with that level.  But...

> (The problem manifests itself because there is just a connection-level 
> flag for
> transactions in pysqlite, shared by all cursors of the connection. So 
> BEGIN
> and COMMIT calls start getting out of sync, failing at the sqlite 
> level.)

Hmm... well, there's a couple possibilities.

One is that SQLite doesn't have good concurrency support.  I should 
look more closely at this, but you may specifically encounter problems 
with using the .select() iterators, which hold a cursor open -- if you 
do another query at the same time weird stuff can happen.  You can use 
list() to force the iterator to flush.

Another possibility is that it's not doing autocommit.  If you aren't 
explicitly using transactions everything should be automatically 
committed, and a connection isn't reused until after the last query on 
that connection was committed.  Only if you are using transactions 
should you require an explicit commit (and even then, when the 
Transaction is disposed of and the connection returned a commit or 
rollback should occur).

Another possibility is you encountered a transaction bug.  Huh... now 
that I think about it someone sent me a bug about that just recently, 
but I think I was busy at that moment and forgot it.  (I don't forget 
SF bugs, but there isn't one) -- anyway, I made a bunch of fixes to 
transactions, but I might have missed something, and your transaction 
might accidentally be getting a connection from the pool when it should 
be using the connection associated with your transaction.

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

[SQLObject] SQLite threadsafety?

[Luke O. <lu...@me...>]

Hello -

Finally had need to use SQLObject with something besides Postgres, this time
SQLite (also, MSAccess vi mx.ODBC, but meh).

Quick question though: running into problems with multiple threads (Webware
servlets) hitting the db at the same time. This is on a single-user install
hence SQLite, but some of the pages make multiple requests to Webware at once.

Looking through it all, the pysqlite.sf.net webpage and my copy of the code
(rev1.17) both show a threadsafety of 1, and only supports one concurrent
cursor/query per connection. The SQLObject DBConnection code though assumes
better threadsafety, sharing a single connection. Is there a newer version of
SQLite that I'm just not finding, or is the SQLObject code wrong?

(The problem manifests itself because there is just a connection-level flag for
transactions in pysqlite, shared by all cursors of the connection. So BEGIN
and COMMIT calls start getting out of sync, failing at the sqlite level.)

- Luke

--
In Pursuit of Counterfactual Histories

Re: [SQLObject] SQLObject CVS

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

On Wednesday, October 8, 2003, at 02:33 PM, Brad Bollenbach wrote:
> 1. Is it relatively stable enough at the moment to use for production 
> apps? I'm doing a major db refactoring of a credit card processing 
> application and figure I might as well take the opportunity to upgrade 
> SQLObject as well.

It should be pretty stable.  There are only some specific, known, 
database-specific bugs that I plan to fix before the next release 
(which should be whenever I get around to fixing those bugs).  There's 
also some open bugs on SF, but they are all really feature requests.

> 2. Is CVS ever accessible? I seem to always get:

SourceForge says they are in the process of upgrading CVS, so it should 
get better Any Day Now.

Anyway, until then use the snapshot (which I try to keep up to date) 
that's listed on the website.

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

Re: [SQLObject] SQLObject CVS

[Marcos D. <md...@gr...>]

On Wed, Oct 08, 2003 at 03:33:50PM -0400, Brad Bollenbach wrote:
> 2. Is CVS ever accessible? I seem to always get:
> 
> bradb@xdev200:~/src/Python/cvs_modules$ cvs 
> -d:pserver:ano...@cv...:/cvsroot/sqlobject login
> Logging in to 
> :pserver:ano...@cv...:2401/cvsroot/sqlobject
> CVS password: [I just hit enter here]
> cvs [login aborted]: recv() from server cvs.sourceforge.net: EOF

    well, sourceforge seems to have problems. I can't get the pages...
always a connection timeout.

[SQLObject] SQLObject CVS

[Brad B. <br...@bb...>]

Hi all,

Two questions about SQLObject in CVS right now:

1. Is it relatively stable enough at the moment to use for production 
apps? I'm doing a major db refactoring of a credit card processing 
application and figure I might as well take the opportunity to upgrade 
SQLObject as well.

2. Is CVS ever accessible? I seem to always get:

bradb@xdev200:~/src/Python/cvs_modules$ cvs 
-d:pserver:ano...@cv...:/cvsroot/sqlobject login
Logging in to 
:pserver:ano...@cv...:2401/cvsroot/sqlobject
CVS password: [I just hit enter here]
cvs [login aborted]: recv() from server cvs.sourceforge.net: EOF

Regards,

--
Brad Bollenbach
BBnet.ca

Re: [SQLObject] Preparing for 0.5

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

On Sun, 2003-10-05 at 07:08, Sidnei da Silva wrote:
> I have Sybase support lying on a branch, but I wont be able to test it
> more throughly nor merge the branch before Oct 15, so that's going to
> have to wait for the next version :)

If it's not polished, I don't really care so long as it doesn't effect
other code.  We'll just document it as experimental.  The same can go
for Oracle.

  Ian

Re: [SQLObject] Preparing for 0.5

[Sidnei da S. <si...@pl...>]

On Sun, Oct 05, 2003 at 03:35:55AM -0500, Ian Bicking wrote:
| I don't think there's much left before 0.5, and there's a lot of
| bugfixes that should be released in a proper version.  In Firebird and
| SQLite there is the string quoting issue, which has to get fixed (along
| with booleans).  Are there any other things that I'm forgetting about?
| 
| Also, someone mentioned PyGreSQL support at some point, but I lost the
| details.  I tried adding it, but it acted weird, and I didn't try very
| hard.  Can anyone test what's in CVS and see what needs to be done? 
| Does anyone care about PoPy?
| 
| Firebird also still has the cursor issue, but I think it's otherwise in
| good shape.

I have Sybase support lying on a branch, but I wont be able to test it
more throughly nor merge the branch before Oct 15, so that's going to
have to wait for the next version :)

-- 
Sidnei da Silva <si...@pl...>
dreamcatching :: making your dreams come true
http://dreamcatcher.homeunix.org

Unix is a Registered Bell of AT&T Trademark Laboratories.
		-- Donn Seeley

Re: [SQLObject] Preparing for 0.5

[Simon W. <cs...@ba...>]

Ian Bicking wrote:
> Also, someone mentioned PyGreSQL support at some point, but I lost the
> details.  I tried adding it, but it acted weird, and I didn't try very
> hard.  Can anyone test what's in CVS and see what needs to be done? 
> Does anyone care about PoPy?

That was me. Here's the code I added to DBConnection.py

try:
     from pyPgSQL import PgSQL
except ImportError:
     PgSQL = None


__all__ = ['MySQLConnection', 'PostgresConnection', 'SQLiteConnection',
            'DBMConnection', 'PyPgSQLConnection']


class PyPgSQLConnection(PostgresConnection):
     def __init__(self, dsn=None, host=None, db=None,
                  user=None, passwd=None, autoCommit=1, **kw):
         assert PgSQL, 'PgSQL module cannot be found'
         if not autoCommit and not kw.has_key('pool'):
             # Pooling doesn't work with transactions...
             kw['pool'] = 0
         self.db = db
         DBAPI.__init__(self, **kw)

     def makeConnection(self):
         return PgSQL.connect(database=self.db)

     def _queryInsertID(self, conn, table, idName, names, values):
         c = conn.cursor()
         q = self._insertSQL(table, names, values)
         if self.debug:
             print 'QueryIns: %s' % q
         c.execute(q)
         c.execute('SELECT %s FROM %s WHERE oid = %s'
                   % (idName, table, c.oidValue))
         return c.fetchone()[0]

My PyPgSQLConnection class simply extends the existing 
PostgresConnection class and redefines the methods that differ for 
PyPgSQL. I haven't tested it formally (it was something of a 15 minutes 
hack) but I've been using it for a few weeks without running in to any 
problems.

Cheers,

Simon Willison
http://simon.incutio.com/

[SQLObject] Preparing for 0.5

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

I don't think there's much left before 0.5, and there's a lot of
bugfixes that should be released in a proper version.  In Firebird and
SQLite there is the string quoting issue, which has to get fixed (along
with booleans).  Are there any other things that I'm forgetting about?

Also, someone mentioned PyGreSQL support at some point, but I lost the
details.  I tried adding it, but it acted weird, and I didn't try very
hard.  Can anyone test what's in CVS and see what needs to be done? 
Does anyone care about PoPy?

Firebird also still has the cursor issue, but I think it's otherwise in
good shape.

  Ian

Re: [SQLObject] caching, threading & zope

[<gs...@sy...>]

On 2003-10-04 22:40:30 +0200 Ian Bicking <ia...@co...> wrote:

hello,

> That's not really true anymore for CVS -- transactions aren't really
> working properly in 0.4.  CVS has an expire method on SQLObject
> instances, which is called when an object is rolled back -- since 0.4
> didn't have this you couldn't use the attribute caching with
> transactions (since it wouldn't be accurate after a rollback).

ok :) - i am currently using the CVS version because i only got a traceback 
when using transactions under 0.4.

btw, there's a typo in the 'transaction' chapter of the documentation 
('_cacheValue' instead of '_cacheValues').

> However, while rollback expires objects in the transaction, commit
> doesn't expire objects outside of the transaction.  But I'm not really
> sure how that should work anyway.

are there any reasons to mix queries which use transactions with queries 
which doesn't in an application?

> Seems like you could go further, and just do:
> 
> Transaction._abort = Transaction.rollback
> Transaction._finish = Transaction.commit
> Transaction._begin = lambda self: None
> 
> Then (if I read your proxy correctly) the transaction can be used
> directly.  Though really that should be done in a subclass of
> Transaction, and then mess with the .transaction() method to use a
> different class.

subclassing from both Transaction and TM isn't that easy/clean because 
method names are overlapping (e.g. commit()). just adding the methods to the 
class doesn't work either, because the transaction has to register itself 
via the _register method first (which calls TM's commit() method). (i have 
attached a new shorter version of the transaction proxy which i am currently 
using.)

> As for passing the transaction, yes, it's a little annoying.  I don't
> know how Zope does this for Z SQL Methods, but the problem is pretty
> much the same.  Does Zope use Acquisition for this, or maybe using one
> transaction per thread?

i guess it's using acquisition, but i haven't looked into the source yet.

cu
/gst


<oodb.py>

Re: [SQLObject] caching, threading & zope

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

On Sat, 2003-10-04 at 15:08, Günther Starnberger wrote:
> hello,
> 
> i just played a little bit around with sqlobject and have a few questions:
> 
> 1) according to the documentation "_cacheValue = False" should be used when 
> using transactions. what is the reason for this (especially when using 
> serialized transactions i can't see any cause for problems when using 
> caching)?

That's not really true anymore for CVS -- transactions aren't really
working properly in 0.4.  CVS has an expire method on SQLObject
instances, which is called when an object is rolled back -- since 0.4
didn't have this you couldn't use the attribute caching with
transactions (since it wouldn't be accurate after a rollback).

However, while rollback expires objects in the transaction, commit
doesn't expire objects outside of the transaction.  But I'm not really
sure how that should work anyway.

> 2) are sqlobject calls (e.g. connection.transaction()) threadsafe or do i 
> need to take care of this?

Yes, these should be threadsafe, though it depends on the exact context
of course -- SQLObject can't address all concurrency issues.  That call
in particular should be threadsafe.  For the most part all SQLObject
calls should be threadsafe, but that doesn't mean your instances will be
threadsafe, depending on how you use them.

I haven't really made use of the DB-API threadsafety variable.  Though
generally SQLObject is assuming a lowest-common-denominator, where
threads cannot concurrently use the same connection (threadsafety level
1).  There is one specified lower level (the whole module is not
threadsafe), but I don't think any supported databases have that
problem.

> 3) btw, i created a small proxy class to bind sqlobject transactions to zope 
> transactions (it's attached to this mail if anyone is interested). to use 
> this class it's required to run zope under python2.2 (that's already the 
> default in debian/unstable).
> 
> usage example:
> def frob(self, REQUEST):
> 	'testmethod'
> 	trans = oodb.new_transaction()
> 	person = oodb.Person6.select(connection = trans)
> 
> (manually passing the transaction to each method is a little bit ugly, but i 
> haven't figured out yet how to do this automatically.)

Seems like you could go further, and just do:

Transaction._abort = Transaction.rollback
Transaction._finish = Transaction.commit
Transaction._begin = lambda self: None

Then (if I read your proxy correctly) the transaction can be used
directly.  Though really that should be done in a subclass of
Transaction, and then mess with the .transaction() method to use a
different class.

As for passing the transaction, yes, it's a little annoying.  I don't
know how Zope does this for Z SQL Methods, but the problem is pretty
much the same.  Does Zope use Acquisition for this, or maybe using one
transaction per thread?

Modeling has a notion of an "editing context" which can make sense. 
Really that's just a different phrasing of the same concept -- but
instead of passing the connection to your class, your class gets passed
to the connection.

  Ian

[SQLObject] caching, threading & zope

[<gs...@sy...>]

hello,

i just played a little bit around with sqlobject and have a few questions:

1) according to the documentation "_cacheValue = False" should be used when 
using transactions. what is the reason for this (especially when using 
serialized transactions i can't see any cause for problems when using 
caching)?

2) are sqlobject calls (e.g. connection.transaction()) threadsafe or do i 
need to take care of this?

3) btw, i created a small proxy class to bind sqlobject transactions to zope 
transactions (it's attached to this mail if anyone is interested). to use 
this class it's required to run zope under python2.2 (that's already the 
default in debian/unstable).

usage example:
def frob(self, REQUEST):
	'testmethod'
	trans = oodb.new_transaction()
	person = oodb.Person6.select(connection = trans)

(manually passing the transaction to each method is a little bit ugly, but i 
haven't figured out yet how to do this automatically.)

cu
/gst

<oodb.py>

Re: [SQLObject] Suggestion: simpler support for %blah% like queries

[Brad B. <br...@bb...>]

On Friday, October 3, 2003, at 02:44 PM, Ian Bicking wrote:

> On Friday, October 3, 2003, at 01:40 PM, Brad Bollenbach wrote:
>>> Ideally I'd like to be able to perform this type of query using 
>>> Python's "in" operator. From reading through SQLBuilder.py this 
>>> seems to be already overloaded to support SQL "something IN 
>>> ('blah1', 'blah2')" operations. Is there any way this could be 
>>> inteligently overloaded to perform like %% queries on strings and IN 
>>> queries on tuples?
>>
>> Python's "in" operator checks for the existence of a value in a 
>> sequence. What you're trying to do is ask for all the objects (or all 
>> the SQLObjects, as it were :) that meet a certain criteria. The 
>> semantics are different.
>
> Not exactly -- SQLBuilder (which you get at with SomeSQLObject.q) 
> captures Python expressions (to the degree possible), which can then 
> be turned into SQL clauses.  In this particular case ("in") I don't 
> think Python makes it possible to do this, but several operators can 
> be overloaded like this.

Perhaps the semantics aren't really completely different, but as you 
say, 1. Python would actually have to allow this (it may or may not, I 
haven't checked), 2. it would have to be written like:

Foo.select('bar' in Foo.q.baz)

instead of the normal:

Foo.select(Foo.q.baz == 'baz')

used for direct comparisons. This would be too inconsistent (and 
perhaps too easy to confuse with writing the other way around), IMHO.

--
Brad Bollenbach
BBnet.ca

Re: [SQLObject] Suggestion: simpler support for %blah% like queries

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

On Friday, October 3, 2003, at 01:40 PM, Brad Bollenbach wrote:
>> Ideally I'd like to be able to perform this type of query using 
>> Python's "in" operator. From reading through SQLBuilder.py this seems 
>> to be already overloaded to support SQL "something IN ('blah1', 
>> 'blah2')" operations. Is there any way this could be inteligently 
>> overloaded to perform like %% queries on strings and IN queries on 
>> tuples?
>
> Python's "in" operator checks for the existence of a value in a 
> sequence. What you're trying to do is ask for all the objects (or all 
> the SQLObjects, as it were :) that meet a certain criteria. The 
> semantics are different.

Not exactly -- SQLBuilder (which you get at with SomeSQLObject.q) 
captures Python expressions (to the degree possible), which can then be 
turned into SQL clauses.  In this particular case ("in") I don't think 
Python makes it possible to do this, but several operators can be 
overloaded like this.

   Ian

Re: [SQLObject] Suggestion: simpler support for %blah% like queries

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

On Friday, October 3, 2003, at 10:36 AM, Simon Willison wrote:
> I just spent some time trying to figure out how to execute the 
> equivalent of a "where col like '%text%'" query using SQLObject. I'm 
> now using the following:
>
> results = Story.select(CONTAINSSTRING(Story.q.body, query))
>
> This is a bit of an eye-sore, especially compared to the neat 
> Story.q.body.startswith('blah') and Story.q.body.endswith('blah') 
> shortcuts.
>
> Ideally I'd like to be able to perform this type of query using 
> Python's "in" operator. From reading through SQLBuilder.py this seems 
> to be already overloaded to support SQL "something IN ('blah1', 
> 'blah2')" operations. Is there any way this could be inteligently 
> overloaded to perform like %% queries on strings and IN queries on 
> tuples?

Hmm... well, there's two string situations, (str in col) and (col in 
str).  (str in col) means "col LIKE '%str%'", and (col in str) means 
"str LIKE '%'||col||'%'".  And then (col in tuple) means "col IN 
(tuple...)".

But I don't think (col in str) can work, because we can't override 
str's __contains__.  Which is why we can't really do that with tuples 
either (and hence the IN() function -- though that function could be 
extended to take strings in addition to tuples.

> Alternatively, a contains() method similar to startswith() and 
> endswith() would be useful.

That seems better.  Since we can't make the "in" operator work for all 
cases, I think it will just be more confusing if it works some places 
and not others.  Easier to just add another method.

   Ian