Akonadi

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

KDE's Doxygen guidelines are available online.