Hi all,
Currently running on 1.5.4-notls and using a single htable that gets loaded at startup ala
modparam("htable", "htable", "local_routes=>size=4;autoexpire=0;dbtable=TABLE_A;") modparam("htable", "key_name_column", "NAME_1") modparam("htable", "key_value_column", "VALUE_1") modparam("htable", "fetch_rows", 512)
This works great, the problem I've run into is attempting to load a second htable on startup whose data comes from a different table/scheme ala
modparam("htable", "htable", "inbound_acl=>size=4;autoexpire=0;dbtable=TABLE_B;") modparam("htable", "key_name_column", "NAME_2") modparam("htable", "key_value_column", "VALUE_2") modparam("htable", "fetch_rows", 512)
With both of them in the config file Kamailio fails to start with the error
INFO:core:init_mod: initializing module htable ERROR:db_mysql:db_mysql_submit_query: driver error on query: Unknown column 'NAME_2' in 'field list'
'NAME_2' does not exist in 'TABLE_1' so that seems to suggest that only the first dbtable value is used, the SQL side supports this:
select NAME_2,key_type,value_type,VALUE_2 from TABLE_A order by key_type
So it uses the first defined database table, but then the last defined key_name and key_value.
Is there any way that I've overlooked in the docs or elsewhere about about loading multiple htables from different database tables? I'd like to use this htable in an ACL and would like to avoid database hits though can make it work with avpops if I have to.
Any suggestions are greatly appreciated.
Thanks, Robin
On 07/05/2011 11:46 AM, Robin Rodriguez wrote:
So it uses the first defined database table, but then the last defined key_name and key_value.
This appears to just be an oversight in the design of the module parameters. One can define multiple tables, but the rest of the modparams are global.
As a workaround, I would suggest a view. Alias the columns identically in the view, but map them to different underlying columns in the different table.
On 07/05/2011 01:51 PM, Alex Balashov wrote:
On 07/05/2011 11:46 AM, Robin Rodriguez wrote:
So it uses the first defined database table, but then the last defined key_name and key_value.
This appears to just be an oversight in the design of the module parameters. One can define multiple tables, but the rest of the modparams are global.
As a workaround, I would suggest a view. Alias the columns identically in the view, but map them to different underlying columns in the different table.
... well, if you intend to write to them, only some databases support that. MySQL does. Not sure about Postgres or others.
Hello,
On 7/5/11 8:21 PM, Alex Balashov wrote:
On 07/05/2011 01:51 PM, Alex Balashov wrote:
On 07/05/2011 11:46 AM, Robin Rodriguez wrote:
So it uses the first defined database table, but then the last defined key_name and key_value.
This appears to just be an oversight in the design of the module parameters. One can define multiple tables, but the rest of the modparams are global.
As a workaround, I would suggest a view. Alias the columns identically in the view, but map them to different underlying columns in the different table.
... well, if you intend to write to them, only some databases support that. MySQL does. Not sure about Postgres or others.
writing back to db from a hash table is possible with devel version (it can be done at shutdown time).
Using view is a good option working with all versions. Another one, starting with 3.0, is to use event_route[htable:mod-init] { ... }
You can use sqlops to do a query to whatsoever table and the load the records in htable. Be sure you load sqlops module first to be initialized at the time htable modules is initialized. Or, just call a Lua script where db connection is intialized and data loaded from there -- this one will work nicely starting with 3.2 since htable exports native API to lua.
Just some ideas ...
Cheers, Daniel
-
Alex Balashov -
Daniel-Constantin Mierla -
Robin Rodriguez