1 /***************************************************************************
2 * Copyright (C) 2005-07 by The Quassel 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) any later version. *
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 // TODO I don't think that this path is failsafe for windows users :)
27 QString backlogFile = Global::quasselDir + "/quassel-storage.sqlite";
28 logDb = QSqlDatabase::addDatabase("QSQLITE");
29 logDb.setDatabaseName(backlogFile);
30 bool ok = logDb.open();
33 qWarning(tr("Could not open backlog database: %1").arg(logDb.lastError().text()).toAscii());
34 qWarning(tr("Disabling logging...").toAscii());
39 // check if the db schema is up to date
40 QSqlQuery query = logDb.exec("SELECT MAX(version) FROM coreinfo");
43 //checkVersion(query.value(0));
44 qDebug() << "Sqlite is ready. Quassel Schema Version:" << query.value(0).toUInt();
49 // we will need those pretty often... so let's speed things up:
50 createBufferQuery = new QSqlQuery(logDb);
51 createBufferQuery->prepare("INSERT INTO buffer (userid, networkid, buffername) VALUES (:userid, (SELECT networkid FROM network WHERE networkname = :networkname), :buffername)");
53 createNetworkQuery = new QSqlQuery(logDb);
54 createNetworkQuery->prepare("INSERT INTO network (userid, networkname) VALUES (:userid, :networkname)");
56 getBufferInfoQuery = new QSqlQuery(logDb);
57 getBufferInfoQuery->prepare("SELECT bufferid FROM buffer "
58 "JOIN network ON buffer.networkid = network.networkid "
59 "WHERE network.networkname = :networkname AND buffer.userid = :userid AND lower(buffer.buffername) = lower(:buffername)");
61 logMessageQuery = new QSqlQuery(logDb);
62 logMessageQuery->prepare("INSERT INTO backlog (time, bufferid, type, flags, senderid, message) "
63 "VALUES (:time, :bufferid, :type, :flags, (SELECT senderid FROM sender WHERE sender = :sender), :message)");
65 addSenderQuery = new QSqlQuery(logDb);
66 addSenderQuery->prepare("INSERT INTO sender (sender) VALUES (:sender)");
68 getLastMessageIdQuery = new QSqlQuery(logDb);
69 getLastMessageIdQuery->prepare("SELECT messageid FROM backlog "
70 "WHERE time = :time AND bufferid = :bufferid AND type = :type AND senderid = (SELECT senderid FROM sender WHERE sender = :sender)");
72 requestMsgsOffsetQuery = new QSqlQuery(logDb);
73 requestMsgsOffsetQuery->prepare("SELECT count(*) FROM backlog WHERE bufferid = :bufferid AND messageid < :messageid");
75 requestMsgsQuery = new QSqlQuery(logDb);
76 requestMsgsQuery->prepare("SELECT messageid, time, type, flags, sender, message, displayname "
78 "JOIN buffer ON backlog.bufferid = buffer.bufferid "
79 "JOIN sender ON backlog.senderid = sender.senderid "
80 "LEFT JOIN buffergroup ON buffer.groupid = buffergroup.groupid "
81 "WHERE buffer.bufferid = :bufferid OR buffer.groupid = (SELECT groupid FROM buffer WHERE bufferid = :bufferid2) "
82 "ORDER BY messageid DESC "
83 "LIMIT :limit OFFSET :offset");
85 requestMsgsSinceOffsetQuery = new QSqlQuery(logDb);
86 requestMsgsSinceOffsetQuery->prepare("SELECT count(*) FROM backlog WHERE bufferid = :bufferid AND time >= :since");
88 requestMsgsSinceQuery = new QSqlQuery(logDb);
89 requestMsgsSinceQuery->prepare("SELECT messageid, time, type, flags, sender, message, displayname "
91 "JOIN buffer ON backlog.bufferid = buffer.bufferid "
92 "JOIN sender ON backlog.senderid = sender.senderid "
93 "LEFT JOIN buffergroup ON buffer.groupid = buffergroup.groupid "
94 "WHERE (buffer.bufferid = :bufferid OR buffer.groupid = (SELECT groupid FROM buffer WHERE bufferid = :bufferid2)) AND "
95 "backlog.time >= :since "
96 "ORDER BY messageid DESC "
97 "LIMIT -1 OFFSET :offset");
99 requestMsgRangeQuery = new QSqlQuery(logDb);
100 requestMsgRangeQuery->prepare("SELECT messageid, time, type, flags, sender, message, displayname "
102 "JOIN buffer ON backlog.bufferid = buffer.bufferid "
103 "JOIN sender ON backlog.senderid = sender.senderid "
104 "LEFT JOIN buffergroup ON buffer.groupid = buffergroup.groupid "
105 "WHERE (buffer.bufferid = :bufferid OR buffer.groupid = (SELECT groupid FROM buffer WHERE bufferid = :bufferid2)) AND "
106 "backlog.messageid >= :firstmsg AND backlog.messageid <= :lastmsg "
107 "ORDER BY messageid DESC ");
111 SqliteStorage::~SqliteStorage() {
113 delete logMessageQuery;
114 delete addSenderQuery;
115 delete getLastMessageIdQuery;
116 delete requestMsgsQuery;
117 delete requestMsgsOffsetQuery;
118 delete requestMsgsSinceQuery;
119 delete requestMsgsSinceOffsetQuery;
120 delete requestMsgRangeQuery;
121 delete createNetworkQuery;
122 delete createBufferQuery;
123 delete getBufferInfoQuery;
128 void SqliteStorage::initDb() {
129 logDb.exec("CREATE TABLE quasseluser ("
130 "userid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
131 "username TEXT UNIQUE NOT NULL,"
132 "password BLOB NOT NULL)");
134 logDb.exec("CREATE TABLE sender ("
135 "senderid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
136 "sender TEXT UNIQUE NOT NULL)");
138 logDb.exec("CREATE TABLE network ("
139 "networkid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
140 "userid INTEGER NOT NULL,"
141 "networkname TEXT NOT NULL,"
142 "UNIQUE (userid, networkname))");
144 logDb.exec("CREATE TABLE buffergroup ("
145 "groupid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
146 "userid INTEGER NOT NULL,"
147 "displayname TEXT)");
149 logDb.exec("CREATE TABLE buffer ("
150 "bufferid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
151 "userid INTEGER NOT NULL,"
153 "networkid INTEGER NOT NULL,"
154 "buffername TEXT NOT NULL)");
156 logDb.exec("CREATE UNIQUE INDEX buffer_idx "
157 "ON buffer(userid, networkid, buffername)");
159 logDb.exec("CREATE TABLE backlog ("
160 "messageid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
161 "time INTEGER NOT NULL,"
162 "bufferid INTEGER NOT NULL,"
163 "type INTEGER NOT NULL,"
164 "flags INTEGER NOT NULL,"
165 "senderid INTEGER NOT NULL,"
168 logDb.exec("CREATE TABLE coreinfo ("
169 "updateid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
170 "version INTEGER NOT NULL)");
172 logDb.exec("INSERT INTO coreinfo (version) VALUES (0)");
175 // something fucked up -> no logging possible
176 // FIXME logDb.lastError is reset whenever exec is called
177 if(logDb.lastError().isValid()) {
178 qWarning(tr("Could not create backlog table: %1").arg(logDb.lastError().text()).toAscii());
179 qWarning(tr("Disabling logging...").toAscii());
180 Q_ASSERT(false); // quassel does require logging
183 addUser("Default", "password");
186 bool SqliteStorage::isAvailable() {
187 if(!QSqlDatabase::isDriverAvailable("QSQLITE")) return false;
191 QString SqliteStorage::displayName() {
192 return QString("SqliteStorage");
195 UserId SqliteStorage::addUser(const QString &user, const QString &password) {
196 QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
197 cryptopass = cryptopass.toHex();
199 QSqlQuery query(logDb);
200 query.prepare("INSERT INTO quasseluser (username, password) VALUES (:username, :password)");
201 query.bindValue(":username", user);
202 query.bindValue(":password", cryptopass);
204 if(query.lastError().isValid() && query.lastError().number() == 19) { // user already exists - sadly 19 seems to be the general constraint violation error...
208 query.prepare("SELECT userid FROM quasseluser WHERE username = :username");
209 query.bindValue(":username", user);
212 UserId uid = query.value(0).toUInt();
213 emit userAdded(uid, user);
217 void SqliteStorage::updateUser(UserId user, const QString &password) {
218 QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
219 cryptopass = cryptopass.toHex();
221 QSqlQuery query(logDb);
222 query.prepare("UPDATE quasseluser SET password = :password WHERE userid = :userid");
223 query.bindValue(":userid", user);
224 query.bindValue(":password", cryptopass);
228 void SqliteStorage::renameUser(UserId user, const QString &newName) {
229 QSqlQuery query(logDb);
230 query.prepare("UPDATE quasseluser SET username = :username WHERE userid = :userid");
231 query.bindValue(":userid", user);
232 query.bindValue(":username", newName);
234 emit userRenamed(user, newName);
237 UserId SqliteStorage::validateUser(const QString &user, const QString &password) {
238 QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
239 cryptopass = cryptopass.toHex();
241 QSqlQuery query(logDb);
242 query.prepare("SELECT userid FROM quasseluser WHERE username = :username AND password = :password");
243 query.bindValue(":username", user);
244 query.bindValue(":password", cryptopass);
248 return query.value(0).toUInt();
255 void SqliteStorage::delUser(UserId user) {
256 QSqlQuery query(logDb);
257 query.prepare("DELETE FROM backlog WHERE bufferid IN (SELECT DISTINCT bufferid FROM buffer WHERE userid = :userid");
258 query.bindValue(":userid", user);
260 query.prepare("DELETE FROM buffer WHERE userid = :userid");
261 query.bindValue(":userid", user);
263 query.prepare("DELETE FROM buffergroup WHERE userid = :userid");
264 query.bindValue(":userid", user);
266 query.prepare("DELETE FROM network WHERE userid = :userid");
267 query.bindValue(":userid", user);
269 query.prepare("DELETE FROM quasseluser WHERE userid = :userid");
270 query.bindValue(":userid", user);
272 // I hate the lack of foreign keys and on delete cascade... :(
273 emit userRemoved(user);
276 void SqliteStorage::createBuffer(UserId user, const QString &network, const QString &buffer) {
277 createBufferQuery->bindValue(":userid", user);
278 createBufferQuery->bindValue(":networkname", network);
279 createBufferQuery->bindValue(":buffername", buffer);
280 createBufferQuery->exec();
282 if(createBufferQuery->lastError().isValid()) {
283 if(createBufferQuery->lastError().number() == 19) { // Null Constraint violation
284 createNetworkQuery->bindValue(":userid", user);
285 createNetworkQuery->bindValue(":networkname", network);
286 createNetworkQuery->exec();
287 createBufferQuery->exec();
288 Q_ASSERT(!createNetworkQuery->lastError().isValid());
289 Q_ASSERT(!createBufferQuery->lastError().isValid());
292 qDebug() << "failed to create Buffer: ErrNo:" << createBufferQuery->lastError().number() << "ErrMsg:" << createBufferQuery->lastError().text();
298 uint SqliteStorage::getNetworkId(UserId user, const QString &network) {
299 QSqlQuery query(logDb);
300 query.prepare("SELECT networkid FROM network "
301 "WHERE userid = :userid AND networkname = :networkname");
302 query.bindValue(":userid", user);
303 query.bindValue(":networkname", network);
307 return query.value(0).toUInt();
312 BufferInfo SqliteStorage::getBufferInfo(UserId user, const QString &network, const QString &buffer) {
314 // TODO: get rid of this hackaround
315 uint networkId = getNetworkId(user, network);
316 getBufferInfoQuery->bindValue(":networkname", network);
317 getBufferInfoQuery->bindValue(":userid", user);
318 getBufferInfoQuery->bindValue(":buffername", buffer);
319 getBufferInfoQuery->exec();
321 if(!getBufferInfoQuery->first()) {
322 createBuffer(user, network, buffer);
323 // TODO: get rid of this hackaround
324 networkId = getNetworkId(user, network);
325 getBufferInfoQuery->exec();
326 if(getBufferInfoQuery->first()) {
327 bufferid = BufferInfo(getBufferInfoQuery->value(0).toUInt(), networkId, 0, network, buffer);
328 emit bufferInfoUpdated(bufferid);
331 bufferid = BufferInfo(getBufferInfoQuery->value(0).toUInt(), networkId, 0, network, buffer);
334 Q_ASSERT(!getBufferInfoQuery->next());
339 QList<BufferInfo> SqliteStorage::requestBuffers(UserId user, QDateTime since) {
340 QList<BufferInfo> bufferlist;
341 QSqlQuery query(logDb);
342 query.prepare("SELECT DISTINCT buffer.bufferid, networkname, buffername FROM buffer "
343 "JOIN network ON buffer.networkid = network.networkid "
344 "JOIN backlog ON buffer.bufferid = backlog.bufferid "
345 "WHERE buffer.userid = :userid AND time >= :time");
346 query.bindValue(":userid", user);
347 if (since.isValid()) {
348 query.bindValue(":time", since.toTime_t());
350 query.bindValue(":time", 0);
355 while(query.next()) {
356 bufferlist << BufferInfo(query.value(0).toUInt(), getNetworkId(user, query.value(1).toString()), 0, query.value(1).toString(), query.value(2).toString());
361 MsgId SqliteStorage::logMessage(Message msg) {
362 logMessageQuery->bindValue(":time", msg.timestamp().toTime_t());
363 logMessageQuery->bindValue(":bufferid", msg.buffer().uid());
364 logMessageQuery->bindValue(":type", msg.type());
365 logMessageQuery->bindValue(":flags", msg.flags());
366 logMessageQuery->bindValue(":sender", msg.sender());
367 logMessageQuery->bindValue(":message", msg.text());
368 logMessageQuery->exec();
370 if(logMessageQuery->lastError().isValid()) {
371 // constraint violation - must be NOT NULL constraint - probably the sender is missing...
372 if(logMessageQuery->lastError().number() == 19) {
373 addSenderQuery->bindValue(":sender", msg.sender());
374 addSenderQuery->exec();
375 watchQuery(addSenderQuery);
376 logMessageQuery->exec();
377 if(!watchQuery(logMessageQuery))
380 watchQuery(logMessageQuery);
384 getLastMessageIdQuery->bindValue(":time", msg.timestamp().toTime_t());
385 getLastMessageIdQuery->bindValue(":bufferid", msg.buffer().uid());
386 getLastMessageIdQuery->bindValue(":type", msg.type());
387 getLastMessageIdQuery->bindValue(":sender", msg.sender());
388 getLastMessageIdQuery->exec();
390 if(getLastMessageIdQuery->first()) {
391 return getLastMessageIdQuery->value(0).toUInt();
392 } else { // somethin went wrong... :(
393 qDebug() << getLastMessageIdQuery->lastQuery() << "time/bufferid/type/sender:" << msg.timestamp().toTime_t() << msg.buffer().uid() << msg.type() << msg.sender();
399 QList<Message> SqliteStorage::requestMsgs(BufferInfo buffer, int lastmsgs, int offset) {
400 QList<Message> messagelist;
401 // we have to determine the real offset first
402 requestMsgsOffsetQuery->bindValue(":bufferid", buffer.uid());
403 requestMsgsOffsetQuery->bindValue(":messageid", offset);
404 requestMsgsOffsetQuery->exec();
405 requestMsgsOffsetQuery->first();
406 offset = requestMsgsOffsetQuery->value(0).toUInt();
408 // now let's select the messages
409 requestMsgsQuery->bindValue(":bufferid", buffer.uid());
410 requestMsgsQuery->bindValue(":bufferid2", buffer.uid()); // Qt can't handle the same placeholder used twice
411 requestMsgsQuery->bindValue(":limit", lastmsgs);
412 requestMsgsQuery->bindValue(":offset", offset);
413 requestMsgsQuery->exec();
414 while(requestMsgsQuery->next()) {
415 Message msg(QDateTime::fromTime_t(requestMsgsQuery->value(1).toInt()),
417 (Message::Type)requestMsgsQuery->value(2).toUInt(),
418 requestMsgsQuery->value(5).toString(),
419 requestMsgsQuery->value(4).toString(),
420 requestMsgsQuery->value(3).toUInt());
421 msg.setMsgId(requestMsgsQuery->value(0).toUInt());
428 QList<Message> SqliteStorage::requestMsgs(BufferInfo buffer, QDateTime since, int offset) {
429 QList<Message> messagelist;
430 // we have to determine the real offset first
431 requestMsgsSinceOffsetQuery->bindValue(":bufferid", buffer.uid());
432 requestMsgsSinceOffsetQuery->bindValue(":since", since.toTime_t());
433 requestMsgsSinceOffsetQuery->exec();
434 requestMsgsSinceOffsetQuery->first();
435 offset = requestMsgsSinceOffsetQuery->value(0).toUInt();
437 // now let's select the messages
438 requestMsgsSinceQuery->bindValue(":bufferid", buffer.uid());
439 requestMsgsSinceQuery->bindValue(":bufferid2", buffer.uid());
440 requestMsgsSinceQuery->bindValue(":since", since.toTime_t());
441 requestMsgsSinceQuery->bindValue(":offset", offset);
442 requestMsgsSinceQuery->exec();
444 while(requestMsgsSinceQuery->next()) {
445 Message msg(QDateTime::fromTime_t(requestMsgsSinceQuery->value(1).toInt()),
447 (Message::Type)requestMsgsSinceQuery->value(2).toUInt(),
448 requestMsgsSinceQuery->value(5).toString(),
449 requestMsgsSinceQuery->value(4).toString(),
450 requestMsgsSinceQuery->value(3).toUInt());
451 msg.setMsgId(requestMsgsSinceQuery->value(0).toUInt());
459 QList<Message> SqliteStorage::requestMsgRange(BufferInfo buffer, int first, int last) {
460 QList<Message> messagelist;
461 requestMsgRangeQuery->bindValue(":bufferid", buffer.uid());
462 requestMsgRangeQuery->bindValue(":bufferid2", buffer.uid());
463 requestMsgRangeQuery->bindValue(":firstmsg", first);
464 requestMsgRangeQuery->bindValue(":lastmsg", last);
466 while(requestMsgRangeQuery->next()) {
467 Message msg(QDateTime::fromTime_t(requestMsgRangeQuery->value(1).toInt()),
469 (Message::Type)requestMsgRangeQuery->value(2).toUInt(),
470 requestMsgRangeQuery->value(5).toString(),
471 requestMsgRangeQuery->value(4).toString(),
472 requestMsgRangeQuery->value(3).toUInt());
473 msg.setMsgId(requestMsgRangeQuery->value(0).toUInt());
480 void SqliteStorage::importOldBacklog() {
481 QSqlQuery query(logDb);
483 query.prepare("SELECT MIN(userid) FROM quasseluser");
486 qDebug() << "create a user first!";
488 user = query.value(0).toUInt();
490 query.prepare("DELETE FROM backlog WHERE bufferid IN (SELECT DISTINCT bufferid FROM buffer WHERE userid = :userid");
491 query.bindValue(":userid", user);
493 query.prepare("DELETE FROM buffer WHERE userid = :userid");
494 query.bindValue(":userid", user);
496 query.prepare("DELETE FROM buffergroup WHERE userid = :userid");
497 query.bindValue(":userid", user);
499 query.prepare("DELETE FROM network WHERE userid = :userid");
500 query.bindValue(":userid", user);
503 qDebug() << "All userdata has been deleted";
504 qDebug() << "importing old backlog files...";
505 initBackLogOld(user);
511 bool SqliteStorage::watchQuery(QSqlQuery *query) {
512 if(query->lastError().isValid()) {
513 qWarning() << "unhandled Error in QSqlQuery!";
514 qWarning() << " last Query:" << query->lastQuery();
515 qWarning() << " executed Query:" << query->executedQuery();
516 qWarning() << " bound Values:" << query->boundValues();
517 qWarning() << " Error Number:" << query->lastError().number();
518 qWarning() << " Error Message:" << query->lastError().text();
519 qWarning() << " Driver Message:" << query->lastError().driverText();
520 qWarning() << " DB Message:" << query->lastError().databaseText();