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 $
29 #include "postgresql_store.h"
31 #include "stg/user_conf.h"
32 #include "stg/user_stat.h"
33 #include "stg/user_ips.h"
34 #include "stg/user_traff.h"
35 #include "stg/common.h"
36 #include "stg/const.h"
37 #include "stg/locker.h"
38 #include "../../../stg_timer.h"
47 //-----------------------------------------------------------------------------
48 int POSTGRESQL_STORE::GetUsersList(std::vector<std::string> * usersList) const
50 STG_LOCKER lock(&mutex);
52 if (PQstatus(connection) != CONNECTION_OK)
54 printfd(__FILE__, "POSTGRESQL_STORE::GetUsersList(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
57 strError = "Connection lost";
58 printfd(__FILE__, "POSTGRESQL_STORE::GetUsersList(): '%s'\n", strError.c_str());
65 if (StartTransaction())
67 printfd(__FILE__, "POSTGRESQL_STORE::GetUsersList(): 'Failed to start transaction'\n");
71 result = PQexec(connection, "SELECT name FROM tb_users");
73 if (PQresultStatus(result) != PGRES_TUPLES_OK)
75 strError = PQresultErrorMessage(result);
77 printfd(__FILE__, "POSTGRESQL_STORE::GetUsersList(): '%s'\n", strError.c_str());
78 if (RollbackTransaction())
80 printfd(__FILE__, "POSTGRESQL_STORE::GetUsersList(): 'Failed to rollback transaction'\n");
85 int tuples = PQntuples(result);
87 for (int i = 0; i < tuples; ++i)
89 usersList->push_back(PQgetvalue(result, i, 0));
94 if (CommitTransaction())
96 printfd(__FILE__, "POSTGRESQL_STORE::GetUsersList(): 'Failed to commit transaction'\n");
103 //-----------------------------------------------------------------------------
104 int POSTGRESQL_STORE::AddUser(const std::string & name) const
106 STG_LOCKER lock(&mutex);
108 if (PQstatus(connection) != CONNECTION_OK)
110 printfd(__FILE__, "POSTGRESQL_STORE::AddUser(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
113 strError = "Connection lost";
114 printfd(__FILE__, "POSTGRESQL_STORE::AddUser(): '%s'\n", strError.c_str());
121 if (StartTransaction())
123 printfd(__FILE__, "POSTGRESQL_STORE::AddUser(): 'Failed to start transaction'\n");
127 std::string elogin = name;
129 if (EscapeString(elogin))
131 printfd(__FILE__, "POSTGRESQL_STORE::AddUser(): 'Failed to escape login'\n");
132 if (RollbackTransaction())
134 printfd(__FILE__, "POSTGRESQL_STORE::AddUser(): 'Failed to rollback transaction'\n");
139 std::ostringstream query;
140 query << "SELECT sp_add_user('" << elogin << "')";
142 result = PQexec(connection, query.str().c_str());
144 if (PQresultStatus(result) != PGRES_TUPLES_OK)
146 strError = PQresultErrorMessage(result);
148 printfd(__FILE__, "POSTGRESQL_STORE::AddUser(): '%s'\n", strError.c_str());
149 if (RollbackTransaction())
151 printfd(__FILE__, "POSTGRESQL_STORE::AddUser(): 'Failed to rollback transaction'\n");
158 if (CommitTransaction())
160 printfd(__FILE__, "POSTGRESQL_STORE::AddUser(): 'Failed to commit transaction'\n");
167 //-----------------------------------------------------------------------------
168 int POSTGRESQL_STORE::DelUser(const std::string & login) const
170 STG_LOCKER lock(&mutex);
172 if (PQstatus(connection) != CONNECTION_OK)
174 printfd(__FILE__, "POSTGRESQL_STORE::DelUser(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
177 strError = "Connection lost";
178 printfd(__FILE__, "POSTGRESQL_STORE::DelUser(): '%s'\n", strError.c_str());
185 if (StartTransaction())
187 printfd(__FILE__, "POSTGRESQL_STORE::DelUser(): 'Failed to start transaction'\n");
191 std::string elogin = login;
193 if (EscapeString(elogin))
195 printfd(__FILE__, "POSTGRESQL_STORE::DelUser(): 'Failed to escape login'\n");
196 if (RollbackTransaction())
198 printfd(__FILE__, "POSTGRESQL_STORE::DelUser(): 'Failed to rollback transaction'\n");
203 std::ostringstream query;
204 query << "DELETE FROM tb_users WHERE name = '" << elogin << "'";
206 result = PQexec(connection, query.str().c_str());
208 if (PQresultStatus(result) != PGRES_COMMAND_OK)
210 strError = PQresultErrorMessage(result);
212 printfd(__FILE__, "POSTGRESQL_STORE::DelUser(): '%s'\n", strError.c_str());
213 if (RollbackTransaction())
215 printfd(__FILE__, "POSTGRESQL_STORE::DelUser(): 'Failed to rollback transaction'\n");
222 if (CommitTransaction())
224 printfd(__FILE__, "POSTGRESQL_STORE::DelUser(): 'Failed to commit transaction'\n");
230 //-----------------------------------------------------------------------------
231 int POSTGRESQL_STORE::SaveUserStat(const STG::UserStat & stat,
232 const std::string & login) const
234 STG_LOCKER lock(&mutex);
236 return SaveStat(stat, login);
238 //-----------------------------------------------------------------------------
239 int POSTGRESQL_STORE::SaveStat(const STG::UserStat & stat,
240 const std::string & login,
244 if (PQstatus(connection) != CONNECTION_OK)
246 printfd(__FILE__, "POSTGRESQL_STORE::SaveStat(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
249 strError = "Connection lost";
250 printfd(__FILE__, "POSTGRESQL_STORE::SaveStat(): '%s'\n", strError.c_str());
257 if (StartTransaction())
259 printfd(__FILE__, "POSTGRESQL_STORE::SaveStat(): 'Failed to start transaction'\n");
263 std::string elogin = login;
265 if (EscapeString(elogin))
267 printfd(__FILE__, "POSTGRESQL_STORE::SaveStat(): 'Failed to escape login'\n");
268 if (RollbackTransaction())
270 printfd(__FILE__, "POSTGRESQL_STORE::SaveStat(): 'Failed to rollback transaction'\n");
275 std::ostringstream query;
276 query << "UPDATE tb_users SET "
277 "cash = " << stat.cash << ", "
278 "free_mb = " << stat.freeMb << ", "
279 "last_activity_time = CAST('" << formatTime(stat.lastActivityTime) << "' AS TIMESTAMP), "
280 "last_cash_add = " << stat.lastCashAdd << ", "
281 "last_cash_add_time = CAST('" << formatTime(stat.lastCashAddTime) << "' AS TIMESTAMP), "
282 "passive_time = " << stat.passiveTime << " "
283 "WHERE name = '" << elogin << "'";
285 result = PQexec(connection, query.str().c_str());
287 if (PQresultStatus(result) != PGRES_COMMAND_OK)
289 strError = PQresultErrorMessage(result);
291 printfd(__FILE__, "POSTGRESQL_STORE::SaveStat(): '%s'\n", strError.c_str());
292 if (RollbackTransaction())
294 printfd(__FILE__, "POSTGRESQL_STORE::SaveStat(): 'Failed to rollback transaction'\n");
303 MakeDate(date, year, month);
305 for (int dir = 0; dir < DIR_NUM; ++dir)
308 query << "SELECT sp_add_stats_traffic ("
309 "'" << elogin << "', "
310 "CAST('" << date << "' AS DATE), "
311 "CAST(" << dir << " AS SMALLINT), "
312 "CAST(" << stat.monthUp[dir] << " AS BIGINT), "
313 "CAST(" << stat.monthDown[dir] << " AS BIGINT))";
315 result = PQexec(connection, query.str().c_str());
317 if (PQresultStatus(result) != PGRES_TUPLES_OK)
319 strError = PQresultErrorMessage(result);
321 printfd(__FILE__, "POSTGRESQL_STORE::SaveStat(): '%s'\n", strError.c_str());
322 if (RollbackTransaction())
324 printfd(__FILE__, "POSTGRESQL_STORE::SaveStat(): 'Failed to rollback transaction'\n");
332 if (CommitTransaction())
334 printfd(__FILE__, "POSTGRESQL_STORE::SaveStat(): 'Failed to commit transaction'\n");
341 //-----------------------------------------------------------------------------
342 int POSTGRESQL_STORE::SaveUserConf(const STG::UserConf & conf,
343 const std::string & login) const
345 STG_LOCKER lock(&mutex);
347 if (PQstatus(connection) != CONNECTION_OK)
349 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
352 strError = "Connection lost";
353 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): '%s'\n", strError.c_str());
360 if (StartTransaction())
362 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to start transaction'\n");
366 std::string elogin = login;
368 if (EscapeString(elogin))
370 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to escape login'\n");
371 if (RollbackTransaction())
373 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
378 std::ostringstream query;
379 query << "SELECT pk_user FROM tb_users WHERE name = '" << elogin << "'";
381 result = PQexec(connection, query.str().c_str());
383 if (PQresultStatus(result) != PGRES_TUPLES_OK)
385 strError = PQresultErrorMessage(result);
387 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): '%s'\n", strError.c_str());
388 if (RollbackTransaction())
390 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
395 int tuples = PQntuples(result);
399 strError = "Failed to fetch user's ID";
400 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Invalid number of tuples. Wanted 1, actulally %d'\n", tuples);
402 if (RollbackTransaction())
404 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
412 std::stringstream tuple;
413 tuple << PQgetvalue(result, 0, 0);
420 std::string eaddress = conf.address;
421 std::string eemail = conf.email;
422 std::string egroup = conf.group;
423 std::string enote = conf.note;
424 std::string epassword = conf.password;
425 std::string ephone = conf.phone;
426 std::string erealname = conf.realName;
427 std::string etariffname = conf.tariffName;
428 std::string enexttariff = conf.nextTariff;
429 std::string ecorporation = conf.corp;
431 if (EscapeString(eaddress))
433 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to escape address'\n");
434 if (RollbackTransaction())
436 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
441 if (EscapeString(eemail))
443 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to escape email'\n");
444 if (RollbackTransaction())
446 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
451 if (EscapeString(egroup))
453 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to escape group'\n");
454 if (RollbackTransaction())
456 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
461 if (EscapeString(enote))
463 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to escape note'\n");
464 if (RollbackTransaction())
466 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
471 if (EscapeString(epassword))
473 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to escape password'\n");
474 if (RollbackTransaction())
476 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
481 if (EscapeString(ephone))
483 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to escape phone'\n");
484 if (RollbackTransaction())
486 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
491 if (EscapeString(erealname))
493 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to escape real name'\n");
494 if (RollbackTransaction())
496 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
501 if (EscapeString(etariffname))
503 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to escape tariff name'\n");
504 if (RollbackTransaction())
506 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
511 if (EscapeString(enexttariff))
513 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to escape next tariff name'\n");
514 if (RollbackTransaction())
516 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
521 if (EscapeString(ecorporation))
523 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to escape corporation name'\n");
524 if (RollbackTransaction())
526 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
532 query << "UPDATE tb_users SET "
533 "address = '" << eaddress << "', "
534 "always_online = " << (conf.alwaysOnline ? "'t'" : "'f'") << ", "
535 "credit = " << conf.credit << ", "
536 "credit_expire = CAST('" << formatTime(conf.creditExpire) << "' AS TIMESTAMP), "
537 "disabled = " << (conf.disabled ? "'t'" : "'f'") << ", "
538 "disabled_detail_stat = " << (conf.disabledDetailStat ? "'t'" : "'f'") << ", "
539 "email = '" << eemail << "', "
540 "grp = '" << egroup << "', "
541 "note = '" << enote << "', "
542 "passive = " << (conf.passive ? "'t'" : "'f'") << ", "
543 "passwd = '" << epassword << "', "
544 "phone = '" << ephone << "', "
545 "real_name = '" << erealname << "', "
546 "fk_tariff = (SELECT pk_tariff "
548 "WHERE name = '" << etariffname << "'), "
549 "fk_tariff_change = (SELECT pk_tariff "
551 "WHERE name = '" << enexttariff << "'), "
552 "fk_corporation = (SELECT pk_corporation "
553 "FROM tb_corporations "
554 "WHERE name = '" << ecorporation << "') "
555 "WHERE pk_user = " << uid;
557 result = PQexec(connection, query.str().c_str());
559 if (PQresultStatus(result) != PGRES_COMMAND_OK)
561 strError = PQresultErrorMessage(result);
563 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): '%s'\n", strError.c_str());
564 if (RollbackTransaction())
566 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
573 if (SaveUserServices(uid, conf.services))
575 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to save user's services'\n");
576 if (RollbackTransaction())
578 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
583 if (SaveUserData(uid, conf.userdata))
585 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to save user's data'\n");
586 if (RollbackTransaction())
588 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
593 if (SaveUserIPs(uid, conf.ips))
595 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to save user's IPs'\n");
596 if (RollbackTransaction())
598 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to rollback transaction'\n");
603 if (CommitTransaction())
605 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserConf(): 'Failed to commit transaction'\n");
612 //-----------------------------------------------------------------------------
613 int POSTGRESQL_STORE::RestoreUserStat(STG::UserStat * stat,
614 const std::string & login) const
616 STG_LOCKER lock(&mutex);
618 if (PQstatus(connection) != CONNECTION_OK)
620 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserStat(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
623 strError = "Connection lost";
624 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserStat(): '%s'\n", strError.c_str());
631 if (StartTransaction())
633 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserStat(): 'Failed to start transaction'\n");
637 std::string elogin = login;
639 if (EscapeString(elogin))
641 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserStat(): 'Failed to escape login'\n");
642 if (RollbackTransaction())
644 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserStat(): 'Failed to rollback transaction'\n");
650 std::ostringstream query;
651 query << "SELECT cash, free_mb, "
652 "last_activity_time, last_cash_add, "
653 "last_cash_add_time, passive_time "
655 "WHERE name = '" << elogin << "'";
657 result = PQexec(connection, query.str().c_str());
660 if (PQresultStatus(result) != PGRES_TUPLES_OK)
662 strError = PQresultErrorMessage(result);
663 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserStat(): '%s'\n", strError.c_str());
665 if (RollbackTransaction())
667 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserStat(): 'Failed to rollback transaction'\n");
672 int tuples = PQntuples(result);
676 strError = "Failed to fetch user's stat";
677 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserStat(): 'Invalid number of tuples. Wanted 1, actulally %d'\n", tuples);
679 if (RollbackTransaction())
681 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserStat(): 'Failed to rollback transaction'\n");
687 std::stringstream tuple;
688 tuple << PQgetvalue(result, 0, 0) << " ";
689 tuple << PQgetvalue(result, 0, 1) << " ";
690 stat->lastActivityTime = readTime(PQgetvalue(result, 0, 2));
691 tuple << PQgetvalue(result, 0, 3) << " ";
692 stat->lastCashAddTime = readTime(PQgetvalue(result, 0, 4));
693 tuple << PQgetvalue(result, 0, 5) << " ";
700 >> stat->passiveTime;
704 std::ostringstream query;
705 query << "SELECT dir_num, upload, download "
706 "FROM tb_stats_traffic "
707 "WHERE fk_user IN (SELECT pk_user FROM tb_users WHERE name = '" << elogin << "') AND "
708 "DATE_TRUNC('month', stats_date) = DATE_TRUNC('month', CAST('" << formatTime(stgTime) << "' AS TIMESTAMP))";
710 result = PQexec(connection, query.str().c_str());
713 if (PQresultStatus(result) != PGRES_TUPLES_OK)
715 strError = PQresultErrorMessage(result);
716 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserStat(): '%s'\n", strError.c_str());
718 if (RollbackTransaction())
720 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserStat(): 'Failed to rollback transaction'\n");
725 tuples = PQntuples(result);
727 for (int i = 0; i < tuples; ++i)
729 std::stringstream tuple;
730 tuple << PQgetvalue(result, i, 0) << " ";
731 tuple << PQgetvalue(result, i, 1) << " ";
732 tuple << PQgetvalue(result, i, 2) << " ";
737 tuple >> stat->monthUp[dir];
738 tuple >> stat->monthDown[dir];
743 if (CommitTransaction())
745 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserStat(): 'Failed to commit transaction'\n");
752 //-----------------------------------------------------------------------------
753 int POSTGRESQL_STORE::RestoreUserConf(STG::UserConf * conf,
754 const std::string & login) const
756 STG_LOCKER lock(&mutex);
758 if (PQstatus(connection) != CONNECTION_OK)
760 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserConf(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
763 strError = "Connection lost";
764 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserConf(): '%s'\n", strError.c_str());
771 if (StartTransaction())
773 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserStat(): 'Failed to start transaction'\n");
777 std::string elogin = login;
779 if (EscapeString(elogin))
781 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserStat(): 'Failed to escape login'\n");
782 if (RollbackTransaction())
784 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserStat(): 'Failed to rollback transaction'\n");
790 std::ostringstream query;
791 query << "SELECT tb_users.pk_user, tb_users.address, tb_users.always_online, "
792 "tb_users.credit, tb_users.credit_expire, tb_users.disabled, "
793 "tb_users.disabled_detail_stat, tb_users.email, tb_users.grp, "
794 "tb_users.note, tb_users.passive, tb_users.passwd, tb_users.phone, "
795 "tb_users.real_name, tf1.name, tf2.name, tb_corporations.name "
796 "FROM tb_users LEFT JOIN tb_tariffs AS tf1 "
797 "ON tf1.pk_tariff = tb_users.fk_tariff "
798 "LEFT JOIN tb_tariffs AS tf2 "
799 "ON tf2.pk_tariff = tb_users.fk_tariff_change "
800 "LEFT JOIN tb_corporations "
801 "ON tb_corporations.pk_corporation = tb_users.fk_corporation "
802 "WHERE tb_users.name = '" << elogin << "'";
804 result = PQexec(connection, query.str().c_str());
807 if (PQresultStatus(result) != PGRES_TUPLES_OK)
809 strError = PQresultErrorMessage(result);
810 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserConf(): '%s'\n", strError.c_str());
812 if (RollbackTransaction())
814 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserConf(): 'Failed to rollback transaction'\n");
819 int tuples = PQntuples(result);
823 strError = "Failed to fetch user's stat";
824 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserConf(): 'Invalid number of tuples. Wanted 1, actulally %d'\n", tuples);
826 if (RollbackTransaction())
828 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserConf(): 'Failed to rollback transaction'\n");
836 std::stringstream tuple;
837 tuple << PQgetvalue(result, 0, 0) << " "; // uid
838 conf->address = PQgetvalue(result, 0, 1); // address
839 conf->alwaysOnline = !strncmp(PQgetvalue(result, 0, 2), "t", 1);
840 tuple << PQgetvalue(result, 0, 3) << " "; // credit
841 conf->creditExpire = readTime(PQgetvalue(result, 0, 4)); // creditExpire
842 conf->disabled = !strncmp(PQgetvalue(result, 0, 5), "t", 1);
843 conf->disabledDetailStat = !strncmp(PQgetvalue(result, 0, 6), "t", 1);
844 conf->email = PQgetvalue(result, 0, 7); // email
845 conf->group = PQgetvalue(result, 0, 8); // group
846 conf->note = PQgetvalue(result, 0, 9); // note
847 conf->passive = !strncmp(PQgetvalue(result, 0, 10), "t", 1);
848 conf->password = PQgetvalue(result, 0, 11); // password
849 conf->phone = PQgetvalue(result, 0, 12); // phone
850 conf->realName = PQgetvalue(result, 0, 13); // realName
851 conf->tariffName = PQgetvalue(result, 0, 14); // tariffName
852 conf->nextTariff = PQgetvalue(result, 0, 15); // nextTariff
853 conf->corp = PQgetvalue(result, 0, 16); // corp
857 if (conf->tariffName == "")
858 conf->tariffName = NO_TARIFF_NAME;
859 if (conf->corp == "")
860 conf->corp = NO_CORP_NAME;
867 std::ostringstream query;
868 query << "SELECT name FROM tb_services "
869 "WHERE pk_service IN (SELECT fk_service "
870 "FROM tb_users_services "
871 "WHERE fk_user = " << uid << ")";
873 result = PQexec(connection, query.str().c_str());
876 if (PQresultStatus(result) != PGRES_TUPLES_OK)
878 strError = PQresultErrorMessage(result);
879 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserConf(): '%s'\n", strError.c_str());
881 if (RollbackTransaction())
883 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserConf(): 'Failed to rollback transaction'\n");
888 tuples = PQntuples(result);
890 for (int i = 0; i < tuples; ++i)
892 conf->services.push_back(PQgetvalue(result, i, 0));
898 std::ostringstream query;
899 query << "SELECT num, data "
900 "FROM tb_users_data "
901 "WHERE fk_user = " << uid;
903 result = PQexec(connection, query.str().c_str());
906 if (PQresultStatus(result) != PGRES_TUPLES_OK)
908 strError = PQresultErrorMessage(result);
909 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserConf(): '%s'\n", strError.c_str());
911 if (RollbackTransaction())
913 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserConf(): 'Failed to rollback transaction'\n");
918 tuples = PQntuples(result);
920 for (int i = 0; i < tuples; ++i)
923 if (str2x(PQgetvalue(result, i, 0), num))
925 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserConf(): 'Failed to convert string to int'\n");
929 if (num < USERDATA_NUM &&
932 conf->userdata[num] = PQgetvalue(result, i, 1);
940 std::ostringstream query;
941 query << "SELECT host(ip), masklen(ip) "
942 "FROM tb_allowed_ip "
943 "WHERE fk_user = " << uid;
945 result = PQexec(connection, query.str().c_str());
948 if (PQresultStatus(result) != PGRES_TUPLES_OK)
950 strError = PQresultErrorMessage(result);
951 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserConf(): '%s'\n", strError.c_str());
953 if (RollbackTransaction())
955 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserConf(): 'Failed to rollback transaction'\n");
960 tuples = PQntuples(result);
963 for (int i = 0; i < tuples; ++i)
967 im.ip = inet_strington(PQgetvalue(result, i, 0));
969 if (str2x(PQgetvalue(result, i, 1), im.mask))
971 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserConf(): 'Failed to fetch mask'\n");
981 if (CommitTransaction())
983 printfd(__FILE__, "POSTGRESQL_STORE::RestoreUserConf(): 'Failed to commit transaction'\n");
990 //-----------------------------------------------------------------------------
991 int POSTGRESQL_STORE::WriteUserChgLog(const std::string & login,
992 const std::string & admLogin,
994 const std::string & paramName,
995 const std::string & oldValue,
996 const std::string & newValue,
997 const std::string & message = "") const
999 STG_LOCKER lock(&mutex);
1001 if (PQstatus(connection) != CONNECTION_OK)
1003 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserChgLog(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
1006 strError = "Connection lost";
1007 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserChgLog(): '%s'\n", strError.c_str());
1014 if (StartTransaction())
1016 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserChgLog(): 'Failed to start transaction'\n");
1020 std::string elogin(login);
1021 std::string eadminLogin(admLogin);
1022 std::string eparam(paramName);
1023 std::string eold(oldValue);
1024 std::string enew(newValue);
1025 std::string emessage(message);
1027 if (EscapeString(elogin))
1029 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserChgLog(): 'Failed to escape login'\n");
1030 if (RollbackTransaction())
1032 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserChgLog(): 'Failed to rollback transaction'\n");
1037 if (EscapeString(eadminLogin))
1039 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserChgLog(): 'Failed to escape admin's login'\n");
1040 if (RollbackTransaction())
1042 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserChgLog(): 'Failed to rollback transaction'\n");
1047 if (EscapeString(eparam))
1049 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserChgLog(): 'Failed to escape param's name'\n");
1050 if (RollbackTransaction())
1052 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserChgLog(): 'Failed to rollback transaction'\n");
1057 if (EscapeString(eold))
1059 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserChgLog(): 'Failed to escape old value'\n");
1060 if (RollbackTransaction())
1062 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserChgLog(): 'Failed to rollback transaction'\n");
1067 if (EscapeString(enew))
1069 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserChgLog(): 'Failed to escape new value'\n");
1070 if (RollbackTransaction())
1072 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserChgLog(): 'Failed to rollback transaction'\n");
1077 std::ostringstream query;
1078 query << "SELECT sp_add_param_log_entry("
1079 "'" << elogin << "', "
1080 "'" << eadminLogin << "', CAST('"
1081 << inet_ntostring(admIP) << "/32' AS INET), "
1082 "'" << eparam << "', "
1083 "CAST('" << formatTime(stgTime) << "' AS TIMESTAMP), "
1084 "'" << eold << "', "
1085 "'" << enew << "', "
1086 "'" << emessage << "')";
1088 result = PQexec(connection, query.str().c_str());
1090 if (PQresultStatus(result) != PGRES_TUPLES_OK)
1092 strError = PQresultErrorMessage(result);
1094 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserChgLog(): '%s'\n", strError.c_str());
1095 if (RollbackTransaction())
1097 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserChgLog(): 'Failed to rollback transaction'\n");
1104 if (CommitTransaction())
1106 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserChgLog(): 'Failed to commit transaction'\n");
1113 //-----------------------------------------------------------------------------
1114 int POSTGRESQL_STORE::WriteUserConnect(const std::string & login, uint32_t ip) const
1116 STG_LOCKER lock(&mutex);
1118 if (PQstatus(connection) != CONNECTION_OK)
1120 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserConnect(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
1123 strError = "Connection lost";
1124 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserConnect(): '%s'\n", strError.c_str());
1131 if (StartTransaction())
1133 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserConnect(): 'Failed to start transaction'\n");
1137 std::string elogin(login);
1139 if (EscapeString(elogin))
1141 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserConnect(): 'Failed to escape login'\n");
1142 if (RollbackTransaction())
1144 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserConnect(): 'Failed to rollback transaction'\n");
1149 std::ostringstream query;
1152 query << "SELECT sp_add_session_log_entry("
1153 "'" << elogin << "', "
1154 "CAST('" << formatTime(stgTime) << "' AS TIMESTAMP), "
1156 << inet_ntostring(ip) << "/32' AS INET), 0)";
1160 query << "SELECT sp_add_session_log_entry("
1161 "'" << elogin << "', "
1162 "CAST('" << formatTime(stgTime) << "' AS TIMESTAMP), "
1164 << inet_ntostring(ip) << "/32' AS INET), 0, 0, '')";
1167 result = PQexec(connection, query.str().c_str());
1169 if (PQresultStatus(result) != PGRES_TUPLES_OK)
1171 strError = PQresultErrorMessage(result);
1173 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserConnect(): '%s'\n", strError.c_str());
1174 if (RollbackTransaction())
1176 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserConnect(): 'Failed to rollback transaction'\n");
1183 if (CommitTransaction())
1185 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserConnect(): 'Failed to commit transaction'\n");
1192 //-----------------------------------------------------------------------------
1193 int POSTGRESQL_STORE::WriteUserDisconnect(const std::string & login,
1194 const STG::DirTraff & monthUp,
1195 const STG::DirTraff & monthDown,
1196 const STG::DirTraff & sessionUp,
1197 const STG::DirTraff & sessionDown,
1200 const std::string & reason) const
1202 STG_LOCKER lock(&mutex);
1204 if (PQstatus(connection) != CONNECTION_OK)
1206 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserDisconnect(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
1209 strError = "Connection lost";
1210 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserDisconnect(): '%s'\n", strError.c_str());
1217 if (StartTransaction())
1219 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserDisconnect(): 'Failed to start transaction'\n");
1223 std::string elogin(login);
1225 if (EscapeString(elogin))
1227 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserDisconnect(): 'Failed to escape login'\n");
1228 if (RollbackTransaction())
1230 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserDisconnect(): 'Failed to rollback transaction'\n");
1235 std::string ereason(reason);
1237 if (EscapeString(ereason))
1239 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserDisconnect(): 'Failed to escape reason'\n");
1240 if (RollbackTransaction())
1242 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserDisconnect(): 'Failed to rollback transaction'\n");
1248 std::ostringstream query;
1251 // Old database version - no freeMb logging support
1252 query << "SELECT sp_add_session_log_entry("
1253 "'" << elogin << "', "
1254 "CAST('" << formatTime(stgTime) << "' AS TIMESTAMP), "
1255 "'d', CAST('0.0.0.0/0' AS INET), "
1260 query << "SELECT sp_add_session_log_entry("
1261 "'" << elogin << "', "
1262 "CAST('" << formatTime(stgTime) << "' AS TIMESTAMP), "
1263 "'d', CAST('0.0.0.0/0' AS INET), "
1264 << cash << ", " << freeMb << ", '" << ereason << "')";
1267 result = PQexec(connection, query.str().c_str());
1270 if (PQresultStatus(result) != PGRES_TUPLES_OK)
1272 strError = PQresultErrorMessage(result);
1274 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserDisconnect(): '%s'\n", strError.c_str());
1275 if (RollbackTransaction())
1277 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserDisconnect(): 'Failed to rollback transaction'\n");
1282 int tuples = PQntuples(result);
1286 strError = "Failed to fetch session's log ID";
1287 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserDisconnect(): 'Invalid number of tuples. Wanted 1, actulally %d'\n", tuples);
1289 if (RollbackTransaction())
1291 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserDisconnect(): 'Failed to rollback transaction'\n");
1298 if (str2x(PQgetvalue(result, 0, 0), lid))
1300 strError = "Failed to convert string to int";
1301 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserDisconnect(): '%s'\n", strError.c_str());
1303 if (RollbackTransaction())
1305 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserDisconnect(): 'Failed to rollback transaction'\n");
1312 for (int i = 0; i < DIR_NUM; ++i)
1314 std::ostringstream query;
1315 query << "INSERT INTO tb_sessions_data "
1319 "session_download, "
1325 << sessionUp[i] << ", "
1326 << sessionDown[i] << ", "
1327 << monthUp[i] << ", "
1328 << monthDown[i] << ")";
1330 result = PQexec(connection, query.str().c_str());
1332 if (PQresultStatus(result) != PGRES_COMMAND_OK)
1334 strError = PQresultErrorMessage(result);
1336 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserDisconnect(): '%s'\n", strError.c_str());
1337 if (RollbackTransaction())
1339 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserDisconnect(): 'Failed to rollback transaction'\n");
1347 if (CommitTransaction())
1349 printfd(__FILE__, "POSTGRESQL_STORE::WriteUserDisconnect(): 'Failed to commit transaction'\n");
1356 //-----------------------------------------------------------------------------
1357 int POSTGRESQL_STORE::WriteDetailedStat(const STG::TraffStat & statTree,
1359 const std::string & login) const
1361 STG_LOCKER lock(&mutex);
1363 if (PQstatus(connection) != CONNECTION_OK)
1365 printfd(__FILE__, "POSTGRESQL_STORE::WriteDetailedStat(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
1368 strError = "Connection lost";
1369 printfd(__FILE__, "POSTGRESQL_STORE::WriteDetailedStat(): '%s'\n", strError.c_str());
1374 if (StartTransaction())
1376 printfd(__FILE__, "POSTGRESQL_STORE::WriteDetailedStat(): 'Failed to start transaction'\n");
1380 std::string elogin(login);
1382 if (EscapeString(elogin))
1384 printfd(__FILE__, "POSTGRESQL_STORE::WriteDetailedStat(): 'Failed to escape login'\n");
1385 if (RollbackTransaction())
1387 printfd(__FILE__, "POSTGRESQL_STORE::WriteDetailedStat(): 'Failed to rollback transaction'\n");
1392 STG::TraffStat::const_iterator it;
1393 time_t currTime = time(NULL);
1395 for (it = statTree.begin(); it != statTree.end(); ++it)
1397 std::ostringstream query;
1398 query << "INSERT INTO tb_detail_stats "
1399 "(till_time, from_time, fk_user, "
1400 "dir_num, ip, download, upload, cost) "
1402 "CAST('" << formatTime(currTime) << "' AS TIMESTAMP), "
1403 "CAST('" << formatTime(lastStat) << "' AS TIMESTAMP), "
1404 "(SELECT pk_user FROM tb_users WHERE name = '" << elogin << "'), "
1405 << it->first.dir << ", "
1406 << "CAST('" << inet_ntostring(it->first.ip) << "' AS INET), "
1407 << it->second.down << ", "
1408 << it->second.up << ", "
1409 << it->second.cash << ")";
1411 PGresult * result = PQexec(connection, query.str().c_str());
1413 if (PQresultStatus(result) != PGRES_COMMAND_OK)
1415 strError = PQresultErrorMessage(result);
1417 printfd(__FILE__, "POSTGRESQL_STORE::WriteDetailedStat(): '%s'\n", strError.c_str());
1418 if (RollbackTransaction())
1420 printfd(__FILE__, "POSTGRESQL_STORE::WriteDetailedStat(): 'Failed to rollback transaction'\n");
1428 if (CommitTransaction())
1430 printfd(__FILE__, "POSTGRESQL_STORE::WriteDetailedStat(): 'Failed to commit transaction'\n");
1437 //-----------------------------------------------------------------------------
1438 int POSTGRESQL_STORE::SaveMonthStat(const STG::UserStat & stat, int month, int year, const std::string & login) const
1440 STG_LOCKER lock(&mutex);
1442 return SaveStat(stat, login, year, month);
1445 //-----------------------------------------------------------------------------
1446 int POSTGRESQL_STORE::SaveUserServices(uint32_t uid,
1447 const std::vector<std::string> & services) const
1452 std::ostringstream query;
1453 query << "DELETE FROM tb_users_services WHERE fk_user = " << uid;
1455 result = PQexec(connection, query.str().c_str());
1457 if (PQresultStatus(result) != PGRES_COMMAND_OK)
1459 strError = PQresultErrorMessage(result);
1461 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserServices(): '%s'\n", strError.c_str());
1468 std::vector<std::string>::const_iterator it;
1470 for (it = services.begin(); it != services.end(); ++it)
1472 std::string ename = *it;
1474 if (EscapeString(ename))
1476 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserServices(): 'Failed to escape service name'\n");
1480 std::ostringstream query;
1481 query << "INSERT INTO tb_users_services "
1482 "(fk_user, fk_service) "
1485 "(SELECT pk_service "
1487 "WHERE name = '" << ename << "'))";
1489 result = PQexec(connection, query.str().c_str());
1491 if (PQresultStatus(result) != PGRES_COMMAND_OK)
1493 strError = PQresultErrorMessage(result);
1495 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserServices(): '%s'\n", strError.c_str());
1505 //-----------------------------------------------------------------------------
1506 int POSTGRESQL_STORE::SaveUserIPs(uint32_t uid,
1507 const STG::UserIPs & ips) const
1512 std::ostringstream query;
1513 query << "DELETE FROM tb_allowed_ip WHERE fk_user = " << uid;
1515 result = PQexec(connection, query.str().c_str());
1518 if (PQresultStatus(result) != PGRES_COMMAND_OK)
1520 strError = PQresultErrorMessage(result);
1522 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserIPs(): '%s'\n", strError.c_str());
1528 for (size_t i = 0; i < ips.count(); ++i)
1530 std::ostringstream query;
1531 query << "INSERT INTO tb_allowed_ip "
1534 "(" << uid << ", CAST('"
1535 << inet_ntostring(ips[i].ip) << "/"
1536 << static_cast<int>(ips[i].mask) << "' AS INET))";
1538 result = PQexec(connection, query.str().c_str());
1540 if (PQresultStatus(result) != PGRES_COMMAND_OK)
1542 strError = PQresultErrorMessage(result);
1544 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserIPs(): '%s'\n", strError.c_str());
1554 //-----------------------------------------------------------------------------
1555 int POSTGRESQL_STORE::SaveUserData(uint32_t uid,
1556 const std::vector<std::string> & data) const
1558 for (unsigned i = 0; i < data.size(); ++i)
1560 std::string edata = data[i];
1562 if (EscapeString(edata))
1564 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserData(): 'Failed to escape userdata field'\n");
1570 std::ostringstream query;
1571 query << "SELECT sp_set_user_data("
1573 << "CAST(" << i << " AS SMALLINT), "
1574 << "'" << edata << "')";
1576 result = PQexec(connection, query.str().c_str());
1578 if (PQresultStatus(result) != PGRES_TUPLES_OK)
1580 strError = PQresultErrorMessage(result);
1582 printfd(__FILE__, "POSTGRESQL_STORE::SaveUserData(): '%s'\n", strError.c_str());