Asterisk RealTime database Architecture
Terminology/Files
Driver – A compiled module containing database specific code that accepts the generalized function calls that RealTime makes. As of this writing, only ODBC, MySQL (via asterisk-addons) and LDAP (see http://free.oxymium.net/Asterisk/ and http://bugs.digium.com/view.php?id=5768) drivers are available.
Family – A name associated with a RealTime call. Examples: sippeers, sipusers, voicemail.
extconfig.conf – The configuration file that contains the information necessary to bind specific families to specific drivers.
res_odbc.conf – The configuration file for ODBC RealTime.
res_mysql.conf – The configuration file for MySQL RealTime.
res_ldap.conf – The configuration file for LDAP RealTime.
ODBC – Open DataBase Connectivity
MySQL – the world’s most popular open source database
OpenLDAP – Open source implementation of the Lightweight Directory Access Protocol
There are 2 methods of using RealTime: ODBC and MySQL. Yes, you can use ODBC to connect to MySQL and many other ODBC supported databases. (Being an avid MySQL user and advocate, I didn’t want to bother with ODBC so I wrote the RealTime MySQL driver over the weekend.)
Extconfig – RealTime
RealTime configuration is where configuration values are read/updated in real time.
Example: Lets say you have 2 SIP users defined in your sip.conf and you want to add a 3rd. You add them to the file then execute the command ‘sip reload’. This re-reads your sip.conf and allows the 3rd to register.
With RealTime, all you do is add 1 new record to the table that sipusers has been bound to. No reloading necessary.
RealTime maps take the following fomat:
[settings]
<family name> => <driver>,<database name>~np~[~/np~,table_name~np~]~/np~
sippeers => mysql,asterisk,sip_peers
sipusers => mysql,asterisk,sip_users
queues => mysql,asterisk,queue_table
queue_members => mysql,asterisk,queue_member_table
meetme => mysql,asterisk,meetme_table
voicemail => mysql,asterisk^
Above we have four examples. The first example will bind the family name “sippeers” to the table “sip_peers” in the database “asterisk” using the MySQL driver. The last example will bind the family name “voicemail” to the table “voicemail” (because we ommited the table name, it defaults to the family name) in the database “test” using the MySQL driver.
It is worth noting that sipusers and sippeers may both refer to the same table, if you wish.
NOTE: extconfig.conf is parsed each time you connect to the asterisk CLI.
/etc/asterisk/cdr_mysql.conf
[global]
hostname=localhost
dbname=asterisk
table=cdr
password=asterisk123
user=asterisk
;password=root
;user=root
port=3306
;sock=/var/lib/mysql/mysql.sock
sock=/var/run/mysqld/mysqld.sock
;sock=/tmp/mysqld.sock
userfield=1
/etc/mysql/my.cnf
socket = /var/run/mysqld/mysqld.sock
#
# Table structure for table `sipfriends`
#
CREATE TABLE IF NOT EXISTS `sipfriends` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
`ipaddr` varchar(15) DEFAULT NULL,
`port` int(5) DEFAULT NULL,
`regseconds` int(11) DEFAULT NULL,
`defaultuser` varchar(10) DEFAULT NULL,
`fullcontact` varchar(35) DEFAULT NULL,
`regserver` varchar(20) DEFAULT NULL,
`useragent` varchar(20) DEFAULT NULL,
`lastms` int(11) DEFAULT NULL,
`host` varchar(40) DEFAULT NULL,
`type` enum(‘friend’,’user’,’peer’) DEFAULT NULL,
`context` varchar(40) DEFAULT NULL,
`permit` varchar(40) DEFAULT NULL,
`deny` varchar(40) DEFAULT NULL,
`secret` varchar(40) DEFAULT NULL,
`md5secret` varchar(40) DEFAULT NULL,
`remotesecret` varchar(40) DEFAULT NULL,
`transport` enum(‘udp’,’tcp’,’udp,tcp’,’tcp,udp’) DEFAULT NULL,
`dtmfmode` enum(‘rfc2833′,’info’,’shortinfo’,’inband’,’auto’) DEFAULT NULL,
`directmedia` enum(‘yes’,’no’,’nonat’,’update’) DEFAULT NULL,
`nat` enum(‘yes’,’no’,’never’,’route’) DEFAULT NULL,
`callgroup` varchar(40) DEFAULT NULL,
`pickupgroup` varchar(40) DEFAULT NULL,
`language` varchar(40) DEFAULT NULL,
`allow` varchar(40) DEFAULT NULL,
`disallow` varchar(40) DEFAULT NULL,
`insecure` varchar(40) DEFAULT NULL,
`trustrpid` enum(‘yes’,’no’) DEFAULT NULL,
`progressinband` enum(‘yes’,’no’,’never’) DEFAULT NULL,
`promiscredir` enum(‘yes’,’no’) DEFAULT NULL,
`useclientcode` enum(‘yes’,’no’) DEFAULT NULL,
`accountcode` varchar(40) DEFAULT NULL,
`setvar` varchar(40) DEFAULT NULL,
`callerid` varchar(40) DEFAULT NULL,
`amaflags` varchar(40) DEFAULT NULL,
`callcounter` enum(‘yes’,’no’) DEFAULT NULL,
`busylevel` int(11) DEFAULT NULL,
`allowoverlap` enum(‘yes’,’no’) DEFAULT NULL,
`allowsubscribe` enum(‘yes’,’no’) DEFAULT NULL,
`videosupport` enum(‘yes’,’no’) DEFAULT NULL,
`maxcallbitrate` int(11) DEFAULT NULL,
`rfc2833compensate` enum(‘yes’,’no’) DEFAULT NULL,
`mailbox` varchar(40) DEFAULT NULL,
`session-timers` enum(‘accept’,’refuse’,’originate’) DEFAULT NULL,
`session-expires` int(11) DEFAULT NULL,
`session-minse` int(11) DEFAULT NULL,
`session-refresher` enum(‘uac’,’uas’) DEFAULT NULL,
`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` int(11) DEFAULT NULL,
`rtpholdtimeout` int(11) DEFAULT NULL,
`sendrpid` enum(‘yes’,’no’) DEFAULT NULL,
`outboundproxy` varchar(40) DEFAULT NULL,
`callbackextension` varchar(40) DEFAULT NULL,
`registertrying` enum(‘yes’,’no’) DEFAULT NULL,
`timert1` int(11) DEFAULT NULL,
`timerb` int(11) DEFAULT NULL,
`qualifyfreq` int(11) DEFAULT NULL,
`constantssrc` enum(‘yes’,’no’) DEFAULT NULL,
`contactpermit` varchar(40) DEFAULT NULL,
`contactdeny` varchar(40) DEFAULT NULL,
`usereqphone` enum(‘yes’,’no’) DEFAULT NULL,
`textsupport` enum(‘yes’,’no’) DEFAULT NULL,
`faxdetect` enum(‘yes’,’no’) DEFAULT NULL,
`buggymwi` enum(‘yes’,’no’) DEFAULT NULL,
`auth` varchar(40) DEFAULT NULL,
`fullname` varchar(40) DEFAULT NULL,
`trunkname` 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,
`mohinterpret` varchar(40) DEFAULT NULL,
`mohsuggest` varchar(40) DEFAULT NULL,
`parkinglot` varchar(40) DEFAULT NULL,
`hasvoicemail` enum(‘yes’,’no’) DEFAULT NULL,
`subscribemwi` enum(‘yes’,’no’) DEFAULT NULL,
`vmexten` varchar(40) DEFAULT NULL,
`autoframing` enum(‘yes’,’no’) DEFAULT NULL,
`rtpkeepalive` int(11) DEFAULT NULL,
`call-limit` int(11) DEFAULT NULL,
`g726nonstandard` enum(‘yes’,’no’) DEFAULT NULL,
`ignoresdpversion` enum(‘yes’,’no’) DEFAULT NULL,
`allowtransfer` enum(‘yes’,’no’) DEFAULT NULL,
`dynamic` enum(‘yes’,’no’) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
KEY `ipaddr` (`ipaddr`,`port`),
KEY `host` (`host`,`port`)
) ENGINE=MyISAM;