X-Git-Url: https://git.quassel-irc.org/?p=quassel.git;a=blobdiff_plain;f=src%2Fcore%2Fpostgresqlstorage.cpp;h=84e6a05fc293d1250daf5fb1b7687a58be3d786b;hp=04212249da3d6249c36407c0bfaa80da3fe3ee4f;hb=85c5d43cd1a1f6b4e9fe41fe533c743517017d37;hpb=0446cc2c41de41575c92bdd0b32cf795ac10e6e2 diff --git a/src/core/postgresqlstorage.cpp b/src/core/postgresqlstorage.cpp index 04212249..84e6a05f 100644 --- a/src/core/postgresqlstorage.cpp +++ b/src/core/postgresqlstorage.cpp @@ -48,6 +48,7 @@ AbstractSqlMigrationWriter *PostgreSqlStorage::createMigrationWriter() { } bool PostgreSqlStorage::isAvailable() const { + qDebug() << QSqlDatabase::drivers(); if(!QSqlDatabase::isDriverAvailable("QPSQL")) return false; return true; } @@ -61,22 +62,28 @@ QString PostgreSqlStorage::description() const { return tr("PostgreSQL Turbo Bomber HD!"); } -QVariantMap PostgreSqlStorage::setupKeys() const { +QStringList PostgreSqlStorage::setupKeys() const { + QStringList keys; + keys << "Username" + << "Password" + << "Hostname" + << "Port" + << "Database"; + return keys; +} +QVariantMap PostgreSqlStorage::setupDefaults() const { QVariantMap map; map["Username"] = QVariant(QString("quassel")); - map["Password"] = QVariant(QString()); map["Hostname"] = QVariant(QString("localhost")); map["Port"] = QVariant(5432); map["Database"] = QVariant(QString("quassel")); return map; } -bool PostgreSqlStorage::setup(const QVariantMap &settings) { - bool success = AbstractSqlStorage::setup(settings); - if(success) { - logDb().exec(QString("ALTER USER %1 SET standard_conforming_strings TO on").arg(userName())); - } - return success; +void PostgreSqlStorage::initDbSession(QSqlDatabase &db) { + // this blows... but unfortunately Qt's PG driver forces us to this... + db.exec("set standard_conforming_strings = off"); + db.exec("set escape_string_warning = off"); } void PostgreSqlStorage::setConnectionProperties(const QVariantMap &properties) { @@ -143,12 +150,13 @@ UserId PostgreSqlStorage::addUser(const QString &user, const QString &password) return uid; } -void PostgreSqlStorage::updateUser(UserId user, const QString &password) { +bool PostgreSqlStorage::updateUser(UserId user, const QString &password) { QSqlQuery query(logDb()); query.prepare(queryString("update_userpassword")); query.bindValue(":userid", user.toInt()); query.bindValue(":password", cryptedPassword(password)); safeExec(query); + return query.numRowsAffected() != 0; } void PostgreSqlStorage::renameUser(UserId user, const QString &newName) { @@ -174,6 +182,19 @@ UserId PostgreSqlStorage::validateUser(const QString &user, const QString &passw } } +UserId PostgreSqlStorage::getUserId(const QString &user) { + QSqlQuery query(logDb()); + query.prepare(queryString("select_userid")); + query.bindValue(":username", user); + safeExec(query); + + if(query.first()) { + return query.value(0).toInt(); + } else { + return 0; + } +} + UserId PostgreSqlStorage::internalUser() { QSqlQuery query(logDb()); query.prepare(queryString("select_internaluser")); @@ -317,7 +338,7 @@ IdentityId PostgreSqlStorage::createIdentity(UserId user, CoreIdentity &identity } if(!db.commit()) { - qWarning() << "PostgreSqlStorage::createIdentity(): commiting data failed!"; + qWarning() << "PostgreSqlStorage::createIdentity(): committing data failed!"; qWarning() << " -" << qPrintable(db.lastError().text()); return IdentityId(); } @@ -400,7 +421,7 @@ bool PostgreSqlStorage::updateIdentity(UserId user, const CoreIdentity &identity } if(!db.commit()) { - qWarning() << "PostgreSqlStorage::updateIdentity(): commiting data failed!"; + qWarning() << "PostgreSqlStorage::updateIdentity(): committing data failed!"; qWarning() << " -" << qPrintable(db.lastError().text()); return false; } @@ -529,7 +550,7 @@ NetworkId PostgreSqlStorage::createNetwork(UserId user, const NetworkInfo &info) } if(!db.commit()) { - qWarning() << "PostgreSqlStorage::updateNetwork(): commiting data failed!"; + qWarning() << "PostgreSqlStorage::createNetwork(): committing data failed!"; qWarning() << " -" << qPrintable(db.lastError().text()); return NetworkId(); } @@ -538,7 +559,7 @@ NetworkId PostgreSqlStorage::createNetwork(UserId user, const NetworkInfo &info) void PostgreSqlStorage::bindNetworkInfo(QSqlQuery &query, const NetworkInfo &info) { query.bindValue(":networkname", info.networkName); - query.bindValue(":identityid", info.identity.toInt()); + query.bindValue(":identityid", info.identity.isValid() ? info.identity.toInt() : QVariant()); query.bindValue(":encodingcodec", QString(info.codecForEncoding)); query.bindValue(":decodingcodec", QString(info.codecForDecoding)); query.bindValue(":servercodec", QString(info.codecForServer)); @@ -547,6 +568,9 @@ void PostgreSqlStorage::bindNetworkInfo(QSqlQuery &query, const NetworkInfo &inf query.bindValue(":useautoidentify", info.useAutoIdentify); query.bindValue(":autoidentifyservice", info.autoIdentifyService); query.bindValue(":autoidentifypassword", info.autoIdentifyPassword); + query.bindValue(":usesasl", info.useSasl); + query.bindValue(":saslaccount", info.saslAccount); + query.bindValue(":saslpassword", info.saslPassword); query.bindValue(":useautoreconnect", info.useAutoReconnect); query.bindValue(":autoreconnectinterval", info.autoReconnectInterval); query.bindValue(":autoreconnectretries", info.autoReconnectRetries); @@ -616,7 +640,7 @@ bool PostgreSqlStorage::updateNetwork(UserId user, const NetworkInfo &info) { } if(!db.commit()) { - qWarning() << "PostgreSqlStorage::updateNetwork(): commiting data failed!"; + qWarning() << "PostgreSqlStorage::updateNetwork(): committing data failed!"; qWarning() << " -" << qPrintable(db.lastError().text()); return false; } @@ -686,6 +710,9 @@ QList PostgreSqlStorage::networks(UserId user) { net.autoReconnectRetries = networksQuery.value(13).toInt(); net.unlimitedReconnectRetries = networksQuery.value(14).toBool(); net.rejoinChannels = networksQuery.value(15).toBool(); + net.useSasl = networksQuery.value(16).toBool(); + net.saslAccount = networksQuery.value(17).toString(); + net.saslPassword = networksQuery.value(18).toString(); serversQuery.bindValue(":networkid", net.networkId.toInt()); safeExec(serversQuery); @@ -886,6 +913,8 @@ BufferInfo PostgreSqlStorage::bufferInfo(UserId user, const NetworkId &networkId createQuery.bindValue(":buffertype", (int)type); createQuery.bindValue(":buffername", buffer); createQuery.bindValue(":buffercname", buffer.toLower()); + createQuery.bindValue(":joined", type & BufferInfo::ChannelBuffer ? true : false); + safeExec(createQuery); if(createQuery.lastError().isValid()) { @@ -991,7 +1020,7 @@ bool PostgreSqlStorage::removeBuffer(const UserId &user, const BufferId &bufferI return false; case 1: db.commit(); - return false; + return true; default: // there was more then one buffer deleted... qWarning() << "PostgreSqlStorage::removeBuffer(): Userid" << user << "BufferId" << "caused deletion of" << numRows << "Buffers! Rolling back transaction..."; @@ -1027,7 +1056,7 @@ bool PostgreSqlStorage::renameBuffer(const UserId &user, const BufferId &bufferI return false; case 1: db.commit(); - return false; + return true; default: // there was more then one buffer deleted... qWarning() << "PostgreSqlStorage::renameBuffer(): Userid" << user << "BufferId" << "affected" << numRows << "Buffers! Rolling back transaction..."; @@ -1045,7 +1074,7 @@ bool PostgreSqlStorage::mergeBuffersPermanently(const UserId &user, const Buffer } QSqlQuery checkQuery(db); - checkQuery.prepare("SELECT count(*) FROM buffer" + checkQuery.prepare("SELECT count(*) FROM buffer " "WHERE userid = :userid AND bufferid IN (:buffer1, :buffer2)"); checkQuery.bindValue(":userid", user.toInt()); checkQuery.bindValue(":buffer1", bufferId1.toInt()); @@ -1073,6 +1102,7 @@ bool PostgreSqlStorage::mergeBuffersPermanently(const UserId &user, const Buffer QSqlQuery delBufferQuery(logDb()); delBufferQuery.prepare(queryString("delete_buffer_for_bufferid")); + delBufferQuery.bindValue(":userid", user.toInt()); delBufferQuery.bindValue(":bufferid", bufferId2.toInt()); safeExec(delBufferQuery); if(!watchQuery(delBufferQuery)) { @@ -1122,18 +1152,49 @@ QHash PostgreSqlStorage::bufferLastSeenMsgIds(UserId user) { return lastSeenHash; } -bool PostgreSqlStorage::logMessage(Message &msg) { +void PostgreSqlStorage::setBufferMarkerLineMsg(UserId user, const BufferId &bufferId, const MsgId &msgId) { + QSqlQuery query(logDb()); + query.prepare(queryString("update_buffer_markerlinemsgid")); + + query.bindValue(":userid", user.toInt()); + query.bindValue(":bufferid", bufferId.toInt()); + query.bindValue(":markerlinemsgid", msgId.toInt()); + safeExec(query); + watchQuery(query); +} + +QHash PostgreSqlStorage::bufferMarkerLineMsgIds(UserId user) { + QHash markerLineHash; + QSqlDatabase db = logDb(); - if(!db.transaction()) { - qWarning() << "PostgreSqlStorage::logMessage(): cannot start transaction!"; + if(!beginReadOnlyTransaction(db)) { + qWarning() << "PostgreSqlStorage::bufferMarkerLineMsgIds(): cannot start read only transaction!"; qWarning() << " -" << qPrintable(db.lastError().text()); - return false; + return markerLineHash; } - if(!prepareQuery("insert_message", queryString("insert_message"), db)) { - qWarning() << "PostgreSqlStorage::logMessages(): unable to prepare query:" << queryString("insert_message"); - qWarning() << " Error:" << db.lastError().text(); + QSqlQuery query(db); + query.prepare(queryString("select_buffer_markerlinemsgids")); + query.bindValue(":userid", user.toInt()); + safeExec(query); + if(!watchQuery(query)) { db.rollback(); + return markerLineHash; + } + + while(query.next()) { + markerLineHash[query.value(0).toInt()] = query.value(1).toInt(); + } + + db.commit(); + return markerLineHash; +} + +bool PostgreSqlStorage::logMessage(Message &msg) { + QSqlDatabase db = logDb(); + if(!db.transaction()) { + qWarning() << "PostgreSqlStorage::logMessage(): cannot start transaction!"; + qWarning() << " -" << qPrintable(db.lastError().text()); return false; } @@ -1151,7 +1212,6 @@ bool PostgreSqlStorage::logMessage(Message &msg) { db.rollback(); db.transaction(); - // it's possible that the sender was already added by another thread // since the insert might fail we're setting a savepoint savePoint("sender_sp1", db); @@ -1190,12 +1250,6 @@ bool PostgreSqlStorage::logMessages(MessageList &msgs) { return false; } - if(!prepareQuery("insert_sender", queryString("insert_sender"), db)) { - qWarning() << "PostgreSqlStorage::logMessages(): unable to prepare query:" << queryString("insert_sender"); - qWarning() << " Error:" << db.lastError().text(); - db.rollback(); - return false; - } QSet senders; for(int i = 0; i < msgs.count(); i++) { const QString &sender = msgs.at(i).sender(); @@ -1212,12 +1266,6 @@ bool PostgreSqlStorage::logMessages(MessageList &msgs) { } // yes we loop twice over the same list. This avoids alternating queries. - if(!prepareQuery("insert_message", queryString("insert_message"), db)) { - qWarning() << "PostgreSqlStorage::logMessages(): unable to prepare query:" << queryString("insert_message"); - qWarning() << " Error:" << db.lastError().text(); - db.rollback(); - return false; - } bool error = false; for(int i = 0; i < msgs.count(); i++) { Message &msg = msgs[i]; @@ -1285,13 +1333,6 @@ QList PostgreSqlStorage::requestMsgs(UserId user, BufferId bufferId, Ms else params << "ALL"; - if(!prepareQuery(queryName, queryString(queryName), db)) { - qWarning() << "PostgreSqlStorage::logMessages(): unable to prepare query:" << queryString(queryName); - qWarning() << " Error:" << db.lastError().text(); - db.rollback(); - return messagelist; - } - QSqlQuery query = executePreparedQuery(queryName, params, db); if(!watchQuery(query)) { @@ -1395,30 +1436,49 @@ bool PostgreSqlStorage::beginReadOnlyTransaction(QSqlDatabase &db) { return !query.lastError().isValid(); } -bool PostgreSqlStorage::prepareQuery(const QString &handle, const QString &query, const QSqlDatabase &db) { - if(_preparedQueries.contains(db.connectionName()) && _preparedQueries[db.connectionName()].contains(handle)) - return true; // already prepared +QSqlQuery PostgreSqlStorage::prepareAndExecuteQuery(const QString &queryname, const QString ¶mstring, const QSqlDatabase &db) { + // Query preparing is done lazily. That means that instead of always checking if the query is already prepared + // we just EXECUTE and catch the error + QSqlQuery query; - QMutexLocker locker(&_queryHashMutex); + db.exec("SAVEPOINT quassel_prepare_query"); + if(paramstring.isNull()) { + query = db.exec(QString("EXECUTE quassel_%1").arg(queryname)); + } else { + query = db.exec(QString("EXECUTE quassel_%1 (%2)").arg(queryname).arg(paramstring)); + } - static unsigned int stmtCount = 0; - QString queryId = QLatin1String("quassel_") + QString::number(++stmtCount, 16); - // qDebug() << "prepare:" << QString("PREPARE %1 AS %2").arg(queryId).arg(query); - db.exec(QString("PREPARE %1 AS %2").arg(queryId).arg(query)); if(db.lastError().isValid()) { - return false; + // and once again: Qt leaves us without error codes so we either parse (language dependant(!)) strings + // or we just guess the error. As we're only interested in unprepared queries, this will be our guess. :) + db.exec("ROLLBACK TO SAVEPOINT quassel_prepare_query"); + QSqlQuery checkQuery = db.exec(QString("SELECT count(name) FROM pg_prepared_statements WHERE name = 'quassel_%1' AND from_sql = TRUE").arg(queryname.toLower())); + checkQuery.first(); + if(checkQuery.value(0).toInt() == 0) { + db.exec(QString("PREPARE quassel_%1 AS %2").arg(queryname).arg(queryString(queryname))); + if(db.lastError().isValid()) { + qWarning() << "PostgreSqlStorage::prepareQuery(): unable to prepare query:" << queryname << "AS" << queryString(queryname); + qWarning() << " Error:" << db.lastError().text(); + return QSqlQuery(db); + } + } + // we alwas execute the query again, even if the query was already prepared. + // this ensures, that the error is properly propagated to the calling function + // (otherwise the lasst call would be the test select to pg_prepared_statements + // which always gives a proper result) + if(paramstring.isNull()) { + query = db.exec(QString("EXECUTE quassel_%1").arg(queryname)); + } else { + query = db.exec(QString("EXECUTE quassel_%1 (%2)").arg(queryname).arg(paramstring)); + } } else { - _preparedQueries[db.connectionName()][handle] = queryId; - return true; + // only release the SAVEPOINT + db.exec("RELEASE SAVEPOINT quassel_prepare_query"); } + return query; } -QSqlQuery PostgreSqlStorage::executePreparedQuery(const QString &handle, const QVariantList ¶ms, const QSqlDatabase &db) { - if(!_preparedQueries.contains(db.connectionName()) || !_preparedQueries[db.connectionName()].contains(handle)) { - qWarning() << "PostgreSqlStorage::executePreparedQuery() no prepared Query with handle" << handle << "on Database" << db.connectionName(); - return QSqlQuery(db); - } - +QSqlQuery PostgreSqlStorage::executePreparedQuery(const QString &queryname, const QVariantList ¶ms, const QSqlDatabase &db) { QSqlDriver *driver = db.driver(); QStringList paramStrings; @@ -1434,21 +1494,14 @@ QSqlQuery PostgreSqlStorage::executePreparedQuery(const QString &handle, const Q paramStrings << driver->formatValue(field); } - const QString &queryId = _preparedQueries[db.connectionName()][handle]; if(params.isEmpty()) { - return db.exec(QString("EXECUTE %1").arg(queryId)); + return prepareAndExecuteQuery(queryname, db); } else { - // qDebug() << "preparedExec:" << QString("EXECUTE %1 (%2)").arg(queryId).arg(paramStrings.join(", ")); - return db.exec(QString("EXECUTE %1 (%2)").arg(queryId).arg(paramStrings.join(", "))); + return prepareAndExecuteQuery(queryname, paramStrings.join(", "), db); } } -QSqlQuery PostgreSqlStorage::executePreparedQuery(const QString &handle, const QVariant ¶m, const QSqlDatabase &db) { - if(!_preparedQueries.contains(db.connectionName()) || !_preparedQueries[db.connectionName()].contains(handle)) { - qWarning() << "PostgreSqlStorage::executePreparedQuery() no prepared Query with handle" << handle << "on Database" << db.connectionName(); - return QSqlQuery(db); - } - +QSqlQuery PostgreSqlStorage::executePreparedQuery(const QString &queryname, const QVariant ¶m, const QSqlDatabase &db) { QSqlField field; field.setType(param.type()); if(param.isNull()) @@ -1456,20 +1509,12 @@ QSqlQuery PostgreSqlStorage::executePreparedQuery(const QString &handle, const Q else field.setValue(param); - const QString &queryId = _preparedQueries[db.connectionName()][handle]; QString paramString = db.driver()->formatValue(field); - - // qDebug() << "preparedExec:" << QString("EXECUTE %1 (%2)").arg(queryId).arg(paramString); - return db.exec(QString("EXECUTE %1 (%2)").arg(queryId).arg(paramString)); + return prepareAndExecuteQuery(queryname, paramString, db); } -void PostgreSqlStorage::deallocateQuery(const QString &handle, const QSqlDatabase &db) { - if(!_preparedQueries.contains(db.connectionName()) || !_preparedQueries[db.connectionName()].contains(handle)) { - return; - } - QMutexLocker locker(&_queryHashMutex); - QString queryId = _preparedQueries[db.connectionName()].take(handle); - db.exec(QString("DEALLOCATE %1").arg(queryId)); +void PostgreSqlStorage::deallocateQuery(const QString &queryname, const QSqlDatabase &db) { + db.exec(QString("DEALLOCATE quassel_%1").arg(queryname)); } // ======================================== @@ -1490,6 +1535,7 @@ bool PostgreSqlMigrationWriter::prepareQuery(MigrationObject mo) { query = queryString("migrate_write_sender"); break; case Identity: + _validIdentities.clear(); query = queryString("migrate_write_identity"); break; case IdentityNick: @@ -1532,6 +1578,7 @@ bool PostgreSqlMigrationWriter::writeMo(const SenderMO &sender) { //bool PostgreSqlMigrationWriter::writeIdentity(const IdentityMO &identity) { bool PostgreSqlMigrationWriter::writeMo(const IdentityMO &identity) { + _validIdentities << identity.id.toInt(); bindValue(0, identity.id.toInt()); bindValue(1, identity.userid.toInt()); bindValue(2, identity.identityname); @@ -1569,7 +1616,10 @@ bool PostgreSqlMigrationWriter::writeMo(const NetworkMO &network) { bindValue(0, network.networkid.toInt()); bindValue(1, network.userid.toInt()); bindValue(2, network.networkname); - bindValue(3, network.identityid.toInt()); + if(_validIdentities.contains(network.identityid.toInt())) + bindValue(3, network.identityid.toInt()); + else + bindValue(3, QVariant()); bindValue(4, network.encodingcodec); bindValue(5, network.decodingcodec); bindValue(6, network.servercodec); @@ -1588,6 +1638,9 @@ bool PostgreSqlMigrationWriter::writeMo(const NetworkMO &network) { bindValue(19, network.awaymessage); bindValue(20, network.attachperform); bindValue(21, network.detachperform); + bindValue(22, network.usesasl); + bindValue(23, network.saslaccount); + bindValue(24, network.saslpassword); return exec(); } @@ -1601,8 +1654,9 @@ bool PostgreSqlMigrationWriter::writeMo(const BufferMO &buffer) { bindValue(5, buffer.buffercname); bindValue(6, (int)buffer.buffertype); bindValue(7, buffer.lastseenmsgid); - bindValue(8, buffer.key); - bindValue(9, buffer.joined); + bindValue(8, buffer.markerlinemsgid); + bindValue(9, buffer.key); + bindValue(10, buffer.joined); return exec(); } @@ -1649,6 +1703,7 @@ bool PostgreSqlMigrationWriter::postProcess() { QSqlDatabase db = logDb(); QList sequences; sequences << Sequence("backlog", "messageid") + << Sequence("buffer", "bufferid") << Sequence("identity", "identityid") << Sequence("identity_nick", "nickid") << Sequence("ircserver", "serverid")