[asterisk-dev] sip.conf realtime - potential logic flaw ?

[Philipp Kempgen]

Andy Davidson wrote:

> Asterisk fires some SQL queries (to get all of them, i temporarily  
> removed the select privs from the asterisk user) based on the code at  
> the top of this email, akin to :
> 
> SELECT * FROM sip_buddies WHERE name = 'customer5'
> SELECT * FROM sip_buddies WHERE host = '1.1.1.1' AND port = '5060'
> SELECT * FROM sip_buddies WHERE ipaddr = '1.1.1.1' AND port = '5060'
> SELECT * FROM sip_buddies WHERE host = '1.1.1.1' ORDER BY host
> SELECT * FROM sip_buddies WHERE ipaddr = '1.1.1.1' ORDER BY ipaddr
> SELECT * FROM sip_buddies WHERE name = '1.1.1.1'
> SELECT * FROM sip_buddies WHERE name = '1.1.1.1'

I don't see a problem here.
However, two things:

It doesn't make sense to ORDER BY host if you have a
host = '1.1.1.1' clause anyway. Same thing for ipaddr. The DBMS
might be clever enough to optimize the query. But it might just
mean more work. (I would have to look up whether the MySQL
query optimizer does that or not.)

I'd prefer the port column (and others) to be numeric (no quotes).
Numeric columns use less space on the disk and less space in memory.
The indexes are smaller and comparing numeric values is faster than
comparing strings. I know that you can simply make the port column
numeric (unsigned smallint or something) and let MySQL tolerate that
the query uses quotes. But then there's a little extra work for MySQL
to convert the value back to an integer.

Regards,
  Philipp Kempgen

-- 
amooma GmbH - Bachstr. 126 - 56566 Neuwied - http://www.amooma.de
    Let's use IT to solve problems and not to create new ones.
          Asterisk? -> http://www.das-asterisk-buch.de

Geschäftsführer: Stefan Wintermeyer
Handelsregister: Neuwied B 14998