--- /dev/null
+/*
+ * 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;
{
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;
}
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;
}
"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))";
{
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;
}
if (CommitTransaction())
{
- printfd(__FILE__, "POSTGRESQL_STORE::SaveStat(): 'Failed to commit transaction'\n");
+ printfd(__FILE__, "POSTGRESQL_STORE::SaveUserStat(): 'Failed to commit transaction'\n");
return -1;
}
}
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 "
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;
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());
{
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;
}
//-----------------------------------------------------------------------------
git.stg.codes / stg.git / commitdiff