HOW TO: SQLite backend for Akonadi

[mrvanes]

I've been looking for this information a long time and found it somewhere on Google groups. I thought it was worth spreading.
I've made the change (Kubuntu Oneiric/KDE 4.8.2/akonadi-backend-sqlite 1.7.2-0ubuntu1~ppa2~oneiric1) and have so far not seen any problems.

Would be great if this could be configured in Akonadi Server Configuration. Better even if this could become the standard for Kubuntu in the future.

Techbase says that it isn't recommended, but seeing Gentoo switched to SQLite
as default back with Akonadi 1.4, that statement is outdated IMO. More
evidence for this is the fact that Techbase mentions concurrent access
problems, which should have been resolved in SQLite 3.7 (Write-ahead_Logging
enabled by default).
I have been using Akonadi with SQLite as backend for several months now, and
haven't encountered any issues. On the other hand, I have never compared
performance with MySQL.

SQLite isn't available in the GUI configuration, so you'll have to set it
manually in ~/.config/akonadiserverrc. The database driver is QSQLITE3. here is
my akonadiserverrc :

[%General]
Driver=QSQLITE3

[QSQLITE3]
Name=/home/serafean/.local/share/akonadi/akonadi.db

[Debug]
Tracer=null

The only line you need to change is the Driver line, the rest is filled with
defaults on first run.

I recommend starting with a clean Akonadi configuration. That means removing :
~/.config/akonadi
~/.local/share/akonadi
~/.kde4/share/config/akonadi*

Your local contacts and mail aren't stored in those directories (unless you so
explicitly specified in an agent configuration), so local data is safe.
Akonadi being a cache, removing the database destroys that cache, meaning any
IMAP/GoogleContacts/Facebook ressource will have to resynchronize with its
respective source. This will also happen on a simple database change! The
cache apparently is the database.

--/ /--

TIP : While testing with a new user, kres-migrator created about a dozen local
contact/local mail resources pointing to the same directories, if like me you
don't want to go through them manually, and don't have anything to migrate
from an old kdepim (<4.7), a workaround is disabling kres-migrator by editing
its config : ~/.kde4/share/config/kres-migratorrc :

[Migration]
Enabled=false

Source: https://groups.google.com/forum/#!ms...A/uNC83sXHSFMJ

[woodsmoke]

Hi MRvanes,

And please allow me to welcome you to a very friendly forum in which the members really do pay attention to new ideas.

So....please do not be a stranger!

Well, I tried going to SQLlite about four months ago, but never could really figure out if it "took" or not.

I was reporting what I "thought" were "better results" than some other posters but, still that may have been a "tweakage" on my part.

If you could give particulars about your experiences, and I mean that "literally" -- particulars, like does the connexion to google mail etc. continually have to be re-enabled, etc, then others could compare their results to yours.

And, if it does indeed seem to work well, then it might be suggested that you then report that to the devs.

There is "apparently" some "turmoil" about Kontact/Ksuite/Calligra/ etc. There have even been posts here about defaulting to something like Thunderbird.

[woodsmoke]

Please read my previous post.

I did not make the change in the script because I'm not to bright in all that stuff. Hmmmm

woodsmoke

[SteveRiley]

SQLite is certainly lighter than MySQL, and from what I've read of late, most of the older problems that SQLite created for Akonadi have been eliminated. Nevertheless, to use KDE-PIM still requires two databases: Virtuoso for Nepomuk and MySQL or SQLite for Akonadi. Better, I think, would be for the KDE-PIM team to concentrate on getting akonadi-backend-odbc fully functional. Then we could use a single database -- Virtuoso -- for both Nepomuk and Akonadi, reducing overall system load. It seems rather silly to have to run two full databases just for email and calendaring.

[woodsmoke]

So, SR, we've kind of had this discussion before, but what is your "take" on why Kontact continuously "disengages" from Google mail/calendar?

Is it a Kontact/database thing, or is it something going on with Google that they are not completely open about their product(s)?

woodsmoke

[SteveRiley]

Well, I don't use all of Kontact. Instead, I use only KMail and KOrganizer.

KMail has been working rather well of late. The only time it throws fits is when I try to do "too much" at once -- for example, delete a bunch of emails and then immediately empty the trash. I have to wait a few seconds between these steps for GMail to catch up with the IMAP instructions issued by KMail.

KOrganizer is still a bit fragile. If I use the Akonadi Google resource, I can only see my primary calendar. So I continue to use CalDAV. After a while, KOrganizer just seems to flake out and forget about how to talk to Google calendar. I delete and re-create the resources, and it works again. Really, though, for me, having a calendar on the desktop isn't too important. I manage my calendar from my Android phone.

[mrvanes]

If you could give particulars about your experiences, and I mean that "literally" -- particulars, like does the connexion to google mail etc. continually have to be re-enabled, etc, then others could compare their results to yours.

First, thx for the warm welcome! I'll stick around for sure.

I have 2 disconnected IMAP servers configured (both Zimbra mail servers) and my Google Contacts + Calendar. Only Calendar doesn't work, but that isn't any different when Akonadi is configured to work with MySQL. Probably this bug https://bugs.kde.org/show_bug.cgi?id=268643

@SteveRiley It's not only speed I'm after. I realy like the idea of Akonadi and a centralized meta storage for PIM/search items. I just don't like the idea of running a full-fledged database to support that.

[mrvanes]

I can add that, while using this backend for some days, my kontact (kmail) has never felt so stable as now!
Maybe wishful thinking, but I'm a happy camper!

[woodsmoke]

There is another recent thread on this.

woodsmoke

[SteveRiley]

Just this evening I switched over to SQLite, too. I've tried this in the past with mixed results. I'm curious to see how it's improved.

The instructions in that Google Groups posting need slight modification for we Kubuntu-ers:

• akonadiserverrc is in the directory ~/.config/akonadi/
• The third item in the list of things to clean is ~/.kde/share/config/akonadi*
• kres-migratorrc is in the directory ~/.kde/share/config/

[SteveRiley]

Well, I can report that

steve@x1:~$ [B]apt-cache policy akonadi-server akonadi-backend-sqlite libsqlite3-0[/B]
akonadi-server:
  Installed: 1.7.2-0ubuntu1
  Candidate: 1.7.2-0ubuntu1
  Version table:
 *** 1.7.2-0ubuntu1 0
        500 http://mirror.anl.gov/pub/ubuntu/ precise/main amd64 Packages
        100 /var/lib/dpkg/status
akonadi-backend-sqlite:
  Installed: 1.7.2-0ubuntu1
  Candidate: 1.7.2-0ubuntu1
  Version table:
 *** 1.7.2-0ubuntu1 0
        500 http://mirror.anl.gov/pub/ubuntu/ precise/universe amd64 Packages
        100 /var/lib/dpkg/status
libsqlite3-0:
  Installed: 3.7.9-2ubuntu1
  Candidate: 3.7.9-2ubuntu1
  Version table:
 *** 3.7.9-2ubuntu1 0
        500 http://mirror.anl.gov/pub/ubuntu/ precise/main amd64 Packages
        100 /var/lib/dpkg/status

work very well with Gmail. I can select a group of messages in an IMAP folder, delete them, navigate to trash, empty it, and everything just works. With SQLite, Akonadi seems better able to tolerate the delays as Gmail moves stuff to trash.