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), :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 buffer.userid = :userid 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(":networkname", network);
315 createBufferQuery->bindValue(":buffername", buffer);
316 createBufferQuery->exec();
318 if(createBufferQuery->lastError().isValid()) {
319 if(createBufferQuery->lastError().number() == 19) { // Null Constraint violation
320 createNetworkQuery->bindValue(":userid", user);
321 createNetworkQuery->bindValue(":networkname", network);
322 createNetworkQuery->exec();
323 createBufferQuery->exec();
324 Q_ASSERT(!createNetworkQuery->lastError().isValid());
325 Q_ASSERT(!createBufferQuery->lastError().isValid());
328 qDebug() << "failed to create Buffer: ErrNo:" << createBufferQuery->lastError().number() << "ErrMsg:" << createBufferQuery->lastError().text();
334 uint SqliteStorage::getNetworkId(UserId user, const QString &network) {
335 QSqlQuery query(logDb);
336 query.prepare("SELECT networkid FROM network "
337 "WHERE userid = :userid AND networkname = :networkname");
338 query.bindValue(":userid", user);
339 query.bindValue(":networkname", network);
343 return query.value(0).toUInt();
345 createBuffer(user, network, "");
348 return query.value(0).toUInt();
350 qWarning() << "NETWORK NOT FOUND:" << network << "for User:" << user;
356 BufferInfo SqliteStorage::getBufferInfo(UserId user, const QString &network, const QString &buffer) {
358 // TODO: get rid of this hackaround
359 uint networkId = getNetworkId(user, network);
360 getBufferInfoQuery->bindValue(":networkname", network);
361 getBufferInfoQuery->bindValue(":userid", user);
362 getBufferInfoQuery->bindValue(":buffername", buffer);
363 getBufferInfoQuery->exec();
365 if(!getBufferInfoQuery->first()) {
366 createBuffer(user, network, buffer);
367 getBufferInfoQuery->exec();
368 if(getBufferInfoQuery->first()) {
369 bufferid = BufferInfo(getBufferInfoQuery->value(0).toUInt(), networkId, 0, network, buffer);
370 emit bufferInfoUpdated(bufferid);
373 bufferid = BufferInfo(getBufferInfoQuery->value(0).toUInt(), networkId, 0, network, buffer);
376 Q_ASSERT(!getBufferInfoQuery->next());
381 QList<BufferInfo> SqliteStorage::requestBuffers(UserId user, QDateTime since) {
382 QList<BufferInfo> bufferlist;
383 QSqlQuery query(logDb);
384 query.prepare("SELECT DISTINCT buffer.bufferid, networkname, buffername FROM buffer "
385 "JOIN network ON buffer.networkid = network.networkid "
386 "JOIN backlog ON buffer.bufferid = backlog.bufferid "
387 "WHERE buffer.userid = :userid AND time >= :time");
388 query.bindValue(":userid", user);
389 if (since.isValid()) {
390 query.bindValue(":time", since.toTime_t());
392 query.bindValue(":time", 0);
397 while(query.next()) {
398 bufferlist << BufferInfo(query.value(0).toUInt(), getNetworkId(user, query.value(1).toString()), 0, query.value(1).toString(), query.value(2).toString());
403 MsgId SqliteStorage::logMessage(Message msg) {
404 logMessageQuery->bindValue(":time", msg.timestamp().toTime_t());
405 logMessageQuery->bindValue(":bufferid", msg.buffer().uid());
406 logMessageQuery->bindValue(":type", msg.type());
407 logMessageQuery->bindValue(":flags", msg.flags());
408 logMessageQuery->bindValue(":sender", msg.sender());
409 logMessageQuery->bindValue(":message", msg.text());
410 logMessageQuery->exec();
412 if(logMessageQuery->lastError().isValid()) {
413 // constraint violation - must be NOT NULL constraint - probably the sender is missing...
414 if(logMessageQuery->lastError().number() == 19) {
415 addSenderQuery->bindValue(":sender", msg.sender());
416 addSenderQuery->exec();
417 watchQuery(addSenderQuery);
418 logMessageQuery->exec();
419 if(!watchQuery(logMessageQuery))
422 watchQuery(logMessageQuery);
426 getLastMessageIdQuery->bindValue(":time", msg.timestamp().toTime_t());
427 getLastMessageIdQuery->bindValue(":bufferid", msg.buffer().uid());
428 getLastMessageIdQuery->bindValue(":type", msg.type());
429 getLastMessageIdQuery->bindValue(":sender", msg.sender());
430 getLastMessageIdQuery->exec();
432 if(getLastMessageIdQuery->first()) {
433 return getLastMessageIdQuery->value(0).toUInt();
434 } else { // somethin went wrong... :(
435 qDebug() << getLastMessageIdQuery->lastQuery() << "time/bufferid/type/sender:" << msg.timestamp().toTime_t() << msg.buffer().uid() << msg.type() << msg.sender();
441 QList<Message> SqliteStorage::requestMsgs(BufferInfo buffer, int lastmsgs, int offset) {
442 QList<Message> messagelist;
443 // we have to determine the real offset first
444 requestMsgsOffsetQuery->bindValue(":bufferid", buffer.uid());
445 requestMsgsOffsetQuery->bindValue(":messageid", offset);
446 requestMsgsOffsetQuery->exec();
447 requestMsgsOffsetQuery->first();
448 offset = requestMsgsOffsetQuery->value(0).toUInt();
450 // now let's select the messages
451 requestMsgsQuery->bindValue(":bufferid", buffer.uid());
452 requestMsgsQuery->bindValue(":bufferid2", buffer.uid()); // Qt can't handle the same placeholder used twice
453 requestMsgsQuery->bindValue(":limit", lastmsgs);
454 requestMsgsQuery->bindValue(":offset", offset);
455 requestMsgsQuery->exec();
456 while(requestMsgsQuery->next()) {
457 Message msg(QDateTime::fromTime_t(requestMsgsQuery->value(1).toInt()),
459 (Message::Type)requestMsgsQuery->value(2).toUInt(),
460 requestMsgsQuery->value(5).toString(),
461 requestMsgsQuery->value(4).toString(),
462 requestMsgsQuery->value(3).toUInt());
463 msg.setMsgId(requestMsgsQuery->value(0).toUInt());
470 QList<Message> SqliteStorage::requestMsgs(BufferInfo buffer, QDateTime since, int offset) {
471 QList<Message> messagelist;
472 // we have to determine the real offset first
473 requestMsgsSinceOffsetQuery->bindValue(":bufferid", buffer.uid());
474 requestMsgsSinceOffsetQuery->bindValue(":since", since.toTime_t());
475 requestMsgsSinceOffsetQuery->exec();
476 requestMsgsSinceOffsetQuery->first();
477 offset = requestMsgsSinceOffsetQuery->value(0).toUInt();
479 // now let's select the messages
480 requestMsgsSinceQuery->bindValue(":bufferid", buffer.uid());
481 requestMsgsSinceQuery->bindValue(":bufferid2", buffer.uid());
482 requestMsgsSinceQuery->bindValue(":since", since.toTime_t());
483 requestMsgsSinceQuery->bindValue(":offset", offset);
484 requestMsgsSinceQuery->exec();
486 while(requestMsgsSinceQuery->next()) {
487 Message msg(QDateTime::fromTime_t(requestMsgsSinceQuery->value(1).toInt()),
489 (Message::Type)requestMsgsSinceQuery->value(2).toUInt(),
490 requestMsgsSinceQuery->value(5).toString(),
491 requestMsgsSinceQuery->value(4).toString(),
492 requestMsgsSinceQuery->value(3).toUInt());
493 msg.setMsgId(requestMsgsSinceQuery->value(0).toUInt());
501 QList<Message> SqliteStorage::requestMsgRange(BufferInfo buffer, int first, int last) {
502 QList<Message> messagelist;
503 requestMsgRangeQuery->bindValue(":bufferid", buffer.uid());
504 requestMsgRangeQuery->bindValue(":bufferid2", buffer.uid());
505 requestMsgRangeQuery->bindValue(":firstmsg", first);
506 requestMsgRangeQuery->bindValue(":lastmsg", last);
508 while(requestMsgRangeQuery->next()) {
509 Message msg(QDateTime::fromTime_t(requestMsgRangeQuery->value(1).toInt()),
511 (Message::Type)requestMsgRangeQuery->value(2).toUInt(),
512 requestMsgRangeQuery->value(5).toString(),
513 requestMsgRangeQuery->value(4).toString(),
514 requestMsgRangeQuery->value(3).toUInt());
515 msg.setMsgId(requestMsgRangeQuery->value(0).toUInt());
522 QString SqliteStorage::backlogFile(bool createPath) {
523 // kinda ugly, but I currently see no other way to do that
525 QString quasselDir = QDir::homePath() + qgetenv("APPDATA") + "\\quassel\\";
527 QString quasselDir = QDir::homePath() + "/.quassel/";
531 QDir *qDir = new QDir(quasselDir);
532 if (!qDir->exists(quasselDir)) {
533 qDir->mkpath(quasselDir);
538 return quasselDir + "quassel-storage.sqlite";
541 bool SqliteStorage::watchQuery(QSqlQuery *query) {
542 if(query->lastError().isValid()) {
543 qWarning() << "unhandled Error in QSqlQuery!";
544 qWarning() << " last Query:" << query->lastQuery();
545 qWarning() << " executed Query:" << query->executedQuery();
546 qWarning() << " bound Values:" << query->boundValues();
547 qWarning() << " Error Number:" << query->lastError().number();
548 qWarning() << " Error Message:" << query->lastError().text();
549 qWarning() << " Driver Message:" << query->lastError().driverText();
550 qWarning() << " DB Message:" << query->lastError().databaseText();