Re: updating before insert

=?UTF-8?Q?Gerhard=20H=C3=A4ring=20 wrote:
> On Fri, 10 Aug 2007 00:53:51 -0400, "Eric S. Johansson" <[email protected]> wrote:
>> I've tried a couple different ways of doing this but I'm screwing
>> something up.  I know this is part SQL but it's also part Python and how
>> they interfere... I mean interacts with each other.
>>
>> What's the right way to update a few fields in a record if the record
>> exists otherwise, create the record?
> 
> Oracle has the nonstandard MERGE statement that comes in very handy for such cases. SQLite has "INSERT OR REPLACE", which AFAIK is less powerful (I think it does a UNIQUE check on all columns).
> 
> What I'd recommend is this:
> 
> Create a UNIQUE INDEX on the columns in question.
> 
> Then just INSERT from pysqlite. If this fails with an IntegrityError, do the equivalent UPDATE instead. Then COMMIT.
> 

I was afraid you were going to say that because that's what I am doing already.


address_table = '''
my_address      TEXT,
their_address   TEXT,
last_seen       TIMESTAMP,
PRIMARY KEY (my_address, their_address)
'''

Except I just noticed its primary key instead of unique index.   reading up on 
table creation apparently you can describe keys as unique.  Is there anyway to 
retrofit uniqueness to a key?  Can I define  UNIQUE PRIMARY KEY?

and this is the code for an insertion/update


         insert_command = "insert into %s (my_address, their_address, last_seen) 
values (?, ?, ?)" % self.table_name
         update_command = "update %s set my_address=?, their_address=?, 
last_seen=? where (my_address=? and their_address=?)" % self.table_name

         now = datetime.date.today()

         try:
             self.cursor.execute(insert_command, (user_address, their_address, now))
         except Exception, error:
             #print error
             self.cursor.execute(update_command, (user_address, their_address, 
now, user_address, their_address))

         self.connection.commit()

as for locking, I have no problem with that.  I can lock shared for everything 
except writing which gets a lock exclusive which is blocked until all of the 
shareds are done.

>> Related is:
>>
>> What's the right way to test for a record (including a predicate against
>> some of the fields (specifically timestamp calculations)) and if the
>> record doesn't exist, add it.
> 
> There's no way to do this reliably without locking the whole table. So do as I described: INSERT. If fails, then UPDATE. No race conditions here.
> 
okay.  I think I will lock in both cases because there is a race condition in 
each case.  In the first case (update versus insert) the race condition is the 
between the insert and the update.  Admittedly, it's a small window but one 
nevertheless.  I should just grab an exclusive lock before the insert and 
release after the commit.  The other problem where I  test first (select)I 
should also grab exclusive lock.  Then around all other queries I should place a 
shared lock.

After all of Dijkstra's work with semaphores, one would think that the bright 
people that create SQL could have been a little smarter about locking.  This 
whole transaction/retries stuff is a tad fuzzy.

---eric


-- 
Speech-recognition in use.  It makes mistakes, I correct some.