Re: dlr_sdb bug

On Mittwoch, September 17, 2003, at 02:24  Uhr, Rory Campbell-Lange 
wrote:

> Hi. I've been playing around using sdb for my dlr storage. I've got it
> working nicely for Postgresql. However I had to do some muddling around
> with the code (I've never worked with C before) after noting some
> oddities in the database calls.
>
> UPDATE and DELETE don't take "Limit" clauses, certainly not for
> Postgres.


You can remove the LIMIT clauses, they are safety checks only to only 
modify one record if for some reasons there are two found.
This should only happen if you send more than one message per second to 
the same destination over the same SMSC and get the same timestamp.


>

> Postgres syntax:
>
>     Command:     UPDATE
>     Description: update rows of a table
>     Syntax:
>     UPDATE [ ONLY ] table SET col = expression [, ...]
>         [ FROM fromlist ]
>         [ WHERE condition ]
>
>     Command:     DELETE
>     Description: delete rows of a table
>     Syntax:
>     DELETE FROM [ ONLY ] table [ WHERE condition ]
>
> Thus, in the remove and update lines of the code below, I have removed 
> the
> sdb_get_limit_str() references.
>
> Another note is that some people may wish to keep a record of their 
> dlrs
> and not have them deleted by default. I patched dlr.c to call update on
> delete rather than delete, which meant that the dlr status is recorded
> in the dlr table.
>
>
>    29	static const char* sdb_get_limit_str()
>    30	{
>    31	    switch (sdb_conn_type) {
>    32	        case SDB_ORACLE:
>    33	            return "AND ROWNUM < 2";
>    34	        case SDB_OTHER:
>    35	        default:
>    36	            return "LIMIT 1";
>    37	    }
>    38	}
>
>   144	    debug("dlr.sdb", 0, "SDB: updating DLR status in database");
>   145	    sql = octstr_format("UPDATE %s SET %s=%d WHERE %s='%s' AND 
> %s='%s' %s",
>   146	                        octstr_get_cstr(fields->table),
>   147	                        octstr_get_cstr(fields->field_status), 
> status,
>   148	                        octstr_get_cstr(fields->field_smsc), 
> octstr_get_cstr(smsc),
>   149	                        octstr_get_cstr(fields->field_ts), 
> octstr_get_cstr(ts), sdb_get_limit_str());
>
>   165	    debug("dlr.sdb", 0, "removing DLR from database");
>   166	    sql = octstr_format("DELETE FROM %s WHERE %s='%s' AND 
> %s='%s' %s",
>   167	                        octstr_get_cstr(fields->table),
>   168	                        octstr_get_cstr(fields->field_smsc), 
> octstr_get_cstr(smsc),
>   169	                        octstr_get_cstr(fields->field_ts), 
> octstr_get_cstr(ts), sdb_get_limit_str());
>
> -- 
> Rory Campbell-Lange
> <[email protected]>
> <www.campbell-lange.net>
>
>
Andreas Fink
Global Networks Switzerland AG

------------------------------------------------------------------
Tel: +41-61-6666333  Fax: +41-61-6666334   Mobile: +41-79-2457333
Global Networks, Inc. Clarastrasse 3, 4058 Basel, Switzerland
Web: http://www.global-networks.ch/      [email protected]
------------------------------------------------------------------