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 getBufferInfoQuery = new QSqlQuery(logDb);
57 getBufferInfoQuery->prepare("SELECT bufferid FROM buffer "
58 "JOIN network ON buffer.networkid = network.networkid "
59 "WHERE network.networkname = :networkname AND buffer.userid = :userid AND lower(buffer.buffername) = lower(: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() {
113 delete logMessageQuery;
114 delete addSenderQuery;
115 delete getLastMessageIdQuery;
116 delete requestMsgsQuery;
117 delete requestMsgsOffsetQuery;
118 delete requestMsgsSinceQuery;
119 delete requestMsgsSinceOffsetQuery;
120 delete requestMsgRangeQuery;
121 delete createNetworkQuery;
122 delete createBufferQuery;
123 delete getBufferInfoQuery;
128 void SqliteStorage::initDb() {
129 logDb.exec("CREATE TABLE quasseluser ("
130 "userid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
131 "username TEXT UNIQUE NOT NULL,"
132 "password BLOB NOT NULL)");
134 logDb.exec("CREATE TABLE sender ("
135 "senderid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
136 "sender TEXT UNIQUE NOT NULL)");
138 logDb.exec("CREATE TABLE network ("
139 "networkid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
140 "userid INTEGER NOT NULL,"
141 "networkname TEXT NOT NULL,"
142 "UNIQUE (userid, networkname))");
144 logDb.exec("CREATE TABLE buffergroup ("
145 "groupid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
146 "userid INTEGER NOT NULL,"
147 "displayname TEXT)");
149 logDb.exec("CREATE TABLE buffer ("
150 "bufferid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
151 "userid INTEGER NOT NULL,"
153 "networkid INTEGER NOT NULL,"
154 "buffername TEXT NOT NULL)");
156 logDb.exec("CREATE UNIQUE INDEX buffer_idx "
157 "ON buffer(userid, networkid, buffername)");
159 logDb.exec("CREATE TABLE backlog ("
160 "messageid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
161 "time INTEGER NOT NULL,"
162 "bufferid INTEGER NOT NULL,"
163 "type INTEGER NOT NULL,"
164 "flags INTEGER NOT NULL,"
165 "senderid INTEGER NOT NULL,"
168 logDb.exec("CREATE TABLE coreinfo ("
169 "updateid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
170 "version INTEGER NOT NULL)");
172 logDb.exec("INSERT INTO coreinfo (version) VALUES (0)");
175 // something fucked up -> no logging possible
176 // FIXME logDb.lastError is reset whenever exec is called
177 if(logDb.lastError().isValid()) {
178 qWarning(tr("Could not create backlog table: %1").arg(logDb.lastError().text()).toAscii());
179 qWarning(tr("Disabling logging...").toAscii());
180 Q_ASSERT(false); // quassel does require logging
183 addUser("Default", "password");
186 bool SqliteStorage::isAvailable() {
187 if(!QSqlDatabase::isDriverAvailable("QSQLITE")) return false;
191 QString SqliteStorage::displayName() {
192 return QString("SqliteStorage");
195 UserId SqliteStorage::addUser(const QString &user, const QString &password) {
196 QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
197 cryptopass = cryptopass.toHex();
199 QSqlQuery query(logDb);
200 query.prepare("INSERT INTO quasseluser (username, password) VALUES (:username, :password)");
201 query.bindValue(":username", user);
202 query.bindValue(":password", cryptopass);
204 if(query.lastError().isValid() && query.lastError().number() == 19) { // user already exists - sadly 19 seems to be the general constraint violation error...
208 query.prepare("SELECT userid FROM quasseluser WHERE username = :username");
209 query.bindValue(":username", user);
212 UserId uid = query.value(0).toUInt();
213 emit userAdded(uid, user);
217 void SqliteStorage::updateUser(UserId user, const QString &password) {
218 QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
219 cryptopass = cryptopass.toHex();
221 QSqlQuery query(logDb);
222 query.prepare("UPDATE quasseluser SET password = :password WHERE userid = :userid");
223 query.bindValue(":userid", user);
224 query.bindValue(":password", cryptopass);
228 void SqliteStorage::renameUser(UserId user, const QString &newName) {
229 QSqlQuery query(logDb);
230 query.prepare("UPDATE quasseluser SET username = :username WHERE userid = :userid");
231 query.bindValue(":userid", user);
232 query.bindValue(":username", newName);
234 emit userRenamed(user, newName);
237 UserId SqliteStorage::validateUser(const QString &user, const QString &password) {
238 QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
239 cryptopass = cryptopass.toHex();
241 QSqlQuery query(logDb);
242 query.prepare("SELECT userid FROM quasseluser WHERE username = :username AND password = :password");
243 query.bindValue(":username", user);
244 query.bindValue(":password", cryptopass);
248 return query.value(0).toUInt();
255 void SqliteStorage::delUser(UserId user) {
256 QSqlQuery query(logDb);
257 query.prepare("DELETE FROM backlog WHERE bufferid IN (SELECT DISTINCT bufferid FROM buffer WHERE userid = :userid");
258 query.bindValue(":userid", user);
260 query.prepare("DELETE FROM buffer WHERE userid = :userid");
261 query.bindValue(":userid", user);
263 query.prepare("DELETE FROM buffergroup WHERE userid = :userid");
264 query.bindValue(":userid", user);
266 query.prepare("DELETE FROM network WHERE userid = :userid");
267 query.bindValue(":userid", user);
269 query.prepare("DELETE FROM quasseluser WHERE userid = :userid");
270 query.bindValue(":userid", user);
272 // I hate the lack of foreign keys and on delete cascade... :(
273 emit userRemoved(user);
276 void SqliteStorage::createBuffer(UserId user, const QString &network, const QString &buffer) {
277 createBufferQuery->bindValue(":userid", user);
278 createBufferQuery->bindValue(":networkname", network);
279 createBufferQuery->bindValue(":buffername", buffer);
280 createBufferQuery->exec();
282 if(createBufferQuery->lastError().isValid()) {
283 if(createBufferQuery->lastError().number() == 19) { // Null Constraint violation
284 createNetworkQuery->bindValue(":userid", user);
285 createNetworkQuery->bindValue(":networkname", network);
286 createNetworkQuery->exec();
287 createBufferQuery->exec();
288 Q_ASSERT(!createNetworkQuery->lastError().isValid());
289 Q_ASSERT(!createBufferQuery->lastError().isValid());
292 qDebug() << "failed to create Buffer: ErrNo:" << createBufferQuery->lastError().number() << "ErrMsg:" << createBufferQuery->lastError().text();
298 uint SqliteStorage::getNetworkId(UserId user, const QString &network) {
299 QSqlQuery query(logDb);
300 query.prepare("SELECT networkid FROM network "
301 "WHERE userid = :userid AND networkname = :networkname");
302 query.bindValue(":userid", user);
303 query.bindValue(":networkname", network);
307 return query.value(0).toUInt();
309 createBuffer(user, network, "");
312 return query.value(0).toUInt();
314 qWarning() << "NETWORK NOT FOUND:" << network << "for User:" << user;
320 BufferInfo SqliteStorage::getBufferInfo(UserId user, const QString &network, const QString &buffer) {
322 // TODO: get rid of this hackaround
323 uint networkId = getNetworkId(user, network);
324 getBufferInfoQuery->bindValue(":networkname", network);
325 getBufferInfoQuery->bindValue(":userid", user);
326 getBufferInfoQuery->bindValue(":buffername", buffer);
327 getBufferInfoQuery->exec();
329 if(!getBufferInfoQuery->first()) {
330 createBuffer(user, network, buffer);
331 getBufferInfoQuery->exec();
332 if(getBufferInfoQuery->first()) {
333 bufferid = BufferInfo(getBufferInfoQuery->value(0).toUInt(), networkId, 0, network, buffer);
334 emit bufferInfoUpdated(bufferid);
337 bufferid = BufferInfo(getBufferInfoQuery->value(0).toUInt(), networkId, 0, network, buffer);
340 Q_ASSERT(!getBufferInfoQuery->next());
345 QList<BufferInfo> SqliteStorage::requestBuffers(UserId user, QDateTime since) {
346 QList<BufferInfo> bufferlist;
347 QSqlQuery query(logDb);
348 query.prepare("SELECT DISTINCT buffer.bufferid, networkname, buffername FROM buffer "
349 "JOIN network ON buffer.networkid = network.networkid "
350 "JOIN backlog ON buffer.bufferid = backlog.bufferid "
351 "WHERE buffer.userid = :userid AND time >= :time");
352 query.bindValue(":userid", user);
353 if (since.isValid()) {
354 query.bindValue(":time", since.toTime_t());
356 query.bindValue(":time", 0);
361 while(query.next()) {
362 bufferlist << BufferInfo(query.value(0).toUInt(), getNetworkId(user, query.value(1).toString()), 0, query.value(1).toString(), query.value(2).toString());
367 MsgId SqliteStorage::logMessage(Message msg) {
368 logMessageQuery->bindValue(":time", msg.timestamp().toTime_t());
369 logMessageQuery->bindValue(":bufferid", msg.buffer().uid());
370 logMessageQuery->bindValue(":type", msg.type());
371 logMessageQuery->bindValue(":flags", msg.flags());
372 logMessageQuery->bindValue(":sender", msg.sender());
373 logMessageQuery->bindValue(":message", msg.text());
374 logMessageQuery->exec();
376 if(logMessageQuery->lastError().isValid()) {
377 // constraint violation - must be NOT NULL constraint - probably the sender is missing...
378 if(logMessageQuery->lastError().number() == 19) {
379 addSenderQuery->bindValue(":sender", msg.sender());
380 addSenderQuery->exec();
381 watchQuery(addSenderQuery);
382 logMessageQuery->exec();
383 if(!watchQuery(logMessageQuery))
386 watchQuery(logMessageQuery);
390 getLastMessageIdQuery->bindValue(":time", msg.timestamp().toTime_t());
391 getLastMessageIdQuery->bindValue(":bufferid", msg.buffer().uid());
392 getLastMessageIdQuery->bindValue(":type", msg.type());
393 getLastMessageIdQuery->bindValue(":sender", msg.sender());
394 getLastMessageIdQuery->exec();
396 if(getLastMessageIdQuery->first()) {
397 return getLastMessageIdQuery->value(0).toUInt();
398 } else { // somethin went wrong... :(
399 qDebug() << getLastMessageIdQuery->lastQuery() << "time/bufferid/type/sender:" << msg.timestamp().toTime_t() << msg.buffer().uid() << msg.type() << msg.sender();
405 QList<Message> SqliteStorage::requestMsgs(BufferInfo buffer, int lastmsgs, int offset) {
406 QList<Message> messagelist;
407 // we have to determine the real offset first
408 requestMsgsOffsetQuery->bindValue(":bufferid", buffer.uid());
409 requestMsgsOffsetQuery->bindValue(":messageid", offset);
410 requestMsgsOffsetQuery->exec();
411 requestMsgsOffsetQuery->first();
412 offset = requestMsgsOffsetQuery->value(0).toUInt();
414 // now let's select the messages
415 requestMsgsQuery->bindValue(":bufferid", buffer.uid());
416 requestMsgsQuery->bindValue(":bufferid2", buffer.uid()); // Qt can't handle the same placeholder used twice
417 requestMsgsQuery->bindValue(":limit", lastmsgs);
418 requestMsgsQuery->bindValue(":offset", offset);
419 requestMsgsQuery->exec();
420 while(requestMsgsQuery->next()) {
421 Message msg(QDateTime::fromTime_t(requestMsgsQuery->value(1).toInt()),
423 (Message::Type)requestMsgsQuery->value(2).toUInt(),
424 requestMsgsQuery->value(5).toString(),
425 requestMsgsQuery->value(4).toString(),
426 requestMsgsQuery->value(3).toUInt());
427 msg.setMsgId(requestMsgsQuery->value(0).toUInt());
434 QList<Message> SqliteStorage::requestMsgs(BufferInfo buffer, QDateTime since, int offset) {
435 QList<Message> messagelist;
436 // we have to determine the real offset first
437 requestMsgsSinceOffsetQuery->bindValue(":bufferid", buffer.uid());
438 requestMsgsSinceOffsetQuery->bindValue(":since", since.toTime_t());
439 requestMsgsSinceOffsetQuery->exec();
440 requestMsgsSinceOffsetQuery->first();
441 offset = requestMsgsSinceOffsetQuery->value(0).toUInt();
443 // now let's select the messages
444 requestMsgsSinceQuery->bindValue(":bufferid", buffer.uid());
445 requestMsgsSinceQuery->bindValue(":bufferid2", buffer.uid());
446 requestMsgsSinceQuery->bindValue(":since", since.toTime_t());
447 requestMsgsSinceQuery->bindValue(":offset", offset);
448 requestMsgsSinceQuery->exec();
450 while(requestMsgsSinceQuery->next()) {
451 Message msg(QDateTime::fromTime_t(requestMsgsSinceQuery->value(1).toInt()),
453 (Message::Type)requestMsgsSinceQuery->value(2).toUInt(),
454 requestMsgsSinceQuery->value(5).toString(),
455 requestMsgsSinceQuery->value(4).toString(),
456 requestMsgsSinceQuery->value(3).toUInt());
457 msg.setMsgId(requestMsgsSinceQuery->value(0).toUInt());
465 QList<Message> SqliteStorage::requestMsgRange(BufferInfo buffer, int first, int last) {
466 QList<Message> messagelist;
467 requestMsgRangeQuery->bindValue(":bufferid", buffer.uid());
468 requestMsgRangeQuery->bindValue(":bufferid2", buffer.uid());
469 requestMsgRangeQuery->bindValue(":firstmsg", first);
470 requestMsgRangeQuery->bindValue(":lastmsg", last);
472 while(requestMsgRangeQuery->next()) {
473 Message msg(QDateTime::fromTime_t(requestMsgRangeQuery->value(1).toInt()),
475 (Message::Type)requestMsgRangeQuery->value(2).toUInt(),
476 requestMsgRangeQuery->value(5).toString(),
477 requestMsgRangeQuery->value(4).toString(),
478 requestMsgRangeQuery->value(3).toUInt());
479 msg.setMsgId(requestMsgRangeQuery->value(0).toUInt());
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 query.prepare("DELETE FROM backlog WHERE bufferid IN (SELECT DISTINCT bufferid FROM buffer WHERE userid = :userid");
497 query.bindValue(":userid", user);
499 query.prepare("DELETE FROM buffer WHERE userid = :userid");
500 query.bindValue(":userid", user);
502 query.prepare("DELETE FROM buffergroup WHERE userid = :userid");
503 query.bindValue(":userid", user);
505 query.prepare("DELETE FROM network WHERE userid = :userid");
506 query.bindValue(":userid", user);
509 qDebug() << "All userdata has been deleted";
510 qDebug() << "importing old backlog files...";
511 initBackLogOld(user);
517 bool SqliteStorage::watchQuery(QSqlQuery *query) {
518 if(query->lastError().isValid()) {
519 qWarning() << "unhandled Error in QSqlQuery!";
520 qWarning() << " last Query:" << query->lastQuery();
521 qWarning() << " executed Query:" << query->executedQuery();
522 qWarning() << " bound Values:" << query->boundValues();
523 qWarning() << " Error Number:" << query->lastError().number();
524 qWarning() << " Error Message:" << query->lastError().text();
525 qWarning() << " Driver Message:" << query->lastError().driverText();
526 qWarning() << " DB Message:" << query->lastError().databaseText();