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