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
signature.asc
Description: This is a digitally signed message part