Re: PostgresSQL DLR support

On Fri, 2004-01-30 at 00:57, Alexander Malysh wrote:
> 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...
> 
OID...
DELETE FROM %s WHERE oid = (SELECT oid FROM %s WHERE %s='%s' AND %s='%s'
LIMIT 1)

For uniqueness use of OID, postgres suggest to do vacuum reqularly..

> 
> >
> > 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'.
-- 
Regards,
Yulianto W.
Senior Consultant
Limaxindo Intersistem
 Galaxy Bumi Permai F4-14
 Phones: +62315925300, +62315925533
 Fax: +62315925577
Contact:
 mobile: +628563025257
 mailto:[email protected]
 ICQ #14388169
 MSN [email protected]
 YPager sehari24jam
GPG fingerprint:
 E2BC 9029 F3FB 90C7 2FAB 9E42 421E F015 119E 0D5D
GPG Public key:
  http://pgp.mit.edu:11371/pks/lookup?op=get&search=0x119E0D5D
-- 
Regards,
[cogito ergo sum]

GPG fingerprint:
 E2BC 9029 F3FB 90C7 2FAB 9E42 421E F015 119E 0D5D
GPG Public key:
  http://pgp.mit.edu:11371/pks/lookup?op=get&search=0x119E0D5D

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQBAGfY+Qh7wFRGeDV0RArISAJ4j81ecKkod08bT+pVBxCcRjRnfOwCggp0R
H/ge13LzLkFPFTczujoVdPw=
=EnWl
-----END PGP SIGNATURE-----