Quassel IRC  Pre-Release
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Properties Friends Macros
sqlitestorage.cpp
Go to the documentation of this file.
1 /***************************************************************************
2  * Copyright (C) 2005-2015 by the Quassel Project *
3  * devel@quassel-irc.org *
4  * *
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. *
9  * *
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. *
14  * *
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  * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. *
19  ***************************************************************************/
20 
21 #include "sqlitestorage.h"
22 
23 #include <QtSql>
24 
25 #include "logger.h"
26 #include "network.h"
27 #include "quassel.h"
28 
30 
32  : AbstractSqlStorage(parent)
33 {
34 }
35 
36 
38 {
39 }
40 
41 
43 {
44  if (!QSqlDatabase::isDriverAvailable("QSQLITE")) return false;
45  return true;
46 }
47 
48 
50 {
51  // We identify the backend to use for the monolithic core by its displayname.
52  // so only change this string if you _really_ have to and make sure the core
53  // setup for the mono client still works ;)
54  return QString("SQLite");
55 }
56 
57 
59 {
60  return tr("SQLite is a file-based database engine that does not require any setup. It is suitable for small and medium-sized "
61  "databases that do not require access via network. Use SQLite if your Quassel Core should store its data on the same machine "
62  "it is running on, and if you only expect a few users to use your core.");
63 }
64 
65 
67 {
68  // only used when there is a singlethread (during startup)
69  // so we don't need locking here
70  QSqlQuery query = logDb().exec("SELECT value FROM coreinfo WHERE key = 'schemaversion'");
71  if (query.first())
72  return query.value(0).toInt();
73 
74  // maybe it's really old... (schema version 0)
75  query = logDb().exec("SELECT MAX(version) FROM coreinfo");
76  if (query.first())
77  return query.value(0).toInt();
78 
80 }
81 
82 
84 {
85  // only used when there is a singlethread (during startup)
86  // so we don't need locking here
87  QSqlQuery query(logDb());
88  query.prepare("UPDATE coreinfo SET value = :version WHERE key = 'schemaversion'");
89  query.bindValue(":version", newVersion);
90  query.exec();
91 
92  bool success = true;
93  if (query.lastError().isValid()) {
94  qCritical() << "SqliteStorage::updateSchemaVersion(int): Updating schema version failed!";
95  success = false;
96  }
97  return success;
98 }
99 
100 
102 {
103  // only used when there is a singlethread (during startup)
104  // so we don't need locking here
105  QSqlQuery query(logDb());
106  query.prepare("INSERT INTO coreinfo (key, value) VALUES ('schemaversion', :version)");
107  query.bindValue(":version", version);
108  query.exec();
109 
110  bool success = true;
111  if (query.lastError().isValid()) {
112  qCritical() << "SqliteStorage::setupSchemaVersion(int): Updating schema version failed!";
113  success = false;
114  }
115  return success;
116 }
117 
118 
119 UserId SqliteStorage::addUser(const QString &user, const QString &password)
120 {
121  QSqlDatabase db = logDb();
122  UserId uid;
123 
124  db.transaction();
125  // this scope ensures that the query is freed in sqlite before we call unlock()
126  // this ensures that our thread doesn't hold a internal after unlock is called
127  // (see sqlites doc on implicit locking for details)
128  {
129  QSqlQuery query(db);
130  query.prepare(queryString("insert_quasseluser"));
131  query.bindValue(":username", user);
132  query.bindValue(":password", hashPassword(password));
133  query.bindValue(":hashversion", Storage::HashVersion::Latest);
134  lockForWrite();
135  safeExec(query);
136  if (query.lastError().isValid() && query.lastError().number() == 19) { // user already exists - sadly 19 seems to be the general constraint violation error...
137  db.rollback();
138  }
139  else {
140  uid = query.lastInsertId().toInt();
141  db.commit();
142  }
143  }
144  unlock();
145 
146  if (uid.isValid())
147  emit userAdded(uid, user);
148  return uid;
149 }
150 
151 
152 bool SqliteStorage::updateUser(UserId user, const QString &password)
153 {
154  QSqlDatabase db = logDb();
155  bool success = false;
156 
157  db.transaction();
158  {
159  QSqlQuery query(db);
160  query.prepare(queryString("update_userpassword"));
161  query.bindValue(":userid", user.toInt());
162  query.bindValue(":password", hashPassword(password));
163  query.bindValue(":hashversion", Storage::HashVersion::Latest);
164  lockForWrite();
165  safeExec(query);
166  success = query.numRowsAffected() != 0;
167  db.commit();
168  }
169  unlock();
170  return success;
171 }
172 
173 
174 void SqliteStorage::renameUser(UserId user, const QString &newName)
175 {
176  QSqlDatabase db = logDb();
177  db.transaction();
178  {
179  QSqlQuery query(db);
180  query.prepare(queryString("update_username"));
181  query.bindValue(":userid", user.toInt());
182  query.bindValue(":username", newName);
183  lockForWrite();
184  safeExec(query);
185  db.commit();
186  }
187  unlock();
188  emit userRenamed(user, newName);
189 }
190 
191 
192 UserId SqliteStorage::validateUser(const QString &user, const QString &password)
193 {
194  UserId userId;
195  QString hashedPassword;
196  Storage::HashVersion hashVersion;
197 
198  {
199  QSqlQuery query(logDb());
200  query.prepare(queryString("select_authuser"));
201  query.bindValue(":username", user);
202 
203  lockForRead();
204  safeExec(query);
205 
206  if (query.first()) {
207  userId = query.value(0).toInt();
208  hashedPassword = query.value(1).toString();
209  hashVersion = static_cast<Storage::HashVersion>(query.value(2).toInt());
210  }
211  }
212  unlock();
213 
214  UserId returnUserId;
215  if (userId != 0 && checkHashedPassword(userId, password, hashedPassword, hashVersion)) {
216  returnUserId = userId;
217  }
218  return returnUserId;
219 }
220 
221 
222 UserId SqliteStorage::getUserId(const QString &username)
223 {
224  UserId userId;
225 
226  {
227  QSqlQuery query(logDb());
228  query.prepare(queryString("select_userid"));
229  query.bindValue(":username", username);
230 
231  lockForRead();
232  safeExec(query);
233 
234  if (query.first()) {
235  userId = query.value(0).toInt();
236  }
237  }
238  unlock();
239 
240  return userId;
241 }
242 
243 
245 {
246  UserId userId;
247 
248  {
249  QSqlQuery query(logDb());
250  query.prepare(queryString("select_internaluser"));
251  lockForRead();
252  safeExec(query);
253 
254  if (query.first()) {
255  userId = query.value(0).toInt();
256  }
257  }
258  unlock();
259 
260  return userId;
261 }
262 
263 
265 {
266  QSqlDatabase db = logDb();
267  db.transaction();
268 
269  lockForWrite();
270  {
271  QSqlQuery query(db);
272  query.prepare(queryString("delete_backlog_by_uid"));
273  query.bindValue(":userid", user.toInt());
274  safeExec(query);
275 
276  query.prepare(queryString("delete_buffers_by_uid"));
277  query.bindValue(":userid", user.toInt());
278  safeExec(query);
279 
280  query.prepare(queryString("delete_networks_by_uid"));
281  query.bindValue(":userid", user.toInt());
282  safeExec(query);
283 
284  query.prepare(queryString("delete_quasseluser"));
285  query.bindValue(":userid", user.toInt());
286  safeExec(query);
287  // I hate the lack of foreign keys and on delete cascade... :(
288  db.commit();
289  }
290  unlock();
291 
292  emit userRemoved(user);
293 }
294 
295 
296 void SqliteStorage::setUserSetting(UserId userId, const QString &settingName, const QVariant &data)
297 {
298  QByteArray rawData;
299  QDataStream out(&rawData, QIODevice::WriteOnly);
300  out.setVersion(QDataStream::Qt_4_2);
301  out << data;
302 
303  QSqlDatabase db = logDb();
304  db.transaction();
305  {
306  QSqlQuery query(db);
307  query.prepare(queryString("insert_user_setting"));
308  query.bindValue(":userid", userId.toInt());
309  query.bindValue(":settingname", settingName);
310  query.bindValue(":settingvalue", rawData);
311  lockForWrite();
312  safeExec(query);
313 
314  if (query.lastError().isValid()) {
315  QSqlQuery updateQuery(db);
316  updateQuery.prepare(queryString("update_user_setting"));
317  updateQuery.bindValue(":userid", userId.toInt());
318  updateQuery.bindValue(":settingname", settingName);
319  updateQuery.bindValue(":settingvalue", rawData);
320  safeExec(updateQuery);
321  }
322  db.commit();
323  }
324  unlock();
325 }
326 
327 
328 QVariant SqliteStorage::getUserSetting(UserId userId, const QString &settingName, const QVariant &defaultData)
329 {
330  QVariant data = defaultData;
331  {
332  QSqlQuery query(logDb());
333  query.prepare(queryString("select_user_setting"));
334  query.bindValue(":userid", userId.toInt());
335  query.bindValue(":settingname", settingName);
336  lockForRead();
337  safeExec(query);
338 
339  if (query.first()) {
340  QByteArray rawData = query.value(0).toByteArray();
341  QDataStream in(&rawData, QIODevice::ReadOnly);
342  in.setVersion(QDataStream::Qt_4_2);
343  in >> data;
344  }
345  }
346  unlock();
347  return data;
348 }
349 
350 
352 {
353  IdentityId identityId;
354 
355  QSqlDatabase db = logDb();
356  db.transaction();
357 
358  {
359  QSqlQuery query(db);
360  query.prepare(queryString("insert_identity"));
361  query.bindValue(":userid", user.toInt());
362  query.bindValue(":identityname", identity.identityName());
363  query.bindValue(":realname", identity.realName());
364  query.bindValue(":awaynick", identity.awayNick());
365  query.bindValue(":awaynickenabled", identity.awayNickEnabled() ? 1 : 0);
366  query.bindValue(":awayreason", identity.awayReason());
367  query.bindValue(":awayreasonenabled", identity.awayReasonEnabled() ? 1 : 0);
368  query.bindValue(":autoawayenabled", identity.awayReasonEnabled() ? 1 : 0);
369  query.bindValue(":autoawaytime", identity.autoAwayTime());
370  query.bindValue(":autoawayreason", identity.autoAwayReason());
371  query.bindValue(":autoawayreasonenabled", identity.autoAwayReasonEnabled() ? 1 : 0);
372  query.bindValue(":detachawayenabled", identity.detachAwayEnabled() ? 1 : 0);
373  query.bindValue(":detachawayreason", identity.detachAwayReason());
374  query.bindValue(":detachawayreasonenabled", identity.detachAwayReasonEnabled() ? 1 : 0);
375  query.bindValue(":ident", identity.ident());
376  query.bindValue(":kickreason", identity.kickReason());
377  query.bindValue(":partreason", identity.partReason());
378  query.bindValue(":quitreason", identity.quitReason());
379 #ifdef HAVE_SSL
380  query.bindValue(":sslcert", identity.sslCert().toPem());
381  query.bindValue(":sslkey", identity.sslKey().toPem());
382 #else
383  query.bindValue(":sslcert", QByteArray());
384  query.bindValue(":sslkey", QByteArray());
385 #endif
386 
387  lockForWrite();
388  safeExec(query);
389 
390  identityId = query.lastInsertId().toInt();
391  if (!identityId.isValid()) {
392  watchQuery(query);
393  }
394  else {
395  QSqlQuery deleteNickQuery(db);
396  deleteNickQuery.prepare(queryString("delete_nicks"));
397  deleteNickQuery.bindValue(":identityid", identityId.toInt());
398  safeExec(deleteNickQuery);
399 
400  QSqlQuery insertNickQuery(db);
401  insertNickQuery.prepare(queryString("insert_nick"));
402  foreach(QString nick, identity.nicks()) {
403  insertNickQuery.bindValue(":identityid", identityId.toInt());
404  insertNickQuery.bindValue(":nick", nick);
405  safeExec(insertNickQuery);
406  }
407  }
408  db.commit();
409  }
410  unlock();
411  identity.setId(identityId);
412  return identityId;
413 }
414 
415 
417 {
418  QSqlDatabase db = logDb();
419  bool error = false;
420  db.transaction();
421 
422  {
423  QSqlQuery checkQuery(db);
424  checkQuery.prepare(queryString("select_checkidentity"));
425  checkQuery.bindValue(":identityid", identity.id().toInt());
426  checkQuery.bindValue(":userid", user.toInt());
427  lockForRead();
428  safeExec(checkQuery);
429 
430  // there should be exactly one identity for the given id and user
431  error = (!checkQuery.first() || checkQuery.value(0).toInt() != 1);
432  }
433  if (error) {
434  unlock();
435  return false;
436  }
437 
438  {
439  QSqlQuery query(db);
440  query.prepare(queryString("update_identity"));
441  query.bindValue(":identityname", identity.identityName());
442  query.bindValue(":realname", identity.realName());
443  query.bindValue(":awaynick", identity.awayNick());
444  query.bindValue(":awaynickenabled", identity.awayNickEnabled() ? 1 : 0);
445  query.bindValue(":awayreason", identity.awayReason());
446  query.bindValue(":awayreasonenabled", identity.awayReasonEnabled() ? 1 : 0);
447  query.bindValue(":autoawayenabled", identity.awayReasonEnabled() ? 1 : 0);
448  query.bindValue(":autoawaytime", identity.autoAwayTime());
449  query.bindValue(":autoawayreason", identity.autoAwayReason());
450  query.bindValue(":autoawayreasonenabled", identity.autoAwayReasonEnabled() ? 1 : 0);
451  query.bindValue(":detachawayenabled", identity.detachAwayEnabled() ? 1 : 0);
452  query.bindValue(":detachawayreason", identity.detachAwayReason());
453  query.bindValue(":detachawayreasonenabled", identity.detachAwayReasonEnabled() ? 1 : 0);
454  query.bindValue(":ident", identity.ident());
455  query.bindValue(":kickreason", identity.kickReason());
456  query.bindValue(":partreason", identity.partReason());
457  query.bindValue(":quitreason", identity.quitReason());
458 #ifdef HAVE_SSL
459  query.bindValue(":sslcert", identity.sslCert().toPem());
460  query.bindValue(":sslkey", identity.sslKey().toPem());
461 #else
462  query.bindValue(":sslcert", QByteArray());
463  query.bindValue(":sslkey", QByteArray());
464 #endif
465  query.bindValue(":identityid", identity.id().toInt());
466  safeExec(query);
467  watchQuery(query);
468 
469  QSqlQuery deleteNickQuery(db);
470  deleteNickQuery.prepare(queryString("delete_nicks"));
471  deleteNickQuery.bindValue(":identityid", identity.id().toInt());
472  safeExec(deleteNickQuery);
473  watchQuery(deleteNickQuery);
474 
475  QSqlQuery insertNickQuery(db);
476  insertNickQuery.prepare(queryString("insert_nick"));
477  foreach(QString nick, identity.nicks()) {
478  insertNickQuery.bindValue(":identityid", identity.id().toInt());
479  insertNickQuery.bindValue(":nick", nick);
480  safeExec(insertNickQuery);
481  watchQuery(insertNickQuery);
482  }
483  db.commit();
484  }
485  unlock();
486  return true;
487 }
488 
489 
491 {
492  QSqlDatabase db = logDb();
493  db.transaction();
494 
495  bool error = false;
496  {
497  QSqlQuery checkQuery(db);
498  checkQuery.prepare(queryString("select_checkidentity"));
499  checkQuery.bindValue(":identityid", identityId.toInt());
500  checkQuery.bindValue(":userid", user.toInt());
501  lockForRead();
502  safeExec(checkQuery);
503 
504  // there should be exactly one identity for the given id and user
505  error = (!checkQuery.first() || checkQuery.value(0).toInt() != 1);
506  }
507  if (error) {
508  unlock();
509  return;
510  }
511 
512  {
513  QSqlQuery deleteNickQuery(db);
514  deleteNickQuery.prepare(queryString("delete_nicks"));
515  deleteNickQuery.bindValue(":identityid", identityId.toInt());
516  safeExec(deleteNickQuery);
517 
518  QSqlQuery deleteIdentityQuery(db);
519  deleteIdentityQuery.prepare(queryString("delete_identity"));
520  deleteIdentityQuery.bindValue(":identityid", identityId.toInt());
521  deleteIdentityQuery.bindValue(":userid", user.toInt());
522  safeExec(deleteIdentityQuery);
523  db.commit();
524  }
525  unlock();
526 }
527 
528 
529 QList<CoreIdentity> SqliteStorage::identities(UserId user)
530 {
531  QList<CoreIdentity> identities;
532  QSqlDatabase db = logDb();
533  db.transaction();
534 
535  {
536  QSqlQuery query(db);
537  query.prepare(queryString("select_identities"));
538  query.bindValue(":userid", user.toInt());
539 
540  QSqlQuery nickQuery(db);
541  nickQuery.prepare(queryString("select_nicks"));
542 
543  lockForRead();
544  safeExec(query);
545 
546  while (query.next()) {
547  CoreIdentity identity(IdentityId(query.value(0).toInt()));
548 
549  identity.setIdentityName(query.value(1).toString());
550  identity.setRealName(query.value(2).toString());
551  identity.setAwayNick(query.value(3).toString());
552  identity.setAwayNickEnabled(!!query.value(4).toInt());
553  identity.setAwayReason(query.value(5).toString());
554  identity.setAwayReasonEnabled(!!query.value(6).toInt());
555  identity.setAutoAwayEnabled(!!query.value(7).toInt());
556  identity.setAutoAwayTime(query.value(8).toInt());
557  identity.setAutoAwayReason(query.value(9).toString());
558  identity.setAutoAwayReasonEnabled(!!query.value(10).toInt());
559  identity.setDetachAwayEnabled(!!query.value(11).toInt());
560  identity.setDetachAwayReason(query.value(12).toString());
561  identity.setDetachAwayReasonEnabled(!!query.value(13).toInt());
562  identity.setIdent(query.value(14).toString());
563  identity.setKickReason(query.value(15).toString());
564  identity.setPartReason(query.value(16).toString());
565  identity.setQuitReason(query.value(17).toString());
566 #ifdef HAVE_SSL
567  identity.setSslCert(query.value(18).toByteArray());
568  identity.setSslKey(query.value(19).toByteArray());
569 #endif
570 
571  nickQuery.bindValue(":identityid", identity.id().toInt());
572  QList<QString> nicks;
573  safeExec(nickQuery);
574  watchQuery(nickQuery);
575  while (nickQuery.next()) {
576  nicks << nickQuery.value(0).toString();
577  }
578  identity.setNicks(nicks);
579  identities << identity;
580  }
581  db.commit();
582  }
583  unlock();
584  return identities;
585 }
586 
587 
589 {
590  NetworkId networkId;
591 
592  QSqlDatabase db = logDb();
593  db.transaction();
594 
595  bool error = false;
596  {
597  QSqlQuery query(db);
598  query.prepare(queryString("insert_network"));
599  query.bindValue(":userid", user.toInt());
600  bindNetworkInfo(query, info);
601  lockForWrite();
602  safeExec(query);
603  if (!watchQuery(query)) {
604  db.rollback();
605  error = true;
606  }
607  else {
608  networkId = query.lastInsertId().toInt();
609  }
610  }
611  if (error) {
612  unlock();
613  return NetworkId();
614  }
615 
616  {
617  QSqlQuery insertServersQuery(db);
618  insertServersQuery.prepare(queryString("insert_server"));
619  foreach(Network::Server server, info.serverList) {
620  insertServersQuery.bindValue(":userid", user.toInt());
621  insertServersQuery.bindValue(":networkid", networkId.toInt());
622  bindServerInfo(insertServersQuery, server);
623  safeExec(insertServersQuery);
624  if (!watchQuery(insertServersQuery)) {
625  db.rollback();
626  error = true;
627  break;
628  }
629  }
630  if (!error)
631  db.commit();
632  }
633  unlock();
634  if (error)
635  return NetworkId();
636  else
637  return networkId;
638 }
639 
640 
641 void SqliteStorage::bindNetworkInfo(QSqlQuery &query, const NetworkInfo &info)
642 {
643  query.bindValue(":networkname", info.networkName);
644  query.bindValue(":identityid", info.identity.toInt());
645  query.bindValue(":encodingcodec", QString(info.codecForEncoding));
646  query.bindValue(":decodingcodec", QString(info.codecForDecoding));
647  query.bindValue(":servercodec", QString(info.codecForServer));
648  query.bindValue(":userandomserver", info.useRandomServer ? 1 : 0);
649  query.bindValue(":perform", info.perform.join("\n"));
650  query.bindValue(":useautoidentify", info.useAutoIdentify ? 1 : 0);
651  query.bindValue(":autoidentifyservice", info.autoIdentifyService);
652  query.bindValue(":autoidentifypassword", info.autoIdentifyPassword);
653  query.bindValue(":usesasl", info.useSasl ? 1 : 0);
654  query.bindValue(":saslaccount", info.saslAccount);
655  query.bindValue(":saslpassword", info.saslPassword);
656  query.bindValue(":useautoreconnect", info.useAutoReconnect ? 1 : 0);
657  query.bindValue(":autoreconnectinterval", info.autoReconnectInterval);
658  query.bindValue(":autoreconnectretries", info.autoReconnectRetries);
659  query.bindValue(":unlimitedconnectretries", info.unlimitedReconnectRetries ? 1 : 0);
660  query.bindValue(":rejoinchannels", info.rejoinChannels ? 1 : 0);
661  if (info.networkId.isValid())
662  query.bindValue(":networkid", info.networkId.toInt());
663 }
664 
665 
667 {
668  query.bindValue(":hostname", server.host);
669  query.bindValue(":port", server.port);
670  query.bindValue(":password", server.password);
671  query.bindValue(":ssl", server.useSsl ? 1 : 0);
672  query.bindValue(":sslversion", server.sslVersion);
673  query.bindValue(":useproxy", server.useProxy ? 1 : 0);
674  query.bindValue(":proxytype", server.proxyType);
675  query.bindValue(":proxyhost", server.proxyHost);
676  query.bindValue(":proxyport", server.proxyPort);
677  query.bindValue(":proxyuser", server.proxyUser);
678  query.bindValue(":proxypass", server.proxyPass);
679 }
680 
681 
683 {
684  QSqlDatabase db = logDb();
685  bool error = false;
686  db.transaction();
687 
688  {
689  QSqlQuery updateQuery(db);
690  updateQuery.prepare(queryString("update_network"));
691  updateQuery.bindValue(":userid", user.toInt());
692  bindNetworkInfo(updateQuery, info);
693 
694  lockForWrite();
695  safeExec(updateQuery);
696  if (!watchQuery(updateQuery) || updateQuery.numRowsAffected() != 1) {
697  error = true;
698  db.rollback();
699  }
700  }
701  if (error) {
702  unlock();
703  return false;
704  }
705 
706  {
707  QSqlQuery dropServersQuery(db);
708  dropServersQuery.prepare("DELETE FROM ircserver WHERE networkid = :networkid");
709  dropServersQuery.bindValue(":networkid", info.networkId.toInt());
710  safeExec(dropServersQuery);
711  if (!watchQuery(dropServersQuery)) {
712  error = true;
713  db.rollback();
714  }
715  }
716  if (error) {
717  unlock();
718  return false;
719  }
720 
721  {
722  QSqlQuery insertServersQuery(db);
723  insertServersQuery.prepare(queryString("insert_server"));
724  foreach(Network::Server server, info.serverList) {
725  insertServersQuery.bindValue(":userid", user.toInt());
726  insertServersQuery.bindValue(":networkid", info.networkId.toInt());
727  bindServerInfo(insertServersQuery, server);
728  safeExec(insertServersQuery);
729  if (!watchQuery(insertServersQuery)) {
730  error = true;
731  db.rollback();
732  break;
733  }
734  }
735  }
736 
737  db.commit();
738  unlock();
739  return !error;
740 }
741 
742 
743 bool SqliteStorage::removeNetwork(UserId user, const NetworkId &networkId)
744 {
745  QSqlDatabase db = logDb();
746  bool error = false;
747  db.transaction();
748 
749  {
750  QSqlQuery deleteNetworkQuery(db);
751  deleteNetworkQuery.prepare(queryString("delete_network"));
752  deleteNetworkQuery.bindValue(":networkid", networkId.toInt());
753  deleteNetworkQuery.bindValue(":userid", user.toInt());
754  lockForWrite();
755  safeExec(deleteNetworkQuery);
756  if (!watchQuery(deleteNetworkQuery) || deleteNetworkQuery.numRowsAffected() != 1) {
757  error = true;
758  db.rollback();
759  }
760  }
761  if (error) {
762  unlock();
763  return false;
764  }
765 
766  {
767  QSqlQuery deleteBacklogQuery(db);
768  deleteBacklogQuery.prepare(queryString("delete_backlog_for_network"));
769  deleteBacklogQuery.bindValue(":networkid", networkId.toInt());
770  safeExec(deleteBacklogQuery);
771  if (!watchQuery(deleteBacklogQuery)) {
772  db.rollback();
773  error = true;
774  }
775  }
776  if (error) {
777  unlock();
778  return false;
779  }
780 
781  {
782  QSqlQuery deleteBuffersQuery(db);
783  deleteBuffersQuery.prepare(queryString("delete_buffers_for_network"));
784  deleteBuffersQuery.bindValue(":networkid", networkId.toInt());
785  safeExec(deleteBuffersQuery);
786  if (!watchQuery(deleteBuffersQuery)) {
787  db.rollback();
788  error = true;
789  }
790  }
791  if (error) {
792  unlock();
793  return false;
794  }
795 
796  {
797  QSqlQuery deleteServersQuery(db);
798  deleteServersQuery.prepare(queryString("delete_ircservers_for_network"));
799  deleteServersQuery.bindValue(":networkid", networkId.toInt());
800  safeExec(deleteServersQuery);
801  if (!watchQuery(deleteServersQuery)) {
802  db.rollback();
803  error = true;
804  }
805  }
806  if (error) {
807  unlock();
808  return false;
809  }
810 
811  db.commit();
812  unlock();
813  return true;
814 }
815 
816 
817 QList<NetworkInfo> SqliteStorage::networks(UserId user)
818 {
819  QList<NetworkInfo> nets;
820 
821  QSqlDatabase db = logDb();
822  db.transaction();
823 
824  {
825  QSqlQuery networksQuery(db);
826  networksQuery.prepare(queryString("select_networks_for_user"));
827  networksQuery.bindValue(":userid", user.toInt());
828 
829  QSqlQuery serversQuery(db);
830  serversQuery.prepare(queryString("select_servers_for_network"));
831 
832  lockForRead();
833  safeExec(networksQuery);
834  if (watchQuery(networksQuery)) {
835  while (networksQuery.next()) {
836  NetworkInfo net;
837  net.networkId = networksQuery.value(0).toInt();
838  net.networkName = networksQuery.value(1).toString();
839  net.identity = networksQuery.value(2).toInt();
840  net.codecForServer = networksQuery.value(3).toString().toLatin1();
841  net.codecForEncoding = networksQuery.value(4).toString().toLatin1();
842  net.codecForDecoding = networksQuery.value(5).toString().toLatin1();
843  net.useRandomServer = networksQuery.value(6).toInt() == 1 ? true : false;
844  net.perform = networksQuery.value(7).toString().split("\n");
845  net.useAutoIdentify = networksQuery.value(8).toInt() == 1 ? true : false;
846  net.autoIdentifyService = networksQuery.value(9).toString();
847  net.autoIdentifyPassword = networksQuery.value(10).toString();
848  net.useAutoReconnect = networksQuery.value(11).toInt() == 1 ? true : false;
849  net.autoReconnectInterval = networksQuery.value(12).toUInt();
850  net.autoReconnectRetries = networksQuery.value(13).toInt();
851  net.unlimitedReconnectRetries = networksQuery.value(14).toInt() == 1 ? true : false;
852  net.rejoinChannels = networksQuery.value(15).toInt() == 1 ? true : false;
853  net.useSasl = networksQuery.value(16).toInt() == 1 ? true : false;
854  net.saslAccount = networksQuery.value(17).toString();
855  net.saslPassword = networksQuery.value(18).toString();
856 
857  serversQuery.bindValue(":networkid", net.networkId.toInt());
858  safeExec(serversQuery);
859  if (!watchQuery(serversQuery)) {
860  nets.clear();
861  break;
862  }
863  else {
864  Network::ServerList servers;
865  while (serversQuery.next()) {
867  server.host = serversQuery.value(0).toString();
868  server.port = serversQuery.value(1).toUInt();
869  server.password = serversQuery.value(2).toString();
870  server.useSsl = serversQuery.value(3).toInt() == 1 ? true : false;
871  server.sslVersion = serversQuery.value(4).toInt();
872  server.useProxy = serversQuery.value(5).toInt() == 1 ? true : false;
873  server.proxyType = serversQuery.value(6).toInt();
874  server.proxyHost = serversQuery.value(7).toString();
875  server.proxyPort = serversQuery.value(8).toUInt();
876  server.proxyUser = serversQuery.value(9).toString();
877  server.proxyPass = serversQuery.value(10).toString();
878  servers << server;
879  }
880  net.serverList = servers;
881  nets << net;
882  }
883  }
884  }
885  }
886  db.commit();
887  unlock();
888  return nets;
889 }
890 
891 
893 {
894  QList<NetworkId> connectedNets;
895 
896  QSqlDatabase db = logDb();
897  db.transaction();
898 
899  {
900  QSqlQuery query(db);
901  query.prepare(queryString("select_connected_networks"));
902  query.bindValue(":userid", user.toInt());
903  lockForRead();
904  safeExec(query);
905  watchQuery(query);
906 
907  while (query.next()) {
908  connectedNets << query.value(0).toInt();
909  }
910  db.commit();
911  }
912  unlock();
913  return connectedNets;
914 }
915 
916 
917 void SqliteStorage::setNetworkConnected(UserId user, const NetworkId &networkId, bool isConnected)
918 {
919  QSqlDatabase db = logDb();
920  db.transaction();
921 
922  {
923  QSqlQuery query(db);
924  query.prepare(queryString("update_network_connected"));
925  query.bindValue(":userid", user.toInt());
926  query.bindValue(":networkid", networkId.toInt());
927  query.bindValue(":connected", isConnected ? 1 : 0);
928 
929  lockForWrite();
930  safeExec(query);
931  watchQuery(query);
932  db.commit();
933  }
934  unlock();
935 }
936 
937 
938 QHash<QString, QString> SqliteStorage::persistentChannels(UserId user, const NetworkId &networkId)
939 {
940  QHash<QString, QString> persistentChans;
941 
942  QSqlDatabase db = logDb();
943  db.transaction();
944  {
945  QSqlQuery query(db);
946  query.prepare(queryString("select_persistent_channels"));
947  query.bindValue(":userid", user.toInt());
948  query.bindValue(":networkid", networkId.toInt());
949 
950  lockForRead();
951  safeExec(query);
952  watchQuery(query);
953  while (query.next()) {
954  persistentChans[query.value(0).toString()] = query.value(1).toString();
955  }
956  }
957  unlock();
958  return persistentChans;
959 }
960 
961 
962 void SqliteStorage::setChannelPersistent(UserId user, const NetworkId &networkId, const QString &channel, bool isJoined)
963 {
964  QSqlDatabase db = logDb();
965  db.transaction();
966 
967  {
968  QSqlQuery query(db);
969  query.prepare(queryString("update_buffer_persistent_channel"));
970  query.bindValue(":userid", user.toInt());
971  query.bindValue(":networkId", networkId.toInt());
972  query.bindValue(":buffercname", channel.toLower());
973  query.bindValue(":joined", isJoined ? 1 : 0);
974 
975  lockForWrite();
976  safeExec(query);
977  watchQuery(query);
978  db.commit();
979  }
980  unlock();
981 }
982 
983 
984 void SqliteStorage::setPersistentChannelKey(UserId user, const NetworkId &networkId, const QString &channel, const QString &key)
985 {
986  QSqlDatabase db = logDb();
987  db.transaction();
988 
989  {
990  QSqlQuery query(db);
991  query.prepare(queryString("update_buffer_set_channel_key"));
992  query.bindValue(":userid", user.toInt());
993  query.bindValue(":networkId", networkId.toInt());
994  query.bindValue(":buffercname", channel.toLower());
995  query.bindValue(":key", key);
996 
997  lockForWrite();
998  safeExec(query);
999  watchQuery(query);
1000  db.commit();
1001  }
1002  unlock();
1003 }
1004 
1005 
1007 {
1008  QSqlDatabase db = logDb();
1009  db.transaction();
1010 
1011  QString awayMsg;
1012  {
1013  QSqlQuery query(db);
1014  query.prepare(queryString("select_network_awaymsg"));
1015  query.bindValue(":userid", user.toInt());
1016  query.bindValue(":networkid", networkId.toInt());
1017 
1018  lockForRead();
1019  safeExec(query);
1020  watchQuery(query);
1021  if (query.first())
1022  awayMsg = query.value(0).toString();
1023  db.commit();
1024  }
1025  unlock();
1026 
1027  return awayMsg;
1028 }
1029 
1030 
1031 void SqliteStorage::setAwayMessage(UserId user, NetworkId networkId, const QString &awayMsg)
1032 {
1033  QSqlDatabase db = logDb();
1034  db.transaction();
1035 
1036  {
1037  QSqlQuery query(db);
1038  query.prepare(queryString("update_network_set_awaymsg"));
1039  query.bindValue(":userid", user.toInt());
1040  query.bindValue(":networkid", networkId.toInt());
1041  query.bindValue(":awaymsg", awayMsg);
1042 
1043  lockForWrite();
1044  safeExec(query);
1045  watchQuery(query);
1046  db.commit();
1047  }
1048  unlock();
1049 }
1050 
1051 
1053 {
1054  QSqlDatabase db = logDb();
1055  db.transaction();
1056 
1057  QString modes;
1058  {
1059  QSqlQuery query(db);
1060  query.prepare(queryString("select_network_usermode"));
1061  query.bindValue(":userid", user.toInt());
1062  query.bindValue(":networkid", networkId.toInt());
1063 
1064  lockForRead();
1065  safeExec(query);
1066  watchQuery(query);
1067  if (query.first())
1068  modes = query.value(0).toString();
1069  db.commit();
1070  }
1071  unlock();
1072 
1073  return modes;
1074 }
1075 
1076 
1077 void SqliteStorage::setUserModes(UserId user, NetworkId networkId, const QString &userModes)
1078 {
1079  QSqlDatabase db = logDb();
1080  db.transaction();
1081 
1082  {
1083  QSqlQuery query(db);
1084  query.prepare(queryString("update_network_set_usermode"));
1085  query.bindValue(":userid", user.toInt());
1086  query.bindValue(":networkid", networkId.toInt());
1087  query.bindValue(":usermode", userModes);
1088 
1089  lockForWrite();
1090  safeExec(query);
1091  watchQuery(query);
1092  db.commit();
1093  }
1094  unlock();
1095 }
1096 
1097 
1098 BufferInfo SqliteStorage::bufferInfo(UserId user, const NetworkId &networkId, BufferInfo::Type type, const QString &buffer, bool create)
1099 {
1100  QSqlDatabase db = logDb();
1101  db.transaction();
1102 
1104  {
1105  QSqlQuery query(db);
1106  query.prepare(queryString("select_bufferByName"));
1107  query.bindValue(":networkid", networkId.toInt());
1108  query.bindValue(":userid", user.toInt());
1109  query.bindValue(":buffercname", buffer.toLower());
1110 
1111  lockForRead();
1112  safeExec(query);
1113 
1114  if (query.first()) {
1115  bufferInfo = BufferInfo(query.value(0).toInt(), networkId, (BufferInfo::Type)query.value(1).toInt(), 0, buffer);
1116  if (query.next()) {
1117  qCritical() << "SqliteStorage::getBufferInfo(): received more then one Buffer!";
1118  qCritical() << " Query:" << query.lastQuery();
1119  qCritical() << " bound Values:";
1120  QList<QVariant> list = query.boundValues().values();
1121  for (int i = 0; i < list.size(); ++i)
1122  qCritical() << i << ":" << list.at(i).toString().toLatin1().data();
1123  Q_ASSERT(false);
1124  }
1125  }
1126  else if (create) {
1127  // let's create the buffer
1128  QSqlQuery createQuery(db);
1129  createQuery.prepare(queryString("insert_buffer"));
1130  createQuery.bindValue(":userid", user.toInt());
1131  createQuery.bindValue(":networkid", networkId.toInt());
1132  createQuery.bindValue(":buffertype", (int)type);
1133  createQuery.bindValue(":buffername", buffer);
1134  createQuery.bindValue(":buffercname", buffer.toLower());
1135  createQuery.bindValue(":joined", type & BufferInfo::ChannelBuffer ? 1 : 0);
1136 
1137  unlock();
1138  lockForWrite();
1139  safeExec(createQuery);
1140  watchQuery(createQuery);
1141  bufferInfo = BufferInfo(createQuery.lastInsertId().toInt(), networkId, type, 0, buffer);
1142  }
1143  }
1144  db.commit();
1145  unlock();
1146  return bufferInfo;
1147 }
1148 
1149 
1151 {
1152  QSqlDatabase db = logDb();
1153  db.transaction();
1154 
1156  {
1157  QSqlQuery query(db);
1158  query.prepare(queryString("select_buffer_by_id"));
1159  query.bindValue(":userid", user.toInt());
1160  query.bindValue(":bufferid", bufferId.toInt());
1161 
1162  lockForRead();
1163  safeExec(query);
1164 
1165  if (watchQuery(query) && query.first()) {
1166  bufferInfo = BufferInfo(query.value(0).toInt(), query.value(1).toInt(), (BufferInfo::Type)query.value(2).toInt(), 0, query.value(4).toString());
1167  Q_ASSERT(!query.next());
1168  }
1169  db.commit();
1170  }
1171  unlock();
1172  return bufferInfo;
1173 }
1174 
1175 
1176 QList<BufferInfo> SqliteStorage::requestBuffers(UserId user)
1177 {
1178  QList<BufferInfo> bufferlist;
1179 
1180  QSqlDatabase db = logDb();
1181  db.transaction();
1182 
1183  {
1184  QSqlQuery query(db);
1185  query.prepare(queryString("select_buffers"));
1186  query.bindValue(":userid", user.toInt());
1187 
1188  lockForRead();
1189  safeExec(query);
1190  watchQuery(query);
1191  while (query.next()) {
1192  bufferlist << BufferInfo(query.value(0).toInt(), query.value(1).toInt(), (BufferInfo::Type)query.value(2).toInt(), query.value(3).toInt(), query.value(4).toString());
1193  }
1194  db.commit();
1195  }
1196  unlock();
1197 
1198  return bufferlist;
1199 }
1200 
1201 
1203 {
1204  QList<BufferId> bufferList;
1205 
1206  QSqlDatabase db = logDb();
1207  db.transaction();
1208 
1209  {
1210  QSqlQuery query(db);
1211  query.prepare(queryString("select_buffers_for_network"));
1212  query.bindValue(":networkid", networkId.toInt());
1213  query.bindValue(":userid", user.toInt());
1214 
1215  lockForRead();
1216  safeExec(query);
1217  watchQuery(query);
1218  while (query.next()) {
1219  bufferList << BufferId(query.value(0).toInt());
1220  }
1221  db.commit();
1222  }
1223  unlock();
1224 
1225  return bufferList;
1226 }
1227 
1228 
1229 bool SqliteStorage::removeBuffer(const UserId &user, const BufferId &bufferId)
1230 {
1231  QSqlDatabase db = logDb();
1232  db.transaction();
1233 
1234  bool error = false;
1235  {
1236  QSqlQuery delBufferQuery(db);
1237  delBufferQuery.prepare(queryString("delete_buffer_for_bufferid"));
1238  delBufferQuery.bindValue(":bufferid", bufferId.toInt());
1239  delBufferQuery.bindValue(":userid", user.toInt());
1240 
1241  lockForWrite();
1242  safeExec(delBufferQuery);
1243 
1244  error = (!watchQuery(delBufferQuery) || delBufferQuery.numRowsAffected() != 1);
1245  }
1246 
1247  if (error) {
1248  db.rollback();
1249  unlock();
1250  return false;
1251  }
1252 
1253  {
1254  QSqlQuery delBacklogQuery(db);
1255  delBacklogQuery.prepare(queryString("delete_backlog_for_buffer"));
1256  delBacklogQuery.bindValue(":bufferid", bufferId.toInt());
1257 
1258  safeExec(delBacklogQuery);
1259  error = !watchQuery(delBacklogQuery);
1260  }
1261 
1262  if (error) {
1263  db.rollback();
1264  }
1265  else {
1266  db.commit();
1267  }
1268  unlock();
1269  return !error;
1270 }
1271 
1272 
1273 bool SqliteStorage::renameBuffer(const UserId &user, const BufferId &bufferId, const QString &newName)
1274 {
1275  QSqlDatabase db = logDb();
1276  db.transaction();
1277 
1278  bool error = false;
1279  {
1280  QSqlQuery query(db);
1281  query.prepare(queryString("update_buffer_name"));
1282  query.bindValue(":buffername", newName);
1283  query.bindValue(":buffercname", newName.toLower());
1284  query.bindValue(":bufferid", bufferId.toInt());
1285  query.bindValue(":userid", user.toInt());
1286 
1287  lockForWrite();
1288  safeExec(query);
1289 
1290  error = query.lastError().isValid();
1291  // unexepcted error occured (19 == constraint violation)
1292  if (error && query.lastError().number() != 19) {
1293  watchQuery(query);
1294  }
1295  else {
1296  error |= (query.numRowsAffected() != 1);
1297  }
1298  }
1299  if (error) {
1300  db.rollback();
1301  }
1302  else {
1303  db.commit();
1304  }
1305  unlock();
1306  return !error;
1307 }
1308 
1309 
1310 bool SqliteStorage::mergeBuffersPermanently(const UserId &user, const BufferId &bufferId1, const BufferId &bufferId2)
1311 {
1312  QSqlDatabase db = logDb();
1313  db.transaction();
1314 
1315  bool error = false;
1316  {
1317  QSqlQuery checkQuery(db);
1318  checkQuery.prepare(queryString("select_buffers_for_merge"));
1319  checkQuery.bindValue(":oldbufferid", bufferId2.toInt());
1320  checkQuery.bindValue(":newbufferid", bufferId1.toInt());
1321  checkQuery.bindValue(":userid", user.toInt());
1322 
1323  lockForRead();
1324  safeExec(checkQuery);
1325  error = (!checkQuery.first() || checkQuery.value(0).toInt() != 2);
1326  }
1327  if (error) {
1328  db.rollback();
1329  unlock();
1330  return false;
1331  }
1332 
1333  {
1334  QSqlQuery query(db);
1335  query.prepare(queryString("update_backlog_bufferid"));
1336  query.bindValue(":oldbufferid", bufferId2.toInt());
1337  query.bindValue(":newbufferid", bufferId1.toInt());
1338  safeExec(query);
1339  error = !watchQuery(query);
1340  }
1341  if (error) {
1342  db.rollback();
1343  unlock();
1344  return false;
1345  }
1346 
1347  {
1348  QSqlQuery delBufferQuery(db);
1349  delBufferQuery.prepare(queryString("delete_buffer_for_bufferid"));
1350  delBufferQuery.bindValue(":bufferid", bufferId2.toInt());
1351  delBufferQuery.bindValue(":userid", user.toInt());
1352  safeExec(delBufferQuery);
1353  error = !watchQuery(delBufferQuery);
1354  }
1355 
1356  if (error) {
1357  db.rollback();
1358  }
1359  else {
1360  db.commit();
1361  }
1362  unlock();
1363  return !error;
1364 }
1365 
1366 
1367 void SqliteStorage::setBufferLastSeenMsg(UserId user, const BufferId &bufferId, const MsgId &msgId)
1368 {
1369  QSqlDatabase db = logDb();
1370  db.transaction();
1371 
1372  {
1373  QSqlQuery query(db);
1374  query.prepare(queryString("update_buffer_lastseen"));
1375  query.bindValue(":userid", user.toInt());
1376  query.bindValue(":bufferid", bufferId.toInt());
1377  query.bindValue(":lastseenmsgid", msgId.toInt());
1378 
1379  lockForWrite();
1380  safeExec(query);
1381  watchQuery(query);
1382  }
1383  db.commit();
1384  unlock();
1385 }
1386 
1387 
1388 QHash<BufferId, MsgId> SqliteStorage::bufferLastSeenMsgIds(UserId user)
1389 {
1390  QHash<BufferId, MsgId> lastSeenHash;
1391 
1392  QSqlDatabase db = logDb();
1393  db.transaction();
1394 
1395  bool error = false;
1396  {
1397  QSqlQuery query(db);
1398  query.prepare(queryString("select_buffer_lastseen_messages"));
1399  query.bindValue(":userid", user.toInt());
1400 
1401  lockForRead();
1402  safeExec(query);
1403  error = !watchQuery(query);
1404  if (!error) {
1405  while (query.next()) {
1406  lastSeenHash[query.value(0).toInt()] = query.value(1).toInt();
1407  }
1408  }
1409  }
1410 
1411  db.commit();
1412  unlock();
1413  return lastSeenHash;
1414 }
1415 
1416 
1417 void SqliteStorage::setBufferMarkerLineMsg(UserId user, const BufferId &bufferId, const MsgId &msgId)
1418 {
1419  QSqlDatabase db = logDb();
1420  db.transaction();
1421 
1422  {
1423  QSqlQuery query(db);
1424  query.prepare(queryString("update_buffer_markerlinemsgid"));
1425  query.bindValue(":userid", user.toInt());
1426  query.bindValue(":bufferid", bufferId.toInt());
1427  query.bindValue(":markerlinemsgid", msgId.toInt());
1428 
1429  lockForWrite();
1430  safeExec(query);
1431  watchQuery(query);
1432  }
1433  db.commit();
1434  unlock();
1435 }
1436 
1437 
1438 QHash<BufferId, MsgId> SqliteStorage::bufferMarkerLineMsgIds(UserId user)
1439 {
1440  QHash<BufferId, MsgId> markerLineHash;
1441 
1442  QSqlDatabase db = logDb();
1443  db.transaction();
1444 
1445  bool error = false;
1446  {
1447  QSqlQuery query(db);
1448  query.prepare(queryString("select_buffer_markerlinemsgids"));
1449  query.bindValue(":userid", user.toInt());
1450 
1451  lockForRead();
1452  safeExec(query);
1453  error = !watchQuery(query);
1454  if (!error) {
1455  while (query.next()) {
1456  markerLineHash[query.value(0).toInt()] = query.value(1).toInt();
1457  }
1458  }
1459  }
1460 
1461  db.commit();
1462  unlock();
1463  return markerLineHash;
1464 }
1465 
1466 
1468 {
1469  QSqlDatabase db = logDb();
1470  db.transaction();
1471 
1472  bool error = false;
1473  {
1474  QSqlQuery logMessageQuery(db);
1475  logMessageQuery.prepare(queryString("insert_message"));
1476 
1477  logMessageQuery.bindValue(":time", msg.timestamp().toTime_t());
1478  logMessageQuery.bindValue(":bufferid", msg.bufferInfo().bufferId().toInt());
1479  logMessageQuery.bindValue(":type", msg.type());
1480  logMessageQuery.bindValue(":flags", (int)msg.flags());
1481  logMessageQuery.bindValue(":sender", msg.sender());
1482  logMessageQuery.bindValue(":message", msg.contents());
1483 
1484  lockForWrite();
1485  safeExec(logMessageQuery);
1486 
1487  if (logMessageQuery.lastError().isValid()) {
1488  // constraint violation - must be NOT NULL constraint - probably the sender is missing...
1489  if (logMessageQuery.lastError().number() == 19) {
1490  QSqlQuery addSenderQuery(db);
1491  addSenderQuery.prepare(queryString("insert_sender"));
1492  addSenderQuery.bindValue(":sender", msg.sender());
1493  safeExec(addSenderQuery);
1494  safeExec(logMessageQuery);
1495  error = !watchQuery(logMessageQuery);
1496  }
1497  else {
1498  watchQuery(logMessageQuery);
1499  }
1500  }
1501  if (!error) {
1502  MsgId msgId = logMessageQuery.lastInsertId().toInt();
1503  if (msgId.isValid()) {
1504  msg.setMsgId(msgId);
1505  }
1506  else {
1507  error = true;
1508  }
1509  }
1510  }
1511 
1512  if (error) {
1513  db.rollback();
1514  }
1515  else {
1516  db.commit();
1517  }
1518 
1519  unlock();
1520  return !error;
1521 }
1522 
1523 
1525 {
1526  QSqlDatabase db = logDb();
1527  db.transaction();
1528 
1529  {
1530  QSet<QString> senders;
1531  QSqlQuery addSenderQuery(db);
1532  addSenderQuery.prepare(queryString("insert_sender"));
1533  lockForWrite();
1534  for (int i = 0; i < msgs.count(); i++) {
1535  const QString &sender = msgs.at(i).sender();
1536  if (senders.contains(sender))
1537  continue;
1538  senders << sender;
1539 
1540  addSenderQuery.bindValue(":sender", sender);
1541  safeExec(addSenderQuery);
1542  }
1543  }
1544 
1545  bool error = false;
1546  {
1547  QSqlQuery logMessageQuery(db);
1548  logMessageQuery.prepare(queryString("insert_message"));
1549  for (int i = 0; i < msgs.count(); i++) {
1550  Message &msg = msgs[i];
1551 
1552  logMessageQuery.bindValue(":time", msg.timestamp().toTime_t());
1553  logMessageQuery.bindValue(":bufferid", msg.bufferInfo().bufferId().toInt());
1554  logMessageQuery.bindValue(":type", msg.type());
1555  logMessageQuery.bindValue(":flags", (int)msg.flags());
1556  logMessageQuery.bindValue(":sender", msg.sender());
1557  logMessageQuery.bindValue(":message", msg.contents());
1558 
1559  safeExec(logMessageQuery);
1560  if (!watchQuery(logMessageQuery)) {
1561  error = true;
1562  break;
1563  }
1564  else {
1565  msg.setMsgId(logMessageQuery.lastInsertId().toInt());
1566  }
1567  }
1568  }
1569 
1570  if (error) {
1571  db.rollback();
1572  unlock();
1573  // we had a rollback in the db so we need to reset all msgIds
1574  for (int i = 0; i < msgs.count(); i++) {
1575  msgs[i].setMsgId(MsgId());
1576  }
1577  }
1578  else {
1579  db.commit();
1580  unlock();
1581  }
1582  return !error;
1583 }
1584 
1585 
1586 QList<Message> SqliteStorage::requestMsgs(UserId user, BufferId bufferId, MsgId first, MsgId last, int limit)
1587 {
1588  QList<Message> messagelist;
1589 
1590  QSqlDatabase db = logDb();
1591  db.transaction();
1592 
1593  bool error = false;
1595  {
1596  // code dupication from getBufferInfo:
1597  // this is due to the impossibility of nesting transactions and recursive locking
1598  QSqlQuery bufferInfoQuery(db);
1599  bufferInfoQuery.prepare(queryString("select_buffer_by_id"));
1600  bufferInfoQuery.bindValue(":userid", user.toInt());
1601  bufferInfoQuery.bindValue(":bufferid", bufferId.toInt());
1602 
1603  lockForRead();
1604  safeExec(bufferInfoQuery);
1605  error = !watchQuery(bufferInfoQuery) || !bufferInfoQuery.first();
1606  if (!error) {
1607  bufferInfo = BufferInfo(bufferInfoQuery.value(0).toInt(), bufferInfoQuery.value(1).toInt(), (BufferInfo::Type)bufferInfoQuery.value(2).toInt(), 0, bufferInfoQuery.value(4).toString());
1608  error = !bufferInfo.isValid();
1609  }
1610  }
1611  if (error) {
1612  db.rollback();
1613  unlock();
1614  return messagelist;
1615  }
1616 
1617  {
1618  QSqlQuery query(db);
1619  if (last == -1 && first == -1) {
1620  query.prepare(queryString("select_messagesNewestK"));
1621  }
1622  else if (last == -1) {
1623  query.prepare(queryString("select_messagesNewerThan"));
1624  query.bindValue(":firstmsg", first.toInt());
1625  }
1626  else {
1627  query.prepare(queryString("select_messages"));
1628  query.bindValue(":lastmsg", last.toInt());
1629  query.bindValue(":firstmsg", first.toInt());
1630  }
1631  query.bindValue(":bufferid", bufferId.toInt());
1632  query.bindValue(":limit", limit);
1633 
1634  safeExec(query);
1635  watchQuery(query);
1636 
1637  while (query.next()) {
1638  Message msg(QDateTime::fromTime_t(query.value(1).toInt()),
1639  bufferInfo,
1640  (Message::Type)query.value(2).toUInt(),
1641  query.value(5).toString(),
1642  query.value(4).toString(),
1643  (Message::Flags)query.value(3).toUInt());
1644  msg.setMsgId(query.value(0).toInt());
1645  messagelist << msg;
1646  }
1647  }
1648  db.commit();
1649  unlock();
1650 
1651  return messagelist;
1652 }
1653 
1654 
1655 QList<Message> SqliteStorage::requestAllMsgs(UserId user, MsgId first, MsgId last, int limit)
1656 {
1657  QList<Message> messagelist;
1658 
1659  QSqlDatabase db = logDb();
1660  db.transaction();
1661 
1662  QHash<BufferId, BufferInfo> bufferInfoHash;
1663  {
1664  QSqlQuery bufferInfoQuery(db);
1665  bufferInfoQuery.prepare(queryString("select_buffers"));
1666  bufferInfoQuery.bindValue(":userid", user.toInt());
1667 
1668  lockForRead();
1669  safeExec(bufferInfoQuery);
1670  watchQuery(bufferInfoQuery);
1671  while (bufferInfoQuery.next()) {
1672  BufferInfo bufferInfo = BufferInfo(bufferInfoQuery.value(0).toInt(), bufferInfoQuery.value(1).toInt(), (BufferInfo::Type)bufferInfoQuery.value(2).toInt(), bufferInfoQuery.value(3).toInt(), bufferInfoQuery.value(4).toString());
1673  bufferInfoHash[bufferInfo.bufferId()] = bufferInfo;
1674  }
1675 
1676  QSqlQuery query(db);
1677  if (last == -1) {
1678  query.prepare(queryString("select_messagesAllNew"));
1679  }
1680  else {
1681  query.prepare(queryString("select_messagesAll"));
1682  query.bindValue(":lastmsg", last.toInt());
1683  }
1684  query.bindValue(":userid", user.toInt());
1685  query.bindValue(":firstmsg", first.toInt());
1686  query.bindValue(":limit", limit);
1687  safeExec(query);
1688 
1689  watchQuery(query);
1690 
1691  while (query.next()) {
1692  Message msg(QDateTime::fromTime_t(query.value(2).toInt()),
1693  bufferInfoHash[query.value(1).toInt()],
1694  (Message::Type)query.value(3).toUInt(),
1695  query.value(6).toString(),
1696  query.value(5).toString(),
1697  (Message::Flags)query.value(4).toUInt());
1698  msg.setMsgId(query.value(0).toInt());
1699  messagelist << msg;
1700  }
1701  }
1702  db.commit();
1703  unlock();
1704  return messagelist;
1705 }
1706 
1707 
1709 {
1710  return Quassel::configDirPath() + "quassel-storage.sqlite";
1711 }
1712 
1713 
1714 bool SqliteStorage::safeExec(QSqlQuery &query, int retryCount)
1715 {
1716  query.exec();
1717 
1718  if (!query.lastError().isValid())
1719  return true;
1720 
1721  switch (query.lastError().number()) {
1722  case 5: // SQLITE_BUSY 5 /* The database file is locked */
1723  case 6: // SQLITE_LOCKED 6 /* A table in the database is locked */
1724  if (retryCount < _maxRetryCount)
1725  return safeExec(query, retryCount + 1);
1726  default:
1727  return false;
1728  }
1729 }
1730 
1731 
1732 // ========================================
1733 // SqliteMigration
1734 // ========================================
1736  : SqliteStorage(),
1737  _maxId(0)
1738 {
1739 }
1740 
1741 
1743 {
1744  QString queryString;
1745  switch (mo) {
1746  case Sender:
1747  queryString = "SELECT max(senderid) FROM sender";
1748  break;
1749  case Backlog:
1750  queryString = "SELECT max(messageid) FROM backlog";
1751  break;
1752  default:
1753  _maxId = 0;
1754  return;
1755  }
1756  QSqlQuery query = logDb().exec(queryString);
1757  query.first();
1758  _maxId = query.value(0).toInt();
1759 }
1760 
1761 
1763 {
1764  setMaxId(mo);
1765 
1766  switch (mo) {
1767  case QuasselUser:
1768  newQuery(queryString("migrate_read_quasseluser"), logDb());
1769  break;
1770  case Identity:
1771  newQuery(queryString("migrate_read_identity"), logDb());
1772  break;
1773  case IdentityNick:
1774  newQuery(queryString("migrate_read_identity_nick"), logDb());
1775  break;
1776  case Network:
1777  newQuery(queryString("migrate_read_network"), logDb());
1778  break;
1779  case Buffer:
1780  newQuery(queryString("migrate_read_buffer"), logDb());
1781  break;
1782  case Sender:
1783  newQuery(queryString("migrate_read_sender"), logDb());
1784  bindValue(0, 0);
1785  bindValue(1, stepSize());
1786  break;
1787  case Backlog:
1788  newQuery(queryString("migrate_read_backlog"), logDb());
1789  bindValue(0, 0);
1790  bindValue(1, stepSize());
1791  break;
1792  case IrcServer:
1793  newQuery(queryString("migrate_read_ircserver"), logDb());
1794  break;
1795  case UserSetting:
1796  newQuery(queryString("migrate_read_usersetting"), logDb());
1797  break;
1798  }
1799  return exec();
1800 }
1801 
1802 
1804 {
1805  if (!next())
1806  return false;
1807 
1808  user.id = value(0).toInt();
1809  user.username = value(1).toString();
1810  user.password = value(2).toString();
1811  return true;
1812 }
1813 
1814 
1816 {
1817  if (!next())
1818  return false;
1819 
1820  identity.id = value(0).toInt();
1821  identity.userid = value(1).toInt();
1822  identity.identityname = value(2).toString();
1823  identity.realname = value(3).toString();
1824  identity.awayNick = value(4).toString();
1825  identity.awayNickEnabled = value(5).toInt() == 1 ? true : false;
1826  identity.awayReason = value(6).toString();
1827  identity.awayReasonEnabled = value(7).toInt() == 1 ? true : false;
1828  identity.autoAwayEnabled = value(8).toInt() == 1 ? true : false;
1829  identity.autoAwayTime = value(9).toInt();
1830  identity.autoAwayReason = value(10).toString();
1831  identity.autoAwayReasonEnabled = value(11).toInt() == 1 ? true : false;
1832  identity.detachAwayEnabled = value(12).toInt() == 1 ? true : false;
1833  identity.detachAwayReason = value(13).toString();
1834  identity.detchAwayReasonEnabled = value(14).toInt() == 1 ? true : false;
1835  identity.ident = value(15).toString();
1836  identity.kickReason = value(16).toString();
1837  identity.partReason = value(17).toString();
1838  identity.quitReason = value(18).toString();
1839  identity.sslCert = value(19).toByteArray();
1840  identity.sslKey = value(20).toByteArray();
1841  return true;
1842 }
1843 
1844 
1846 {
1847  if (!next())
1848  return false;
1849 
1850  identityNick.nickid = value(0).toInt();
1851  identityNick.identityId = value(1).toInt();
1852  identityNick.nick = value(2).toString();
1853  return true;
1854 }
1855 
1856 
1858 {
1859  if (!next())
1860  return false;
1861 
1862  network.networkid = value(0).toInt();
1863  network.userid = value(1).toInt();
1864  network.networkname = value(2).toString();
1865  network.identityid = value(3).toInt();
1866  network.encodingcodec = value(4).toString();
1867  network.decodingcodec = value(5).toString();
1868  network.servercodec = value(6).toString();
1869  network.userandomserver = value(7).toInt() == 1 ? true : false;
1870  network.perform = value(8).toString();
1871  network.useautoidentify = value(9).toInt() == 1 ? true : false;
1872  network.autoidentifyservice = value(10).toString();
1873  network.autoidentifypassword = value(11).toString();
1874  network.useautoreconnect = value(12).toInt() == 1 ? true : false;
1875  network.autoreconnectinterval = value(13).toInt();
1876  network.autoreconnectretries = value(14).toInt();
1877  network.unlimitedconnectretries = value(15).toInt() == 1 ? true : false;
1878  network.rejoinchannels = value(16).toInt() == 1 ? true : false;
1879  network.connected = value(17).toInt() == 1 ? true : false;
1880  network.usermode = value(18).toString();
1881  network.awaymessage = value(19).toString();
1882  network.attachperform = value(20).toString();
1883  network.detachperform = value(21).toString();
1884  network.usesasl = value(22).toInt() == 1 ? true : false;
1885  network.saslaccount = value(23).toString();
1886  network.saslpassword = value(24).toString();
1887  return true;
1888 }
1889 
1890 
1892 {
1893  if (!next())
1894  return false;
1895 
1896  buffer.bufferid = value(0).toInt();
1897  buffer.userid = value(1).toInt();
1898  buffer.groupid = value(2).toInt();
1899  buffer.networkid = value(3).toInt();
1900  buffer.buffername = value(4).toString();
1901  buffer.buffercname = value(5).toString();
1902  buffer.buffertype = value(6).toInt();
1903  buffer.lastseenmsgid = value(7).toInt();
1904  buffer.markerlinemsgid = value(8).toInt();
1905  buffer.key = value(9).toString();
1906  buffer.joined = value(10).toInt() == 1 ? true : false;
1907  return true;
1908 }
1909 
1910 
1912 {
1913  int skipSteps = 0;
1914  while (!next()) {
1915  if (sender.senderId < _maxId) {
1916  bindValue(0, sender.senderId + (skipSteps * stepSize()));
1917  bindValue(1, sender.senderId + ((skipSteps + 1) * stepSize()));
1918  skipSteps++;
1919  if (!exec())
1920  return false;
1921  }
1922  else {
1923  return false;
1924  }
1925  }
1926 
1927  sender.senderId = value(0).toInt();
1928  sender.sender = value(1).toString();
1929  return true;
1930 }
1931 
1932 
1934 {
1935  int skipSteps = 0;
1936  while (!next()) {
1937  if (backlog.messageid < _maxId) {
1938  bindValue(0, backlog.messageid.toInt() + (skipSteps * stepSize()));
1939  bindValue(1, backlog.messageid.toInt() + ((skipSteps + 1) * stepSize()));
1940  skipSteps++;
1941  if (!exec())
1942  return false;
1943  }
1944  else {
1945  return false;
1946  }
1947  }
1948 
1949  backlog.messageid = value(0).toInt();
1950  backlog.time = QDateTime::fromTime_t(value(1).toInt()).toUTC();
1951  backlog.bufferid = value(2).toInt();
1952  backlog.type = value(3).toInt();
1953  backlog.flags = value(4).toInt();
1954  backlog.senderid = value(5).toInt();
1955  backlog.message = value(6).toString();
1956  return true;
1957 }
1958 
1959 
1961 {
1962  if (!next())
1963  return false;
1964 
1965  ircserver.serverid = value(0).toInt();
1966  ircserver.userid = value(1).toInt();
1967  ircserver.networkid = value(2).toInt();
1968  ircserver.hostname = value(3).toString();
1969  ircserver.port = value(4).toInt();
1970  ircserver.password = value(5).toString();
1971  ircserver.ssl = value(6).toInt() == 1 ? true : false;
1972  ircserver.sslversion = value(7).toInt();
1973  ircserver.useproxy = value(8).toInt() == 1 ? true : false;
1974  ircserver.proxytype = value(9).toInt();
1975  ircserver.proxyhost = value(10).toString();
1976  ircserver.proxyport = value(11).toInt();
1977  ircserver.proxyuser = value(12).toString();
1978  ircserver.proxypass = value(13).toString();
1979  return true;
1980 }
1981 
1982 
1984 {
1985  if (!next())
1986  return false;
1987 
1988  userSetting.userid = value(0).toInt();
1989  userSetting.settingname = value(1).toString();
1990  userSetting.settingvalue = value(2).toByteArray();
1991 
1992  return true;
1993 }