}
bool PostgreSqlStorage::isAvailable() const {
+ qDebug() << QSqlDatabase::drivers();
if(!QSqlDatabase::isDriverAvailable("QPSQL")) return false;
return true;
}
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) {
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) {
}
}
+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"));
}
if(!db.commit()) {
- qWarning() << "PostgreSqlStorage::createIdentity(): commiting data failed!";
+ qWarning() << "PostgreSqlStorage::createIdentity(): committing data failed!";
qWarning() << " -" << qPrintable(db.lastError().text());
return IdentityId();
}
}
if(!db.commit()) {
- qWarning() << "PostgreSqlStorage::updateIdentity(): commiting data failed!";
+ qWarning() << "PostgreSqlStorage::updateIdentity(): committing data failed!";
qWarning() << " -" << qPrintable(db.lastError().text());
return false;
}
}
if(!db.commit()) {
- qWarning() << "PostgreSqlStorage::updateNetwork(): commiting data failed!";
+ qWarning() << "PostgreSqlStorage::createNetwork(): committing data failed!";
qWarning() << " -" << qPrintable(db.lastError().text());
return NetworkId();
}
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));
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);
}
if(!db.commit()) {
- qWarning() << "PostgreSqlStorage::updateNetwork(): commiting data failed!";
+ qWarning() << "PostgreSqlStorage::updateNetwork(): committing data failed!";
qWarning() << " -" << qPrintable(db.lastError().text());
return false;
}
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);
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()) {
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...";
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...";
}
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());
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)) {
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<BufferId, MsgId> PostgreSqlStorage::bufferMarkerLineMsgIds(UserId user) {
+ QHash<BufferId, MsgId> 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()) {
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;
<< 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();
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<QString> senders;
+ QList<int> senderIdList;
+ QHash<QString, int> 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];
<< 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)) {
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)) {
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;
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())
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));
}
// ========================================
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();
}
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();
}
QSqlDatabase db = logDb();
QList<Sequence> sequences;
sequences << Sequence("backlog", "messageid")
+ << Sequence("buffer", "bufferid")
<< Sequence("identity", "identityid")
<< Sequence("identity_nick", "nickid")
<< Sequence("ircserver", "serverid")
projects / quassel.git / blobdiff