1. ====== Asterisk ODBC using SQLite3 ======
2.  
3. Asterisk uses the ODBC abstraction layer for SQL database support.  AstLinux supports a SQLite3 driver for ODBC access to local file databases.
4.  
5. !!Note: AstLinux 1.1.1 or later is required!!
6.  
7. ===== Asterisk ODBC Configuration =====
8.  
9. The system is automatically configured for ODBC, establishing an SQLite3 driver and two DSN's:
10.   * DSN=**asterisk**, points to ''/mnt/kd/asterisk-odbc.sqlite3'' used by func_odbc in Asterisk.
11.   * DSN=**asterisk-cdr**, points to ''/mnt/kd/cdr-sqlite3/cdr-odbc.sqlite3'' used by cdr_adaptive_odbc and cdr_odbc.
12.  
13. ODBC support is enabled in Asterisk by editing the ''/etc/asterisk/res_odbc.conf'' configuration file.
14.  
15. Default ''res_odbc.conf'' snippet (with the many comments removed for simplicity):
16.   [asterisk]
17.   enabled => no
18.   dsn => asterisk
19.   pre-connect => yes
20.  
21. Enable by setting ''enabled'' to ''yes'':
22.   [asterisk]
23.   enabled => yes
24.   dsn => asterisk
25.   pre-connect => yes
26.  
27. If you want to use SQL CDR logging, also add the following section:
28.   [asterisk-cdr]
29.   enabled => yes
30.   dsn => asterisk-cdr
31.   pre-connect => yes
32.  
33. ===== Dialplan Functions with func_odbc =====
34.  
35. As the first line states, "The func_odbc dialplan function is arguably the coolest and most powerful dialplan function in Asterisk"
36. [[http://www.asteriskdocs.org/en/3rd_Edition/asterisk-book-html-chunk/getting_funky.html|Example: func_odbc]]
37.  
38. 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'' .
39.  
40.   CREATE TABLE IF NOT EXISTS 'sip_users' (
41.     'id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
42.     'sipuser' TEXT NOT NULL,
43.     'lastname' TEXT DEFAULT '',
44.     'firstname' TEXT DEFAULT '',
45.     'out_cxid' INTEGER DEFAULT 7,
46.     'vm' INTEGER DEFAULT 0,
47.     'vmbox' TEXT DEFAULT '',
48.     'email' TEXT DEFAULT '',
49.     'ext_intern' TEXT DEFAULT '',
50.     'ext_extern' TEXT DEFAULT '',
51.     'fax_ext' TEXT DEFAULT '',
52.     'fax_email' TEXT DEFAULT '',
53.     'xmpp_jid' TEXT DEFAULT ''
54.   );
55.  
56.   CREATE TABLE IF NOT EXISTS 'out_context' (
57.     'id' INTEGER PRIMARY KEY NOT NULL,
58.     'context' TEXT NOT NULL,
59.     'description' TEXT DEFAULT ''
60.   );
61.  
62.   CREATE TABLE IF NOT EXISTS 'ip_phones' (
63.     'id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
64.     'type' TEXT DEFAULT '',
65.     'firmware' TEXT DEFAULT '',
66.     'hostname' TEXT DEFAULT '',
67.     'ipv4' TEXT DEFAULT '',
68.     'ipv6' TEXT DEFAULT '',
69.     'mac' TEXT DEFAULT '',
70.     'sipuser_id' INTEGER
71.   );
72.  
73. !!Note ->!! The out_context table is automatically populated with id's from 0-7.
74.  
75. 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.
76.  
77. {{:userdoc:odbc-example-table.jpg?nolink|}}
78.  
79. Example ''/etc/asterisk/func_odbc.conf'' snippet:
80.   [SIPUSERS]
81.   dsn=asterisk
82.   readsql=SELECT ${ARG1} FROM sip_users WHERE ${ARG2}='${SQL_ESC(${ARG3})}'
83.  
84. Example ''/etc/asterisk/extensions.conf'' snippet:
85.   ; ODBC Test
86.   exten => 999,1,NoOp(ODBC Query Test)
87.   ;
88.   ; ODBC_SIPUSERS: SELECT ARG1 FROM sip_users WHERE ARG2=ARG3
89.   ;   in the case: SELECT sipuser FROM sip_users WHERE ext_intern=101
90.   ;
91.   same => n,Set(val=${ODBC_SIPUSERS(sipuser,ext_intern,101)})
92.   same => n,NoOp(ODBC-ReturnValue: ${val})
93.   same => n,Hangup()
94.  
95. It is very simple and very powerful!
96.  
97. !!Tip ->!! Teach yourself some basic SQL commands.  [[http://www.w3schools.com/sql/|SQL Tutorial]]
98.  
99. ===== Mass Deployment SQL Table =====
100.  
101. 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.
102.  
103. !!Note: AstLinux 1.1.7 or later is required!!
104.  
105. Using the SQL-Data tab in the web interface, view an example 'phoneprov' table generated by the PhoneProv tab:
106.  
107. {{:userdoc:odbc-phoneprov-table.jpg?nolink|}}
108.  
109. The SQL schema is of the form:
110.  
111.   CREATE TABLE 'phoneprov' (
112.     'id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
113.     'ext' TEXT DEFAULT '',
114.     'cid_name' TEXT DEFAULT '',
115.     'account' TEXT DEFAULT '',
116.     'line' INTEGER DEFAULT 1,
117.     'mac_addr' TEXT DEFAULT '',
118.     'template' TEXT DEFAULT '',
119.     'model' TEXT DEFAULT '',
120.     'vendor' TEXT DEFAULT ''
121.   );
122.  
123. 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:
124.  
125.   [SIPACCOUNT]
126.   dsn=asterisk
127.   readsql=SELECT ${ARG1} FROM phoneprov WHERE ${ARG2}='${SQL_ESC(${ARG3})}'
128.  
129. In the dialplan you could then use something like this:
130.  
131.   same => n,Set(val=${ODBC_SIPACCOUNT(account,ext,100)})
132.  
133. to get the SIP account (peer-name) from a given extension (100) via SQL.
134.  
135. Detailed Mass Deployment documentation can be found here:
136. **[[userdoc:tt_ip_phone_provisioning|IP Phone Mass Deployment]]**
137.  
138. ===== CDR Call Logging with SQL =====
139.  
140. SQL CDR logging support is enabled in Asterisk by editing the ''/etc/asterisk/cdr_adaptive_odbc.conf'' configuration file.
141.  
142. !!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.
143.  
144. Default ''cdr_adaptive_odbc.conf'' snippet:
145.   ;[first]
146.   ;connection=mysql1
147.   ;table=cdr
148.  
149. Enable by replacing with:
150.   [first]
151.   connection=asterisk-cdr
152.   table=cdr
153.   alias start => calldate
154.  
155. If you prefer some other CDR variable other than ''start'' for the calldate column, the ''alias'' line allows you to do that.
156.  
157. !!Tip ->!!  Be sure to read the comments in the ''cdr_adaptive_odbc.conf'' file, very flexible CDR logging options are available.
158.  
159. !!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:
160.  
161.   CREATE TABLE 'cdr' (
162.     'id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
163.     'calldate' TEXT,
164.     'clid' TEXT,
165.     'src' TEXT,
166.     'dst' TEXT,
167.     'dcontext' TEXT,
168.     'channel' TEXT,
169.     'dstchannel' TEXT,
170.     'lastapp' TEXT,
171.     'lastdata' TEXT,
172.     'duration' REAL,
173.     'billsec' REAL,
174.     'disposition' TEXT,
175.     'amaflags' INTEGER,
176.     'accountcode' TEXT,
177.     'uniqueid' TEXT,
178.     'userfield' TEXT
179.   );
180.