Skip to main content

Installing LuaSQL

About

LuaSQL is a simple interface from Lua to a DBMS - LuaSQL

It is also possible to use the FS ODBC support through freeswitch.Dbh, without installing LuaSQL

Since FreeSWITCH 1.4, you need to use Lua5.2.

For those willing to keep using Lua5.1 for backwards compatibility reasons, there is a mod_lua available in legacy directory `freeswitch/src/mod/legacy/languages/mod_lua/`

Click here to expand ToC

Dependencies

You will need to install Lua5.2, for instance on Debian7, you can run the following:

#Install Dependencies
apt-get install -y lua5.2 liblua5.2-dev
apt-get install -y libpq-dev

Install LuaSQL

Download LuaSQL 2.3.0:

wget https://github.com/keplerproject/luasql/archive/v2.3.0.zip
unzip v2.3.0.zip
cd luasql-2.3.0/

You will need to change the 'config' file to reflect your system and the DBMS to interface with.

The config file is divided into three broad sections:

  1. DB Name
  2. Paths to different libraries
  3. Paths to DB related libraries

First check the #2 if it matches your flavor of OS.

Then, you have to select only *one* driver to compile at a time. Suppose you want to compile MySQL, then you have to uncomment only the MySQL line from #1 and #3. All other lines in both these sections should be commented.

MySQL Server

sed -i 's/#T= mysql/T= odbc/g' config
sed -i 's/T=sqlite3/#T=sqlite3/g' config

On RHEL / CentOS, the path for MySQL libs and include is not the same as the default config file that comes with LuaSQL.

Please make the Driver parameters changes as follows:

DRIVER_LIBS= -L/usr/lib/mysql -lmysqlclient -lz
DRIVER_INCS= -I/usr/include/mysql

On x64_86 architecture, you need to change a few more things in 'config' file

Set proper values for LUA_LIBDIR, LUA_DIR , LUA_INC , DRIVER_LIBS, DRIVER_INCS

To allow compile properly, you will also need -fPIC:

sed -i 's/WARN= /WARN= -fPIC /g' config 

Then compile and install:

make
make install

ODBC

If earlier you wanted to use Microsoft SQL Server earlier you would have changed the following:

sed -i 's/#T= mysql/T= odbc/g' config
sed -i 's/T=sqlite3/#T=sqlite3/g' config

Then make the Driver parameters changes as follows:

DRIVER_LIBS= -L/usr/lib64 -lodbc
DRIVER_INCS= -DUNIXODBC -I/usr/include

Compile and install!

Script examples

LuaSQL Mysql example

Next you can do something like:

#!/usr/local/bin/lua
require "luasql.mysql"

env = assert (luasql.mysql())
con = assert (env:connect("database","username","password","localhost"))
cur = assert (con:execute"SELECT * FROM table")
row = cur:fetch ({}, "a")

session:setVariable("varname", tostring(row.column));

cur:close()
con:close()
env:close()

LuaSQL ODBC example

For ODBC, you need to edit the odbc.ini and odbcinst.ini (both in /etc on RHEL). A typical MySQL configuration looks like:

File: odbcinst.ini

# Driver from the MyODBC package
# Setup from the unixODBC package
[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib/libmyodbc.so
Setup = /usr/lib/libodbcmyS.so
FileUsage = 1

Note that the *.so files should be in the folder.
File: odbc.ini
[mydsn]
Driver = MySQL
SERVER = localhost
PORT = 3306
DATABASE = mydatabase
OPTION = 67108864
SOCKET = /var/lib/mysql/mysql.sock

NOTE: I found you also need to set some environment variables for scripts to run properly

echo export LUA_PATH=/usr/local/freeswitch/scripts/?.lua >> /etc/bashrc
echo export LUA_CPATH=/usr/local/freeswitch/scripts/?.so >> /etc/bashrc
echo export PATH=$PATH:/usr/local/freeswitch/bin >> ~/.bashrc

NOTE: you need to symlink the shared object (i.e., mysql.so) to /usr/local/lib/lua/5.1/luasql/mysql.so

See also

  • Script to install LuaSQL with Postgresql on Debian7 Here