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 && wget 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: do not forget to change FS_DB_USER & FS_DB_SPASSWORD on your data
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:
cd /usr/share/freeswitch/scripts && touch directory_xml.lua && chown freeswitch. directory_xml.lua
and paste there this script: do not forget to change FS_DB_USER & FS_DB_SPASSWORD on your data
-- 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))
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