Report on trying out the SQLite backend

[Erik Quaeghebeur]

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.)


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?


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.

* 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?

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

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

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


Best,

Erik