Re: PostgresSQL DLR support

Hi Marcin,

On Thursday 29 January 2004 17:02, Marcin Sobieszczan'ski wrote:
> Alexander Malysh said the following on 2004-01-29 15:17:
> > On Thursday 29 January 2004 14:29, Martin Atukunda wrote:
> >>On Wednesday 28 January 2004 13:28, Alexander  Malysh wrote:
> >>>cvs diff -Nau
> >>
> >>something like this?
> >
> > yep
> >
> > but I don't believe something like this will works with pgsql:
> > "UPDATE %s SET %s=%d WHERE %s='%s' AND %s='%s' LIMIT 1;"
> >
> > or like this too:
> > "DELETE FROM %s WHERE %s='%s' AND %s='%s' LIMIT 1;"
>
> I'm sure it does not work ;-> I have also started implementing postgres
> DLR support, but after your mail I wanted to see your implementation.
>
> the idea to make 'LIMIT 1' in update and delete in postgres is to add
> new column in the tabel (let's say 'id' - with type 'serial')

why?? You can just use subselect with (don't know exactly the name for this) 
'cid'. Just search by google for: postgresql select limit. I've seen such 
discussion by psql ML...

>
> CREATE TABLE dlr (
> 	id serial,
> 	smsc varchar(40),
> 	...
> );
>
> then you may do delete with subquery:
> DELETE FROM %s WHERE id IN (SELECT id FROM %s WHERE %s='%s' AND %s='%s'
> LIMIT 1)
>
> but I have the question: why there is 'LIMIT 1' in update or delete?
> doesn't it meen that it will be deleted (or updated) a random row (which
> of course fulfil where clause)? is there need to limit deletion?

for updates it's makes not soo much sense , but we do it. 
For Deletes it's very important to delete only one row, because for such 
protos as UCP we construct "msg-id" for lookup in DB with "destination
+timestamp". But timestamp is in seconds (not even in milliseconds). So when 
you send 2 or more messages to thesame destination to thesame time (think of 
concatenated sms) then otherwise you will delete all dlr entries instead of 
only one.

>
> --
> another idea:
> while writing code i found that adding dlr support for others database
> frontends (including libpq from postgres and - probably - unixODBC)
> might be done little bit simple by changing some in dlr concept. the
> idea is to write one dlr engine using dbpool, of course there is need to
> implement update and select into dbpool. problem is with different sql
> syntax in various dbs (for example 'limit 1' in mysql and 'rownum < 2'
> in oracle). the function call could be parameterized by sql query (or it
> part).
>
> in my opinion dlr engine should look (for example) like oracle dlr
> implementation, what do you thing about it?

hmm, that was my first attempt to get things right ;)
Oracle's OCI is just complex and I've introduced 'select/update' within 
dbpool. IMO, we should abstract more, so that dbpool deal with DBConnection 
and DBConnection object should implement 'select/update'.

-- 
Best regards / Mit besten Grüßen aus Düsseldorf

Dipl.-Ing.
Alexander Malysh
___________________________________________

Centrium GmbH
Vogelsanger Weg 80
40470 Düsseldorf

Fon: +49 (0211) 74 84 51 80
Fax: +49 (0211) 277 49 109

email: [email protected]
web: www.centrium.de
msn: [email protected]
icq: 98063111
___________________________________________

Please avoid sending me Word, Excel or PowerPoint attachments.
See http://www.fsf.org/philosophy/no-word-attachments.html