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());
39 NO, we should not start a transaction to check if transactions are supported :-)
40 if(!logDb.transaction()) {
41 qWarning(tr("Database driver does not support transactions. This might lead to a corrupt database!").toAscii());
45 // check if the db schema is up to date
46 QSqlQuery query = logDb.exec("SELECT MAX(version) FROM coreinfo");
49 //checkVersion(query.value(0));
50 qDebug() << "Sqlite is ready. Quassel Schema Version:" << query.value(0).toUInt();
55 // we will need those pretty often... so let's speed things up:
56 createBufferQuery = new QSqlQuery(logDb);
57 createBufferQuery->prepare("INSERT INTO buffer (userid, networkid, buffername) VALUES (:userid, (SELECT networkid FROM network WHERE networkname = :networkname), :buffername)");
59 createNetworkQuery = new QSqlQuery(logDb);
60 createNetworkQuery->prepare("INSERT INTO network (userid, networkname) VALUES (:userid, :networkname)");
62 getBufferIdQuery = new QSqlQuery(logDb);
63 getBufferIdQuery->prepare("SELECT bufferid FROM buffer "
64 "JOIN network ON buffer.networkid = network.networkid "
65 "WHERE network.networkname = :networkname AND buffer.userid = :userid AND buffer.buffername = :buffername "
69 logMessageQuery = new QSqlQuery(logDb);
70 logMessageQuery->prepare("INSERT INTO backlog (time, bufferid, type, flags, senderid, message) "
71 "VALUES (:time, :bufferid, :type, :flags, (SELECT senderid FROM sender WHERE sender = :sender), :message)");
73 addSenderQuery = new QSqlQuery(logDb);
74 addSenderQuery->prepare("INSERT INTO sender (sender) VALUES (:sender)");
76 getLastMessageIdQuery = new QSqlQuery(logDb);
77 getLastMessageIdQuery->prepare("SELECT messageid FROM backlog "
78 "WHERE time = :time AND bufferid = :bufferid AND type = :type AND senderid = (SELECT senderid FROM sender WHERE sender = :sender)");
80 requestMsgsOffsetQuery = new QSqlQuery(logDb);
81 requestMsgsOffsetQuery->prepare("SELECT count(*) FROM backlog WHERE bufferid = :bufferid AND messageid < :messageid");
83 requestMsgsQuery = new QSqlQuery(logDb);
84 requestMsgsQuery->prepare("SELECT messageid, time, type, flags, sender, message, displayname "
86 "JOIN buffer ON backlog.bufferid = buffer.bufferid "
87 "JOIN sender ON backlog.senderid = sender.senderid "
88 "LEFT JOIN buffergroup ON buffer.groupid = buffergroup.groupid "
89 "WHERE buffer.bufferid = :bufferid OR buffer.groupid = (SELECT groupid FROM buffer WHERE bufferid = :bufferid2) "
90 "ORDER BY messageid DESC "
91 "LIMIT :limit OFFSET :offset");
93 requestMsgsSinceOffsetQuery = new QSqlQuery(logDb);
94 requestMsgsSinceOffsetQuery->prepare("SELECT count(*) FROM backlog WHERE bufferid = :bufferid AND time >= :since");
96 requestMsgsSinceQuery = new QSqlQuery(logDb);
97 requestMsgsSinceQuery->prepare("SELECT messageid, time, type, flags, sender, message, displayname "
99 "JOIN buffer ON backlog.bufferid = buffer.bufferid "
100 "JOIN sender ON backlog.senderid = sender.senderid "
101 "LEFT JOIN buffergroup ON buffer.groupid = buffergroup.groupid "
102 "WHERE (buffer.bufferid = :bufferid OR buffer.groupid = (SELECT groupid FROM buffer WHERE bufferid = :bufferid2)) AND "
103 "backlog.time >= :since "
104 "ORDER BY messageid DESC "
105 "LIMIT -1 OFFSET :offset");
107 requestMsgRangeQuery = new QSqlQuery(logDb);
108 requestMsgRangeQuery->prepare("SELECT messageid, time, type, flags, sender, message, displayname "
110 "JOIN buffer ON backlog.bufferid = buffer.bufferid "
111 "JOIN sender ON backlog.senderid = sender.senderid "
112 "LEFT JOIN buffergroup ON buffer.groupid = buffergroup.groupid "
113 "WHERE (buffer.bufferid = :bufferid OR buffer.groupid = (SELECT groupid FROM buffer WHERE bufferid = :bufferid2)) AND "
114 "backlog.messageid >= :firstmsg AND backlog.messageid <= :lastmsg "
115 "ORDER BY messageid DESC ");
119 SqliteStorage::~SqliteStorage() {
120 delete logMessageQuery;
121 delete addSenderQuery;
122 delete getLastMessageIdQuery;
123 delete requestMsgsQuery;
124 delete requestMsgsOffsetQuery;
125 delete requestMsgsSinceQuery;
126 delete requestMsgsSinceOffsetQuery;
127 delete requestMsgRangeQuery;
128 delete createNetworkQuery;
129 delete createBufferQuery;
130 delete getBufferIdQuery;
132 //qDebug() << logDb.lastError().text();
136 void SqliteStorage::initDb() {
137 //logDb.transaction();
138 logDb.exec("CREATE TABLE quasseluser ("
139 "userid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
140 "username TEXT UNIQUE NOT NULL,"
141 "password BLOB NOT NULL)");
143 logDb.exec("CREATE TABLE sender ("
144 "senderid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
145 "sender TEXT UNIQUE NOT NULL)");
147 logDb.exec("CREATE TABLE network ("
148 "networkid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
149 "userid INTEGER NOT NULL,"
150 "networkname TEXT NOT NULL,"
151 "UNIQUE (userid, networkname))");
153 logDb.exec("CREATE TABLE buffergroup ("
154 "groupid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
155 "userid INTEGER NOT NULL,"
156 "displayname TEXT)");
158 logDb.exec("CREATE TABLE buffer ("
159 "bufferid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
160 "userid INTEGER NOT NULL,"
162 "networkid INTEGER NOT NULL,"
163 "buffername TEXT NOT NULL)");
165 logDb.exec("CREATE UNIQUE INDEX buffer_idx "
166 "ON buffer(userid, networkid, buffername)");
168 logDb.exec("CREATE TABLE backlog ("
169 "messageid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
170 "time INTEGER NOT NULL,"
171 "bufferid INTEGER NOT NULL,"
172 "type INTEGER NOT NULL,"
173 "flags INTEGER NOT NULL,"
174 "senderid INTEGER NOT NULL,"
175 "message TEXT NOT NULL)");
177 logDb.exec("CREATE TABLE coreinfo ("
178 "updateid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
179 "version INTEGER NOT NULL)");
181 logDb.exec("INSERT INTO coreinfo (version) VALUES (0)");
184 // something fucked up -> no logging possible
185 if(logDb.lastError().isValid()) {
186 qWarning(tr("Could not create backlog table: %1").arg(logDb.lastError().text()).toAscii());
187 qWarning(tr("Disabling logging...").toAscii());
189 Q_ASSERT(false); // quassel does require logging
195 bool SqliteStorage::isAvailable() {
196 if(!QSqlDatabase::isDriverAvailable("QSQLITE")) return false;
200 QString SqliteStorage::displayName() {
201 // I think the class name is a good start here
202 return QString("SqliteStorage");
205 UserId SqliteStorage::addUser(QString user, QString password) {
206 QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
207 cryptopass = cryptopass.toHex();
209 //logDb.transaction();
210 QSqlQuery query(logDb);
211 query.prepare("INSERT INTO quasseluser (username, password) VALUES (:username, :password)");
212 query.bindValue(":username", user);
213 query.bindValue(":password", cryptopass);
215 if(query.lastError().isValid() && query.lastError().number() == 19) { // user already exists - sadly 19 seems to be the general constraint violation error...
221 query.prepare("SELECT userid FROM quasseluser WHERE username = :username");
222 query.bindValue(":username", user);
225 return query.value(0).toUInt();
228 void SqliteStorage::updateUser(UserId user, QString password) {
229 QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
230 cryptopass = cryptopass.toHex();
232 //logDb.transaction();
233 QSqlQuery query(logDb);
234 query.prepare("UPDATE quasseluser SET password = :password WHERE userid = :userid");
235 query.bindValue(":userid", user);
236 query.bindValue(":password", cryptopass);
241 UserId SqliteStorage::validateUser(QString user, QString password) {
242 QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
243 cryptopass = cryptopass.toHex();
245 QSqlQuery query(logDb);
246 query.prepare("SELECT userid FROM quasseluser WHERE username = :username AND password = :password");
247 query.bindValue(":username", user);
248 query.bindValue(":password", cryptopass);
252 return query.value(0).toUInt();
258 void SqliteStorage::delUser(UserId user) {
259 //logDb.transaction();
260 QSqlQuery query(logDb);
261 // FIXME: backlog has no userid, it's in bufferid
262 query.prepare("DELETE FROM backlog WHERE userid = :userid");
263 query.bindValue(":userid", user);
265 query.prepare("DELETE FROM buffer WHERE userid = :userid");
266 query.bindValue(":userid", user);
268 query.prepare("DELETE FROM buffergroup WHERE userid = :userid");
269 query.bindValue(":userid", user);
271 query.prepare("DELETE FROM network WHERE userid = :userid");
272 query.bindValue(":userid", user);
274 query.prepare("DELETE FROM quasseluser WHERE userid = :userid");
275 query.bindValue(":userid", user);
278 // I hate the lack of foreign keys and on delete cascade... :(
281 void SqliteStorage::createBuffer(UserId user, QString network, QString buffer) {
282 //qDebug() << "creating buffer:" << user << network << buffer;
283 //logDb.transaction();
284 createBufferQuery->bindValue(":userid", user);
285 createBufferQuery->bindValue(":networkname", network);
286 createBufferQuery->bindValue(":buffername", buffer);
287 createBufferQuery->exec();
289 if(createBufferQuery->lastError().isValid()) {
290 if(createBufferQuery->lastError().number() == 19) { // Null Constraint violation
291 createNetworkQuery->bindValue(":userid", user);
292 createNetworkQuery->bindValue(":networkname", network);
293 createNetworkQuery->exec();
294 createBufferQuery->exec();
295 //qDebug() << "net" << createNetworkQuery->lastError().text();
296 //qDebug() << "buf" << createBufferQuery->lastError().text();
297 Q_ASSERT(!createNetworkQuery->lastError().isValid());
298 Q_ASSERT(!createBufferQuery->lastError().isValid());
301 qDebug() << "failed to create Buffer: ErrNo:" << createBufferQuery->lastError().number() << "ErrMsg:" << createBufferQuery->lastError().text();
308 BufferId SqliteStorage::getBufferId(UserId user, QString network, QString buffer) {
309 if(buffer == "") buffer = "$$$"; // FIXME
311 QSqlQuery *getBufferIdQuery = new QSqlQuery(logDb);
312 getBufferIdQuery->prepare("SELECT bufferid FROM buffer "
313 "JOIN network ON buffer.networkid = network.networkid "
314 "WHERE network.networkname = :networkname AND buffer.userid = :userid AND buffer.buffername = :buffername "
317 getBufferIdQuery->bindValue(":networkname", network);
318 getBufferIdQuery->bindValue(":userid", user);
319 getBufferIdQuery->bindValue(":buffername", buffer);
320 getBufferIdQuery->exec();
323 if(!getBufferIdQuery->first()) {
324 createBuffer(user, network, buffer);
325 getBufferIdQuery->exec();
326 flg = getBufferIdQuery->first();
329 if(buffer == "$$$") buffer = "";
330 BufferId result = BufferId(getBufferIdQuery->value(0).toUInt(), network, buffer);
331 //getBufferIdQuery->clear(); // this is active for some reason, which wrecks havoc with later transactions
332 getBufferIdQuery->last();
333 //qDebug() << "active" << getBufferIdQuery->isActive();
334 if(flg) emit bufferIdUpdated(result);
335 delete getBufferIdQuery;
339 QList<BufferId> SqliteStorage::requestBuffers(UserId user, QDateTime since) {
340 QList<BufferId> bufferlist;
341 QSqlQuery query(logDb);
342 // FIXME: fix query (and make it run in sane time)
343 query.prepare("SELECT buffer.bufferid, networkname, buffername FROM buffer "
344 //"JOIN buffer ON buffer.bufferid = backlog.bufferid "
345 "JOIN network ON buffer.networkid = network.networkid "
346 //"JOIN backlog ON buffer.bufferid = backlog.bufferid "
347 "WHERE buffer.userid = 1");// AND time >= 0");
348 //query.bindValue(":userid", user);
349 //if(since.isValid()) query.bindValue(":time", since.toTime_t());
350 //else query.bindValue(":time",0);
351 //qDebug() << query.boundValues();
353 //qDebug() << query.lastError().text();
354 while(query.next()) {
355 QString buf = query.value(2).toString();
356 if(buf == "$$$") buf = "";
357 bufferlist << BufferId(query.value(0).toUInt(), query.value(1).toString(), buf);
362 MsgId SqliteStorage::logMessage(Message msg) {
363 if(msg.sender == "") msg.sender = "$$$"; // FIXME handle empty sender strings in a sane way
364 //logDb.transaction();
365 logMessageQuery->bindValue(":time", msg.timeStamp.toTime_t());
366 logMessageQuery->bindValue(":bufferid", msg.buffer.uid());
367 logMessageQuery->bindValue(":type", msg.type);
368 logMessageQuery->bindValue(":flags", msg.flags);
369 logMessageQuery->bindValue(":sender", msg.sender);
370 logMessageQuery->bindValue(":message", msg.text);
371 logMessageQuery->exec();
372 // constraint violation - must be NOT NULL constraint - probably the sender is missing...
373 if(logMessageQuery->lastError().isValid()) {
374 if(logMessageQuery->lastError().number() == 19) {
375 addSenderQuery->bindValue(":sender", msg.sender);
376 addSenderQuery->exec();
377 logMessageQuery->exec();
379 qDebug() << "unhandled DB Error in logMessage(): Number:" << logMessageQuery->lastError().number() << "ErrMsg:" << logMessageQuery->lastError().text();
385 getLastMessageIdQuery->bindValue(":time", msg.timeStamp.toTime_t());
386 getLastMessageIdQuery->bindValue(":bufferid", msg.buffer.uid());
387 getLastMessageIdQuery->bindValue(":type", msg.type);
388 getLastMessageIdQuery->bindValue(":sender", msg.sender);
389 getLastMessageIdQuery->exec();
391 if(getLastMessageIdQuery->first()) {
392 return getLastMessageIdQuery->value(0).toUInt();
393 } else { // somethin went wrong... :(
394 qDebug() << getLastMessageIdQuery->lastQuery();
400 QList<Message> SqliteStorage::requestMsgs(BufferId buffer, int lastmsgs, int offset) {
401 QList<Message> messagelist;
402 // we have to determine the real offset first
403 requestMsgsOffsetQuery->bindValue(":bufferid", buffer.uid());
404 requestMsgsOffsetQuery->bindValue(":messageid", offset);
405 requestMsgsOffsetQuery->exec();
406 requestMsgsOffsetQuery->first();
407 offset = requestMsgsOffsetQuery->value(0).toUInt();
409 // now let's select the messages
410 requestMsgsQuery->bindValue(":bufferid", buffer.uid());
411 requestMsgsQuery->bindValue(":bufferid2", buffer.uid()); // Qt can't handle the same placeholder used twice
412 requestMsgsQuery->bindValue(":limit", lastmsgs);
413 requestMsgsQuery->bindValue(":offset", offset);
414 requestMsgsQuery->exec();
415 while(requestMsgsQuery->next()) {
416 Message msg(QDateTime::fromTime_t(requestMsgsQuery->value(1).toInt()),
418 (Message::Type)requestMsgsQuery->value(2).toUInt(),
419 requestMsgsQuery->value(5).toString(),
420 requestMsgsQuery->value(4).toString(),
421 requestMsgsQuery->value(3).toUInt());
422 msg.msgId = requestMsgsQuery->value(0).toUInt();
423 if(msg.sender == "$$$") msg.sender = ""; // FIXME
431 QList<Message> SqliteStorage::requestMsgs(BufferId buffer, QDateTime since, int offset) {
432 QList<Message> messagelist;
433 // we have to determine the real offset first
434 requestMsgsSinceOffsetQuery->bindValue(":bufferid", buffer.uid());
435 requestMsgsSinceOffsetQuery->bindValue(":since", since.toTime_t());
436 requestMsgsSinceOffsetQuery->exec();
437 requestMsgsSinceOffsetQuery->first();
438 offset = requestMsgsSinceOffsetQuery->value(0).toUInt();
440 // now let's select the messages
441 requestMsgsSinceQuery->bindValue(":bufferid", buffer.uid());
442 requestMsgsSinceQuery->bindValue(":bufferid2", buffer.uid());
443 requestMsgsSinceQuery->bindValue(":since", since.toTime_t());
444 requestMsgsSinceQuery->bindValue(":offset", offset);
445 requestMsgsSinceQuery->exec();
447 while(requestMsgsSinceQuery->next()) {
448 Message msg(QDateTime::fromTime_t(requestMsgsSinceQuery->value(1).toInt()),
450 (Message::Type)requestMsgsSinceQuery->value(2).toUInt(),
451 requestMsgsSinceQuery->value(5).toString(),
452 requestMsgsSinceQuery->value(4).toString(),
453 requestMsgsSinceQuery->value(3).toUInt());
454 msg.msgId = requestMsgsSinceQuery->value(0).toUInt();
455 if(msg.sender == "$$$") msg.sender = ""; // FIXME
464 QList<Message> SqliteStorage::requestMsgRange(BufferId buffer, int first, int last) {
465 QList<Message> messagelist;
466 requestMsgRangeQuery->bindValue(":bufferid", buffer.uid());
467 requestMsgRangeQuery->bindValue(":bufferid2", buffer.uid());
468 requestMsgRangeQuery->bindValue(":firstmsg", first);
469 requestMsgRangeQuery->bindValue(":lastmsg", last);
471 while(requestMsgRangeQuery->next()) {
472 Message msg(QDateTime::fromTime_t(requestMsgRangeQuery->value(1).toInt()),
474 (Message::Type)requestMsgRangeQuery->value(2).toUInt(),
475 requestMsgRangeQuery->value(5).toString(),
476 requestMsgRangeQuery->value(4).toString(),
477 requestMsgRangeQuery->value(3).toUInt());
478 msg.msgId = requestMsgRangeQuery->value(0).toUInt();
479 if(msg.sender == "$$$") msg.sender = ""; // FIXME
486 void SqliteStorage::importOldBacklog() {
487 QSqlQuery query(logDb);
489 query.prepare("SELECT MIN(userid) FROM quasseluser");
492 qDebug() << "create a user first!";
494 user = query.value(0).toUInt();
496 // FIXME: backlog does not have userid, we have to select bufferids first
497 query.prepare("DELETE FROM backlog WHERE userid = :userid");
498 query.bindValue(":userid", user);
500 query.prepare("DELETE FROM buffer WHERE userid = :userid");
501 query.bindValue(":userid", user);
503 query.prepare("DELETE FROM buffergroup WHERE userid = :userid");
504 query.bindValue(":userid", user);
506 query.prepare("DELETE FROM network WHERE userid = :userid");
507 query.bindValue(":userid", user);
510 qDebug() << "All userdata has been deleted";
511 qDebug() << "importing old backlog files...";
512 initBackLogOld(user);