From 4ca22a7c19dd0b94609280faf64108b1ea54e415 Mon Sep 17 00:00:00 2001 From: Maxim Mamontov Date: Tue, 14 Jun 2011 12:34:49 +0300 Subject: [PATCH] Full month stats patches added (derived from GTS version) --- .../inst/var/01-alter-02.postgresql.sql | 196 ++++++++++++++++++ .../store/postgresql/postgresql_store.h | 6 +- .../postgresql/postgresql_store_users.cpp | 178 ++++++++++++---- 3 files changed, 342 insertions(+), 38 deletions(-) create mode 100644 projects/stargazer/inst/var/01-alter-02.postgresql.sql diff --git a/projects/stargazer/inst/var/01-alter-02.postgresql.sql b/projects/stargazer/inst/var/01-alter-02.postgresql.sql new file mode 100644 index 00000000..e14a455b --- /dev/null +++ b/projects/stargazer/inst/var/01-alter-02.postgresql.sql @@ -0,0 +1,196 @@ +/* + * DB migration from v01 to v02 (postgres) + */ + +BEGIN; + +CREATE TABLE tb_month_stats +( + pk_month_stats BIGSERIAL PRIMARY KEY, + fk_user INTEGER NOT NULL, + stats_date DATE NOT NULL, + cash dm_money, + free_mb dm_money, + last_activity_time TIMESTAMP NOT NULL, + last_cash_add dm_money, + last_cash_add_time TIMESTAMP NOT NULL, + passive_time INTEGER NOT NULL, + + FOREIGN KEY (fk_user) + REFERENCES tb_users (pk_user) + ON DELETE CASCADE, + UNIQUE (fk_user, stats_date) +); + +CREATE TABLE tb_month_stats_traffic +( + pk_month_stat_traffic BIGSERIAL PRIMARY KEY, + fk_month_stats BIGINT NOT NULL, + dir_num SMALLINT NOT NULL, + download BIGINT NOT NULL, + upload BIGINT NOT NULL, + + FOREIGN KEY (fk_month_stats) + REFERENCES tb_month_stats (pk_month_stats) + ON DELETE CASCADE, + UNIQUE (fk_month_stats, dir_num) +); + +INSERT INTO tb_month_stats + (fk_user, + stats_date, + cash, + free_mb, + last_activity_time, + last_cash_add, + last_cash_add_time, + passive_time) +SELECT fk_user, + stats_date, + 0, + 0, + '1970-01-01 00:00:00'::TIMESTAMP WITHOUT TIME ZONE, + 0, + '1970-01-01 00:00:00'::TIMESTAMP WITHOUT TIME ZONE, + 0 +FROM tb_stats_traffic +WHERE date_trunc('month', stats_date) < date_trunc('month', 'now'::DATE) +GROUP BY fk_user, + stats_date; + +INSERT INTO tb_month_stats_traffic + (fk_month_stats, + dir_num, + download, + upload) +SELECT s.pk_month_stats, + t.dir_num, + t.download, + t.upload +FROM tb_stats_traffic AS t +LEFT JOIN tb_month_stats AS s + ON s.fk_user = t.fk_user AND + s.stats_date = t.stats_date +WHERE date_trunc('month', t.stats_date) < date_trunc('month', 'now'::DATE); + +DROP FUNCTION sp_add_stats_traffic ( dm_name, DATE, SMALLINT, BIGINT, BIGINT ); + +CREATE FUNCTION sp_add_stats_traffic (_login dm_name, + _dir_num SMALLINT, + _upload BIGINT, + _download BIGINT) +RETURNS INTEGER +AS $$ +DECLARE + _pk_user INTEGER; +BEGIN + SELECT pk_user INTO _pk_user + FROM tb_users + WHERE name = _login; + + IF _pk_user IS NULL THEN + RAISE EXCEPTION 'User % not found', _login; + RETURN -1; + END IF; + + UPDATE tb_stats_traffic SET + upload = _upload, + download = _download + WHERE fk_user = _pk_user AND + dir_num = _dir_num; + + IF NOT FOUND THEN + INSERT INTO tb_stats_traffic + (fk_user, + dir_num, + upload, + download) + VALUES + (_pk_user, + _dir_num, + _upload, + _download); + END IF; + + RETURN 1; +END; +$$ LANGUAGE plpgsql; + +DELETE FROM tb_stats_traffic WHERE date_trunc('month', stats_date) < date_trunc('month', 'now'::DATE); +ALTER TABLE tb_stats_traffic DROP stats_date; +ALTER TABLE tb_stats_traffic ADD UNIQUE (fk_user, dir_num); + + +CREATE FUNCTION sp_add_month_stats (_login dm_name, + _stats_date DATE, + _cash dm_money, + _free_mb dm_money, + _last_activity_time TIMESTAMP, + _last_cash_add dm_money, + _last_cash_add_time TIMESTAMP, + _passive_time INTEGER) +RETURNS BIGINT +AS $$ +DECLARE + _pk_user INTEGER; + _pk_month_stats BIGINT; +BEGIN + SELECT pk_user INTO _pk_user + FROM tb_users + WHERE name = _login; + + IF _pk_user IS NULL THEN + RAISE EXCEPTION 'User % not found', _login; + RETURN -1; + END IF; + + INSERT INTO tb_month_stats + (fk_user, + stats_date, + cash, + free_mb, + last_activity_time, + last_cash_add, + last_cash_add_time, + passive_time) + VALUES + (_pk_user, + _stats_date, + _cash, + _free_mb, + _last_activity_time, + _last_cash_add, + _last_cash_add_time, + _passive_time); + + SELECT CURRVAL('tb_month_stats_pk_month_stats_seq') INTO _pk_month_stats; + + RETURN _pk_month_stats; +END; +$$ LANGUAGE plpgsql; + +CREATE FUNCTION sp_add_month_stats_traffic (_pk_month_stats BIGINT, + _dir_num SMALLINT, + _upload BIGINT, + _download BIGINT) +RETURNS INTEGER +AS $$ +BEGIN + INSERT INTO tb_month_stats_traffic + (fk_month_stats, + dir_num, + upload, + download) + VALUES + (_pk_month_stats, + _dir_num, + _upload, + _download); + + RETURN 1; +END; +$$ LANGUAGE plpgsql; + +UPDATE tb_info SET version = 7; + +COMMIT; diff --git a/projects/stargazer/plugins/store/postgresql/postgresql_store.h b/projects/stargazer/plugins/store/postgresql/postgresql_store.h index 8169137e..7b3d7660 100644 --- a/projects/stargazer/plugins/store/postgresql/postgresql_store.h +++ b/projects/stargazer/plugins/store/postgresql/postgresql_store.h @@ -37,9 +37,9 @@ #include "stg/store.h" -// Minimal DB version is 5 -// Recommended DB version is 6 (support FreeMb logging on disconnects) -#define DB_MIN_VERSION 5 +// Minimal DB version is 7 +// Recommended DB version is 7 (support full month stats) +#define DB_MIN_VERSION 7 extern "C" STORE * GetStore(); diff --git a/projects/stargazer/plugins/store/postgresql/postgresql_store_users.cpp b/projects/stargazer/plugins/store/postgresql/postgresql_store_users.cpp index 11c757f2..df802eea 100644 --- a/projects/stargazer/plugins/store/postgresql/postgresql_store_users.cpp +++ b/projects/stargazer/plugins/store/postgresql/postgresql_store_users.cpp @@ -227,30 +227,20 @@ int POSTGRESQL_STORE::SaveUserStat(const USER_STAT & stat, { STG_LOCKER lock(&mutex, __FILE__, __LINE__); -return SaveStat(stat, login); -} -//----------------------------------------------------------------------------- -int POSTGRESQL_STORE::SaveStat(const USER_STAT & stat, - const string & login, - int year, - int month) const -{ if (PQstatus(connection) != CONNECTION_OK) { - printfd(__FILE__, "POSTGRESQL_STORE::SaveStat(): 'Connection lost. Trying to reconnect...'\n", strError.c_str()); + printfd(__FILE__, "POSTGRESQL_STORE::SaveUserStat(): 'Connection lost. Trying to reconnect...'\n", strError.c_str()); if (Reset()) { strError = "Connection lost"; - printfd(__FILE__, "POSTGRESQL_STORE::SaveStat(): '%s'\n", strError.c_str()); + printfd(__FILE__, "POSTGRESQL_STORE::SaveUserStat(): '%s'\n", strError.c_str()); return -1; } } -PGresult * result; - if (StartTransaction()) { - printfd(__FILE__, "POSTGRESQL_STORE::SaveStat(): 'Failed to start transaction'\n"); + printfd(__FILE__, "POSTGRESQL_STORE::SaveUserStat(): 'Failed to start transaction'\n"); return -1; } @@ -258,10 +248,10 @@ std::string elogin = login; if (EscapeString(elogin)) { - printfd(__FILE__, "POSTGRESQL_STORE::SaveStat(): 'Failed to escape login'\n"); + printfd(__FILE__, "POSTGRESQL_STORE::SaveUserStat(): 'Failed to escape login'\n"); if (RollbackTransaction()) { - printfd(__FILE__, "POSTGRESQL_STORE::SaveStat(): 'Failed to rollback transaction'\n"); + printfd(__FILE__, "POSTGRESQL_STORE::SaveUserStat(): 'Failed to rollback transaction'\n"); } return -1; } @@ -276,32 +266,27 @@ query << "UPDATE tb_users SET " "passive_time = " << stat.passiveTime << " " "WHERE name = '" << elogin << "'"; -result = PQexec(connection, query.str().c_str()); +PGresult * result = PQexec(connection, query.str().c_str()); if (PQresultStatus(result) != PGRES_COMMAND_OK) { strError = PQresultErrorMessage(result); PQclear(result); - printfd(__FILE__, "POSTGRESQL_STORE::SaveStat(): '%s'\n", strError.c_str()); + printfd(__FILE__, "POSTGRESQL_STORE::SaveUserStat(): '%s'\n", strError.c_str()); if (RollbackTransaction()) { - printfd(__FILE__, "POSTGRESQL_STORE::SaveStat(): 'Failed to rollback transaction'\n"); + printfd(__FILE__, "POSTGRESQL_STORE::SaveUserStat(): 'Failed to rollback transaction'\n"); } return -1; } PQclear(result); -std::string date; - -MakeDate(date, year, month); - for (int dir = 0; dir < DIR_NUM; ++dir) { query.str(""); query << "SELECT sp_add_stats_traffic (" "'" << elogin << "', " - "CAST('" << date << "' AS DATE), " "CAST(" << dir << " AS SMALLINT), " "CAST(" << stat.up[dir] << " AS BIGINT), " "CAST(" << stat.down[dir] << " AS BIGINT))"; @@ -312,10 +297,10 @@ for (int dir = 0; dir < DIR_NUM; ++dir) { strError = PQresultErrorMessage(result); PQclear(result); - printfd(__FILE__, "POSTGRESQL_STORE::SaveStat(): '%s'\n", strError.c_str()); + printfd(__FILE__, "POSTGRESQL_STORE::SaveUserStat(): '%s'\n", strError.c_str()); if (RollbackTransaction()) { - printfd(__FILE__, "POSTGRESQL_STORE::SaveStat(): 'Failed to rollback transaction'\n"); + printfd(__FILE__, "POSTGRESQL_STORE::SaveUserStat(): 'Failed to rollback transaction'\n"); } return -1; } @@ -325,7 +310,7 @@ for (int dir = 0; dir < DIR_NUM; ++dir) if (CommitTransaction()) { - printfd(__FILE__, "POSTGRESQL_STORE::SaveStat(): 'Failed to commit transaction'\n"); + printfd(__FILE__, "POSTGRESQL_STORE::SaveUserStat(): 'Failed to commit transaction'\n"); return -1; } @@ -639,7 +624,7 @@ if (EscapeString(elogin)) } std::stringstream query; -query << "SELECT cash, free_mb, " +query << "SELECT pk_user, cash, free_mb, " "last_activity_time, last_cash_add, " "last_cash_add_time, passive_time " "FROM tb_users " @@ -676,14 +661,18 @@ if (tuples != 1) std::stringstream tuple; tuple << PQgetvalue(result, 0, 0) << " "; tuple << PQgetvalue(result, 0, 1) << " "; -stat->lastActivityTime = TS2Int(PQgetvalue(result, 0, 2)); -tuple << PQgetvalue(result, 0, 3) << " "; -stat->lastCashAddTime = TS2Int(PQgetvalue(result, 0, 4)); -tuple << PQgetvalue(result, 0, 5) << " "; +tuple << PQgetvalue(result, 0, 2) << " "; +stat->lastActivityTime = TS2Int(PQgetvalue(result, 0, 3)); +tuple << PQgetvalue(result, 0, 4) << " "; +stat->lastCashAddTime = TS2Int(PQgetvalue(result, 0, 5)); +tuple << PQgetvalue(result, 0, 6); PQclear(result); -tuple >> stat->cash +uint32_t uid; + +tuple >> uid + >> stat->cash >> stat->freeMb >> stat->lastCashAdd >> stat->passiveTime; @@ -692,8 +681,7 @@ query.str(""); query << "SELECT dir_num, upload, download " "FROM tb_stats_traffic " - "WHERE fk_user IN (SELECT pk_user FROM tb_users WHERE name = '" << elogin << "') AND " - "DATE_TRUNC('month', stats_date) = DATE_TRUNC('month', CAST('" << Int2TS(stgTime) << "' AS TIMESTAMP))"; + "WHERE fk_user = " << uid; result = PQexec(connection, query.str().c_str()); @@ -1421,7 +1409,127 @@ int POSTGRESQL_STORE::SaveMonthStat(const USER_STAT & stat, int month, int year, { STG_LOCKER lock(&mutex, __FILE__, __LINE__); -return SaveStat(stat, login, year, month); +if (PQstatus(connection) != CONNECTION_OK) + { + printfd(__FILE__, "POSTGRESQL_STORE::SaveMonthStat(): 'Connection lost. Trying to reconnect...'\n", strError.c_str()); + if (Reset()) + { + strError = "Connection lost"; + printfd(__FILE__, "POSTGRESQL_STORE::SaveMonthStat(): '%s'\n", strError.c_str()); + return -1; + } + } + +if (StartTransaction()) + { + printfd(__FILE__, "POSTGRESQL_STORE::SaveMonthStat(): 'Failed to start transaction'\n"); + return -1; + } + +std::string elogin = login; + +if (EscapeString(elogin)) + { + printfd(__FILE__, "POSTGRESQL_STORE::SaveMonthStat(): 'Failed to escape login'\n"); + if (RollbackTransaction()) + { + printfd(__FILE__, "POSTGRESQL_STORE::SaveMonthStat(): 'Failed to rollback transaction'\n"); + } + return -1; + } + +std::string date; + +MakeDate(date, year, month); + +std::stringstream query; +query << "SELECT sp_add_month_stats(" + "'" << elogin << "'," + "CAST('" << date << "' AS DATE), " + "CAST(" << stat.cash << " AS dm_money), " + "CAST(" << stat.freeMb << " AS dm_money), " + "CAST('" << Int2TS(stat.lastActivityTime) << "' AS TIMESTAMP), " + "CAST(" << stat.lastCashAdd << " AS dm_money), " + "CAST('" << Int2TS(stat.lastCashAddTime) << "' AS TIMESTAMP), " + "CAST(" << stat.passiveTime << " AS INTEGER))"; + +PGresult * result = PQexec(connection, query.str().c_str()); + +if (PQresultStatus(result) != PGRES_TUPLES_OK) + { + strError = PQresultErrorMessage(result); + PQclear(result); + printfd(__FILE__, "POSTGRESQL_STORE::SaveMonthStat(): '%s'\n", strError.c_str()); + if (RollbackTransaction()) + { + printfd(__FILE__, "POSTGRESQL_STORE::SaveMonthStat(): 'Failed to rollback transaction'\n"); + } + return -1; + } + +int tuples = PQntuples(result); + +if (tuples != 1) + { + strError = "Failed to fetch month stat's ID"; + printfd(__FILE__, "POSTGRESQL_STORE::SaveMonthStat(): 'Invalid number of tuples. Wanted 1, actulally %d'\n", tuples); + PQclear(result); + if (RollbackTransaction()) + { + printfd(__FILE__, "POSTGRESQL_STORE::SaveMonthStat(): 'Failed to rollback transaction'\n"); + } + return -1; + } + +uint32_t sid; + +if (str2x(PQgetvalue(result, 0, 0), sid)) + { + strError = "Failed to convert string to int"; + printfd(__FILE__, "POSTGRESQL_STORE::SaveMonthStat(): '%s'\n", strError.c_str()); + PQclear(result); + if (RollbackTransaction()) + { + printfd(__FILE__, "POSTGRESQL_STORE::SaveMonthStat(): 'Failed to rollback transaction'\n"); + } + return -1; + } + +PQclear(result); + +for (int dir = 0; dir < DIR_NUM; ++dir) + { + query.str(""); + query << "SELECT sp_add_month_stats_traffic (" + << sid << ", " + "CAST(" << dir << " AS SMALLINT), " + "CAST(" << stat.up[dir] << " AS BIGINT), " + "CAST(" << stat.down[dir] << " AS BIGINT))"; + + result = PQexec(connection, query.str().c_str()); + + if (PQresultStatus(result) != PGRES_TUPLES_OK) + { + strError = PQresultErrorMessage(result); + PQclear(result); + printfd(__FILE__, "POSTGRESQL_STORE::SaveMonthStat(): '%s'\n", strError.c_str()); + if (RollbackTransaction()) + { + printfd(__FILE__, "POSTGRESQL_STORE::SaveMonthStat(): 'Failed to rollback transaction'\n"); + } + return -1; + } + + PQclear(result); + } + +if (CommitTransaction()) + { + printfd(__FILE__, "POSTGRESQL_STORE::SaveMonthStat(): 'Failed to commit transaction'\n"); + return -1; + } + +return 0; } //----------------------------------------------------------------------------- -- 2.44.2