[Serusers] lcr and mysql db

roger leszczynski rogerles at gmail.com
Wed Mar 15 17:33:43 CET 2006


THis is from an irc convo with brettnem, much thanks for his help.....
the lcr mod will lookup the ruri in the lcr table.. matching prefix and
from_ruri
which gives a grp_id.. this grp_id is matched up to a grp_id in gw_grp (I
think, I munged the functions)
the grp_id correlates to multiple possible gateways for that call
one table resolves prefixes (numbers) to route groups.. the other resolves
route groups to a list of gateways (or a feature server)
lcr will route based on NPANXX to certain provider, or route DIDs to feature
servers out of the box with like 2 lines of code in ser.cfg.. that's right
you really should have more than 2.. but you got the idea.. you need to
check for load_gws failure (ie, no route 404)

example for:
sip:1234567890 at test.com to sip:1234567890 at test.org

tamp4x mysql> describe lcr;
tamp4x +----------+---------------------+------+-----+---------+-------+
tamp4x | Field    | Type                | Null | Key | Default | Extra |
tamp4x +----------+---------------------+------+-----+---------+-------+
tamp4x | prefix   | varchar(16)         |      | MUL |         |       |
tamp4x | from_uri | varchar(128)        |      | MUL | %       |       |
tamp4x | grp_id   | int(10) unsigned    |      | MUL | 0       |       |
tamp4x | priority | tinyint(3) unsigned |      |     | 0       |       |
tamp4x +----------+---------------------+------+-----+---------+-------+
tamp4x 4 rows in set (0.00 sec)
tamp4x mysql> describe gw;
tamp4x +------------+----------------------+------+-----+---------+-------+
tamp4x | Field      | Type                 | Null | Key | Default | Extra |
tamp4x +------------+----------------------+------+-----+---------+-------+
tamp4x | gw_name    | varchar(128)         |      | PRI |         |       |
tamp4x | ip_addr    | int(10) unsigned     |      |     | 0       |       |
tamp4x | port       | smallint(5) unsigned | YES  |     | NULL    |       |
tamp4x | uri_scheme | tinyint(3) unsigned  | YES  |     | NULL    |       |
tamp4x | transport  | tinyint(3) unsigned  | YES  |     | NULL    |       |
tamp4x | grp_id     | int(10) unsigned     |      | MUL | 0       |       |
tamp4x +------------+----------------------+------+-----+---------+-------+
tamp4x mysql> describe gw_grp;
tamp4x
+----------+------------------+------+-----+---------+----------------+
tamp4x | Field    | Type             | Null | Key | Default | Extra
|
tamp4x
+----------+------------------+------+-----+---------+----------------+
tamp4x | grp_id   | int(10) unsigned |      | PRI | NULL    | auto_increment
|
tamp4x | grp_name | varchar(64)      |      |     |         |
|
tamp4x
+----------+------------------+------+-----+---------+----------------+
tamp4x 2 rows in set (0.02 sec)
tamp4x mysql> describe grp;
tamp4x
+---------------+--------------+------+-----+---------------------+-------+
tamp4x | Field         | Type         | Null | Key | Default             |
Extra |
tamp4x
+---------------+--------------+------+-----+---------------------+-------+
tamp4x | username      | varchar(64)  |      | PRI |
|       |
tamp4x | domain        | varchar(128) |      | PRI |
|       |
tamp4x | grp           | varchar(50)  |      | PRI |
|       |
tamp4x | last_modified | datetime     |      |     | 0000-00-00 00:00:00
|       |
tamp4x
+---------------+--------------+------+-----+---------------------+-------+
that is the table structures

make sure your version table looks like this:

+-----------------------+---------------+
| table_name            | table_version |
+-----------------------+---------------+
| subscriber            |             5 |
| reserved              |             1 |
| phonebook             |             1 |
| pending               |             4 |
| missed_calls          |             2 |
| location              |          1001 |
| aliases               |          1001 |
| grp                   |             2 |
| event                 |             1 |
| active_sessions       |             1 |
| acc                   |             2 |
| config                |             1 |
| silo                  |             3 |
| realm                 |             1 |
| domain                |             1 |
| uri                   |             1 |
| server_monitoring     |             1 |
| server_monitoring_agg |             1 |
| trusted               |             1 |
| usr_preferences       |             2 |
| usr_preferences_types |             1 |
| admin_privileges      |             1 |
| calls_forwarding      |             1 |
| speed_dial            |             2 |
| dbaliases             |             1 |
| gw                    |             3 |
| gw_grp                |             1 |
| lcr                   |             1 |
+-----------------------+---------------+




insert into lcr values(1234567890,'%',100,50);
insert into gw values('my-nifty-gateway',122432324,5060,'','',100);

the ip address you have to reverse the octets and THEN convert to long
giving 122432324  (test.org)

here is the script for IP conversion: (make sure you have Net::IP module
installed)


   1. #!/usr/bin/perl
   2. use Net::IP;
   3. my $ip = new Net::IP ($ARGV[0]) or die (Net::IP::Error());
   4. print "In Binary: " . $ip->binip . "\n";
   5. $decip= bin2dec($ip->binip);
   6. print "DecIP:$decip\n";
   7. sub bin2dec {
   8.     return unpack("N", pack("B32", substr("0" x 32 . shift, -32)));
   9. }






the 100 is the important data. it's the key that binds the first record in
the lcr table to the ip record in the gw table
that way, anything that routes to your nifty gateway, you just put it into
gw_grp 100 and it works.. just one entry in the gw table.  the ipaddr is
what you want the destination host to be.


now here is my ser.cfg

 # ----------- global configuration parameters ------------------------

# debug=9          # debug level (cmd line: -dddddddddd)
 fork=yes
 log_stderror=yes# (cmd line: -E)
 check_via=no    # (cmd. line: -v)
 dns=no           # (cmd. line: -r)
 rev_dns=no      # (cmd. line: -R)
 listen=64.201.13.52
 port=5060
 children=4
# fifo="/tmp/ser_fifo"


 # ------------------ module loading ----------------------------------




 loadmodule "/usr/local/sip_router/modules/mysql/mysql.so"
 loadmodule "/usr/local/sip_router/modules/nathelper/nathelper.so"
 loadmodule "/usr/local/sip_router/modules/sl/sl.so"
 loadmodule "/usr/local/sip_router/modules/tm/tm.so"
 loadmodule "/usr/local/sip_router/modules/rr/rr.so"
 loadmodule "/usr/local/sip_router/modules/maxfwd/maxfwd.so"
 loadmodule "/usr/local/sip_router/modules/usrloc/usrloc.so"
 loadmodule "/usr/local/sip_router/modules/registrar/registrar.so"
 loadmodule "/usr/local/sip_router/modules/lcr/lcr.so"

#!!!!!!!!!!you may need to use openser's table formatting!!!!!!!#
#modparam("lcr","db_url", "mysql://root:password@localhost/openser")
modparam("lcr","db_url", "mysql://root:password@localhost/ser")

# main routing logic

 route{


        if (method=="REGISTER"){
                #record_route();
                forward(mysip.server.com,5060);
        };

        #loose_route();

        if (method=="INVITE") {
                #record_route();
                force_rtp_proxy();
                if (!load_gws()) {
                        sl_send_reply("500", "Server Internal Error - Cannot
load gateways");
                        break;
                        };
        };

        # forward to current uri now
        if (!t_relay()) {
            sl_reply_error();
        };
}

  onreply_route[1] {
       if (status=~"[12][0-9][0-9]")
                force_rtp_proxy();

  }





Ok hopefully this will work for someone out there , let me know

because its not working perfectly for me... when i attempt to make a call i
get memory errors...
1(2850) ERROR: sip_msg_cloner: cannot allocate memory
 1(2850) ERROR: new_t: out of mem:
 1(2850) ERROR: t_newtran: new_t failed
 1(2850) ERROR: sl_reply_error used: I'm terribly sorry, server error
occurred (1/SL)
 2(2852) ERROR: build_req_buf_from_sip_req: out of memory
 2(2852) ERROR: forward_request: building failed
 2(2852) Warning: sl_send_reply: I won't send a reply for ACK!!
 2(2852) ERROR: sl_reply_error used: I'm terribly sorry, server error
occurred (2/SL)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sip-router.org/pipermail/sr-users/attachments/20060315/44804fa1/attachment.htm>


More information about the sr-users mailing list