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"
24 SqliteStorage::SqliteStorage() {
25 // TODO I don't think that this path is failsafe for windows users :)
26 QString backlogFile = Global::quasselDir + "/quassel-storage.sqlite";
27 logDb = QSqlDatabase::addDatabase("QSQLITE");
28 logDb.setDatabaseName(backlogFile);
29 bool ok = logDb.open();
32 qWarning(tr("Could not open backlog database: %1").arg(logDb.lastError().text()).toAscii());
33 qWarning(tr("Disabling logging...").toAscii());
38 // check if the db schema is up to date
39 QSqlQuery query = logDb.exec("SELECT MAX(version) FROM coreinfo");
42 //checkVersion(query.value(0));
43 qDebug() << "Sqlite is ready. Quassel Schema Version:" << query.value(0).toUInt();
48 // we will need those pretty often... so let's speed things up:
49 createBufferQuery = new QSqlQuery(logDb);
50 createBufferQuery->prepare("INSERT INTO buffer (userid, networkid, buffername) VALUES (:userid, (SELECT networkid FROM network WHERE networkname = :networkname), :buffername)");
52 createNetworkQuery = new QSqlQuery(logDb);
53 createNetworkQuery->prepare("INSERT INTO network (userid, networkname) VALUES (:userid, :networkname)");
55 getBufferIdQuery = new QSqlQuery(logDb);
56 getBufferIdQuery->prepare("SELECT bufferid FROM buffer "
57 "JOIN network ON buffer.networkid = network.networkid "
58 "WHERE network.networkname = :networkname AND buffer.userid = :userid AND buffer.buffername = :buffername ");
60 logMessageQuery = new QSqlQuery(logDb);
61 logMessageQuery->prepare("INSERT INTO backlog (time, bufferid, type, flags, senderid, message) "
62 "VALUES (:time, :bufferid, :type, :flags, (SELECT senderid FROM sender WHERE sender = :sender), :message)");
64 addSenderQuery = new QSqlQuery(logDb);
65 addSenderQuery->prepare("INSERT INTO sender (sender) VALUES (:sender)");
67 getLastMessageIdQuery = new QSqlQuery(logDb);
68 getLastMessageIdQuery->prepare("SELECT messageid FROM backlog "
69 "WHERE time = :time AND bufferid = :bufferid AND type = :type AND senderid = (SELECT senderid FROM sender WHERE sender = :sender)");
71 requestMsgsOffsetQuery = new QSqlQuery(logDb);
72 requestMsgsOffsetQuery->prepare("SELECT count(*) FROM backlog WHERE bufferid = :bufferid AND messageid < :messageid");
74 requestMsgsQuery = new QSqlQuery(logDb);
75 requestMsgsQuery->prepare("SELECT messageid, time, type, flags, sender, message, displayname "
77 "JOIN buffer ON backlog.bufferid = buffer.bufferid "
78 "JOIN sender ON backlog.senderid = sender.senderid "
79 "LEFT JOIN buffergroup ON buffer.groupid = buffergroup.groupid "
80 "WHERE buffer.bufferid = :bufferid OR buffer.groupid = (SELECT groupid FROM buffer WHERE bufferid = :bufferid2) "
81 "ORDER BY messageid DESC "
82 "LIMIT :limit OFFSET :offset");
84 requestMsgsSinceOffsetQuery = new QSqlQuery(logDb);
85 requestMsgsSinceOffsetQuery->prepare("SELECT count(*) FROM backlog WHERE bufferid = :bufferid AND time >= :since");
87 requestMsgsSinceQuery = new QSqlQuery(logDb);
88 requestMsgsSinceQuery->prepare("SELECT messageid, time, type, flags, sender, message, displayname "
90 "JOIN buffer ON backlog.bufferid = buffer.bufferid "
91 "JOIN sender ON backlog.senderid = sender.senderid "
92 "LEFT JOIN buffergroup ON buffer.groupid = buffergroup.groupid "
93 "WHERE (buffer.bufferid = :bufferid OR buffer.groupid = (SELECT groupid FROM buffer WHERE bufferid = :bufferid2)) AND "
94 "backlog.time >= :since "
95 "ORDER BY messageid DESC "
96 "LIMIT -1 OFFSET :offset");
98 requestMsgRangeQuery = new QSqlQuery(logDb);
99 requestMsgRangeQuery->prepare("SELECT messageid, time, type, flags, sender, message, displayname "
101 "JOIN buffer ON backlog.bufferid = buffer.bufferid "
102 "JOIN sender ON backlog.senderid = sender.senderid "
103 "LEFT JOIN buffergroup ON buffer.groupid = buffergroup.groupid "
104 "WHERE (buffer.bufferid = :bufferid OR buffer.groupid = (SELECT groupid FROM buffer WHERE bufferid = :bufferid2)) AND "
105 "backlog.messageid >= :firstmsg AND backlog.messageid <= :lastmsg "
106 "ORDER BY messageid DESC ");
110 SqliteStorage::~SqliteStorage() {
111 delete logMessageQuery;
112 delete addSenderQuery;
113 delete getLastMessageIdQuery;
114 delete requestMsgsQuery;
115 delete requestMsgsOffsetQuery;
116 delete requestMsgsSinceQuery;
117 delete requestMsgsSinceOffsetQuery;
118 delete requestMsgRangeQuery;
119 delete createNetworkQuery;
120 delete createBufferQuery;
121 delete getBufferIdQuery;
126 void SqliteStorage::initDb() {
127 logDb.exec("CREATE TABLE quasseluser ("
128 "userid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
129 "username TEXT UNIQUE NOT NULL,"
130 "password BLOB NOT NULL)");
132 logDb.exec("CREATE TABLE sender ("
133 "senderid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
134 "sender TEXT UNIQUE NOT NULL)");
136 logDb.exec("CREATE TABLE network ("
137 "networkid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
138 "userid INTEGER NOT NULL,"
139 "networkname TEXT NOT NULL,"
140 "UNIQUE (userid, networkname))");
142 logDb.exec("CREATE TABLE buffergroup ("
143 "groupid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
144 "userid INTEGER NOT NULL,"
145 "displayname TEXT)");
147 logDb.exec("CREATE TABLE buffer ("
148 "bufferid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
149 "userid INTEGER NOT NULL,"
151 "networkid INTEGER NOT NULL,"
152 "buffername TEXT NOT NULL)");
154 logDb.exec("CREATE UNIQUE INDEX buffer_idx "
155 "ON buffer(userid, networkid, buffername)");
157 logDb.exec("CREATE TABLE backlog ("
158 "messageid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
159 "time INTEGER NOT NULL,"
160 "bufferid INTEGER NOT NULL,"
161 "type INTEGER NOT NULL,"
162 "flags INTEGER NOT NULL,"
163 "senderid INTEGER NOT NULL,"
164 "message TEXT NOT NULL)");
166 logDb.exec("CREATE TABLE coreinfo ("
167 "updateid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
168 "version INTEGER NOT NULL)");
170 logDb.exec("INSERT INTO coreinfo (version) VALUES (0)");
173 // something fucked up -> no logging possible
174 // FIXME logDb.lastError is reset whenever exec is called
175 if(logDb.lastError().isValid()) {
176 qWarning(tr("Could not create backlog table: %1").arg(logDb.lastError().text()).toAscii());
177 qWarning(tr("Disabling logging...").toAscii());
178 Q_ASSERT(false); // quassel does require logging
182 bool SqliteStorage::isAvailable() {
183 if(!QSqlDatabase::isDriverAvailable("QSQLITE")) return false;
187 QString SqliteStorage::displayName() {
188 // I think the class name is a good start here
189 return QString("SqliteStorage");
192 UserId SqliteStorage::addUser(QString user, QString password) {
193 QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
194 cryptopass = cryptopass.toHex();
196 QSqlQuery query(logDb);
197 query.prepare("INSERT INTO quasseluser (username, password) VALUES (:username, :password)");
198 query.bindValue(":username", user);
199 query.bindValue(":password", cryptopass);
201 if(query.lastError().isValid() && query.lastError().number() == 19) { // user already exists - sadly 19 seems to be the general constraint violation error...
205 query.prepare("SELECT userid FROM quasseluser WHERE username = :username");
206 query.bindValue(":username", user);
209 return query.value(0).toUInt();
212 void SqliteStorage::updateUser(UserId user, QString password) {
213 QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
214 cryptopass = cryptopass.toHex();
216 QSqlQuery query(logDb);
217 query.prepare("UPDATE quasseluser SET password = :password WHERE userid = :userid");
218 query.bindValue(":userid", user);
219 query.bindValue(":password", cryptopass);
223 UserId SqliteStorage::validateUser(QString user, QString password) {
224 QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
225 cryptopass = cryptopass.toHex();
227 QSqlQuery query(logDb);
228 query.prepare("SELECT userid FROM quasseluser WHERE username = :username AND password = :password");
229 query.bindValue(":username", user);
230 query.bindValue(":password", cryptopass);
234 return query.value(0).toUInt();
240 void SqliteStorage::delUser(UserId user) {
241 QSqlQuery query(logDb);
242 query.prepare("DELETE FROM backlog WHERE bufferid IN (SELECT DISTINCT bufferid FROM buffer WHERE userid = :userid");
243 query.bindValue(":userid", user);
245 query.prepare("DELETE FROM buffer WHERE userid = :userid");
246 query.bindValue(":userid", user);
248 query.prepare("DELETE FROM buffergroup WHERE userid = :userid");
249 query.bindValue(":userid", user);
251 query.prepare("DELETE FROM network WHERE userid = :userid");
252 query.bindValue(":userid", user);
254 query.prepare("DELETE FROM quasseluser WHERE userid = :userid");
255 query.bindValue(":userid", user);
257 // I hate the lack of foreign keys and on delete cascade... :(
260 void SqliteStorage::createBuffer(UserId user, QString network, QString buffer) {
261 createBufferQuery->bindValue(":userid", user);
262 createBufferQuery->bindValue(":networkname", network);
263 createBufferQuery->bindValue(":buffername", buffer);
264 createBufferQuery->exec();
266 if(createBufferQuery->lastError().isValid()) {
267 if(createBufferQuery->lastError().number() == 19) { // Null Constraint violation
268 createNetworkQuery->bindValue(":userid", user);
269 createNetworkQuery->bindValue(":networkname", network);
270 createNetworkQuery->exec();
271 createBufferQuery->exec();
272 Q_ASSERT(!createNetworkQuery->lastError().isValid());
273 Q_ASSERT(!createBufferQuery->lastError().isValid());
276 qDebug() << "failed to create Buffer: ErrNo:" << createBufferQuery->lastError().number() << "ErrMsg:" << createBufferQuery->lastError().text();
282 BufferId SqliteStorage::getBufferId(UserId user, QString network, QString buffer) {
284 getBufferIdQuery->bindValue(":networkname", network);
285 getBufferIdQuery->bindValue(":userid", user);
286 getBufferIdQuery->bindValue(":buffername", buffer);
287 getBufferIdQuery->exec();
289 if(!getBufferIdQuery->first()) {
290 createBuffer(user, network, buffer);
291 getBufferIdQuery->exec();
292 if(getBufferIdQuery->first()) {
293 bufferid = BufferId(getBufferIdQuery->value(0).toUInt(), network, buffer);
294 emit bufferIdUpdated(bufferid);
297 bufferid = BufferId(getBufferIdQuery->value(0).toUInt(), network, buffer);
300 Q_ASSERT(!getBufferIdQuery->next());
305 QList<BufferId> SqliteStorage::requestBuffers(UserId user, QDateTime since) {
306 QList<BufferId> bufferlist;
307 QSqlQuery query(logDb);
308 query.prepare("SELECT DISTINCT buffer.bufferid, networkname, buffername FROM buffer "
309 "JOIN network ON buffer.networkid = network.networkid "
310 "JOIN backlog ON buffer.bufferid = backlog.bufferid "
311 "WHERE buffer.userid = :userid AND time >= :time");
312 query.bindValue(":userid", user);
313 if (since.isValid()) {
314 query.bindValue(":time", since.toTime_t());
316 query.bindValue(":time", 0);
321 while(query.next()) {
322 bufferlist << BufferId(query.value(0).toUInt(), query.value(1).toString(), query.value(2).toString());
327 MsgId SqliteStorage::logMessage(Message msg) {
328 logMessageQuery->bindValue(":time", msg.timeStamp.toTime_t());
329 logMessageQuery->bindValue(":bufferid", msg.buffer.uid());
330 logMessageQuery->bindValue(":type", msg.type);
331 logMessageQuery->bindValue(":flags", msg.flags);
332 logMessageQuery->bindValue(":sender", msg.sender);
333 logMessageQuery->bindValue(":message", msg.text);
334 logMessageQuery->exec();
336 if(logMessageQuery->lastError().isValid()) {
337 // constraint violation - must be NOT NULL constraint - probably the sender is missing...
338 if(logMessageQuery->lastError().number() == 19) {
339 addSenderQuery->bindValue(":sender", msg.sender);
340 addSenderQuery->exec();
341 logMessageQuery->exec();
342 Q_ASSERT(!logMessageQuery->lastError().isValid());
344 qDebug() << "unhandled DB Error in logMessage(): Number:" << logMessageQuery->lastError().number() << "ErrMsg:" << logMessageQuery->lastError().text();
348 getLastMessageIdQuery->bindValue(":time", msg.timeStamp.toTime_t());
349 getLastMessageIdQuery->bindValue(":bufferid", msg.buffer.uid());
350 getLastMessageIdQuery->bindValue(":type", msg.type);
351 getLastMessageIdQuery->bindValue(":sender", msg.sender);
352 getLastMessageIdQuery->exec();
354 if(getLastMessageIdQuery->first()) {
355 return getLastMessageIdQuery->value(0).toUInt();
356 } else { // somethin went wrong... :(
357 qDebug() << getLastMessageIdQuery->lastQuery() << "time/bufferid/type/sender:" << msg.timeStamp.toTime_t() << msg.buffer.uid() << msg.type << msg.sender;
363 QList<Message> SqliteStorage::requestMsgs(BufferId buffer, int lastmsgs, int offset) {
364 QList<Message> messagelist;
365 // we have to determine the real offset first
366 requestMsgsOffsetQuery->bindValue(":bufferid", buffer.uid());
367 requestMsgsOffsetQuery->bindValue(":messageid", offset);
368 requestMsgsOffsetQuery->exec();
369 requestMsgsOffsetQuery->first();
370 offset = requestMsgsOffsetQuery->value(0).toUInt();
372 // now let's select the messages
373 requestMsgsQuery->bindValue(":bufferid", buffer.uid());
374 requestMsgsQuery->bindValue(":bufferid2", buffer.uid()); // Qt can't handle the same placeholder used twice
375 requestMsgsQuery->bindValue(":limit", lastmsgs);
376 requestMsgsQuery->bindValue(":offset", offset);
377 requestMsgsQuery->exec();
378 while(requestMsgsQuery->next()) {
379 Message msg(QDateTime::fromTime_t(requestMsgsQuery->value(1).toInt()),
381 (Message::Type)requestMsgsQuery->value(2).toUInt(),
382 requestMsgsQuery->value(5).toString(),
383 requestMsgsQuery->value(4).toString(),
384 requestMsgsQuery->value(3).toUInt());
385 msg.msgId = requestMsgsQuery->value(0).toUInt();
393 QList<Message> SqliteStorage::requestMsgs(BufferId buffer, QDateTime since, int offset) {
394 QList<Message> messagelist;
395 // we have to determine the real offset first
396 requestMsgsSinceOffsetQuery->bindValue(":bufferid", buffer.uid());
397 requestMsgsSinceOffsetQuery->bindValue(":since", since.toTime_t());
398 requestMsgsSinceOffsetQuery->exec();
399 requestMsgsSinceOffsetQuery->first();
400 offset = requestMsgsSinceOffsetQuery->value(0).toUInt();
402 // now let's select the messages
403 requestMsgsSinceQuery->bindValue(":bufferid", buffer.uid());
404 requestMsgsSinceQuery->bindValue(":bufferid2", buffer.uid());
405 requestMsgsSinceQuery->bindValue(":since", since.toTime_t());
406 requestMsgsSinceQuery->bindValue(":offset", offset);
407 requestMsgsSinceQuery->exec();
409 while(requestMsgsSinceQuery->next()) {
410 Message msg(QDateTime::fromTime_t(requestMsgsSinceQuery->value(1).toInt()),
412 (Message::Type)requestMsgsSinceQuery->value(2).toUInt(),
413 requestMsgsSinceQuery->value(5).toString(),
414 requestMsgsSinceQuery->value(4).toString(),
415 requestMsgsSinceQuery->value(3).toUInt());
416 msg.msgId = requestMsgsSinceQuery->value(0).toUInt();
424 QList<Message> SqliteStorage::requestMsgRange(BufferId buffer, int first, int last) {
425 QList<Message> messagelist;
426 requestMsgRangeQuery->bindValue(":bufferid", buffer.uid());
427 requestMsgRangeQuery->bindValue(":bufferid2", buffer.uid());
428 requestMsgRangeQuery->bindValue(":firstmsg", first);
429 requestMsgRangeQuery->bindValue(":lastmsg", last);
431 while(requestMsgRangeQuery->next()) {
432 Message msg(QDateTime::fromTime_t(requestMsgRangeQuery->value(1).toInt()),
434 (Message::Type)requestMsgRangeQuery->value(2).toUInt(),
435 requestMsgRangeQuery->value(5).toString(),
436 requestMsgRangeQuery->value(4).toString(),
437 requestMsgRangeQuery->value(3).toUInt());
438 msg.msgId = requestMsgRangeQuery->value(0).toUInt();
445 void SqliteStorage::importOldBacklog() {
446 QSqlQuery query(logDb);
448 query.prepare("SELECT MIN(userid) FROM quasseluser");
451 qDebug() << "create a user first!";
453 user = query.value(0).toUInt();
455 query.prepare("DELETE FROM backlog WHERE bufferid IN (SELECT DISTINCT bufferid FROM buffer WHERE userid = :userid");
456 query.bindValue(":userid", user);
458 query.prepare("DELETE FROM buffer WHERE userid = :userid");
459 query.bindValue(":userid", user);
461 query.prepare("DELETE FROM buffergroup WHERE userid = :userid");
462 query.bindValue(":userid", user);
464 query.prepare("DELETE FROM network WHERE userid = :userid");
465 query.bindValue(":userid", user);
468 qDebug() << "All userdata has been deleted";
469 qDebug() << "importing old backlog files...";
470 initBackLogOld(user);