From aca3263f8f94e0d6640bdf857e71115151b85037 Mon Sep 17 00:00:00 2001 From: Marcus Eggenberger Date: Sat, 28 Feb 2009 19:08:20 +0100 Subject: [PATCH] first version of postgres migration writer --- .../PostgreSQL/14/migrate_write_backlog.sql | 2 + .../PostgreSQL/14/migrate_write_buffer.sql | 2 + .../PostgreSQL/14/migrate_write_identity.sql | 3 + .../14/migrate_write_identity_nick.sql | 2 + .../PostgreSQL/14/migrate_write_ircserver.sql | 2 + .../PostgreSQL/14/migrate_write_network.sql | 2 + .../14/migrate_write_quasseluser.sql | 2 + .../PostgreSQL/14/migrate_write_sender.sql | 2 + .../14/migrate_write_usersetting.sql | 2 + src/core/postgresqlstorage.cpp | 178 +++++++++++++++++- src/core/postgresqlstorage.h | 40 +++- src/core/sql.qrc | 9 + 12 files changed, 242 insertions(+), 4 deletions(-) create mode 100644 src/core/SQL/PostgreSQL/14/migrate_write_backlog.sql create mode 100644 src/core/SQL/PostgreSQL/14/migrate_write_buffer.sql create mode 100644 src/core/SQL/PostgreSQL/14/migrate_write_identity.sql create mode 100644 src/core/SQL/PostgreSQL/14/migrate_write_identity_nick.sql create mode 100644 src/core/SQL/PostgreSQL/14/migrate_write_ircserver.sql create mode 100644 src/core/SQL/PostgreSQL/14/migrate_write_network.sql create mode 100644 src/core/SQL/PostgreSQL/14/migrate_write_quasseluser.sql create mode 100644 src/core/SQL/PostgreSQL/14/migrate_write_sender.sql create mode 100644 src/core/SQL/PostgreSQL/14/migrate_write_usersetting.sql diff --git a/src/core/SQL/PostgreSQL/14/migrate_write_backlog.sql b/src/core/SQL/PostgreSQL/14/migrate_write_backlog.sql new file mode 100644 index 00000000..a6035221 --- /dev/null +++ b/src/core/SQL/PostgreSQL/14/migrate_write_backlog.sql @@ -0,0 +1,2 @@ +INSERT INTO backlog (messageid, time, bufferid, type, flags, senderid, message) +VALUES (?, ?, ?, ?, ?, ?, ?) diff --git a/src/core/SQL/PostgreSQL/14/migrate_write_buffer.sql b/src/core/SQL/PostgreSQL/14/migrate_write_buffer.sql new file mode 100644 index 00000000..d2bf1f85 --- /dev/null +++ b/src/core/SQL/PostgreSQL/14/migrate_write_buffer.sql @@ -0,0 +1,2 @@ +INSERT INTO buffer (bufferid, userid, groupid, networkid, buffername, buffercname, buffertype, lastseenmsgid, key, joined) +VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) diff --git a/src/core/SQL/PostgreSQL/14/migrate_write_identity.sql b/src/core/SQL/PostgreSQL/14/migrate_write_identity.sql new file mode 100644 index 00000000..e9abfbe6 --- /dev/null +++ b/src/core/SQL/PostgreSQL/14/migrate_write_identity.sql @@ -0,0 +1,3 @@ +INSERT INTO identity (identityid, userid, identityname, realname, awaynick, awaynickenabled, awayreason, awayreasonenabled, autoawayenabled, autoawaytime, autoawayreason, + autoawayreasonenabled, detachawayenabled, detachawayreason, detachawayreasonenabled, ident, kickreason, partreason, quitreason, sslcert, sslkey) +VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) \ No newline at end of file diff --git a/src/core/SQL/PostgreSQL/14/migrate_write_identity_nick.sql b/src/core/SQL/PostgreSQL/14/migrate_write_identity_nick.sql new file mode 100644 index 00000000..466a5713 --- /dev/null +++ b/src/core/SQL/PostgreSQL/14/migrate_write_identity_nick.sql @@ -0,0 +1,2 @@ +INSERT INTO identity_nick (nickid, identityid, nick) +VALUES (?, ?, ?) diff --git a/src/core/SQL/PostgreSQL/14/migrate_write_ircserver.sql b/src/core/SQL/PostgreSQL/14/migrate_write_ircserver.sql new file mode 100644 index 00000000..e4d45dae --- /dev/null +++ b/src/core/SQL/PostgreSQL/14/migrate_write_ircserver.sql @@ -0,0 +1,2 @@ +INSERT INTO ircserver (serverid, userid, networkid, hostname, port, password, ssl, sslversion, useproxy, proxytype, proxyhost, proxyport, proxyuser, proxypass) +VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) diff --git a/src/core/SQL/PostgreSQL/14/migrate_write_network.sql b/src/core/SQL/PostgreSQL/14/migrate_write_network.sql new file mode 100644 index 00000000..eeae51ae --- /dev/null +++ b/src/core/SQL/PostgreSQL/14/migrate_write_network.sql @@ -0,0 +1,2 @@ +INSERT INTO network (networkid, userid, networkname, identityid, encodingcodec, decodingcodec, servercodec, userandomserver, perform, useautoidentify, autoidentifyservice, autoidentifypassword, useautoreconnect, autoreconnectinterval, autoreconnectretries, unlimitedconnectretries, rejoinchannels, connected, usermode, awaymessage, attachperform, detachperform) +VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) diff --git a/src/core/SQL/PostgreSQL/14/migrate_write_quasseluser.sql b/src/core/SQL/PostgreSQL/14/migrate_write_quasseluser.sql new file mode 100644 index 00000000..ae18c226 --- /dev/null +++ b/src/core/SQL/PostgreSQL/14/migrate_write_quasseluser.sql @@ -0,0 +1,2 @@ +INSERT INTO quasseluser (userid, username, password) +VALUES (?, ?, ?) diff --git a/src/core/SQL/PostgreSQL/14/migrate_write_sender.sql b/src/core/SQL/PostgreSQL/14/migrate_write_sender.sql new file mode 100644 index 00000000..bf953cd3 --- /dev/null +++ b/src/core/SQL/PostgreSQL/14/migrate_write_sender.sql @@ -0,0 +1,2 @@ +INSERT INTO sender (senderid, sender) +VALUES (?, ?) diff --git a/src/core/SQL/PostgreSQL/14/migrate_write_usersetting.sql b/src/core/SQL/PostgreSQL/14/migrate_write_usersetting.sql new file mode 100644 index 00000000..3b4c681f --- /dev/null +++ b/src/core/SQL/PostgreSQL/14/migrate_write_usersetting.sql @@ -0,0 +1,2 @@ +INSERT INTO user_setting (userid, settingname, settingvalue) +VALUES (?, ?, ?) diff --git a/src/core/postgresqlstorage.cpp b/src/core/postgresqlstorage.cpp index f0978086..c37bab43 100644 --- a/src/core/postgresqlstorage.cpp +++ b/src/core/postgresqlstorage.cpp @@ -26,8 +26,6 @@ #include "network.h" #include "quassel.h" -int PostgreSqlStorage::_maxRetryCount = 150; // yes this is a large number... only other way to "handle" this is bailing out... - PostgreSqlStorage::PostgreSqlStorage(QObject *parent) : AbstractSqlStorage(parent), _port(-1) @@ -1439,3 +1437,179 @@ void PostgreSqlStorage::deallocateQuery(const QString &handle, const QSqlDatabas db.exec(QString("DEALLOCATE %1").arg(queryId)); } + + + + +// ======================================== +// PostgreSqlMigrationWriter +// ======================================== +PostgreSqlMigrationWriter::PostgreSqlMigrationWriter() + : PostgreSqlStorage() +{ +} + +bool PostgreSqlMigrationWriter::prepareQuery(MigrationObject mo) { + QString query; + switch(mo) { + case QuasselUser: + query = queryString("migrate_write_quasseluser"); + break; + case Sender: + query = queryString("migrate_write_sender"); + break; + case Identity: + query = queryString("migrate_write_identity"); + break; + case IdentityNick: + query = queryString("migrate_write_identity_nick"); + break; + case Network: + query = queryString("migrate_write_network"); + break; + case Buffer: + query = queryString("migrate_write_buffer"); + break; + case Backlog: + query = queryString("migrate_write_backlog"); + break; + case IrcServer: + query = queryString("migrate_write_ircserver"); + break; + case UserSetting: + query = queryString("migrate_write_usersetting"); + break; + } + newQuery(query, logDb()); + return true; +} + +//bool PostgreSqlMigrationWriter::writeUser(const QuasselUserMO &user) { +bool PostgreSqlMigrationWriter::writeMo(const QuasselUserMO &user) { + bindValue(0, user.id.toInt()); + bindValue(1, user.username); + bindValue(2, user.password); + return exec(); +} + +//bool PostgreSqlMigrationWriter::writeSender(const SenderMO &sender) { +bool PostgreSqlMigrationWriter::writeMo(const SenderMO &sender) { + bindValue(0, sender.senderId); + bindValue(1, sender.sender); + return exec(); +} + +//bool PostgreSqlMigrationWriter::writeIdentity(const IdentityMO &identity) { +bool PostgreSqlMigrationWriter::writeMo(const IdentityMO &identity) { + bindValue(0, identity.id.toInt()); + bindValue(1, identity.userid.toInt()); + bindValue(2, identity.identityname); + bindValue(3, identity.realname); + bindValue(4, identity.awayNick); + bindValue(5, identity.awayNickEnabled); + bindValue(6, identity.awayReason); + bindValue(7, identity.awayReasonEnabled); + bindValue(8, identity.autoAwayEnabled); + bindValue(9, identity.autoAwayTime); + bindValue(10, identity.autoAwayReason); + bindValue(11, identity.autoAwayReasonEnabled); + bindValue(12, identity.detachAwayEnabled); + bindValue(13, identity.detachAwayReason); + bindValue(14, identity.detchAwayReasonEnabled); + bindValue(15, identity.ident); + bindValue(16, identity.kickReason); + bindValue(17, identity.partReason); + bindValue(18, identity.quitReason); + bindValue(19, identity.sslCert); + bindValue(20, identity.sslKey); + return exec(); +} + +//bool PostgreSqlMigrationWriter::writeIdentityNick(const IdentityNickMO &identityNick) { +bool PostgreSqlMigrationWriter::writeMo(const IdentityNickMO &identityNick) { + bindValue(0, identityNick.nickid); + bindValue(1, identityNick.identityId.toInt()); + bindValue(2, identityNick.nick); + return exec(); +} + +//bool PostgreSqlMigrationWriter::writeNetwork(const NetworkMO &network) { +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()); + bindValue(4, network.encodingcodec); + bindValue(5, network.decodingcodec); + bindValue(6, network.servercodec); + bindValue(7, network.userandomserver); + bindValue(8, network.perform); + bindValue(9, network.useautoidentify); + bindValue(10, network.autoidentifyservice); + bindValue(11, network.autoidentifypassword); + bindValue(12, network.useautoreconnect); + bindValue(13, network.autoreconnectinterval); + bindValue(14, network.autoreconnectretries); + bindValue(15, network.unlimitedconnectretries); + bindValue(16, network.rejoinchannels); + bindValue(17, network.connected); + bindValue(18, network.usermode); + bindValue(19, network.awaymessage); + bindValue(20, network.attachperform); + bindValue(21, network.detachperform); + return exec(); +} + +//bool PostgreSqlMigrationWriter::writeBuffer(const BufferMO &buffer) { +bool PostgreSqlMigrationWriter::writeMo(const BufferMO &buffer) { + bindValue(0, buffer.bufferid.toInt()); + bindValue(1, buffer.userid.toInt()); + bindValue(2, buffer.groupid); + bindValue(3, buffer.networkid.toInt()); + bindValue(4, buffer.buffername); + bindValue(5, buffer.buffercname); + bindValue(6, (int)buffer.buffertype); + bindValue(7, buffer.lastseenmsgid); + bindValue(8, buffer.key); + bindValue(9, buffer.joined); + return exec(); +} + +//bool PostgreSqlMigrationWriter::writeBacklog(const BacklogMO &backlog) { +bool PostgreSqlMigrationWriter::writeMo(const BacklogMO &backlog) { + bindValue(0, backlog.messageid.toInt()); + bindValue(1, backlog.time); + bindValue(2, backlog.bufferid.toInt()); + bindValue(3, backlog.type); + bindValue(4, (int)backlog.flags); + bindValue(5, backlog.senderid); + bindValue(6, backlog.message); + return exec(); +} + +//bool PostgreSqlMigrationWriter::writeIrcServer(const IrcServerMO &ircserver) { +bool PostgreSqlMigrationWriter::writeMo(const IrcServerMO &ircserver) { + bindValue(0, ircserver.serverid); + bindValue(1, ircserver.userid.toInt()); + bindValue(2, ircserver.networkid.toInt()); + bindValue(3, ircserver.hostname); + bindValue(4, ircserver.port); + bindValue(5, ircserver.password); + bindValue(6, ircserver.ssl); + bindValue(7, ircserver.sslversion); + bindValue(8, ircserver.useproxy); + bindValue(9, ircserver.proxytype); + bindValue(10, ircserver.proxyhost); + bindValue(11, ircserver.proxyport); + bindValue(12, ircserver.proxyuser); + bindValue(13, ircserver.proxypass); + return exec(); +} + +//bool PostgreSqlMigrationWriter::writeUserSetting(const UserSettingMO &userSetting) { +bool PostgreSqlMigrationWriter::writeMo(const UserSettingMO &userSetting) { + bindValue(0, userSetting.userid.toInt()); + bindValue(1, userSetting.settingname); + bindValue(2, userSetting.settingvalue); + return exec(); +} diff --git a/src/core/postgresqlstorage.h b/src/core/postgresqlstorage.h index f699ca11..f78422a0 100644 --- a/src/core/postgresqlstorage.h +++ b/src/core/postgresqlstorage.h @@ -130,8 +130,6 @@ private: QString _userName; QString _password; - static int _maxRetryCount; - typedef QHash QueryHash; QHash _preparedQueries; // one query hash per db connection QMutex _queryHashMutex; @@ -140,4 +138,42 @@ private: inline void PostgreSqlStorage::safeExec(QSqlQuery &query) { query.exec(); } + +// ======================================== +// PostgreSqlMigration +// ======================================== +class PostgreSqlMigrationWriter : public PostgreSqlStorage, public AbstractSqlMigrationWriter { + Q_OBJECT + +public: + PostgreSqlMigrationWriter(); + +// virtual bool writeUser(const QuasselUserMO &user); +// virtual bool writeSender(const SenderMO &sender); +// virtual bool writeIdentity(const IdentityMO &identity); +// virtual bool writeIdentityNick(const IdentityNickMO &identityNick); +// virtual bool writeNetwork(const NetworkMO &network); +// virtual bool writeBuffer(const BufferMO &buffer); +// virtual bool writeBacklog(const BacklogMO &backlog); +// virtual bool writeIrcServer(const IrcServerMO &ircserver); +// virtual bool writeUserSetting(const UserSettingMO &userSetting); + + virtual bool writeMo(const QuasselUserMO &user); + virtual bool writeMo(const SenderMO &sender); + virtual bool writeMo(const IdentityMO &identity); + virtual bool writeMo(const IdentityNickMO &identityNick); + virtual bool writeMo(const NetworkMO &network); + virtual bool writeMo(const BufferMO &buffer); + virtual bool writeMo(const BacklogMO &backlog); + virtual bool writeMo(const IrcServerMO &ircserver); + virtual bool writeMo(const UserSettingMO &userSetting); + + bool prepareQuery(MigrationObject mo); + +protected: + virtual inline bool transaction() { return logDb().transaction(); } + virtual inline void rollback() { logDb().rollback(); } + virtual inline bool commit() { return logDb().commit(); } +}; + #endif diff --git a/src/core/sql.qrc b/src/core/sql.qrc index d028ac7e..307fb947 100644 --- a/src/core/sql.qrc +++ b/src/core/sql.qrc @@ -21,6 +21,15 @@ ./SQL/PostgreSQL/14/insert_sender.sql ./SQL/PostgreSQL/14/insert_server.sql ./SQL/PostgreSQL/14/insert_user_setting.sql + ./SQL/PostgreSQL/14/migrate_write_backlog.sql + ./SQL/PostgreSQL/14/migrate_write_buffer.sql + ./SQL/PostgreSQL/14/migrate_write_identity.sql + ./SQL/PostgreSQL/14/migrate_write_identity_nick.sql + ./SQL/PostgreSQL/14/migrate_write_ircserver.sql + ./SQL/PostgreSQL/14/migrate_write_network.sql + ./SQL/PostgreSQL/14/migrate_write_quasseluser.sql + ./SQL/PostgreSQL/14/migrate_write_sender.sql + ./SQL/PostgreSQL/14/migrate_write_usersetting.sql ./SQL/PostgreSQL/14/select_authuser.sql ./SQL/PostgreSQL/14/select_buffer_by_id.sql ./SQL/PostgreSQL/14/select_buffer_lastseen_messages.sql -- 2.20.1