Re: [Asterisk-Users] mysql phone number pattern match query
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++, ilen(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 ___ --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
[Asterisk-Users] 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; Asterisk mysql cdr dst field sample record value 011441316551212 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
Re: [Asterisk-Users] mysql phone number pattern match query
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 1403888. You would have a mysql query like this: SELECT * FROM list WHERE '1403888' 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] Aleph Communications ASTPP - Open Source Voip Billing Calling Cards www.aleph-com.net/astpp ___ --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
