[sr-dev] Initial DB Merge Proposal

[Jan Janak]

On Fri, Sep 25, 2009 at 12:17 AM, Grzegorz Stanislawski
<stangrze at netitel.pl> wrote:
>> 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.

That's not trivial to implement anymore, unlike my simplified proposal
with '$' above. It's not only about the parser, you have to change the
way how you pass parameters to the query in the module and that's the
more complicated part. I am not saying that it is difficult to do, but
it takes some planning and coding.

And if we eventually do something like this, I'd prefer to do that one
little extra step and implement support for selects, PVs, and AVPs
instead of escape sequences like %1 and %2. The query from your
example might then look like this:

select ... where user=@authorization.username and domain=@from.uri.host

Internally escape sequences like %1 and %2 would almost always be
resolved to selects or PVs anyway simply because there are so many of
them, they are convenient to use and they can extract almost anything
from SIP messages or the proxy environment.

> [...]
> 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.

There is no need to store any attributes in the credentials table. You
can get everything with one query from both credentials and user_attrs
tables like this:

mysql> select c.uid, c.password, a.name, a.value from credentials as
c, user_attrs as a where auth_username='abc' and a.uid=c.uid;
+-----+----------+------------------+---------------------+
| uid | password | name             | value               |
+-----+----------+------------------+---------------------+
| 2   | heslo    | datetime_created | 2009-04-06 16:44:18 |
+-----+----------+------------------+---------------------+
1 row in set (0.00 sec)

In the example above columns uid and password come from table
credentials, columns name and value are the AVPs for the user stored
in user_attrs table.

Minimizing the number of queries per SIP message is something I have
had on my todo list for quite a while, including the optimization
above. I have done some private experiments with this stuff and
managed to cut the number of queries down to 2 per SIP message with
MySQL. Those two queries loaded all the attributes, digest
credentials, URIs and contacts from location table.

So yes, cutting down the number of queries is another thing worth
doing. I did not want throw this in yet because we haven't agreed on a
database schema and having a databases schema that supports this
efficiently is a precondition for such optimizations. So right now we
need to agree on the database schema for sip-router.

   Jan.