Akonadi

dbinitializer.cpp
1/***************************************************************************
2 * SPDX-FileCopyrightText: 2006 Tobias Koenig <tokoe@kde.org> *
3 * SPDX-FileCopyrightText: 2012 Volker Krause <vkrause@kde.org> *
4 * *
5 * SPDX-License-Identifier: LGPL-2.0-or-later *
6 ***************************************************************************/
7
8#include "dbinitializer.h"
9#include "akonadiserver_debug.h"
10#include "dbexception.h"
11#include "dbinitializer_p.h"
12#include "dbtype.h"
13#include "entities.h"
14#include "schema.h"
15#include "storage/datastore.h"
16
17#include <QDateTime>
18#include <QSqlQuery>
19#include <QStringList>
20
21#include <algorithm>
22
23#include "private/tristate_p.h"
24
25using namespace Akonadi::Server;
26
28{
30 switch (DbType::type(database)) {
31 case DbType::MySQL:
32 i.reset(new DbInitializerMySql(database));
33 break;
34 case DbType::Sqlite:
35 i.reset(new DbInitializerSqlite(database));
36 break;
37 case DbType::PostgreSQL:
38 i.reset(new DbInitializerPostgreSql(database));
39 break;
40 case DbType::Unknown:
41 qCCritical(AKONADISERVER_LOG) << database.driverName() << "backend not supported";
42 break;
43 }
44 i->mSchema = schema;
45 return i;
46}
47
49 : mDatabase(database)
50 , mSchema(nullptr)
51 , mTestInterface(nullptr)
52{
53 m_introspector = DbIntrospector::createInstance(mDatabase);
54}
55
59
61{
62 try {
63 qCInfo(AKONADISERVER_LOG) << "Running DB initializer";
64
65 const auto tables = mSchema->tables();
66 for (const TableDescription &table : tables) {
67 if (!checkTable(table)) {
68 return false;
69 }
70 }
71
72 const auto relations = mSchema->relations();
73 for (const RelationDescription &relation : relations) {
74 if (!checkRelation(relation)) {
75 return false;
76 }
77 }
78
79#ifndef DBINITIALIZER_UNITTEST
80 // Now finally check and set the generation identifier if necessary
81 auto store = DataStore::dataStoreForDatabase(mDatabase);
82 SchemaVersion version = SchemaVersion::retrieveAll(store).at(0);
83 if (version.generation() == 0) {
84 version.setGeneration(QDateTime::currentDateTimeUtc().toSecsSinceEpoch());
85 version.update(store);
86
87 qCDebug(AKONADISERVER_LOG) << "Generation:" << version.generation();
88 }
89#endif
90
91 qCInfo(AKONADISERVER_LOG) << "DB initializer done";
92 return true;
93 } catch (const DbException &e) {
94 mErrorMsg = QString::fromUtf8(e.what());
95 }
96 return false;
97}
98
99bool DbInitializer::checkTable(const TableDescription &tableDescription)
100{
101 qCDebug(AKONADISERVER_LOG) << "checking table " << tableDescription.name;
102
103 if (!m_introspector->hasTable(tableDescription.name)) {
104 // Get the CREATE TABLE statement for the specific SQL dialect
105 const QString createTableStatement = buildCreateTableStatement(tableDescription);
106 qCDebug(AKONADISERVER_LOG) << createTableStatement;
107 execQuery(createTableStatement);
108 } else {
109 // Check for every column whether it exists, and add the missing ones
110 for (const ColumnDescription &columnDescription : tableDescription.columns) {
111 if (!m_introspector->hasColumn(tableDescription.name, columnDescription.name)) {
112 // Don't add the column on update, DbUpdater will add it
113 if (columnDescription.noUpdate) {
114 continue;
115 }
116 // Get the ADD COLUMN statement for the specific SQL dialect
117 const QString statement = buildAddColumnStatement(tableDescription, columnDescription);
118 qCDebug(AKONADISERVER_LOG) << statement;
119 execQuery(statement);
120 }
121 }
122
123 // NOTE: we do intentionally not delete any columns here, we defer that to the updater,
124 // very likely previous columns contain data that needs to be moved to a new column first.
125 }
126
127 // Add initial data if table is empty
128 if (tableDescription.data.isEmpty()) {
129 return true;
130 }
131 if (m_introspector->isTableEmpty(tableDescription.name)) {
132 for (const DataDescription &dataDescription : tableDescription.data) {
133 // Get the INSERT VALUES statement for the specific SQL dialect
134 const QString statement = buildInsertValuesStatement(tableDescription, dataDescription);
135 qCDebug(AKONADISERVER_LOG) << statement;
136 execQuery(statement);
137 }
138 }
139
140 return true;
141}
142
143void DbInitializer::checkForeignKeys(const TableDescription &tableDescription)
144{
145 try {
146 const QList<DbIntrospector::ForeignKey> existingForeignKeys = m_introspector->foreignKeyConstraints(tableDescription.name);
147 for (const ColumnDescription &column : tableDescription.columns) {
148 DbIntrospector::ForeignKey existingForeignKey;
149 for (const DbIntrospector::ForeignKey &fk : existingForeignKeys) {
150 if (QString::compare(fk.column, column.name, Qt::CaseInsensitive) == 0) {
151 existingForeignKey = fk;
152 break;
153 }
154 }
155
156 if (!column.refTable.isEmpty() && !column.refColumn.isEmpty()) {
157 if (!existingForeignKey.column.isEmpty()) {
158 // there's a constraint on this column, check if it's the correct one
159 if (QString::compare(existingForeignKey.refTable, column.refTable + QLatin1StringView("table"), Qt::CaseInsensitive) == 0
160 && QString::compare(existingForeignKey.refColumn, column.refColumn, Qt::CaseInsensitive) == 0
161 && QString::compare(existingForeignKey.onUpdate, referentialActionToString(column.onUpdate), Qt::CaseInsensitive) == 0
162 && QString::compare(existingForeignKey.onDelete, referentialActionToString(column.onDelete), Qt::CaseInsensitive) == 0) {
163 continue; // all good
164 }
165
166 const auto statements = buildRemoveForeignKeyConstraintStatements(existingForeignKey, tableDescription);
167 if (!statements.isEmpty()) {
168 qCDebug(AKONADISERVER_LOG) << "Found existing foreign constraint that doesn't match the schema:" << existingForeignKey.name
169 << existingForeignKey.column << existingForeignKey.refTable << existingForeignKey.refColumn;
170 m_removedForeignKeys << statements;
171 }
172 }
173
174 const auto statements = buildAddForeignKeyConstraintStatements(tableDescription, column);
175 if (statements.isEmpty()) { // not supported
176 return;
177 }
178
179 m_pendingForeignKeys << statements;
180
181 } else if (!existingForeignKey.column.isEmpty()) {
182 // constraint exists but we don't want one here
183 const auto statements = buildRemoveForeignKeyConstraintStatements(existingForeignKey, tableDescription);
184 if (!statements.isEmpty()) {
185 qCDebug(AKONADISERVER_LOG) << "Found unexpected foreign key constraint:" << existingForeignKey.name << existingForeignKey.column
186 << existingForeignKey.refTable << existingForeignKey.refColumn;
187 m_removedForeignKeys << statements;
188 }
189 }
190 }
191 } catch (const DbException &e) {
192 qCDebug(AKONADISERVER_LOG) << "Fixing foreign key constraints failed:" << e.what();
193 }
194}
195
196void DbInitializer::checkIndexes(const TableDescription &tableDescription)
197{
198 // Add indices
199 for (const IndexDescription &indexDescription : tableDescription.indexes) {
200 // sqlite3 needs unique index identifiers per db
201 const QString indexName = QStringLiteral("%1_%2").arg(tableDescription.name, indexDescription.name);
202 if (!m_introspector->hasIndex(tableDescription.name, indexName)) {
203 // Get the CREATE INDEX statement for the specific SQL dialect
204 m_pendingIndexes << buildCreateIndexStatement(tableDescription, indexDescription);
205 }
206 }
207}
208
209bool DbInitializer::checkRelation(const RelationDescription &relationDescription)
210{
211 return checkTable(RelationTableDescription(relationDescription));
212}
213
215{
216 return mErrorMsg;
217}
218
220{
221 const auto tables = mSchema->tables();
222 for (const TableDescription &table : tables) {
223 // Make sure the foreign key constraints are all there
224 checkForeignKeys(table);
225 checkIndexes(table);
226 }
227 const auto relations = mSchema->relations();
228 for (const RelationDescription &relation : relations) {
229 RelationTableDescription relTable(relation);
230 checkForeignKeys(relTable);
231 checkIndexes(relTable);
232 }
233
234 try {
235 if (!m_pendingIndexes.isEmpty()) {
236 qCDebug(AKONADISERVER_LOG) << "Updating indexes";
237 execPendingQueries(m_pendingIndexes);
238 m_pendingIndexes.clear();
239 }
240
241 if (!m_removedForeignKeys.isEmpty()) {
242 qCDebug(AKONADISERVER_LOG) << "Removing invalid foreign key constraints";
243 execPendingQueries(m_removedForeignKeys);
244 m_removedForeignKeys.clear();
245 }
246
247 if (!m_pendingForeignKeys.isEmpty()) {
248 qCDebug(AKONADISERVER_LOG) << "Adding new foreign key constraints";
249 execPendingQueries(m_pendingForeignKeys);
250 m_pendingForeignKeys.clear();
251 }
252 } catch (const DbException &e) {
253 qCCritical(AKONADISERVER_LOG) << "Updating index failed: " << e.what();
254 return false;
255 }
256
257 qCDebug(AKONADISERVER_LOG) << "Indexes successfully created";
258 return true;
259}
260
261void DbInitializer::execPendingQueries(const QStringList &queries)
262{
263 for (const QString &statement : queries) {
264 qCDebug(AKONADISERVER_LOG) << statement;
265 execQuery(statement);
266 }
267}
268
270{
271 Q_UNUSED(size)
272 if (col.type == QLatin1StringView("int")) {
273 return QStringLiteral("INTEGER");
274 }
275 if (col.type == QLatin1StringView("qint64")) {
276 return QStringLiteral("BIGINT");
277 }
278 if (col.type == QLatin1StringView("QString")) {
279 return QStringLiteral("TEXT");
280 }
281 if (col.type == QLatin1StringView("QByteArray")) {
282 return QStringLiteral("LONGBLOB");
283 }
284 if (col.type == QLatin1StringView("QDateTime")) {
285 return QStringLiteral("TIMESTAMP");
286 }
287 if (col.type == QLatin1StringView("bool")) {
288 return QStringLiteral("BOOL");
289 }
290 if (col.isEnum) {
291 return QStringLiteral("TINYINT");
292 }
293
294 qCCritical(AKONADISERVER_LOG) << "Invalid type" << col.type;
295 Q_ASSERT(false);
296 return QString();
297}
298
300{
301 if (col.type == QLatin1StringView("QDateTime") && value == QLatin1StringView("QDateTime::currentDateTimeUtc()")) {
302 return QStringLiteral("CURRENT_TIMESTAMP");
303 } else if (col.isEnum) {
304 return QString::number(col.enumValueMap[value]);
305 }
306 return value;
307}
308
309QString DbInitializer::buildAddColumnStatement(const TableDescription &tableDescription, const ColumnDescription &columnDescription) const
310{
311 return QStringLiteral("ALTER TABLE %1 ADD COLUMN %2").arg(tableDescription.name, buildColumnStatement(columnDescription, tableDescription));
312}
313
314QString DbInitializer::buildCreateIndexStatement(const TableDescription &tableDescription, const IndexDescription &indexDescription) const
315{
316 const QString indexName = QStringLiteral("%1_%2").arg(tableDescription.name, indexDescription.name);
317 QStringList columns;
318 if (indexDescription.sort.isEmpty()) {
319 columns = indexDescription.columns;
320 } else {
321 columns.reserve(indexDescription.columns.count());
322 std::transform(indexDescription.columns.cbegin(),
323 indexDescription.columns.cend(),
324 std::back_insert_iterator<QStringList>(columns),
325 [&indexDescription](const QString &column) {
326 return QStringLiteral("%1 %2").arg(column, indexDescription.sort);
327 });
328 }
329
330 return QStringLiteral("CREATE %1 INDEX %2 ON %3 (%4)")
331 .arg(indexDescription.isUnique ? QStringLiteral("UNIQUE") : QString(), indexName, tableDescription.name, columns.join(QLatin1Char(',')));
332}
333
335{
336 Q_UNUSED(table)
337 Q_UNUSED(column)
338 return {};
339}
340
342{
343 Q_UNUSED(fk)
344 Q_UNUSED(table)
345 return {};
346}
347
348QString DbInitializer::buildReferentialAction(ColumnDescription::ReferentialAction onUpdate, ColumnDescription::ReferentialAction onDelete)
349{
350 return QLatin1StringView("ON UPDATE ") + referentialActionToString(onUpdate) + QLatin1StringView(" ON DELETE ") + referentialActionToString(onDelete);
351}
352
353QString DbInitializer::referentialActionToString(ColumnDescription::ReferentialAction action)
354{
355 switch (action) {
356 case ColumnDescription::Cascade:
357 return QStringLiteral("CASCADE");
358 case ColumnDescription::Restrict:
359 return QStringLiteral("RESTRICT");
360 case ColumnDescription::SetNull:
361 return QStringLiteral("SET NULL");
362 }
363
364 Q_ASSERT(!"invalid referential action enum!");
365 return QString();
366}
367
369{
370 QStringList cols;
371 for (const ColumnDescription &column : std::as_const(table.columns)) {
372 if (column.isPrimaryKey) {
373 cols.push_back(column.name);
374 }
375 }
376 return QLatin1StringView("PRIMARY KEY (") + cols.join(QLatin1StringView(", ")) + QLatin1Char(')');
377}
378
379void DbInitializer::execQuery(const QString &queryString)
380{
381 // if ( Q_UNLIKELY( mTestInterface ) ) { Qt 4.7 has no Q_UNLIKELY yet
382 if (mTestInterface) {
383 mTestInterface->execStatement(queryString);
384 return;
385 }
386
387 QSqlQuery query(mDatabase);
388 if (!query.exec(queryString)) {
389 throw DbException(query);
390 }
391}
392
393void DbInitializer::setTestInterface(TestInterface *interface)
394{
395 mTestInterface = interface;
396}
397
398void DbInitializer::setIntrospector(const DbIntrospector::Ptr &introspector)
399{
400 m_introspector = introspector;
401}
A helper class that describes a column of a table for the DbInitializer.
Definition schematypes.h:23
A helper class that describes the predefined data of a table for the DbInitializer.
Definition schematypes.h:65
static DataStore * dataStoreForDatabase(const QSqlDatabase &db)
Returns DataStore associated with the given database connection.
Definition datastore.cpp:98
Exception for reporting SQL errors.
Definition dbexception.h:19
static DbInitializer::Ptr createInstance(const QSqlDatabase &database, Schema *schema=nullptr)
Returns an initializer instance for a given backend.
virtual QStringList buildRemoveForeignKeyConstraintStatements(const DbIntrospector::ForeignKey &fk, const TableDescription &table) const
Returns an SQL statements to remove the foreign key constraint fk from table table.
virtual ~DbInitializer()
Destroys the database initializer.
virtual QString sqlValue(const ColumnDescription &col, const QString &value) const
Overwrite in backend-specific sub-classes to return the SQL value for a given C++ value.
DbInitializer(const QSqlDatabase &database)
Creates a new database initializer.
bool run()
Starts the initialization process.
virtual QString sqlType(const ColumnDescription &col, int size) const
Overwrite in backend-specific sub-classes to return the SQL type for a given C++ type.
QString errorMsg() const
Returns the textual description of an occurred error.
virtual QStringList buildAddForeignKeyConstraintStatements(const TableDescription &table, const ColumnDescription &column) const
Returns an SQL statements to add a foreign key constraint to an existing column column.
bool updateIndexesAndConstraints()
Checks and creates missing indexes.
virtual QString buildCreateTableStatement(const TableDescription &tableDescription) const =0
Returns a backend-specific CREATE TABLE SQL query describing given table.
static QString buildPrimaryKeyStatement(const TableDescription &table)
Use for multi-column primary keys during table creation.
A structure describing an existing foreign key.
static DbIntrospector::Ptr createInstance(const QSqlDatabase &database)
Returns an introspector instance for a given database.
A helper class that describes indexes of a table for the DbInitializer.
Definition schematypes.h:53
A helper class that describes the relation between two tables for the DbInitializer.
Definition schematypes.h:91
TableDescription constructed based on RelationDescription.
Methods to access the desired database schema (.
Definition schema.h:19
virtual QList< RelationDescription > relations()=0
List of relations (N:M helper tables) in the schema.
virtual QList< TableDescription > tables()=0
List of tables in the schema.
A helper class that describes a table for the DbInitializer.
Definition schematypes.h:77
Type type(const QSqlDatabase &db)
Returns the type of the given database object.
Definition dbtype.cpp:11
QDateTime currentDateTimeUtc()
const_iterator cbegin() const const
const_iterator cend() const const
void clear()
qsizetype count() const const
bool isEmpty() const const
void push_back(parameter_type value)
void reserve(qsizetype size)
QString driverName() const const
QString arg(Args &&... args) const const
int compare(QLatin1StringView s1, const QString &s2, Qt::CaseSensitivity cs)
QString fromUtf8(QByteArrayView str)
bool isEmpty() const const
QString number(double n, char format, int precision)
QString join(QChar separator) const const
CaseInsensitive
This file is part of the KDE documentation.
Documentation copyright © 1996-2024 The KDE developers.
Generated on Sat Dec 21 2024 17:01:42 by doxygen 1.12.0 written by Dimitri van Heesch, © 1997-2006

KDE's Doxygen guidelines are available online.