> Hi all,
>
> this afternoon I stumbled into a bug [1] (or I haven't found the right
> setting yet ;)) with SQLObject with PostgreSQL.
> To summarise, I have to work with a database that already exists, an gets
> queried by other applications as well. So I cannot change the DB layout.
> The guy that created the database (ehum... that would be me) though it
> was
> a brilliant idea to write it all in camel-back notation (actually I do
> that in all databases). Now, PostgreSQL requires table names, column
> names, and all other objects containing capital letters to be enclosed in
> double quotes. Otherwise it converts it all to lowercase. So
> SELECT * FROM Hotel;
> becomes
> SELECT * FROM hotel;
> And that table does not exist.
>
> I got around the problem by adding a |table = '"Hotel"'| statement to
> sqlmeta. So far so good. I can query the database. But, when I try to
> insert a new row, it needs to get the next insert id, where it fails
> horribly:
>
> -----------------------------------------------------------------------------------
>>>> x = Hotel(name="foo", address=None, owner=None, establishment=None)
> Traceback (most recent call last):
> File "<console>", line 1, in ?
> File
> "/usr/lib/python2.4/site-packages/SQLObject-0.7.1dev_r1675-py2.4.egg/sqlobject/declarative.py",
> line 92, in _wrapper
> return_value = fn(self, *args, **kwargs)
> File
> "/usr/lib/python2.4/site-packages/SQLObject-0.7.1dev_r1675-py2.4.egg/sqlobject/main.py",
> line 1197, in __init__
> self._create(id, **kw)
> File
> "/usr/lib/python2.4/site-packages/SQLObject-0.7.1dev_r1675-py2.4.egg/sqlobject/main.py",
> line 1224, in _create
> self._SO_finishCreate(id)
> File
> "/usr/lib/python2.4/site-packages/SQLObject-0.7.1dev_r1675-py2.4.egg/sqlobject/main.py",
> line 1248, in _SO_finishCreate
> id, names, values)
> File
> "/usr/lib/python2.4/site-packages/SQLObject-0.7.1dev_r1675-py2.4.egg/sqlobject/dbconnection.py",
> line 759, in queryInsertID
> return self._dbConnection._queryInsertID(
> File
> "/usr/lib/python2.4/site-packages/SQLObject-0.7.1dev_r1675-py2.4.egg/sqlobject/postgres/pgconnection.py",
> line 126, in _queryInsertID
> c.execute("SELECT NEXTVAL('%s')" % sequenceName)
> ProgrammingError: ERROR: invalid name syntax
>
> SELECT NEXTVAL('""Hotel"_"idHotel"_seq"')
> -----------------------------------------------------------------------------------
>
> The last line reveals that it has trouble to deal with the added quotes.
>
> Is there a possibility to get around this, or is the only way to modify
> SQLObject? I was already trying to start with the latter option, but I
> have no idea where to start. Where should these kind of transformations
> go?
>
> References:
> [1]:
> http://sourceforge.net/tracker/index.php?func=detail&aid=1541095&group_id=74338&atid=540672
>
>
> Best regards,
>
> Michel Albert
>
Ok, this is a quick and *very* dirty patch to solve this:
-----------------------------------------------------------------------------------
--- ../../postgres/pgconnection.py 2006-08-16 18:48:52.000000000 +0200
+++ ../../postgres_patched/pgconnection.py 2006-08-16
19:16:49.000000000 +0200
@@ -121,6 +121,8 @@
idName = soInstance.sqlmeta.idName
sequenceName = getattr(soInstance, '_idSequence',
'%s_%s_seq' % (table, idName))
+ sequenceName = '"' + sequenceName.replace('"', '') + '"'
+
c = conn.cursor()
if id is None:
c.execute("SELECT NEXTVAL('%s')" % sequenceName)
-----------------------------------------------------------------------------------
!!! Please pay attention to the folders mentioend in the patch !!!
Essentially this removes all existing quotes from the sequence name, and
wraps it anew with quotes. Like this, the way I described by fixing this
using sqlmeta, is still necessary, but it works.
It might be possible, that this happens elsewhere too, but I have no time
to dig through every line :(
Regards,
Mich
-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
sqlobject-discuss mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
