MySQL

Asterisk umożliwia przeniesienie części konfiguracji z plików
do bazy danych np. MySQL lub PostgreSQL. Taki tryb pracy Asterisk-a
nazywa się Realtime. Gdyż zmiany na bazie danych powodują automatyczną
zmianę pracy konfiguracji. Gdyby edytować dialplan w pliku, należałoby 
ponownie załadować konfigurację komendą:

root# asterisk -rx 'dialplan reload'

Zmiany w sip.conf:

root# asterisk -rx 'sip reload'

Tak wystarczy UPDATE na odpowiedniej kolumnie i Asterisk będzie działał
z aktualną konfiguracją.

Do bazy danych można między innymi przenieść:

- skrzynki poczty głosowej
- dialplan
- sip.conf
- kolejki i konsultantów (members)
- CDR-y - rejestr połączeń

Poniższy przykład jak tego dokonać będzie dotyczył silnika MySQL.
Należy zainstalować Asterisk zgodnie z procesem przedstawionym w zakładce instalacja.
Należy zainstalować devele MySQL oraz Asterisk Add-ons.

Tworzymy domyślną bazę danych:



##
##Database: `asterisk`
##
CREATE DATABASE `asterisk` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `asterisk`;

##########################################################

##
##Table structure for table `cdr`
##

CREATE TABLE IF NOT EXISTS `cdr` (
 `recid` mediumint(8) unsigned NOT NULL auto_increment COMMENT 'Record ID',
 `calldate` datetime NOT NULL default '0000-00-00 00:00:00',
 `clid` varchar(80) NOT NULL default '',
 `src` varchar(80) NOT NULL default '',
 `dst` varchar(80) NOT NULL default '',
 `dcontext` varchar(80) NOT NULL default '',
 `channel` varchar(80) NOT NULL default '',
 `dstchannel` varchar(80) NOT NULL default '',
 `lastapp` varchar(80) NOT NULL default '',
 `lastdata` varchar(80) NOT NULL default '',
 `duration` int(11) NOT NULL default '0',
 `billsec` int(11) NOT NULL default '0',
 `disposition` varchar(45) NOT NULL default '',
 `amaflags` int(11) NOT NULL default '0',
 `accountcode` varchar(20) NOT NULL default '',
 `uniqueid` varchar(32) NOT NULL default '',
 `userfield` varchar(255) NOT NULL default '',
 PRIMARY KEY  (`recid`),
 KEY `calldate` (`calldate`),
 KEY `dst` (`dst`),
 KEY `accountcode` (`accountcode`),
 KEY `src` (`src`),
 KEY `disposition` (`disposition`),
 KEY `uniqueid` (`uniqueid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

##########################################################

##
##Table structure for table `queue_members`
##

CREATE TABLE IF NOT EXISTS `queue_members` (
  `queue_name` varchar(128) NOT NULL default '',
  `interface` varchar(128) NOT NULL default '',
  `penalty` int(11) default NULL,
  `uniqueid` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY  (`queue_name`,`interface`,`uniqueid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

##########################################################

##
##Table structure for table `queues`
##

CREATE TABLE IF NOT EXISTS `queues` (
  `name` varchar(128) NOT NULL,
  `musiconhold` varchar(128) default 'default',
  `announce` varchar(128) default NULL,
  `context` varchar(128) default NULL,
  `timeout` int(11) default '60',
  `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,
  `periodic_announce` varchar(128) default NULL,
  `periodic_announce_frequency` int(11) default NULL,
  `retry` int(11) default '5',
  `ringinuse` varchar(5) NOT NULL default 'no',
  `autofill` varchar(5) NOT NULL default 'yes',
  `autopause` varchar(5) NOT NULL default 'no',
  `setinterfacevar` varchar(5) NOT NULL default 'yes',
  `wrapuptime` int(11) default '30',
  `maxlen` int(11) default NULL,
  `servicelevel` int(11) default NULL,
  `strategy` varchar(128) default 'ringall',
  `joinempty` varchar(128) default 'no',
  `leavewhenempty` varchar(128) default 'yes',
  `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,
  PRIMARY KEY  (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

##########################################################

##
##Table structure for table `sip`
##

CREATE TABLE IF NOT EXISTS `sip` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(80) NOT NULL DEFAULT '',
 `context` varchar(80) DEFAULT NULL,
 `callingpres` enum('allowed_not_screened','allowed_passed_screen','allowed_failed_screen',
	'allowed','prohib_not_screened','prohib_passed_screen','prohib_failed_screen','prohib',
	'unavailable') DEFAULT 'allowed_not_screened',
 `deny` varchar(95) DEFAULT NULL,
 `permit` varchar(95) DEFAULT NULL,
 `secret` varchar(80) DEFAULT NULL,
 `md5secret` varchar(80) DEFAULT NULL,
 `remotesecret` varchar(250) DEFAULT NULL,
 `transport` enum('tcp','udp','tcp,udp') DEFAULT NULL,
 `host` varchar(31) NOT NULL DEFAULT '',
 `nat` varchar(5) NOT NULL DEFAULT 'no',
 `type` enum('user','peer','friend') NOT NULL DEFAULT 'friend',
 `accountcode` varchar(20) DEFAULT NULL,
 `amaflags` varchar(13) DEFAULT NULL,
 `callgroup` varchar(10) DEFAULT NULL,
 `callerid` varchar(80) DEFAULT NULL,
 `defaultip` varchar(15) DEFAULT NULL,
 `dtmfmode` varchar(7) DEFAULT NULL,
 `fromuser` varchar(80) DEFAULT NULL,
 `fromdomain` varchar(80) DEFAULT NULL,
 `insecure` varchar(4) DEFAULT NULL,
 `language` char(2) DEFAULT NULL,
 `mailbox` varchar(50) DEFAULT NULL,
 `pickupgroup` varchar(10) DEFAULT NULL,
 `qualify` char(3) DEFAULT NULL,
 `regexten` varchar(80) DEFAULT NULL,
 `rtptimeout` char(3) DEFAULT NULL,
 `rtpholdtimeout` char(3) DEFAULT NULL,
 `setvar` varchar(100) DEFAULT NULL,
 `disallow` varchar(100) DEFAULT 'all',
 `allow` varchar(100) DEFAULT 'g729;ilbc;gsm;ulaw;alaw',
 `fullcontact` varchar(80) NOT NULL DEFAULT '',
 `ipaddr` varchar(15) NOT NULL DEFAULT '',
 `port` mediumint(5) unsigned NOT NULL DEFAULT '0',
 `username` varchar(80) NOT NULL DEFAULT '',
 `defaultuser` varchar(80) NOT NULL DEFAULT '',
 `subscribecontext` varchar(80) DEFAULT NULL,
 `directmedia` enum('yes','no') DEFAULT NULL,
 `trustrpid` enum('yes','no') DEFAULT NULL,
 `sendrpid` enum('yes','no') DEFAULT NULL,
 `progressinband` enum('never','yes','no') DEFAULT NULL,
 `promiscredir` enum('yes','no') DEFAULT NULL,
 `useclientcode` enum('yes','no') DEFAULT NULL,
 `callcounter` enum('yes','no') DEFAULT NULL,
 `busylevel` int(10) unsigned DEFAULT NULL,
 `allowoverlap` enum('yes','no') DEFAULT 'yes',
 `allowsubscribe` enum('yes','no') DEFAULT 'yes',
 `allowtransfer` enum('yes','no') DEFAULT 'yes',
 `ignoresdpversion` enum('yes','no') DEFAULT 'no',
 `template` varchar(100) DEFAULT NULL,
 `videosupport` enum('yes','no','always') DEFAULT 'no',
 `maxcallbitrate` int(10) unsigned DEFAULT NULL,
 `rfc2833compensate` enum('yes','no') DEFAULT 'yes',
 `session-timers` enum('originate','accept','refuse') DEFAULT 'accept',
 `session-expires` int(5) unsigned DEFAULT '1800',
 `session-minse` int(5) unsigned DEFAULT '90',
 `session-refresher` enum('uac','uas') DEFAULT 'uas',
 `t38pt_usertpsource` enum('yes','no') DEFAULT NULL,
 `outboundproxy` varchar(250) DEFAULT NULL,
 `callbackextension` varchar(250) DEFAULT NULL,
 `registertrying` enum('yes','no') DEFAULT 'yes',
 `timert1` int(5) unsigned DEFAULT '500',
 `timerb` int(8) unsigned DEFAULT NULL,
 `qualifyfreq` int(5) unsigned DEFAULT '120',
 `contactpermit` varchar(250) DEFAULT NULL,
 `contactdeny` varchar(250) DEFAULT NULL,
 `lastms` int(11) NOT NULL,
 `regserver` varchar(100) NOT NULL DEFAULT '',
 `regseconds` int(11) NOT NULL DEFAULT '0',
 `useragent` varchar(50) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`),
 UNIQUE KEY `name` (`name`),
 KEY `name_2` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC ; 

##########################################################

#
# Table structure for table `extensions_table`
#

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`)
) TYPE=MyISAM;

##########################################################

##
##Table structure for table `voicemail_messages`
##

CREATE TABLE IF NOT EXISTS `voicemail_messages` (
  `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  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1088 ;

##########################################################

#
# Table structure for table `meetme`
#

CREATE TABLE `meetme` (
  `confno` char(80) NOT NULL default '0',
  `starttime` datetime NOT NULL default '0000-00-00 00:00:00',
  `endtime` datetime NOT NULL default '2099-12-31 23:59:59' ,
  `pin` char(20) default NULL,
  `opts` char(100) default NULL,
  `adminpin` char(20) default NULL,
  `adminopts` char(100) default NULL,
  `members` int(11) NOT NULL default '0',
  `maxusers` int(11) NOT NULL default '0',
  PRIMARY KEY  (`confno`,`starttime`)
);  

##########################################################

##
##Table structure for table `voicemail_users`
##

CREATE TABLE IF NOT EXISTS `voicemail_users` (
  `uniqueid` int(11) NOT NULL auto_increment,
  `customer_id` varchar(11) NOT NULL default '0',
  `context` varchar(50) NOT NULL default 'default',
  `mailbox` varchar(11) NOT NULL default '0',
  `password` varchar(5) NOT NULL default '6666',
  `fullname` varchar(150) NOT NULL,
  `email` varchar(50) NOT NULL,
  `pager` varchar(50) NOT NULL,
  `tz` varchar(10) NOT NULL default 'en',
  `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  DEFAULT CHARSET=latin1 AUTO_INCREMENT=101 ;

## ########################################################

##
##Create realtime user with approximately the right privs.
##

CREATE USER 'astrealtime'@'localhost' IDENTIFIED BY 'haslo_asterisk';
GRANT FILE ON * . * TO 'astrealtime'@'localhost' IDENTIFIED BY 'haslo_asterisk' 
	WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 
	MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
GRANT INSERT ON `asterisk`.`cdr` TO 'astrealtime'@'localhost';
GRANT SELECT , INSERT , UPDATE , DELETE ON `asterisk`.`queue_members` TO 'astrealtime'@'localhost';
GRANT SELECT , UPDATE ON `asterisk`.`queues` TO 'astrealtime'@'localhost';
GRANT SELECT , UPDATE ON `asterisk`.`sip` TO 'astrealtime'@'localhost';
GRANT SELECT , UPDATE ON `asterisk`.`voicemail_users` TO 'astrealtime'@'localhost';
GRANT SELECT , UPDATE , INSERT , DELETE ON `asterisk`.`voicemail_messages` TO 'astrealtime'@'localhost'; 
GRANT SELECT ON `asterisk`.`extensions` TO 'astrealtime'@'localhost';
GRANT SELECT , UPDATE ON `asterisk`.`meetme` TO 'astrealtime'@'localhost';



Tworzymy i edytujemy pliki z konfiguracją połączenia z bazą danych:

root# nano /etc/asterisk/cdr_mysql.conf

[global]
hostname=localhost
dbname=asterisk
table=cdr
password=haslo_asterisk
user=astrealtime
dbsock = /var/run/mysqld/mysqld.sock

root# nano /etc/asterisk/res_mysql.conf

[general]
dbhost=localhost
dbname=asterisk
dbuser=astrealtime
dbpass=haslo_asterisk
dbsock = /var/run/mysqld/mysqld.sock

root# nano /etc/asterisk/extconfig.conf

[settings]
sipusers => mysql,general,sip
sippeers => mysql,general,sip
extensions => mysql,general,extensions 
queues => mysql,general,queues
queue_members => mysql,general,queue_members
meetme => mysql,general,meetme
voicemail => mysql,general,voicemail_users

Po restarcie asteriska:

root# asterisk -rx 'core restart now'

możemy sprawdzić, czy mamy połączenie z bazą danych:

asterisk*CLI> cdr mysql status
Connected to asterisk on socket file default using table cdr for 4 minutes, 0 seconds.
  Wrote 0 records since last restart.

asterisk*CLI> realtime mysql status
general connected to asterisk@localhost, port 3306 with username astrealtime for 4 minutes.


Jak to działa ?

Dodajemy użytkownika:

mysql> insert into sip (`name`, `context`, `secret`, `host`, `nat`, `type`, `qualify`, `username`,
 `defaultuser`, `allow`, `disallow`) values ('andrzej_100','polaczenia','123456','dynamic','yes',
'friend','yes','andrzej_100','andrzej_100','alaw','all');
Query OK, 1 row affected, 1 warning (0.00 sec)

Dodajemy kolejkę:

mysql> insert into queues (`name`) values ('sekretariat');
Query OK, 1 row affected (0.00 sec)

Dodajemy konsultanta do kolejki:

mysql> insert into queue_members values ('sekretariat','SIP/andrzej_100','1','1');
Query OK, 1 row affected (0.00 sec)

Dodajemy obsługę połączenia:

mysql> insert into extensions (`context`, `exten`, `priority`, `app`, `appdata`) 
values ('polaczenia', '2000', '1', 'Queue', 'sekretariat');
Query OK, 1 row affected (0.00 sec)

Modyfikujemy pliki:

root# nano /etc/asterisk/sip.conf

Odkomentowujemy wpisy:

rtcachefriends=yes             ; Cache realtime friends by adding them to the internal list
                                ; just like friends added from the config file only on a
                                ; as-needed basis? (yes|no)

rtsavesysname=yes              ; Save systemname in realtime database at registration
                                ; Default= no

rtupdate=yes                   ; Send registry updates to database using realtime? (yes|no)

Edytujemy plik:

root# nano /etc/asterisk/extensions.conf

Dodajemy wpis:

[polaczenia]
switch => Realtime

Edytujemy plik:

root# nano /etc/asterisk/queues.conf

Dodajemy wpis:

[general]
realtime_family = queues, queue_members

Przeładowujemy konfiguracje:

root# asterisk -rx 'reload'

Rejetrujemy SIP-a i sprawdzamy rezultaty:

asterisk*CLI> queue show sekretariat
sekretariat has 0 calls (max unlimited) in 'ringall' strategy (0s holdtime, 0s talktime), W:0, C:0, A:0, SL:0.0% within 0s
   Members:
      SIP/andrzej_100 with penalty 1 (realtime) (Not in use) has taken no calls yet
   No Callers

asterisk*CLI> dialplan show polaczenia
[ Context 'polaczenia' created by 'pbx_config' ]
  Alt. Switch =>    'Realtime/'                                   [pbx_config]

asterisk*CLI> sip show peer andrzej_100

  * Name       : andrzej_100
  Realtime peer: Yes, cached

Teraz możemy przetestować dialplan z REALTIME wybierając 2000:

 == Using SIP RTP CoS mark 5
    -- Executing Queue("SIP/andrzej_100-00000000", "sekretariat")
    -- Started music on hold, class 'default', on SIP/andrzej_100-00000000
  == Using SIP RTP CoS mark 5
    -- SIP/andrzej_100-00000001 is ringing
    -- SIP/andrzej_100-00000001 answered SIP/andrzej_100-00000000
    -- Stopped music on hold on SIP/andrzej_100-00000000
    -- Packet2Packet bridging SIP/andrzej_100-00000000 and SIP/andrzej_100-00000001
    -- Started music on hold, class 'default', on SIP/andrzej_100-00000001

Widać, że dzwonimy sami do siebie, a dialplan jest zrealizowany na podstawie bazy.
W taki sam sposób dodajemy inne spisy do dialplanu oraz definiujemy usługę poczty głosowej itp.