FreeSwitch Directory MySql storage with Lua Dbh

mod_lua

install lua interpretator

sudo apt install lua5.2

check mod-lua:

freeswitch@fs3> module_exists mod_lua
true

else install it's:

sudo apt install freeswitch-mod-lua

odbc (debian 8)

install unix odbc

   sudo apt install unixodbc

install mysql odbc connector

cd /usr/src &&
https://dev.mysql.com/get/Downloads/Connector-ODBC/5.3/mysql-connector-odbc-5.3.10-linux-debian8-x86-64bit.tar.gz &&
tar zxvf mysql-connector-odbc-5.3.10-linux-debian8-x86-64bit.tar.gz &&
cd mysql-connector-odbc-5.3.10-linux-debian8-x86-64bit/lib &&
cp libmyodbc5* /usr/lib/x86_64-linux-gnu/odbc/
cat >> /etc/odbcinst.ini << EOF
[MySQL]
Driver=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc5w.so
UsageCount=2

EOF

Create connection to DB

cat >> /etc/odbc.ini << EOF
[freeswitch]
Description=MySQL connection to 'freeswitch' database
driver=MySQL
server=localhost
database=freeswitch
Port=3306
Socket=/var/run/mysqld/mysqld.sock
option=3

EOF

Create database

Connect to mysql cli and:

mysql> create database freeswitch;
mysql> grant all privileges on freeswitch.* to FS_DB_USER@localhost identified by 'FS_DB_PASSWORD';
mysql> flush privileges;
mysql> use freeswitch;

Create table 'directory':

CREATE TABLE `directory` (
  `domain` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `id` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `number-alias` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mailbox` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `cidr` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `password` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `toll_allow` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `user_context` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `default_gateway` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `effective_caller_id_name` varchar(512) COLLATE utf8_unicode_ci DEFAULT NULL,
  `effective_caller_id_number` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `outbound_caller_id_name` varchar(512) COLLATE utf8_unicode_ci DEFAULT NULL,
  `outbound_caller_id_number` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `callgroup` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `uservar1` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `uservar2` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `uservar3` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

You may add other columns, if you needs.

Lua dbh script

Add the following script in directory /usr/share/freeswitch/scripts:

usr/share/freeswitch/scripts &&
touch directory_xml.lua &&
chown freeswitch. directory_xml.lua
   
cat >> directory_xml.lua << EOF   
-- directory_xml.lua
-- freeswitch.consoleLog("notice", "Debug from directory_xml.lua, provided params:\n" .. params:serialize() .. "\n")
 
local req_domain = params:getHeader("domain")
local req_key    = params:getHeader("key")
local req_user   = params:getHeader("user")
 
assert (req_domain and req_key and req_user,
  "This example script only supports generating directory xml for a single user !\n")
 
local dbh = freeswitch.Dbh("odbc://freeswitch:FS_DB_USER:FS_DB_PASSWORD")
if dbh:connected() == false then
  freeswitch.consoleLog("notice", "directory_xml.lua cannot connect to database" .. dsn .. "\n")
  return
end

-- it's probably wise to sanitize input to avoid SQL injections !
local dir_query = string.format("select * from directory where domain = '%s' and `%s`='%s' limit 1", req_domain, req_key, req_user)

assert (dbh:query(dir_query, function(u)
  XML_STRING =
[[<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<document type="freeswitch/xml">
  <section name="directory">
    <domain name="]] .. u.domain .. [[">
            <user id="]] .. u.id .. [[" mailbox="]] .. u.mailbox .. [[" cidr="]]
           .. u.cidr .. [[" number-alias="]] .. u["number-alias"] .. [[">
        <params>
          <param name="a1-hash" value="]] .. u.password .. [["/>
          <param name="dial-string" value="{sip_secure_media=${regex(${sofia_contact(${dialed_user}@${dialed_domain})}|transport=tls)},presence_id=${dialed_user}@${dialed_domain}}${sofia_contact(${dialed_user}@${dialed_domain})}"/>
          <param name="jsonrpc-allowed-methods" value="verto"/>
          <param name="jsonrpc-allowed-event-channels" value="demo,conference"/>
        </params>
        <variables>
		  <variable name="toll_allow" value="]] .. u.toll_allow .. [["/>
          <variable name="user_context" value="]] .. u.user_context .. [["/>
		  <variable name="default_gateway" value="]] .. u.default_gateway .. [["/>
		  <variable name="effective_caller_id_name" value="]] .. u.effective_caller_id_name .. [["/>
		  <variable name="effective_caller_id_number" value="]] .. u.effective_caller_id_number .. [["/>
		  <variable name="outbound_caller_id_name" value="]] .. u.outbound_caller_id_name .. [["/>
		  <variable name="outbound_caller_id_number" value="]] .. u.outbound_caller_id_number .. [["/>
          <variable name="callgroup" value="]] .. u.callgroup .. [["/>
          <variable name="uservar1" value="]] .. u.uservar1 .. [["/>
          <variable name="uservar2" value="]] .. u.uservar2 .. [["/>
          <variable name="uservar3" value="]] .. u.uservar3 .. [["/>
        </variables>
      </user>
    </domain>
  </section>
</document>]]
 
  -- comment the following line for production:
  freeswitch.consoleLog("notice", "Debug from directory_xml.lua, generated XML:\n" .. XML_STRING .. "\n")
end))

EOF

and enable sending XML directory lookups through Lua by adding the following lines to your lua.conf.xml::

    <param name="xml-handler-script" value="/usr/share/freeswitch/scripts/directory_xml.lua"/>
    <param name="xml-handler-bindings" value="directory"/>

Example insert minimum values:

insert into directory (domain, id, password, effective_caller_id_number, effective_caller_id_name, outbound_caller_id_name, outbound_caller_id_number, toll_allow, callgroup, default_gateway) values ('domain value','userid value','$password',...)

https://freeswitch.org/confluence/display/FREESWITCH/Lua+FreeSWITCH+Dbh

Только авторизованные участники могут оставлять комментарии.
  • blog/freeswitch_directory_mysql_storage_with_lua_dbh.txt
  • Последние изменения: 2018/08/07