[Asterisk-bugs] [Asterisk 0010109]: [Realtime] Wrong Matching within extensions table

[noreply at bugs.digium.com]

A NOTE has been added to this issue. 
====================================================================== 
http://bugs.digium.com/view.php?id=10109 
====================================================================== 
Reported By:                Medozas
Assigned To:                
====================================================================== 
Project:                    Asterisk
Issue ID:                   10109
Category:                   Addons/res_config_mysql
Reproducibility:            always
Severity:                   major
Priority:                   normal
Status:                     new
Asterisk Version:            1.2.19  
SVN Branch (only for SVN checkouts, not tarball releases): N/A  
SVN Revision (number only!):  
Disclaimer on File?:        No 
Request Review:              
====================================================================== 
Date Submitted:             07-03-2007 09:40 CDT
Last Modified:              07-03-2007 16:27 CDT
====================================================================== 
Summary:                    [Realtime] Wrong Matching within extensions table
Description: 
First of all I'm sorry I can't supply any patches for this (as rather
newbie to patch-management), but I will describe it so hopefully everybody
understands what i mean.

We have a setup asterisk here with realtime backend for both flat
(cat_metric, var_metric, etc.) and dynamic (sippeers, iaxpeers, etc.)
running successfully and stable.

But: with in extconfig.conf setup "extensions =>
mysql,<DATABASE>,<EXTENSIONS-TABLE>" everything works ok actually, but the
matching is incorrect.

Example:

Extension defined in extensions table:
-> "_0."
-> "_0174."

The following happens:

at line 255 of res_config_mysql.c (asterisk-addons package) and
at line 250 of res/res_config_odbc.c (asterisk core)
snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " ORDER BY %s",
initfield);

all extensions are being sorted ASCENDING. This behaviour is wrong, as to
limit Queries sent to database ARA hits the first match, and executes
app/appdata with  first found match.

So even now calling 0174123 will first be matched with the _0. rule, even
if _0174. is more precise.

Please see query examples at "Additional Information"

The easiest fix for this problem is to sort DESCENDING, as the order of
matching now is the same as with flat file configuration (->best match).

So changing above stated line to

snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " ORDER BY %s
DESC", initfield);

returns expected results. Now when 0174. is being called, it matches 0174.
first, and only when different from 0174. pattern it goes to next _0. and
fires that one successfully.

This problem is only with pattern matching, as before making the pattern
lookup realtime always tries to find the exact match of dialed number:

[output already with patched version (DESC)]
Jul  3 15:03:59 DEBUG[19101] res_config_mysql.c: MySQL RealTime: Retrieve
SQL: SELECT * FROM config_extensions WHERE exten = '0174XXXXXXX' AND
context = 'TERMINATION' AND priority = '1'
Jul  3 15:03:59 DEBUG[19101] res_config_mysql.c: MySQL RealTime:
Everything is fine.
Jul  3 15:03:59 DEBUG[19101] res_config_mysql.c: MySQL RealTime: Retrieve
SQL: SELECT * FROM config_extensions WHERE exten LIKE '\\_%' AND context =
'TERMINATION' AND priority = '1' ORDER BY exten DESC
Jul  3 15:03:59 DEBUG[19101] res_config_mysql.c: MySQL RealTime:
Everything is fine.

As ARA always tries to lookup priority directly by number, it doesn't
matter how the priority gets sorted anyways, and so this isn't a problem
(AND priority = '1' / AND priority = '2', etc.)'

Changing this line has no sideeffects as we have seen, as the flat config
has own ordering (cat_metric/var_metric, etc.) and all other tables like
sippeers or iaxpeers make no difference even if they would sort DESC.

As said above we have this change running now on our LIVE installation and
there is no difference in 100% functionality except now using correct
matchind for dial patterns.

Sorry if there might be something unclear, as this is my first bug-report
with asterisk ;)
====================================================================== 

---------------------------------------------------------------------- 
 Medozas - 07-03-07 16:27  
---------------------------------------------------------------------- 
The diff uploaded represents a permanent and tested fix of the issue stated
above. It does NOT match in descending way, as qwell (thanks for the note)
correctly stated this doesn't make a "best match".

It's using kind of quicksort-elimination to get the best match.

The patch is against 1.2.19, and isn't hard to adapt to 1.2.20 or cvs
(although not tested). Credits for this patch go to Jan Engelhardt. This
code has been tested now in various ways and declared stable from our
sides. Would be great to get response about this one.

Would be nice to see this one in mainstream once. 

Issue History 
Date Modified   Username       Field                    Change               
====================================================================== 
07-03-07 16:27  Medozas        Note Added: 0066474                          
======================================================================