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 getBufferIdQuery = new QSqlQuery(logDb);
57 getBufferIdQuery->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 getBufferIdQuery;
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
184 bool SqliteStorage::isAvailable() {
185 if(!QSqlDatabase::isDriverAvailable("QSQLITE")) return false;
189 QString SqliteStorage::displayName() {
190 return QString("SqliteStorage");
193 UserId SqliteStorage::addUser(const QString &user, const QString &password) {
194 QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
195 cryptopass = cryptopass.toHex();
197 QSqlQuery query(logDb);
198 query.prepare("INSERT INTO quasseluser (username, password) VALUES (:username, :password)");
199 query.bindValue(":username", user);
200 query.bindValue(":password", cryptopass);
202 if(query.lastError().isValid() && query.lastError().number() == 19) { // user already exists - sadly 19 seems to be the general constraint violation error...
206 query.prepare("SELECT userid FROM quasseluser WHERE username = :username");
207 query.bindValue(":username", user);
210 UserId uid = query.value(0).toUInt();
211 emit userAdded(uid, user);
215 void SqliteStorage::updateUser(UserId user, const QString &password) {
216 QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
217 cryptopass = cryptopass.toHex();
219 QSqlQuery query(logDb);
220 query.prepare("UPDATE quasseluser SET password = :password WHERE userid = :userid");
221 query.bindValue(":userid", user);
222 query.bindValue(":password", cryptopass);
226 void SqliteStorage::renameUser(UserId user, const QString &newName) {
227 QSqlQuery query(logDb);
228 query.prepare("UPDATE quasseluser SET username = :username WHERE userid = :userid");
229 query.bindValue(":userid", user);
230 query.bindValue(":username", newName);
232 emit userRenamed(user, newName);
235 UserId SqliteStorage::validateUser(const QString &user, const QString &password) {
236 QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
237 cryptopass = cryptopass.toHex();
239 QSqlQuery query(logDb);
240 query.prepare("SELECT userid FROM quasseluser WHERE username = :username AND password = :password");
241 query.bindValue(":username", user);
242 query.bindValue(":password", cryptopass);
246 return query.value(0).toUInt();
253 void SqliteStorage::delUser(UserId user) {
254 QSqlQuery query(logDb);
255 query.prepare("DELETE FROM backlog WHERE bufferid IN (SELECT DISTINCT bufferid FROM buffer WHERE userid = :userid");
256 query.bindValue(":userid", user);
258 query.prepare("DELETE FROM buffer WHERE userid = :userid");
259 query.bindValue(":userid", user);
261 query.prepare("DELETE FROM buffergroup WHERE userid = :userid");
262 query.bindValue(":userid", user);
264 query.prepare("DELETE FROM network WHERE userid = :userid");
265 query.bindValue(":userid", user);
267 query.prepare("DELETE FROM quasseluser WHERE userid = :userid");
268 query.bindValue(":userid", user);
270 // I hate the lack of foreign keys and on delete cascade... :(
271 emit userRemoved(user);
274 void SqliteStorage::createBuffer(UserId user, const QString &network, const QString &buffer) {
275 createBufferQuery->bindValue(":userid", user);
276 createBufferQuery->bindValue(":networkname", network);
277 createBufferQuery->bindValue(":buffername", buffer);
278 createBufferQuery->exec();
280 if(createBufferQuery->lastError().isValid()) {
281 if(createBufferQuery->lastError().number() == 19) { // Null Constraint violation
282 createNetworkQuery->bindValue(":userid", user);
283 createNetworkQuery->bindValue(":networkname", network);
284 createNetworkQuery->exec();
285 createBufferQuery->exec();
286 Q_ASSERT(!createNetworkQuery->lastError().isValid());
287 Q_ASSERT(!createBufferQuery->lastError().isValid());
290 qDebug() << "failed to create Buffer: ErrNo:" << createBufferQuery->lastError().number() << "ErrMsg:" << createBufferQuery->lastError().text();
296 uint SqliteStorage::getNetworkId(UserId user, const QString &network) {
297 QSqlQuery query(logDb);
298 query.prepare("SELECT networkid FROM network "
299 "WHERE userid = :userid AND networkname = :networkname");
300 query.bindValue(":userid", user);
301 query.bindValue(":networkname", network);
305 return query.value(0).toUInt();
310 BufferId SqliteStorage::getBufferId(UserId user, const QString &network, const QString &buffer) {
312 getBufferIdQuery->bindValue(":networkname", network);
313 getBufferIdQuery->bindValue(":userid", user);
314 getBufferIdQuery->bindValue(":buffername", buffer);
315 getBufferIdQuery->exec();
317 if(!getBufferIdQuery->first()) {
318 createBuffer(user, network, buffer);
319 getBufferIdQuery->exec();
320 if(getBufferIdQuery->first()) {
321 bufferid = BufferId(getBufferIdQuery->value(0).toUInt(), network, buffer);
322 emit bufferIdUpdated(bufferid);
325 bufferid = BufferId(getBufferIdQuery->value(0).toUInt(), network, buffer);
328 Q_ASSERT(!getBufferIdQuery->next());
333 QList<BufferId> SqliteStorage::requestBuffers(UserId user, QDateTime since) {
334 QList<BufferId> bufferlist;
335 QSqlQuery query(logDb);
336 query.prepare("SELECT DISTINCT buffer.bufferid, networkname, buffername FROM buffer "
337 "JOIN network ON buffer.networkid = network.networkid "
338 "JOIN backlog ON buffer.bufferid = backlog.bufferid "
339 "WHERE buffer.userid = :userid AND time >= :time");
340 query.bindValue(":userid", user);
341 if (since.isValid()) {
342 query.bindValue(":time", since.toTime_t());
344 query.bindValue(":time", 0);
349 while(query.next()) {
350 bufferlist << BufferId(query.value(0).toUInt(), query.value(1).toString(), query.value(2).toString());
355 MsgId SqliteStorage::logMessage(Message msg) {
356 logMessageQuery->bindValue(":time", msg.timeStamp.toTime_t());
357 logMessageQuery->bindValue(":bufferid", msg.buffer.uid());
358 logMessageQuery->bindValue(":type", msg.type);
359 logMessageQuery->bindValue(":flags", msg.flags);
360 logMessageQuery->bindValue(":sender", msg.sender);
361 logMessageQuery->bindValue(":message", msg.text);
362 logMessageQuery->exec();
364 if(logMessageQuery->lastError().isValid()) {
365 // constraint violation - must be NOT NULL constraint - probably the sender is missing...
366 if(logMessageQuery->lastError().number() == 19) {
367 addSenderQuery->bindValue(":sender", msg.sender);
368 addSenderQuery->exec();
369 logMessageQuery->exec();
370 Q_ASSERT(!logMessageQuery->lastError().isValid());
372 qDebug() << "unhandled DB Error in logMessage(): Number:" << logMessageQuery->lastError().number() << "ErrMsg:" << logMessageQuery->lastError().text();
376 getLastMessageIdQuery->bindValue(":time", msg.timeStamp.toTime_t());
377 getLastMessageIdQuery->bindValue(":bufferid", msg.buffer.uid());
378 getLastMessageIdQuery->bindValue(":type", msg.type);
379 getLastMessageIdQuery->bindValue(":sender", msg.sender);
380 getLastMessageIdQuery->exec();
382 if(getLastMessageIdQuery->first()) {
383 return getLastMessageIdQuery->value(0).toUInt();
384 } else { // somethin went wrong... :(
385 qDebug() << getLastMessageIdQuery->lastQuery() << "time/bufferid/type/sender:" << msg.timeStamp.toTime_t() << msg.buffer.uid() << msg.type << msg.sender;
391 QList<Message> SqliteStorage::requestMsgs(BufferId buffer, int lastmsgs, int offset) {
392 QList<Message> messagelist;
393 // we have to determine the real offset first
394 requestMsgsOffsetQuery->bindValue(":bufferid", buffer.uid());
395 requestMsgsOffsetQuery->bindValue(":messageid", offset);
396 requestMsgsOffsetQuery->exec();
397 requestMsgsOffsetQuery->first();
398 offset = requestMsgsOffsetQuery->value(0).toUInt();
400 // now let's select the messages
401 requestMsgsQuery->bindValue(":bufferid", buffer.uid());
402 requestMsgsQuery->bindValue(":bufferid2", buffer.uid()); // Qt can't handle the same placeholder used twice
403 requestMsgsQuery->bindValue(":limit", lastmsgs);
404 requestMsgsQuery->bindValue(":offset", offset);
405 requestMsgsQuery->exec();
406 while(requestMsgsQuery->next()) {
407 Message msg(QDateTime::fromTime_t(requestMsgsQuery->value(1).toInt()),
409 (Message::Type)requestMsgsQuery->value(2).toUInt(),
410 requestMsgsQuery->value(5).toString(),
411 requestMsgsQuery->value(4).toString(),
412 requestMsgsQuery->value(3).toUInt());
413 msg.msgId = requestMsgsQuery->value(0).toUInt();
420 QList<Message> SqliteStorage::requestMsgs(BufferId buffer, QDateTime since, int offset) {
421 QList<Message> messagelist;
422 // we have to determine the real offset first
423 requestMsgsSinceOffsetQuery->bindValue(":bufferid", buffer.uid());
424 requestMsgsSinceOffsetQuery->bindValue(":since", since.toTime_t());
425 requestMsgsSinceOffsetQuery->exec();
426 requestMsgsSinceOffsetQuery->first();
427 offset = requestMsgsSinceOffsetQuery->value(0).toUInt();
429 // now let's select the messages
430 requestMsgsSinceQuery->bindValue(":bufferid", buffer.uid());
431 requestMsgsSinceQuery->bindValue(":bufferid2", buffer.uid());
432 requestMsgsSinceQuery->bindValue(":since", since.toTime_t());
433 requestMsgsSinceQuery->bindValue(":offset", offset);
434 requestMsgsSinceQuery->exec();
436 while(requestMsgsSinceQuery->next()) {
437 Message msg(QDateTime::fromTime_t(requestMsgsSinceQuery->value(1).toInt()),
439 (Message::Type)requestMsgsSinceQuery->value(2).toUInt(),
440 requestMsgsSinceQuery->value(5).toString(),
441 requestMsgsSinceQuery->value(4).toString(),
442 requestMsgsSinceQuery->value(3).toUInt());
443 msg.msgId = requestMsgsSinceQuery->value(0).toUInt();
451 QList<Message> SqliteStorage::requestMsgRange(BufferId buffer, int first, int last) {
452 QList<Message> messagelist;
453 requestMsgRangeQuery->bindValue(":bufferid", buffer.uid());
454 requestMsgRangeQuery->bindValue(":bufferid2", buffer.uid());
455 requestMsgRangeQuery->bindValue(":firstmsg", first);
456 requestMsgRangeQuery->bindValue(":lastmsg", last);
458 while(requestMsgRangeQuery->next()) {
459 Message msg(QDateTime::fromTime_t(requestMsgRangeQuery->value(1).toInt()),
461 (Message::Type)requestMsgRangeQuery->value(2).toUInt(),
462 requestMsgRangeQuery->value(5).toString(),
463 requestMsgRangeQuery->value(4).toString(),
464 requestMsgRangeQuery->value(3).toUInt());
465 msg.msgId = requestMsgRangeQuery->value(0).toUInt();
472 void SqliteStorage::importOldBacklog() {
473 QSqlQuery query(logDb);
475 query.prepare("SELECT MIN(userid) FROM quasseluser");
478 qDebug() << "create a user first!";
480 user = query.value(0).toUInt();
482 query.prepare("DELETE FROM backlog WHERE bufferid IN (SELECT DISTINCT bufferid FROM buffer WHERE userid = :userid");
483 query.bindValue(":userid", user);
485 query.prepare("DELETE FROM buffer WHERE userid = :userid");
486 query.bindValue(":userid", user);
488 query.prepare("DELETE FROM buffergroup WHERE userid = :userid");
489 query.bindValue(":userid", user);
491 query.prepare("DELETE FROM network WHERE userid = :userid");
492 query.bindValue(":userid", user);
495 qDebug() << "All userdata has been deleted";
496 qDebug() << "importing old backlog files...";
497 initBackLogOld(user);