#include "sqlitedb.h"

#include "sqlite.h"

#include "sqlitetablemodel.h"

#include "CipherDialog.h"

#include "Settings.h"

#include <QFile>

#include <QMessageBox>

#include <QProgressDialog>

#include <QApplication>

#include <QInputDialog>

#include <QFileInfo>

#include <QDir>

#include <QDateTime>

#include <functional>

#include <atomic>

template <typename T>

struct Callback;

template <typename Ret, typename... Params>

struct Callback<Ret(Params...)> {

template <typename... Args>

static Ret callback(Args... args) { return func(args...); }

static std::function<Ret(Params...)> func;

};

template <typename Ret, typename... Params>

std::function<Ret(Params...)> Callback<Ret(Params...)>::func;

int collCompare(void* /*pArg*/, int sizeA, const void* sA, int sizeB, const void* sB)

{

if(sizeA == sizeB)

return memcmp(sA, sB, sizeA);

return sizeA - sizeB;

}

static int sqlite_compare_utf16( void* /*arg*/,int size1, const void *str1, int size2, const void* str2)

{

const QString string1 = QString::fromRawData(reinterpret_cast<const QChar*>(str1), size1 / sizeof(QChar));

const QString string2 = QString::fromRawData(reinterpret_cast<const QChar*>(str2), size2 / sizeof(QChar));

return QString::compare(string1, string2, Qt::CaseSensitive);

}

static int sqlite_compare_utf16ci( void* /*arg*/,int size1, const void *str1, int size2, const void* str2)

{

const QString string1 = QString::fromRawData(reinterpret_cast<const QChar*>(str1), size1 / sizeof(QChar));

const QString string2 = QString::fromRawData(reinterpret_cast<const QChar*>(str2), size2 / sizeof(QChar));

return QString::compare(string1, string2, Qt::CaseInsensitive);

}

void DBBrowserDB::collationNeeded(void* /*pData*/, sqlite3* /*db*/, int eTextRep, const char* sCollationName)

{

QString name(sCollationName);

// Don't request built-in collations. SQLite requests these collations even though they are built into

// the library. Since we have no need for overriding them, we just silently ignore these requests.

if(name.compare("BINARY", Qt::CaseInsensitive) &&

name.compare("NOCASE", Qt::CaseInsensitive) &&

name.compare("RTRIM", Qt::CaseInsensitive))

{

emit requestCollation(name, eTextRep);

}

}

static void regexp(sqlite3_context* ctx, int /*argc*/, sqlite3_value* argv[])

{

// Get arguments and check their values

QRegExp arg1((const char*)sqlite3_value_text(argv[0]));

QString arg2((const char*)sqlite3_value_text(argv[1]));

if(!arg1.isValid())

return sqlite3_result_error(ctx, "invalid operand", -1);

// Set the pattern matching syntax to a Perl-like one. This is the default in Qt 4.x but Qt 5

// changes this to a greedy one (QRegExp::RegExp2). To make sure the behaviour of our application

// doesn't change depending on the build environment, we make sure to always set the same pattern

// matching syntax.

arg1.setPatternSyntax(QRegExp::RegExp);

// Perform the actual matching and return the result. Note that Qt's QRegExp returns -1 if the regex

// doesn't match and the position in the string otherwise; SQLite expects a 0 for not found and a 1 for found.

sqlite3_result_int(ctx, arg1.indexIn(arg2) >= 0);

}

bool DBBrowserDB::isOpen ( ) const

{

return _db != nullptr;

}

bool DBBrowserDB::getDirty() const

{

return !savepointList.empty();

}

bool DBBrowserDB::open(const QString& db, bool readOnly)

{

if (isOpen()) close();

isEncrypted = false;

dontCheckForStructureUpdates = false;

// Get encryption settings for database file

CipherDialog* cipher = nullptr;

if(tryEncryptionSettings(db, &isEncrypted, cipher) == false)

return false;

// Open database file

if(sqlite3_open_v2(db.toUtf8(), &_db, readOnly ? SQLITE_OPEN_READONLY : SQLITE_OPEN_READWRITE, NULL) != SQLITE_OK)

{

lastErrorMessage = QString::fromUtf8((const char*)sqlite3_errmsg(_db));

return false;

}

// Set encryption details if database is encrypted

#ifdef ENABLE_SQLCIPHER

if(isEncrypted && cipher)

{

executeSQL(QString("PRAGMA key = %1").arg(cipher->password()), false, false);

if(cipher->pageSize() != 1024)

executeSQL(QString("PRAGMA cipher_page_size = %1;").arg(cipher->pageSize()), false, false);

}

#endif

delete cipher;

if (_db)

{

// add UTF16 collation (comparison is performed by QString functions)

sqlite3_create_collation(_db, "UTF16", SQLITE_UTF16, nullptr, sqlite_compare_utf16);

// add UTF16CI (case insensitive) collation (comparison is performed by QString functions)

sqlite3_create_collation(_db, "UTF16CI", SQLITE_UTF16, nullptr, sqlite_compare_utf16ci);

// register collation callback

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);

void (*c_callback)(void*, sqlite3*, int, const char*) = static_cast<decltype(c_callback)>(Callback<void(void*, sqlite3*, int, const char*)>::callback);

sqlite3_collation_needed(_db, NULL, c_callback);

// Set foreign key settings as requested in the preferences

bool foreignkeys = Settings::getValue("db", "foreignkeys").toBool();

setPragma("foreign_keys", foreignkeys ? "1" : "0");

// Enable extension loading

sqlite3_enable_load_extension(_db, 1);

// Register REGEXP function

if(Settings::getValue("extensions", "disableregex").toBool() == false)

sqlite3_create_function(_db, "REGEXP", 2, SQLITE_UTF8, NULL, regexp, NULL, NULL);

// Check if file is read only

QFileInfo fi(db);

QFileInfo fid(fi.absoluteDir().absolutePath());

isReadOnly = readOnly || !fi.isWritable() || !fid.isWritable();

// Execute default SQL

if(!isReadOnly)

{

QString default_sql = Settings::getValue("db", "defaultsqltext").toString();

if(!default_sql.isEmpty())

executeMultiSQL(default_sql, false, true);

}

curDBFilename = db;

updateSchema();

return true;

} else {

return false;

}

}

bool DBBrowserDB::attach(const QString& filename, QString attach_as)

{

// Check if this file has already been attached and abort if this is the case

QString sql = "PRAGMA database_list;";

logSQL(sql, kLogMsg_App);

sqlite3_stmt* db_vm;

if(sqlite3_prepare_v2(_db, sql.toUtf8(), sql.toUtf8().length(), &db_vm, NULL) == SQLITE_OK)

{

// Loop through all the databases

QFileInfo fi(filename);

while(sqlite3_step(db_vm) == SQLITE_ROW)

{

QFileInfo path(QString::fromUtf8((const char*)sqlite3_column_text(db_vm, 2)));

if(fi == path)

{

QString schema = QString::fromUtf8((const char*)sqlite3_column_text(db_vm, 1));

QMessageBox::information(nullptr, qApp->applicationName(), tr("This database has already been attached. Its schema name is '%1'.").arg(schema));

return false;

}

}

}

// Ask for name to be given to the attached database if none was provided

if(attach_as.isEmpty())

attach_as = QInputDialog::getText(nullptr,

qApp->applicationName(),

tr("Please specify the database name under which you want to access the attached database"),

QLineEdit::Normal,

QFileInfo(filename).baseName()

).trimmed();

if(attach_as.isNull())

return false;

#ifdef ENABLE_SQLCIPHER

// Try encryption settings

CipherDialog* cipher = nullptr;

bool is_encrypted;

if(tryEncryptionSettings(filename, &is_encrypted, cipher) == false)

return false;

// Attach database

QString key;

if(cipher && is_encrypted)

key = "KEY " + cipher->password();

if(!executeSQL(QString("ATTACH '%1' AS %2 %3").arg(filename).arg(sqlb::escapeIdentifier(attach_as)).arg(key), false))

{

QMessageBox::warning(nullptr, qApp->applicationName(), lastErrorMessage);

return false;

}

if(cipher && cipher->pageSize() != 1024)

{

if(!executeSQL(QString("PRAGMA %1.cipher_page_size = %2").arg(sqlb::escapeIdentifier(attach_as)).arg(cipher->pageSize()), false))

{

QMessageBox::warning(nullptr, qApp->applicationName(), lastErrorMessage);

return false;

}

}

#else

// Attach database

if(!executeSQL(QString("ATTACH '%1' AS %2").arg(filename).arg(sqlb::escapeIdentifier(attach_as)), false))

{

QMessageBox::warning(nullptr, qApp->applicationName(), lastErrorMessage);

return false;

}

#endif

// Update schema to load database schema of the newly attached database

updateSchema();

return true;

}

bool DBBrowserDB::tryEncryptionSettings(const QString& filename, bool* encrypted, CipherDialog*& cipherSettings)

{

lastErrorMessage = tr("Invalid file format");

// Open database file

sqlite3* dbHandle;

if(sqlite3_open_v2(filename.toUtf8(), &dbHandle, SQLITE_OPEN_READONLY, NULL) != SQLITE_OK)

return false;

// Try reading from database

*encrypted = false;

cipherSettings = nullptr;

while(true)

{

QString statement = "SELECT COUNT(*) FROM sqlite_master;";

QByteArray utf8Statement = statement.toUtf8();

sqlite3_stmt* vm;

const char* tail;

int err = sqlite3_prepare_v2(dbHandle, utf8Statement, utf8Statement.length(), &vm, &tail);

if(err == SQLITE_BUSY || err == SQLITE_PERM || err == SQLITE_NOMEM || err == SQLITE_IOERR || err == SQLITE_CORRUPT || err == SQLITE_CANTOPEN)

{

lastErrorMessage = QString::fromUtf8((const char*)sqlite3_errmsg(dbHandle));

sqlite3_close(dbHandle);

return false;

}

if(sqlite3_step(vm) != SQLITE_ROW)

{

sqlite3_finalize(vm);

#ifdef ENABLE_SQLCIPHER

delete cipherSettings;

cipherSettings = new CipherDialog(nullptr, false);

if(cipherSettings->exec())

{

// Close and reopen database first to be in a clean state after the failed read attempt from above

sqlite3_close(dbHandle);

if(sqlite3_open_v2(filename.toUtf8(), &dbHandle, SQLITE_OPEN_READONLY, NULL) != SQLITE_OK)

{

delete cipherSettings;

cipherSettings = nullptr;

return false;

}

// Set key and, if it differs from the default value, the page size

sqlite3_exec(dbHandle, QString("PRAGMA key = %1").arg(cipherSettings->password()).toUtf8(), NULL, NULL, NULL);

if(cipherSettings->pageSize() != 1024)

sqlite3_exec(dbHandle, QString("PRAGMA cipher_page_size = %1;").arg(cipherSettings->pageSize()).toUtf8(), NULL, NULL, NULL);

*encrypted = true;

} else {

sqlite3_close(dbHandle);

*encrypted = false;

delete cipherSettings;

cipherSettings = nullptr;

return false;

}

#else

lastErrorMessage = QString::fromUtf8((const char*)sqlite3_errmsg(dbHandle));

sqlite3_close(dbHandle);

return false;

#endif

} else {

sqlite3_finalize(vm);

sqlite3_close(dbHandle);

return true;

}

}

}

bool DBBrowserDB::setSavepoint(const QString& pointname)

{

if(!isOpen())

return false;

if(savepointList.contains(pointname))

return true;

QString query = QString("SAVEPOINT %1;").arg(sqlb::escapeIdentifier(pointname));

executeSQL(query, false, false);

savepointList.append(pointname);

emit dbChanged(getDirty());

return true;

}

bool DBBrowserDB::releaseSavepoint(const QString& pointname)

{

if(!isOpen())

return false;

if(savepointList.contains(pointname) == false)

// If there is no such savepoint in the list,

// we have already released it, so in this case

// the operation should be successfull

return true;

QString query = QString("RELEASE %1;").arg(sqlb::escapeIdentifier(pointname));

if(!executeSQL(query, false, false))

return false;

// SQLite releases all savepoints that were created between

// creation of given savepoint and releasing of it,

// so we should too

int point_index = savepointList.lastIndexOf(pointname);

savepointList.erase(savepointList.begin()+point_index, savepointList.end());

emit dbChanged(getDirty());

return true;

}

bool DBBrowserDB::revertToSavepoint(const QString& pointname)

{

if(!isOpen() || savepointList.contains(pointname) == false)

return false;

QString query = QString("ROLLBACK TO SAVEPOINT %1;").arg(sqlb::escapeIdentifier(pointname));

executeSQL(query, false, false);

query = QString("RELEASE %1;").arg(sqlb::escapeIdentifier(pointname));

executeSQL(query, false, false);

// SQLite releases all savepoints that were created between

// creation of given savepoint and releasing of it,

// so we should too

int point_index = savepointList.lastIndexOf(pointname);

savepointList.erase(savepointList.begin()+point_index, savepointList.end());

emit dbChanged(getDirty());

return true;

}

bool DBBrowserDB::releaseAllSavepoints()

{

for(const QString& point : savepointList)

{

if(!releaseSavepoint(point))

return false;

}

// When still in a transaction, commit that too

if(sqlite3_get_autocommit(_db) == 0)

executeSQL("COMMIT;", false, false);

return true;

}

bool DBBrowserDB::revertAll()

{

for(const QString& point : savepointList)

{

if(!revertToSavepoint(point))

return false;

}

return true;

}

bool DBBrowserDB::create ( const QString & db)

{

if (isOpen()) close();

// read encoding from settings and open with sqlite3_open for utf8 and sqlite3_open16 for utf16

QString sEncoding = Settings::getValue("db", "defaultencoding").toString();

int openresult = SQLITE_OK;

if(sEncoding == "UTF-8" || sEncoding == "UTF8" || sEncoding == "Latin1")

openresult = sqlite3_open(db.toUtf8(), &_db);

else

openresult = sqlite3_open16(db.utf16(), &_db);

if( openresult != SQLITE_OK ){

lastErrorMessage = QString::fromUtf8((const char*)sqlite3_errmsg(_db));

sqlite3_close(_db);

_db = nullptr;

return false;

}

if (_db)

{

// Set foreign key settings as requested in the preferences

bool foreignkeys = Settings::getValue("db", "foreignkeys").toBool();

setPragma("foreign_keys", foreignkeys ? "1" : "0");

// Enable extension loading

sqlite3_enable_load_extension(_db, 1);

// Register REGEXP function

if(Settings::getValue("extensions", "disableregex").toBool() == false)

sqlite3_create_function(_db, "REGEXP", 2, SQLITE_UTF8, NULL, regexp, NULL, NULL);

// force sqlite3 do write proper file header

// if we don't create and drop the table we might end up

// with a 0 byte file, if the user cancels the create table dialog

{

NoStructureUpdateChecks nup(*this);

executeSQL("CREATE TABLE notempty (id integer primary key);", false, false);

executeSQL("DROP TABLE notempty;", false, false);

}

// Execute default SQL

QString default_sql = Settings::getValue("db", "defaultsqltext").toString();

if(!default_sql.isEmpty())

executeMultiSQL(default_sql, false, true);

curDBFilename = db;

isEncrypted = false;

isReadOnly = false;

updateSchema();

return true;

} else {

return false;

}

}

bool DBBrowserDB::close()

{

if(_db)

{

if (getDirty())

{

QMessageBox::StandardButton reply = QMessageBox::question(nullptr,

QApplication::applicationName(),

tr("Do you want to save the changes "

"made to the database file %1?").arg(curDBFilename),

QMessageBox::Save | QMessageBox::No | QMessageBox::Cancel);

// If the user clicked the cancel button stop here and return false

if(reply == QMessageBox::Cancel)

return false;

// If he didn't it was either yes or no

if(reply == QMessageBox::Save)

releaseAllSavepoints();

else

revertAll(); //not really necessary, I think... but will not hurt.

}

sqlite3_close(_db);

}

_db = nullptr;

schemata.clear();

savepointList.clear();

emit dbChanged(getDirty());

emit structureUpdated();

// Return true to tell the calling function that the closing wasn't cancelled by the user

return true;

}

bool DBBrowserDB::dump(const QString& filename,

const QStringList & tablesToDump,

bool insertColNames,

bool insertNewSyntx,

bool exportSchema,

bool exportData,

bool keepOldSchema)

{

// Open file

QFile file(filename);

if(file.open(QIODevice::WriteOnly))

{

QApplication::setOverrideCursor(Qt::WaitCursor);

size_t numRecordsTotal = 0, numRecordsCurrent = 0;

objectMap objMap = schemata["main"]; // We only always export the main database, not the attached databases

QList<sqlb::ObjectPtr> tables = objMap.values("table");

QMutableListIterator<sqlb::ObjectPtr> it(tables);

while(it.hasNext())

{

it.next();

// Remove the sqlite_stat1 table if there is one

if(it.value()->name() == "sqlite_stat1" || it.value()->name() == "sqlite_sequence")

{

it.remove();

} else {

// Otherwise get the number of records in this table

SqliteTableModel tableModel(*this);

tableModel.setTable(sqlb::ObjectIdentifier("main", it.value()->name()));

numRecordsTotal += tableModel.totalRowCount();

}

}

QProgressDialog progress(tr("Exporting database to SQL file..."),

tr("Cancel"), 0, numRecordsTotal);

progress.setWindowModality(Qt::ApplicationModal);

progress.show();

qApp->processEvents();

// Open text stream to the file

QTextStream stream(&file);

// Put the SQL commands in a transaction block

stream << "BEGIN TRANSACTION;\n";

// Loop through all tables first as they are required to generate views, indices etc. later

for(auto it=tables.constBegin();it!=tables.constEnd();++it)

{

if (tablesToDump.indexOf((*it)->name()) == -1)

continue;

// Write the SQL string used to create this table to the output file

if(exportSchema)

{

if(!keepOldSchema)

stream << QString("DROP TABLE IF EXISTS %1;\n").arg(sqlb::escapeIdentifier((*it)->name()));

if((*it)->fullyParsed())

stream << (*it)->sql("main", true) << "\n";

else

stream << (*it)->originalSql() << ";\n";

}

// If the user doesn't want the data to be exported skip the rest of the loop block here

if(!exportData)

continue;

// get columns

QStringList cols((*it).dynamicCast<sqlb::Table>()->fieldNames());

QString sQuery = QString("SELECT * FROM %1;").arg(sqlb::escapeIdentifier((*it)->name()));

QByteArray utf8Query = sQuery.toUtf8();

sqlite3_stmt *stmt;

QString lineSep(QString(")%1\n").arg(insertNewSyntx?',':';'));

int status = sqlite3_prepare_v2(this->_db, utf8Query.data(), utf8Query.size(), &stmt, NULL);

if(SQLITE_OK == status)

{

int columns = sqlite3_column_count(stmt);

size_t counter = 0;

qApp->processEvents();

while(sqlite3_step(stmt) == SQLITE_ROW)

{

if (counter) stream << lineSep;

if (!insertNewSyntx || !counter)

{

stream << "INSERT INTO " << sqlb::escapeIdentifier((*it)->name());

if (insertColNames)

stream << " (" << cols.join(",") << ")";

stream << " VALUES (";

}

else

{

stream << " (";

}

for (int i = 0; i < columns; ++i)

{

int fieldsize = sqlite3_column_bytes(stmt, i);

int fieldtype = sqlite3_column_type(stmt, i);

QByteArray bcontent(

(const char*)sqlite3_column_blob(stmt, i),

fieldsize);

if(bcontent.left(2048).contains('\0')) // binary check

{

stream << QString("X'%1'").arg(QString(bcontent.toHex()));

}

else

{

switch(fieldtype)

{

case SQLITE_TEXT:

case SQLITE_BLOB:

stream << "'" << bcontent.replace("'", "''") << "'";

break;

case SQLITE_NULL:

stream << "NULL";

break;

case SQLITE_FLOAT:

if(bcontent.indexOf("Inf") != -1)

stream << "'" << bcontent << "'";

else

stream << bcontent;

break;

default:

stream << bcontent;

}

}

if(i != columns - 1)

stream << ',';

}

progress.setValue(++numRecordsCurrent);

if(counter % 5000 == 0)

qApp->processEvents();

counter++;

if(progress.wasCanceled())

{

sqlite3_finalize(stmt);

file.close();

file.remove();

QApplication::restoreOverrideCursor();

return false;

}

}

if (counter > 0) stream << ");\n";

}

sqlite3_finalize(stmt);

}

// Now dump all the other objects (but only if we are exporting the schema)

if(exportSchema)

{

for(auto it=objMap.constBegin();it!=objMap.constEnd();++it)

{

// Make sure it's not a table again

if(it.value()->type() == sqlb::Object::Types::Table)

continue;

// If this object is based on a table (e.g. is an index for that table) it depends on the existence of this table.

// So if we didn't export the base table this depends on, don't export this object either.

if(!(*it)->baseTable().isEmpty() && !tablesToDump.contains((*it)->baseTable()))

continue;

// Write the SQL string used to create this object to the output file

if(!(*it)->originalSql().isEmpty())

{

if(!keepOldSchema)

stream << QString("DROP %1 IF EXISTS %2;\n")

.arg(sqlb::Object::typeToString((*it)->type()).toUpper())

.arg(sqlb::escapeIdentifier((*it)->name()));

if((*it)->fullyParsed())

stream << (*it)->sql("main", true) << "\n";

else

stream << (*it)->originalSql() << ";\n";

}

}

}

// Done

stream << "COMMIT;\n";

file.close();

QApplication::restoreOverrideCursor();

qApp->processEvents();

return true;

}

return false;

}

bool DBBrowserDB::executeSQL(QString statement, bool dirtyDB, bool logsql)

{

if (!isOpen())

{

lastErrorMessage = tr("No database file opened");

return false;

}

statement = statement.trimmed();

if (logsql) logSQL(statement, kLogMsg_App);

if (dirtyDB) setSavepoint();

char* errmsg;

if (SQLITE_OK == sqlite3_exec(_db, statement.toUtf8(), NULL, NULL, &errmsg))

{

// Update DB structure after executing an SQL statement. But try to avoid doing unnecessary updates.

if(!dontCheckForStructureUpdates && (statement.startsWith("ALTER", Qt::CaseInsensitive) ||

statement.startsWith("CREATE", Qt::CaseInsensitive) ||

statement.startsWith("DROP", Qt::CaseInsensitive) ||

statement.startsWith("ROLLBACK", Qt::CaseInsensitive)))

updateSchema();

return true;

} else {

lastErrorMessage = QString("%1 (%2)").arg(QString::fromUtf8(errmsg)).arg(statement);

qWarning() << "executeSQL: " << statement << "->" << errmsg;

sqlite3_free(errmsg);

return false;

}

}

bool DBBrowserDB::executeMultiSQL(const QString& statement, bool dirty, bool log)

{

// First check if a DB is opened

if(!isOpen())

{

lastErrorMessage = tr("No database file opened");

return false;

}

QString query = statement.trimmed();

// Check if this SQL containts any transaction statements

QRegExp transactionRegex("^\\s*BEGIN TRANSACTION;|COMMIT;\\s*$");

if(query.contains(transactionRegex))

{

// If so remove them anc create a savepoint instead by overriding the dirty parameter

query.remove(transactionRegex);

dirty = true;

}

// Log the statement if needed

if(log)

logSQL(query, kLogMsg_App);

// Set DB to dirty/create restore point if necessary

QString savepoint_name;

if(dirty)

{

savepoint_name = generateSavepointName("execmultisql");

setSavepoint(savepoint_name);

}

// Show progress dialog

int statement_size = query.size();

QProgressDialog progress(tr("Executing SQL..."),

tr("Cancel"), 0, statement_size);

progress.setWindowModality(Qt::ApplicationModal);

progress.show();

// Execute the statement by looping until SQLite stops giving back a tail string

sqlite3_stmt* vm;

QByteArray utf8Query = query.toUtf8();

const char *tail = utf8Query.data();

int res = SQLITE_OK;

unsigned int line = 0;

bool structure_updated = false;

while(tail && *tail != 0 && (res == SQLITE_OK || res == SQLITE_DONE))

{

line++;

size_t tail_length = strlen(tail);

// Update progress dialog, keep UI responsive

progress.setValue(statement_size - tail_length);

qApp->processEvents();

if(progress.wasCanceled())

{

lastErrorMessage = tr("Action cancelled.");

return false;

}

// Check whether the DB structure is changed by this statement

QString qtail = QString(tail);

if(!dontCheckForStructureUpdates && !structure_updated && (qtail.startsWith("ALTER", Qt::CaseInsensitive) ||

qtail.startsWith("CREATE", Qt::CaseInsensitive) ||

qtail.startsWith("DROP", Qt::CaseInsensitive) ||

qtail.startsWith("ROLLBACK", Qt::CaseInsensitive)))

structure_updated = true;

// Execute next statement

res = sqlite3_prepare_v2(_db, tail, tail_length, &vm, &tail);

if(res == SQLITE_OK)

{

switch(sqlite3_step(vm))

{

case SQLITE_OK:

case SQLITE_ROW:

case SQLITE_DONE:

case SQLITE_MISUSE: // This is a workaround around problematic user scripts. If they lead to empty commands,

// SQLite will return a misuse error which we hereby ignore.

sqlite3_finalize(vm);

break;

default:

// In case of *any* error abort the execution and roll back the transaction

sqlite3_finalize(vm);

if(dirty)

revertToSavepoint(savepoint_name);

lastErrorMessage = tr("Error in statement #%1: %2.\nAborting execution%3.")

.arg(line)

.arg(sqlite3_errmsg(_db))

.arg(dirty ? tr(" and rolling back") : "");

qWarning() << lastErrorMessage;

return false;

}

} else {

if(dirty)

revertToSavepoint(savepoint_name);

lastErrorMessage = tr("Error in statement #%1: %2.\nAborting execution%3.")

.arg(line)

.arg(sqlite3_errmsg(_db))

.arg(dirty ? tr(" and rolling back") : "");

qWarning() << lastErrorMessage;

return false;

}

}

// If the DB structure was changed by some command in this SQL script, update our schema representations

if(structure_updated)

updateSchema();

// Exit

return true;

}

bool DBBrowserDB::getRow(const sqlb::ObjectIdentifier& table, const QString& rowid, QVector<QByteArray>& rowdata)

{

QString sQuery = QString("SELECT * FROM %1 WHERE %2='%3';")

.arg(table.toString())

.arg(sqlb::escapeIdentifier(getObjectByName(table).dynamicCast<sqlb::Table>()->rowidColumn()))

.arg(rowid);

QByteArray utf8Query = sQuery.toUtf8();

sqlite3_stmt *stmt;

bool ret = false;

if(sqlite3_prepare_v2(_db, utf8Query, utf8Query.size(), &stmt, NULL) == SQLITE_OK)

{

// even this is a while loop, the statement should always only return 1 row

while(sqlite3_step(stmt) == SQLITE_ROW)

{

for (int i = 0; i < sqlite3_column_count(stmt); ++i)

{

if(sqlite3_column_type(stmt, i) == SQLITE_NULL)

{

rowdata.append(QByteArray());

} else {

int bytes = sqlite3_column_bytes(stmt, i);

if(bytes)

rowdata.append(QByteArray(static_cast<const char*>(sqlite3_column_blob(stmt, i)), bytes));

else

rowdata.append(QByteArray(""));

}

}

ret = true;

}

}

sqlite3_finalize(stmt);

return ret;

}

QString DBBrowserDB::max(const sqlb::ObjectIdentifier& tableName, sqlb::FieldPtr field) const

{

QString sQuery = QString("SELECT MAX(CAST(%2 AS INTEGER)) FROM %1;").arg(tableName.toString()).arg(sqlb::escapeIdentifier(field->name()));

QByteArray utf8Query = sQuery.toUtf8();

sqlite3_stmt *stmt;

QString ret = "0";

if(sqlite3_prepare_v2(_db, utf8Query, utf8Query.size(), &stmt, NULL) == SQLITE_OK)

{

// even this is a while loop, the statement should always only return 1 row

while(sqlite3_step(stmt) == SQLITE_ROW)

{

if(sqlite3_column_count(stmt) == 1)

ret = QString::fromUtf8(reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0)));

}

}

sqlite3_finalize(stmt);

return ret;

}

QString DBBrowserDB::emptyInsertStmt(const QString& schemaName, const sqlb::Table& t, const QString& pk_value) const

{

QString stmt = QString("INSERT INTO %1.%2").arg(sqlb::escapeIdentifier(schemaName)).arg(sqlb::escapeIdentifier(t.name()));

QStringList vals;

QStringList fields;

for(const sqlb::FieldPtr& f : t.fields())

{

sqlb::ConstraintPtr pk = t.constraint({f}, sqlb::Constraint::PrimaryKeyConstraintType);

if(pk)

{

fields << f->name();

if(!pk_value.isNull())

{

vals << pk_value;

} else {

if(f->notnull())

{

QString maxval = this->max(sqlb::ObjectIdentifier(schemaName, t.name()), f);

vals << QString::number(maxval.toLongLong() + 1);

} else {

vals << "NULL";

}

}

} else if(f->notnull() && f->defaultValue().length() == 0) {

fields << f->name();

if(f->isInteger())

vals << "0";

else

vals << "''";

} else {

// don't insert into fields with a default value

// or we will never see it.

if(f->defaultValue().length() == 0)

{

fields << f->name();

vals << "NULL";

}

}

}

if(fields.empty())

{

stmt.append(" DEFAULT VALUES;");

} else {

stmt.append("(");

for(const QString& f : fields)

stmt.append(sqlb::escapeIdentifier(f) + ",");

stmt.chop(1);

stmt.append(") VALUES (");

stmt.append(vals.join(","));

stmt.append(");");

}

return stmt;

}

QString DBBrowserDB::addRecord(const sqlb::ObjectIdentifier& tablename)

{

if (!isOpen()) return QString();

sqlb::TablePtr table = getObjectByName(tablename).dynamicCast<sqlb::Table>();

if(!table)

return QString();

// 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

// and adding one to it.

QString sInsertstmt;

QString pk_value;

if(table->isWithoutRowidTable())

{

pk_value = QString::number(max(tablename, table->fields().at(table->findField(table->rowidColumn()))).toLongLong() + 1);

sInsertstmt = emptyInsertStmt(tablename.schema(), *table, pk_value);

} else {

sInsertstmt = emptyInsertStmt(tablename.schema(), *table);

}

if(!executeSQL(sInsertstmt))

{

qWarning() << "addRecord: " << lastErrorMessage;

return QString();

} else {

if(table->isWithoutRowidTable())

return pk_value;

else

return QString::number(sqlite3_last_insert_rowid(_db));

}

}

bool DBBrowserDB::deleteRecords(const sqlb::ObjectIdentifier& table, const QStringList& rowids)

{

if (!isOpen()) return false;

QStringList quoted_rowids;

for(QString rowid : rowids)

quoted_rowids.append("'" + rowid.replace("'", "''") + "'");

QString statement = QString("DELETE FROM %1 WHERE %2 IN (%3);")

.arg(table.toString())

.arg(sqlb::escapeIdentifier(getObjectByName(table).dynamicCast<sqlb::Table>()->rowidColumn()))

.arg(quoted_rowids.join(", "));

if(executeSQL(statement))

{

return true;

} else {

qWarning() << "deleteRecord: " << lastErrorMessage;

return false;

}

}

bool DBBrowserDB::updateRecord(const sqlb::ObjectIdentifier& table, const QString& column, const QString& rowid, const QByteArray& value, bool itsBlob, const QString& pseudo_pk)

{