X-Git-Url: https://git.quassel-irc.org/?p=quassel.git;a=blobdiff_plain;f=core%2Fsqlitestorage.cpp;h=b45f18585bf7374f1e063616fa43e81758afc438;hp=ba07cbed6b0fff83bc7cae49d878bb43645ced6b;hb=bb0bf17b61958e92529338596574f7a2a4f61d2a;hpb=88dd38b5ab9a2f24ab4c5ca7825c963fee21267c

diff --git a/core/sqlitestorage.cpp b/core/sqlitestorage.cpp
index ba07cbed..b45f1858 100644
--- a/core/sqlitestorage.cpp
+++ b/core/sqlitestorage.cpp
@@ -23,7 +23,7 @@
 
 SqliteStorage::SqliteStorage() {
   // TODO I don't think that this path is failsafe for windows users :) 
-  QString backlogFile = Global::quasselDir + "/quassel-backlog-newstyle.sqlite";
+  QString backlogFile = Global::quasselDir + "/quassel-storage.sqlite";
   logDb = QSqlDatabase::addDatabase("QSQLITE");
   logDb.setDatabaseName(backlogFile);
   bool ok = logDb.open();
@@ -34,82 +34,77 @@ SqliteStorage::SqliteStorage() {
     Q_ASSERT(ok);
     return;
   }
-  
-  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()) {
-    // TOTO VersionCheck
+    // TODO VersionCheck
     //checkVersion(query.value(0));
     qDebug() << "Sqlite is ready. Quassel Schema Version:" << query.value(0).toUInt();
   } else {
     initDb();
   }
-  
+
   // we will need those pretty often... so let's speed things up:
-  createBufferQuery = new QSqlQuery();
+  createBufferQuery = new QSqlQuery(logDb);
   createBufferQuery->prepare("INSERT INTO buffer (userid, networkid, buffername) VALUES (:userid, (SELECT networkid FROM network WHERE networkname = :networkname), :buffername)");
-  
-  createNetworkQuery = new QSqlQuery();
+
+  createNetworkQuery = new QSqlQuery(logDb);
   createNetworkQuery->prepare("INSERT INTO network (userid, networkname) VALUES (:userid, :networkname)");
-  
-  getBufferIdQuery = new 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");
+                            "WHERE network.networkname = :networkname AND buffer.userid = :userid AND buffer.buffername = :buffername ");
 
-  
-  logMessageQuery = new QSqlQuery();
+  logMessageQuery = new QSqlQuery(logDb);
   logMessageQuery->prepare("INSERT INTO backlog (time, bufferid, type, flags, senderid, message) "
                            "VALUES (:time, :bufferid, :type, :flags, (SELECT senderid FROM sender WHERE sender = :sender), :message)");
-  
-  addSenderQuery = new QSqlQuery();
+
+  addSenderQuery = new QSqlQuery(logDb);
   addSenderQuery->prepare("INSERT INTO sender (sender) VALUES (:sender)");
 
-  getLastMessageIdQuery = new QSqlQuery();
+  getLastMessageIdQuery = new QSqlQuery(logDb);
   getLastMessageIdQuery->prepare("SELECT messageid FROM backlog "
                                  "WHERE time = :time AND bufferid = :bufferid AND type = :type AND senderid = (SELECT senderid FROM sender WHERE sender = :sender)");
 
-  requestMsgsOffsetQuery = new QSqlQuery();
+  requestMsgsOffsetQuery = new QSqlQuery(logDb);
   requestMsgsOffsetQuery->prepare("SELECT count(*) FROM backlog WHERE bufferid = :bufferid AND messageid < :messageid");
-  
-  requestMsgsQuery = new QSqlQuery();
+
+  requestMsgsQuery = new QSqlQuery(logDb);
   requestMsgsQuery->prepare("SELECT messageid, time,  type, flags, sender, message, displayname "
                             "FROM backlog "
                             "JOIN buffer ON backlog.bufferid = buffer.bufferid "
                             "JOIN sender ON backlog.senderid = sender.senderid "
                             "LEFT JOIN buffergroup ON buffer.groupid = buffergroup.groupid "
-                            "WHERE buffer.bufferid = :bufferid OR buffer.groupid = (SELECT groupid FROM buffer WHERE bufferid = :bufferid) "
+                            "WHERE buffer.bufferid = :bufferid OR buffer.groupid = (SELECT groupid FROM buffer WHERE bufferid = :bufferid2) "
                             "ORDER BY messageid DESC "
                             "LIMIT :limit OFFSET :offset");
-  
-  requestMsgsSinceOffsetQuery = new QSqlQuery();
+
+  requestMsgsSinceOffsetQuery = new QSqlQuery(logDb);
   requestMsgsSinceOffsetQuery->prepare("SELECT count(*) FROM backlog WHERE bufferid = :bufferid AND time >= :since");
-  
-  requestMsgsSinceQuery = new QSqlQuery();
+
+  requestMsgsSinceQuery = new QSqlQuery(logDb);
   requestMsgsSinceQuery->prepare("SELECT messageid, time,  type, flags, sender, message, displayname "
                                  "FROM backlog "
                                  "JOIN buffer ON backlog.bufferid = buffer.bufferid "
                                  "JOIN sender ON backlog.senderid = sender.senderid "
                                  "LEFT JOIN buffergroup ON buffer.groupid = buffergroup.groupid "
-                                 "WHERE (buffer.bufferid = :bufferid OR buffer.groupid = (SELECT groupid FROM buffer WHERE bufferid = :bufferid)) AND "
+                                 "WHERE (buffer.bufferid = :bufferid OR buffer.groupid = (SELECT groupid FROM buffer WHERE bufferid = :bufferid2)) AND "
                                  "backlog.time >= :since "
                                  "ORDER BY messageid DESC "
                                  "LIMIT -1 OFFSET :offset");
-  
-  requestMsgRangeQuery = new QSqlQuery();
+
+  requestMsgRangeQuery = new QSqlQuery(logDb);
   requestMsgRangeQuery->prepare("SELECT messageid, time,  type, flags, sender, message, displayname "
                                 "FROM backlog "
                                 "JOIN buffer ON backlog.bufferid = buffer.bufferid "
                                 "JOIN sender ON backlog.senderid = sender.senderid "
                                 "LEFT JOIN buffergroup ON buffer.groupid = buffergroup.groupid "
-                                "WHERE (buffer.bufferid = :bufferid OR buffer.groupid = (SELECT groupid FROM buffer WHERE bufferid = :bufferid)) AND "
+                                "WHERE (buffer.bufferid = :bufferid OR buffer.groupid = (SELECT groupid FROM buffer WHERE bufferid = :bufferid2)) AND "
                                 "backlog.messageid >= :firstmsg AND backlog.messageid <= :lastmsg "
                                 "ORDER BY messageid DESC ");
-  
+
 }
 
 SqliteStorage::~SqliteStorage() {
@@ -176,30 +171,28 @@ void SqliteStorage::initDb() {
 
 
   // 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());
-    logDb.rollback();
     Q_ASSERT(false); // quassel does require logging
-  } else {
-    logDb.commit();
   }
 }
 
 bool SqliteStorage::isAvailable() {
-  // oh yes we're available... at least I do hope so :)
+  if(!QSqlDatabase::isDriverAvailable("QSQLITE")) return false;
   return true;
 }
 
 QString SqliteStorage::displayName() {
-  // I think the class name is a got start here
+  // I think the class name is a good start here
   return QString("SqliteStorage");
 }
 
 UserId SqliteStorage::addUser(QString user, QString password) {
   QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
   cryptopass = cryptopass.toHex();
-  
+
   QSqlQuery query(logDb);
   query.prepare("INSERT INTO quasseluser (username, password) VALUES (:username, :password)");
   query.bindValue(":username", user);
@@ -208,8 +201,7 @@ UserId SqliteStorage::addUser(QString user, QString password) {
   if(query.lastError().isValid() && query.lastError().number() == 19) { // user already exists - sadly 19 seems to be the general constraint violation error...
     return 0;
   } 
-  logDb.commit();
-  
+
   query.prepare("SELECT userid FROM quasseluser WHERE username = :username");
   query.bindValue(":username", user);
   query.exec();
@@ -220,19 +212,18 @@ UserId SqliteStorage::addUser(QString user, QString password) {
 void SqliteStorage::updateUser(UserId user, QString password) {
   QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
   cryptopass = cryptopass.toHex();
-  
+
   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) {
   QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
   cryptopass = cryptopass.toHex();
-  
+
   QSqlQuery query(logDb);
   query.prepare("SELECT userid FROM quasseluser WHERE username = :username AND password = :password");
   query.bindValue(":username", user);
@@ -244,12 +235,11 @@ UserId SqliteStorage::validateUser(QString user, QString password) {
   } else {
     return 0;
   }
-  
 }
 
 void SqliteStorage::delUser(UserId user) {
   QSqlQuery query(logDb);
-  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");
@@ -264,19 +254,17 @@ void SqliteStorage::delUser(UserId user) {
   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) {
-  qDebug() << "creating buffer:" << user << network << buffer;
   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 
       createNetworkQuery->bindValue(":userid", user);
       createNetworkQuery->bindValue(":networkname", network);
       createNetworkQuery->exec();
@@ -286,12 +274,13 @@ void SqliteStorage::createBuffer(UserId user, QString network, QString buffer) {
     } else {
       // do panic!
       qDebug() << "failed to create Buffer: ErrNo:" << createBufferQuery->lastError().number() << "ErrMsg:" << createBufferQuery->lastError().text();
+      Q_ASSERT(false);
     }
   }
-  logDb.commit();
 }
 
 BufferId SqliteStorage::getBufferId(UserId user, QString network, QString buffer) {
+  BufferId bufferid;
   getBufferIdQuery->bindValue(":networkname", network);
   getBufferIdQuery->bindValue(":userid", user);
   getBufferIdQuery->bindValue(":buffername", buffer);
@@ -300,22 +289,35 @@ BufferId SqliteStorage::getBufferId(UserId user, QString network, QString buffer
   if(!getBufferIdQuery->first()) {
     createBuffer(user, network, buffer);
     getBufferIdQuery->exec();
-    Q_ASSERT(getBufferIdQuery->first());
+    if(getBufferIdQuery->first()) {
+      bufferid = BufferId(getBufferIdQuery->value(0).toUInt(), network, buffer);
+      emit bufferIdUpdated(bufferid);
+    }
+  } else {
+    bufferid = BufferId(getBufferIdQuery->value(0).toUInt(), network, buffer);
   }
-  
-  return BufferId(getBufferIdQuery->value(0).toUInt(), network, buffer);
+
+  Q_ASSERT(!getBufferIdQuery->next());
+
+  return bufferid;
 }
 
 QList<BufferId> SqliteStorage::requestBuffers(UserId user, QDateTime since) {
   QList<BufferId> bufferlist;
   QSqlQuery query(logDb);
-  query.prepare("SELECT bufferid, networkname, buffername FROM buffer "
+  query.prepare("SELECT DISTINCT buffer.bufferid, networkname, buffername FROM buffer "
                 "JOIN network ON buffer.networkid = network.networkid "
                 "JOIN backlog ON buffer.bufferid = backlog.bufferid "
                 "WHERE buffer.userid = :userid AND time >= :time");
   query.bindValue(":userid", user);
-  query.bindValue(":time", since.toTime_t());
+  if (since.isValid()) {
+    query.bindValue(":time", since.toTime_t());
+  } else {
+    query.bindValue(":time", 0);
+  }
+  
   query.exec();
+
   while(query.next()) {
     bufferlist << BufferId(query.value(0).toUInt(), query.value(1).toString(), query.value(2).toString());
   }
@@ -330,29 +332,30 @@ MsgId SqliteStorage::logMessage(Message msg) {
   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()) {
+    // 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();
+      Q_ASSERT(!logMessageQuery->lastError().isValid());
     } 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(":sender", msg.sender);
   getLastMessageIdQuery->exec();
-  
+
   if(getLastMessageIdQuery->first()) {
     return getLastMessageIdQuery->value(0).toUInt();
   } else { // somethin went wrong... :(
+    qDebug() << getLastMessageIdQuery->lastQuery() << "time/bufferid/type/sender:" << msg.timeStamp.toTime_t() << msg.buffer.uid() << msg.type << msg.sender;
+    Q_ASSERT(false);
     return 0;
   }
 }
@@ -365,13 +368,13 @@ QList<Message> SqliteStorage::requestMsgs(BufferId buffer, int lastmsgs, int off
   requestMsgsOffsetQuery->exec();
   requestMsgsOffsetQuery->first();
   offset = requestMsgsOffsetQuery->value(0).toUInt();
-  
+
   // now let's select the messages
   requestMsgsQuery->bindValue(":bufferid", buffer.uid());
+  requestMsgsQuery->bindValue(":bufferid2", buffer.uid());  // Qt can't handle the same placeholder used twice
   requestMsgsQuery->bindValue(":limit", lastmsgs);
   requestMsgsQuery->bindValue(":offset", offset);
   requestMsgsQuery->exec();
-  
   while(requestMsgsQuery->next()) {
     Message msg(QDateTime::fromTime_t(requestMsgsQuery->value(1).toInt()),
                 buffer,
@@ -382,7 +385,7 @@ QList<Message> SqliteStorage::requestMsgs(BufferId buffer, int lastmsgs, int off
     msg.msgId = requestMsgsQuery->value(0).toUInt();
     messagelist << msg;
   }
-  
+
   return messagelist;
 }
 
@@ -395,13 +398,14 @@ QList<Message> SqliteStorage::requestMsgs(BufferId buffer, QDateTime since, int
   requestMsgsSinceOffsetQuery->exec();
   requestMsgsSinceOffsetQuery->first();
   offset = requestMsgsSinceOffsetQuery->value(0).toUInt();  
-  
+
   // now let's select the messages
   requestMsgsSinceQuery->bindValue(":bufferid", buffer.uid());
+  requestMsgsSinceQuery->bindValue(":bufferid2", buffer.uid());
   requestMsgsSinceQuery->bindValue(":since", since.toTime_t());
   requestMsgsSinceQuery->bindValue(":offset", offset);
   requestMsgsSinceQuery->exec();
-  
+
   while(requestMsgsSinceQuery->next()) {
     Message msg(QDateTime::fromTime_t(requestMsgsSinceQuery->value(1).toInt()),
                 buffer,
@@ -412,8 +416,7 @@ QList<Message> SqliteStorage::requestMsgs(BufferId buffer, QDateTime since, int
     msg.msgId = requestMsgsSinceQuery->value(0).toUInt();
     messagelist << msg;
   }
-  
-    
+
   return messagelist;
 }
 
@@ -421,9 +424,10 @@ QList<Message> SqliteStorage::requestMsgs(BufferId buffer, QDateTime since, int
 QList<Message> SqliteStorage::requestMsgRange(BufferId buffer, int first, int last) {
   QList<Message> messagelist;
   requestMsgRangeQuery->bindValue(":bufferid", buffer.uid());
+  requestMsgRangeQuery->bindValue(":bufferid2", buffer.uid());
   requestMsgRangeQuery->bindValue(":firstmsg", first);
   requestMsgRangeQuery->bindValue(":lastmsg", last);
-  
+
   while(requestMsgRangeQuery->next()) {
     Message msg(QDateTime::fromTime_t(requestMsgRangeQuery->value(1).toInt()),
                 buffer,
@@ -434,7 +438,7 @@ QList<Message> SqliteStorage::requestMsgRange(BufferId buffer, int first, int la
     msg.msgId = requestMsgRangeQuery->value(0).toUInt();
     messagelist << msg;
   }
-  
+
   return messagelist;
 }
 
@@ -448,7 +452,7 @@ void SqliteStorage::importOldBacklog() {
   } else {
     user = query.value(0).toUInt();
   }
-  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");