The SQLite desire is really an ease of development issue. I have a
co-worker who's doing mainly CSS and HTML design, and he doesn't need 20GB
of data on his development platform to do that. In fact, his system has
only 128G SSD and having a full Postgres database is going to be a problem
for him. Even just getting the psycopg2 adapter is proving to be a hassle
because he first needs to download XCode just to get the mandatory C
compiler to install the driver.
Instead of SQLite, I thought I could get him running on pg8000, but that
failed to run my database. At the time I thought the issue was lack of
Python 2.7 support, but Massimo mentioned he thinks the pg8000 driver
should be compatible. Which implies there is some other difference between
what psycopg2 and pg8000 can accept.
So I'm in a bit of a platform quagmire. I love Postgres and psycopg2 for
my production deployment, but development is becoming a hassle. I thought
I could easily isolate the database changes into my own library, but the
loading of the library is inconvenient because as you say, it needs to be
done dynamically like the db_uri stuff. I don't see any way to avoid using
"special features" when Postgres can't handle an "insert or ignore"
statement without a setpoint and rollback for the statement.
-- Joe
On Friday, August 9, 2013 12:44:45 PM UTC-7, Niphlod wrote:
>
> 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.
