http://www.smashingmagazine.com/2008/12/18/8-useful-wordpress-sql-hacks/
Posted under MySql, Programming
This post was written by Chirag on December 28, 2008
Asterisk real-time DB with Trixbox
mysql> show columns in extensions in asteriskrealtime;
+———-+————–+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+———-+————–+——+—–+———+—————-+
| id | int(11) | NO | MUL | NULL | auto_increment |
| context | varchar(20) | NO | PRI | NULL | |
| exten | varchar(20) | NO | PRI | NULL | |
| priority | tinyint(4) | NO | PRI | 0 | |
| app | varchar(20) | NO | | NULL | |
| appdata | varchar(128) | NO | | NULL | |
+———-+————–+——+—–+———+—————-+
mysql> show columns in iax_buddies in asteriskrealtime;
+————-+————–+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+————-+————–+——+—–+———+——-+
| name | varchar(30) | NO | PRI | NULL | |
| username | varchar(30) | YES | UNI | NULL | |
| type | varchar(6) | NO | | NULL | |
| secret | varchar(50) | YES | | NULL | |
| md5secret | varchar(32) | YES | | NULL | |
| dbsecret | varchar(100) | YES | | NULL | |
| notransfer | varchar(10) | YES | | NULL | |
| inkeys | varchar(100) | YES | | NULL | |
| auth | varchar(100) | YES | | NULL | |
| accountcode | varchar(100) | YES | | NULL | |
| amaflags | varchar(100) | YES | | NULL | |
| callerid | varchar(100) | YES | | NULL | |
| context | varchar(100) | YES | | NULL | |
| defaultip | varchar(15) | YES | | NULL | |
| host | varchar(31) | NO | | dynamic | |
| language | varchar(5) | YES | | NULL | |
| mailbox | varchar(50) | YES | | NULL | |
| deny | varchar(95) | YES | | NULL | |
| permit | varchar(95) | YES | | NULL | |
| qualify | varchar(4) | YES | | NULL | |
| disallow | varchar(100) | YES | | NULL | |
| allow | varchar(100) | YES | | NULL | |
| ipaddr | varchar(15) | YES | | NULL | |
| port | int(11) | YES | | 0 | |
| regseconds | int(11) | YES | | 0 | |
+————-+————–+——+—–+———+——-+
mysql> show columns in queue_member_table in asteriskrealtime;
+————+————–+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+————+————–+——+—–+———+——-+
| queue_name | varchar(128) | NO | PRI | NULL | |
| interface | varchar(128) | NO | PRI | NULL | |
| penalty | int(11) | YES | | NULL | |
+————+————–+——+—–+———+——-+
mysql> show columns in queue_table in asteriskrealtime;
+————————+————–+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+————————+————–+——+—–+———+——-+
| name | varchar(128) | NO | PRI | NULL | |
| musiconhold | varchar(128) | YES | | NULL | |
| announce | varchar(128) | YES | | NULL | |
| context | varchar(128) | YES | | NULL | |
| timeout | int(11) | YES | | NULL | |
| monitor_join | tinyint(1) | YES | | NULL | |
| monitor_format | varchar(128) | YES | | NULL | |
| queue_youarenext | varchar(128) | YES | | NULL | |
| queue_thereare | varchar(128) | YES | | NULL | |
| queue_callswaiting | varchar(128) | YES | | NULL | |
| queue_holdtime | varchar(128) | YES | | NULL | |
| queue_minutes | varchar(128) | YES | | NULL | |
| queue_seconds | varchar(128) | YES | | NULL | |
| queue_lessthan | varchar(128) | YES | | NULL | |
| queue_thankyou | varchar(128) | YES | | NULL | |
| queue_reporthold | varchar(128) | YES | | NULL | |
| announce_frequency | int(11) | YES | | NULL | |
| announce_round_seconds | int(11) | YES | | NULL | |
| announce_holdtime | varchar(128) | YES | | NULL | |
| retry | int(11) | YES | | NULL | |
| wrapuptime | int(11) | YES | | NULL | |
| maxlen | int(11) | YES | | NULL | |
| servicelevel | int(11) | YES | | NULL | |
| strategy | varchar(128) | YES | | NULL | |
| joinempty | varchar(128) | YES | | NULL | |
| leavewhenempty | varchar(128) | YES | | NULL | |
| eventmemberstatus | tinyint(1) | YES | | NULL | |
| eventwhencalled | tinyint(1) | YES | | NULL | |
| reportholdtime | tinyint(1) | YES | | NULL | |
| memberdelay | int(11) | YES | | NULL | |
| weight | int(11) | YES | | NULL | |
| timeoutrestart | tinyint(1) | YES | | NULL | |
+————————+————–+——+—–+———+——-+
mysql> show columns in sip_buddies in asteriskrealtime;
+—————-+————–+——+—–+————————-+—————-+
| Field | Type | Null | Key | Default | Extra |
+—————-+————–+——+—–+————————-+—————-+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(80) | NO | UNI | NULL | |
| accountcode | varchar(20) | YES | | NULL | |
| amaflags | varchar(7) | YES | | NULL | |
| callgroup | varchar(10) | YES | | NULL | |
| callerid | varchar(80) | YES | | NULL | |
| canreinvite | char(3) | YES | | yes | |
| context | varchar(80) | YES | | NULL | |
| defaultip | varchar(15) | YES | | NULL | |
| dtmfmode | varchar(7) | YES | | NULL | |
| fromuser | varchar(80) | YES | | NULL | |
| fromdomain | varchar(80) | YES | | NULL | |
| fullcontact | varchar(80) | YES | | NULL | |
| host | varchar(31) | NO | | NULL | |
| insecure | varchar(4) | YES | | NULL | |
| language | char(2) | YES | | NULL | |
| mailbox | varchar(50) | YES | | NULL | |
| md5secret | varchar(80) | YES | | NULL | |
| nat | varchar(5) | NO | | no | |
| deny | varchar(95) | YES | | NULL | |
| permit | varchar(95) | YES | | NULL | |
| mask | varchar(95) | YES | | NULL | |
| pickupgroup | varchar(10) | YES | | NULL | |
| port | varchar(5) | NO | | NULL | |
| qualify | char(3) | YES | | NULL | |
| restrictcid | char(1) | YES | | NULL | |
| rtptimeout | char(3) | YES | | NULL | |
| rtpholdtimeout | char(3) | YES | | NULL | |
| secret | varchar(80) | YES | | NULL | |
| type | varchar(6) | NO | | friend | |
| username | varchar(80) | NO | | NULL | |
| disallow | varchar(100) | YES | | all | |
| allow | varchar(100) | YES | | g729;ilbc;gsm;ulaw;alaw | |
| musiconhold | varchar(100) | YES | | NULL | |
| regseconds | int(11) | NO | | 0 | |
| ipaddr | varchar(15) | NO | | NULL | |
| regexten | varchar(80) | NO | | NULL | |
| cancallforward | char(3) | YES | | yes | |
+—————-+————–+——+—–+————————-+—————-+
mysql> show columns in voicemail_users in asteriskrealtime;
+————-+————-+——+—–+——————-+—————-+
| Field | Type | Null | Key | Default | Extra |
+————-+————-+——+—–+——————-+—————-+
| uniqueid | int(11) | NO | MUL | NULL | auto_increment |
| customer_id | int(11) | NO | | 0 | |
| context | varchar(50) | NO | | NULL | |
| mailbox | int(5) | NO | PRI | 0 | |
| password | varchar(4) | NO | | 0 | |
| fullname | varchar(50) | NO | | NULL | |
| email | varchar(50) | NO | | NULL | |
| pager | varchar(50) | NO | | NULL | |
| stamp | timestamp | YES | | CURRENT_TIMESTAMP | |
| attach | char(3) | NO | | no | |
| saycid | char(3) | NO | | yes | |
| hidefromdir | char(3) | NO | | no | |
+————-+————-+——+—–+——————-+—————-+
Posted under Asterisk, Linux, MySql, Trixbox
This post was written by Chirag on July 30, 2008
Asterisk database on MySql
Core System :
Network Manager :
This would take care of system IP configuration in a DHCP network . This could be made static from gui , so that end users dont have to connect everytime they want to login . Share Sense :
The system advertises to the gui software to connect to the pbx box which could be authenticated with a standard password. Also option to change the password Call Routing Trunks Incoming :PSTN
Welcome Window navigated to connect the PSTN jack to connect to the PORT 1 of the PBX and ask for callerid . This would repeat , the wizard for other ports and stop if we need to . These PORT 1 … n will have standard configuration.
VOIP :
Followed by the pstn , Voip trunk wizard runs to configure the incoming trunks and outbound .
Extensions and Groups :
Wizard for creating the extensions specific to a person and also added to group in case need. This would have VM enabled with the user defined email id .
Call Routing \ Dial plan
Based on the VOIP & PSTN , we will have the outbound dial rules like 1XXXXXXXXX is routed thru PSTN and [2-9]X. is routed thry voip trunk.
Call Attendant :
Here the incoming trunks are handled based on the trunks (PSTN & voip) created ..
Endpoint Manager:
This will configure the GXP phones with the settings once added in the network.
Description of the CDR table in asteriskcdr :
`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 ”,
`userfield` varchar(255) NOT NULL default ”
1. what is the difference between clid and src?
if my ext is 601 which is the src , my clid could be < Kias> or <601>
2. what is dcontext?
Destination context: every incoming call has a context like IVR , Ring groups , Call forwarding
3. possible values of lastapp and lastdata?
lastapp could be VM for eg , lastdata could be some DTMF recd from the callee (1,2 …)
4. what is the difference between duration and billsec?
Duration is with ring time and billsec is actual conversation period
5. significance of disposition/amaflags/accountcode/userfield?
dispostion could be Answered , No -Answer , hangup , Busy , Rejected etc
Accountcode , Amaflags,userfield are related to A2billing
RealTime support is currently available for the following families:
sippeers
sipusers
iaxpeers
iaxusers
voicemail
queues and queue_members (used together for the Queue application).
extensions
tables sip_buddies & extensions_table are created in asterisk db — ???
1. User id should mention Tehcnology ( SIP /IAX )
2. Zap trunks and Incoming preferences
3. Separate tab for Inbound Trunks ( SIp, Iax ) with host name , username , secret ,codecs and context . Preferences will take the incoming parameters
4. Outbound trunks : Dial plan specific wherein like 00 prefix will take SIP trunk1
& pattern NXXXXXXXX will take Zap 1
e.g. INSERT INTO `extensions` (`id`, `context`, `exten`, `priority`, `app`, `appdata`)
VALUES (5, ‘cytel’, ‘8322008630′, ‘1′, ‘Dial’, ‘SIP/3044,30′);
INSERT into sip_buddies (id, name, callerid, context, canreinvite, insecure, type, host, secret, allow, nat) VALUES (”,’1001′,’JD’,'incoming’,'no’,'port,invite’,'friend’,'dynamic’,'bob123′,’all’,'yes’);
Below is the structure for sip ext , iax ext & extension rules table
Table structure for table `sip_buddies`
CREATE TABLE `sip_buddies` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(80) NOT NULL default ”,
`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,
`call-limit` smallint(5) unsigned default NULL,
`callgroup` varchar(10) default NULL,
`callerid` varchar(80) default NULL,
`cancallforward` char(3) default ‘yes’,
`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,
`insecure` varchar(4) default NULL,
`language` char(2) default NULL,
`mailbox` varchar(50) default NULL,
`md5secret` varchar(80) default NULL,
`deny` varchar(95) default NULL,
`permit` varchar(95) default NULL,
`mask` varchar(95) default NULL,
`musiconhold` varchar(100) default NULL,
`pickupgroup` varchar(10) default NULL,
`qualify` char(3) default NULL,
`regexten` varchar(80) default NULL,
`restrictcid` char(3) default NULL,
`rtptimeout` char(3) default NULL,
`rtpholdtimeout` char(3) default NULL,
`secret` varchar(80) 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` smallint(5) unsigned NOT NULL default ‘0′,
`regserver` varchar(100) default NULL,
`regseconds` int(11) NOT NULL default ‘0′,
`username` varchar(80) NOT NULL default ”,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
KEY `name_2` (`name`)
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
Table structure for table `iax_buddies`
CREATE TABLE iax_buddies (
name varchar(30) primary key NOT NULL,
username varchar(30),
type varchar(6) NOT NULL,
secret varchar(50),
md5secret varchar(32),
dbsecret varchar(100),
notransfer varchar(10),
inkeys varchar(100),
outkey varchar(100),
auth varchar(100),
accountcode varchar(100),
amaflags varchar(100),
callerid varchar(100),
context varchar(100),
defaultip varchar(15),
host varchar(31) NOT NULL default ‘dynamic’,
language char(5),
mailbox varchar(50),
deny varchar(95),
permit varchar(95),
equalify varchar(4),
disallow varchar(100),
allow varchar(100),
ipaddr varchar(15),
port integer default 0,
regseconds integer default 0
);
CREATE UNIQUE INDEX iax_buddies_username_idx ON iax_buddies(username); Table structure for table `extensions_table`
CREATE TABLE `extensions_table` (
`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;
Asterisk:
There are 2 Databases
1. Asteriskcdr
Tables : cdr , will handle the reports part of call handling and other call details
2. Asterisk
Tables: sip_buddies,extensions_table,voicemail_users
For eg we have a scenario like this , we need to add sip extensions and sip peers
Sip extensions are those which will be registered to the box as an extension and sip peer are those which are trunk to any providers like varphonex, call centric ,voxbone etc.
Peers can give Inbound/ outbound functionalities , some outbound alone . In case of incoming , we get DID from the providers which are added to our box as extensions.
Now I add an user - sip extension the possible values
`id` int(11) NOT NULL auto_increment, - Auto increment
`name` varchar(80) NOT NULL default ”, - It could be the person name eg Kias or Chirag … or any number too 1001 or 1002
`host` varchar(31) NOT NULL default ”, - Host will be dynamic , as we will be in a DHCP environment . This is for user extension alone ,in case of peer will have provider IP address or domain name
`nat` varchar(5) NOT NULL default ’no’, - We will have nat as yes since we are in NAT environment
`type` enum(’user’,'peer’,'friend’) NOT NULL default ’friend’, - default value for user is friend & for peer is peer ,in our case here is friend
`accountcode` varchar(20) default NULL, - NULL
`amaflags` varchar(13) default NULL, - NULL
`call-limit` smallint(5) unsigned default NULL, - we can make it as 10 default
`callgroup` varchar(10) default NULL, - suppose we have a group of users for hunting , we need to update the group number . We can standardize 500 … 600 as group numbers, it can be NULL by default
`callerid` varchar(80) default NULL, - any value eg 1001 or Kias
`cancallforward` char(3) default ’yes’, default will be yes
`canreinvite` char(3) default ’yes’, default will be yes
`context` varchar(80) default NULL, we can group all sip extension as sip-internal context , possible value is sip-internal
`defaultip` varchar(15) default NULL, - could be NULL
`dtmfmode` varchar(7) default NULL, rfc2833 will be default, we can include inband as choice
`fromuser` varchar(80) default NULL, will be NULL
`fromdomain` varchar(80) default NULL , will be NULL
`insecure` varchar(4) default NULL, NULL
`language` char(2) default NULL, , NULL
`mailbox` varchar(50) default NULL, suppose 1001 is the extension we will have 1001@default
`md5secret` varchar(80) default NULL, NULL
`deny` varchar(95) default NULL, NULL
`permit` varchar(95) default NULL, NULL
`mask` varchar(95) default NULL, NULL
`musiconhold` varchar(100) default NULL, NULL or default
`pickupgroup` varchar(10) default NULL, 1
`qualify` char(3) default NULL, yes
`regexten` varchar(80) default NULL,eg 1001 or the preferred extenion number
`restrictcid` char(3) default NULL, NULL
`rtptimeout` char(3) default NULL, NULL ( we define those directly )
`rtpholdtimeout` char(3) default NULL, NULL ( we define those directly )
`secret` varchar(80) default NULL, password for the softphone or IP phone eg 1234!#$
`setvar` varchar(100) default NULL, NULL
`disallow` varchar(100) default ’all’, all
`allow` varchar(100) default ’g729;ilbc;gsm;ulaw;alaw’, ulaw;g729
`fullcontact` varchar(80) NOT NULL default ”, ”
`ipaddr` varchar(15) NOT NULL default ”, ”
`port` smallint(5) unsigned NOT NULL default ’0′, 5060
`regserver` varchar(100) default NULL, IP address of our linux box
`regseconds` int(11) NOT NULL default ’0′, ”
`username` varchar(80) NOT NULL default ”, 1001
SIP:
host - dynamic 99% time
advance option: md5secret, account code, default, ip address, call group, pickup group, from domain, reg. server, qualify : yes/no
remove: RTP hold timeout, rtp timeout, nat: (must be yes), AMA flags, restrict callerid, set var, deny, permit, mask
allow: ulaw first
disallow: ‘ ‘ (SPACE) / all : if SPACE, select allow codecs, otherwise all
full contact: only readable field
music on hold: no / default
insecure: no / port,invite
port: default value 5060 (SIP), 4569 (IAX)
context: (readable) Kias will provide values
from user : same as user name, advance option
language: two char, ‘en’ etc
dtmf mode: rfc2833, inband, info, auto (drop down)
reg. extens: same as user (1001 e.g.)
reg. second: 3000ms
IAX:
same as sip
md5secret: regular screen required
allow, disallow, reg.seconds
port: 4569 (default)
remove: in keys, out keys
advance: - auth: plain/md5, no xfer : yes/no
exten:
id: auto-generate
exten: same as user name (sip/iax) e.g. 101
priority: 1
application: dial, playback, forward
application data: /101
call report:
filter: disposition, source, dest, bill sec,
move port and regseconds in the SIP user template to advanced options
I add a sip user 101 . in the preferences like settings he can choose different templates like
1. Plain
when someone dials 101 , it will ring his extension alone in this case
values in extension_table for id , exten, priority,app,app_data ( “”,101,1,Dial,SIP/101)
this would simply ring his /her extension
2. Voicemail
then we add 2 rules
(”",101,1,Dial,SIP/101|30)
(”",101,2.Voicemail,u101@default)
this rules would dial for 30 secs then take the caller to voicemail
3. Simulataneous ring 2 or more extens
(”",101,1,Dial,SIP/101&SIP/102 )
This will ring 2 more extensions .
Pl advise if we could create template like this which will update with the database directly.
———————————
This could be a standard IVR for the system .,pl check attached csv
the context explains
the call lands
Play the greetings . The Eapen15 file could be custom wav file saved thru tftp and we could have standard message also/
It waits for the exten to be dialed by the caller
if 1001 is pressed it dial the sip phone 1001
or if timeouts it goes to the extension 1001 - which needs user confirmation in our template
. Like which would be receptionist etc ..
if invalid dial repeats the message 2 times , else hangs up
——————————————————–
CREATE TABLE `voicemail_users` (
`uniqueid` int(11) NOT NULL auto_increment,
`customer_id` varchar(11) NOT NULL default ‘0′,
`context` varchar(50) NOT NULL default ”,
`mailbox` varchar(11) NOT NULL default ‘0′,
`password` varchar(5) NOT NULL default ‘0′,
`fullname` varchar(150) NOT NULL default ”,
`email` varchar(50) NOT NULL default ”,
`pager` varchar(50) NOT NULL default ”,
`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 default ”,
`callback` varchar(10) NOT NULL default ”,
`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 ;
Insert into voicemail_users (context,mailbox,password,review) values (default,4051,4444,yes)
———————————————————————-
All custom recorded files are to be stored in
/var/lib/asterisk/sounds/custom as gsm files
for i in *.mp3; do lame –decode $i `basename $i .mp3`.wav; done
for i in *.wav; do sox $i -r 8000 -v 0.2 -c 1 $(basename $i .wav).gsm resample -ql; done
Before uploading any file it has to be resampled in the above format .
In case of Music on Hold , the wav fiels has to be converted to mp3 format as follows:
/var/lib/asterisk/mohmp3/
$LAME -S -V7 -B24 –tt $OUT –add-id3v2 $OUT.wav $OUT.mp3
Posted under Asterisk, Linux, MySql, Trixbox
This post was written by Chirag on July 19, 2008
