alternatives to Firebird: sqlite [was: Firebird doesn't support MSVC 2015]

[Lionel Elie Mamane]

On Mon, Mar 07, 2016 at 08:23:53AM +0200, Noel Grandin wrote:

> In the space of open-source SQL database engines, SQLite is far and
> away the most widely used and supported option.

> Maybe we should try using that instead?

A few examples of how SQLite differs from the SQL standard (be it de facto
or de jure standard) in quite deep, fundamental ways:

 * lack of other datatypes than "integer", "real" (float), "string of characaters"
   and "string of bytes": the basic ones are dates, times, timestamps.

 * lack of datatype checking; one can insert "noel" in a column of
   type "integer"

 * type attached to value, not to column

These can be worked around in an application that is written
specifically for SQLite, but are thorns in the case of an application
(like LibreOffice) that targets the "general SQL standard / de facto
agreement", and *wants* rely on the DBMS for error-checking.

Also annoying, but can be worked around:

 * lack of "RIGHT JOIN"

It might not be obvious why lack of "RIGHT JOIN" is
annoying, since:

 1) one can switch the order of tables, and right to left

 2) the above switch can be done, since SQLite supports parenthesised
    joins [which is not guaranteed by the basic SQL standard]

The answer lies in compatibility with other databases, that support
LEFT and RIGHT, but not parenthesised joins. We could have a
compatibility flag that says "do things the first way vs do them the
second way". We already have some flags like that :)

I haven't even started talking about missing features, like e.g. FULL
OUTER JOIN.

<troll>Or bite the GPL bullet and embed MariaDB.</troll>