sqlobject-discuss — All things SQLObject-related

Re: [SQLObject] Looking towards 0.5

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

On 05/22/03 10:18, Luke Opperman wrote:
> To start a separate thread, I'd like to open up discussion for what new things
> people are working on or considering for 0.5 (since 0.4 will be mostly a
> maintenance release, as I count it new features are just improved createSQL
> stuff and changes to Col to allow SQLObject inheritance to work properly.)
> 
> My thoughts for post-0.4:
> 
> * MemoryTransaction or an equivalent.
> 
> * Some decision on concurrency control/resolution (should SQLObject offer any,
> and if so then let's do it.)
> 
> * Actual enforcement of a constraints/validators system, at least providing
> toSQL/toPython functionality. (Ian, I know you've been busy on FormEncode, and
> that will be a help here :)
> 
> That's all I've got for now.

The framework needs a solid test suite. I can see there are some bugs
that have crept into SQLObject that would probably never have been
there in the first place with test-driven development (e.g. dropTable
not dropping sequences for PostgreSQL backends).

I've been insanely busy with work and talks in the past few weeks
(spent six hours on the road last night going to and returning from
giving a talk to the Brandon Unix User group!), am moving to Quebec
City on May 30, and have to prepare for the EuroPython talk late next
month about SQLObject.

But I'm definitely interested in retrofitting a test suite onto the
framework (basically by writing unit tests against the documented API)
and hope that future SQLObject contributions will submit unit tests
with their code.

-- 
Brad Bollenbach
BBnet.ca

[SQLObject] Looking towards 0.5

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

To start a separate thread, I'd like to open up discussion for what new things
people are working on or considering for 0.5 (since 0.4 will be mostly a
maintenance release, as I count it new features are just improved createSQL
stuff and changes to Col to allow SQLObject inheritance to work properly.)

My thoughts for post-0.4:

* MemoryTransaction or an equivalent.

* Some decision on concurrency control/resolution (should SQLObject offer any,
and if so then let's do it.)

* Actual enforcement of a constraints/validators system, at least providing
toSQL/toPython functionality. (Ian, I know you've been busy on FormEncode, and
that will be a help here :)

That's all I've got for now.

- Luke

[SQLObject] Releasing 0.4

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

Ian et al -

Following on Mohan's problems with 0.3, and restarting a thread from a few
weeks ago, what do you have in mind before releasing 0.4. Haven't seen any
major CVS updates since I submitted the transaction implementation, and we're
using CVS+MemoryTransaction+MultiRegistry+odds&ends in a couple apps about to
go live here. So at least for my use cases, CVS has not revealed any bugs in
the last week or two. How about an 0.4b after the holiday weekend?

Here's my list of maintained changes against CVS, just for the record (and if
anyone thinks they should go in for a new release...):

* Multiple classRegistry's, via optional class-level "_registry" string.

* Converting _defaultOrder to dbcolumn via column lookup if it's in
python-style. (Select orderBy expects db-style, but it makes more sense to me
to specify in python-style.)

* Use of _defaultOrder in Joins performJoin(), for now a naive python sort.

* Minor changes in new (setting inst._connection if passed in), and joins
(using inst's _connection) that make logical sense, and allow
MemoryTransaction to work.

* SQLObject.copyToConnection()

* aliasing of destroy to destroySelf for backwards compatibility until destroy
is meaningful..

* A few miscellaneous columns. Most important, BooleanCol. (However, BooleanCol
doesn't actually work properly with Postgres, as Postgres refuses to accept
integers for BOOL fields; it's a long story. Need to implement toSQL/toPython
Column functionality to fix this. But that's probably for 0.5 :)

* MemoryTransaction, although I'd understand if this were left out of this
release. :) Related note: I need to write some docs on MemoryTransaction
still.


And that's all I've got.

- Luke

[SQLObject] Thoughts on how to get from values to objects

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

Been thinking about how to marshal from a set of values stored in the
DB to an object and want to share what I have come up with.

The following has two sections:  my misunderstanding of what SQLObject
currently does (Ian, be patient with me) and a possible alternative
approach.


1. SQLObject
------------

It seems to me that database columns translate to properties in
SQLObject and that the setter method of the property also triggers an
UPDATE query  (while the insert query is handled by the new method,
not attribute access).

Important is probably that there are no explicit database methods such
as insert (store, save) and update. This is done implicitly while
using the object (i.e., attribute access).

When SQLObject retrieves an object from the db, it first gets a list
of column values and then calls the objects class (eg., p=Person()) to
create an object instance and then sets all the values.  [I derive
this from the statement from the manual that "__init__ is called
everytime an object is just fetched from the cache"].  

What in plain python classes normally goes in __init__ should go in
the _init method for SQLObject subclasses.  _init seems to be called
by SQLObject's new method.  


2. Possible Alternative Approach
--------------------------------

A possible alternative approach makes a major change in the look and
feel inassuch that persistence methods (insert, update, ..) need to be
called explicitly.  

My current intuition is that this is a good choice although I haven't
cristallized the reasons out very clearly.  The following matters for
my intuition:

* it allows the use of temporary object (without ever storing them in
  the db

* if we use transactions, we need to deal with storage issues anyhow

* same goes for resolution of collisions in concurrancy control.  I
  don't see a way of hiding these issues from the application
  programmer

So in this case, column values could be stored in normal attributes
(in the __dict__) without the need for properties that overload the
setter method to update the db.  

To retrieve an object from the db, a first step retrieves the column
values, then creates an empty object using <class>.__new__(<class>)
(thus avoiding __init__) and then assign the (unmarshelled) values to
the __dict__ (or better through use of normal setattr).  

The advantages of this seem to be that objects behave much more like
normal python objects including:

  * __init__ is just normal

  * I believe (haven't verified) that it should be possible for users
    to define properties in lieu of just normal __dict__ items without
    any adverse effects.  (I reason that the use of getattr and
    setattr by the middleware works equally well)


I hope this was of interest to someone (has anyone gotten that far
reading) and am hoping for comments and being straightened out if I
got it wrong...

cheers
--b

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

[SQLObject] off topic: code coverage tool!

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

This may be of interest to some of you.  I use unittest continuously
while writing software and today, I added the use of Gareth Rees;
coverage.py (http://www.garethrees.org/2001/12/04/python-coverage/)
that annotates my code to show what hasn't been covered by the test.

REALLY COOL!

And I may have found a real problem with its help already!

enjoy

--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] destroy() throwing an exception : AttributeError: 'CacheSet' object has no attribute 'purge'

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

Bob, yes you identified reasonable fixes. There are other known problems with
the Cache from 0.3, be warned...

The short answer is that 0.3 is horribly out of date, it's time to get cracking
on another public release. CVS is relatively stable right now, if you aren't
putting this immediately into production.

- Luke

RE: [SQLObject] destroy() throwing an exception : AttributeError: 'CacheSet' object has no attribute 'purge'

[Bob T. <bo...@ca...>]

I ran into this problem also. I believe the fix is to make the following
two changes:

In SQLObject.py, change this line in the destroy method of SQLObject:

self._connection.cache.purge(self,self.id)

To:

self._connection.cache.purge(self.__class__,self.id)

In the Cache.py file, add this method to the CacheSet class:

     def purge(self, cls, id):
         try:
             self.caches[cls.__name__].purge(id)
         except KeyError:
             pass

Perhaps Ian might verify that this is the correct fix.

This is not related to the bug you ran into, but I also found I had to
add this function to Constraints.py:

def isFloat(obj, col, value):
    if type(value) is not (type(1.1)):
        raise BadValue("only allows floats", obj, col, value)

Regards,
Bob

-----Original Message-----
From: Mohanaraj [mailto:sar...@am...]=20
Sent: Wednesday, May 21, 2003 5:26 PM
To: sql...@li...
Subject: [SQLObject] destroy() throwing an exception : AttributeError:
'CacheSet' object has no attribute 'purge'


Dear all,

I am using python2.2, sqlobject0.3 and sqlite. When I try to destroy an
object=20
I get the following exception. I have tried commenting line 831 in the
code=20
and it seems to work after that. I have not tried this with any other db

other then sqlite. Any feedback would be graciously appreciated.

Thanks mates.


>>> food =3D Food.new(name=3D'Dodo')
>>> food.destroy()
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
  File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line
831, in=20
destroy
    self._connection.cache.purge(self.id)
AttributeError: 'CacheSet' object has no attribute 'purge'
>>>


-------------------------------------------------------
This SF.net email is sponsored by: ObjectStore.
If flattening out C++ or Java code to make your application fit in a
relational database is painful, don't do it! Check out ObjectStore. Now
part of Progress Software. http://www.objectstore.net/sourceforge
_______________________________________________
sqlobject-discuss mailing list sql...@li...
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

[SQLObject] destroy() throwing an exception : AttributeError: 'CacheSet' object has no attribute 'purge'

[Mohanaraj <sar...@am...>]

Dear all,

I am using python2.2, sqlobject0.3 and sqlite. When I try to destroy an object 
I get the following exception. I have tried commenting line 831 in the code 
and it seems to work after that. I have not tried this with any other db 
other then sqlite. Any feedback would be graciously appreciated.

Thanks mates.


>>> food = Food.new(name='Dodo')
>>> food.destroy()
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
  File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 831, in 
destroy
    self._connection.cache.purge(self.id)
AttributeError: 'CacheSet' object has no attribute 'purge'
>>>

Re: [SQLObject] collision detection strategy

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

On 21 May 2003 01:20:02 +0100
Matt Goodall <ma...@po...> wrote:

> Unless the check is done during the update then there is no way to
> guarantee that the optimistic lock is honoured. Unless, that is, the
> object's row is locked using "select ... for update" (or something
> similar) just before the update.

It seems that in the case of a single process application (possibly
multi-threaded), the locking could move from the dbms to the
middle-layer.  But this is not valid for all cases and to me it seems
that (optimistic) locking in the dbms is easier and more general...

At least in web apps, select .. for update seems to be problematic
since a client may die away after locking without ever committing a
change....  So there must be timeouts, administration, etc.  Ugh...

Also, if one would select for update just before committing a change,
the client app could still die in the meantime (even if it's shorter)
and at that point one basically has nothing more than optimistic
locking...

So it seems (as you have said for web apps) that optimistic locking is
the only way..

--b

Re: [SQLObject] collision detection strategy

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

> Can you explain what you mean by "take out a lock"?

I suppose the correct term would be acquire a lock.
This idea comes from the diagram for optimistic locking at
http://www.agiledata.org/essays/concurrencyControl.html#OptimisticLocking.

I also looked into the Postgres documentation and it states:

<quote
src=http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=explicit-locking.html#LOCKING-ROWS>

 In addition to table-level locks, there are row-level locks. A
 row-level lock on a specific row is automatically acquired when the
 row is updated (or deleted or marked for update). The lock is held
 until the transaction commits or rolls back. Row-level locks don't
 affect data querying; they block writers to the same row only. To
 acquire a row-level lock on a row without actually modifying the row,
 select the row with SELECT FOR UPDATE . Note that once a particular
 row-level lock is acquired, the transaction may update the row
 multiple times without fear of conflicts.

</quote>

So I guess that at least in Postgres, row locks are acquired
automatically...

I see little probability for problems when updating a single object
(table row) since it is a single (atomic??) SQL command.  Trouble is
more likely if in a transaction, several related (as in 1:many or
many:many relationships) objects need to be updated in a transaction.
It seems Postgres takes care of this too since it locks for the
duration of the whole transaction...  Hmmmm...  but it locks the
row...  So if two transactions try to modify a set of objects at the
same time, both may lock rows but deadlock each other???

Hmm...  I'm not good at this kind of stuff...seems difficult...

--b

Re: [SQLObject] collision detection strategy

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

On Tue, 2003-05-20 at 19:20, Matt Goodall wrote:
> > For instance, with the timestamp, what is the object's starting point? 
> > Let's say you do:
> > 
> > obj = Something(someID) # op A
> > x = obj.a               # op B
> > y = obj.a               # op C
> > obj.a = x + 10          # op D
> > 
> > What is the concurrency expectation?  I.e., when is does optimistic
> > locking fail?  All of A, B, and C are potential times when the timestamp
> > could be fetched from the database.
> 
> I may be missing something obvious but why would reading the timestamp
> ever make an opt lock fail? The only time it matters is when the
> database is updated isn't it?

It's a question of: supposing someone changes the row between A and D,
should it fail?  B and D?  C and D?  It could be implemented any of
those ways (well, between B and D would require more code than I show
there), or in an entirely different way.  Using optimistic locking, it
kind of boils down to when the timestamp was last retrieved from the
database.

As for what you want, any of those options is a valid programming
decision.  Maybe obj.a is dependent on other attributes, or other
objects entirely, so A and D is right.  Maybe you just want to make sure
you've incremented it by 10, so B and D is right.  Maybe you do your own
consistency checking of x and y, so C to D is all that's necessary. 
Some sort of annotation is necessary.

> > So if we have to be explicit about locking, how do we express that?  How
> > do you say what you are locking?  Columns, rows, tables?  All are valid
> > options, and none particularly more difficult than the others.
> 
> Agh! Not table locking, please! The MySQL manual is the only place that
> sort of talk is acceptable ;-).
> 
> I would imagine optimistic locking would take place at the object level
> which generally means row level in the database.

Given a locking interface, it shouldn't be hard to implement other
locking (no one will force you to use it :).  Locking on timestamps or
other IDs is the most difficult, so if we do that there's no reason we
can't do the other locking too.

  Ian

Re: [SQLObject] collision detection strategy

[Matt G. <ma...@po...>]

On Wed, 2003-05-21 at 00:41, Ian Bicking wrote:
> On Tue, 2003-05-20 at 17:57, Luke Opperman wrote:
> > The other thought I'm having right now is that SQLObject seems to simplify some
> > of this if you're using cached objects, because now there is only one possible
> > object, so it's all on the python side to deal with locking as the developer
> > sees fit. but I haven't entirely thought this through.
> 
> Yes, in the case where there's one multi-threaded process that's in
> total control of the database, SQLObject makes it possible to do safe
> locking on the Python side.  Programming in Webware (i.e., a
> multi-threaded application server), this covers a large portion of my
> usage.
> 
> I had some various locks in there and thought of more, but now I feel
> like the programmer needs to be explicit about their locking, because
> there's no automated way to do it.

Unless the check is done during the update then there is no way to
guarantee that the optimistic lock is honoured. Unless, that is, the
object's row is locked using "select ... for update" (or something
similar) just before the update.

Of course, having a locking attribute with a small chance of error is
better than no locking at all.

> 
> For instance, with the timestamp, what is the object's starting point? 
> Let's say you do:
> 
> obj = Something(someID) # op A
> x = obj.a               # op B
> y = obj.a               # op C
> obj.a = x + 10          # op D
> 
> What is the concurrency expectation?  I.e., when is does optimistic
> locking fail?  All of A, B, and C are potential times when the timestamp
> could be fetched from the database.

I may be missing something obvious but why would reading the timestamp
ever make an opt lock fail? The only time it matters is when the
database is updated isn't it?

>   With caching and multithreading,
> it's even more complicated (though I think if you want to do this
> locking you'll have to either not use threading, or use per-thread
> instances).
> 
> So if we have to be explicit about locking, how do we express that?  How
> do you say what you are locking?  Columns, rows, tables?  All are valid
> options, and none particularly more difficult than the others.

Agh! Not table locking, please! The MySQL manual is the only place that
sort of talk is acceptable ;-).

I would imagine optimistic locking would take place at the object level
which generally means row level in the database.

- Matt

Re: [SQLObject] collision detection strategy

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

On Tue, 2003-05-20 at 17:57, Luke Opperman wrote:
> The other thought I'm having right now is that SQLObject seems to simplify some
> of this if you're using cached objects, because now there is only one possible
> object, so it's all on the python side to deal with locking as the developer
> sees fit. but I haven't entirely thought this through.

Yes, in the case where there's one multi-threaded process that's in
total control of the database, SQLObject makes it possible to do safe
locking on the Python side.  Programming in Webware (i.e., a
multi-threaded application server), this covers a large portion of my
usage.

I had some various locks in there and thought of more, but now I feel
like the programmer needs to be explicit about their locking, because
there's no automated way to do it.

For instance, with the timestamp, what is the object's starting point? 
Let's say you do:

obj = Something(someID) # op A
x = obj.a               # op B
y = obj.a               # op C
obj.a = x + 10          # op D

What is the concurrency expectation?  I.e., when is does optimistic
locking fail?  All of A, B, and C are potential times when the timestamp
could be fetched from the database.  With caching and multithreading,
it's even more complicated (though I think if you want to do this
locking you'll have to either not use threading, or use per-thread
instances).

So if we have to be explicit about locking, how do we express that?  How
do you say what you are locking?  Columns, rows, tables?  All are valid
options, and none particularly more difficult than the others.

  Ian

Re: [SQLObject] collision detection strategy

[Matt G. <ma...@po...>]

On Wed, 2003-05-21 at 00:22, Bud P. Bruegger wrote:
> Thanks for the feedback!
> 
> Actually, in my ongoing implementation, I have used a GUID generator that I
> found in the Python Cookbook.  It uses time (up to the second???) but
> also mixes in the IP of the machine and a random number.  That way,
> problems with lack of time synch across machines, second-only time,
> and similar can be avoided. 

Sounds like a reasonable solution.

You'd have to be really unlucky to get problems now - 2 users, both
using a web site from behind the same masquerading firewall and are both
given the same random number ;-).

>  
> 
> Do you see any need to take out a lock for the update query?  I suppse
> it is highly unlikely (in my app, at least), that two updates on the
> same row happen at the same time...

Can you explain what you mean by "take out a lock"?

> 
> --b
> 
> On 21 May 2003 00:06:58 +0100
> Matt Goodall <ma...@po...> wrote:
> 
> > I've used this technique (optimistic locking) before and it works very
> > well. It's the only way for web work.
> > 
> > The only thing I would say is that a timestamp is not the ideal
> > indicator of change. It might be unlikely, but it is possible for a
> > database record to be read and updated without the timestamp's value
> > changing. In fact, some database timestamps (Oracle 8i, IIRC) only have
> > a 1 second accuracy which increases the chance of missed collisions
> > considerably.
> > 
> > An incrementing integer version number is possibly a better choice. The
> > only problem with this is when the number gets to big for the column -
> > PostgreSQL, for instance, considers it an error.
> > 
> > Cheers, Matt
> > 
> > On Tue, 2003-05-20 at 23:29, Bud P. Bruegger wrote:
> > > Had an idea for collision detection (see
> > > http://www.agiledata.org/essays/concurrencyControl.html) and it seems
> > > quite a bit simpler to what is described in the above essay.  Can
> > > someone comment on whether the following is sound?
> > > 
> > > * The shadow data of an object includes a timestamp (or better GUID)
> > > 
> > > * every INSERT and UPDATE query changes this timestamp.
> > > 
> > > * The (in memory) python object keeps the timestamp information from
> > >   the last select, insert, or update query.
> > > 
> > > * When the state of an object is written to the dbms (UPDATE query),
> > >   collision detection is necessary to make sure noone else modified
> > >   the object since...
> > > 
> > > * this can be done by the following query:
> > >   UPDATE table SET 
> > >      col1=val1, 
> > >      ..., 
> > >      colN = valN, 
> > >      timeStamp = <newTimeStamp> 
> > >   WHERE id=<id> 
> > >   AND   timeStamp = <oldTimeStamp>
> > > 
> > > This works only if none else has modified the record (since otherwise,
> > > timeStamp is not equal <oldTimeStamp>).
> > > 
> > > With the DB-API, it seems the way to determine whether the update was
> > > successful or a collision prevented the database from being changed is
> > > to look at the rowcount attribute of the cursor:  1 means success, 0
> > > means collision.  
> > > 
> > > I checked this with psycopg and it seems to work.  
> > > 
> > > I suppose it would also be sound to wrap this in a transaction...
> > > 
> > > Does anyone see any problems with this approach?
> > > 
> > > many thanks
> > > --b
> > > 
> > >      
> > > 
> > > /-----------------------------------------------------------------
> > > | Bud P. Bruegger, Ph.D. 
> > > | Sistema (www.sistema.it)
> > > | Via U. Bassi, 54
> > > | 58100 Grosseto, Italy
> > > | +39-0564-411682 (voice and fax)
> > > \-----------------------------------------------------------------
> > > 
> > > 
> > > -------------------------------------------------------
> > > This SF.net email is sponsored by: ObjectStore.
> > > If flattening out C++ or Java code to make your application fit in a
> > > relational database is painful, don't do it! Check out ObjectStore.
> > > Now part of Progress Software. http://www.objectstore.net/sourceforge
> > > _______________________________________________
> > > sqlobject-discuss mailing list
> > > sql...@li...
> > > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
> > 
> > 
> > 
> > -------------------------------------------------------
> > This SF.net email is sponsored by: ObjectStore.
> > If flattening out C++ or Java code to make your application fit in a
> > relational database is painful, don't do it! Check out ObjectStore.
> > Now part of Progress Software. http://www.objectstore.net/sourceforge
> > _______________________________________________
> > sqlobject-discuss mailing list
> > sql...@li...
> > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
> > 
> 
> 
> /-----------------------------------------------------------------
> | Bud P. Bruegger, Ph.D. 
> | Sistema (www.sistema.it)
> | Via U. Bassi, 54
> | 58100 Grosseto, Italy
> | +39-0564-411682 (voice and fax)
> \-----------------------------------------------------------------

Re: [SQLObject] collision detection strategy

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

Thanks, Luke.

and here some reactions from an equally fried bains..

On Tue, 20 May 2003 17:57:52 -0500
Luke Opperman <lu...@me...> wrote:

> Bud -
> 
> Your example appears to me to be optimistic row-level locking, straight
> forward. At first glance it seems like a reasonable implementation, although
> the big question with optimistic/detection schemes is how do you recover? I
> generally think this needs to be at the programmer's discretion, both for
> recovery method (typically overwrite or exception in my experience) and for
> scope (for X transaction, is attribute-level reasonable?)

Definitely a difficult question!  I will first opt for simple
exception...

> The other thought I'm having right now is that SQLObject seems to simplify
> some of this if you're using cached objects, because now there is only one
> possible object, so it's all on the python side to deal with locking as the
> developer sees fit. but I haven't entirely thought this through.

This is true if there is a single cache, ie. a single interpreter.
But I'll likely run in a multi-process and possibly multi-machine
environment...  Keeping a cache coherent in a distributed setting is
probably a non-trivial task..

One thing I've thought of is to funnel all write activity into a
single process...  Not sure how difficult this is..

But I think I'll keep it simple for starters..

> Also, transactions or otherwise, it would seem that with cached objects it
> would be possible to support setting and managing what level of locking is
> appropriate on a class(table) or instance(row) or attribute level basis.

I have looked at the concurrancy control doc of PostgreSQL and am
quite impressed.  If I wasn't too tired to understand, they decide on
the "separation level" per transaction (not per table or instance...).  


cheers
-b

Re: [SQLObject] collision detection strategy

[Matt G. <ma...@po...>]

On Tue, 2003-05-20 at 23:57, Luke Opperman wrote:
> Bud -

<snip>

> The other thought I'm having right now is that SQLObject seems to simplify some
> of this if you're using cached objects, because now there is only one possible
> object, so it's all on the python side to deal with locking as the developer
> sees fit. but I haven't entirely thought this through.

This assumes that **all** updates are going through SQLObject in a
single process. The lock check should definitely happen in the database
- it's the only part of the system that truly knows.

Obviously, if you've got another process bypassing SQLObject's caching
then you've got other problems anyway ;-).

> 
> Also, transactions or otherwise, it would seem that with cached objects it
> would be possible to support setting and managing what level of locking is
> appropriate on a class(table) or instance(row) or attribute level basis.
> 
> My brain is already fried, so i won't try to think this out anymore.
> 
> - Luke
> 
> 
> 
> -------------------------------------------------------
> This SF.net email is sponsored by: ObjectStore.
> If flattening out C++ or Java code to make your application fit in a
> relational database is painful, don't do it! Check out ObjectStore.
> Now part of Progress Software. http://www.objectstore.net/sourceforge
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

Re: [SQLObject] collision detection strategy

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

Thanks for the feedback!

Actually, in my ongoing implementation, I have used a GUID generator that I
found in the Python Cookbook.  It uses time (up to the second???) but
also mixes in the IP of the machine and a random number.  That way,
problems with lack of time synch across machines, second-only time,
and similar can be avoided.  

Do you see any need to take out a lock for the update query?  I suppse
it is highly unlikely (in my app, at least), that two updates on the
same row happen at the same time...

--b

On 21 May 2003 00:06:58 +0100
Matt Goodall <ma...@po...> wrote:

> I've used this technique (optimistic locking) before and it works very
> well. It's the only way for web work.
> 
> The only thing I would say is that a timestamp is not the ideal
> indicator of change. It might be unlikely, but it is possible for a
> database record to be read and updated without the timestamp's value
> changing. In fact, some database timestamps (Oracle 8i, IIRC) only have
> a 1 second accuracy which increases the chance of missed collisions
> considerably.
> 
> An incrementing integer version number is possibly a better choice. The
> only problem with this is when the number gets to big for the column -
> PostgreSQL, for instance, considers it an error.
> 
> Cheers, Matt
> 
> On Tue, 2003-05-20 at 23:29, Bud P. Bruegger wrote:
> > Had an idea for collision detection (see
> > http://www.agiledata.org/essays/concurrencyControl.html) and it seems
> > quite a bit simpler to what is described in the above essay.  Can
> > someone comment on whether the following is sound?
> > 
> > * The shadow data of an object includes a timestamp (or better GUID)
> > 
> > * every INSERT and UPDATE query changes this timestamp.
> > 
> > * The (in memory) python object keeps the timestamp information from
> >   the last select, insert, or update query.
> > 
> > * When the state of an object is written to the dbms (UPDATE query),
> >   collision detection is necessary to make sure noone else modified
> >   the object since...
> > 
> > * this can be done by the following query:
> >   UPDATE table SET 
> >      col1=val1, 
> >      ..., 
> >      colN = valN, 
> >      timeStamp = <newTimeStamp> 
> >   WHERE id=<id> 
> >   AND   timeStamp = <oldTimeStamp>
> > 
> > This works only if none else has modified the record (since otherwise,
> > timeStamp is not equal <oldTimeStamp>).
> > 
> > With the DB-API, it seems the way to determine whether the update was
> > successful or a collision prevented the database from being changed is
> > to look at the rowcount attribute of the cursor:  1 means success, 0
> > means collision.  
> > 
> > I checked this with psycopg and it seems to work.  
> > 
> > I suppose it would also be sound to wrap this in a transaction...
> > 
> > Does anyone see any problems with this approach?
> > 
> > many thanks
> > --b
> > 
> >      
> > 
> > /-----------------------------------------------------------------
> > | Bud P. Bruegger, Ph.D. 
> > | Sistema (www.sistema.it)
> > | Via U. Bassi, 54
> > | 58100 Grosseto, Italy
> > | +39-0564-411682 (voice and fax)
> > \-----------------------------------------------------------------
> > 
> > 
> > -------------------------------------------------------
> > This SF.net email is sponsored by: ObjectStore.
> > If flattening out C++ or Java code to make your application fit in a
> > relational database is painful, don't do it! Check out ObjectStore.
> > Now part of Progress Software. http://www.objectstore.net/sourceforge
> > _______________________________________________
> > sqlobject-discuss mailing list
> > sql...@li...
> > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
> 
> 
> 
> -------------------------------------------------------
> This SF.net email is sponsored by: ObjectStore.
> If flattening out C++ or Java code to make your application fit in a
> relational database is painful, don't do it! Check out ObjectStore.
> Now part of Progress Software. http://www.objectstore.net/sourceforge
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
> 


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

Re: [SQLObject] collision detection strategy

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

Bud -

Your example appears to me to be optimistic row-level locking, straight
forward. At first glance it seems like a reasonable implementation, although
the big question with optimistic/detection schemes is how do you recover? I
generally think this needs to be at the programmer's discretion, both for
recovery method (typically overwrite or exception in my experience) and for
scope (for X transaction, is attribute-level reasonable?)

The other thought I'm having right now is that SQLObject seems to simplify some
of this if you're using cached objects, because now there is only one possible
object, so it's all on the python side to deal with locking as the developer
sees fit. but I haven't entirely thought this through.

Also, transactions or otherwise, it would seem that with cached objects it
would be possible to support setting and managing what level of locking is
appropriate on a class(table) or instance(row) or attribute level basis.

My brain is already fried, so i won't try to think this out anymore.

- Luke

Re: [SQLObject] collision detection strategy

[Matt G. <ma...@po...>]

I've used this technique (optimistic locking) before and it works very
well. It's the only way for web work.

The only thing I would say is that a timestamp is not the ideal
indicator of change. It might be unlikely, but it is possible for a
database record to be read and updated without the timestamp's value
changing. In fact, some database timestamps (Oracle 8i, IIRC) only have
a 1 second accuracy which increases the chance of missed collisions
considerably.

An incrementing integer version number is possibly a better choice. The
only problem with this is when the number gets to big for the column -
PostgreSQL, for instance, considers it an error.

Cheers, Matt

On Tue, 2003-05-20 at 23:29, Bud P. Bruegger wrote:
> Had an idea for collision detection (see
> http://www.agiledata.org/essays/concurrencyControl.html) and it seems
> quite a bit simpler to what is described in the above essay.  Can
> someone comment on whether the following is sound?
> 
> * The shadow data of an object includes a timestamp (or better GUID)
> 
> * every INSERT and UPDATE query changes this timestamp.
> 
> * The (in memory) python object keeps the timestamp information from
>   the last select, insert, or update query.
> 
> * When the state of an object is written to the dbms (UPDATE query),
>   collision detection is necessary to make sure noone else modified
>   the object since...
> 
> * this can be done by the following query:
>   UPDATE table SET 
>      col1=val1, 
>      ..., 
>      colN = valN, 
>      timeStamp = <newTimeStamp> 
>   WHERE id=<id> 
>   AND   timeStamp = <oldTimeStamp>
> 
> This works only if none else has modified the record (since otherwise,
> timeStamp is not equal <oldTimeStamp>).
> 
> With the DB-API, it seems the way to determine whether the update was
> successful or a collision prevented the database from being changed is
> to look at the rowcount attribute of the cursor:  1 means success, 0
> means collision.  
> 
> I checked this with psycopg and it seems to work.  
> 
> I suppose it would also be sound to wrap this in a transaction...
> 
> Does anyone see any problems with this approach?
> 
> many thanks
> --b
> 
>      
> 
> /-----------------------------------------------------------------
> | Bud P. Bruegger, Ph.D. 
> | Sistema (www.sistema.it)
> | Via U. Bassi, 54
> | 58100 Grosseto, Italy
> | +39-0564-411682 (voice and fax)
> \-----------------------------------------------------------------
> 
> 
> -------------------------------------------------------
> This SF.net email is sponsored by: ObjectStore.
> If flattening out C++ or Java code to make your application fit in a
> relational database is painful, don't do it! Check out ObjectStore.
> Now part of Progress Software. http://www.objectstore.net/sourceforge
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

[SQLObject] collision detection strategy

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

Had an idea for collision detection (see
http://www.agiledata.org/essays/concurrencyControl.html) and it seems
quite a bit simpler to what is described in the above essay.  Can
someone comment on whether the following is sound?

* The shadow data of an object includes a timestamp (or better GUID)

* every INSERT and UPDATE query changes this timestamp.

* The (in memory) python object keeps the timestamp information from
  the last select, insert, or update query.

* When the state of an object is written to the dbms (UPDATE query),
  collision detection is necessary to make sure noone else modified
  the object since...

* this can be done by the following query:
  UPDATE table SET 
     col1=val1, 
     ..., 
     colN = valN, 
     timeStamp = <newTimeStamp> 
  WHERE id=<id> 
  AND   timeStamp = <oldTimeStamp>

This works only if none else has modified the record (since otherwise,
timeStamp is not equal <oldTimeStamp>).

With the DB-API, it seems the way to determine whether the update was
successful or a collision prevented the database from being changed is
to look at the rowcount attribute of the cursor:  1 means success, 0
means collision.  

I checked this with psycopg and it seems to work.  

I suppose it would also be sound to wrap this in a transaction...

Does anyone see any problems with this approach?

many thanks
--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] another question on list special methods..

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

On Tue, 2003-05-20 at 11:12, Bud P.Bruegger wrote:
> Sorry, I wasn't clear.  I need something for the iteration ('in' and
> 'for').  I have since read up a litte and it seems I need __iter__
> (see http://www.python.org/doc/current/ref/sequence-types.html).

Yes.  You might look at SQLObject.SelectResults.__inter__ (which is
dull), or DBConnection.DBAPI.iterSelect (which is a generator, and
actually implements SelectResults iteration).

__iter__ can also return an object with a next() method.  In comparison
the old __getitem__ technique is a pain.

  Ian

Re: [SQLObject] another question on list special methods..

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

"for .. in" uses __getitem__, which must raise IndexError to stop the
iteration.

Nick

On Tue, 2003-05-20 at 11:12, Bud P.Bruegger wrote:
> Sorry, I wasn't clear.  I need something for the iteration ('in' and
> 'for').  I have since read up a litte and it seems I need __iter__
> (see http://www.python.org/doc/current/ref/sequence-types.html).
> 
> What kind of surprises me is that the built in lists (as in type([]))
> don't have an __iter__ method but are a prime example for supporting
> the "in" loop...  Maybe a leftover of older python versions...
> 
> --b
> 
> On 20 May 2003 10:43:58 -0500
> Nick <ni...@dd...> wrote:
> 
> > I assumed wrong.  Looks like __getitem__ is what you want.
> > 
> > Nick
> > 
> > On Tue, 2003-05-20 at 07:04, Bud P.Bruegger wrote:
> > > and as a followup:
> > > 
> > > Of particular magic to me is what happends here:
> > > 
> > > for item in myList:
> > >    print item
> > > 
> > > I frankly haven't a clue what secial method to override to get control
> > > over what is returned as items...
> > > 
> > > any idea?
> > > 
> > > /-----------------------------------------------------------------
> > > | Bud P. Bruegger, Ph.D. 
> > > | Sistema (www.sistema.it)
> > > | Via U. Bassi, 54
> > > | 58100 Grosseto, Italy
> > > | +39-0564-411682 (voice and fax)
> > > \-----------------------------------------------------------------
> > > 
> > > 
> > > -------------------------------------------------------
> > > This SF.net email is sponsored by: ObjectStore.
> > > If flattening out C++ or Java code to make your application fit in a
> > > relational database is painful, don't do it! Check out ObjectStore.
> > > Now part of Progress Software. http://www.objectstore.net/sourceforge
> > > _______________________________________________
> > > sqlobject-discuss mailing list
> > > sql...@li...
> > > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
> > 
> > 
> > 
> > -------------------------------------------------------
> > This SF.net email is sponsored by: ObjectStore.
> > If flattening out C++ or Java code to make your application fit in a
> > relational database is painful, don't do it! Check out ObjectStore.
> > Now part of Progress Software. http://www.objectstore.net/sourceforge
> > _______________________________________________
> > sqlobject-discuss mailing list
> > sql...@li...
> > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
> > 
> 
> 
> /-----------------------------------------------------------------
> | Bud P. Bruegger, Ph.D. 
> | Sistema (www.sistema.it)
> | Via U. Bassi, 54
> | 58100 Grosseto, Italy
> | +39-0564-411682 (voice and fax)
> \-----------------------------------------------------------------

Re: [SQLObject] another question on list special methods..

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

Sorry, I wasn't clear.  I need something for the iteration ('in' and
'for').  I have since read up a litte and it seems I need __iter__
(see http://www.python.org/doc/current/ref/sequence-types.html).

What kind of surprises me is that the built in lists (as in type([]))
don't have an __iter__ method but are a prime example for supporting
the "in" loop...  Maybe a leftover of older python versions...

--b

On 20 May 2003 10:43:58 -0500
Nick <ni...@dd...> wrote:

> I assumed wrong.  Looks like __getitem__ is what you want.
> 
> Nick
> 
> On Tue, 2003-05-20 at 07:04, Bud P.Bruegger wrote:
> > and as a followup:
> > 
> > Of particular magic to me is what happends here:
> > 
> > for item in myList:
> >    print item
> > 
> > I frankly haven't a clue what secial method to override to get control
> > over what is returned as items...
> > 
> > any idea?
> > 
> > /-----------------------------------------------------------------
> > | Bud P. Bruegger, Ph.D. 
> > | Sistema (www.sistema.it)
> > | Via U. Bassi, 54
> > | 58100 Grosseto, Italy
> > | +39-0564-411682 (voice and fax)
> > \-----------------------------------------------------------------
> > 
> > 
> > -------------------------------------------------------
> > This SF.net email is sponsored by: ObjectStore.
> > If flattening out C++ or Java code to make your application fit in a
> > relational database is painful, don't do it! Check out ObjectStore.
> > Now part of Progress Software. http://www.objectstore.net/sourceforge
> > _______________________________________________
> > sqlobject-discuss mailing list
> > sql...@li...
> > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
> 
> 
> 
> -------------------------------------------------------
> This SF.net email is sponsored by: ObjectStore.
> If flattening out C++ or Java code to make your application fit in a
> relational database is painful, don't do it! Check out ObjectStore.
> Now part of Progress Software. http://www.objectstore.net/sourceforge
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
> 


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

Re: [SQLObject] another question on list special methods..

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

I assumed wrong.  Looks like __getitem__ is what you want.

Nick

On Tue, 2003-05-20 at 07:04, Bud P.Bruegger wrote:
> and as a followup:
> 
> Of particular magic to me is what happends here:
> 
> for item in myList:
>    print item
> 
> I frankly haven't a clue what secial method to override to get control
> over what is returned as items...
> 
> any idea?
> 
> /-----------------------------------------------------------------
> | Bud P. Bruegger, Ph.D. 
> | Sistema (www.sistema.it)
> | Via U. Bassi, 54
> | 58100 Grosseto, Italy
> | +39-0564-411682 (voice and fax)
> \-----------------------------------------------------------------
> 
> 
> -------------------------------------------------------
> This SF.net email is sponsored by: ObjectStore.
> If flattening out C++ or Java code to make your application fit in a
> relational database is painful, don't do it! Check out ObjectStore.
> Now part of Progress Software. http://www.objectstore.net/sourceforge
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

Re: [SQLObject] another question on list special methods..

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

I assume __repr__

On Tue, 2003-05-20 at 07:04, Bud P.Bruegger wrote:
> and as a followup:
> 
> Of particular magic to me is what happends here:
> 
> for item in myList:
>    print item
> 
> I frankly haven't a clue what secial method to override to get control
> over what is returned as items...
> 
> any idea?
> 
> /-----------------------------------------------------------------
> | Bud P. Bruegger, Ph.D. 
> | Sistema (www.sistema.it)
> | Via U. Bassi, 54
> | 58100 Grosseto, Italy
> | +39-0564-411682 (voice and fax)
> \-----------------------------------------------------------------
> 
> 
> -------------------------------------------------------
> This SF.net email is sponsored by: ObjectStore.
> If flattening out C++ or Java code to make your application fit in a
> relational database is painful, don't do it! Check out ObjectStore.
> Now part of Progress Software. http://www.objectstore.net/sourceforge
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss