Asterisk ODBC using SQLite3

Asterisk uses the ODBC abstraction layer for SQL database support. AstLinux supports a SQLite3 driver for ODBC access to local file databases.

Note: AstLinux 1.1.1 or later is required

The system is automatically configured for ODBC, establishing an SQLite3 driver and two DSN's:

• DSN=asterisk, points to /mnt/kd/asterisk-odbc.sqlite3 used by func_odbc in Asterisk.
• DSN=asterisk-cdr, points to /mnt/kd/cdr-sqlite3/cdr-odbc.sqlite3 used by cdr_adaptive_odbc and cdr_odbc.

ODBC support is enabled in Asterisk by editing the /etc/asterisk/res_odbc.conf configuration file.

Default res_odbc.conf snippet (with the many comments removed for simplicity):

[asterisk]
enabled => no
dsn => asterisk
pre-connect => yes

Enable by setting enabled to yes:

[asterisk]
enabled => yes
dsn => asterisk
pre-connect => yes

If you want to use SQL CDR logging, also add the following section:

[asterisk-cdr]
enabled => yes
dsn => asterisk-cdr
pre-connect => yes

As the first line states, “The func_odbc dialplan function is arguably the coolest and most powerful dialplan function in Asterisk” Example: func_odbc

The first step in using func_odbc dialplan functions is to create a database with some SQL schema, for example when the AstLinux SQL-Data tab in the web interface is first accessed it automatically creates the following schema in file /mnt/kd/asterisk-odbc.sqlite3 .

CREATE TABLE IF NOT EXISTS 'sip_users' (
  'id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  'sipuser' TEXT NOT NULL,
  'lastname' TEXT DEFAULT '',
  'firstname' TEXT DEFAULT '',
  'out_cxid' INTEGER DEFAULT 7,
  'vm' INTEGER DEFAULT 0,
  'vmbox' TEXT DEFAULT '',
  'email' TEXT DEFAULT '',
  'ext_intern' TEXT DEFAULT '',
  'ext_extern' TEXT DEFAULT '',
  'fax_ext' TEXT DEFAULT '',
  'fax_email' TEXT DEFAULT '',
  'xmpp_jid' TEXT DEFAULT ''
);

CREATE TABLE IF NOT EXISTS 'out_context' (
  'id' INTEGER PRIMARY KEY NOT NULL,
  'context' TEXT NOT NULL,
  'description' TEXT DEFAULT ''
);

CREATE TABLE IF NOT EXISTS 'ip_phones' (
  'id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  'type' TEXT DEFAULT '',
  'firmware' TEXT DEFAULT '',
  'hostname' TEXT DEFAULT '',
  'ipv4' TEXT DEFAULT '',
  'ipv6' TEXT DEFAULT '',
  'mac' TEXT DEFAULT '',
  'sipuser_id' INTEGER
);

Note -> The out_context table is automatically populated with id's from 0-7.

A simple example is the best way to demonstrate how this works. Using the SQL-Data tab in the web interface, enter some data into the sip_users table.

Example /etc/asterisk/func_odbc.conf snippet:

[SIPUSERS]
dsn=asterisk
readsql=SELECT ${ARG1} FROM sip_users WHERE ${ARG2}='${SQL_ESC(${ARG3})}'

Example /etc/asterisk/extensions.conf snippet:

; ODBC Test
exten => 999,1,NoOp(ODBC Query Test)
;
; ODBC_SIPUSERS: SELECT ARG1 FROM sip_users WHERE ARG2=ARG3
;   in the case: SELECT sipuser FROM sip_users WHERE ext_intern=101
;
same => n,Set(val=${ODBC_SIPUSERS(sipuser,ext_intern,101)})
same => n,NoOp(ODBC-ReturnValue: ${val})
same => n,Hangup()

It is very simple and very powerful!

Tip -> Teach yourself some basic SQL commands. SQL Tutorial

When using the PhoneProv tab in the web interface, by default the template's (sql_enable=yes) automatically generate a 'phoneprov' table in the Asterisk ODBC SQLite3 database.

Note: AstLinux 1.1.7 or later is required

Using the SQL-Data tab in the web interface, view an example 'phoneprov' table generated by the PhoneProv tab:

The SQL schema is of the form:

CREATE TABLE 'phoneprov' (
  'id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  'ext' TEXT DEFAULT '',
  'cid_name' TEXT DEFAULT '',
  'account' TEXT DEFAULT '',
  'line' INTEGER DEFAULT 1,
  'mac_addr' TEXT DEFAULT '',
  'template' TEXT DEFAULT '',
  'model' TEXT DEFAULT '',
  'vendor' TEXT DEFAULT ''
);

Following the examples above, adding a context to /etc/asterisk/func_odbc.conf to define a new function, the dialplan can use the PhoneProv data. Here is an example which fits the new 'phoneprov' table:

[SIPACCOUNT]
dsn=asterisk
readsql=SELECT ${ARG1} FROM phoneprov WHERE ${ARG2}='${SQL_ESC(${ARG3})}'

In the dialplan you could then use something like this:

same => n,Set(val=${ODBC_SIPACCOUNT(account,ext,100)})

to get the SIP account (peer-name) from a given extension (100) via SQL.

Detailed Mass Deployment documentation can be found here: IP Phone Mass Deployment

SQL CDR logging support is enabled in Asterisk by editing the /etc/asterisk/cdr_adaptive_odbc.conf configuration file.

Note -> The /etc/asterisk/cdr_odbc.conf configuration file could alternatively be used, but cdr_adaptive_odbc is more flexible and robust as when cdr_adaptive_odbc loads it retrieves the cdr table schema and ONLY logs to that schema, so there is never a schema mismatch problem.

Default cdr_adaptive_odbc.conf snippet:

;[first]
;connection=mysql1
;table=cdr

Enable by replacing with:

[first]
connection=asterisk-cdr
table=cdr
alias start => calldate

If you prefer some other CDR variable other than start for the calldate column, the alias line allows you to do that.

Tip -> Be sure to read the comments in the cdr_adaptive_odbc.conf file, very flexible CDR logging options are available.

Note -> If the file /mnt/kd/cdr-sqlite3/cdr-odbc.sqlite3 does not exist when Asterisk starts, it will be automatically created with the following SQL schema:

CREATE TABLE 'cdr' (
  'id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  'calldate' TEXT,
  'clid' TEXT,
  'src' TEXT,
  'dst' TEXT,
  'dcontext' TEXT,
  'channel' TEXT,
  'dstchannel' TEXT,
  'lastapp' TEXT,
  'lastdata' TEXT,
  'duration' REAL,
  'billsec' REAL,
  'disposition' TEXT,
  'amaflags' INTEGER,
  'accountcode' TEXT,
  'uniqueid' TEXT,
  'userfield' TEXT
);