Author: rupa Date: Mon Mar 16 11:07:11 2009 New Revision: 12621 Log: update sample config with custom_sql example Modified: freeswitch/trunk/conf/autoload_configs/lcr.conf.xml Modified: freeswitch/trunk/conf/autoload_configs/lcr.conf.xml ============================================================================== --- freeswitch/trunk/conf/autoload_configs/lcr.conf.xml (original) +++ freeswitch/trunk/conf/autoload_configs/lcr.conf.xml Mon Mar 16 11:07:11 2009 @@ -16,5 +16,45 @@ <param name="id" value="2"/> <param name="order_by" value="reliability,quality"/> </profile> +<!-- + Some samples of how to do custom SQL: + + ============================================================= + PostgreSQL with contrib prefix module which supports fast + prefix queries. Ideal option. + ============================================================= + <profile name="pg_prefix"> + <param name="custom_sql" value=" +SELECT l.digits, c.carrier_name, l.rate, cg.prefix AS gw_prefix, cg.suffix AS gw_suffix, l.lead_strip, l.trail_strip, l.prefix, l.suffix +FROM lcr l JOIN carriers c ON l.carrier_id=c.id JOIN carrier_gateway cg ON c.id=cg.carrier_id WHERE c.enabled = '1' AND cg.enabled = '1' AND l.enabled = '1' AND digits_prefix @> '%q' +AND CURRENT_TIMESTAMP BETWEEN date_start AND date_end +ORDER BY digits DESC, random(); + "/> + </profile> + ============================================================= + PostgreSQL with contrib prefix module which supports fast + prefix queries. Ideal option. Alternate syntax which requies + a session but allows variable substitution. + ============================================================= + <profile name="pg_prefix2"> + <param name="custom_sql" value=" +SELECT l.digits, c.carrier_name, l.rate, cg.prefix AS gw_prefix, cg.suffix AS gw_suffix, l.lead_strip, l.trail_strip, l.prefix, l.suffix +FROM lcr l JOIN carriers c ON l.carrier_id=c.id JOIN carrier_gateway cg ON c.id=cg.carrier_id WHERE c.enabled = '1' AND cg.enabled = '1' AND l.enabled = '1' AND digits_prefix @> '${lcr_query_digits}' +AND CURRENT_TIMESTAMP BETWEEN date_start AND date_end +ORDER BY digits DESC, random(); + "/> + </profile> + ============================================================= + Demonstrates use of computed inlist. + ============================================================= + <profile name="inlist"> + <param name="custom_sql" value=" +SELECT l.digits, c.carrier_name, l.rate, cg.prefix AS gw_prefix, cg.suffix AS gw_suffix, l.lead_strip, l.trail_strip, l.prefix, l.suffix +FROM lcr l JOIN carriers c ON l.carrier_id=c.id JOIN carrier_gateway cg ON c.id=cg.carrier_id WHERE c.enabled = '1' AND cg.enabled = '1' AND l.enabled = '1' AND digits IN (${lcr_query_expanded_digits}) +AND CURRENT_TIMESTAMP BETWEEN date_start AND date_end +ORDER BY digits DESC, random(); + "/> + </profile> +--> </profiles> </configuration>