asterisk users - Feb 2006 - mysql phone number pattern match query

Does anyone have a mysql query that will compare a number from the
asterisk cdr to a table of international country+city codes to determine
the closest match?

 

The two fields are;

 

1.	Asterisk mysql cdr 'dst' field - sample record value
'011441316551212'
2.	rate table data like this

 

DialPattern

011447977

011447979

011447980

011447981

011447984

011447985

011447986

011447987

011447988

011447989

011447990

011448

011449

01144

 

 

The goal is to find the _longest_ matching record from the rate table
for each dialed number. In this case '01144'

 

I am not a mySQL expert (obviously), my limited SQL experience is with
MS SQL where stored procedures and views are an option.

 

This is with mySQL 4.x, so no views.

 

Something like this

 

Select dialpattern from rates where left 5 match left 5 of dst

Order by length of dialpattern, descending

Compare dialpattern to the first x number of digits from dst where x the length
of dial pattern

The first match (when ordered by length descending) is the correct
result (longest match)

 

Too bad mySQL does not understand English :-)

 

 

 

 

 

 

 

 

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://lists.digium.com/pipermail/asterisk-users/attachments/20060222/9943fcfa/attachment.htm

What are the contents of your database? If you can put in a regex 
expression then I can tell you exactly how to do it, otherwise I can 
tell you close. In ASTPP, I'm doing it similar to how ASTCC does it. 
I'll lay it out here:

Pattern field in CDR
^1403.* will match anything beginning with 1403. Let's say you had 
dialed 14038880000. You would have a mysql query like this:
SELECT * FROM list WHERE '14038880000' RLIKE pattern ORDER by 
LENGTH(pattern) DESC

/blatant plug starts/
Are you building a billing system? If so, have a look at www.astpp.org, 
it has all this sort of stuff in place already.
/blatant plug ends/

Hope this helps

Darren Wiebe
darren@aleph-com.net


Damon Estep wrote:
> Does anyone have a mysql query that will compare a number from the 
> asterisk cdr to a table of international country+city codes to 
> determine the closest match?
>
> The two fields are;
>
>    1. Asterisk mysql cdr ?dst? field ? sample record value
>       ?011441316551212?
>    2. rate table data like this
>
> DialPattern
>
> 011447977
>
> 011447979
>
> 011447980
>
> 011447981
>
> 011447984
>
> 011447985
>
> 011447986
>
> 011447987
>
> 011447988
>
> 011447989
>
> 011447990
>
> 011448
>
> 011449
>
> 01144
>
> The goal is to find the _/longest/_ matching record from the rate 
> table for each dialed number. In this case ?01144?
>
> I am not a mySQL expert (obviously), my limited SQL experience is with 
> MS SQL where stored procedures and views are an option.
>
> This is with mySQL 4.x, so no views.
>
> Something like this
>
> Select dialpattern from rates where left 5 match left 5 of dst
>
> Order by length of dialpattern, descending
>
> Compare dialpattern to the first x number of digits from dst where x = 
> the length of dial pattern
>
> The first match (when ordered by length descending) is the correct 
> result (longest match)
>
> Too bad mySQL does not understand English J
>
>------------------------------------------------------------------------
>
>_______________________________________________
>--Bandwidth and Colocation provided by Easynews.com --
>
>Asterisk-Users mailing list
>To UNSUBSCRIBE or update options visit:
>   http://lists.digium.com/mailman/listinfo/asterisk-users
>  
>

-- 
Darren Wiebe
darren@aleph-com.net
Aleph Communications
ASTPP - Open Source Voip Billing & Calling Cards
www.aleph-com.net/astpp

>
>
>  
>
> I am not a mySQL expert (obviously), my limited SQL experience is with 
> MS SQL where stored procedures and views are an option.
>
>  
>
> This is with mySQL 4.x, so no views.
>
I'm no an expert too, but even if the algorithm is right and seems to 
bring some optimization I think mysql way of do things can't leverage 
such a method
> Select dialpattern from rates where left 5 match left 5 of dst
>
this is a select of a substring, I don't think mysql can index a 
substring, so the query will be redone completely every time
> Order by length of dialpattern, descending
>
I'm pretty sure mysql isn't so good at sorting, you're wasting a
little
more time
> Compare dialpattern to the first x number of digits from dst where x = 
> the length of dial pattern
>
here you have another substring
> The first match (when ordered by length descending) is the correct 
> result (longest match)
>
>  
>
Now of course the performance issue is relative since we are searching 
between two little strings and not for some book with 'asterisk' and 
'future' in the title on amazon.
Since performance isn't probably an issue I suggest a simple

price = None
for (i=1, i++, i<len(dialstring))
    price = select price from rates where prefix = 
dialstring[0:len(dialstring)-i]
    if price != None break
if price == None we don't know how to bill this call
else do stuff

you have an O(len(dialstring)) search but the code is simple and cpus 
are fast

If you know your system will never call numbers shorter than m you can 
substitue len(dialstring) with len(dialstring)-m

If performance is an issue maybe (never tried myself) you can split the 
prefixes table in one table for the first 4 chars, like

0011 America1
0012 America2
...
0020 Egypt
...
0086 China
...


and one table for every destination with the remaining part of the code, 
so you
first do a select on the first 4 chars of the dialed number, you know 
you'll always have one and only one match.
the match is the name of the table where to do the O(n) search, but now 
n is even smaller and there is also a smaller  number of rows to search 
from.
(too bad international prefixes aren't all of the same length, so the 
numbers in the tables have less sense and you probably need a little 
more complex billing application)

If you need to investigate what is the better query use EXPLAIN in front 
of them, and look at how mysql will do the query, what index uses and 
how many lines will it go through ....