1 /***************************************************************************
2 * Copyright (C) 2005-07 by the Quassel Project *
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) version 3. *
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"
29 int SqliteStorage::_maxRetryCount = 150; // yes this is a large number... only other way to "handle" this is bailing out...
31 SqliteStorage::SqliteStorage(QObject *parent)
32 : AbstractSqlStorage(parent)
36 SqliteStorage::~SqliteStorage() {
39 bool SqliteStorage::isAvailable() const {
40 if(!QSqlDatabase::isDriverAvailable("QSQLITE")) return false;
44 QString SqliteStorage::displayName() const {
45 return QString("SQLite");
48 QString SqliteStorage::description() const {
49 return tr("SQLite is a file-based database engine that does not require any setup. It is suitable for small and medium-sized "
50 "databases that do not require access via network. Use SQLite if your Quassel Core should store its data on the same machine "
51 "it is running on, and if you only expect a few users to use your core.");
54 int SqliteStorage::installedSchemaVersion() {
55 QSqlQuery query = logDb().exec("SELECT value FROM coreinfo WHERE key = 'schemaversion'");
57 return query.value(0).toInt();
59 // maybe it's really old... (schema version 0)
60 query = logDb().exec("SELECT MAX(version) FROM coreinfo");
62 return query.value(0).toInt();
64 return AbstractSqlStorage::installedSchemaVersion();
67 UserId SqliteStorage::addUser(const QString &user, const QString &password) {
68 QSqlQuery query(logDb());
69 query.prepare(queryString("insert_quasseluser"));
70 query.bindValue(":username", user);
71 query.bindValue(":password", cryptedPassword(password));
73 if(query.lastError().isValid() && query.lastError().number() == 19) { // user already exists - sadly 19 seems to be the general constraint violation error...
77 query.prepare(queryString("select_userid"));
78 query.bindValue(":username", user);
81 UserId uid = query.value(0).toInt();
82 emit userAdded(uid, user);
86 void SqliteStorage::updateUser(UserId user, const QString &password) {
87 QSqlQuery query(logDb());
88 query.prepare(queryString("update_userpassword"));
89 query.bindValue(":userid", user.toInt());
90 query.bindValue(":password", cryptedPassword(password));
94 void SqliteStorage::renameUser(UserId user, const QString &newName) {
95 QSqlQuery query(logDb());
96 query.prepare(queryString("update_username"));
97 query.bindValue(":userid", user.toInt());
98 query.bindValue(":username", newName);
100 emit userRenamed(user, newName);
103 UserId SqliteStorage::validateUser(const QString &user, const QString &password) {
104 QSqlQuery query(logDb());
105 query.prepare(queryString("select_authuser"));
106 query.bindValue(":username", user);
107 query.bindValue(":password", cryptedPassword(password));
111 return query.value(0).toInt();
117 UserId SqliteStorage::internalUser() {
118 QSqlQuery query(logDb());
119 query.prepare(queryString("select_internaluser"));
123 return query.value(0).toInt();
129 void SqliteStorage::delUser(UserId user) {
130 QSqlQuery query(logDb());
131 query.prepare(queryString("delete_backlog_by_uid"));
132 query.bindValue(":userid", user.toInt());
135 query.prepare(queryString("delete_buffers_by_uid"));
136 query.bindValue(":userid", user.toInt());
139 query.prepare(queryString("delete_networks_by_uid"));
140 query.bindValue(":userid", user.toInt());
143 query.prepare(queryString("delete_quasseluser"));
144 query.bindValue(":userid", user.toInt());
146 // I hate the lack of foreign keys and on delete cascade... :(
147 emit userRemoved(user);
150 void SqliteStorage::setUserSetting(UserId userId, const QString &settingName, const QVariant &data) {
152 QDataStream out(&rawData, QIODevice::WriteOnly);
153 out.setVersion(QDataStream::Qt_4_2);
156 QSqlQuery query(logDb());
157 query.prepare(queryString("insert_user_setting"));
158 query.bindValue(":userid", userId.toInt());
159 query.bindValue(":settingname", settingName);
160 query.bindValue(":settingvalue", rawData);
163 if(query.lastError().isValid()) {
164 QSqlQuery updateQuery(logDb());
165 updateQuery.prepare(queryString("update_user_setting"));
166 updateQuery.bindValue(":userid", userId.toInt());
167 updateQuery.bindValue(":settingname", settingName);
168 updateQuery.bindValue(":settingvalue", rawData);
169 safeExec(updateQuery);
174 QVariant SqliteStorage::getUserSetting(UserId userId, const QString &settingName, const QVariant &defaultData) {
175 QSqlQuery query(logDb());
176 query.prepare(queryString("select_user_setting"));
177 query.bindValue(":userid", userId.toInt());
178 query.bindValue(":settingname", settingName);
183 QByteArray rawData = query.value(0).toByteArray();
184 QDataStream in(&rawData, QIODevice::ReadOnly);
185 in.setVersion(QDataStream::Qt_4_2);
193 IdentityId SqliteStorage::createIdentity(UserId user, CoreIdentity &identity) {
194 IdentityId identityId;
196 QSqlQuery query(logDb());
197 query.prepare(queryString("insert_identity"));
198 query.bindValue(":userid", user.toInt());
199 query.bindValue(":identityname", identity.identityName());
200 query.bindValue(":realname", identity.realName());
201 query.bindValue(":awaynick", identity.awayNick());
202 query.bindValue(":awaynickenabled", identity.awayNickEnabled() ? 1 : 0);
203 query.bindValue(":awayreason", identity.awayReason());
204 query.bindValue(":awayreasonenabled", identity.awayReasonEnabled() ? 1 : 0);
205 query.bindValue(":autoawayenabled", identity.awayReasonEnabled() ? 1 : 0);
206 query.bindValue(":autoawaytime", identity.autoAwayTime());
207 query.bindValue(":autoawayreason", identity.autoAwayReason());
208 query.bindValue(":autoawayreasonenabled", identity.autoAwayReasonEnabled() ? 1 : 0);
209 query.bindValue(":detachawayenabled", identity.detachAwayEnabled() ? 1 : 0);
210 query.bindValue(":detachawayreason", identity.detachAwayReason());
211 query.bindValue(":detachawayreasonenabled", identity.detachAwayReasonEnabled() ? 1 : 0);
212 query.bindValue(":ident", identity.ident());
213 query.bindValue(":kickreason", identity.kickReason());
214 query.bindValue(":partreason", identity.partReason());
215 query.bindValue(":quitreason", identity.quitReason());
217 query.bindValue(":sslcert", identity.sslCert().toPem());
218 query.bindValue(":sslkey", identity.sslKey().toPem());
220 query.bindValue(":sslcert", QByteArray());
221 query.bindValue(":sslkey", QByteArray());
226 identityId = query.lastInsertId().toInt();
227 if(!identityId.isValid()) {
230 QSqlQuery deleteNickQuery(logDb());
231 deleteNickQuery.prepare(queryString("delete_nicks"));
232 deleteNickQuery.bindValue(":identityid", identityId.toInt());
233 safeExec(deleteNickQuery);
235 QSqlQuery insertNickQuery(logDb());
236 insertNickQuery.prepare(queryString("insert_nick"));
237 foreach(QString nick, identity.nicks()) {
238 insertNickQuery.bindValue(":identityid", identityId.toInt());
239 insertNickQuery.bindValue(":nick", nick);
240 safeExec(insertNickQuery);
243 identity.setId(identityId);
247 bool SqliteStorage::updateIdentity(UserId user, const CoreIdentity &identity) {
248 QSqlQuery checkQuery(logDb());
249 checkQuery.prepare(queryString("select_checkidentity"));
250 checkQuery.bindValue(":identityid", identity.id().toInt());
251 checkQuery.bindValue(":userid", user.toInt());
252 safeExec(checkQuery);
254 if(!checkQuery.first() || checkQuery.value(0).toInt() != 1) // there should be exactly one identity for the given id and user
257 QSqlQuery query(logDb());
258 query.prepare(queryString("update_identity"));
259 query.bindValue(":identityname", identity.identityName());
260 query.bindValue(":realname", identity.realName());
261 query.bindValue(":awaynick", identity.awayNick());
262 query.bindValue(":awaynickenabled", identity.awayNickEnabled() ? 1 : 0);
263 query.bindValue(":awayreason", identity.awayReason());
264 query.bindValue(":awayreasonenabled", identity.awayReasonEnabled() ? 1 : 0);
265 query.bindValue(":autoawayenabled", identity.awayReasonEnabled() ? 1 : 0);
266 query.bindValue(":autoawaytime", identity.autoAwayTime());
267 query.bindValue(":autoawayreason", identity.autoAwayReason());
268 query.bindValue(":autoawayreasonenabled", identity.autoAwayReasonEnabled() ? 1 : 0);
269 query.bindValue(":detachawayenabled", identity.detachAwayEnabled() ? 1 : 0);
270 query.bindValue(":detachawayreason", identity.detachAwayReason());
271 query.bindValue(":detachawayreasonenabled", identity.detachAwayReasonEnabled() ? 1 : 0);
272 query.bindValue(":ident", identity.ident());
273 query.bindValue(":kickreason", identity.kickReason());
274 query.bindValue(":partreason", identity.partReason());
275 query.bindValue(":quitreason", identity.quitReason());
277 query.bindValue(":sslcert", identity.sslCert().toPem());
278 query.bindValue(":sslkey", identity.sslKey().toPem());
280 query.bindValue(":sslcert", QByteArray());
281 query.bindValue(":sslkey", QByteArray());
283 query.bindValue(":identityid", identity.id().toInt());
287 QSqlQuery deleteNickQuery(logDb());
288 deleteNickQuery.prepare(queryString("delete_nicks"));
289 deleteNickQuery.bindValue(":identityid", identity.id().toInt());
290 safeExec(deleteNickQuery);
291 watchQuery(deleteNickQuery);
293 QSqlQuery insertNickQuery(logDb());
294 insertNickQuery.prepare(queryString("insert_nick"));
295 foreach(QString nick, identity.nicks()) {
296 insertNickQuery.bindValue(":identityid", identity.id().toInt());
297 insertNickQuery.bindValue(":nick", nick);
298 safeExec(insertNickQuery);
299 watchQuery(insertNickQuery);
305 void SqliteStorage::removeIdentity(UserId user, IdentityId identityId) {
306 QSqlQuery checkQuery(logDb());
307 checkQuery.prepare(queryString("select_checkidentity"));
308 checkQuery.bindValue(":identityid", identityId.toInt());
309 checkQuery.bindValue(":userid", user.toInt());
310 safeExec(checkQuery);
312 if(!checkQuery.first() || checkQuery.value(0).toInt() != 1) // there should be exactly one identity for the given id and user
315 QSqlQuery deleteNickQuery(logDb());
316 deleteNickQuery.prepare(queryString("delete_nicks"));
317 deleteNickQuery.bindValue(":identityid", identityId.toInt());
318 safeExec(deleteNickQuery);
320 QSqlQuery deleteIdentityQuery(logDb());
321 deleteIdentityQuery.prepare(queryString("delete_identity"));
322 deleteIdentityQuery.bindValue(":identityid", identityId.toInt());
323 deleteIdentityQuery.bindValue(":userid", user.toInt());
324 safeExec(deleteIdentityQuery);
327 QList<CoreIdentity> SqliteStorage::identities(UserId user) {
328 QList<CoreIdentity> identities;
330 QSqlQuery query(logDb());
331 query.prepare(queryString("select_identities"));
332 query.bindValue(":userid", user.toInt());
334 QSqlQuery nickQuery(logDb());
335 nickQuery.prepare(queryString("select_nicks"));
339 while(query.next()) {
340 CoreIdentity identity(IdentityId(query.value(0).toInt()));
342 identity.setIdentityName(query.value(1).toString());
343 identity.setRealName(query.value(2).toString());
344 identity.setAwayNick(query.value(3).toString());
345 identity.setAwayNickEnabled(!!query.value(4).toInt());
346 identity.setAwayReason(query.value(5).toString());
347 identity.setAwayReasonEnabled(!!query.value(6).toInt());
348 identity.setAutoAwayEnabled(!!query.value(7).toInt());
349 identity.setAutoAwayTime(query.value(8).toInt());
350 identity.setAutoAwayReason(query.value(9).toString());
351 identity.setAutoAwayReasonEnabled(!!query.value(10).toInt());
352 identity.setDetachAwayEnabled(!!query.value(11).toInt());
353 identity.setDetachAwayReason(query.value(12).toString());
354 identity.setDetachAwayReasonEnabled(!!query.value(13).toInt());
355 identity.setIdent(query.value(14).toString());
356 identity.setKickReason(query.value(15).toString());
357 identity.setPartReason(query.value(16).toString());
358 identity.setQuitReason(query.value(17).toString());
360 identity.setSslCert(query.value(18).toByteArray());
361 identity.setSslKey(query.value(19).toByteArray());
364 nickQuery.bindValue(":identityid", identity.id().toInt());
365 QList<QString> nicks;
367 watchQuery(nickQuery);
368 while(nickQuery.next()) {
369 nicks << nickQuery.value(0).toString();
371 identity.setNicks(nicks);
372 identities << identity;
377 NetworkId SqliteStorage::createNetwork(UserId user, const NetworkInfo &info) {
379 QSqlQuery query(logDb());
380 query.prepare(queryString("insert_network"));
381 query.bindValue(":userid", user.toInt());
382 query.bindValue(":networkname", info.networkName);
385 networkId = getNetworkId(user, info.networkName);
386 if(!networkId.isValid()) {
389 NetworkInfo newNetworkInfo = info;
390 newNetworkInfo.networkId = networkId;
391 updateNetwork(user, newNetworkInfo);
396 bool SqliteStorage::updateNetwork(UserId user, const NetworkInfo &info) {
397 if(!isValidNetwork(user, info.networkId))
400 QSqlQuery updateQuery(logDb());
401 updateQuery.prepare(queryString("update_network"));
402 updateQuery.bindValue(":networkname", info.networkName);
403 updateQuery.bindValue(":identityid", info.identity.toInt());
404 updateQuery.bindValue(":usecustomencoding", info.useCustomEncodings ? 1 : 0);
405 updateQuery.bindValue(":encodingcodec", QString(info.codecForEncoding));
406 updateQuery.bindValue(":decodingcodec", QString(info.codecForDecoding));
407 updateQuery.bindValue(":servercodec", QString(info.codecForServer));
408 updateQuery.bindValue(":userandomserver", info.useRandomServer ? 1 : 0);
409 updateQuery.bindValue(":perform", info.perform.join("\n"));
410 updateQuery.bindValue(":useautoidentify", info.useAutoIdentify ? 1 : 0);
411 updateQuery.bindValue(":autoidentifyservice", info.autoIdentifyService);
412 updateQuery.bindValue(":autoidentifypassword", info.autoIdentifyPassword);
413 updateQuery.bindValue(":useautoreconnect", info.useAutoReconnect ? 1 : 0);
414 updateQuery.bindValue(":autoreconnectinterval", info.autoReconnectInterval);
415 updateQuery.bindValue(":autoreconnectretries", info.autoReconnectRetries);
416 updateQuery.bindValue(":unlimitedconnectretries", info.unlimitedReconnectRetries ? 1 : 0);
417 updateQuery.bindValue(":rejoinchannels", info.rejoinChannels ? 1 : 0);
418 updateQuery.bindValue(":networkid", info.networkId.toInt());
419 safeExec(updateQuery);
420 if(!watchQuery(updateQuery))
423 QSqlQuery dropServersQuery(logDb());
424 dropServersQuery.prepare("DELETE FROM ircserver WHERE networkid = :networkid");
425 dropServersQuery.bindValue(":networkid", info.networkId.toInt());
426 safeExec(dropServersQuery);
427 if(!watchQuery(dropServersQuery))
430 QSqlQuery insertServersQuery(logDb());
431 insertServersQuery.prepare(queryString("insert_server"));
432 foreach(Network::Server server, info.serverList) {
433 insertServersQuery.bindValue(":userid", user.toInt());
434 insertServersQuery.bindValue(":networkid", info.networkId.toInt());
435 insertServersQuery.bindValue(":hostname", server.host);
436 insertServersQuery.bindValue(":port", server.port);
437 insertServersQuery.bindValue(":password", server.password);
438 insertServersQuery.bindValue(":ssl", server.useSsl ? 1 : 0);
439 insertServersQuery.bindValue(":sslversion", server.sslVersion);
440 insertServersQuery.bindValue(":useproxy", server.useProxy ? 1 : 0);
441 insertServersQuery.bindValue(":proxytype", server.proxyType);
442 insertServersQuery.bindValue(":proxyhost", server.proxyHost);
443 insertServersQuery.bindValue(":proxyport", server.proxyPort);
444 insertServersQuery.bindValue(":proxyuser", server.proxyUser);
445 insertServersQuery.bindValue(":proxypass", server.proxyPass);
447 safeExec(insertServersQuery);
448 if(!watchQuery(insertServersQuery))
455 bool SqliteStorage::removeNetwork(UserId user, const NetworkId &networkId) {
456 if(!isValidNetwork(user, networkId))
459 bool withTransaction = logDb().driver()->hasFeature(QSqlDriver::Transactions);
460 if(withTransaction) {
462 if(!logDb().transaction()) {
463 qWarning() << "SqliteStorage::removeNetwork(): cannot start transaction. continuing with out rollback support!";
464 withTransaction = false;
468 QSqlQuery deleteBacklogQuery(logDb());
469 deleteBacklogQuery.prepare(queryString("delete_backlog_for_network"));
470 deleteBacklogQuery.bindValue(":networkid", networkId.toInt());
471 safeExec(deleteBacklogQuery);
472 if(!watchQuery(deleteBacklogQuery)) {
478 QSqlQuery deleteBuffersQuery(logDb());
479 deleteBuffersQuery.prepare(queryString("delete_buffers_for_network"));
480 deleteBuffersQuery.bindValue(":networkid", networkId.toInt());
481 safeExec(deleteBuffersQuery);
482 if(!watchQuery(deleteBuffersQuery)) {
488 QSqlQuery deleteServersQuery(logDb());
489 deleteServersQuery.prepare(queryString("delete_ircservers_for_network"));
490 deleteServersQuery.bindValue(":networkid", networkId.toInt());
491 safeExec(deleteServersQuery);
492 if(!watchQuery(deleteServersQuery)) {
498 QSqlQuery deleteNetworkQuery(logDb());
499 deleteNetworkQuery.prepare(queryString("delete_network"));
500 deleteNetworkQuery.bindValue(":networkid", networkId.toInt());
501 safeExec(deleteNetworkQuery);
502 if(!watchQuery(deleteNetworkQuery)) {
512 QList<NetworkInfo> SqliteStorage::networks(UserId user) {
513 QList<NetworkInfo> nets;
515 QSqlQuery networksQuery(logDb());
516 networksQuery.prepare(queryString("select_networks_for_user"));
517 networksQuery.bindValue(":userid", user.toInt());
519 QSqlQuery serversQuery(logDb());
520 serversQuery.prepare(queryString("select_servers_for_network"));
522 safeExec(networksQuery);
523 if(!watchQuery(networksQuery))
526 while(networksQuery.next()) {
528 net.networkId = networksQuery.value(0).toInt();
529 net.networkName = networksQuery.value(1).toString();
530 net.identity = networksQuery.value(2).toInt();
531 net.codecForServer = networksQuery.value(3).toString().toAscii();
532 net.codecForEncoding = networksQuery.value(4).toString().toAscii();
533 net.codecForDecoding = networksQuery.value(5).toString().toAscii();
534 net.useRandomServer = networksQuery.value(6).toInt() == 1 ? true : false;
535 net.perform = networksQuery.value(7).toString().split("\n");
536 net.useAutoIdentify = networksQuery.value(8).toInt() == 1 ? true : false;
537 net.autoIdentifyService = networksQuery.value(9).toString();
538 net.autoIdentifyPassword = networksQuery.value(10).toString();
539 net.useAutoReconnect = networksQuery.value(11).toInt() == 1 ? true : false;
540 net.autoReconnectInterval = networksQuery.value(12).toUInt();
541 net.autoReconnectRetries = networksQuery.value(13).toInt();
542 net.unlimitedReconnectRetries = networksQuery.value(14).toInt() == 1 ? true : false;
543 net.rejoinChannels = networksQuery.value(15).toInt() == 1 ? true : false;
545 serversQuery.bindValue(":networkid", net.networkId.toInt());
546 safeExec(serversQuery);
547 if(!watchQuery(serversQuery))
550 Network::ServerList servers;
551 while(serversQuery.next()) {
552 Network::Server server;
553 server.host = serversQuery.value(0).toString();
554 server.port = serversQuery.value(1).toUInt();
555 server.password = serversQuery.value(2).toString();
556 server.useSsl = serversQuery.value(3).toInt() == 1 ? true : false;
557 server.sslVersion = serversQuery.value(4).toInt();
558 server.useProxy = serversQuery.value(5).toInt() == 1 ? true : false;
559 server.proxyType = serversQuery.value(6).toInt();
560 server.proxyHost = serversQuery.value(7).toString();
561 server.proxyPort = serversQuery.value(8).toUInt();
562 server.proxyUser = serversQuery.value(9).toString();
563 server.proxyPass = serversQuery.value(10).toString();
566 net.serverList = servers;
572 bool SqliteStorage::isValidNetwork(UserId user, const NetworkId &networkId) {
573 QSqlQuery query(logDb());
574 query.prepare(queryString("select_networkExists"));
575 query.bindValue(":userid", user.toInt());
576 query.bindValue(":networkid", networkId.toInt());
579 watchQuery(query); // there should not occur any errors
583 Q_ASSERT(!query.next());
587 bool SqliteStorage::isValidBuffer(const UserId &user, const BufferId &bufferId) {
588 QSqlQuery query(logDb());
589 query.prepare(queryString("select_bufferExists"));
590 query.bindValue(":userid", user.toInt());
591 query.bindValue(":bufferid", bufferId.toInt());
598 Q_ASSERT(!query.next());
602 NetworkId SqliteStorage::getNetworkId(UserId user, const QString &network) {
603 QSqlQuery query(logDb());
604 query.prepare("SELECT networkid FROM network "
605 "WHERE userid = :userid AND networkname = :networkname");
606 query.bindValue(":userid", user.toInt());
607 query.bindValue(":networkname", network);
611 return query.value(0).toInt();
616 QList<NetworkId> SqliteStorage::connectedNetworks(UserId user) {
617 QList<NetworkId> connectedNets;
618 QSqlQuery query(logDb());
619 query.prepare(queryString("select_connected_networks"));
620 query.bindValue(":userid", user.toInt());
624 while(query.next()) {
625 connectedNets << query.value(0).toInt();
628 return connectedNets;
631 void SqliteStorage::setNetworkConnected(UserId user, const NetworkId &networkId, bool isConnected) {
632 QSqlQuery query(logDb());
633 query.prepare(queryString("update_network_connected"));
634 query.bindValue(":userid", user.toInt());
635 query.bindValue(":networkid", networkId.toInt());
636 query.bindValue(":connected", isConnected ? 1 : 0);
641 QHash<QString, QString> SqliteStorage::persistentChannels(UserId user, const NetworkId &networkId) {
642 QHash<QString, QString> persistentChans;
643 QSqlQuery query(logDb());
644 query.prepare(queryString("select_persistent_channels"));
645 query.bindValue(":userid", user.toInt());
646 query.bindValue(":networkid", networkId.toInt());
650 while(query.next()) {
651 persistentChans[query.value(0).toString()] = query.value(1).toString();
654 return persistentChans;
657 void SqliteStorage::setChannelPersistent(UserId user, const NetworkId &networkId, const QString &channel, bool isJoined) {
658 QSqlQuery query(logDb());
659 query.prepare(queryString("update_buffer_persistent_channel"));
660 query.bindValue(":userid", user.toInt());
661 query.bindValue(":networkId", networkId.toInt());
662 query.bindValue(":buffercname", channel.toLower());
663 query.bindValue(":joined", isJoined ? 1 : 0);
668 void SqliteStorage::setPersistentChannelKey(UserId user, const NetworkId &networkId, const QString &channel, const QString &key) {
669 QSqlQuery query(logDb());
670 query.prepare(queryString("update_buffer_set_channel_key"));
671 query.bindValue(":userid", user.toInt());
672 query.bindValue(":networkId", networkId.toInt());
673 query.bindValue(":buffercname", channel.toLower());
674 query.bindValue(":key", key);
680 void SqliteStorage::createBuffer(UserId user, const NetworkId &networkId, BufferInfo::Type type, const QString &buffer) {
681 QSqlQuery query(logDb());
682 query.prepare(queryString("insert_buffer"));
683 query.bindValue(":userid", user.toInt());
684 query.bindValue(":networkid", networkId.toInt());
685 query.bindValue(":buffertype", (int)type);
686 query.bindValue(":buffername", buffer);
687 query.bindValue(":buffercname", buffer.toLower());
693 BufferInfo SqliteStorage::bufferInfo(UserId user, const NetworkId &networkId, BufferInfo::Type type, const QString &buffer, bool create) {
694 QSqlQuery query(logDb());
695 query.prepare(queryString("select_bufferByName"));
696 query.bindValue(":networkid", networkId.toInt());
697 query.bindValue(":userid", user.toInt());
698 query.bindValue(":buffercname", buffer.toLower());
705 createBuffer(user, networkId, type, buffer);
709 qWarning() << "unable to create BufferInfo for:" << user << networkId << buffer;
714 BufferInfo bufferInfo = BufferInfo(query.value(0).toInt(), networkId, (BufferInfo::Type)query.value(1).toInt(), 0, buffer);
716 qCritical() << "SqliteStorage::getBufferInfo(): received more then one Buffer!";
717 qCritical() << " Query:" << query.lastQuery();
718 qCritical() << " bound Values:";
719 QList<QVariant> list = query.boundValues().values();
720 for (int i = 0; i < list.size(); ++i)
721 qCritical() << i << ":" << list.at(i).toString().toAscii().data();
728 BufferInfo SqliteStorage::getBufferInfo(UserId user, const BufferId &bufferId) {
729 QSqlQuery query(logDb());
730 query.prepare(queryString("select_buffer_by_id"));
731 query.bindValue(":userid", user.toInt());
732 query.bindValue(":bufferid", bufferId.toInt());
734 if(!watchQuery(query))
740 BufferInfo bufferInfo(query.value(0).toInt(), query.value(1).toInt(), (BufferInfo::Type)query.value(2).toInt(), 0, query.value(4).toString());
741 Q_ASSERT(!query.next());
746 QList<BufferInfo> SqliteStorage::requestBuffers(UserId user) {
747 QList<BufferInfo> bufferlist;
748 QSqlQuery query(logDb());
749 query.prepare(queryString("select_buffers"));
750 query.bindValue(":userid", user.toInt());
754 while(query.next()) {
755 bufferlist << BufferInfo(query.value(0).toInt(), query.value(1).toInt(), (BufferInfo::Type)query.value(2).toInt(), query.value(3).toInt(), query.value(4).toString());
760 QList<BufferId> SqliteStorage::requestBufferIdsForNetwork(UserId user, NetworkId networkId) {
761 QList<BufferId> bufferList;
762 QSqlQuery query(logDb());
763 query.prepare(queryString("select_buffers_for_network"));
764 query.bindValue(":networkid", networkId.toInt());
765 query.bindValue(":userid", user.toInt());
769 while(query.next()) {
770 bufferList << BufferId(query.value(0).toInt());
775 bool SqliteStorage::removeBuffer(const UserId &user, const BufferId &bufferId) {
776 if(!isValidBuffer(user, bufferId))
779 QSqlQuery delBacklogQuery(logDb());
780 delBacklogQuery.prepare(queryString("delete_backlog_for_buffer"));
781 delBacklogQuery.bindValue(":bufferid", bufferId.toInt());
782 safeExec(delBacklogQuery);
783 if(!watchQuery(delBacklogQuery))
786 QSqlQuery delBufferQuery(logDb());
787 delBufferQuery.prepare(queryString("delete_buffer_for_bufferid"));
788 delBufferQuery.bindValue(":bufferid", bufferId.toInt());
789 safeExec(delBufferQuery);
790 if(!watchQuery(delBufferQuery))
796 bool SqliteStorage::renameBuffer(const UserId &user, const BufferId &bufferId, const QString &newName) {
797 if(!isValidBuffer(user, bufferId))
800 QSqlQuery query(logDb());
801 query.prepare(queryString("update_buffer_name"));
802 query.bindValue(":buffername", newName);
803 query.bindValue(":buffercname", newName.toLower());
804 query.bindValue(":bufferid", bufferId.toInt());
806 if(query.lastError().isValid()) {
807 // unexepcted error occured (19 == constraint violation)
808 if(query.lastError().number() != 19)
816 bool SqliteStorage::mergeBuffersPermanently(const UserId &user, const BufferId &bufferId1, const BufferId &bufferId2) {
817 if(!isValidBuffer(user, bufferId1) || !isValidBuffer(user, bufferId2))
820 QSqlQuery query(logDb());
821 query.prepare(queryString("update_backlog_bufferid"));
822 query.bindValue(":oldbufferid", bufferId2.toInt());
823 query.bindValue(":newbufferid", bufferId1.toInt());
825 if(!watchQuery(query))
828 QSqlQuery delBufferQuery(logDb());
829 delBufferQuery.prepare(queryString("delete_buffer_for_bufferid"));
830 delBufferQuery.bindValue(":bufferid", bufferId2.toInt());
831 safeExec(delBufferQuery);
832 watchQuery(delBufferQuery);
837 void SqliteStorage::setBufferLastSeenMsg(UserId user, const BufferId &bufferId, const MsgId &msgId) {
838 QSqlQuery query(logDb());
839 query.prepare(queryString("update_buffer_lastseen"));
841 query.bindValue(":userid", user.toInt());
842 query.bindValue(":bufferid", bufferId.toInt());
843 query.bindValue(":lastseenmsgid", msgId.toInt());
848 QHash<BufferId, MsgId> SqliteStorage::bufferLastSeenMsgIds(UserId user) {
849 QHash<BufferId, MsgId> lastSeenHash;
850 QSqlQuery query(logDb());
851 query.prepare(queryString("select_buffer_lastseen_messages"));
852 query.bindValue(":userid", user.toInt());
854 if(!watchQuery(query))
857 while(query.next()) {
858 lastSeenHash[query.value(0).toInt()] = query.value(1).toInt();
863 MsgId SqliteStorage::logMessage(Message msg) {
864 QSqlQuery logMessageQuery(logDb());
865 logMessageQuery.prepare(queryString("insert_message"));
867 logMessageQuery.bindValue(":time", msg.timestamp().toTime_t());
868 logMessageQuery.bindValue(":bufferid", msg.bufferInfo().bufferId().toInt());
869 logMessageQuery.bindValue(":type", msg.type());
870 logMessageQuery.bindValue(":flags", (int)msg.flags());
871 logMessageQuery.bindValue(":sender", msg.sender());
872 logMessageQuery.bindValue(":message", msg.contents());
873 safeExec(logMessageQuery);
875 if(logMessageQuery.lastError().isValid()) {
876 // constraint violation - must be NOT NULL constraint - probably the sender is missing...
877 if(logMessageQuery.lastError().number() == 19) {
878 QSqlQuery addSenderQuery(logDb());
879 addSenderQuery.prepare(queryString("insert_sender"));
880 addSenderQuery.bindValue(":sender", msg.sender());
881 safeExec(addSenderQuery);
882 safeExec(logMessageQuery);
883 if(!watchQuery(logMessageQuery))
886 watchQuery(logMessageQuery);
890 MsgId msgId = logMessageQuery.lastInsertId().toInt();
891 Q_ASSERT(msgId.isValid());
895 QList<Message> SqliteStorage::requestMsgs(UserId user, BufferId bufferId, MsgId first, MsgId last, int limit) {
896 QList<Message> messagelist;
898 BufferInfo bufferInfo = getBufferInfo(user, bufferId);
899 if(!bufferInfo.isValid())
902 QSqlQuery query(logDb());
904 if(last == -1 && first == -1) {
905 query.prepare(queryString("select_messagesNewestK"));
906 } else if(last == -1) {
907 query.prepare(queryString("select_messagesNewerThan"));
908 query.bindValue(":firstmsg", first.toInt());
910 query.prepare(queryString("select_messages"));
911 query.bindValue(":lastmsg", last.toInt());
912 query.bindValue(":firstmsg", first.toInt());
915 query.bindValue(":bufferid", bufferId.toInt());
916 query.bindValue(":limit", limit);
921 while(query.next()) {
922 Message msg(QDateTime::fromTime_t(query.value(1).toInt()),
924 (Message::Type)query.value(2).toUInt(),
925 query.value(5).toString(),
926 query.value(4).toString(),
927 (Message::Flags)query.value(3).toUInt());
928 msg.setMsgId(query.value(0).toInt());
934 QList<Message> SqliteStorage::requestAllMsgs(UserId user, MsgId first, MsgId last, int limit) {
935 QList<Message> messagelist;
937 QHash<BufferId, BufferInfo> bufferInfoHash;
938 foreach(BufferInfo bufferInfo, requestBuffers(user)) {
939 bufferInfoHash[bufferInfo.bufferId()] = bufferInfo;
942 QSqlQuery query(logDb());
944 query.prepare(queryString("select_messagesAllNew"));
946 query.prepare(queryString("select_messagesAll"));
947 query.bindValue(":lastmsg", last.toInt());
949 query.bindValue(":userid", user.toInt());
950 query.bindValue(":firstmsg", first.toInt());
951 query.bindValue(":limit", limit);
956 while(query.next()) {
957 Message msg(QDateTime::fromTime_t(query.value(2).toInt()),
958 bufferInfoHash[query.value(1).toInt()],
959 (Message::Type)query.value(3).toUInt(),
960 query.value(6).toString(),
961 query.value(5).toString(),
962 (Message::Flags)query.value(4).toUInt());
963 msg.setMsgId(query.value(0).toInt());
970 QString SqliteStorage::backlogFile() {
971 return Quassel::configDirPath() + "quassel-storage.sqlite";
974 bool SqliteStorage::safeExec(QSqlQuery &query, int retryCount) {
977 if(!query.lastError().isValid())
980 switch(query.lastError().number()) {
981 case 5: // SQLITE_BUSY 5 /* The database file is locked */
982 case 6: // SQLITE_LOCKED 6 /* A table in the database is locked */
983 if(retryCount < _maxRetryCount)
984 return safeExec(query, retryCount + 1);