The ODBC connector is a database abstraction layer that makes it possible for Asterisk to communicate with a wide range of databases without requiring the developers to create a separate database connector for every database Asterisk wants to support. This saves a lot of development effort and code maintenance. There is a slight performance cost to this because we are adding another application layer between Asterisk and the database. However, this can be mitigated with proper design and is well worth it when you need powerful, flexible database capabilities in your Asterisk system.
Before we install the connector in Asterisk, we have to install ODBC into Linux itself. To install the ODBC drivers, simply run the command:
# yum install -y unixODBC unixODBC-devel libtool-ltdl libtool-ltdl-devel
See Chapter 3, Installing Asterisk for the matrix of packages you should have installed.
We need to install the unixODBC-devel package because it is used by Asterisk to create the ODBC modules we will be using throughout this chapter.
Verify that you have the PostgreSQL ODBC driver configured in the
/etc/odbcinst.ini file. It should
look something like this:
[PostgreSQL] Description = ODBC for PostgreSQL Driver = /usr/lib/libodbcpsql.so Setup = /usr/lib/libodbcpsqlS.so FileUsage = 1
Verify the system is able to see the driver by running the following
command. It should return the label name PostgreSQL if
all is well.
#odbcinst -q -d[PostgreSQL]
Next, configure the /etc/odbc.ini file, which is used to create an
identifier that Asterisk will use to reference this configuration. If at
any point in the future you need to change the database to something else,
you simply need to reconfigure this file, allowing Asterisk to continue to
point to the same place.[135]
[asterisk-connector] Description = PostgreSQL connection to 'asterisk' database Driver = PostgreSQL Database = asterisk Servername = localhost UserName = asterisk Password = welcome Port = 5432 Protocol = 7.4 ReadOnly = No RowVersioning = No ShowSystemTables = No ShowOidColumn = No FakeOidIndex = No ConnSettings =
Let’s verify that we can connect to our database using the isql application. The isql
application will not perform the connect as the root
user, and must be run as the same owner as the database. Since the owner
of the asterisk database under PostgreSQL is the
asterisk user, we must create a Linux account with
the same name. In Chapter 14, Potpourri, we will take
advantage of this user to run Asterisk as
non-root.
#su - asterisk$echo "select 1" | isql -v asterisk-connector+---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> +------------+ | ?column? | +------------+ | 1 | +------------+ SQLRowCount returns 1 1 rows fetched$exit
With
unixODBC installed, configured, and verified to work,
we need to recompile Asterisk so that the ODBC modules are created and
installed. Change back to your Asterisk sources directory and run the
./configure script so it knows you
have installed unixODBC.
#cd /usr/src/asterisk-1.4#make distclean#./configure#make menuselect#make install
Almost everything in this chapter is turned on by default. You
will want to run make menuselect to verify that the
ODBC related modules are enabled. These include
cdr_odbc, func_odbc,
func_realtime, pbx_realtime,
res_config_odbc, res_odbc.
For voicemail stored in an ODBC database, be sure to select
ODBC_STORAGE from the Voicemail Build Options menu.
You can verify the modules exist in the /usr/lib/asterisk/modules/
directory.
ODBC connections are configured in the res_odbc.conf file located in /etc/asterisk. The res_odbc.conf file sets the parameters that the various Asterisk modules will use to connect to the database.[136]
Modify the res_odbc.conf file:
[asterisk] enabled => yes dsn => asterisk-connector username => asterisk password => welcome pooling => no limit => 0 pre-connect => yes
The dsn option points at the database connection
we configured in /etc/odbc.ini, and
the pre-connect option tells Asterisk to open up and maintain a connection to the
database when loading the res_odbc.so module. This
lowers some of the overhead that would come from repeatedly setting up
and tearing down the connection to the database.
Once you’ve configured res_odbc.conf, start
Asterisk and verify the database connection with the odbc show CLI command:
*CLI> odbc show
Name: asterisk
DSN: asterisk-connector
Pooled: no
Connected: yes
[135] Yes, this is excessively verbose. The only entries you really
need are Driver, Database, and Servername. Even the Username and Password are specified elsewhere, as seen
later.
[136] The pooling and limit options are quite useful for MS SQL Server and Sybase
databases. These permit you to establish multiple connections (up to
limit connections) to a database
while ensuring that each connection has only one statement executing
at once (this is due to a limitation in the protocol used by these
database servers).