'Lite' Databases (Re: sqlite3 and dates)

[rurpy at yahoo.com]

On 02/19/2015 12:07 AM, Steven D'Aprano wrote:
> rurpy at 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.