1 #include "sqlitedb.h"
2 #include "sqlite.h"
3 #include "sqlitetablemodel.h"
4 #include "CipherDialog.h"
5 #include "CipherSettings.h"
6 #include "DotenvFormat.h"
7 #include "Settings.h"
8
9 #include <QFile>
10 #include <QMessageBox>
11 #include <QProgressDialog>
12 #include <QApplication>
13 #include <QInputDialog>
14 #include <QFileInfo>
15 #include <QDir>
16 #include <QDebug>
17 #include <QThread>
18 #include <QRegularExpression>
19 #include <json.hpp>
20
21 #include <algorithm>
22 #include <array>
23 #include <atomic>
24 #include <cctype>
25 #include <chrono>
26 #include <functional>
27
28 using json = nlohmann::json;
29
30 QStringList DBBrowserDB::Datatypes = {"INTEGER", "TEXT", "BLOB", "REAL", "NUMERIC"};
31
32 // Helper template to allow turning member functions into a C-style function pointer
33 // See https://stackoverflow.com/questions/19808054/convert-c-function-pointer-to-c-function-pointer/19809787
34 template <typename T>
35 struct Callback;
36 template <typename Ret, typename... Params>
37 struct Callback<Ret(Params...)> {
38 template <typename... Args>
callbackCallback39 static Ret callback(Args... args) { return func(args...); }
40 static std::function<Ret(Params...)> func;
41 };
42 template <typename Ret, typename... Params>
43 std::function<Ret(Params...)> Callback<Ret(Params...)>::func;
44
45 namespace sqlb {
escapeIdentifier(const QString & id)46 QString escapeIdentifier(const QString& id)
47 {
48 return QString::fromStdString(escapeIdentifier(id.toStdString()));
49 }
escapeString(const QString & literal)50 QString escapeString(const QString& literal)
51 {
52 return QString::fromStdString(escapeString(literal.toStdString()));
53 }
54 }
55
56 // collation callbacks
collCompare(void *,int sizeA,const void * sA,int sizeB,const void * sB)57 int collCompare(void* /*pArg*/, int sizeA, const void* sA, int sizeB, const void* sB)
58 {
59 if(sizeA == sizeB)
60 return memcmp(sA, sB, static_cast<size_t>(sizeA));
61 return sizeA - sizeB;
62 }
63
sqlite_compare_utf16(void *,int size1,const void * str1,int size2,const void * str2)64 static int sqlite_compare_utf16( void* /*arg*/,int size1, const void *str1, int size2, const void* str2)
65 {
66 const QString string1 = QString::fromRawData(reinterpret_cast<const QChar*>(str1), static_cast<int>(static_cast<size_t>(size1) / sizeof(QChar)));
67 const QString string2 = QString::fromRawData(reinterpret_cast<const QChar*>(str2), static_cast<int>(static_cast<size_t>(size2) / sizeof(QChar)));
68
69 return QString::compare(string1, string2, Qt::CaseSensitive);
70 }
71
sqlite_compare_utf16ci(void *,int size1,const void * str1,int size2,const void * str2)72 static int sqlite_compare_utf16ci( void* /*arg*/,int size1, const void *str1, int size2, const void* str2)
73 {
74 const QString string1 = QString::fromRawData(reinterpret_cast<const QChar*>(str1), static_cast<int>(static_cast<size_t>(size1) / sizeof(QChar)));
75 const QString string2 = QString::fromRawData(reinterpret_cast<const QChar*>(str2), static_cast<int>(static_cast<size_t>(size2) / sizeof(QChar)));
76
77 return QString::compare(string1, string2, Qt::CaseInsensitive);
78 }
79
sqlite_make_single_value(sqlite3_context * ctx,int num_arguments,sqlite3_value * arguments[])80 static void sqlite_make_single_value(sqlite3_context* ctx, int num_arguments, sqlite3_value* arguments[])
81 {
82 json array;
83 for(int i=0;i<num_arguments;i++)
84 array.push_back(reinterpret_cast<const char*>(sqlite3_value_text(arguments[i])));
85
86 std::string output = array.dump();
87 char* output_str = new char[output.size()+1];
88 std::strcpy(output_str, output.c_str());
89
90 sqlite3_result_text(ctx, output_str, static_cast<int>(output.length()), [](void* ptr) {
91 char* cptr = static_cast<char*>(ptr);
92 delete cptr;
93 });
94 }
95
DBBrowserDB()96 DBBrowserDB::DBBrowserDB() :
97 _db(nullptr),
98 db_used(false),
99 isEncrypted(false),
100 isReadOnly(false),
101 dontCheckForStructureUpdates(false)
102 {
103 // Register error log callback. This needs to be done before SQLite is first used
104 Callback<void(void*, int, const char*)>::func = std::bind(&DBBrowserDB::errorLogCallback, this, std::placeholders::_1, std::placeholders::_2, std::placeholders::_3);
105 void (*log_callback)(void*, int, const char*) = static_cast<decltype(log_callback)>(Callback<void(void*, int, const char*)>::callback);
106 sqlite3_config(SQLITE_CONFIG_LOG, log_callback, nullptr);
107 }
108
collationNeeded(void *,sqlite3 *,int eTextRep,const char * sCollationName)109 void DBBrowserDB::collationNeeded(void* /*pData*/, sqlite3* /*db*/, int eTextRep, const char* sCollationName)
110 {
111 QString name(sCollationName);
112
113 // Don't request built-in collations. SQLite requests these collations even though they are built into
114 // the library. Since we have no need for overriding them, we just silently ignore these requests.
115 if(name.compare("BINARY", Qt::CaseInsensitive) &&
116 name.compare("NOCASE", Qt::CaseInsensitive) &&
117 name.compare("RTRIM", Qt::CaseInsensitive))
118 {
119 emit requestCollation(name, eTextRep);
120 }
121 }
122
errorLogCallback(void *,int error_code,const char * message)123 void DBBrowserDB::errorLogCallback(void* /*user_data*/, int error_code, const char* message)
124 {
125 QString msg = QString("(%1) %2").arg(error_code).arg(message);
126
127 logSQL(msg, kLogMsg_ErrorLog);
128 }
129
regexp(sqlite3_context * ctx,int,sqlite3_value * argv[])130 static void regexp(sqlite3_context* ctx, int /*argc*/, sqlite3_value* argv[])
131 {
132 // This is a cache for the last 50 regular expressions. Compiling them takes some time, so we want to cache the compiled
133 // regular expressions for performance purposes.
134 static std::array<std::pair<QString, QRegularExpression>, 50> regex_cache;
135
136 // Check if pattern is in cache
137 QString pattern{reinterpret_cast<const char*>(sqlite3_value_text(argv[0]))};
138 QRegularExpression regex;
139 const auto it = std::find_if(regex_cache.begin(), regex_cache.end(), [pattern](const std::pair<QString, QRegularExpression>& val) {
140 return val.first == pattern;
141 });
142 if(it == regex_cache.end())
143 {
144 // Pattern is not in cache. Create a new regular expressions object, compile it, and insert it into the cache
145 regex.setPattern(pattern);
146 regex.setPatternOptions(QRegularExpression::UseUnicodePropertiesOption);
147 if(!regex.isValid())
148 return sqlite3_result_error(ctx, "invalid operand", -1);
149 regex.optimize();
150
151 static size_t regex_cache_size;
152 regex_cache_size = (regex_cache_size + 1) % regex_cache.size();
153 regex_cache[regex_cache_size] = {pattern, regex};
154 } else {
155 // Pattern is in the cache. Just retrieve it
156 regex = it->second;
157 }
158
159 // Get arguments and check their values
160 QString arg2{reinterpret_cast<const char*>(sqlite3_value_text(argv[1]))};
161
162 // Perform the actual matching and return the result.
163 // SQLite expects a 0 for not found and a 1 for found.
164 sqlite3_result_int(ctx, regex.match(arg2).hasMatch());
165 }
166
isOpen() const167 bool DBBrowserDB::isOpen ( ) const
168 {
169 return _db != nullptr;
170 }
171
getDirty() const172 bool DBBrowserDB::getDirty() const
173 {
174 return !savepointList.empty();
175 }
176
open(const QString & db,bool readOnly)177 bool DBBrowserDB::open(const QString& db, bool readOnly)
178 {
179 if (isOpen()) close();
180
181 isEncrypted = false;
182 dontCheckForStructureUpdates = false;
183
184 // Get encryption settings for database file
185 CipherSettings* cipherSettings = nullptr;
186 if(tryEncryptionSettings(db, &isEncrypted, cipherSettings) == false)
187 return false;
188
189 // Open database file
190 if(sqlite3_open_v2(db.toUtf8(), &_db, readOnly ? SQLITE_OPEN_READONLY : SQLITE_OPEN_READWRITE, nullptr) != SQLITE_OK)
191 {
192 lastErrorMessage = QString::fromUtf8(sqlite3_errmsg(_db));
193 return false;
194 }
195
196 // Set encryption details if database is encrypted
197 #ifdef ENABLE_SQLCIPHER
198 if(isEncrypted && cipherSettings)
199 {
200 executeSQL("PRAGMA key = " + cipherSettings->getPassword(), false, false);
201 executeSQL("PRAGMA cipher_page_size = " + std::to_string(cipherSettings->getPageSize()), false, false);
202 executeSQL("PRAGMA kdf_iter = " + std::to_string(cipherSettings->getKdfIterations()), false, false);
203 executeSQL("PRAGMA cipher_hmac_algorithm = " + cipherSettings->getHmacAlgorithm(), false, false);
204 executeSQL("PRAGMA cipher_kdf_algorithm = " + cipherSettings->getKdfAlgorithm(), false, false);
205 executeSQL("PRAGMA cipher_plaintext_header_size = " + std::to_string(cipherSettings->getPlaintextHeaderSize()), false, false);
206 }
207 #endif
208 delete cipherSettings;
209
210 if (_db)
211 {
212 // add UTF16 collation (comparison is performed by QString functions)
213 sqlite3_create_collation(_db, "UTF16", SQLITE_UTF16, nullptr, sqlite_compare_utf16);
214 // add UTF16CI (case insensitive) collation (comparison is performed by QString functions)
215 sqlite3_create_collation(_db, "UTF16CI", SQLITE_UTF16, nullptr, sqlite_compare_utf16ci);
216
217 // register collation callback
218 Callback<void(void*, sqlite3*, int, const char*)>::func = std::bind(&DBBrowserDB::collationNeeded, this, std::placeholders::_1, std::placeholders::_2, std::placeholders::_3, std::placeholders::_4);
219 void (*c_callback)(void*, sqlite3*, int, const char*) = static_cast<decltype(c_callback)>(Callback<void(void*, sqlite3*, int, const char*)>::callback);
220 sqlite3_collation_needed(_db, nullptr, c_callback);
221
222 // Set foreign key settings as requested in the preferences
223 bool foreignkeys = Settings::getValue("db", "foreignkeys").toBool();
224 setPragma("foreign_keys", foreignkeys ? "1" : "0");
225
226 // Register REGEXP function
227 if(Settings::getValue("extensions", "disableregex").toBool() == false)
228 sqlite3_create_function(_db, "REGEXP", 2, SQLITE_UTF8, nullptr, regexp, nullptr, nullptr);
229
230 // Register our internal helper function for putting multiple values into a single column
231 sqlite3_create_function_v2(
232 _db,
233 "sqlb_make_single_value",
234 -1,
235 SQLITE_UTF8 | SQLITE_DETERMINISTIC,
236 nullptr,
237 sqlite_make_single_value,
238 nullptr,
239 nullptr,
240 nullptr
241 );
242
243 // Check if file is read only. In-memory databases are never read only
244 if(db == ":memory:")
245 {
246 isReadOnly = false;
247 } else {
248 QFileInfo fi(db);
249 QFileInfo fid(fi.absoluteDir().absolutePath());
250 isReadOnly = readOnly || !fi.isWritable() || !fid.isWritable();
251 }
252
253 // Load extensions
254 loadExtensionsFromSettings();
255
256 // Execute default SQL
257 if(!isReadOnly)
258 {
259 QByteArray default_sql = Settings::getValue("db", "defaultsqltext").toByteArray();
260 if(!default_sql.isEmpty())
261 executeMultiSQL(default_sql, false, true);
262 }
263
264 curDBFilename = db;
265
266 updateSchema();
267
268 return true;
269 } else {
270 return false;
271 }
272 }
273
attach(const QString & filePath,QString attach_as)274 bool DBBrowserDB::attach(const QString& filePath, QString attach_as)
275 {
276 if(!_db)
277 return false;
278
279 waitForDbRelease();
280
281 // Check if this file has already been attached and abort if this is the case
282 QFileInfo fi(filePath);
283 bool ok = executeSQL("PRAGMA database_list", false, true, [fi](int, std::vector<QByteArray> values, std::vector<QByteArray>) -> bool {
284 QFileInfo path(values.at(2));
285 if(fi == path)
286 {
287 QString schema = values.at(1);
288 QMessageBox::information(nullptr, qApp->applicationName(), tr("This database has already been attached. Its schema name is '%1'.").arg(schema));
289 return true;
290 }
291
292 return false;
293 });
294
295 if(ok == false)
296 return false;
297
298 // Ask for name to be given to the attached database if none was provided
299 if(attach_as.isEmpty())
300 attach_as = QInputDialog::getText(nullptr,
301 qApp->applicationName(),
302 tr("Please specify the database name under which you want to access the attached database"),
303 QLineEdit::Normal,
304 QFileInfo(filePath).baseName()
305 ).trimmed();
306 if(attach_as.isNull())
307 return false;
308
309 #ifdef ENABLE_SQLCIPHER
310 // Try encryption settings
311 CipherSettings* cipherSettings = nullptr;
312 bool is_encrypted;
313 if(tryEncryptionSettings(filePath, &is_encrypted, cipherSettings) == false)
314 return false;
315
316 // Attach database
317 std::string key;
318 if(cipherSettings && is_encrypted)
319 key = "KEY " + cipherSettings->getPassword();
320 else
321 key = "KEY ''";
322
323 // Only apply cipher settings if the database is encrypted
324 if(cipherSettings && is_encrypted)
325 {
326 if(!executeSQL("PRAGMA cipher_default_page_size = " + std::to_string(cipherSettings->getPageSize()), false))
327 {
328 QMessageBox::warning(nullptr, qApp->applicationName(), lastErrorMessage);
329 return false;
330 }
331 if(!executeSQL("PRAGMA cipher_default_kdf_iter = " + std::to_string(cipherSettings->getKdfIterations()), false))
332 {
333 QMessageBox::warning(nullptr, qApp->applicationName(), lastErrorMessage);
334 return false;
335 }
336 if(!executeSQL("PRAGMA cipher_hmac_algorithm = " + cipherSettings->getHmacAlgorithm(), false))
337 {
338 QMessageBox::warning(nullptr, qApp->applicationName(), lastErrorMessage);
339 return false;
340 }
341 if(!executeSQL("PRAGMA cipher_kdf_algorithm = " + cipherSettings->getKdfAlgorithm(), false))
342 {
343 QMessageBox::warning(nullptr, qApp->applicationName(), lastErrorMessage);
344 return false;
345 }
346 if(!executeSQL("PRAGMA cipher_plaintext_header_size = " + std::to_string(cipherSettings->getPlaintextHeaderSize()), false))
347 {
348 QMessageBox::warning(nullptr, qApp->applicationName(), lastErrorMessage);
349 return false;
350 }
351 }
352
353 if(!executeSQL("ATTACH " + sqlb::escapeString(filePath.toStdString()) + " AS " + sqlb::escapeIdentifier(attach_as.toStdString()) + " " + key, false))
354 {
355 QMessageBox::warning(nullptr, qApp->applicationName(), lastErrorMessage);
356 return false;
357 }
358
359 // Clean up cipher settings
360 delete cipherSettings;
361 #else
362 // Attach database
363 if(!executeSQL("ATTACH " + sqlb::escapeString(filePath.toStdString()) + " AS " + sqlb::escapeIdentifier(attach_as.toStdString()), false))
364 {
365 QMessageBox::warning(nullptr, qApp->applicationName(), lastErrorMessage);
366 return false;
367 }
368 #endif
369
370 // Update schema to load database schema of the newly attached database
371 updateSchema();
372
373 return true;
374 }
375
tryEncryptionSettings(const QString & filePath,bool * encrypted,CipherSettings * & cipherSettings) const376 bool DBBrowserDB::tryEncryptionSettings(const QString& filePath, bool* encrypted, CipherSettings*& cipherSettings) const
377 {
378 lastErrorMessage = tr("Invalid file format");
379
380 // Open database file
381 sqlite3* dbHandle;
382 if(sqlite3_open_v2(filePath.toUtf8(), &dbHandle, SQLITE_OPEN_READONLY, nullptr) != SQLITE_OK)
383 return false;
384
385 // Try reading from database
386
387 #ifdef ENABLE_SQLCIPHER
388 bool isDotenvChecked = false;
389
390 // Determine default encryption settings depending on the SQLCipher version we use
391 QString sqlite_version, sqlcipher_version;
392 getSqliteVersion(sqlite_version, sqlcipher_version);
393 int enc_default_page_size, enc_default_kdf_iter;
394 int enc_default_plaintext_header_size = 0;
395 std::string enc_default_hmac_algorithm, enc_default_kdf_algorithm;
396 if(sqlcipher_version.startsWith('4'))
397 {
398 enc_default_page_size = 4096;
399 enc_default_kdf_iter = 256000;
400 enc_default_hmac_algorithm = "SHA512";
401 enc_default_kdf_algorithm = "SHA512";
402 } else {
403 enc_default_page_size = 1024;
404 enc_default_kdf_iter = 64000;
405 enc_default_hmac_algorithm = "SHA1";
406 enc_default_kdf_algorithm = "SHA1";
407 }
408 #endif
409
410 *encrypted = false;
411 cipherSettings = nullptr;
412 while(true)
413 {
414 const std::string statement = "SELECT COUNT(*) FROM sqlite_master;";
415 sqlite3_stmt* vm;
416 const char* tail;
417 int err = sqlite3_prepare_v2(dbHandle, statement.c_str(), static_cast<int>(statement.size()), &vm, &tail);
418 if(err == SQLITE_BUSY || err == SQLITE_PERM || err == SQLITE_NOMEM || err == SQLITE_IOERR || err == SQLITE_CORRUPT || err == SQLITE_CANTOPEN)
419 {
420 lastErrorMessage = QString::fromUtf8(sqlite3_errmsg(dbHandle));
421 sqlite3_close(dbHandle);
422 return false;
423 }
424
425 if(sqlite3_step(vm) != SQLITE_ROW)
426 {
427 sqlite3_finalize(vm);
428 #ifdef ENABLE_SQLCIPHER
429 bool foundDotenvPassword = false;
430
431 // Being in a while loop, we don't want to check the same file multiple times
432 if (!isDotenvChecked) {
433 QFile databaseFile(filePath);
434 QFileInfo databaseFileInfo(databaseFile);
435
436 QString databaseDirectoryPath = databaseFileInfo.dir().path();
437 QString databaseFileName(databaseFileInfo.fileName());
438
439 QString dotenvFilePath = databaseDirectoryPath + "/.env";
440 static const QSettings::Format dotenvFormat = QSettings::registerFormat("env", &DotenvFormat::readEnvFile, nullptr);
441 QSettings dotenv(dotenvFilePath, dotenvFormat);
442
443 QVariant passwordValue = dotenv.value(databaseFileName);
444
445 foundDotenvPassword = !passwordValue.isNull();
446
447 isDotenvChecked = true;
448
449 if (foundDotenvPassword)
450 {
451 std::string password = passwordValue.toString().toStdString();
452
453 QVariant keyFormatValue = dotenv.value(databaseFileName + "_keyFormat", QVariant(CipherSettings::KeyFormats::Passphrase));
454 CipherSettings::KeyFormats keyFormat = CipherSettings::getKeyFormat(keyFormatValue.toInt());
455
456 int pageSize = dotenv.value(databaseFileName + "_pageSize", enc_default_page_size).toInt();
457 int kdfIterations = dotenv.value(databaseFileName + "_kdfIter", enc_default_kdf_iter).toInt();
458 int plaintextHeaderSize = dotenv.value(databaseFileName + "_plaintextHeaderSize", enc_default_plaintext_header_size).toInt();
459 std::string hmacAlgorithm = dotenv.value(databaseFileName + "_hmacAlgorithm", QString::fromStdString(enc_default_hmac_algorithm)).toString().toStdString();
460 std::string kdfAlgorithm = dotenv.value(databaseFileName + "_kdfAlgorithm", QString::fromStdString(enc_default_kdf_algorithm)).toString().toStdString();
461
462 delete cipherSettings;
463 cipherSettings = new CipherSettings();
464
465 cipherSettings->setKeyFormat(keyFormat);
466 cipherSettings->setPassword(password);
467 cipherSettings->setPageSize(pageSize);
468 cipherSettings->setKdfIterations(kdfIterations);
469 cipherSettings->setHmacAlgorithm("HMAC_" + hmacAlgorithm);
470 cipherSettings->setKdfAlgorithm("PBKDF2_HMAC_" + kdfAlgorithm);
471 cipherSettings->setPlaintextHeaderSize(plaintextHeaderSize);
472 }
473 }
474
475 if(foundDotenvPassword)
476 {
477 // Skip the CipherDialog prompt for now to test if the dotenv settings are correct
478 } else {
479 CipherDialog *cipherDialog = new CipherDialog(nullptr, false);
480 if(cipherDialog->exec())
481 {
482 delete cipherSettings;
483 cipherSettings = new CipherSettings(cipherDialog->getCipherSettings());
484 } else {
485 sqlite3_close(dbHandle);
486 *encrypted = false;
487 delete cipherSettings;
488 cipherSettings = nullptr;
489 return false;
490 }
491 }
492
493 // Close and reopen database first to be in a clean state after the failed read attempt from above
494 sqlite3_close(dbHandle);
495 if(sqlite3_open_v2(filePath.toUtf8(), &dbHandle, SQLITE_OPEN_READONLY, nullptr) != SQLITE_OK)
496 {
497 delete cipherSettings;
498 cipherSettings = nullptr;
499 return false;
500 }
501
502 // Set the key
503 sqlite3_exec(dbHandle, ("PRAGMA key = " + cipherSettings->getPassword()).c_str(), nullptr, nullptr, nullptr);
504
505 // Set the settings if they differ from the default values
506 if(cipherSettings->getPageSize() != enc_default_page_size)
507 sqlite3_exec(dbHandle, ("PRAGMA cipher_page_size = " + std::to_string(cipherSettings->getPageSize())).c_str(), nullptr, nullptr, nullptr);
508 if(cipherSettings->getKdfIterations() != enc_default_kdf_iter)
509 sqlite3_exec(dbHandle, ("PRAGMA kdf_iter = " + std::to_string(cipherSettings->getKdfIterations())).c_str(), nullptr, nullptr, nullptr);
510 if(cipherSettings->getHmacAlgorithm() != enc_default_hmac_algorithm)
511 sqlite3_exec(dbHandle, ("PRAGMA cipher_hmac_algorithm = " + cipherSettings->getHmacAlgorithm()).c_str(), nullptr, nullptr, nullptr);
512 if(cipherSettings->getKdfAlgorithm() != enc_default_kdf_algorithm)
513 sqlite3_exec(dbHandle, ("PRAGMA cipher_kdf_algorithm = " + cipherSettings->getKdfAlgorithm()).c_str(), nullptr, nullptr, nullptr);
514 if(cipherSettings->getPlaintextHeaderSize() != enc_default_plaintext_header_size)
515 sqlite3_exec(dbHandle, ("PRAGMA cipher_plaintext_header_size = " + std::to_string(cipherSettings->getPlaintextHeaderSize())).c_str(), nullptr, nullptr, nullptr);
516
517 *encrypted = true;
518 #else
519 lastErrorMessage = QString::fromUtf8(sqlite3_errmsg(dbHandle));
520 sqlite3_close(dbHandle);
521 return false;
522 #endif
523 } else {
524 sqlite3_finalize(vm);
525 sqlite3_close(dbHandle);
526 return true;
527 }
528 }
529 }
530
getSqliteVersion(QString & sqlite,QString & sqlcipher)531 void DBBrowserDB::getSqliteVersion(QString& sqlite, QString& sqlcipher)
532 {
533 sqlite = QString(SQLITE_VERSION);
534
535 // The SQLCipher version must be queried via a pragma and for a pragma we need a database connection.
536 // Because we want to be able to query the SQLCipher version without opening a database file first, we
537 // open a separate connection to an in-memory database here.
538 sqlcipher = QString();
539 #ifdef ENABLE_SQLCIPHER
540 sqlite3* dummy;
541 if(sqlite3_open(":memory:", &dummy) == SQLITE_OK)
542 {
543 sqlite3_stmt* stmt;
544 if(sqlite3_prepare_v2(dummy, "PRAGMA cipher_version", -1, &stmt, nullptr) == SQLITE_OK)
545 {
546 if(sqlite3_step(stmt) == SQLITE_ROW)
547 sqlcipher = QByteArray(static_cast<const char*>(sqlite3_column_blob(stmt, 0)), sqlite3_column_bytes(stmt, 0));
548
549 sqlite3_finalize(stmt);
550 }
551
552 sqlite3_close(dummy);
553 }
554 #endif
555 }
556
setSavepoint(const std::string & pointname)557 bool DBBrowserDB::setSavepoint(const std::string& pointname)
558 {
559 if(!isOpen())
560 return false;
561 if(isReadOnly) {
562 qWarning() << "setSavepoint: not done. DB is read-only";
563 return false;
564 }
565 if(contains(savepointList, pointname))
566 return true;
567
568 executeSQL("SAVEPOINT " + sqlb::escapeIdentifier(pointname) + ";", false, true);
569 savepointList.push_back(pointname);
570 emit dbChanged(getDirty());
571
572 return true;
573 }
574
releaseSavepoint(const std::string & pointname)575 bool DBBrowserDB::releaseSavepoint(const std::string& pointname)
576 {
577 if(!isOpen())
578 return false;
579 if(contains(savepointList, pointname) == false)
580 // If there is no such savepoint in the list,
581 // we have already released it, so in this case
582 // the operation should be successfull
583 return true;
584
585 if(!executeSQL("RELEASE " + sqlb::escapeIdentifier(pointname) + ";", false, true))
586 return false;
587 // SQLite releases all savepoints that were created between
588 // creation of given savepoint and releasing of it,
589 // so we should too
590 auto it = std::find(savepointList.rbegin(), savepointList.rend(), pointname).base() - 1;
591 savepointList.erase(it, savepointList.end());
592 emit dbChanged(getDirty());
593
594 return true;
595 }
596
revertToSavepoint(const std::string & pointname)597 bool DBBrowserDB::revertToSavepoint(const std::string& pointname)
598 {
599 if(!isOpen() || contains(savepointList, pointname) == false)
600 return false;
601
602 executeSQL("ROLLBACK TO SAVEPOINT " + sqlb::escapeIdentifier(pointname) + ";", false, true);
603 executeSQL("RELEASE " + sqlb::escapeIdentifier(pointname) + ";", false, true);
604 // SQLite releases all savepoints that were created between
605 // creation of given savepoint and releasing of it,
606 // so we should too
607 auto it = std::find(savepointList.rbegin(), savepointList.rend(), pointname).base() - 1;
608 savepointList.erase(it, savepointList.end());
609 emit dbChanged(getDirty());
610
611 return true;
612 }
613
releaseAllSavepoints()614 bool DBBrowserDB::releaseAllSavepoints()
615 {
616 if(!_db)
617 return false;
618
619 waitForDbRelease();
620
621 while(!savepointList.empty())
622 {
623 if(!releaseSavepoint(savepointList.front()))
624 return false;
625 }
626
627 // When still in a transaction, commit that too
628 if(sqlite3_get_autocommit(_db) == 0)
629 executeSQL("COMMIT;", false, true);
630
631 return true;
632 }
633
revertAll()634 bool DBBrowserDB::revertAll()
635 {
636 while(!savepointList.empty())
637 {
638 if(!revertToSavepoint(savepointList.front()))
639 return false;
640 }
641 return true;
642 }
643
create(const QString & db)644 bool DBBrowserDB::create ( const QString & db)
645 {
646 if (isOpen())
647 close();
648
649 // read encoding from settings and open with sqlite3_open for utf8 and sqlite3_open16 for utf16
650 QString sEncoding = Settings::getValue("db", "defaultencoding").toString();
651
652 int openresult = SQLITE_OK;
653
654 if(sEncoding == "UTF-8" || sEncoding == "UTF8" || sEncoding == "Latin1")
655 openresult = sqlite3_open(db.toUtf8(), &_db);
656 else
657 openresult = sqlite3_open16(db.utf16(), &_db);
658
659 if( openresult != SQLITE_OK ){
660 lastErrorMessage = QString::fromUtf8(sqlite3_errmsg(_db));
661 sqlite3_close(_db);
662 _db = nullptr;
663 return false;
664 }
665
666 if (_db)
667 {
668 // force sqlite3 do write proper file header
669 // if we don't create and drop the table we might end up
670 // with a 0 byte file, if the user cancels the create table dialog
671 {
672 NoStructureUpdateChecks nup(*this);
673 executeSQL("CREATE TABLE notempty (id integer primary key);", false, false);
674 executeSQL("DROP TABLE notempty;", false, false);
675 }
676
677 // Close database and open it through the code for opening existing database files. This is slightly less efficient but saves us some duplicate
678 // code.
679 sqlite3_close(_db);
680 return open(db);
681 } else {
682 return false;
683 }
684 }
685
close()686 bool DBBrowserDB::close()
687 {
688 waitForDbRelease();
689
690 if(_db)
691 {
692 if (getDirty())
693 {
694 // In-memory databases can't be saved to disk. So the need another text than regular databases.
695 // Note that the QMessageBox::Yes option in the :memory: case and the QMessageBox::No option in the regular case are
696 // doing the same job: proceeding but not saving anything.
697 QMessageBox::StandardButton reply;
698 if(curDBFilename == ":memory:")
699 {
700 reply = QMessageBox::question(nullptr,
701 QApplication::applicationName(),
702 tr("Do you really want to close this temporary database? All data will be lost."),
703 QMessageBox::Yes | QMessageBox::Cancel);
704 } else {
705 reply = QMessageBox::question(nullptr,
706 QApplication::applicationName(),
707 tr("Do you want to save the changes made to the database file %1?").arg(curDBFilename),
708 QMessageBox::Save | QMessageBox::Discard | QMessageBox::Cancel);
709 }
710
711 // If the user clicked the cancel button stop here and return false
712 if(reply == QMessageBox::Cancel)
713 return false;
714
715 // If he didn't it was either yes or no
716 if(reply == QMessageBox::Save)
717 releaseAllSavepoints();
718 else
719 revertAll(); //not really necessary, I think... but will not hurt.
720 }
721 if(sqlite3_close(_db) != SQLITE_OK)
722 qWarning() << tr("Database didn't close correctly, probably still busy");
723 _db = nullptr;
724 }
725
726 schemata.clear();
727 savepointList.clear();
728 emit dbChanged(getDirty());
729 emit structureUpdated();
730
731 // Return true to tell the calling function that the closing wasn't cancelled by the user
732 return true;
733 }
734
get(const QString & user,bool force_wait)735 DBBrowserDB::db_pointer_type DBBrowserDB::get(const QString& user, bool force_wait)
736 {
737 if(!_db)
738 return nullptr;
739
740 waitForDbRelease(force_wait ? Wait : Ask);
741
742 db_user = user;
743 db_used = true;
744 emit databaseInUseChanged(true, user);
745
746 return db_pointer_type(_db, DatabaseReleaser(this));
747 }
748
waitForDbRelease(ChoiceOnUse choice) const749 void DBBrowserDB::waitForDbRelease(ChoiceOnUse choice) const
750 {
751 if(!_db)
752 return;
753
754 // We can't show a message box from another thread than the main thread. So instead of crashing we
755 // just decide that we don't interrupt any running query in this case.
756 if(choice == Ask && QThread::currentThread() != QApplication::instance()->thread())
757 choice = Wait;
758
759 std::unique_lock<std::mutex> lk(m);
760 while(db_used) {
761 // notify user, give him the opportunity to cancel that
762 auto str = db_user;
763 lk.unlock();
764
765 bool cancel = choice == CancelOther;
766 if(choice == Ask) {
767 QMessageBox msgBox;
768 msgBox.setText(tr("The database is currently busy: ") + str);
769 msgBox.setInformativeText(tr("Do you want to abort that other operation?"));
770 msgBox.setStandardButtons(QMessageBox::Yes | QMessageBox::No);
771 msgBox.setDefaultButton(QMessageBox::No);
772 int ret = msgBox.exec();
773
774 cancel = ret == QMessageBox::Yes;
775 }
776 if(cancel)
777 sqlite3_interrupt(_db);
778
779 lk.lock();
780 cv.wait(lk, [this](){ return !db_used; });
781 }
782 }
783
dump(const QString & filePath,const std::vector<std::string> & tablesToDump,bool insertColNames,bool insertNewSyntx,bool exportSchema,bool exportData,bool keepOldSchema) const784 bool DBBrowserDB::dump(const QString& filePath,
785 const std::vector<std::string>& tablesToDump,
786 bool insertColNames,
787 bool insertNewSyntx,
788 bool exportSchema,
789 bool exportData,
790 bool keepOldSchema) const
791 {
792 waitForDbRelease();
793
794 // Open file
795 QFile file(filePath);
796 if(file.open(QIODevice::WriteOnly|QIODevice::Text))
797 {
798 QApplication::setOverrideCursor(Qt::WaitCursor);
799
800 // Count the total number of all records in all tables for the progress dialog
801 size_t numRecordsTotal = 0;
802 objectMap objMap = schemata.at("main"); // We only always export the main database, not the attached databases
803 std::vector<sqlb::ObjectPtr> tables;
804 auto all_tables = objMap.equal_range("table");
805 for(auto it=all_tables.first;it!=all_tables.second;++it)
806 {
807 // Never export the sqlite_stat1 and the sqlite_sequence tables if they exist. Also only export any tables which are selected for export.
808 if(it->second->name() != "sqlite_stat1" && it->second->name() != "sqlite_sequence" && contains(tablesToDump, it->second->name()))
809 {
810 // Get the number of records in this table and remember to export it
811 tables.push_back(it->second);
812 numRecordsTotal += querySingleValueFromDb("SELECT COUNT(*) FROM " + sqlb::ObjectIdentifier("main", it->second->name()).toString()).toUInt();
813 }
814 }
815
816 QProgressDialog progress(tr("Exporting database to SQL file..."),
817 tr("Cancel"), 0, static_cast<int>(numRecordsTotal));
818 progress.setWindowModality(Qt::ApplicationModal);
819 progress.show();
820 qApp->processEvents();
821
822 // Open text stream to the file
823 QTextStream stream(&file);
824
825 // Put the SQL commands in a transaction block
826 stream << "BEGIN TRANSACTION;\n";
827
828 // First export the schema of all selected tables. We need to export the schema of all tables before we export the first INSERT statement to
829 // make sure foreign keys are working properly.
830 if(exportSchema)
831 {
832 for(const auto& it : tables)
833 {
834 // Write the SQL string used to create this table to the output file
835 if(!keepOldSchema)
836 stream << QString("DROP TABLE IF EXISTS %1;\n").arg(QString::fromStdString(sqlb::escapeIdentifier(it->name())));
837
838 if(it->fullyParsed())
839 stream << QString::fromStdString(it->sql("main", true)) << "\n";
840 else
841 stream << QString::fromStdString(it->originalSql()) << ";\n";
842 }
843 }
844
845 // Now export the data as well
846 if(exportData)
847 {
848 for(const auto& it : tables)
849 {
850 // get columns
851 sqlb::StringVector cols = std::dynamic_pointer_cast<sqlb::Table>(it)->fieldNames();
852
853 std::string sQuery = "SELECT * FROM " + sqlb::escapeIdentifier(it->name());
854 sqlite3_stmt *stmt;
855 QString lineSep(QString(")%1\n").arg(insertNewSyntx?',':';'));
856
857 int status = sqlite3_prepare_v2(_db, sQuery.c_str(), static_cast<int>(sQuery.size()), &stmt, nullptr);
858 if(SQLITE_OK == status)
859 {
860 int columns = sqlite3_column_count(stmt);
861 size_t counter = 0;
862 size_t numRecordsCurrent = 0;
863 qApp->processEvents();
864 while(sqlite3_step(stmt) == SQLITE_ROW)
865 {
866 if (counter) stream << lineSep;
867
868 if (!insertNewSyntx || !counter)
869 {
870 stream << "INSERT INTO " << QString::fromStdString(sqlb::escapeIdentifier(it->name()));
871 if (insertColNames)
872 stream << " (" << QString::fromStdString(sqlb::joinStringVector(sqlb::escapeIdentifier(cols), ",")) << ")";
873 stream << " VALUES (";
874 }
875 else
876 {
877 stream << " (";
878 }
879
880 for (int i = 0; i < columns; ++i)
881 {
882 int fieldsize = sqlite3_column_bytes(stmt, i);
883 int fieldtype = sqlite3_column_type(stmt, i);
884 QByteArray bcontent(
885 reinterpret_cast<const char*>(sqlite3_column_blob(stmt, i)),
886 fieldsize);
887
888 if(bcontent.left(2048).contains('\0')) // binary check
889 {
890 stream << QString("X'%1'").arg(QString(bcontent.toHex()));
891 }
892 else
893 {
894 switch(fieldtype)
895 {
896 case SQLITE_TEXT:
897 case SQLITE_BLOB:
898 stream << sqlb::escapeString(bcontent);
899 break;
900 case SQLITE_NULL:
901 stream << "NULL";
902 break;
903 case SQLITE_FLOAT:
904 if(bcontent.indexOf("Inf") != -1)
905 stream << "'" << bcontent << "'";
906 else
907 stream << bcontent;
908 break;
909 default:
910 stream << bcontent;
911 }
912 }
913 if(i != columns - 1)
914 stream << ',';
915 }
916
917 progress.setValue(static_cast<int>(++numRecordsCurrent));
918 if(counter % 5000 == 0)
919 qApp->processEvents();
920 counter++;
921
922 if(progress.wasCanceled())
923 {
924 sqlite3_finalize(stmt);
925 file.close();
926 file.remove();
927 QApplication::restoreOverrideCursor();
928 return false;
929 }
930 }
931 if (counter > 0) stream << ");\n";
932 }
933 sqlite3_finalize(stmt);
934 }
935 }
936
937 // Finally export all objects other than tables
938 if(exportSchema)
939 {
940 for(const auto& obj : objMap)
941 {
942 const auto& it = obj.second;
943
944 // Make sure it's not a table again
945 if(it->type() == sqlb::Object::Types::Table)
946 continue;
947
948 // If this object is based on a table (e.g. is an index for that table) it depends on the existence of this table.
949 // So if we didn't export the base table this depends on, don't export this object either.
950 if(!it->baseTable().empty() && !contains(tablesToDump, it->baseTable()))
951 continue;
952
953 // Write the SQL string used to create this object to the output file
954 if(!it->originalSql().empty())
955 {
956 if(!keepOldSchema)
957 stream << QString("DROP %1 IF EXISTS %2;\n").arg(
958 QString::fromStdString(sqlb::Object::typeToString(it->type())).toUpper(),
959 QString::fromStdString(sqlb::escapeIdentifier(it->name())));
960
961 if(it->fullyParsed())
962 stream << QString::fromStdString(it->sql("main", true)) << "\n";
963 else
964 stream << QString::fromStdString(it->originalSql()) << ";\n";
965 }
966 }
967 }
968
969 // Done
970 stream << "COMMIT;\n";
971 file.close();
972
973 QApplication::restoreOverrideCursor();
974 qApp->processEvents();
975 return true;
976 }
977 return false;
978 }
979
980 // Callback for sqlite3_exec. It receives the user callback in the first parameter. Converts parameters
981 // to C++ classes and calls user callback.
callbackWrapper(void * callback,int numberColumns,char ** values,char ** columnNames)982 int DBBrowserDB::callbackWrapper (void* callback, int numberColumns, char** values, char** columnNames)
983 {
984 std::vector<QByteArray> valuesList;
985 std::vector<QByteArray> namesList;
986
987 for (int i=0; i<numberColumns; i++) {
988 valuesList.emplace_back(values[i]);
989 namesList.emplace_back(columnNames[i]);
990 }
991
992 execCallback userCallback = *(static_cast<execCallback*>(callback));
993 return userCallback(numberColumns, valuesList, namesList);
994 }
995
executeSQL(const std::string & statement,bool dirtyDB,bool logsql,execCallback callback)996 bool DBBrowserDB::executeSQL(const std::string& statement, bool dirtyDB, bool logsql, execCallback callback)
997 {
998 waitForDbRelease();
999 if(!_db)
1000 {
1001 lastErrorMessage = tr("No database file opened");
1002 return false;
1003 }
1004
1005 if (dirtyDB) setSavepoint();
1006 if (logsql) logSQL(QString::fromStdString(statement), kLogMsg_App);
1007
1008 char* errmsg;
1009 if (SQLITE_OK == sqlite3_exec(_db, statement.c_str(), callback ? callbackWrapper : nullptr, &callback, &errmsg))
1010 {
1011 // Update DB structure after executing an SQL statement. But try to avoid doing unnecessary updates.
1012 if(!dontCheckForStructureUpdates && (starts_with_ci(statement, "ALTER") ||
1013 starts_with_ci(statement, "CREATE") ||
1014 starts_with_ci(statement, "DROP") ||
1015 starts_with_ci(statement, "ROLLBACK")))
1016 updateSchema();
1017
1018 return true;
1019 } else {
1020 lastErrorMessage = QString("%1 (%2)").arg(QString::fromUtf8(errmsg), QString::fromStdString(statement));
1021 qWarning() << "executeSQL: " << lastErrorMessage;
1022 sqlite3_free(errmsg);
1023
1024 return false;
1025 }
1026 }
1027
executeMultiSQL(QByteArray query,bool dirty,bool log)1028 bool DBBrowserDB::executeMultiSQL(QByteArray query, bool dirty, bool log)
1029 {
1030 waitForDbRelease();
1031 if(!_db)
1032 {
1033 lastErrorMessage = tr("No database file opened");
1034 return false;
1035 }
1036
1037 // Log the statement if needed
1038 if(log)
1039 logSQL(query, kLogMsg_App);
1040
1041 // Show progress dialog
1042 QProgressDialog progress(tr("Executing SQL..."),
1043 tr("Cancel"), 0, 100);
1044 progress.setWindowModality(Qt::ApplicationModal);
1045 progress.show();
1046
1047 // Execute the statement by looping until SQLite stops giving back a tail string
1048 sqlite3_stmt* vm;
1049 const char *tail = query.constData();
1050 const char * const tail_start = tail;
1051 const char * const tail_end = tail + query.size() + 1;
1052 size_t total_tail_length = static_cast<size_t>(tail_end - tail_start);
1053 unsigned int line = 0;
1054 bool structure_updated = false;
1055 int last_progress_value = -1;
1056 std::string savepoint_name;
1057 while(tail && *tail != 0)
1058 {
1059 line++;
1060
1061 // Update progress dialog, keep UI responsive. Make sure to not spend too much time updating the progress dialog in case there are many small statements.
1062 int progress_value = static_cast<int>(static_cast<float>(tail - tail_start) / static_cast<float>(total_tail_length) * 100.0f);
1063 if(progress_value > last_progress_value)
1064 {
1065 progress.setValue(progress_value);
1066 qApp->processEvents();
1067 if(progress.wasCanceled())
1068 {
1069 lastErrorMessage = tr("Action cancelled.");
1070 return false;
1071 }
1072 last_progress_value = progress_value;
1073 }
1074
1075 // Check next statement
1076 {
1077 // Ignore all whitespace at the start of the current tail
1078 const char* tail_ptr = tail;
1079 while(std::isspace(*tail_ptr))
1080 tail_ptr++;
1081
1082 // Convert the first couple of bytes of the tail to a C++ string for easier handling. We only need the first 8 bytes (in cae it's a ROLLBACK
1083 // statement), so no need to convert the entire tail. If the tail is less than 8 bytes long, make sure not to read past its end.
1084 size_t length = std::min(static_cast<size_t>(tail_end - tail + 1), static_cast<size_t>(8));
1085 std::string next_statement(tail_ptr, length);
1086 std::transform(next_statement.begin(), next_statement.end(), next_statement.begin(), ::toupper);
1087
1088 // Check for transaction statements and skip until the next semicolon
1089 if(next_statement.compare(0, 6, "COMMIT") == 0 ||
1090 next_statement.compare(0, 4, "END ") == 0 ||
1091 next_statement.compare(0, 6, "BEGIN ") == 0)
1092 {
1093 while(tail != tail_end)
1094 {
1095 if(*tail++ == ';')
1096 break;
1097 }
1098
1099 // Set DB to dirty and create a restore point if we haven't done that yet
1100 if(savepoint_name.empty())
1101 {
1102 savepoint_name = generateSavepointName("execmultisql");
1103 setSavepoint(savepoint_name);
1104 dirty = true;
1105 }
1106
1107 // Don't just execute next statement. Start next statement with the same checks
1108 continue;
1109 }
1110
1111 // Check whether the DB structure is changed by this statement
1112 if(!dontCheckForStructureUpdates && !structure_updated)
1113 {
1114 // Check if it's a modifying statement
1115 if(next_statement.compare(0, 5, "ALTER") == 0 ||
1116 next_statement.compare(0, 6, "CREATE") == 0 ||
1117 next_statement.compare(0, 4, "DROP") == 0 ||
1118 next_statement.compare(0, 8, "ROLLBACK") == 0)
1119 structure_updated = true;
1120 }
1121 }
1122
1123 // Execute next statement
1124 if(sqlite3_prepare_v2(_db, tail, static_cast<int>(tail_end - tail + 1), &vm, &tail) == SQLITE_OK)
1125 {
1126 switch(sqlite3_step(vm))
1127 {
1128 case SQLITE_OK:
1129 case SQLITE_ROW:
1130 case SQLITE_DONE:
1131 case SQLITE_MISUSE: // This is a workaround around problematic user scripts. If they lead to empty commands,
1132 // SQLite will return a misuse error which we hereby ignore.
1133 sqlite3_finalize(vm);
1134 break;
1135 default:
1136 // In case of *any* error abort the execution and roll back the transaction
1137
1138 // Make sure to save the error message first before any other function can mess around with it
1139 lastErrorMessage = tr("Error in statement #%1: %2.\nAborting execution%3.").arg(
1140 QString::number(line),
1141 sqlite3_errmsg(_db),
1142 dirty ? tr(" and rolling back") : "");
1143 qWarning() << lastErrorMessage;
1144
1145 // Clean up
1146 sqlite3_finalize(vm);
1147 if(dirty)
1148 revertToSavepoint(savepoint_name);
1149 return false;
1150 }
1151 } else {
1152 lastErrorMessage = tr("Error in statement #%1: %2.\nAborting execution%3.").arg(
1153 QString::number(line),
1154 sqlite3_errmsg(_db),
1155 dirty ? tr(" and rolling back") : "");
1156 qWarning() << lastErrorMessage;
1157 if(dirty)
1158 revertToSavepoint(savepoint_name);
1159 return false;
1160 }
1161 }
1162
1163 // If the DB structure was changed by some command in this SQL script, update our schema representations
1164 if(structure_updated)
1165 updateSchema();
1166
1167 // Exit
1168 return true;
1169 }
1170
querySingleValueFromDb(const std::string & sql,bool log,ChoiceOnUse choice) const1171 QByteArray DBBrowserDB::querySingleValueFromDb(const std::string& sql, bool log, ChoiceOnUse choice) const
1172 {
1173 waitForDbRelease(choice);
1174 if(!_db)
1175 return QByteArray();
1176
1177 if(log)
1178 logSQL(QString::fromStdString(sql), kLogMsg_App);
1179
1180 QByteArray retval;
1181
1182 sqlite3_stmt* stmt;
1183 if(sqlite3_prepare_v2(_db, sql.c_str(), static_cast<int>(sql.size()), &stmt, nullptr) == SQLITE_OK)
1184 {
1185 // Execute the statement. We distinguish three types of results:
1186 // SQLITE_ROW in case some data was returned from the database. This data is then used as a return value.
1187 // SQLITE_DONE in case the statement executed successfully but did not return any data. We do nothing in this case, leaving the return value empty.
1188 // Any other case is an error, so we need to prepare an error message.
1189 int result = sqlite3_step(stmt);
1190 if(result == SQLITE_ROW)
1191 {
1192 if(sqlite3_column_count(stmt) > 0 && sqlite3_column_type(stmt, 0) != SQLITE_NULL)
1193 {
1194 int bytes = sqlite3_column_bytes(stmt, 0);
1195 if(bytes)
1196 retval = QByteArray(static_cast<const char*>(sqlite3_column_blob(stmt, 0)), bytes);
1197 else
1198 retval = "";
1199 }
1200 } else if(result != SQLITE_DONE) {
1201 lastErrorMessage = tr("didn't receive any output from %1").arg(QString::fromStdString(sql));
1202 qWarning() << lastErrorMessage;
1203 }
1204
1205 sqlite3_finalize(stmt);
1206 } else {
1207 lastErrorMessage = tr("could not execute command: %1").arg(sqlite3_errmsg(_db));
1208 qWarning() << lastErrorMessage;
1209 }
1210
1211 return retval;
1212 }
1213
getRow(const sqlb::ObjectIdentifier & table,const QString & rowid,std::vector<QByteArray> & rowdata) const1214 bool DBBrowserDB::getRow(const sqlb::ObjectIdentifier& table, const QString& rowid, std::vector<QByteArray>& rowdata) const
1215 {
1216 waitForDbRelease();
1217 if(!_db)
1218 return false;
1219
1220 std::string query = "SELECT * FROM " + table.toString() + " WHERE ";
1221
1222 // For a single rowid column we can use a simple WHERE condition, for multiple rowid columns we have to use sqlb_make_single_value to decode the composed rowid values.
1223 sqlb::StringVector pks = getObjectByName<sqlb::Table>(table)->rowidColumns();
1224 if(pks.size() == 1)
1225 query += sqlb::escapeIdentifier(pks.front()) + "='" + rowid.toStdString() + "'";
1226 else
1227 query += "sqlb_make_single_value(" + sqlb::joinStringVector(sqlb::escapeIdentifier(pks), ",") + ")=" + sqlb::escapeString(rowid.toStdString());
1228
1229 sqlite3_stmt *stmt;
1230 bool ret = false;
1231 if(sqlite3_prepare_v2(_db, query.c_str(), static_cast<int>(query.size()), &stmt, nullptr) == SQLITE_OK)
1232 {
1233 // even this is a while loop, the statement should always only return 1 row
1234 while(sqlite3_step(stmt) == SQLITE_ROW)
1235 {
1236 for (int i = 0; i < sqlite3_column_count(stmt); ++i)
1237 {
1238 if(sqlite3_column_type(stmt, i) == SQLITE_NULL)
1239 {
1240 rowdata.emplace_back();
1241 } else {
1242 int bytes = sqlite3_column_bytes(stmt, i);
1243 if(bytes)
1244 rowdata.emplace_back(static_cast<const char*>(sqlite3_column_blob(stmt, i)), bytes);
1245 else
1246 rowdata.emplace_back("");
1247 }
1248 }
1249 ret = true;
1250 }
1251 }
1252 sqlite3_finalize(stmt);
1253
1254 return ret;
1255 }
1256
max(const sqlb::ObjectIdentifier & tableName,const std::string & field) const1257 unsigned long DBBrowserDB::max(const sqlb::ObjectIdentifier& tableName, const std::string& field) const
1258 {
1259 std::string query = "SELECT MAX(CAST(" + sqlb::escapeIdentifier(field) + " AS INTEGER)) FROM " + tableName.toString();
1260 return querySingleValueFromDb(query).toULong();
1261 }
1262
emptyInsertStmt(const std::string & schemaName,const sqlb::Table & t,const QString & pk_value) const1263 std::string DBBrowserDB::emptyInsertStmt(const std::string& schemaName, const sqlb::Table& t, const QString& pk_value) const
1264 {
1265 std::string stmt = "INSERT INTO " + sqlb::escapeIdentifier(schemaName) + "." + sqlb::escapeIdentifier(t.name());
1266
1267 sqlb::StringVector vals;
1268 sqlb::StringVector fields;
1269 for(const sqlb::Field& f : t.fields)
1270 {
1271 // Never insert into a generated column
1272 if(!f.generated().empty())
1273 continue;
1274
1275 sqlb::ConstraintPtr pk = t.constraint({f.name()}, sqlb::Constraint::PrimaryKeyConstraintType);
1276 if(pk)
1277 {
1278 fields.push_back(f.name());
1279
1280 if(!pk_value.isNull())
1281 {
1282 vals.push_back(f.isText()? "'" + pk_value.toStdString() + "'" : pk_value.toStdString());
1283 } else {
1284 if(f.notnull())
1285 {
1286 unsigned long maxval = this->max(sqlb::ObjectIdentifier(schemaName, t.name()), f.name());
1287 std::string newval = std::to_string(maxval + 1);
1288 vals.push_back(f.isText()? "'" + newval + "'" : newval);
1289 } else {
1290 vals.push_back("NULL");
1291 }
1292 }
1293 } else if(f.notnull() && f.defaultValue().length() == 0) {
1294 fields.push_back(f.name());
1295
1296 if(f.isInteger())
1297 vals.push_back("0");
1298 else
1299 vals.push_back("''");
1300 } else {
1301 // don't insert into fields with a default value
1302 // or we will never see it.
1303 if(f.defaultValue().length() == 0)
1304 {
1305 fields.push_back(f.name());
1306 vals.push_back("NULL");
1307 }
1308 }
1309 }
1310
1311 if(fields.empty())
1312 {
1313 stmt.append(" DEFAULT VALUES;");
1314 } else {
1315 stmt.append("(");
1316 stmt.append(sqlb::joinStringVector(sqlb::escapeIdentifier(fields), ","));
1317 stmt.append(") VALUES (");
1318 stmt.append(sqlb::joinStringVector(vals, ","));
1319 stmt.append(");");
1320 }
1321
1322 return stmt;
1323 }
1324
addRecord(const sqlb::ObjectIdentifier & tablename)1325 QString DBBrowserDB::addRecord(const sqlb::ObjectIdentifier& tablename)
1326 {
1327 waitForDbRelease();
1328 if(!_db)
1329 return QString();
1330
1331 sqlb::TablePtr table = getObjectByName<sqlb::Table>(tablename);
1332 if(!table)
1333 return QString();
1334
1335 // For tables without rowid we have to set the primary key by ourselves. We do so by querying for the largest value in the PK column
1336 // and adding one to it.
1337 std::string sInsertstmt;
1338 QString pk_value;
1339 if(table->withoutRowidTable())
1340 {
1341 // For multiple rowid columns we just use the value of the last one and increase that one by one. If this doesn't yield a valid combination
1342 // the insert record dialog should pop up automatically.
1343 pk_value = QString::number(max(tablename, table->rowidColumns().back()) + 1);
1344 sInsertstmt = emptyInsertStmt(tablename.schema(), *table, pk_value);
1345 } else {
1346 sInsertstmt = emptyInsertStmt(tablename.schema(), *table);
1347 }
1348
1349 if(!executeSQL(sInsertstmt))
1350 {
1351 qWarning() << "addRecord: " << lastErrorMessage;
1352 return QString();
1353 } else {
1354 if(table->withoutRowidTable())
1355 return pk_value;
1356 else
1357 return QString::number(sqlite3_last_insert_rowid(_db));
1358 }
1359 }
1360
deleteRecords(const sqlb::ObjectIdentifier & table,const std::vector<QString> & rowids,const sqlb::StringVector & pseudo_pk)1361 bool DBBrowserDB::deleteRecords(const sqlb::ObjectIdentifier& table, const std::vector<QString>& rowids, const sqlb::StringVector& pseudo_pk)
1362 {
1363 if (!isOpen()) return false;
1364
1365 // Get primary key of the object to edit.
1366 sqlb::StringVector pks = primaryKeyForEditing(table, pseudo_pk);
1367 if(pks.empty())
1368 {
1369 lastErrorMessage = tr("Cannot delete this object");
1370 return false;
1371 }
1372
1373 // Quote all values in advance
1374 std::vector<std::string> quoted_rowids;
1375 for(QString rowid : rowids)
1376 quoted_rowids.push_back(sqlb::escapeString(rowid.toStdString()));
1377
1378 // For a single rowid column we can use a SELECT ... IN(...) statement which is faster.
1379 // For multiple rowid columns we have to use sqlb_make_single_value to decode the composed rowid values.
1380 std::string statement;
1381 if(pks.size() == 1)
1382 {
1383 statement = "DELETE FROM " + table.toString() + " WHERE " + pks.at(0) + " IN ("+ sqlb::joinStringVector(quoted_rowids, ", ") + ");";
1384 } else {
1385 statement = "DELETE FROM " + table.toString() + " WHERE ";
1386
1387 statement += "sqlb_make_single_value(";
1388 for(const auto& pk : pks)
1389 statement += sqlb::escapeIdentifier(pk) + ",";
1390 statement.erase(statement.end()-1);
1391 statement += ") IN (" + sqlb::joinStringVector(quoted_rowids, ", ") + ")";
1392 }
1393
1394 if(executeSQL(statement))
1395 {
1396 return true;
1397 } else {
1398 qWarning() << "deleteRecord: " << lastErrorMessage;
1399 return false;
1400 }
1401 }
1402
updateRecord(const sqlb::ObjectIdentifier & table,const std::string & column,const QString & rowid,const QByteArray & value,int force_type,const sqlb::StringVector & pseudo_pk)1403 bool DBBrowserDB::updateRecord(const sqlb::ObjectIdentifier& table, const std::string& column,
1404 const QString& rowid, const QByteArray& value, int force_type, const sqlb::StringVector& pseudo_pk)
1405 {
1406 waitForDbRelease();
1407 if (!isOpen()) return false;
1408
1409 // Get primary key of the object to edit.
1410 sqlb::StringVector pks = primaryKeyForEditing(table, pseudo_pk);
1411 if(pks.empty())
1412 {
1413 lastErrorMessage = tr("Cannot set data on this object");
1414 return false;
1415 }
1416
1417 std::string sql = "UPDATE " + table.toString() + " SET " + sqlb::escapeIdentifier(column) + "=? WHERE ";
1418
1419 // For a single rowid column we can use a simple WHERE condition, for multiple rowid columns we have to use sqlb_make_single_value to decode the composed rowid values.
1420 if(pks.size() == 1)
1421 sql += sqlb::escapeIdentifier(pks.front()) + "=" + sqlb::escapeString(rowid.toStdString());
1422 else
1423 sql += "sqlb_make_single_value(" + sqlb::joinStringVector(sqlb::escapeIdentifier(pks), ",") + ")=" + sqlb::escapeString(rowid.toStdString());
1424
1425 setSavepoint();
1426 logSQL(QString::fromStdString(sql), kLogMsg_App);
1427
1428 // If we get a NULL QByteArray we insert a NULL value, and for that
1429 // we can pass NULL to sqlite3_bind_text() so that it behaves like sqlite3_bind_null()
1430 const char *rawValue = value.isNull() ? nullptr : value.constData();
1431
1432 sqlite3_stmt* stmt;
1433 int success = 1;
1434 if(sqlite3_prepare_v2(_db, sql.c_str(), static_cast<int>(sql.size()), &stmt, nullptr) != SQLITE_OK)
1435 success = 0;
1436 if(success == 1) {
1437 if(force_type == SQLITE_BLOB)
1438 {
1439 if(sqlite3_bind_blob(stmt, 1, rawValue, value.length(), SQLITE_STATIC))
1440 success = -1;
1441 } else if(force_type == SQLITE_INTEGER) {
1442 if(sqlite3_bind_int64(stmt, 1, value.toLongLong()))
1443 success = -1;
1444 } else if(force_type == SQLITE_FLOAT) {
1445 if(sqlite3_bind_double(stmt, 1, value.toDouble()))
1446 success = -1;
1447 } else {
1448 if(sqlite3_bind_text(stmt, 1, rawValue, value.length(), SQLITE_STATIC))
1449 success = -1;
1450 }
1451 }
1452 if(success == 1 && sqlite3_step(stmt) != SQLITE_DONE)
1453 success = -1;
1454 if(success != 0 && sqlite3_finalize(stmt) != SQLITE_OK)
1455 success = -1;
1456
1457 if(success == 1)
1458 {
1459 return true;
1460 } else {
1461 lastErrorMessage = sqlite3_errmsg(_db);
1462 qWarning() << "updateRecord: " << lastErrorMessage;
1463 return false;
1464 }
1465 }
1466
primaryKeyForEditing(const sqlb::ObjectIdentifier & table,const sqlb::StringVector & pseudo_pk) const1467 sqlb::StringVector DBBrowserDB::primaryKeyForEditing(const sqlb::ObjectIdentifier& table, const sqlb::StringVector& pseudo_pk) const
1468 {
1469 // This function returns the primary key of the object to edit. For views we support 'pseudo' primary keys which must be specified manually.
1470 // If no pseudo pk is specified we'll take the rowid column of the table instead. If this neither a table nor was a pseudo-PK specified,
1471 // it is most likely a view that hasn't been configured for editing yet. In this case we return a null string to abort.
1472
1473 if(pseudo_pk.empty())
1474 {
1475 sqlb::TablePtr tbl = getObjectByName<sqlb::Table>(table);
1476 if(tbl)
1477 return tbl->rowidColumns();
1478 } else {
1479 return pseudo_pk;
1480 }
1481
1482 return sqlb::StringVector();
1483 }
1484
createTable(const sqlb::ObjectIdentifier & name,const sqlb::FieldVector & structure)1485 bool DBBrowserDB::createTable(const sqlb::ObjectIdentifier& name, const sqlb::FieldVector& structure)
1486 {
1487 // Build SQL statement
1488 sqlb::Table table(name.name());
1489 for(size_t i=0;i<structure.size();i++)
1490 table.fields.push_back(structure.at(i));
1491
1492 // Execute it and update the schema
1493 return executeSQL(table.sql(name.schema()));
1494 }
1495
addColumn(const sqlb::ObjectIdentifier & tablename,const sqlb::Field & field)1496 bool DBBrowserDB::addColumn(const sqlb::ObjectIdentifier& tablename, const sqlb::Field& field)
1497 {
1498 return executeSQL("ALTER TABLE " + tablename.toString() + " ADD COLUMN " + field.toString());
1499 }
1500
alterTable(const sqlb::ObjectIdentifier & tablename,const sqlb::Table & new_table,AlterTableTrackColumns track_columns,std::string newSchemaName)1501 bool DBBrowserDB::alterTable(const sqlb::ObjectIdentifier& tablename, const sqlb::Table& new_table, AlterTableTrackColumns track_columns, std::string newSchemaName)
1502 {
1503 // This function is split into three different parts:
1504 // Part 1 checks the arguments and prepares them for processing. It also prepares the transaction etc.
1505 // Part 2 uses the built-in abilities of SQLite to change as much of the table schema as possible.
1506 // Part 3 does the rest of the changes to the table schema. It also finishes the transaction etc.
1507
1508 //
1509 // P A R T 1
1510 //
1511
1512 // If no new schema name has been set, we just use the old schema name
1513 if(newSchemaName.empty())
1514 {
1515 newSchemaName = tablename.schema();
1516
1517 // When renaming the table in the current schema, check if it doesn't exist already in there
1518 if(tablename.name() != new_table.name() && getObjectByName(sqlb::ObjectIdentifier(newSchemaName, new_table.name())) != nullptr)
1519 {
1520 lastErrorMessage = tr("A table with the name '%1' already exists in schema '%2'.").arg(QString::fromStdString(new_table.name()), QString::fromStdString(newSchemaName));
1521 return false;
1522 }
1523 } else {
1524 // We're moving the table to a different schema. So check first if it doesn't already exist in the new schema.
1525 if(newSchemaName != tablename.schema() && getObjectByName(sqlb::ObjectIdentifier(newSchemaName, new_table.name())) != nullptr)
1526 {
1527 lastErrorMessage = tr("A table with the name '%1' already exists in schema '%2'.").arg(QString::fromStdString(new_table.name()), QString::fromStdString(newSchemaName));
1528 return false;
1529 }
1530 }
1531
1532 // Get old table schema
1533 sqlb::TablePtr old_table_ptr = getObjectByName<sqlb::Table>(tablename);
1534 if(old_table_ptr == nullptr)
1535 {
1536 lastErrorMessage = tr("No table with name '%1' exists in schema '%2'.").arg(QString::fromStdString(tablename.name()), QString::fromStdString(tablename.schema()));
1537 return false;
1538 }
1539 sqlb::Table old_table(*old_table_ptr);
1540
1541 // Check if tracked fields actually exist in the old table
1542 for(const auto& old_it : track_columns)
1543 {
1544 if(!old_it.first.isNull() && sqlb::findField(old_table, old_it.first.toStdString()) == old_table.fields.end())
1545 {
1546 lastErrorMessage = tr("Cannot find column %1.").arg(old_it.first);
1547 return false;
1548 }
1549 }
1550
1551 // Check if there are any columns in the old table which are not mentioned in the tracked columns list.
1552 // We do this before checking if all tracked fields are in the new table to make sure the following check includes them.
1553 for(const auto& field : old_table.fields)
1554 {
1555 if(track_columns.find(QString::fromStdString(field.name())) == track_columns.end())
1556 {
1557 // If a field isn't tracked, add it to the list and indicate explicitly that it has the same name in the new table
1558 track_columns[QString::fromStdString(field.name())] = QString::fromStdString(field.name());
1559 }
1560 }
1561
1562 // Check if tracked fields actually exist in the new table
1563 for(const auto& new_name_it : track_columns)
1564 {
1565 if(!new_name_it.second.isNull() && sqlb::findField(new_table, new_name_it.second.toStdString()) == new_table.fields.end())
1566 {
1567 lastErrorMessage = tr("Cannot find column %1.").arg(new_name_it.second);
1568 return false;
1569 }
1570 }
1571
1572 // Check if any changes were made to the table schema
1573 if(old_table == new_table)
1574 return true;
1575
1576 // Create savepoint to be able to go back to it in case of any error
1577 std::string savepointName = generateSavepointName("renamecolumn");
1578 if(!setSavepoint(savepointName))
1579 {
1580 lastErrorMessage = tr("Creating savepoint failed. DB says: %1").arg(lastErrorMessage);
1581 return false;
1582 }
1583
1584 // No automatic schema updates from now on
1585 NoStructureUpdateChecks nup(*this);
1586
1587 //
1588 // P A R T 2
1589 //
1590
1591 // This variable is used to track whether something was changed by this part of the function
1592 bool changed_something = false;
1593
1594 // Rename table if necessary
1595 if(newSchemaName == tablename.schema() && tablename.name() != new_table.name())
1596 {
1597 if(!renameTable(tablename.schema(), old_table.name(), new_table.name()))
1598 {
1599 revertToSavepoint(savepointName);
1600 return false;
1601 }
1602
1603 changed_something = true;
1604 }
1605
1606 // Add columns if necessary
1607 for(const auto& field : new_table.fields)
1608 {
1609 // We loop through all the fields of the new table schema and check for each of them if they are new.
1610 // If so, we add that field. The reason for looping through the new table schema instead of the track_columns
1611 // map is that this way we make sure to preserve their order which increases our chances that we are done after
1612 // this step.
1613 if(std::any_of(track_columns.begin(), track_columns.end(), [&field](const std::pair<QString, QString>& p) {
1614 return p.first.isNull() && p.second.toStdString() == field.name();
1615 }))
1616 {
1617 if(!addColumn(sqlb::ObjectIdentifier(tablename.schema(), new_table.name()), field))
1618 {
1619 revertToSavepoint(savepointName);
1620 return false;
1621 }
1622 }
1623
1624 changed_something = true;
1625 }
1626
1627 // Newer versions of SQLite add a better ALTER TABLE support which we can use
1628 #if SQLITE_VERSION_NUMBER >= 3025000
1629 // If the name of a field should be changed do that by using SQLite's ALTER TABLE feature. We build a new
1630 // map for tracking column names here which uses the update column names as the old names too. This is to
1631 // make sure we are using the new table layout for later updates.
1632 AlterTableTrackColumns new_track_columns;
1633 for(const auto& old_name_it : track_columns)
1634 {
1635 QString old_name = old_name_it.first;
1636
1637 QString new_name = track_columns[old_name];
1638 if(!old_name.isNull() && !new_name.isNull() && new_name != old_name)
1639 {
1640 if(!executeSQL("ALTER TABLE " + sqlb::ObjectIdentifier(tablename.schema(), new_table.name()).toString() + " RENAME COLUMN " +
1641 sqlb::escapeIdentifier(old_name.toStdString()) + " TO " + sqlb::escapeIdentifier(new_name.toStdString())))
1642 {
1643 QString error(tr("Renaming the column failed. DB says:\n%1").arg(lastErrorMessage));
1644 revertToSavepoint(savepointName);
1645 lastErrorMessage = error;
1646 return false;
1647 }
1648
1649 changed_something = true;
1650 new_track_columns.insert({new_name, new_name});
1651 } else {
1652 new_track_columns.insert({old_name, new_name});
1653 }
1654 }
1655 track_columns.swap(new_track_columns);
1656 #endif
1657
1658 // Update our schema representation to get the new table and all the changed triggers, views and indices
1659 if(changed_something)
1660 {
1661 updateSchema();
1662 old_table = *getObjectByName<sqlb::Table>(sqlb::ObjectIdentifier(tablename.schema(), new_table.name()));
1663 }
1664
1665 // Check if there's still more work to be done or if we are finished now
1666 if(tablename.schema() == newSchemaName && old_table == new_table)
1667 {
1668 // Release the savepoint - everything went fine
1669 if(!releaseSavepoint(savepointName))
1670 {
1671 lastErrorMessage = tr("Releasing savepoint failed. DB says: %1").arg(lastErrorMessage);
1672 return false;
1673 }
1674
1675 // Success, update the DB schema before returning
1676 updateSchema();
1677 return true;
1678 }
1679
1680 //
1681 // P A R T 3
1682 //
1683
1684 // Create a new table with the desired schema and a name that doesn't exist yet
1685 std::string new_table_name = new_table.name();
1686 sqlb::Table new_table_with_random_name(new_table);
1687 new_table_with_random_name.setName(generateTemporaryTableName(newSchemaName));
1688 if(!executeSQL(new_table_with_random_name.sql(newSchemaName), true, true))
1689 {
1690 QString error(tr("Creating new table failed. DB says: %1").arg(lastErrorMessage));
1691 revertToSavepoint(savepointName);
1692 lastErrorMessage = error;
1693 return false;
1694 }
1695
1696 // Assemble list of column names to copy from in the old table and list of column names to into into in the new table
1697 sqlb::StringVector copy_values_from;
1698 sqlb::StringVector copy_values_to;
1699 for(const auto& from_it : track_columns)
1700 {
1701 const auto& from = from_it.first;
1702
1703 // Ignore new fields
1704 if(from.isNull())
1705 continue;
1706
1707 // Ignore deleted fields
1708 QString to = track_columns[from];
1709 if(to.isNull())
1710 continue;
1711
1712 copy_values_from.push_back(from.toStdString());
1713 copy_values_to.push_back(to.toStdString());
1714 }
1715
1716 // Copy the data from the old table to the new one
1717 if(!executeSQL("INSERT INTO " + sqlb::escapeIdentifier(newSchemaName) + "." + sqlb::escapeIdentifier(new_table_with_random_name.name()) +
1718 " (" + sqlb::joinStringVector(sqlb::escapeIdentifier(copy_values_to), ",") + ") SELECT " +
1719 sqlb::joinStringVector(sqlb::escapeIdentifier(copy_values_from), ",") + " FROM " +
1720 sqlb::escapeIdentifier(tablename.schema()) + "." + sqlb::escapeIdentifier(old_table.name())))
1721 {
1722 QString error(tr("Copying data to new table failed. DB says:\n%1").arg(lastErrorMessage));
1723 revertToSavepoint(savepointName);
1724 lastErrorMessage = error;
1725 return false;
1726 }
1727
1728 // Save all indices, triggers and views associated with this table because SQLite deletes them when we drop the table in the next step
1729 std::vector<std::string> otherObjectsSql;
1730 for(const auto& schema : schemata[tablename.schema()])
1731 {
1732 const auto& it = schema.second;
1733
1734 // If this object references the table and it's not the table itself save it's SQL string
1735 if(it->baseTable() == old_table.name() && it->type() != sqlb::Object::Types::Table)
1736 {
1737 // If this is an index, update the fields first. This highly increases the chance that the SQL statement won't throw an
1738 // error later on when we try to recreate it.
1739 if(it->type() == sqlb::Object::Types::Index)
1740 {
1741 sqlb::IndexPtr idx = std::dynamic_pointer_cast<sqlb::Index>(it);
1742
1743 // Loop through all changes to the table schema. For indices only the column names are relevant, so it suffices to look at the
1744 // list of tracked columns
1745 for(const auto& from_it : track_columns)
1746 {
1747 const auto& from = from_it.first;
1748 const auto& to = from_it.second;
1749
1750 // Are we updating the field name or are we removing the field entirely?
1751 if(!to.isNull())
1752 {
1753 // We're updating the field name. So search for it in the index and replace it whereever it is found
1754 for(size_t i=0;i<idx->fields.size();i++)
1755 {
1756 if(idx->fields[i].name() == from.toStdString())
1757 idx->fields[i].setName(to.toStdString());
1758 }
1759 } else {
1760 // We're removing a field. So remove it from any indices, too.
1761 while(sqlb::removeField(idx, from.toStdString()))
1762 ;
1763 }
1764 }
1765
1766 // Only try to add the index later if it has any columns remaining. Also use the new schema name here, too, to basically move
1767 // any index that references the table to the same new schema as the table.
1768 if(idx->fields.size())
1769 otherObjectsSql.push_back(idx->sql(newSchemaName));
1770 } else {
1771 // If it's a view or a trigger we don't have any chance to corrections yet. Just store the statement as is and
1772 // hope for the best.
1773 otherObjectsSql.push_back(it->originalSql() + ";");
1774 }
1775 }
1776 }
1777
1778 // We need to disable foreign keys here. The reason is that in the next step the entire table will be dropped and there might be foreign keys
1779 // in other tables that reference this table. These foreign keys would then cause the drop command in the next step to fail. However, we can't
1780 // simply disable foreign keys here since that is not allowed from inside a transaction and we definitely are inside a transaction at that point.
1781 // So what we do instead is defer foreign key enforcement until the end of the transaction which effectively disables foreign keys for us here.
1782 // But because we don't really want to defer foreign keys, the former value of that pragma is saved here in order to restore the old value later.
1783 QString foreignKeysOldSettings = getPragma("defer_foreign_keys");
1784 setPragma("defer_foreign_keys", "1");
1785
1786 // Delete the old table
1787 if(!executeSQL("DROP TABLE " + sqlb::escapeIdentifier(tablename.schema()) + "." + sqlb::escapeIdentifier(old_table.name()), true, true))
1788 {
1789 QString error(tr("Deleting old table failed. DB says: %1").arg(lastErrorMessage));
1790 revertToSavepoint(savepointName);
1791 lastErrorMessage = error;
1792 return false;
1793 }
1794
1795 // Rename the temporary table
1796 if(!renameTable(newSchemaName, new_table_with_random_name.name(), new_table.name()))
1797 {
1798 revertToSavepoint(savepointName);
1799 return false;
1800 }
1801
1802 // Restore the former foreign key settings
1803 setPragma("defer_foreign_keys", foreignKeysOldSettings);
1804
1805 // Restore the saved triggers, views and indices
1806 std::string errored_sqls;
1807 for(const std::string& sql : otherObjectsSql)
1808 {
1809 if(!executeSQL(sql, true, true))
1810 errored_sqls += sql + "\n";
1811 }
1812 if(!errored_sqls.empty())
1813 {
1814 QMessageBox::information(nullptr, qApp->applicationName(), tr("Restoring some of the objects associated with this table failed. "
1815 "This is most likely because some column names changed. "
1816 "Here's the SQL statement which you might want to fix and execute manually:\n\n")
1817 + QString::fromStdString(errored_sqls));
1818 }
1819
1820 // Release the savepoint - everything went fine
1821 if(!releaseSavepoint(savepointName))
1822 {
1823 lastErrorMessage = tr("Releasing savepoint failed. DB says: %1").arg(lastErrorMessage);
1824 return false;
1825 }
1826
1827 // Success, update the DB schema before returning
1828 updateSchema();
1829 return true;
1830 }
1831
renameTable(const std::string & schema,const std::string & from_table,const std::string & to_table)1832 bool DBBrowserDB::renameTable(const std::string& schema, const std::string& from_table, const std::string& to_table)
1833 {
1834 // Do nothing if table names are the same
1835 if(from_table == to_table)
1836 return true;
1837
1838 // Check if table names only differ in case. If they do, we have to rename the table twice because SQLite can't rename 'table' to 'Table'.
1839 // To solve this we rename 'table' to 'some temp name' and then 'some temp name' to 'Table'.
1840 if(compare_ci(from_table, to_table))
1841 {
1842 // Generate a temporary table name and rename the table via that by recusrively calling this function
1843 std::string temp_name = generateTemporaryTableName(schema);
1844 if(!renameTable(schema, from_table, temp_name))
1845 return false;
1846 if(!renameTable(schema, temp_name, to_table))
1847 return false;
1848
1849 // Exit here
1850 return true;
1851 }
1852
1853 // The old and the new table names differ (and that not only in case)
1854
1855 // Rename the table
1856 std::string sql = "ALTER TABLE " + sqlb::escapeIdentifier(schema) + "." + sqlb::escapeIdentifier(from_table) + " RENAME TO " + sqlb::escapeIdentifier(to_table);
1857 if(!executeSQL(sql))
1858 {
1859 QString error = tr("Error renaming table '%1' to '%2'.\n"
1860 "Message from database engine:\n%3").arg(QString::fromStdString(from_table), QString::fromStdString(to_table), lastErrorMessage);
1861 lastErrorMessage = error;
1862 qWarning() << lastErrorMessage;
1863 return false;
1864 } else {
1865 return true;
1866 }
1867 }
1868
logSQL(const QString & statement,LogMessageType msgtype) const1869 void DBBrowserDB::logSQL(const QString& statement, LogMessageType msgtype) const
1870 {
1871 // Remove any leading and trailing spaces, tabs, or line breaks first
1872 emit sqlExecuted(statement.trimmed(), msgtype);
1873 }
1874
updateSchema()1875 void DBBrowserDB::updateSchema()
1876 {
1877 waitForDbRelease();
1878
1879 schemata.clear();
1880
1881 // Exit here is no DB is opened
1882 if(!isOpen())
1883 return;
1884
1885 // Get a list of all databases. This list always includes the main and the temp database but can include more items if there are attached databases
1886 if(!executeSQL("PRAGMA database_list;", false, true, [this](int, std::vector<QByteArray> db_values, std::vector<QByteArray>) -> bool {
1887 // Get the schema name which is in column 1 (counting starts with 0). 0 contains an ID and 2 the file path.
1888 const std::string schema_name = db_values.at(1).toStdString();
1889
1890 // Always add the schema to the map. This makes sure it's even then added when there are no objects in the database
1891 schemata[schema_name] = objectMap();
1892
1893 // Get a list of all the tables for the current database schema. We need to do this differently for normal databases and the temporary schema
1894 // because SQLite doesn't understand the "temp.sqlite_master" notation.
1895 std::string statement;
1896 if(schema_name == "temp")
1897 statement = "SELECT type,name,sql,tbl_name FROM sqlite_temp_master;";
1898 else
1899 statement = "SELECT type,name,sql,tbl_name FROM " + sqlb::escapeIdentifier(schema_name) + ".sqlite_master;";
1900
1901 if(!executeSQL(statement, false, true, [this, schema_name](int, std::vector<QByteArray> values, std::vector<QByteArray>) -> bool {
1902 const std::string val_type = values.at(0).toStdString();
1903 const std::string val_name = values.at(1).toStdString();
1904 std::string val_sql = values.at(2).toStdString();
1905 const std::string val_tblname = values.at(3).toStdString();
1906
1907 if(!val_sql.empty())
1908 {
1909 val_sql.erase(std::remove(val_sql.begin(), val_sql.end(), '\r'), val_sql.end());
1910
1911 sqlb::ObjectPtr object;
1912 if(val_type == "table")
1913 object = sqlb::Table::parseSQL(val_sql);
1914 else if(val_type == "index")
1915 object = sqlb::Index::parseSQL(val_sql);
1916 else if(val_type == "trigger")
1917 object = sqlb::Trigger::parseSQL(val_sql);
1918 else if(val_type == "view")
1919 object = sqlb::View::parseSQL(val_sql);
1920 else
1921 return false;
1922
1923 // If parsing wasn't successful set the object name manually, so that at least the name is going to be correct
1924 if(!object->fullyParsed())
1925 object->setName(val_name);
1926
1927 // For virtual tables and views query the column list using the SQLite pragma because for both we can't yet rely on our grammar parser
1928 if((object->type() == sqlb::Object::Types::Table && std::dynamic_pointer_cast<sqlb::Table>(object)->isVirtual()) || object->type() == sqlb::Object::Types::View)
1929 {
1930 const auto columns = queryColumnInformation(schema_name, val_name);
1931
1932 if(object->type() == sqlb::Object::Types::Table)
1933 {
1934 sqlb::TablePtr tab = std::dynamic_pointer_cast<sqlb::Table>(object);
1935 for(const auto& column : columns)
1936 tab->fields.emplace_back(column.first, column.second);
1937 } else {
1938 sqlb::ViewPtr view = std::dynamic_pointer_cast<sqlb::View>(object);
1939 for(const auto& column : columns)
1940 view->fields.emplace_back(column.first, column.second);
1941 }
1942 } else if(object->type() == sqlb::Object::Types::Trigger) {
1943 // For triggers set the name of the table the trigger operates on here because we don't have a parser for trigger statements yet.
1944 sqlb::TriggerPtr trg = std::dynamic_pointer_cast<sqlb::Trigger>(object);
1945 trg->setTable(val_tblname);
1946 }
1947
1948 schemata[schema_name].insert({val_type, object});
1949 }
1950
1951 return false;
1952 }))
1953 {
1954 qWarning() << tr("could not get list of db objects: %1").arg(sqlite3_errmsg(_db));
1955 }
1956
1957 return false;
1958 }))
1959 {
1960 qWarning() << tr("could not get list of databases: %1").arg(sqlite3_errmsg(_db));
1961 }
1962
1963 emit structureUpdated();
1964 }
1965
getPragma(const std::string & pragma) const1966 QString DBBrowserDB::getPragma(const std::string& pragma) const
1967 {
1968 if (pragma=="case_sensitive_like")
1969 return querySingleValueFromDb("SELECT 'x' NOT LIKE 'X';");
1970 else
1971 return querySingleValueFromDb("PRAGMA " + pragma + ";");
1972 }
1973
setPragma(const std::string & pragma,const QString & value)1974 bool DBBrowserDB::setPragma(const std::string& pragma, const QString& value)
1975 {
1976 // Set the pragma value
1977 std::string sql = "PRAGMA " + pragma + " = '" + value.toStdString() + "';";
1978
1979 // In general, we want to commit changes before running pragmas because most of them can't be rolled back and some of them
1980 // even fail when run in a transaction. However, the defer_foreign_keys pragma has neither problem and we need it to be settable
1981 // inside transactions (see the renameColumn() function where it is set and reset at some point and where we don't want the changes
1982 // to be committed just because of this pragma).
1983 if(pragma != "defer_foreign_keys")
1984 releaseSavepoint();
1985
1986 bool res = executeSQL(sql, false, true); // PRAGMA statements are usually not transaction bound, so we can't revert
1987 if( !res )
1988 qWarning() << tr("Error setting pragma %1 to %2: %3").arg(QString::fromStdString(pragma), value, lastErrorMessage);
1989
1990 // If this is the page_size or the auto_vacuum pragma being set, we need to execute the vacuum command right after the pragma statement or the new
1991 // settings won't be saved.
1992 if(res && (pragma == "page_size" || pragma == "auto_vacuum"))
1993 res = executeSQL("VACUUM;", false, true);
1994
1995 return res;
1996 }
1997
setPragma(const std::string & pragma,const QString & value,QString & originalvalue)1998 bool DBBrowserDB::setPragma(const std::string& pragma, const QString& value, QString& originalvalue)
1999 {
2000 if( originalvalue != value )
2001 {
2002 if( setPragma(pragma, value))
2003 {
2004 originalvalue = value;
2005 return true;
2006 }
2007 }
2008 return false;
2009 }
2010
setPragma(const std::string & pragma,int value,int & originalvalue)2011 bool DBBrowserDB::setPragma(const std::string& pragma, int value, int& originalvalue)
2012 {
2013 if( originalvalue != value )
2014 {
2015 QString val = QString::number(value);
2016 QString origval = QString::number(originalvalue);
2017 if( setPragma(pragma, val, origval))
2018 {
2019 originalvalue = value;
2020 }
2021 }
2022 return false;
2023 }
2024
loadExtension(const QString & filePath)2025 bool DBBrowserDB::loadExtension(const QString& filePath)
2026 {
2027 waitForDbRelease();
2028 if(!_db)
2029 return false;
2030
2031 // Check if file exists
2032 if(!QFile::exists(filePath))
2033 {
2034 lastErrorMessage = tr("File not found.");
2035 return false;
2036 }
2037
2038 // Enable extension loading
2039 sqlite3_enable_load_extension(_db, 1);
2040
2041 // Try to load extension
2042 char* error;
2043 int result = sqlite3_load_extension(_db, filePath.toUtf8(), nullptr, &error);
2044
2045 // Disable extension loading if so configured
2046 // (we don't want to leave the possibility of calling load_extension() from SQL without user informed permission)
2047 if (!Settings::getValue("extensions", "enable_load_extension").toBool())
2048 sqlite3_enable_load_extension(_db, 0);
2049
2050 if (result == SQLITE_OK)
2051 {
2052 return true;
2053 } else {
2054 lastErrorMessage = QString::fromUtf8(error);
2055 sqlite3_free(error);
2056 return false;
2057 }
2058 }
2059
2060
loadExtensionsFromSettings()2061 void DBBrowserDB::loadExtensionsFromSettings()
2062 {
2063 if(!_db)
2064 return;
2065
2066 sqlite3_enable_load_extension(_db, Settings::getValue("extensions", "enable_load_extension").toBool());
2067
2068 QStringList list = Settings::getValue("extensions", "list").toStringList();
2069 for(const QString& ext : list)
2070 {
2071 if(loadExtension(ext) == false)
2072 QMessageBox::warning(nullptr, QApplication::applicationName(), tr("Error loading extension: %1").arg(lastError()));
2073 }
2074 }
2075
queryColumnInformation(const std::string & schema_name,const std::string & object_name) const2076 std::vector<std::pair<std::string, std::string>> DBBrowserDB::queryColumnInformation(const std::string& schema_name, const std::string& object_name) const
2077 {
2078 waitForDbRelease();
2079
2080 std::vector<std::pair<std::string, std::string>> result;
2081 std::string statement = "PRAGMA " + sqlb::escapeIdentifier(schema_name) + ".TABLE_INFO(" + sqlb::escapeIdentifier(object_name) + ");";
2082 logSQL(QString::fromStdString(statement), kLogMsg_App);
2083
2084 sqlite3_stmt* vm;
2085 const char* tail;
2086 if(sqlite3_prepare_v2(_db, statement.c_str(), static_cast<int>(statement.size()), &vm, &tail) == SQLITE_OK)
2087 {
2088 while(sqlite3_step(vm) == SQLITE_ROW)
2089 {
2090 std::string name = reinterpret_cast<const char*>(sqlite3_column_text(vm, 1));
2091 std::string type = reinterpret_cast<const char*>(sqlite3_column_text(vm, 2));
2092
2093 result.push_back(std::make_pair(name, type));
2094 }
2095 sqlite3_finalize(vm);
2096 } else{
2097 lastErrorMessage = tr("could not get column information");
2098 }
2099
2100 return result;
2101 }
2102
generateSavepointName(const std::string & identifier) const2103 std::string DBBrowserDB::generateSavepointName(const std::string& identifier) const
2104 {
2105 // Generate some sort of unique name for a savepoint for internal use.
2106 return "db4s_" + identifier + "_" + std::to_string(std::chrono::system_clock::now().time_since_epoch().count());
2107 }
2108
generateTemporaryTableName(const std::string & schema) const2109 std::string DBBrowserDB::generateTemporaryTableName(const std::string& schema) const
2110 {
2111 // We're using a static variable as a counter here instead of checking from the beginning onwards every time. This has
2112 // two reasons: 1) It makes the function thread-safe, and 2) it saves us some time because in case older temporary tables
2113 // are still in use. Both reasons don't matter too much for now, but just in case...
2114 static std::atomic_uint counter;
2115
2116 while(true)
2117 {
2118 std::string table_name = "sqlb_temp_table_" + std::to_string(++counter);
2119 if(!getObjectByName(sqlb::ObjectIdentifier(schema, table_name)))
2120 return table_name;
2121 }
2122 }
2123
interruptQuery()2124 void DBBrowserDB::interruptQuery()
2125 {
2126 if(!_db)
2127 return;
2128
2129 sqlite3_interrupt(_db);
2130 }
2131