开放数据库连接（Open Database Connectivity，[ODBC](https://baike.baidu.com/item/ODBC)）是为解决异构数据库间的数据共享而产生的，基于Windows环境的一种数据库访问接口标准ODBC 为异构数据库访问提供统一接口，允许应用程序以SQL 为数据存取标准，存取不同DBMS管理的数据；使应用程序直接操纵DB中的数据，免除随DB的改变而改变。用ODBC 可以访问各类计算机上的DB文件，甚至访问如Excel 表和ASCI I数据文件这类非数据库对象。
Data Source Name (DSN) [1] 的PDO命名惯例为：PDO驱动程序的名称，后面为一个冒号，再后面是可选的驱动程序连接数据库变量信息，如主机名、端口和数据库名。
<param name="core-db-dsn" value="database:username:password"/>
or
<param name="core-db-dsn" value="odbc://database:username:password"/>
<param name="core-db-dsn" value="database:username:"/>
or
<param name="core-db-dsn" value="odbc://database:username"/>
<param name="core-db-dsn" value="database::"/>
or
<param name="core-db-dsn" value="odbc://database"/>
(懒加载，不需要再odbc.ini中配置数据源)
<param name="core-db-dsn" value="odbc://DRIVER=mysql;SERVER=a.b.c.d;UID=username;PWD=secretpassword;DATABASE=freeswitch;OPTION=67108864">
使用之前 请确保你linux上已经安装mysql或其他数据库，和ODBC驱动
unixODBC >= 2.3
数据库ODBC驱动
sudo apt-get install unixodbc-dev
MySQL:
`sudo apt-get install libmyodbc`
PostgreSQL
`sudo apt-get install odbc-postgresql`
Mysql: /etc/odbc.ini
[freeswitch]
Driver = /usr/lib64/odbc/libmyodbc.so
SERVER = localhost
PORT = 3306
DATABASE = freeswitch
USER = root
PASSWORD = password
Socket = /var/lib/mysql/mysql.sock //这行只针对本地，远程链接数据库请删除这行
OPTION = 67108864 //可加可不加，参考“网址1”,
网址1：
http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration-connection-parameters.html
注意**：如果你安装的是32为的操作系统 那么这这块驱动路径改成：
/usr/lib64/ 改成 /usr/lib/
Mysql : /etc/odbcinst.ini
[MySQL]
Description = MySQL driver
Driver = /usr/lib64/odbc/libmyodbc.so
Setup = /usr/lib64/odbc/libodbcmyS.so
UsageCount = 1
FileUsage = 1
Threading = 0
注意:如果你电脑上有这个 /usr/lib64/odbc/libmyodbc**_r**.so 驱动 那你必须使用这个
每修改odbcinst.ini，重启freeswitch生效
PostgreSQL ： /etc/odbc.ini
[freeswitch]
; WARNING: The old psql odbc driver psqlodbc.so is now renamed psqlodbcw.so
; in version 08.x. Note that the library can also be installed under an other
; path than /usr/local/lib/ following your installation.
Driver = /usr/lib64/odbc/psqlodbcw.so
Description=Connection to LDAP/POSTGRESQL
Servername=localhost
Port=5432
Protocol=6.4
FetchBufferSize=99
Username=postgres
Password=password
Database=freeswitch
ReadOnly=no
Debug=0
CommLog=0
注意：如果你安装的是32为的操作系统 那么这这块驱动路径改成：
/usr/lib64/ 改成 /usr/lib/
PostgreSQL ： /etc/odbcinst.ini
[PostgreSQL]
Description = PostgreSQL driver for Linux & Win64
Driver = /usr/lib64/odbc/psqlodbcw.so
常用命令：
odbcinst -j //查看本机安装odbc驱动版本信息
odbcinst -q -d //查看odbc ini配置文件配置数据库是否成功
isql -v freeswitch //验证odbc链接名字为freeswitch的链接是否成功
yum install unixODBC-devel mysql-connector-odbc
注意不要使用utf8_bin编码，尤其ip配置
UPDATE mysql.user
SET password=OLD_PASSWORD('somepassword')
WHERE user='someuser'
AND host='somehost';
./configure
尝试把odbc驱动拷贝到freeswitch目录
mkdir /usr/local/freeswitch/etc
ln -s /etc/odbcinst.ini /usr/local/freeswitch/etc/odbcinst.ini
ln -s /etc/odbc.ini /usr/local/freeswitch/etc/odbc.ini
Lua: LuaSQL /可以使用ODBC连接PostgreSQL、Oracle、or MySQL等数据库
freeswitch.Dbh //推荐
推荐使用freeswitch.Dbh,因为它使用数据库连接池，高效处理速度。
官网：
http://keplerproject.org/luasql/
样例：
-- this example of how to connect to postgres from Lua
local luasql = require "luasql.postgres"
--
-- Database connection settings
--
DBHOST = '127.0.0.1'
DBNAME = 'dbname'
DBUSER = 'dbusername'
DBPASS = 'dbpassword'
DBPORT = 5432
local env = assert (luasql.postgres())
local dbcon = env:connect(DBNAME, DBUSER, DBPASS, DBHOST, DBPORT)
sql = 'SELECT pin, name FROM customer'
res, serr = dbcon:execute(sql)
dbcon:close()
env:close()
相较于TCP连接访问数据库、LuaSQL env:connect(), freeswitch.Dbh在使用数据库连接池在数据库读写速度上有明显提高
local dbh = freeswitch.Dbh("dsn","user","pass") --ODBC
-- or --
local dbh = freeswitch.Dbh("core:my_db") -- sqlite
-- or --
local dbh = freeswitch.Dbh("sqlite://my_db") -- sqlite
-- or --
local dbh = freeswitch.Dbh("odbc://my_db:uname:passwd") --连接odbc数据库
assert(dbh:connected()) --判断数据库是否连接成功
dbh:test_reactive("SELECT * FROM my_table",
"DROP TABLE my_table",
"CREATE TABLE my_table (id INTEGER(8), name VARCHAR(255))")
dbh:query("INSERT INTO my_table VALUES(1, 'foo')") --查询语句
dbh:query("INSERT INTO my_table VALUES(2, 'bar')") --查询语句
dbh:query("SELECT id, name FROM my_table", function(row)
stream:write(string.format("%5s : %s\n", row.id, row.name))
end)
dbh:query("UPDATE my_table SET name = 'changed'")
stream:write("Affected rows: " .. dbh:affected_rows() .. "\n")
dbh:release() -- 释放关闭数据库连接
freeswitch.Dbh(odbc://my_db:uname:passwd) 获取ODBC数据库连接池
freeswitch.Dbh(“sqlite://my_db”) 获取核心数据库连接池，如果不存在数据库则创建
dbh:connected() 数据量连接返回true，否则返回false
dbh:test_reactive(“test_sql”, “drop_sql”, “reactive_sql”) 执行test_sql 且如果失败则执行drop_sql 和 reactive_sql
1
样例
-- this is an example of how to connect to Postgres using ODBC from Lua
local dbh = freeswitch.Dbh("odbc://my_db:uname:passwd") -- connect to ODBC database
assert(dbh:connected()) -- exits the script if we didn't connect properly
dbh:query("SELECT pin, name FROM customer", function(row)
stream:write(string.format("%5s : %s\n", row.id, row.name))
end)
dbh:release() -- optional
本示例展示的是如何从数据库地区变量值，并将其设置成Freeswitch通道变量
假使你有一个did_users数据库表，这个表有两列数据 did和user和主键
<extension name="map_did_to_user" continue="true">
<condition field="destination_number" expression="^(\+|00)(\d+)$">
<action inline="true" application="lua" data="map_did_to_user.lua $2"/>
</condition>
</extension>
<extension name="if_user_then_">
<condition field="${user}" expression="...
data="map_did_to_user.lua指向lua脚本
注意：
lua脚本必须以关键字运行，以产生channel变量
map_did_to_user.lua
-- map_did_to_user.lua
-- takes DID as first argument
local did = argv[1]
local dbh = freeswitch.Dbh("odbc://datasourcename:username:password")
local function set_session_variables(row)
-- Sets session variables with the same names as the columns from the database
for key, val in pairs(row) do
if session then
session:setVariable(key, val)
end
freeswitch.consoleLog("DEBUG", string.format("set(%s=%s)\n", key, val))
end
end
assert(dbh:connected())
local sql_query = "SELECT user FROM did_users WHERE did = " .. did
assert(dbh:query(sql_query, set_session_variables))
The script checks if a session is available. If it is, the key/value will be set as a channel variable. The result will always be printed to your console in the debug level.
脚本检查session是否存在，如果存在则将key/value设置成通道变量，结果会打印在debug级别的console日志控制台
domain, id, mailbox, number-alias, password, dial-string and user_context (all varchars).lua.conf.xml
<param name="xml-handler-script" value="gen_dir_user_xml.lua"/>
<param name="xml-handler-bindings" value="directory"/>
-- gen_dir_user_xml.lua
-- example script for generating user directory XML
-- comment the following line for production:
freeswitch.consoleLog("notice", "Debug from gen_dir_user_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://datasourcename:username:password")
if dbh:connected() == false then
freeswitch.consoleLog("notice", "gen_dir_user_xml.lua cannot connect to database" .. dsn .. "\n")
return
end
-- it's probably wise to sanitize input to avoid SQL injections !
local my_query = string.format("select * from users where domain = '%s' and `%s`='%s' limit 1",
req_domain, req_key, req_user)
assert (dbh:query(my_query, function(u) -- there will be only 0 or 1 iteration (limit 1)
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="password" value="]] .. u.password .. [["/>
<param name="dial-string" value="]] .. u["dial-string"] .. [["/>
</params>
<variables>
<variable name="user_context" value="]] .. u.user_context .. [["/>
</variables>
</user>
</domain>
</section>
</document>]]
-- comment the following line for production:
freeswitch.consoleLog("notice", "Debug from gen_dir_user_xml.lua, generated XML:\n" .. XML_STRING .. "\n")
end))
-- db_connect.lua
-- Connects to a database using freeswitch.Dbh connection pooling, checks PIN, reads balance
-- A hangup function makes the code a bit cleaner
local dbh = freeswitch.Dbh("odbc://datasourcename:username:password")
local row = {}
function hangup_call ()
session:streamFile("ivr/ivr-thank_you.wav")
session:sleep(250)
session:streamFile("voicemail/vm-goodbye.wav")
session:hangup()
end
if dbh:connected() == false then
freeswitch.consoleLog("notice", "gen_dir_user_xml.lua cannot connect to database" .. dsn .. "\n")
hangup_call()
end
-- Set invalid entry file
invalid = "ivr/ivr-that_was_an_invalid_entry.wav"
-- Greet caller
session:answer()
session:streamFile("ivr/ivr-hello.wav")
tries = 0
while (tries < 3) do
-- Collect account number
acct = session:playAndGetDigits(3, 5, 3, 7000, "#", "phrase:enter_message_number", invalid, ".+")
if (acct) then
-- Pull account from database -> assumes that acct is unique
my_query = "select * from users where acct = " .. acct
assert(dbh:query(my_query, function(qrow)
for key, val in pairs(qrow) do
row[key] = val
end
end))
end
-- Confirm that we received the record
if (row.pin ~= nil) then
-- We have an account, now collect PIN and check
tries = 0
while (tries < 3) do
pin = session:playAndGetDigits(3, 5, 3, 7000, "#", "ivr/ivr-please_enter_pin_followed_by_pound.wav", invalid, "\\d+")
if (row.pin == pin) then
user_repeat = true
while(user_repeat == true) do
session:streamFile("voicemail/vm-you_have.wav")
session:sleep(200)
session:say(row.balance, "en", "currency", "pronounced")
session:sleep(200)
-- "to repeat these options, please press 1"
digits = session:playAndGetDigits(1,1,3,7000,"#",
"file_string://ivr/ivr-to_repeat_these_options.wav!ivr/ivr-please.wav!voicemail/vm-press.wav!digits/1.wav",
invalid,"\\d+")
-- repeat y/n
freeswitch.consoleLog("INFO","User entered '" .. digits .. "'\n")
if (digits == "1") then
user_repeat = true
else
hangup_call()
break
end
end
else
-- Callerr entered wrong PIN
session:streamFile("ivr/ivr-that_was_an_invalid_entry.wav")
tries = tries + 1;
end
end
if (tries > 2) then
-- Too many failed attempts to enter PIN
session:streamFile("voicemail/vm-abort.wav")
hangup_call()
break
end
else
-- We did not find this account
session:streamFile(invalid)
tries = tries + 1;
end
end -- while (tries < 3)
if (tries > 2) then
session:streamFile("voicemail/vm-abort.wav")
hangup_call()
end
[root@localhost db]# ls
call_limit.db core.db fifo.db json.db sofia_reg_external.db sofia_reg_external-ipv6.db sofia_reg_internal.db sofia_reg_internal-ipv6.db voicemail_default.db
DSN格式
<type>://<connection string>
SQLite DSNs
sqlite://filename
sqlite:///usr/local/freeswitch/db/mega.db
PostgreSQL DSNs
pgsql://hostaddr=127.0.0.1 dbname=freeswitch user=freeswitch password= options='-c client_min_messages=NOTICE' application_name='freeswitch'"
ODBC DSNs
<param name="core-db-dsn" value="database:username:password"/>
or
<param name="core-db-dsn" value="odbc://database:username:password"/>
DSN with username
<param name="core-db-dsn" value="database:username:"/>
or
<param name="core-db-dsn" value="odbc://database"/>
DSN-less
<param name="core-db-dsn" value="odbc://DRIVER=mysql;SERVER=a.b.c.d;UID=username;PWD=secretpassword;DATABASE=freeswitch;OPTION=67108864">
conf/autoload_configs/switch.conf.xml
<param name="core-db-dsn" value="odbc://tuxza:admin:pass" />
or
<param name="odbc-dsn" value="odbc://your_dsn_name:your_db_user:your_db_password"/>
switch.conf.xml
<param name="core-recovery-db-dsn" value="odbc://your_dsn_name:your_db_user:your_db_password"/>
sofia.conf.xml
sip_profiles/external.xml
sip_profiles/internal.xml
sip_profiles/internal-ipv6.xml
db.conf.xml
voicemail.conf.xml
lcr.conf.xml
nibblebill.conf.xml
use("ODBC");
var db = new ODBC("dsnName", "username", "password");
db.connect();
Return value
true - 连接成功
false - 连接失败
use("ODBC");
var db = new ODBC("dsnName", "username", "password");
var sql="delete from users";
db.execute(sql);
boolean - true 连接成功
var row = db.getData();
console_log("notice",row["my_field"]+"\n");
返回值array
result = db.nextRow();
在getData前调用
boolean - true 表示有下一行数据
var row, result;
result = db.query("sql statement");
while (db.nextRow()) {
row = db.getData();
console_log("Row[0]: " + row[0] + "\n");
}
db.close();
example1
//Simple Example
use("ODBC");
var DSN = "test_odbc";
var DB_USER = "abc";
var DB_PASS = "abc";
var db = new ODBC(DSN, DB_USER, DB_PASS);
db.connect();
//Do something with our database connection.
db.close();
example2
//Example of opening a connection more than once in your script.
var db = new ODBC(DSN, DB_USER, DB_PASS);
db.connect();
//Do something...
db.close();
db = new ODBC(DSN, DB_USER, DB_PASS);
db.connect();
//Do something...
db.close();
<load module="mod_spidermonkey_odbc"/>
注意：由于公司网络限制及操作系统限制，在安装本软件的过程中会出现很多问题
需要先解决的问题：
1.网络问题 由于公司网络限制，即使在正确配置虚拟机网络设置的情况下，在主机和宿主机相互都可以ping通的前提下，任然无法上网，可以采用一下方法解决
a.在自己电脑上下载相关文件，传到公司电脑。
b.公司电脑连接自己手机热点下载（在这种情况下，在下载freeswitch过程中由于现在速度过慢可以选择从主机端下载，通过xftp传到宿主电脑，然后进行解压安装等操作）
VMware® Workstation 15 Pro（破解码网上有很多）
CentOS-6.6-x86_64-bin-DVD1.iso
freeswitch-1.6.6.tar.gz
网址：https://files.freeswitch.org/freeswitch-releases/
这个对方推荐直接从该网址进行下载，通过ftp传到宿主电脑，这样下载速度快一些
下载地址：https://www.newasp.net/soft/467373.html
破解：
用户名：newasp
公司名称：newasp
序列号：181030-116246-999646
使用xshell xftp插件和宿主机进行交互
freeswitch下载地址：
https://files.freeswitch.org/freeswitch-releases/
contOS6 数据库odbc连接驱动
centOS6 mysql数据库连接程序
点击 “文件”–》新建虚拟机–》典型–》下一步
点击浏览 ，这个地方选择前文所说下载好的centOS6的iso安装包，然后下一步
然后输入系统登录账户和密码，这个地方的设置类似于windows系统锁屏之后的账户和密码，和centOS系统内部root账户和密码无关。centOS系统内部账户和密码等会单独设置。
系统名称:vmware 打开界面之后点击该名称可以切换到系统启动页面
位置：这个位置很重要，一定要记住。最好放到不容易被删除的地方。如果向拷贝虚拟机系统镜像的话，直接蒋该文件夹下面和刚才新建的系统名称 相关文件全部打包拷贝至其他电脑上，就可以使用vmware直接打开克隆之后的系统
最大磁盘大小：这个可以设置大一点，不是立马生效的，类似与系统最大硬盘容量
点击下一步，之后即可完成虚拟系统的创建。
点击 “开启此虚拟机”。
至此，如果你出现下图的报错提示信息，说明你的电脑系统bios设置了禁用vitural machine。怎么办？联系桌面运维，将系统bios设置里面的带有 …vitural machine相关字眼的选项设置成enable（因为咱们自己没有权限啦，不能改，要装机密码的，所以就去找人家吧）
完成上一步之后，你就可以开始安装你刚才配置的centOS6的镜像了。
centOS6镜像的安装这个地方就不说了，有一点得提醒一下
系统账户密码，这个得设置
网络这个也得设置
假设你已经安装完成了centOS系统，而且可以正常登陆，那么进行系统第一件事情就是ping
ping -b www.baidu.com //查看是否可以上网
ping 你的主机ip //如果ping不通，返回虚拟机安装步骤 单独对虚拟机进行设置
如何查看自己的虚拟机的宿主机子ip,命令行: ip a ，下文中的
[root@localhost etc]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:7f:8a:a9 brd ff:ff:ff:ff:ff:ff
inet 192.168.122.132/24 brd 192.168.122.255 scope global eth0
inet6 fe80::20c:29ff:fe7f:8aa9/64 scope link
valid_lft forever preferred_lft forever
[root@localhost etc]#
eth0:虚拟网卡名称
inet 192.168.122.132 //你的虚拟机的ip地址
如何知道宿主机的ip？
上文一级知道自己虚拟机的ip地址，那么在下图中看网段192.168.122，我的是
VMnet8 这个是我现在用的虚拟网卡的名称
192.168.122.0 这个就是我的宿主机的ip地址
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UAVfmd0N-1573810445166)(D:\Users\chenyj91\AppData\Roaming\Typora\typora-user-images\1572854693511.png)]
好了，回到之前ping 的步骤,ping宿主机
ping 192.168.122.0
如果可以通，那么ok，如果不通
service iptables stop/start 关闭/启动防火墙
这里关闭防火墙 ，然后再ping
宿主机子ping虚拟机，如果不通那么关闭宿主机防火墙
ping 192.168.122.132
在确保之前步骤都执行通的前提下 再做下文。
修改centOS6系统root用户权限
cd etc
ls
vi sudoers
找到自己注册的账户名称的那一行，将第一个500,改成0
后文，打开root权限只需要：
[chen@localhost Desktop]$ su
Password:
[root@localhost Desktop]#
注意：这里如果你开通root权限，下文中使用xftp传文件的时候会显示没有权限。
安装步骤这里不说，一路下一步，激活环节参照上文注册码及用户名
这里只说链接虚拟机的配置，以xftp为例：
端口：22
主机：这个地方是你的虚拟机的ip地址
其他
传文件的界面如下
至此，假设你已经完成上文所有步骤，且验证正确。
freeswitch文件下载,选择下载xxx.tar.gz的1.6的压缩包
https://files.freeswitch.org/freeswitch-releases/
在windows系统上下载好之后通过ftp传至linux系统
or
命令行下载（这种方式很慢）
$ sudo yum install epel-release vim -y
$ curl -O http://files.freeswitch.org/freeswitch-releases/freeswitch-1.6.6.tar.bz2
$ sudo yum install bzip2 -y
$ tar xvjf freeswitch-1.6.6.tar.bz2
安装系统需要的插件
sudo yum install gcc-c++ sqlite-devel zlib-devel libcurl-devel pcre-devel speex-devel ldns-devel libedit-devel openssl-devel epel-release vim bzip2 -y
$ sudo yum install libjpeg-devel lua-devel libsndfile-devel libyuv-devel git libtool -y
如果你是下载的xxx.tar.gz格式的文件，则自己解压，至于文件存放路径自己创建
这里我默认的传到/根目录
[root@localhost /]#tar zxvf freeswitch-1.6.6.tar.gz
解压完成之后
[root@localhost /]# cd freeswitch-1.6.6
[root@localhost /]# ls //查看相关文件
安装freeswitch
[root@localhost freeswitch-1.6.6]# ./configure //如果报错 先解决报错，然后再执行这个命令，直到不报错，此版本本命令不报错
如果在make的过程中报错停止，请在命令行输入
[root@localhost freeswitch-1.6.6]# vi modules.conf 注释掉相关的模块即可，然后重复make命令
如果make命令执行完之后出现freeswitch图标，则表示make完成
至此，make install是不会报错的，make install 完成之后还是会出现freeswitch图标
启动freeswitch
[root@localhost local]# cd /usr/local/freeswitch/
[root@localhost bin]# ./freeswitch //启动
安装mysql及MySQ odbc
[root@localhost bin]# yum search mysql //找到mysql-connector-odbc.x86_64和mysql-server.x86_64 分别执行yum install xxx进行安装
[root@localhost bin]# yum search odbc //找到unixODBC.x86_64 ，俺也和上面一样安装
mysql常用命令：
查看是否安装了mysql数据库：rpm -qa | grep mysql
强制卸载mysql数据库：rpm -e nodeps mysql
通过命令查看yum上提供的数据库可下载版本：yum list | grep mysql
安装数据库： yum -y install mysql-server mysql-devel
启动数据库： service mysqld start
查看是否是开机启动(若2~5都是on则表明是开机启动)：
chkconfig --list | grep mysqld
若2~5都是off则用以下命令设置： chkconfig mysqld on
chkconfig --list | grep mysqld
启动mysql： service mysqld start
设置用户及密码(输入以下命令后两次回车，及第一次密码直接回车即可)： mysqladmin -u root -p password 'root'
测试登录mysql： mysql -u root -p
停止数据库 service mysqld stop
常用命令
[root@localhost bin]#odbcinst -j //这个地方主要看配置文件路径和驱动地址
[root@localhost bin]# odbc -q -d //查看odbcinst.ini文件配置成功的数据库连接名称
[root@localhost bin]#isql -v freeswitch root root //执行该命令查看odbc.ini文件中配置的数据库名称为freeswitch，账户和密码为 root root 的数据库数据库连接是否成功
[root@localhost etc]# odbcinst -j
unixODBC 2.3.7
DRIVERS............: /usr/local/etc/odbcinst.ini //下文中要修改的文件地址
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini //下文中要修改的文件地址
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
[MySQL]
Description = MySQL driver
Driver = /usr/lib64/libmyodbc5.so
Setup = /usr/lib64/odbc/libodbcmyS.so //驱动地址
UsageCount = 1
FileUsage = 1
Threading = 0
libmyodbc5.so//驱动地址 64–lib64 32–lib 这个驱动文件是mysql安装后生成的
[freeswitch]
Driver = MySql
SERVER =locahost
PORT =3306
DATABASE = freeswitch_db
USER = root
PASSWORD = root
正确编辑完上面两个文件之后，验证
[root@localhost bin]#isql -v freeswitch root root //进行验证
如果执行isql -v freeswitch root root ,显示下面的信息，表示数据库连接成功
[root@localhost etc]# isql -v freeswitch root root
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select * from user
本实例意在修改宿主机mysql数据库运行虚拟机进行访问，模拟生产环境freeswitch调用远程数据库获取信息
D:\Users\chenyj91>mysql -uroot -proot
mysql> show databases;
mysql> use mysql;
mysql> select User,authentication_string,Host from user;
查看到的结果如下：
mysql> select User,authentication_string,Host from user;
+------+-----------------------+-----------+
| User | authentication_string | Host |
+------+-----------------------+-----------+
| root | | localhost |
+------+-----------------------+-----------+
1 row in set (0.01 sec)
这里可以看出root默认都是localhost访问权限
修改访问权限
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root';
Query OK, 0 rows affected (0.01 sec)
%代表所有主机，也可以具体到你的主机ip地址，root表示远程连接的密码
刷新权限策略：
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)
查看结果：
mysql> select User,authentication_string,Host from user;
+------+-----------------------+-----------+
| User | authentication_string | Host |
+------+-----------------------+-----------+
| root | | localhost |
| root | NULL | % |
+------+-----------------------+-----------+
修改odbc.ini进行验证
[freeswitch]
Driver = MySql
SERVER =192.168.122.1
PORT =3306
DATABASE = freeswitch_db
USER = root
PASSWORD = root
重启宿主机mysql服务
新建数据表 freeswitch_db
验证：
[root@localhost etc]# isql -v freeswitch root root
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
至此 ，连接远程mysql 数据库成功
该表用于存储拨号计划中的电话号码
CREATE TABLE
USER
(
ID INT(40) NOT NULL AUTO_INCREMENT,
NAME VARCHAR(40),
PASSWORD VARCHAR(40),
PRIMARY KEY (ID)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入数据
#插入两个用户
insert into user (user,password) values('8003','123456')
将freeswitch/conf/autoload_configs/lua.conf.xml中的两行代码修改为如下所示
<param name="xml-handler-script" value="gen_dir_user_xml.lua" />
<param name="xml-handler-bindings" value="directory" />
这里调用的脚本是freeswitch/script/gen_dir_user_xml.lua内容如下，默认无该脚本，需自行创建，这里我添加了一些中文日志，等会我们修改完之后再freesitch控制台查看这些日志，验证我们添加的模块是否起作用。
freeswitch.consoleLog("NOTICE","脚本获取user数据...\n");
local req_domain = params:getHeader("domain")
local req_key = params:getHeader("key")
local req_user = params:getHeader("user")
local req_password = params:getHeader("pass")
freeswitch.consoleLog("NOTICE","开始连接Mysql数据库...\r\n");
local dbh = freeswitch.Dbh("freeswitch","root","root");
assert(dbh:connected());
dbh:query("select password from user where name="..req_user,function(row)
freeswitch.consoleLog("NOTICE","Mysql select password="..string.format("%s\n",row.password))
req_password=string.format("%s",row.password)
end);
dbh:release();
freeswitch.consoleLog("NOTICE","info:"..req_domain.."--"..req_key.."--"..req_user.."--"..req_password.."\n");
if req_domain ~= nil and req_key ~= nil and req_user ~= nil then
XML_STRING =
[[<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<document type="freeswitch/xml">
<section name="directory">
<domain name="]]..req_domain..[[">
<params>
<param name="dial-string"
value="{presence_id=${dialed_user}@${dialed_domain}}${sofia_contact(${dialed_user}@${dialed_domain})}"/>
</params>
<groups>
<group name="default">
<users>
<user id="]]..req_user..[[">
<params>
<param name="password" value="]]..req_password..[["/>
<param name="vm-password" value="]]..req_password..[["/>
</params>
<variables>
<variable name="toll_allow" value="domestic,international,local"/>
<variable name="accountcode" value="]]..req_user..[["/>
<variable name="user_context" value="default"/>
<variable name="directory-visible" value="true"/>
<variable name="directory-exten-visible" value="true"/>
<variable name="limit_max" value="15"/>
<variable name="effective_caller_id_name" value="Extension ]]..req_user..[["/>
<variable name="effective_caller_id_number" value="]]..req_user..[["/>
<variable name="outbound_caller_id_name" value="${outbound_caller_name}"/>
<variable name="outbound_caller_id_number" value="${outbound_caller_id}"/>
<variable name="callgroup" value="techsupport"/>
</variables>
</user>
</users>
</group>
</groups>
</domain>
</section>
</document>]]
else
XML_STRING =
[[<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<document type="freeswitch/xml">
<section name="directory">
</section>
</document>]]
end
freeswitch.consoleLog("NOTICE", "debug from gen_dir_user_xml.lua, generated XML:\n" .. XML_STRING .. "\n");
注意：
local dbh = freeswitch.Dbh(“freeswitch”,“root”,“root”);
这个地方的数据库职的是odb.ini文件中[]中的那个名字，不是mysql数据库中的数据库名字
修改freeswitch/conf/directory/default.xml中的一部分内容，使得通过xml验证用户的功能失效，这样lua才能真正接管用户注册，删除的内容如下：
<group name="default">
<users>
<X-PRE-PROCESS cmd="include" data="default/*.xml"/>
</users>
</group>
刚才我们在数据库添加了一条8003的账户，密码为123456；本实例这里我们一直尚未修改freeswitch默认密码：1234;只是将密码123456作为一个反例来验证电话号注册的过程。
freeswitch@localhost.localdomain> 2019-11-05 07:37:04.090718 [NOTICE] switch_cpp.cpp:1356 脚本获取user数据...
2019-11-05 07:37:04.090718 [NOTICE] switch_cpp.cpp:1356 开始连接Mysql数据库...
2019-11-05 07:37:04.090718 [CRIT] switch_core_sqldb.c:483 Failure! ODBC NOT AVAILABLE! Can't connect to DSN freeswitch_db
2019-11-05 07:37:04.090718 [ERR] freeswitch_lua.cpp:367 Connection failed. DBH NOT Connected.
2019-11-05 07:37:04.090718 [ERR] mod_lua.cpp:203 /usr/local/freeswitch/scripts/gen_dir_user_xml.lua:11: assertion failed!
stack traceback:
[C]: in function 'assert'
/usr/local/freeswitch/scripts/gen_dir_user_xml.lua:11: in main chunk
2019-11-05 07:37:04.090718 [ERR] mod_lua.cpp:270 LUA script parse/execute error!
2019-11-05 07:37:04.090718 [WARNING] sofia_reg.c:2874 Can't find user [8003@192.168.122.132] from 192.168.122.1
You must define a domain called '192.168.122.132' in your directory and add a user with the id="8003" attribute
and you must configure your device to use the proper domain in it's authentication credentials.
2019-11-05 07:37:04.090718 [NOTICE] switch_cpp.cpp:1356 脚本获取user数据...
2019-11-05 07:37:04.090718 [NOTICE] switch_cpp.cpp:1356 开始连接Mysql数据库...
2019-11-05 07:37:04.090718 [CRIT] switch_core_sqldb.c:483 Failure! ODBC NOT AVAILABLE! Can't connect to DSN freeswitch_db
2019-11-05 07:37:04.090718 [ERR] freeswitch_lua.cpp:367 Connection failed. DBH NOT Connected.
2019-11-05 07:37:04.090718 [ERR] mod_lua.cpp:203 /usr/local/freeswitch/scripts/gen_dir_user_xml.lua:11: assertion failed!
stack traceback:
[C]: in function 'assert'
/usr/local/freeswitch/scripts/gen_dir_user_xml.lua:11: in main chunk
2019-11-05 07:37:04.090718 [ERR] mod_lua.cpp:270 LUA script parse/execute error!
2019-11-05 07:37:04.090718 [WARNING] sofia_reg.c:2874 Can't find user [8003@192.168.122.132] from 192.168.122.1
You must define a domain called '192.168.122.132' in your directory and add a user with the id="8003" attribute
and you must configure your device to use the proper domain in it's authentication credentials.
2019-11-05 07:37:07.951807 [NOTICE] switch_cpp.cpp:1356 脚本获取user数据...
2019-11-05 07:37:07.951807 [NOTICE] switch_cpp.cpp:1356 开始连接Mysql数据库...
2019-11-05 07:37:07.951807 [CRIT] switch_core_sqldb.c:483 Failure! ODBC NOT AVAILABLE! Can't connect to DSN freeswitch_db
2019-11-05 07:37:07.951807 [ERR] freeswitch_lua.cpp:367 Connection failed. DBH NOT Connected.
2019-11-05 07:37:07.951807 [ERR] mod_lua.cpp:203 /usr/local/freeswitch/scripts/gen_dir_user_xml.lua:11: assertion failed!
stack traceback:
[C]: in function 'assert'
/usr/local/freeswitch/scripts/gen_dir_user_xml.lua:11: in main chunk
2019-11-05 07:37:07.951807 [ERR] mod_lua.cpp:270 LUA script parse/execute error!
2019-11-05 07:37:07.951807 [WARNING] sofia_reg.c:2874 Can't find user [8003@192.168.122.132] from 192.168.122.1
You must define a domain called '192.168.122.132' in your directory and add a user with the id="8003" attribute
and you must configure your device to use the proper domain in it's authentication credentials.
2019-11-05 07:37:08.050764 [NOTICE] switch_cpp.cpp:1356 脚本获取user数据...
2019-11-05 07:37:08.050764 [NOTICE] switch_cpp.cpp:1356 开始连接Mysql数据库...
2019-11-05 07:37:08.050764 [CRIT] switch_core_sqldb.c:483 Failure! ODBC NOT AVAILABLE! Can't connect to DSN freeswitch_db
2019-11-05 07:37:08.050764 [ERR] freeswitch_lua.cpp:367 Connection failed. DBH NOT Connected.
2019-11-05 07:37:08.050764 [ERR] mod_lua.cpp:203 /usr/local/freeswitch/scripts/gen_dir_user_xml.lua:11: assertion failed!
stack traceback:
[C]: in function 'assert'
/usr/local/freeswitch/scripts/gen_dir_user_xml.lua:11: in main chunk
2019-11-05 07:37:08.050764 [ERR] mod_lua.cpp:270 LUA script parse/execute error!
2019-11-05 07:37:08.050764 [NOTICE] switch_cpp.cpp:1356 脚本获取user数据...
2019-11-05 07:37:08.050764 [NOTICE] switch_cpp.cpp:1356 开始连接Mysql数据库...
2019-11-05 07:37:08.050764 [CRIT] switch_core_sqldb.c:483 Failure! ODBC NOT AVAILABLE! Can't connect to DSN freeswitch_db
2019-11-05 07:37:08.050764 [ERR] freeswitch_lua.cpp:367 Connection failed. DBH NOT Connected.
2019-11-05 07:37:08.050764 [ERR] mod_lua.cpp:203 /usr/local/freeswitch/scripts/gen_dir_user_xml.lua:11: assertion failed!
stack traceback:
[C]: in function 'assert'
/usr/local/freeswitch/scripts/gen_dir_user_xml.lua:11: in main chunk
2019-11-05 07:37:08.050764 [ERR] mod_lua.cpp:270 LUA script parse/execute error!
报错：
ODBC驱动不可用
解决办法：
1.首先检查unixODBC与unixODBC-devel安装是否有问题，在看下isql -v DSN是否能连接上
2.如果安装都没问题，那就可能是安装时没有./configure --enable-core-odbc-support,
再进入freeswitch源码目录下，执行重新make && make install就OK了
这里我们看到了刚才添加的日志信息，还有8003
刚才注册电话号码的时候 密码用的是123456,将数据库表user中8003的密码改成1234,且修改软电话注册页面的密码为1234重新点击register.这里为了看的清楚，最好将之前的freeswitch打印的日志信息清理一下；
日志信息如下
freeswitch@localhost.localdomain> 2019-11-05 07:43:00.492554 [NOTICE] switch_cpp.cpp:1356 脚本获取user数据...
2019-11-05 07:43:00.492554 [NOTICE] switch_cpp.cpp:1356 开始连接Mysql数据库...
2019-11-05 07:43:00.492554 [CRIT] switch_core_sqldb.c:483 Failure! ODBC NOT AVAILABLE! Can't connect to DSN freeswitch_db
2019-11-05 07:43:00.492554 [ERR] freeswitch_lua.cpp:367 Connection failed. DBH NOT Connected.
2019-11-05 07:43:00.492554 [ERR] mod_lua.cpp:203 /usr/local/freeswitch/scripts/gen_dir_user_xml.lua:11: assertion failed!
stack traceback:
[C]: in function 'assert'
/usr/local/freeswitch/scripts/gen_dir_user_xml.lua:11: in main chunk
2019-11-05 07:43:00.492554 [ERR] mod_lua.cpp:270 LUA script parse/execute error!
2019-11-05 07:43:00.492554 [WARNING] sofia_reg.c:2874 Can't find user [8003@192.168.122.132] from 192.168.122.1
You must define a domain called '192.168.122.132' in your directory and add a user with the id="8003" attribute
and you must configure your device to use the proper domain in it's authentication credentials.
2019-11-05 07:43:00.591532 [NOTICE] switch_cpp.cpp:1356 脚本获取user数据...
2019-11-05 07:43:00.591532 [NOTICE] switch_cpp.cpp:1356 开始连接Mysql数据库...
这里一个8003的账户就注册成功了