X-Git-Url: https://git.quassel-irc.org/?p=quassel.git;a=blobdiff_plain;f=core%2Fsqlitestorage.cpp;fp=core%2Fsqlitestorage.cpp;h=9b6f88de0ab024392dc6cec1672ccd5368b02950;hp=ba07cbed6b0fff83bc7cae49d878bb43645ced6b;hb=322892bd401ed5f8e837ebf0cc78053376e41842;hpb=26c07f2ab71ab640cba10bd3ecec0f05dd5a0d64 diff --git a/core/sqlitestorage.cpp b/core/sqlitestorage.cpp index ba07cbed..9b6f88de 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,86 @@ SqliteStorage::SqliteStorage() { Q_ASSERT(ok); 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()) { - // 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 " + "LIMIT 1"); - - 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() { @@ -125,10 +129,12 @@ SqliteStorage::~SqliteStorage() { delete createBufferQuery; delete getBufferIdQuery; logDb.close(); + //qDebug() << logDb.lastError().text(); } void SqliteStorage::initDb() { + //logDb.transaction(); logDb.exec("CREATE TABLE quasseluser (" "userid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," "username TEXT UNIQUE NOT NULL," @@ -187,29 +193,31 @@ void SqliteStorage::initDb() { } 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(); - + + //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... + logDb.rollback(); return 0; } logDb.commit(); - + query.prepare("SELECT userid FROM quasseluser WHERE username = :username"); query.bindValue(":username", user); query.exec(); @@ -220,7 +228,8 @@ 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(); - + + //logDb.transaction(); QSqlQuery query(logDb); query.prepare("UPDATE quasseluser SET password = :password WHERE userid = :userid"); query.bindValue(":userid", user); @@ -232,7 +241,7 @@ void SqliteStorage::updateUser(UserId user, QString password) { 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,11 +253,12 @@ UserId SqliteStorage::validateUser(QString user, QString password) { } else { return 0; } - } void SqliteStorage::delUser(UserId user) { + //logDb.transaction(); QSqlQuery query(logDb); + // FIXME: backlog has no userid, it's in bufferid query.prepare("DELETE FROM backlog WHERE userid = :userid"); query.bindValue(":userid", user); query.exec(); @@ -269,60 +279,89 @@ void SqliteStorage::delUser(UserId user) { } void SqliteStorage::createBuffer(UserId user, QString network, QString buffer) { - qDebug() << "creating buffer:" << user << network << 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()) { if(createBufferQuery->lastError().number() == 19) { // Null Constraint violation 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 { // 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) { + 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"); + 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(); - Q_ASSERT(getBufferIdQuery->first()); + flg = getBufferIdQuery->first(); + Q_ASSERT(flg); } - - return 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; } QList SqliteStorage::requestBuffers(UserId user, QDateTime since) { QList bufferlist; QSqlQuery query(logDb); - query.prepare("SELECT bufferid, networkname, buffername FROM buffer " + // 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 " "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()); + //"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(); query.exec(); + //qDebug() << query.lastError().text(); while(query.next()) { - bufferlist << BufferId(query.value(0).toUInt(), query.value(1).toString(), query.value(2).toString()); + QString buf = query.value(2).toString(); + if(buf == "$$$") buf = ""; + bufferlist << BufferId(query.value(0).toUInt(), query.value(1).toString(), buf); } 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); @@ -330,8 +369,7 @@ 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... + // constraint violation - must be NOT NULL constraint - probably the sender is missing... if(logMessageQuery->lastError().isValid()) { if(logMessageQuery->lastError().number() == 19) { addSenderQuery->bindValue(":sender", msg.sender); @@ -343,16 +381,18 @@ MsgId SqliteStorage::logMessage(Message msg) { } 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(); + Q_ASSERT(false); return 0; } } @@ -365,13 +405,13 @@ QList 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, @@ -380,9 +420,10 @@ QList SqliteStorage::requestMsgs(BufferId buffer, int lastmsgs, int off requestMsgsQuery->value(4).toString(), requestMsgsQuery->value(3).toUInt()); msg.msgId = requestMsgsQuery->value(0).toUInt(); + if(msg.sender == "$$$") msg.sender = ""; // FIXME messagelist << msg; } - + return messagelist; } @@ -395,13 +436,14 @@ QList 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, @@ -410,10 +452,11 @@ QList SqliteStorage::requestMsgs(BufferId buffer, QDateTime since, int requestMsgsSinceQuery->value(4).toString(), requestMsgsSinceQuery->value(3).toUInt()); msg.msgId = requestMsgsSinceQuery->value(0).toUInt(); + if(msg.sender == "$$$") msg.sender = ""; // FIXME messagelist << msg; } - - + + return messagelist; } @@ -421,9 +464,10 @@ QList SqliteStorage::requestMsgs(BufferId buffer, QDateTime since, int QList SqliteStorage::requestMsgRange(BufferId buffer, int first, int last) { QList 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, @@ -432,9 +476,10 @@ QList SqliteStorage::requestMsgRange(BufferId buffer, int first, int la requestMsgRangeQuery->value(4).toString(), requestMsgRangeQuery->value(3).toUInt()); msg.msgId = requestMsgRangeQuery->value(0).toUInt(); + if(msg.sender == "$$$") msg.sender = ""; // FIXME messagelist << msg; } - + return messagelist; } @@ -448,6 +493,7 @@ void SqliteStorage::importOldBacklog() { } 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.bindValue(":userid", user); query.exec();