ASTERISK-10463: Change SQL order by clause that selects sections for better business-logic implementation

[Home]

Summary: ASTERISK-10463: Change SQL order by clause that selects sections for better business-logic implementation

Reporter: Max Litnitskiy (litnimax) Labels:

Date Opened: 2007-10-06 12:35:34 Date Closed: 2007-10-07 00:26:11

Priority: Major Regression? No

Status: Closed/Complete Components: Addons/res_config_mysql

Versions: Frequency of
Occurrence

Related
Issues:

Environment: Attachments: ( 0) res_config.patch

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?

Comments: By: Tilghman Lesher (tilghman) 2007-10-07 00:25:27

Absolutely not. This would break sip.conf and iax.conf for certain behavior (such as the user used for unauthenticated hosts).