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 // I think the class name is a good start here
191 return QString("SqliteStorage");
194 UserId SqliteStorage::addUser(QString user, QString password) {
195 QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
196 cryptopass = cryptopass.toHex();
198 QSqlQuery query(logDb);
199 query.prepare("INSERT INTO quasseluser (username, password) VALUES (:username, :password)");
200 query.bindValue(":username", user);
201 query.bindValue(":password", cryptopass);
203 if(query.lastError().isValid() && query.lastError().number() == 19) { // user already exists - sadly 19 seems to be the general constraint violation error...
207 query.prepare("SELECT userid FROM quasseluser WHERE username = :username");
208 query.bindValue(":username", user);
211 UserId uid = query.value(0).toUInt();
212 emit userAdded(uid, user);
216 void SqliteStorage::updateUser(UserId user, QString password) {
217 QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
218 cryptopass = cryptopass.toHex();
220 QSqlQuery query(logDb);
221 query.prepare("UPDATE quasseluser SET password = :password WHERE userid = :userid");
222 query.bindValue(":userid", user);
223 query.bindValue(":password", cryptopass);
227 void SqliteStorage::renameUser(UserId user, QString newName) {
228 QSqlQuery query(logDb);
229 query.prepare("UPDATE quasseluser SET username = :username WHERE userid = :userid");
230 query.bindValue(":userid", user);
231 query.bindValue(":username", newName);
233 emit userRenamed(user, newName);
236 UserId SqliteStorage::validateUser(QString user, QString password) {
237 QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
238 cryptopass = cryptopass.toHex();
240 QSqlQuery query(logDb);
241 query.prepare("SELECT userid FROM quasseluser WHERE username = :username AND password = :password");
242 query.bindValue(":username", user);
243 query.bindValue(":password", cryptopass);
247 return query.value(0).toUInt();
254 void SqliteStorage::delUser(UserId user) {
255 QSqlQuery query(logDb);
256 query.prepare("DELETE FROM backlog WHERE bufferid IN (SELECT DISTINCT bufferid FROM buffer WHERE userid = :userid");
257 query.bindValue(":userid", user);
259 query.prepare("DELETE FROM buffer WHERE userid = :userid");
260 query.bindValue(":userid", user);
262 query.prepare("DELETE FROM buffergroup WHERE userid = :userid");
263 query.bindValue(":userid", user);
265 query.prepare("DELETE FROM network WHERE userid = :userid");
266 query.bindValue(":userid", user);
268 query.prepare("DELETE FROM quasseluser WHERE userid = :userid");
269 query.bindValue(":userid", user);
271 // I hate the lack of foreign keys and on delete cascade... :(
272 emit userRemoved(user);
275 void SqliteStorage::createBuffer(UserId user, QString network, QString buffer) {
276 createBufferQuery->bindValue(":userid", user);
277 createBufferQuery->bindValue(":networkname", network);
278 createBufferQuery->bindValue(":buffername", buffer);
279 createBufferQuery->exec();
281 if(createBufferQuery->lastError().isValid()) {
282 if(createBufferQuery->lastError().number() == 19) { // Null Constraint violation
283 createNetworkQuery->bindValue(":userid", user);
284 createNetworkQuery->bindValue(":networkname", network);
285 createNetworkQuery->exec();
286 createBufferQuery->exec();
287 Q_ASSERT(!createNetworkQuery->lastError().isValid());
288 Q_ASSERT(!createBufferQuery->lastError().isValid());
291 qDebug() << "failed to create Buffer: ErrNo:" << createBufferQuery->lastError().number() << "ErrMsg:" << createBufferQuery->lastError().text();
297 BufferId SqliteStorage::getBufferId(UserId user, QString network, QString buffer) {
299 getBufferIdQuery->bindValue(":networkname", network);
300 getBufferIdQuery->bindValue(":userid", user);
301 getBufferIdQuery->bindValue(":buffername", buffer);
302 getBufferIdQuery->exec();
304 if(!getBufferIdQuery->first()) {
305 createBuffer(user, network, buffer);
306 getBufferIdQuery->exec();
307 if(getBufferIdQuery->first()) {
308 bufferid = BufferId(getBufferIdQuery->value(0).toUInt(), network, buffer);
309 emit bufferIdUpdated(bufferid);
312 bufferid = BufferId(getBufferIdQuery->value(0).toUInt(), network, buffer);
315 Q_ASSERT(!getBufferIdQuery->next());
320 QList<BufferId> SqliteStorage::requestBuffers(UserId user, QDateTime since) {
321 QList<BufferId> bufferlist;
322 QSqlQuery query(logDb);
323 query.prepare("SELECT DISTINCT buffer.bufferid, networkname, buffername FROM buffer "
324 "JOIN network ON buffer.networkid = network.networkid "
325 "JOIN backlog ON buffer.bufferid = backlog.bufferid "
326 "WHERE buffer.userid = :userid AND time >= :time");
327 query.bindValue(":userid", user);
328 if (since.isValid()) {
329 query.bindValue(":time", since.toTime_t());
331 query.bindValue(":time", 0);
336 while(query.next()) {
337 bufferlist << BufferId(query.value(0).toUInt(), query.value(1).toString(), query.value(2).toString());
342 MsgId SqliteStorage::logMessage(Message msg) {
343 logMessageQuery->bindValue(":time", msg.timeStamp.toTime_t());
344 logMessageQuery->bindValue(":bufferid", msg.buffer.uid());
345 logMessageQuery->bindValue(":type", msg.type);
346 logMessageQuery->bindValue(":flags", msg.flags);
347 logMessageQuery->bindValue(":sender", msg.sender);
348 logMessageQuery->bindValue(":message", msg.text);
349 logMessageQuery->exec();
351 if(logMessageQuery->lastError().isValid()) {
352 // constraint violation - must be NOT NULL constraint - probably the sender is missing...
353 if(logMessageQuery->lastError().number() == 19) {
354 addSenderQuery->bindValue(":sender", msg.sender);
355 addSenderQuery->exec();
356 logMessageQuery->exec();
357 Q_ASSERT(!logMessageQuery->lastError().isValid());
359 qDebug() << "unhandled DB Error in logMessage(): Number:" << logMessageQuery->lastError().number() << "ErrMsg:" << logMessageQuery->lastError().text();
363 getLastMessageIdQuery->bindValue(":time", msg.timeStamp.toTime_t());
364 getLastMessageIdQuery->bindValue(":bufferid", msg.buffer.uid());
365 getLastMessageIdQuery->bindValue(":type", msg.type);
366 getLastMessageIdQuery->bindValue(":sender", msg.sender);
367 getLastMessageIdQuery->exec();
369 if(getLastMessageIdQuery->first()) {
370 return getLastMessageIdQuery->value(0).toUInt();
371 } else { // somethin went wrong... :(
372 qDebug() << getLastMessageIdQuery->lastQuery() << "time/bufferid/type/sender:" << msg.timeStamp.toTime_t() << msg.buffer.uid() << msg.type << msg.sender;
378 QList<Message> SqliteStorage::requestMsgs(BufferId buffer, int lastmsgs, int offset) {
379 QList<Message> messagelist;
380 // we have to determine the real offset first
381 requestMsgsOffsetQuery->bindValue(":bufferid", buffer.uid());
382 requestMsgsOffsetQuery->bindValue(":messageid", offset);
383 requestMsgsOffsetQuery->exec();
384 requestMsgsOffsetQuery->first();
385 offset = requestMsgsOffsetQuery->value(0).toUInt();
387 // now let's select the messages
388 requestMsgsQuery->bindValue(":bufferid", buffer.uid());
389 requestMsgsQuery->bindValue(":bufferid2", buffer.uid()); // Qt can't handle the same placeholder used twice
390 requestMsgsQuery->bindValue(":limit", lastmsgs);
391 requestMsgsQuery->bindValue(":offset", offset);
392 requestMsgsQuery->exec();
393 while(requestMsgsQuery->next()) {
394 Message msg(QDateTime::fromTime_t(requestMsgsQuery->value(1).toInt()),
396 (Message::Type)requestMsgsQuery->value(2).toUInt(),
397 requestMsgsQuery->value(5).toString(),
398 requestMsgsQuery->value(4).toString(),
399 requestMsgsQuery->value(3).toUInt());
400 msg.msgId = requestMsgsQuery->value(0).toUInt();
407 QList<Message> SqliteStorage::requestMsgs(BufferId buffer, QDateTime since, int offset) {
408 QList<Message> messagelist;
409 // we have to determine the real offset first
410 requestMsgsSinceOffsetQuery->bindValue(":bufferid", buffer.uid());
411 requestMsgsSinceOffsetQuery->bindValue(":since", since.toTime_t());
412 requestMsgsSinceOffsetQuery->exec();
413 requestMsgsSinceOffsetQuery->first();
414 offset = requestMsgsSinceOffsetQuery->value(0).toUInt();
416 // now let's select the messages
417 requestMsgsSinceQuery->bindValue(":bufferid", buffer.uid());
418 requestMsgsSinceQuery->bindValue(":bufferid2", buffer.uid());
419 requestMsgsSinceQuery->bindValue(":since", since.toTime_t());
420 requestMsgsSinceQuery->bindValue(":offset", offset);
421 requestMsgsSinceQuery->exec();
423 while(requestMsgsSinceQuery->next()) {
424 Message msg(QDateTime::fromTime_t(requestMsgsSinceQuery->value(1).toInt()),
426 (Message::Type)requestMsgsSinceQuery->value(2).toUInt(),
427 requestMsgsSinceQuery->value(5).toString(),
428 requestMsgsSinceQuery->value(4).toString(),
429 requestMsgsSinceQuery->value(3).toUInt());
430 msg.msgId = requestMsgsSinceQuery->value(0).toUInt();
438 QList<Message> SqliteStorage::requestMsgRange(BufferId buffer, int first, int last) {
439 QList<Message> messagelist;
440 requestMsgRangeQuery->bindValue(":bufferid", buffer.uid());
441 requestMsgRangeQuery->bindValue(":bufferid2", buffer.uid());
442 requestMsgRangeQuery->bindValue(":firstmsg", first);
443 requestMsgRangeQuery->bindValue(":lastmsg", last);
445 while(requestMsgRangeQuery->next()) {
446 Message msg(QDateTime::fromTime_t(requestMsgRangeQuery->value(1).toInt()),
448 (Message::Type)requestMsgRangeQuery->value(2).toUInt(),
449 requestMsgRangeQuery->value(5).toString(),
450 requestMsgRangeQuery->value(4).toString(),
451 requestMsgRangeQuery->value(3).toUInt());
452 msg.msgId = requestMsgRangeQuery->value(0).toUInt();
459 void SqliteStorage::importOldBacklog() {
460 QSqlQuery query(logDb);
462 query.prepare("SELECT MIN(userid) FROM quasseluser");
465 qDebug() << "create a user first!";
467 user = query.value(0).toUInt();
469 query.prepare("DELETE FROM backlog WHERE bufferid IN (SELECT DISTINCT bufferid FROM buffer WHERE userid = :userid");
470 query.bindValue(":userid", user);
472 query.prepare("DELETE FROM buffer WHERE userid = :userid");
473 query.bindValue(":userid", user);
475 query.prepare("DELETE FROM buffergroup WHERE userid = :userid");
476 query.bindValue(":userid", user);
478 query.prepare("DELETE FROM network WHERE userid = :userid");
479 query.bindValue(":userid", user);
482 qDebug() << "All userdata has been deleted";
483 qDebug() << "importing old backlog files...";
484 initBackLogOld(user);