Re: sqlite3

[Teemu Likonen]

* 2021-12-07 07:54:23+0100, Lars Ingebrigtsen wrote:

> Teemu Likonen <tlikonen@iki.fi> writes:
>
>> I think Emacs users would want automatic VACUUM so that they never need
>> to worry about it. (1) One option for that is to set SQLite's
>>
>>     PRAGMA auto_vacuum = FULL;
>>
>> before any tables are created in database.
>
> Thanks; that seems like an easy enough solution, so I've added it to
> sticky.el.

That should be enough. In this early stage of development (and without
seeing the code myself) I would also suggest or remind of adding a
metadata table and version number of the database structure, like this:

    CREATE TABLE metadata (key TEXT PRIMARY KEY NOT NULL,
                          intvalue INTEGER,
                          textvalue TEXT);

    INSERT INTO metadata (key, intvalue) VALUES ('version', 1);

In my experience there will _always_ come a need to change the database
structure. A version number metadata makes it easier to perform upgrades
correctly. Other metadata may be needed later too.

In SQL standard, PRIMARY KEY implies NOT NULL, but this is not the case
in SQlite. It has to be added explicitly, thus PRIMARY KEY NOT NULL (see
above).

-- 
/// Teemu Likonen - .-.. https://www.iki.fi/tlikonen/
// OpenPGP: 6965F03973F0D4CA22B9410F0F2CAE0E07608462

signature.asc
Description: PGP signature