[sr-dev] Initial DB Merge Proposal

[Grzegorz Stanislawski]

Jan Janak pisze:
> On Thu, Sep 24, 2009 at 2:59 AM, Grzegorz Stanislawski
> <stangrze at netitel.pl> wrote:
>> 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.
 >
> Yes, I agree that it would be better to have the query configurable
> instead of configuring just column names. Currently most modules have
> [..]
> special cases. Maybe we could start by implementing the easier
> version, that would give us the possibility to configure the query
> string but not its parameters. That's trivial to implement. Later we
> could add support for arbitrary parameters that are resolved to AVPs,
> selects, PVs, whatever.
 >
Version capable of resolving variables would be very useful for 
accounting, and this is actually already done for db_extra modparam.

While having variable resolving for lookup queries might be overkill 
especially that in some cases some PVs or AVPs are not yet known which 
may lead to errors and confusion, i'd suggest not to trivialize it to 
syntax of mysql_prepare. Having well defined %1 %2 or something like 
this, would allow to for example use
"select .. from credentials where user=%1 and domain=%2" when 
multidomain support is enabled and "select .. .. where user=%1" when not.
simple parser in module init could change % macros to ? and add certain 
keys to list for further execution.

> 
>> 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.)
> 
> I am not sure about this yet. I mean it is doable, butthere would be
> some precautions we would need to take, for example, to ensure that
> select * does not overview anything important.
>
 From what i have learned, in most cases, there is a fixed small set of 
quite stable (not changed often) attributes which are used every time 
packet  is processed. It might be better to have it loaded in one query 
together with credentials/domainid, (or even stored in domain cache).
Having attributes in one row with user credentials is not something 
unusual, expression like 
modparam("avpops","db_scheme","email_scheme:table=subscriber;value_col=email_address;value_type=string")
is common in many scripts.
My intention is not to create table like in asterisk realtime which have 
50 columns or so, but 0-10 looks reasonable.
Other avp may be loaded when needed usual way from usual source 
(user_attrs/domain_attrs/avp_db's whatever)

I realize that it may complicate how this data is managed from mi/rpc 
side, bat maybe they can be marked just by another bit in flags and than 
handled accordingly.

Regarding 'select *' issue i don't think it should be our concern. User 
who is smart enough to handle writing of sr.cfg should be able realize 
what he is doing. And if he decides to do it he do at his own risk.


>    Jan.

Grzegorz Stanislawski