I'm currently using Posgres as my database, but I'd like to preserve the
portability that web2py gives me to change databases. I find that I need
to add all sorts of "idioms" in my database code and I'm running dry when
thinking of ways to abstract this and make a layer that allows me to use,
say, SQLite in place of Postgres. Let me provide an example.
When inserting data into Postgres which may collide with an existing row,
and for which there is a unique index, I need to bracket the insert with
code that sets a savepoint, tries the statement, and then either removes
the savepoint or rolls back the transaction. It looks like this:
def ignore_on_fail(token,stmt,*args,**kwargs):
"""Insert a row into the database but bracket the insert
with a 'savepoint' and rollback to it if the statement fails"""
from random import randint
savepoint_name = '_'.join([token,str(randint())])
ret = None
db = current.db
try:
db.executesql('SAVEPOINT %s;'%savepoint_name)
ret = stmt(*args,**kwargs)
db.executesql('RELEASE %s;'%savepoint_name)
except:
db.executesql('ROLLBACK TO %;'%savepoint_name)
return ret
In SQLite, the table is built with a "ON CONFLICT IGNORE" constraint
resolution, so the equivalent fucntion would simply be:
def ignore_on_fail(token,stmt,*args,**kwargs):
"""Insert a row into the database but bracket the insert
with a 'savepoint' and rollback to it if the statement fails"""
ret = stmt(*args,**kwargs)
return ret
But where to put these functions? And how do I choose one set over the
other, based on something that isn't known until the "db.py" file is read?
I can put these in a module and import them, but the module name must be
known at compile time (since these database commands are occurring in
modules as well) and that isn't known because the db.py file has not yet
been read. My head hurts!
There must be some pattern I'm missing to extend my database code so that I
can prevent it from being too tied to a single platform. What pattern do
you use?
-- Joe
--
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
