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() {
112 delete logMessageQuery;
113 delete addSenderQuery;
114 delete getLastMessageIdQuery;
115 delete requestMsgsQuery;
116 delete requestMsgsOffsetQuery;
117 delete requestMsgsSinceQuery;
118 delete requestMsgsSinceOffsetQuery;
119 delete requestMsgRangeQuery;
120 delete createNetworkQuery;
121 delete createBufferQuery;
122 delete getBufferIdQuery;
127 void SqliteStorage::initDb() {
128 logDb.exec("CREATE TABLE quasseluser ("
129 "userid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
130 "username TEXT UNIQUE NOT NULL,"
131 "password BLOB NOT NULL)");
133 logDb.exec("CREATE TABLE sender ("
134 "senderid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
135 "sender TEXT UNIQUE NOT NULL)");
137 logDb.exec("CREATE TABLE network ("
138 "networkid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
139 "userid INTEGER NOT NULL,"
140 "networkname TEXT NOT NULL,"
141 "UNIQUE (userid, networkname))");
143 logDb.exec("CREATE TABLE buffergroup ("
144 "groupid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
145 "userid INTEGER NOT NULL,"
146 "displayname TEXT)");
148 logDb.exec("CREATE TABLE buffer ("
149 "bufferid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
150 "userid INTEGER NOT NULL,"
152 "networkid INTEGER NOT NULL,"
153 "buffername TEXT NOT NULL)");
155 logDb.exec("CREATE UNIQUE INDEX buffer_idx "
156 "ON buffer(userid, networkid, buffername)");
158 logDb.exec("CREATE TABLE backlog ("
159 "messageid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
160 "time INTEGER NOT NULL,"
161 "bufferid INTEGER NOT NULL,"
162 "type INTEGER NOT NULL,"
163 "flags INTEGER NOT NULL,"
164 "senderid INTEGER NOT NULL,"
165 "message TEXT NOT NULL)");
167 logDb.exec("CREATE TABLE coreinfo ("
168 "updateid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
169 "version INTEGER NOT NULL)");
171 logDb.exec("INSERT INTO coreinfo (version) VALUES (0)");
174 // something fucked up -> no logging possible
175 // FIXME logDb.lastError is reset whenever exec is called
176 if(logDb.lastError().isValid()) {
177 qWarning(tr("Could not create backlog table: %1").arg(logDb.lastError().text()).toAscii());
178 qWarning(tr("Disabling logging...").toAscii());
179 Q_ASSERT(false); // quassel does require logging
183 bool SqliteStorage::isAvailable() {
184 if(!QSqlDatabase::isDriverAvailable("QSQLITE")) return false;
188 QString SqliteStorage::displayName() {
189 // I think the class name is a good start here
190 return QString("SqliteStorage");
193 UserId SqliteStorage::addUser(QString user, 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 return query.value(0).toUInt();
213 void SqliteStorage::updateUser(UserId user, QString password) {
214 QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
215 cryptopass = cryptopass.toHex();
217 QSqlQuery query(logDb);
218 query.prepare("UPDATE quasseluser SET password = :password WHERE userid = :userid");
219 query.bindValue(":userid", user);
220 query.bindValue(":password", cryptopass);
224 UserId SqliteStorage::validateUser(QString user, QString password) {
225 QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
226 cryptopass = cryptopass.toHex();
228 QSqlQuery query(logDb);
229 query.prepare("SELECT userid FROM quasseluser WHERE username = :username AND password = :password");
230 query.bindValue(":username", user);
231 query.bindValue(":password", cryptopass);
235 return query.value(0).toUInt();
242 void SqliteStorage::delUser(UserId user) {
243 QSqlQuery query(logDb);
244 query.prepare("DELETE FROM backlog WHERE bufferid IN (SELECT DISTINCT bufferid FROM buffer WHERE userid = :userid");
245 query.bindValue(":userid", user);
247 query.prepare("DELETE FROM buffer WHERE userid = :userid");
248 query.bindValue(":userid", user);
250 query.prepare("DELETE FROM buffergroup WHERE userid = :userid");
251 query.bindValue(":userid", user);
253 query.prepare("DELETE FROM network WHERE userid = :userid");
254 query.bindValue(":userid", user);
256 query.prepare("DELETE FROM quasseluser WHERE userid = :userid");
257 query.bindValue(":userid", user);
259 // I hate the lack of foreign keys and on delete cascade... :(
262 void SqliteStorage::createBuffer(UserId user, QString network, QString buffer) {
263 createBufferQuery->bindValue(":userid", user);
264 createBufferQuery->bindValue(":networkname", network);
265 createBufferQuery->bindValue(":buffername", buffer);
266 createBufferQuery->exec();
268 if(createBufferQuery->lastError().isValid()) {
269 if(createBufferQuery->lastError().number() == 19) { // Null Constraint violation
270 createNetworkQuery->bindValue(":userid", user);
271 createNetworkQuery->bindValue(":networkname", network);
272 createNetworkQuery->exec();
273 createBufferQuery->exec();
274 Q_ASSERT(!createNetworkQuery->lastError().isValid());
275 Q_ASSERT(!createBufferQuery->lastError().isValid());
278 qDebug() << "failed to create Buffer: ErrNo:" << createBufferQuery->lastError().number() << "ErrMsg:" << createBufferQuery->lastError().text();
284 BufferId SqliteStorage::getBufferId(UserId user, QString network, QString buffer) {
286 getBufferIdQuery->bindValue(":networkname", network);
287 getBufferIdQuery->bindValue(":userid", user);
288 getBufferIdQuery->bindValue(":buffername", buffer);
289 getBufferIdQuery->exec();
291 if(!getBufferIdQuery->first()) {
292 createBuffer(user, network, buffer);
293 getBufferIdQuery->exec();
294 if(getBufferIdQuery->first()) {
295 bufferid = BufferId(getBufferIdQuery->value(0).toUInt(), network, buffer);
296 emit bufferIdUpdated(bufferid);
299 bufferid = BufferId(getBufferIdQuery->value(0).toUInt(), network, buffer);
302 Q_ASSERT(!getBufferIdQuery->next());
307 QList<BufferId> SqliteStorage::requestBuffers(UserId user, QDateTime since) {
308 QList<BufferId> bufferlist;
309 QSqlQuery query(logDb);
310 query.prepare("SELECT DISTINCT buffer.bufferid, networkname, buffername FROM buffer "
311 "JOIN network ON buffer.networkid = network.networkid "
312 "JOIN backlog ON buffer.bufferid = backlog.bufferid "
313 "WHERE buffer.userid = :userid AND time >= :time");
314 query.bindValue(":userid", user);
315 if (since.isValid()) {
316 query.bindValue(":time", since.toTime_t());
318 query.bindValue(":time", 0);
323 while(query.next()) {
324 bufferlist << BufferId(query.value(0).toUInt(), query.value(1).toString(), query.value(2).toString());
329 MsgId SqliteStorage::logMessage(Message msg) {
330 logMessageQuery->bindValue(":time", msg.timeStamp.toTime_t());
331 logMessageQuery->bindValue(":bufferid", msg.buffer.uid());
332 logMessageQuery->bindValue(":type", msg.type);
333 logMessageQuery->bindValue(":flags", msg.flags);
334 logMessageQuery->bindValue(":sender", msg.sender);
335 logMessageQuery->bindValue(":message", msg.text);
336 logMessageQuery->exec();
338 if(logMessageQuery->lastError().isValid()) {
339 // constraint violation - must be NOT NULL constraint - probably the sender is missing...
340 if(logMessageQuery->lastError().number() == 19) {
341 addSenderQuery->bindValue(":sender", msg.sender);
342 addSenderQuery->exec();
343 logMessageQuery->exec();
344 Q_ASSERT(!logMessageQuery->lastError().isValid());
346 qDebug() << "unhandled DB Error in logMessage(): Number:" << logMessageQuery->lastError().number() << "ErrMsg:" << logMessageQuery->lastError().text();
350 getLastMessageIdQuery->bindValue(":time", msg.timeStamp.toTime_t());
351 getLastMessageIdQuery->bindValue(":bufferid", msg.buffer.uid());
352 getLastMessageIdQuery->bindValue(":type", msg.type);
353 getLastMessageIdQuery->bindValue(":sender", msg.sender);
354 getLastMessageIdQuery->exec();
356 if(getLastMessageIdQuery->first()) {
357 return getLastMessageIdQuery->value(0).toUInt();
358 } else { // somethin went wrong... :(
359 qDebug() << getLastMessageIdQuery->lastQuery() << "time/bufferid/type/sender:" << msg.timeStamp.toTime_t() << msg.buffer.uid() << msg.type << msg.sender;
365 QList<Message> SqliteStorage::requestMsgs(BufferId buffer, int lastmsgs, int offset) {
366 QList<Message> messagelist;
367 // we have to determine the real offset first
368 requestMsgsOffsetQuery->bindValue(":bufferid", buffer.uid());
369 requestMsgsOffsetQuery->bindValue(":messageid", offset);
370 requestMsgsOffsetQuery->exec();
371 requestMsgsOffsetQuery->first();
372 offset = requestMsgsOffsetQuery->value(0).toUInt();
374 // now let's select the messages
375 requestMsgsQuery->bindValue(":bufferid", buffer.uid());
376 requestMsgsQuery->bindValue(":bufferid2", buffer.uid()); // Qt can't handle the same placeholder used twice
377 requestMsgsQuery->bindValue(":limit", lastmsgs);
378 requestMsgsQuery->bindValue(":offset", offset);
379 requestMsgsQuery->exec();
380 while(requestMsgsQuery->next()) {
381 Message msg(QDateTime::fromTime_t(requestMsgsQuery->value(1).toInt()),
383 (Message::Type)requestMsgsQuery->value(2).toUInt(),
384 requestMsgsQuery->value(5).toString(),
385 requestMsgsQuery->value(4).toString(),
386 requestMsgsQuery->value(3).toUInt());
387 msg.msgId = requestMsgsQuery->value(0).toUInt();
394 QList<Message> SqliteStorage::requestMsgs(BufferId buffer, QDateTime since, int offset) {
395 QList<Message> messagelist;
396 // we have to determine the real offset first
397 requestMsgsSinceOffsetQuery->bindValue(":bufferid", buffer.uid());
398 requestMsgsSinceOffsetQuery->bindValue(":since", since.toTime_t());
399 requestMsgsSinceOffsetQuery->exec();
400 requestMsgsSinceOffsetQuery->first();
401 offset = requestMsgsSinceOffsetQuery->value(0).toUInt();
403 // now let's select the messages
404 requestMsgsSinceQuery->bindValue(":bufferid", buffer.uid());
405 requestMsgsSinceQuery->bindValue(":bufferid2", buffer.uid());
406 requestMsgsSinceQuery->bindValue(":since", since.toTime_t());
407 requestMsgsSinceQuery->bindValue(":offset", offset);
408 requestMsgsSinceQuery->exec();
410 while(requestMsgsSinceQuery->next()) {
411 Message msg(QDateTime::fromTime_t(requestMsgsSinceQuery->value(1).toInt()),
413 (Message::Type)requestMsgsSinceQuery->value(2).toUInt(),
414 requestMsgsSinceQuery->value(5).toString(),
415 requestMsgsSinceQuery->value(4).toString(),
416 requestMsgsSinceQuery->value(3).toUInt());
417 msg.msgId = requestMsgsSinceQuery->value(0).toUInt();
425 QList<Message> SqliteStorage::requestMsgRange(BufferId buffer, int first, int last) {
426 QList<Message> messagelist;
427 requestMsgRangeQuery->bindValue(":bufferid", buffer.uid());
428 requestMsgRangeQuery->bindValue(":bufferid2", buffer.uid());
429 requestMsgRangeQuery->bindValue(":firstmsg", first);
430 requestMsgRangeQuery->bindValue(":lastmsg", last);
432 while(requestMsgRangeQuery->next()) {
433 Message msg(QDateTime::fromTime_t(requestMsgRangeQuery->value(1).toInt()),
435 (Message::Type)requestMsgRangeQuery->value(2).toUInt(),
436 requestMsgRangeQuery->value(5).toString(),
437 requestMsgRangeQuery->value(4).toString(),
438 requestMsgRangeQuery->value(3).toUInt());
439 msg.msgId = requestMsgRangeQuery->value(0).toUInt();
446 void SqliteStorage::importOldBacklog() {
447 QSqlQuery query(logDb);
449 query.prepare("SELECT MIN(userid) FROM quasseluser");
452 qDebug() << "create a user first!";
454 user = query.value(0).toUInt();
456 query.prepare("DELETE FROM backlog WHERE bufferid IN (SELECT DISTINCT bufferid FROM buffer WHERE userid = :userid");
457 query.bindValue(":userid", user);
459 query.prepare("DELETE FROM buffer WHERE userid = :userid");
460 query.bindValue(":userid", user);
462 query.prepare("DELETE FROM buffergroup WHERE userid = :userid");
463 query.bindValue(":userid", user);
465 query.prepare("DELETE FROM network WHERE userid = :userid");
466 query.bindValue(":userid", user);
469 qDebug() << "All userdata has been deleted";
470 qDebug() << "importing old backlog files...";
471 initBackLogOld(user);