}
bool PostgreSqlStorage::isAvailable() const {
+ qDebug() << QSqlDatabase::drivers();
if(!QSqlDatabase::isDriverAvailable("QPSQL")) return false;
return true;
}
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::createNetwork(): commiting data failed!";
+ qWarning() << "PostgreSqlStorage::createNetwork(): committing data failed!";
qWarning() << " -" << qPrintable(db.lastError().text());
return NetworkId();
}
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()) {
}
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;
}
-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<BufferId, MsgId> PostgreSqlStorage::bufferMarkerLineMsgIds(UserId user) {
+ QHash<BufferId, MsgId> 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;
}
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);
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;
for(int i = 0; i < msgs.count(); i++) {
const QString &sender = msgs.at(i).sender();
}
// 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];
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 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;
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();
}