This is a tough point. One thing is saying that your app should run on any
relational database supported by web2py (i.e. you can say to your users
that they can use whatever db they feel comfortable to use).
Unfortunately, you reached a point where you feel compelled to use some
features that are pertinent only to a single db engine, and I really don't
see the "added bonus" of complicating your code vs restricting the choice
to a single db engine. The fact that your app is considering running on
SQLite and in Postgresql makes me nervous: one is a tiny non-concurrent
on-disk database with a ton of quirks, the other is a full fledged engine
(maybe the most feature-packed out there).
That being said as a totally personal opinion (don't blame my narrow
mindset :P), your code should be clearly divided as the adapters in DAL
are, else you're forced to inspect the db._uri to choose the right
method/class/function.
Your code should be loaded AFTER the db.py is read....or you must code a
global setting that will "choose" both your adapter AND the relevant
connection string for your db.py.
On Friday, August 9, 2013 6:53:22 PM UTC+2, Joe Barnhart wrote:
>
> 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 web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
