[Serusers] query for lcr table

Simone Cittadini mymailforlists at gmail.com
Wed Oct 5 16:55:41 CEST 2005


Iqbal ha scritto:

> Hi
>
> I have a query which select the correct prefix from the lcr table, but 
> have realised that it my be incorrect, is anyone else using something 
> similiar for rating 
> SELECT prefix from lcr WHERE 'sip:1234 at sip.domain.com' LIKE 
> lcr.from_uri AND '35387318220' LIKE CONCAT(lcr.prefix, '%') ORDER BY 
> CHAR_LENGTH(lcr.prefix), lcr.priority DESC, RAND() ;
>
> Now this returns
>
> +--------+
> | prefix |
> +--------+
> | 353    |
> | 3538   |
> | 35387  |
> +--------+
>
> Now I assume you should pick the one with the best match, i.e at the 
> bottom, if so should the query not have ASC no desc in the query, 
> rather than having to add another line which takes all result and sees 
> length of each.

I do this way (this is a python prototype of a personalized cdr_mysql.so
for asterisk, anyway you should catch the algorithm ...

for i in range(len(calledNum)-6):
    prefix = calledNum[5:i+7]
    queryRate = 'SELECT '+accountCode+' FROM rates WHERE prefix LIKE
\''+prefix+'%\' OR prefix = \''+prefix+'\''
    dbCursor.execute(queryRate)
    numeroRighe = int(dbCursor.rowcount)
    if numeroRighe == 1:
        break
    elif numeroRighe == 0:
        queryRate = 'SELECT '+accountCode+' FROM rates WHERE prefix =
\''+prefix[0:len(prefix)-1]+'\''
            dbCursor.execute(queryRate)
        break

rate = dbCursor.fetchone()
rate = rate[0]





More information about the sr-users mailing list