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);
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";
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) {
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()) {
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;
169 while (!childElement.
isNull()) {
171 if (updateApplicable(childElement.
attribute(QStringLiteral(
"backends")))) {
172 updateSet.statements << buildRawSqlStatement(childElement);
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
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);
222bool DbUpdater::complexUpdate_25()
224 qCDebug(AKONADISERVER_LOG) <<
"Starting database update to version 25";
236 query.exec(QStringLiteral(
"ALTER TABLE PartTable_old RENAME TO PartTable"));
241 query.exec(QStringLiteral(
"DROP TABLE IF EXISTS PartTable_new"));
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";
266 description.name = QStringLiteral(
"PartTable_new");
269 idColumn.name = QStringLiteral(
"id");
270 idColumn.type = QStringLiteral(
"qint64");
271 idColumn.isAutoIncrement =
true;
272 idColumn.isPrimaryKey =
true;
273 description.columns << idColumn;
276 pimItemIdColumn.name = QStringLiteral(
"pimItemId");
277 pimItemIdColumn.type = QStringLiteral(
"qint64");
278 pimItemIdColumn.allowNull =
false;
279 description.columns << pimItemIdColumn;
282 partTypeIdColumn.name = QStringLiteral(
"partTypeId");
283 partTypeIdColumn.type = QStringLiteral(
"qint64");
284 partTypeIdColumn.allowNull =
false;
285 description.columns << partTypeIdColumn;
288 dataColumn.name = QStringLiteral(
"data");
289 dataColumn.type = QStringLiteral(
"QByteArray");
290 description.columns << dataColumn;
293 dataSizeColumn.name = QStringLiteral(
"datasize");
294 dataSizeColumn.type = QStringLiteral(
"qint64");
295 dataSizeColumn.allowNull =
false;
296 description.columns << dataSizeColumn;
299 versionColumn.name = QStringLiteral(
"version");
300 versionColumn.type = QStringLiteral(
"int");
301 versionColumn.defaultValue = QStringLiteral(
"0");
302 description.columns << versionColumn;
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);
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()) {
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";
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";
393 if (dbType == DbType::PostgreSQL || dbType == DbType::Sqlite) {
394 if (dbType == DbType::PostgreSQL) {
398 if (!
query.exec(QStringLiteral(
"ALTER TABLE PartTable RENAME TO PartTable_old"))) {
399 qCCritical(AKONADISERVER_LOG) <<
query.lastError().text();
405 if (!
query.exec(QStringLiteral(
"ALTER TABLE PartTable_new RENAME TO PartTable"))) {
406 qCCritical(AKONADISERVER_LOG) <<
query.lastError().text();
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";
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";
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");
461 if (!
query.exec(QStringLiteral(
"PRAGMA foreign_key_checks=OFF"))) {
462 qCCritical(AKONADISERVER_LOG,
"Failed to disable foreign key checks!");
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;
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));
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";
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) {
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"
A helper class that describes a column of a table for the DbInitializer.
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.
Helper class to construct arbitrary SQL queries.
TableDescription constructed based on RelationDescription.
A helper class that describes a table for the DbInitializer.
A helper class that contains an update set.
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(GameStandardAction id)
KIOCORE_EXPORT CopyJob * copy(const QList< QUrl > &src, const QUrl &dest, JobFlags flags=DefaultFlags)
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
QSqlError lastError() const const
QString text() 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