Chapter 16. Relational Database Integration

Table of Contents

Installing and Configuring PostgreSQL and MySQL
Installing PostgreSQL for CentOS
Installing PostgreSQL for Ubuntu
Installing MySQL for CentOS
Installing MySQL for Ubuntu
Configuring PostgreSQL
Configuring MySQL
Installing and Configuring ODBC
Configuring ODBC for PostgreSQL
Configuring ODBC for MySQL
Configuring ODBC for Microsoft SQL
Validating the ODBC Connector
Configuring res_odbc to Allow Asterisk to Connect Through ODBC
Managing Databases
Troubleshooting Database Issues
A Gentle Introduction to func_odbc
Getting Funky with func_odbc: Hot-Desking
Using Realtime
Static Realtime
Dynamic Realtime
Storing Call Detail Records (CDRs)
ODBC Voicemail
Creating the Large Object Type for PostgreSQL
ODBC Voicemail Storage Table Layout
Configuring voicemail.conf for ODBC Storage
Testing ODBC Voicemail
Verifying binary data stored in PostgreSQL
Verifying binary data stored in MySQL
Conclusion

Few things are harder to put up with than the annoyance of a good example.

Mark Twain

In this chapter 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 of this book.

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, you shouldn’t have any problems with this chapter.

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. Our favorite dialplan function, which we will cover later in this chapter, is func_odbc.

While not all Asterisk deployments will require relational databases, understanding how to harness them opens a treasure chest full of new ways to design your telecom solution.