asterisk:realtime:kamailio-4.0.x-asterisk-11.3.0-astdb

Differences

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

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision
Previous revision
asterisk:realtime:kamailio-4.0.x-asterisk-11.3.0-astdb [2013/05/14 12:09] – [Asterisk Database] adminasterisk:realtime:kamailio-4.0.x-asterisk-11.3.0-astdb [2014/04/30 16:35] (current) – [Config File] admin
Line 238: Line 238:
 GRANT ALL ON asterisk.* to asterisk@localhost IDENTIFIED BY 'asterisk_password'; GRANT ALL ON asterisk.* to asterisk@localhost IDENTIFIED BY 'asterisk_password';
  
 +DROP TABLE IF EXISTS sipusers;
 CREATE TABLE `sipusers` ( CREATE TABLE `sipusers` (
      `id` int(11) NOT NULL AUTO_INCREMENT,      `id` int(11) NOT NULL AUTO_INCREMENT,
Line 312: Line 313:
       `trunkname` varchar(40) DEFAULT NULL,       `trunkname` varchar(40) DEFAULT NULL,
       `cid_number` varchar(40) DEFAULT NULL,       `cid_number` varchar(40) DEFAULT NULL,
-      `callingpres` enum('allowed_not_screened','allowed_passed_screen','allowed_failed_screen','allowed','prohib_not_screened','prohib_passed_screen','prohib_failed_screen','prohib') DEFAULT NULL,+      `callingpres` enum('allowed_not_screened','allowed_passed_screen','allowed_failed_screen' 
 +                         ,'allowed','prohib_not_screened','prohib_passed_screen','prohib_failed_screen' 
 +                         ,'prohib') DEFAULT NULL,
       `mohinterpret` varchar(40) DEFAULT NULL,       `mohinterpret` varchar(40) DEFAULT NULL,
       `mohsuggest` varchar(40) DEFAULT NULL,       `mohsuggest` varchar(40) DEFAULT NULL,
Line 334: Line 337:
  
  
 +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 351: Line 355:
  
  
-CREATE TABLE `voiceboxes` +DROP TABLE IF EXISTS voicemail; 
-  `uniqueid` int(4) NOT NULL AUTO_INCREMENT, +CREATE TABLE voicemail 
-  `customer_id` varchar(10) DEFAULT NULL, + -- All of these column names are very specific, including "uniqueid" Do not change them if you wish voicemail to work. 
-  `context` varchar(10) NOT NULL, + uniqueid INT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY
-  `mailbox` varchar(10) NOT NULL, + -- Mailbox context. 
-  `password` varchar(12) NOT NULL, + context CHAR(80) NOT NULL DEFAULT 'default'
-  `fullname` varchar(150DEFAULT NULL+ -- Mailbox number.  Should be numeric. 
-  `email` varchar(50DEFAULT NULL+ mailbox CHAR(80) NOT NULL, 
-  `pager` varchar(50DEFAULT NULL+ -- Must be numeric.  Negative if you don't want it to be changed from VoicemailMain 
-  `tz` varchar(10DEFAULT 'central'+ password CHAR(80) NOT NULL, 
-  `attach` enum('yes','no'NOT NULL DEFAULT 'yes'+ -- Used in email and for Directory app 
-  `saycid` enum('yes','no'NOT NULL DEFAULT 'yes'+ fullname CHAR(80), 
-  `dialout` varchar(10DEFAULT NULL+ -- Email address (will get sound file if attach=yes) 
-  `callback` varchar(10DEFAULT NULL+ email CHAR(80), 
-  `review` enum('yes','no') NOT NULL DEFAULT 'no', + -- Email address (won't get sound file) 
-  `operator` enum('yes','no'NOT NULL DEFAULT 'no', + pager CHAR(80), 
-  `envelope` enum('yes','no'NOT NULL DEFAULT 'no'+ -- Attach sound file to email - YES/no 
-  `sayduration` enum('yes','no'NOT NULL DEFAULT 'no'+ attach CHAR(3), 
-  `saydurationm` tinyint(4NOT NULL DEFAULT '1'+ -- Which sound format to attach 
-  `sendvoicemail` enum('yes','no'NOT NULL DEFAULT 'no'+ attachfmt CHAR(10), 
-  `delete` enum('yes','no'DEFAULT 'no'+ -- Send email from this address 
-  `nextaftercmd` enum('yes','no'NOT NULL DEFAULT 'yes'+ serveremail CHAR(80), 
-  `forcename` enum('yes','no'NOT NULL DEFAULT 'no'+ -- Prompts in alternative language 
-  `forcegreetings` enum('yes','no'NOT NULL DEFAULT 'no'+ language CHAR(20), 
-  `hidefromdir` enum('yes','no'NOT NULL DEFAULT 'yes'+ -- Alternative timezone, as defined in voicemail.conf 
-  `stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP+ tz CHAR(30)
-  PRIMARY KEY (`uniqueid`), + -- Delete voicemail from server after sending email notification - yes/NO 
-  KEY `mailbox_context` (`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 CHAR(3), 
 + -- Warn user a temporary greeting exists - yes/NO 
 + tempgreetwarn CHAR(3), 
 + -- Allow '0to jump out during greeting - yes/NO 
 + operator CHAR(3)
 + -- Hear date/time of message within VoicemailMain - YES/no 
 + envelope CHAR(3), 
 + -- Hear length of message within VoicemailMain - yes/NO 
 + sayduration CHAR(3), 
 + -- Minimum duration in minutes to say 
 + saydurationm INT(3), 
 + -- 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 (from advanced menu) 
 + dialout CHAR(80), 
 + -- Context in which to execute 0 or * escape during greeting 
 + exitcontext CHAR(80), 
 + -- Maximum messages in a folder (100 if not specified) 
 + maxmsg INT(5), 
 + -- Increase DB gain on recorded message by this amount (0.0 means none) 
 + volgain DECIMAL(5,2), 
 + -- IMAP user for authentication (if using IMAP storage) 
 + imapuser VARCHAR(80), 
 + -- IMAP password for authentication (if using IMAP storage) 
 + 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 
-  `msgnum` int(11NOT NULL DEFAULT '0', + filename CHAR(255) NOT NULL PRIMARY KEY
-  `dir` varchar(80) DEFAULT ''+ -- Mailbox number (without context
-  `context` varchar(80) DEFAULT ''+ origmailbox CHAR(80), 
-  `macrocontext` varchar(80) DEFAULT ''+ -- Dialplan context 
-  `callerid` varchar(40DEFAULT ''+ context CHAR(80), 
-  `origtime` varchar(40DEFAULT ''+ -- Dialplan context, if voicemail was invoked from a macro 
-  `duration` varchar(20DEFAULT ''+ macrocontext CHAR(80), 
-  `mailboxuser` varchar(80) DEFAULT ''+ -- Dialplan extension 
-  `mailboxcontext` varchar(80) DEFAULT ''+ exten CHAR(80), 
-  `recording` longblob+ -- Dialplan priority 
-  `flag` varchar(128DEFAULT ''+ priority INT(5), 
-  PRIMARY KEY (`id`), + -- Name of the channel, when message was left 
-  KEY `dir` (`dir`)+ callerchan CHAR(80), 
 + -- CallerID on the channel, when message was left 
 + callerid CHAR(80), 
 + -- Contrary to the name, origdate is a full datetime, in localized format 
 + origdate CHAR(30), 
 + -- Same date as origdate, but in Unixtime 
 + origtime INT(11), 
 + -- Value of the channel variable VM_CATEGORY, if set 
 + category CHAR(30), 
 + -- Length of the message, in seconds 
 + duration INT(11) 
 +); 
 +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 itself 
 + recording BLOB
 + -- Text flags indicating urgency of the message 
 + flag CHAR(30), 
 + -- Value of channel variable VM_CATEGORY, if set 
 + 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 KEY (dir, msgnum)
 ); );
 </code> </code>
Line 419: Line 504:
 </note> </note>
 <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> </note>
 <note important> <note important>
-**voicemessages** 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.+**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.
 </note> </note>
 <note> <note>
 ​If you need to do adjustments to the tables used by Asterisk, check the SQL scripts from Asterisk source tree located in **contrib/​realtime/​mysql**. ​If you need to do adjustments to the tables used by Asterisk, check the SQL scripts from Asterisk source tree located in **contrib/​realtime/​mysql**.
 +</note>
 +<note important>
 +In previous versions of this tutorial, there were different table names for **voicemail** and **voicemail_messages** (respectively **voiceboxes** and **voicemessages**) -- they were changed to match the default name in Asterisk, because they have same structure. **sipusers** and **sipregs** are a bit customized, thus they have different names - **sipusers** maps over **sippeers** and **sipregs** can be actually a view over **sipusers**. The table **voicemail_messages** is used for storing voice messages in database via odbc, you have to set its value inside voicemail.conf file, in the attribute **odbctable**.
 </note> </note>
  
Line 476: Line 564:
 sippeers => odbc,asterisk,sipusers sippeers => odbc,asterisk,sipusers
 sipregs => odbc,asterisk,sipregs sipregs => odbc,asterisk,sipregs
-voicemail => odbc,asterisk,voiceboxes+voicemail => odbc,asterisk,voicemail
 </code> </code>
  
Line 513: Line 601:
  
 <code sql> <code sql>
-insert into sipusers (name, username, host, sippasswd, fromuser, fromdomain, mailbox)+insert into sipusers (name, defaultuser, host, sippasswd, fromuser, fromdomain, mailbox)
   values ('101', '101', 'dynamic', '101', '101', 'yoursip.com', '101');   values ('101', '101', 'dynamic', '101', '101', 'yoursip.com', '101');
-insert into sipusers (name, username, host, sippasswd, fromuser, fromdomain, mailbox)+insert into sipusers (name, defaultuser, host, sippasswd, fromuser, fromdomain, mailbox)
   values ('102', '102', 'dynamic', '102', '102', 'yoursip.com', '102');   values ('102', '102', 'dynamic', '102', '102', 'yoursip.com', '102');
-insert into sipusers (name, username, host, sippasswd, fromuser, fromdomain, mailbox)+insert into sipusers (name, defaultuser, host, sippasswd, fromuser, fromdomain, mailbox)
   values ('103', '103', 'dynamic', '103', '103', 'yoursip.com', '103');   values ('103', '103', 'dynamic', '103', '103', 'yoursip.com', '103');
  
Line 524: Line 612:
 insert into sipregs(name) values('103'); insert into sipregs(name) values('103');
  
-insert into voiceboxes(customer_id, context, mailbox, password) values ('101', 'default', '101', '1234'); +insert into voicemail(context, mailbox, password) values ('default', '101', '1234'); 
-insert into voiceboxes(customer_id, context, mailbox, password) values ('101', 'default', '102', '1234'); +insert into voicemail(context, mailbox, password) values ('default', '102', '1234'); 
-insert into voiceboxes(customer_id, context, mailbox, password) values ('101', 'default', '103', '1234');+insert into voicemail(context, mailbox, password) values ('default', '103', '1234');
 </code> </code>
  
Line 936: Line 1024:
  
 #!ifdef WITH_ASTERISK #!ifdef WITH_ASTERISK
-modparam("auth_db", "user_column", "username")+modparam("auth_db", "user_column", "name")
 modparam("auth_db", "password_column", "sippasswd") modparam("auth_db", "password_column", "sippasswd")
 modparam("auth_db", "db_url", DBASTURL) modparam("auth_db", "db_url", DBASTURL)
Line 1301: Line 1389:
 # Authentication route # Authentication route
 route[AUTH] { route[AUTH] {
 +
 + # if caller is not local subscriber, then check if it calls
 + # a local destination, otherwise deny, not an open relay here
 + if (from_uri!=myself && uri!=myself)
 + {
 + sl_send_reply("403","Not relaying");
 + exit;
 + }
 +
 #!ifdef WITH_AUTH #!ifdef WITH_AUTH
  
Line 1332: Line 1429:
  consume_credentials();  consume_credentials();
  }  }
- # if caller is not local subscriber, then check if it calls 
- # a local destination, otherwise deny, not an open relay here 
- if (from_uri!=myself && uri!=myself) 
- { 
- sl_send_reply("403","Not relaying"); 
- exit; 
- } 
- 
 #!endif #!endif
  return;  return;

100%


Copyright 2010-2020 Asipto.com