Akonadi

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

KDE's Doxygen guidelines are available online.