bool DBBrowserDB::updateRecord(const QString& table, const QString& column, int row, const QByteArray& value)
{
if (!isOpen()) return false;
lastErrorMessage = QString("no error");
QString sql = QString("UPDATE `%1` SET `%2`=? WHERE rowid=%3;").arg(table).arg(column).arg(row);
logSQL(sql, kLogMsg_App);
setRestorePoint();
sqlite3_stmt* stmt;
int success = 1;
if(sqlite3_prepare_v2(_db, sql.toUtf8(), -1, &stmt, 0) != SQLITE_OK)
success = 0;
if(success == 1 && sqlite3_bind_text(stmt, 1, value.constData(), value.length(), SQLITE_STATIC) != SQLITE_OK)
success = -1;
if(success == 1 && sqlite3_step(stmt) != SQLITE_DONE)
success = -1;
if(success != 0 && sqlite3_finalize(stmt) != SQLITE_OK)
success = -1;
if(success == 1)
{
return true;
} else {
lastErrorMessage = sqlite3_errmsg(_db);
qWarning() << "updateRecord: " << lastErrorMessage;
return false;
}
}
void DBBrowserDB::setDirty(bool dirtyval)
{
if ((dirty==false)&&(dirtyval==true))
{
setRestorePoint();
}
dirty = dirtyval;
if (logWin)
{
logWin->msgDBDirtyState(dirty);
}
}
bool DBBrowserDB::deleteRecord(const QString& table, int rowid)
{
char * errmsg;
if (!isOpen()) return false;
bool ok = false;
lastErrorMessage = QString("no error");
QString statement = QString("DELETE FROM `%1` WHERE rowid=%2;").arg(table).arg(rowid);
if (_db){
logSQL(statement, kLogMsg_App);
setRestorePoint();
if (SQLITE_OK==sqlite3_exec(_db,statement.toUtf8(),
NULL,NULL,&errmsg)){
ok=true;
} else {
lastErrorMessage = QString::fromUtf8(errmsg);
qWarning() << "deleteRecord: " << lastErrorMessage;
}
}
return ok;
}
int DBBrowserDB::addRecord(const QString& sTableName)
{
char *errmsg;
if (!isOpen()) return false;
// add record is seldom called, for now this is ok
// but if we ever going to add a lot of records
// we should cache the parsed tables somewhere
sqlb::Table table = sqlb::Table::parseSQL(getObjectByName(sTableName).getsql()).first;
QString sInsertstmt = table.emptyInsertStmt();
lastErrorMessage = "";
logSQL(sInsertstmt, kLogMsg_App);
setRestorePoint();
if (SQLITE_OK != sqlite3_exec(_db, sInsertstmt.toUtf8(), NULL, NULL, &errmsg))
{
lastErrorMessage = QString::fromUtf8(errmsg);
qWarning() << "addRecord: " << lastErrorMessage;
return -1;
} else {
return sqlite3_last_insert_rowid(_db);
}
}
bool DBBrowserDB::executeSQL ( const QString & statement, bool dirtyDB, bool logsql)
{
char *errmsg;
bool ok = false;
if (!isOpen()) return false;
if (_db){
if (logsql) logSQL(statement, kLogMsg_App);
if (dirtyDB) setRestorePoint();
if (SQLITE_OK==sqlite3_exec(_db,statement.toUtf8(),
NULL,NULL,&errmsg)){
ok=true;
}
}
if (!ok){
lastErrorMessage = QString::fromUtf8(errmsg);
qWarning() << "executeSQL: " << statement << "->" << lastErrorMessage;
return false;
}
return true;
}
bool DBBrowserDB::executeMultiSQL(const QString& statement, bool dirty, bool log)
{
// First check if a DB is opened
if(!isOpen())
return false;
// Log the statement if needed
if(log)
logSQL(statement, kLogMsg_App);
// Set DB to dirty/create restore point if necessary
if(dirty)
setRestorePoint();
// Show progress dialog
int statement_size = statement.size();
QProgressDialog progress(QObject::tr("Executing SQL..."),
QObject::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 = statement.toUtf8();
const char *tail = utf8Query.data();
int res = 0;
unsigned int line = 0;
do
{
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 = QObject::tr("Action cancelled.");
return false;
}
// Execute next statement
res = sqlite3_prepare_v2(_db, tail, tail_length, &vm, &tail);
if(res == SQLITE_OK)
{
if(sqlite3_step(vm) == SQLITE_ERROR)
{
sqlite3_finalize(vm);
lastErrorMessage = QObject::tr("Error in statement #%1: %2.\n"
"Aborting execution.").arg(line).arg(sqlite3_errmsg(_db));
qWarning() << lastErrorMessage;
return false;
} else {
sqlite3_finalize(vm);
}
} else {
lastErrorMessage = QObject::tr("Error in statement #%1: %2.\n"
"Aborting execution.").arg(line).arg(sqlite3_errmsg(_db));
qWarning() << lastErrorMessage;
return false;
}
} while(tail && *tail != 0 && (res == SQLITE_OK || res == SQLITE_DONE));
// Exit
return true;
}
