8#include "akonadischema.h" 
    9#include "akonadiserver_debug.h" 
   12#include "dbinitializer_p.h" 
   13#include "dbintrospector.h" 
   16#include "querybuilder.h" 
   17#include "selectquerybuilder.h" 
   19#include "private/dbus_p.h" 
   21#include <QCoreApplication> 
   22#include <QDBusConnection> 
   29#include <QDomDocument> 
   30#include <QElapsedTimer> 
   35using namespace Akonadi::Server;
 
   38    : m_database(database)
 
   39    , m_filename(filename)
 
 
   47    auto currentVersion = SchemaVersion::retrieveAll(store).at(0);
 
   49    UpdateSet::Map updates;
 
   51    if (!parseUpdateSets(currentVersion.version(), updates)) {
 
   67        Q_ASSERT(it.key() > currentVersion.version());
 
   68        qCDebug(AKONADISERVER_LOG) << 
"DbUpdater: update to version:" << it.key() << 
" mandatory:" << it.value().abortOnFailure;
 
   71        bool hasTransaction = 
false;
 
   72        if (it.value().complex) { 
 
   73            const QString methodName = QStringLiteral(
"complexUpdate_%1()").
arg(it.value().version);
 
   77                qCCritical(AKONADISERVER_LOG) << 
"Update to version" << it.value().version << 
"marked as complex, but no implementation is available";
 
   80                method.
invoke(
this, Q_RETURN_ARG(
bool, success));
 
   82                    qCCritical(AKONADISERVER_LOG) << 
"Update failed";
 
   86            success = m_database.transaction();
 
   88                hasTransaction = 
true;
 
   90                for (
const QString &statement : statements) {
 
   92                    success = query.exec(statement);
 
   94                        qCCritical(AKONADISERVER_LOG) << 
"DBUpdater: query error:" << query.lastError().text() << m_database.lastError().text();
 
   95                        qCCritical(AKONADISERVER_LOG) << 
"Query was: " << statement;
 
   96                        qCCritical(AKONADISERVER_LOG) << 
"Target version was: " << it.key();
 
   97                        qCCritical(AKONADISERVER_LOG) << 
"Mandatory: " << it.
value().abortOnFailure;
 
  104            currentVersion.setVersion(it.key());
 
  105            success = currentVersion.update();
 
  108        if (!success || (hasTransaction && !m_database.commit())) {
 
  109            qCCritical(AKONADISERVER_LOG) << 
"Failed to commit transaction for database update";
 
  110            if (hasTransaction) {
 
  111                m_database.rollback();
 
  113            if (it.value().abortOnFailure) {
 
 
  123bool DbUpdater::parseUpdateSets(
int currentVersion, UpdateSet::Map &updates)
 const 
  125    QFile file(m_filename);
 
  127        qCCritical(AKONADISERVER_LOG) << 
"Unable to open update description file" << m_filename;
 
  133    const auto result = document.
setContent(&file);
 
  135        qCCritical(AKONADISERVER_LOG) << 
"Unable to parse update description file" << m_filename << 
":" << result.errorMessage << 
"at line" << result.errorLine
 
  136                                      << 
"column" << result.errorColumn;
 
  142        qCCritical(AKONADISERVER_LOG) << 
"Invalid update description file format";
 
  148    while (!updateElement.
isNull()) {
 
  149        if (updateElement.
tagName() == QLatin1StringView(
"update")) {
 
  150            const int version = updateElement.
attribute(QStringLiteral(
"version"), QStringLiteral(
"-1")).
toInt();
 
  152                qCCritical(AKONADISERVER_LOG) << 
"Invalid version attribute in database update description";
 
  157                qCCritical(AKONADISERVER_LOG) << 
"Duplicate version attribute in database update description";
 
  161            if (version <= currentVersion) {
 
  162                qCDebug(AKONADISERVER_LOG) << 
"skipping update" << 
version;
 
  166                updateSet.abortOnFailure = (updateElement.
attribute(QStringLiteral(
"abortOnFailure")) == QLatin1StringView(
"true"));
 
  169                while (!childElement.
isNull()) {
 
  170                    if (childElement.
tagName() == QLatin1StringView(
"raw-sql")) {
 
  171                        if (updateApplicable(childElement.
attribute(QStringLiteral(
"backends")))) {
 
  172                            updateSet.statements << buildRawSqlStatement(childElement);
 
  174                    } 
else if (childElement.
tagName() == QLatin1StringView(
"complex-update")) {
 
  175                        if (updateApplicable(childElement.
attribute(QStringLiteral(
"backends")))) {
 
  176                            updateSet.complex = 
true;
 
  184                if (!updateSet.statements.
isEmpty() || updateSet.complex) {
 
  185                    updates.
insert(version, updateSet);
 
  195bool DbUpdater::updateApplicable(
const QString &backends)
 const 
  197    const QStringList matchingBackends = backends.
split(QLatin1Char(
','));
 
  199    QString currentBackend;
 
  202        currentBackend = QStringLiteral(
"mysql");
 
  204    case DbType::PostgreSQL:
 
  205        currentBackend = QStringLiteral(
"psql");
 
  208        currentBackend = QStringLiteral(
"sqlite");
 
  210    case DbType::Unknown:
 
  214    return matchingBackends.
contains(currentBackend);
 
  217QString DbUpdater::buildRawSqlStatement(
const QDomElement &element)
 const 
  222bool DbUpdater::complexUpdate_25()
 
  224    qCDebug(AKONADISERVER_LOG) << 
"Starting database update to version 25";
 
  229    QElapsedTimer ttotal;
 
  235        QSqlQuery 
query(m_database);
 
  236        query.exec(QStringLiteral(
"ALTER TABLE PartTable_old RENAME TO PartTable"));
 
  240        QSqlQuery 
query(m_database);
 
  241        query.exec(QStringLiteral(
"DROP TABLE IF EXISTS PartTable_new"));
 
  246        QSqlQuery 
query(m_database);
 
  247        if (dbType == DbType::Sqlite) {
 
  248            query.exec(QStringLiteral(
"DELETE FROM PartTypeTable"));
 
  250            query.exec(QStringLiteral(
"TRUNCATE TABLE PartTypeTable"));
 
  258        QueryBuilder qb(store, QStringLiteral(
"PartTable"), QueryBuilder::Delete);
 
  259        qb.addValueCondition(QStringLiteral(
"PartTable.name"), Query::Equals, QLatin1StringView(
"GID"));
 
  263    qCDebug(AKONADISERVER_LOG) << 
"Creating a PartTable_new";
 
  265        TableDescription description;
 
  266        description.name = QStringLiteral(
"PartTable_new");
 
  268        ColumnDescription idColumn;
 
  269        idColumn.name = QStringLiteral(
"id");
 
  270        idColumn.type = QStringLiteral(
"qint64");
 
  271        idColumn.isAutoIncrement = 
true;
 
  272        idColumn.isPrimaryKey = 
true;
 
  273        description.columns << idColumn;
 
  275        ColumnDescription pimItemIdColumn;
 
  276        pimItemIdColumn.name = QStringLiteral(
"pimItemId");
 
  277        pimItemIdColumn.type = QStringLiteral(
"qint64");
 
  278        pimItemIdColumn.allowNull = 
false;
 
  279        description.columns << pimItemIdColumn;
 
  281        ColumnDescription partTypeIdColumn;
 
  282        partTypeIdColumn.name = QStringLiteral(
"partTypeId");
 
  283        partTypeIdColumn.type = QStringLiteral(
"qint64");
 
  284        partTypeIdColumn.allowNull = 
false;
 
  285        description.columns << partTypeIdColumn;
 
  287        ColumnDescription dataColumn;
 
  288        dataColumn.name = QStringLiteral(
"data");
 
  289        dataColumn.type = QStringLiteral(
"QByteArray");
 
  290        description.columns << dataColumn;
 
  292        ColumnDescription dataSizeColumn;
 
  293        dataSizeColumn.name = QStringLiteral(
"datasize");
 
  294        dataSizeColumn.type = QStringLiteral(
"qint64");
 
  295        dataSizeColumn.allowNull = 
false;
 
  296        description.columns << dataSizeColumn;
 
  298        ColumnDescription versionColumn;
 
  299        versionColumn.name = QStringLiteral(
"version");
 
  300        versionColumn.type = QStringLiteral(
"int");
 
  301        versionColumn.defaultValue = QStringLiteral(
"0");
 
  302        description.columns << versionColumn;
 
  304        ColumnDescription externalColumn;
 
  305        externalColumn.name = QStringLiteral(
"external");
 
  306        externalColumn.type = QStringLiteral(
"bool");
 
  307        externalColumn.defaultValue = QStringLiteral(
"false");
 
  308        description.columns << externalColumn;
 
  311        const QString queryString = initializer->buildCreateTableStatement(description);
 
  313        QSqlQuery 
query(m_database);
 
  314        if (!
query.exec(queryString)) {
 
  315            qCCritical(AKONADISERVER_LOG) << 
query.lastError().text();
 
  320    qCDebug(AKONADISERVER_LOG) << 
"Migrating part types";
 
  323        QueryBuilder qb(store, QStringLiteral(
"PartTable"), QueryBuilder::Select);
 
  324        qb.setDistinct(
true);
 
  325        qb.addColumn(QStringLiteral(
"PartTable.name"));
 
  328            qCCritical(AKONADISERVER_LOG) << qb.query().lastError().text();
 
  333        auto &
query = qb.query();
 
  334        while (
query.next()) {
 
  336            const QString partName = 
query.
value(0).toString();
 
  337            const QString ns = partName.
left(3);
 
  338            const QString 
name = partName.
mid(4);
 
  341                QueryBuilder qb(store, QStringLiteral(
"PartTypeTable"), QueryBuilder::Insert);
 
  342                qb.setColumnValue(QStringLiteral(
"ns"), ns);
 
  343                qb.setColumnValue(QStringLiteral(
"name"), name);
 
  345                    qCCritical(AKONADISERVER_LOG) << qb.query().lastError().text();
 
  349            qCDebug(AKONADISERVER_LOG) << 
"\t Moved part type" << partName << 
"to PartTypeTable";
 
  353    qCDebug(AKONADISERVER_LOG) << 
"Migrating data from PartTable to PartTable_new";
 
  355        QSqlQuery 
query(m_database);
 
  357        if (dbType == DbType::PostgreSQL) {
 
  358            queryString = QStringLiteral(
 
  359                "INSERT INTO PartTable_new (id, pimItemId, partTypeId, data, datasize, version, external) " 
  360                "SELECT PartTable.id, PartTable.pimItemId, PartTypeTable.id, PartTable.data, " 
  361                "       PartTable.datasize, PartTable.version, PartTable.external " 
  363                "LEFT JOIN PartTypeTable ON " 
  364                "          PartTable.name = CONCAT(PartTypeTable.ns, ':', PartTypeTable.name)");
 
  365        } 
else if (dbType == DbType::MySQL) {
 
  366            queryString = QStringLiteral(
 
  367                "INSERT INTO PartTable_new (id, pimItemId, partTypeId, data, datasize, version, external) " 
  368                "SELECT PartTable.id, PartTable.pimItemId, PartTypeTable.id, PartTable.data, " 
  369                "PartTable.datasize, PartTable.version, PartTable.external " 
  371                "LEFT JOIN PartTypeTable ON PartTable.name = CONCAT(PartTypeTable.ns, ':', PartTypeTable.name)");
 
  372        } 
else if (dbType == DbType::Sqlite) {
 
  373            queryString = QStringLiteral(
 
  374                "INSERT INTO PartTable_new (id, pimItemId, partTypeId, data, datasize, version, external) " 
  375                "SELECT PartTable.id, PartTable.pimItemId, PartTypeTable.id, PartTable.data, " 
  376                "PartTable.datasize, PartTable.version, PartTable.external " 
  378                "LEFT JOIN PartTypeTable ON PartTable.name = PartTypeTable.ns || ':' || PartTypeTable.name");
 
  381        if (!
query.exec(queryString)) {
 
  382            qCCritical(AKONADISERVER_LOG) << 
query.lastError().text();
 
  387    qCDebug(AKONADISERVER_LOG) << 
"Swapping PartTable_new for PartTable";
 
  391        QSqlQuery 
query(m_database);
 
  393        if (dbType == DbType::PostgreSQL || dbType == DbType::Sqlite) {
 
  394            if (dbType == DbType::PostgreSQL) {
 
  395                m_database.transaction();
 
  398            if (!
query.exec(QStringLiteral(
"ALTER TABLE PartTable RENAME TO PartTable_old"))) {
 
  399                qCCritical(AKONADISERVER_LOG) << 
query.lastError().text();
 
  400                m_database.rollback();
 
  405            if (!
query.exec(QStringLiteral(
"ALTER TABLE PartTable_new RENAME TO PartTable"))) {
 
  406                qCCritical(AKONADISERVER_LOG) << 
query.lastError().text();
 
  407                m_database.rollback();
 
  411            if (dbType == DbType::PostgreSQL) {
 
  415            if (!
query.exec(QStringLiteral(
"RENAME TABLE PartTable TO PartTable_old," 
  416                                           "             PartTable_new TO PartTable"))) {
 
  417                qCCritical(AKONADISERVER_LOG) << 
query.lastError().text();
 
  423    qCDebug(AKONADISERVER_LOG) << 
"Removing PartTable_old";
 
  425        QSqlQuery 
query(m_database);
 
  426        if (!
query.exec(QStringLiteral(
"DROP TABLE PartTable_old;"))) {
 
  428            qCDebug(AKONADISERVER_LOG) << 
query.lastError().text();
 
  429            qCDebug(AKONADISERVER_LOG) << 
"Not a fatal problem, continuing...";
 
  434    qCDebug(AKONADISERVER_LOG) << 
"Final tuning of new PartTable";
 
  436        QSqlQuery 
query(m_database);
 
  437        if (dbType == DbType::PostgreSQL) {
 
  438            query.exec(QStringLiteral(
"ALTER TABLE PartTable RENAME CONSTRAINT parttable_new_pkey TO parttable_pkey"));
 
  439            query.exec(QStringLiteral(
"ALTER SEQUENCE parttable_new_id_seq RENAME TO parttable_id_seq"));
 
  440            query.exec(QStringLiteral(
"SELECT setval('parttable_id_seq', MAX(id) + 1) FROM PartTable"));
 
  441        } 
else if (dbType == DbType::MySQL) {
 
  443            query.exec(QStringLiteral(
"ALTER TABLE PartTable AUTO_INCREMENT = 0"));
 
  447    qCDebug(AKONADISERVER_LOG) << 
"Update done in" << ttotal.
elapsed() << 
"ms";
 
  455bool DbUpdater::complexUpdate_36()
 
  457    qCDebug(AKONADISERVER_LOG, 
"Starting database update to version 36");
 
  460    QSqlQuery 
query(m_database);
 
  461    if (!
query.exec(QStringLiteral(
"PRAGMA foreign_key_checks=OFF"))) {
 
  462        qCCritical(AKONADISERVER_LOG, 
"Failed to disable foreign key checks!");
 
  466    const auto hasForeignKeys = [](
const TableDescription &desc) {
 
  467        return std::any_of(desc.columns.cbegin(), desc.columns.cend(), [](
const ColumnDescription &col) {
 
  468            return !col.refTable.isEmpty() && !col.refColumn.isEmpty();
 
  472    const auto recreateTableWithForeignKeys = [
this](
const TableDescription &table) -> QPair<bool, QSqlQuery> {
 
  473        qCDebug(AKONADISERVER_LOG) << 
"Updating foreign keys in table" << table.name;
 
  475        QSqlQuery 
query(m_database);
 
  479        query.exec(QStringLiteral(
"ALTER TABLE %1_old RENAME TO %1").arg(table.name));
 
  480        query.exec(QStringLiteral(
"DROP TABLE %1_new").arg(table.name));
 
  482        qCDebug(AKONADISERVER_LOG, 
"\tCreating table %s_new with foreign keys", qUtf8Printable(table.name));
 
  485            TableDescription 
copy = table;
 
  486            copy.name += QStringLiteral(
"_new");
 
  487            if (!
query.exec(initializer->buildCreateTableStatement(copy))) {
 
  489                return {
false, std::move(query)};
 
  493        qCDebug(AKONADISERVER_LOG,
 
  494                "\tCopying values from %s to %s_new (this may take a very long of time...)",
 
  495                qUtf8Printable(table.name),
 
  496                qUtf8Printable(table.name));
 
  497        if (!
query.exec(QStringLiteral(
"INSERT INTO %1_new SELECT * FROM %1").arg(table.name))) {
 
  499            return {
false, std::move(query)};
 
  502        qCDebug(AKONADISERVER_LOG, 
"\tSwapping %s_new for %s", qUtf8Printable(table.name), qUtf8Printable(table.name));
 
  503        if (!
query.exec(QStringLiteral(
"ALTER TABLE %1 RENAME TO %1_old").arg(table.name))) {
 
  505            return {
false, std::move(query)};
 
  508        if (!
query.exec(QStringLiteral(
"ALTER TABLE %1_new RENAME TO %1").arg(table.name))) {
 
  510            return {
false, std::move(query)};
 
  513        qCDebug(AKONADISERVER_LOG, 
"\tRemoving table %s_old", qUtf8Printable(table.name));
 
  514        if (!
query.exec(QStringLiteral(
"DROP TABLE %1_old").arg(table.name))) {
 
  516            qCWarning(AKONADISERVER_LOG, 
"Failed to DROP TABLE %s (not fatal, update will continue)", qUtf8Printable(table.name));
 
  517            qCWarning(AKONADISERVER_LOG, 
"Error: %s", qUtf8Printable(
query.lastError().text()));
 
  520        qCDebug(AKONADISERVER_LOG) << 
"\tOptimizing table %s", qUtf8Printable(table.name);
 
  521        if (!
query.exec(QStringLiteral(
"ANALYZE %1").arg(table.name))) {
 
  523            qCWarning(AKONADISERVER_LOG, 
"Failed to ANALYZE %s (not fatal, update will continue)", qUtf8Printable(table.name));
 
  524            qCWarning(AKONADISERVER_LOG, 
"Error: %s", qUtf8Printable(
query.lastError().text()));
 
  527        qCDebug(AKONADISERVER_LOG) << 
"\tDone";
 
  528        return {
true, QSqlQuery()};
 
  531    AkonadiSchema schema;
 
  532    const auto tables = schema.tables();
 
  533    for (
const auto &table : tables) {
 
  534        if (!hasForeignKeys(table)) {
 
  538        const auto &[
ok, 
query] = recreateTableWithForeignKeys(table);
 
  540            qCCritical(AKONADISERVER_LOG, 
"SQL error when updating table %s", qUtf8Printable(table.name));
 
  541            qCCritical(AKONADISERVER_LOG, 
"Query: %s", qUtf8Printable(
query.executedQuery()));
 
  542            qCCritical(AKONADISERVER_LOG, 
"Error: %s", qUtf8Printable(
query.lastError().text()));
 
  547    const auto relations = schema.relations();
 
  548    for (
const auto &relation : relations) {
 
  549        const RelationTableDescription table(relation);
 
  550        const auto &[
ok, 
query] = recreateTableWithForeignKeys(table);
 
  552            qCCritical(AKONADISERVER_LOG, 
"SQL error when updating relation table %s", qUtf8Printable(table.name));
 
  553            qCCritical(AKONADISERVER_LOG, 
"Query: %s", qUtf8Printable(
query.executedQuery()));
 
  554            qCCritical(AKONADISERVER_LOG, 
"Error: %s", qUtf8Printable(
query.lastError().text()));
 
  559    qCDebug(AKONADISERVER_LOG) << 
"Running VACUUM to reduce DB size";
 
  560    if (!
query.exec(QStringLiteral(
"VACUUM"))) {
 
  561        qCWarning(AKONADISERVER_LOG) << 
"Vacuum failed (not fatal, update will continue)";
 
  562        qCWarning(AKONADISERVER_LOG) << 
"Error:" << 
query.lastError().text();
 
  568#include "moc_dbupdater.cpp" 
static DataStore * dataStoreForDatabase(const QSqlDatabase &db)
Returns DataStore associated with the given database connection.
 
static DbInitializer::Ptr createInstance(const QSqlDatabase &database, Schema *schema=nullptr)
Returns an initializer instance for a given backend.
 
DbUpdater(const QSqlDatabase &database, const QString &filename)
Creates a new database updates.
 
bool run()
Starts the update process.
 
Type
Supported database types.
 
Type type(const QSqlDatabase &db)
Returns the type of the given database object.
 
Helper integration between Akonadi and Qt.
 
KSERVICE_EXPORT KService::List query(FilterFunc filterFunc)
 
KDB_EXPORT KDbVersionInfo version()
 
QString name(StandardAction id)
 
const QList< QKeySequence > & copy()
 
const char * constData() const const
 
QDBusError lastError() const const
 
QDBusConnection sessionBus()
 
bool unregisterService(const QString &serviceName)
 
QString message() const const
 
QDomElement documentElement() const const
 
ParseResult setContent(QAnyStringView text, ParseOptions options)
 
QString attribute(const QString &name, const QString &defValue) const const
 
QString tagName() const const
 
QString text() const const
 
QDomElement firstChildElement(const QString &tagName, const QString &namespaceURI) const const
 
bool isNull() const const
 
QDomElement nextSiblingElement(const QString &tagName, const QString &namespaceURI) const const
 
qint64 elapsed() const const
 
bool isEmpty() const const
 
T value(qsizetype i) const const
 
const_iterator constBegin() const const
 
const_iterator constEnd() const const
 
bool contains(const Key &key) const const
 
iterator insert(const Key &key, const T &value)
 
bool isEmpty() const const
 
QString arg(Args &&... args) const const
 
QString left(qsizetype n) const const
 
QString mid(qsizetype position, qsizetype n) const const
 
QStringList split(QChar sep, Qt::SplitBehavior behavior, Qt::CaseSensitivity cs) const const
 
int toInt(bool *ok, int base) const const
 
QByteArray toLatin1() const const
 
QString trimmed() const const
 
bool contains(QLatin1StringView str, Qt::CaseSensitivity cs) const const