1: /*
2: * out_db.c Database Output
3: *
4: * Copyright (c) 2001-2005 Thomas Graf <tgraf@suug.ch>
5: *
6: * Permission is hereby granted, free of charge, to any person obtaining a
7: * copy of this software and associated documentation files (the "Software"),
8: * to deal in the Software without restriction, including without limitation
9: * the rights to use, copy, modify, merge, publish, distribute, sublicense,
10: * and/or sell copies of the Software, and to permit persons to whom the
11: * Software is furnished to do so, subject to the following conditions:
12: *
13: * The above copyright notice and this permission notice shall be included
14: * in all copies or substantial portions of the Software.
15: *
16: * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS
17: * OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
18: * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
19: * THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
20: * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
21: * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
22: * DEALINGS IN THE SOFTWARE.
23: */
24:
25: /*
26: * NOTE
27: *
28: * This whole code is a realy mess and written in a hurry, most interactions
29: * should be cached to save traffic. It really is meant as experimental
30: * features.
31: */
32:
33: #include <bmon/bmon.h>
34: #include <bmon/node.h>
35: #include <bmon/output.h>
36: #include <bmon/graph.h>
37: #include <bmon/input.h>
38: #include <bmon/utils.h>
39:
40: #include <dbi/dbi.h>
41:
42: static char *c_driverdir = NULL;
43: static char *c_driver = "mysql";
44: static char *c_host = "localhost";
45: static char *c_username = "bmon";
46: static char *c_dbname = "bmon";
47: static char *c_mask = "mhd";
48: static char *c_password;
49: static int c_interval = 3;
50:
51: static int do_read;
52: static int do_sec;
53: static int do_min;
54: static int do_hour;
55: static int do_day;
56:
57: static dbi_conn db_conn;
58:
59: static int create_tables(void)
60: {
61: dbi_result r;
62:
63: r = dbi_conn_query(db_conn,
64: "CREATE TABLE IF NOT EXISTS nodes (" \
65: " id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE," \
66: " name TEXT NOT NULL," \
67: " source TEXT," \
68: " PRIMARY KEY (id)" \
69: ")");
70:
71: if (!r)
72: return 0;
73: else
74: dbi_result_free(r);
75:
76: r = dbi_conn_query(db_conn,
77: "CREATE TABLE IF NOT EXISTS items (" \
78: " id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE," \
79: " name TEXT NOT NULL," \
80: " description TEXT," \
81: " node INT UNSIGNED NOT NULL," \
82: " handle INT UNSIGNED," \
83: " parent INT UNSIGNED," \
84: " indent INT UNSIGNED," \
85: " rx_usage SMALLINT NOT NULL," \
86: " tx_usage SMALLINT NOT NULL," \
87: " PRIMARY KEY (id)," \
88: " FOREIGN KEY (node) REFERENCES nodes(id)," \
89: " FOREIGN KEY (parent) REFERENCES items(id)" \
90: ")");
91:
92: if (!r)
93: return 0;
94: else
95: dbi_result_free(r);
96:
97: r = dbi_conn_query(db_conn,
98: "CREATE TABLE IF NOT EXISTS attrs (" \
99: " id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE," \
100: " name CHAR(15) NOT NULL," \
101: " item INT UNSIGNED NOT NULL," \
102: " rx_rate INT UNSIGNED," \
103: " tx_rate INT UNSIGNED," \
104: " rx_counter BIGINT UNSIGNED NOT NULL," \
105: " tx_counter BIGINT UNSIGNED NOT NULL," \
106: " PRIMARY KEY (id)," \
107: " FOREIGN KEY (item) REFERENCES items(id)" \
108: ")");
109:
110: if (!r)
111: return 0;
112: else
113: dbi_result_free(r);
114:
115: r = dbi_conn_query(db_conn,
116: "CREATE TABLE IF NOT EXISTS attr_desc (" \
117: " id CHAR(15) NOT NULL UNIQUE," \
118: " is_num SMALLINT UNSIGNED NOT NULL," \
119: " txt TEXT NOT NULL," \
120: " PRIMARY KEY(id)" \
121: ")");
122:
123: if (!r)
124: return 0;
125: else
126: dbi_result_free(r);
127:
128: r = dbi_conn_query(db_conn,
129: "CREATE TABLE IF NOT EXISTS hist_r (" \
130: " attr INT UNSIGNED NOT NULL," \
131: " ts INT UNSIGNED NOT NULL," \
132: " offset INT UNSIGNED NOT NULL," \
133: " rx_rate INT UNSIGNED NOT NULL," \
134: " tx_rate INT UNSIGNED NOT NULL," \
135: " PRIMARY KEY (attr, ts, offset)," \
136: " FOREIGN KEY (attr) REFERENCES attrs(id)" \
137: ")");
138:
139: if (!r)
140: return 0;
141: else
142: dbi_result_free(r);
143:
144: r = dbi_conn_query(db_conn,
145: "CREATE TABLE IF NOT EXISTS hist_s (" \
146: " attr INT UNSIGNED NOT NULL," \
147: " ts INT UNSIGNED NOT NULL," \
148: " offset INT UNSIGNED NOT NULL," \
149: " rx_rate INT UNSIGNED NOT NULL," \
150: " tx_rate INT UNSIGNED NOT NULL," \
151: " PRIMARY KEY (attr, ts, offset)," \
152: " FOREIGN KEY (attr) REFERENCES attrs(id)" \
153: ")");
154:
155: if (!r)
156: return 0;
157: else
158: dbi_result_free(r);
159:
160: r = dbi_conn_query(db_conn,
161: "CREATE TABLE IF NOT EXISTS hist_m (" \
162: " attr INT UNSIGNED NOT NULL," \
163: " ts INT UNSIGNED NOT NULL," \
164: " offset INT UNSIGNED NOT NULL," \
165: " rx_rate INT UNSIGNED NOT NULL," \
166: " tx_rate INT UNSIGNED NOT NULL," \
167: " PRIMARY KEY (attr, ts, offset)," \
168: " FOREIGN KEY (attr) REFERENCES attrs(id)" \
169: ")");
170:
171: if (!r)
172: return 0;
173: else
174: dbi_result_free(r);
175:
176: r = dbi_conn_query(db_conn,
177: "CREATE TABLE IF NOT EXISTS hist_h (" \
178: " attr INT UNSIGNED NOT NULL," \
179: " ts INT UNSIGNED NOT NULL," \
180: " offset INT UNSIGNED NOT NULL," \
181: " rx_rate INT UNSIGNED NOT NULL," \
182: " tx_rate INT UNSIGNED NOT NULL," \
183: " PRIMARY KEY (attr, ts, offset)," \
184: " FOREIGN KEY (attr) REFERENCES attrs(id)" \
185: ")");
186:
187: if (!r)
188: return 0;
189: else
190: dbi_result_free(r);
191:
192: r = dbi_conn_query(db_conn,
193: "CREATE TABLE IF NOT EXISTS hist_d (" \
194: " attr INT UNSIGNED NOT NULL," \
195: " ts INT UNSIGNED NOT NULL," \
196: " offset INT UNSIGNED NOT NULL," \
197: " rx_rate INT UNSIGNED NOT NULL," \
198: " tx_rate INT UNSIGNED NOT NULL," \
199: " PRIMARY KEY (attr, ts, offset)," \
200: " FOREIGN KEY (attr) REFERENCES attrs(id)" \
201: ")");
202:
203: if (!r)
204: return 0;
205: else
206: dbi_result_free(r);
207:
208: return 1;
209: }
210:
211: static int insert_attr_desc(struct attr_type *a, void *arg)
212: {
213: dbi_result r = dbi_conn_queryf(db_conn,
214: "SELECT id FROM attr_desc WHERE id = '%s'", a->name);
215:
216: if (!r)
217: return -1;
218:
219: if (dbi_result_first_row(r) == 1) {
220: dbi_result_free(r);
221: return 0;
222: }
223:
224: dbi_result_free(r);
225:
226: r = dbi_conn_queryf(db_conn,
227: "INSERT INTO attr_desc (id, is_num, txt) VALUES " \
228: "('%s', %d, '%s')", a->name, a->unit == U_NUMBER,
229: a->desc);
230:
231: if (!r)
232: return -1;
233:
234: dbi_result_free(r);
235: return 0;
236: }
237:
238: static int insert_attr_descs(void)
239: {
240: return !foreach_attr_type(&insert_attr_desc, NULL);
241: }
242:
243: static void add_history(const char *table, int attr_id, timestamp_t *ts,
244: rate_cnt_t rx, rate_cnt_t tx)
245: {
246: dbi_result r = dbi_conn_queryf(db_conn,
247: "INSERT INTO %s (attr, ts, offset, rx_rate, tx_rate) " \
248: "values (%d, %u, %u, %u, %u)",
249: table, attr_id, (unsigned int) ts->tv_sec,
250: (unsigned int) ts->tv_usec, rx, tx);
251:
252: if (r)
253: dbi_result_free(r);
254: }
255:
256:
257: static inline int attr_exists(const char *name, int item_id)
258: {
259: int ret = -1;
260:
261: dbi_result r = dbi_conn_queryf(db_conn,
262: "SELECT id FROM attrs WHERE name = '%s' AND " \
263: "item = %d", name, item_id);
264:
265: if (r) {
266: if (dbi_result_first_row(r) == 1)
267: ret = (int) dbi_result_get_long(r, "id");
268: dbi_result_free(r);
269: }
270:
271: return ret;
272: }
273:
274: static inline int insert_attr(stat_attr_t *a, int item_id)
275: {
276: dbi_result r;
277:
278: r = dbi_conn_queryf(db_conn,
279: "INSERT INTO attrs (name, item, rx_rate, tx_rate, " \
280: "rx_counter, tx_counter) VALUES ('%s', %d, %d, %d, " \
281: "%lld, %lld)", type2name(a->a_type), item_id,
282: attr_get_rx_rate(a), attr_get_tx_rate(a),
283: attr_get_rx(a), attr_get_tx(a));
284:
285: if (!r)
286: return -1;
287:
288: dbi_result_free(r);
289: return attr_exists(type2name(a->a_type), item_id);
290:
291: }
292:
293: static inline int up_attr(stat_attr_t *a, int attr_id)
294: {
295: dbi_result r;
296:
297: r = dbi_conn_queryf(db_conn,
298: "UPDATE attrs SET rx_rate = %d, tx_rate = %d, " \
299: "rx_counter = %lld, tx_counter = %lld WHERE id = %d",
300: attr_get_rx_rate(a), attr_get_tx_rate(a),
301: attr_get_rx(a), attr_get_tx(a), attr_id);
302:
303: if (!r)
304: return -1;
305:
306: dbi_result_free(r);
307:
308: return 0;
309: }
310:
311: static inline int item_exists(item_t *it, node_t *node, int node_id)
312: {
313: int ret = -1;
314: dbi_result r;
315:
316: if (it->i_flags & ITEM_FLAG_IS_CHILD) {
317: int parent;
318: item_t *p = get_item(node, it->i_parent);
319: if (!p)
320: goto no_parent;
321:
322: parent = item_exists(p, node, node_id);
323: if (parent < 0)
324: goto no_parent;
325:
326: r = dbi_conn_queryf(db_conn,
327: "SELECT id FROM items WHERE node = %d AND " \
328: "name = '%s' AND handle = %d AND parent = %d",
329: node_id, it->i_name, it->i_handle, parent);
330: goto skip;
331: }
332:
333: no_parent:
334: r = dbi_conn_queryf(db_conn,
335: "SELECT id FROM items WHERE node = %d AND " \
336: "name = '%s' AND handle = %d", node_id, it->i_name, it->i_handle);
337:
338: skip:
339: if (r) {
340: if (dbi_result_first_row(r) == 1)
341: ret = (int) dbi_result_get_long(r, "id");
342: dbi_result_free(r);
343: }
344:
345: return ret;
346: }
347:
348: static inline int insert_item(item_t *it, node_t *node, int node_id)
349: {
350: dbi_result r;
351:
352: if (it->i_flags & ITEM_FLAG_IS_CHILD) {
353: int parent;
354: item_t *p = get_item(node, it->i_parent);
355: if (!p)
356: goto no_parent;
357:
358: parent = item_exists(p, node, node_id);
359: if (parent < 0)
360: goto no_parent;
361:
362: r = dbi_conn_queryf(db_conn,
363: "INSERT INTO items (name, node, handle, parent, " \
364: "indent, description) VALUES ('%s', %d, %d, %d, " \
365: "%d, %s%s%s)",
366: it->i_name, node_id, it->i_handle,
367: parent, it->i_level,
368: it->i_desc ? "'" : "",
369: it->i_desc ? it->i_desc : "NULL",
370: it->i_desc ? "'" : "");
371: goto skip;
372: }
373:
374: no_parent:
375: r = dbi_conn_queryf(db_conn,
376: "INSERT INTO items (name, node, handle) VALUES " \
377: "('%s', %d, %d)", it->i_name, node_id, it->i_handle);
378:
379: skip:
380: if (!r)
381: return -1;
382:
383: dbi_result_free(r);
384: return item_exists(it, node, node_id);
385: }
386:
387: static inline int up_item(item_t *it, int item_id)
388: {
389: dbi_result r;
390:
391: r = dbi_conn_queryf(db_conn,
392: "UPDATE attrs SET rx_usage = %d, tx_usage = %d " \
393: "WHERE id = %d",
394: it->i_rx_usage, it->i_tx_usage, item_id);
395:
396: if (!r)
397: return -1;
398:
399: dbi_result_free(r);
400:
401: return 0;
402: }
403:
404: static inline int node_exists(const char *name)
405: {
406: int ret = -1;
407: dbi_result r = dbi_conn_queryf(db_conn,
408: "SELECT id FROM nodes WHERE name = '%s'", name);
409:
410: if (r) {
411: if (dbi_result_first_row(r) == 1)
412: ret = (int) dbi_result_get_long(r, "id");
413: dbi_result_free(r);
414: }
415:
416: return ret;
417: }
418:
419: static inline int insert_node(const char *name, const char *source)
420: {
421: dbi_result r;
422:
423: if (source)
424: r = dbi_conn_queryf(db_conn,
425: "INSERT INTO nodes (name, source) VALUES ('%s', '%s')",
426: name, source);
427: else
428: r = dbi_conn_queryf(db_conn,
429: "INSERT INTO nodes (name) VALUES ('%s')", name);
430:
431: if (!r)
432: return -1;
433:
434: dbi_result_free(r);
435:
436: return node_exists(name);
437:
438: }
439:
440: static inline int cur_hist_index(int index)
441: {
442: return index == 0 ? (HISTORY_SIZE - 1) : (index - 1);
443: }
444:
445: struct xdata {
446: int node_id;
447: node_t *node;
448: };
449:
450: static void write_per_attr(stat_attr_t *a, void *arg)
451: {
452: int attr_id;
453: int item_id = (int) arg;
454:
455: attr_id = attr_exists(type2name(a->a_type), item_id);
456: if (attr_id < 0) {
457: attr_id = insert_attr(a, item_id);
458: if (attr_id < 0)
459: return;
460: } else
461: up_attr(a, attr_id);
462:
463: if (a->a_flags & ATTR_FLAG_HISTORY) {
464: history_t *h = &((stat_attr_hist_t *) a)->a_hist;
465:
466: if (do_read && get_read_interval() != 1.0f) {
467: int idx = cur_hist_index(h->h_read.he_index);
468: add_history("hist_r", attr_id,
469: &h->h_read.he_last_update,
470: h->h_read.he_rx.hd_data[idx],
471: h->h_read.he_tx.hd_data[idx]);
472: }
473:
474: if (do_sec && diff_now(&h->h_sec.he_last_update) < 0.5f) {
475: int idx = cur_hist_index(h->h_sec.he_index);
476: add_history("hist_s", attr_id,
477: &h->h_sec.he_last_update,
478: h->h_sec.he_rx.hd_data[idx],
479: h->h_sec.he_tx.hd_data[idx]);
480: }
481:
482: if (do_min && diff_now(&h->h_min.he_last_update) < 30.0f) {
483: int idx = cur_hist_index(h->h_min.he_index);
484: add_history("hist_m", attr_id,
485: &h->h_min.he_last_update,
486: h->h_min.he_rx.hd_data[idx],
487: h->h_min.he_tx.hd_data[idx]);
488: }
489:
490: if (do_hour && diff_now(&h->h_hour.he_last_update) < 1800.0f) {
491: int idx = cur_hist_index(h->h_hour.he_index);
492: add_history("hist_h", attr_id,
493: &h->h_hour.he_last_update,
494: h->h_hour.he_rx.hd_data[idx],
495: h->h_hour.he_tx.hd_data[idx]);
496: }
497:
498: if (do_day && diff_now(&h->h_day.he_last_update) < 43200.0f) {
499: int idx = cur_hist_index(h->h_day.he_index);
500: add_history("hist_d", attr_id,
501: &h->h_day.he_last_update,
502: h->h_day.he_rx.hd_data[idx],
503: h->h_day.he_tx.hd_data[idx]);
504: }
505: }
506: }
507:
508: static void write_per_item(item_t *item, void *arg)
509: {
510: int item_id;
511: struct xdata *x = (struct xdata *) arg;
512:
513: item_id = item_exists(item, x->node, x->node_id);
514: if (item_id < 0)
515: item_id = insert_item(item, x->node, x->node_id);
516:
517: if (item_id < 0)
518: return;
519:
520: up_item(item, item_id);
521:
522: foreach_attr(item, &write_per_attr, (void *) item_id);
523: }
524:
525: static void write_per_node(node_t *node, void *arg)
526: {
527: struct xdata x = { .node = node };
528:
529: x.node_id = node_exists(node->n_name);
530:
531: if (x.node_id < 0)
532: x.node_id = insert_node(node->n_name, node->n_from);
533:
534: if (x.node_id < 0)
535: return;
536:
537: foreach_item(node, write_per_item, &x);
538: }
539:
540: void db_draw(void)
541: {
542: static int rem = 1;
543:
544: if (--rem)
545: return;
546: else
547: rem = c_interval;
548:
549: foreach_node(write_per_node, NULL);
550: }
551:
552: static void print_module_help(void)
553: {
554: printf(
555: "DB - Database Output\n" \
556: "\n" \
557: " Writes current rate estimations into a database for\n" \
558: " other tools to pick up.\n" \
559: "\n" \
560: " Author: Thomas Graf <tgraf@suug.ch>\n" \
561: "\n" \
562: " Options:\n" \
563: " driverdir=DIR Directory containing libdi drivers\n" \
564: " driver=DRIVER DB driver (default: mysql)\n" \
565: " host=HOST Host the database is on (default: localhost)\n" \
566: " dbname=NAME Name of database (default: bmon)\n" \
567: " username=NAME Authentication username (default: bmon)\n" \
568: " password=TEXT Authentication password\n" \
569: " mask=MASK Write selection mask (default: mhd)\n" \
570: " interval=SEC Update interval in seconds (default: 3)\n" \
571: "\n" \
572: " Mask Attributes:\n" \
573: " r Read interval\n" \
574: " s Seconds\n" \
575: " m Minutes\n" \
576: " h Hours\n" \
577: " d Days\n" \
578: "\n" \
579: " Examples:\n" \
580: " -O 'db:password=bmon;mask=rmhd'");
581: }
582:
583: static void db_set_opts(tv_t *attrs)
584: {
585: while (attrs) {
586: if (!strcasecmp(attrs->type, "driverdir") && attrs->value)
587: c_driverdir = attrs->value;
588: else if (!strcasecmp(attrs->type, "driver") && attrs->value)
589: c_driver = attrs->value;
590: else if (!strcasecmp(attrs->type, "host") && attrs->value)
591: c_host = attrs->value;
592: else if (!strcasecmp(attrs->type, "username") && attrs->value)
593: c_username = attrs->value;
594: else if (!strcasecmp(attrs->type, "dbname") && attrs->value)
595: c_dbname = attrs->value;
596: else if (!strcasecmp(attrs->type, "password") && attrs->value)
597: c_password = attrs->value;
598: else if (!strcasecmp(attrs->type, "mask") && attrs->value)
599: c_mask = attrs->value;
600: else if (!strcasecmp(attrs->type, "interval") && attrs->value)
601: c_interval = strtol(attrs->value, NULL, 0);
602: else if (!strcasecmp(attrs->type, "help")) {
603: print_module_help();
604: exit(0);
605: }
606:
607: attrs = attrs->next;
608: }
609: }
610:
611: static int db_probe(void)
612: {
613: if (strchr(c_mask, 'r'))
614: do_read = 1;
615:
616: if (strchr(c_mask, 's'))
617: do_sec = 1;
618:
619: if (strchr(c_mask, 'm'))
620: do_min = 1;
621:
622: if (strchr(c_mask, 'h'))
623: do_hour = 1;
624:
625: if (strchr(c_mask, 'd'))
626: do_day = 1;
627:
628: if (c_password == NULL) {
629: fprintf(stderr, "You must specify the database password\n");
630: return 0;
631: }
632:
633: if (dbi_initialize(c_driverdir) < 0) {
634: fprintf(stderr, "Cannot initialize DBI layer\n");
635: return 0;
636: }
637:
638: db_conn = dbi_conn_new(c_driver);
639: if (db_conn == NULL) {
640: fprintf(stderr, "Cannot initialize connection \"%s\"\n",
641: c_driver);
642: return 0;
643: }
644:
645: dbi_conn_set_option(db_conn, "host", c_host);
646: dbi_conn_set_option(db_conn, "username", c_username);
647: dbi_conn_set_option(db_conn, "password", c_password);
648: dbi_conn_set_option(db_conn, "dbname", c_dbname);
649:
650: if (dbi_conn_connect(db_conn) < 0) {
651: fprintf(stderr, "Cannot open database \"%s\" connection on " \
652: "%s@%s\n", c_dbname, c_username, c_host);
653: return 0;
654: }
655:
656: if (!create_tables()) {
657: fprintf(stderr, "Could not create tables\n");
658: return 0;
659: }
660:
661: if (!insert_attr_descs()) {
662: fprintf(stderr, "Could not insert attribute descriptions\n");
663: return 0;
664: }
665:
666: return 1;
667: }
668:
669: static void db_shutdown(void)
670: {
671: if (db_conn)
672: dbi_conn_close(db_conn);
673:
674: dbi_shutdown();
675: }
676:
677: static struct output_module db_ops = {
678: .om_name = "db",
679: .om_draw = db_draw,
680: .om_set_opts = db_set_opts,
681: .om_probe = db_probe,
682: .om_shutdown db_shutdown,
683: };
684:
685: static void __init db_init(void)
686: {
687: register_secondary_output_module(&db_ops);
688: }
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to out_db.c CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / bmon / src