This is an old revision of the document!
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
Asterisk ODBC Configuration
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
Dialplan Functions with func_odbc
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 file 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, '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 IF NOT EXISTS 'out_context' ( 'id' INTEGER PRIMARY KEY NOT NULL, 'context' TEXT NOT NULL, 'description' TEXT ); CREATE TABLE IF NOT EXISTS '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.
CDR Call Logging with SQL
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 );