bool DBBrowserDB::deleteRecord( int wrow) { char * errmsg; if (!hasValidBrowseSet) return false; if (!isOpen()) return false; bool ok = false; rowList tab = browseRecs; QStringList& rt = tab[wrow]; QString& rowid = rt[0]; lastErrorMessage = QString("no error"); QString statement = "DELETE FROM "; statement.append(GetEncodedQString(curBrowseTableName)); statement.append(" WHERE rowid="); statement.append(rowid); statement.append(";"); if (_db) { logSQL(statement, kLogMsg_App); setDirty(true); if (SQLITE_OK==sqlite3_exec(_db,GetEncodedQString(statement), NULL,NULL,&errmsg)) { ok=true; } else { lastErrorMessage = QString(errmsg); } } return ok; }
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; } }
bool DBBrowserDB::addRecord ( ) { char *errmsg; if (!hasValidBrowseSet) return false; if (!isOpen()) return false; bool ok = false; int fields = browseFields.count(); QString emptyvalue = curNewData; QString statement = "INSERT INTO "; statement.append(GetEncodedQString(curBrowseTableName)); statement.append(" VALUES("); for ( int i=1; i<=fields; i++ ) { statement.append(emptyvalue); if (i<fields) statement.append(", "); } statement.append(");"); lastErrorMessage = QString("no error"); if (_db) { logSQL(statement, kLogMsg_App); setDirty(true); if (SQLITE_OK==sqlite3_exec(_db,statement,NULL,NULL, &errmsg)) { ok=true; //int newrowid = sqlite3_last_insert_rowid(_db); } else { lastErrorMessage = QString(errmsg); } } return ok; }
QString DBBrowserDB::getPragma(const QString& pragma) { if(!isOpen()) return ""; QString sql = QString("PRAGMA %1").arg(pragma); sqlite3_stmt* vm; const char* tail; QString retval = ""; // Get value from DB int err = sqlite3_prepare_v2(_db, sql.toUtf8(), sql.toUtf8().length(), &vm, &tail); if(err == SQLITE_OK){ logSQL(sql, kLogMsg_App); if(sqlite3_step(vm) == SQLITE_ROW) retval = QString::fromUtf8((const char *) sqlite3_column_text(vm, 0)); else qWarning() << QObject::tr("didn't receive any output from pragma %1").arg(pragma); sqlite3_finalize(vm); } else { qWarning() << QObject::tr("could not execute pragma command: %1, %2").arg(err).arg(sqlite3_errmsg(_db)); } // Return it return retval; }
void DBBrowserDB::getTableRecords( const QString & tablename ) { sqlite3_stmt *vm; const char *tail; int ncol; QStringList r; // char *errmsg; int err=0; // int tabnum = 0; browseRecs.clear(); idmap.clear(); lastErrorMessage = QString("no error"); QString statement = "SELECT rowid, * FROM "; statement.append( GetEncodedQString(tablename) ); statement.append(" ORDER BY rowid; "); //qDebug(statement); logSQL(statement, kLogMsg_App); err=sqlite3_prepare(_db,statement,statement.length(), &vm, &tail); if (err == SQLITE_OK) { int rownum = 0; while ( sqlite3_step(vm) == SQLITE_ROW ) { r.clear(); ncol = sqlite3_data_count(vm); for (int e=0; e<ncol; e++) { char * strresult = 0; QString rv; strresult = (char *) sqlite3_column_text(vm, e); rv = QString(strresult); r << GetDecodedQString(rv); if (e==0) { idmap.insert(rv.toInt(),rownum); rownum++; } } browseRecs.append(r); } sqlite3_finalize(vm); } else { lastErrorMessage = QString ("could not get fields"); } }
bool DBBrowserDB::updateRecord(int wrow, int wcol, const QString & wtext) { char * errmsg; if (!hasValidBrowseSet) return false; if (!isOpen()) return false; bool ok = false; lastErrorMessage = QString("no error"); QStringList& rt = browseRecs[wrow]; QString& rowid = rt[0]; QString& cv = rt[wcol+1];//must account for rowid QString ct = browseFields.at(wcol); QString statement = "UPDATE "; statement.append(GetEncodedQString(curBrowseTableName)); statement.append(" SET "); statement.append(GetEncodedQString(ct)); statement.append("="); QString wenc = GetEncodedQString(wtext); char * formSQL = sqlite3_mprintf("%Q",(const char *) wenc); statement.append(formSQL); if (formSQL) sqlite3_free(formSQL); statement.append(" WHERE rowid="); statement.append(rowid); statement.append(";"); if (_db) { logSQL(statement, kLogMsg_App); setDirty(true); if (SQLITE_OK==sqlite3_exec(_db,statement, NULL,NULL,&errmsg)) { ok=true; /*update local copy*/ cv = wtext; } else { lastErrorMessage = QString(errmsg); } } return ok; }
/** * @brief Quit Event * Diese Funktion wird aufgerufen, wenn ein Client den Channel verlaesst. * Loggt das Event. * @param session ein Pointer von irc_session_t, Session von der ein Event erzeugt wird. * @param event ein konstanter char Pointer, Event Name * @param origin ein konstanter char Pointer, Herkunfts Name (Person die den Raum verlaesst) * @param params const char, Liste der Parameter (0 - Grund des Verlassens) * @param count ein unsigned int, Anzahl der Parameter */ void event_quit (irc_session_t * session, const char * event, const char * origin, const char ** params, unsigned int count) { if(logging==1) { char nickbuf[128]; irc_target_get_nick (origin, nickbuf, sizeof(nickbuf)); char theTime[100]; struct tm * clock; time_t sec; time(&sec); clock = localtime(&sec); strftime(theTime, sizeof(theTime), "%Y-%m-%d %H:%M:%S", clock); char message[1024]; sprintf(message, "INSERT INTO log (name, chan, message, date) VALUES ('%s', 'quit', '%s', '%s');", nickbuf, "", theTime); logSQL(message); } }
resultMap DBBrowserDB::getFindResults( const QString & wstatement) { sqlite3_stmt *vm; const char *tail; int ncol; // char *errmsg; int err=0; resultMap res; lastErrorMessage = QString("no error"); QString encstatement = GetEncodedQString(wstatement); logSQL(encstatement, kLogMsg_App); err=sqlite3_prepare(_db,encstatement,encstatement.length(), &vm, &tail); if (err == SQLITE_OK) { int rownum = 0; int recnum = 0; QString r; while ( sqlite3_step(vm) == SQLITE_ROW ) { ncol = sqlite3_data_count(vm); for (int e=0; e<ncol; e++) { char * strresult = 0; strresult = (char *) sqlite3_column_text(vm, e); r = QString(strresult); if (e==0) { rownum = r.toInt(); rowIdMap::iterator mit = idmap.find(rownum); recnum = *mit; } } res.insert(recnum, GetDecodedQString(r)); } sqlite3_finalize(vm); } else { lastErrorMessage = QString(sqlite3_errmsg(_db)); } return res; }
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; }
/** * @brief Channel Event * Funktion wird aufgerufen, sobald jemand etwas laut im Channel sagt/eine Nachricht schreibt. * Loggt Event. * Reagiert auf eigenen Namen. * @param session ein Pointer von irc_session_t, Session von der ein Event erzeugt wird. * @param event ein konstanter char Pointer, Event Name * @param origin ein konstanter char Pointer, Herkunfts Name (Person die die Nachricht geschrieben hat) * @param params const char, Liste der Parameter (0 - Channel Name, 1 - Nachricht Text) * @param count ein unsigned int, Anzahl der Parameter */ void event_channel (irc_session_t * session, const char * event, const char * origin, const char ** params, unsigned int count) { if ( count != 2 ) return; printf ("'%s' said in channel %s: %s\n", origin ? origin : "someone", params[0], params[1] ); if ( !origin ) return; if(logging==1) { char nickbuf[128]; irc_target_get_nick (origin, nickbuf, sizeof(nickbuf)); char theTime[100]; struct tm * clock; time_t sec; time(&sec); clock = localtime(&sec); strftime(theTime, sizeof(theTime), "%Y-%m-%d %H:%M:%S", clock); char message[1024]; sprintf(message, "INSERT INTO log (name, chan, message, date) VALUES ('%s', 'said in %s:', '%s', '%s');", nickbuf, params[0], params[1], theTime); logSQL(message); } //Name des Bots in der Nachricht if ( strncmp( params[1], bot_name, (unsigned)strlen(bot_name) ) == 0 ) { irc_cmd_msg (session, params[0], "Whisper me: 'help'"); } }
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::executeSQLDirect ( const QString & statement) { //no transaction support char *errmsg; bool ok=false; if (!isOpen()) return false; if (_db) { logSQL(statement, kLogMsg_App); if (SQLITE_OK==sqlite3_exec(_db,GetEncodedQString(statement), NULL,NULL,&errmsg)) { ok=true; } } if (!ok) { lastErrorMessage = QString(errmsg); return false; } else { return true; } }
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::compact ( ) { char *errmsg; bool ok=false; if (!isOpen()) return false; if (_db) { save(); logSQL(QString("VACUUM;"), kLogMsg_App); if (SQLITE_OK==sqlite3_exec(_db,"VACUUM;", NULL,NULL,&errmsg)) { ok=true; setDirty(false); } } if (!ok) { lastErrorMessage = QString(errmsg); return false; } else { 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; }
void DBBrowserDB::updateSchema( ) { // qDebug ("Getting list of tables"); sqlite3_stmt *vm; const char *tail; QStringList r; int err=0; QString num; int idxnum =0; int tabnum = 0; idxmap.clear(); tbmap.clear(); lastErrorMessage = QString("no error"); QString statement = "SELECT name, sql " "FROM sqlite_master " "WHERE type='table' ;"; err=sqlite3_prepare(_db, (const char *) statement,statement.length(), &vm, &tail); if (err == SQLITE_OK) { logSQL(statement, kLogMsg_App); while ( sqlite3_step(vm) == SQLITE_ROW ) { num.setNum(tabnum); QString val1, val2; val1 = QString((const char *) sqlite3_column_text(vm, 0)); val2 = QString((const char *) sqlite3_column_text(vm, 1)); tbmap[num] = DBBrowserTable(GetDecodedQString(val1), GetDecodedQString(val2)); tabnum++; } sqlite3_finalize(vm); } else { qDebug ("could not get list of tables: %d, %s",err,sqlite3_errmsg(_db)); } //now get the field list for each table in tbmap tableMap::Iterator it; for ( it = tbmap.begin(); it != tbmap.end(); ++it ) { statement = "PRAGMA TABLE_INFO("; statement.append( (const char *) GetEncodedQString(it.data().getname())); statement.append(");"); logSQL(statement, kLogMsg_App); err=sqlite3_prepare(_db,statement,statement.length(), &vm, &tail); if (err == SQLITE_OK) { it.data(). fldmap.clear(); int e = 0; while ( sqlite3_step(vm) == SQLITE_ROW ) { if (sqlite3_column_count(vm)==6) { QString val1, val2; int ispk= 0; val1 = QString((const char *) sqlite3_column_text(vm, 1)); val2 = QString((const char *) sqlite3_column_text(vm, 2)); ispk = sqlite3_column_int(vm, 5); if (ispk==1) { val2.append(QString(" PRIMARY KEY")); } it.data().addField(e,GetDecodedQString(val1),GetDecodedQString(val2)); e++; } } sqlite3_finalize(vm); } else { lastErrorMessage = QString ("could not get types"); } } statement = "SELECT name, sql " "FROM sqlite_master " "WHERE type='index' "; /*"ORDER BY name;"*/ //finally get indices err=sqlite3_prepare(_db,statement,statement.length(), &vm, &tail); logSQL(statement, kLogMsg_App); if (err == SQLITE_OK) { while ( sqlite3_step(vm) == SQLITE_ROW ) { QString val1, val2; val1 = QString((const char *) sqlite3_column_text(vm, 0)); val2 = QString((const char *) sqlite3_column_text(vm, 1)); num.setNum(idxnum); idxmap[num] = DBBrowserIndex(GetDecodedQString(val1),GetDecodedQString(val2)); idxnum ++; } sqlite3_finalize(vm); } else { lastErrorMessage = QString ("could not get list of indices"); } }
/** * @brief Private Message Event * Diese Funktion wird aufgerufen, wenn der Bot eine Private Message erhält. * Loggt das Event. * Überprüft ob die geschickte Nachricht ein Befehl ist, wenn ja wird dieser ausgeführt. * Reagiert auf quit, help, topic <text>, nick <name>, log, logon, logoff, time, lastseen <name>, mode, whois. * @param session ein Pointer von irc_session_t, Session von der ein Event erzeugt wird. * @param event ein konstanter char Pointer, Event Name * @param origin ein konstanter char Pointer, Herkunfts Name (Person die die Private Nachricht geschickt hat) * @param params const char, Liste der Parameter (0 - Eigener Nick, 1 - Message Text) * @param count ein unsigned int, Anzahl der Parameter */ void event_privmsg (irc_session_t * session, const char * event, const char * origin, const char ** params, unsigned int count) { printf ("'%s' said me (%s): %s\n", origin ? origin : "someone", params[0], params[1] ); if ( !origin ) return; char nickbuf[128]; irc_target_get_nick (origin, nickbuf, sizeof(nickbuf)); if(logging==1) { char theTime[100]; struct tm * clock; time_t sec; time(&sec); clock = localtime(&sec); strftime(theTime, sizeof(theTime), "%Y-%m-%d %H:%M:%S", clock); char message[1024]; sprintf(message, "INSERT INTO log (name, chan, message, date) VALUES ('%s', 'said me (%s):', '%s', '%s');", nickbuf, params[0], params[1], theTime); logSQL(message); } if ( !strcmp (params[1], "quit") ) irc_cmd_quit (session, "of course, Master!"); if ( !strcmp (params[1], "help") ) { irc_cmd_msg (session, nickbuf, "quit, help, topic <text>, nick <name>, log, logon, logoff, time, lastseen <name>"); } if ( !strcmp (params[1], "time") ) { char theTime[100]; struct tm * clock; time_t sec; time(&sec); clock = localtime(&sec); strftime(theTime, sizeof(theTime), "%Y-%m-%d %H:%M:%S", clock); irc_cmd_msg (session, nickbuf, theTime); } if ( !strcmp (params[1], "logon") ) { logging = 1; irc_cmd_msg (session, nickbuf, "Logging eingeschaltet"); } if ( !strcmp (params[1], "logoff") ) { logging = 0; irc_cmd_msg (session, nickbuf, "Logging ausgeschaltet"); } if ( !strcmp (params[1], "log") ) { sqlite3_stmt *vm; sqlite3_open(DB_FILE, &dbhandle); sqlite3_prepare(dbhandle, "SELECT * FROM log", -1, &vm, NULL); while (sqlite3_step(vm) != SQLITE_DONE) { char text[1024]; sprintf(text, "(%s) %s %s %s", sqlite3_column_text(vm, 4), sqlite3_column_text(vm, 1),sqlite3_column_text(vm, 2),sqlite3_column_text(vm, 3)); irc_cmd_msg (session, nickbuf, text); } sqlite3_finalize(vm); sqlite3_close(dbhandle); } if ( strstr (params[1], "lastseen ") == params[1] ) { sqlite3_stmt *vm; sqlite3_open(DB_FILE, &dbhandle); char search[50]; char name[50]; sprintf(name, "%s", params[1] + 9); if(name[(strlen(name)-1)] == ' ') { name[(strlen(name)-1)] = '\0'; } sprintf(search, "SELECT * FROM log WHERE name = '%s' ORDER BY id DESC LIMIT 1", name); sqlite3_prepare(dbhandle, search, -1, &vm, NULL); sqlite3_step(vm); char text[1024]; if(sqlite3_column_text(vm, 1)!=NULL) { sprintf(text, "%s last seen %s", sqlite3_column_text(vm, 1), sqlite3_column_text(vm, 4)); } else { sprintf(text, "%s never seen", params[1]+9); } irc_cmd_msg (session, nickbuf, text); sqlite3_finalize(vm); sqlite3_close(dbhandle); } if ( !strcmp (params[1], "topic") ) irc_cmd_topic (session, params[0], 0); else if ( strstr (params[1], "topic ") == params[1] ) irc_cmd_topic (session, params[0], params[1] + 6); if ( strstr (params[1], "mode ") == params[1] ) irc_cmd_channel_mode (session, params[0], params[1] + 5); if ( strstr (params[1], "nick ") == params[1] ) { irc_cmd_nick (session, params[1] + 5); } if ( strstr (params[1], "whois ") == params[1] ) irc_cmd_whois (session, params[1] + 5); }
void DBBrowserDB::updateSchema( ) { sqlite3_stmt *vm; const char *tail; int err=0; lastErrorMessage = QObject::tr("no error"); objMap.clear(); // Exit here is no DB is opened if(!isOpen()) return; QString statement = "SELECT type,name,sql,tbl_name FROM sqlite_master UNION SELECT type,name,sql,tbl_name FROM sqlite_temp_master;"; QByteArray utf8Statement = statement.toUtf8(); err=sqlite3_prepare_v2(_db, utf8Statement, utf8Statement.length(), &vm, &tail); if (err == SQLITE_OK){ logSQL(statement, kLogMsg_App); while ( sqlite3_step(vm) == SQLITE_ROW ){ QString val1 = QString::fromUtf8((const char*)sqlite3_column_text(vm, 0)); QString val2 = QString::fromUtf8((const char*)sqlite3_column_text(vm, 1)); QString val3 = QString::fromUtf8((const char*)sqlite3_column_text(vm, 2)); QString val4 = QString::fromUtf8((const char*)sqlite3_column_text(vm, 3)); val3.replace("\r", ""); if(val1 == "table" || val1 == "index" || val1 == "view" || val1 == "trigger") objMap.insert(val1, DBBrowserObject(val2, val3, val1, val4)); else qWarning() << QObject::tr("unknown object type %1").arg(val1); } sqlite3_finalize(vm); }else{ qWarning() << QObject::tr("could not get list of db objects: %1, %2").arg(err).arg(sqlite3_errmsg(_db)); } //now get the field list for each table objectMap::Iterator it; for ( it = objMap.begin(); it != objMap.end(); ++it ) { // Use our SQL parser to generate the field list for tables. For views we currently have to fall back to the // pragma SQLite offers. if((*it).gettype() == "table") { sqlb::Table t((*it).getname()); (*it).fldmap = t.parseSQL((*it).getsql()).first.fields(); } else if((*it).gettype() == "view") { statement = QString("PRAGMA TABLE_INFO(`%1`);").arg((*it).getname()); logSQL(statement, kLogMsg_App); err=sqlite3_prepare_v2(_db,statement.toUtf8(),statement.length(), &vm, &tail); if (err == SQLITE_OK){ while ( sqlite3_step(vm) == SQLITE_ROW ){ if (sqlite3_column_count(vm)==6) { QString val_name = QString::fromUtf8((const char *)sqlite3_column_text(vm, 1)); QString val_type = QString::fromUtf8((const char *)sqlite3_column_text(vm, 2)); sqlb::FieldPtr f(new sqlb::Field(val_name, val_type)); (*it).addField(f); } } sqlite3_finalize(vm); } else{ lastErrorMessage = QObject::tr("could not get types"); } } } }