Fixes to SqliteStorage:

- removed all transactions in favor of speed
- fixed some queries

diff --git a/core/server.cpp b/core/server.cpp
index f7d7916..74f9c7a 100644 (file)
--- a/core/server.cpp
+++ b/core/server.cpp
@@ -167,7 +167,7 @@ void Server::handleServerMsg(QString msg) {
       return;
     }
     // OK, first we split the raw message into its various parts...
       return;
     }
     // OK, first we split the raw message into its various parts...

-    QString prefix;
+    QString prefix = "";

     QString cmd;
     QStringList params;
 
     QString cmd;
     QStringList params;
 


@@ -738,7 +738,7 @@ void Server::handleServer001(QString prefix, QStringList params) {
 /* RPL_ISUPPORT */
 // TODO Complete 005 handling, also use sensible defaults for non-sent stuff
 void Server::handleServer005(QString prefix, QStringList params) {
 /* RPL_ISUPPORT */
 // TODO Complete 005 handling, also use sensible defaults for non-sent stuff
 void Server::handleServer005(QString prefix, QStringList params) {

-  qDebug() << prefix << params;
+  //qDebug() << prefix << params;

   params.removeLast();
   foreach(QString p, params) {
     QString key = p.section("=", 0, 0);
   params.removeLast();
   foreach(QString p, params) {
     QString key = p.section("=", 0, 0);

diff --git a/core/sqlitestorage.cpp b/core/sqlitestorage.cpp
index 9b6f88d..b2804dd 100644 (file)
--- a/core/sqlitestorage.cpp
+++ b/core/sqlitestorage.cpp
@@ -35,13 +35,6 @@ SqliteStorage::SqliteStorage() {
     return;
   }
 
     return;
   }
 

-  /*
-  NO, we should not start a transaction to check if transactions are supported :-)
-  if(!logDb.transaction()) {
-    qWarning(tr("Database driver does not support transactions. This might lead to a corrupt database!").toAscii());
-  }
-  */
-

   // check if the db schema is up to date
   QSqlQuery query = logDb.exec("SELECT MAX(version) FROM coreinfo");
   if(query.first()) {
   // check if the db schema is up to date
   QSqlQuery query = logDb.exec("SELECT MAX(version) FROM coreinfo");
   if(query.first()) {


@@ -62,9 +55,7 @@ SqliteStorage::SqliteStorage() {
   getBufferIdQuery = new QSqlQuery(logDb);
   getBufferIdQuery->prepare("SELECT bufferid FROM buffer "
                             "JOIN network ON buffer.networkid = network.networkid "
   getBufferIdQuery = new QSqlQuery(logDb);
   getBufferIdQuery->prepare("SELECT bufferid FROM buffer "
                             "JOIN network ON buffer.networkid = network.networkid "

-                            "WHERE network.networkname = :networkname AND buffer.userid = :userid AND buffer.buffername = :buffername "
-                            "LIMIT 1");
-
+                            "WHERE network.networkname = :networkname AND buffer.userid = :userid AND buffer.buffername = :buffername ");

 
   logMessageQuery = new QSqlQuery(logDb);
   logMessageQuery->prepare("INSERT INTO backlog (time, bufferid, type, flags, senderid, message) "
 
   logMessageQuery = new QSqlQuery(logDb);
   logMessageQuery->prepare("INSERT INTO backlog (time, bufferid, type, flags, senderid, message) "


@@ -129,12 +120,10 @@ SqliteStorage::~SqliteStorage() {
   delete createBufferQuery;
   delete getBufferIdQuery;
   logDb.close();
   delete createBufferQuery;
   delete getBufferIdQuery;
   logDb.close();

-  //qDebug() << logDb.lastError().text();

 }
 
 
 void SqliteStorage::initDb() {
 }
 
 
 void SqliteStorage::initDb() {

-  //logDb.transaction();

   logDb.exec("CREATE TABLE quasseluser ("
              "userid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
              "username TEXT UNIQUE NOT NULL,"
   logDb.exec("CREATE TABLE quasseluser ("
              "userid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
              "username TEXT UNIQUE NOT NULL,"


@@ -182,13 +171,11 @@ void SqliteStorage::initDb() {
 
 
   // something fucked up -> no logging possible
 
 
   // something fucked up -> no logging possible

+  // FIXME logDb.lastError is reset whenever exec is called

   if(logDb.lastError().isValid()) { 
     qWarning(tr("Could not create backlog table: %1").arg(logDb.lastError().text()).toAscii());
     qWarning(tr("Disabling logging...").toAscii());
   if(logDb.lastError().isValid()) { 
     qWarning(tr("Could not create backlog table: %1").arg(logDb.lastError().text()).toAscii());
     qWarning(tr("Disabling logging...").toAscii());

-    logDb.rollback();

     Q_ASSERT(false); // quassel does require logging
     Q_ASSERT(false); // quassel does require logging

-  } else {
-    logDb.commit();

   }
 }
 
   }
 }
 


@@ -206,17 +193,14 @@ UserId SqliteStorage::addUser(QString user, QString password) {
   QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
   cryptopass = cryptopass.toHex();
 
   QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
   cryptopass = cryptopass.toHex();
 

-  //logDb.transaction();

   QSqlQuery query(logDb);
   query.prepare("INSERT INTO quasseluser (username, password) VALUES (:username, :password)");
   query.bindValue(":username", user);
   query.bindValue(":password", cryptopass);
   query.exec();
   if(query.lastError().isValid() && query.lastError().number() == 19) { // user already exists - sadly 19 seems to be the general constraint violation error...
   QSqlQuery query(logDb);
   query.prepare("INSERT INTO quasseluser (username, password) VALUES (:username, :password)");
   query.bindValue(":username", user);
   query.bindValue(":password", cryptopass);
   query.exec();
   if(query.lastError().isValid() && query.lastError().number() == 19) { // user already exists - sadly 19 seems to be the general constraint violation error...

-    logDb.rollback();

     return 0;
   } 
     return 0;
   } 

-  logDb.commit();

 
   query.prepare("SELECT userid FROM quasseluser WHERE username = :username");
   query.bindValue(":username", user);
 
   query.prepare("SELECT userid FROM quasseluser WHERE username = :username");
   query.bindValue(":username", user);


@@ -229,13 +213,11 @@ void SqliteStorage::updateUser(UserId user, QString password) {
   QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
   cryptopass = cryptopass.toHex();
 
   QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
   cryptopass = cryptopass.toHex();
 

-  //logDb.transaction();

   QSqlQuery query(logDb);
   query.prepare("UPDATE quasseluser SET password = :password WHERE userid = :userid");
   query.bindValue(":userid", user);
   query.bindValue(":password", cryptopass);
   query.exec();
   QSqlQuery query(logDb);
   query.prepare("UPDATE quasseluser SET password = :password WHERE userid = :userid");
   query.bindValue(":userid", user);
   query.bindValue(":password", cryptopass);
   query.exec();

-  logDb.commit();

 }
 
 UserId SqliteStorage::validateUser(QString user, QString password) {
 }
 
 UserId SqliteStorage::validateUser(QString user, QString password) {


@@ -256,10 +238,8 @@ UserId SqliteStorage::validateUser(QString user, QString password) {
 }
 
 void SqliteStorage::delUser(UserId user) {
 }
 
 void SqliteStorage::delUser(UserId user) {

-  //logDb.transaction();

   QSqlQuery query(logDb);
   QSqlQuery query(logDb);

-  // FIXME: backlog has no userid, it's in bufferid
-  query.prepare("DELETE FROM backlog WHERE userid = :userid");
+  query.prepare("DELETE FROM backlog WHERE bufferid IN (SELECT DISTINCT bufferid FROM buffer WHERE userid = :userid");

   query.bindValue(":userid", user);
   query.exec();
   query.prepare("DELETE FROM buffer WHERE userid = :userid");
   query.bindValue(":userid", user);
   query.exec();
   query.prepare("DELETE FROM buffer WHERE userid = :userid");


@@ -274,26 +254,22 @@ void SqliteStorage::delUser(UserId user) {
   query.prepare("DELETE FROM quasseluser WHERE userid = :userid");
   query.bindValue(":userid", user);
   query.exec();
   query.prepare("DELETE FROM quasseluser WHERE userid = :userid");
   query.bindValue(":userid", user);
   query.exec();

-  logDb.commit();

   // I hate the lack of foreign keys and on delete cascade... :(
 }
 
 void SqliteStorage::createBuffer(UserId user, QString network, QString buffer) {
   // I hate the lack of foreign keys and on delete cascade... :(
 }
 
 void SqliteStorage::createBuffer(UserId user, QString network, QString buffer) {

-  //qDebug() << "creating buffer:" << user << network << buffer;
-  //logDb.transaction();

   createBufferQuery->bindValue(":userid", user);
   createBufferQuery->bindValue(":networkname", network);
   createBufferQuery->bindValue(":buffername", buffer);
   createBufferQuery->exec();
 
   if(createBufferQuery->lastError().isValid()) {
   createBufferQuery->bindValue(":userid", user);
   createBufferQuery->bindValue(":networkname", network);
   createBufferQuery->bindValue(":buffername", buffer);
   createBufferQuery->exec();
 
   if(createBufferQuery->lastError().isValid()) {

-    if(createBufferQuery->lastError().number() == 19) { // Null Constraint violation
+    if(createBufferQuery->lastError().number() == 19) { // Null Constraint violation 
+      qDebug() << createBufferQuery->lastError().text();

       createNetworkQuery->bindValue(":userid", user);
       createNetworkQuery->bindValue(":networkname", network);
       createNetworkQuery->exec();
       createBufferQuery->exec();
       createNetworkQuery->bindValue(":userid", user);
       createNetworkQuery->bindValue(":networkname", network);
       createNetworkQuery->exec();
       createBufferQuery->exec();

-      //qDebug() << "net" << createNetworkQuery->lastError().text();
-      //qDebug() << "buf" << createBufferQuery->lastError().text();

       Q_ASSERT(!createNetworkQuery->lastError().isValid());
       Q_ASSERT(!createBufferQuery->lastError().isValid());
     } else {
       Q_ASSERT(!createNetworkQuery->lastError().isValid());
       Q_ASSERT(!createBufferQuery->lastError().isValid());
     } else {


@@ -302,66 +278,54 @@ void SqliteStorage::createBuffer(UserId user, QString network, QString buffer) {
       Q_ASSERT(false);
     }
   }
       Q_ASSERT(false);
     }
   }

-  logDb.commit();

 }
 
 BufferId SqliteStorage::getBufferId(UserId user, QString network, QString buffer) {
 }
 
 BufferId SqliteStorage::getBufferId(UserId user, QString network, QString buffer) {

-  if(buffer == "") buffer = "$$$";  // FIXME
-
-  QSqlQuery *getBufferIdQuery = new QSqlQuery(logDb);
-  getBufferIdQuery->prepare("SELECT bufferid FROM buffer "
-      "JOIN network ON buffer.networkid = network.networkid "
-      "WHERE network.networkname = :networkname AND buffer.userid = :userid AND buffer.buffername = :buffername "
-      "LIMIT 1");
-
+  BufferId bufferid;

   getBufferIdQuery->bindValue(":networkname", network);
   getBufferIdQuery->bindValue(":userid", user);
   getBufferIdQuery->bindValue(":buffername", buffer);
   getBufferIdQuery->exec();
 
   getBufferIdQuery->bindValue(":networkname", network);
   getBufferIdQuery->bindValue(":userid", user);
   getBufferIdQuery->bindValue(":buffername", buffer);
   getBufferIdQuery->exec();
 

-  bool flg = false;

   if(!getBufferIdQuery->first()) {
     createBuffer(user, network, buffer);
     getBufferIdQuery->exec();
   if(!getBufferIdQuery->first()) {
     createBuffer(user, network, buffer);
     getBufferIdQuery->exec();

-    flg = getBufferIdQuery->first();
-    Q_ASSERT(flg);
+    if(getBufferIdQuery->first()) {
+      bufferid = BufferId(getBufferIdQuery->value(0).toUInt(), network, buffer);
+      emit bufferIdUpdated(bufferid);
+    }
+  } else {
+    bufferid = BufferId(getBufferIdQuery->value(0).toUInt(), network, buffer);

   }
   }

-  if(buffer == "$$$") buffer = "";
-  BufferId result = BufferId(getBufferIdQuery->value(0).toUInt(), network, buffer);
-  //getBufferIdQuery->clear(); // this is active for some reason, which wrecks havoc with later transactions
-  getBufferIdQuery->last();
-  //qDebug() << "active" << getBufferIdQuery->isActive();
-  if(flg) emit bufferIdUpdated(result);
-  delete getBufferIdQuery;
-  return result;
+
+  Q_ASSERT(!getBufferIdQuery->next());
+
+  return bufferid;

 }
 
 QList<BufferId> SqliteStorage::requestBuffers(UserId user, QDateTime since) {
   QList<BufferId> bufferlist;
   QSqlQuery query(logDb);
 }
 
 QList<BufferId> SqliteStorage::requestBuffers(UserId user, QDateTime since) {
   QList<BufferId> bufferlist;
   QSqlQuery query(logDb);

-  // FIXME: fix query (and make it run in sane time)
-  query.prepare("SELECT buffer.bufferid, networkname, buffername FROM buffer "
-                //"JOIN buffer ON buffer.bufferid = backlog.bufferid "
+  query.prepare("SELECT DISTINCT buffer.bufferid, networkname, buffername FROM buffer "

                 "JOIN network ON buffer.networkid = network.networkid "
                 "JOIN network ON buffer.networkid = network.networkid "

-                //"JOIN backlog ON buffer.bufferid = backlog.bufferid "
-                "WHERE buffer.userid = 1");// AND time >= 0");
-  //query.bindValue(":userid", user);
-  //if(since.isValid()) query.bindValue(":time", since.toTime_t());
-  //else query.bindValue(":time",0);
-  //qDebug() << query.boundValues();
+                "JOIN backlog ON buffer.bufferid = backlog.bufferid "
+                "WHERE buffer.userid = :userid AND time >= :time");
+  query.bindValue(":userid", user);
+  if (since.isValid()) {
+    query.bindValue(":time", since.toTime_t());
+  } else {
+    query.bindValue(":time", 0);
+  }
+  

   query.exec();
   query.exec();

-  //qDebug() << query.lastError().text();
+

   while(query.next()) {
   while(query.next()) {

-    QString buf = query.value(2).toString();
-    if(buf == "$$$") buf = "";
-    bufferlist << BufferId(query.value(0).toUInt(), query.value(1).toString(), buf);
+    bufferlist << BufferId(query.value(0).toUInt(), query.value(1).toString(), query.value(2).toString());

   }
   return bufferlist;
 }
 
 MsgId SqliteStorage::logMessage(Message msg) {
   }
   return bufferlist;
 }
 
 MsgId SqliteStorage::logMessage(Message msg) {

-  if(msg.sender == "") msg.sender = "$$$";  // FIXME handle empty sender strings in a sane way
-  //logDb.transaction();

   logMessageQuery->bindValue(":time", msg.timeStamp.toTime_t());
   logMessageQuery->bindValue(":bufferid", msg.buffer.uid());
   logMessageQuery->bindValue(":type", msg.type);
   logMessageQuery->bindValue(":time", msg.timeStamp.toTime_t());
   logMessageQuery->bindValue(":bufferid", msg.buffer.uid());
   logMessageQuery->bindValue(":type", msg.type);


@@ -369,19 +333,19 @@ MsgId SqliteStorage::logMessage(Message msg) {
   logMessageQuery->bindValue(":sender", msg.sender);
   logMessageQuery->bindValue(":message", msg.text);
   logMessageQuery->exec();
   logMessageQuery->bindValue(":sender", msg.sender);
   logMessageQuery->bindValue(":message", msg.text);
   logMessageQuery->exec();

-  // constraint violation - must be NOT NULL constraint - probably the sender is missing...
+  

   if(logMessageQuery->lastError().isValid()) {
   if(logMessageQuery->lastError().isValid()) {

+    // constraint violation - must be NOT NULL constraint - probably the sender is missing...

     if(logMessageQuery->lastError().number() == 19) { 
       addSenderQuery->bindValue(":sender", msg.sender);
       addSenderQuery->exec();
       logMessageQuery->exec();
     if(logMessageQuery->lastError().number() == 19) { 
       addSenderQuery->bindValue(":sender", msg.sender);
       addSenderQuery->exec();
       logMessageQuery->exec();

+      Q_ASSERT(!logMessageQuery->lastError().isValid());

     } else {
       qDebug() << "unhandled DB Error in logMessage(): Number:" << logMessageQuery->lastError().number() << "ErrMsg:" << logMessageQuery->lastError().text();
     }
   }
 
     } else {
       qDebug() << "unhandled DB Error in logMessage(): Number:" << logMessageQuery->lastError().number() << "ErrMsg:" << logMessageQuery->lastError().text();
     }
   }
 

-  logDb.commit();
-

   getLastMessageIdQuery->bindValue(":time", msg.timeStamp.toTime_t());
   getLastMessageIdQuery->bindValue(":bufferid", msg.buffer.uid());
   getLastMessageIdQuery->bindValue(":type", msg.type);
   getLastMessageIdQuery->bindValue(":time", msg.timeStamp.toTime_t());
   getLastMessageIdQuery->bindValue(":bufferid", msg.buffer.uid());
   getLastMessageIdQuery->bindValue(":type", msg.type);


@@ -391,7 +355,7 @@ MsgId SqliteStorage::logMessage(Message msg) {
   if(getLastMessageIdQuery->first()) {
     return getLastMessageIdQuery->value(0).toUInt();
   } else { // somethin went wrong... :(
   if(getLastMessageIdQuery->first()) {
     return getLastMessageIdQuery->value(0).toUInt();
   } else { // somethin went wrong... :(

-    qDebug() << getLastMessageIdQuery->lastQuery();
+    qDebug() << getLastMessageIdQuery->lastQuery() << "time/bufferid/type/sender:" << msg.timeStamp.toTime_t() << msg.buffer.uid() << msg.type << msg.sender;

     Q_ASSERT(false);
     return 0;
   }
     Q_ASSERT(false);
     return 0;
   }


@@ -420,7 +384,6 @@ QList<Message> SqliteStorage::requestMsgs(BufferId buffer, int lastmsgs, int off
                 requestMsgsQuery->value(4).toString(),
                 requestMsgsQuery->value(3).toUInt());
     msg.msgId = requestMsgsQuery->value(0).toUInt();
                 requestMsgsQuery->value(4).toString(),
                 requestMsgsQuery->value(3).toUInt());
     msg.msgId = requestMsgsQuery->value(0).toUInt();

-    if(msg.sender == "$$$") msg.sender = "";  // FIXME

     messagelist << msg;
   }
 
     messagelist << msg;
   }
 


@@ -452,11 +415,9 @@ QList<Message> SqliteStorage::requestMsgs(BufferId buffer, QDateTime since, int
                 requestMsgsSinceQuery->value(4).toString(),
                 requestMsgsSinceQuery->value(3).toUInt());
     msg.msgId = requestMsgsSinceQuery->value(0).toUInt();
                 requestMsgsSinceQuery->value(4).toString(),
                 requestMsgsSinceQuery->value(3).toUInt());
     msg.msgId = requestMsgsSinceQuery->value(0).toUInt();

-    if(msg.sender == "$$$") msg.sender = "";  // FIXME

     messagelist << msg;
   }
 
     messagelist << msg;
   }
 

-

   return messagelist;
 }
 
   return messagelist;
 }
 


@@ -476,7 +437,6 @@ QList<Message> SqliteStorage::requestMsgRange(BufferId buffer, int first, int la
                 requestMsgRangeQuery->value(4).toString(),
                 requestMsgRangeQuery->value(3).toUInt());
     msg.msgId = requestMsgRangeQuery->value(0).toUInt();
                 requestMsgRangeQuery->value(4).toString(),
                 requestMsgRangeQuery->value(3).toUInt());
     msg.msgId = requestMsgRangeQuery->value(0).toUInt();

-    if(msg.sender == "$$$") msg.sender = "";  // FIXME

     messagelist << msg;
   }
 
     messagelist << msg;
   }
 


@@ -493,8 +453,7 @@ void SqliteStorage::importOldBacklog() {
   } else {
     user = query.value(0).toUInt();
   }
   } else {
     user = query.value(0).toUInt();
   }

-  // FIXME: backlog does not have userid, we have to select bufferids first
-  query.prepare("DELETE FROM backlog WHERE userid = :userid");
+  query.prepare("DELETE FROM backlog WHERE bufferid IN (SELECT DISTINCT bufferid FROM buffer WHERE userid = :userid");

   query.bindValue(":userid", user);
   query.exec();
   query.prepare("DELETE FROM buffer WHERE userid = :userid");
   query.bindValue(":userid", user);
   query.exec();
   query.prepare("DELETE FROM buffer WHERE userid = :userid");