smsd/services/sql.c

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