Futuresql

threadeddatabase.cpp
1 // SPDX-FileCopyrightText: 2022 Jonah BrĂ¼chert <[email protected]>
2 //
3 // SPDX-License-Identifier: LGPL-2.1-only OR LGPL-3.0-only OR LicenseRef-KDE-Accepted-LGPL
4 
5 #include "threadeddatabase.h"
6 #include "threadeddatabase_p.h"
7 
8 #include <QDir>
9 #include <QSqlDatabase>
10 #include <QSqlQuery>
11 #include <QUrl>
12 #include <QStringBuilder>
13 #include <QVariant>
14 #include <QSqlResult>
15 #include <QSqlError>
16 #include <QLoggingCategory>
17 
18 #include <unordered_map>
19 
20 #define SCHAMA_MIGRATIONS_TABLE "__qt_schema_migrations"
21 
22 Q_DECLARE_LOGGING_CATEGORY(asyncdatabase)
23 Q_LOGGING_CATEGORY(asyncdatabase, "futuresql")
24 
25 namespace asyncdatabase_private {
26 
27 // migrations
28 void createInternalTable(QSqlDatabase &database) {
29  QSqlQuery query(QStringLiteral("create table if not exists " SCHAMA_MIGRATIONS_TABLE " ("
30  "version Text primary key not null, "
31  "run_on timestamp not null default current_timestamp)"), database);
32  if (!query.exec()) {
33  printSqlError(query);
34  }
35 }
36 
37 void markMigrationRun(QSqlDatabase &database, const QString &name) {
38  qCDebug(asyncdatabase) << "Marking migration" << name << "as done.";
39 
40  QSqlQuery query(database);
41  if (!query.prepare(QStringLiteral("insert into " SCHAMA_MIGRATIONS_TABLE " (version) values (:name)"))) {
42  printSqlError(query);
43  }
44  query.bindValue(QStringLiteral(":name"), name);
45  if (!query.exec()) {
46  printSqlError(query);
47  }
48 }
49 
50 QString currentDatabaseVersion(QSqlDatabase &database) {
51  QSqlQuery query(database);
52  query.prepare(QStringLiteral("select version from " SCHAMA_MIGRATIONS_TABLE " order by version desc limit 1"));
53  query.exec();
54 
55  if (query.next()) {
56  return query.value(0).toString();
57  } else {
58  return {};
59  }
60 }
61 
62 void runDatabaseMigrations(QSqlDatabase &database, const QString &migrationDirectory)
63 {
64  createInternalTable(database);
65 
66  QDir dir(migrationDirectory);
67  const auto entries = dir.entryList(QDir::Filter::Dirs | QDir::Filter::NoDotAndDotDot, QDir::SortFlag::Name);
68 
69  const QString currentVersion = currentDatabaseVersion(database);
70  for (const auto &entry : entries) {
71  QDir subdir(entry);
72  if (subdir.dirName() > currentVersion) {
73  QFile file(migrationDirectory % QDir::separator() % entry % QDir::separator() % u"up.sql");
74  if (!file.open(QFile::ReadOnly)) {
75  qCDebug(asyncdatabase) << "Failed to open migration file" << file.fileName();
76  }
77  qCDebug(asyncdatabase) << "Running migration" << subdir.dirName();
78 
79  database.transaction();
80 
81  // Hackish
82  const auto statements = file.readAll().split(';');
83 
84  bool migrationSuccessful = true;
85  for (const QByteArray &statement : statements) {
86  const auto trimmedStatement = QString::fromUtf8(statement.trimmed());
87  QSqlQuery query(database);
88 
89  if (!trimmedStatement.isEmpty()) {
90  qCDebug(asyncdatabase) << "Running" << trimmedStatement;
91  if (!query.prepare(trimmedStatement)) {
92  printSqlError(query);
93  migrationSuccessful = false;
94  } else {
95  bool success = query.exec();
96  migrationSuccessful &= success;
97  if (!success) {
98  printSqlError(query);
99  }
100  }
101  }
102  }
103 
104  if (migrationSuccessful) {
105  database.commit();
106  markMigrationRun(database, subdir.dirName());
107  } else {
108  qCWarning(asyncdatabase) << "Migration" << subdir.dirName() << "failed, retrying next time.";
109  qCWarning(asyncdatabase) << "Stopping migrations here, as the next migration may depens on this one.";
110  database.rollback();
111  return;
112  }
113  }
114  }
115  qCDebug(asyncdatabase) << "Migrations finished";
116 }
117 
118 struct AsyncSqlDatabasePrivate {
119  QSqlDatabase database;
120  std::unordered_map<QString, QSqlQuery> preparedQueryCache;
121 };
122 
123 // Internal asynchronous database class
124 QFuture<void> AsyncSqlDatabase::establishConnection(const DatabaseConfiguration &configuration)
125 {
126  return runAsync([=, this] {
127  d->database = QSqlDatabase::addDatabase(configuration.type());
128  if (configuration.databaseName()) {
129  d->database.setDatabaseName(*configuration.databaseName());
130  }
131  if (configuration.hostName()) {
132  d->database.setHostName(*configuration.hostName());
133  }
134  if (configuration.userName()) {
135  d->database.setUserName(*configuration.userName());
136  }
137  if (configuration.password()) {
138  d->database.setPassword(*configuration.password());
139  }
140 
141  if (!d->database.open()) {
142  qCDebug(asyncdatabase) << "Failed to open database" << d->database.lastError().text();
143  if (configuration.databaseName()) {
144  qCDebug(asyncdatabase) << "Tried to use database" << *configuration.databaseName();
145  }
146  }
147  });
148 }
149 
150 auto AsyncSqlDatabase::runMigrations(const QString &migrationDirectory) -> QFuture<void> {
151  return runAsync([=, this] {
152  runDatabaseMigrations(d->database, migrationDirectory);
153  });
154 }
155 auto AsyncSqlDatabase::setCurrentMigrationLevel(const QString &migrationName) -> QFuture<void> {
156  return runAsync([=, this] {
157  createInternalTable(d->database);
158  markMigrationRun(d->database, migrationName);
159  });
160 }
161 
162 AsyncSqlDatabase::AsyncSqlDatabase()
163  : QObject()
164  , d(std::make_unique<AsyncSqlDatabasePrivate>())
165 {
166 }
167 
168 AsyncSqlDatabase::~AsyncSqlDatabase() {
169  runAsync([db = d->database] {
170  QSqlDatabase::removeDatabase(db.databaseName());
171  });
172 };
173 
174 Row AsyncSqlDatabase::retrieveRow(const QSqlQuery &query) {
175  Row row;
176  int i = 0;
177 
178  while (true) {
179  if (query.isValid()) {
180  QVariant value = query.value(i);
181  if (value.isValid()) {
182  row.push_back(std::move(value));
183  i++;
184  } else {
185  break;
186  }
187  } else {
188  break;
189  }
190  }
191  return row;
192 }
193 
194 Rows AsyncSqlDatabase::retrieveRows(QSqlQuery &query)
195 {
196  Rows rows;
197  while (query.next()) {
198  rows.push_back(retrieveRow(query));
199  }
200 
201  return rows;
202 }
203 
204 std::optional<Row> AsyncSqlDatabase::retrieveOptionalRow(QSqlQuery &query)
205 {
206  query.next();
207 
208  if (query.isValid()) {
209  return retrieveRow(query);
210  } else {
211  return std::nullopt;
212  }
213 }
214 
215 QSqlDatabase &AsyncSqlDatabase::db()
216 {
217  return d->database;
218 }
219 
220 void printSqlError(const QSqlQuery &query)
221 {
222  qCDebug(asyncdatabase) << "SQL error:" << query.lastError().text();
223 }
224 
225 std::optional<QSqlQuery> AsyncSqlDatabase::prepareQuery(const QSqlDatabase &database, const QString &sqlQuery)
226 {
227  qCDebug(asyncdatabase) << "Running" << sqlQuery;
228 
229  // Check whether we already have a prepared version of this query
230  if (d->preparedQueryCache.contains(sqlQuery)) {
231  return d->preparedQueryCache[sqlQuery];
232  }
233 
234  // If not, prepare one
235  QSqlQuery query(database);
236 
237  // If this fails, return without caching the query
238  if (!query.prepare(sqlQuery)) {
239  printSqlError(query);
240  return {};
241  }
242 
243  // Else, cache the prepared query
244  d->preparedQueryCache.insert({sqlQuery, query});
245  return query;
246 }
247 
248 QSqlQuery AsyncSqlDatabase::runQuery(QSqlQuery &&query)
249 {
250  if (!query.exec()) {
251  printSqlError(query);
252  }
253  return std::move(query);
254 }
255 
256 }
257 
258 struct DatabaseConfigurationPrivate : public QSharedData {
259  QString type;
260  std::optional<QString> hostName;
261  std::optional<QString> databaseName;
262  std::optional<QString> userName;
263  std::optional<QString> password;
264 };
265 
266 DatabaseConfiguration::DatabaseConfiguration() : d(new DatabaseConfigurationPrivate)
267 {}
268 
269 DatabaseConfiguration::~DatabaseConfiguration() = default;
270 DatabaseConfiguration::DatabaseConfiguration(const DatabaseConfiguration &) = default;
271 
273  d->type = type;
274 }
275 
276 void DatabaseConfiguration::setType(DatabaseType type)
277 {
278  switch (type) {
279  case DatabaseType::SQLite:
280  d->type = QStringLiteral("QSQLITE");
281  return;
282  }
283 
284  Q_UNREACHABLE();
285 }
286 
288  return d->type;
289 }
290 
292  d->hostName = hostName;
293 }
294 
295 const std::optional<QString> &DatabaseConfiguration::hostName() const {
296  return d->hostName;
297 }
298 
300  d->databaseName = databaseName;
301 }
302 
303 const std::optional<QString> &DatabaseConfiguration::databaseName() const {
304  return d->databaseName;
305 }
306 
308  d->userName = userName;
309 }
310 
311 const std::optional<QString> &DatabaseConfiguration::userName() const {
312  return d->userName;
313 }
314 
316  d->password = password;
317 }
318 
319 const std::optional<QString> &DatabaseConfiguration::password() const {
320  return d->password;
321 }
322 
323 
324 struct ThreadedDatabasePrivate {
325  asyncdatabase_private::AsyncSqlDatabase db;
326 };
327 
328 std::unique_ptr<ThreadedDatabase> ThreadedDatabase::establishConnection(const DatabaseConfiguration &config) {
329  auto threadedDb = std::unique_ptr<ThreadedDatabase>(new ThreadedDatabase());
330  threadedDb->setObjectName(QStringLiteral("database thread"));
331  threadedDb->d->db.moveToThread(&*threadedDb);
332  threadedDb->start();
333  threadedDb->d->db.establishConnection(config);
334  return threadedDb;
335 }
336 
337 auto ThreadedDatabase::runMigrations(const QString &migrationDirectory) -> QFuture<void> {
338  return d->db.runMigrations(migrationDirectory);
339 }
340 
342  return d->db.setCurrentMigrationLevel(migrationName);
343 }
344 
345 ThreadedDatabase::ThreadedDatabase()
346  : QThread()
347  , d(std::make_unique<ThreadedDatabasePrivate>())
348 {
349 }
350 
351 ThreadedDatabase::~ThreadedDatabase()
352 {
353  quit();
354  wait();
355 }
356 
357 asyncdatabase_private::AsyncSqlDatabase &ThreadedDatabase::db()
358 {
359  return d->db;
360 }
auto runMigrations(const QString &migrationDirectory) -> QFuture< void >
Run the database migrations in the given directory.
std::optional< QSqlQuery > query(const QString &queryStatement)
bool isValid() const const
void setType(const QString &type)
Set the name of the database driver. If it is included in DatabaseType, use the enum overload instead...
QString fromUtf8(const char *str, int size)
void setUserName(const QString &userName)
Set user name.
Type type(const QSqlDatabase &db)
QChar separator()
void setHostName(const QString &hostName)
Set the hostname.
bool rollback()
bool wait(QDeadlineTimer deadline)
auto setCurrentMigrationLevel(const QString &migrationName) -> QFuture< void >
Declare that the database is currently at the state of the migration in the migration subdirectory mi...
void quit()
Options for connecting to a database.
QSqlDatabase addDatabase(const QString &type, const QString &connectionName)
KSharedConfigPtr config()
void setDatabaseName(const QString &databaseName)
Set the name of the database (path of the file for SQLite)
static std::unique_ptr< ThreadedDatabase > establishConnection(const DatabaseConfiguration &config)
Connect to a database.
A database connection that lives on a new thread.
KIOFILEWIDGETS_EXPORT QString dir(const QString &fileClass)
const char * name(StandardAction id)
const QString & type() const
Get the name of the database driver.
bool transaction()
QSqlDatabase database(const QString &connectionName, bool open)
void setPassword(const QString &password)
Set password.
This file is part of the KDE documentation.
Documentation copyright © 1996-2023 The KDE developers.
Generated on Wed Sep 27 2023 03:47:03 by doxygen 1.8.17 written by Dimitri van Heesch, © 1997-2006

KDE's Doxygen guidelines are available online.