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