akonadi and postgresql

Akonadi is The PIM Storage Service for kde4, by default it works with mysql.

I really wish to have akonadi with postgresql, nothing against mysql, but i really love postgresql 🙂

Some postgresql support is already in akonadi code (kdesupport/akonadi),
so i made some fun trying akonadi with it (and it does work!).

Here’s the things i had to change to make it work.

  • First, i create an akonadi user and database

    createuser akonadi
    createdb -U akonadi akonadi -E UTF-8

  • Akonadi configuration with postgresql

    in : ~/.config/akonadi/akonadiserverrc



  • Entites xsl template

    in : kdesupport/akonadi/server/src/storage/entities-source.xsl

    (query.isNull(<xsl:value-of select="position() - 1"/>)) ? <xsl:value-of select="@type"/>() :
    query.value( <xsl:value-of select="position() - 1"/> ).value&lt;<xsl:value-of select="@type"/>&gt;()

    It’s important to check if the result is null before setting it, otherwise it would produce unexpected results with postgresql

    QList<> ::retrieveFiltered( const QString &key, const QVariant &value )
    + qDebug() << "::::: RETRIEVE FILTERED " << key;
    QSqlDatabase db = DataStore::self()->database();
    if ( !db.isOpen() )
    return QList<>();
    @@ -295,7 +300,10 @@
    statement.append( tableName() );
    statement.append( QLatin1String(" WHERE ") );
    statement.append( key );
    - statement.append( QLatin1String(" = :key") );
    + if ( value.isNull() )
    + statement.append( QLatin1String(" is null") );
    + else
    + statement.append( QLatin1String(" = :key") );
    query.prepare( statement );
    query.bindValue( QLatin1String(":key"), value );
    if ( !query.exec() ) {

    – In retrieve filtered method, if we are selecting all collections without parent
    parent_id = 0 will not work in postgresql, we have to change it for parent_id is null

    – When inserting data, we have to append RETURNING id to the insert sentence to retrieve the id

  • Akonadi database definition in kdesupport/akonadi/server/src/storage/akonadidb.xml

    Creating and initializing db:
    Here would be really nice to have some attribute to allow describe custom sentences for each different db manager (when needed)
    something like

    <column name="name" type="QByteArray" sqltype="VARCHAR(255)" properties="character set utf8 collate utf8_bin NOT NULL" driver="mysql"/>
    <column name="name" type="QByteArray" sqltype="VARCHAR(255)" properties=" NOT NULL" driver="postgresql"/>

  • DataStore in kdesupport/akonadi/server/src/storage/datastore.cpp

    QSqlQuery->lastInsertId does not work with postgresql (at least i can’t make it work, with or without oids 😦 ),
    so using ‘returning id’ in the insert sentence we can get it with
    insertId = query.record().value(QLatin1String(“id”)).toLongLong(&ok);

  • kdesupport/akonadi/server/src/handler/aklist.cpp
    To retrieve all collections without parent

    Collection::List list = Collection::retrieveFiltered( Collection::parentIdColumn(), QVariant() );

    instead of

    Collection::List list = Collection::retrieveFiltered( Collection::parentIdColumn(), 0 );

  • And other small changes …

    Here’s the patch akonadi postgresql patch

    This is only for testing purposes,
    some more serious and tested support would be very nice 🙂

    I hope to have some time to update my todoplasmoid to work with akonadi

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: