[sr-dev] Initial DB Merge Proposal

[Grzegorz Stanislawski]

Jan Janak pisze:
> Hello everybody,
> 
> If we ever want to proceed to merging individual modules, we will need
> to address differences in our database schemas, because many modules
> depend on them. I would like to start the discussion off, so I
> prepared an initial proposal for the merge. You can find the HTML
> [..]
> Hopefully the document will be useful, I welcome any feedback or questions!
 >

Hi there.

  Maybe its good point to reconsider whole approach to database behind 
ser. Ser itself provides lots of flexibility and freedom to processing 
of sip messages and it is its main strength, however it provide no 
flexibility at all to structure of database which drives it, and me and 
many people i know it is its main weakness.
I of course know  there are modules like db_ops, using which one may do 
arbitrary queries, but you cannot use it to fetch most important data 
like credentials. Of course one can rename almost every column by 
setting appropriate modparam in config file, but different field names 
are just not sufficient in some cases.
Also storage model of avp, very good and useful imo, is not acceptable 
for many situations and for many users. I know one can use avpops 
db_scheme but it looks like hack to achieve a feature i'm talking about, 
and its done by some extra queries, which would be unneeded.

What i'd like to propose is to allow user enter its own query string (as 
modparam) which would be executed by certain module in certain situation.
What is to be decided is what fields module require in result, what type 
they have to be and in what order. and also what data may be provided in 
query do match right result.
example usage would be
modparam("auth_db","cred_query"."select passwd,flags from credentials 
where user='$au' and realm='$ar'");
or if this is to difficult to implement maybe:
modparam("auth_db","cred_query"."select passwd,flags from credentials 
where user='%1' and realm='%2'");
while documentation would state that %1 is macro which will resolve to 
auth username %2 to auth realm and so on. And module expects at least 2 
column in result where first one has to be string and contain user 
password, and second has to be int and contain flags.
Going further we could make that if more columns available would be 
converted to user avps using column names as attribute names. (domain 
module could do same thing for domain attrs.)

For acc module in other example would be something like:
modparam("acc_db","req_query","insert into acc values ($something)");
modparam("acc_db","reply_query","update acc set something=$something 
where whatever");
and so on

I'd like to notice that that scheme is used successfully for example in 
freeradius or gnugk.

I know it can break compatibility with old scripts, however i can 
imagine that modules can support both ways simply by checking if *_query 
modparam is defined, and use new way if yes and old way if not.
I'm concerned that it will improve speed as query string can be passed 
(after resolving macros of pvs or avps) directly to db engine (currently 
there is a lot of code which builds structures for db_api, and then db 
driver builds query from that)
I realize of course that overall performance may fall if user create 
some neck breaking queries, but it is his choice.

What do You think?

> 
>    Jan.
> 
Grzegorz Stanislawski