[sr-dev] Initial DB Merge Proposal

Jan Janak jan at ryngle.com
Thu Sep 24 12:28:56 CEST 2009


On Thu, Sep 24, 2009 at 2:59 AM, Grzegorz Stanislawski
<stangrze at netitel.pl> wrote:
> 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.
>
> 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.

Yes, I agree that it would be better to have the query configurable
instead of configuring just column names. Currently most modules have
column names configurable, but it is done this way for historic
reasons, mainly because it was not possible to check column types and
results in older versions of SER.

Regarding your two examples; the second example is actually easier to
implement than the first one. To be more precise, it is trivial to
implement in modules that come from SER and use libsrdb2. SER modules
do not assemble the SQL query at runtime anymore, instead they compile
it when SER initializes. The compiled query is sent to the database
server and all return columns and column types are verified to ensure
that the number of returned columns and their types match what the
module expects.

If you rewrite your second example as:
modparam("auth_db", "cred_query", "select passwd,flags from
credentials where user=? and realm=?")

then implementing this parameter in auth_db module takes only a couple
lines of code, because the value of the parameter is exactly the query
string that the module builds and passes to the database api. There
would be no need to check the number of returned columns or query
parameters because this is what mysql does for us. We would just need
to document that the query is given two argumens by the module and
they should be denoted by question marks (in case of mysql). This
would behave as expected, if you configure a query that does not
return correct columns or takes more or less arguments then SER
refuses to start.

Your first example with pseudo-variables inside the query string is
more difficult to implement because we would need to parse the query
string in SER to obtain a list of pseudo-variables, build the data
structures for query parameters according to the number of
pseudo-variables used inside the query and then resolve the values of
those parameters at runtime when the module actually executes the
query. Note that it not that difficult to do, most of the functions we
would need to achieve this have already been implemented and are
available in SER. But it takes more work and bigger changes in
modules.

But I agree that this kind of flexibility would be good to have for
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.

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

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

Yes.

> 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)

SER modules (those that come from ser and not kamailio) won't be
affected because they do not build SQL queries at runtime anymore.
This is done just once in mod_init and the query is then executed
repeatedly at runtime with different argument values.

> 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?

I think it would be definitely worth doing, especially the easy
variant without configurable query arguments. That would give us extra
flexibility with only minimal changes in the code. We can extend it
and add support for selects, AVPs, or pseudo-variables when we have
that.

   Jan.



More information about the sr-dev mailing list