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 
7 #include <QDir>
8 #include <QSqlDatabase>
9 #include <QSqlQuery>
10 #include <QUrl>
11 #include <QStringBuilder>
12 #include <QVariant>
13 #include <QSqlResult>
14 #include <QSqlError>
15 #include <QLoggingCategory>
16 
17 #define SCHAMA_MIGRATIONS_TABLE "__qt_schema_migrations"
18 
19 Q_DECLARE_LOGGING_CATEGORY(asyncdatabase)
20 Q_LOGGING_CATEGORY(asyncdatabase, "futuresql")
21 
22 namespace asyncdatabase_private {
23 
24 // migrations
25 void createInternalTable(QSqlDatabase &database) {
26  QSqlQuery query(QStringLiteral("create table if not exists " SCHAMA_MIGRATIONS_TABLE " ("
27  "version Text primary key not null, "
28  "run_on timestamp not null default current_timestamp)"), database);
29  if (!query.exec()) {
30  printSqlError(query);
31  }
32 }
33 
34 void markMigrationRun(QSqlDatabase &database, const QString &name) {
35  qCDebug(asyncdatabase) << "Marking migration" << name << "as done.";
36 
37  QSqlQuery query(database);
38  if (!query.prepare(QStringLiteral("insert into " SCHAMA_MIGRATIONS_TABLE " (version) values (:name)"))) {
39  printSqlError(query);
40  }
41  query.bindValue(QStringLiteral(":name"), name);
42  if (!query.exec()) {
43  printSqlError(query);
44  }
45 }
46 
47 QString currentDatabaseVersion(QSqlDatabase &database) {
48  QSqlQuery query(database);
49  query.prepare(QStringLiteral("select version from " SCHAMA_MIGRATIONS_TABLE " order by version desc limit 1"));
50  query.exec();
51 
52  query.next();
53  return query.value(0).toString();
54 }
55 
56 void runDatabaseMigrations(QSqlDatabase &database, const QString &migrationDirectory)
57 {
58  createInternalTable(database);
59 
60  QDir dir(migrationDirectory);
61  const auto entries = dir.entryList(QDir::Filter::Dirs | QDir::Filter::NoDotAndDotDot, QDir::SortFlag::Name);
62 
63  const QString currentVersion = currentDatabaseVersion(database);
64  for (const auto &entry : entries) {
65  QDir subdir(entry);
66  if (subdir.dirName() > currentVersion) {
67  QFile file(migrationDirectory % QDir::separator() % entry % QDir::separator() % u"up.sql");
68  if (!file.open(QFile::ReadOnly)) {
69  qCDebug(asyncdatabase) << "Failed to open migration file" << file.fileName();
70  }
71  qCDebug(asyncdatabase) << "Running migration" << subdir.dirName();
72 
73  database.transaction();
74 
75  // Hackish
76  const auto statements = file.readAll().split(';');
77 
78  bool migrationSuccessful = true;
79  for (const QByteArray &statement : statements) {
80  const auto trimmedStatement = QString::fromUtf8(statement.trimmed());
81  QSqlQuery query(database);
82 
83  if (!trimmedStatement.isEmpty()) {
84  qCDebug(asyncdatabase) << "Running" << trimmedStatement;
85  if (!query.prepare(trimmedStatement)) {
86  printSqlError(query);
87  } else {
88  bool success = query.exec();
89  migrationSuccessful &= success;
90  if (!success) {
91  printSqlError(query);
92  }
93  }
94  }
95  }
96 
97  if (migrationSuccessful) {
98  database.commit();
99  markMigrationRun(database, subdir.dirName());
100  } else {
101  database.rollback();
102  }
103  }
104  }
105  qCDebug(asyncdatabase) << "Migrations finished";
106 }
107 
108 struct AsyncSqlDatabasePrivate {
109  QSqlDatabase database;
110 };
111 
112 // Internal asynchronous database class
113 QFuture<void> AsyncSqlDatabase::establishConnection(const DatabaseConfiguration &configuration)
114 {
115  return runAsync([=, this] {
116  d->database = QSqlDatabase::addDatabase(configuration.type());
117  if (configuration.databaseName()) {
118  d->database.setDatabaseName(*configuration.databaseName());
119  }
120  if (configuration.hostName()) {
121  d->database.setHostName(*configuration.hostName());
122  }
123  if (configuration.userName()) {
124  d->database.setUserName(*configuration.userName());
125  }
126  if (configuration.password()) {
127  d->database.setPassword(*configuration.password());
128  }
129 
130  if (!d->database.open()) {
131  qCDebug(asyncdatabase) << "Failed to open database" << d->database.lastError().text();
132  if (configuration.databaseName()) {
133  qCDebug(asyncdatabase) << "Tried to use database" << *configuration.databaseName();
134  }
135  }
136  });
137 }
138 
139 auto AsyncSqlDatabase::runMigrations(const QString &migrationDirectory) -> QFuture<void> {
140  return runAsync([=, this] {
141  runDatabaseMigrations(d->database, migrationDirectory);
142  });
143 }
144 auto AsyncSqlDatabase::setCurrentMigrationLevel(const QString &migrationName) -> QFuture<void> {
145  return runAsync([=, this] {
146  createInternalTable(d->database);
147  markMigrationRun(d->database, migrationName);
148  });
149 }
150 
151 AsyncSqlDatabase::AsyncSqlDatabase()
152  : QObject()
153  , d(std::make_unique<AsyncSqlDatabasePrivate>())
154 {
155 }
156 
157 AsyncSqlDatabase::~AsyncSqlDatabase() {
158  runAsync([db = d->database] {
159  QSqlDatabase::removeDatabase(db.databaseName());
160  });
161 };
162 
163 Row AsyncSqlDatabase::retrieveRow(const QSqlQuery &query) {
164  Row row;
165  int i = 0;
166 
167  while (true) {
168  if (query.isValid()) {
169  QVariant value = query.value(i);
170  if (value.isValid()) {
171  row.push_back(std::move(value));
172  i++;
173  } else {
174  break;
175  }
176  } else {
177  break;
178  }
179  }
180  return row;
181 }
182 
183 Rows AsyncSqlDatabase::retrieveRows(QSqlQuery &query)
184 {
185  Rows rows;
186  while (query.next()) {
187  rows.push_back(retrieveRow(query));
188  }
189 
190  return rows;
191 }
192 
193 std::optional<Row> AsyncSqlDatabase::retrieveOptionalRow(QSqlQuery &query)
194 {
195  query.next();
196 
197  if (query.isValid()) {
198  return retrieveRow(query);
199  } else {
200  return std::nullopt;
201  }
202 }
203 
204 QSqlDatabase &AsyncSqlDatabase::db()
205 {
206  return d->database;
207 }
208 
209 void printSqlError(const QSqlQuery &query)
210 {
211  qCDebug(asyncdatabase) << "SQL error:" << query.lastError().text();
212 }
213 
214 QSqlQuery prepareQuery(const QSqlDatabase &database, const QString &sqlQuery)
215 {
216  qCDebug(asyncdatabase) << "Running" << sqlQuery;
217  QSqlQuery query(database);
218  if (!query.prepare(sqlQuery)) {
219  printSqlError(query);
220  }
221  return query;
222 }
223 
224 QSqlQuery runQuery(QSqlQuery &&query)
225 {
226  if (!query.exec()) {
227  printSqlError(query);
228  }
229  return std::move(query);
230 }
231 
232 }
233 
234 struct DatabaseConfigurationPrivate : public QSharedData {
235  QString type;
236  std::optional<QString> hostName;
237  std::optional<QString> databaseName;
238  std::optional<QString> userName;
239  std::optional<QString> password;
240 };
241 
242 DatabaseConfiguration::DatabaseConfiguration() : d(new DatabaseConfigurationPrivate)
243 {}
244 
245 DatabaseConfiguration::~DatabaseConfiguration() = default;
246 DatabaseConfiguration::DatabaseConfiguration(const DatabaseConfiguration &) = default;
247 
249  d->type = type;
250 }
251 
252 void DatabaseConfiguration::setType(DatabaseType type)
253 {
254  switch (type) {
255  case DatabaseType::SQLite:
256  d->type = QStringLiteral("QSQLITE");
257  return;
258  }
259 
260  Q_UNREACHABLE();
261 }
262 
264  return d->type;
265 }
266 
268  d->hostName = hostName;
269 }
270 
271 const std::optional<QString> &DatabaseConfiguration::hostName() const {
272  return d->hostName;
273 }
274 
276  d->databaseName = databaseName;
277 }
278 
279 const std::optional<QString> &DatabaseConfiguration::databaseName() const {
280  return d->databaseName;
281 }
282 
284  d->userName = userName;
285 }
286 
287 const std::optional<QString> &DatabaseConfiguration::userName() const {
288  return d->userName;
289 }
290 
292  d->password = password;
293 }
294 
295 const std::optional<QString> &DatabaseConfiguration::password() const {
296  return d->password;
297 }
298 
299 
300 struct ThreadedDatabasePrivate {
301  asyncdatabase_private::AsyncSqlDatabase db;
302 };
303 
304 std::unique_ptr<ThreadedDatabase> ThreadedDatabase::establishConnection(const DatabaseConfiguration &config) {
305  auto threadedDb = std::unique_ptr<ThreadedDatabase>(new ThreadedDatabase());
306  threadedDb->setObjectName(QStringLiteral("database thread"));
307  threadedDb->d->db.moveToThread(&*threadedDb);
308  threadedDb->start();
309  threadedDb->d->db.establishConnection(config);
310  return threadedDb;
311 }
312 
313 auto ThreadedDatabase::runMigrations(const QString &migrationDirectory) -> QFuture<void> {
314  return d->db.runMigrations(migrationDirectory);
315 }
316 
318  return d->db.setCurrentMigrationLevel(migrationName);
319 }
320 
321 ThreadedDatabase::ThreadedDatabase()
322  : QThread()
323  , d(std::make_unique<ThreadedDatabasePrivate>())
324 {
325 }
326 
327 ThreadedDatabase::~ThreadedDatabase()
328 {
329  quit();
330  wait();
331 }
332 
333 asyncdatabase_private::AsyncSqlDatabase &ThreadedDatabase::db()
334 {
335  return d->db;
336 }
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)
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 Sat Apr 1 2023 03:55:59 by doxygen 1.8.17 written by Dimitri van Heesch, © 1997-2006

KDE's Doxygen guidelines are available online.