Thread: [SQLObject] Problem With UPDATE
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: CLIFFORD I. <cli...@di...> - 2004-09-30 10:04:12
|
Hello, Since this question did not elicit any response on the Webware list and since it could well be a SQLObject problem, I shall ask here as well. I am running WebWare 0.8.1, SQLObject 0.6, mod_webkit2, and PostgresSQL 7.4.1 on Mandrake 10. I have a form that uses a JavaScript library to make an xmlrpc call to WebWare. When the user enters a new item and hits the Submit button, an xmlprc call is made which does an INSERT into the appropriate PostgreSQL table via SQLObject and another xmlrpc call is made which does a SELECT * on that same table so that the list of items can be updated. The the new item is inserted and it appears on the list of items displayed in a table on the form, no problem. Keep this in mind. I have a simple JavaScript routine that sorts the columns in the table when the user clicks on a link representing the columns. I noticed that the first item was not in the correct sort order. Upon closer examination, I noticed it had a leading space. I went to a shell where I had psql running and did a "UPDATE my_table set name = 'No Leading Space Name' where id = 99". I refreshed the browser but saw the first row in the table was still the item with the leading space. I double checked from psql to make sure I had not made a mistake and sure enough, there was no leading space and things sorted properly. I inserted another row using the form described above. Again, the new item was added to the db and to the table on the form but the problematic data remained *unchanged*. In order for the new item to be added to the table, it had to have come from the result of the xmlrpc request. If I restart the AppServer and refresh the browser, the data in the table on the form reflects what is in the db. To eliminate the JavaScript libraries, I started a Python shell and hit the xmlrpc server from there. The output of psql and the Python shell are below. devdb=# select * from grp_type; id | description | mod_user_id | mod_date -----+-------------+-------------+------------------------ 145 | Test 1 | cilkay | 2004-09-23 01:53:17-04 146 | Test 2 | cilkay | 2004-09-23 01:53:17-04 147 | Test 3 | cilkay | 2004-09-23 01:53:17-04 148 | Test 4 | cilkay | 2004-09-23 01:53:17-04 150 | Test 6 | cilkay | 2004-09-27 04:10:53-04 151 | Test 7 | cilkay | 2004-09-27 04:10:53-04 149 | Test 5 | cilkay | 2004-09-27 00:13:21-04 (7 rows) >>> import xmlrpclib >>> server = xmlrpclib.Server('http://localhost/wk/ww/grpTypeMethods') >>> groups = server.getAll() >>> print groups {'146': 'Test 2', '147': 'Test 3', '151': 'Test 7', '145': 'Test 1', '150': 'Test 6', '148': 'Test 4', '149': ' Test 5'} >>> devdb=# update grp_type set description = 'Test 5' where id = 149; UPDATE 1 devdb=# select * from grp_type; id | description | mod_user_id | mod_date -----+-------------+-------------+------------------------ 145 | Test 1 | cilkay | 2004-09-23 01:53:17-04 146 | Test 2 | cilkay | 2004-09-23 01:53:17-04 147 | Test 3 | cilkay | 2004-09-23 01:53:17-04 148 | Test 4 | cilkay | 2004-09-23 01:53:17-04 150 | Test 6 | cilkay | 2004-09-27 04:10:53-04 151 | Test 7 | cilkay | 2004-09-27 04:10:53-04 149 | Test 5 | cilkay | 2004-09-27 00:13:21-04 (7 rows) >>> groups = server.getAll() >>> print groups {'146': 'Test 2', '147': 'Test 3', '151': 'Test 7', '145': 'Test 1', '150': 'Test 6', '148': 'Test 4', '149': ' Test 5'} >>> Note the leading space in ' Test 5' in the Python results. Note that the db has changed. Now I restart the AppServer and execute server.getAll() again. >>> groups = server.getAll() >>> print groups {'146': 'Test 2', '147': 'Test 3', '151': 'Test 7', '145': 'Test 1', '150': 'Test 6', '148': 'Test 4', '149': 'Test 5'} >>> Python now reflects the state of the database. If I do an INSERT or a DELETE on grp_type from psql and execute server.getAll(), the changes will be reflected in "groups". It is only on UPDATE that it does not work as it should. Why is it behaving like this? More importantly, how can I make this work as it should? The xmlrpc code is below. from WebKit.XMLRPCServlet import XMLRPCServlet from devdb import * from psycopg import * # needed to trap for exceptions class grpTypeMethods(XMLRPCServlet): def exposedMethods(self): return ['listMethods','insert','getOne','getAll'] def listMethods(self): return ['listMethods','insert','getOne','getAll'] def insert(self,theDescription): try: newGrpType = GrpType( description=theDescription, modUserId='cilkay' ) return newGrpType.id except IntegrityError: return 0 def remove(self,theID): theGrpType = GrpType.get(theID) theGrpType.destroySelf() def getOne(self,theID): groupType = GrpType.get(theID) return [groupType.id,groupType.description] def getAll(self): result = {} grpTypes = None grpTypes = GrpType.select(orderBy=GrpType.q.description) for theGroupType in grpTypes: result[str(theGroupType.id)] = theGroupType.description return result Regards, Clifford Ilkay Dinamis Corporation 3266 Yonge Street, Suite 1419 Toronto, Ontario Canada M4N 3P6 Tel: 416-410-3326 |
From: Ian B. <ia...@co...> - 2004-09-30 15:46:25
|
CLIFFORD ILKAY wrote: > Hello, > > Since this question did not elicit any response on the Webware list and > since it could well be a SQLObject problem, I shall ask here as well. > > I am running WebWare 0.8.1, SQLObject 0.6, mod_webkit2, and PostgresSQL > 7.4.1 on Mandrake 10. I have a form that uses a JavaScript library to > make an xmlrpc call to WebWare. When the user enters a new item and hits > the Submit button, an xmlprc call is made which does an INSERT into the > appropriate PostgreSQL table via SQLObject and another xmlrpc call is > made which does a SELECT * on that same table so that the list of items > can be updated. The the new item is inserted and it appears on the list > of items displayed in a table on the form, no problem. Keep this in mind. > > I have a simple JavaScript routine that sorts the columns in the table > when the user clicks on a link representing the columns. I noticed that > the first item was not in the correct sort order. Upon closer > examination, I noticed it had a leading space. I went to a shell where I > had psql running and did a "UPDATE my_table set name = 'No Leading Space > Name' where id = 99". I refreshed the browser but saw the first row in > the table was still the item with the leading space. I double checked > from psql to make sure I had not made a mistake and sure enough, there > was no leading space and things sorted properly. I inserted another row > using the form described above. Again, the new item was added to the db > and to the table on the form but the problematic data remained > *unchanged*. In order for the new item to be added to the table, it had > to have come from the result of the xmlrpc request. If I restart the > AppServer and refresh the browser, the data in the table on the form > reflects what is in the db. SQLObject caches objects fairly aggressively. When you update something in the database without using SQLObject, the cache will still remain with the old (now incorrect) values. When you restart the AppServer, you are purging the cache, and hence the problem went away. You can refresh values with .sync() or .expire() (expire refreshes the values lazily). Right now there's no "expire everything" method, though there really should be. Does that explain your problem? -- Ian Bicking / ia...@co... / http://blog.ianbicking.org |
From: CLIFFORD I. <cli...@di...> - 2004-09-30 21:46:24
|
At 10:45 AM 30/09/2004 -0500, Ian Bicking wrote: >SQLObject caches objects fairly aggressively. When you update something >in the database without using SQLObject, the cache will still remain with >the old (now incorrect) values. When you restart the AppServer, you are >purging the cache, and hence the problem went away. >You can refresh values with .sync() or .expire() (expire refreshes the >values lazily). Right now there's no "expire everything" method, though >there really should be. > >Does that explain your problem? Hi Ian, I have eliminated Webware out of the loop for testing purposes. Regardless of whether I use two different SQLObject instances to change the data or one instance of SQLObject with psql, I get the same behaviour. Below, I have two Python shells running which I have called Instance 1 and Instance 2. You will notice that the second instance does NOT see changes made by the first even though SQLObject is used on both cases. How would you use sync() or expire() in the example below? I could not find any documentation on it. Instance 1 ---------- >>> from myClasses import * >>> from psycopg import * # needed to trap for exceptions >>> grpTypes = GrpType.select(orderBy=GrpType.q.name) >>> for theGroupType in grpTypes: ... print theGroupType.id, theGroupType.name ... 1 Test 1 2 Test 2 3 Test 3 4 Test 4 5 Test 5 6 Test 6 7 Test 7 Instance 2 ---------- Execute same code as above, same results. Instance 1 ---------- >>> x = GrpType.get(7) >>> x <GrpType 7 name='Test 7' modUserId='cilkay' modDate=<DateTime object for '2004-09-30 16:46:39.88' at 407c83d8>> >>> x.name = 'I have changed' >>> x <GrpType 7 name='I have changed' modUserId='cilkay' modDate=<DateTime object for '2004-09-30 16:46:39.88' at 407c83d8>> >>> grpTypes = GrpType.select(orderBy=GrpType.q.name) >>> for theGroupType in grpTypes: ... print theGroupType.id, theGroupType.name ... 7 I have changed 1 Test 1 2 Test 2 3 Test 3 4 Test 4 5 Test 5 6 Test 6 Instance 2 ---------- >>> grpTypes = GrpType.select(orderBy=GrpType.q.name) >>> for theGroupType in grpTypes: ... print theGroupType.id, theGroupType.name ... 1 Test 1 2 Test 2 3 Test 3 4 Test 4 5 Test 5 6 Test 6 7 Test 7 Regards, Clifford Ilkay Dinamis Corporation 3266 Yonge Street, Suite 1419 Toronto, Ontario Canada M4N 3P6 Tel: 416-410-3326 |
From: Ian B. <ia...@co...> - 2004-10-01 16:18:28
|
CLIFFORD ILKAY wrote: > I have eliminated Webware out of the loop for testing purposes. > Regardless of whether I use two different SQLObject instances to change > the data or one instance of SQLObject with psql, I get the same > behaviour. Below, I have two Python shells running which I have called > Instance 1 and Instance 2. You will notice that the second instance does > NOT see changes made by the first even though SQLObject is used on both > cases. Yes, it's really an issue of two processes -- one process doesn't see what the other process is doing. > How would you use sync() or expire() in the example below? I could not > find any documentation on it. You'd put in .sync before using the item. But that will lead to an excess of queries. I think you can do: GrpType._connection.cache.expireAll() This technique isn't well tested. But it will essentially forget all the cached values. Hmm... or maybe you need to do: cache = GrpType._connection.cache for id in cache.allIDs(GrpType): cache.expire(id, GrpType) You can also use sync, like this (which will be slow): > Instance 1 > ---------- > >>> from myClasses import * > >>> from psycopg import * # needed to trap for exceptions > >>> grpTypes = GrpType.select(orderBy=GrpType.q.name) > >>> for theGroupType in grpTypes: theGroupType.sync() > ... print theGroupType.id, theGroupType.name > ... > 1 Test 1 > 2 Test 2 > 3 Test 3 > 4 Test 4 > 5 Test 5 > 6 Test 6 > 7 Test 7 -- Ian Bicking / ia...@co... / http://blog.ianbicking.org |