server/storage/dbinitializer.cpp

0001 /***************************************************************************
0002  *   SPDX-FileCopyrightText: 2006 Tobias Koenig <tokoe@kde.org>            *
0003  *   SPDX-FileCopyrightText: 2012 Volker Krause <vkrause@kde.org>          *
0004  *                                                                         *
0005  *   SPDX-License-Identifier: LGPL-2.0-or-later                            *
0006  ***************************************************************************/
0007
0008 #include "dbinitializer.h"
0009 #include "akonadiserver_debug.h"
0010 #include "dbexception.h"
0011 #include "dbinitializer_p.h"
0012 #include "dbtype.h"
0013 #include "entities.h"
0014 #include "schema.h"
0015 #include "storage/datastore.h"
0016
0017 #include <QDateTime>
0018 #include <QSqlQuery>
0019 #include <QStringList>
0020
0021 #include <algorithm>
0022
0023 #include "private/tristate_p.h"
0024
0025 using namespace Akonadi::Server;
0026
0027 DbInitializer::Ptr DbInitializer::createInstance(const QSqlDatabase &database, Schema *schema)
0028 {
0029     DbInitializer::Ptr i;
0030     switch (DbType::type(database)) {
0031     case DbType::MySQL:
0032         i.reset(new DbInitializerMySql(database));
0033         break;
0034     case DbType::Sqlite:
0035         i.reset(new DbInitializerSqlite(database));
0036         break;
0037     case DbType::PostgreSQL:
0038         i.reset(new DbInitializerPostgreSql(database));
0039         break;
0040     case DbType::Unknown:
0041         qCCritical(AKONADISERVER_LOG) << database.driverName() << "backend not supported";
0042         break;
0043     }
0044     i->mSchema = schema;
0045     return i;
0046 }
0047
0048 DbInitializer::DbInitializer(const QSqlDatabase &database)
0049     : mDatabase(database)
0050     , mSchema(nullptr)
0051     , mTestInterface(nullptr)
0052 {
0053     m_introspector = DbIntrospector::createInstance(mDatabase);
0054 }
0055
0056 DbInitializer::~DbInitializer()
0057 {
0058 }
0059
0060 bool DbInitializer::run()
0061 {
0062     try {
0063         qCInfo(AKONADISERVER_LOG) << "Running DB initializer";
0064
0065         const auto tables = mSchema->tables();
0066         for (const TableDescription &table : tables) {
0067             if (!checkTable(table)) {
0068                 return false;
0069             }
0070         }
0071
0072         const auto relations = mSchema->relations();
0073         for (const RelationDescription &relation : relations) {
0074             if (!checkRelation(relation)) {
0075                 return false;
0076             }
0077         }
0078
0079 #ifndef DBINITIALIZER_UNITTEST
0080         // Now finally check and set the generation identifier if necessary
0081         auto store = DataStore::dataStoreForDatabase(mDatabase);
0082         SchemaVersion version = SchemaVersion::retrieveAll(store).at(0);
0083         if (version.generation() == 0) {
0084             version.setGeneration(QDateTime::currentDateTimeUtc().toSecsSinceEpoch());
0085             version.update(store);
0086
0087             qCDebug(AKONADISERVER_LOG) << "Generation:" << version.generation();
0088         }
0089 #endif
0090
0091         qCInfo(AKONADISERVER_LOG) << "DB initializer done";
0092         return true;
0093     } catch (const DbException &e) {
0094         mErrorMsg = QString::fromUtf8(e.what());
0095     }
0096     return false;
0097 }
0098
0099 bool DbInitializer::checkTable(const TableDescription &tableDescription)
0100 {
0101     qCDebug(AKONADISERVER_LOG) << "checking table " << tableDescription.name;
0102
0103     if (!m_introspector->hasTable(tableDescription.name)) {
0104         // Get the CREATE TABLE statement for the specific SQL dialect
0105         const QString createTableStatement = buildCreateTableStatement(tableDescription);
0106         qCDebug(AKONADISERVER_LOG) << createTableStatement;
0107         execQuery(createTableStatement);
0108     } else {
0109         // Check for every column whether it exists, and add the missing ones
0110         for (const ColumnDescription &columnDescription : tableDescription.columns) {
0111             if (!m_introspector->hasColumn(tableDescription.name, columnDescription.name)) {
0112                 // Don't add the column on update, DbUpdater will add it
0113                 if (columnDescription.noUpdate) {
0114                     continue;
0115                 }
0116                 // Get the ADD COLUMN statement for the specific SQL dialect
0117                 const QString statement = buildAddColumnStatement(tableDescription, columnDescription);
0118                 qCDebug(AKONADISERVER_LOG) << statement;
0119                 execQuery(statement);
0120             }
0121         }
0122
0123         // NOTE: we do intentionally not delete any columns here, we defer that to the updater,
0124         // very likely previous columns contain data that needs to be moved to a new column first.
0125     }
0126
0127     // Add initial data if table is empty
0128     if (tableDescription.data.isEmpty()) {
0129         return true;
0130     }
0131     if (m_introspector->isTableEmpty(tableDescription.name)) {
0132         for (const DataDescription &dataDescription : tableDescription.data) {
0133             // Get the INSERT VALUES statement for the specific SQL dialect
0134             const QString statement = buildInsertValuesStatement(tableDescription, dataDescription);
0135             qCDebug(AKONADISERVER_LOG) << statement;
0136             execQuery(statement);
0137         }
0138     }
0139
0140     return true;
0141 }
0142
0143 void DbInitializer::checkForeignKeys(const TableDescription &tableDescription)
0144 {
0145     try {
0146         const QList<DbIntrospector::ForeignKey> existingForeignKeys = m_introspector->foreignKeyConstraints(tableDescription.name);
0147         for (const ColumnDescription &column : tableDescription.columns) {
0148             DbIntrospector::ForeignKey existingForeignKey;
0149             for (const DbIntrospector::ForeignKey &fk : existingForeignKeys) {
0150                 if (QString::compare(fk.column, column.name, Qt::CaseInsensitive) == 0) {
0151                     existingForeignKey = fk;
0152                     break;
0153                 }
0154             }
0155
0156             if (!column.refTable.isEmpty() && !column.refColumn.isEmpty()) {
0157                 if (!existingForeignKey.column.isEmpty()) {
0158                     // there's a constraint on this column, check if it's the correct one
0159                     if (QString::compare(existingForeignKey.refTable, column.refTable + QLatin1StringView("table"), Qt::CaseInsensitive) == 0
0160                         && QString::compare(existingForeignKey.refColumn, column.refColumn, Qt::CaseInsensitive) == 0
0161                         && QString::compare(existingForeignKey.onUpdate, referentialActionToString(column.onUpdate), Qt::CaseInsensitive) == 0
0162                         && QString::compare(existingForeignKey.onDelete, referentialActionToString(column.onDelete), Qt::CaseInsensitive) == 0) {
0163                         continue; // all good
0164                     }
0165
0166                     const auto statements = buildRemoveForeignKeyConstraintStatements(existingForeignKey, tableDescription);
0167                     if (!statements.isEmpty()) {
0168                         qCDebug(AKONADISERVER_LOG) << "Found existing foreign constraint that doesn't match the schema:" << existingForeignKey.name
0169                                                    << existingForeignKey.column << existingForeignKey.refTable << existingForeignKey.refColumn;
0170                         m_removedForeignKeys << statements;
0171                     }
0172                 }
0173
0174                 const auto statements = buildAddForeignKeyConstraintStatements(tableDescription, column);
0175                 if (statements.isEmpty()) { // not supported
0176                     return;
0177                 }
0178
0179                 m_pendingForeignKeys << statements;
0180
0181             } else if (!existingForeignKey.column.isEmpty()) {
0182                 // constraint exists but we don't want one here
0183                 const auto statements = buildRemoveForeignKeyConstraintStatements(existingForeignKey, tableDescription);
0184                 if (!statements.isEmpty()) {
0185                     qCDebug(AKONADISERVER_LOG) << "Found unexpected foreign key constraint:" << existingForeignKey.name << existingForeignKey.column
0186                                                << existingForeignKey.refTable << existingForeignKey.refColumn;
0187                     m_removedForeignKeys << statements;
0188                 }
0189             }
0190         }
0191     } catch (const DbException &e) {
0192         qCDebug(AKONADISERVER_LOG) << "Fixing foreign key constraints failed:" << e.what();
0193     }
0194 }
0195
0196 void DbInitializer::checkIndexes(const TableDescription &tableDescription)
0197 {
0198     // Add indices
0199     for (const IndexDescription &indexDescription : tableDescription.indexes) {
0200         // sqlite3 needs unique index identifiers per db
0201         const QString indexName = QStringLiteral("%1_%2").arg(tableDescription.name, indexDescription.name);
0202         if (!m_introspector->hasIndex(tableDescription.name, indexName)) {
0203             // Get the CREATE INDEX statement for the specific SQL dialect
0204             m_pendingIndexes << buildCreateIndexStatement(tableDescription, indexDescription);
0205         }
0206     }
0207 }
0208
0209 bool DbInitializer::checkRelation(const RelationDescription &relationDescription)
0210 {
0211     return checkTable(RelationTableDescription(relationDescription));
0212 }
0213
0214 QString DbInitializer::errorMsg() const
0215 {
0216     return mErrorMsg;
0217 }
0218
0219 bool DbInitializer::updateIndexesAndConstraints()
0220 {
0221     const auto tables = mSchema->tables();
0222     for (const TableDescription &table : tables) {
0223         // Make sure the foreign key constraints are all there
0224         checkForeignKeys(table);
0225         checkIndexes(table);
0226     }
0227     const auto relations = mSchema->relations();
0228     for (const RelationDescription &relation : relations) {
0229         RelationTableDescription relTable(relation);
0230         checkForeignKeys(relTable);
0231         checkIndexes(relTable);
0232     }
0233
0234     try {
0235         if (!m_pendingIndexes.isEmpty()) {
0236             qCDebug(AKONADISERVER_LOG) << "Updating indexes";
0237             execPendingQueries(m_pendingIndexes);
0238             m_pendingIndexes.clear();
0239         }
0240
0241         if (!m_removedForeignKeys.isEmpty()) {
0242             qCDebug(AKONADISERVER_LOG) << "Removing invalid foreign key constraints";
0243             execPendingQueries(m_removedForeignKeys);
0244             m_removedForeignKeys.clear();
0245         }
0246
0247         if (!m_pendingForeignKeys.isEmpty()) {
0248             qCDebug(AKONADISERVER_LOG) << "Adding new foreign key constraints";
0249             execPendingQueries(m_pendingForeignKeys);
0250             m_pendingForeignKeys.clear();
0251         }
0252     } catch (const DbException &e) {
0253         qCCritical(AKONADISERVER_LOG) << "Updating index failed: " << e.what();
0254         return false;
0255     }
0256
0257     qCDebug(AKONADISERVER_LOG) << "Indexes successfully created";
0258     return true;
0259 }
0260
0261 void DbInitializer::execPendingQueries(const QStringList &queries)
0262 {
0263     for (const QString &statement : queries) {
0264         qCDebug(AKONADISERVER_LOG) << statement;
0265         execQuery(statement);
0266     }
0267 }
0268
0269 QString DbInitializer::sqlType(const ColumnDescription &col, int size) const
0270 {
0271     Q_UNUSED(size)
0272     if (col.type == QLatin1StringView("int")) {
0273         return QStringLiteral("INTEGER");
0274     }
0275     if (col.type == QLatin1StringView("qint64")) {
0276         return QStringLiteral("BIGINT");
0277     }
0278     if (col.type == QLatin1StringView("QString")) {
0279         return QStringLiteral("TEXT");
0280     }
0281     if (col.type == QLatin1StringView("QByteArray")) {
0282         return QStringLiteral("LONGBLOB");
0283     }
0284     if (col.type == QLatin1StringView("QDateTime")) {
0285         return QStringLiteral("TIMESTAMP");
0286     }
0287     if (col.type == QLatin1StringView("bool")) {
0288         return QStringLiteral("BOOL");
0289     }
0290     if (col.isEnum) {
0291         return QStringLiteral("TINYINT");
0292     }
0293
0294     qCCritical(AKONADISERVER_LOG) << "Invalid type" << col.type;
0295     Q_ASSERT(false);
0296     return QString();
0297 }
0298
0299 QString DbInitializer::sqlValue(const ColumnDescription &col, const QString &value) const
0300 {
0301     if (col.type == QLatin1StringView("QDateTime") && value == QLatin1StringView("QDateTime::currentDateTimeUtc()")) {
0302         return QStringLiteral("CURRENT_TIMESTAMP");
0303     } else if (col.isEnum) {
0304         return QString::number(col.enumValueMap[value]);
0305     }
0306     return value;
0307 }
0308
0309 QString DbInitializer::buildAddColumnStatement(const TableDescription &tableDescription, const ColumnDescription &columnDescription) const
0310 {
0311     return QStringLiteral("ALTER TABLE %1 ADD COLUMN %2").arg(tableDescription.name, buildColumnStatement(columnDescription, tableDescription));
0312 }
0313
0314 QString DbInitializer::buildCreateIndexStatement(const TableDescription &tableDescription, const IndexDescription &indexDescription) const
0315 {
0316     const QString indexName = QStringLiteral("%1_%2").arg(tableDescription.name, indexDescription.name);
0317     QStringList columns;
0318     if (indexDescription.sort.isEmpty()) {
0319         columns = indexDescription.columns;
0320     } else {
0321         columns.reserve(indexDescription.columns.count());
0322         std::transform(indexDescription.columns.cbegin(),
0323                        indexDescription.columns.cend(),
0324                        std::back_insert_iterator<QStringList>(columns),
0325                        [&indexDescription](const QString &column) {
0326                            return QStringLiteral("%1 %2").arg(column, indexDescription.sort);
0327                        });
0328     }
0329
0330     return QStringLiteral("CREATE %1 INDEX %2 ON %3 (%4)")
0331         .arg(indexDescription.isUnique ? QStringLiteral("UNIQUE") : QString(), indexName, tableDescription.name, columns.join(QLatin1Char(',')));
0332 }
0333
0334 QStringList DbInitializer::buildAddForeignKeyConstraintStatements(const TableDescription &table, const ColumnDescription &column) const
0335 {
0336     Q_UNUSED(table)
0337     Q_UNUSED(column)
0338     return {};
0339 }
0340
0341 QStringList DbInitializer::buildRemoveForeignKeyConstraintStatements(const DbIntrospector::ForeignKey &fk, const TableDescription &table) const
0342 {
0343     Q_UNUSED(fk)
0344     Q_UNUSED(table)
0345     return {};
0346 }
0347
0348 QString DbInitializer::buildReferentialAction(ColumnDescription::ReferentialAction onUpdate, ColumnDescription::ReferentialAction onDelete)
0349 {
0350     return QLatin1StringView("ON UPDATE ") + referentialActionToString(onUpdate) + QLatin1StringView(" ON DELETE ") + referentialActionToString(onDelete);
0351 }
0352
0353 QString DbInitializer::referentialActionToString(ColumnDescription::ReferentialAction action)
0354 {
0355     switch (action) {
0356     case ColumnDescription::Cascade:
0357         return QStringLiteral("CASCADE");
0358     case ColumnDescription::Restrict:
0359         return QStringLiteral("RESTRICT");
0360     case ColumnDescription::SetNull:
0361         return QStringLiteral("SET NULL");
0362     }
0363
0364     Q_ASSERT(!"invalid referential action enum!");
0365     return QString();
0366 }
0367
0368 QString DbInitializer::buildPrimaryKeyStatement(const TableDescription &table)
0369 {
0370     QStringList cols;
0371     for (const ColumnDescription &column : std::as_const(table.columns)) {
0372         if (column.isPrimaryKey) {
0373             cols.push_back(column.name);
0374         }
0375     }
0376     return QLatin1StringView("PRIMARY KEY (") + cols.join(QLatin1StringView(", ")) + QLatin1Char(')');
0377 }
0378
0379 void DbInitializer::execQuery(const QString &queryString)
0380 {
0381     // if ( Q_UNLIKELY( mTestInterface ) ) { Qt 4.7 has no Q_UNLIKELY yet
0382     if (mTestInterface) {
0383         mTestInterface->execStatement(queryString);
0384         return;
0385     }
0386
0387     QSqlQuery query(mDatabase);
0388     if (!query.exec(queryString)) {
0389         throw DbException(query);
0390     }
0391 }
0392
0393 void DbInitializer::setTestInterface(TestInterface *interface)
0394 {
0395     mTestInterface = interface;
0396 }
0397
0398 void DbInitializer::setIntrospector(const DbIntrospector::Ptr &introspector)
0399 {
0400     m_introspector = introspector;
0401 }