[sr-dev] DB replace

[Daniel-Constantin Mierla]

Hello,

looking at previous commits and the utility of a "replace" SQL command, 
I think it can be implemented in kamailio for those modules providing 
"affected_rows" but no native "replace".

Pretty much like done for postgres case inside rls/pua, but done within 
a mutex lock, to avoid any kind of race. For proper concurrency, I think 
there should be an array of mutexes (gen_lock_set_t) , the exact mutex 
to be used will be selected based on a hash value over the unique key 
values of the record.

That means the "replace" DB API method should be extended with two new 
parameters to specify the unique key values, a db_val_t array (kvals) 
and its number of elements (kn):

typedef int (*db_replace_f) (const db1_con_t* handle, const db_key_t* keys,
                 const db_val_t* vals, const int n, const db_val_t* 
kvals, const int kn);

In the case the unique key values are at the beginning of the vals 
array, then vals can be reused for kvals and kn set accordingly.

When the kvals array is null, no mutex will be used. Of course, if db 
library has native "replace" function, that will be used and the last 
two parameters will be ignored.

In short, to implement replace() for postgres:
- DB API replace method will be extended with two parameters
- db_postgres will allocate at startup an array of mutex locks (AS) - 
its size (LS) to be set by module parameter
- postgres replace() will compute the hash as: H = 
get_hash1_raw(kvals[0]) + ... + get_hash1_raw(kvals[kn-1]) -- if kval[N] 
is not string, but integer, its value can be used directly instead of 
getting to string and then hashing it
- the mutex to be used will be M = AS[H % LS]
- the rest of replace() as pseudocode:

lock(M)
do DB UPDATE
if affected rows==0 do DB INSERT
unlock(M)

or, as the chance to have insert succeed in most of the cases can be 
relevant in some cases, the order can be:

lock(M)
do DB INSERT
if insert error do DB UPDATE
unlock(M)

The way to be done can be a third new param to replace method -- this 
implementation does not require "affected rows", eventually to detect 
based on error code that it was a duplicate key failure.

Then some other modules can use replace() mechanism easier (e.g, as 
discussed on IRC devel meeting, usrloc can solve inconsistency when db 
was not available for some time) -- such usage (insert or replace) can 
be a matter of module parameters, if wanted, from case to case.

Any other opinions?

I am not a postgres user, but I can try to make a patch if there are 
testers and nobody else wants to take over.

Cheers,
Daniel

-- 
Daniel-Constantin Mierla
Kamailio Advanced Training, April 23-26, 2012, Berlin, Germany
http://www.asipto.com/index.php/kamailio-advanced-training/