Need more space for bigger sql queries
[pulsecounter.git] / linux / dbstore.c
1 #include <stdio.h>
2 #include <stdlib.h>
3 #include <stdint.h>
4 #include <time.h>
5 #include <ctype.h>
6 #include <string.h>
7
8 #include <glib.h>
9
10 #include <mysql/mysql.h>
11
12 #include "dbstore.h"
13
14 static char *host = NULL;
15 static char *user = NULL;
16 static char *pass = NULL;
17 static char *dbnm = "watermeter";
18
19 int dbconfig(char *conffile)
20 {
21         FILE *fp = fopen(conffile, "r");
22         int lc = 0;
23         int rc = 0;
24         char buf[128];
25
26         if (!fp) return 1;
27         while (fgets(buf, sizeof(buf), fp)) {
28                 char *k, *v, *e;
29
30                 lc++;
31                 e = buf + strlen(buf) - 1;
32                 if (*e == '\n') *e = '\0';
33                 else {
34                         g_warning("%s:%d line too long", conffile, lc);
35                         rc = 1;
36                         break;
37                 }
38                 if ((k = strchr(buf, '#'))) {
39                         e=k;
40                         *e = '\0';
41                 }
42                 for (k = buf; k < e && isspace(*k); k++) /*nothing*/ ;
43                 for (v = k; v < e && !isspace(*v)
44                             && *v != ':' && *v != '='; v++) /*nothing*/ ;
45                 *v++ = '\0';
46                 if (*k == '\0') continue; /* empty or comment-only line */
47                 for (; v < e && (isspace(*v) || *v == ':' || *v == '=')
48                                                         ; v++) /*nothing*/ ;
49                 if (v >= e) {
50                         g_warning("%s:%d no value for key \"%s\"",
51                                         conffile, lc, k);
52                         rc = 1;
53                         break;
54                 } 
55 #ifdef TEST_CONFIG
56                 printf("k=%s v=%s\n", k, v);
57 #endif
58                 if      (!strcmp(k, "host"))     host = strdup(v);
59                 else if (!strcmp(k, "user"))     user = strdup(v);
60                 else if (!strcmp(k, "password")) pass = strdup(v);
61                 else if (!strcmp(k, "database")) dbnm = strdup(v);
62                 else {
63                         g_warning("%s:%d unrecognized key \"%s\"",
64                                         conffile, lc, k);
65                         rc = 1;
66                         break;
67                 }
68         }
69         return rc;
70 }
71
72 int dbstore(uint8_t which, uint32_t val)
73 {
74         time_t t;
75         int i;
76         MYSQL mysql;
77         int rc = 0;
78         struct tm tm;
79         char buf[64];
80         char tstr[32], prevtstr[32];
81         char *table = (which == 1) ? "cold" : "hot";
82         char statement[256];
83         MYSQL_RES *result;
84         uint32_t prev_val = 0;
85         int bogus = 0;
86
87         t = time(NULL);
88         (void)gmtime_r(&t, &tm);
89         (void)strftime(tstr, sizeof(tstr), "%Y-%m-%d %H:%M:%S", &tm);
90         mysql_init(&mysql);
91         if(!mysql_real_connect(&mysql, host, user, pass, dbnm, 0, NULL, 0)) {
92                 g_warning("mysql connect error: %s\n", mysql_error(&mysql));
93                 return 1;
94         }
95         mysql_autocommit(&mysql, FALSE);
96         /* ==== Was the sensor reset since last measurement? ==== */
97         snprintf(statement, sizeof(statement),
98                  "select value from %scnt order by timestamp desc limit 1;\n",
99                  table);
100         if ((rc = mysql_query(&mysql, statement)))
101                 g_warning("mysql \"%s\" error: %s\n",
102                                 statement, mysql_error(&mysql));
103         else if ((result = mysql_store_result(&mysql))){
104                 MYSQL_ROW row = mysql_fetch_row(result);
105                 if (row && *row) prev_val = atoi(*row);
106                 mysql_free_result(result);
107         }
108         if (val < prev_val) {
109                 snprintf(statement, sizeof(statement),
110                          "insert into %sadj values (\"%s\",%u);\n",
111                          table, tstr, prev_val);
112                 g_info("%s %u <= %u, %s", table, val, prev_val, statement);
113                 if ((rc = mysql_query(&mysql, statement)))
114                         g_warning("mysql \"%s\" error: %s\n",
115                                         statement, mysql_error(&mysql));
116         }
117         /* ==== Is this a part of a series of bogus events? ==== */
118         snprintf(statement, sizeof(statement),
119                  "select timestamp from %scnt "
120                  "where timestamp > subtime(\"%s\", \"0 0:0:10\") "
121                  "order by timestamp desc;\n",
122                  table, tstr);
123         if ((rc = mysql_query(&mysql, statement)))
124                 g_warning("mysql \"%s\" error: %s\n",
125                                 statement, mysql_error(&mysql));
126         else if ((result = mysql_store_result(&mysql))){
127                 my_ulonglong rows = mysql_num_rows(result);
128
129                 if (rows > 0) {
130                         bogus = 1;
131                         MYSQL_ROW row = mysql_fetch_row(result);
132                         if (row && *row) {
133                                 strncpy(prevtstr, *row, sizeof(prevtstr));
134                         } else {
135                                 g_warning("mysql_fetch_row after \"%s\": "
136                                           "no result despite rows=%llu\n",
137                                           statement, rows);
138                                 bogus = -1;
139                         }
140                 }
141                 if (rows == 1)
142                         bogus = 2;
143
144                 mysql_free_result(result);
145         }
146         if (bogus > 0) {
147                 snprintf(statement, sizeof(statement),
148                          "insert into %sadj values (\"%s\", -1);\n",
149                          table, tstr);
150                 g_info("%s %u at \"%s\" bogus, %s",
151                         table, val, tstr, statement);
152                 if ((rc = mysql_query(&mysql, statement)))
153                         g_warning("mysql \"%s\" error: %s\n",
154                                         statement, mysql_error(&mysql));
155         }
156         if (bogus > 1) {
157                 snprintf(statement, sizeof(statement),
158                          "insert into %sadj values (\"%s\", -1);\n",
159                          table, prevtstr);
160                 g_info("previous %s at \"%s\" was bogus too, %s",
161                         table, prevtstr, statement);
162                 if ((rc = mysql_query(&mysql, statement)))
163                         g_warning("mysql \"%s\" error: %s\n",
164                                         statement, mysql_error(&mysql));
165         }
166         /* ==== Update the counter table regardless ==== */
167         snprintf(statement, sizeof(statement),
168                  "insert into %scnt values (\"%s\",%u);\n",
169                  table, tstr, val);
170         if ((rc = mysql_query(&mysql, statement)))
171                 g_warning("mysql \"%s\" error: %s\n",
172                                 statement, mysql_error(&mysql));
173         /* ======== */
174         if (!rc) {
175                 if ((rc = mysql_commit(&mysql)))
176                         g_warning("mysql commit error: %s\n",
177                                         mysql_error(&mysql));
178         }
179         mysql_close(&mysql);
180         return rc;
181 }
182
183 #ifdef TEST_CONFIG
184 int main(int const argc, char *argv[])
185 {
186         if (dbconfig(argv[1])) {
187                 printf("could not parse config file\n");
188                 return 1;
189         }
190         printf("host: %s\nuser: %s\npass: %s\ndbnm: %s\n",
191                 host, user, pass, dbnm);
192         return 0;
193 }
194 #endif