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;

About these ads
  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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: