X-Git-Url: https://git.quassel-irc.org/?p=quassel.git;a=blobdiff_plain;f=src%2Fcore%2Fpostgresqlstorage.cpp;h=2de296046317344f692493dd032820a342dccc32;hp=d65b4ee0e044fc43eb278433ebfba5b0f62c8b96;hb=636812603e3308fece0ad677977c85b5cc9f18ee;hpb=c53377c152225754948165f082fe2062a5403dd1 diff --git a/src/core/postgresqlstorage.cpp b/src/core/postgresqlstorage.cpp index d65b4ee0..2de29604 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; } @@ -79,12 +80,10 @@ QVariantMap PostgreSqlStorage::setupDefaults() const { 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) { @@ -151,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) { @@ -182,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")); @@ -325,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(); } @@ -408,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; } @@ -537,7 +550,7 @@ NetworkId PostgreSqlStorage::createNetwork(UserId user, const NetworkInfo &info) } if(!db.commit()) { - qWarning() << "PostgreSqlStorage::createNetwork(): commiting data failed!"; + qWarning() << "PostgreSqlStorage::createNetwork(): committing data failed!"; qWarning() << " -" << qPrintable(db.lastError().text()); return NetworkId(); } @@ -555,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); @@ -624,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; } @@ -694,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); @@ -894,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()) { @@ -1053,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()); @@ -1081,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)) { @@ -1130,6 +1152,44 @@ QHash PostgreSqlStorage::bufferLastSeenMsgIds(UserId user) { return lastSeenHash; } +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(!beginReadOnlyTransaction(db)) { + qWarning() << "PostgreSqlStorage::bufferMarkerLineMsgIds(): cannot start read only transaction!"; + qWarning() << " -" << qPrintable(db.lastError().text()); + return markerLineHash; + } + + 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()) { @@ -1138,11 +1198,26 @@ bool PostgreSqlStorage::logMessage(Message &msg) { return false; } - 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; + QSqlQuery getSenderIdQuery = executePreparedQuery("select_senderid", msg.sender(), db); + int senderId; + if(getSenderIdQuery.first()) { + senderId = getSenderIdQuery.value(0).toInt(); + } else { + // 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); + QSqlQuery addSenderQuery = executePreparedQuery("insert_sender", msg.sender(), db); + + if(addSenderQuery.lastError().isValid()) { + rollbackSavePoint("sender_sp1", db); + getSenderIdQuery = db.exec(getSenderIdQuery.lastQuery()); + getSenderIdQuery.first(); + senderId = getSenderIdQuery.value(0).toInt(); + } else { + releaseSavePoint("sender_sp1", db); + addSenderQuery.first(); + senderId = addSenderQuery.value(0).toInt(); + } } QVariantList params; @@ -1150,33 +1225,13 @@ bool PostgreSqlStorage::logMessage(Message &msg) { << msg.bufferInfo().bufferId().toInt() << msg.type() << (int)msg.flags() - << msg.sender() + << senderId << msg.contents(); QSqlQuery logMessageQuery = executePreparedQuery("insert_message", params, db); - if(logMessageQuery.lastError().isValid()) { - // first we need to reset the transaction + if(!watchQuery(logMessageQuery)) { 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); - QSqlQuery addSenderQuery = executePreparedQuery("insert_sender", msg.sender(), db); - if(addSenderQuery.lastError().isValid()) - rollbackSavePoint("sender_sp1", db); - else - releaseSavePoint("sender_sp1", db); - - logMessageQuery = db.exec(logMessageQuery.lastQuery()); - if(!watchQuery(logMessageQuery)) { - qDebug() << "==================== Sender Query:"; - watchQuery(addSenderQuery); - qDebug() << "==================== /Sender Query"; - db.rollback(); - return false; - } + return false; } logMessageQuery.first(); @@ -1198,34 +1253,41 @@ 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; + QList senderIdList; + QHash senderIds; + QSqlQuery addSenderQuery; + QSqlQuery selectSenderQuery;; for(int i = 0; i < msgs.count(); i++) { const QString &sender = msgs.at(i).sender(); - if(senders.contains(sender)) + if(senderIds.contains(sender)) { + senderIdList << senderIds[sender]; continue; - senders << sender; + } - savePoint("sender_sp", db); - QSqlQuery addSenderQuery = executePreparedQuery("insert_sender", sender, db); - if(addSenderQuery.lastError().isValid()) - rollbackSavePoint("sender_sp", db); - else - releaseSavePoint("sender_sp", db); + selectSenderQuery = executePreparedQuery("select_senderid", sender, db); + if(selectSenderQuery.first()) { + senderIdList << selectSenderQuery.value(0).toInt(); + senderIds[sender] = selectSenderQuery.value(0).toInt(); + } else { + savePoint("sender_sp", db); + addSenderQuery= executePreparedQuery("insert_sender", sender, db); + if(addSenderQuery.lastError().isValid()) { + // seems it was inserted meanwhile... by a different thread + rollbackSavePoint("sender_sp", db); + selectSenderQuery = db.exec(selectSenderQuery.lastQuery()); + selectSenderQuery.first(); + senderIdList << selectSenderQuery.value(0).toInt(); + senderIds[sender] = selectSenderQuery.value(0).toInt(); + } else { + releaseSavePoint("sender_sp", db); + addSenderQuery.first(); + senderIdList << addSenderQuery.value(0).toInt(); + senderIds[sender] = addSenderQuery.value(0).toInt(); + } + } } // 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]; @@ -1234,7 +1296,7 @@ bool PostgreSqlStorage::logMessages(MessageList &msgs) { << msg.bufferInfo().bufferId().toInt() << msg.type() << (int)msg.flags() - << msg.sender() + << senderIdList.at(i) << msg.contents(); QSqlQuery logMessageQuery = executePreparedQuery("insert_message", params, db); if(!watchQuery(logMessageQuery)) { @@ -1293,13 +1355,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)) { @@ -1403,30 +1458,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 last call would be the testing select to pg_prepared_statements + // which always gives a proper result and the error would be lost) + 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; @@ -1442,21 +1516,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()) @@ -1464,20 +1531,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)); } // ======================================== @@ -1601,6 +1660,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(); } @@ -1614,8 +1676,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(); }