2 * This program is free software; you can redistribute it and/or modify
3 * it under the terms of the GNU General Public License as published by
4 * the Free Software Foundation; either version 2 of the License, or
5 * (at your option) any later version.
7 * This program is distributed in the hope that it will be useful,
8 * but WITHOUT ANY WARRANTY; without even the implied warranty of
9 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
10 * GNU General Public License for more details.
12 * You should have received a copy of the GNU General Public License
13 * along with this program; if not, write to the Free Software
14 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
18 * Author : Maxim Mamontov <faust@stargazer.dp.ua>
22 * User manipulation methods
25 * $Date: 2010/05/07 07:26:36 $
36 #include "stg/const.h"
37 #include "stg/locker.h"
38 #include "../../../stg_timer.h"
39 #include "postgresql_store.h"
41 //-----------------------------------------------------------------------------
42 int POSTGRESQL_STORE::GetUsersList(std::vector<std::string> * usersList) const
44 STG_LOCKER lock(&mutex);
46 if (PQstatus(connection) != CONNECTION_OK)
48 printfd(__FILE__, "POSTGRESQL_STORE::GetUsersList(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
51 strError = "Connection lost";
52 printfd(__FILE__, "POSTGRESQL_STORE::GetUsersList(): '%s'\n", strError.c_str());
59 if (StartTransaction())
61 printfd(__FILE__, "POSTGRESQL_STORE::GetUsersList(): 'Failed to start transaction'\n");
65 result = PQexec(connection, "SELECT name FROM tb_users");
67 if (PQresultStatus(result) != PGRES_TUPLES_OK)
69 strError = PQresultErrorMessage(result);
71 printfd(__FILE__, "POSTGRESQL_STORE::GetUsersList(): '%s'\n", strError.c_str());
72 if (RollbackTransaction())
74 printfd(__FILE__, "POSTGRESQL_STORE::GetUsersList(): 'Failed to rollback transaction'\n");
79 int tuples = PQntuples(result);
81 for (int i = 0; i < tuples; ++i)
83 usersList->push_back(PQgetvalue(result, i, 0));
88 if (CommitTransaction())
90 printfd(__FILE__, "POSTGRESQL_STORE::GetUsersList(): 'Failed to commit transaction'\n");
97 //-----------------------------------------------------------------------------
98 int POSTGRESQL_STORE::AddUser(const std::string & name) const
100 STG_LOCKER lock(&mutex);
102 if (PQstatus(connection) != CONNECTION_OK)
104 printfd(__FILE__, "POSTGRESQL_STORE::AddUser(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
107 strError = "Connection lost";
108 printfd(__FILE__, "POSTGRESQL_STORE::AddUser(): '%s'\n", strError.c_str());
115 if (StartTransaction())
117 printfd(__FILE__, "POSTGRESQL_STORE::AddUser(): 'Failed to start transaction'\n");
121 std::string elogin = name;
123 if (EscapeString(elogin))
125 printfd(__FILE__, "POSTGRESQL_STORE::AddUser(): 'Failed to escape login'\n");
126 if (RollbackTransaction())
128 printfd(__FILE__, "POSTGRESQL_STORE::AddUser(): 'Failed to rollback transaction'\n");
133 std::ostringstream query;
134 query << "SELECT sp_add_user('" << elogin << "')";
136 result = PQexec(connection, query.str().c_str());
138 if (PQresultStatus(result) != PGRES_TUPLES_OK)
140 strError = PQresultErrorMessage(result);
142 printfd(__FILE__, "POSTGRESQL_STORE::AddUser(): '%s'\n", strError.c_str());
143 if (RollbackTransaction())
145 printfd(__FILE__, "POSTGRESQL_STORE::AddUser(): 'Failed to rollback transaction'\n");
152 if (CommitTransaction())
154 printfd(__FILE__, "POSTGRESQL_STORE::AddUser(): 'Failed to commit transaction'\n");
161 //-----------------------------------------------------------------------------
162 int POSTGRESQL_STORE::DelUser(const std::string & login) const
164 STG_LOCKER lock(&mutex);
166 if (PQstatus(connection) != CONNECTION_OK)
168 printfd(__FILE__, "POSTGRESQL_STORE::DelUser(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
171 strError = "Connection lost";
172 printfd(__FILE__, "POSTGRESQL_STORE::DelUser(): '%s'\n", strError.c_str());
179 if (StartTransaction())
181 printfd(__FILE__, "POSTGRESQL_STORE::DelUser(): 'Failed to start transaction'\n");
185 std::string elogin = login;
187 if (EscapeString(elogin))
189 printfd(__FILE__, "POSTGRESQL_STORE::DelUser(): 'Failed to escape login'\n");
190 if (RollbackTransaction())
192 printfd(__FILE__, "POSTGRESQL_STORE::DelUser(): 'Failed to rollback transaction'\n");
197 std::ostringstream query;
198 query << "DELETE FROM tb_users WHERE name = '" << elogin << "'";
200 result = PQexec(connection, query.str().c_str());
202 if (PQresultStatus(result) != PGRES_COMMAND_OK)
204 strError = PQresultErrorMessage(result);
206 printfd(__FILE__, "POSTGRESQL_STORE::DelUser(): '%s'\n", strError.c_str());
207 if (RollbackTransaction())
209 printfd(__FILE__, "POSTGRESQL_STORE::DelUser(): 'Failed to rollback transaction'\n");
216 if (CommitTransaction())
218 printfd(__FILE__, "POSTGRESQL_STORE::DelUser(): 'Failed to commit transaction'\n");
224 //-----------------------------------------------------------------------------
225 int POSTGRESQL_STORE::SaveUserStat(const USER_STAT & stat,
226 const std::string & login) const
228 STG_LOCKER lock(&mutex);
230 return SaveStat(stat, login);
232 //-----------------------------------------------------------------------------
233 int POSTGRESQL_STORE::SaveStat(const USER_STAT & stat,
234 const std::string & login,
238 if (PQstatus(connection) != CONNECTION_OK)
240 printfd(__FILE__, "POSTGRESQL_STORE::SaveStat(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
243 strError = "Connection lost";
244 printfd(__FILE__, "POSTGRESQL_STORE::SaveStat(): '%s'\n", strError.c_str());
251 if (StartTransaction())
253 printfd(__FILE__, "POSTGRESQL_STORE::SaveStat(): 'Failed to start transaction'\n");
257 std::string elogin = login;
259 if (EscapeString(elogin))
261 printfd(__FILE__, "POSTGRESQL_STORE::SaveStat(): 'Failed to escape login'\n");
262 if (RollbackTransaction())
264 printfd(__FILE__, "POSTGRESQL_STORE::SaveStat(): 'Failed to rollback transaction'\n");
269 std::ostringstream query;
270 query << "UPDATE tb_users SET "
271 "cash = " << stat.cash << ", "
272 "free_mb = " << stat.freeMb << ", "
273 "last_activity_time = CAST('" << Int2TS(stat.lastActivityTime) << "' AS TIMESTAMP), "
274 "last_cash_add = " << stat.lastCashAdd << ", "
275 "last_cash_add_time = CAST('" << Int2TS(stat.lastCashAddTime) << "' AS TIMESTAMP), "
276 "passive_time = " << stat.passiveTime << " "
277 "WHERE name = '" << elogin << "'";
279 result = PQexec(connection, query.str().c_str());
281 if (PQresultStatus(result) != PGRES_COMMAND_OK)
283 strError = PQresultErrorMessage(result);
285 printfd(__FILE__, "POSTGRESQL_STORE::SaveStat(): '%s'\n", strError.c_str());
286 if (RollbackTransaction())
288 printfd(__FILE__, "POSTGRESQL_STORE::SaveStat(): 'Failed to rollback transaction'\n");
297 MakeDate(date, year, month);
299 for (int dir = 0; dir < DIR_NUM; ++dir)
302 query << "SELECT sp_add_stats_traffic ("
303 "'" << elogin << "', "
304 "CAST('" << date << "' AS DATE), "
305 "CAST(" << dir << " AS SMALLINT), "
306 "CAST(" << stat.monthUp[dir] << " AS BIGINT), "
307 "CAST(" << stat.monthDown[dir] << " AS BIGINT))";
309 result = PQexec(connection, query.str().c_str());
311 if (PQresultStatus(result) != PGRES_TUPLES_OK)
313 strError = PQresultErrorMessage(result);
315 printfd(__FILE__, "POSTGRESQL_STORE::SaveStat(): '%s'\n", strError.c_str());
316 if (RollbackTransaction())
318 printfd(__FILE__, "POSTGRESQL_STORE::SaveStat(): 'Failed to rollback transaction'\n");
326 if (CommitTransaction())
328 printfd(__FILE__, "POSTGRESQL_STORE::SaveStat(): 'Failed to commit transaction'\n");
335 //-----------------------------------------------------------------------------
336 int POSTGRESQL_STORE::SaveUserConf(const USER_CONF & conf,
337 const std::string & login) const
339 STG_LOCKER lock(&mutex);
341 if (PQstatus(connection) != CONNECTION_OK)
343 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
346 strError = "Connection lost";
347 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): '%s'\n", strError.c_str());
354 if (StartTransaction())
356 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to start transaction'\n");
360 std::string elogin = login;
362 if (EscapeString(elogin))
364 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to escape login'\n");
365 if (RollbackTransaction())
367 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
372 std::ostringstream query;
373 query << "SELECT pk_user FROM tb_users WHERE name = '" << elogin << "'";
375 result = PQexec(connection, query.str().c_str());
377 if (PQresultStatus(result) != PGRES_TUPLES_OK)
379 strError = PQresultErrorMessage(result);
381 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): '%s'\n", strError.c_str());
382 if (RollbackTransaction())
384 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
389 int tuples = PQntuples(result);
393 strError = "Failed to fetch user's ID";
394 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Invalid number of tuples. Wanted 1, actulally %d'\n", tuples);
396 if (RollbackTransaction())
398 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
406 std::stringstream tuple;
407 tuple << PQgetvalue(result, 0, 0);
414 std::string eaddress = conf.address;
415 std::string eemail = conf.email;
416 std::string egroup = conf.group;
417 std::string enote = conf.note;
418 std::string epassword = conf.password;
419 std::string ephone = conf.phone;
420 std::string erealname = conf.realName;
421 std::string etariffname = conf.tariffName;
422 std::string enexttariff = conf.nextTariff;
423 std::string ecorporation = conf.corp;
425 if (EscapeString(eaddress))
427 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to escape address'\n");
428 if (RollbackTransaction())
430 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
435 if (EscapeString(eemail))
437 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to escape email'\n");
438 if (RollbackTransaction())
440 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
445 if (EscapeString(egroup))
447 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to escape group'\n");
448 if (RollbackTransaction())
450 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
455 if (EscapeString(enote))
457 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to escape note'\n");
458 if (RollbackTransaction())
460 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
465 if (EscapeString(epassword))
467 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to escape password'\n");
468 if (RollbackTransaction())
470 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
475 if (EscapeString(ephone))
477 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to escape phone'\n");
478 if (RollbackTransaction())
480 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
485 if (EscapeString(erealname))
487 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to escape real name'\n");
488 if (RollbackTransaction())
490 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
495 if (EscapeString(etariffname))
497 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to escape tariff name'\n");
498 if (RollbackTransaction())
500 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
505 if (EscapeString(enexttariff))
507 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to escape next tariff name'\n");
508 if (RollbackTransaction())
510 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
515 if (EscapeString(ecorporation))
517 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to escape corporation name'\n");
518 if (RollbackTransaction())
520 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
526 query << "UPDATE tb_users SET "
527 "address = '" << eaddress << "', "
528 "always_online = " << (conf.alwaysOnline ? "'t'" : "'f'") << ", "
529 "credit = " << conf.credit << ", "
530 "credit_expire = CAST('" << Int2TS(conf.creditExpire) << "' AS TIMESTAMP), "
531 "disabled = " << (conf.disabled ? "'t'" : "'f'") << ", "
532 "disabled_detail_stat = " << (conf.disabledDetailStat ? "'t'" : "'f'") << ", "
533 "email = '" << eemail << "', "
534 "grp = '" << egroup << "', "
535 "note = '" << enote << "', "
536 "passive = " << (conf.passive ? "'t'" : "'f'") << ", "
537 "passwd = '" << epassword << "', "
538 "phone = '" << ephone << "', "
539 "real_name = '" << erealname << "', "
540 "fk_tariff = (SELECT pk_tariff "
542 "WHERE name = '" << etariffname << "'), "
543 "fk_tariff_change = (SELECT pk_tariff "
545 "WHERE name = '" << enexttariff << "'), "
546 "fk_corporation = (SELECT pk_corporation "
547 "FROM tb_corporations "
548 "WHERE name = '" << ecorporation << "') "
549 "WHERE pk_user = " << uid;
551 result = PQexec(connection, query.str().c_str());
553 if (PQresultStatus(result) != PGRES_COMMAND_OK)
555 strError = PQresultErrorMessage(result);
557 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): '%s'\n", strError.c_str());
558 if (RollbackTransaction())
560 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
567 if (SaveUserServices(uid, conf.service))
569 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to save user's services'\n");
570 if (RollbackTransaction())
572 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
577 if (SaveUserData(uid, conf.userdata))
579 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to save user's data'\n");
580 if (RollbackTransaction())
582 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
587 if (SaveUserIPs(uid, conf.ips))
589 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to save user's IPs'\n");
590 if (RollbackTransaction())
592 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
597 if (CommitTransaction())
599 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to commit transaction'\n");
606 //-----------------------------------------------------------------------------
607 int POSTGRESQL_STORE::RestoreUserStat(USER_STAT * stat,
608 const std::string & login) const
610 STG_LOCKER lock(&mutex);
612 if (PQstatus(connection) != CONNECTION_OK)
614 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserStat(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
617 strError = "Connection lost";
618 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserStat(): '%s'\n", strError.c_str());
625 if (StartTransaction())
627 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserStat(): 'Failed to start transaction'\n");
631 std::string elogin = login;
633 if (EscapeString(elogin))
635 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserStat(): 'Failed to escape login'\n");
636 if (RollbackTransaction())
638 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserStat(): 'Failed to rollback transaction'\n");
644 std::ostringstream query;
645 query << "SELECT cash, free_mb, "
646 "last_activity_time, last_cash_add, "
647 "last_cash_add_time, passive_time "
649 "WHERE name = '" << elogin << "'";
651 result = PQexec(connection, query.str().c_str());
654 if (PQresultStatus(result) != PGRES_TUPLES_OK)
656 strError = PQresultErrorMessage(result);
657 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserStat(): '%s'\n", strError.c_str());
659 if (RollbackTransaction())
661 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserStat(): 'Failed to rollback transaction'\n");
666 int tuples = PQntuples(result);
670 strError = "Failed to fetch user's stat";
671 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserStat(): 'Invalid number of tuples. Wanted 1, actulally %d'\n", tuples);
673 if (RollbackTransaction())
675 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserStat(): 'Failed to rollback transaction'\n");
681 std::stringstream tuple;
682 tuple << PQgetvalue(result, 0, 0) << " ";
683 tuple << PQgetvalue(result, 0, 1) << " ";
684 stat->lastActivityTime = TS2Int(PQgetvalue(result, 0, 2));
685 tuple << PQgetvalue(result, 0, 3) << " ";
686 stat->lastCashAddTime = TS2Int(PQgetvalue(result, 0, 4));
687 tuple << PQgetvalue(result, 0, 5) << " ";
694 >> stat->passiveTime;
698 std::ostringstream query;
699 query << "SELECT dir_num, upload, download "
700 "FROM tb_stats_traffic "
701 "WHERE fk_user IN (SELECT pk_user FROM tb_users WHERE name = '" << elogin << "') AND "
702 "DATE_TRUNC('month', stats_date) = DATE_TRUNC('month', CAST('" << Int2TS(stgTime) << "' AS TIMESTAMP))";
704 result = PQexec(connection, query.str().c_str());
707 if (PQresultStatus(result) != PGRES_TUPLES_OK)
709 strError = PQresultErrorMessage(result);
710 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserStat(): '%s'\n", strError.c_str());
712 if (RollbackTransaction())
714 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserStat(): 'Failed to rollback transaction'\n");
719 tuples = PQntuples(result);
721 for (int i = 0; i < tuples; ++i)
723 std::stringstream tuple;
724 tuple << PQgetvalue(result, i, 0) << " ";
725 tuple << PQgetvalue(result, i, 1) << " ";
726 tuple << PQgetvalue(result, i, 2) << " ";
731 tuple >> stat->monthUp[dir];
732 tuple >> stat->monthDown[dir];
737 if (CommitTransaction())
739 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserStat(): 'Failed to commit transaction'\n");
746 //-----------------------------------------------------------------------------
747 int POSTGRESQL_STORE::RestoreUserConf(USER_CONF * conf,
748 const std::string & login) const
750 STG_LOCKER lock(&mutex);
752 if (PQstatus(connection) != CONNECTION_OK)
754 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserConf(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
757 strError = "Connection lost";
758 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserConf(): '%s'\n", strError.c_str());
765 if (StartTransaction())
767 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserStat(): 'Failed to start transaction'\n");
771 std::string elogin = login;
773 if (EscapeString(elogin))
775 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserStat(): 'Failed to escape login'\n");
776 if (RollbackTransaction())
778 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserStat(): 'Failed to rollback transaction'\n");
784 std::ostringstream query;
785 query << "SELECT tb_users.pk_user, tb_users.address, tb_users.always_online, "
786 "tb_users.credit, tb_users.credit_expire, tb_users.disabled, "
787 "tb_users.disabled_detail_stat, tb_users.email, tb_users.grp, "
788 "tb_users.note, tb_users.passive, tb_users.passwd, tb_users.phone, "
789 "tb_users.real_name, tf1.name, tf2.name, tb_corporations.name "
790 "FROM tb_users LEFT JOIN tb_tariffs AS tf1 "
791 "ON tf1.pk_tariff = tb_users.fk_tariff "
792 "LEFT JOIN tb_tariffs AS tf2 "
793 "ON tf2.pk_tariff = tb_users.fk_tariff_change "
794 "LEFT JOIN tb_corporations "
795 "ON tb_corporations.pk_corporation = tb_users.fk_corporation "
796 "WHERE tb_users.name = '" << elogin << "'";
798 result = PQexec(connection, query.str().c_str());
801 if (PQresultStatus(result) != PGRES_TUPLES_OK)
803 strError = PQresultErrorMessage(result);
804 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserConf(): '%s'\n", strError.c_str());
806 if (RollbackTransaction())
808 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserConf(): 'Failed to rollback transaction'\n");
813 int tuples = PQntuples(result);
817 strError = "Failed to fetch user's stat";
818 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserConf(): 'Invalid number of tuples. Wanted 1, actulally %d'\n", tuples);
820 if (RollbackTransaction())
822 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserConf(): 'Failed to rollback transaction'\n");
830 std::stringstream tuple;
831 tuple << PQgetvalue(result, 0, 0) << " "; // uid
832 conf->address = PQgetvalue(result, 0, 1); // address
833 conf->alwaysOnline = !strncmp(PQgetvalue(result, 0, 2), "t", 1);
834 tuple << PQgetvalue(result, 0, 3) << " "; // credit
835 conf->creditExpire = TS2Int(PQgetvalue(result, 0, 4)); // creditExpire
836 conf->disabled = !strncmp(PQgetvalue(result, 0, 5), "t", 1);
837 conf->disabledDetailStat = !strncmp(PQgetvalue(result, 0, 6), "t", 1);
838 conf->email = PQgetvalue(result, 0, 7); // email
839 conf->group = PQgetvalue(result, 0, 8); // group
840 conf->note = PQgetvalue(result, 0, 9); // note
841 conf->passive = !strncmp(PQgetvalue(result, 0, 10), "t", 1);
842 conf->password = PQgetvalue(result, 0, 11); // password
843 conf->phone = PQgetvalue(result, 0, 12); // phone
844 conf->realName = PQgetvalue(result, 0, 13); // realName
845 conf->tariffName = PQgetvalue(result, 0, 14); // tariffName
846 conf->nextTariff = PQgetvalue(result, 0, 15); // nextTariff
847 conf->corp = PQgetvalue(result, 0, 16); // corp
851 if (conf->tariffName == "")
852 conf->tariffName = NO_TARIFF_NAME;
853 if (conf->corp == "")
854 conf->corp = NO_CORP_NAME;
861 std::ostringstream query;
862 query << "SELECT name FROM tb_services "
863 "WHERE pk_service IN (SELECT fk_service "
864 "FROM tb_users_services "
865 "WHERE fk_user = " << uid << ")";
867 result = PQexec(connection, query.str().c_str());
870 if (PQresultStatus(result) != PGRES_TUPLES_OK)
872 strError = PQresultErrorMessage(result);
873 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserConf(): '%s'\n", strError.c_str());
875 if (RollbackTransaction())
877 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserConf(): 'Failed to rollback transaction'\n");
882 tuples = PQntuples(result);
884 for (int i = 0; i < tuples; ++i)
886 conf->service.push_back(PQgetvalue(result, i, 0));
892 std::ostringstream query;
893 query << "SELECT num, data "
894 "FROM tb_users_data "
895 "WHERE fk_user = " << uid;
897 result = PQexec(connection, query.str().c_str());
900 if (PQresultStatus(result) != PGRES_TUPLES_OK)
902 strError = PQresultErrorMessage(result);
903 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserConf(): '%s'\n", strError.c_str());
905 if (RollbackTransaction())
907 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserConf(): 'Failed to rollback transaction'\n");
912 tuples = PQntuples(result);
914 for (int i = 0; i < tuples; ++i)
917 if (str2x(PQgetvalue(result, i, 0), num))
919 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserConf(): 'Failed to convert string to int'\n");
923 if (num < USERDATA_NUM &&
926 conf->userdata[num] = PQgetvalue(result, i, 1);
934 std::ostringstream query;
935 query << "SELECT host(ip), masklen(ip) "
936 "FROM tb_allowed_ip "
937 "WHERE fk_user = " << uid;
939 result = PQexec(connection, query.str().c_str());
942 if (PQresultStatus(result) != PGRES_TUPLES_OK)
944 strError = PQresultErrorMessage(result);
945 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserConf(): '%s'\n", strError.c_str());
947 if (RollbackTransaction())
949 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserConf(): 'Failed to rollback transaction'\n");
954 tuples = PQntuples(result);
957 for (int i = 0; i < tuples; ++i)
963 ip = inet_strington(PQgetvalue(result, i, 0));
965 if (str2x(PQgetvalue(result, i, 1), mask))
967 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserConf(): 'Failed to fetch mask'\n");
979 if (CommitTransaction())
981 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserConf(): 'Failed to commit transaction'\n");
988 //-----------------------------------------------------------------------------
989 int POSTGRESQL_STORE::WriteUserChgLog(const std::string & login,
990 const std::string & admLogin,
992 const std::string & paramName,
993 const std::string & oldValue,
994 const std::string & newValue,
995 const std::string & message = "") const
997 STG_LOCKER lock(&mutex);
999 if (PQstatus(connection) != CONNECTION_OK)
1001 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserChgLog(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
1004 strError = "Connection lost";
1005 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserChgLog(): '%s'\n", strError.c_str());
1012 if (StartTransaction())
1014 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserChgLog(): 'Failed to start transaction'\n");
1018 std::string elogin(login);
1019 std::string eadminLogin(admLogin);
1020 std::string eparam(paramName);
1021 std::string eold(oldValue);
1022 std::string enew(newValue);
1023 std::string emessage(message);
1025 if (EscapeString(elogin))
1027 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserChgLog(): 'Failed to escape login'\n");
1028 if (RollbackTransaction())
1030 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserChgLog(): 'Failed to rollback transaction'\n");
1035 if (EscapeString(eadminLogin))
1037 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserChgLog(): 'Failed to escape admin's login'\n");
1038 if (RollbackTransaction())
1040 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserChgLog(): 'Failed to rollback transaction'\n");
1045 if (EscapeString(eparam))
1047 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserChgLog(): 'Failed to escape param's name'\n");
1048 if (RollbackTransaction())
1050 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserChgLog(): 'Failed to rollback transaction'\n");
1055 if (EscapeString(eold))
1057 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserChgLog(): 'Failed to escape old value'\n");
1058 if (RollbackTransaction())
1060 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserChgLog(): 'Failed to rollback transaction'\n");
1065 if (EscapeString(enew))
1067 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserChgLog(): 'Failed to escape new value'\n");
1068 if (RollbackTransaction())
1070 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserChgLog(): 'Failed to rollback transaction'\n");
1075 std::ostringstream query;
1076 query << "SELECT sp_add_param_log_entry("
1077 "'" << elogin << "', "
1078 "'" << eadminLogin << "', CAST('"
1079 << inet_ntostring(admIP) << "/32' AS INET), "
1080 "'" << eparam << "', "
1081 "CAST('" << Int2TS(stgTime) << "' AS TIMESTAMP), "
1082 "'" << eold << "', "
1083 "'" << enew << "', "
1084 "'" << emessage << "')";
1086 result = PQexec(connection, query.str().c_str());
1088 if (PQresultStatus(result) != PGRES_TUPLES_OK)
1090 strError = PQresultErrorMessage(result);
1092 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserChgLog(): '%s'\n", strError.c_str());
1093 if (RollbackTransaction())
1095 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserChgLog(): 'Failed to rollback transaction'\n");
1102 if (CommitTransaction())
1104 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserChgLog(): 'Failed to commit transaction'\n");
1111 //-----------------------------------------------------------------------------
1112 int POSTGRESQL_STORE::WriteUserConnect(const std::string & login, uint32_t ip) const
1114 STG_LOCKER lock(&mutex);
1116 if (PQstatus(connection) != CONNECTION_OK)
1118 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserConnect(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
1121 strError = "Connection lost";
1122 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserConnect(): '%s'\n", strError.c_str());
1129 if (StartTransaction())
1131 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserConnect(): 'Failed to start transaction'\n");
1135 std::string elogin(login);
1137 if (EscapeString(elogin))
1139 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserConnect(): 'Failed to escape login'\n");
1140 if (RollbackTransaction())
1142 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserConnect(): 'Failed to rollback transaction'\n");
1147 std::ostringstream query;
1150 query << "SELECT sp_add_session_log_entry("
1151 "'" << elogin << "', "
1152 "CAST('" << Int2TS(stgTime) << "' AS TIMESTAMP), "
1154 << inet_ntostring(ip) << "/32' AS INET), 0)";
1158 query << "SELECT sp_add_session_log_entry("
1159 "'" << elogin << "', "
1160 "CAST('" << Int2TS(stgTime) << "' AS TIMESTAMP), "
1162 << inet_ntostring(ip) << "/32' AS INET), 0, 0, '')";
1165 result = PQexec(connection, query.str().c_str());
1167 if (PQresultStatus(result) != PGRES_TUPLES_OK)
1169 strError = PQresultErrorMessage(result);
1171 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserConnect(): '%s'\n", strError.c_str());
1172 if (RollbackTransaction())
1174 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserConnect(): 'Failed to rollback transaction'\n");
1181 if (CommitTransaction())
1183 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserConnect(): 'Failed to commit transaction'\n");
1190 //-----------------------------------------------------------------------------
1191 int POSTGRESQL_STORE::WriteUserDisconnect(const std::string & login,
1192 const DIR_TRAFF & monthUp,
1193 const DIR_TRAFF & monthDown,
1194 const DIR_TRAFF & sessionUp,
1195 const DIR_TRAFF & sessionDown,
1198 const std::string & reason) const
1200 STG_LOCKER lock(&mutex);
1202 if (PQstatus(connection) != CONNECTION_OK)
1204 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserDisconnect(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
1207 strError = "Connection lost";
1208 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserDisconnect(): '%s'\n", strError.c_str());
1215 if (StartTransaction())
1217 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserDisconnect(): 'Failed to start transaction'\n");
1221 std::string elogin(login);
1223 if (EscapeString(elogin))
1225 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserDisconnect(): 'Failed to escape login'\n");
1226 if (RollbackTransaction())
1228 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserDisconnect(): 'Failed to rollback transaction'\n");
1233 std::string ereason(reason);
1235 if (EscapeString(ereason))
1237 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserDisconnect(): 'Failed to escape reason'\n");
1238 if (RollbackTransaction())
1240 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserDisconnect(): 'Failed to rollback transaction'\n");
1246 std::ostringstream query;
1249 // Old database version - no freeMb logging support
1250 query << "SELECT sp_add_session_log_entry("
1251 "'" << elogin << "', "
1252 "CAST('" << Int2TS(stgTime) << "' AS TIMESTAMP), "
1253 "'d', CAST('0.0.0.0/0' AS INET), "
1258 query << "SELECT sp_add_session_log_entry("
1259 "'" << elogin << "', "
1260 "CAST('" << Int2TS(stgTime) << "' AS TIMESTAMP), "
1261 "'d', CAST('0.0.0.0/0' AS INET), "
1262 << cash << ", " << freeMb << ", '" << ereason << "')";
1265 result = PQexec(connection, query.str().c_str());
1268 if (PQresultStatus(result) != PGRES_TUPLES_OK)
1270 strError = PQresultErrorMessage(result);
1272 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserDisconnect(): '%s'\n", strError.c_str());
1273 if (RollbackTransaction())
1275 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserDisconnect(): 'Failed to rollback transaction'\n");
1280 int tuples = PQntuples(result);
1284 strError = "Failed to fetch session's log ID";
1285 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserDisconnect(): 'Invalid number of tuples. Wanted 1, actulally %d'\n", tuples);
1287 if (RollbackTransaction())
1289 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserDisconnect(): 'Failed to rollback transaction'\n");
1296 if (str2x(PQgetvalue(result, 0, 0), lid))
1298 strError = "Failed to convert string to int";
1299 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserDisconnect(): '%s'\n", strError.c_str());
1301 if (RollbackTransaction())
1303 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserDisconnect(): 'Failed to rollback transaction'\n");
1310 for (int i = 0; i < DIR_NUM; ++i)
1312 std::ostringstream query;
1313 query << "INSERT INTO tb_sessions_data "
1317 "session_download, "
1323 << sessionUp[i] << ", "
1324 << sessionDown[i] << ", "
1325 << monthUp[i] << ", "
1326 << monthDown[i] << ")";
1328 result = PQexec(connection, query.str().c_str());
1330 if (PQresultStatus(result) != PGRES_COMMAND_OK)
1332 strError = PQresultErrorMessage(result);
1334 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserDisconnect(): '%s'\n", strError.c_str());
1335 if (RollbackTransaction())
1337 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserDisconnect(): 'Failed to rollback transaction'\n");
1345 if (CommitTransaction())
1347 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserDisconnect(): 'Failed to commit transaction'\n");
1354 //-----------------------------------------------------------------------------
1355 int POSTGRESQL_STORE::WriteDetailedStat(const std::map<IP_DIR_PAIR, STAT_NODE> & statTree,
1357 const std::string & login) const
1359 STG_LOCKER lock(&mutex);
1361 if (PQstatus(connection) != CONNECTION_OK)
1363 printfd(__FILE__, "POSTGRESQL_STORE::WriteDetailedStat(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
1366 strError = "Connection lost";
1367 printfd(__FILE__, "POSTGRESQL_STORE::WriteDetailedStat(): '%s'\n", strError.c_str());
1372 if (StartTransaction())
1374 printfd(__FILE__, "POSTGRESQL_STORE::WriteDetailedStat(): 'Failed to start transaction'\n");
1378 std::string elogin(login);
1380 if (EscapeString(elogin))
1382 printfd(__FILE__, "POSTGRESQL_STORE::WriteDetailedStat(): 'Failed to escape login'\n");
1383 if (RollbackTransaction())
1385 printfd(__FILE__, "POSTGRESQL_STORE::WriteDetailedStat(): 'Failed to rollback transaction'\n");
1390 std::map<IP_DIR_PAIR, STAT_NODE>::const_iterator it;
1391 time_t currTime = time(NULL);
1393 for (it = statTree.begin(); it != statTree.end(); ++it)
1395 std::ostringstream query;
1396 query << "INSERT INTO tb_detail_stats "
1397 "(till_time, from_time, fk_user, "
1398 "dir_num, ip, download, upload, cost) "
1400 "CAST('" << Int2TS(currTime) << "' AS TIMESTAMP), "
1401 "CAST('" << Int2TS(lastStat) << "' AS TIMESTAMP), "
1402 "(SELECT pk_user FROM tb_users WHERE name = '" << elogin << "'), "
1403 << it->first.dir << ", "
1404 << "CAST('" << inet_ntostring(it->first.ip) << "' AS INET), "
1405 << it->second.down << ", "
1406 << it->second.up << ", "
1407 << it->second.cash << ")";
1409 PGresult * result = PQexec(connection, query.str().c_str());
1411 if (PQresultStatus(result) != PGRES_COMMAND_OK)
1413 strError = PQresultErrorMessage(result);
1415 printfd(__FILE__, "POSTGRESQL_STORE::WriteDetailedStat(): '%s'\n", strError.c_str());
1416 if (RollbackTransaction())
1418 printfd(__FILE__, "POSTGRESQL_STORE::WriteDetailedStat(): 'Failed to rollback transaction'\n");
1426 if (CommitTransaction())
1428 printfd(__FILE__, "POSTGRESQL_STORE::WriteDetailedStat(): 'Failed to commit transaction'\n");
1435 //-----------------------------------------------------------------------------
1436 int POSTGRESQL_STORE::SaveMonthStat(const USER_STAT & stat, int month, int year, const std::string & login) const
1438 STG_LOCKER lock(&mutex);
1440 return SaveStat(stat, login, year, month);
1443 //-----------------------------------------------------------------------------
1444 int POSTGRESQL_STORE::SaveUserServices(uint32_t uid,
1445 const std::vector<std::string> & services) const
1450 std::ostringstream query;
1451 query << "DELETE FROM tb_users_services WHERE fk_user = " << uid;
1453 result = PQexec(connection, query.str().c_str());
1455 if (PQresultStatus(result) != PGRES_COMMAND_OK)
1457 strError = PQresultErrorMessage(result);
1459 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserServices(): '%s'\n", strError.c_str());
1466 std::vector<std::string>::const_iterator it;
1468 for (it = services.begin(); it != services.end(); ++it)
1470 std::string ename = *it;
1472 if (EscapeString(ename))
1474 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserServices(): 'Failed to escape service name'\n");
1478 std::ostringstream query;
1479 query << "INSERT INTO tb_users_services "
1480 "(fk_user, fk_service) "
1483 "(SELECT pk_service "
1485 "WHERE name = '" << ename << "'))";
1487 result = PQexec(connection, query.str().c_str());
1489 if (PQresultStatus(result) != PGRES_COMMAND_OK)
1491 strError = PQresultErrorMessage(result);
1493 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserServices(): '%s'\n", strError.c_str());
1503 //-----------------------------------------------------------------------------
1504 int POSTGRESQL_STORE::SaveUserIPs(uint32_t uid,
1505 const USER_IPS & ips) const
1510 std::ostringstream query;
1511 query << "DELETE FROM tb_allowed_ip WHERE fk_user = " << uid;
1513 result = PQexec(connection, query.str().c_str());
1516 if (PQresultStatus(result) != PGRES_COMMAND_OK)
1518 strError = PQresultErrorMessage(result);
1520 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserIPs(): '%s'\n", strError.c_str());
1526 for (size_t i = 0; i < ips.Count(); ++i)
1528 std::ostringstream query;
1529 query << "INSERT INTO tb_allowed_ip "
1532 "(" << uid << ", CAST('"
1533 << inet_ntostring(ips[i].ip) << "/"
1534 << static_cast<int>(ips[i].mask) << "' AS INET))";
1536 result = PQexec(connection, query.str().c_str());
1538 if (PQresultStatus(result) != PGRES_COMMAND_OK)
1540 strError = PQresultErrorMessage(result);
1542 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserIPs(): '%s'\n", strError.c_str());
1552 //-----------------------------------------------------------------------------
1553 int POSTGRESQL_STORE::SaveUserData(uint32_t uid,
1554 const std::vector<std::string> & data) const
1556 for (unsigned i = 0; i < data.size(); ++i)
1558 std::string edata = data[i];
1560 if (EscapeString(edata))
1562 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserData(): 'Failed to escape userdata field'\n");
1568 std::ostringstream query;
1569 query << "SELECT sp_set_user_data("
1571 << "CAST(" << i << " AS SMALLINT), "
1572 << "'" << edata << "')";
1574 result = PQexec(connection, query.str().c_str());
1576 if (PQresultStatus(result) != PGRES_TUPLES_OK)
1578 strError = PQresultErrorMessage(result);
1580 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserData(): '%s'\n", strError.c_str());