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 buffer.buffername = :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,"
166 "message TEXT 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 uint networkId = getNetworkId(user, network);
315 getBufferInfoQuery->bindValue(":networkname", network);
316 getBufferInfoQuery->bindValue(":userid", user);
317 getBufferInfoQuery->bindValue(":buffername", buffer);
318 getBufferInfoQuery->exec();
320 if(!getBufferInfoQuery->first()) {
321 createBuffer(user, network, buffer);
322 getBufferInfoQuery->exec();
323 if(getBufferInfoQuery->first()) {
324 bufferid = BufferInfo(getBufferInfoQuery->value(0).toUInt(), networkId, 0, network, buffer);
325 emit bufferInfoUpdated(bufferid);
328 bufferid = BufferInfo(getBufferInfoQuery->value(0).toUInt(), networkId, 0, network, buffer);
331 Q_ASSERT(!getBufferInfoQuery->next());
336 QList<BufferInfo> SqliteStorage::requestBuffers(UserId user, QDateTime since) {
337 QList<BufferInfo> bufferlist;
338 QSqlQuery query(logDb);
339 query.prepare("SELECT DISTINCT buffer.bufferid, networkname, buffername FROM buffer "
340 "JOIN network ON buffer.networkid = network.networkid "
341 "JOIN backlog ON buffer.bufferid = backlog.bufferid "
342 "WHERE buffer.userid = :userid AND time >= :time");
343 query.bindValue(":userid", user);
344 if (since.isValid()) {
345 query.bindValue(":time", since.toTime_t());
347 query.bindValue(":time", 0);
352 while(query.next()) {
353 bufferlist << BufferInfo(query.value(0).toUInt(), getNetworkId(user, query.value(1).toString()), 0, query.value(1).toString(), query.value(2).toString());
358 MsgId SqliteStorage::logMessage(Message msg) {
359 logMessageQuery->bindValue(":time", msg.timestamp().toTime_t());
360 logMessageQuery->bindValue(":bufferid", msg.buffer().uid());
361 logMessageQuery->bindValue(":type", msg.type());
362 logMessageQuery->bindValue(":flags", msg.flags());
363 logMessageQuery->bindValue(":sender", msg.sender());
364 logMessageQuery->bindValue(":message", msg.text());
365 logMessageQuery->exec();
367 if(logMessageQuery->lastError().isValid()) {
368 // constraint violation - must be NOT NULL constraint - probably the sender is missing...
369 if(logMessageQuery->lastError().number() == 19) {
370 addSenderQuery->bindValue(":sender", msg.sender());
371 addSenderQuery->exec();
372 watchQuery(addSenderQuery);
373 logMessageQuery->exec();
374 if(!watchQuery(logMessageQuery))
377 watchQuery(logMessageQuery);
381 getLastMessageIdQuery->bindValue(":time", msg.timestamp().toTime_t());
382 getLastMessageIdQuery->bindValue(":bufferid", msg.buffer().uid());
383 getLastMessageIdQuery->bindValue(":type", msg.type());
384 getLastMessageIdQuery->bindValue(":sender", msg.sender());
385 getLastMessageIdQuery->exec();
387 if(getLastMessageIdQuery->first()) {
388 return getLastMessageIdQuery->value(0).toUInt();
389 } else { // somethin went wrong... :(
390 qDebug() << getLastMessageIdQuery->lastQuery() << "time/bufferid/type/sender:" << msg.timestamp().toTime_t() << msg.buffer().uid() << msg.type() << msg.sender();
396 QList<Message> SqliteStorage::requestMsgs(BufferInfo buffer, int lastmsgs, int offset) {
397 QList<Message> messagelist;
398 // we have to determine the real offset first
399 requestMsgsOffsetQuery->bindValue(":bufferid", buffer.uid());
400 requestMsgsOffsetQuery->bindValue(":messageid", offset);
401 requestMsgsOffsetQuery->exec();
402 requestMsgsOffsetQuery->first();
403 offset = requestMsgsOffsetQuery->value(0).toUInt();
405 // now let's select the messages
406 requestMsgsQuery->bindValue(":bufferid", buffer.uid());
407 requestMsgsQuery->bindValue(":bufferid2", buffer.uid()); // Qt can't handle the same placeholder used twice
408 requestMsgsQuery->bindValue(":limit", lastmsgs);
409 requestMsgsQuery->bindValue(":offset", offset);
410 requestMsgsQuery->exec();
411 while(requestMsgsQuery->next()) {
412 Message msg(QDateTime::fromTime_t(requestMsgsQuery->value(1).toInt()),
414 (Message::Type)requestMsgsQuery->value(2).toUInt(),
415 requestMsgsQuery->value(5).toString(),
416 requestMsgsQuery->value(4).toString(),
417 requestMsgsQuery->value(3).toUInt());
418 msg.setMsgId(requestMsgsQuery->value(0).toUInt());
425 QList<Message> SqliteStorage::requestMsgs(BufferInfo buffer, QDateTime since, int offset) {
426 QList<Message> messagelist;
427 // we have to determine the real offset first
428 requestMsgsSinceOffsetQuery->bindValue(":bufferid", buffer.uid());
429 requestMsgsSinceOffsetQuery->bindValue(":since", since.toTime_t());
430 requestMsgsSinceOffsetQuery->exec();
431 requestMsgsSinceOffsetQuery->first();
432 offset = requestMsgsSinceOffsetQuery->value(0).toUInt();
434 // now let's select the messages
435 requestMsgsSinceQuery->bindValue(":bufferid", buffer.uid());
436 requestMsgsSinceQuery->bindValue(":bufferid2", buffer.uid());
437 requestMsgsSinceQuery->bindValue(":since", since.toTime_t());
438 requestMsgsSinceQuery->bindValue(":offset", offset);
439 requestMsgsSinceQuery->exec();
441 while(requestMsgsSinceQuery->next()) {
442 Message msg(QDateTime::fromTime_t(requestMsgsSinceQuery->value(1).toInt()),
444 (Message::Type)requestMsgsSinceQuery->value(2).toUInt(),
445 requestMsgsSinceQuery->value(5).toString(),
446 requestMsgsSinceQuery->value(4).toString(),
447 requestMsgsSinceQuery->value(3).toUInt());
448 msg.setMsgId(requestMsgsSinceQuery->value(0).toUInt());
456 QList<Message> SqliteStorage::requestMsgRange(BufferInfo buffer, int first, int last) {
457 QList<Message> messagelist;
458 requestMsgRangeQuery->bindValue(":bufferid", buffer.uid());
459 requestMsgRangeQuery->bindValue(":bufferid2", buffer.uid());
460 requestMsgRangeQuery->bindValue(":firstmsg", first);
461 requestMsgRangeQuery->bindValue(":lastmsg", last);
463 while(requestMsgRangeQuery->next()) {
464 Message msg(QDateTime::fromTime_t(requestMsgRangeQuery->value(1).toInt()),
466 (Message::Type)requestMsgRangeQuery->value(2).toUInt(),
467 requestMsgRangeQuery->value(5).toString(),
468 requestMsgRangeQuery->value(4).toString(),
469 requestMsgRangeQuery->value(3).toUInt());
470 msg.setMsgId(requestMsgRangeQuery->value(0).toUInt());
477 void SqliteStorage::importOldBacklog() {
478 QSqlQuery query(logDb);
480 query.prepare("SELECT MIN(userid) FROM quasseluser");
483 qDebug() << "create a user first!";
485 user = query.value(0).toUInt();
487 query.prepare("DELETE FROM backlog WHERE bufferid IN (SELECT DISTINCT bufferid FROM buffer WHERE userid = :userid");
488 query.bindValue(":userid", user);
490 query.prepare("DELETE FROM buffer WHERE userid = :userid");
491 query.bindValue(":userid", user);
493 query.prepare("DELETE FROM buffergroup WHERE userid = :userid");
494 query.bindValue(":userid", user);
496 query.prepare("DELETE FROM network WHERE userid = :userid");
497 query.bindValue(":userid", user);
500 qDebug() << "All userdata has been deleted";
501 qDebug() << "importing old backlog files...";
502 initBackLogOld(user);
508 bool SqliteStorage::watchQuery(QSqlQuery *query) {
509 if(query->lastError().isValid()) {
510 qWarning() << "unhandled Error in QSqlQuery!";
511 qWarning() << " last Query:" << query->lastQuery();
512 qWarning() << " executed Query:" << query->executedQuery();
513 qWarning() << " bound Values:" << query->boundValues();
514 qWarning() << " Error Number:" << query->lastError().number();
515 qWarning() << " Error Message:" << query->lastError().text();
516 qWarning() << " Driver Message:" << query->lastError().driverText();
517 qWarning() << " DB Message:" << query->lastError().databaseText();