Projects/PIM/Akonadi/Database - Revision history

~reeves-87: /* Generic */ run->running

2012-12-03T02:15:51Z

‎Generic: run->running

Krop: /* PostgreSQL */

2012-03-13T17:21:40Z

‎PostgreSQL

Bernhard: /* Sqlite */ one more link about sqlite concurrency

2011-08-25T10:05:25Z

‎Sqlite: one more link about sqlite concurrency

Bernhard: /* Sqlite */ adding link about SQlite's concurrency support and its limitations

2011-08-25T09:56:48Z

‎Sqlite: adding link about SQlite's concurrency support and its limitations

Vkrause: /* Sqlite */

2011-07-31T08:03:15Z

‎Sqlite

Vkrause: Slightly extend the Sqlite performance issues.

2011-07-17T09:45:14Z

Slightly extend the Sqlite performance issues.

Vkrause: Note about akonadiserverrc.

2011-04-16T14:34:10Z

Note about akonadiserverrc.

Vkrause: Note some MySQL and Sqlite details.

2011-04-16T14:19:44Z

Note some MySQL and Sqlite details.

Vkrause: Document database backend operation modes.

2011-04-16T14:16:29Z

Document database backend operation modes.

Vkrause: Start Akonadi database internals documentation.

2011-04-16T14:08:41Z

Start Akonadi database internals documentation.

New page

= Akonadi Server Database Internals =

This page contains information about how the Akonadi server uses its internal SQL database. This is purely for Akonadi server developers, you are NOT supposed to use the database yourself, by-passing Akonadi!

== Database Schema ==

* [http://api.kde.org/kdesupport-api/kdesupport-apidocs/akonadi/html/akonadi_server_database.html Visualization]
* Code is in server/src/storage
** akonadidb.xml contains the schema in an XML format
** dbinitializer* contains the code to create tables out of that
**updatedb.xml contains schema update instructions
** dbupdater.* executes those

== Backends ==

=== MySQL ===

Status: working, default backend for desktop systems

TODO

=== Sqlite ===

Status: working with limitations, default backend for mobile systems

TODO

=== PostgreSQL ===

Status: unknown

TODO

=== Virtuoso ===

Status: not working

Proof-of-concept work was done during Akademy 2010, up to the point where the database could be created successfully. Known issues:

* Some column names used by Akonadi are Virtuoso keywords
* Some of the first INSERT query fails with a signed vs. unsigned int mismatch/overflow.

Completion of Virtuoso support is considered non-trivial and probably requires a lot of work on the Akonadi side, the SQL dialect is too different from what we support so far.

=== MySQL/Embedded ===

Status: not working

Development stopped, MySQL/Embedded does not support transactions.

== Tools ==

=== Generic ===

''akonadiconsole'' has support for browsing the database and run arbitrary SQL queries. See [[Development/Tutorials/Akonadi/Application]].

=== MySQL ===

There are convenience scripts for running mysqld in the configuration used by Akonadi as well as (and probably more useful) running the MySQL command line client connected to the Akonadi database. See server/src/storage/akonadi-mysql-[client|server].sh.

[[Category:Akonadi]]

@@ Line 43: / Line 43: @@
 * Requires patched QtSql driver to fix concurrency issues (Akonadi ships a fork of the driver with the necessary changes)
 * Slower than MySQL (see e.g. http://bertjan.broeksemaatjes.nl/node/53), based on our measurments about 2-5x on the query level, but still in the milliseconds range, so that's probably not a big issue.
-* Mutex-based transaction serialization in Akonadi (required because SQlite's support of concurrency might not be enough) can block clients while background operations such as a IMAP sync are going on. This is the real killer here, the more clients (including background processes like indexing) are accessing the data, the more blocking you experience. Background http://www.sqlite.org/faq.html#q5 ,  http://www.somacon.com/p369.php , http://www.sqlite.org/lockingv3.html
+* Mutex-based transaction serialization in Akonadi (required because SQlite's support of concurrency might not be enough) can block clients while background operations such as a IMAP sync are going on. This is the real killer here, the more clients (including background processes like indexing) are accessing the data, the more blocking you experience. Background http://www.sqlite.org/faq.html#q5 ,  http://www.somacon.com/p369.php , http://www.sqlite.org/lockingv3.html , http://www.mail-archive.com/sqlite-users%40sqlite.org/msg43873.html
 === PostgreSQL ===

@@ Line 43: / Line 43: @@
 * Requires patched QtSql driver to fix concurrency issues (Akonadi ships a fork of the driver with the necessary changes)
 * Slower than MySQL (see e.g. http://bertjan.broeksemaatjes.nl/node/53), based on our measurments about 2-5x on the query level, but still in the milliseconds range, so that's probably not a big issue.
-* Mutex-based transaction serialization in Akonadi (required due to lack of concurrency support in Sqlite) can block clients while background operations such as a IMAP sync are going on. This is the real killer here, the more clients (including background processes like indexing) are accessing the data, the more blocking you experience.
+* Mutex-based transaction serialization in Akonadi (required because SQlite's support of concurrency might not be enough) can block clients while background operations such as a IMAP sync are going on. This is the real killer here, the more clients (including background processes like indexing) are accessing the data, the more blocking you experience. Background http://www.sqlite.org/faq.html#q5 ,  http://www.somacon.com/p369.php , http://www.sqlite.org/lockingv3.html
 === PostgreSQL ===

@@ Line 41: / Line 41: @@
 Known issues:
 * Requires newer version than the default Qt one.
-* Requires patched QtSql driver to fix concurrency issues
+* Requires patched QtSql driver to fix concurrency issues (Akonadi ships a fork of the driver with the necessary changes)
 * Slower than MySQL (see e.g. http://bertjan.broeksemaatjes.nl/node/53), based on our measurments about 2-5x on the query level, but still in the milliseconds range, so that's probably not a big issue.
 * Mutex-based transaction serialization in Akonadi (required due to lack of concurrency support in Sqlite) can block clients while background operations such as a IMAP sync are going on. This is the real killer here, the more clients (including background processes like indexing) are accessing the data, the more blocking you experience.

@@ Line 42: / Line 42: @@
 * Requires newer version than the default Qt one.
 * Requires patched QtSql driver to fix concurrency issues
-* Mutex-based transaction serialization in Akonadi (required due to lack of concurrency support in Sqlite) can block clients while background operations such as a IMAP sync are going on.
+* Slower than MySQL (see e.g. http://bertjan.broeksemaatjes.nl/node/53), based on our measurments about 2-5x on the query level, but still in the milliseconds range, so that's probably not a big issue.
 === PostgreSQL ===

@@ Line 22: / Line 22: @@
 * Internal server: Akonadi manages the database server process itself, taking care of starting/stopping and configuring it.
 * External server: Akonadi connects to an already running database server.
 === MySQL ===
@@ Line 70: / Line 72: @@
 Development stopped, MySQL/Embedded does not support transactions.
 == Tools ==

@@ Line 79: / Line 79: @@
 === Generic ===
-''akonadiconsole'' has support for browsing the database and run arbitrary SQL queries. See [[Development/Tutorials/Akonadi/Application]].
+''akonadiconsole'' has support for browsing the database and running arbitrary SQL queries. See [[Development/Tutorials/Akonadi/Application]].
 === MySQL ===

@@ Line 47: / Line 47: @@
 === PostgreSQL ===
-Status: unknown
+Status: Working
 Supported modes: Internal, External
-TODO
+Known issues:
 === Virtuoso ===

@@ Line 29: / Line 29: @@
 Supported modes: Internal, External
-TODO
+MySQL server configuration files for the internal mode can be found here: server/src/storage/mysql-global*.conf.
 === Sqlite ===
@@ Line 37: / Line 37: @@
 Supported modes: Embedded
-TODO
+Known issues:
 === PostgreSQL ===

@@ Line 13: / Line 13: @@
 == Backends ==
 === MySQL ===
 Status: working, default backend for desktop systems
 TODO
@@ Line 23: / Line 34: @@
 Status: working with limitations, default backend for mobile systems
 TODO
@@ Line 29: / Line 42: @@
 Status: unknown
 TODO
@@ Line 35: / Line 50: @@
 Status: not working
 Proof-of-concept work was done during Akademy 2010, up to the point where the database could be created successfully. Known issues:
@@ Line 46: / Line 63: @@
 Status: not working
 Development stopped, MySQL/Embedded does not support transactions.