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 UserId uid = query.value(0).toUInt();
211 emit userAdded(uid, user);
215 void SqliteStorage::updateUser(UserId user, 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, 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(QString user, 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, QString network, 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 BufferId SqliteStorage::getBufferId(UserId user, QString network, QString buffer) {
298 getBufferIdQuery->bindValue(":networkname", network);
299 getBufferIdQuery->bindValue(":userid", user);
300 getBufferIdQuery->bindValue(":buffername", buffer);
301 getBufferIdQuery->exec();
303 if(!getBufferIdQuery->first()) {
304 createBuffer(user, network, buffer);
305 getBufferIdQuery->exec();
306 if(getBufferIdQuery->first()) {
307 bufferid = BufferId(getBufferIdQuery->value(0).toUInt(), network, buffer);
308 emit bufferIdUpdated(bufferid);
311 bufferid = BufferId(getBufferIdQuery->value(0).toUInt(), network, buffer);
314 Q_ASSERT(!getBufferIdQuery->next());
319 QList<BufferId> SqliteStorage::requestBuffers(UserId user, QDateTime since) {
320 QList<BufferId> bufferlist;
321 QSqlQuery query(logDb);
322 query.prepare("SELECT DISTINCT buffer.bufferid, networkname, buffername FROM buffer "
323 "JOIN network ON buffer.networkid = network.networkid "
324 "JOIN backlog ON buffer.bufferid = backlog.bufferid "
325 "WHERE buffer.userid = :userid AND time >= :time");
326 query.bindValue(":userid", user);
327 if (since.isValid()) {
328 query.bindValue(":time", since.toTime_t());
330 query.bindValue(":time", 0);
335 while(query.next()) {
336 bufferlist << BufferId(query.value(0).toUInt(), query.value(1).toString(), query.value(2).toString());
341 MsgId SqliteStorage::logMessage(Message msg) {
342 logMessageQuery->bindValue(":time", msg.timeStamp.toTime_t());
343 logMessageQuery->bindValue(":bufferid", msg.buffer.uid());
344 logMessageQuery->bindValue(":type", msg.type);
345 logMessageQuery->bindValue(":flags", msg.flags);
346 logMessageQuery->bindValue(":sender", msg.sender);
347 logMessageQuery->bindValue(":message", msg.text);
348 logMessageQuery->exec();
350 if(logMessageQuery->lastError().isValid()) {
351 // constraint violation - must be NOT NULL constraint - probably the sender is missing...
352 if(logMessageQuery->lastError().number() == 19) {
353 addSenderQuery->bindValue(":sender", msg.sender);
354 addSenderQuery->exec();
355 logMessageQuery->exec();
356 Q_ASSERT(!logMessageQuery->lastError().isValid());
358 qDebug() << "unhandled DB Error in logMessage(): Number:" << logMessageQuery->lastError().number() << "ErrMsg:" << logMessageQuery->lastError().text();
362 getLastMessageIdQuery->bindValue(":time", msg.timeStamp.toTime_t());
363 getLastMessageIdQuery->bindValue(":bufferid", msg.buffer.uid());
364 getLastMessageIdQuery->bindValue(":type", msg.type);
365 getLastMessageIdQuery->bindValue(":sender", msg.sender);
366 getLastMessageIdQuery->exec();
368 if(getLastMessageIdQuery->first()) {
369 return getLastMessageIdQuery->value(0).toUInt();
370 } else { // somethin went wrong... :(
371 qDebug() << getLastMessageIdQuery->lastQuery() << "time/bufferid/type/sender:" << msg.timeStamp.toTime_t() << msg.buffer.uid() << msg.type << msg.sender;
377 QList<Message> SqliteStorage::requestMsgs(BufferId buffer, int lastmsgs, int offset) {
378 QList<Message> messagelist;
379 // we have to determine the real offset first
380 requestMsgsOffsetQuery->bindValue(":bufferid", buffer.uid());
381 requestMsgsOffsetQuery->bindValue(":messageid", offset);
382 requestMsgsOffsetQuery->exec();
383 requestMsgsOffsetQuery->first();
384 offset = requestMsgsOffsetQuery->value(0).toUInt();
386 // now let's select the messages
387 requestMsgsQuery->bindValue(":bufferid", buffer.uid());
388 requestMsgsQuery->bindValue(":bufferid2", buffer.uid()); // Qt can't handle the same placeholder used twice
389 requestMsgsQuery->bindValue(":limit", lastmsgs);
390 requestMsgsQuery->bindValue(":offset", offset);
391 requestMsgsQuery->exec();
392 while(requestMsgsQuery->next()) {
393 Message msg(QDateTime::fromTime_t(requestMsgsQuery->value(1).toInt()),
395 (Message::Type)requestMsgsQuery->value(2).toUInt(),
396 requestMsgsQuery->value(5).toString(),
397 requestMsgsQuery->value(4).toString(),
398 requestMsgsQuery->value(3).toUInt());
399 msg.msgId = requestMsgsQuery->value(0).toUInt();
406 QList<Message> SqliteStorage::requestMsgs(BufferId buffer, QDateTime since, int offset) {
407 QList<Message> messagelist;
408 // we have to determine the real offset first
409 requestMsgsSinceOffsetQuery->bindValue(":bufferid", buffer.uid());
410 requestMsgsSinceOffsetQuery->bindValue(":since", since.toTime_t());
411 requestMsgsSinceOffsetQuery->exec();
412 requestMsgsSinceOffsetQuery->first();
413 offset = requestMsgsSinceOffsetQuery->value(0).toUInt();
415 // now let's select the messages
416 requestMsgsSinceQuery->bindValue(":bufferid", buffer.uid());
417 requestMsgsSinceQuery->bindValue(":bufferid2", buffer.uid());
418 requestMsgsSinceQuery->bindValue(":since", since.toTime_t());
419 requestMsgsSinceQuery->bindValue(":offset", offset);
420 requestMsgsSinceQuery->exec();
422 while(requestMsgsSinceQuery->next()) {
423 Message msg(QDateTime::fromTime_t(requestMsgsSinceQuery->value(1).toInt()),
425 (Message::Type)requestMsgsSinceQuery->value(2).toUInt(),
426 requestMsgsSinceQuery->value(5).toString(),
427 requestMsgsSinceQuery->value(4).toString(),
428 requestMsgsSinceQuery->value(3).toUInt());
429 msg.msgId = requestMsgsSinceQuery->value(0).toUInt();
437 QList<Message> SqliteStorage::requestMsgRange(BufferId buffer, int first, int last) {
438 QList<Message> messagelist;
439 requestMsgRangeQuery->bindValue(":bufferid", buffer.uid());
440 requestMsgRangeQuery->bindValue(":bufferid2", buffer.uid());
441 requestMsgRangeQuery->bindValue(":firstmsg", first);
442 requestMsgRangeQuery->bindValue(":lastmsg", last);
444 while(requestMsgRangeQuery->next()) {
445 Message msg(QDateTime::fromTime_t(requestMsgRangeQuery->value(1).toInt()),
447 (Message::Type)requestMsgRangeQuery->value(2).toUInt(),
448 requestMsgRangeQuery->value(5).toString(),
449 requestMsgRangeQuery->value(4).toString(),
450 requestMsgRangeQuery->value(3).toUInt());
451 msg.msgId = requestMsgRangeQuery->value(0).toUInt();
458 void SqliteStorage::importOldBacklog() {
459 QSqlQuery query(logDb);
461 query.prepare("SELECT MIN(userid) FROM quasseluser");
464 qDebug() << "create a user first!";
466 user = query.value(0).toUInt();
468 query.prepare("DELETE FROM backlog WHERE bufferid IN (SELECT DISTINCT bufferid FROM buffer WHERE userid = :userid");
469 query.bindValue(":userid", user);
471 query.prepare("DELETE FROM buffer WHERE userid = :userid");
472 query.bindValue(":userid", user);
474 query.prepare("DELETE FROM buffergroup WHERE userid = :userid");
475 query.bindValue(":userid", user);
477 query.prepare("DELETE FROM network WHERE userid = :userid");
478 query.bindValue(":userid", user);
481 qDebug() << "All userdata has been deleted";
482 qDebug() << "importing old backlog files...";
483 initBackLogOld(user);