dlr_sdb.c and SQLite support

[Thanos Chatziathanassiou]

I've noticed that there is a configure option ``--with-sqlite'' and 
``--with-sqlite3'', but no relevant gw/dlr_sqlite.c and gw/dlr_sqlite3.c 
files.
I have put in support for SDB_SQLITE in gw/dlr_sdb.c (attached) and was 
wondering if someone is already working on a separate dlr_sqlite.c or if 
I should go ahead and do that.

Regards,
Thanos Chatziathanassiou
--- gw/dlr_sdb.c.orig   Wed Sep 21 05:01:22 2005
+++ gw/dlr_sdb.c        Tue Apr 10 17:01:41 2007
@@ -87,6 +87,7 @@
     SDB_ORACLE,
     SDB_MYSQL,
     SDB_POSTGRES,
+    SDB_SQLITE,
     SDB_OTHER
 };

@@ -101,6 +102,8 @@
         case SDB_MYSQL:
         case SDB_POSTGRES:
             return "LIMIT 1";
+        case SDB_SQLITE:
+            return "LIMIT 1";
         case SDB_OTHER:
         default:
             return "";
@@ -254,11 +258,30 @@
     int        state;

     debug("dlr.sdb", 0, "SDB: updating DLR status in database");
-    sql = octstr_format("UPDATE %s SET %s=%d WHERE %s='%s' AND %s='%s' %s",
-                        octstr_get_cstr(fields->table),
-                        octstr_get_cstr(fields->field_status), status,
-                        octstr_get_cstr(fields->field_smsc), octstr_get_cstr(smsc),
-                        octstr_get_cstr(fields->field_ts), octstr_get_cstr(ts), sdb_get_limit_str());
+
+    if (sdb_conn_type == SDB_SQLITE) {
+        /* 
+         * sqlite does not support ``LIMIT'' on update, but because we have
+         * an auto-increment column, we can use that instead 
+         */
+           sql = octstr_format("UPDATE %s SET %s=%d WHERE %s='%s' AND %s='%s' AND %s='%s' AND id in (SELECT id FROM %s WHERE %s='%s' AND %s='%s' AND %s='%s' ORDER BY id LIMIT 1)",
+                       octstr_get_cstr(fields->table),
+                       octstr_get_cstr(fields->field_status), status,
+                       octstr_get_cstr(fields->field_smsc), octstr_get_cstr(smsc),
+                       octstr_get_cstr(fields->field_ts), octstr_get_cstr(ts),
+                       octstr_get_cstr(fields->field_dst), octstr_get_cstr(dst),
+                       octstr_get_cstr(fields->table),
+                       octstr_get_cstr(fields->field_smsc), octstr_get_cstr(smsc),
+                       octstr_get_cstr(fields->field_ts), octstr_get_cstr(ts),
+                       octstr_get_cstr(fields->field_dst), octstr_get_cstr(dst));
+    }
+    else {
+       sql = octstr_format("UPDATE %s SET %s=%d WHERE %s='%s' AND %s='%s' %s",
+                        octstr_get_cstr(fields->table),
+                        octstr_get_cstr(fields->field_status), status,
+                        octstr_get_cstr(fields->field_smsc), octstr_get_cstr(smsc),
+                        octstr_get_cstr(fields->field_ts), octstr_get_cstr(ts), sdb_get_limit_str());
+    }

 #if defined(DLR_TRACE)
      debug("dlr.sdb", 0, "SDB: sql: %s", octstr_get_cstr(sql));
@@ -291,7 +314,22 @@
                             octstr_get_cstr(fields->table),
                             octstr_get_cstr(fields->field_smsc), octstr_get_cstr(smsc),
                             octstr_get_cstr(fields->field_ts), octstr_get_cstr(ts));
-    } else {
+    } else if (sdb_conn_type == SDB_SQLITE) {
+        /*
+         * SQLite doesn't support limiting delete/update queries,
+         * thus we need to use a select subquery.
+         */
+        sql = octstr_format("DELETE FROM %s WHERE id in \
+                            (SELECT id FROM %s WHERE %s='%s' AND %s='%s' AND %s='%s' LIMIT 1)",
+                            octstr_get_cstr(fields->table),
+                            octstr_get_cstr(fields->table),
+                            octstr_get_cstr(fields->field_smsc), octstr_get_cstr(smsc),
+                            octstr_get_cstr(fields->field_ts), octstr_get_cstr(ts),
+                            octstr_get_cstr(fields->field_dst), octstr_get_cstr(dst));
+     } else {
         sql = octstr_format("DELETE FROM %s WHERE %s='%s' AND %s='%s' %s",
                             octstr_get_cstr(fields->table),
                             octstr_get_cstr(fields->field_smsc), octstr_get_cstr(smsc),
@@ -419,6 +457,9 @@
     }
     else if (octstr_search(sdb_url, octstr_imm("postgres:"), 0) == 0) {
         sdb_conn_type = SDB_POSTGRES;
+    }
+    else if (octstr_search(sdb_url, octstr_imm("sqlite:"), 0) == 0) {
+       sdb_conn_type = SDB_SQLITE;
     }
     else
         sdb_conn_type = SDB_OTHER;