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 uint networkId = getNetworkId(user, network);
313 getBufferIdQuery->bindValue(":networkname", network);
314 getBufferIdQuery->bindValue(":userid", user);
315 getBufferIdQuery->bindValue(":buffername", buffer);
316 getBufferIdQuery->exec();
318 if(!getBufferIdQuery->first()) {
319 createBuffer(user, network, buffer);
320 getBufferIdQuery->exec();
321 if(getBufferIdQuery->first()) {
322 bufferid = BufferId(getBufferIdQuery->value(0).toUInt(), networkId, 0, network, buffer);
323 emit bufferIdUpdated(bufferid);
326 bufferid = BufferId(getBufferIdQuery->value(0).toUInt(), networkId, 0, network, buffer);
329 Q_ASSERT(!getBufferIdQuery->next());
334 QList<BufferId> SqliteStorage::requestBuffers(UserId user, QDateTime since) {
335 QList<BufferId> bufferlist;
336 QSqlQuery query(logDb);
337 query.prepare("SELECT DISTINCT buffer.bufferid, networkname, buffername FROM buffer "
338 "JOIN network ON buffer.networkid = network.networkid "
339 "JOIN backlog ON buffer.bufferid = backlog.bufferid "
340 "WHERE buffer.userid = :userid AND time >= :time");
341 query.bindValue(":userid", user);
342 if (since.isValid()) {
343 query.bindValue(":time", since.toTime_t());
345 query.bindValue(":time", 0);
350 while(query.next()) {
351 bufferlist << BufferId(query.value(0).toUInt(), getNetworkId(user, query.value(1).toString()), 0, query.value(1).toString(), query.value(2).toString());
356 MsgId SqliteStorage::logMessage(Message msg) {
357 logMessageQuery->bindValue(":time", msg.timeStamp().toTime_t());
358 logMessageQuery->bindValue(":bufferid", msg.buffer().uid());
359 logMessageQuery->bindValue(":type", msg.type());
360 logMessageQuery->bindValue(":flags", msg.flags());
361 logMessageQuery->bindValue(":sender", msg.sender());
362 logMessageQuery->bindValue(":message", msg.text());
363 logMessageQuery->exec();
365 if(logMessageQuery->lastError().isValid()) {
366 // constraint violation - must be NOT NULL constraint - probably the sender is missing...
367 if(logMessageQuery->lastError().number() == 19) {
368 addSenderQuery->bindValue(":sender", msg.sender());
369 addSenderQuery->exec();
370 logMessageQuery->exec();
371 Q_ASSERT(!logMessageQuery->lastError().isValid());
373 qDebug() << "unhandled DB Error in logMessage(): Number:" << logMessageQuery->lastError().number() << "ErrMsg:" << logMessageQuery->lastError().text();
377 getLastMessageIdQuery->bindValue(":time", msg.timeStamp().toTime_t());
378 getLastMessageIdQuery->bindValue(":bufferid", msg.buffer().uid());
379 getLastMessageIdQuery->bindValue(":type", msg.type());
380 getLastMessageIdQuery->bindValue(":sender", msg.sender());
381 getLastMessageIdQuery->exec();
383 if(getLastMessageIdQuery->first()) {
384 return getLastMessageIdQuery->value(0).toUInt();
385 } else { // somethin went wrong... :(
386 qDebug() << getLastMessageIdQuery->lastQuery() << "time/bufferid/type/sender:" << msg.timeStamp().toTime_t() << msg.buffer().uid() << msg.type() << msg.sender();
392 QList<Message> SqliteStorage::requestMsgs(BufferId buffer, int lastmsgs, int offset) {
393 QList<Message> messagelist;
394 // we have to determine the real offset first
395 requestMsgsOffsetQuery->bindValue(":bufferid", buffer.uid());
396 requestMsgsOffsetQuery->bindValue(":messageid", offset);
397 requestMsgsOffsetQuery->exec();
398 requestMsgsOffsetQuery->first();
399 offset = requestMsgsOffsetQuery->value(0).toUInt();
401 // now let's select the messages
402 requestMsgsQuery->bindValue(":bufferid", buffer.uid());
403 requestMsgsQuery->bindValue(":bufferid2", buffer.uid()); // Qt can't handle the same placeholder used twice
404 requestMsgsQuery->bindValue(":limit", lastmsgs);
405 requestMsgsQuery->bindValue(":offset", offset);
406 requestMsgsQuery->exec();
407 while(requestMsgsQuery->next()) {
408 Message msg(QDateTime::fromTime_t(requestMsgsQuery->value(1).toInt()),
410 (Message::Type)requestMsgsQuery->value(2).toUInt(),
411 requestMsgsQuery->value(5).toString(),
412 requestMsgsQuery->value(4).toString(),
413 requestMsgsQuery->value(3).toUInt());
414 msg.setMsgId(requestMsgsQuery->value(0).toUInt());
421 QList<Message> SqliteStorage::requestMsgs(BufferId buffer, QDateTime since, int offset) {
422 QList<Message> messagelist;
423 // we have to determine the real offset first
424 requestMsgsSinceOffsetQuery->bindValue(":bufferid", buffer.uid());
425 requestMsgsSinceOffsetQuery->bindValue(":since", since.toTime_t());
426 requestMsgsSinceOffsetQuery->exec();
427 requestMsgsSinceOffsetQuery->first();
428 offset = requestMsgsSinceOffsetQuery->value(0).toUInt();
430 // now let's select the messages
431 requestMsgsSinceQuery->bindValue(":bufferid", buffer.uid());
432 requestMsgsSinceQuery->bindValue(":bufferid2", buffer.uid());
433 requestMsgsSinceQuery->bindValue(":since", since.toTime_t());
434 requestMsgsSinceQuery->bindValue(":offset", offset);
435 requestMsgsSinceQuery->exec();
437 while(requestMsgsSinceQuery->next()) {
438 Message msg(QDateTime::fromTime_t(requestMsgsSinceQuery->value(1).toInt()),
440 (Message::Type)requestMsgsSinceQuery->value(2).toUInt(),
441 requestMsgsSinceQuery->value(5).toString(),
442 requestMsgsSinceQuery->value(4).toString(),
443 requestMsgsSinceQuery->value(3).toUInt());
444 msg.setMsgId(requestMsgsSinceQuery->value(0).toUInt());
452 QList<Message> SqliteStorage::requestMsgRange(BufferId buffer, int first, int last) {
453 QList<Message> messagelist;
454 requestMsgRangeQuery->bindValue(":bufferid", buffer.uid());
455 requestMsgRangeQuery->bindValue(":bufferid2", buffer.uid());
456 requestMsgRangeQuery->bindValue(":firstmsg", first);
457 requestMsgRangeQuery->bindValue(":lastmsg", last);
459 while(requestMsgRangeQuery->next()) {
460 Message msg(QDateTime::fromTime_t(requestMsgRangeQuery->value(1).toInt()),
462 (Message::Type)requestMsgRangeQuery->value(2).toUInt(),
463 requestMsgRangeQuery->value(5).toString(),
464 requestMsgRangeQuery->value(4).toString(),
465 requestMsgRangeQuery->value(3).toUInt());
466 msg.setMsgId(requestMsgRangeQuery->value(0).toUInt());
473 void SqliteStorage::importOldBacklog() {
474 QSqlQuery query(logDb);
476 query.prepare("SELECT MIN(userid) FROM quasseluser");
479 qDebug() << "create a user first!";
481 user = query.value(0).toUInt();
483 query.prepare("DELETE FROM backlog WHERE bufferid IN (SELECT DISTINCT bufferid FROM buffer WHERE userid = :userid");
484 query.bindValue(":userid", user);
486 query.prepare("DELETE FROM buffer WHERE userid = :userid");
487 query.bindValue(":userid", user);
489 query.prepare("DELETE FROM buffergroup WHERE userid = :userid");
490 query.bindValue(":userid", user);
492 query.prepare("DELETE FROM network WHERE userid = :userid");
493 query.bindValue(":userid", user);
496 qDebug() << "All userdata has been deleted";
497 qDebug() << "importing old backlog files...";
498 initBackLogOld(user);