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.

2 thoughts on “Asterisk RealTime database Architecture

  • May 22, 2014 at 6:37 am
    Permalink

    /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

  • September 20, 2014 at 2:12 pm
    Permalink

    #
    # 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;

Leave a Reply