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();
241 void SqliteStorage::delUser(UserId user) {
242 QSqlQuery query(logDb);
243 query.prepare("DELETE FROM backlog WHERE bufferid IN (SELECT DISTINCT bufferid FROM buffer WHERE userid = :userid");
244 query.bindValue(":userid", user);
246 query.prepare("DELETE FROM buffer WHERE userid = :userid");
247 query.bindValue(":userid", user);
249 query.prepare("DELETE FROM buffergroup WHERE userid = :userid");
250 query.bindValue(":userid", user);
252 query.prepare("DELETE FROM network WHERE userid = :userid");
253 query.bindValue(":userid", user);
255 query.prepare("DELETE FROM quasseluser WHERE userid = :userid");
256 query.bindValue(":userid", user);
258 // I hate the lack of foreign keys and on delete cascade... :(
261 void SqliteStorage::createBuffer(UserId user, QString network, QString buffer) {
262 createBufferQuery->bindValue(":userid", user);
263 createBufferQuery->bindValue(":networkname", network);
264 createBufferQuery->bindValue(":buffername", buffer);
265 createBufferQuery->exec();
267 if(createBufferQuery->lastError().isValid()) {
268 if(createBufferQuery->lastError().number() == 19) { // Null Constraint violation
269 createNetworkQuery->bindValue(":userid", user);
270 createNetworkQuery->bindValue(":networkname", network);
271 createNetworkQuery->exec();
272 createBufferQuery->exec();
273 Q_ASSERT(!createNetworkQuery->lastError().isValid());
274 Q_ASSERT(!createBufferQuery->lastError().isValid());
277 qDebug() << "failed to create Buffer: ErrNo:" << createBufferQuery->lastError().number() << "ErrMsg:" << createBufferQuery->lastError().text();
283 BufferId SqliteStorage::getBufferId(UserId user, QString network, QString buffer) {
285 getBufferIdQuery->bindValue(":networkname", network);
286 getBufferIdQuery->bindValue(":userid", user);
287 getBufferIdQuery->bindValue(":buffername", buffer);
288 getBufferIdQuery->exec();
290 if(!getBufferIdQuery->first()) {
291 createBuffer(user, network, buffer);
292 getBufferIdQuery->exec();
293 if(getBufferIdQuery->first()) {
294 bufferid = BufferId(getBufferIdQuery->value(0).toUInt(), network, buffer);
295 emit bufferIdUpdated(bufferid);
298 bufferid = BufferId(getBufferIdQuery->value(0).toUInt(), network, buffer);
301 Q_ASSERT(!getBufferIdQuery->next());
306 QList<BufferId> SqliteStorage::requestBuffers(UserId user, QDateTime since) {
307 QList<BufferId> bufferlist;
308 QSqlQuery query(logDb);
309 query.prepare("SELECT DISTINCT buffer.bufferid, networkname, buffername FROM buffer "
310 "JOIN network ON buffer.networkid = network.networkid "
311 "JOIN backlog ON buffer.bufferid = backlog.bufferid "
312 "WHERE buffer.userid = :userid AND time >= :time");
313 query.bindValue(":userid", user);
314 if (since.isValid()) {
315 query.bindValue(":time", since.toTime_t());
317 query.bindValue(":time", 0);
322 while(query.next()) {
323 bufferlist << BufferId(query.value(0).toUInt(), query.value(1).toString(), query.value(2).toString());
328 MsgId SqliteStorage::logMessage(Message msg) {
329 logMessageQuery->bindValue(":time", msg.timeStamp.toTime_t());
330 logMessageQuery->bindValue(":bufferid", msg.buffer.uid());
331 logMessageQuery->bindValue(":type", msg.type);
332 logMessageQuery->bindValue(":flags", msg.flags);
333 logMessageQuery->bindValue(":sender", msg.sender);
334 logMessageQuery->bindValue(":message", msg.text);
335 logMessageQuery->exec();
337 if(logMessageQuery->lastError().isValid()) {
338 // constraint violation - must be NOT NULL constraint - probably the sender is missing...
339 if(logMessageQuery->lastError().number() == 19) {
340 addSenderQuery->bindValue(":sender", msg.sender);
341 addSenderQuery->exec();
342 logMessageQuery->exec();
343 Q_ASSERT(!logMessageQuery->lastError().isValid());
345 qDebug() << "unhandled DB Error in logMessage(): Number:" << logMessageQuery->lastError().number() << "ErrMsg:" << logMessageQuery->lastError().text();
349 getLastMessageIdQuery->bindValue(":time", msg.timeStamp.toTime_t());
350 getLastMessageIdQuery->bindValue(":bufferid", msg.buffer.uid());
351 getLastMessageIdQuery->bindValue(":type", msg.type);
352 getLastMessageIdQuery->bindValue(":sender", msg.sender);
353 getLastMessageIdQuery->exec();
355 if(getLastMessageIdQuery->first()) {
356 return getLastMessageIdQuery->value(0).toUInt();
357 } else { // somethin went wrong... :(
358 qDebug() << getLastMessageIdQuery->lastQuery() << "time/bufferid/type/sender:" << msg.timeStamp.toTime_t() << msg.buffer.uid() << msg.type << msg.sender;
364 QList<Message> SqliteStorage::requestMsgs(BufferId buffer, int lastmsgs, int offset) {
365 QList<Message> messagelist;
366 // we have to determine the real offset first
367 requestMsgsOffsetQuery->bindValue(":bufferid", buffer.uid());
368 requestMsgsOffsetQuery->bindValue(":messageid", offset);
369 requestMsgsOffsetQuery->exec();
370 requestMsgsOffsetQuery->first();
371 offset = requestMsgsOffsetQuery->value(0).toUInt();
373 // now let's select the messages
374 requestMsgsQuery->bindValue(":bufferid", buffer.uid());
375 requestMsgsQuery->bindValue(":bufferid2", buffer.uid()); // Qt can't handle the same placeholder used twice
376 requestMsgsQuery->bindValue(":limit", lastmsgs);
377 requestMsgsQuery->bindValue(":offset", offset);
378 requestMsgsQuery->exec();
379 while(requestMsgsQuery->next()) {
380 Message msg(QDateTime::fromTime_t(requestMsgsQuery->value(1).toInt()),
382 (Message::Type)requestMsgsQuery->value(2).toUInt(),
383 requestMsgsQuery->value(5).toString(),
384 requestMsgsQuery->value(4).toString(),
385 requestMsgsQuery->value(3).toUInt());
386 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);