Relational Database Integration
we are going to explore integrating some Asterisk features and functions into a database. There are several databases available for Linux, but we have chosen to limit our discussion to the two most popular: PostgreSQL and MySQL. We will also explain how to configure Linux to connect to a Microsoft SQL database via ODBC; however, configuration of the Windows/Microsoft portion is beyond the scope.
Regardless of which database you use, this chapter focuses primarily on the ODBC connector, so as long as you have some familiarity with getting your favorite database ODBC-ready.
Integrating Asterisk with databases is one of the fundamental aspects of building a large clustered or distributed system. The power of the database will enable you to use dynamically changing data in your dialplans, for tasks such as sharing information across an array of Asterisk systems or integrating with web-based services.
Installing and Configuring PostgreSQL and MySQL
we will show how to install and configure PostgreSQL and MySQL on both RHEL and Ubuntu. 1 It is recommended that you only install one database at a time while working through this section. Pick the database you are most comfortable with, as there is no wrong choice.
Installing PostgreSQL for RHEL
The following command can be used to install the PostgreSQL server and its dependencies from the console:
$ sudo yum install postgresql-server
…
Install 3 Package(s)
Upgrade 0 Package(s)
Total download size: 6.3 M
Installed size: 29 M
Is this ok [y/N]: y
After installing the database, you must initialize it:
$ sudo service postgresql initdb
Then start the database:
$ sudo service postgresql start
Installing PostgreSQL for Ubuntu
To install PostgreSQL on Ubuntu, run the following command. You will be prompted to also install any additional packages that are dependencies of the application. Press Enter to accept the list of dependencies, at which point the packages will be installed and PostgreSQL will be automatically started and initialized:
$ sudo apt-get install postgresql
…
After this operation, 19.1MB of additional disk space will be used.
Do you want to continue [Y/n]? y
Installing MySQL for RHEL
To install MySQL on RHEL, run the following command. You will be prompted to install several dependencies. Press Enter to accept, and the MySQL server and dependency packages will be installed:
$ sudo yum install mysql-server
…
Install 3 Package(s)
Upgrade 0 Package(s)
Total download size: 9.6 M
Installed size: 27 M
Is this ok [y/N]: y
Then start the MySQL database by running:
$ sudo service mysqld start
Installing MySQL for Ubuntu
To install MySQL on Ubuntu, run the following command. You will be prompted to install several dependencies. Press Enter to accept, and the MySQL server and its dependency packages will be installed:
$ sudo apt-get install mysql-server
Need to get 24.0MB of archives.
After this operation, 60.6MB of additional disk space will be used.
Do you want to continue [Y/n]? y
During the installation, you will be placed into a configuration wizard to help you through the initial configuration of the database. You will be prompted to enter a new password for the root user. Type in a strong password and press Enter. You will then be asked to confirm the password. Type your strong password again, followed by Enter. You will then be returned to the console, where the installation will complete. The MySQL service will now be running.
Configuring PostgreSQL
Next, create a user called asterisk, which you will use to connect to and manage the database. You can switch to the postgres user by using the following command:
Then run the following commands to create the asterisk user in the database and set up
permissions:
$ createuser -P
Enter name of user to add: asterisk
Enter password for new user:
Enter it again:
Shall the new role be a superuser? (y/n) n
Shall the new user be allowed to create databases? (y/n) y
Shall the new user to add: asterisk be allowed to create more new users? (y/n) n
CREATE ROLE
Now, edit the pg_hba.conf file in order to allow the asterisk user you just created to connect to the PostgreSQL server over the TCP/IP socket.
On RHEL, this file will be located at /var/lib/pgsql/data/pg_hba.conf. On Ubuntu, you will find it at /etc/postgresql/8.4/main/pg_hba.conf.
At the end of the file, replace everything below this line:
# TYPE DATABASE USER CIDR-ADDRESS METHOD
with the following:
# TYPE DATABASE USER CIDR-ADDRESS METHOD
host all asterisk 127.0.0.1/32 md5
local all asterisk trust
Now you can create the database that we will use throughout this chapter. Call the database asterisk and set the owner to your asterisk user:
$ createdb –owner=asterisk asterisk
CREATE DATABASE
You can set the password for the asterisk user like so:
$ psql -d template1
template1=# “ALTER USER asterisk WITH PASSWORD ‘password'”
template1=# \q
Exit from the postgres user:
$ exit
Then restart the PostgreSQL server. On RHEL:
$ sudo service postgresql restart
On Ubuntu:
$ sudo /etc/init.d/postgresql restart
You can verify your connection to the PostgreSQL server via TCP/IP, like so:
$ psql -h 127.0.0.1 -U asterisk
Password for user asterisk:
Welcome to psql 9.1, the PostgreSQL interactive terminal.
Type:
\copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
asterisk=>
Configuring MySQL
With the MySQL database now running, you should secure your installation. Conveniently, there is a script you can execute that will allow you to enter a new password for the root user, along with some additional options. The script is pretty straightforward, and after entering and confirming your root password you can continue to select the defaults unless you have a specific reason not to.
Execute the following script:
$ sudo /usr/bin/mysql_secure_installation
Then connect to the database console so you can create your asterisk user and set up
permissions:
$ mysql -u root -p
Enter password:
After entering the password, you will be presented with the mysql console prompt. You can now create your asterisk user by executing the CREATE USER command. The % is a wildcard indicating the asterisk user can connect from any host and is IDENTIFIED BY the password some_secret_password (which you should obviously change). Note the trailing semicolon:
mysql> CREATE USER ‘asterisk’@’%’ IDENTIFIED BY ‘some_secret_password’;
Query OK, 0 rows affected (0.00 sec)
Let’s also create the initial database you’ll use throughout this chapter:
mysql> CREATE DATABASE asterisk;
Query OK, 1 rows affected (0.00 sec)
Now that you’ve created your user and database, you need to assign permissions for the asterisk user to access the asterisk database:
mysql> GRANT ALL PRIVILEGES ON asterisk.* TO ‘asterisk’@’%’;
Query OK, 0 rows affected (0.00 sec)
Finally, exit from the console and verify that your permissions are correct by logging back into the asterisk database as the asterisk user:
mysql> exit
Bye
# mysql -u asterisk -p asterisk
Enter password:
mysql>
You will need this password when you configure and test the ODBC connector, so keep it handy. You’re now ready to move on to the next section.
Installing and Configuring ODBC
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 connector for every database Asterisk wants to support. This saves a lot of development effort and code maintenance. There is a slight performance cost, because we are adding another application layer between Asterisk and the database, but this can be mitigated with proper design and is well worth it when you need powerful, flexible database capabilities in your Asterisk system.
Before you install the connector in Asterisk, you have to install ODBC into Linux itself. To install the ODBC drivers, use one of the following commands.
On RHEL:
$ sudo yum install unixODBC unixODBC-devel libtool-ltdl libtool-ltdl-devel
On Ubuntu:
$ sudo apt-get install unixODBC unixODBC-dev
You’ll also need to install the unixODBC development package, because Asterisk uses it to build the ODBC modules
To install the MySQL ODBC connector on RHEL:
$ sudo yum install mysql-connector-odbc
To install the PostgreSQL ODBC connector on RHEL:
$ sudo yum install postgresql-odbc
To install the PostgreSQL ODBC connector on Ubuntu:
$ sudo apt-get install odbc-postgresql
Or to install the MySQL ODBC connector on Ubuntu:
$ sudo apt-get install libmyodbc
Configuring ODBC for PostgreSQL
Configuration for the PostgreSQL ODBC driver is done in the /etc/odbcinst.ini file.
On RHEL, the default file already contains some data, including that for PostgreSQL, so just verify that the data exists. The file will look like the following:
[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/lib/psqlodbc.so
Setup = /usr/lib/libodbcpsqlS.so
Driver64 = /usr/lib64/psqlodbc.so
Setup64 = /usr/lib64/libodbcpsqlS.so
FileUsage = 1
On Ubuntu, the /etc/odbcinst.ini file will be blank, so you’ll need to add the data to that configuration file. Add the following to the odbcinst.ini file:
[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/lib/odbc/psqlodbca.so
Setup = /usr/lib/odbc/libodbcpsqlS.so
FileUsage = 1
In either case, you can use cat > /etc/odbcinst.ini to write a clean configuration file, as we’ve done in other chapters. Just use Ctrl+D to save the file once you’re done.
Verify that 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:
[asterisk-connector]
Description = PostgreSQL connection to ‘asterisk’ database
Driver = PostgreSQL
Database = asterisk
Servername = localhost
Port = 5432
Protocol = 8.1
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
ConnSettings =
Configuring ODBC for MySQL
Configuration for the MySQL ODBC driver is done in the /etc/odbcinst.ini file.
On RHEL, the default file already contains some data, including for MySQL, but it needs
to be uncommented and requires a couple of changes. Replace the existing text with the
following:
[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib/libmyodbc5.so
Setup = /usr/lib/libodbcmyS.so
Driver64 = /usr/lib64/libmyodbc5.so
Setup64 = /usr/lib64/libodbcmyS.so
FileUsage = 1
On Ubuntu, the /etc/odbcinst.ini file will be blank, so you’ll need to add the data to that configuration file. Add the following to the odbcinst.ini file:
[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib/odbc/libmyodbc.so
Setup = /usr/lib/odbc/libodbcmyS.so
FileUsage = 1
In either case, you can use cat > /etc/odbcinst.ini to write a clean configuration file. Just use Ctrl+D to save the file once you’re done.
Verify that the system is able to see the driver by running the following command. It should return the label name MySQL if all is well:
$ odbcinst -q -d
[MySQL]
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:
[asterisk-connector]
Description = MySQL connection to ‘asterisk’ database
Driver = MySQL
Database = asterisk
Server = localhost
Port = 3306
Socket = /var/lib/mysql/mysql.sock
The location of this file could be different, so you might have to find it on your system.
Configuring ODBC for Microsoft SQL
Connecting to Microsoft SQL (MS SQL) is similar to connecting to either MySQL or PostgreSQL, as we’ve previously discussed. The configuration of MS SQL is beyond the scope of this book, but the following information will get your Asterisk box configured to connect to your MS SQL database once you’ve enabled the appropriate permissions on your database.
To connect to MS SQL, you need to install the FreeTDS drivers using the package manager (or by compiling via the source files available at http://www.freetds.org).
On RHEL:
$ sudo yum install freetds
On Ubuntu:
$ sudo apt-get install tdsodbc
After installing the drivers, you need to configure the /etc/odbcinst.ini file, which tells the system where the driver files are located.
Insert the following text into the /etc/odbcinst.ini file with your favorite text editor or with the following command:
$ sudo cat > /etc/odbcinst.ini
[FreeTDS]
Description = ODBC for Microsoft SQL
Driver = /usr/lib/i386-linux-gnu/odbc/libtdsodbc.so
UsageCount = 1
Threading = 2
Ctrl+D
Verify that the system is able to see the driver by running the following command. It should return the label name FreeTDS if all is well:
$ odbcinst -q -d
[FreeTDS]
Once you’ve configured the drivers, you need to modify the /etc/odbc.ini file to control how to connect to the database:
[asterisk-connector]
Description = MS SQL connection to ‘asterisk’ database
Driver = FreeTDS
Database = asterisk
Server = 192.168.100.1
Trace = No
TDS_Version = 7.0
Port = 1433
In the next section, you will be able to validate your connection to the MS SQL server.
Validating the ODBC Connector
Now, verify that you can connect to your database using the isql application. echo the select 1 statement and pipe it into isql, which will then connect using the asterisk-connector section you added to /etc/odbc.ini. You should get the following output (or at least something similar; we’re looking for a result of 1 rows fetched ):
$ echo “select 1” | isql -v asterisk-connector asterisk some_secret_password
+—————————————+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+—————————————+
SQL>
+————+
| ?column? |
+————+
| 1 |
+————+
SQLRowCount returns 1
1 rows fetched
# The password you used when you created the ‘asterisk’@ user in the “Configuring MySQL” section.
Now that unixODBC is installed, configured, and verified to work, you need to recompile Asterisk so that the ODBC modules are created and installed. Change back to your Asterisk source directory and run the ./configure script so it knows you have installed unixODBC:
$ cd ~/src/asterisk-complete/asterisk/11
$ ./configure
$ make menuselect
$ make install
Once Linux is able to use ODBC, Asterisk will also be able to use ODBC. The point is, get your ODBC going under Linux with no errors before you dive into the Asterisk portion of the configuration process.
Compiling the ODBC Modules for Asterisk
Now that ODBC is installed and tested, you will need to compile the relevant modules for Asterisk.
The easiest way to do this is to simply go back to your installation directory (“Asterisk”), re-run sudo ./configure, followed by sudo make install, and automake will recognize that the dependencies for ODBC are now met and will automatically compile and install the relevant modules for Asterisk. If you want, you can run make menuselect, and verify that the Dialplan Function func_odbc now shows as [*] func_odbc , and the Resource Module res_odbc also is listed as [*] res_odbc .
Configuring res_odbc to Allow Asterisk to Connect Through ODBC
Asterisk ODBC connections are configured in the res_odbc.conf file located in /etc/ asterisk. The res_odbc.conf file sets the parameters that various Asterisk modules will use to connect to the database.
Modify the res_odbc.conf file so it looks like the following:
[asterisk]
enabled => yes
dsn => asterisk-connector
username => asterisk
password => welcome
pooling => no
limit => 1
pre-connect => yes
The dsn option points at the database connection you 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
ODBC DSN Settings
—————–
Name: asterisk
DSN: asterisk-connector
Last connection attempt: 1969-12-31 19:00:00
Pooled: No
Connected: Yes
