Asterisk RealTime PostgreSQL
Created by: blankman,Last modification on Fri 06 of Jun, 2008 [23:45 UTC] by JustRumours
So, rather than have this stuff all over the place, I thought it would be easier if I just made a page that would handle all things RealTime and PostgreSQL.
While PostgreSQL can still be used Asterisk cdr pgsql, it can no longer be used with the voicemail system. Thus, if you are using the old pgsql for your db driven voicemail, when you upgrade, you will have to use the unixODBC code. As such, I figured it was just as easy to use the unixODBC subsystem for the CDR. I haven't done any preformance testing yet, but my guess is that the over head is not enough to justify having two different DataAccess layers. Anyhow, I choose to use one, so here is how I got RealTime and the CDR subsystem to use unixODBC.
These are the tables that I used to make my Asterisk RealTime work. I also am including a modified cdr table. I am planning on using the system with an online replicator (http://www.commandprompt.com), which means that all tables must have a primary key. As such, I added the primary key to the cdr table create statement. Lastly, I am put in some reasonable (for us anyway :P) defaults. I also changed the tables to reflect some of the "size" constraints that we run into. We tend to have very long appdata sections for our extensions since we use the app_sql to do things in the database from within the dialplan.
WARNING: Asterisk versions prior to 1.4.15 suffer a vulnerability in res_config_pgsql. If you want Postgres realtime, update immediately to 1.4.15!
NOTE: We are using PostgreSQL 8.1
CREATE TABLE cdr (
cdr_pkey serial PRIMARY KEY,
calldate timestamp with time zone NOT NULL default now(),
clid text NOT NULL default '',
src text NOT NULL default '',
dst text NOT NULL default '',
dcontext text NOT NULL default '',
channel text NOT NULL default '',
dstchannel text NOT NULL default '',
lastapp text NOT NULL default '',
lastdata text NOT NULL default '',
duration bigint NOT NULL default '0',
billsec bigint NOT NULL default '0',
disposition text NOT NULL default '',
amaflags bigint NOT NULL default '0',
accountcode text NOT NULL default '',
uniqueid text NOT NULL default '',
userfield text NOT NULL default ''
);
NOTE: If you are going to do lots of lookups on the table, it may be worth it to add an index or two :)
;
; cdr_odbc.conf
;
[global]
dsn=AsteriskCdr
username=asterisk
password=xxxxx
loguniqueid=yes
dispositionstring=yes
table=cdr ;"cdr" is default table name
usegmtime=no ; set to "yes" to log in GMT
CREATE TABLE voicemail (
uniqueid serial PRIMARY KEY,
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 'EST',
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 int2 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 now(),
UNIQUE (context,mailbox)
);
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) DEFAULT 'yes',
inkeys varchar(100),
outkeys varchar(100),
auth varchar(100) NOT NULL DEFAULT 'md5',
accountcode varchar(100),
amaflags varchar(100),
callerid varchar(100),
context varchar(100) NOT NULL,
defaultip varchar(15),
host varchar(31) NOT NULL DEFAULT 'dynamic',
language char(5),
mailbox varchar(50),
deny varchar(95),
permit varchar(95),
qualify varchar(4) DEFAULT 'yes',
disallow varchar(100) NOT NULL DEFAULT 'all',
allow varchar(100) NOT NULL DEFAULT 'ulaw',
ipaddr varchar(15),
port integer DEFAULT 0,
regseconds integer DEFAULT 0,
UNIQUE (username)
);
CREATE TABLE sip_buddies (
id serial NOT NULL primary key,
name varchar(80) NOT NULL default '',
host varchar(31) NOT NULL default '',
nat varchar(5) NOT NULL default 'no',
type varchar(255) NOT NULL,
CHECK (type IN('user','peer','friend')),
accountcode varchar(20) default NULL,
amaflags varchar(13) 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 'gsm,g726aal2,g726,g729;ilbc;ulaw;alaw',
fullcontact varchar(80) NOT NULL default '',
ipaddr varchar(15) NOT NULL default '',
port smallint NOT NULL default '0',
regserver varchar(100) default NULL,
regseconds integer NOT NULL default '0',
username varchar(80) NOT NULL default ''
);
NOTE: Notice that the name AND username need to be unique in their columns. For those that are a little unsure: the name is what your remote IAX clients need to use as a username. Look at the example below.
CREATE TABLE extensions (
id serial,
context varchar(40) NOT NULL default '',
exten varchar(40) NOT NULL default '',
priority int4 NOT NULL default 0,
app varchar(40) NOT NULL default '',
appdata varchar(256) NOT NULL default '',
PRIMARY KEY (context,exten,priority),
UNIQUE (id)
);
[settings]
iaxusers => odbc,asterisk,iax_buddies
iaxpeers => odbc,asterisk,iax_buddies
;sipusers => odbc,asterisk
;sippeers => odbc,asterisk
voicemail => odbc,asterisk,voicemail
ext_switch => odbc,asterisk,extensions
I used the same table for the iaxusers and the iaxpeers. If you really want to be "flexible" and you need to have both peers, users, and friends type, you should make two tables not one. Then and the extensions into the particular one you want. In the future when I have more time, I will make an example of this.
[iaxswitch]
switch => Realtime/mycontext@ext_switch
For the inserts I used:
INSERT INTO extensions VALUES (1, 'mycontext', '2815551212', 1, 'Playback', 'pbx-invalid');
INSERT INTO extensions VALUES (1, 'mycontext', '_617555XXXX', 1, 'Voicemail', 'u1013@default');
INSERT INTO iax_buddies (name,username,type,secret,auth,callerid,mailbox,context) values ('test-1013','test1013','friend','xxxx','md5','"Test User" <1013>','1013','iaxswitch');
INSERT INTO voicemail (context,mailbox) values ('default','1013');
Since we wanted to test with IAXy's, here is what we used with provision from the cvs iaxyprov:
; IAXY Provisioning Realtime testing
dhcp
server: 192.168.123.121
user: test-1013
pass: xxxx
register
codec: ulaw
;codec: adpcm
^
This page accessed 18209 times since creation on Mon 26 of Dec, 2005 [06:04 UTC].
While PostgreSQL can still be used Asterisk cdr pgsql, it can no longer be used with the voicemail system. Thus, if you are using the old pgsql for your db driven voicemail, when you upgrade, you will have to use the unixODBC code. As such, I figured it was just as easy to use the unixODBC subsystem for the CDR. I haven't done any preformance testing yet, but my guess is that the over head is not enough to justify having two different DataAccess layers. Anyhow, I choose to use one, so here is how I got RealTime and the CDR subsystem to use unixODBC.
These are the tables that I used to make my Asterisk RealTime work. I also am including a modified cdr table. I am planning on using the system with an online replicator (http://www.commandprompt.com), which means that all tables must have a primary key. As such, I added the primary key to the cdr table create statement. Lastly, I am put in some reasonable (for us anyway :P) defaults. I also changed the tables to reflect some of the "size" constraints that we run into. We tend to have very long appdata sections for our extensions since we use the app_sql to do things in the database from within the dialplan.
WARNING: Asterisk versions prior to 1.4.15 suffer a vulnerability in res_config_pgsql. If you want Postgres realtime, update immediately to 1.4.15!
NOTE: We are using PostgreSQL 8.1
Cdr
CREATE TABLE cdr (
cdr_pkey serial PRIMARY KEY,
calldate timestamp with time zone NOT NULL default now(),
clid text NOT NULL default '',
src text NOT NULL default '',
dst text NOT NULL default '',
dcontext text NOT NULL default '',
channel text NOT NULL default '',
dstchannel text NOT NULL default '',
lastapp text NOT NULL default '',
lastdata text NOT NULL default '',
duration bigint NOT NULL default '0',
billsec bigint NOT NULL default '0',
disposition text NOT NULL default '',
amaflags bigint NOT NULL default '0',
accountcode text NOT NULL default '',
uniqueid text NOT NULL default '',
userfield text NOT NULL default ''
);
NOTE: If you are going to do lots of lookups on the table, it may be worth it to add an index or two :)
Asterisk cdr_odbc.conf
;
; cdr_odbc.conf
;
[global]
dsn=AsteriskCdr
username=asterisk
password=xxxxx
loguniqueid=yes
dispositionstring=yes
table=cdr ;"cdr" is default table name
usegmtime=no ; set to "yes" to log in GMT
RealTime Voicemail
CREATE TABLE voicemail (
uniqueid serial PRIMARY KEY,
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 'EST',
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 int2 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 now(),
UNIQUE (context,mailbox)
);
RealTime IAX peers/users
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) DEFAULT 'yes',
inkeys varchar(100),
outkeys varchar(100),
auth varchar(100) NOT NULL DEFAULT 'md5',
accountcode varchar(100),
amaflags varchar(100),
callerid varchar(100),
context varchar(100) NOT NULL,
defaultip varchar(15),
host varchar(31) NOT NULL DEFAULT 'dynamic',
language char(5),
mailbox varchar(50),
deny varchar(95),
permit varchar(95),
qualify varchar(4) DEFAULT 'yes',
disallow varchar(100) NOT NULL DEFAULT 'all',
allow varchar(100) NOT NULL DEFAULT 'ulaw',
ipaddr varchar(15),
port integer DEFAULT 0,
regseconds integer DEFAULT 0,
UNIQUE (username)
);
SIP
CREATE TABLE sip_buddies (
id serial NOT NULL primary key,
name varchar(80) NOT NULL default '',
host varchar(31) NOT NULL default '',
nat varchar(5) NOT NULL default 'no',
type varchar(255) NOT NULL,
CHECK (type IN('user','peer','friend')),
accountcode varchar(20) default NULL,
amaflags varchar(13) 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 'gsm,g726aal2,g726,g729;ilbc;ulaw;alaw',
fullcontact varchar(80) NOT NULL default '',
ipaddr varchar(15) NOT NULL default '',
port smallint NOT NULL default '0',
regserver varchar(100) default NULL,
regseconds integer NOT NULL default '0',
username varchar(80) NOT NULL default ''
);
NOTE: Notice that the name AND username need to be unique in their columns. For those that are a little unsure: the name is what your remote IAX clients need to use as a username. Look at the example below.
RealTime Extensions
CREATE TABLE extensions (
id serial,
context varchar(40) NOT NULL default '',
exten varchar(40) NOT NULL default '',
priority int4 NOT NULL default 0,
app varchar(40) NOT NULL default '',
appdata varchar(256) NOT NULL default '',
PRIMARY KEY (context,exten,priority),
UNIQUE (id)
);
[settings]
iaxusers => odbc,asterisk,iax_buddies
iaxpeers => odbc,asterisk,iax_buddies
;sipusers => odbc,asterisk
;sippeers => odbc,asterisk
voicemail => odbc,asterisk,voicemail
ext_switch => odbc,asterisk,extensions
I used the same table for the iaxusers and the iaxpeers. If you really want to be "flexible" and you need to have both peers, users, and friends type, you should make two tables not one. Then and the extensions into the particular one you want. In the future when I have more time, I will make an example of this.
[iaxswitch]
switch => Realtime/mycontext@ext_switch
For the inserts I used:
INSERT INTO extensions VALUES (1, 'mycontext', '2815551212', 1, 'Playback', 'pbx-invalid');
INSERT INTO extensions VALUES (1, 'mycontext', '_617555XXXX', 1, 'Voicemail', 'u1013@default');
INSERT INTO iax_buddies (name,username,type,secret,auth,callerid,mailbox,context) values ('test-1013','test1013','friend','xxxx','md5','"Test User" <1013>','1013','iaxswitch');
INSERT INTO voicemail (context,mailbox) values ('default','1013');
Since we wanted to test with IAXy's, here is what we used with provision from the cvs iaxyprov:
; IAXY Provisioning Realtime testing
dhcp
server: 192.168.123.121
user: test-1013
pass: xxxx
register
codec: ulaw
;codec: adpcm
^
See Also
- Asterisk RealTime
- Asterisk RealTime Static
- Asterisk RealTime Sip
- Asterisk RealTime IAX
- Asterisk RealTime Voicemail
- Asterisk RealTime Extensions
- Realtime voicemail tricks
- Asterisk PGSQL
This page accessed 18209 times since creation on Mon 26 of Dec, 2005 [06:04 UTC].
Comments