userdoc:tt_asterisk-odbc [AstLinux Documentation]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== 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"
[[http://www.asteriskdocs.org/en/3rd_Edition/asterisk-book-html-chunk/getting_funky.html|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.

{{:userdoc:odbc-example-table.jpg?nolink|}}

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.  [[http://www.w3schools.com/sql/|SQL Tutorial]]

===== Mass Deployment SQL Table =====

When using the **[[userdoc:tt_ip_phoneprov_howto|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:

{{:userdoc:odbc-phoneprov-table.jpg?nolink|}}

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:
**[[userdoc:tt_ip_phone_provisioning|IP Phone Mass Deployment]]**

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