Cyrus Patel wrote:
Hi Thanos,
Thanks very much for that.
Yes please - could you send me your latest patch.
There you go (against 1.4.1, but should apply to CVS as well)...
comments and fixes welcome of course
Cheers,
Cyrus
-----Original Message-----
From: Thanos Chatziathanassiou [mailto:[EMAIL PROTECTED]
Sent: 13 June 2007 14:46
To: Cyrus Patel
Cc: users@kannel.org
Subject: Re: DLR storage using SQLite3
Cyrus Patel wrote:
Hi,
I recently saw some activity on the devel mailing list about getting
DLR storage support using SQLite/SQLite3?
That would be me...I admit I've fallen behind on that, but I'll tell you
what works so far
I was just wondering if there is a patch available with this
functionality?
No native dlr_sqlite / dlr_sqlite3 yet, although you could use dlr_sdb
for which I've done the few fixes required.
I can try and locate the patch to dlr_sdb.c for you, which works pretty
well in my experience (which - come to think of it - might be the reason
I was in no hurry to complete the native dlr part yet).
Mind you it is a little carelessly written, as I haven't gotten around
to bringing it up to the standards of the rest of kannel code.
Regards,
Thanos Chatziathanassiou
Thanks in advance,
Cyrus
**Cyrus Patel****
**Senior Software Engineer**
**Seeker Wireless****
* *
**TEL: +61 2 9499 9848**
**FAX: +61 2 9499 9845**
**MOB: +61 413 685 795******
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>******
** **
*Seeker Wireless Pty. Ltd., ABN 95 106 550 805. This electronic
mail includes information from Seeker Wireless Pty Ltd which may be
privileged or confidential. This information is for the use of the
individual(s) or entity named in the greeting above. If you are not
the intended recipient please be aware that any use of this
information is prohibited. If you have received this electronic mail
in error, please notify the sender (above) by return email**.***
No virus found in this outgoing message. Checked by AVG Free Edition.
Version: 7.5.472 / Virus Database: 269.8.13/844 - Release Date:
11/06/2007 17:10
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.472 / Virus Database: 269.8.15/847 - Release Date: 12/06/2007 21:42
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.472 / Virus Database: 269.8.15/847 - Release Date: 12/06/2007 21:42
--- dlr_sdb.c.orig Wed Sep 21 05:01:22 2005
+++ dlr_sdb.c Tue Apr 10 17:12:26 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 "";
@@ -216,13 +219,14 @@
gw_assert(res != NULL);
- sql = octstr_format("SELECT %s, %s, %s, %s, %s, %s FROM %s WHERE %s='%s' AND %s='%s' %s",
+ sql = octstr_format("SELECT %s, %s, %s, %s, %s, %s FROM %s WHERE %s='%s' AND %s='%s' AND %s='%s' %s",
octstr_get_cstr(fields->field_mask), octstr_get_cstr(fields->field_serv),
octstr_get_cstr(fields->field_url), octstr_get_cstr(fields->field_src),
octstr_get_cstr(fields->field_dst), octstr_get_cstr(fields->field_boxc),
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), sdb_get_limit_str());
+ octstr_get_cstr(fields->field_ts), octstr_get_cstr(ts),
+ octstr_get_cstr(fields->field_dst), octstr_get_cstr(dst), sdb_get_limit_str());
#if defined(DLR_TRACE)
debug("dlr.sdb", 0, "SDB: sql: %s", octstr_get_cstr(sql));
@@ -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' 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),
+ octstr_get_cstr(fields->field_dst), octstr_get_cstr(dst), 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.
+ * - notice that for uniqueness use of `id', sqlite suggests
+ * to do vacuum regularly, even if it's virtually impossible
+ * to hit duplicates since oid's are given in a row
+ */
+ 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;
