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 'sip_users' (
  'id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  'sipuser' TEXT NOT NULL,
  'lastname' TEXT,
  'firstname' TEXT,
  'out_cxid' INTEGER DEFAULT 7,
  'vm' INTEGER DEFAULT 0,
  'vmbox' TEXT,
  'email' TEXT,
  'ext_intern' TEXT,
  'ext_extern' TEXT,
  'fax_ext' TEXT,
  'fax_email' TEXT,
  'xmpp_jid' TEXT
);

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

CREATE TABLE 'ip_phones' (
  'id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  'type' TEXT,
  'firmware' TEXT,
  'hostname' TEXT,
  'ipv4' TEXT,
  'ipv6' TEXT,
  'mac' TEXT,
  '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.

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()

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
);