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);
}
//*******************************************************************
// retrieve the definitions + status
// get it from the cache. if there but too old: delete
// If a probe definition does not exist, it will be created.
// in case of mysql-has-gone-away type errors, we keep on running,
// it will be caught later-on.
//*******************************************************************
void *bb_cpu_get_def(trx *t, int create)
{
struct probe_def *def;
struct bb_cpu_result *res = (struct bb_cpu_result *)t->res;
dbi_result result;
time_t now = time(NULL);
if (res->color != STAT_PURPLE && res->server == 0) {
// first we find the serverid, this will be used to find the probe definition in the hashtable
result = db_query(t->probe->db, 0, query_server_by_name, res->hostname, res->hostname,
res->hostname, res->hostname, res->hostname);
if (!result) {
return(NULL);
}
if (dbi_result_next_row(result)) {
res->server = dbi_result_get_int_idx(result, 0);
} else {
LOG(LOG_NOTICE, "%s:%u@%s: server %s not found", res->realm, res->stattime, t->fromhost, res->hostname);
dbi_result_free(result);
return(NULL);
}
dbi_result_free(result);
}
// look in the cache for the def
def = g_hash_table_lookup(t->probe->cache, &res->server);
if (def && def->stamp < now - (120 + uw_rand(240))) { // older then 2 - 6 minutes?
g_hash_table_remove(t->probe->cache, &res->server);
def = NULL;
}
// if not there retrieve from database and insert in hash
if (def == NULL) {
def = g_malloc0(t->probe->def_size);
def->stamp = time(NULL);
def->server = res->server;
def->pgroup = 1;
strcpy(def->hide, "no");
// first find the definition based on the serverid
result = db_query(t->probe->db, 0, "select id, yellow, red, contact, hide, email, sms, delay "
"from pr_%s_def where server = '%u'",
res->name, res->server);
if (!result) {
g_free(def);
return(NULL);
}
if (dbi_result_get_numrows(result) == 0) { // DEF RECORD NOT FOUND
char sequence[40];
// no def record found? Create one.
dbi_result_free(result);
result = db_query(t->probe->db, 0,
"insert into pr_%s_def (server, description) values ('%u', '%s')",
res->name, res->server, res->hostname);
dbi_result_free(result);
sprintf(sequence, "pr_%s_def_id_seq", res->name);
def->probeid = dbi_conn_sequence_last(t->probe->db, sequence);
LOG(LOG_NOTICE, "%s:%u@%s: pr_%s_def created for %s, id = %u",
res->realm, res->stattime, t->fromhost, res->name, res->hostname, def->probeid);
result = db_query(t->probe->db, 0, "select id, yellow, red, contact, hide, email, sms, delay "
"from pr_%s_def where id = '%u'",
res->name, def->probeid);
}
if (!dbi_result_next_row(result)) {
LOG(LOG_NOTICE, "%s:%u@%s: no pr_%s_def found for server %u - skipped",
res->realm, res->stattime, t->fromhost, res->name, res->server);
dbi_result_free(result);
g_free(def);
return(NULL);
}
def->probeid = dbi_result_get_int(result, "id");
def->yellow = dbi_result_get_float(result, "yellow");
def->red = dbi_result_get_float(result, "red");
def->contact = dbi_result_get_int(result, "contact");
strcpy(def->hide, dbi_result_get_string_default(result, "hide", "no"));
strcpy(def->email, dbi_result_get_string_default(result, "email", ""));
strcpy(def->sms, dbi_result_get_string_default(result, "sms", ""));
def->delay = dbi_result_get_int(result, "delay");
dbi_result_free(result);
result = db_query(t->probe->db, 0,
"select color "
"from pr_status "
"where class = '%u' and probe = '%u'", t->probe->class, def->probeid);
if (result) {
if (dbi_result_next_row(result)) {
def->color = dbi_result_get_int(result, "color");
} else {
LOG(LOG_NOTICE, "%s:%u@%s: pr_status record for %s id %u not found",
res->realm, res->stattime, t->fromhost, res->name, def->probeid);
}
dbi_result_free(result);
}
if (!def->color) def->color = res->color;
result = db_query(t->probe->db, 0,
"select stattime from pr_%s_raw use index(probstat) "
"where probe = '%u' order by stattime desc limit 1",
res->name, def->probeid);
if (result) {
if (dbi_result_next_row(result)) {
def->newest = dbi_result_get_int(result, "stattime");
}
dbi_result_free(result);
}
g_hash_table_insert(t->probe->cache, guintdup(def->server), def);
}
void refresh_database(database *db)
{
dbi_result result;
char qry[1024];
char *error;
sprintf(qry, "SELECT pr_mysqlstats_def.id, pr_mysqlstats_def.domid, pr_mysqlstats_def.tblid, pr_realm.name, "
" pr_mysqlstats_def.ipaddress, pr_mysqlstats_def.dbname, "
" pr_mysqlstats_def.dbuser, pr_mysqlstats_def.dbpasswd,"
" pr_mysqlstats_def.yellow, pr_mysqlstats_def.red "
"FROM pr_mysqlstats_def, pr_realm "
"WHERE pr_mysqlstats_def.id > 1 and pr_mysqlstats_def.disable <> 'yes'"
" and pr_mysqlstats_def.pgroup = '%d' and pr_realm.id = pr_mysqlstats_def.domid",
(unsigned)OPT_VALUE_GROUPID);
result = db_query(db, 1, qry);
if (!result) {
return;
}
while (dbi_result_next_row(result)) {
int id;
struct probedef *probe;
id = dbi_result_get_int(result, "id");
probe = g_hash_table_lookup(cache, &id);
if (!probe) {
probe = g_malloc0(sizeof(struct probedef));
probe->id = id;
if (dbi_result_get_int(result, "domid") > 1) {
probe->probeid = dbi_result_get_int(result, "tblid");
probe->realm = dbi_result_get_string_copy(result, "name");
} else {
probe->probeid = probe->id;
}
//fprintf(stderr, "Adding %u\n", 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->dbname) g_free(probe->dbname);
probe->dbname = dbi_result_get_string_copy(result, "dbname");
if (probe->dbuser) g_free(probe->dbuser);
probe->dbuser = dbi_result_get_string_copy(result, "dbuser");
if (probe->dbpasswd) g_free(probe->dbpasswd);
probe->dbpasswd = dbi_result_get_string_copy(result, "dbpasswd");
probe->yellow = dbi_result_get_float(result, "yellow");
probe->red = dbi_result_get_float(result, "yellow");
if (probe->msg) g_free(probe->msg);
probe->msg = NULL;
probe->seen = 1;
}
dbi_result_free(result);
if (dbi_conn_error(db, &error) == DBI_ERROR_NONE) {
g_hash_table_foreach_remove(cache, return_seen, NULL);
} else {
LOG(LOG_ERR, "%s", error);
g_hash_table_foreach(cache, reset_seen, NULL);
}
}
//*******************************************************************
// SUMMARIZE A TABLE INTO AN OLDER PERIOD
//*******************************************************************
static void mysqlstats_summarize(trx *t, char *from, char *into, guint slot, guint slotlow, guint slothigh, gint resummarize)
{
dbi_result result;
struct probe_def *def = (struct probe_def *)t->def;
float avg_yellow, avg_red;
int avg_selectq;
int avg_insertq;
int avg_updateq;
int avg_deleteq;
guint stattime;
guint max_color;
stattime = slotlow + ((slothigh-slotlow)/2);
result = db_query(t->probe->db, 0,
"select avg(selectq) as avg_selectq, avg(insertq) as avg_insertq, avg(updateq) as avg_updateq, "
" avg(deleteq) as avg_deleteq, max(color) as max_color, avg(yellow) as avg_yellow, avg(red) as avg_red"
"from pr_mysqlstats_%s use index(probstat) "
"where probe = '%d' and stattime >= %d and stattime < %d",
from, def->probeid, slotlow, slothigh);
if (!result) return;
if (dbi_result_get_numrows(result) == 0) { // no records found
LOG(LOG_NOTICE, "nothing to summarize from %s for probe %u %u %u",
from, def->probeid, slotlow, slothigh);
dbi_result_free(result);
return;
}
if (!dbi_result_next_row(result)) {
dbi_result_free(result);
return;
}
if (dbi_result_field_is_null_idx(result, 0)) {
LOG(LOG_NOTICE, "nothing to summarize from %s for probe %u %u %u",
from, def->probeid, slotlow, slothigh);
dbi_result_free(result);
return;
}
avg_selectq = dbi_result_get_int(result, "avg_selectq");
avg_insertq = dbi_result_get_int(result, "avg_insertq");
avg_updateq = dbi_result_get_int(result, "avg_updateq");
avg_deleteq = dbi_result_get_int(result, "avg_deleteq");
max_color = dbi_result_get_int(result, "max_color");
avg_yellow = dbi_result_get_float(result, "avg_yellow");
avg_red = dbi_result_get_float(result, "avg_red");
dbi_result_free(result);
if (resummarize) {
// delete old values
result = db_query(t->probe->db, 0,
"delete from pr_mysqlstats_%s where probe = '%u' and stattime = '%u'",
into, def->probeid, stattime);
dbi_result_free(result);
}
result = db_query(t->probe->db, 0,
"insert into pr_mysqlstats_%s (selectq, insertq, updateq, deleteq, probe, color, stattime, "
" yellow, red, slot) "
" values ('%u', '%u', '%u', '%u', '%d', '%u', '%d', "
" '%f', '%f', '%u')",
into, avg_selectq, avg_insertq, avg_updateq, avg_deleteq, def->probeid,
max_color, stattime, avg_yellow, avg_red, slot);
dbi_result_free(result);
}
//*******************************************************************
// SUMMARIZE A TABLE INTO AN OLDER PERIOD
//*******************************************************************
static void bb_cpu_summarize(trx *t, char *from, char *into, guint slot, guint slotlow, guint slothigh, gint resummarize)
{
dbi_result result;
struct probe_def *def = (struct probe_def *)t->def;
float avg_yellow, avg_red;
gfloat avg_loadavg;
guint avg_user, avg_system, avg_idle;
guint avg_swapped, avg_free, avg_buffered, avg_cached, avg_used;
guint stattime;
guint max_color;
stattime = slotlow + ((slothigh-slotlow)/2);
result = db_query(t->probe->db, 0,
"select avg(loadavg) as avg_loadavg, avg(user) as avg_user, avg(system) as avg_system, "
" avg(idle) as avg_idle, avg(swapped) as avg_swapped, avg(free) as avg_free, "
" avg(buffered) as avg_buffered, avg(cached) as avg_cached, avg(used) as avg_used, "
" max(color) as max_color, avg(yellow) as avg_yellow, avg(red) as avg_red "
"from pr_bb_cpu_%s use index(probstat) "
"where probe = '%d' and stattime >= %d and stattime < %d",
from, def->probeid, slotlow, slothigh);
if (!result) return;
if (dbi_result_get_numrows(result) == 0) { // no records found
LOG(LOG_NOTICE, "nothing to summarize from %s for probe %u %u %u",
from, def->probeid, slotlow, slothigh);
dbi_result_free(result);
return;
}
if (!dbi_result_next_row(result)) {
dbi_result_free(result);
return;
}
if (dbi_result_field_is_null_idx(result, 0)) {
LOG(LOG_NOTICE, "nothing to summarize from %s for probe %u %u %u",
from, def->probeid, slotlow, slothigh);
dbi_result_free(result);
return;
}
avg_loadavg = dbi_result_get_float(result, "avg_loadavg");
avg_user = dbi_result_get_int(result, "avg_user");
avg_system = dbi_result_get_int(result, "avg_system");
avg_idle = dbi_result_get_int(result, "avg_idle");
avg_swapped = dbi_result_get_int(result, "avg_swapped");
avg_free = dbi_result_get_int(result, "avg_free");
avg_buffered= dbi_result_get_int(result, "avg_buffered");
avg_cached = dbi_result_get_int(result, "avg_cached");
avg_used = dbi_result_get_int(result, "avg_used");
max_color = dbi_result_get_int(result, "max_color");
avg_yellow = dbi_result_get_float(result, "avg_yellow");
avg_red = dbi_result_get_float(result, "avg_red");
dbi_result_free(result);
if (resummarize) {
// delete old values
result = db_query(t->probe->db, 0,
"delete from pr_bb_cpu_%s where probe = '%u' and stattime = '%u'",
into, def->probeid, stattime);
dbi_result_free(result);
}
result = db_query(t->probe->db, 0,
"insert into pr_bb_cpu_%s (loadavg, user, system, idle, swapped, free, buffered, cached, "
" used, probe, color, stattime, yellow, red, slot) "
" values ('%f', '%u', '%u', '%u', '%u', '%u', '%u', '%u', "
" '%u', '%d', '%u', '%d', '%f', '%f', '%u')",
into,
avg_loadavg, avg_user, avg_system, avg_idle,
avg_swapped, avg_free, avg_buffered, avg_cached,
avg_used, def->probeid, max_color, stattime,
avg_yellow, avg_red, slot);
dbi_result_free(result);
}
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_float(_field_binding_t *binding) {
*(float *)binding->bindto = dbi_result_get_float((dbi_result)binding->result, binding->fieldname);
}