Asterisk Full RealTime Database example

Configure asterisk MySql connection:

create the res_mysql.conf file in /etc/asterisk vi /etc/asterisk/res_mysql.conf enter the following:

 [general]
 dbhost = 127.0.0.1
 dbname = asteriskrealtime
 dbuser = root
 dbpass = password
 dbport = 3306

save the following configuration files, we will use a very short files instead in order to simplefy the setup:

 cp /etc/asterisk/extensions.conf /etc/asterisk/extensions.conf_orig
 cp /etc/asterisk/extconfig.conf /etc/asterisk/extconfig.conf_orig
 cp /etc/asterisk/sip.conf /etc/asterisk/sip.conf_orig

Setting the RealTime tables in asterisk

In order to let asterisk know of the RealTime tables, change the extconfig.conf file as follow

[settings]
sippeers => mysql,general,sip_buddies
extensions => mysql,general,extensions
voicemail => mysql,general,voicemail_users
queues => mysql,general,queue_table
queue_members => mysql,general,queue_member_table
meetme => mysql,general,meetme

Configure sip.conf and exensioncs.conf

sip.conf should Register your account and DID with the SIP provider Change the default context to “from-sip” for the inbound calls from the SIP provider. Sip.conf will look as follow:

 [general]
 context=from-sip                        ; Default context for incoming calls
 ;
 bindport=5060                   ; bindport is the local UDP port that Asterisk will listen on
 bindaddr=0.0.0.0                ; IP address to bind to (0.0.0.0 binds to all)
 ;
 disallow=all                    ; First disallow all codecs
 allow=gsm
 allow=ulaw                      ; Allow codecs in order of preference
 ;
 register => 12121111111:1234:11111111@sipauth.deltathree.com/1000

Configure extentions.conf to take the context “from-sip” from RealTime:

 [general]
 [globals]
 ;
 [from-sip]
 switch =>Realtime

Creating the database

check that the mysql is running, and start it if needed:

 /etc/init.d/mysqld status
 /etc/init.d/mysqld start

in order to start mysql after reboot check the status

 chkconfig | grep mysql

If 3,4,5 are “off” turn to “on”

 chkconfig --level 345 mysqld on

set your mysql root password (in needed):

 mysqladmin -u root password password

go into the mysql CLI:

  mysql -u root -p

insert the “password” Create the database if not exist, we we call it “asteriskrealtime”

 create database if not exists `asteriskrealtime`;

change to the asteriskrealtime database:

 use asteriskrealtime;

now we will create the following tables:

sip_buddies
extensions
voicemail_users
queue_table
queue_member_table
meetme

 CREATE DATABASE  IF NOT EXISTS`asteriskrealtime`;
 USE `asteriskrealtime`;

 CREATE TABLE `sip_buddies` (
 `id` int(11) NOT NULL auto_increment,
 `name` varchar(80) NOT NULL,
 `callerid` varchar(80) default NULL,
 `defaultuser` varchar(80) NOT NULL,
 `regexten` varchar(80) NOT NULL,
 `secret` varchar(80) default NULL,
 `mailbox` varchar(50) default NULL,
 `accountcode` varchar(20) default NULL,
 `context` varchar(80) default NULL,
 `amaflags` varchar(7) default NULL,
 `callgroup` varchar(10) default NULL,
 `canreinvite` char(3) default 'yes',
 `defaultip` varchar(15) default NULL,
 `dtmfmode` varchar(7) default NULL,
 `fromuser` varchar(80) default NULL,
 `fromdomain` varchar(80) default NULL,
 `fullcontact` varchar(80) default NULL,
 `host` varchar(31) NOT NULL,
 `insecure` varchar(4) default NULL,
 `language` char(2) default NULL,
 `md5secret` varchar(80) default NULL,
 `nat` varchar(5) NOT NULL default 'no',
 `deny` varchar(95) default NULL,
 `permit` varchar(95) default NULL,
 `mask` varchar(95) default NULL,
 `pickupgroup` varchar(10) default NULL,
 `port` varchar(5) NOT NULL,
 `qualify` char(3) default NULL,
 `restrictcid` char(1) default NULL,
 `rtptimeout` char(3) default NULL,
 `rtpholdtimeout` char(3) default NULL,
 `type` varchar(6) NOT NULL default 'friend',
 `disallow` varchar(100) default 'all',
 `allow` varchar(100) default 'g729;ilbc;gsm;ulaw;alaw',
 `musiconhold` varchar(100) default NULL,
 `regseconds` int(11) NOT NULL default '0',
 `ipaddr` varchar(15) NOT NULL,
 `cancallforward` char(3) default 'yes',
 `lastms` int(11) NOT NULL,
 `useragent` char(255) default NULL,
 `regserver` varchar(100) default NULL,
 PRIMARY KEY  (`id`),
 UNIQUE KEY `name` (`name`),
 KEY `name_2` (`name`)
 ) ENGINE=MyISAM AUTO_INCREMENT=893 DEFAULT CHARSET=latin1;

 CREATE TABLE `extensions` (
  `id` int(11) NOT NULL auto_increment,
  `context` varchar(20) NOT NULL default '',
  `exten` varchar(20) NOT NULL default '',
  `priority` tinyint(4) NOT NULL default '0',
  `app` varchar(20) NOT NULL default '',
  `appdata` varchar(128) NOT NULL default '',
  PRIMARY KEY  (`context`,`exten`,`priority`),
  KEY `id` (`id`)
  ) ENGINE=MyISAM AUTO_INCREMENT=257 DEFAULT CHARSET=latin1;

 CREATE TABLE `voicemail_users` (
  `uniqueid` int(11) NOT NULL auto_increment,
  `customer_id` varchar(11) NOT NULL default '0',
  `context` varchar(50) NOT NULL,
  `mailbox` varchar(11) NOT NULL default '0',
  `password` varchar(5) NOT NULL default '0',
  `fullname` varchar(150) NOT NULL,
  `email` varchar(50) NOT NULL,
  `pager` varchar(50) NOT NULL,
  `tz` varchar(10) NOT NULL default 'central',
  `attach` varchar(4) NOT NULL default 'yes',
  `saycid` varchar(4) NOT NULL default 'yes',
  `dialout` varchar(10) NOT NULL,
  `callback` varchar(10) NOT NULL,
  `review` varchar(4) NOT NULL default 'no',
  `operator` varchar(4) NOT NULL default 'no',
  `envelope` varchar(4) NOT NULL default 'no',
  `sayduration` varchar(4) NOT NULL default 'no',
  `saydurationm` tinyint(4) NOT NULL default '1',
  `sendvoicemail` varchar(4) NOT NULL default 'no',
  `delete` varchar(4) NOT NULL default 'no',
  `nextaftercmd` varchar(4) NOT NULL default 'yes',
  `forcename` varchar(4) NOT NULL default 'no',
  `forcegreetings` varchar(4) NOT NULL default 'no',
  `hidefromdir` varchar(4) NOT NULL default 'yes',
  `stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`uniqueid`),
  KEY `mailbox_context` (`mailbox`,`context`)
  ) ENGINE=MyISAM AUTO_INCREMENT=2001 DEFAULT CHARSET=latin1;

 CREATE TABLE `queue_table` (
  `name` varchar(128) NOT NULL,
  `musiconhold` varchar(128) default NULL,
  `announce` varchar(128) default NULL,
  `context` varchar(128) default NULL,
  `timeout` int(11) default NULL,
  `monitor_join` tinyint(1) default NULL,
  `monitor_format` varchar(128) default NULL,
  `queue_youarenext` varchar(128) default NULL,
  `queue_thereare` varchar(128) default NULL,
  `queue_callswaiting` varchar(128) default NULL,
  `queue_holdtime` varchar(128) default NULL,
  `queue_minutes` varchar(128) default NULL,
  `queue_seconds` varchar(128) default NULL,
  `queue_lessthan` varchar(128) default NULL,
  `queue_thankyou` varchar(128) default NULL,
  `queue_reporthold` varchar(128) default NULL,
  `announce_frequency` int(11) default NULL,
  `announce_round_seconds` int(11) default NULL,
  `announce_holdtime` varchar(128) default NULL,
  `retry` int(11) default NULL,
  `wrapuptime` int(11) default NULL,
  `maxlen` int(11) default NULL,
  `servicelevel` int(11) default NULL,
  `strategy` varchar(128) default NULL,
  `joinempty` varchar(128) default NULL,
  `leavewhenempty` varchar(128) default NULL,
  `eventmemberstatus` tinyint(1) default NULL,
  `eventwhencalled` tinyint(1) default NULL,
  `reportholdtime` tinyint(1) default NULL,
  `memberdelay` int(11) default NULL,
  `weight` int(11) default NULL,
  `timeoutrestart` tinyint(1) default NULL,
  `periodic_announce` varchar(50) default NULL,
  `periodic_announce_frequency` int(11) default NULL,
  `ringinuse` tinyint(1) default NULL,
  `setinterfacevar` tinyint(1) default NULL,
  PRIMARY KEY  (`name`)
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 CREATE TABLE `queue_member_table` (
  `uniqueid` int(10) unsigned NOT NULL auto_increment,
  `membername` varchar(40) default NULL,
  `queue_name` varchar(128) default NULL,
  `interface` varchar(128) default NULL,
  `penalty` int(11) default NULL,
  `paused` int(11) default NULL,
  PRIMARY KEY  (`uniqueid`),
  UNIQUE KEY `queue_interface` (`queue_name`,`interface`)
  ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

   CREATE TABLE `meetme` (
  `confno` varchar(80) NOT NULL default '0',
  `username` varchar(64) NOT NULL default '',
  `domain` varchar(128) NOT NULL default '',
  `pin` varchar(20) default NULL,
  `adminpin` varchar(20) default NULL,
  `members` int(11) NOT NULL default '0',
  PRIMARY KEY  (`confno`)
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

verify that all tables created:

 mysql> show tables;
 +----------------------------+
 | Tables_in_asteriskrealtime |
 +----------------------------+
 | extensions                 | 
 | meetme                     | 
 | queue_member_table         | 
 | queue_table                | 
 | sip_buddies                | 
 | voicemail_users            | 
 +----------------------------+
 6 rows in set (0.01 sec)

Insert data to the tables:

insert the users to the sip_buddies table: 1000 1000 as extensions and 11111111 as user of sip provider. I will use deltathree as the sip provider

User 11111111 pass 1234
Sip server: sipauth.deltathree.com

note it is a fake user and password. you will need a real user to test real call. you can test with this fake user but you will get an error announcement from the sip provider.

 INSERT INTO asteriskrealtime.sip_buddies (
 NAME, defaultuser, secret, context, HOST, nat, qualify, TYPE)
 VALUES 	(
 '1000', '1000', '1234', 'from-sip', 'dynamic', 'yes', 'no', 'friend');
 ;
 INSERT INTO asteriskrealtime.sip_buddies (
 NAME, defaultuser, secret, context, HOST, nat, qualify, TYPE)
 VALUES (
 '2000', '2000', '1234', 'from-sip', 'dynamic', 'yes', 'no', 'friend');
 ;	
 INSERT INTO asteriskrealtime.sip_buddies (
 NAME, defaultuser, secret, context, HOST, nat, qualify, TYPE)
 VALUES 	(
 'deltathree', '11111111', '1234', 'from-sip', 'sipauth.deltathree.com', 'yes', 'no', 'friend');

inster the dial-plan in the extensions table

 insert  into `extensions`(`context`,`exten`,`priority`,`app`,`appdata`) 
  values 
  ('from-sip','12121111111',1,'Dial','SIP/1000|60'),
  ('from-sip','12122222222',1,'Dial','SIP/2000|60'),
  ('from-sip','_X.',1,'Dial','SIP/${EXTEN}|30'),
  ('from-sip','_9X.',1,'Dial','SIP/${EXTEN:1}@deltathree'),
  ('from-sip','_X.',2,'VoiceMail','${EXTEN}@from-sip'),
  ('from-sip','_X.',3,'hangup',''),
  ('from-sip','_*0',1,'VoiceMailMain','${CALLERID(num)}@from-sip'),
  ('from-sip','12127777777',1,'Queue','my_queue'),
  ('from-sip','12129999999',1,'MeetMe','my_conf');

add voicemail to ext 1000 & 2000:

 INSERT INTO asteriskrealtime.voicemail_users 
 (customer_id, 	context, 	mailbox, 	PASSWORD, 	fullname, 	email )	
 VALUES	
 ('1000', 	'from-sip', 	'1000', '1234' , 	'User-A', 'UserA@myemail.com');
 ;
 INSERT INTO asteriskrealtime.voicemail_users 
 (customer_id, 	context, 	mailbox, 	PASSWORD, 	fullname, 	email )	
 VALUES	
 ('2000', 	'from-sip', 	'2000', '1234' , 	'User-B', 'UserB@myemail.com');

Insert the new queue “my_queue”:

 INSERT INTO `queue_table` 
 (`name`,`context`) 
 VALUES 
 ('my_queue','from-sip');

Insert the two extensions 1000 and 2000 as members:

 INSERT INTO `queue_member_table`
 (`uniqueid`,`membername`,`queue_name`,`interface`,`penalty`,`paused`) 
 VALUES 
 (1,'SIP/1000@from-sip','my_queue','SIP/1000',NULL,0);
 ;
 INSERT INTO `queue_member_table`
 (`uniqueid`,`membername`,`queue_name`,`interface`,`penalty`,`paused`)
 VALUES 
 (2,'SIP/2000@from-sip','my_queue','SIP/2000',NULL,0);

insert the conference bridge:

 INSERT INTO `meetme` (`confno`,`username`,`domain`,`pin`,`adminpin`,`members`) 
  VALUES 
  ('my_conf','','','5555','4444',0);

Restart asterisk and go into the CLI:

 asterisk -rx "restart now"
 asterisk -r

Check the asterisk realtime status

 localhost*CLI> realtime mysql status 
 general connected to asteriskrealtime@127.0.0.1, port 3306 with username root for 12 seconds.

2 thoughts on “Asterisk Full RealTime Database example”

eduguruPost author
September 10, 2013 at 6:15 am
CREATE TABLE `queue_log` (
`id` int(10) unsigned NOT NULL auto_increment,
`time` char(10) unsigned default NULL,
`callid` varchar(32) NOT NULL default ”,
`queuename` varchar(32) NOT NULL default ”,
`agent` varchar(32) NOT NULL default ”,
`event` varchar(32) NOT NULL default ”,
`data` varchar(255) NOT NULL default ”,
PRIMARY KEY (`id`)
);
Log in to Reply
eduguruPost author
September 10, 2013 at 6:19 am
CREATE TABLE IF NOT EXISTS `queue_log` (
`recid` int(10) unsigned NOT NULL auto_increment,
`origid` int(10) unsigned NOT NULL,
`callid` varchar(32) NOT NULL default ”,
`queuename` varchar(32) NOT NULL default ”,
`agentdev` varchar(32) NOT NULL,
`event` varchar(32) NOT NULL default ”,
`data` varchar(128) NOT NULL,
`data1` varchar(128) NOT NULL,
`data2` varchar(128) NOT NULL,
`data3` varchar(128) NOT NULL,
`data4` varchar(128) NOT NULL,
`data5` varchar(128) NOT NULL,
`time` datetime NOT NULL default ‘0000-00-00 00:00:00’,
PRIMARY KEY (`recid`),
KEY `data1` (`data1`),
KEY `data2` (`data2`),
KEY `data3` (`data3`),
KEY `event` (`event`),
KEY `queuename` (`queuename`),
KEY `callid` (`callid`),
KEY `time` (`time`),
KEY `agentdev` (`agentdev`),
KEY `origid` (`origid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
Log in to Reply

Eduguru

2 thoughts on “Asterisk Full RealTime Database example”

Leave a Reply Cancel reply

Configure asterisk MySql connection:

Setting the RealTime tables in asterisk

Configure sip.conf and exensioncs.conf

Creating the database

Insert data to the tables:

Posts to Review:

2 thoughts on “Asterisk Full RealTime Database example”

Leave a Reply Cancel reply