sqlobject-discuss — All things SQLObject-related

[SQLObject] Going out of town

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

I'll out of town until about the 25th, so my possible non-response to
any questions is likely due to that.  If people have questions, I hope
others here will be able to help -- there's been several of you that
have done that before, and I appreciate that.  

Ta-ta... Ian

Re: [SQLObject] ? Treadsafety, transactions, DB-API ?

[Bud P. B. <bu...@si...>]

On 11 Jun 2003 14:34:29 -0500
Ian Bicking <ia...@co...> wrote:

> If you *don't* use transactions, then you can be sure that each object
> is the only instance of that object (for that id) in the process.  So
> you can put locks on the object itself.

Thanks, Ian, for the clarifications.  Definitely a non-trivial
problem...

So handling single-threading, multi-processing with transactions seems
manageable with some optimistic concurrancy control in the dbms, but
multi-threading with transactions seems a real tough nut to crack...

In the latter case, one probably needs a complete transaction engine
in SQLObject (in addition to that in the dbms).  Different
transactions need to see "their" versions of objects..... or lock them
for writing until the end of the transaction......  difficult and
I'm wondering how easy it is to hide this complexity from application
programmers...

A maybe better approach would be that different threads have isolated
instances of objects (at least conceptually, and then you could have
a copy on write or similar for efficiency), their own cache (a single
instance per object in any given thread).  But that is a complicated
version of multi-processing, what advantage would the threading bring
(particularly in python where an interpreter can run only on a single
processor--if I got that correctly).  

just thinking aload hoping it's of interest to others...

-b


/-----------------------------------------------------------------
| Bud P. Bruegger, Ph.D. 
| Sistema (www.sistema.it)
| Via U. Bassi, 54
| 58100 Grosseto, Italy
| +39-0564-411682 (voice and fax)
\-----------------------------------------------------------------

Re: [SQLObject] Date for next release?

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

On Wed, 2003-06-11 at 23:21, xtian wrote:
> I'm using SQLObject (0.3) in a project at the moment, and I've hit a 
> couple of bugs (which seem to be well-known already, so I haven't 
> reported them, and just used the patches mentioned in the archives etc.)
> 
> I've seen several mentions of the next version, and I was just wondering 
> when it was likely to be released? Should I keep using 0.3, or should I 
> be using CVS?

I'm planning a release Real Soon.  Right now I just want to finish
redoing the documentation, there shouldn't be any other big changes.  So
CVS is a decent basis for work.

  Ian

[SQLObject] Date for next release?

[xtian <xt...@to...>]

I'm using SQLObject (0.3) in a project at the moment, and I've hit a 
couple of bugs (which seem to be well-known already, so I haven't 
reported them, and just used the patches mentioned in the archives etc.)

I've seen several mentions of the next version, and I was just wondering 
when it was likely to be released? Should I keep using 0.3, or should I 
be using CVS?

Thanks,
Christian

Re: [SQLObject] ? Treadsafety, transactions, DB-API ?

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

On Wed, 2003-06-11 at 12:38, Bud P.Bruegger wrote:
> Can someone help me calm my mental storm stemming from a confusion
> and lack of understanding on treadsafety, transactions, and DB-API?
> 
> For SQLObject to be threadsafe, do threads need an isolation of their
> transactions?  It seems so, since one thread will typically want to
> commit while another one is in the middle of its transaction...  

Right now you do transactions something like:

someConn = PostgresConnection(...)
trans = someConn.transaction()

myObj1 = MyObject(connection=trans)
...
trans.commit()

> But then again, if multiple threads modify the same
> objects (in memory), does isolation of transactions really guarantee
> consistency?  It seems that this could only be true if there
> were locks on application objects.  For example,  a multi-threaded app
> that transfers funds between instances of Account would probalby have
> to lock (or version) affected Account instances for the duration of a
> transaction such that no other thread can debit or accredit funds and
> mess up consistency.  Does SQLObject do something of this sort or has
> my reasoning gone astray somewhere?

There's no locking implemented.  We've talked about some ideas... I seem
to remember you being the one that initially brought it up ;)

If you *don't* use transactions, then you can be sure that each object
is the only instance of that object (for that id) in the process.  So
you can put locks on the object itself.  Like:

class Account(SQLObject):

    def _init(self, id):
        SQLObject._init(self, id)
        self.lock = threading.Lock()

acct = Account(10)
acct.lock.acquire()
...
acct.lock.release()

Doesn't work if you have more than one process accessing the database at
a time, and they all need to be locked.

> Also, in my (probably faulty) understanding of the DB-API,
> transactions are properties of connections.  Does that mean that
> different threads need to use different connections?  Does SQLObject
> do that?  

Yes, that's what .transaction() does.

  Ian

[SQLObject] ? Treadsafety, transactions, DB-API ?

[Bud P. B. <bu...@si...>]

Can someone help me calm my mental storm stemming from a confusion
and lack of understanding on treadsafety, transactions, and DB-API?

For SQLObject to be threadsafe, do threads need an isolation of their
transactions?  It seems so, since one thread will typically want to
commit while another one is in the middle of its transaction...  

But then again, if multiple threads modify the same
objects (in memory), does isolation of transactions really guarantee
consistency?  It seems that this could only be true if there
were locks on application objects.  For example,  a multi-threaded app
that transfers funds between instances of Account would probalby have
to lock (or version) affected Account instances for the duration of a
transaction such that no other thread can debit or accredit funds and
mess up consistency.  Does SQLObject do something of this sort or has
my reasoning gone astray somewhere?

Also, in my (probably faulty) understanding of the DB-API,
transactions are properties of connections.  Does that mean that
different threads need to use different connections?  Does SQLObject
do that?  

many thanks for any clarifications

--b


/-----------------------------------------------------------------
| Bud P. Bruegger, Ph.D. 
| Sistema (www.sistema.it)
| Via U. Bassi, 54
| 58100 Grosseto, Italy
| +39-0564-411682 (voice and fax)
\-----------------------------------------------------------------

[SQLObject] Possible bug due to column attribute override?

[Edmund L. <el...@in...>]

I think I've come across a bug, but it could be the way I've overridded 
an attribute.

Here's the class (minus join and table name declarations):

class RequestData(SQLObject):
         _columns = [
         IntCol("requestId", foreignKey="Request", notNone=True),
         IntCol("fieldId", foreignKey="Field", notNone=True),
         StringCol("value", default=None)]

     def _get_value(self):
         val = self._SO_get_value()
         return cvtGet[self.field.dataType](val)

     def _set_value(self, val):
         try:
             val = cvtSet[self.field.dataType](val)
         except:
             raise
         self._SO_set_value(val)


Here's what happens:

  >>> r = RequestData.new(requestId=2006, fieldId=500)
Traceback (most recent call last):
   File "<stdin>", line 1, in ?
   File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 
797, in new
     setattr(inst, name, value)
   File "/home/elian/mefa/Contexts/FDS/Lib/FdsApi.py", line 209, in 
_set_value
     val = cvtSet[self.field.dataType](val)
   File "<string>", line 1, in <lambda>
AttributeError: 'RequestData' object has no attribute '_SO_val_fieldId'


I'm mystified as to why SQLObject is trying to run the _set_value() 
method on fieldId...


...Edmund.

Re: [SQLObject] Passthrough pure SQL?

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

I have an ugly system in place that i think catches all the cases of stale
objects I've used SQLObject for so far, with one caveat. In pseduo-code:

perform SQL
for id in modifiedIDs:
  val = connection.cache.get(class, id)
  connection.cache.purge(class, id)
  if val is not None: # cached value exists!
    # rather than decipher all the SQL that was modified
    newVal = class(id)
    val.__dict__ = newVal.__dict__

The downside to this is that the versions that existed before this operation
are now separated from the cache, and therefore don't act as singleton cached
objects anymore.

The fix would be to have a .reloadFromDB() or similar (.reload() might be
sufficient?) that could reload an object in-place (which just calls
_SO_selectOne()/selectInit?), and then this is easy code. (The only challenge
now is knowing which IDs to reload, or to query the entire store and reload
all... just don't invalidate the cached objects! :)

- Luke


Quoting Edmund Lian <el...@in...>:

> Ian Bicking wrote:
>
> > Yes, the connections have a query(sql) method, with no return value.
> > There's also an attribute conn, which is the actual connection object.
> > So long as you aren't using cacheValues, or you aren't modifying rows
> > that could have instantiated SQLObject instances, it shouldn't be a
> > problem.
>
> This would be tricky... I thought that just invalidating the cache would
> do the trick, but what if the cache contains uncommitted changes?
> Hmmm... in the case of absolutely new objects, this won't be an issue
> but I think monkeying with data that might already be in the cache is
> problematic.
>
> ...Edmund.
>
>
>
> -------------------------------------------------------
> This SF.net email is sponsored by:  Etnus, makers of TotalView, The best
> thread debugger on the planet. Designed with thread debugging features
> you've never dreamed of, try TotalView 6 free at www.etnus.com.
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>


--
 i find your contempt for naked feet curious.

Re: [SQLObject] Passthrough pure SQL?

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

On Mon, 2003-06-09 at 21:05, Edmund Lian wrote:
> Is there anyway to pass pure SQL through to the database? I was thinking 
> that if performance is an issue, then under some circumstances, it would 
> be nice to be able to override selected object methods and use pure SQL 
> to do things, I think.

Yes, the connections have a query(sql) method, with no return value. 
There's also an attribute conn, which is the actual connection object. 
So long as you aren't using cacheValues, or you aren't modifying rows
that could have instantiated SQLObject instances, it shouldn't be a
problem.

> The specific example I'm thinking of is a situation where a table needs 
> to have N rows inserted into it, and the data are from other tables. 
> Right now, I'd have to write a loop to do this, emitting at least N 
> queries. If I could pass SQL through, I could replace the loop with just 
> one SQL statement.
>
> Is this a good idea?

That might be easiest.  It would be reasonable to support this sort of
insertion directly, kind of like .set() updates multiple attributes at
once.  But I'm not sure how to handle the ID generation, assuming you
want a way to get the newly created objects back; maybe it just wouldn't
have a meaningful return, and you'd have to retrieve the new objects via
.select or some other manner.  I don't think that would be too hard to
implement.

  Ian

Re: [SQLObject] Passthrough pure SQL?

[Edmund L. <el...@in...>]

Ian Bicking wrote:

> Yes, the connections have a query(sql) method, with no return value. 
> There's also an attribute conn, which is the actual connection object. 
> So long as you aren't using cacheValues, or you aren't modifying rows
> that could have instantiated SQLObject instances, it shouldn't be a
> problem.

This would be tricky... I thought that just invalidating the cache would 
do the trick, but what if the cache contains uncommitted changes? 
Hmmm... in the case of absolutely new objects, this won't be an issue 
but I think monkeying with data that might already be in the cache is 
problematic.

...Edmund.

[SQLObject] Passthrough pure SQL?

[Edmund L. <el...@in...>]

Is there anyway to pass pure SQL through to the database? I was thinking 
that if performance is an issue, then under some circumstances, it would 
be nice to be able to override selected object methods and use pure SQL 
to do things, I think.

The specific example I'm thinking of is a situation where a table needs 
to have N rows inserted into it, and the data are from other tables. 
Right now, I'd have to write a loop to do this, emitting at least N 
queries. If I could pass SQL through, I could replace the loop with just 
one SQL statement.

Is this a good idea?

...Edmund.

Re: [SQLObject] RelatedJoin bug

[Nick <ni...@dd...>]

Edmund Lian wrote:
> However, I suppose the biggest reason for wanting to keep more than one 
> related join in a table is to allow for easy querying.

It's not uncommon to have 3 (but far less common to have more than that) 
related columns in a single table.  It'll happen in 4th normal form, and 
there *is* somewhat of a convenience factor, depending on what it is you're 
relating.

Nick

Re: [SQLObject] Namespace pollution

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

On Mon, 2003-06-09 at 01:34, Edmund Lian wrote:
> Example:
> 
> class Dog(SQLObject);
>      _columns = [
>          Col("name"),
>          Col("personId", foreignKey="Person")]
>      _joins = [
>          MultipleJoin("Flea"),
>          RelatedJoin("Friend")]

The new style (which is explained in the documentation I haven't quite
finished) is better in this regard:

class Dog(SQLObject):
    name = Col()
    person = ForeignKey('Person')
    fleas = MultipleJoin('Flea')
    friends = RelatedJoin('Friend')

addFriend/removeFriend is still implied.  Perhaps if I make the join
(friends) more powerful it will alleviate even this, so you'd do:

aDog.friends.add(aPerson)

> When I wrote the message, I was thinking of this problem and said to 
> myself: "wouldn't it be nice if I could just instantiate the object and 
> ask it what attributes and methods it had". So I did a obj.__dict__ and 
> found out that I could not. There must be some way to introspect the 
> object to get this info... just haven't figured out how yet.

SQLObject adds these all to the class, so you should be able to look at
obj.__class__.__dict__.

  Ian

Re: [SQLObject] Namespace pollution

[Edmund L. <el...@in...>]

Ian Bicking wrote:

> On Sun, 2003-06-01 at 02:35, Edmund Lian wrote:
> 
>>The way SO does things is quite nice. I do like the use of metaclasses 
>>to add in new methods as a result of joins. But, there needs to be a way 
>>to inspect the objects to see what methods and attributes have been 
>>automagically added, I think.
[ snip ]
> Hmm... well, there are some private variables that hold information,
> like _SO_columnDict, in addition to the public _columns and _joins (poor
> naming aside).  There's nothing very public at this point.  Like with
> most of these things, I don't want to commit to anything until I know
> how it's *really* going to be used -- mostly to design the interface
> more appropriately.

I think the most confusing thing for me right now is keeping track of 
the new methods and attributes that have been added to a class as the 
result of a join.

Example:

class Dog(SQLObject);
     _columns = [
         Col("name"),
         Col("personId", foreignKey="Person")]
     _joins = [
         MultipleJoin("Flea"),
         RelatedJoin("Friend")]

When I look at this class definition some time after I've written it, I 
  have to consciously remember that the class actually has:

     (1) The attributes "name" and "personId"
     (2) The implied attribute "person"
     (3) The implied attribute "fleas"
     (4) The implied attribute "friends"
     (5) The implied method addFriend

I know the information is there in the class definition, but it isn't 
immediately obvious. One has to scan and interpret the definition to 
figure out what's there. It becomes harder when the class definition 
gets longer or more complex.

When I wrote the message, I was thinking of this problem and said to 
myself: "wouldn't it be nice if I could just instantiate the object and 
ask it what attributes and methods it had". So I did a obj.__dict__ and 
found out that I could not. There must be some way to introspect the 
object to get this info... just haven't figured out how yet.

...Edmund.

Re: [SQLObject] RelatedJoin bug

[Edmund L. <el...@in...>]

Luke Opperman wrote:

> Care to elaborate on the design reasons for sticking these both
> in one table instead of two?

Sorry to take so long to get back to you... I had been rummaging around 
looking for an example from a rather wierd organization relationship 
situation I came across a few months back at a government organization. 
After looking at it, I don't think it applies here.

However, I suppose the biggest reason for wanting to keep more than one 
related join in a table is to allow for easy querying.

By keeping all the related join mappings in a single table, it is easier 
to make queries on the relationships themselves. E.g., find all 
relationships where a Person has a simultaneous relationship with (Cat 
and Dog and Bird). This is mostly useful when the relationship or 
characteristics of the relationship are important.

If each pairwise relationship was scattered across separate mapping 
tables, it becomes a bit more cumbersome to make queries on 
relationships. What's more if some attributes only apply when certain 
combinations of relationships are true, then maybe it make sense to 
store them with the mapping table.

Or, maybe the data model really needs to be rewritten... But anyway, it 
is nice to have the option of using perverted data model.  :-)

...Edmund.

Re: [SQLObject] Hook for .new() method?

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

Just with new, like:

def new(cls, **kw):
    obj = super(cls).new(**kw)
    # ... do stuff ...
    return obj
new = classmethod(new)


On Mon, 2003-06-09 at 00:06, Edmund Lian wrote:
> What's the best way to hook some code into the .new()? Basically, I need 
> to run some code after an object is instantiated for the first time. Is 
> this what the _init() method is for?
> 
> ...Edmund.
> 
> 
> 
> -------------------------------------------------------
> This SF.net email is sponsored by:  Etnus, makers of TotalView, The best
> thread debugger on the planet. Designed with thread debugging features
> you've never dreamed of, try TotalView 6 free at www.etnus.com.
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

[SQLObject] Hook for .new() method?

[Edmund L. <el...@in...>]

What's the best way to hook some code into the .new()? Basically, I need 
to run some code after an object is instantiated for the first time. Is 
this what the _init() method is for?

...Edmund.

Re: [SQLObject] alternateID selections

[Edmund L. <el...@in...>]

Ian Bicking wrote:

> No, I think SQLObjectNotFound is right.  .byWhatever() is analogous to
> normal class instantiation, not selection.

Ah, OK, this makes sense, thanks.

...Edmund.

Re: [SQLObject] alternateID selections

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

On Fri, 2003-06-06 at 11:51, Edmund Lian wrote:
> I notice that when alternateID=True, and the associated .byWhatever() 
> selection is performed, a SQLObjectNotFound exception is raised rather 
> than None or [] being returned. However, when a normal .select() method 
> is used, [] is returned.
> 
> Does this seem a bit inconsistent? I'm thinking that with a .select(), 
> one expects a list, so an empty list for no result makes sense. 
> Similarly, with a .byWhatever(), one expects an object, so perhaps None 
> for no result makes more sense than an exception?

No, I think SQLObjectNotFound is right.  .byWhatever() is analogous to
normal class instantiation, not selection.  NULL references do get
resolved to None, but those are explicit references in the database --
the analog of a join with a NULL reference (which doesn't throw an
exception) would be a call like SomeObject.byWhatever(None), which isn't
sensical.

  Ian

[SQLObject] Pydoc doesn't like SQLObject

[Edmund L. <el...@in...>]

Very strange. When I run the pydoc HTTP server via pydoc -p <port 
number>, everything looks fine.

But, when I navigate to SQLObject within ~/site-packages, I can get into 
the SQLObject package, but trying to get into DBConnection, SQLBuilder 
or SQLObject raises an exception. Cache, Col, Constraints, Join, Style, 
and util are fine.

Is anybody else noticing this?

...Edmund.

[SQLObject] alternateID selections

[Edmund L. <el...@in...>]

I notice that when alternateID=True, and the associated .byWhatever() 
selection is performed, a SQLObjectNotFound exception is raised rather 
than None or [] being returned. However, when a normal .select() method 
is used, [] is returned.

Does this seem a bit inconsistent? I'm thinking that with a .select(), 
one expects a list, so an empty list for no result makes sense. 
Similarly, with a .byWhatever(), one expects an object, so perhaps None 
for no result makes more sense than an exception?

...Edmund.

Re: [SQLObject] PyPgSQL support?

[xtian <xt...@to...>]

Thanks Ian, Luke and Edmund -

Yes, I was looking through the pyPgSQL docs, and the notes about things 
differing from the DB-api worried me a bit. I've got the old version of 
psycopg you've mentioned, and it seems to work fine.

Cheers,
Christian

Luke Opperman wrote:
> There is an old version of psycopg compiled for windows at
> http://www.stickpeople.com/projects/python/psycopg/  we use it here for
> similar purposes (build on windows, deploy on linux) with complete success. 
> (Just unpack the zip file and copy psycopg.pyd and libpq.dll to your
> site-packages directory.)
> 
> I know we had trouble using pyPgSQL instead in the past with SQLObject, largely
> I think because pyPgSQL does some odd stuff with creating it's own PgTypes for
> a lot of return values (as I recall, might have been something else...)
> 
> psycopg is much much faster for us, as a bonus.
> 
> - Luke
> 

Re: [SQLObject] PyPgSQL support?

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

There is an old version of psycopg compiled for windows at
http://www.stickpeople.com/projects/python/psycopg/  we use it here for
similar purposes (build on windows, deploy on linux) with complete success. 
(Just unpack the zip file and copy psycopg.pyd and libpq.dll to your
site-packages directory.)

I know we had trouble using pyPgSQL instead in the past with SQLObject, largely
I think because pyPgSQL does some odd stuff with creating it's own PgTypes for
a lot of return values (as I recall, might have been something else...)

psycopg is much much faster for us, as a bonus.

- Luke

Quoting xtian <xt...@to...>:

> Hi -
>
> It looks like the PostGreSQL support for SQLObject expects psycopg - is
> there any particular reason for this? I'm doing development on Windows
> (although we'll eventually be deploying on Linux), and I don't think I
> can go through the compilation process required for psycopg. (Unless
> anyone has some compiled binaries for windows/Python2.2 lying around?)
>
> It doesn't look like it would take much to make PostgresConnection use
> PyPgSQL.PgSQL (which has binaries for windows), although I haven't
> looked closely. Are there any nasty gotchas I should know about?  Have
> braver men tried and failed?
>
> Thanks,
> Christian
>
>
>
> -------------------------------------------------------
> This SF.net email is sponsored by:  Etnus, makers of TotalView, The best
> thread debugger on the planet. Designed with thread debugging features
> you've never dreamed of, try TotalView 6 free at www.etnus.com.
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>


--
 i find your contempt for naked feet curious.

Re: [SQLObject] PyPgSQL support?

[Edmund L. <el...@in...>]

Ian Bicking wrote:

> I chose psycopg because it was there -- I'm not up enough on Postgres to
> know what drivers are best, though psycopg seems to be one of the more
> actively maintained.

I've used it. It is pretty good and is actively maintained. In my 
benchmarks, psycopg was faster. But, pyPgSQL uses a Python style license 
whereas psycopg uses the GPL. May or may not be important.

pyPgSQL does wrap results in a class that allows access to columns via 
attribute style access, but it's not real important if you're layering 
SQLObject on top of it. Support for Python datatypes seems better than 
in psycopg

To get last OID, use cursor.oidValue. It isn't standard across 
PostgreSQL drivers. Oh, one other thing--cursor.rowcount doesn't return 
anything unless a fetch is performed first, unlike psycopg.

...Edmund.

Re: [SQLObject] PyPgSQL support?

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

On Wed, 2003-06-04 at 22:42, xtian wrote:
> It looks like the PostGreSQL support for SQLObject expects psycopg - is 
> there any particular reason for this? I'm doing development on Windows 
> (although we'll eventually be deploying on Linux), and I don't think I 
> can go through the compilation process required for psycopg. (Unless 
> anyone has some compiled binaries for windows/Python2.2 lying around?)
> 
> It doesn't look like it would take much to make PostgresConnection use 
> PyPgSQL.PgSQL (which has binaries for windows), although I haven't 
> looked closely. Are there any nasty gotchas I should know about?  Have 
> braver men tried and failed?

I chose psycopg because it was there -- I'm not up enough on Postgres to
know what drivers are best, though psycopg seems to be one of the more
actively maintained.

I think it should be easy enough to use a different driver.  I don't
know if cursor.lastoid() is standard across the various Postgres
drivers, otherwise I don't believe there's anything else that's not
simple DBAPI.  Try doing

import pypgsql as psycopg

at the top of DBConnection, and maybe run the test:

python tests/test.py -dpostgres

(For the testing environment you'll have to set up a database "test",
like "createdb test")