?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
Translation update done using Pootle.
[gammu.git] / smsd / services / sql.c
blobe25591756ffb85bc550f7035f44918e856a04310
1 /**
2 * libsql database service
3 *
4 * Part of Gammu project
5 *
6 * Copyright (C) 2009-2011 Michal Čihař
7 * (c) 2010 Miloslav Semler
8 *
9 * Licensed under GNU GPL version 2 or later
10 */
11
12 #define _XOPEN_SOURCE
13 #define _BSD_SOURCE
14 #include <time.h>
15 #include <gammu.h>
16 #include "../../helper/strptime.h"
17
18 #include <stdarg.h>
19 #include <stdlib.h>
20 #include <string.h>
21 #include <stdio.h>
22 #include <errno.h>
23 #include <time.h>
24 #include <assert.h>
25 #ifdef WIN32
26 #include <windows.h>
27 #endif
28
29 #include "../core.h"
30 #include "../../helper/string.h"
31
32 /* FIXME: I know this is broken, need to figure out better way */
33 const char now_plus_odbc[] = "{fn CURRENT_TIMESTAMP()} + INTERVAL %d SECOND";
34 const char now_plus_mysql[] = "(NOW() + INTERVAL %d SECOND) + 0";
35 const char now_plus_pgsql[] = "now() + interval '%d seconds'";
36 const char now_plus_sqlite[] = "datetime('now', '+%d seconds')";
37 const char now_plus_freetds[] = "DATEADD('second', %d, CURRENT_TIMESTAMP)";
38 const char now_plus_access[] = "now()+#00:00:%d#";
39 const char now_plus_fallback[] = "NOW() + INTERVAL %d SECOND";
40
41 /* configurable SQL queries */
42 char * SMSDSQL_queries[SQL_QUERY_LAST_NO];
43
44 static const char *SMSDSQL_NowPlus(GSM_SMSDConfig * Config, int seconds)
45 {
46 const char *driver_name;
47 static char result[100];
48
49 if (Config->sql != NULL) {
50 driver_name = Config->sql;
51 } else {
52 driver_name = Config->driver;
53 }
54
55 if (strcasecmp(driver_name, "mysql") == 0 || strcasecmp(driver_name, "native_mysql") == 0) {
56 sprintf(result, now_plus_mysql, seconds);
57 } else if (strcasecmp(driver_name, "pgsql") == 0 || strcasecmp(driver_name, "native_pgsql") == 0) {
58 sprintf(result, now_plus_pgsql, seconds);
59 } else if (strncasecmp(driver_name, "sqlite", 6) == 0) {
60 sprintf(result, now_plus_sqlite, seconds);
61 } else if (strcasecmp(driver_name, "freetds") == 0) {
62 sprintf(result, now_plus_freetds, seconds);
63 } else if (strcasecmp(driver_name, "access") == 0) {
64 sprintf(result, now_plus_access, seconds);
65 } else if (strcasecmp(driver_name, "odbc") == 0) {
66 sprintf(result, now_plus_odbc, seconds);
67 } else {
68 sprintf(result, now_plus_fallback, seconds);
69 }
70 return result;
71 }
72
73 const char escape_char_odbc[] = "";
74 const char escape_char_mysql[] = "`";
75 const char escape_char_pgsql[] = "\"";
76 const char escape_char_sqlite[] = "";
77 const char escape_char_freetds[] = "";
78 const char escape_char_fallback[] = "";
79
80 static const char *SMSDSQL_EscapeChar(GSM_SMSDConfig * Config)
81 {
82 const char *driver_name;
83
84 if (Config->sql != NULL) {
85 driver_name = Config->sql;
86 } else {
87 driver_name = Config->driver;
88 }
89
90 if (strcasecmp(driver_name, "mysql") == 0 || strcasecmp(driver_name, "native_mysql") == 0) {
91 return escape_char_mysql;
92 } else if (strcasecmp(driver_name, "pgsql") == 0 || strcasecmp(driver_name, "native_pgsql") == 0) {
93 return escape_char_pgsql;
94 } else if (strncasecmp(driver_name, "sqlite", 6) == 0) {
95 return escape_char_sqlite;
96 } else if (strcasecmp(driver_name, "freetds") == 0 || strcasecmp(driver_name, "mssql") == 0 || strcasecmp(driver_name, "sybase") == 0) {
97 return escape_char_freetds;
98 } else if (strcasecmp(Config->driver, "odbc") == 0) {
99 return escape_char_odbc;
100 } else {
101 return escape_char_fallback;
102 }
103 }
104
105 const char top_clause_access[] = "TOP";
106 const char top_clause_fallback[] = "";
107
108 static const char *SMSDSQL_TopClause(GSM_SMSDConfig * Config, const char *count)
109 {
110 const char *driver_name;
111 static char result[100];
112
113 if (Config->sql != NULL) {
114 driver_name = Config->sql;
115 } else {
116 driver_name = Config->driver;
117 }
118
119 if (strcasecmp(driver_name, "access") == 0) {
120 strcpy(result, top_clause_access);
121 strcat(result, " ");
122 strcat(result, count);
123 return result;
124 } else {
125 return top_clause_fallback;
126 }
127 }
128
129 const char limit_clause_access[] = "";
130 const char limit_clause_fallback[] = "LIMIT";
131
132 static const char *SMSDSQL_LimitClause(GSM_SMSDConfig * Config, const char *count)
133 {
134 const char *driver_name;
135 static char result[100];
136
137 if (Config->sql != NULL) {
138 driver_name = Config->sql;
139 } else {
140 driver_name = Config->driver;
141 }
142
143 if (strcasecmp(driver_name, "access") == 0) {
144 return limit_clause_access;
145 } else {
146 strcpy(result, limit_clause_fallback);
147 strcat(result, " ");
148 strcat(result, count);
149 return result;
150 }
151 }
152
153 const char now_odbc[] = "{fn CURRENT_TIMESTAMP()}";
154 const char now_mysql[] = "NOW()";
155 const char now_pgsql[] = "now()";
156 const char now_sqlite[] = "datetime('now')";
157 const char now_freetds[] = "CURRENT_TIMESTAMP";
158 const char now_access[] = "now()";
159 const char now_fallback[] = "NOW()";
160
161 const char currtime_odbc[] = "{fn CURTIME()}";
162 const char currtime_mysql[] = "CURTIME()";
163 const char currtime_pgsql[] = "localtime";
164 const char currtime_sqlite[] = "time('now')";
165 const char currtime_freetds[] = "CURRENT_TIME";
166 const char currtime_fallback[] = "CURTIME()";
167
168 static const char *SMSDSQL_CurrentTime(GSM_SMSDConfig * Config)
169 {
170 const char *driver_name;
171
172 if (Config->sql != NULL) {
173 driver_name = Config->sql;
174 } else {
175 driver_name = Config->driver;
176 }
177
178 if (strcasecmp(driver_name, "mysql") == 0 || strcasecmp(driver_name, "native_mysql") == 0) {
179 return currtime_mysql;
180 } else if (strcasecmp(driver_name, "pgsql") == 0 || strcasecmp(driver_name, "native_pgsql") == 0) {
181 return currtime_pgsql;
182 } else if (strncasecmp(driver_name, "sqlite", 6) == 0) {
183 return currtime_sqlite;
184 } else if (strcasecmp(driver_name, "freetds") == 0 || strcasecmp(driver_name, "mssql") == 0 || strcasecmp(driver_name, "sybase") == 0) {
185 return currtime_freetds;
186 } else if (strcasecmp(Config->driver, "odbc") == 0) {
187 return currtime_odbc;
188 } else {
189 return currtime_fallback;
190 }
191 }
192 static const char *SMSDSQL_Now(GSM_SMSDConfig * Config)
193 {
194 const char *driver_name;
195
196 if (Config->sql != NULL) {
197 driver_name = Config->sql;
198 } else {
199 driver_name = Config->driver;
200 }
201
202 if (strcasecmp(driver_name, "mysql") == 0 || strcasecmp(driver_name, "native_mysql") == 0) {
203 return now_mysql;
204 } else if (strcasecmp(driver_name, "pgsql") == 0 || strcasecmp(driver_name, "native_pgsql") == 0) {
205 return now_pgsql;
206 } else if (strncasecmp(driver_name, "sqlite", 6) == 0) {
207 return now_sqlite;
208 } else if (strcasecmp(driver_name, "freetds") == 0 || strcasecmp(driver_name, "mssql") == 0 || strcasecmp(driver_name, "sybase") == 0) {
209 return now_freetds;
210 } else if (strcasecmp(Config->driver, "access") == 0) {
211 return now_access;
212 } else if (strcasecmp(Config->driver, "odbc") == 0) {
213 return now_odbc;
214 } else {
215 return now_fallback;
216 }
217 }
218 static GSM_Error SMSDSQL_Query(GSM_SMSDConfig * Config, const char *query, SQL_result * res)
219 {
220 SQL_Error error;
221 int attempts = 1;
222 struct GSM_SMSDdbobj *db = Config->db;
223
224 for (attempts = 1; attempts < Config->backend_retries; attempts++) {
225 SMSD_Log(DEBUG_SQL, Config, "Execute SQL: %s", query);
226 error = db->Query(Config, query, res);
227 if (error == SQL_OK)
228 return ERR_NONE;
229
230 if (error != SQL_TIMEOUT){
231 SMSD_Log(DEBUG_INFO, Config, "SQL failure: %d", error);
232 sleep(attempts * attempts);
233 continue;
234 }
235
236 SMSD_Log(DEBUG_INFO, Config, "SQL failed (timeout): %s", query);
237 if (attempts >= Config->backend_retries) {
238 return ERR_TIMEOUT;
239 }
240 /* We will try to reconnect */
241 SMSD_Log(DEBUG_INFO, Config, "reconnecting to database!");
242 error = SQL_TIMEOUT;
243 while (error != SQL_OK && attempts <= Config->backend_retries) {
244 SMSD_Log(DEBUG_INFO, Config, "Reconnecting after %d seconds...", attempts * attempts);
245 sleep(attempts * attempts);
246 db->Free(Config);
247 error = db->Connect(Config);
248 attempts++;
249 }
250 }
251 return ERR_TIMEOUT;
252 }
253
254 void SMSDSQL_Time2String(GSM_SMSDConfig * Config, time_t timestamp, char *static_buff, size_t size)
255 {
256 struct tm *timestruct;
257 const char *driver_name;
258
259 if (Config->sql != NULL) {
260 driver_name = Config->sql;
261 } else {
262 driver_name = Config->driver;
263 }
264
265 if (timestamp == -2) {
266 strcpy(static_buff, "0000-00-00 00:00:00");
267 } else if (strcasecmp(driver_name, "pgsql") == 0 || strcasecmp(driver_name, "native_pgsql") == 0) {
268 timestruct = gmtime(&timestamp);
269 strftime(static_buff, size, "%Y-%m-%d %H:%M:%S GMT", timestruct);
270 } else if (strcasecmp(driver_name, "access") == 0) {
271 timestruct = gmtime(&timestamp);
272 strftime(static_buff, size, "'%Y-%m-%d %H:%M:%S'", timestruct);
273 } else if (strcasecmp(Config->driver, "odbc") == 0) {
274 timestruct = gmtime(&timestamp);
275 strftime(static_buff, size, "{ ts '%Y-%m-%d %H:%M:%S' }", timestruct);
276 } else {
277 timestruct = localtime(&timestamp);
278 strftime(static_buff, size, "%Y-%m-%d %H:%M:%S", timestruct);
279 }
280 }
281
282 static GSM_Error SMSDSQL_NamedQuery(GSM_SMSDConfig * Config, const char *sql_query, GSM_SMSMessage *sms,
283 const SQL_Var *params, SQL_result * res)
284 {
285 char buff[65536], *ptr, c, static_buff[8192];
286 char *buffer2, *end;
287 const char *to_print, *q = sql_query;
288 int int_to_print;
289 int numeric;
290 int n, argc = 0;
291 struct GSM_SMSDdbobj *db = Config->db;
292
293 if (params != NULL) {
294 while (params[argc].type != SQL_TYPE_NONE) argc++;
295 }
296
297 ptr = buff;
298
299 do {
300 if (*q != '%') {
301 *ptr++ = *q;
302 continue;
303 }
304 c = *(++q);
305 if( c >= '0' && c <= '9'){
306 n = strtoul(q, &end, 10) - 1;
307 if (n < argc && n >= 0) {
308 switch(params[n].type){
309 case SQL_TYPE_INT:
310 ptr += sprintf(ptr, "%i", params[n].v.i);
311 break;
312 case SQL_TYPE_STRING:
313 buffer2 = db->QuoteString(Config, params[n].v.s);
314 memcpy(ptr, buffer2, strlen(buffer2));
315 ptr += strlen(buffer2);
316 free(buffer2);
317 break;
318 default:
319 SMSD_Log(DEBUG_ERROR, Config, "SQL: unknown type: %i (application bug) in query: `%s`", params[n].type, sql_query);
320 return ERR_BUG;
321 break;
322 }
323 } else {
324 SMSD_Log(DEBUG_ERROR, Config, "SQL: wrong number of parameter: %i (max %i) in query: `%s`", n+1, argc, sql_query);
325 return ERR_UNKNOWN;
326 }
327 q = end - 1;
328 continue;
329 }
330 numeric = 0;
331 to_print = NULL;
332 switch (c) {
333 case 'I':
334 to_print = Config->Status->IMEI;
335 break;
336 case 'P':
337 to_print = Config->PhoneID;
338 break;
339 case 'N':
340 snprintf(static_buff, sizeof(static_buff), "Gammu %s, %s, %s", GAMMU_VERSION, GetOS(), GetCompiler());
341 to_print = static_buff;
342 break;
343 case 'A':
344 to_print = Config->CreatorID;
345 break;
346 default:
347 if (sms != NULL) {
348 switch (c) {
349 case 'R':
350 EncodeUTF8(static_buff, sms->Number);
351 to_print = static_buff;
352 break;
353 case 'F':
354 EncodeUTF8(static_buff, sms->SMSC.Number);
355 to_print = static_buff;
356 break;
357 case 'u':
358 if (sms->UDH.Type != UDH_NoUDH) {
359 EncodeHexBin(static_buff, sms->UDH.Text, sms->UDH.Length);
360 to_print = static_buff;
361 }else{
362 to_print = "";
363 }
364 break;
365 case 'x':
366 int_to_print = sms->Class;
367 numeric = 1;
368 break;
369 case 'c':
370 to_print = GSM_SMSCodingToString(sms->Coding);
371 break;
372 case 't':
373 int_to_print = sms->MessageReference;
374 numeric = 1;
375 break;
376 case 'E':
377 switch (sms->Coding) {
378 case SMS_Coding_Unicode_No_Compression:
379 case SMS_Coding_Default_No_Compression:
380 EncodeHexUnicode(static_buff, sms->Text, UnicodeLength(sms->Text));
381 break;
382 case SMS_Coding_8bit:
383 EncodeHexBin(static_buff, sms->Text, sms->Length);
384 break;
385 default:
386 *static_buff = '\0';
387 break;
388 }
389 to_print = static_buff;
390 break;
391 case 'T':
392 switch (sms->Coding) {
393 case SMS_Coding_Unicode_No_Compression:
394 case SMS_Coding_Default_No_Compression:
395 EncodeUTF8(static_buff, sms->Text);
396 to_print = static_buff;
397 break;
398 default:
399 to_print = "";
400 break;
401 }
402 break;
403 case 'V':
404 if (sms->SMSC.Validity.Format == SMS_Validity_RelativeFormat) {
405 int_to_print = sms->SMSC.Validity.Relative;
406 } else {
407 int_to_print = -1;
408 }
409 numeric = 1;
410 break;
411 case 'C':
412 SMSDSQL_Time2String(Config, Fill_Time_T(sms->SMSCTime), static_buff, sizeof(static_buff));
413 to_print = static_buff;
414 break;
415 case 'd':
416 SMSDSQL_Time2String(Config, Fill_Time_T(sms->DateTime), static_buff, sizeof(static_buff));
417 to_print = static_buff;
418 break;
419 case 'e':
420 int_to_print = sms->DeliveryStatus;
421 numeric = 1;
422 break;
423 default:
424 SMSD_Log(DEBUG_ERROR, Config, "SQL: uexpected char '%c' in query: %s", c, sql_query);
425 return ERR_UNKNOWN;
426
427 } /* end of switch */
428 } else {
429 SMSD_Log(DEBUG_ERROR, Config, "Syntax error in query.. uexpected char '%c' in query: %s", c, sql_query);
430 return ERR_UNKNOWN;
431 }
432 break;
433 } /* end of switch */
434 if (numeric) {
435 ptr += sprintf(ptr, "%i", int_to_print);
436 } else if (to_print != NULL) {
437 buffer2 = db->QuoteString(Config, to_print);
438 memcpy(ptr, buffer2, strlen(buffer2));
439 ptr += strlen(buffer2);
440 free(buffer2);
441 } else {
442 memcpy(ptr, "NULL", 4);
443 ptr += 4;
444 }
445 } while (*(++q) != '\0');
446 *ptr = '\0';
447 return SMSDSQL_Query(Config, buff, res);
448
449 }
450
451 static GSM_Error SMSDSQL_CheckTable(GSM_SMSDConfig * Config, const char *table)
452 {
453 SQL_result res;
454 char buffer[200];
455 GSM_Error error;
456 struct GSM_SMSDdbobj *db = Config->db;
457 const char *escape_char;
458
459 escape_char = SMSDSQL_EscapeChar(Config);
460
461 sprintf(buffer, "SELECT %s %sID%s FROM %s %s", SMSDSQL_TopClause(Config, "1"), escape_char, escape_char, table, SMSDSQL_LimitClause(Config, "1"));
462 error = SMSDSQL_Query(Config, buffer, &res);
463 if (error != ERR_NONE) {
464 SMSD_Log(DEBUG_ERROR, Config, "Table %s not found, disconnecting!", table);
465 db->Free(Config);
466 return ERR_UNKNOWN;
467 }
468 db->FreeResult(Config, &res);
469 return ERR_NONE;
470 }
471
472 /* Disconnects from a database */
473 static GSM_Error SMSDSQL_Free(GSM_SMSDConfig * Config)
474 {
475 int i;
476 SMSD_Log(DEBUG_SQL, Config, "Disconnecting from SQL database.");
477 Config->db->Free(Config);
478 /* free configuration */
479 for(i = 0; i < SQL_QUERY_LAST_NO; i++){
480 free(SMSDSQL_queries[i]);
481 SMSDSQL_queries[i] = NULL;
482 }
483 return ERR_NONE;
484 }
485
486 /* Connects to database */
487 static GSM_Error SMSDSQL_Init(GSM_SMSDConfig * Config)
488 {
489 SQL_result res;
490 int version;
491 GSM_Error error;
492 struct GSM_SMSDdbobj *db;
493 const char *escape_char;
494 char buffer[100];
495
496 #ifdef WIN32
497 _tzset();
498 #else
499 tzset();
500 #endif
501
502 db = Config->db;
503
504 if (db->Connect(Config) != SQL_OK)
505 return ERR_UNKNOWN;
506
507 error = SMSDSQL_CheckTable(Config, "outbox");
508 if (error != ERR_NONE)
509 return error;
510 error = SMSDSQL_CheckTable(Config, "outbox_multipart");
511 if (error != ERR_NONE)
512 return error;
513 error = SMSDSQL_CheckTable(Config, "sentitems");
514 if (error != ERR_NONE)
515 return error;
516 error = SMSDSQL_CheckTable(Config, "inbox");
517 if (error != ERR_NONE)
518 return error;
519
520 escape_char = SMSDSQL_EscapeChar(Config);
521
522 sprintf(buffer, "SELECT %sVersion%s FROM gammu", escape_char, escape_char);
523 if (SMSDSQL_Query(Config, buffer, &res) != ERR_NONE) {
524 db->Free(Config);
525 return ERR_UNKNOWN;
526 }
527 if (db->NextRow(Config, &res) != 1) {
528 SMSD_Log(DEBUG_ERROR, Config, "Failed to seek to first row!");
529 db->FreeResult(Config, &res);
530 db->Free(Config);
531 return ERR_UNKNOWN;
532 }
533 version = db->GetNumber(Config, &res, 0);
534 db->FreeResult(Config, &res);
535 if (SMSD_CheckDBVersion(Config, version) != ERR_NONE) {
536 db->Free(Config);
537 return ERR_UNKNOWN;
538 }
539
540 SMSD_Log(DEBUG_INFO, Config, "Connected to Database %s: %s on %s", Config->driver, Config->database, Config->host);
541
542 return ERR_NONE;
543 }
544
545 static GSM_Error SMSDSQL_InitAfterConnect(GSM_SMSDConfig * Config)
546 {
547 SQL_result res;
548 struct GSM_SMSDdbobj *db = Config->db;
549 SQL_Var vars[3] = {{SQL_TYPE_STRING, {NULL}}, {SQL_TYPE_STRING, {NULL}}, {SQL_TYPE_NONE, {NULL}}};
550
551 if (SMSDSQL_NamedQuery(Config, SMSDSQL_queries[SQL_QUERY_DELETE_PHONE], NULL, NULL, &res) != ERR_NONE) {
552 SMSD_Log(DEBUG_INFO, Config, "Error deleting from database (%s)", __FUNCTION__);
553 return ERR_UNKNOWN;
554 }
555 db->FreeResult(Config, &res);
556
557 SMSD_Log(DEBUG_INFO, Config, "Inserting phone info");
558 vars[0].v.s = Config->enable_send ? "yes" : "no";
559 vars[1].v.s = Config->enable_receive ? "yes" : "no";
560
561 if (SMSDSQL_NamedQuery(Config, SMSDSQL_queries[SQL_QUERY_INSERT_PHONE], NULL, vars, &res) != ERR_NONE) {
562 SMSD_Log(DEBUG_INFO, Config, "Error inserting into database (%s)", __FUNCTION__);
563 return ERR_UNKNOWN;
564 }
565 db->FreeResult(Config, &res);
566
567 return ERR_NONE;
568 }
569
570 /* Save SMS from phone (called Inbox sms - it's in phone Inbox) somewhere */
571 static GSM_Error SMSDSQL_SaveInboxSMS(GSM_MultiSMSMessage * sms, GSM_SMSDConfig * Config, char **Locations)
572 {
573 SQL_result res, res2;
574 SQL_Var vars[3];
575 struct GSM_SMSDdbobj *db = Config->db;
576 const char *q, *status;
577
578 char smstext[3 * GSM_MAX_SMS_LENGTH + 1];
579 char destinationnumber[3 * GSM_MAX_NUMBER_LENGTH + 1];
580 char smsc_message[3 * GSM_MAX_NUMBER_LENGTH + 1];
581 int i;
582 time_t t_time1, t_time2;
583 gboolean found;
584 long diff;
585 unsigned long long new_id;
586 size_t locations_size = 0, locations_pos = 0;
587 const char *state, *smsc;
588
589 *Locations = NULL;
590
591 for (i = 0; i < sms->Number; i++) {
592 EncodeUTF8(destinationnumber, sms->SMS[i].Number);
593 EncodeUTF8(smsc_message, sms->SMS[i].SMSC.Number);
594 if (sms->SMS[i].PDU == SMS_Status_Report) {
595 EncodeUTF8(smstext, sms->SMS[i].Text);
596 SMSD_Log(DEBUG_INFO, Config, "Delivery report: %s to %s", smstext, destinationnumber);
597
598 if (SMSDSQL_NamedQuery(Config, SMSDSQL_queries[SQL_QUERY_SAVE_INBOX_SMS_SELECT], &sms->SMS[i], NULL, &res) != ERR_NONE) {
599 SMSD_Log(DEBUG_INFO, Config, "Error reading from database (%s)", __FUNCTION__);
600 return ERR_UNKNOWN;
601 }
602
603 found = FALSE;
604 while (db->NextRow(Config, &res)) {
605 smsc = db->GetString(Config, &res, 4);
606 state = db->GetString(Config, &res, 1);
607 SMSD_Log(DEBUG_NOTICE, Config, "Checking for delivery report, SMSC=%s, state=%s", smsc, state);
608
609 if (strcmp(smsc, smsc_message) != 0) {
610 if (Config->skipsmscnumber[0] == 0 || strcmp(Config->skipsmscnumber, smsc)) {
611 continue;
612 }
613 }
614
615 if (strcmp(state, "SendingOK") == 0 || strcmp(state, "DeliveryPending") == 0) {
616 t_time1 = db->GetDate(Config, &res, 2);
617 if (t_time1 < 0) {
618 SMSD_Log(DEBUG_ERROR, Config, "Invalid SendingDateTime -1 for SMS TPMR=%i", sms->SMS[i].MessageReference);
619 return ERR_UNKNOWN;
620 }
621 t_time2 = Fill_Time_T(sms->SMS[i].DateTime);
622 diff = t_time2 - t_time1;
623
624 if (diff > -Config->deliveryreportdelay && diff < Config->deliveryreportdelay) {
625 found = TRUE;
626 break;
627 } else {
628 SMSD_Log(DEBUG_NOTICE, Config,
629 "Delivery report would match, but time delta is too big (%ld), consider increasing DeliveryReportDelay", diff);
630 }
631 }
632 }
633
634 if (found) {
635 if (!strcmp(smstext, "Delivered")) {
636 q = SMSDSQL_queries[SQL_QUERY_SAVE_INBOX_SMS_UPDATE_DELIVERED];
637 } else {
638 q = SMSDSQL_queries[SQL_QUERY_SAVE_INBOX_SMS_UPDATE];
639 }
640
641 if (!strcmp(smstext, "Delivered")) {
642 status = "DeliveryOK";
643 } else if (!strcmp(smstext, "Failed")) {
644 status = "DeliveryFailed";
645 } else if (!strcmp(smstext, "Pending")) {
646 status = "DeliveryPending";
647 } else if (!strcmp(smstext, "Unknown")) {
648 status = "DeliveryUnknown";
649 } else {
650 status = "";
651 }
652
653 vars[0].type = SQL_TYPE_STRING;
654 vars[0].v.s = status; /* Status */
655 vars[1].type = SQL_TYPE_INT;
656 vars[1].v.i = (long)db->GetNumber(Config, &res, 0); /* ID */
657 vars[2].type = SQL_TYPE_NONE;
658
659 if (SMSDSQL_NamedQuery(Config, q, &sms->SMS[i], vars, &res2) != ERR_NONE) {
660 SMSD_Log(DEBUG_INFO, Config, "Error writing to database (%s)", __FUNCTION__);
661 return ERR_UNKNOWN;
662 }
663 db->FreeResult(Config, &res2);
664 }
665 db->FreeResult(Config, &res);
666 continue;
667 }
668
669 if (sms->SMS[i].PDU != SMS_Deliver)
670 continue;
671
672 if (SMSDSQL_NamedQuery(Config, SMSDSQL_queries[SQL_QUERY_SAVE_INBOX_SMS_INSERT], &sms->SMS[i], NULL, &res) != ERR_NONE) {
673 SMSD_Log(DEBUG_INFO, Config, "Error writing to database (%s)", __FUNCTION__);
674 return ERR_UNKNOWN;
675 }
676
677 new_id = db->SeqID(Config, "inbox_ID_seq");
678 if (new_id == 0) {
679 SMSD_Log(DEBUG_INFO, Config, "Failed to get inserted row ID (%s)", __FUNCTION__);
680 return ERR_UNKNOWN;
681 }
682 SMSD_Log(DEBUG_NOTICE, Config, "Inserted message id %lu", (long)new_id);
683
684 db->FreeResult(Config, &res);
685
686 if (new_id != 0) {
687 if (locations_pos + 10 >= locations_size) {
688 locations_size += 40;
689 *Locations = (char *)realloc(*Locations, locations_size);
690 assert(*Locations != NULL);
691 if (locations_pos == 0) {
692 *Locations[0] = 0;
693 }
694 }
695 locations_pos += sprintf((*Locations) + locations_pos, "%lu ", (long)new_id);
696 }
697
698 if (SMSDSQL_NamedQuery(Config, SMSDSQL_queries[SQL_QUERY_UPDATE_RECEIVED], &sms->SMS[i], NULL, &res2) != ERR_NONE) {
699 SMSD_Log(DEBUG_INFO, Config, "Error updating number of received messages (%s)", __FUNCTION__);
700 return ERR_UNKNOWN;
701 }
702 db->FreeResult(Config, &res2);
703
704 }
705
706 return ERR_NONE;
707 }
708
709 static GSM_Error SMSDSQL_RefreshSendStatus(GSM_SMSDConfig * Config, char *ID)
710 {
711 SQL_result res;
712 struct GSM_SMSDdbobj *db = Config->db;
713 SQL_Var vars[2] = {
714 {SQL_TYPE_STRING, {ID}},
715 {SQL_TYPE_NONE, {NULL}}};
716
717 if (SMSDSQL_NamedQuery(Config, SMSDSQL_queries[SQL_QUERY_REFRESH_SEND_STATUS], NULL, vars, &res) != ERR_NONE) {
718 SMSD_Log(DEBUG_INFO, Config, "Error writing to database (%s)", __FUNCTION__);
719 return ERR_UNKNOWN;
720 }
721
722 if (db->AffectedRows(Config, &res) == 0) {
723 db->FreeResult(Config, &res);
724 return ERR_UNKNOWN;
725 }
726
727 db->FreeResult(Config, &res);
728 return ERR_NONE;
729 }
730
731 /* Find one multi SMS to sending and return it (or return ERR_EMPTY)
732 * There is also set ID for SMS
733 */
734 static GSM_Error SMSDSQL_FindOutboxSMS(GSM_MultiSMSMessage * sms, GSM_SMSDConfig * Config, char *ID)
735 {
736 SQL_result res;
737 struct GSM_SMSDdbobj *db = Config->db;
738 int i;
739 gboolean found = FALSE;
740 time_t timestamp;
741 const char *coding;
742 const char *text;
743 const char *sender_id;
744 size_t text_len;
745 const char *text_decoded;
746 const char *destination;
747 const char *udh;
748 const char *q;
749 size_t udh_len;
750 unsigned int limit = 1;
751 SQL_Var vars[3];
752
753 /* Min. limit is 8 SMS, limit grows with higher loopsleep setting Max. limit is 30 messages.*/
754 limit = Config->loopsleep>1 ? Config->loopsleep * 4 : 8;
755 limit = limit>30 ? 30 : limit;
756
757 vars[0].type = SQL_TYPE_INT;
758 vars[0].v.i = limit;
759 vars[1].type = SQL_TYPE_NONE;
760
761 if (SMSDSQL_NamedQuery(Config, SMSDSQL_queries[SQL_QUERY_FIND_OUTBOX_SMS_ID], NULL, vars, &res) != ERR_NONE) {
762 SMSD_Log(DEBUG_INFO, Config, "Error reading from database (%s)", __FUNCTION__);
763 return ERR_UNKNOWN;
764 }
765
766 while (db->NextRow(Config, &res)) {
767 sprintf(ID, "%ld", (long)db->GetNumber(Config, &res, 0));
768 timestamp = db->GetDate(Config, &res, 1);
769 if (timestamp == -1) {
770 SMSD_Log(DEBUG_INFO, Config, "Invalid date for InsertIntoDB.");
771 return ERR_UNKNOWN;
772 }
773 SMSDSQL_Time2String(Config, timestamp, Config->DT, sizeof(Config->DT));
774 sender_id = db->GetString(Config, &res, 3);
775 if (sender_id == NULL || strlen(sender_id) == 0 || !strcmp(sender_id, Config->PhoneID)) {
776 if (SMSDSQL_RefreshSendStatus(Config, ID) == ERR_NONE) {
777 found = TRUE;
778 break;
779 }
780 }
781 }
782
783 db->FreeResult(Config, &res);
784
785 if (!found) {
786 return ERR_EMPTY;
787 }
788
789 sms->Number = 0;
790 for (i = 0; i < GSM_MAX_MULTI_SMS; i++) {
791 GSM_SetDefaultSMSData(&sms->SMS[i]);
792 sms->SMS[i].SMSC.Number[0] = 0;
793 sms->SMS[i].SMSC.Number[1] = 0;
794 }
795
796 for (i = 1; i < GSM_MAX_MULTI_SMS + 1; i++) {
797 vars[0].type = SQL_TYPE_STRING;
798 vars[0].v.s = ID;
799 vars[1].type = SQL_TYPE_INT;
800 vars[1].v.i = i;
801 vars[2].type = SQL_TYPE_NONE;
802 if (i == 1) {
803 q = SMSDSQL_queries[SQL_QUERY_FIND_OUTBOX_BODY];
804 } else {
805 q = SMSDSQL_queries[SQL_QUERY_FIND_OUTBOX_MULTIPART];
806 }
807 if (SMSDSQL_NamedQuery(Config, q, NULL, vars, &res) != ERR_NONE) {
808 SMSD_Log(DEBUG_ERROR, Config, "Error reading from database (%s)", __FUNCTION__);
809 return ERR_UNKNOWN;
810 }
811
812 if (db->NextRow(Config, &res) != 1) {
813 db->FreeResult(Config, &res);
814 return ERR_NONE;
815 }
816
817 coding = db->GetString(Config, &res, 1);
818 text = db->GetString(Config, &res, 0);
819 if (text == NULL) {
820 text_len = 0;
821 } else {
822 text_len = strlen(text);
823 }
824 text_decoded = db->GetString(Config, &res, 4);
825 udh = db->GetString(Config, &res, 2);
826 if (udh == NULL) {
827 udh_len = 0;
828 } else {
829 udh_len = strlen(udh);
830 }
831
832 sms->SMS[sms->Number].Coding = GSM_StringToSMSCoding(coding);
833 if (sms->SMS[sms->Number].Coding == 0) {
834 if (text == NULL || text_len == 0) {
835 SMSD_Log(DEBUG_NOTICE, Config, "Assuming default coding for text message");
836 sms->SMS[sms->Number].Coding = SMS_Coding_Default_No_Compression;
837 } else {
838 SMSD_Log(DEBUG_NOTICE, Config, "Assuming 8bit coding for binary message");
839 sms->SMS[sms->Number].Coding = SMS_Coding_8bit;
840 }
841 }
842
843 if (text == NULL || text_len == 0) {
844 if (text_decoded == NULL) {
845 SMSD_Log(DEBUG_ERROR, Config, "Message without text!");
846 return ERR_UNKNOWN;
847 } else {
848 SMSD_Log(DEBUG_NOTICE, Config, "Message: %s", text_decoded);
849 DecodeUTF8(sms->SMS[sms->Number].Text, text_decoded, strlen(text_decoded));
850 }
851 } else {
852 switch (sms->SMS[sms->Number].Coding) {
853 case SMS_Coding_Unicode_No_Compression:
854
855 case SMS_Coding_Default_No_Compression:
856 DecodeHexUnicode(sms->SMS[sms->Number].Text, text, text_len);
857 break;
858
859 case SMS_Coding_8bit:
860 DecodeHexBin(sms->SMS[sms->Number].Text, text, text_len);
861 sms->SMS[sms->Number].Length = text_len / 2;
862 break;
863
864 default:
865 break;
866 }
867 }
868
869 if (i == 1) {
870 destination = db->GetString(Config, &res, 6);
871 if (destination == NULL) {
872 SMSD_Log(DEBUG_ERROR, Config, "Message without recipient!");
873 return ERR_UNKNOWN;
874 }
875 DecodeUTF8(sms->SMS[sms->Number].Number, destination, strlen(destination));
876 } else {
877 CopyUnicodeString(sms->SMS[sms->Number].Number, sms->SMS[0].Number);
878 }
879
880 sms->SMS[sms->Number].UDH.Type = UDH_NoUDH;
881 if (udh != NULL && udh_len != 0) {
882 sms->SMS[sms->Number].UDH.Type = UDH_UserUDH;
883 sms->SMS[sms->Number].UDH.Length = udh_len / 2;
884 DecodeHexBin(sms->SMS[sms->Number].UDH.Text, udh, udh_len);
885 }
886
887 sms->SMS[sms->Number].Class = db->GetNumber(Config, &res, 3);
888 sms->SMS[sms->Number].PDU = SMS_Submit;
889 sms->Number++;
890
891 if (i == 1) {
892 strcpy(Config->CreatorID, db->GetString(Config, &res, 10));
893 Config->relativevalidity = db->GetNumber(Config, &res, 8);
894
895 Config->currdeliveryreport = db->GetBool(Config, &res, 9);
896
897 /* Is this a multipart message? */
898 if (!db->GetBool(Config, &res, 7)) {
899 db->FreeResult(Config, &res);
900 break;
901 }
902
903 }
904 db->FreeResult(Config, &res);
905 }
906
907 return ERR_NONE;
908 }
909
910 /* After sending SMS is moved to Sent Items or Error Items. */
911 static GSM_Error SMSDSQL_MoveSMS(GSM_MultiSMSMessage * sms UNUSED, GSM_SMSDConfig * Config, char *ID, gboolean alwaysDelete UNUSED, gboolean sent UNUSED)
912 {
913 SQL_result res;
914 SQL_Var vars[2];
915 struct GSM_SMSDdbobj *db = Config->db;
916
917 vars[0].type = SQL_TYPE_STRING;
918 vars[0].v.s = ID;
919 vars[1].type = SQL_TYPE_NONE;
920
921 if (SMSDSQL_NamedQuery(Config, SMSDSQL_queries[SQL_QUERY_DELETE_OUTBOX], NULL, vars, &res) != ERR_NONE) {
922 SMSD_Log(DEBUG_INFO, Config, "Error deleting from database (%s)", __FUNCTION__);
923 return ERR_UNKNOWN;
924 }
925 db->FreeResult(Config, &res);
926
927 if (SMSDSQL_NamedQuery(Config, SMSDSQL_queries[SQL_QUERY_DELETE_OUTBOX_MULTIPART], NULL, vars, &res) != ERR_NONE) {
928 SMSD_Log(DEBUG_INFO, Config, "Error deleting from database (%s)", __FUNCTION__);
929 return ERR_UNKNOWN;
930 }
931 db->FreeResult(Config, &res);
932
933 return ERR_NONE;
934 }
935
936 /* Adds SMS to Outbox */
937 static GSM_Error SMSDSQL_CreateOutboxSMS(GSM_MultiSMSMessage * sms, GSM_SMSDConfig * Config, char *NewID)
938 {
939 char creator[200];
940 int i;
941 unsigned int ID = 0;
942 SQL_result res;
943 SQL_Var vars[6];
944 struct GSM_SMSDdbobj *db = Config->db;
945 const char *report, *multipart, *q;
946
947 sprintf(creator, "Gammu %s",GAMMU_VERSION); /* %1 */
948 multipart = (sms->Number == 1) ? "FALSE" : "TRUE"; /* %3 */
949
950 for (i = 0; i < sms->Number; i++) {
951 report = (sms->SMS[i].PDU == SMS_Status_Report) ? "yes": "default"; /* %2 */
952 if (i == 0) {
953 q = SMSDSQL_queries[SQL_QUERY_CREATE_OUTBOX];
954 } else {
955 q = SMSDSQL_queries[SQL_QUERY_CREATE_OUTBOX_MULTIPART];
956 }
957
958 vars[0].type = SQL_TYPE_STRING;
959 vars[0].v.s = creator;
960 vars[1].type = SQL_TYPE_STRING;
961 vars[1].v.s = report;
962 vars[2].type = SQL_TYPE_STRING;
963 vars[2].v.s = multipart;
964 vars[3].type = SQL_TYPE_INT;
965 vars[3].v.i = i+1;
966 vars[4].type = SQL_TYPE_INT;
967 vars[4].v.i = ID;
968 vars[5].type = SQL_TYPE_NONE;
969
970 if (SMSDSQL_NamedQuery(Config, q, &sms->SMS[i], vars, &res) != ERR_NONE) {
971 SMSD_Log(DEBUG_INFO, Config, "Error writing to database (%s)", __FUNCTION__);
972 return ERR_UNKNOWN;
973 }
974 if (i == 0) {
975 ID = db->SeqID(Config, "outbox_ID_seq");
976 if (ID == 0) {
977 SMSD_Log(DEBUG_INFO, Config, "Failed to get inserted row ID (%s)", __FUNCTION__);
978 return ERR_UNKNOWN;
979 }
980 }
981 db->FreeResult(Config, &res);
982 }
983 SMSD_Log(DEBUG_INFO, Config, "Written message with ID %u", ID);
984 if (NewID != NULL)
985 sprintf(NewID, "%d", ID);
986 return ERR_NONE;
987 }
988
989 static GSM_Error SMSDSQL_AddSentSMSInfo(GSM_MultiSMSMessage * sms, GSM_SMSDConfig * Config, char *ID, int Part, GSM_SMSDSendingError err, int TPMR)
990 {
991 SQL_result res;
992 struct GSM_SMSDdbobj *db = Config->db;
993
994 const char *message_state;
995 SQL_Var vars[6];
996 char smsc[GSM_MAX_NUMBER_LENGTH + 1];
997 char destination[GSM_MAX_NUMBER_LENGTH + 1];
998
999 EncodeUTF8(smsc, sms->SMS[Part - 1].SMSC.Number);
1000 EncodeUTF8(destination, sms->SMS[Part - 1].Number);
1001
1002 if (err == SMSD_SEND_OK) {
1003 SMSD_Log(DEBUG_NOTICE, Config, "Transmitted %s (%s: %i) to %s", Config->SMSID,
1004 (Part == sms->Number ? "total" : "part"), Part, DecodeUnicodeString(sms->SMS[0].Number));
1005 }
1006
1007 if (err == SMSD_SEND_OK) {
1008 if (sms->SMS[Part - 1].PDU == SMS_Status_Report) {
1009 message_state = "SendingOK";
1010 } else {
1011 message_state = "SendingOKNoReport";
1012 }
1013 } else if (err == SMSD_SEND_SENDING_ERROR) {
1014 message_state = "SendingError";
1015 } else if (err == SMSD_SEND_ERROR) {
1016 message_state = "Error";
1017 } else {
1018 SMSD_Log(DEBUG_INFO, Config, "Unknown SMS state: %d, assuming Error", err);
1019 message_state = "Error";
1020 }
1021
1022 /* 1 = ID, 2 = SequencePosition, 3 = Status, 4 = TPMR, 5 = insertintodb */
1023 vars[0].type = SQL_TYPE_STRING;
1024 vars[0].v.s = ID;
1025 vars[1].type = SQL_TYPE_INT;
1026 vars[1].v.i = Part;
1027 vars[2].type = SQL_TYPE_STRING;
1028 vars[2].v.s = message_state;
1029 vars[3].type = SQL_TYPE_INT;
1030 vars[3].v.i = TPMR;
1031 vars[4].type = SQL_TYPE_STRING;
1032 vars[4].v.s = Config->DT;
1033 vars[5].type = SQL_TYPE_NONE;
1034
1035 if (SMSDSQL_NamedQuery(Config, SMSDSQL_queries[SQL_QUERY_ADD_SENT_INFO], &sms->SMS[Part - 1], vars, &res) != ERR_NONE) {
1036 SMSD_Log(DEBUG_INFO, Config, "Error writing to database (%s)", __FUNCTION__);
1037 return ERR_UNKNOWN;
1038 }
1039 db->FreeResult(Config, &res);
1040
1041 if (SMSDSQL_NamedQuery(Config, SMSDSQL_queries[SQL_QUERY_UPDATE_SENT], &sms->SMS[Part - 1], NULL, &res) != ERR_NONE) {
1042 SMSD_Log(DEBUG_INFO, Config, "Error updating number of sent messages (%s)", __FUNCTION__);
1043 return ERR_UNKNOWN;
1044 }
1045 db->FreeResult(Config, &res);
1046
1047 return ERR_NONE;
1048 }
1049
1050 static GSM_Error SMSDSQL_RefreshPhoneStatus(GSM_SMSDConfig * Config)
1051 {
1052 SQL_result res;
1053 SQL_Var vars[3] = {
1054 {SQL_TYPE_INT, {NULL}},
1055 {SQL_TYPE_INT, {NULL}},
1056 {SQL_TYPE_NONE, {NULL}}};
1057 struct GSM_SMSDdbobj *db = Config->db;
1058 vars[0].v.i = Config->Status->Charge.BatteryPercent;
1059 vars[1].v.i = Config->Status->Network.SignalPercent;
1060
1061 if (SMSDSQL_NamedQuery(Config, SMSDSQL_queries[SQL_QUERY_REFRESH_PHONE_STATUS], NULL, vars, &res) != ERR_NONE) {
1062 SMSD_Log(DEBUG_INFO, Config, "Error writing to database (%s)", __FUNCTION__);
1063 return ERR_UNKNOWN;
1064 }
1065 db->FreeResult(Config, &res);
1066
1067 return ERR_NONE;
1068 }
1069
1070 /*
1071 * better strcat... shows where is the bug
1072 */
1073 #define STRCAT_MAX 80
1074 GSM_Error SMSDSQL_option(GSM_SMSDConfig *Config, int optint, const char *option, ...)
1075 {
1076 size_t len[STRCAT_MAX], to_alloc = 0;
1077 int i, j;
1078 va_list ap;
1079 const char *arg;
1080 const char *args[STRCAT_MAX];
1081 char *buffer, *ptr;
1082
1083 /* read from config */
1084 buffer = INI_GetValue(Config->smsdcfgfile, "sql", option, FALSE);
1085 /* found? */
1086 if (buffer != NULL){
1087 SMSDSQL_queries[optint] = strdup(buffer); /* avoid to double free */
1088 return ERR_NONE;
1089 }
1090
1091 /* not found.. we use default query */
1092 va_start(ap, option);
1093 for(i = 0; i < STRCAT_MAX; i++){
1094 arg = va_arg(ap, const char *);
1095 if (arg == NULL)
1096 break;
1097 len[i] = strlen(arg);
1098 args[i] = arg;
1099 to_alloc += len[i];
1100 }
1101 va_end(ap);
1102
1103 if (i == STRCAT_MAX) {
1104 SMSD_Log(DEBUG_ERROR, Config, "STRCAT_MAX too small.. consider increase this value for option %s", option);
1105 return ERR_UNKNOWN;
1106 }
1107
1108 buffer = malloc(to_alloc+1);
1109 if (buffer == NULL){
1110 SMSD_Log(DEBUG_ERROR, Config, "Insufficient memory problem for option %s", option);
1111 return ERR_UNKNOWN;
1112 }
1113 ptr = buffer;
1114 for (j = 0; j < i; j++) {
1115 memcpy(ptr, args[j], len[j]);
1116 ptr += len[j];
1117 }
1118 *ptr = '\0';
1119 SMSDSQL_queries[optint] = buffer;
1120 return ERR_NONE;
1121 }
1122
1123
1124 /**
1125 * Reads common options for database backends.
1126 */
1127 GSM_Error SMSDSQL_ReadConfiguration(GSM_SMSDConfig *Config)
1128 {
1129 int locktime;
1130 const char *escape_char;
1131
1132 Config->user = INI_GetValue(Config->smsdcfgfile, "smsd", "user", FALSE);
1133 if (Config->user == NULL) {
1134 Config->user="root";
1135 }
1136
1137 Config->password = INI_GetValue(Config->smsdcfgfile, "smsd", "password", FALSE);
1138 if (Config->password == NULL) {
1139 Config->password="";
1140 }
1141
1142 Config->host = INI_GetValue(Config->smsdcfgfile, "smsd", "host", FALSE);
1143 if (Config->host == NULL) {
1144 /* Backward compatibility */
1145 Config->host = INI_GetValue(Config->smsdcfgfile, "smsd", "pc", FALSE);
1146 }
1147 if (Config->host == NULL) {
1148 Config->host="localhost";
1149 }
1150
1151 Config->database = INI_GetValue(Config->smsdcfgfile, "smsd", "database", FALSE);
1152 if (Config->database == NULL) {
1153 Config->database="sms";
1154 }
1155
1156 Config->driverspath = INI_GetValue(Config->smsdcfgfile, "smsd", "driverspath", FALSE);
1157
1158 Config->sql = INI_GetValue(Config->smsdcfgfile, "smsd", "sql", FALSE);
1159
1160 Config->dbdir = INI_GetValue(Config->smsdcfgfile, "smsd", "dbdir", FALSE);
1161
1162 if (Config->driver == NULL) {
1163 SMSD_Log(DEBUG_ERROR, Config, "No database driver selected. Must be native_mysql, native_pgsql, ODBC or DBI one.");
1164 return ERR_UNKNOWN;
1165 }
1166
1167 Config->db = NULL;
1168 #ifdef HAVE_MYSQL_MYSQL_H
1169 if (!strcasecmp(Config->driver, "native_mysql"))
1170 Config->db = &SMSDMySQL;
1171 #endif
1172 #ifdef HAVE_POSTGRESQL_LIBPQ_FE_H
1173 if (!strcasecmp(Config->driver, "native_pgsql"))
1174 Config->db = &SMSDPgSQL;
1175 #endif
1176 #ifdef ODBC_FOUND
1177 if (!strcasecmp(Config->driver, "odbc"))
1178 Config->db = &SMSDODBC;
1179 if (Config->sql == NULL) {
1180 SMSD_Log(DEBUG_INFO, Config, "Using generic SQL for ODBC, this might fail. In such case please set SQL configuration option.");
1181 }
1182 #endif
1183 if (Config->db == NULL) {
1184 #ifdef LIBDBI_FOUND
1185 Config->db = &SMSDDBI;
1186 #else
1187 SMSD_Log(DEBUG_ERROR, Config, "Unknown DB driver");
1188 return ERR_UNKNOWN;
1189 #endif
1190 }
1191
1192 escape_char = SMSDSQL_EscapeChar(Config);
1193 #define ESCAPE_FIELD(x) escape_char, x, escape_char
1194
1195 locktime = Config->loopsleep * 8; /* reserve 8 sec per message */
1196 locktime = locktime < 60 ? 60 : locktime; /* Minimum time reserve is 60 sec */
1197
1198 if (SMSDSQL_option(Config, SQL_QUERY_DELETE_PHONE, "delete_phone",
1199 "DELETE FROM phones WHERE ", ESCAPE_FIELD("IMEI"), " = %I", NULL) != ERR_NONE) {
1200 return ERR_UNKNOWN;
1201 }
1202
1203 if (SMSDSQL_option(Config, SQL_QUERY_INSERT_PHONE, "insert_phone",
1204 "INSERT INTO phones (",
1205 ESCAPE_FIELD("IMEI"),
1206 ", ", ESCAPE_FIELD("ID"),
1207 ", ", ESCAPE_FIELD("Send"),
1208 ", ", ESCAPE_FIELD("Receive"),
1209 ", ", ESCAPE_FIELD("InsertIntoDB"),
1210 ", ", ESCAPE_FIELD("TimeOut"),
1211 ", ", ESCAPE_FIELD("Client"),
1212 ", ", ESCAPE_FIELD("Battery"),
1213 ", ", ESCAPE_FIELD("Signal"),
1214 ") VALUES (%I, %P, %1, %2, ",
1215 SMSDSQL_Now(Config),
1216 ", ",
1217 SMSDSQL_NowPlus(Config, 10),
1218 ", %N, -1, -1)", NULL) != ERR_NONE) {
1219 return ERR_UNKNOWN;
1220 }
1221
1222 if (SMSDSQL_option(Config, SQL_QUERY_SAVE_INBOX_SMS_SELECT, "save_inbox_sms_select",
1223 "SELECT ",
1224 ESCAPE_FIELD("ID"),
1225 ", ", ESCAPE_FIELD("Status"),
1226 ", ", ESCAPE_FIELD("SendingDateTime"),
1227 ", ", ESCAPE_FIELD("DeliveryDateTime"),
1228 ", ", ESCAPE_FIELD("SMSCNumber"), " "
1229 "FROM sentitems WHERE ",
1230 ESCAPE_FIELD("DeliveryDateTime"), " IS NULL AND ",
1231 ESCAPE_FIELD("SenderID"), " = %P AND ",
1232 ESCAPE_FIELD("TPMR"), " = %t AND ",
1233 ESCAPE_FIELD("DestinationNumber"), " = %R", NULL) != ERR_NONE) {
1234 return ERR_UNKNOWN;
1235 }
1236
1237 if (SMSDSQL_option(Config, SQL_QUERY_SAVE_INBOX_SMS_UPDATE_DELIVERED, "save_inbox_sms_update_delivered",
1238 "UPDATE sentitems "
1239 "SET ", ESCAPE_FIELD("DeliveryDateTime"), " = %C"
1240 ", ", ESCAPE_FIELD("Status"), " = %1"
1241 ", ", ESCAPE_FIELD("StatusError"), " = %e"
1242 " WHERE ", ESCAPE_FIELD("ID"), " = %2"
1243 " AND ", ESCAPE_FIELD("TPMR"), " = %t", NULL) != ERR_NONE) {
1244 return ERR_UNKNOWN;
1245 }
1246
1247 if (SMSDSQL_option(Config, SQL_QUERY_SAVE_INBOX_SMS_UPDATE, "save_inbox_sms_update",
1248 "UPDATE sentitems "
1249 "SET ", ESCAPE_FIELD("Status"), " = %1"
1250 ", ", ESCAPE_FIELD("StatusError"), " = %e"
1251 " WHERE ", ESCAPE_FIELD("ID"), " = %2"
1252 " AND ", ESCAPE_FIELD("TPMR"), " = %t", NULL) != ERR_NONE) {
1253 return ERR_UNKNOWN;
1254 }
1255
1256 if (SMSDSQL_option(Config, SQL_QUERY_SAVE_INBOX_SMS_INSERT, "save_inbox_sms_insert",
1257 "INSERT INTO inbox "
1258 "(", ESCAPE_FIELD("ReceivingDateTime"),
1259 ", ", ESCAPE_FIELD("Text"),
1260 ", ", ESCAPE_FIELD("SenderNumber"),
1261 ", ", ESCAPE_FIELD("Coding"),
1262 ", ", ESCAPE_FIELD("SMSCNumber"),
1263 ", ", ESCAPE_FIELD("UDH"),
1264 ", ", ESCAPE_FIELD("Class"),
1265 ", ", ESCAPE_FIELD("TextDecoded"),
1266 ", ", ESCAPE_FIELD("RecipientID"), ")"
1267 " VALUES (%d, %E, %R, %c, %F, %u, %x, %T, %P)", NULL) != ERR_NONE) {
1268 return ERR_UNKNOWN;
1269 }
1270
1271 if (SMSDSQL_option(Config, SQL_QUERY_UPDATE_RECEIVED, "update_received",
1272 "UPDATE phones SET ",
1273 ESCAPE_FIELD("Received"), " = ", ESCAPE_FIELD("Received"), " + 1"
1274 " WHERE ", ESCAPE_FIELD("IMEI"), " = %I", NULL) != ERR_NONE) {
1275 return ERR_UNKNOWN;
1276 }
1277
1278 if (SMSDSQL_option(Config, SQL_QUERY_REFRESH_SEND_STATUS, "refresh_send_status",
1279 "UPDATE outbox SET ",
1280 ESCAPE_FIELD("SendingTimeOut"), " = ", SMSDSQL_NowPlus(Config, locktime),
1281 " WHERE ", ESCAPE_FIELD("ID"), " = %1"
1282 " AND (", ESCAPE_FIELD("SendingTimeOut"), " < ", SMSDSQL_Now(Config),
1283 " OR ", ESCAPE_FIELD("SendingTimeOut"), " IS NULL)", NULL) != ERR_NONE) {
1284 return ERR_UNKNOWN;
1285 }
1286
1287 if (SMSDSQL_option(Config, SQL_QUERY_FIND_OUTBOX_SMS_ID, "find_outbox_sms_id",
1288 "SELECT ", SMSDSQL_TopClause(Config, "%1"),
1289 ESCAPE_FIELD("ID"),
1290 ", ", ESCAPE_FIELD("InsertIntoDB"),
1291 ", ", ESCAPE_FIELD("SendingDateTime"),
1292 ", ", ESCAPE_FIELD("SenderID"),
1293 " FROM outbox WHERE ",
1294 ESCAPE_FIELD("SendingDateTime"), " < ", SMSDSQL_Now(Config),
1295 " AND ", ESCAPE_FIELD("SendingTimeOut"), " < ", SMSDSQL_Now(Config),
1296 " AND ", ESCAPE_FIELD("SendBefore"), " >= ", SMSDSQL_CurrentTime(Config),
1297 " AND ", ESCAPE_FIELD("SendAfter"), " <= ", SMSDSQL_CurrentTime(Config),
1298 " AND ( ", ESCAPE_FIELD("SenderID"), " is NULL OR ", ESCAPE_FIELD("SenderID"), " = '' OR ", ESCAPE_FIELD("SenderID"), " = %P )"
1299 " ORDER BY ", ESCAPE_FIELD("InsertIntoDB"), " ASC ", SMSDSQL_LimitClause(Config, "%1"), NULL) != ERR_NONE) {
1300 return ERR_UNKNOWN;
1301 }
1302
1303 if (SMSDSQL_option(Config, SQL_QUERY_FIND_OUTBOX_BODY, "find_outbox_body",
1304 "SELECT ",
1305 ESCAPE_FIELD("Text"),
1306 ", ", ESCAPE_FIELD("Coding"),
1307 ", ", ESCAPE_FIELD("UDH"),
1308 ", ", ESCAPE_FIELD("Class"),
1309 ", ", ESCAPE_FIELD("TextDecoded"),
1310 ", ", ESCAPE_FIELD("ID"),
1311 ", ", ESCAPE_FIELD("DestinationNumber"),
1312 ", ", ESCAPE_FIELD("MultiPart"),
1313 ", ", ESCAPE_FIELD("RelativeValidity"),
1314 ", ", ESCAPE_FIELD("DeliveryReport"),
1315 ", ", ESCAPE_FIELD("CreatorID"),
1316 " FROM outbox WHERE ",
1317 ESCAPE_FIELD("ID"), "=%1", NULL) != ERR_NONE) {
1318 return ERR_UNKNOWN;
1319 }
1320
1321 if (SMSDSQL_option(Config, SQL_QUERY_FIND_OUTBOX_MULTIPART, "find_outbox_multipart",
1322 "SELECT ",
1323 ESCAPE_FIELD("Text"),
1324 ", ", ESCAPE_FIELD("Coding"),
1325 ", ", ESCAPE_FIELD("UDH"),
1326 ", ", ESCAPE_FIELD("Class"),
1327 ", ", ESCAPE_FIELD("TextDecoded"),
1328 ", ", ESCAPE_FIELD("ID"),
1329 ", ", ESCAPE_FIELD("SequencePosition"),
1330 " FROM outbox_multipart WHERE ",
1331 ESCAPE_FIELD("ID"), "=%1 AND ",
1332 ESCAPE_FIELD("SequencePosition"), "=%2", NULL) != ERR_NONE) {
1333 return ERR_UNKNOWN;
1334 }
1335
1336 if (SMSDSQL_option(Config, SQL_QUERY_DELETE_OUTBOX, "delete_outbox",
1337 "DELETE FROM outbox WHERE ", ESCAPE_FIELD("ID"), "=%1", NULL) != ERR_NONE) {
1338 return ERR_UNKNOWN;
1339 }
1340
1341 if (SMSDSQL_option(Config, SQL_QUERY_DELETE_OUTBOX_MULTIPART, "delete_outbox_multipart",
1342 "DELETE FROM outbox_multipart WHERE ", ESCAPE_FIELD("ID"), "=%1", NULL) != ERR_NONE) {
1343 return ERR_UNKNOWN;
1344 }
1345
1346 if (SMSDSQL_option(Config, SQL_QUERY_CREATE_OUTBOX, "create_outbox",
1347 "INSERT INTO outbox "
1348 "(", ESCAPE_FIELD("CreatorID"),
1349 ", ", ESCAPE_FIELD("SenderID"),
1350 ", ", ESCAPE_FIELD("DeliveryReport"),
1351 ", ", ESCAPE_FIELD("MultiPart"),
1352 ", ", ESCAPE_FIELD("InsertIntoDB"),
1353 ", ", ESCAPE_FIELD("Text"),
1354 ", ", ESCAPE_FIELD("DestinationNumber"),
1355 ", ", ESCAPE_FIELD("RelativeValidity"),
1356 ", ", ESCAPE_FIELD("Coding"),
1357 ", ", ESCAPE_FIELD("UDH"),
1358 ", ", ESCAPE_FIELD("Class"),
1359 ", ", ESCAPE_FIELD("TextDecoded"), ") VALUES "
1360 "(%1, %P, %2, %3, ", SMSDSQL_Now(Config),
1361 ", %E, %R, %V, %c, %u, %x, %T)", NULL) != ERR_NONE) {
1362 return ERR_UNKNOWN;
1363 }
1364
1365 if (SMSDSQL_option(Config, SQL_QUERY_CREATE_OUTBOX_MULTIPART, "create_outbox_multipart",
1366 "INSERT INTO outbox_multipart "
1367 "(", ESCAPE_FIELD("SequencePosition"),
1368 ", ", ESCAPE_FIELD("Text"),
1369 ", ", ESCAPE_FIELD("Coding"),
1370 ", ", ESCAPE_FIELD("UDH"),
1371 ", ", ESCAPE_FIELD("Class"),
1372 ", ", ESCAPE_FIELD("TextDecoded"),
1373 ", ", ESCAPE_FIELD("ID"), ") VALUES (%4, %E, %c, %u, %x, %T, %5)", NULL) != ERR_NONE) {
1374 return ERR_UNKNOWN;
1375 }
1376
1377 if (SMSDSQL_option(Config, SQL_QUERY_ADD_SENT_INFO, "add_sent_info",
1378 "INSERT INTO sentitems "
1379 "(", ESCAPE_FIELD("CreatorID"),
1380 ", ", ESCAPE_FIELD("ID"),
1381 ", ", ESCAPE_FIELD("SequencePosition"),
1382 ", ", ESCAPE_FIELD("Status"),
1383 ", ", ESCAPE_FIELD("SendingDateTime"),
1384 ", ", ESCAPE_FIELD("SMSCNumber"),
1385 ", ", ESCAPE_FIELD("TPMR"),
1386 ", ", ESCAPE_FIELD("SenderID"),
1387 ", ", ESCAPE_FIELD("Text"),
1388 ", ", ESCAPE_FIELD("DestinationNumber"),
1389 ", ", ESCAPE_FIELD("Coding"),
1390 ", ", ESCAPE_FIELD("UDH"),
1391 ", ", ESCAPE_FIELD("Class"),
1392 ", ", ESCAPE_FIELD("TextDecoded"),
1393 ", ", ESCAPE_FIELD("InsertIntoDB"),
1394 ", ", ESCAPE_FIELD("RelativeValidity"),
1395 ") "
1396 " VALUES (%A, %1, %2, %3, ",
1397 SMSDSQL_Now(Config),
1398 ", %F, %4, %P, %E, %R, %c, %u, %x, %T, %5, %V)", NULL) != ERR_NONE) {
1399 return ERR_UNKNOWN;
1400 }
1401
1402 if (SMSDSQL_option(Config, SQL_QUERY_UPDATE_SENT, "update_sent",
1403 "UPDATE phones SET ",
1404 ESCAPE_FIELD("Sent"), "= ", ESCAPE_FIELD("Sent"), " + 1"
1405 " WHERE ", ESCAPE_FIELD("IMEI"), " = %I", NULL) != ERR_NONE) {
1406 return ERR_UNKNOWN;
1407 }
1408
1409 if (SMSDSQL_option(Config, SQL_QUERY_REFRESH_PHONE_STATUS, "refresh_phone_status",
1410 "UPDATE phones SET ",
1411 ESCAPE_FIELD("TimeOut"), "= ", SMSDSQL_NowPlus(Config, 10),
1412 ", ", ESCAPE_FIELD("Battery"), " = %1"
1413 ", ", ESCAPE_FIELD("Signal"), " = %2"
1414 " WHERE ", ESCAPE_FIELD("IMEI"), " = %I", NULL) != ERR_NONE) {
1415 return ERR_UNKNOWN;
1416 }
1417 #undef ESCAPE_FIELD
1418
1419 return ERR_NONE;
1420 }
1421
1422 time_t SMSDSQL_ParseDate(GSM_SMSDConfig * Config, const char *date)
1423 {
1424 char *parse_res;
1425 struct tm timestruct;
1426 GSM_DateTime DT;
1427
1428 if (strcmp(date, "0000-00-00 00:00:00") == 0) {
1429 return -2;
1430 }
1431
1432 parse_res = strptime(date, "%Y-%m-%d %H:%M:%S", &timestruct);
1433
1434 if (parse_res != NULL && *parse_res == 0) {
1435 DT.Year = timestruct.tm_year + 1900;
1436 DT.Month = timestruct.tm_mon + 1;
1437 DT.Day = timestruct.tm_mday;
1438 DT.Hour = timestruct.tm_hour;
1439 DT.Minute = timestruct.tm_min;
1440 DT.Second = timestruct.tm_sec;
1441
1442 return Fill_Time_T(DT);
1443 }
1444 /* Used during testing */
1445 if (Config != NULL) {
1446 SMSD_Log(DEBUG_ERROR, Config, "Failed to parse date: %s", date);
1447 }
1448 return -1;
1449 }
1450
1451 GSM_SMSDService SMSDSQL = {
1452 SMSDSQL_Init,
1453 SMSDSQL_Free,
1454 SMSDSQL_InitAfterConnect,
1455 SMSDSQL_SaveInboxSMS,
1456 SMSDSQL_FindOutboxSMS,
1457 SMSDSQL_MoveSMS,
1458 SMSDSQL_CreateOutboxSMS,
1459 SMSDSQL_AddSentSMSInfo,
1460 SMSDSQL_RefreshSendStatus,
1461 SMSDSQL_RefreshPhoneStatus,
1462 SMSDSQL_ReadConfiguration
1463 };
1464
1465 /* How should editor hadle tabs in this file? Add editor commands here.
1466 * vim: noexpandtab sw=8 ts=8 sts=8:
1467 */
Gammu All Mobile Management Utilities