Using pysqlite2 instead of sqlite3 when desired
Matthew D. Hancher
In order to load the SpatiaLite extensions for SQLite, you first need
to enable SQLite extensions in the first place. Unfortunately,
pysqlite did not expose this functionality until pysqlite 2.5.0, which
means it didn't make it into Python's sqlite3 module (which is
essentially a snapshot of pysqlite 2.3.2, even in Python 2.6 ; I
haven't checked Py3k).
The same need arises if you want to use any other SQLite extensions,
like the full-text-search extension. So, I'm trying to figure out the
cleanest way to let users take advantage of a newer pysqlite2 if it's
present, since currently Django always uses sqlite3 if it's there.
The original reason for making sqlite3 the default was to "to do the
right thing first and fallback" (#2772), which made the (seemingly
reasonable) assumption that sqlite3 would become the undisputed
correct name in the future. Unfortunately, the package itself
retained the pysqlite2 name, and only the Python-internal snapshots
(which will inevitably become outdated) have the sqlite3 name.
Options include:
A. Leave sqlite3 as the default, and add a configuration setting that
forces use of pysqlite2 if desired.
B. Always try both sqlite3 and pysqlite2, and use whichever has the
greater version number if both are present.
C. Same as B, but with an optional configuration option to force one
or the other if desired.
D. Switch to making pysqlite2 the default, since that's the correct
name for the module if the user has explicitly installed it, and treat
the Python-bundled version as the fallback.
E. Develop arbitrarily convoluted bits of logic that pay attention to
e.g. what Python version we're running under or the phase of the moon
or something.
I think I'm personally slightly inclined towards C, but I would be
happy with B or D. I don't really like A because I worry that the
sqlite3 module will become increasingly outdated and this
configuration option will be an extra step and source of confusion for
new users. But users would already be forced to think about the issue
when installing pysqlite2 in the first place, so A would not be the
end of the world either, and it does minimize the chance of
accidentally breaking users with partially-broken pysqlite2
installations that Django is currently blissfully ignoring.
Thoughts? If there's a consensus on an approach, then I can open a
ticket with a patch....
Matt
Matt Hancher
Intelligent Systems Division
NASA Ames Research Center
Matthew....@nasa.gov
Malcolm Tredinnick
On Mon, 2008-11-17 at 16:19 -0800, Matthew D. Hancher wrote:
[...]
> Options include:
>
> A. Leave sqlite3 as the default, and add a configuration setting that
> forces use of pysqlite2 if desired.
>
> B. Always try both sqlite3 and pysqlite2, and use whichever has the
> greater version number if both are present.
>
> C. Same as B, but with an optional configuration option to force one
> or the other if desired.
>
> D. Switch to making pysqlite2 the default, since that's the correct
> name for the module if the user has explicitly installed it, and treat
> the Python-bundled version as the fallback.
>
> E. Develop arbitrarily convoluted bits of logic that pay attention to
> e.g. what Python version we're running under or the phase of the moon
> or something.
>
> I think I'm personally slightly inclined towards C, but I would be
> happy with B or D. I don't really like A because I worry that the
> sqlite3 module will become increasingly outdated and this
> configuration option will be an extra step and source of confusion for
> new users.
I suspect things are a little hairier than you realise, too. There's
evidence (by which I mean "people using Windows have reported" -- and
despite their choice of OS they seem otherwise reliable) that Windows
has a different version of sqlite3 in their Python binaries to what I
have, for example, in my Linux (Fedora) installed versions.
We should do something, since it's quite reasonable to expect Python 2.5
will be in active use for years to come, so hoping that Python upstream
get their act together and/or SQLite development speed slows down are
not particularly realistic.
I tend to prefer option A (or D), since the number of people who'll
really need to use it should be small and, frankly, if you're up to
using GIS features, then reading documentation will hopefully be a skill
you've already mastered. Beginners won't need to worry about this, since
they won't be using features that require this setting to be touched.
I dislike options B and C because they will load two modules if you have
them both installed. Django's already fairly memory hungry and this only
adds to the burden.
Option D is not unreasonable, either. I can't think of a way it can
backfire, given that pysqlite2 is compiled against a particular Python
version and so shouldn't be accidentally importing the 2.4 version with
Python 2.5 or something. But I may be missing some outwardly-looking
crazy installation option that causes that to happen.
Strong -1 on E. It's been suggested previously as a way to work around
problems with sqlite3 and Windows and I didn't like it then either (in
that particular case, it turned out to be symptomatic of a broader class
of bug in SQLite that we still have to fix, so I feel somewhat justified
in preferring cleaner code at the time).
I guess I prefer D the most, subject to a slight concern if the wrong
version could possibly be imported (but I can't imagine how). Then
option A. Don't like the others at all.
Regards,
Malcolm
Matt Hancher
wrote:
>
> [...]
>
> > Options include:
>
> > A. Leave sqlite3 as the default, and add a configuration setting that
> > forces use of pysqlite2 if desired.
>
> > B. Always try both sqlite3 and pysqlite2, and use whichever has the
> > greater version number if both are present.
>
> > C. Same as B, but with an optional configuration option to force one
> > or the other if desired.
>
> > D. Switch to making pysqlite2 the default, since that's the correct
> > name for the module if the user has explicitly installed it, and treat
> > the Python-bundled version as the fallback.
> I guess I prefer D the most, subject to a slight concern if the wrong
> version could possibly be imported (but I can't imagine how). Then
> option A. Don't like the others at all.
cause
someone somewhere a problem. All it would take would be for them
to have an outdated version of pysqlite2 installed on their system.
Django would have been happily ignoring it since r3818, and suddenly
we'd be using it instead. Lord knows how many babies might be killed.
So, if you're not a fan of option B then I'm inclined to go with the
conservative option A. Then the question is, what should the
configuration option look like? Here are two options:
1: SQLITE_MODULE_NAME = 'pysqlite2'
2: DATABASE_OPTIONS = {'module': 'pysqlite2'}
3: USE_PYSQLITE2 = True
4: # Other
Preferences? My current vote is option 2, because it does not
introduce a new top-level setting, but it does sort of overload an
existing one, so I'd understand arguments in favor of option 1.
Malcolm Tredinnick
This is what I was hinting at (since I have plenty of modules for python
2.3 and 2.4 sitting around), but think about how it can really happen.
SQLite is a binary module. So you will have had to have intentionally
built a binary module of an earlier version of pysqlite2 for a version
of Python that *already contains* sqlite3. You did this by accident,
apparently, since you didn't know it was an earlier version. Is this
really a use-case we need to cater for?
When you upgrade from Python 2.4 to Python 2.5, your pysqlite2 module
for python2.4 is no longer in the game.
Still, it is a minor inconvenience, so I could still sleep at night if
we went with option A. The need to use the pysqlite2 version apparently
affects Windows users of Python 2.5.1 (maybe 2.5.2 also, I don't know),
since there was some version skew there that Ramiro Morales identified
as a problem and people using spatial support. The latter group can
certainly read about the new setting and use it. It's the former group
I'm a little more concerned about. It includes a lot of beginners who
are not famous for their ability to read documentation or debug problems
as a broad group.
> So, if you're not a fan of option B then I'm inclined to go with the
> conservative option A. Then the question is, what should the
> configuration option look like? Here are two options:
>
> 1: SQLITE_MODULE_NAME = 'pysqlite2'
> 2: DATABASE_OPTIONS = {'module': 'pysqlite2'}
> 3: USE_PYSQLITE2 = True
> 4: # Other
>
> Preferences? My current vote is option 2, because it does not
> introduce a new top-level setting, but it does sort of overload an
> existing one, so I'd understand arguments in favor of option 1.
It's a toss-up between options 1 and 2, isn't it? Normally, I'd prefer
option 1, but it adds a bunch of extra files to support essentially one
import statement.
Since so far only you and I have made real input here, I'd like to hear
what any of the other maintainers (or anybody else who uses this stuff)
thinks. Realistically, any of the options are survivable, so barring any
great arguments one way or the other, we should just pick one and commit
it. I don't see the functionality request as being controversial and I'm
probably putting too much effort into worrying about the fine details.
Cheers,
Malcolm
oggie rob
> what any of the other maintainers (or anybody else who uses this stuff)
> thinks. Realistically, any of the options are survivable, so barring any
> great arguments one way or the other, we should just pick one and commit
> it. I don't see the functionality request as being controversial and I'm
> probably putting too much effort into worrying about the fine details.
was simply being aware that using pysqlite was a quick and simple
solution to concurrency and db locking issues. So making it explicit
wouldn't hurt, for the sole reason that it communicates that concept
(assuming of course it is documented :)
Since sqlite takes advantage of the DATABASE_OPTIONS field for
timeout, it would probably make sense to use # 2.
-rob
Ramiro Morales
<mal...@pointy-stick.com> wrote:
>> > On Mon, 2008-11-17 at 16:19 -0800, Matthew D. Hancher wrote:
>> > > Options include:
>> >
>> > > A. Leave sqlite3 as the default, and add a configuration setting that
>> > > forces use of pysqlite2 if desired.
>> >
>> > > B. Always try both sqlite3 and pysqlite2, and use whichever has the
>> > > greater version number if both are present.
>> >
>> > > C. Same as B, but with an optional configuration option to force one
>> > > or the other if desired.
>> >
>> > > D. Switch to making pysqlite2 the default, since that's the correct
>> > > name for the module if the user has explicitly installed it, and treat
>> > > the Python-bundled version as the fallback.
>>
>
> we went with option A. The need to use the pysqlite2 version apparently
> affects Windows users of Python 2.5.1 (maybe 2.5.2 also, I don't know),
> since there was some version skew there that Ramiro Morales identified
> as a problem
Actually, it was Python 2.5.2, it includes SQLite 3.3.4 (and sqlite3
module version is 2.3.2) and since then we've [1]learned that official
win32 binary installers of Python 2.5.4 bundles the same combination.
Regards,
--
Ramiro Morales
1. http://groups.google.com/group/django-developers/msg/1368697b43ea27ea?hl=en