The following command can be used to install the PostgreSQL server and its dependencies from the console:

$ sudo yum install -y postgresql-server
Install       3 Package(s)
Upgrade       0 Package(s)

Total download size: 6.9 M
Is this ok [y/N]: y

Then start the database, which will take a few seconds to initialize for the first time:

$ sudo service postgresql start

Now head to the section called “Configuring PostgreSQL” for instructions on how to perform the initial configuration.

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

Now head to the section called “Configuring PostgreSQL” for instructions on how to perform the initial configuration.

To install MySQL on CentOS, 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       5 Package(s)
Upgrade       0 Package(s)

Total download size: 27 M
Is this ok [y/N]: y

Then start the MySQL database by running:

$ sudo service mysqld start

Now head to the section called “Configuring MySQL” to perform the initial configuration.

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.

Now head to the section called “Configuring MySQL” to perform the initial configuration.

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:

$ sudo su - postgres

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 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 CentOS, 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

host    all     asterisk          ::1/128                               md5

[28000][unixODBC]FATAL:  no pg_hba.conf entry for host "::1", user "asterisk", 
database "asterisk", SSL off
[ISQL]ERROR: Could not SQLConnect

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 CentOS:

$ sudo service postgresql restart

On Ubuntu:

$ sudo /etc/init.d/postgresql-8.4 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 8.1.21, 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=>

You’re now ready to move on to the section called “Installing and Configuring ODBC”.

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^[140] 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’re now ready to move on to the section called “Installing and Configuring ODBC”.

Configuration for the PostgreSQL ODBC driver is done in the /etc/odbcinst.ini file.

On CentOS 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/libodbcpsql.so
Setup           = /usr/lib/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^[141]:

[asterisk-connector]
Description         = PostgreSQL connection to 'asterisk' database
Driver              = PostgreSQL
Database            = asterisk
Servername          = localhost
UserName            = asterisk
Password            = welcome
Port                = 5432
Protocol            = 8.1
ReadOnly            = No
RowVersioning       = No
ShowSystemTables    = No
ShowOidColumn       = No
FakeOidIndex        = No
ConnSettings        =

Configuration for the MySQL ODBC driver is done in the /etc/odbcinst.ini file.

On CentOS the default file already contains some data, including that 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/libmyodbc3.so
Setup = /usr/lib/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, 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 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
UserName              = asterisk
Password              = welcome
Port                  = 3306
Socket                = /var/lib/mysql/mysql.sock

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 CentOS:

$ sudo yum install freetds

On Ubuntu:

$ sudo apt-get install freetds

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/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
UserName        = asterisk
Password        = welcome
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.

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
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> +------------+
| ?column?   |
+------------+
| 1          |
+------------+
SQLRowCount returns 1
1 rows fetched
$ exit

With unixODBC 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/1.8
$ ./configure
$ make menuselect
$ make install

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

When working with ODBC database connections and Asterisk, it is important to remember that the ODBC connection abstracts some of the information passed between Asterisk and the database. In cases where things are not working as expected, you may need to enable logging on your database platform to see what Asterisk is sending to the database (e.g., what SELECT, INSERT, or UPDATE statements are being triggered from Asterisk), what the database is seeing, and why the database may be rejecting the statements.

For example, one of the most common problems found with ODBC database integration is an incorrectly defined table, or a missing column that Asterisk expects to exist. While great strides have been made in the form of adaptive modules, not all parts of Asterisk are adaptive. In the case of ODBC voicemail storage, you may have missed a column such as flag, which is a new column not previously found in versions of Asterisk prior to 1.8.^[142] In order to debug why your data is not being written to the database as expected, you should enable statement logging on the database side, and then determine what statement is being executed and why the database is rejecting it.

Static realtime is useful when you want to load from a database the configuration that you would normally place in the configuration files in /etc/asterisk. The same rules that apply to flat files on your system still apply when using static realtime. For example, after making changes to the configuration you must either run the reload command from the Asterisk CLI, or reload the module associated with the configuration file (e.g., using module reload chan_sip.so).

When using static realtime, we tell Asterisk which files we want to load from the database using the following syntax in the extconfig.conf file:

; /etc/asterisk/extconfig.conf
filename.conf => driver,database[,table]

The static realtime module uses a specifically formatted table to read the configuration of static files in from the database. Table 16.2, “Table layout and description of ast_config” illustrates the columns as they should be defined in your database:

A simple file we can load from static realtime is the musiconhold.conf^[149] file. Let’s start by moving this file to a temporary location:

$ cd /etc/asterisk
$ mv musiconhold.conf musiconhold.conf.old

In order for the classes to be removed from memory, we need to restart Asterisk. Then we can verify that our classes are blank by running moh show classes:

*CLI> core restart now
*CLI> moh show classes
*CLI>

Let’s put the [default] class back into Asterisk, but now we’ll load it from the database. Connect to your database and execute the following INSERT statements:

> INSERT INTO ast_config (filename,category,var_name,var_val)
  VALUES ('musiconhold.conf','default','mode','files');

> INSERT INTO ast_config (filename,category,var_name,var_val)
  VALUES ('musiconhold.conf','default','directory','/var/lib/asterisk/moh');

You can verify that your values have made it into the database by running a SELECT statement:

asterisk=# SELECT filename,category,var_name,var_val FROM ast_config;

 filename         | category       | var_name     | var_val
------------------+----------------+--------------+------------------------
 musiconhold.conf | default        | mode         | files
 musiconhold.conf | default        | directory    | /var/lib/asterisk/moh
(2 rows)

There’s one last thing to modify in the extconfig.conf file in the /etc/asterisk directory to tell Asterisk to get the data for musiconhold.conf from the database using the ODBC connection. The first column states that we’re using the ODBC drivers to connect (res_odbc.conf) and that the connection name is asterisk (as defined with [asterisk] in res_odbc.conf). Add the following line to the end of the extconfig.conf file, and then save it:

musiconhold.conf => odbc,asterisk,ast_config

Then connect to the Asterisk console and perform a reload:

*CLI> module reload res_musiconhold.so

You can now verify that your music on hold classes are loading from the database by running moh show classes:

*CLI> moh show classes
Class: general
        Mode: files
        Directory: /var/lib/asterisk/moh

And there you go: musiconhold.conf loaded from the database. If you have issues with the reload of the module loading the data into memory, try restarting Asterisk. You can perform the same steps in order to load other flat files from the database, as needed.

The dynamic realtime system is used to load objects that may change often, such as SIP/IAX2 users and peers, queues and their members, and voicemail messages. Likewise, when new records are likely to be added on a regular basis, we can utilize the power of the database to let us load this information on an as-needed basis.

All of realtime is configured in the /etc/asterisk/extconfig.conf file, but dynamic realtime has well-defined configuration names. Defining something like SIP peers is done with the following format:

; extconfig.conf
sippeers => driver,database[,table]

The table name is optional. If it is omitted, Asterisk will use the predefined name (i.e., sippeers) to identify the table in which to look up the data.

In our example, we’ll be using the ast_sippeers table to store our SIP peer information. So, to configure Asterisk to load all SIP peers from our database using realtime, we would define something like this:

; extconfig.conf
sippeers => odbc,asterisk,ast_sipfriends

To also load our SIP users from the database, we would define the sipusers object like so:

sipusers => odbc,asterisk,ast_sipfriends

You may have noticed we used the same table for both the sippeers and sipusers. This is because there will be a type field (just as if we were defining the type in the sip.conf file) that will let us define a type of user, peer, or friend. If you unload chan_sip.so and then load it back into memory (i.e., using module unload chan_sip.so followed by module load chan_sip.so) after configuring extconfig.conf, you will be greeted with some warnings telling you which columns you’re missing for the realtime table. Since we’ve enabled sippeers and sipusers in extconfig.conf, we will get the following on the console (which has been trimmed due to space requirements):

WARNING: Realtime table ast_sipfriends@asterisk requires column 
'name', but that column does not exist!

WARNING: Realtime table ast_sipfriends@asterisk requires column 
'ipaddr', but that column does not exist!

WARNING: Realtime table ast_sipfriends@asterisk requires column 
'port', but that column does not exist!

WARNING: Realtime table ast_sipfriends@asterisk requires column 
'regseconds', but that column does not exist!

WARNING: Realtime table ast_sipfriends@asterisk requires column 
'defaultuser', but that column does not exist!

WARNING: Realtime table ast_sipfriends@asterisk requires column 
'fullcontact', but that column does not exist!

WARNING: Realtime table ast_sipfriends@asterisk requires column 
'regserver', but that column does not exist!

WARNING: Realtime table ast_sipfriends@asterisk requires column 
'useragent', but that column does not exist!

WARNING: Realtime table ast_sipfriends@asterisk requires column 
'lastms', but that column does not exist!

As you can see, we are missing several columns from the table ast_sipfriends, which we’ve defined as connecting to the asterisk object as defined in res_odbc.conf. The next step is to create our ast_sipfriends table with all the columns listed by the warning messages, in addition to the following: the type column, which is required to define users, peers, and friends; the secret column, which is used for setting a password; and the host column, which allows us to define whether the peer is dynamically registering to us or has a static IP address. Table 16.3, “Minimal sippeers/sipusers realtime table” lists all of the columns that should appear in our table, and their types.

For each peer you want to register, you need to insert data in the columns type, name, secret, context, host, and defaultuser. The rest of the columns will be populated automatically when the peer registers.

The port, regseconds, and ipaddr fields are required to let Asterisk store the registration information for the peer so it can determine where to send the calls. (Note that if the peer is static, you will have to populate the ipaddr field yourself.) The port field is optional and defaults to the standard port defined in the [general] section, and the regseconds will remain blank. Table 16.4, “Example information used to populate the ast_sipfriends table” lists some sample values that we’ll use to populate our ast_sipfriends table.

Prior to registering your peer, though, you need to enable realtime caching in sip.conf. Otherwise, the peer will not be loaded into memory, and the registration will not be remembered. If your peers only place calls and don’t need to register to your system, you don’t need to enable realtime caching because the peers will be checked against the database each time they place a call. However, if you load your peers into memory, the database will only need to be contacted on initial registration, and after the registration expires.

Additional options in sip.conf exist for realtime peers. These are defined in the [general] section and described in Table 16.5, “Realtime options in sip.conf”.

After enabling rtcachefriends=yes in sip.conf and reloading chan_sip.so (using module reload chan_sip.so), you can register your peer to Asterisk using realtime, and the peer should then be populated into memory. You will be able to verify this by executing the sip show peers command on the Asterisk console:

Name/username              Host         Dyn Port Status      Realtime
0000FFFF0008/0000FFFF0008  172.16.0.160  D  5060 Unmonitored Cached RT

If you were to look at the table in the database directly, you would see something like this:

+--------+--------------+---------+-----------+---------+--------------+
| type   | name         | secret  | context   | host    | ipaddr       |
+--------+--------------+---------+-----------+---------+--------------+
| friend | 0000FFFF0008 | welcome | LocalSets | dynamic | 172.16.0.160 | 
+--------+--------------+---------+-----------+---------+--------------+
+------+------------+--------------+-------------------------------------+
| port | regseconds | defaultuser  | fullcontact                         |
+------+------------+--------------+-------------------------------------+
| 5060 | 1283928895 | 0000FFFF0008 | sip:0000FFFF0008@172.16.0.160:52722 |
+------+------------+--------------+-------------------------------------+
+-----------+-----------------+--------+
| regserver | useragent       | lastms |
+-----------+-----------------+--------+
| NULL      | Zoiper rev.6739 |      0 | 
+-----------+-----------------+--------+

There are many more options for that we can define for SIP friends, such as the caller ID; adding that information is as simple as adding a callerid column to the table. See the sip.conf.sample file for more options that can be defined for SIP friends.

While MySQL has a BLOB (Binary Large OBject) type, we have to tell PostgreSQL how to handle large objects. This includes creating a trigger to clean up the data when we delete from the database a record that references a large object.

Connect to the database as the asterisk user from the console:

$ psql -h localhost -U asterisk asterisk
Password:

At the PostgreSQL console, run the following script to create the large object type:

CREATE FUNCTION loin (cstring) RETURNS lo AS 'oidin' LANGUAGE internal
IMMUTABLE STRICT;

CREATE FUNCTION loout (lo) RETURNS cstring AS 'oidout' LANGUAGE internal
IMMUTABLE STRICT;

CREATE FUNCTION lorecv (internal) RETURNS lo AS 'oidrecv' LANGUAGE internal
IMMUTABLE STRICT;

CREATE FUNCTION losend (lo) RETURNS bytea AS 'oidrecv' LANGUAGE internal
IMMUTABLE STRICT;

CREATE TYPE lo ( INPUT = loin, OUTPUT = loout, RECEIVE = lorecv, SEND = losend,
INTERNALLENGTH = 4, PASSEDBYVALUE );

CREATE CAST (lo AS oid) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (oid AS lo) WITHOUT FUNCTION AS IMPLICIT;

We’ll be making use of the PostgreSQL procedural language called pgSQL/PL to create a function. This function will be called from a trigger that gets executed whenever we modify or delete a record in the table used to store voicemail messages. This is so the data is cleaned up and not left as an orphan in the database:

CREATE FUNCTION vm_lo_cleanup() RETURNS "trigger"
    AS $$
    declare
      msgcount INTEGER;
    begin
      -- raise notice 'Starting lo_cleanup function for large object with oid
         %',old.recording;
      -- If it is an update action but the BLOB (lo) field was not changed,
         don't do anything
      if (TG_OP = 'UPDATE') then
        if ((old.recording = new.recording) or (old.recording is NULL)) then
          raise notice 'Not cleaning up the large object table,
          as recording has not changed';
          return new;
        end if;
      end if;
      if (old.recording IS NOT NULL) then
        SELECT INTO msgcount COUNT(*) AS COUNT FROM voicemessages WHERE recording
        = old.recording;
        if (msgcount > 0) then
          raise notice 'Not deleting record from the large object table, as object
          is still referenced';
          return new;
        else
          perform lo_unlink(old.recording);
          if found then
            raise notice 'Cleaning up the large object table';
            return new;
          else
            raise exception 'Failed to clean up the large object table';
            return old;
          end if;
        end if;
      else
        raise notice 'No need to clean up the large object table, 
        no recording on old row';

        return new;
      end if;
    end$$
    LANGUAGE plpgsql;

We’re going to create a table called voicemessages where the voicemail information will be stored:

CREATE TABLE voicemessages
(
  uniqueid serial PRIMARY KEY,
  msgnum int4,
  dir varchar(80),
  context varchar(80),
  macrocontext varchar(80),
  callerid varchar(40),
  origtime varchar(40),
  duration varchar(20),
  mailboxuser varchar(80),
  mailboxcontext varchar(80),
  recording lo,
  label varchar(30),
  "read" bool DEFAULT false,
  flag varchar(10)
);

And now we need to associate a trigger with our newly created table in order to perform cleanup whenever we change or delete a record in the voicemessages table:

CREATE TRIGGER vm_cleanup AFTER DELETE OR UPDATE ON voicemessages FOR EACH ROW
EXECUTE PROCEDURE vm_lo_cleanup();

We’ll be utilizing the voicemessages table for storing our voicemail information in an ODBC-connected database. Table 16.6, “ODBC voicemail storage table layout” describes the table configuration for ODBC voicemail storage. If you’re using a PostgreSQL database, the table definition and large object support were configured in the preceding section.

There isn’t much to add to the voicemail.conf file to enable the ODBC voicemail storage. In fact, it’s only three lines! Normally, you probably have multiple format types defined in the [general] section of voicemail.conf, but we need to set this to a single format because we can only save one file (format) to the database. The wav49 format is a compressed WAV file format that should be playable on both Linux and Microsoft Windows desktops.

The odbcstorage option points at the name you defined in the res_odbc.conf file (if you’ve been following along in this chapter, then we called it asterisk). The odbctable option refers to the table where voicemail information should be stored. In the examples in this chapter we use the table named voicemessages:

[general]
format=wav49
odbcstorage=asterisk
odbctable=voicemessages

You may want to create a separate voicemail context, or you can utilize the default voicemail context. Alternatively, you can skip creating a new user and use an existing user, such as 0000FFFF0001. We’ll define the mailbox in the default voicemail context like so:

[default]
1000 => 1000,J.P. Wiser

Now connect to your Asterisk console and unload then reload the app_voicemail.so module:

*CLI> module unload app_voicemail.so
  == Unregistered application 'VoiceMail'
  == Unregistered application 'VoiceMailMain'
  == Unregistered application 'MailboxExists'
  == Unregistered application 'VMAuthenticate'

*CLI> module load app_voicemail.so
 Loaded /usr/lib/asterisk/modules/app_voicemail.so => 
(Comedian Mail (Voicemail System))
  == Registered application 'VoiceMail'
  == Registered application 'VoiceMailMain'
  == Registered application 'MailboxExists'
  == Registered application 'VMAuthenticate'
  == Parsing '/etc/asterisk/voicemail.conf': Found

Then verify that your new mailbox loaded successfully:

*CLI> voicemail show users for default
Context    Mbox  User                      Zone       NewMsg
default    1000  J.P. Wiser                                0

Let’s create some simple dialplan logic to leave and retrieve some voicemail from our test voicemail box. You can use the simple dialplan logic that follows (or, of course, any voicemail delivery and retrieval functionality you defined earlier in this book):

[odbc_vm_test]
exten => 100,1,VoiceMail(1000@default)      ; leave a voicemail
exten => 200,1,VoiceMailMain(1000@default)  ; retrieve a voicemail

Once you’ve updated your extensions.conf file, be sure to reload the dialplan:

*CLI> dialplan reload

You can either include the odbc_vm_test context into a context accessible by an existing user, or create a separate user to test with. If you wish to do the latter, you could define a new SIP user in sip.conf like so (this will work assuming the phone is on the local LAN):

[odbc_test_user]
type=friend
secret=supersecret
context=odbc_vm_test
host=dynamic
qualify=yes
disallow=all
allow=ulaw
allow=gsm

Don’t forget to reload the SIP module:

*CLI> module reload chan_sip.so

And verify that the SIP user exists:

*CLI> sip show users like odbc_test_user
Username         Secret           Accountcode      Def.Context      ACL  NAT
odbc_test_user   supersecret                       odbc_vm_test     No   RFC3581

Then configure your phone or client with the username odbc_test_user and password <supersecret>, and place a call to extension 100 to leave a voicemail. If successful, you should see something like:

    -- Executing VoiceMail("SIP/odbc_test_user-10228cac", "1000@default") in new 
       stack
    -- Playing 'vm-intro' (language 'en')
    -- Playing 'beep' (language 'en')
    -- Recording the message
    -- x=0, open writing:  /var/spool/asterisk/voicemail/default/1000/tmp/dlZunm
       format: wav49, 0x101f6534
    -- User ended message by pressing #
    -- Playing 'auth-thankyou' (language 'en')
  == Parsing '/var/spool/asterisk/voicemail/default/1000/INBOX/msg0000.txt': Found

Now that you’ve confirmed everything was stored in the database correctly, you can try listening to it via the VoiceMailMain() application by dialing extension 200:

*CLI>
    -- Executing VoiceMailMain("SIP/odbc_test_user-10228cac", 
       "1000@default") in new stack
    -- Playing 'vm-password' (language 'en')
    -- Playing 'vm-youhave' (language 'en')
    -- Playing 'digits/1' (language 'en')
    -- Playing 'vm-INBOX' (language 'en')
    -- Playing 'vm-message' (language 'en')
    -- Playing 'vm-onefor' (language 'en')
    -- Playing 'vm-INBOX' (language 'en')
    -- Playing 'vm-messages' (language 'en')
    -- Playing 'vm-opts' (language 'en')
    -- Playing 'vm-first' (language 'en')
    -- Playing 'vm-message' (language 'en')
  == Parsing '/var/spool/asterisk/voicemail/default/1000/INBOX/msg0000.txt': Found

$ psql -h localhost -U asterisk asterisk
Password:

localhost=# SELECT uniqueid,dir,callerid,mailboxcontext,recording FROM voicemessages;
uniqueid | dir                                              | callerid     
---------+--------------------------------------------------+--------------
1        | /var/spool/asterisk/voicemail/default/1000/INBOX | +18005551212 


| mailboxcontext | recording |
+----------------+-----------+
| default        | 47395     |
(1 row)

localhost=# \lo_list
    Large objects
  ID   | Description
-------+-------------
 47395 |
(1 row)

localhost=# \lo_export 47395 /tmp/voicemail-47395.wav
lo_export

$ play /tmp/voicemail-47395.wav

Input Filename : /tmp/voicemail-47395.wav
Sample Size    : 8-bits
Sample Encoding: wav
Channels       : 1
Sample Rate    : 8000

Time: 00:06.22 [00:00.00] of 00:00.00 (  0.0%) Output Buffer: 298.36K

Done.

$ mysql -u asterisk -p asterisk
Enter password:

mysql> SELECT uniqueid, msgnum, callerid, mailboxuser, mailboxcontext, `read`
    -> FROM voicemessages;
+----------+--------+------------------------------+-------------
| uniqueid | msgnum | callerid                     | mailboxuser 
+----------+--------+------------------------------+-------------
|        1 |      0 | "Leif Madsen" <100>          | 100         
|        2 |      1 | "Leif Madsen" <100>          | 100         
|        3 |      2 | "Leif Madsen" <100>          | 100         
|        5 |      0 | "Julie Bryant" <12565551111> | 100         
+----------+--------+------------------------------+-------------
+----------------+------+
| mailboxcontext | read |
+----------------+------+
| shifteight.org |    0 |
| shifteight.org |    0 |
| shifteight.org |    0 |
| default        |    0 |
+----------------+------+

mysql> SELECT recording FROM voicemessages WHERE uniqueid = '5'
    -> DUMPFILE '/tmp/voicemail_recording.wav';

$ play /tmp/voicemail_recording.wav 

voicemail_recording.wav:

 File Size: 7.28k     Bit Rate: 13.1k
  Encoding: GSM           
  Channels: 1 @ 16-bit   
Samplerate: 8000Hz       
Replaygain: off         
  Duration: 00:00:04.44  

In:100%  00:00:04.44 [00:00:00.00] Out:35.5k [      |      ] Hd:4.4 Clip:0    
Done.