This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revisionNext revisionBoth sides next revision | ||
asterisk:realtime:kamailio-4.0.x-asterisk-11.3.0-astdb [2013/05/14 12:02] – [Asterisk Database] admin | asterisk:realtime:kamailio-4.0.x-asterisk-11.3.0-astdb [2013/06/03 21:30] – [Sample data] admin | ||
---|---|---|---|
Line 238: | Line 238: | ||
GRANT ALL ON asterisk.* to asterisk@localhost IDENTIFIED BY ' | GRANT ALL ON asterisk.* to asterisk@localhost IDENTIFIED BY ' | ||
+ | DROP TABLE IF EXISTS sipusers; | ||
CREATE TABLE `sipusers` ( | CREATE TABLE `sipusers` ( | ||
- | | + | `id` int(11) NOT NULL AUTO_INCREMENT, |
- | `name` varchar(80) NOT NULL DEFAULT | + | `name` varchar(10) NOT NULL, |
- | `host` varchar(31) NOT NULL DEFAULT '', | + | `ipaddr` varchar(15) |
- | `nat` varchar(5) NOT NULL DEFAULT 'no', | + | `port` int(5) DEFAULT NULL, |
- | `type` enum('user',' | + | `regseconds` int(11) DEFAULT NULL, |
- | `accountcode` varchar(20) DEFAULT NULL, | + | `defaultuser` varchar(10) DEFAULT |
- | `amaflags` varchar(13) DEFAULT NULL, | + | `fullcontact` varchar(35) |
- | `call-limit` smallint(5) unsigned | + | `regserver` varchar(20) DEFAULT NULL, |
- | `callgroup` varchar(10) DEFAULT NULL, | + | `useragent` varchar(20) DEFAULT NULL, |
- | `callerid` varchar(80) DEFAULT NULL, | + | `lastms` int(11) DEFAULT NULL, |
- | `cancallforward` char(3) DEFAULT ' | + | `host` varchar(40) DEFAULT NULL, |
- | `canreinvite` char(3) DEFAULT ' | + | `type` enum('friend',' |
- | `context` varchar(80) DEFAULT NULL, | + | `context` varchar(40) DEFAULT |
- | `defaultip` varchar(15) DEFAULT NULL, | + | `permit` varchar(40) |
- | `dtmfmode` varchar(7) DEFAULT NULL, | + | `deny` varchar(40) DEFAULT NULL, |
- | `fromuser` varchar(80) DEFAULT NULL, | + | `secret` varchar(40) DEFAULT NULL, |
- | `fromdomain` varchar(80) DEFAULT NULL, | + | `md5secret` varchar(40) DEFAULT NULL, |
- | `insecure` varchar(4) DEFAULT NULL, | + | `remotesecret` varchar(40) DEFAULT NULL, |
- | `language` char(2) DEFAULT NULL, | + | `transport` enum('udp',' |
- | `mailbox` varchar(50) DEFAULT NULL, | + | `dtmfmode` enum('rfc2833',' |
- | `md5secret` varchar(80) DEFAULT NULL, | + | `directmedia` enum(' |
- | `deny` varchar(95) DEFAULT NULL, | + | `nat` enum('yes',' |
- | `permit` varchar(95) DEFAULT NULL, | + | `callgroup` varchar(40) DEFAULT NULL, |
- | `mask` varchar(95) DEFAULT NULL, | + | `pickupgroup` varchar(40) DEFAULT NULL, |
- | `musiconhold` varchar(100) DEFAULT NULL, | + | `language` varchar(40) DEFAULT NULL, |
- | `pickupgroup` varchar(10) DEFAULT NULL, | + | `disallow` varchar(40) DEFAULT NULL, |
- | `qualify` char(3) DEFAULT NULL, | + | `allow` varchar(40) DEFAULT NULL, |
- | `regexten` varchar(80) DEFAULT NULL, | + | `insecure` varchar(40) DEFAULT |
- | `restrictcid` char(3) DEFAULT NULL, | + | `trustrpid` enum(' |
- | `rtptimeout` | + | `progressinband` enum(' |
- | `rtpholdtimeout` | + | `promiscredir` enum(' |
- | `secret` varchar(80) DEFAULT NULL, | + | `useclientcode` enum(' |
- | `setvar` varchar(100) DEFAULT NULL, | + | `accountcode` varchar(40) DEFAULT NULL, |
- | `disallow` varchar(100) DEFAULT NULL, | + | `setvar` varchar(40) DEFAULT NULL, |
- | `allow` varchar(100) DEFAULT NULL, | + | `callerid` varchar(40) DEFAULT NULL, |
- | `fullcontact` varchar(80) NOT NULL DEFAULT '', | + | `amaflags` varchar(40) DEFAULT NULL, |
- | `ipaddr` varchar(45) DEFAULT NULL, | + | `callcounter` enum(' |
- | `port` mediumint(5) unsigned NOT NULL DEFAULT '0', | + | `busylevel` int(11) DEFAULT NULL, |
- | `regserver` varchar(100) DEFAULT NULL, | + | |
- | `regseconds` int(11) NOT NULL DEFAULT '0', | + | `allowsubscribe` enum(' |
- | `lastms` int(11) NOT NULL DEFAULT | + | `videosupport` enum(' |
- | `username` varchar(80) NOT NULL DEFAULT '', | + | `maxcallbitrate` int(11) DEFAULT NULL, |
- | `defaultuser` varchar(80) NOT NULL DEFAULT '', | + | `rfc2833compensate` enum(' |
- | `subscribecontext` varchar(80) DEFAULT NULL, | + | |
- | `useragent` varchar(20) DEFAULT NULL, | + | `session-timers` enum(' |
- | `sippasswd` varchar(80) DEFAULT NULL, | + | `session-expires` int(11) DEFAULT NULL, |
- | PRIMARY KEY (`id`), | + | `session-minse` int(11) DEFAULT NULL, |
- | UNIQUE KEY `name_uk` (`name`) | + | `session-refresher` enum(' |
- | ); | + | `t38pt_usertpsource` varchar(40) DEFAULT NULL, |
+ | `regexten` varchar(40) DEFAULT NULL, | ||
+ | `fromdomain` varchar(40) DEFAULT NULL, | ||
+ | `fromuser` varchar(40) DEFAULT NULL, | ||
+ | `qualify` varchar(40) DEFAULT NULL, | ||
+ | `defaultip` varchar(40) DEFAULT NULL, | ||
+ | `rtptimeout` | ||
+ | `rtpholdtimeout` | ||
+ | `sendrpid` enum(' | ||
+ | `outboundproxy` varchar(40) DEFAULT NULL, | ||
+ | `callbackextension` varchar(40) DEFAULT NULL, | ||
+ | `timert1` int(11) DEFAULT NULL, | ||
+ | `timerb` int(11) DEFAULT | ||
+ | `qualifyfreq` int(11) | ||
+ | `constantssrc` enum('yes',' | ||
+ | `contactpermit` varchar(40) DEFAULT NULL, | ||
+ | `contactdeny` varchar(40) DEFAULT | ||
+ | `usereqphone` enum(' | ||
+ | `textsupport` enum('yes',' | ||
+ | `faxdetect` enum(' | ||
+ | `buggymwi` enum(' | ||
+ | `auth` varchar(40) DEFAULT NULL, | ||
+ | `fullname` varchar(40) DEFAULT | ||
+ | `trunkname` varchar(40) | ||
+ | `cid_number` varchar(40) DEFAULT NULL, | ||
+ | `callingpres` enum('allowed_not_screened',' | ||
+ | ,' | ||
+ | ,' | ||
+ | | ||
+ | `mohsuggest` varchar(40) | ||
+ | `parkinglot` varchar(40) DEFAULT | ||
+ | `hasvoicemail` enum(' | ||
+ | `subscribemwi` enum('yes',' | ||
+ | `vmexten` varchar(40) DEFAULT | ||
+ | `autoframing` enum(' | ||
+ | `rtpkeepalive` int(11) DEFAULT NULL, | ||
+ | `call-limit` int(11) DEFAULT NULL, | ||
+ | `g726nonstandard` enum('yes',' | ||
+ | `ignoresdpversion` enum(' | ||
+ | `allowtransfer` enum(' | ||
+ | | ||
+ | | ||
+ | PRIMARY KEY (`id`), | ||
+ | UNIQUE KEY `name` (`name`), | ||
+ | KEY `ipaddr` (`ipaddr`, | ||
+ | KEY `host` (`host`, | ||
+ | ) ENGINE=MyISAM; | ||
+ | DROP TABLE IF EXISTS sipregs; | ||
CREATE TABLE `sipregs` ( | CREATE TABLE `sipregs` ( | ||
`id` int(11) NOT NULL AUTO_INCREMENT, | `id` int(11) NOT NULL AUTO_INCREMENT, | ||
Line 307: | Line 355: | ||
- | CREATE TABLE `voiceboxes` | + | DROP TABLE IF EXISTS voicemail; |
- | | + | CREATE TABLE voicemail |
- | | + | -- All of these column names are very specific, including " |
- | `context` varchar(10) NOT NULL, | + | uniqueid |
- | | + | -- Mailbox context. |
- | | + | context |
- | `fullname` varchar(150) DEFAULT NULL, | + | -- Mailbox number. |
- | `email` varchar(50) DEFAULT NULL, | + | mailbox |
- | `pager` varchar(50) DEFAULT NULL, | + | -- Must be numeric. |
- | `tz` varchar(10) DEFAULT ' | + | password |
- | `attach` enum(' | + | -- Used in email and for Directory app |
- | | + | fullname |
- | | + | -- Email address (will get sound file if attach=yes) |
- | | + | email CHAR(80), |
- | `review` enum('yes','no') NOT NULL DEFAULT ' | + | -- Email address (won't get sound file) |
- | `operator` enum(' | + | pager CHAR(80), |
- | `envelope` enum(' | + | -- Attach sound file to email - YES/no |
- | `sayduration` enum(' | + | attach CHAR(3), |
- | `saydurationm` tinyint(4) NOT NULL DEFAULT ' | + | -- Which sound format to attach |
- | | + | attachfmt CHAR(10), |
- | | + | -- Send email from this address |
- | | + | serveremail CHAR(80), |
- | | + | -- Prompts in alternative language |
- | | + | language CHAR(20), |
- | | + | -- Alternative timezone, as defined in voicemail.conf |
- | | + | tz CHAR(30), |
- | | + | -- Delete voicemail from server after sending email notification - yes/NO |
- | KEY `mailbox_context` | + | deletevoicemail CHAR(3), |
+ | -- Read back CallerID information during playback - yes/NO | ||
+ | saycid CHAR(3), | ||
+ | -- Allow user to send voicemail from within VoicemailMain - YES/no | ||
+ | sendvoicemail CHAR(3), | ||
+ | -- Listen to voicemail and approve before sending - yes/NO | ||
+ | review | ||
+ | -- Warn user a temporary greeting exists - yes/NO | ||
+ | tempgreetwarn CHAR(3), | ||
+ | -- Allow '0' | ||
+ | operator | ||
+ | -- Hear date/time of message within VoicemailMain - YES/no | ||
+ | envelope | ||
+ | -- Hear length of message within VoicemailMain - yes/NO | ||
+ | sayduration | ||
+ | -- Minimum duration in minutes to say | ||
+ | saydurationm | ||
+ | -- Force new user to record name when entering voicemail - yes/NO | ||
+ | forcename CHAR(3), | ||
+ | -- Force new user to record greetings when entering voicemail - yes/NO | ||
+ | forcegreetings CHAR(3), | ||
+ | -- Context in which to dial extension for callback | ||
+ | callback CHAR(80), | ||
+ | -- Context in which to dial extension | ||
+ | dialout CHAR(80), | ||
+ | -- Context in which to execute 0 or * escape during greeting | ||
+ | exitcontext CHAR(80), | ||
+ | -- Maximum messages in a folder | ||
+ | maxmsg INT(5), | ||
+ | -- Increase DB gain on recorded message by this amount | ||
+ | volgain DECIMAL(5,2), | ||
+ | -- IMAP user for authentication | ||
+ | imapuser VARCHAR(80), | ||
+ | -- IMAP password for authentication | ||
+ | imappassword VARCHAR(80), | ||
+ | -- IMAP server location (if using IMAP storage) | ||
+ | imapsever VARCHAR(80), | ||
+ | -- IMAP port (if using IMAP storage) | ||
+ | imapport VARCHAR(8), | ||
+ | -- IMAP flags (if using IMAP storage) | ||
+ | imapflags VARCHAR(80), | ||
+ | stamp timestamp | ||
); | ); | ||
- | + | DROP TABLE IF EXISTS voicemail_data; | |
- | CREATE TABLE `voicemessages` | + | CREATE TABLE voicemail_data |
- | `id` int(11) NOT NULL AUTO_INCREMENT, | + | -- Path to the recording |
- | | + | filename CHAR(255) NOT NULL PRIMARY KEY, |
- | `dir` varchar(80) DEFAULT '' | + | -- Mailbox number |
- | `context` varchar(80) DEFAULT '' | + | origmailbox CHAR(80), |
- | `macrocontext` varchar(80) DEFAULT '' | + | -- Dialplan |
- | `callerid` varchar(40) DEFAULT '' | + | context CHAR(80), |
- | `origtime` varchar(40) DEFAULT '' | + | -- Dialplan context, if voicemail was invoked from a macro |
- | `duration` varchar(20) DEFAULT '' | + | macrocontext |
- | | + | -- Dialplan extension |
- | | + | exten CHAR(80), |
- | `recording` longblob, | + | -- Dialplan priority |
- | `flag` varchar(128) DEFAULT '' | + | priority INT(5), |
- | | + | -- Name of the channel, when message was left |
- | KEY `dir` (`dir`) | + | callerchan CHAR(80), |
+ | -- CallerID on the channel, when message was left | ||
+ | callerid | ||
+ | -- Contrary to the name, origdate is a full datetime, in localized format | ||
+ | origdate CHAR(30), | ||
+ | -- Same date as origdate, but in Unixtime | ||
+ | origtime | ||
+ | -- Value of the channel variable VM_CATEGORY, | ||
+ | category CHAR(30), | ||
+ | -- Length of the message, in seconds | ||
+ | duration | ||
+ | ); | ||
+ | DROP TABLE IF EXISTS voicemail_messages; | ||
+ | CREATE TABLE voicemail_messages ( | ||
+ | -- Logical directory | ||
+ | dir CHAR(255), | ||
+ | -- Message number within the logical directory | ||
+ | msgnum INT(4), | ||
+ | -- Dialplan context | ||
+ | context CHAR(80), | ||
+ | -- Dialplan context, if Voicemail was invoked from a macro | ||
+ | macrocontext CHAR(80), | ||
+ | -- CallerID, when the message was left | ||
+ | callerid CHAR(80), | ||
+ | -- Date when the message was left, in Unixtime | ||
+ | origtime INT(11), | ||
+ | -- Length of the message, in seconds | ||
+ | duration INT(11), | ||
+ | -- The recording | ||
+ | recording BLOB, | ||
+ | -- Text flags indicating urgency of the message | ||
+ | flag CHAR(30), | ||
+ | -- Value of channel variable VM_CATEGORY, | ||
+ | category CHAR(30), | ||
+ | -- Owner of the mailbox | ||
+ | mailboxuser CHAR(30), | ||
+ | -- Context of the owner of the mailbox | ||
+ | mailboxcontext CHAR(30), | ||
+ | -- Unique ID of the message, | ||
+ | msg_id char(40), | ||
+ | PRIMARY | ||
); | ); | ||
</ | </ | ||
Line 375: | Line 504: | ||
</ | </ | ||
<note important> | <note important> | ||
- | **voiceboxes** is used to store voicemail box profiles and has the standard structure required by Asterisk. Storing voice box profiles in database allows to run several instances of Asterisk that can be load balanced or used in fail-over mode to store or listen to voice messages. | + | **voicemail** is used to store voicemail box profiles and has the standard structure required by Asterisk. Storing voice box profiles in database allows to run several instances of Asterisk that can be load balanced or used in fail-over mode to store or listen to voice messages. |
+ | </ | ||
+ | <note important> | ||
+ | **voicemail_messages** is used to store voice messages and has the standard structure required by Asterisk. Storing voice messages in database allows to run several instances of Asterisk that can be load balanced or used in fail-over mode to store or listen to voice messages. | ||
+ | </ | ||
+ | < | ||
+ | If you need to do adjustments to the tables used by Asterisk, check the SQL scripts from Asterisk source tree located in **contrib/ | ||
</ | </ | ||
<note important> | <note important> | ||
- | **voicemessages** | + | In previous versions of this tutorial, there were different table names for **voicemail** and **voicemail_messages** (respectively **voiceboxes** and **voicemessages**) -- they were changed |
</ | </ | ||
- | < | ||
- | </ | ||
===== UnixODBC Configuration ===== | ===== UnixODBC Configuration ===== | ||
Line 431: | Line 564: | ||
sippeers => odbc, | sippeers => odbc, | ||
sipregs => odbc, | sipregs => odbc, | ||
- | voicemail => odbc, | + | voicemail => odbc, |
</ | </ | ||
Line 468: | Line 601: | ||
<code sql> | <code sql> | ||
- | insert into sipusers (name, | + | insert into sipusers (name, |
values (' | values (' | ||
- | insert into sipusers (name, | + | insert into sipusers (name, |
values (' | values (' | ||
- | insert into sipusers (name, | + | insert into sipusers (name, |
values (' | values (' | ||
Line 479: | Line 612: | ||
insert into sipregs(name) values(' | insert into sipregs(name) values(' | ||
- | insert into voiceboxes(customer_id, | + | insert into voicemail(context, mailbox, password) values (' |
- | insert into voiceboxes(customer_id, | + | insert into voicemail(context, mailbox, password) values (' |
- | insert into voiceboxes(customer_id, | + | insert into voicemail(context, mailbox, password) values (' |
</ | </ | ||