Posts Tagged ‘ akonadi ’

akonadi with postgresql, part 3

Now, allowing to change database driver (MySQL or postgreSQL) from the akonadi kcm module.

The patch akonadi database driver patch

snapshot63

snapshot64

akonadi with postgresql, part 2

Akonadi with postgresql, part 2

Now, cleaning the last patch, to make more sense, and to limit changes to try to affect the minimum possible to current working mysql support.

Here’s the new patch patch.

And the list of chages

  • Akonadi database description file and schema

    File : kdesupport/akonadi/server/src/storage/akonadidb.xml and kdesupport/akonadi/server/src/storage/akonadidb.xsd

    Changes description :
    * New element ‘columnproperty’ into column, to describe database type dependent properties


    Index: akonadi/server/src/storage/akonadidb.xsd
    ===================================================================
    --- akonadi/server/src/storage/akonadidb.xsd (revision 978545)
    +++ akonadi/server/src/storage/akonadidb.xsd (working copy)
    @@ -67,8 +67,18 @@
    <xsd:attribute name="refColumn" type="xsd:string"/>
    <xsd:attribute name="methodName" type="xsd:string"/>
    <xsd:attribute name="properties" type="xsd:string"/>
    + <xsd:attribute name="dbtype" type="xsd:string"/>
    </xsd:complexType>

    + <xsd:complexType name="columnpropertyType">
    + <xsd:attribute name="sqltype" type="xsd:string" use="required"/>
    + <xsd:attribute name="default" type="xsd:string"/>
    + <xsd:attribute name="refTable" type="xsd:string"/>
    + <xsd:attribute name="refColumn" type="xsd:string"/>
    + <xsd:attribute name="properties" type="xsd:string" />
    + <xsd:attribute name="dbtype" type="xsd:string" use="required"/>
    + </xsd:complexType>
    +
    <xsd:complexType name="indexType">
    <xsd:attribute name="name" type="xsd:string" use="required"/>
    <xsd:attribute name="columns" type="xsd:string" use="required"/>
    @@ -87,6 +97,7 @@
    <xsd:complexType name="dataType">
    <xsd:attribute name="columns" type="xsd:string" use="required"/>
    <xsd:attribute name="values" type="xsd:string" use="required"/>
    + <xsd:attribute name="dbtype" type="xsd:string"/>
    </xsd:complexType>

    </xsd:schema>
    Index: akonadi/server/src/storage/akonadidb.xml
    ===================================================================
    --- akonadi/server/src/storage/akonadidb.xml (revision 978545)
    +++ akonadi/server/src/storage/akonadidb.xml (working copy)
    @@ -72,7 +72,10 @@

    <table name="Resource">
    <column name="id" type="qint64" properties="NOT NULL PRIMARY KEY AUTOINCREMENT"/>
    - <column name="name" type="QString" sqltype="VARCHAR(255)" properties="BINARY NOT NULL UNIQUE"/>
    + <column name="name" type="QString">
    + <columnproperty sqltype="VARCHAR(255)" properties="BINARY NOT NULL UNIQUE" dbtype="QMYSQL"/>
    + <columnproperty sqltype="VARCHAR(255)" properties="NOT NULL UNIQUE" dbtype="QPSQL"/>
    + </column>
    <reference name="collections" table="Collection" key="resourceId"/>
    <data columns="name" values="'akonadi_search_resource'"/>
    </table>
    @@ -80,9 +83,18 @@
    <table name="Collection">
    <column name="id" type="qint64" properties="NOT NULL PRIMARY KEY AUTOINCREMENT"/>
    <column name="remoteId" type="QString"/>
    - <column name="name" type="QByteArray" sqltype="VARCHAR(255)" properties="BINARY character set utf8 collate utf8_bin NOT NULL"/>
    - <column name="parentId" type="qint64" refTable="Collection" refColumn="id" methodName="parent" properties="DEFAULT 0 REFERENCES Collection(id)"/>
    - <column name="resourceId" type="qint64" refTable="Resource" refColumn="id" properties="NOT NULL REFERENCES Resource(id)"/>
    + <column name="name" type="QByteArray">
    + <columnproperty sqltype="VARCHAR(255)" properties="BINARY character set utf8 collate utf8_bin NOT NULL" dbtype="QMYSQL"/>
    + <columnproperty sqltype="VARCHAR(255)" properties="NOT NULL" dbtype="QPSQL"/>
    + </column>
    + <column name="parentId" type="qint64" methodName="parent" refTable="Collection" refColumn="id">
    + <columnproperty sqltype="bigint" properties="DEFAULT 0 REFERENCES Collection(id)" dbtype="QMYSQL"/>
    + <columnproperty sqltype="int8" properties="DEFAULT null REFERENCES CollectionTable(id)" dbtype="QPSQL"/>
    + </column>
    + <column name="resourceId" type="qint64" refTable="Resource" refColumn="id">
    + <columnproperty sqltype="bigint" properties="NOT NULL REFERENCES Resource(id)" dbtype="QMYSQL"/>
    + <columnproperty sqltype="int8" properties="NOT NULL REFERENCES ResourceTable(id)" dbtype="QPSQL"/>
    + </column>
    <column name="subscribed" type="bool" default="true" properties="NOT NULL DEFAULT true"/>
    <column name="cachePolicyInherit" type="bool" default="true" properties="NOT NULL DEFAULT true"/>
    <column name="cachePolicyCheckInterval" type="int" default="-1" properties="NOT NULL DEFAULT -1"/>
    @@ -93,7 +105,8 @@
    <reference name="children" table="Collection" key="parentId"/>
    <reference name="items" table="PimItem" key="collectionId"/>
    <reference name="attributes" table="CollectionAttribute" key="collectionId"/>
    - <data columns="parentId,name,resourceId" values="0,'Search',1"/>
    + <data columns="parentId,name,resourceId" values="0,'Search',1" dbtype="QMYSQL"/>
    + <data columns="parentId,name,resourceId" values="null,'Search',1" dbtype="QPSQL"/>
    </table>

    <table name="MimeType">
    @@ -111,8 +124,14 @@
    <column name="id" type="qint64" properties="NOT NULL PRIMARY KEY AUTOINCREMENT"/>
    <column name="rev" type="int" properties="NOT NULL DEFAULT 0"/>
    <column name="remoteId" type="QString"/>
    - <column name="collectionId" type="qint64" refTable="Collection" refColumn="id" properties="REFERENCES Collection(id)"/>
    - <column name="mimeTypeId" type="qint64" refTable="MimeType" refColumn="id" properties="REFERENCES MimeType(id)"/>
    + <column name="collectionId" type="qint64" refTable="Collection" refColumn="id">
    + <columnproperty sqltype="BIGINT" properties="REFERENCES Collection(id)" dbtype="QMYSQL"/>
    + <columnproperty sqltype="int8" properties="REFERENCES CollectionTable(id)" dbtype="QPSQL"/>
    + </column>
    + <column name="mimeTypeId" type="qint64" refTable="MimeType" refColumn="id">
    + <columnproperty sqltype="BIGINT" properties="REFERENCES MimeType(id)" dbtype="QMYSQL"/>
    + <columnproperty sqltype="int8" properties="REFERENCES MimeTypeTable(id)" dbtype="QPSQL"/>
    + </column>
    <column name="datetime" type="QDateTime" properties="DEFAULT CURRENT_TIMESTAMP">
    <comment>create/modified time</comment>
    </column>
    @@ -154,7 +173,10 @@

    <table name="CollectionAttribute">
    <column name="id" type="qint64" properties="NOT NULL PRIMARY KEY AUTOINCREMENT"/>
    - <column name="collectionId" type="qint64" refTable="Collection" refColumn="id" properties="REFERENCES Collection(id)"/>
    + <column name="collectionId" type="qint64" refTable="Collection" refColumn="id">
    + <columnproperty sqltype="BIGINT" properties="REFERENCES Collection(id)" dbtype="QMYSQL"/>
    + <columnproperty sqltype="int8" properties="REFERENCES CollectionTable(id)" dbtype="QPSQL"/>
    + </column>
    <column name="type" type="QByteArray" properties="NOT NULL"/>
    <column name="value" type="QByteArray"/>
    <index name="collectionIndex" columns="collectionId" unique="false"/>

  • Akonadi database storage initializer

    File : kdesupport/akonadi/server/src/storage/dbinitializer.cpp

    Changes description :
    * When postgresql, create database relations with complete table name
    * Check for database type dependent properties


    --- akonadi/server/src/storage/dbinitializer.cpp (revision 978545)
    +++ akonadi/server/src/storage/dbinitializer.cpp (working copy)
    @@ -132,8 +132,23 @@
    if ( entry.second.startsWith( QLatin1String("CHAR") ) )
    entry.second.replace(QLatin1String("CHAR"), QLatin1String("VARCHAR"));
    }
    + // check the dbtype
    + if ( columnElement.childNodes().size() > 0) {
    + for (int i=0; i<columnElement.childNodes().size(); i++) {
    + QDomElement e = columnElement.childNodes().at(i).toElement();
    +
    + if (e.attribute( QLatin1String("dbtype")) == mDatabase.driverName()) {
    + entry.second = e.attribute( QLatin1String("sqltype") );
    + entry.second += QLatin1String(" ") + e.attribute( QLatin1String("properties"));
    + }
    + }
    + }
    +
    columnsList.append( entry );
    - } else if ( columnElement.tagName() == QLatin1String( "data" ) ) {
    +
    + } else if ( columnElement.tagName() == QLatin1String( "data" ) &&
    + (!columnElement.attributes().contains(QLatin1String ("dbtype")) ||
    + columnElement.attribute( QLatin1String( "dbtype" ) ) == mDatabase.driverName())) {
    QString values = columnElement.attribute( QLatin1String("values") );
    if ( mDatabase.driverName().startsWith( QLatin1String("QMYSQL") ) )
    values.replace( QLatin1String("\\"), QLatin1String("\\\\") );
    @@ -168,7 +183,7 @@
    if( element.hasAttribute( QLatin1String("properties") ) )
    columns.append( QLatin1String(", ") + element.attribute( QLatin1String("properties") ) );

    - const QString statement = QString::fromLatin1( "CREATE TABLE %1 (%2);" ).arg( tableName, columns );
    + const QString statement = QString::fromLatin1( "CREATE TABLE %1 (%2) WITH OIDS;" ).arg( tableName, columns );
    qDebug() << statement;

    if ( !query.exec( statement ) ) {
    @@ -212,7 +227,7 @@

    // TODO: remove obsolete columns (when sqlite will support it) and adapt column type modifications
    }
    -
    +/*
    // add indices
    columnElement = element.firstChildElement();
    while ( !columnElement.isNull() ) {
    @@ -240,8 +255,7 @@
    }
    columnElement = columnElement.nextSiblingElement();
    }
    -
    -
    +*/
    // add initial data if table is empty
    const QString statement = QString::fromLatin1( "SELECT * FROM %1 LIMIT 1" ).arg( tableName );
    if ( !query.exec( statement ) ) {
    @@ -278,12 +292,12 @@
    statement += QString::fromLatin1("%1_%2 INTEGER REFERENCES %3(%4), " )
    .arg( table1 )
    .arg( col1 )
    - .arg( table1 )
    + .arg( (mDatabase.driverName() == QLatin1String("QPSQL")) ? table1 + QString::fromLatin1("Table") : table1 )
    .arg( col1 );
    statement += QString::fromLatin1("%1_%2 INTEGER REFERENCES %3(%4), " )
    .arg( table2 )
    .arg( col2 )
    - .arg( table2 )
    + .arg( (mDatabase.driverName() == QLatin1String("QPSQL")) ? table2 + QString::fromLatin1("Table") : table2 )
    .arg( col2 );
    statement += QString::fromLatin1("PRIMARY KEY (%1_%2, %3_%4));" ).arg( table1 ).arg( col1 ).arg( table2 ).arg( col2 );
    qDebug() << statement;
    @@ -335,7 +349,7 @@
    if ( mDatabase.driverName().startsWith( QLatin1String("QMYSQL") ) ) {
    statement = QString::fromLatin1( "SHOW INDEXES FROM %1" ).arg( tableName );
    statement += QString::fromLatin1( " WHERE `Key_name` = '%1'" ).arg( indexName );
    - } else if( mDatabase.driverName() == QLatin1String("PSQL") ) {
    + } else if( mDatabase.driverName() == QLatin1String("QPSQL") ) {
    statement = QLatin1String( "SELECT indexname FROM pq_indexes" );
    statement += QString::fromLatin1( " WHERE tablename = '%1'" ).arg( tableName );
    statement += QString::fromLatin1( " AND indexname = '%1';" ).arg( indexName );

  • Collection list handler

    File : kdesupport/akonadi/server/src/handler/aklist.cpp
    Changes description :
    * Get root collections (without parent) with null parent_id instead of 0


    --- akonadi/server/src/handler/aklist.cpp (revision 978545)
    +++ akonadi/server/src/handler/aklist.cpp (working copy)
    @@ -141,7 +141,7 @@
    }
    } else {
    if ( depth != 0 ) {
    - Collection::List list = Collection::retrieveFiltered( Collection::parentIdColumn(), 0 );
    + Collection::List list = Collection::retrieveFiltered( Collection::parentIdColumn(), QVariant() );
    collections << list;
    }
    --depth;

  • Entity header

    File : kdesupport/akonadi/server/src/storage/entity.h
    Changes description :
    * Include QSqlRecord header, to manually access recent inserted id when using postgresql


    --- akonadi/server/src/storage/entity.h (revision 978545)
    +++ akonadi/server/src/storage/entity.h (working copy)
    @@ -31,6 +31,7 @@
    #include <QtSql/QSqlDatabase>
    #include <QtSql/QSqlQuery>
    #include <QtSql/QSqlError>
    +#include <QtSql/QSqlRecord>

  • Entity xsl template

    File : kdesupport/akonadi/server/src/storage/entities.xsl
    Changes description :
    * Check if the value is null before setting it in the entity constructor, to avoid unexpected results (at least with postgresql)


    --- akonadi/server/src/storage/entities.xsl (revision 978545)
    +++ akonadi/server/src/storage/entities.xsl (working copy)
    @@ -163,7 +163,8 @@

    <xsl:value-of select="$className"/> rv(
    <xsl:for-each select="column">
    - query.value( <xsl:value-of select="position() - 1"/> ).value<<xsl:value-of select="@type"/>>()
    + (query.isNull(<xsl:value-of select="position() - 1"/>)) ? <xsl:value-of select="@type"/>() :
    + query.value( <xsl:value-of select="position() - 1"/> ).value<<xsl:value-of select="@type"/>>()
    <xsl:if test="position() != last()">,</xsl:if>
    </xsl:for-each>
    );

  • Entity source (cpp) xsl template

    File : kdesupport/akonadi/server/src/storage/entities-source.xsl
    Changes description :

    • extractResult method: check if the value is null before setting it
    • retrieveFiltered method: if using postgresql, when filtering by a reference column and null value, use ‘is null’ instead of ‘ = 0’
    • insert method: – add ‘returning id’ option when using postgresql to get the inserted id
      – not insert 0 or null references values
      – get the inserted id manually when using postgresql ( i cannot make DataStore::lastinsertid work )


    --- akonadi/server/src/storage/entities-source.xsl (revision 978545)
    +++ akonadi/server/src/storage/entities-source.xsl (working copy)
    @@ -237,7 +237,8 @@
    while ( query.next() ) {
    rv.append( <xsl:value-of select="$className"/>(
    <xsl:for-each select="column">
    - query.value( <xsl:value-of select="position() - 1"/> ).value<<xsl:value-of select="@type"/>>()
    + (query.isNull(<xsl:value-of select="position() - 1"/>)) ? <xsl:value-of select="@type"/>() :
    + query.value( <xsl:value-of select="position() - 1"/> ).value<<xsl:value-of select="@type"/>>()
    <xsl:if test="position() != last()">,</xsl:if>
    </xsl:for-each>
    ) );
    @@ -295,7 +296,10 @@
    statement.append( tableName() );
    statement.append( QLatin1String(" WHERE ") );
    statement.append( key );
    - statement.append( QLatin1String(" = :key") );
    + if ( value.isNull() && db.driverName() == QLatin1String("QPSQL"))
    + statement.append( QLatin1String(" is null") );
    + else
    + statement.append( QLatin1String(" = :key") );
    query.prepare( statement );
    query.bindValue( QLatin1String(":key"), value );
    if ( !query.exec() ) {
    @@ -425,20 +429,42 @@

    QStringList cols, vals;
    <xsl:for-each select="column[@name != 'id']">
    - if ( d-><xsl:value-of select="@name"/>_changed ) {
    - cols.append( <xsl:value-of select="@name"/>Column() );
    - vals.append( QLatin1String( ":<xsl:value-of select="@name"/>" ) );
    - }
    + <xsl:variable name="refColumn"><xsl:value-of select="@refColumn"/></xsl:variable>
    + <xsl:if test="$refColumn = 'id'">
    + if ( d-><xsl:value-of select="@name"/>_changed && d-><xsl:value-of select="@name"/> > 0 ) {
    + cols.append( <xsl:value-of select="@name"/>Column() );
    + vals.append( QLatin1String( ":<xsl:value-of select="@name"/>" ) );
    + }
    + </xsl:if>
    + <xsl:if test="$refColumn != 'id'">
    + if ( d-><xsl:value-of select="@name"/>_changed ) {
    + cols.append( <xsl:value-of select="@name"/>Column() );
    + vals.append( QLatin1String( ":<xsl:value-of select="@name"/>" ) );
    + }
    + </xsl:if>
    </xsl:for-each>
    - QString statement = QString::fromLatin1("INSERT INTO <xsl:value-of select="$tableName"/> (%1) VALUES (%2)")
    - .arg( cols.join( QLatin1String(",") ), vals.join( QLatin1String(",") ) );

    + QString insertOptions;
    + if (DataStore::self()->database().driverName() == QLatin1String("QPSQL")) {
    + insertOptions = QLatin1String( " RETURNING id " );
    + }
    + QString statement = QString::fromLatin1("INSERT INTO <xsl:value-of select="$tableName"/> (%1) VALUES (%2) %3")
    + .arg( cols.join( QLatin1String(",") ), vals.join( QLatin1String(",") ), insertOptions );
    +
    QSqlQuery query( db );
    query.prepare( statement );
    <xsl:for-each select="column[@name != 'id']">
    - if ( d-><xsl:value-of select="@name"/>_changed ) {
    - query.bindValue( QLatin1String(":<xsl:value-of select="@name"/>"), this-><xsl:value-of select="@name"/>() );
    - }
    + <xsl:variable name="refColumn"><xsl:value-of select="@refColumn"/></xsl:variable>
    + <xsl:if test="$refColumn = 'id'">
    + if ( d-><xsl:value-of select="@name"/>_changed && d-><xsl:value-of select="@name"/> > 0 ) {
    + query.bindValue( QLatin1String(":<xsl:value-of select="@name"/>"), this-><xsl:value-of select="@name"/>() );
    + }
    + </xsl:if>
    + <xsl:if test="$refColumn != 'id'">
    + if ( d-><xsl:value-of select="@name"/>_changed ) {
    + query.bindValue( QLatin1String(":<xsl:value-of select="@name"/>"), this-><xsl:value-of select="@name"/>() );
    + }
    + </xsl:if>
    </xsl:for-each>

    if ( !query.exec() ) {
    @@ -447,7 +473,15 @@
    return false;
    }

    - setId( DataStore::self()->lastInsertId( query ) );
    + // TODO : make postgresql work with DataStore::lastInsertId too
    + if (DataStore::self()->database().driverName() == QLatin1String("QPSQL")) {
    + query.next();
    + setId( query.record().value(QLatin1String("id")).toLongLong() );
    + }
    + else {
    + setId( DataStore::self()->lastInsertId( query ) );
    + }
    +
    if ( insertId )
    *insertId = id();
    return true;

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


    [%General]
    Driver=QPSQL
    SizeThreshold=4096
    ExternalPayload=false

    [QPSQL]
    Name=akonadi
    User=akonadi
    Password=akonadi
    Options=
    StartServer=false

  • 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