1 /***************************************************************************
2 * Copyright (C) 2005-07 by the Quassel IRC Team *
3 * devel@quassel-irc.org *
5 * This program is free software; you can redistribute it and/or modify *
6 * it under the terms of the GNU General Public License as published by *
7 * the Free Software Foundation; either version 2 of the License, or *
8 * (at your option) version 3. *
10 * This program is distributed in the hope that it will be useful, *
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of *
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the *
13 * GNU General Public License for more details. *
15 * You should have received a copy of the GNU General Public License *
16 * along with this program; if not, write to the *
17 * Free Software Foundation, Inc., *
18 * 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA. *
19 ***************************************************************************/
21 #include "sqlitestorage.h"
25 SqliteStorage::SqliteStorage() {
26 logMessageQuery = NULL;
27 addSenderQuery = NULL;
28 getLastMessageIdQuery = NULL;
29 requestMsgsQuery = NULL;
30 requestMsgsOffsetQuery = NULL;
31 requestMsgsSinceQuery = NULL;
32 requestMsgsSinceOffsetQuery = NULL;
33 requestMsgRangeQuery = NULL;
34 createNetworkQuery = NULL;
35 createBufferQuery = NULL;
36 getBufferInfoQuery = NULL;
39 SqliteStorage::~SqliteStorage() {
40 if (logMessageQuery) delete logMessageQuery;
41 if (addSenderQuery) delete addSenderQuery;
42 if (getLastMessageIdQuery) delete getLastMessageIdQuery;
43 if (requestMsgsQuery) delete requestMsgsQuery;
44 if (requestMsgsOffsetQuery) delete requestMsgsOffsetQuery;
45 if (requestMsgsSinceQuery) delete requestMsgsSinceQuery;
46 if (requestMsgsSinceOffsetQuery) delete requestMsgsSinceOffsetQuery;
47 if (requestMsgRangeQuery) delete requestMsgRangeQuery;
48 if (createNetworkQuery) delete createNetworkQuery;
49 if (createBufferQuery) delete createBufferQuery;
50 if (getBufferInfoQuery) delete getBufferInfoQuery;
55 bool SqliteStorage::isAvailable() {
56 if(!QSqlDatabase::isDriverAvailable("QSQLITE")) return false;
60 QString SqliteStorage::displayName() {
61 return QString("SQlite");
64 bool SqliteStorage::setup(const QVariantMap &settings) {
67 // this extra scope is needed to be able to remove the database connection later
69 logDb = QSqlDatabase::addDatabase("QSQLITE", "quassel_setup");
70 logDb.setDatabaseName(SqliteStorage::backlogFile(true));
74 qWarning(tr("Could not open backlog database: %1").arg(logDb.lastError().text()).toAscii());
76 logDb.exec("CREATE TABLE quasseluser ("
77 "userid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
78 "username TEXT UNIQUE NOT NULL,"
79 "password BLOB NOT NULL)");
81 logDb.exec("CREATE TABLE sender ("
82 "senderid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
83 "sender TEXT UNIQUE NOT NULL)");
85 logDb.exec("CREATE TABLE network ("
86 "networkid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
87 "userid INTEGER NOT NULL,"
88 "networkname TEXT NOT NULL,"
89 "UNIQUE (userid, networkname))");
91 logDb.exec("CREATE TABLE buffergroup ("
92 "groupid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
93 "userid INTEGER NOT NULL,"
96 logDb.exec("CREATE TABLE buffer ("
97 "bufferid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
98 "userid INTEGER NOT NULL,"
100 "networkid INTEGER NOT NULL,"
101 "buffername TEXT NOT NULL)");
103 logDb.exec("CREATE UNIQUE INDEX buffer_idx "
104 "ON buffer(userid, networkid, buffername)");
106 logDb.exec("CREATE TABLE backlog ("
107 "messageid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
108 "time INTEGER NOT NULL,"
109 "bufferid INTEGER NOT NULL,"
110 "type INTEGER NOT NULL,"
111 "flags INTEGER NOT NULL,"
112 "senderid INTEGER NOT NULL,"
115 logDb.exec("CREATE TABLE coreinfo ("
116 "updateid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
117 "version INTEGER NOT NULL)");
119 logDb.exec("INSERT INTO coreinfo (version) VALUES (0)");
121 // something fucked up -> no logging possible
122 // FIXME logDb.lastError is reset whenever exec is called
123 if(logDb.lastError().isValid()) {
124 qWarning(tr("Could not create backlog table: %1").arg(logDb.lastError().text()).toAscii());
125 qWarning(tr("Disabling logging...").toAscii());
126 Q_ASSERT(false); // quassel does require logging
134 QSqlDatabase::removeDatabase("quassel_setup");
138 bool SqliteStorage::init(const QVariantMap &settings) {
141 // i need the extra scope to be able to remove the database connection
143 logDb = QSqlDatabase::database("quassel_connection", false);
144 if (!logDb.isValid()) {
145 logDb = QSqlDatabase::addDatabase("QSQLITE", "quassel_connection");
147 logDb.setDatabaseName(SqliteStorage::backlogFile());
150 qWarning(tr("Could not open backlog database: %1").arg(logDb.lastError().text()).toAscii());
155 //QSqlDatabase::removeDatabase("quassel_connection");
159 // check if the db schema is up to date
160 QSqlQuery query = logDb.exec("SELECT MAX(version) FROM coreinfo");
163 //checkVersion(query.value(0));
164 qDebug() << "Sqlite is ready. Quassel Schema Version:" << query.value(0).toUInt();
166 qWarning("Sqlite is not ready!");
170 // we will need those pretty often... so let's speed things up:
171 createBufferQuery = new QSqlQuery(logDb);
172 createBufferQuery->prepare("INSERT INTO buffer (userid, networkid, buffername) VALUES (:userid, (SELECT networkid FROM network WHERE networkname = :networkname AND userid = :userid2), :buffername)");
174 createNetworkQuery = new QSqlQuery(logDb);
175 createNetworkQuery->prepare("INSERT INTO network (userid, networkname) VALUES (:userid, :networkname)");
177 getBufferInfoQuery = new QSqlQuery(logDb);
178 getBufferInfoQuery->prepare("SELECT bufferid FROM buffer "
179 "JOIN network ON buffer.networkid = network.networkid "
180 "WHERE network.networkname = :networkname AND network.userid = :userid AND buffer.userid = :userid2 AND lower(buffer.buffername) = lower(:buffername)");
182 logMessageQuery = new QSqlQuery(logDb);
183 logMessageQuery->prepare("INSERT INTO backlog (time, bufferid, type, flags, senderid, message) "
184 "VALUES (:time, :bufferid, :type, :flags, (SELECT senderid FROM sender WHERE sender = :sender), :message)");
186 addSenderQuery = new QSqlQuery(logDb);
187 addSenderQuery->prepare("INSERT INTO sender (sender) VALUES (:sender)");
189 getLastMessageIdQuery = new QSqlQuery(logDb);
190 getLastMessageIdQuery->prepare("SELECT messageid FROM backlog "
191 "WHERE time = :time AND bufferid = :bufferid AND type = :type AND senderid = (SELECT senderid FROM sender WHERE sender = :sender)");
193 requestMsgsOffsetQuery = new QSqlQuery(logDb);
194 requestMsgsOffsetQuery->prepare("SELECT count(*) FROM backlog WHERE bufferid = :bufferid AND messageid < :messageid");
196 requestMsgsQuery = new QSqlQuery(logDb);
197 requestMsgsQuery->prepare("SELECT messageid, time, type, flags, sender, message, displayname "
199 "JOIN buffer ON backlog.bufferid = buffer.bufferid "
200 "JOIN sender ON backlog.senderid = sender.senderid "
201 "LEFT JOIN buffergroup ON buffer.groupid = buffergroup.groupid "
202 "WHERE buffer.bufferid = :bufferid OR buffer.groupid = (SELECT groupid FROM buffer WHERE bufferid = :bufferid2) "
203 "ORDER BY messageid DESC "
204 "LIMIT :limit OFFSET :offset");
206 requestMsgsSinceOffsetQuery = new QSqlQuery(logDb);
207 requestMsgsSinceOffsetQuery->prepare("SELECT count(*) FROM backlog WHERE bufferid = :bufferid AND time >= :since");
209 requestMsgsSinceQuery = new QSqlQuery(logDb);
210 requestMsgsSinceQuery->prepare("SELECT messageid, time, type, flags, sender, message, displayname "
212 "JOIN buffer ON backlog.bufferid = buffer.bufferid "
213 "JOIN sender ON backlog.senderid = sender.senderid "
214 "LEFT JOIN buffergroup ON buffer.groupid = buffergroup.groupid "
215 "WHERE (buffer.bufferid = :bufferid OR buffer.groupid = (SELECT groupid FROM buffer WHERE bufferid = :bufferid2)) AND "
216 "backlog.time >= :since "
217 "ORDER BY messageid DESC "
218 "LIMIT -1 OFFSET :offset");
220 requestMsgRangeQuery = new QSqlQuery(logDb);
221 requestMsgRangeQuery->prepare("SELECT messageid, time, type, flags, sender, message, displayname "
223 "JOIN buffer ON backlog.bufferid = buffer.bufferid "
224 "JOIN sender ON backlog.senderid = sender.senderid "
225 "LEFT JOIN buffergroup ON buffer.groupid = buffergroup.groupid "
226 "WHERE (buffer.bufferid = :bufferid OR buffer.groupid = (SELECT groupid FROM buffer WHERE bufferid = :bufferid2)) AND "
227 "backlog.messageid >= :firstmsg AND backlog.messageid <= :lastmsg "
228 "ORDER BY messageid DESC ");
233 UserId SqliteStorage::addUser(const QString &user, const QString &password) {
234 QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
235 cryptopass = cryptopass.toHex();
237 QSqlQuery query(logDb);
238 query.prepare("INSERT INTO quasseluser (username, password) VALUES (:username, :password)");
239 query.bindValue(":username", user);
240 query.bindValue(":password", cryptopass);
242 if(query.lastError().isValid() && query.lastError().number() == 19) { // user already exists - sadly 19 seems to be the general constraint violation error...
246 query.prepare("SELECT userid FROM quasseluser WHERE username = :username");
247 query.bindValue(":username", user);
250 UserId uid = query.value(0).toUInt();
251 emit userAdded(uid, user);
255 void SqliteStorage::updateUser(UserId user, const QString &password) {
256 QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
257 cryptopass = cryptopass.toHex();
259 QSqlQuery query(logDb);
260 query.prepare("UPDATE quasseluser SET password = :password WHERE userid = :userid");
261 query.bindValue(":userid", user);
262 query.bindValue(":password", cryptopass);
266 void SqliteStorage::renameUser(UserId user, const QString &newName) {
267 QSqlQuery query(logDb);
268 query.prepare("UPDATE quasseluser SET username = :username WHERE userid = :userid");
269 query.bindValue(":userid", user);
270 query.bindValue(":username", newName);
272 emit userRenamed(user, newName);
275 UserId SqliteStorage::validateUser(const QString &user, const QString &password) {
276 QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
277 cryptopass = cryptopass.toHex();
279 QSqlQuery query(logDb);
280 query.prepare("SELECT userid FROM quasseluser WHERE username = :username AND password = :password");
281 query.bindValue(":username", user);
282 query.bindValue(":password", cryptopass);
286 return query.value(0).toUInt();
293 void SqliteStorage::delUser(UserId user) {
294 QSqlQuery query(logDb);
295 query.prepare("DELETE FROM backlog WHERE bufferid IN (SELECT DISTINCT bufferid FROM buffer WHERE userid = :userid");
296 query.bindValue(":userid", user);
298 query.prepare("DELETE FROM buffer WHERE userid = :userid");
299 query.bindValue(":userid", user);
301 query.prepare("DELETE FROM buffergroup WHERE userid = :userid");
302 query.bindValue(":userid", user);
304 query.prepare("DELETE FROM network WHERE userid = :userid");
305 query.bindValue(":userid", user);
307 query.prepare("DELETE FROM quasseluser WHERE userid = :userid");
308 query.bindValue(":userid", user);
310 // I hate the lack of foreign keys and on delete cascade... :(
311 emit userRemoved(user);
314 void SqliteStorage::createBuffer(UserId user, const QString &network, const QString &buffer) {
315 createBufferQuery->bindValue(":userid", user);
316 createBufferQuery->bindValue(":userid2", user); // Qt can't handle same placeholder twice (maybe sqlites fault)
317 createBufferQuery->bindValue(":networkname", network);
318 createBufferQuery->bindValue(":buffername", buffer);
319 createBufferQuery->exec();
321 if(createBufferQuery->lastError().isValid()) {
322 if(createBufferQuery->lastError().number() == 19) { // Null Constraint violation
323 createNetworkQuery->bindValue(":userid", user);
324 createNetworkQuery->bindValue(":networkname", network);
325 createNetworkQuery->exec();
326 createBufferQuery->exec();
327 Q_ASSERT(!createNetworkQuery->lastError().isValid());
328 Q_ASSERT(!createBufferQuery->lastError().isValid());
331 qDebug() << "failed to create Buffer: ErrNo:" << createBufferQuery->lastError().number() << "ErrMsg:" << createBufferQuery->lastError().text();
337 uint SqliteStorage::getNetworkId(UserId user, const QString &network) {
338 QSqlQuery query(logDb);
339 query.prepare("SELECT networkid FROM network "
340 "WHERE userid = :userid AND networkname = :networkname");
341 query.bindValue(":userid", user);
342 query.bindValue(":networkname", network);
346 return query.value(0).toUInt();
348 createBuffer(user, network, "");
351 return query.value(0).toUInt();
353 qWarning() << "NETWORK NOT FOUND:" << network << "for User:" << user;
359 BufferInfo SqliteStorage::getBufferInfo(UserId user, const QString &network, const QString &buffer) {
361 // TODO: get rid of this hackaround
362 uint networkId = getNetworkId(user, network);
363 getBufferInfoQuery->bindValue(":networkname", network);
364 getBufferInfoQuery->bindValue(":userid", user);
365 getBufferInfoQuery->bindValue(":userid2", user); // Qt can't handle same placeholder twice... though I guess it's sqlites fault
366 getBufferInfoQuery->bindValue(":buffername", buffer);
367 getBufferInfoQuery->exec();
369 if(!getBufferInfoQuery->first()) {
370 createBuffer(user, network, buffer);
371 getBufferInfoQuery->exec();
372 if(getBufferInfoQuery->first()) {
373 bufferid = BufferInfo(getBufferInfoQuery->value(0).toUInt(), networkId, 0, network, buffer);
374 emit bufferInfoUpdated(bufferid);
377 bufferid = BufferInfo(getBufferInfoQuery->value(0).toUInt(), networkId, 0, network, buffer);
380 Q_ASSERT(!getBufferInfoQuery->next());
385 QList<BufferInfo> SqliteStorage::requestBuffers(UserId user, QDateTime since) {
386 QList<BufferInfo> bufferlist;
387 QSqlQuery query(logDb);
388 query.prepare("SELECT DISTINCT buffer.bufferid, networkname, buffername FROM buffer "
389 "JOIN network ON buffer.networkid = network.networkid "
390 "JOIN backlog ON buffer.bufferid = backlog.bufferid "
391 "WHERE buffer.userid = :userid AND time >= :time");
392 query.bindValue(":userid", user);
393 if (since.isValid()) {
394 query.bindValue(":time", since.toTime_t());
396 query.bindValue(":time", 0);
401 while(query.next()) {
402 bufferlist << BufferInfo(query.value(0).toUInt(), getNetworkId(user, query.value(1).toString()), 0, query.value(1).toString(), query.value(2).toString());
407 MsgId SqliteStorage::logMessage(Message msg) {
408 logMessageQuery->bindValue(":time", msg.timestamp().toTime_t());
409 logMessageQuery->bindValue(":bufferid", msg.buffer().uid());
410 logMessageQuery->bindValue(":type", msg.type());
411 logMessageQuery->bindValue(":flags", msg.flags());
412 logMessageQuery->bindValue(":sender", msg.sender());
413 logMessageQuery->bindValue(":message", msg.text());
414 logMessageQuery->exec();
416 if(logMessageQuery->lastError().isValid()) {
417 // constraint violation - must be NOT NULL constraint - probably the sender is missing...
418 if(logMessageQuery->lastError().number() == 19) {
419 addSenderQuery->bindValue(":sender", msg.sender());
420 addSenderQuery->exec();
421 watchQuery(addSenderQuery);
422 logMessageQuery->exec();
423 if(!watchQuery(logMessageQuery))
426 watchQuery(logMessageQuery);
430 getLastMessageIdQuery->bindValue(":time", msg.timestamp().toTime_t());
431 getLastMessageIdQuery->bindValue(":bufferid", msg.buffer().uid());
432 getLastMessageIdQuery->bindValue(":type", msg.type());
433 getLastMessageIdQuery->bindValue(":sender", msg.sender());
434 getLastMessageIdQuery->exec();
436 if(getLastMessageIdQuery->first()) {
437 return getLastMessageIdQuery->value(0).toUInt();
438 } else { // somethin went wrong... :(
439 qDebug() << getLastMessageIdQuery->lastQuery() << "time/bufferid/type/sender:" << msg.timestamp().toTime_t() << msg.buffer().uid() << msg.type() << msg.sender();
445 QList<Message> SqliteStorage::requestMsgs(BufferInfo buffer, int lastmsgs, int offset) {
446 QList<Message> messagelist;
447 // we have to determine the real offset first
448 requestMsgsOffsetQuery->bindValue(":bufferid", buffer.uid());
449 requestMsgsOffsetQuery->bindValue(":messageid", offset);
450 requestMsgsOffsetQuery->exec();
451 requestMsgsOffsetQuery->first();
452 offset = requestMsgsOffsetQuery->value(0).toUInt();
454 // now let's select the messages
455 requestMsgsQuery->bindValue(":bufferid", buffer.uid());
456 requestMsgsQuery->bindValue(":bufferid2", buffer.uid()); // Qt can't handle the same placeholder used twice
457 requestMsgsQuery->bindValue(":limit", lastmsgs);
458 requestMsgsQuery->bindValue(":offset", offset);
459 requestMsgsQuery->exec();
460 while(requestMsgsQuery->next()) {
461 Message msg(QDateTime::fromTime_t(requestMsgsQuery->value(1).toInt()),
463 (Message::Type)requestMsgsQuery->value(2).toUInt(),
464 requestMsgsQuery->value(5).toString(),
465 requestMsgsQuery->value(4).toString(),
466 requestMsgsQuery->value(3).toUInt());
467 msg.setMsgId(requestMsgsQuery->value(0).toUInt());
474 QList<Message> SqliteStorage::requestMsgs(BufferInfo buffer, QDateTime since, int offset) {
475 QList<Message> messagelist;
476 // we have to determine the real offset first
477 requestMsgsSinceOffsetQuery->bindValue(":bufferid", buffer.uid());
478 requestMsgsSinceOffsetQuery->bindValue(":since", since.toTime_t());
479 requestMsgsSinceOffsetQuery->exec();
480 requestMsgsSinceOffsetQuery->first();
481 offset = requestMsgsSinceOffsetQuery->value(0).toUInt();
483 // now let's select the messages
484 requestMsgsSinceQuery->bindValue(":bufferid", buffer.uid());
485 requestMsgsSinceQuery->bindValue(":bufferid2", buffer.uid());
486 requestMsgsSinceQuery->bindValue(":since", since.toTime_t());
487 requestMsgsSinceQuery->bindValue(":offset", offset);
488 requestMsgsSinceQuery->exec();
490 while(requestMsgsSinceQuery->next()) {
491 Message msg(QDateTime::fromTime_t(requestMsgsSinceQuery->value(1).toInt()),
493 (Message::Type)requestMsgsSinceQuery->value(2).toUInt(),
494 requestMsgsSinceQuery->value(5).toString(),
495 requestMsgsSinceQuery->value(4).toString(),
496 requestMsgsSinceQuery->value(3).toUInt());
497 msg.setMsgId(requestMsgsSinceQuery->value(0).toUInt());
505 QList<Message> SqliteStorage::requestMsgRange(BufferInfo buffer, int first, int last) {
506 QList<Message> messagelist;
507 requestMsgRangeQuery->bindValue(":bufferid", buffer.uid());
508 requestMsgRangeQuery->bindValue(":bufferid2", buffer.uid());
509 requestMsgRangeQuery->bindValue(":firstmsg", first);
510 requestMsgRangeQuery->bindValue(":lastmsg", last);
512 while(requestMsgRangeQuery->next()) {
513 Message msg(QDateTime::fromTime_t(requestMsgRangeQuery->value(1).toInt()),
515 (Message::Type)requestMsgRangeQuery->value(2).toUInt(),
516 requestMsgRangeQuery->value(5).toString(),
517 requestMsgRangeQuery->value(4).toString(),
518 requestMsgRangeQuery->value(3).toUInt());
519 msg.setMsgId(requestMsgRangeQuery->value(0).toUInt());
526 QString SqliteStorage::backlogFile(bool createPath) {
527 // kinda ugly, but I currently see no other way to do that
529 QString quasselDir = QDir::homePath() + qgetenv("APPDATA") + "\\quassel\\";
531 QString quasselDir = QDir::homePath() + "/.quassel/";
535 QDir *qDir = new QDir(quasselDir);
536 if (!qDir->exists(quasselDir)) {
537 qDir->mkpath(quasselDir);
542 return quasselDir + "quassel-storage.sqlite";
545 bool SqliteStorage::watchQuery(QSqlQuery *query) {
546 if(query->lastError().isValid()) {
547 qWarning() << "unhandled Error in QSqlQuery!";
548 qWarning() << " last Query:" << query->lastQuery();
549 qWarning() << " executed Query:" << query->executedQuery();
550 qWarning() << " bound Values:" << query->boundValues();
551 qWarning() << " Error Number:" << query->lastError().number();
552 qWarning() << " Error Message:" << query->lastError().text();
553 qWarning() << " Driver Message:" << query->lastError().driverText();
554 qWarning() << " DB Message:" << query->lastError().databaseText();