swd::blacklist_filters swd::database::get_blacklist_filters() {
swd::log::i()->send(swd::notice, "Get blacklist filters from db");
ensure_connection();
boost::unique_lock<boost::mutex> scoped_lock(dbi_mutex_);
dbi_result res = dbi_conn_query(conn_, "SELECT id, impact, rule FROM blacklist_filters");
if (!res) {
throw swd::exceptions::database_exception("Can't execute blacklist_filters query");
}
swd::blacklist_filters filters;
while (dbi_result_next_row(res)) {
swd::blacklist_filter_ptr filter(new swd::blacklist_filter());
filter->set_id(dbi_result_get_uint(res, "id"));
filter->set_impact(dbi_result_get_uint(res, "impact"));
filter->set_regex(dbi_result_get_string(res, "rule"));
filters.push_back(filter);
}
dbi_result_free(res);
return filters;
}
const char *dbi_result_get_string_default(dbi_result Result, const char *fieldname, const char *def)
{
const char *ret = dbi_result_get_string(Result, fieldname);
if (ret) return ret;
return def;
}
int rpserver_db_pair_lookup(dbi_conn *db, const char *localid, char *buf, size_t maxlen)
{
dbi_result *result;
int len = 0;
result = dbi_conn_queryf(db,
"SELECT hname FROM "DB_TABLE_DEVICES
" WHERE localid='%s' AND name IS NULL "
,localid);
if(result){
if(dbi_result_next_row(result)) {
len += snprintf(buf+len, maxlen-len, "%s",
dbi_result_get_string(result, "hname"));
}
} else {
const char *err;
dbi_conn_error(db, &err);
LOG(LOG_INFO, "%s():%d - dbi error querying database (%s)\n",
__FUNCTION__, __LINE__, err);
return -1;
}
dbi_result_free(result);
return len;
}
void refresh_database(dbi_conn conn)
{
dbi_result result;
char qry[1024];
sprintf(qry, "SELECT pr_pop3_def.id, pr_pop3_def.domid, pr_pop3_def.tblid, pr_realm.name, "
" pr_pop3_def.ipaddress, pr_pop3_def.username, "
" pr_pop3_def.password, "
" pr_pop3_def.yellow, pr_pop3_def.red "
"FROM pr_pop3_def, pr_realm "
"WHERE pr_pop3_def.id > 1 and pr_pop3_def.disable <> 'yes'"
" and pr_pop3_def.pgroup = '%d' and pr_realm.id = pr_pop3_def.domid",
(unsigned)OPT_VALUE_GROUPID);
result = db_query(conn, 1, qry);
if (!result) {
return;
}
while (dbi_result_next_row(result)) {
int id;
struct probedef *probe;
id = dbi_result_get_uint(result, "id");
probe = g_hash_table_lookup(cache, &id);
if (!probe) {
probe = g_malloc0(sizeof(struct probedef));
if (dbi_result_get_uint(result, "domid") > 1) {
probe->probeid = dbi_result_get_uint(result, "tblid");
probe->realm=strdup(dbi_result_get_string(result, "name"));
} else {
probe->probeid = probe->id;
}
g_hash_table_insert(cache, guintdup(id), probe);
}
if (probe->ipaddress) g_free(probe->ipaddress);
probe->ipaddress = dbi_result_get_string_copy(result, "ipaddress");
if (probe->username) g_free(probe->username);
probe->username = dbi_result_get_string_copy(result, "username");
if (probe->password) g_free(probe->password);
probe->password = dbi_result_get_string_copy(result, "password");
probe->yellow = dbi_result_get_float(result, "yellow");
probe->red = dbi_result_get_float(result, "red");
if (probe->msg) g_free(probe->msg);
probe->msg = NULL;
probe->seen = 1;
}
if (dbi_conn_error_flag(conn)) {
const char *errmsg;
dbi_conn_error(conn, &errmsg);
LOG(LOG_ERR, "%s", errmsg);
g_hash_table_foreach(cache, reset_seen, NULL);
} else {
g_hash_table_foreach_remove(cache, return_seen, NULL);
}
dbi_result_free(result);
}
swd::profile_ptr swd::database::get_profile(const std::string& server_ip,
const int& profile_id) {
std::stringstream log_message;
log_message << "Get profile from db -> server_ip: " << server_ip
<< "; profile_id: " << profile_id;
swd::log::i()->send(swd::notice, log_message.str());
/* Test the database connection status. Tries to reconnect if disconnected. */
ensure_connection();
/* Mutex to avoid race conditions. */
boost::unique_lock<boost::mutex> scoped_lock(dbi_mutex_);
/**
* First we escape server_ip. It comes from a trusted source, but better safe
* than sorry. This does not work with std::string though.
*/
char *server_ip_esc = strdup(server_ip.c_str());
dbi_conn_quote_string(conn_, &server_ip_esc);
/* Insert the ip and execute the query. */
dbi_result res = dbi_conn_queryf(conn_, "SELECT id, hmac_key, mode, "
"whitelist_enabled, blacklist_enabled, integrity_enabled, flooding_enabled, "
"blacklist_threshold, cache_outdated FROM profiles WHERE %s LIKE "
"prepare_wildcard(server_ip) AND id = %i", server_ip_esc, profile_id);
/* Don't forget to free server_ip_esc to avoid a memory leak. */
free(server_ip_esc);
if (!res) {
throw swd::exceptions::database_exception("Can't execute profile query");
}
if (dbi_result_get_numrows(res) != 1) {
throw swd::exceptions::database_exception("Can't get profile");
}
if (!dbi_result_next_row(res)) {
throw swd::exceptions::database_exception("No profile?");
}
swd::profile_ptr profile(new swd::profile());
profile->set_server_ip(server_ip),
profile->set_id(dbi_result_get_uint(res, "id"));
profile->set_mode(dbi_result_get_uint(res, "mode"));
profile->set_whitelist_enabled(dbi_result_get_uint(res, "whitelist_enabled") == 1);
profile->set_blacklist_enabled(dbi_result_get_uint(res, "blacklist_enabled") == 1);
profile->set_integrity_enabled(dbi_result_get_uint(res, "integrity_enabled") == 1);
profile->set_flooding_enabled(dbi_result_get_uint(res, "flooding_enabled") == 1);
profile->set_key(dbi_result_get_string(res, "hmac_key"));
profile->set_blacklist_threshold(dbi_result_get_uint(res, "blacklist_threshold"));
profile->set_cache_outdated(dbi_result_get_uint(res, "cache_outdated") == 1);
dbi_result_free(res);
return profile;
}
swd::whitelist_rules swd::database::get_whitelist_rules(const int& profile_id,
const std::string& caller, const std::string& path) {
swd::log::i()->send(swd::notice, "Get whitelist rules from db");
ensure_connection();
boost::unique_lock<boost::mutex> scoped_lock(dbi_mutex_);
char *caller_esc = strdup(caller.c_str());
dbi_conn_quote_string(conn_, &caller_esc);
char *path_esc = strdup(path.c_str());
dbi_conn_quote_string(conn_, &path_esc);
/**
* Remove LIKE single character wildcard, because it could result easily in security
* problems if a user forgets to escape an underscore. And instead of a percentage sign
* it is nicer to use an asterisk, because it is more common.
*/
dbi_result res = dbi_conn_queryf(conn_, "SELECT r.id, r.path, f.id as filter_id, "
"f.rule, f.impact, r.min_length, r.max_length FROM whitelist_rules AS r, "
"whitelist_filters AS f WHERE r.filter_id = f.id AND r.profile_id = %i AND %s LIKE "
"prepare_wildcard(r.caller) AND %s LIKE prepare_wildcard(r.path) AND r.status = %i",
profile_id, caller_esc, path_esc, STATUS_ACTIVATED);
free(caller_esc);
free(path_esc);
if (!res) {
throw swd::exceptions::database_exception("Can't execute whitelist_rules query");
}
swd::whitelist_rules rules;
while (dbi_result_next_row(res)) {
swd::whitelist_filter_ptr filter(new swd::whitelist_filter());
filter->set_id(dbi_result_get_uint(res, "filter_id"));
filter->set_regex(dbi_result_get_string(res, "rule"));
swd::whitelist_rule_ptr rule(new swd::whitelist_rule());
rule->set_id(dbi_result_get_uint(res, "id"));
rule->set_filter(filter);
rule->set_min_length(dbi_result_get_uint(res, "min_length"));
rule->set_max_length(dbi_result_get_uint(res, "max_length"));
rules.push_back(rule);
}
dbi_result_free(res);
return rules;
}
int find_expired_probes(struct dbspec *dbspec)
{
xmlDocPtr doc;
xmlNodePtr probe;
dbi_result result;
dbi_conn conn;
int count = 0;
char buf[10];
sprintf(buf, "%d", dbspec->port);
conn = open_database(OPT_ARG(DBTYPE), dbspec->host, buf, dbspec->db,
dbspec->user, dbspec->password);
if (!conn) return 0;
doc = UpwatchXmlDoc("result", NULL);
// find all expired probes, but skip those for which processing
// has been stopped for some reason
result = db_query(conn, 0, "select probe.name, pr_status.probe, "
" pr_status.server, pr_status.color, "
" pr_status.expires "
"from pr_status, probe "
"where probe.id = pr_status.class and color <> 400 "
" and expires < UNIX_TIMESTAMP()-30 "
" and expires < probe.lastseen"
" and probe.expiry = 'yes'");
if (!result) goto errexit;
while (dbi_result_next_row(result)) {
char buffer[256];
time_t now = time(NULL);
probe = xmlNewChild(xmlDocGetRootElement(doc), NULL, dbi_result_get_string(result, "name"), NULL);
xmlSetProp(probe, "realm", dbspec->realm);
xmlSetProp(probe, "id", dbi_result_get_string(result, "probe"));
xmlSetProp(probe, "server", dbi_result_get_string(result, "server"));
sprintf(buffer, "%u", (int) now); xmlSetProp(probe, "date", buffer);
xmlSetProp(probe, "expires", dbi_result_get_string(result, "expires"));
xmlNewChild(probe, NULL, "color", "400"); // PURPLE
xmlNewChild(probe, NULL, "prevcolor", dbi_result_get_string(result, "color"));
LOG(LOG_INFO, "%s: purpled %s %d", dbspec->realm, dbi_result_get_string(result, "name"),
dbi_result_get_uint(result, "probe"));
count++;
}
if (count) {
xmlSetDocCompressMode(doc, OPT_VALUE_COMPRESS);
spool_result(OPT_ARG(SPOOLDIR), OPT_ARG(OUTPUT), doc, NULL);
LOG(LOG_INFO, "%s: purpled %u probes", dbspec->realm, count);
}
errexit:
if (result) dbi_result_free(result);
if (conn) close_database(conn);
if (doc) xmlFreeDoc(doc);
return count;
}
swd::integrity_rules swd::database::get_integrity_rules(const int& profile_id,
const std::string& caller) {
swd::log::i()->send(swd::notice, "Get integrity rules from db");
ensure_connection();
boost::unique_lock<boost::mutex> scoped_lock(dbi_mutex_);
char *caller_esc = strdup(caller.c_str());
dbi_conn_quote_string(conn_, &caller_esc);
dbi_result res = dbi_conn_queryf(conn_, "SELECT r.id, r.algorithm, r.digest FROM "
"integrity_rules AS r WHERE r.profile_id = %i AND %s LIKE prepare_wildcard(r.caller) "
"AND r.status = %i", profile_id, caller_esc, STATUS_ACTIVATED);
free(caller_esc);
if (!res) {
throw swd::exceptions::database_exception("Can't execute whitelist_rules query");
}
swd::integrity_rules rules;
while (dbi_result_next_row(res)) {
swd::integrity_rule_ptr rule(new swd::integrity_rule());
rule->set_id(dbi_result_get_uint(res, "id"));
rule->set_algorithm(dbi_result_get_string(res, "algorithm"));
rule->set_digest(dbi_result_get_string(res, "digest"));
rules.push_back(rule);
}
dbi_result_free(res);
return rules;
}
//*******************************************************************
// SEE IF THE USER HAS BEEN NOTIFIED FOR THIS COLOR
//*******************************************************************
void get_previous_pr_hist(trx *t)
{
dbi_result result;
t->res->changed = 0;
strcpy(t->res->notified, "yes"); // if we cannot find pr_hist record, assume the user has already been warned
result = db_query(t->probe->db, 0,
"select id, stattime, notified, prv_color "
"from pr_hist "
"where probe = '%u' and class = '%u' and stattime <= '%u' "
"order by stattime desc limit 1",
t->def->probeid, t->probe->class, t->res->stattime);
if (!result) return;
if (dbi_result_next_row(result)) {
t->res->prevhistid = dbi_result_get_longlong(t->probe->db, "id");
t->res->changed = dbi_result_get_uint(t->probe->db, "stattime");
strcpy(t->res->notified, dbi_result_get_string(t->probe->db, "notified"));
t->res->prevhistcolor = dbi_result_get_uint(t->probe->db, "prv_color");
}
dbi_result_free(result);
return;
}
//************************************************************************
// read pr_status file for expired probes. Construct fake results
// for those.
//***********************************************************************
int run(void)
{
dbi_result result;
dbi_conn conn;
int count = 0;
conn = open_database(OPT_ARG(DBTYPE), OPT_ARG(DBHOST), OPT_ARG(DBPORT), OPT_ARG(DBNAME),
OPT_ARG(DBUSER), OPT_ARG(DBPASSWD));
if (!conn) return 0;
LOG(LOG_INFO, "processing ..");
uw_setproctitle("reading info from database");
result = db_query(conn, 0, "select pr_realm.id, pr_realm.name, pr_realm.host, "
" pr_realm.port, pr_realm.db, pr_realm.user, "
" pr_realm.password "
"from pr_realm "
"where pr_realm.id > 1");
if (result) {
while (dbi_result_next_row(result)) {
struct dbspec db;
db.domid = dbi_result_get_uint(result, "id");
db.realm = dbi_result_get_string(result, "name");
db.host = dbi_result_get_string(result, "host");
db.port = dbi_result_get_uint(result, "port");
db.db = dbi_result_get_string(result, "db");
db.user = dbi_result_get_string(result, "user");
db.password = dbi_result_get_string(result, "password");
uw_setproctitle("checking %s", dbi_result_get_string(result, "name"));
LOG(LOG_DEBUG, "checking %s", dbi_result_get_string(result, "name"));
count += find_expired_probes(&db);
}
dbi_result_free(result);
}
close_database(conn);
LOG(LOG_INFO, "sleeping");
return count;
}
/**
* Go through the database, read and add to the server all
* the server permissions stored.
*
* @param c the configuration of the db
* @param s the server
*/
int db_create_sv_privileges(struct config *c, struct server *s)
{
char *q = "SELECT * FROM server_privileges WHERE server_id = %i;";
dbi_result res;
const char *group;
int g;
struct server_privileges *sp = s->privileges;
logger(LOG_INFO, "Loading server privileges.");
res = dbi_conn_queryf(c->conn, q, s->id);
if (res) {
while (dbi_result_next_row(res)) {
/* Get the id of the group from the string */
group = dbi_result_get_string(res, "user_group");
if (strcmp(group, "server_admin") == 0) {
g = PRIV_SERVER_ADMIN;
} else if (strcmp(group, "channel_admin") == 0) {
g = PRIV_CHANNEL_ADMIN;
} else if (strcmp(group, "operator") == 0) {
g = PRIV_OPERATOR;
} else if (strcmp(group, "voice") == 0) {
g = PRIV_VOICE;
} else if (strcmp(group, "registered") == 0) {
g = PRIV_REGISTERED;
} else if (strcmp(group, "anonymous") == 0) {
g = PRIV_ANONYMOUS;
} else {
logger(LOG_ERR, "server_privileges.user_group = %s, \
expected : server_admin, channel_admin, \
operator, voice, registered, anonymous.",
group);
continue;
}
logger(LOG_DBG, "GROUP : %i", g);
/* Copy all privileges to the server... */
sp->priv[g][SP_ADM_DEL_SERVER] = dbi_result_get_uint(res, "adm_del_server");
sp->priv[g][SP_ADM_ADD_SERVER] = dbi_result_get_uint(res, "adm_add_server");
sp->priv[g][SP_ADM_LIST_SERVERS] = dbi_result_get_uint(res, "adm_list_servers");
sp->priv[g][SP_ADM_SET_PERMISSIONS] = dbi_result_get_uint(res, "adm_set_permissions");
sp->priv[g][SP_ADM_CHANGE_USER_PASS] = dbi_result_get_uint(res, "adm_change_user_pass");
sp->priv[g][SP_ADM_CHANGE_OWN_PASS] = dbi_result_get_uint(res, "adm_change_own_pass");
sp->priv[g][SP_ADM_LIST_REGISTRATIONS] = dbi_result_get_uint(res, "adm_list_registrations");
sp->priv[g][SP_ADM_REGISTER_PLAYER] = dbi_result_get_uint(res, "adm_register_player");
sp->priv[g][SP_ADM_CHANGE_SERVER_CODECS] = dbi_result_get_uint(res, "adm_change_server_codecs");
sp->priv[g][SP_ADM_CHANGE_SERVER_TYPE] = dbi_result_get_uint(res, "adm_change_server_type");
sp->priv[g][SP_ADM_CHANGE_SERVER_PASS] = dbi_result_get_uint(res, "adm_change_server_pass");
sp->priv[g][SP_ADM_CHANGE_SERVER_WELCOME] = dbi_result_get_uint(res, "adm_change_server_welcome");
sp->priv[g][SP_ADM_CHANGE_SERVER_MAXUSERS] = dbi_result_get_uint(res, "adm_change_server_maxusers");
sp->priv[g][SP_ADM_CHANGE_SERVER_NAME] = dbi_result_get_uint(res, "adm_change_server_name");
sp->priv[g][SP_ADM_CHANGE_WEBPOST_URL] = dbi_result_get_uint(res, "adm_change_webpost_url");
sp->priv[g][SP_ADM_CHANGE_SERVER_PORT] = dbi_result_get_uint(res, "adm_change_server_port");
sp->priv[g][SP_ADM_START_SERVER] = dbi_result_get_uint(res, "adm_start_server");
sp->priv[g][SP_ADM_STOP_SERVER] = dbi_result_get_uint(res, "adm_stop_server");
sp->priv[g][SP_ADM_MOVE_PLAYER] = dbi_result_get_uint(res, "adm_move_player");
sp->priv[g][SP_ADM_BAN_IP] = dbi_result_get_uint(res, "adm_ban_ip");
sp->priv[g][SP_CHA_DELETE] = dbi_result_get_uint(res, "cha_delete");
sp->priv[g][SP_CHA_CREATE_MODERATED] = dbi_result_get_uint(res, "cha_create_moderated");
sp->priv[g][SP_CHA_CREATE_SUBCHANNELED] = dbi_result_get_uint(res, "cha_create_subchanneled");
sp->priv[g][SP_CHA_CREATE_DEFAULT] = dbi_result_get_uint(res, "cha_create_default");
sp->priv[g][SP_CHA_CREATE_UNREGISTERED] = dbi_result_get_uint(res, "cha_create_unregistered");
sp->priv[g][SP_CHA_CREATE_REGISTERED] = dbi_result_get_uint(res, "cha_create_registered");
sp->priv[g][SP_CHA_JOIN_REGISTERED] = dbi_result_get_uint(res, "cha_join_registered");
sp->priv[g][SP_CHA_JOIN_WO_PASS] = dbi_result_get_uint(res, "cha_join_wo_pass");
sp->priv[g][SP_CHA_CHANGE_CODEC] = dbi_result_get_uint(res, "cha_change_codec");
sp->priv[g][SP_CHA_CHANGE_MAXUSERS] = dbi_result_get_uint(res, "cha_change_maxusers");
sp->priv[g][SP_CHA_CHANGE_ORDER] = dbi_result_get_uint(res, "cha_change_order");
sp->priv[g][SP_CHA_CHANGE_DESC] = dbi_result_get_uint(res, "cha_change_desc");
sp->priv[g][SP_CHA_CHANGE_TOPIC] = dbi_result_get_uint(res, "cha_change_topic");
sp->priv[g][SP_CHA_CHANGE_PASS] = dbi_result_get_uint(res, "cha_change_pass");
sp->priv[g][SP_CHA_CHANGE_NAME] = dbi_result_get_uint(res, "cha_change_name");
sp->priv[g][SP_PL_GRANT_ALLOWREG] = dbi_result_get_uint(res, "pl_grant_allowreg");
sp->priv[g][SP_PL_GRANT_VOICE] = dbi_result_get_uint(res, "pl_grant_voice");
sp->priv[g][SP_PL_GRANT_AUTOVOICE] = dbi_result_get_uint(res, "pl_grant_autovoice");
sp->priv[g][SP_PL_GRANT_OP] = dbi_result_get_uint(res, "pl_grant_op");
sp->priv[g][SP_PL_GRANT_AUTOOP] = dbi_result_get_uint(res, "pl_grant_autoop");
sp->priv[g][SP_PL_GRANT_CA] = dbi_result_get_uint(res, "pl_grant_ca");
sp->priv[g][SP_PL_GRANT_SA] = dbi_result_get_uint(res, "pl_grant_sa");
sp->priv[g][SP_PL_REGISTER_PLAYER] = dbi_result_get_uint(res, "pl_register_player");
sp->priv[g][SP_PL_REVOKE_ALLOWREG] = dbi_result_get_uint(res, "pl_revoke_allowreg");
sp->priv[g][SP_PL_REVOKE_VOICE] = dbi_result_get_uint(res, "pl_revoke_voice");
sp->priv[g][SP_PL_REVOKE_AUTOVOICE] = dbi_result_get_uint(res, "pl_revoke_autovoice");
sp->priv[g][SP_PL_REVOKE_OP] = dbi_result_get_uint(res, "pl_revoke_op");
sp->priv[g][SP_PL_REVOKE_AUTOOP] = dbi_result_get_uint(res, "pl_revoke_autoop");
sp->priv[g][SP_PL_REVOKE_CA] = dbi_result_get_uint(res, "pl_revoke_ca");
sp->priv[g][SP_PL_REVOKE_SA] = dbi_result_get_uint(res, "pl_revoke_sa");
sp->priv[g][SP_PL_ALLOW_SELF_REG] = dbi_result_get_uint(res, "pl_allow_self_reg");
sp->priv[g][SP_PL_DEL_REGISTRATION] = dbi_result_get_uint(res, "pl_del_registration");
sp->priv[g][SP_OTHER_CH_COMMANDER] = dbi_result_get_uint(res, "other_ch_commander");
sp->priv[g][SP_OTHER_CH_KICK] = dbi_result_get_uint(res, "other_ch_kick");
sp->priv[g][SP_OTHER_SV_KICK] = dbi_result_get_uint(res, "other_sv_kick");
sp->priv[g][SP_OTHER_TEXT_PL] = dbi_result_get_uint(res, "other_text_pl");
sp->priv[g][SP_OTHER_TEXT_ALL_CH] = dbi_result_get_uint(res, "other_text_all_ch");
sp->priv[g][SP_OTHER_TEXT_IN_CH] = dbi_result_get_uint(res, "other_text_in_ch");
sp->priv[g][SP_OTHER_TEXT_ALL] = dbi_result_get_uint(res, "other_text_all");
}
dbi_result_free(res);
}
return 1;
}
unsigned int dbi_result_get_fields(dbi_result Result, const char *format, ...) {
char **tokens, **fieldnames;
unsigned int curidx = 0, numtokens = 0, uflag;
va_list ap;
if (!RESULT) return DBI_FIELD_ERROR;
numtokens = _parse_field_formatstr(format, &tokens, &fieldnames);
if (numtokens == DBI_FIELD_ERROR) {
return numtokens;
}
va_start(ap, format);
while (curidx < numtokens) {
uflag = strlen(tokens[curidx]) > 1 && tokens[curidx][0] == 'u';
switch (tokens[curidx][strlen(tokens[curidx])-1]) {
case 'c': /* char */
if (uflag) /* unsigned */
*va_arg(ap, unsigned char *) = dbi_result_get_uchar(Result, fieldnames[curidx]);
else
*va_arg(ap, char *) = dbi_result_get_char(Result, fieldnames[curidx]);
break;
case 'h': /* sHort ("S"tring was taken) */
if (uflag) /* unsigned */
*va_arg(ap, unsigned short *) = dbi_result_get_ushort(Result, fieldnames[curidx]);
else
*va_arg(ap, short *) = dbi_result_get_short(Result, fieldnames[curidx]);
break;
case 'l': /* 4-byte integer (both l and i work) */
case 'i':
if (uflag) /* unsigned */
*va_arg(ap, unsigned int *) = dbi_result_get_uint(Result, fieldnames[curidx]);
else
*va_arg(ap, int *) = dbi_result_get_int(Result, fieldnames[curidx]);
break;
case 'L': /* long long */
if (uflag) /* unsigned */
*va_arg(ap, unsigned long long *) = dbi_result_get_ulonglong(Result, fieldnames[curidx]);
else
*va_arg(ap, long long *) = dbi_result_get_longlong(Result, fieldnames[curidx]);
break;
case 'f': /* float */
*va_arg(ap, float *) = dbi_result_get_float(Result, fieldnames[curidx]);
break;
case 'd': /* double */
*va_arg(ap, double *) = dbi_result_get_double(Result, fieldnames[curidx]);
break;
case 's': /* string */
*va_arg(ap, const char **) = dbi_result_get_string(Result, fieldnames[curidx]);
break;
case 'b': /* binary */
*va_arg(ap, const unsigned char **) = dbi_result_get_binary(Result, fieldnames[curidx]);
break;
case 'S': /* string copy */
*va_arg(ap, char **) = dbi_result_get_string_copy(Result, fieldnames[curidx]);
break;
case 'B': /* binary copy */
*va_arg(ap, unsigned char **) = dbi_result_get_binary_copy(Result, fieldnames[curidx]);
break;
case 'm': /* datetiMe (what... you have any better ideas?? */
*va_arg(ap, time_t *) = dbi_result_get_datetime(Result, fieldnames[curidx]);
break;
}
curidx++;
}
va_end(ap);
_free_string_list(tokens, numtokens);
_free_string_list(fieldnames, numtokens);
return numtokens;
}
static void _bind_helper_string(_field_binding_t *binding) {
*(const char **)binding->bindto = dbi_result_get_string((dbi_result)binding->result, binding->fieldname);
}
int rpserver_db_pair_exec(dbi_conn *db, const char *localid, const char *name, char *buf, size_t maxlen)
{
dbi_result *result;
int len = 0;
int boxid = -1;
result = dbi_conn_queryf(db, "DELETE FROM "DB_TABLE_DEVICES" WHERE name='%s' AND iid IS NULL\n", name);
if(result){
dbi_result_free(result);
} else {
const char *err;
dbi_conn_error(db, &err);
LOG(LOG_INFO, "%s():%d - dbi error querying database (%s)\n", __FUNCTION__, __LINE__, err);
return -1;
}
result = dbi_conn_queryf(db, "SELECT device_id FROM "DB_TABLE_DEVICES" WHERE localid='%s' AND name IS NULL\n", localid);
if(result){
if(dbi_result_next_row(result)) {
boxid = dbi_result_get_int(result, "device_id");
LOG(LOG_INFO, "%s():%d - boxid is %d\n", __FUNCTION__, __LINE__, boxid);
}
dbi_result_free(result);
} else {
const char *err;
dbi_conn_error(db, &err);
LOG(LOG_INFO, "%s():%d - dbi error querying database (%s)\n", __FUNCTION__, __LINE__, err);
return -1;
}
if(boxid < 0){
LOG(LOG_INFO, "%s():%d - boxid %d !!!!\n", __FUNCTION__, __LINE__, boxid);
return -1;
}
result = dbi_conn_queryf(db, "UPDATE "DB_TABLE_DEVICES" SET name='%s' WHERE device_id=%d", name, boxid);
if(result){
dbi_result_free(result);
} else {
const char *err;
dbi_conn_error(db, &err);
LOG(LOG_INFO, "%s():%d - dbi error querying database (%s)\n", __FUNCTION__, __LINE__, err);
return -1;
}
LOG(LOG_INFO, "%s():%d - name updated to '%s' for moxid %d !!!!\n", __FUNCTION__, __LINE__, name, boxid);
result = dbi_conn_queryf(db, "SELECT hname, localid FROM "DB_TABLE_DEVICES" WHERE device_id=%d", boxid);
if(result){
if(dbi_result_next_row(result)) {
len += snprintf(buf+len, maxlen-len, "%s: %s",
dbi_result_get_string(result, "localid"),
dbi_result_get_string(result, "hname")
);
}
dbi_result_free(result);
} else {
const char *err;
dbi_conn_error(db, &err);
LOG(LOG_INFO, "%s():%d - dbi error querying database (%s)\n",
__FUNCTION__, __LINE__, err);
return -1;
}
LOG(LOG_INFO, "%s():%d - read localiid and hname: %s !!!!\n", __FUNCTION__, __LINE__, name, buf);
int other_pwid = -1;
int working_pwid = 0;
result = dbi_conn_queryf(db,
"SELECT pwid , used, atid FROM acc_devices_pwl JOIN acc_types USING(atid)"
" WHERE agid = 1 AND acc_devices_pwl.enable = 1 AND acc_types.enable =1"
" AND id = %d ORDER BY atid ASC", boxid);
if(result){
if(dbi_result_next_row(result)) {
other_pwid = dbi_result_get_int(result, "pwid");
if(dbi_result_get_int(result, "used")==1)
working_pwid++;
if(dbi_result_get_int(result, "atid")== REMACC_TYPE_MAC)
working_pwid++;
}
dbi_result_free(result);
} else {
const char *err;
dbi_conn_error(db, &err);
LOG(LOG_INFO, "%s():%d - dbi error querying database (%s)\n",
__FUNCTION__, __LINE__, err);
return -1;
}
LOG(LOG_INFO, "%s():%d - searched for other pwid : %d !!!!\n", __FUNCTION__, __LINE__, working_pwid);
if(working_pwid>0){
LOG(LOG_INFO, "%s():%d - has working pwid, no new pwid are issued (%d)\n" ,__FUNCTION__, __LINE__,working_pwid);
return len;
}
char other_pwid_str[32];
if(other_pwid < 0){
sprintf(other_pwid_str, "NULL");
} else {
sprintf(other_pwid_str, "%d", other_pwid);
}
result = dbi_conn_queryf(db, "INSERT INTO `rembox`.`acc_devices_pwl`"
" (`pwid` ,`atid` ,`id` ,`username` ,`password` ,`enable` ,`exported` ,`used` ,`created` ,`replaced`)"
" VALUES (NULL , %d, %d, '%s', NULL , '1', '0', '0', CURRENT_TIMESTAMP , %s)"
, REMACC_TYPE_MAC, boxid, name, other_pwid_str);
if(result){
dbi_result_free(result);
} else {
const char *err;
dbi_conn_error(db, &err);
LOG(LOG_INFO, "%s():%d - dbi error querying database (%s)\n", __FUNCTION__, __LINE__, err);
return -1;
}
LOG(LOG_INFO, "%s():%d - inserted new pwid : %d !!!!\n", __FUNCTION__, __LINE__, dbi_conn_sequence_last(db, NULL));
return len;
}
static void calc_emp_cds_stats(struct module *mod, dbi_conn conn)
{
int ct = 0;
char dev_ip[LEN_32];
char dev_port[LEN_32];
dbi_result result = 0;
ST_CDS *pe = NULL;
tDBConn *pconn = NULL;
pe = (ST_CDS *)mod->emp_array;
/* acquire every enterprise id */
result = dbi_conn_queryf(conn, "select e_id from \"T_Enterprise\" order by e_id");
if (result) {
while(dbi_result_next_row(result)) {
pe[ct].sdr_id = dbi_result_get_longlong(result, "e_id");
convert_time_to_string(statis.cur_time, pe[ct].sdr_record_time, 0);
convert_time_to_string(statis.cur_time - 24*60*60, pe[ct].sdr_time, 1);
pe[ct].sdr_date_flag = 0;
ct++;
}
dbi_result_free(result);
}
for (ct = 0; ct < mod->emp_record; ct++)
{
long long sdr_id = pe[ct].sdr_id;
result = dbi_conn_queryf(conn, "select d_ip2 from \"T_Device\" where d_id = \
(select e_ss_id from \"T_Enterprise\" where e_id = %lld);", sdr_id);
if (result) {
while (dbi_result_next_row(result)) {
memset(dev_ip, '\0', LEN_32);
memset(dev_port, '\0', LEN_32);
strcpy(dev_ip, dbi_result_get_string(result, "d_ip2"));
strcpy(dev_port, "5432");
}
dbi_result_free(result);
}
if ((pconn = attach_conn(SSDB, dev_ip, dev_port)) == NULL)
{
logRecord(LOG_ERR, "%s(%d): failed to get db conn\n", __FUNCTION__, __LINE__);
continue;
}
pe[ct].sdr_ptt_htime = dbi_query_long(pconn->conn, "sum", "select sum(callsec) from \
\"cdr_%lld\" where endtime < %lld and (call_type = 3 or call_type = 4);",
sdr_id, statis.cur_time);
pe[ct].sdr_ptt_hcount = dbi_query_long(pconn->conn, "count", "select count(*) from \
\"cdr_%lld\" where endtime < %lld and (call_type = 3 or call_type = 4);",
sdr_id, statis.cur_time);
pe[ct].sdr_call_htime = 2 * dbi_query_long(pconn->conn, "sum", "select sum(callsec) from \
\"cdr_%lld\" where endtime < %lld and (call_type = 1 or call_type = 2);",
sdr_id, statis.cur_time);
pe[ct].sdr_call_hcount = 2 * dbi_query_long(pconn->conn, "count", "select count(*) from \
\"cdr_%lld\" where endtime < %lld and (call_type = 1 or call_type = 2);",
sdr_id, statis.cur_time);
pe[ct].sdr_video_htime = 2 * dbi_query_long(pconn->conn, "sum", "select sum(callsec) from \
\"cdr_%lld\" where endtime < %lld and call_type = 2;",
sdr_id, statis.cur_time);
pe[ct].sdr_video_hcount = 2 * dbi_query_long(pconn->conn, "count", "select count(*) from \
\"cdr_%lld\" where endtime < %lld and call_type = 2",
sdr_id, statis.cur_time);
pe[ct].sdr_audio_htime = 2 * dbi_query_long(pconn->conn, "sum", "select sum(callsec) from \
\"cdr_%lld\" where endtime < %lld and call_type = 1;",
sdr_id, statis.cur_time);
pe[ct].sdr_audio_hcount = 2 * dbi_query_long(pconn->conn, "count", "select count(*) from \
\"cdr_%lld\" where endtime < %lld and call_type = 1;",
sdr_id, statis.cur_time);
detach_conn(pconn);
}
//log_cds_stats(pe, mod->emp_record);
return;
}
static void calc_amp_cds(struct module *mod, dbi_conn conn, int interval)
{
int ct = 0, st = 0;
dbi_result result = 0;
ST_CDS *pa = NULL;
ST_CDS *pe = NULL;
long long rst = 0;
logRecord( LOG_INFO, "%s(%d): cal amp, interval:%d", __FUNCTION__, __LINE__, interval );
pa = (ST_CDS *)mod->amp_array;
pe = (ST_CDS *)mod->emp_array;
/* acquire omp uss stats */
pa[0].sdr_id = 0;
strncpy(pa[0].sdr_amp_id, "0", sizeof(pa[0].sdr_amp_id) - 1);
convert_time_to_string(statis.cur_time, pa[ct].sdr_record_time, 0);
convert_time_to_string(statis.cur_time - 24*60*60, pa[ct].sdr_time, 1);
if (interval == 0) {
pa[0].sdr_date_flag = 0;
} else if (interval == 7) {
pa[0].sdr_date_flag = 1;
} else {
pa[0].sdr_date_flag = 2;
}
for (ct = 0; ct < mod->emp_record; ct++)
{
pa[0].sdr_ptt_htime += pe[ct].sdr_ptt_htime;
pa[0].sdr_ptt_hcount += pe[ct].sdr_ptt_hcount;
pa[0].sdr_call_htime += pe[ct].sdr_call_htime;
pa[0].sdr_call_hcount += pe[ct].sdr_call_hcount;
pa[0].sdr_video_htime += pe[ct].sdr_video_htime;
pa[0].sdr_video_hcount += pe[ct].sdr_video_hcount;
pa[0].sdr_audio_htime += pe[ct].sdr_audio_htime;
pa[0].sdr_audio_hcount += pe[ct].sdr_audio_hcount;
}
st = 1;
/* acquire amp uss stats */
result = dbi_conn_queryf(conn, "select ag_number from \"T_Agents\" order by ag_number");
if (result) {
while(dbi_result_next_row(result)) {
//pa[st].sdr_id = atoll(dbi_result_get_string(result, "ag_number"));
strncpy( pa[st].sdr_amp_id, dbi_result_get_string(result, "ag_number"), sizeof(pa[st].sdr_amp_id) - 1 );
pa[st].sdr_amp_id[sizeof(pa[st].sdr_amp_id) - 1] = '\0';
convert_time_to_string(statis.cur_time, pa[st].sdr_record_time, 0);
convert_time_to_string(statis.cur_time - 24*60*60, pa[st].sdr_time, 1);
if (interval == 0) {
pa[st].sdr_date_flag = 0;
} else if (interval == 7) {
pa[st].sdr_date_flag = 1;
} else {
pa[st].sdr_date_flag = 2;
}
st++;
}
dbi_result_free(result);
}
for (st = 1; st < mod->amp_record; st++)
{
/*
result = dbi_conn_queryf(conn, "select e_id from \"T_Enterprise\" where \
e_ag_path like '%s%lld%s' order by e_id", "%", pa[st].sdr_id, "%");
*/
result = dbi_conn_queryf(conn, "select e_id from \"T_Enterprise\" where \
e_ag_path like '%s%s%s' order by e_id", "%", pa[st].sdr_amp_id, "%");
if (result) {
while(dbi_result_next_row(result)) {
rst = dbi_result_get_longlong(result, "e_id");
for (ct = 0; ct < mod->emp_record; ct++) {
if (rst == pe[ct].sdr_id) {
pa[st].sdr_ptt_htime += pe[ct].sdr_ptt_htime;
pa[st].sdr_ptt_hcount += pe[ct].sdr_ptt_hcount;
pa[st].sdr_call_htime += pe[ct].sdr_call_htime;
pa[st].sdr_call_hcount += pe[ct].sdr_call_hcount;
pa[st].sdr_video_htime += pe[ct].sdr_video_htime;
pa[st].sdr_video_hcount += pe[ct].sdr_video_hcount;
pa[st].sdr_audio_htime += pe[ct].sdr_audio_htime;
pa[st].sdr_audio_hcount += pe[ct].sdr_audio_hcount;
break;
}
}
}
dbi_result_free(result);
}
}
//log_cds_stats(pa, mod->amp_record);
return;
}
static void calc_emp_uas_stats(struct module *mod, dbi_conn conn)
{
int ct = 0;
char dev_ip[LEN_32];
char dev_port[LEN_32];
dbi_result result = 0;
ST_UAS *pe = NULL;
tDBConn *pconn = NULL;
int user_status = -1;
pe = (ST_UAS *)mod->emp_array;
/* acquire every enterprise id */
result = dbi_conn_queryf(conn, "select u_number, u_e_id from \"T_User\" order by u_e_id");
if (result) {
while(dbi_result_next_row(result)) {
pe[ct].sdr_num = atoll(dbi_result_get_string(result, "u_number"));
pe[ct].sdr_id = dbi_result_get_longlong(result, "u_e_id");
convert_time_to_string(statis.cur_time - 24*60*60, pe[ct].sdr_time, 1);
ct++;
}
dbi_result_free(result);
}
logRecord(LOG_INFO, "%s(%d) uas start cal...", __FUNCTION__, __LINE__ );
for (ct = 0; ct < mod->emp_record; ct++)
{
long long sdr_id = pe[ct].sdr_id;
long long sdr_num = pe[ct].sdr_num;
result = dbi_conn_queryf(conn, "select d_ip2 from \"T_Device\" where d_id = \
(select e_ss_id from \"T_Enterprise\" where e_id = %lld);", sdr_id);
if (result) {
while (dbi_result_next_row(result)) {
memset(dev_ip, '\0', LEN_32);
memset(dev_port, '\0', LEN_32);
strcpy(dev_ip, dbi_result_get_string(result, "d_ip2"));
strcpy(dev_port, "5432");
}
dbi_result_free(result);
}
if ((pconn = attach_conn(SSDB, dev_ip, dev_port)) == NULL)
{
logRecord(LOG_ERR, "%s(%d): failed to get SSdb conn, ssip(%s)-ssport(%s).\n", \
__FUNCTION__, __LINE__, dev_ip, dev_port );
continue;
}
pe[ct].sdr_online_times = dbi_query_long(pconn->conn, "count", "select count(*) \
from \"loginrecord_%lld\" where time between '%lld' and '%lld' \
and type = 1 and number = '%lld';",
sdr_id, (statis.cur_time-24*60*60), statis.cur_time, sdr_num);
pe[ct].sdr_offline_times = dbi_query_long(pconn->conn, "count", "select count(*) \
from \"loginrecord_%lld\" where time between '%lld' and '%lld' \
and type = 2 and number = '%lld';",
sdr_id, (statis.cur_time-24*60*60), statis.cur_time, sdr_num);
/*
if (pe[ct].sdr_online_times != 0 || pe[ct].sdr_offline_times != 0)
pe[ct].sdr_active_flag = 1;
*/
if ( (pe[ct].sdr_online_times > 0) || (pe[ct].sdr_offline_times > 0) )
{
pe[ct].sdr_active_flag = 1;
}
else
{
/* not find yesterday user status,
* need look up user last status in loginrecord_e_id,
*/
user_status = dbi_query_long( pconn->conn, "type", "select type \
from \"loginrecord_%lld\" where number='%lld' order by time desc limit 1;", \
sdr_id, sdr_num );
if ( user_status == USER_ONLINE )
{
pe[ct].sdr_active_flag = USER_STATUS_ACTIVITY;
}
else if ( user_status == USER_OFFLINE )
{
pe[ct].sdr_active_flag = USER_STATUS_INACTIVITY;
}
else
{
pe[ct].sdr_active_flag = USER_STATUS_INACTIVITY;
}
}
detach_conn(pconn);
}
logRecord(LOG_INFO, "%s(%d) uas end cal...", __FUNCTION__, __LINE__ );
//log_uas_stats(pe, mod->emp_record);
return;
}
