SQLite patch for smsd

[Santeri Toikka]

Hello

This is my first commit of this kind, so please bear with me. I wrote a 
SQLite support for smsd. Give it a spin and reply with feedback.

--
Santeri Toikka

From 045b3e6d40bc43f5565b80cf9f5343e53f6478d4 Mon Sep 17 00:00:00 2001
From: Santeri Toikka <address@hidden>
Date: Tue, 6 Apr 2010 13:11:00 +0300
Subject: [PATCH 1/2] SQLite forgotten files

---
 smsd/sms.tables.sqlite.sql |   23 ++++
 smsd/sqlite.c              |  252 ++++++++++++++++++++++++++++++++++++++++++++
 2 files changed, 275 insertions(+), 0 deletions(-)
 create mode 100644 smsd/sms.tables.sqlite.sql
 create mode 100644 smsd/sqlite.c

diff --git a/smsd/sms.tables.sqlite.sql b/smsd/sms.tables.sqlite.sql
new file mode 100644
index 0000000..85a2b0f
--- /dev/null
+++ b/smsd/sms.tables.sqlite.sql
@@ -0,0 +1,23 @@
+-- $ sqlite3 -init ./sms.tables.sqlite.sql smsd.db
+create table inbox (
+    "id" integer primary key,
+    "number" text not null,
+    "smsdate" text not null,
+    "insertdate" text not null,
+    "text" text,
+    "phone" integer,
+    "processed" integer default 0
+);
+create table outbox (
+    "id" integer primary key,
+    "number" text not null,
+    "processed_date" text not null,
+    "insertdate" text not null,
+    "text" text,
+    "phone" integer,
+    "processed" integer default 0,
+    "error" integer default -1 not null,
+    "dreport" integer default 0 not null,
+    "not_before" text default '00:00:00' not null,
+    "not_after" text default '23:59:59' not null
+);
diff --git a/smsd/sqlite.c b/smsd/sqlite.c
new file mode 100644
index 0000000..f99d3be
--- /dev/null
+++ b/smsd/sqlite.c
@@ -0,0 +1,252 @@
+/*
+
+  $Id$
+
+  S M S D
+
+  A Linux/Unix tool for the mobile phones.
+
+  This file is part of gnokii.
+
+  Gnokii is free software; you can redistribute it and/or modify
+  it under the terms of the GNU General Public License as published by
+  the Free Software Foundation; either version 2 of the License, or
+  (at your option) any later version.
+
+  Gnokii is distributed in the hope that it will be useful,
+  but WITHOUT ANY WARRANTY; without even the implied warranty of
+  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+  GNU General Public License for more details.
+
+  You should have received a copy of the GNU General Public License
+  along with gnokii; if not, write to the Free Software
+  Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
+
+  Copyright (C) 1999 Pavel Jan�k ml., Hugh Blemings
+  Copyright (C) 1999-2005 Jan Derfinak
+  
+  This file is a module to smsd for PostgreSQL db server.
+
+ */
+
+#include "config.h"
+#include <string.h>
+#include <stdlib.h>
+#include <sqlite3.h>
+#include <glib.h>
+#include "smsd.h"
+#include "gnokii.h"
+#include "compat.h"
+#include "utils.h"
+
+static sqlite3 *ppDbInbox;
+static sqlite3 *ppDbOutbox;
+
+GNOKII_API void DB_Bye(void) {
+
+    if (ppDbInbox)
+        sqlite3_close(ppDbInbox);
+
+    if (ppDbOutbox)
+        sqlite3_close(ppDbOutbox);
+}
+
+GNOKII_API gint DB_ConnectInbox(DBConfig connect) {
+    int rc;
+    rc = sqlite3_open(connect.db, &ppDbInbox);
+    if (rc != SQLITE_OK) {
+        g_print(_("Connection to database '%s' on failed.\n"), connect.db);
+        g_print(_("Error: %s\n"), sqlite3_errmsg(ppDbInbox));
+        return 1;
+    }
+    return 0;
+}
+
+GNOKII_API gint DB_ConnectOutbox(DBConfig connect) {
+    int rc;
+    rc = sqlite3_open(connect.db, &ppDbOutbox);
+    if (rc != SQLITE_OK) {
+        g_print(_("Connection to database '%s' on failed.\n"), connect.db);
+        g_print(_("Error: %s\n"), sqlite3_errmsg(ppDbOutbox));
+        return 1;
+    }
+    return 0;
+}
+
+GNOKII_API gint DB_InsertSMS(const gn_sms * const data, const gchar * const 
phone) {
+    GString *buf, *phnStr, *text;
+    time_t rawtime;
+    struct tm * timeinfo;
+    int error;
+    sqlite3_stmt *stmt;
+
+    if (phone[0] == '\0')
+        phnStr = g_string_new("");
+    else {
+        phnStr = g_string_sized_new(32);
+        g_string_printf(phnStr, "'%s',", phone);
+    }
+
+    text = g_string_sized_new(480);
+    g_string_append(text, strEscape((gchar *) data->user_data[0].u.text));
+
+    time(&rawtime);
+    timeinfo = localtime(&rawtime);
+
+    buf = g_string_sized_new(1024);
+    g_string_printf(buf, "INSERT INTO inbox (\"number\", \"smsdate\", 
\"insertdate\",\
+                         \"text\", %s \"processed\") VALUES ('%s', \
+                         '%02d-%02d-%02d %02d:%02d:%02d',\
+                         '%02d-%02d-%02d %02d:%02d:%02d', '%s', %s 0)",
+            phone[0] != '\0' ? "\"phone\"," : "", data->remote.number,
+            data->smsc_time.year, data->smsc_time.month,
+            data->smsc_time.day, data->smsc_time.hour,
+            data->smsc_time.minute, data->smsc_time.second,
+            timeinfo->tm_year + 1900, timeinfo->tm_mon,
+            timeinfo->tm_mday, timeinfo->tm_hour,
+            timeinfo->tm_min, timeinfo->tm_sec,
+            text->str, phnStr->str);
+
+    g_string_free(text, TRUE);
+    g_string_free(phnStr, TRUE);
+
+    // -1: read to end of buffer, NULL: ignore tail
+    error = sqlite3_prepare_v2(ppDbInbox, buf->str, -1, &stmt, NULL);
+    if (error != SQLITE_OK) {
+        g_print(_("%d: Parsing query %s failed!"), __LINE__, buf->str);
+        g_print(_("Error: %s"), sqlite3_errmsg(ppDbInbox));
+        return 1;
+    }
+
+    // run query
+    int i;
+    for (i = 0; i < 6; i++) {
+        error = sqlite3_step(stmt);
+        if (error = SQLITE_DONE)
+            break;
+        
+        sleep(1);
+        sqlite3_reset(stmt);
+    }
+
+    if (error != SQLITE_DONE) {
+        sqlite3_finalize(stmt);
+        g_print(_("%d: INSERT INTO inbox failed.\n"), __LINE__);
+        g_print(_("Error %d: %s\n"), error, sqlite3_errmsg(ppDbInbox));
+        g_print(_("Query: %s\n"), buf->str);
+        g_string_free(buf, TRUE);
+        return (1);
+    }
+
+    sqlite3_finalize(stmt);
+    g_string_free(buf, TRUE);
+    return 0;
+}
+
+GNOKII_API void DB_Look(const gchar * const phone) {
+    GString *buf, *phnStr, *timebuf;
+    gint ret1, numError, error;
+    time_t rawtime;
+    struct tm * timeinfo;
+    sqlite3_stmt * stmt;
+
+    if (phone[0] == '\0')
+        phnStr = g_string_new("");
+    else {
+        phnStr = g_string_sized_new(32);
+        g_string_printf(phnStr, "AND phone = '%s'", phone);
+    }
+
+    time(&rawtime);
+    timeinfo = localtime(&rawtime);
+
+    timebuf = g_string_sized_new(25);
+    g_string_printf(timebuf, "'%02d:%02d:%02d'",
+            timeinfo->tm_hour, timeinfo->tm_min, timeinfo->tm_sec);
+
+    sqlite3_exec(ppDbOutbox, "BEGIN TRANSACTION;", NULL, NULL, NULL);
+
+    // poll for outgoing messages
+    buf = g_string_sized_new(256);
+    g_string_printf(buf, "SELECT id, number, text, dreport FROM outbox \
+                        WHERE processed=0 \
+                        AND %s >= not_before \
+                        AND %s <= not_after \
+                        %s", timebuf->str, timebuf->str, phnStr->str);
+
+    g_string_free(phnStr, TRUE);
+
+    ret1 = sqlite3_prepare_v2(ppDbOutbox, buf->str, -1, &stmt, NULL);
+    if (ret1 != SQLITE_OK) {
+        g_print(_("%d: Parsing query %s failed!"), __LINE__, buf->str);
+        g_print(_("Error: %s"), sqlite3_errmsg(ppDbInbox));
+        return;
+    }
+
+    g_string_printf(timebuf, "'%02d-%02d-%02d %02d:%02d:%02d'",
+            timeinfo->tm_year, timeinfo->tm_mon,
+            timeinfo->tm_mday, timeinfo->tm_hour,
+            timeinfo->tm_min, timeinfo->tm_sec
+            );
+
+    ret1 = sqlite3_step(stmt);
+    while (ret1 == SQLITE_ROW) {
+        int gerror = 0;
+        gn_sms sms;
+
+        gn_sms_default_submit(&sms);
+        memset(&sms.remote.number, 0, sizeof (sms.remote.number));
+        sms.delivery_report = sqlite3_column_int(stmt, 3);
+
+        strncpy(sms.remote.number, sqlite3_column_text(stmt, 1), sizeof 
(sms.remote.number) - 1);
+        sms.remote.number[sizeof (sms.remote.number) - 1] = '\0';
+        if (sms.remote.number[0] == '+')
+            sms.remote.type = GN_GSM_NUMBER_International;
+        else
+            sms.remote.type = GN_GSM_NUMBER_Unknown;
+
+        strncpy((gchar *) sms.user_data[0].u.text, sqlite3_column_text(stmt, 
2), 10 * GN_SMS_MAX_LENGTH + 1);
+        sms.user_data[0].u.text[10 * GN_SMS_MAX_LENGTH] = '\0';
+        sms.user_data[0].length = strlen((gchar *) sms.user_data[0].u.text);
+        sms.user_data[0].type = GN_SMS_DATA_Text;
+        sms.user_data[1].type = GN_SMS_DATA_None;
+        if (!gn_char_def_alphabet(sms.user_data[0].u.text))
+            sms.dcs.u.general.alphabet = GN_SMS_DCS_UCS2;
+
+        gn_log_xdebug("Sending SMS: %s, %s\n", sms.remote.number, 
sms.user_data[0].u.text);
+
+        numError = 0;
+        do {
+            error = WriteSMS(&sms);
+            sleep(1);
+        } while ((error == GN_ERR_TIMEOUT || error == GN_ERR_FAILED) && 
numError++ < 3);
+
+
+        // mark sended
+        g_string_printf(buf, "UPDATE outbox SET processed=1, error='%d', \
+                        processed_date=%s \
+                        WHERE id=%d",
+                gerror, timebuf->str, sqlite3_column_int(stmt, 0)
+                );
+
+        sqlite3_exec(ppDbOutbox, buf->str, NULL, NULL, NULL);
+        ret1 = sqlite3_step(stmt);
+    }
+
+    // rollback if found any errors
+    if (ret1 != SQLITE_DONE) {
+        g_print(_("%d: SELECT FROM outbox command failed.\n"), __LINE__);
+        g_print(_("Error: %s\n"), sqlite3_errmsg(ppDbOutbox));
+        sqlite3_finalize(stmt);
+        sqlite3_exec(ppDbOutbox, "ROLLBACK TRANSACTION;", NULL, NULL, NULL);
+
+        g_string_free(timebuf, TRUE);
+        g_string_free(buf, TRUE);
+        return;
+    }
+    sqlite3_finalize(stmt);
+    sqlite3_exec(ppDbOutbox, "COMMIT;", NULL, NULL, NULL);
+
+    g_string_free(timebuf, TRUE);
+    g_string_free(buf, TRUE);
+}
-- 
1.6.3.3


From 0f27d711da7f3b58452e54370614c01fdace4b31 Mon Sep 17 00:00:00 2001
From: Santeri Toikka <address@hidden>
Date: Tue, 6 Apr 2010 13:25:56 +0300
Subject: [PATCH 2/2] Changes to automake

---
 configure.in     |   19 +++++++++++++++++++
 smsd/Makefile.am |   10 +++++++++-
 smsd/db.h        |    9 ++++++++-
 3 files changed, 36 insertions(+), 2 deletions(-)

diff --git a/configure.in b/configure.in
index b71f42e..4e33bd3 100644
--- a/configure.in
+++ b/configure.in
@@ -752,6 +752,24 @@ fi
 
 AM_CONDITIONAL(HAVE_POSTGRES, test "x$have_postgres" = "xyes")
 
+dnl snip
+AC_ARG_ENABLE(sqlite,
+       AC_HELP_STRING([--disable-sqlite],
+       [disable SQLite support (default is autodetected)]),
+       [enable_sqlite=$enableval],
+       [enable_sqlite=yes])
+have_sqlite=no
+AC_CHECK_HEADERS(sqlite3.h, have_sqlite=yes, have_sqlite=no)
+if test x$enable_sqlite != xno; then
+       have_sqlite=yes
+       SQLITE_LIBS="-L/usr/lib -lsqlite3"
+       SQLITE_CFLAGS="-I/usr/include"
+       AC_SUBST(SQLITE_LIBS)
+       AC_SUBST(SQLITE_CFLAGS)
+fi
+AM_CONDITIONAL(HAVE_SQLITE, test "x$have_sqlite" = "xyes")
+dnl snip
+
 AC_ARG_ENABLE(mysql,
        AC_HELP_STRING([--disable-mysql],
        [disable MySQL support (default is autodetected)]),
@@ -1074,6 +1092,7 @@ echo "
     X (GTK+) support:   $x_support
     smsd support:       $enable_smsd
     Postgres support:   $have_postgres
+    SQLite support:     $have_sqlite
     MySQL support:      $have_mysql
     Debug:              $debug
     XDebug:             $xdebug
diff --git a/smsd/Makefile.am b/smsd/Makefile.am
index d2e4045..40fa911 100644
--- a/smsd/Makefile.am
+++ b/smsd/Makefile.am
@@ -19,6 +19,13 @@ libsmsd_mysql_la_LDFLAGS = $(modules_flags)
 libsmsd_mysql_la_LIBADD = $(MYSQL_LIBS)
 endif
 
+if HAVE_SQLITE
+plugin_LTLIBRARIES += libsmsd_sqlite.la
+libsmsd_sqlite_la_SOURCES = sqlite.c db.h utils.h utils.c
+libsmsd_sqlite_la_LDFLAGS = $(modules_flags)
+libsmsd_sqlite_la_LIBADD = $(SQLITE_LIBS)
+endif
+
 libsmsd_file_la_SOURCES = file.c db.h utils.h utils.c
 libsmsd_file_la_LDFLAGS = $(modules_flags)
 libsmsd_file_la_LIBADD = $(GLIB_LIBS)
@@ -31,6 +38,7 @@ AM_CFLAGS =                           \
        -I$(srcdir)/                    \
        $(GLIB_CFLAGS)                  \
        $(POSTGRES_CFLAGS)              \
+       $(SQLITE_CFLAGS)                \
        $(MYSQL_CFLAGS)                 \
        -DMODULES_DIR=\"${plugindir}\"  \
        -DLOCALEDIR=\"$(localedir)\"    \
@@ -38,4 +46,4 @@ AM_CFLAGS =                           \
 
 man_MANS = man/smsd.8
 
-EXTRA_DIST = pq.c mysql.c README ChangeLog action README.MySQL 
sms.tables.mysql.sql sms.tables.pq.sql README.Tru64 $(man_MANS)
+EXTRA_DIST = sqlite.c pq.c mysql.c README ChangeLog action README.MySQL 
sms.tables.mysql.sql sms.tables.pq.sql README.Tru64 $(man_MANS)
diff --git a/smsd/db.h b/smsd/db.h
index 8c2aa1d..9fc8438 100644
--- a/smsd/db.h
+++ b/smsd/db.h
@@ -22,7 +22,7 @@
   along with gnokii; if not, write to the Free Software
   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 
-  Copyright (C) 1999 Pavel Janík ml., Hugh Blemings
+  Copyright (C) 1999 Pavel Jan�k ml., Hugh Blemings
   Copyright (C) 1999-2005 Jan Derfinak
 
 */
@@ -34,10 +34,17 @@
 #include "smsd.h"
 #include "gnokii.h"
 
+// Close all connections
 GNOKII_API void (*DB_Bye) (void);
+
+// Initialize connection
 GNOKII_API gint (*DB_ConnectInbox) (const DBConfig);
 GNOKII_API gint (*DB_ConnectOutbox) (const DBConfig);
+
+// Write a new message to inbox
 GNOKII_API gint (*DB_InsertSMS) (const gn_sms * const, const gchar * const);
+
+// Send messages from outbox
 GNOKII_API void (*DB_Look) (const gchar * const);
 
 #endif
-- 
1.6.3.3