Asterisk Realtime Architecture

Like any machine tinkered with heavily over time, Asterisk has a lot of exposed configuration points in a lot of places, and it can be hard to know how or why what you want to do isn't working because you neglected to set some variable that became necessary since the last time the module was documented.

I've fallen into this situation for the past couple days, and having finally found the right thing to tweak, let me write down how to use ODBC to access Asterisk's configuration files, including the dial plan, from a real-time database.

First thing: go read the official book by O'Reilly, and read the chapter on ARA.

Once you have downloaded Asterisk, as we did here, run ./configure, then make menuselect and ensure res_odbc, res_config_odbc, and all *_realtime modules are set
to be built. If they have a 'XXX' beside their name, it means that you need to install some dependencies. Search your package installer for "ODBC" and a database that supplies an ODBC driver, such as PostgreSQL or MySQL. You may also need the "-devel" version of those packages.

To make the tutorial complete, we'll pick a DB for the examples, so we choose PostgreSQL.

1. Setting Up the Database

As the DB's (not the system's) root user create a new role and database for Asterisk to use:
createuser -SRdP asterisk-role
createdb --owner=asterisk-role asterisk-db

Then restart the database.

2. Setting Up ODBC

Ensure that /etc/odbcinst.ini exists and contains something like the following:
[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/lib/libodbcpsql.so
Setup = /usr/lib/libodbcpsqlS.so
FileUsage = 1

Ensure that /etc/odbcinst.ini exists and contains something like the following:
[asterisk-postgres-connector]
Description = PostgreSQL connection to 'asterisk' database
Driver = PostgreSQL
Database = asterisk-db
Servername = localhost
UserName = asterisk-role
Password = 123456

Then verify that the connection works using the following command:
echo "select 1" | isql -v asterisk-postgres-connector

3. Setting Up a Basic Asterisk Configuration

extconfig.conf
[settings]
sipusers => odbc,asterisk-postgres-connector,sip_table
sippeers => odbc,asterisk-postgres-connector,sip_table
extensions => odbc,asterisk-postgres-connector,extensions_table

extensions.conf
[general]
[default-sip]
switch => Realtime/@

modules.conf
[modules]
autoload=yes
preload => res_odbc.so
preload => res_config_odbc.so

res_odbc.conf
[ENV]

[asterisk-postgres-connector]
enabled => yes
dsn => asterisk-postgres-connector
username => asterisk-role
password => 123456
pre-connect => yes

sip.conf
[general]

4. Setting up Dialplan and Sip User/Peer Tables

This is perhaps the most mysterious point. The ARA requires a number of columns to be set within the table, and if they aren't it will silently fail. The SIP table in particular. Nearest I can tell, the only source of documentation is here.

Run the following SQL code as the asterisk-role:

CREATE TABLE extensions_table
(
"id" serial,
"context" varchar(20) NOT NULL default '',
"exten" varchar(36) NOT NULL default '',
"priority" smallint NOT NULL default '0',
"app" varchar(20) NOT NULL default '',
"appdata" varchar(128) NOT NULL default '',

PRIMARY KEY (id)
) WITHOUT OIDS;

CREATE TABLE sip_table
(
"id" serial,
"name" varchar(80) NOT NULL default '',
"host" varchar(31) NOT NULL default 'dynamic',
"nat" varchar(5) NOT NULL default 'route',
"type" varchar(6) check (type in ('user','peer','friend')) NOT NULL default 'friend',
"accountcode" varchar(20) default NULL,
"amaflags" varchar(13) default NULL,
"callgroup" varchar(10) default NULL,
"callerid" varchar(80) default NULL,
"call-limit" int NOT NULL default '0',
"cancallforward" char(3) default 'no',
"canreinvite" char(3) default 'no',
"context" varchar(80) default 'default-sip',
"defaultip" varchar(15) default NULL,
"dtmfmode" varchar(7) default NULL,
"fromuser" varchar(80) default NULL,
"fromdomain" varchar(80) default NULL,
"insecure" varchar(4) default NULL,
"language" char(2) default NULL,
"mailbox" varchar(50) default NULL,
"md5secret" varchar(80) default NULL,
"deny" varchar(95) default NULL,
"permit" varchar(95) default NULL,
"mask" varchar(95) default NULL,
"musiconhold" varchar(100) default NULL,
"pickupgroup" varchar(10) default NULL,
"qualify" char(3) default NULL,
"regexten" varchar(80) default NULL,
"restrictcid" char(3) default NULL,
"rtptimeout" char(3) default NULL,
"rtpholdtimeout" char(3) default NULL,
"secret" varchar(80) default NULL,
"setvar" varchar(100) default NULL,
"disallow" varchar(100) default '',
"allow" varchar(100) default '',
"fullcontact" varchar(80) NOT NULL default '',
"ipaddr" varchar(15) NOT NULL default '',
"port" smallint NOT NULL default '0',
"regserver" varchar(100) default NULL,
"regseconds" int NOT NULL default '0',
"username" varchar(80) NOT NULL default '',
"delay" int NOT NULL default '0',
"sortorder" int NOT NULL default '1',
PRIMARY KEY (id),
UNIQUE (name)
) WITHOUT OIDS;

GRANT ALL ON sip_table TO asterisk;
GRANT ALL ON extensions_table TO asterisk;

INSERT INTO extensions_table (context, exten, priority, app, appdata) VALUES ('default-sip', 'sip-extn', 1, 'Answer', '');
INSERT INTO extensions_table (context, exten, priority, app, appdata) VALUES ('default-sip', 'sip-extn', 2, 'Wait', '2');
INSERT INTO extensions_table (context, exten, priority, app, appdata) VALUES ('default-sip', 'sip-extn', 3, 'Playback', 'hello-world');
INSERT INTO extensions_table (context, exten, priority, app, appdata) VALUES ('default-sip', 'sip-extn', 4, 'Wait', '2');
INSERT INTO extensions_table (context, exten, priority, app, appdata) VALUES ('default-sip', 'sip-extn', 5, 'Hangup', '');

INSERT INTO sip_table (name, context, host, type) VALUES ('sip-user', 'default-sip', 'dynamic', 'friend');

5. Test the setup

Run Asterisk on the console, and verify the ODBC connection is working by issuing the command odbc show.

Don't bother trying to run dialplan show or similar, as this will not display what we inserted in the previous step. Instead we need to make a call to test: in your soft-phone, dial sip:sip-extn@asterisk.example.com, and you should hear a voice say "hello world".