I use both PostgreSQL and SQLite in my job. I
have mixed feelings about SQLite. If you play by its rules, it works very
well, but I think you have to understand its rules well. If you do not set
up your indexes correctly and do not use transactions correctly, performance can
be horrible, but if you do, its performance is excellent. For some reason
I doubt I will ever understand, its developer thinks that it is a good thing not
to require a column to hold any particular type of information. One record
can have an integer in a field, while the next record in the table can have a
string in the same field. I would much rather have my database enforce
type consistency, and tell me when I'm screwing up. There is no fixed date
format. I suddenly found that one program that used SQLite began writing
dates as human-readable text strings for no reason I could understand, after it
had been writing them as Julian dates (a floating-point number representing the
number of days since a given date) happily for years. I had to rewrite the
corresponding program that reads the data to be able to handle either strings or
Julian dates correctly and transparently, which was not
easy.
But it is nice to have SQLite available for use in
programs that will be installed at multiple customer sites where we can't be
sure if the main database will be PostgreSQL, SQL Server, Oracle, or something
else.
RobR
