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) {
66 // this extra scope is needed to be able to remove the database connection later
68 logDb = QSqlDatabase::addDatabase("QSQLITE", "quassel_setup");
69 logDb.setDatabaseName(SqliteStorage::backlogFile(true));
73 qWarning(tr("Could not open backlog database: %1").arg(logDb.lastError().text()).toAscii());
75 logDb.exec("CREATE TABLE quasseluser ("
76 "userid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
77 "username TEXT UNIQUE NOT NULL,"
78 "password BLOB NOT NULL)");
80 logDb.exec("CREATE TABLE sender ("
81 "senderid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
82 "sender TEXT UNIQUE NOT NULL)");
84 logDb.exec("CREATE TABLE network ("
85 "networkid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
86 "userid INTEGER NOT NULL,"
87 "networkname TEXT NOT NULL,"
88 "UNIQUE (userid, networkname))");
90 logDb.exec("CREATE TABLE buffergroup ("
91 "groupid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
92 "userid INTEGER NOT NULL,"
95 logDb.exec("CREATE TABLE buffer ("
96 "bufferid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
97 "userid INTEGER NOT NULL,"
99 "networkid INTEGER NOT NULL,"
100 "buffername TEXT NOT NULL)");
102 logDb.exec("CREATE UNIQUE INDEX buffer_idx "
103 "ON buffer(userid, networkid, buffername)");
105 logDb.exec("CREATE TABLE backlog ("
106 "messageid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
107 "time INTEGER NOT NULL,"
108 "bufferid INTEGER NOT NULL,"
109 "type INTEGER NOT NULL,"
110 "flags INTEGER NOT NULL,"
111 "senderid INTEGER NOT NULL,"
114 logDb.exec("CREATE TABLE coreinfo ("
115 "updateid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
116 "version INTEGER NOT NULL)");
118 logDb.exec("INSERT INTO coreinfo (version) VALUES (0)");
120 // something fucked up -> no logging possible
121 // FIXME logDb.lastError is reset whenever exec is called
122 if(logDb.lastError().isValid()) {
123 qWarning(tr("Could not create backlog table: %1").arg(logDb.lastError().text()).toAscii());
124 qWarning(tr("Disabling logging...").toAscii());
125 Q_ASSERT(false); // quassel does require logging
133 QSqlDatabase::removeDatabase("quassel_setup");
137 bool SqliteStorage::init(const QVariantMap &settings) {
139 // i need the extra scope to be able to remove the database connection
141 logDb = QSqlDatabase::database("quassel_connection", false);
142 if (!logDb.isValid()) {
143 logDb = QSqlDatabase::addDatabase("QSQLITE", "quassel_connection");
145 logDb.setDatabaseName(SqliteStorage::backlogFile());
148 qWarning(tr("Could not open backlog database: %1").arg(logDb.lastError().text()).toAscii());
153 //QSqlDatabase::removeDatabase("quassel_connection");
157 // check if the db schema is up to date
158 QSqlQuery query = logDb.exec("SELECT MAX(version) FROM coreinfo");
161 //checkVersion(query.value(0));
162 qDebug() << "Sqlite is ready. Quassel Schema Version:" << query.value(0).toUInt();
164 qWarning("Sqlite is not ready!");
168 // we will need those pretty often... so let's speed things up:
169 createBufferQuery = new QSqlQuery(logDb);
170 createBufferQuery->prepare("INSERT INTO buffer (userid, networkid, buffername) VALUES (:userid, (SELECT networkid FROM network WHERE networkname = :networkname AND userid = :userid2), :buffername)");
172 createNetworkQuery = new QSqlQuery(logDb);
173 createNetworkQuery->prepare("INSERT INTO network (userid, networkname) VALUES (:userid, :networkname)");
175 getBufferInfoQuery = new QSqlQuery(logDb);
176 getBufferInfoQuery->prepare("SELECT bufferid FROM buffer "
177 "JOIN network ON buffer.networkid = network.networkid "
178 "WHERE network.networkname = :networkname AND network.userid = :userid AND buffer.userid = :userid2 AND lower(buffer.buffername) = lower(:buffername)");
180 logMessageQuery = new QSqlQuery(logDb);
181 logMessageQuery->prepare("INSERT INTO backlog (time, bufferid, type, flags, senderid, message) "
182 "VALUES (:time, :bufferid, :type, :flags, (SELECT senderid FROM sender WHERE sender = :sender), :message)");
184 addSenderQuery = new QSqlQuery(logDb);
185 addSenderQuery->prepare("INSERT INTO sender (sender) VALUES (:sender)");
187 getLastMessageIdQuery = new QSqlQuery(logDb);
188 getLastMessageIdQuery->prepare("SELECT messageid FROM backlog "
189 "WHERE time = :time AND bufferid = :bufferid AND type = :type AND senderid = (SELECT senderid FROM sender WHERE sender = :sender)");
191 requestMsgsOffsetQuery = new QSqlQuery(logDb);
192 requestMsgsOffsetQuery->prepare("SELECT count(*) FROM backlog WHERE bufferid = :bufferid AND messageid < :messageid");
194 requestMsgsQuery = new QSqlQuery(logDb);
195 requestMsgsQuery->prepare("SELECT messageid, time, type, flags, sender, message, displayname "
197 "JOIN buffer ON backlog.bufferid = buffer.bufferid "
198 "JOIN sender ON backlog.senderid = sender.senderid "
199 "LEFT JOIN buffergroup ON buffer.groupid = buffergroup.groupid "
200 "WHERE buffer.bufferid = :bufferid OR buffer.groupid = (SELECT groupid FROM buffer WHERE bufferid = :bufferid2) "
201 "ORDER BY messageid DESC "
202 "LIMIT :limit OFFSET :offset");
204 requestMsgsSinceOffsetQuery = new QSqlQuery(logDb);
205 requestMsgsSinceOffsetQuery->prepare("SELECT count(*) FROM backlog WHERE bufferid = :bufferid AND time >= :since");
207 requestMsgsSinceQuery = new QSqlQuery(logDb);
208 requestMsgsSinceQuery->prepare("SELECT messageid, time, type, flags, sender, message, displayname "
210 "JOIN buffer ON backlog.bufferid = buffer.bufferid "
211 "JOIN sender ON backlog.senderid = sender.senderid "
212 "LEFT JOIN buffergroup ON buffer.groupid = buffergroup.groupid "
213 "WHERE (buffer.bufferid = :bufferid OR buffer.groupid = (SELECT groupid FROM buffer WHERE bufferid = :bufferid2)) AND "
214 "backlog.time >= :since "
215 "ORDER BY messageid DESC "
216 "LIMIT -1 OFFSET :offset");
218 requestMsgRangeQuery = new QSqlQuery(logDb);
219 requestMsgRangeQuery->prepare("SELECT messageid, time, type, flags, sender, message, displayname "
221 "JOIN buffer ON backlog.bufferid = buffer.bufferid "
222 "JOIN sender ON backlog.senderid = sender.senderid "
223 "LEFT JOIN buffergroup ON buffer.groupid = buffergroup.groupid "
224 "WHERE (buffer.bufferid = :bufferid OR buffer.groupid = (SELECT groupid FROM buffer WHERE bufferid = :bufferid2)) AND "
225 "backlog.messageid >= :firstmsg AND backlog.messageid <= :lastmsg "
226 "ORDER BY messageid DESC ");
231 UserId SqliteStorage::addUser(const QString &user, const QString &password) {
232 QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
233 cryptopass = cryptopass.toHex();
235 QSqlQuery query(logDb);
236 query.prepare("INSERT INTO quasseluser (username, password) VALUES (:username, :password)");
237 query.bindValue(":username", user);
238 query.bindValue(":password", cryptopass);
240 if(query.lastError().isValid() && query.lastError().number() == 19) { // user already exists - sadly 19 seems to be the general constraint violation error...
244 query.prepare("SELECT userid FROM quasseluser WHERE username = :username");
245 query.bindValue(":username", user);
248 UserId uid = query.value(0).toUInt();
249 emit userAdded(uid, user);
253 void SqliteStorage::updateUser(UserId user, const QString &password) {
254 QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
255 cryptopass = cryptopass.toHex();
257 QSqlQuery query(logDb);
258 query.prepare("UPDATE quasseluser SET password = :password WHERE userid = :userid");
259 query.bindValue(":userid", user);
260 query.bindValue(":password", cryptopass);
264 void SqliteStorage::renameUser(UserId user, const QString &newName) {
265 QSqlQuery query(logDb);
266 query.prepare("UPDATE quasseluser SET username = :username WHERE userid = :userid");
267 query.bindValue(":userid", user);
268 query.bindValue(":username", newName);
270 emit userRenamed(user, newName);
273 UserId SqliteStorage::validateUser(const QString &user, const QString &password) {
274 QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
275 cryptopass = cryptopass.toHex();
277 QSqlQuery query(logDb);
278 query.prepare("SELECT userid FROM quasseluser WHERE username = :username AND password = :password");
279 query.bindValue(":username", user);
280 query.bindValue(":password", cryptopass);
284 return query.value(0).toUInt();
291 void SqliteStorage::delUser(UserId user) {
292 QSqlQuery query(logDb);
293 query.prepare("DELETE FROM backlog WHERE bufferid IN (SELECT DISTINCT bufferid FROM buffer WHERE userid = :userid");
294 query.bindValue(":userid", user);
296 query.prepare("DELETE FROM buffer WHERE userid = :userid");
297 query.bindValue(":userid", user);
299 query.prepare("DELETE FROM buffergroup WHERE userid = :userid");
300 query.bindValue(":userid", user);
302 query.prepare("DELETE FROM network WHERE userid = :userid");
303 query.bindValue(":userid", user);
305 query.prepare("DELETE FROM quasseluser WHERE userid = :userid");
306 query.bindValue(":userid", user);
308 // I hate the lack of foreign keys and on delete cascade... :(
309 emit userRemoved(user);
312 void SqliteStorage::createBuffer(UserId user, const QString &network, const QString &buffer) {
313 createBufferQuery->bindValue(":userid", user);
314 createBufferQuery->bindValue(":userid2", user); // Qt can't handle same placeholder twice (maybe sqlites fault)
315 createBufferQuery->bindValue(":networkname", network);
316 createBufferQuery->bindValue(":buffername", buffer);
317 createBufferQuery->exec();
319 if(createBufferQuery->lastError().isValid()) {
320 if(createBufferQuery->lastError().number() == 19) { // Null Constraint violation
321 createNetworkQuery->bindValue(":userid", user);
322 createNetworkQuery->bindValue(":networkname", network);
323 createNetworkQuery->exec();
324 createBufferQuery->exec();
325 Q_ASSERT(!createNetworkQuery->lastError().isValid());
326 Q_ASSERT(!createBufferQuery->lastError().isValid());
329 qDebug() << "failed to create Buffer: ErrNo:" << createBufferQuery->lastError().number() << "ErrMsg:" << createBufferQuery->lastError().text();
335 uint SqliteStorage::getNetworkId(UserId user, const QString &network) {
336 QSqlQuery query(logDb);
337 query.prepare("SELECT networkid FROM network "
338 "WHERE userid = :userid AND networkname = :networkname");
339 query.bindValue(":userid", user);
340 query.bindValue(":networkname", network);
344 return query.value(0).toUInt();
346 createBuffer(user, network, "");
349 return query.value(0).toUInt();
351 qWarning() << "NETWORK NOT FOUND:" << network << "for User:" << user;
357 BufferInfo SqliteStorage::getBufferInfo(UserId user, const QString &network, const QString &buffer) {
359 // TODO: get rid of this hackaround
360 uint networkId = getNetworkId(user, network);
361 getBufferInfoQuery->bindValue(":networkname", network);
362 getBufferInfoQuery->bindValue(":userid", user);
363 getBufferInfoQuery->bindValue(":userid2", user); // Qt can't handle same placeholder twice... though I guess it's sqlites fault
364 getBufferInfoQuery->bindValue(":buffername", buffer);
365 getBufferInfoQuery->exec();
367 if(!getBufferInfoQuery->first()) {
368 createBuffer(user, network, buffer);
369 getBufferInfoQuery->exec();
370 if(getBufferInfoQuery->first()) {
371 bufferid = BufferInfo(getBufferInfoQuery->value(0).toUInt(), networkId, 0, network, buffer);
372 emit bufferInfoUpdated(bufferid);
375 bufferid = BufferInfo(getBufferInfoQuery->value(0).toUInt(), networkId, 0, network, buffer);
378 Q_ASSERT(!getBufferInfoQuery->next());
383 QList<BufferInfo> SqliteStorage::requestBuffers(UserId user, QDateTime since) {
384 QList<BufferInfo> bufferlist;
385 QSqlQuery query(logDb);
386 query.prepare("SELECT DISTINCT buffer.bufferid, networkname, buffername FROM buffer "
387 "JOIN network ON buffer.networkid = network.networkid "
388 "JOIN backlog ON buffer.bufferid = backlog.bufferid "
389 "WHERE buffer.userid = :userid AND time >= :time");
390 query.bindValue(":userid", user);
391 if (since.isValid()) {
392 query.bindValue(":time", since.toTime_t());
394 query.bindValue(":time", 0);
399 while(query.next()) {
400 bufferlist << BufferInfo(query.value(0).toUInt(), getNetworkId(user, query.value(1).toString()), 0, query.value(1).toString(), query.value(2).toString());
405 MsgId SqliteStorage::logMessage(Message msg) {
406 logMessageQuery->bindValue(":time", msg.timestamp().toTime_t());
407 logMessageQuery->bindValue(":bufferid", msg.buffer().uid());
408 logMessageQuery->bindValue(":type", msg.type());
409 logMessageQuery->bindValue(":flags", msg.flags());
410 logMessageQuery->bindValue(":sender", msg.sender());
411 logMessageQuery->bindValue(":message", msg.text());
412 logMessageQuery->exec();
414 if(logMessageQuery->lastError().isValid()) {
415 // constraint violation - must be NOT NULL constraint - probably the sender is missing...
416 if(logMessageQuery->lastError().number() == 19) {
417 addSenderQuery->bindValue(":sender", msg.sender());
418 addSenderQuery->exec();
419 watchQuery(addSenderQuery);
420 logMessageQuery->exec();
421 if(!watchQuery(logMessageQuery))
424 watchQuery(logMessageQuery);
428 getLastMessageIdQuery->bindValue(":time", msg.timestamp().toTime_t());
429 getLastMessageIdQuery->bindValue(":bufferid", msg.buffer().uid());
430 getLastMessageIdQuery->bindValue(":type", msg.type());
431 getLastMessageIdQuery->bindValue(":sender", msg.sender());
432 getLastMessageIdQuery->exec();
434 if(getLastMessageIdQuery->first()) {
435 return getLastMessageIdQuery->value(0).toUInt();
436 } else { // somethin went wrong... :(
437 qDebug() << getLastMessageIdQuery->lastQuery() << "time/bufferid/type/sender:" << msg.timestamp().toTime_t() << msg.buffer().uid() << msg.type() << msg.sender();
443 QList<Message> SqliteStorage::requestMsgs(BufferInfo buffer, int lastmsgs, int offset) {
444 QList<Message> messagelist;
445 // we have to determine the real offset first
446 requestMsgsOffsetQuery->bindValue(":bufferid", buffer.uid());
447 requestMsgsOffsetQuery->bindValue(":messageid", offset);
448 requestMsgsOffsetQuery->exec();
449 requestMsgsOffsetQuery->first();
450 offset = requestMsgsOffsetQuery->value(0).toUInt();
452 // now let's select the messages
453 requestMsgsQuery->bindValue(":bufferid", buffer.uid());
454 requestMsgsQuery->bindValue(":bufferid2", buffer.uid()); // Qt can't handle the same placeholder used twice
455 requestMsgsQuery->bindValue(":limit", lastmsgs);
456 requestMsgsQuery->bindValue(":offset", offset);
457 requestMsgsQuery->exec();
458 while(requestMsgsQuery->next()) {
459 Message msg(QDateTime::fromTime_t(requestMsgsQuery->value(1).toInt()),
461 (Message::Type)requestMsgsQuery->value(2).toUInt(),
462 requestMsgsQuery->value(5).toString(),
463 requestMsgsQuery->value(4).toString(),
464 requestMsgsQuery->value(3).toUInt());
465 msg.setMsgId(requestMsgsQuery->value(0).toUInt());
472 QList<Message> SqliteStorage::requestMsgs(BufferInfo buffer, QDateTime since, int offset) {
473 QList<Message> messagelist;
474 // we have to determine the real offset first
475 requestMsgsSinceOffsetQuery->bindValue(":bufferid", buffer.uid());
476 requestMsgsSinceOffsetQuery->bindValue(":since", since.toTime_t());
477 requestMsgsSinceOffsetQuery->exec();
478 requestMsgsSinceOffsetQuery->first();
479 offset = requestMsgsSinceOffsetQuery->value(0).toUInt();
481 // now let's select the messages
482 requestMsgsSinceQuery->bindValue(":bufferid", buffer.uid());
483 requestMsgsSinceQuery->bindValue(":bufferid2", buffer.uid());
484 requestMsgsSinceQuery->bindValue(":since", since.toTime_t());
485 requestMsgsSinceQuery->bindValue(":offset", offset);
486 requestMsgsSinceQuery->exec();
488 while(requestMsgsSinceQuery->next()) {
489 Message msg(QDateTime::fromTime_t(requestMsgsSinceQuery->value(1).toInt()),
491 (Message::Type)requestMsgsSinceQuery->value(2).toUInt(),
492 requestMsgsSinceQuery->value(5).toString(),
493 requestMsgsSinceQuery->value(4).toString(),
494 requestMsgsSinceQuery->value(3).toUInt());
495 msg.setMsgId(requestMsgsSinceQuery->value(0).toUInt());
503 QList<Message> SqliteStorage::requestMsgRange(BufferInfo buffer, int first, int last) {
504 QList<Message> messagelist;
505 requestMsgRangeQuery->bindValue(":bufferid", buffer.uid());
506 requestMsgRangeQuery->bindValue(":bufferid2", buffer.uid());
507 requestMsgRangeQuery->bindValue(":firstmsg", first);
508 requestMsgRangeQuery->bindValue(":lastmsg", last);
510 while(requestMsgRangeQuery->next()) {
511 Message msg(QDateTime::fromTime_t(requestMsgRangeQuery->value(1).toInt()),
513 (Message::Type)requestMsgRangeQuery->value(2).toUInt(),
514 requestMsgRangeQuery->value(5).toString(),
515 requestMsgRangeQuery->value(4).toString(),
516 requestMsgRangeQuery->value(3).toUInt());
517 msg.setMsgId(requestMsgRangeQuery->value(0).toUInt());
524 QString SqliteStorage::backlogFile(bool createPath) {
525 // kinda ugly, but I currently see no other way to do that
527 QString quasselDir = QDir::homePath() + qgetenv("APPDATA") + "\\quassel\\";
529 QString quasselDir = QDir::homePath() + "/.quassel/";
533 QDir *qDir = new QDir(quasselDir);
534 if (!qDir->exists(quasselDir)) {
535 qDir->mkpath(quasselDir);
540 return quasselDir + "quassel-storage.sqlite";
543 bool SqliteStorage::watchQuery(QSqlQuery *query) {
544 if(query->lastError().isValid()) {
545 qWarning() << "unhandled Error in QSqlQuery!";
546 qWarning() << " last Query:" << query->lastQuery();
547 qWarning() << " executed Query:" << query->executedQuery();
548 qWarning() << " bound Values:" << query->boundValues();
549 qWarning() << " Error Number:" << query->lastError().number();
550 qWarning() << " Error Message:" << query->lastError().text();
551 qWarning() << " Driver Message:" << query->lastError().driverText();
552 qWarning() << " DB Message:" << query->lastError().databaseText();