Differences

This shows you the differences between two versions of the page.

--- userdoc:tt_asterisk-odbc [2013/03/14 21:41] – abelbeck
+++ userdoc:tt_asterisk-odbc [2014/07/03 19:02] (current) – [Mass Deployment SQL Table] droemel
@@ Line 38: / Line 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'' .
-  CREATE TABLE 'sip_users' (
+  CREATE TABLE IF NOT EXISTS 'sip_users' (
     'id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
     'sipuser' TEXT NOT NULL,
-    'lastname' TEXT,
+    'lastname' TEXT DEFAULT '',
-    'firstname' TEXT,
+    'firstname' TEXT DEFAULT '',
     'out_cxid' INTEGER DEFAULT 7,
     'vm' INTEGER DEFAULT 0,
-    'vmbox' TEXT,
+    'vmbox' TEXT DEFAULT '',
-    'email' TEXT,
+    'email' TEXT DEFAULT '',
-    'ext_intern' TEXT,
+    'ext_intern' TEXT DEFAULT '',
-    'ext_extern' TEXT,
+    'ext_extern' TEXT DEFAULT '',
-    'fax_ext' TEXT,
+    'fax_ext' TEXT DEFAULT '',
-    'fax_email' TEXT,
+    'fax_email' TEXT DEFAULT '',
-    'xmpp_jid' TEXT
+    'xmpp_jid' TEXT DEFAULT ''
   );
-  CREATE TABLE 'out_context' (
+  CREATE TABLE IF NOT EXISTS 'out_context' (
     'id' INTEGER PRIMARY KEY NOT NULL,
     'context' TEXT NOT NULL,
-    'description' TEXT
+    'description' TEXT DEFAULT ''
   );
-  CREATE TABLE 'ip_phones' (
+  CREATE TABLE IF NOT EXISTS 'ip_phones' (
     'id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
-    'type' TEXT,
+    'type' TEXT DEFAULT '',
-    'firmware' TEXT,
+    'firmware' TEXT DEFAULT '',
-    'hostname' TEXT,
+    'hostname' TEXT DEFAULT '',
-    'ipv4' TEXT,
+    'ipv4' TEXT DEFAULT '',
-    'ipv6' TEXT,
+    'ipv6' TEXT DEFAULT '',
-    'mac' TEXT,
+    'mac' TEXT DEFAULT '',
     'sipuser_id' INTEGER
   );
@@ Line 73: / Line 73: @@
 !!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.
+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-sample-table.jpg?nolink|}}
+{{:userdoc:odbc-example-table.jpg?nolink|}}
 Example ''/etc/asterisk/func_odbc.conf'' snippet:
@@ Line 95: / Line 95: @@
 It is very simple and very powerful!
-!!Tip ->!! Teach yourself some basic SQL commands.
+!!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 =====