From FreeSWITCH Wiki

Contents 1 mod_lcr (Least Cost Routing) 1.1 Requirements 1.2 Installing 1.3 CLI / API 1.4 Dialplan 1.5 Dialplan Application 1.6 Sample Usage 1.7 Channel Variables / CDR Logs 1.8 Configuration 1.9 Configuring LCR tables 1.10 Sample Data 1.10.1 Sample data for MySQL 1.10.2 Sample data for PostgreSQL 1.11 Advanced Usage 1.11.1 Profiles 1.11.2 Custom SQL 1.11.3 PostgreSQL and contrib prefix 1.11.4 intralata / intrastate rating 1.11.5 overriding the Caller ID 1.11.6 User Rates 1.12 Using sqlite with mod_lcr

mod_lcr (Least Cost Routing)

mod_lcr implements LCR (Least Cost Routing) for FreeSWITCH. It can operate as a true dialplan (via transfer), invoked from the command line, and as a dialplan application.

I've found that storing digits in the lcr table with the proper international number code and then using the prefix field in the number field to prepend 011 is ideal. So NANP numbers would be 11 digits (12145551212 not 2145551212) and India would be 91 not 01191.

NOTE: Put theory of operation here. Basically, order by longest digits and then by cost. Return a properly formatted dialstring.

Requirements

mod_lcr uses FreeSWITCH ODBC core functions to read from your database in real time. You need to have that support before trying to install this module. For more information, see Using ODBC in the core.

Installing

To use LCR:

Tell FreeSWITCH to compile in this module by editing modules.conf in /usr/src/freeswitch.trunk and uncomment:

#applications/mod_lcr

Now go recompile FreeSWITCH.

make
make install

Tell FreeSWITCH to actually use the LCR module when running by adding the module to modules.conf.xml in /usr/local/freeswitch/conf/autoload_configs:

<load module="mod_lcr"/>

Finally, edit the default config in the autoload_configs directory to hold your database connection information

Now load up FreeSWITCH!

CLI / API

USAGE: lcr <digits> [<lcr profile>] [caller_id] [intrastate] [as xml]

From the commandline issue something similar to:

lcr 12145551111

Which would respond with something like:

API CALL [lcr(12145551111)] output:
 | Digit Match | Carrier  | Rate     | Dialstring                         |
 | 1214        | carrier1 | 0.01000  | sofia/gateway/carrier1/12145551111 |
 | 1           | carrier2 | 0.01440  | sofia/gateway/carrier2/12145551111 |

The XML output is suitable for use from a event socket application.

Dialplan

To use as a dialplan, just transfer to to:

<action application="transfer" data="lcr $1"/>

Dialplan Application

The LCR application can be called from the dialplan by executing it from within your condition as follows:

<action application="lcr" data="$1"/>

It will return a value suitable for use with the bridge application in the variable ${lcr_auto_route}. It will also populate the variables lcr_route_N (where N is 1 to route count) and lcr_route_count for more flexible use of the lcr information in scripts.

Sample Usage

  <extension name="Outbound to PSTN 11 Digits">
    <condition field="destination_number" expression="^(1[2-9][0-9]{2}[2-9]{7})$">
      <action application="lcr" data="$1"/>
      <action application="bridge" data="${lcr_auto_route}"/>
    </condition>
  </extension>

Channel Variables / CDR Logs

LCR will create some channel variables that document which route was actually used to complete the call. lcr_carrier contains the carrier name and lcr_rate contains the rate.

These variables show up automatically on the a and b legs for the XML CDRs. You can include them in your CSV CDRs by editing the template.

In addition the following channel variables are set on the a leg:

lcr_query_digits: digits passed to lcr
lcr_query_profile: profile id used by lcr
lcr_query_expanded_digits: expanded version of digits suitable for use in an IN list

Configuration

• Ensure FreeSWITCH is compiled with ODBC support.
• Ensure your system is setup for ODBC support.
• Import the appropriate database schema for your database. (PostgreSQL and MySQL schema included) -> scripts/contrib/intralanman/C/lcr/sql.
• Load sample data.
• Modify BASE/conf/autoload_configs/lcr.conf.xml to reflect your DSN.
• Modify BASE/conf/modules.conf.xml to load mod_lcr.
• Try it out from the CLI.

Configuring LCR tables

• Someone come up with a layout that works here:

Table: carriers
Purpose: Defines your carriers
Field: carrier_name - name of the carrier
Field: enabled - whether the carrier (thus all it's gateways/lcr entries) are enabled

Table: carrier_gateway
Purpose: Defines gateway information for a given carrier
Field: carrier_id - maps to carrier
Field: prefix - the value to put before the phone number after any translation
Field: suffix - the value to put after the phone number after any translation
Field: codec - codec to use for absolute_codec_string.  Leave empty/null for default.
Field: enabled - whether the gateway (thus all it's lcr entries) are enabled

Table: lcr
Purpose: Defines rules for a given digit sequence
Field: digits - matching digits
Field: rate - rate
Field: intrastate_rate - rate for intrastate calls
Field: intralata_rate - rate for intralata calls
Field: carrier_id - which carrier for this entry
Field: lead_strip - how many digits to strip off front of passed in number
Field: trail_strip - how many digits to strip of end of passed in number
Field: prefix - value to add to front of passed in number
Field: suffix - vaulue to add to end of passed in number
Field: lcr_profile - profile_id
Field: date_start - when this LCR entry becomes valid
Field: date_end - when this LCR entry becomes invalid
Field: quality - alternate field to order by
Field: reliability - alternate field to order by
Field: cid - regular expression to modify the callers caller id number - channel variables are also valid when called from the dial plan
Field: enabled - true/false - whether this LCR entry is enabled

Sample Data

Sample data for MySQL

-- insert two carriers

INSERT INTO carriers (id, carrier_name, enabled) VALUES (1, 'carrier1', 1);
INSERT INTO carriers (id, carrier_name, enabled) VALUES (2, 'carrier2', 1);

-- insert some gateway info

INSERT INTO carrier_gateway (id, carrier_id, prefix, suffix) VALUES (1, 1, 'sofia/gateway/carrier1/', '');
INSERT INTO carrier_gateway (id, carrier_id, prefix, suffix) VALUES (2, 2, 'sofia/external/', '@proxy.carrier2.net:5060');

-- insert some lcr data

INSERT INTO lcr (id, digits, rate, carrier_id, lead_strip, trail_strip, prefix, suffix, date_start, date_end, quality, reliability)
 VALUES (1, '1', 0.15, 1, 0, 0, '', '', current_timestamp - interval 1 year, current_timestamp + interval 1 year , 0, 0);
INSERT INTO lcr (id, digits, rate, carrier_id, lead_strip, trail_strip, prefix, suffix, date_start, date_end, quality, reliability)
 VALUES (2, '1', 0.12, 2, 1, 0, '0', '', current_timestamp - interval 1 year, current_timestamp + interval 1 year , 0, 0);
INSERT INTO lcr (id, digits, rate, carrier_id, lead_strip, trail_strip, prefix, suffix, date_start, date_end, quality, reliability)
 VALUES (3, '1234', 0.05, 1, 0, 0, '', '', current_timestamp - interval 1 year, current_timestamp + interval 1 year , 0, 0);
INSERT INTO lcr (id, digits, rate, carrier_id, lead_strip, trail_strip, prefix, suffix, date_start, date_end, quality, reliability)
 VALUES (4, '1234', 0.02, 2, 1, 0, '0', '', current_timestamp - interval 1 year, current_timestamp + interval 1 year , 0, 0);

Sample data for PostgreSQL

First import the schema and data (since we rely on sequences, drop your existing tables / sequences)

$ psql phone -f postgres-8.3.sql
$ psql phone -f sample_data.sql

Contents of sample_data.sql:

-- insert two carriers

INSERT INTO carriers (carrier_name) VALUES ('carrier1');
INSERT INTO carriers (carrier_name) VALUES ('carrier2');

-- insert some gateway info

INSERT INTO carrier_gateway (carrier_id, prefix, suffix) VALUES
  (1, 'sofia/gateway/carrier1/', '');
INSERT INTO carrier_gateway (carrier_id, prefix, suffix) VALUES
  (2, 'sofia/external/', '@proxy.carrier2.net:5060');

-- insert some lcr data

INSERT INTO lcr (digits, rate, carrier_id, lead_strip, trail_strip,
                 prefix, suffix,
                 date_start, date_end, quality, reliability) VALUES
       ('1', 0.15, 1, 0, 0, '', '',
        current_timestamp - interval 1 year,
        current_timestamp + interval 1 year
        , 0, 0);
INSERT INTO lcr (digits, rate, carrier_id, lead_strip, trail_strip,
                 prefix, suffix,
                 date_start, date_end, quality, reliability) VALUES
       ('1', 0.12, 2, 1, 0, '0', '',
        current_timestamp - interval 1 year,
        current_timestamp + interval 1 year
        , 0, 0);
INSERT INTO lcr (digits, rate, carrier_id, lead_strip, trail_strip,
                 prefix, suffix,
                 date_start, date_end, quality, reliability) VALUES
       ('1234', 0.05, 1, 0, 0, '', '',
        current_timestamp - interval 1 year,
        current_timestamp + interval 1 year
        , 0, 0);
INSERT INTO lcr (digits, rate, carrier_id, lead_strip, trail_strip,
                 prefix, suffix,
                 date_start, date_end, quality, reliability) VALUES
       ('1234', 0.02, 2, 1, 0, '0', '',
        current_timestamp - interval 1 year,
        current_timestamp + interval 1 year
        , 0, 0);

Now run some lookups:

Number that matches only on first digit. You can see that for carrier 2, the first number is stripped and prepended with 0 based on the LCR rules.

API CALL [lcr(12)] output:
 | Digit Match | Carrier  | Rate     | Dialstring                                |
 | 1           | carrier2 | 0.12000  | sofia/external/02@proxy.carrier2.net:5060 |
 | 1           | carrier1 | 0.15000  | sofia/gateway/carrier1/12                 |

Number that matches more specific. Again, same rule applies for carrier2.

API CALL [lcr(12345678)] output:
 | Digit Match | Carrier  | Rate     | Dialstring                                      |
 | 1234        | carrier2 | 0.02000  | sofia/external/02345678@proxy.carrier2.net:5060 |
 | 1234        | carrier1 | 0.05000  | sofia/gateway/carrier1/12345678                 |

Finally, a number that doesn't match:

API CALL [lcr(987)] output:
No Routes To Display

Advanced Usage

Profiles

mod_lcr also has the concept of a profile. This allows one to have alternate rule sets for different customers. Profiles can also be used to modify the field(s) that are used to order the results from the database lookup.

To reload changes to the config, do the following:

reloadxml
reload mod_lcr

CLI:

lcr number profile_name
lcr_admin show profiles

Or dialplan:

      <action application="lcr" data="$1 profile"/>

Parameters

• order_by - controls the ordering of results.
• id - additional parameter to limit query to record with id specified.
• reorder_by_rate - see below.
• quote_in_list - will quote the prefixes in the IN() list passed to the database. May be necessary for MySQL.

Profiles are defined in the lcr.conf.xml file.

For discussion, consider the following:

  <profiles>
    <profile name="rate">
      <param name="order_by" value="rate"/>
    </profile>
    <profile name="quality">
      <param name="order_by" value="quality"/>
    </profile>
    <profile name="reliability">
      <param name="order_by" value="reliability"/>
    </profile>
    <profile name="foo">
      <param name="id" value="2"/>
      <param name="order_by" value="rate"/>
    </profile>
    <profile name=reorder_rate">
      <param name="reorder_by_rate" value="true"/> <!-- default false -->
    </profile>
  </profiles>

This defines five profiles. The first three are the default shipped. They return records ordered by rate, quality, and reliability respectively. The Fourth defines a profile that orders by rate but also selects lcr records that have a profile id of 2.

The fifth defines the parameter "reorder_by_rate" to true which will reorder the list ONLY by rate. Warning: I would generally recommend against this. All LCR modules I know of always use a primary sort order of the prefix.

mod_lcr is table driven. The rules for populating rate, quality, and reliability are up to you in your data load / maintenance scripts. The order_by parameter is really any valid sql clause in the order_by clause and is inserted after "ORDER BY digits" in the sql. "reliability" and "quality" are special in that they are recognized by the module and ordered in descending order automatically.

Custom SQL

If the above does not give you enough flexibility, then you can also define a custom sql statement to do "whatever you want". But, you must keep in mind the following:

• The sql must return the following fields in the exact order. If the number of fields returned differs, then the sql will be rejected and the lcr query will fail.
• The sql may have %q where you want the dialed number to be inserted into the query.
• The sql may use channel variables.

The SQL is supplied on a per-profile basis using the custom-sql parameter:

   <profile name="rate">
     <param name="custom_sql" value="mysql goes %q here;"/>
   </profile>

Or:

   <profile name="rate">
     <param name="custom_sql" value="mysql goes here where prefix IN (${lcr_query_expanded_digits});"/>
   </profile>

digits - the prefix that matched
carrier
rate
gateway prefix - data to prepend to the dial string
gateway suffix - data to append to the dial string
left strip - number of chars to remove from the left of the dialed #
t strip - number of chars to remove from the right of the dialed #
prefix - data to prepend to the dialed number
suffix - data to append to the dialed number
codec - codec to use for aboslute_codec_string - can be null/empty
cid - the regular expression used to modify the user's caller_id_number
user_rate - the end-user rate for the call (refer to user rate section below)

The dialstring is composed of:

gateway_prefix prefix dialed_number (after stripping) suffix gatway_suffix

PostgreSQL and contrib prefix

There is a third-party module available for PostgreSQL 8.1+ called prefix which provides a custom, GiST indexable column type for prefix matching. This module makes it possible to query a rate table with hundreds of thousands of entries in less than a millisecond.

Example usage:

create table rates (id serial not null, prefix prefix_range, [...]);
create index rates_prefix_idx on rates using gist (prefix gist_prefix_range_ops);
insert [...]
select * from rates where prefix @> '16666666666';

A sample query that "works for me" is the following:

    <profile name="use_prefix">
      <param name="custom_sql" value="
SELECT l.digits, c.carrier_name, l.${lcr_rate_field}, cg.prefix AS gw_prefix, cg.suffix AS gw_suffix,
       l.lead_strip, l.trail_strip, l.prefix, l.suffix, cg.codec, l.cid
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, ${lcr_rate_field} asc, random();
      "/>
    </profile>

And can be called by typing at the CLI:

lcr 12145551212 use_prefix

• Note (2009-03-25) You may need to install prefix from CVS for PostgreSQL 8.1 and 8.2 support.

intralata / intrastate rating

Note: this is NANP (North American Numbering Plan) specific.

Some carriers will rate intralata and intrastate calls differently than normal long distance. mod_lcr supports querying a table to determine if the call is intralata or intrastate and provide alternate rates for those calls. This is done by using the caller id number and the called number.

If you plan to use intra type rating you need to have the intrastate_rate or intralata_rate fields defined in the lcr table. Do NOT leave the field null or 0 for carriers that don't distinguish between regular LD and intra style routing. Instead set them to the same value as rate.

intra lata/state selection is done manually by setting the channel variables intrastate or intralata to the value true.

Automatic selection requires the npanxx table loaded into the table npa_nxx_company_ocn. This is the same table as used by Mod_cidlookup#Falling_back_to_.22City_State.22_in_the_absense_of_a_name and refer to that for data loading.

overriding the Caller ID

The field cid allows one to override the CID value on a per-route basis. Sometimes your different carriers have different rules as to what to pass for CID.

The field takes a regular expression of the form:

/search/replace/

In search you put parenthesis '()' around the data you wish to capture. Each () is assigned a $X value which you can use in the replace string.

If you wanted to strip a leading 1 from a #:

/1(\d+)/$1/

Using channel variables in the regular expression is also valid, and they will be expanded to the appropriate session values when called from the dial plan. For example, it's possible to use a custom channel variable to specify the outbound CLI (or just a prefix/suffix) for a specific route, usually when the effective outbound CLI set in the user directory can not be directly related to the outgoing numbers for a specific route. This example will add the area code defined in the channel variable "my_outbound_caller_area_code" to the effective caller ID number:

/(\d+)/${my_outbound_caller_area_code}$1/

Channel variables can also be used in the matching part of the regular expression.

User Rates

In general, calls should be rated "after the fact" using an actual billing engine. There are times, however, where one may want an idea of what the end-user rate is. This can be used in concert with mod_nibblebill for instance.

User Rates require one to use custom sql. The reason is that user rates really should not be part of the lcr tables. Instead, one should be retrieving the user rates from some other table based on information on the caller (eg: account code). The user rate may even be a calculated value based on the real rate (eg: 20% markup). The logic is up to you and can be anything expressed in sql (or even a stored procedure call if it is very complex).

The user rate is in the 12th position in the returned data from the custom sql query (right after CID).

If one wants different user_rates based on intrastate/intralata/interstate then one can use the ${lcr_user_rate} variable in the query. This variable will be set to one of "user_rate", "user_intralata_rate" or "user_intrastate_rate".

Using sqlite with mod_lcr

This installation assumes a debian based system, for other distributions please adapt as necessary.

Install the pre-requisite packages:

apt-get install libsqliteodbc unixodbc-bin sqlite3 sqlite

Add the following to /opt/freeswitch/.odbc.ini:

[fslcrdb]
Description=Freeswitch LCR SQLite database
Driver=SQLite3
Database=/opt/freeswitch/conf/databases/fslcr.db
# optional lock timeout in milliseconds
Timeout=2000

Create sqlite3.sql:

-- SQL Lite 3 tables

-- Table: carriers

-- DROP TABLE carriers;

CREATE TABLE carriers
(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  carrier_name VARCHAR(255) NOT NULL,
  enabled INTEGER NOT NULL DEFAULT '1'
);

-- Table: carrier_gateway

-- DROP TABLE carrier_gateway;

CREATE TABLE carrier_gateway
(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  carrier_id integer REFERENCES carriers(id),
  prefix VARCHAR(128) NOT NULL DEFAULT '',
  suffix VARCHAR(128) NOT NULL DEFAULT '',
  codec VARCHAR(128) NOT NULL DEFAULT '',
  enabled INTEGER NOT NULL DEFAULT '1'
);

-- Index: gateway

-- DROP INDEX gateway;

CREATE UNIQUE INDEX gateway
  ON carrier_gateway
  (prefix, suffix);


-- Table: lcr

-- DROP TABLE lcr;

CREATE TABLE lcr
(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  digits NUMERIC(20, 0),
  rate numeric(11,5) NOT NULL,
  carrier_id integer NOT NULL REFERENCES carriers(id),
  lead_strip integer NOT NULL DEFAULT 0,
  trail_strip integer NOT NULL DEFAULT 0,
  prefix VARCHAR(16) NOT NULL DEFAULT '',
  suffix VARCHAR(16) NOT NULL DEFAULT '',
  lcr_profile INTEGER NOT NULL DEFAULT 0,
  date_start timestamp with time zone NOT NULL DEFAULT '1970-01-01',
  date_end timestamp with time zone NOT NULL DEFAULT '2030-12-31',
  quality numeric(10,6) NOT NULL DEFAULT 0,
  reliability numeric(10,6) NOT NULL DEFAULT 0,
  cid VARCHAR(32) NOT NULL DEFAULT '',
  enabled INTEGER NOT NULL DEFAULT '1'
);

-- Index: digits_rate

-- DROP INDEX digits_rate;

CREATE INDEX digits_rate
  ON lcr
  -- not supported -- USING btree
  (digits, rate);

-- Index: profile_digits_15

-- DROP INDEX profile_digits_15;

CREATE INDEX profile_digits_15
  ON lcr
  (digits, lcr_profile);

-- Index: unique_route

-- DROP INDEX unique_route;

CREATE INDEX unique_route
  ON lcr
  (digits, carrier_id);

Create sample_data.sql:

-- insert two carriers

INSERT INTO carriers (carrier_name) VALUES ('carrier1');
INSERT INTO carriers (carrier_name) VALUES ('carrier2');

-- insert some gateway info

INSERT INTO carrier_gateway (carrier_id, prefix, suffix) VALUES
  (1, 'sofia/gateway/carrier1/', '');
INSERT INTO carrier_gateway (carrier_id, prefix, suffix) VALUES
  (2, 'sofia/external/', '@proxy.carrier2.net:5060');

-- insert some lcr data

INSERT INTO lcr (digits, rate, carrier_id, lead_strip, trail_strip,
                 prefix, suffix,
                 date_start, date_end, quality, reliability) VALUES
       ('1', 0.15, 1, 0, 0, '', '',
	datetime(current_timestamp,'-1 year'),
	datetime(current_timestamp,'+1 year')
        , 0, 0);
INSERT INTO lcr (digits, rate, carrier_id, lead_strip, trail_strip,
                 prefix, suffix,
                 date_start, date_end, quality, reliability) VALUES
       ('1', 0.12, 2, 1, 0, '0', '',
	datetime(current_timestamp,'-1 year'),
	datetime(current_timestamp,'+1 year')
        , 0, 0);
INSERT INTO lcr (digits, rate, carrier_id, lead_strip, trail_strip,
                 prefix, suffix,
                 date_start, date_end, quality, reliability) VALUES
       ('1234', 0.05, 1, 0, 0, '', '',
	datetime(current_timestamp,'-1 year'),
	datetime(current_timestamp,'+1 year')
        , 0, 0);
INSERT INTO lcr (digits, rate, carrier_id, lead_strip, trail_strip,
                 prefix, suffix,
                 date_start, date_end, quality, reliability) VALUES
       ('1234', 0.02, 2, 1, 0, '0', '',
	datetime(current_timestamp,'-1 year'),
	datetime(current_timestamp,'+1 year')
        , 0, 0);

Load in these two SQL files into the DB:

sqlite3 /opt/freeswitch/conf/databases/fslcr.db
.read sqlite3.sql
.read sample_data.sql