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-backlog-newstyle.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 if(!logDb.transaction()) {
39 qWarning(tr("Database driver does not support transactions. This might lead to a corrupt database!").toAscii());
42 // check if the db schema is up to date
43 QSqlQuery query = logDb.exec("SELECT MAX(version) FROM coreinfo");
46 //checkVersion(query.value(0));
47 qDebug() << "Sqlite is ready. Quassel Schema Version:" << query.value(0).toUInt();
52 // we will need those pretty often... so let's speed things up:
53 createBufferQuery = new QSqlQuery();
54 createBufferQuery->prepare("INSERT INTO buffer (userid, networkid, buffername) VALUES (:userid, (SELECT networkid FROM network WHERE networkname = :networkname), :buffername)");
56 createNetworkQuery = new QSqlQuery();
57 createNetworkQuery->prepare("INSERT INTO network (userid, networkname) VALUES (:userid, :networkname)");
59 getBufferIdQuery = new QSqlQuery();
60 getBufferIdQuery->prepare("SELECT bufferid FROM buffer "
61 "JOIN network ON buffer.networkid = network.networkid "
62 "WHERE network.networkname = :networkname AND buffer.userid = :userid AND buffer.buffername = :buffername");
65 logMessageQuery = new QSqlQuery();
66 logMessageQuery->prepare("INSERT INTO backlog (time, bufferid, type, flags, senderid, message) "
67 "VALUES (:time, :bufferid, :type, :flags, (SELECT senderid FROM sender WHERE sender = :sender), :message)");
69 addSenderQuery = new QSqlQuery();
70 addSenderQuery->prepare("INSERT INTO sender (sender) VALUES (:sender)");
72 getLastMessageIdQuery = new QSqlQuery();
73 getLastMessageIdQuery->prepare("SELECT messageid FROM backlog "
74 "WHERE time = :time AND bufferid = :bufferid AND type = :type AND senderid = (SELECT senderid FROM sender WHERE sender = :sender)");
76 requestMsgsOffsetQuery = new QSqlQuery();
77 requestMsgsOffsetQuery->prepare("SELECT count(*) FROM backlog WHERE bufferid = :bufferid AND messageid < :messageid");
79 requestMsgsQuery = new QSqlQuery();
80 requestMsgsQuery->prepare("SELECT messageid, time, type, flags, sender, message, displayname "
82 "JOIN buffer ON backlog.bufferid = buffer.bufferid "
83 "JOIN sender ON backlog.senderid = sender.senderid "
84 "LEFT JOIN buffergroup ON buffer.groupid = buffergroup.groupid "
85 "WHERE buffer.bufferid = :bufferid OR buffer.groupid = (SELECT groupid FROM buffer WHERE bufferid = :bufferid) "
86 "ORDER BY messageid DESC "
87 "LIMIT :limit OFFSET :offset");
89 requestMsgsSinceOffsetQuery = new QSqlQuery();
90 requestMsgsSinceOffsetQuery->prepare("SELECT count(*) FROM backlog WHERE bufferid = :bufferid AND time >= :since");
92 requestMsgsSinceQuery = new QSqlQuery();
93 requestMsgsSinceQuery->prepare("SELECT messageid, time, type, flags, sender, message, displayname "
95 "JOIN buffer ON backlog.bufferid = buffer.bufferid "
96 "JOIN sender ON backlog.senderid = sender.senderid "
97 "LEFT JOIN buffergroup ON buffer.groupid = buffergroup.groupid "
98 "WHERE (buffer.bufferid = :bufferid OR buffer.groupid = (SELECT groupid FROM buffer WHERE bufferid = :bufferid)) AND "
99 "backlog.time >= :since "
100 "ORDER BY messageid DESC "
101 "LIMIT -1 OFFSET :offset");
103 requestMsgRangeQuery = new QSqlQuery();
104 requestMsgRangeQuery->prepare("SELECT messageid, time, type, flags, sender, message, displayname "
106 "JOIN buffer ON backlog.bufferid = buffer.bufferid "
107 "JOIN sender ON backlog.senderid = sender.senderid "
108 "LEFT JOIN buffergroup ON buffer.groupid = buffergroup.groupid "
109 "WHERE (buffer.bufferid = :bufferid OR buffer.groupid = (SELECT groupid FROM buffer WHERE bufferid = :bufferid)) AND "
110 "backlog.messageid >= :firstmsg AND backlog.messageid <= :lastmsg "
111 "ORDER BY messageid DESC ");
115 SqliteStorage::~SqliteStorage() {
116 delete logMessageQuery;
117 delete addSenderQuery;
118 delete getLastMessageIdQuery;
119 delete requestMsgsQuery;
120 delete requestMsgsOffsetQuery;
121 delete requestMsgsSinceQuery;
122 delete requestMsgsSinceOffsetQuery;
123 delete requestMsgRangeQuery;
124 delete createNetworkQuery;
125 delete createBufferQuery;
126 delete getBufferIdQuery;
131 void SqliteStorage::initDb() {
132 logDb.exec("CREATE TABLE quasseluser ("
133 "userid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
134 "username TEXT UNIQUE NOT NULL,"
135 "password BLOB NOT NULL)");
137 logDb.exec("CREATE TABLE sender ("
138 "senderid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
139 "sender TEXT UNIQUE NOT NULL)");
141 logDb.exec("CREATE TABLE network ("
142 "networkid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
143 "userid INTEGER NOT NULL,"
144 "networkname TEXT NOT NULL,"
145 "UNIQUE (userid, networkname))");
147 logDb.exec("CREATE TABLE buffergroup ("
148 "groupid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
149 "userid INTEGER NOT NULL,"
150 "displayname TEXT)");
152 logDb.exec("CREATE TABLE buffer ("
153 "bufferid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
154 "userid INTEGER NOT NULL,"
156 "networkid INTEGER NOT NULL,"
157 "buffername TEXT NOT NULL)");
159 logDb.exec("CREATE UNIQUE INDEX buffer_idx "
160 "ON buffer(userid, networkid, buffername)");
162 logDb.exec("CREATE TABLE backlog ("
163 "messageid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
164 "time INTEGER NOT NULL,"
165 "bufferid INTEGER NOT NULL,"
166 "type INTEGER NOT NULL,"
167 "flags INTEGER NOT NULL,"
168 "senderid INTEGER NOT NULL,"
169 "message TEXT NOT NULL)");
171 logDb.exec("CREATE TABLE coreinfo ("
172 "updateid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
173 "version INTEGER NOT NULL)");
175 logDb.exec("INSERT INTO coreinfo (version) VALUES (0)");
178 // something fucked up -> no logging possible
179 if(logDb.lastError().isValid()) {
180 qWarning(tr("Could not create backlog table: %1").arg(logDb.lastError().text()).toAscii());
181 qWarning(tr("Disabling logging...").toAscii());
183 Q_ASSERT(false); // quassel does require logging
189 bool SqliteStorage::isAvailable() {
190 // oh yes we're available... at least I do hope so :)
194 QString SqliteStorage::displayName() {
195 // I think the class name is a got start here
196 return QString("SqliteStorage");
199 UserId SqliteStorage::addUser(QString user, QString password) {
200 QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
201 cryptopass = cryptopass.toHex();
203 QSqlQuery query(logDb);
204 query.prepare("INSERT INTO quasseluser (username, password) VALUES (:username, :password)");
205 query.bindValue(":username", user);
206 query.bindValue(":password", cryptopass);
208 if(query.lastError().isValid() && query.lastError().number() == 19) { // user already exists - sadly 19 seems to be the general constraint violation error...
213 query.prepare("SELECT userid FROM quasseluser WHERE username = :username");
214 query.bindValue(":username", user);
217 return query.value(0).toUInt();
220 void SqliteStorage::updateUser(UserId user, QString password) {
221 QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
222 cryptopass = cryptopass.toHex();
224 QSqlQuery query(logDb);
225 query.prepare("UPDATE quasseluser SET password = :password WHERE userid = :userid");
226 query.bindValue(":userid", user);
227 query.bindValue(":password", cryptopass);
232 UserId SqliteStorage::validateUser(QString user, QString password) {
233 QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
234 cryptopass = cryptopass.toHex();
236 QSqlQuery query(logDb);
237 query.prepare("SELECT userid FROM quasseluser WHERE username = :username AND password = :password");
238 query.bindValue(":username", user);
239 query.bindValue(":password", cryptopass);
243 return query.value(0).toUInt();
250 void SqliteStorage::delUser(UserId user) {
251 QSqlQuery query(logDb);
252 query.prepare("DELETE FROM backlog WHERE userid = :userid");
253 query.bindValue(":userid", user);
255 query.prepare("DELETE FROM buffer WHERE userid = :userid");
256 query.bindValue(":userid", user);
258 query.prepare("DELETE FROM buffergroup WHERE userid = :userid");
259 query.bindValue(":userid", user);
261 query.prepare("DELETE FROM network WHERE userid = :userid");
262 query.bindValue(":userid", user);
264 query.prepare("DELETE FROM quasseluser WHERE userid = :userid");
265 query.bindValue(":userid", user);
268 // I hate the lack of foreign keys and on delete cascade... :(
271 void SqliteStorage::createBuffer(UserId user, QString network, QString buffer) {
272 qDebug() << "creating buffer:" << user << network << buffer;
273 createBufferQuery->bindValue(":userid", user);
274 createBufferQuery->bindValue(":networkname", network);
275 createBufferQuery->bindValue(":buffername", buffer);
276 createBufferQuery->exec();
278 if(createBufferQuery->lastError().isValid()) {
279 if(createBufferQuery->lastError().number() == 19) { // Null Constraint violation
280 createNetworkQuery->bindValue(":userid", user);
281 createNetworkQuery->bindValue(":networkname", network);
282 createNetworkQuery->exec();
283 createBufferQuery->exec();
284 Q_ASSERT(!createNetworkQuery->lastError().isValid());
285 Q_ASSERT(!createBufferQuery->lastError().isValid());
288 qDebug() << "failed to create Buffer: ErrNo:" << createBufferQuery->lastError().number() << "ErrMsg:" << createBufferQuery->lastError().text();
294 BufferId SqliteStorage::getBufferId(UserId user, QString network, QString buffer) {
295 getBufferIdQuery->bindValue(":networkname", network);
296 getBufferIdQuery->bindValue(":userid", user);
297 getBufferIdQuery->bindValue(":buffername", buffer);
298 getBufferIdQuery->exec();
300 if(!getBufferIdQuery->first()) {
301 createBuffer(user, network, buffer);
302 getBufferIdQuery->exec();
303 Q_ASSERT(getBufferIdQuery->first());
306 return BufferId(getBufferIdQuery->value(0).toUInt(), network, buffer);
309 QList<BufferId> SqliteStorage::requestBuffers(UserId user, QDateTime since) {
310 QList<BufferId> bufferlist;
311 QSqlQuery query(logDb);
312 query.prepare("SELECT bufferid, networkname, buffername FROM buffer "
313 "JOIN network ON buffer.networkid = network.networkid "
314 "JOIN backlog ON buffer.bufferid = backlog.bufferid "
315 "WHERE buffer.userid = :userid AND time >= :time");
316 query.bindValue(":userid", user);
317 query.bindValue(":time", since.toTime_t());
319 while(query.next()) {
320 bufferlist << BufferId(query.value(0).toUInt(), query.value(1).toString(), query.value(2).toString());
325 MsgId SqliteStorage::logMessage(Message msg) {
326 logMessageQuery->bindValue(":time", msg.timeStamp.toTime_t());
327 logMessageQuery->bindValue(":bufferid", msg.buffer.uid());
328 logMessageQuery->bindValue(":type", msg.type);
329 logMessageQuery->bindValue(":flags", msg.flags);
330 logMessageQuery->bindValue(":sender", msg.sender);
331 logMessageQuery->bindValue(":message", msg.text);
332 logMessageQuery->exec();
334 // constraint violation - must be NOT NULL constraint - probably the sender is missing...
335 if(logMessageQuery->lastError().isValid()) {
336 if(logMessageQuery->lastError().number() == 19) {
337 addSenderQuery->bindValue(":sender", msg.sender);
338 addSenderQuery->exec();
339 logMessageQuery->exec();
341 qDebug() << "unhandled DB Error in logMessage(): Number:" << logMessageQuery->lastError().number() << "ErrMsg:" << logMessageQuery->lastError().text();
347 getLastMessageIdQuery->bindValue(":time", msg.timeStamp.toTime_t());
348 getLastMessageIdQuery->bindValue(":bufferid", msg.buffer.uid());
349 getLastMessageIdQuery->bindValue(":type", msg.type);
350 getLastMessageIdQuery->bindValue(":sender", msg.sender);
351 getLastMessageIdQuery->exec();
353 if(getLastMessageIdQuery->first()) {
354 return getLastMessageIdQuery->value(0).toUInt();
355 } else { // somethin went wrong... :(
360 QList<Message> SqliteStorage::requestMsgs(BufferId buffer, int lastmsgs, int offset) {
361 QList<Message> messagelist;
362 // we have to determine the real offset first
363 requestMsgsOffsetQuery->bindValue(":bufferid", buffer.uid());
364 requestMsgsOffsetQuery->bindValue(":messageid", offset);
365 requestMsgsOffsetQuery->exec();
366 requestMsgsOffsetQuery->first();
367 offset = requestMsgsOffsetQuery->value(0).toUInt();
369 // now let's select the messages
370 requestMsgsQuery->bindValue(":bufferid", buffer.uid());
371 requestMsgsQuery->bindValue(":limit", lastmsgs);
372 requestMsgsQuery->bindValue(":offset", offset);
373 requestMsgsQuery->exec();
375 while(requestMsgsQuery->next()) {
376 Message msg(QDateTime::fromTime_t(requestMsgsQuery->value(1).toInt()),
378 (Message::Type)requestMsgsQuery->value(2).toUInt(),
379 requestMsgsQuery->value(5).toString(),
380 requestMsgsQuery->value(4).toString(),
381 requestMsgsQuery->value(3).toUInt());
382 msg.msgId = requestMsgsQuery->value(0).toUInt();
390 QList<Message> SqliteStorage::requestMsgs(BufferId buffer, QDateTime since, int offset) {
391 QList<Message> messagelist;
392 // we have to determine the real offset first
393 requestMsgsSinceOffsetQuery->bindValue(":bufferid", buffer.uid());
394 requestMsgsSinceOffsetQuery->bindValue(":since", since.toTime_t());
395 requestMsgsSinceOffsetQuery->exec();
396 requestMsgsSinceOffsetQuery->first();
397 offset = requestMsgsSinceOffsetQuery->value(0).toUInt();
399 // now let's select the messages
400 requestMsgsSinceQuery->bindValue(":bufferid", buffer.uid());
401 requestMsgsSinceQuery->bindValue(":since", since.toTime_t());
402 requestMsgsSinceQuery->bindValue(":offset", offset);
403 requestMsgsSinceQuery->exec();
405 while(requestMsgsSinceQuery->next()) {
406 Message msg(QDateTime::fromTime_t(requestMsgsSinceQuery->value(1).toInt()),
408 (Message::Type)requestMsgsSinceQuery->value(2).toUInt(),
409 requestMsgsSinceQuery->value(5).toString(),
410 requestMsgsSinceQuery->value(4).toString(),
411 requestMsgsSinceQuery->value(3).toUInt());
412 msg.msgId = requestMsgsSinceQuery->value(0).toUInt();
421 QList<Message> SqliteStorage::requestMsgRange(BufferId buffer, int first, int last) {
422 QList<Message> messagelist;
423 requestMsgRangeQuery->bindValue(":bufferid", buffer.uid());
424 requestMsgRangeQuery->bindValue(":firstmsg", first);
425 requestMsgRangeQuery->bindValue(":lastmsg", last);
427 while(requestMsgRangeQuery->next()) {
428 Message msg(QDateTime::fromTime_t(requestMsgRangeQuery->value(1).toInt()),
430 (Message::Type)requestMsgRangeQuery->value(2).toUInt(),
431 requestMsgRangeQuery->value(5).toString(),
432 requestMsgRangeQuery->value(4).toString(),
433 requestMsgRangeQuery->value(3).toUInt());
434 msg.msgId = requestMsgRangeQuery->value(0).toUInt();
441 void SqliteStorage::importOldBacklog() {
442 QSqlQuery query(logDb);
444 query.prepare("SELECT MIN(userid) FROM quasseluser");
447 qDebug() << "create a user first!";
449 user = query.value(0).toUInt();
451 query.prepare("DELETE FROM backlog WHERE userid = :userid");
452 query.bindValue(":userid", user);
454 query.prepare("DELETE FROM buffer WHERE userid = :userid");
455 query.bindValue(":userid", user);
457 query.prepare("DELETE FROM buffergroup WHERE userid = :userid");
458 query.bindValue(":userid", user);
460 query.prepare("DELETE FROM network WHERE userid = :userid");
461 query.bindValue(":userid", user);
464 qDebug() << "All userdata has been deleted";
465 qDebug() << "importing old backlog files...";
466 initBackLogOld(user);