Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #85771 > unrolled thread

Re: sqlite3 and dates

Back to article view | Back to comp.lang.python

This discussion starts older than the indexed window; earlier articles aren't shown. The article labeled Started by below is the oldest one visible, not the original post.

Contents

  Re: sqlite3 and dates Chris Angelico <rosuav@gmail.com> - 2015-02-18 18:05 +1100
    Re: sqlite3 and dates Johannes Bauer <dfnsonfsduifb@gmx.de> - 2015-02-18 12:11 +0100
      Re: sqlite3 and dates Chris Angelico <rosuav@gmail.com> - 2015-02-18 22:21 +1100
        Re: sqlite3 and dates Johannes Bauer <dfnsonfsduifb@gmx.de> - 2015-02-18 12:57 +0100
          Re: sqlite3 and dates Chris Angelico <rosuav@gmail.com> - 2015-02-18 23:14 +1100
            Re: sqlite3 and dates Johannes Bauer <dfnsonfsduifb@gmx.de> - 2015-02-18 14:13 +0100
            Not sqlite3 and dates Steve Hayes <hayesstw@telkomsa.net> - 2015-02-19 04:53 +0200
          Re: sqlite3 and dates Adam Funk <a24061@ducksburg.com> - 2015-02-19 13:18 +0000
        Re: sqlite3 and dates rurpy@yahoo.com - 2015-02-18 14:17 -0800
          Re: sqlite3 and dates Chris Angelico <rosuav@gmail.com> - 2015-02-19 09:37 +1100
            Not sqlite3 and dates Steve Hayes <hayesstw@telkomsa.net> - 2015-02-19 04:54 +0200
            Re: sqlite3 and dates Adam Funk <a24061@ducksburg.com> - 2015-02-19 13:21 +0000
          Re: sqlite3 and dates Ethan Furman <ethan@stoneleaf.us> - 2015-02-18 14:52 -0800
          'Lite' Databases (Re: sqlite3 and dates) memilanuk <memilanuk@gmail.com> - 2015-02-18 15:32 -0800
            Re: 'Lite' Databases (Re: sqlite3 and dates) Mario Figueiredo <marfig@gmail.com> - 2015-02-19 01:08 +0100
              Re: 'Lite' Databases (Re: sqlite3 and dates) Chris Angelico <rosuav@gmail.com> - 2015-02-19 11:42 +1100
                Re: 'Lite' Databases (Re: sqlite3 and dates) Steven D'Aprano <steve+comp.lang.python@pearwood.info> - 2015-02-19 13:13 +1100
                  Re: 'Lite' Databases (Re: sqlite3 and dates) Mark Lawrence <breamoreboy@yahoo.co.uk> - 2015-02-19 03:43 +0000
                    Re: 'Lite' Databases (Re: sqlite3 and dates) Mario Figueiredo <marfig@gmail.com> - 2015-02-19 08:49 +0100
                  Re: 'Lite' Databases (Re: sqlite3 and dates) rurpy@yahoo.com - 2015-02-18 20:10 -0800
                    Re: 'Lite' Databases (Re: sqlite3 and dates) Steven D'Aprano <steve+comp.lang.python@pearwood.info> - 2015-02-19 18:07 +1100
                      Re: 'Lite' Databases (Re: sqlite3 and dates) Chris Angelico <rosuav@gmail.com> - 2015-02-19 18:23 +1100
                        Re: 'Lite' Databases (Re: sqlite3 and dates) rurpy@yahoo.com - 2015-02-19 12:26 -0800
                          Re: 'Lite' Databases (Re: sqlite3 and dates) Chris Angelico <rosuav@gmail.com> - 2015-02-20 07:47 +1100
                            Re: 'Lite' Databases (Re: sqlite3 and dates) rurpy@yahoo.com - 2015-02-19 20:20 -0800
                              Re: 'Lite' Databases (Re: sqlite3 and dates) Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2015-02-20 09:16 -0500
                              Re: 'Lite' Databases (Re: sqlite3 and dates) Sibylle Koczian <nulla.epistola@web.de> - 2015-02-21 11:44 +0100
                              Re: 'Lite' Databases (Re: sqlite3 and dates) Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2015-02-21 12:54 -0500
                          Re: 'Lite' Databases (Re: sqlite3 and dates) Mario Figueiredo <marfig@gmail.com> - 2015-02-19 22:23 +0100
                            Re: 'Lite' Databases (Re: sqlite3 and dates) rurpy@yahoo.com - 2015-02-19 20:27 -0800
                      Re: 'Lite' Databases (Re: sqlite3 and dates) rurpy@yahoo.com - 2015-02-19 12:20 -0800
              Re: 'Lite' Databases (Re: sqlite3 and dates) rurpy@yahoo.com - 2015-02-18 20:05 -0800
                Re: 'Lite' Databases (Re: sqlite3 and dates) Tim Chase <python.list@tim.thechases.com> - 2015-02-19 08:21 -0600
              Re: 'Lite' Databases (Re: sqlite3 and dates) Gregory Ewing <greg.ewing@canterbury.ac.nz> - 2015-02-19 18:22 +1300
                Re: 'Lite' Databases (Re: sqlite3 and dates) Mario Figueiredo <marfig@gmail.com> - 2015-02-19 08:33 +0100
              Re: 'Lite' Databases (Re: sqlite3 and dates) Mark Lawrence <breamoreboy@yahoo.co.uk> - 2015-02-19 05:32 +0000
              Re: 'Lite' Databases (Re: sqlite3 and dates) Tim Chase <python.list@tim.thechases.com> - 2015-02-19 08:17 -0600
              Re: 'Lite' Databases (Re: sqlite3 and dates) Mark Lawrence <breamoreboy@yahoo.co.uk> - 2015-02-19 15:04 +0000
              Re: 'Lite' Databases (Re: sqlite3 and dates) Chris Angelico <rosuav@gmail.com> - 2015-02-20 02:19 +1100
              Re: 'Lite' Databases (Re: sqlite3 and dates) Tim Chase <python.list@tim.thechases.com> - 2015-02-19 10:03 -0600
                Re: 'Lite' Databases (Re: sqlite3 and dates) rurpy@yahoo.com - 2015-02-19 11:45 -0800
          Re: 'Lite' Databases (Re: sqlite3 and dates) Ben Finney <ben+python@benfinney.id.au> - 2015-02-19 11:03 +1100
            Re: 'Lite' Databases (Re: sqlite3 and dates) Paul Rubin <no.email@nospam.invalid> - 2015-02-20 13:17 -0800
              Re: 'Lite' Databases (Re: sqlite3 and dates) Mark Lawrence <breamoreboy@yahoo.co.uk> - 2015-02-20 21:44 +0000
              Re: 'Lite' Databases (Re: sqlite3 and dates) Ethan Furman <ethan@stoneleaf.us> - 2015-02-20 14:10 -0800
              Re: 'Lite' Databases (Re: sqlite3 and dates) Chris Angelico <rosuav@gmail.com> - 2015-02-21 12:24 +1100
              Re: 'Lite' Databases Ben Finney <ben+python@benfinney.id.au> - 2015-02-21 14:13 +1100
              Re: 'Lite' Databases (Re: sqlite3 and dates) Tim Chase <python.list@tim.thechases.com> - 2015-02-20 15:31 -0600
              Re: 'Lite' Databases Chris Angelico <rosuav@gmail.com> - 2015-02-21 16:39 +1100
              Re: 'Lite' Databases (Re: sqlite3 and dates) Ned Deily <nad@acm.org> - 2015-02-20 22:22 -0800
                Re: 'Lite' Databases (Re: sqlite3 and dates) Paul Rubin <no.email@nospam.invalid> - 2015-02-20 22:42 -0800
                  Re: 'Lite' Databases (Re: sqlite3 and dates) Ned Deily <nad@acm.org> - 2015-02-21 00:17 -0800
                    Re: 'Lite' Databases (Re: sqlite3 and dates) Paul Rubin <no.email@nospam.invalid> - 2015-02-21 00:32 -0800
              Re: 'Lite' Databases (Re: sqlite3 and dates) "Eric S. Johansson" <esj@harvee.org> - 2015-02-21 14:27 -0500
          Re: 'Lite' Databases (Re: sqlite3 and dates) memilanuk <memilanuk@gmail.com> - 2015-02-18 19:33 -0800
          Re: 'Lite' Databases (Re: sqlite3 and dates) Chris Angelico <rosuav@gmail.com> - 2015-02-19 15:01 +1100
          Re: 'Lite' Databases (Re: sqlite3 and dates) Ben Finney <ben+python@benfinney.id.au> - 2015-02-19 15:09 +1100
            Re: 'Lite' Databases (Re: sqlite3 and dates) rurpy@yahoo.com - 2015-02-18 20:26 -0800
            Re: 'Lite' Databases (Re: sqlite3 and dates) Steven D'Aprano <steve+comp.lang.python@pearwood.info> - 2015-02-19 18:23 +1100
          When to use SQLite3 [was Re: 'Lite' Databases (Re: sqlite3 and dates)] Ethan Furman <ethan@stoneleaf.us> - 2015-02-18 20:15 -0800
            Re: When to use SQLite3 [was Re: 'Lite' Databases (Re: sqlite3 and dates)] Steve Hayes <hayesstw@telkomsa.net> - 2015-02-19 06:59 +0200
              Re: When to use SQLite3 [was Re: 'Lite' Databases (Re: sqlite3 and dates)] Ethan Furman <ethan@stoneleaf.us> - 2015-02-18 21:07 -0800
          Re: 'Lite' Databases (Re: sqlite3 and dates) memilanuk <memilanuk@gmail.com> - 2015-02-18 20:29 -0800
          Re: 'Lite' Databases (Re: sqlite3 and dates) Ben Finney <ben+python@benfinney.id.au> - 2015-02-19 15:36 +1100
          Re: 'Lite' Databases (Re: sqlite3 and dates) memilanuk <memilanuk@gmail.com> - 2015-02-18 20:57 -0800
          Re: 'Lite' Databases (Re: sqlite3 and dates) Ben Finney <ben+python@benfinney.id.au> - 2015-02-19 16:16 +1100
          Re: 'Lite' Databases (Re: sqlite3 and dates) memilanuk <memilanuk@gmail.com> - 2015-02-18 21:26 -0800
          Re: 'Lite' Databases (Re: sqlite3 and dates) Ethan Furman <ethan@stoneleaf.us> - 2015-02-18 21:37 -0800
            Re: 'Lite' Databases (Re: sqlite3 and dates) rurpy@yahoo.com - 2015-02-19 13:17 -0800
        Re: sqlite3 and dates Steve Hayes <hayesstw@telkomsa.net> - 2015-02-19 04:48 +0200
          Re: sqlite3 and dates Mark Lawrence <breamoreboy@yahoo.co.uk> - 2015-02-19 03:34 +0000
      Re: sqlite3 and dates Ben Finney <ben+python@benfinney.id.au> - 2015-02-19 07:14 +1100
        Re: sqlite3 and dates rurpy@yahoo.com - 2015-02-18 14:13 -0800
          Re: sqlite3 and dates Steven D'Aprano <steve+comp.lang.python@pearwood.info> - 2015-02-19 10:07 +1100
            Re: sqlite3 and dates rurpy@yahoo.com - 2015-02-18 20:08 -0800

Page 2 of 4 — ← Prev page 1 [2] 3 4  Next page →

#85872 — Re: 'Lite' Databases (Re: sqlite3 and dates)

rurpy@yahoo.com wrote:

> On 02/18/2015 07:13 PM, Steven D'Aprano wrote:> Chris Angelico wrote:
>>>> SQLite misses some important features that makes it better suited as a
>>>> simple datastore, not much unlike shelve. And network use is not one
>>>> of them, since you can actually implement concurrent sqlite access by
>>>> coding an intermediate layer. Assuming of course we are talking about
>>>> a small number of concurrent users.
>>>
>>> This is what I was saying: it's fine for purposes like Firefox's
>>> bookmarks and settings and such (which I think was what it was
>>> originally developed for?). Not so fine over a network.
>> 
>> The sheer number of Firefox bugs related to its use of SQLite says
>> different.
>>
>> Once upon a time, Firefox's config, bookmarks, etc. were stored in plain
>> text files. At worst they were HTML. You could trivially read them, copy
>> them, restore them and even (if you were careful) edit them using the
>> text editor of your choice. Many a time I was on one machine, wanted to
>> know a bookmark from another machine, so I would ssh across to the other
>> machine and run grep over the bookmark file.
> 
> I agree, I prefer plain text files whenever practical.  But since
> the original discussion was about Sqlite vs Postgresql, not Sqlite
> vs text files, shouldn't the question be: would Firefox be better
> if it required you to install and configure Postgreql instead of
> using Sqlite?

Very possibly. With modern dependency management, it isn't hard to install 
Postgresql:

sudo aptitude postgresql

or equivalent should work. For primitive operating systems with no 
dependency management available, Firefox could come with a simple script 
which downloads, installs, configures and runs Postgresql. (Everything is 
simple for the guy who doesn't have to do it.)

Possible snags:

- Possibly postgresql is simply *too big*. Your 50MB(?) Firefox 
  turns into a 2GB install. I doubt it -- on Debian, postgresql 
  is 17MB installed. But I don't know what dependencies I'm not
  counting.

- Or it is impossible to configure without excessive amounts of
  tech-savvy human intervention. Again, I doubt it. I seem to 
  recall needing to create a Postgresql user and password. But 
  maybe even that is too technical for the average Firefox user.

- Maybe there are nasty interactions between Postgresql listening
  on some port and Windows firewall wanting to block that same port.

Or... and here is a radical thought... maybe Firefox could give you the 
choice of which database? By default, it might use Sqlite, to satisfy the 
desktop users who don't want to think about it. And for those who are 
disturbed by the fragility of Sqlite on a network home directory, you just 
set a config setting in about:config to point at your existing Postgresql 
instance, and never need worry about it again.

The Firefox devs surprise and confuse me. On the one hand, they have 
designed a powerful plug-in architecture, and encourage their user-base to 
use it for all sorts of amazing functionality that they don't want to build 
into the core browser. Yay for this. And on the other hand, they are 
*actively hostile* to any suggestion that using SQlite is not the best and 
*only* appropriate solution to the problem of storing config, bookmarks and 
history. A plug-in database architecture would probably work really well.

> I don't see any evidence that it is Sqlite that is the problem
> as opposed to FF's use (or misuse) of it, or other problems that
> are in FF and have nothing to do with Sqlite.

No no, even Sqlite devs recommend against using it on network drives. The 
Firefox problem is that when FF crashes, as it can do, or if you yank the 
power to the computer and everything dies, if your home directory is on a 
network drive, the database may be left in a locked state, or even 
corrupted. Nothing that the FF developers can do, given the choice of 
Sqlite.


> If Sqlite reliably
> implements ACID semantics as they claim, 

Ah, well "reliably" is a tricky word...

http://stackoverflow.com/questions/788517/sqlite-over-a-network-share


-- 
Steve

[toc] | [prev] | [next] | [standalone]

#85874 — Re: 'Lite' Databases (Re: sqlite3 and dates)

On Thu, Feb 19, 2015 at 6:07 PM, Steven D'Aprano
<steve+comp.lang.python@pearwood.info> wrote:
> Very possibly. With modern dependency management, it isn't hard to install
> Postgresql:
>
> sudo aptitude postgresql
>
> or equivalent should work. For primitive operating systems with no
> dependency management available, Firefox could come with a simple script
> which downloads, installs, configures and runs Postgresql. (Everything is
> simple for the guy who doesn't have to do it.)

Definitely a possibility. I'm pretty sure I've seen that exact thing
done by one application (on Windows; on a typical Linux system,
that'll be done by simple dependency management - your package
metadata says "depends on postgresql" and apt-get or yum or whatever
will do the work), and it's quite common for a Windows installer
wizard to go through a series of subcomponents (grab the .NET
framework, grab these hotfixes that the program depends on, grab some
adware toolbar that you forgot to untick, and *then* install the
program you asked for).

> Possible snags:
>
> - Possibly postgresql is simply *too big*. Your 50MB(?) Firefox
>   turns into a 2GB install. I doubt it -- on Debian, postgresql
>   is 17MB installed. But I don't know what dependencies I'm not
>   counting.

Yeah. And frankly, I would be surprised if Firefox is only 50MB these
days. The extra dent of PostgreSQL won't be all that significant - and
don't forget that the SQLite3 dent can be removed, so you're talking
about the difference between them, plus you can omit a whole bunch of
PG's ancillaries.

> - Or it is impossible to configure without excessive amounts of
>   tech-savvy human intervention. Again, I doubt it. I seem to
>   recall needing to create a Postgresql user and password. But
>   maybe even that is too technical for the average Firefox user.

You don't even need to do that. An absolutely default Postgres on
Debian or Ubuntu is ready to use, with peer authentication. If you can
become root, you can then drop privs to the 'postgres' user and
connect to the database that way.

I don't know if it's possible to do a non-root installation of
PostgreSQL, but if it isn't today, it could easily be tomorrow, if
someone puts in a little effort. You'd miss out on boot-time startup,
and it'd probably have to do some password-based authentication (with
autogenerated passwords), but it certainly could work. The Windows
equivalent ("install for just me") is, I think, already possible.

> - Maybe there are nasty interactions between Postgresql listening
>   on some port and Windows firewall wanting to block that same port.

That's definitely an issue, given that Windows doesn't have Unix
domain sockets. But I'm sure it's a solvable problem. How does IDLE
cope with firewall issues?

ChrisA

[toc] | [prev] | [next] | [standalone]

#85933 — Re: 'Lite' Databases (Re: sqlite3 and dates)

On 02/19/2015 12:23 AM, Chris Angelico wrote:
> On Thu, Feb 19, 2015 at 6:07 PM, Steven D'Aprano <steve+comp.lang.python@pearwood.info> wrote:
>> Very possibly. With modern dependency management, it isn't hard to install
>> Postgresql:
>>
>> sudo aptitude postgresql
>>
>> or equivalent should work. For primitive operating systems with no
>> dependency management available, Firefox could come with a simple script
>> which downloads, installs, configures and runs Postgresql. (Everything is
>> simple for the guy who doesn't have to do it.)
> 
> Definitely a possibility. I'm pretty sure I've seen that exact thing
> done by one application (on Windows; on a typical Linux system,
> that'll be done by simple dependency management - your package
> metadata says "depends on postgresql" and apt-get or yum or whatever
> will do the work), and it's quite common for a Windows installer
> wizard to go through a series of subcomponents (grab the .NET
> framework, grab these hotfixes that the program depends on, grab some
> adware toolbar that you forgot to untick, and *then* install the
> program you asked for).
> 
>> Possible snags:
>>
>> - Possibly postgresql is simply *too big*. Your 50MB(?) Firefox
>>    turns into a 2GB install. I doubt it -- on Debian, postgresql
>>    is 17MB installed. But I don't know what dependencies I'm not
>>    counting.
> 
> Yeah. And frankly, I would be surprised if Firefox is only 50MB these
> days. The extra dent of PostgreSQL won't be all that significant - and
> don't forget that the SQLite3 dent can be removed, so you're talking
> about the difference between them, plus you can omit a whole bunch of
> PG's ancillaries.
> 
>> - Or it is impossible to configure without excessive amounts of
>>    tech-savvy human intervention. Again, I doubt it. I seem to
>>    recall needing to create a Postgresql user and password. But
>>    maybe even that is too technical for the average Firefox user.
> 
> You don't even need to do that. An absolutely default Postgres on
> Debian or Ubuntu is ready to use, with peer authentication. If you can
> become root, you can then drop privs to the 'postgres' user and
> connect to the database that way.
> 
> I don't know if it's possible to do a non-root installation of
> PostgreSQL, but if it isn't today, it could easily be tomorrow, if
> someone puts in a little effort. You'd miss out on boot-time startup,
> and it'd probably have to do some password-based authentication (with
> autogenerated passwords), but it certainly could work. The Windows
> equivalent ("install for just me") is, I think, already possible.
> 
>> - Maybe there are nasty interactions between Postgresql listening
>>    on some port and Windows firewall wanting to block that same port.
> 
> That's definitely an issue, given that Windows doesn't have Unix
> domain sockets. But I'm sure it's a solvable problem. How does IDLE
> cope with firewall issues?

There's an awful lot of "could"s, "possible"s etc in your 
and Steven's responses.  And most of those words apply to
issues that aren't problems at all when using Sqlite.

I'll point out that five people in this thread (by my 
count) have said that Postgresql requires a significant 
amount of work to setup and use.  Only you and Steven claim
the opposite.  (And it sounds to me like Steven does not 
have a lot of experience installing, configuring and 
maintaining Postgresql -- please correct me if I am wrong 
Steven.)

I've used Postgresql for a number of small to medium size 
projects for work and personal use.  I too think it is an 
amazing piece of work for free  software.  Consequently I 
used it for a small educational app I developed for my 
personal use.  A number of friends saw it, liked it and 
asked about using it themselves.  Unfortunately none of 
these friends were at all technical and the barrier of 
installing and configuring Postresql would have involved 
me in trying to talk them through it on the phone (risky 
as there were OSs I was unfamiliar with involved) or do 
it myself in person (one of them was 2000mi away) or do 
it remotely (again a cost in coming up with and installing 
remote desktop software on different OSs and non-technical 
doubts about its use ("I don't mind you doing this while 
I'm siting beside you but remote access is a different 
story").  Then there are the ongoing maintenance issues 
I mentioned in my last reply to Steven.

None of these would have been impossible to overcome with
more effort and expense.  Or I could have tried as you 
and Steven suggest to "somehow" package Postgresql in my 
app installer.  That would have been an even bigger cost 
in my time with an uncertain outcome.  None of those 
costs would have been necessary at all had I developed 
a self-contained Sqlite app.

Finally keep in mind that if you develop your app using
Sqlite, it is likely to be far easier to migrate to
a heavy-duty backend like Postgresql later should you 
need to than to go in the other direction when you find 
out you didn't really need Postgresql after all and the 
cost turned out to be higher than you expected.

Postresql is a wonderful tool when used where its features 
are needed.  It is not so wonderful used when a less heavy-
weight solution is can provide the necessary functionality.
To insist on using Postgresql in such circumstances is 
technical masturbation.

It is bad advise to recommend using Postgresql without 
regard to the developer's actual needs.

[toc] | [prev] | [next] | [standalone]

#85934 — Re: 'Lite' Databases (Re: sqlite3 and dates)

On Fri, Feb 20, 2015 at 7:26 AM,  <rurpy@yahoo.com.dmarc.invalid> wrote:
> I'll point out that five people in this thread (by my
> count) have said that Postgresql requires a significant
> amount of work to setup and use.  Only you and Steven claim
> the opposite.  (And it sounds to me like Steven does not
> have a lot of experience installing, configuring and
> maintaining Postgresql -- please correct me if I am wrong
> Steven.)

I don't know about Steven, but I do have a certain amount of
experience installing, configuring, and maintaining; and the biggest
thing I've noted about the "configuring" part is that you can ignore
it most of the time. The docs caution you that the defaults are
deliberately NOT set for maximum performance... but you won't normally
need maximum performance initially anyway. (In any case, "maximum
performance" requires a lot of knowledge of workloads, resource
availability, concurrency model, etc.) The defaults are usually good
enough; the most common changes I've needed to make to Postgres
configs are:

1) Weakening the default security model of listening only on
localhost, to allow other computers on the network to connect to the
database. Obviously it's correct for the default to be secure, here;
anyone who wants to allow remote access will need to be aware of what
s/he is doing anyway, so requiring that a couple of files be edited
isn't a big deal.

2) Increasing logging, eg turning on the logging of all queries. Not
particularly common, but can be handy; and it's a trivial edit anyway.

3) Permitting certain special-purpose users to bypass the normal login
mechanisms, eg for scripted backups. Similarly to #1, the defaults are
absolutely correct, and if you want to weaken your security, it's only
fitting that you have to go in and edit some configs.

Given that I frequently don't need to do _any_ of these, it's safe to
say that configuring PostgreSQL doesn't take a lot of effort at all.
Similarly, "maintaining" isn't really a thing; apart from maintaining
my own data tables (cleaning out junk tables that were for one quick
test, which I often forget to drop when I'm done with them), I don't
have to spend any effort making sure the database keeps running. (In
contrast, a busy MySQL server probably needs to have myisamchk run on
it periodically, which *does* count as maintenance.)

So while it makes sense to talk about *experience* "installing,
configuring, and maintaining", it's really just the installing part
that actually demands effort. And that can be mitigated in a few ways,
but ultimately, there's no single simple solution for everything. Even
SQLite3 has issues with that - as soon as you have two programs that
try to read the same file (eg your application, using Python's module,
and the sqlite3 command line tool), you have version differences, and
quite a number of my students have come to me asking why on earth they
can't upgrade their SQLite3 properly. (Though usually both versions
are "new enough", so it doesn't matter. It's just a point of
confusion.)

ChrisA

[toc] | [prev] | [next] | [standalone]

#85941 — Re: 'Lite' Databases (Re: sqlite3 and dates)

On 02/19/2015 01:47 PM, Chris Angelico wrote:
> On Fri, Feb 20, 2015 at 7:26 AM,  <rurpy@yahoo.com.dmarc.invalid> wrote:
>> I'll point out that five people in this thread (by my
>> count) have said that Postgresql requires a significant
>> amount of work to setup and use.  Only you and Steven claim
>> the opposite.  (And it sounds to me like Steven does not
>> have a lot of experience installing, configuring and
>> maintaining Postgresql -- please correct me if I am wrong
>> Steven.)
> 
> I don't know about Steven, but I do have a certain amount of
> experience installing, configuring, and maintaining;

Right.  I am aware of that as you might have guessed by the 
fact that I directed my question to Steven and not to you. :-)

> [...] the most common changes I've needed to make to Postgres
configs are:
>[...]
> 2) Increasing logging, eg turning on the logging of all queries. Not
> particularly common, but can be handy; and it's a trivial edit anyway.

It may be trivial to you but it is not going to be trivial to
many of end users the kind of apps we're talking about.  One
of the people interested in my little app was at the limits of her
ability when it came to finding a PowerPoint file she'd just been
editing so she could attach it to an email.  So if the logging 
level needs changing, the app better be prepared to do it itself.

>[...]
> So while it makes sense to talk about *experience* "installing,
> configuring, and maintaining", it's really just the installing part
> that actually demands effort. 

No, see below.

> And that can be mitigated in a few ways,
> but ultimately, there's no single simple solution for everything.

That's right but misses the obvious: some solutions are simpler 
than others.  Installing a Postgresql client-server database and 
a Python application is inherently more complex than installing 
a Python application (Sqlite being included in Python).  (Doesn't
that seem rather obvious, even to you?)

And, if using Postgresql is as easy as you claim, surely you 
don't expect to reserve its use only to your applications?  
So we could expect to see many applications, potentially all 
those currently using Sqlite, to start using Postgresql.

Do they all install and run their own independent server?  Each
with its own set of more than a half dozen processes running all
the time?  Each that has to be started up at system boot?  Each
with their own directory of Postgresql software and backend data
directory?  Where do these files all go and how do app developers 
agree on some standard to avoid chaos?  How does each app find a 
network port or socket that doesn't conflict with the others or 
with things the machine owner may run?  For a Sqlite app you are
installing files for which the rules are all pretty well established.

And if the Postresql apps share a server who arbitrates when 
app X decides it needs to set some parameter that breaks app Y?
Or change anything else on the server; things shared in common
by all the apps like users or (as you yourself mentioned) log 
file settings?

The complexities you deny exist do exist and have to be dealt 
with somewhere.  Either you, the developer have to handle them
in the installation and anticipate problems like "Install error:
/var/lib/pgsql/data: already exists" or "data directory contain 
wrong version of Postresql" or you shove the task off onto your 
users in which case they will have to have sufficient expertise 
to deal with them.

Install problems are not of course limited to server software,
they occur with simple Python apps too.  But installing a single
Python app that depends on the version of Sqlite in the minimally
supported version of Python from a self-contained installer will 
have far lower probability of problems than an automated install 
of server software in a very uncontrolled environment.  And whatever
that app install failure probability is, requiring server software 
in addition can only increase it.

> Even
> SQLite3 has issues with that - as soon as you have two programs that
> try to read the same file (eg your application, using Python's module,
> and the sqlite3 command line tool), you have version differences, and
> quite a number of my students have come to me asking why on earth they
> can't upgrade their SQLite3 properly. (Though usually both versions
> are "new enough", so it doesn't matter. It's just a point of
> confusion.)

Irrelevant.  As the application designer you are responsible for 
what you provide (the application and its backend database).  If 
I want to obtain a command line tool somewhere, it's my problem 
to worry about versions -- it has nothing to do with you.

If you mean that you as a developer have to worry about not shipping
two incompatible versions of app code, well... yeah.  I think that's
pretty much a standard part of putting together a working application.

Or is there some other point I missed?

As for no maintenance, I mentioned two common maintenance activities 
in my reply to Steven: backups and server upgrades.

[toc] | [prev] | [next] | [standalone]

#85971 — Re: 'Lite' Databases (Re: sqlite3 and dates)

On Thu, 19 Feb 2015 20:20:42 -0800 (PST), rurpy@yahoo.com.dmarc.invalid
declaimed the following:

>
>Do they all install and run their own independent server?  Each
>with its own set of more than a half dozen processes running all
>the time?  Each that has to be started up at system boot?  Each
>with their own directory of Postgresql software and backend data
>directory?  Where do these files all go and how do app developers 
>agree on some standard to avoid chaos?  How does each app find a 
>network port or socket that doesn't conflict with the others or 
>with things the machine owner may run?  For a Sqlite app you are
>installing files for which the rules are all pretty well established.
>
>And if the Postresql apps share a server who arbitrates when 
>app X decides it needs to set some parameter that breaks app Y?
>Or change anything else on the server; things shared in common
>by all the apps like users or (as you yourself mentioned) log 
>file settings?
>
>The complexities you deny exist do exist and have to be dealt 
>with somewhere.  Either you, the developer have to handle them
>in the installation and anticipate problems like "Install error:
>/var/lib/pgsql/data: already exists" or "data directory contain 
>wrong version of Postresql" or you shove the task off onto your 
>users in which case they will have to have sufficient expertise 
>to deal with them.
>

	The middle-ground is probably something like the embedded version of
Firebird (pity there has been no updated book -- "The Firebird Book" came
out in 2004, v1.5 while 2.5 is current [Whoops, looks like there /is/ an
update, print-on-demand in three overpriced volumes])
-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
    wlfraed@ix.netcom.com    HTTP://wlfraed.home.netcom.com/

[toc] | [prev] | [next] | [standalone]

#86023 — Re: 'Lite' Databases (Re: sqlite3 and dates)

Am 20.02.2015 um 15:16 schrieb Dennis Lee Bieber:
>
> 	The middle-ground is probably something like the embedded version of
> Firebird (pity there has been no updated book -- "The Firebird Book" came
> out in 2004, v1.5 while 2.5 is current [Whoops, looks like there /is/ an
> update, print-on-demand in three overpriced volumes])
>
The second edition as PDF EBook is part of the IBPhoenix DVD (Developer 
Edition $100). But I can't find out from the website what that DVD 
contains in its current edition. It used to have Firebird itself and 
quite a lot of additional software and documentation.

Firebird embedded has no network access and multi-user capabilities at 
all if I read the documentation correctly 
(http://www.firebirdsql.org/manual/ufb-cs-embedded.html). But "A 
Firebird embedded server DLL can also be used as a network client" to a 
regular Firebird server (from the same page). On the other hand triggers 
and stored procedures should work exactly like the client/server 
versions (and the procedure language is pretty similar to PostgreSQL, so 
migration between these two isn't too difficult).

So this is a middle ground with other pros and cons than SQLite versus 
PostgreSQL.

[toc] | [prev] | [next] | [standalone]

#86060 — Re: 'Lite' Databases (Re: sqlite3 and dates)

On Sat, 21 Feb 2015 11:44:24 +0100, Sibylle Koczian <nulla.epistola@web.de>
declaimed the following:

>>
>The second edition as PDF EBook is part of the IBPhoenix DVD (Developer 
>Edition $100). But I can't find out from the website what that DVD 

	I'll pay the $120 for the POD... Considering I spent $800 at FedEx
having a ton of TIVA/ARM PDFs printed and "bound" (I miss Kinko's, were the
binding choices included things that didn't use bulky rings or spirals).

>
>Firebird embedded has no network access and multi-user capabilities at 
>all if I read the documentation correctly 

	No, but as a low-maintenance install, it may be useful... I'd probably
recommend making the "client" with it embedded behave as the server, and
provide network only clients to contact this application... No multiple
clients directly accessing the database itself.
-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
    wlfraed@ix.netcom.com    HTTP://wlfraed.home.netcom.com/

[toc] | [prev] | [next] | [standalone]

#85938 — Re: 'Lite' Databases (Re: sqlite3 and dates)

On Thu, 19 Feb 2015 12:26:04 -0800 (PST), rurpy@yahoo.com wrote:
>
>I'll point out that five people in this thread (by my 
>count) have said that Postgresql requires a significant 
>amount of work to setup and use.  Only you and Steven claim
>the opposite. 

Well, I claim the opposite too.

>Or I could have tried as you 
>and Steven suggest to "somehow" package Postgresql in my 
>app installer.  That would have been an even bigger cost 
>in my time with an uncertain outcome.

I don't see how. You said it was just a simple application this
postgres database was serving. So I must assume you aren't talking of
a complex setup that database synchronization or other features that
force a manual setup.

For your purposes, you could just basically backup your data folder
and package postgres with the same configuration files you have in
your current computer.

>None of those 
>costs would have been necessary at all had I developed 
>a self-contained Sqlite app.

The cost would have probably been much higher, depending on your
project. SQLite would have forced you to move all your business logic
into your code, greatly increasing code maintenance, your application
extensibility and its ability to more easily adapt to new business
requirements.

Conversely, if none of this is true concerning your particular
project, then you just chose the wrong tool. Postgres was overkill for
your particular needs and it was a mistake to think you need it to
function just as a shelve on steroids.

>
>Finally keep in mind that if you develop your app using
>Sqlite, it is likely to be far easier to migrate to
>a heavy-duty backend like Postgresql later should you 
>need to than to go in the other direction when you find 
>out you didn't really need Postgresql after all and the 
>cost turned out to be higher than you expected.

Completely not true! For the reasons mentioned above. You are
concentrating too much on the RDBMS aspects and completely forgetting
about the implications in your codebase.

Whether you move from a non distributed model to a client-server
model, or the other way around, your code will suffer major changes.
And even if you decide to keep the business logic in the client layer
(which is a mistake) when moving from SQLite to a client-server RDBMS,
you will still have to deal with a whole new set of issues regarding
the very nature of concurrent access that will essentially force you
to scrap much of your previous code.

>It is bad advise to recommend using Postgresql without 
>regard to the developer's actual needs.

Naturally. But I must say postgres isn't the bad cat you painted in
your post. It's much, much easier to distribute, deploy and manage
than you are suggesting.

For most systems where performance and database synchronization aren't
a requirement, it can be entirely automated, I know, because that's
how we had it set up on three schools where we sold our integrated
management system. I haven't had a maintenance request call in 8
months.

It's only under critical requirements that postgres necessitates a
baby sitter. And those projects don't suffer from lack of competent
administrators.

[toc] | [prev] | [next] | [standalone]

#85942 — Re: 'Lite' Databases (Re: sqlite3 and dates)

On 02/19/2015 02:23 PM, Mario Figueiredo wrote:
> On Thu, 19 Feb 2015 12:26:04 -0800 (PST), rurpy@yahoo.com wrote:
>>
>> I'll point out that five people in this thread (by my
>> count) have said that Postgresql requires a significant
>> amount of work to setup and use.  Only you and Steven claim
>> the opposite.
> 
> Well, I claim the opposite too.
> 
>> Or I could have tried as you
>> and Steven suggest to "somehow" package Postgresql in my
>> app installer.  That would have been an even bigger cost
>> in my time with an uncertain outcome.
> 
> I don't see how. You said it was just a simple application this
> postgres database was serving. So I must assume you aren't talking of
> a complex setup that database synchronization or other features that
> force a manual setup.

Correct.

> For your purposes, you could just basically backup your data folder
> and package postgres with the same configuration files you have in
> your current computer.

What do you mean by "data folder"?  The directory in which 
Postgresql keeps its database data?  If so you're wrong.  That 
data is at a minimum architecture dependent.  It is also private  
to Postgresql and one would be ill advised to use that as a 
distribution format.  There is also no need to since installing
Postgresql and loading the initial data from some standard format 
is supported and will produce the same results. 

Or are you talking about some sort of data (csv files, sql files
of insert statements, etc) that you use to initially load tables
in your database.  That will be the same whether you are using
Postgresql or Sqlite so I don't see your point. 

>> None of those
>> costs would have been necessary at all had I developed
>> a self-contained Sqlite app.
> 
> The cost would have probably been much higher, depending on your
> project. SQLite would have forced you to move all your business logic
> into your code, greatly increasing code maintenance, your application
> extensibility and its ability to more easily adapt to new business
> requirements.

How so?  nobody's claimed that Sqlite is a replacement for 
Postgresql in large scale, high concurrency "heavy-duty" 
applications.  Ethan Furman posted a list of things that 
Sqlite does well from the Sqlite website.  Here is a link 
if you missed it:

  http://www.sqlite.org/whentouse.html

We are talking about using Postgresql as a backend for 
applications that fit on that list, ie applications like 
the applications already using Sqlite :-) 

From previous posts I am guessing that what you are saying is 
that business logic should be implemented in stored procedures 
and because Sqlite does not offer stored procedures you can't 
implement business logic in Sqlite.

First, recall that we are not talking about a multi-tiered set 
of applications with a middleware layer and mutiuser backend.
We've established already that is the domain of servers like
Postgresql.

While I tend to agree with the idea that business logic 
should be in the database, I'm sure you're aware that that 
is not a universally held opinion and it is certainly not 
a universally implemented one in the domain of applications
we're talking about.  It is easy to find plenty of applications 
that implement all or part of the business logic in the app.  

Given Sqlite's architecture I'm not even sure you can say
it doesn't have stored procedures.  Since programmatic 
access is through the (python-)API you can write a module 
with all the business logic and decree that that all higher
level functions access the database through that module.
How is that effectively different than a set of stored
procedures in a client-server database?

And I can see a justification for not even going that far 
in some cases.  We are talking about small scale applications 
where the database can be considered an "implementation 
detail" of the application but it is easier to implement 
the application by taking advantage of the capabilities of 
a SQL relational database than trying to implement those 
storage details in some ad-hoc way.

> Conversely, if none of this is true concerning your particular
> project, then you just chose the wrong tool. Postgres was overkill for
> your particular needs and it was a mistake to think you need it to
> function just as a shelve on steroids.

Bingo!  You're catching on.  :-)  Again I remind you that no one
has said that Sqlite is a universal replacement for Postgresql

>> Finally keep in mind that if you develop your app using
>> Sqlite, it is likely to be far easier to migrate to
>> a heavy-duty backend like Postgresql later should you
>> need to than to go in the other direction when you find
>> out you didn't really need Postgresql after all and the
>> cost turned out to be higher than you expected.
> 
> Completely not true! For the reasons mentioned above. You are
> concentrating too much on the RDBMS aspects and completely forgetting
> about the implications in your codebase.
> 
> Whether you move from a non distributed model to a client-server
> model, or the other way around, your code will suffer major changes.

> And even if you decide to keep the business logic in the client layer
> (which is a mistake) 

Whether it is a mistake or not is not does not have a simple 
yes/no answer.   See above.

> when moving from SQLite to a client-server RDBMS,
> you will still have to deal with a whole new set of issues regarding
> the very nature of concurrent access that will essentially force you
> to scrap much of your previous code.

How so?  We've already determined that Sqlite provides
concurrent access (albeit on a limited scale).  So you've
already had to deal with it in the way you've designed 
the database.

I think you are saying that if you are moving to Postresql
that you will want to restructure your code to take advantage
of Postgreql's greater capabilities.  Sure, but your exiting
existing Sqlite design will still work while you do that
restructure incrementally.  The code will need changes to
adapt from Sqlite to Postgresql but that should be tractable.
The converse (moving from Postgreql back to Sqlite is much 
harder because now you have reimplement those nice features 
of Postgreql that you took advantage of when writing your 
application but that are hard to port to Sqlite.  So your 
app is broken until you do all that work.

>> It is bad advise to recommend using Postgresql without
>> regard to the developer's actual needs.
> 
> Naturally. But I must say postgres isn't the bad cat you painted in
> your post. It's much, much easier to distribute, deploy and manage
> than you are suggesting.

If you think I've been painting Postgresql as a "bad cat" 
I don't think you've been paying attention.  

  On 02/19/2015 01:26 PM, rurpy wrote:
  > I've used Postgresql for a number of small to medium size 
  > projects for work and personal use.  I too think it is an 
  > amazing piece of work for free  software.

What I have said is that there are many use cases for a SQL
relational database that don't need the features of Postgresql
and for which Sqlite is a lighter-weight, less costly alternative
and hence the better choice.  

Saying that something is not appropriate for use in every
possible environment is not the same as saying it's a "bad cat".

> For most systems where performance and database synchronization aren't
> a requirement, it can be entirely automated, I know, because that's
> how we had it set up on three schools where we sold our integrated
> management system. I haven't had a maintenance request call in 8
> months.

I never said it can't be done.  I claim it can't be done
anywhere as near as easily as you can with Sqlite
*if you don't require the capabilities of Postgresql*. 
How long did it take to develop your automated installer
and over how wide a variety of environments does it work?
Three sites, all institutional, is not what I'd call a
a wide variety.  They all have someone (perhaps defacto)
in the role of systems or network administrator?

> It's only under critical requirements that postgres necessitates a
> baby sitter. And those projects don't suffer from lack of competent
> administrators.

And you (or your users) handle database backups and Postgresql 
upgrades how?

[toc] | [prev] | [next] | [standalone]

#85930 — Re: 'Lite' Databases (Re: sqlite3 and dates)

On 02/19/2015 12:07 AM, Steven D'Aprano wrote:
> rurpy@yahoo.com wrote:
>> On 02/18/2015 07:13 PM, Steven D'Aprano wrote:
>>> Chris Angelico wrote:
>>>>> SQLite misses some important features that makes it better suited as a
>>>>> simple datastore, not much unlike shelve. And network use is not one
>>>>> of them, since you can actually implement concurrent sqlite access by
>>>>> coding an intermediate layer. Assuming of course we are talking about
>>>>> a small number of concurrent users.
>>>>
>>>> This is what I was saying: it's fine for purposes like Firefox's
>>>> bookmarks and settings and such (which I think was what it was
>>>> originally developed for?). Not so fine over a network.
>>>
>>> The sheer number of Firefox bugs related to its use of SQLite says
>>> different.
>>>
>>> Once upon a time, Firefox's config, bookmarks, etc. were stored in plain
>>> text files. At worst they were HTML. You could trivially read them, copy
>>> them, restore them and even (if you were careful) edit them using the
>>> text editor of your choice. Many a time I was on one machine, wanted to
>>> know a bookmark from another machine, so I would ssh across to the other
>>> machine and run grep over the bookmark file.
>>
>> I agree, I prefer plain text files whenever practical.  But since
>> the original discussion was about Sqlite vs Postgresql, not Sqlite
>> vs text files, shouldn't the question be: would Firefox be better
>> if it required you to install and configure Postgreql instead of
>> using Sqlite?
> 
> Very possibly. With modern dependency management, it isn't hard to install
> Postgresql:
> 
> sudo aptitude postgresql
> 
> or equivalent should work.

And the equivalent for Android would be?  Even with Windows, 
Mac and Linux I don't imagine they want to be in the business 
of tracking what package managers are used by what OSes and 
OS version and dealing with the inevitable problems that
will arise, particularly with a userbase that is 99.9% non-
technical.

Compare with Sqlite which require zero end-user involvement 
and puts the management in one place, in-house, and under 
control of the developers.

> For primitive operating systems with no
> dependency management available, Firefox could come with a simple script
> which downloads, installs, configures and runs Postgresql. (Everything is
> simple for the guy who doesn't have to do it.)

Right.  The key is in the last sentence above.  

> Possible snags:
> [...]
> 
> - Or it is impossible to configure without excessive amounts of
>    tech-savvy human intervention. Again, I doubt it. I seem to
>    recall needing to create a Postgresql user and password. But
>    maybe even that is too technical for the average Firefox user.

You want hands-off for non-technical users and hands-on for
technical ones.  The last thing I want is yet another database 
server running -- if I want FF to use Postgresql, I want it 
to use a database in my existing server for which I already 
have management and backup/recovery procedures established.
So now you have to ask me if I want to use an existing server 
or not and if so what its connection details are.  And this is 
not per-install but per profile creation.

My point is not that this is an insoluble problem -- just that 
it is one of many such problems that take some non-trivial amount 
of time recognize and to address -- time which is not necessary 
when using Sqlite.  

> - Maybe there are nasty interactions between Postgresql listening
>    on some port and Windows firewall wanting to block that same port.

The attack surface for any application that has a network port
is vastly greater that one that uses only filesystem apis like
sqlite.  So you've now just undertaken to be responsible to a 
much greater degree for the security of my machine.  FF does 
already have a bucketful of security issues but as a client, 
not as a server.

And what about backing up all that data in the wonderful, 
featureful database you just installed for me?  As you know, 
you can't just include the postresql data files in a file 
system backup.

And what about upgrades to the server?  Are you going to develop
your own upgrade infrastructure, or are you going to tell me to
use the standard Postgresql upgrade methodology (which involves,
in simple cases, dumping the data with pg_dumpall, doing the
upgrade, and restoring with pg_restore_all).  If the latter,
I'm sure that will be really popular with non-technical users.
Of course with sqlite, you just ship a new shared library file 
with your new version of Firefox. 

> Or... and here is a radical thought... maybe Firefox could give you the
> choice of which database? By default, it might use Sqlite, to satisfy the
> desktop users who don't want to think about it. And for those who are
> disturbed by the fragility of Sqlite on a network home directory, you just
> set a config setting in about:config to point at your existing Postgresql
> instance, and never need worry about it again.

Yes, I personally would like that a lot.  But I'm not going to 
stay up at night waiting for it since the number of people like 
me and you are a minuscule fraction of FFs userbase.  There is 
next to no incentive for the Mozilla devs to include such an 
esoteric feature that benefits so few users and has a high cost 
(compared to Sqlite) to add and maintain.

> The Firefox devs surprise and confuse me. On the one hand, they have
> designed a powerful plug-in architecture, and encourage their user-base to
> use it for all sorts of amazing functionality that they don't want to build
> into the core browser. Yay for this. And on the other hand, they are
> *actively hostile* to any suggestion that using SQlite is not the best and
> *only* appropriate solution to the problem of storing config, bookmarks and
> history. A plug-in database architecture would probably work really well.
> 
>> I don't see any evidence that it is Sqlite that is the problem
>> as opposed to FF's use (or misuse) of it, or other problems that
>> are in FF and have nothing to do with Sqlite.
> 
> No no, even Sqlite devs recommend against using it on network drives. 

Yes, you are correct.  I realized that after I posted 
but I decided getting some sleep was more important than 
correcting myself.

> The
> Firefox problem is that when FF crashes, as it can do, or if you yank the
> power to the computer and everything dies, if your home directory is on a
> network drive, the database may be left in a locked state, or even
> corrupted. Nothing that the FF developers can do, given the choice of
> Sqlite.
>
>> If Sqlite reliably
>> implements ACID semantics as they claim,
> 
> Ah, well "reliably" is a tricky word...
> 
> http://stackoverflow.com/questions/788517/sqlite-over-a-network-share

Thanks for that reference.  Now that I know that someone 
named "anon" out on the internet thinks that file-based 
database on shared drives are unreliable, I will definitely 
add that to my "facts" draw. :-)

Seriously, I don't dispute the basic claim but how about 
some credible specifics rather than FUD?  Since you don't 
supply any I will make an attempt:

To reliably implement the D (durable) in ACID, the database 
has to be able to know when data has been permanently written 
to disk.  I don't know if there are wan shared file systems
whose protocols include such notification but I suspect not 
many to none do.  Thus, placing data on a shared filesystem 
is a problem for any database, this is not Sqlite specific.  
Try putting your Postgresql data directory on a remote NFS 
share and let us know how it works out for you.

So again, I don't see how this is specifically Sqlites's 
fault.

I can see your point questioning FF's use of Sqlite but the 
idea of replacing it with Postgresql I think is just downright 
nutty.

[toc] | [prev] | [next] | [standalone]

#85850 — Re: 'Lite' Databases (Re: sqlite3 and dates)

On 02/18/2015 05:08 PM, Mario Figueiredo wrote:
>[...]
> SQLite misses some important features that makes it better suited as a
> simple datastore, not much unlike shelve. And network use is not one
> of them, since you can actually implement concurrent sqlite access by
> coding an intermediate layer. Assuming of course we are talking about
> a small number of concurrent users.

I think there are some persistent misunderstandings about Sqlite
in this thread,  Sqlite offers concurrent access already.  
What Sqlite doesn't offer is high performance concurrent write
access.  That is, it locks the entire database for the duration 
of a write operation.  Given that most such operations are pretty
short, for a small number of concurrent writers this is not a 
big problem.

[toc] | [prev] | [next] | [standalone]

#85924 — Re: 'Lite' Databases (Re: sqlite3 and dates)

On 2015-02-18 20:05, rurpy@yahoo.com.dmarc.invalid wrote:
> Sqlite offers concurrent access already.  
> What Sqlite doesn't offer is high performance concurrent write
> access.  That is, it locks the entire database for the duration 
> of a write operation.  Given that most such operations are pretty
> short, for a small number of concurrent writers this is not a 
> big problem.

Though the entire-database-lock, as Steven mentions in another
section of this thread, causes issues on network-shared file-systems.

-tkc

[toc] | [prev] | [next] | [standalone]

#85865 — Re: 'Lite' Databases (Re: sqlite3 and dates)

Mario Figueiredo wrote:
> Parameterized queries is just a pet peeve of mine that I wish to
> include here. SQLite misses it

How does sqlite3 miss parameterized queries? It supports
DB-API parameter subsitution with '?' according to the
docs.

-- 
Greg

[toc] | [prev] | [next] | [standalone]

#85875 — Re: 'Lite' Databases (Re: sqlite3 and dates)

On Thu, 19 Feb 2015 18:22:57 +1300, Gregory Ewing
<greg.ewing@canterbury.ac.nz> wrote:

>
>How does sqlite3 miss parameterized queries? It supports
>DB-API parameter subsitution with '?' according to the
>docs.

It's actually parameterized views that I meant. Not queries. SQLite
misses the ability to write parameterized views.

[toc] | [prev] | [next] | [standalone]

#85868 — Re: 'Lite' Databases (Re: sqlite3 and dates)

On 19/02/2015 00:08, Mario Figueiredo wrote:
>
> Parameterized queries is just a pet peeve of mine that I wish to
> include here. SQLite misses it and I miss the fact SQLite misses it.
> The less SQL one needs to write in their code, the happier one should
> be.
>

https://docs.python.org/3/library/sqlite3.html#module-sqlite3 paragraphs 
seven and eight.

"Usually your SQL operations will need to use values from Python 
variables. You shouldn’t assemble your query using Python’s string 
operations because doing so is insecure; it makes your program 
vulnerable to an SQL injection attack (see http://xkcd.com/327/ for 
humorous example of what can go wrong).

Instead, use the DB-API’s parameter substitution. Put ? as a placeholder 
wherever you want to use a value, and then provide a tuple of values as 
the second argument to the cursor’s execute() method. (Other database 
modules may use a different placeholder, such as %s or :1.) For example:..."

-- 
My fellow Pythonistas, ask not what our language can do for you, ask
what you can do for our language.

Mark Lawrence

[toc] | [prev] | [next] | [standalone]

#85903 — Re: 'Lite' Databases (Re: sqlite3 and dates)

On 2015-02-19 05:32, Mark Lawrence wrote:
> On 19/02/2015 00:08, Mario Figueiredo wrote:
> > Parameterized queries is just a pet peeve of mine that I wish to
> > include here. SQLite misses it and I miss the fact SQLite misses
> > it. The less SQL one needs to write in their code, the happier
> > one should be.
> 
> Instead, use the DB-API’s parameter substitution. Put ? as a
> placeholder wherever you want to use a value, and then provide a
> tuple of values as the second argument to the cursor’s execute()
> method. (Other database modules may use a different placeholder,
> such as %s or :1.) For example:..."

I think Mario was referring to what other back ends call prepared
statements. So you do something like

  
  sql = "..." # parameters are referenced here
  conn = sqlite3.connect(...)
  stmt = conn.prepare(sql)
  for parameters in list_of_parameters:
    stmt.execute(*parameters)

This saves the SQL processor from recompiling the SQL into internal
byte-code every time.  It's handy if you know a given query will run
multiple times with the same "shape" parameters.  It's not essential,
and some optimize away the need, but many back-end interfaces support
it.

-tkc

[toc] | [prev] | [next] | [standalone]

#85905 — Re: 'Lite' Databases (Re: sqlite3 and dates)

On 19/02/2015 14:17, Tim Chase wrote:
> On 2015-02-19 05:32, Mark Lawrence wrote:
>> On 19/02/2015 00:08, Mario Figueiredo wrote:
>>> Parameterized queries is just a pet peeve of mine that I wish to
>>> include here. SQLite misses it and I miss the fact SQLite misses
>>> it. The less SQL one needs to write in their code, the happier
>>> one should be.
>>
>> Instead, use the DB-API’s parameter substitution. Put ? as a
>> placeholder wherever you want to use a value, and then provide a
>> tuple of values as the second argument to the cursor’s execute()
>> method. (Other database modules may use a different placeholder,
>> such as %s or :1.) For example:..."
>
> I think Mario was referring to what other back ends call prepared
> statements. So you do something like
>
>
>    sql = "..." # parameters are referenced here
>    conn = sqlite3.connect(...)
>    stmt = conn.prepare(sql)
>    for parameters in list_of_parameters:
>      stmt.execute(*parameters)
>
> This saves the SQL processor from recompiling the SQL into internal
> byte-code every time.  It's handy if you know a given query will run
> multiple times with the same "shape" parameters.  It's not essential,
> and some optimize away the need, but many back-end interfaces support
> it.
>
> -tkc
>

Is this 
https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.executemany an 
equivalent?

-- 
My fellow Pythonistas, ask not what our language can do for you, ask
what you can do for our language.

Mark Lawrence

[toc] | [prev] | [next] | [standalone]

#85907 — Re: 'Lite' Databases (Re: sqlite3 and dates)

On Fri, Feb 20, 2015 at 2:04 AM, Mark Lawrence <breamoreboy@yahoo.co.uk> wrote:
>> This saves the SQL processor from recompiling the SQL into internal
>> byte-code every time.  It's handy if you know a given query will run
>> multiple times with the same "shape" parameters.  It's not essential,
>> and some optimize away the need, but many back-end interfaces support
>> it.
>>
>> -tkc
>>
>
> Is this
> https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.executemany an
> equivalent?

That's a different feature, and also useful. Personally, I've never
used executemany() for anything other than INSERT statements, though I
can imagine using it equally for UPDATE. It's useful only when you
have a bulk lot to do all at once; you can't take advantage of it to
repeat a common and complex query. Imagine you run a web server that
shows some statistical information about your session, on every page;
this may require a complex query, which you could retain from one page
request to another. But that only matters to performance.

ChrisA

[toc] | [prev] | [next] | [standalone]

#85913 — Re: 'Lite' Databases (Re: sqlite3 and dates)

On 2015-02-19 15:04, Mark Lawrence wrote:
> On 19/02/2015 14:17, Tim Chase wrote:
>>>> Parameterized queries is just a pet peeve of mine that I wish to
>>>> include here. SQLite misses it and I miss the fact SQLite misses
>>>> it. The less SQL one needs to write in their code, the happier
>>>> one should be.
>>>
>>> Instead, use the DB-API’s parameter substitution. Put ? as a
>>> placeholder wherever you want to use a value, and then provide a
>>> tuple of values as the second argument to the cursor’s execute()
>>> method. (Other database modules may use a different placeholder,
>>> such as %s or :1.) For example:..."
>>
>> I think Mario was referring to what other back ends call prepared
>> statements.
> 
> Is this 
> https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.executemany
> an equivalent?

Depends on whether sqlite3.Cursor.executemany() uses

https://www.sqlite.org/c3ref/stmt.html

under the hood.

-tkc

[toc] | [prev] | [next] | [standalone]

Page 2 of 4 — ← Prev page 1 [2] 3 4  Next page →

Back to top | Article view | comp.lang.python

csiph-web