Report on trying out the SQLite backend

[Daniel Vrátil]

Hi Erik,

On Wednesday, 9 January 2019 14:33:20 CET Erik Quaeghebeur wrote:
> Dear List,
> 
> 
> As a trial, I changed to the SQLite backend ("Driver=QSQLITE3" in
> ~/.config/akonadi/akonadiserverrc). This post is to report on my experience
> and also to perhaps get a reaction to some things from a developer or
> database expert.
> 
> Even though Pablo disadvised using SQLite, until now on the whole akonadi
> has behaved promptly and without losing data. (Sizes: ResourceTable: 10;
> CollectionTable: 57; PimItemTable: 47015; PartTable: 129844.) I did need to
> recreate my DAV resources to get them to do the initial sync. The initial
> sync and indexing of my mail from IMAP (~500 MB, ~40000 messages) took on
> the order of half an hour. (I don't exclude the possibility that I bump
> into the performance issues Pablo hinted at after a longer period.)

Nice, good to hear that it works fairly well.

I tried running my multi-account setup with almost 1 million emails on SQLite, 
and the performance was acceptable (considering the size), although it put a 
lot of strain on my SSD :) (I'm back to Postgres, though, I can't wait 5 
minutes for my inbox to open every time :-))

> 
> I had a look at the database schema and I had the impression no foreign key
> constraints <https://www.sqlite.org/syntax/foreign-key-clause.html> were
> used. So, e.g., instead of
> 
> CREATE TABLE PimItemTable (
> 	id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
> 	rev INTEGER NOT NULL DEFAULT 0,
> 	remoteId TEXT,
> 	remoteRevision TEXT,
> 	gid TEXT,
> 	collectionId BIGINT,
> 	mimeTypeId BIGINT,
> 	datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
> 	atime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
> 	dirty BOOL,
> 	size BIGINT NOT NULL DEFAULT 0
> )
> 
> I would have expected something like
> 
> CREATE TABLE PimItemTable (
> 	id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
> 	rev INTEGER NOT NULL DEFAULT 0,
> 	remoteId TEXT,
> 	remoteRevision TEXT,
> 	gid TEXT,
> 	collectionId INTEGER NOT NULL
>         		REFERENCES CollectionTable (id)
>             	ON UPDATE RESTRICT
> 		ON DELETE CASCADE,
> 	mimeTypeId INTEGER NOT NULL
>         		REFERENCES MimeTypeTable (id)
>             	ON UPDATE RESTRICT
> 		ON DELETE CASCADE,
> 	datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
> 	atime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
> 	dirty BOOL,
> 	size BIGINT NOT NULL DEFAULT 0
> )
> 
> Is there any reason this was not done?

The SQLite code in Akonadi comes from time before foreign keys were supported 
by SQLite

Support for FKs on SQLite was introduced in 18.12, so I guess you are running 
an older version of KDE PIM?

> Because I started akonadi from the command line using akonadictl, I did get
> to see all the messages that are normally lost or sent to the logs. A
> number of those seem worthy of attention.
> 
> * There occur "DATABASE ERROR" " Parameter count mismatch". My feeling is
> that this is an akonadi bug. I've reported it as
> https://bugs.kde.org/show_bug.cgi?id=403034.

Most of the code that generates the queries is shared for all backends, so we 
would probable see this in all backends, if there was a problem in Akonadi 
itself. It could be some argument count limit in the SQLite Qt driver, or in 
sqlite library itself. I'll investigate it later.

> * Many such as "Tokenizer Warning: 8Bit character ' Ã '" Probably not
> related to the backend. But I would guess it is possible to deal correctly
> in some way with 8 bit characters nowadays?

That comes from the MIME parser in KMime. It's usually unescaped 8bit 
characters in sender names or subject headers. It's hard to deal with them 
correctly because we are missing the information about encoding (is it one 
byte of a multibyte unicode character, ISO-8859, CP-1250, ...). Technically 
the email is broken, the standard permits only 7bit characters.

> 
> * Error while moving mail:  "No valid target specified" (At no point had I
> attempted to move a mail.)

Do you have local filters? They do move emails around...

> 
> * org.kde.pim.akonadicore: Deleting items from the akonadi database failed:
> "No items found" (But deleting mails and calendar events works.)

Might be related to the failed SQL query above, in the log you atteched I see 
that it ends with this message, so the failed query is likely a pre-select to 
find all items to be deleted...

> * "Item query returned empty result set" (Perhaps not important.)

In some cases this is an error, in some it's not. But currently it's always 
reported as an error due to lack of ability to distinguish between this and 
any other failure in the query code. Can be safely ignored for now, will get 
fixed at some point :)


Cheers,
Daniel

> 
> 
> Best,
> 
> Erik


-- 
Daniel Vrátil
www.dvratil.cz | dvratil at kde.org
IRC: dvratil on Freenode (#kde, #kontact, #akonadi, #fedora-kde)

GPG Key: 0x4D69557AECB13683
Fingerprint: 0ABD FA55 A4E6 BEA9 9A83 EA97 4D69 557A ECB1 3683
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 833 bytes
Desc: This is a digitally signed message part.
URL: <http://mail.kde.org/pipermail/kdepim-users/attachments/20190114/65584e79/attachment.sig>