[asterisk-bugs] [Asterisk 0010906]: Change SQL order by clause that selects sections for better business-logic implementation
noreply at bugs.digium.com
noreply at bugs.digium.com
Sun Oct 7 00:25:27 CDT 2007
A NOTE has been added to this issue.
======================================================================
http://bugs.digium.com/view.php?id=10906
======================================================================
Reported By: litnimax
Assigned To:
======================================================================
Project: Asterisk
Issue ID: 10906
Category: Addons/res_config_mysql
Reproducibility: always
Severity: feature
Priority: normal
Status: new
Asterisk Version: Addons 1.4.2
SVN Branch (only for SVN checkouts, not tarball releases): N/A
SVN Revision (number only!):
Disclaimer on File?: N/A
Request Review:
======================================================================
Date Submitted: 10-06-2007 12:35 CDT
Last Modified: 10-07-2007 00:25 CDT
======================================================================
Summary: Change SQL order by clause that selects sections for
better business-logic implementation
Description:
When you put config files into database you want to build some business
logic over it. That means that sections will belong to some role in your
application. For example if we speak about sip.conf then [peer1] belongs to
reseller1 and [peer2] belongs to reseller2. How to code it in realtime
static? Currently there is only one way - encode role id into category name
like user_id-section_name (1234-peer1). In your application you will have
code parsing category name for '-' separator to separate category owner
from category name. This is exteremly unhandy. Much better would be to have
category_id field that can be a foreign key to role id. But instead of
adding new column and breaking things may be we can use cat_metric for this
purpose?
Currently in res_config_mysql.c on line 400 we have:
SELECT category, var_name, var_val, cat_metric FROM %s WHERE filename='%s'
and commented=0 ORDER BY filename, cat_metric desc, var_metric asc,
category, var_name, var_val, id
This assumes that cat_metric will be incremented on every record to line
up categories. But if you start using one cat_metric to identify records
owner for many records asterisk cannot understand it:
mysql> SELECT category, var_name, var_val, cat_metric FROM
asterisk_astconfig WHERE filename='sip.conf' and commented=0 ORDER BY
filename, cat_metric desc, var_metric asc, category, var_name, var_val,
id;
+--------------+------------+------------------------------------------+------------+
| category | var_name | var_val |
cat_metric |
+--------------+------------+------------------------------------------+------------+
| Africa-1 | secret | wer |
20863 |
| cariama-1 | secret | sdf |
20863 |
| test22 | type | friend |
20863 |
| Africa-1 | host | 1.2.3.44 |
20863 |
| cariama-1 | host | 9.3.4.5 |
20863 |
| test22 | secret | 23 |
20863 |
| Africa-1 | fromuser | |
20863 |
| cariama-1 | fromuser | |
20863 |
| test22 | host | 1.2.3.4 |
20863 |
| Africa-1 | fromdomain | |
20863 |
| cariama-1 | fromdomain | from.com |
20863 |
| test22 | fromuser | |
20863 |
| Africa-1 | port | 5060 |
20863 |
| cariama-1 | port | 5060 |
20863 |
| test22 | fromdomain | |
20863 |
| Africa-1 | type | friend |
20863 |
| cariama-1 | type | friend |
20863 |
| test22 | port | 5060 |
20863 |
| general | #include | /home/asterisk/pbx/etc/asterisk/sip.conf |
1 |
+--------------+------------+------------------------------------------+------------+
25 rows in set (0.00 sec)
When you issue sip reload asterisk complains:
snowflake # Reloading SIP
== Parsing '/home/asterisk/pbx/etc/asterisk/sip.conf': Found
== Parsing '/home/asterisk/pbx/etc/asterisk/sip_max.conf': Found
== Parsing '/home/asterisk/pbx/etc/asterisk/users.conf': Found
[Oct 6 16:27:18] WARNING[14114]: chan_sip.c:16907 reload_config: Section
'Africa-1' lacks type
[Oct 6 16:27:18] WARNING[14114]: chan_sip.c:16907 reload_config: Section
'cariama-1' lacks type
[Oct 6 16:27:18] WARNING[14114]: chan_sip.c:16907 reload_config: Section
'Africa-1' lacks type
[Oct 6 16:27:18] WARNING[14114]: chan_sip.c:16907 reload_config: Section
'cariama-1' lacks type
[Oct 6 16:27:18] WARNING[14114]: chan_sip.c:16907 reload_config: Section
'test22' lacks type
[Oct 6 16:27:18] WARNING[14114]: chan_sip.c:16907 reload_config: Section
'Africa-1' lacks type
[Oct 6 16:27:18] WARNING[14114]: chan_sip.c:16907 reload_config: Section
'cariama-1' lacks type
[Oct 6 16:27:18] WARNING[14114]: chan_sip.c:16907 reload_config: Section
'test22' lacks type
[Oct 6 16:27:18] WARNING[14114]: chan_sip.c:16907 reload_config: Section
'Africa-1' lacks type
[Oct 6 16:27:18] WARNING[14114]: chan_sip.c:16907 reload_config: Section
'cariama-1' lacks type
[Oct 6 16:27:18] WARNING[14114]: chan_sip.c:16907 reload_config: Section
'test22' lacks type
[Oct 6 16:27:18] WARNING[14114]: chan_sip.c:16907 reload_config: Section
'Africa-1' lacks type
[Oct 6 16:27:18] WARNING[14114]: chan_sip.c:16907 reload_config: Section
'cariama-1' lacks type
[Oct 6 16:27:18] WARNING[14114]: chan_sip.c:16907 reload_config: Section
'test22' lacks type
[Oct 6 16:27:18] WARNING[14114]: chan_sip.c:16907 reload_config: Section
'test22' lacks type
== Parsing '/home/asterisk/pbx/etc/asterisk/sip_notify.conf': Found
I found an easy solution for this - change a bit SQL order by clause to:
SELECT category, var_name, var_val, cat_metric FROM %s WHERE filename='%s'
and commented=0 ORDER BY filename, cat_metric desc, category , var_metric
asc, category, var_name, var_val, id
In this case all is ok with asterisk:
| test22 | type | friend |
20863 |
| test22 | secret | 23 |
20863 |
| test22 | host | 1.2.3.4 |
20863 |
| test22 | fromuser | |
20863 |
| test22 | fromdomain | |
20863 |
| test22 | port | 5060 |
20863 |
| cariama-1 | secret | sdf |
20863 |
| cariama-1 | host | 9.3.4.5 |
20863 |
| cariama-1 | fromuser | |
20863 |
| cariama-1 | fromdomain | from.com |
20863 |
| cariama-1 | port | 5060 |
20863 |
| cariama-1 | type | friend |
20863 |
| Africa-1 | secret | wer |
20863 |
| Africa-1 | host | 1.2.3.44 |
20863 |
| Africa-1 | fromuser | |
20863 |
| Africa-1 | fromdomain | |
20863 |
| Africa-1 | port | 5060 |
20863 |
| Africa-1 | type | friend |
20863 |
and sip reload does not complain.
I think that this change will not break existing solutions and will make
life of 3-rd party developers more easy and consistent :-)
P.S. Why res_config does cat_metric desc not asc so that 1-st peers come
1-st?
======================================================================
----------------------------------------------------------------------
Corydon76 - 10-07-07 00:25
----------------------------------------------------------------------
Absolutely not. This would break sip.conf and iax.conf for certain
behavior (such as the user used for unauthenticated hosts).
Issue History
Date Modified Username Field Change
======================================================================
10-07-07 00:25 Corydon76 Note Added: 0071596
======================================================================
More information about the asterisk-bugs
mailing list