bahjons.com

By default, you rely heavily on Asterisk .conf file for creating your dialplans, iax and sip users and peers, as well as voicemail users. And after making changes to the configuration files, you have to reload Asterisk to apply them. However, the guys at Asterisk have made it easier. With Asterisk Realtime, you can add new users and modify your dialplans on the fly. Everything is neatly stored in a database, and loaded on the fly. So in this article, we will discuss how to get started so that you can make use of Asterisk Realtime.

You are going to begin by installing Asterisk. You can check out my quick install guide here.
Once you have that all complete, you will want to make sure that MYSQL is running. So we can start it by issuing the following command from ssh:

/etc/init.d/mysqld start

We need to create our Asterisk database, and setup our tables now, from the mysql command line:

CREATE DATABASE asterisk;
GRANT ALL PRIVILEGES ON asterisk.* TO 'asterisk'@'localhost' IDENTIFIED BY 'yourpassword' WITH GRANT OPTION;

A table for your sip users and peers:

CREATE TABLE `asterisk`.`sip_buddies` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(80) NOT NULL default '',
`accountcode` varchar(20) default NULL,
`amaflags` varchar(7) default NULL,
`callgroup` varchar(10) default NULL,
`callerid` varchar(80) default NULL,
`canreinvite` char(3) default 'yes',
`context` varchar(80) default NULL,
`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 default '',
`insecure` varchar(20) default NULL,
`language` char(2) default NULL,
`mailbox` varchar(50) 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 default '',
`qualify` char(3) default NULL,
`restrictcid` char(1) default NULL,
`rtptimeout` char(3) default NULL,
`rtpholdtimeout` char(3) default NULL,
`secret` varchar(80) default NULL,
`type` varchar(6) NOT NULL default 'friend',
`username` varchar(80) NOT NULL default '',
`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 default '',
`regexten` varchar(80) NOT NULL default '',
`cancallforward` char(3) default 'yes',
PRIMARY KEY  (`id`),
UNIQUE KEY `name` (`name`),
KEY `name_2` (`name`)
) ENGINE=MyISAM;

And a table for extensions – the backbone of Asterisk:

CREATE TABLE `asterisk`.`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;

And a table for the voicemail users:

CREATE TABLE `asterisk`.`voicemessages` (
`id` int(11) NOT NULL auto_increment,
`msgnum` int(11) NOT NULL default '0',
`dir` varchar(80) default '',
`context` varchar(80) default '',
`macrocontext` varchar(80) default '',
`callerid` varchar(40) default '',
`origtime` varchar(40) default '',
`duration` varchar(20) default '',
`mailboxuser` varchar(80) default '',
`mailboxcontext` varchar(80) default '',
`recording` longblob,
PRIMARY KEY  (`id`),
KEY `dir` (`dir`)
) ENGINE=MyISAM;

And the table for queues:

CREATE TABLE `asterisk`.`queues` (
  `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_type` varchar(50) NOT 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` varchar(4) default NULL,
  `eventwhencalled` varchar(4) 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,
  PRIMARY KEY  (`name`)
) ENGINE=MyISAM

And the queue members:

CREATE TABLE `asterisk`.`queue_members` (
`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` tinyint(1) default NULL,
PRIMARY KEY  (`uniqueid`),
UNIQUE KEY `queue_interface` (`queue_name`,`interface`)
) ENGINE=MyISAM

So now we need to make the needed configuration changes so Asterisk knows to use the database. Assuming that you followed my installation guide (or that you have asterisk-addons installed) we need to copy the res_mysql.conf file, so:

cd /usr/src/asterisk-addons/configs/
cp res_mysql.conf.sample /etc/asterisk/res_mysql.conf

And we can edit that new /etc/asterisk/res_mysql.conf file to show your database settings:

[general]
dbhost = localhost
dbname = asterisk
dbuser = asterisk
dbpass = yourpassword
dbport = 3306
dbsock = /var/lib/mysql/mysql.sock

Next we’ll edit the /etc/asterisk/extconfig.conf to show as follows:

[settings]
sipusers => mysql,asterisk,sip_buddies
sippeers => mysql,asterisk,sip_buddies
extensions => mysql,asterisk,extensions
voicemail => mysql,asterisk,voicemessages
queues => mysql,asterisk,queues
queue_members => mysql,asterisk,queue_members

For extensions to be loaded of the database, we need to edit the /etc/asterisk/extensions.conf file as well. Each context that will be getting their information from the database, will need the following: switch => Realtime/@extensions – For example:

[incoming]

switch => Realtime/@extensions

Restart Asterisk and you’re all set. Now you just need to load your data into the database. Let’s have a look at an example of that. So here’s my extension:

exten => 105,1,Dial(SIP/105)

Here’s my database insert for the above extension:

INSERT into extensions (id, context, exten, priority, app, appdata)

VALUES ('','incoming','105','1','Dial','SIP/105');

And for an example of my sip user, I had the following in my sip.conf at one time:

[105]
type=friend
context=internal
callerid=105
host=dynamic
secret=password
canreinvite=no
insecure=port,invite
allow=all
nat=yes

Here is my insert code for the database:

INSERT into sip_buddies (id, name, callerid, context, canreinvite, insecure, type, host, secret, allow, nat)

VALUES ('','105','Robert','incoming','no','port,invite','friend','dynamic','bob123','all','yes');

I hope this example works well for you. I got it working fairly easy. It is important to note, that I haven’t been able to successfully setup the connection I have with my carrier in the realtime database. So I have to define those in my sip.conf or iax.conf – which isn’t a big deal. Another note is that any general conf settings, I always set those within the conf file, instead of within the realtime database.