Asterisk RealTime PostgreSQL

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


Cdr


CREATE TABLE cdr
(
id serial NOT NULL,
calldate timestamp with time zone NOT NULL DEFAULT now(),
clid character varying(80),
src character varying(80),
dst character varying(80),
dcontext character varying(80),
channel character varying(80),
dstchannel character varying(80),
lastapp character varying(80),
lastdata character varying(80),
duration integer NOT NULL DEFAULT 0,
billsec integer NOT NULL DEFAULT 0,
disposition character varying(45),
amaflags integer NOT NULL DEFAULT 0,
accountcode character varying(20),
uniqueid character varying(150),
userfield character varying(255),
CONSTRAINT cdr_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
CREATE INDEX billsec
ON cdr
USING btree
(billsec);
CREATE INDEX calldate
ON cdr
USING btree
(calldate);
CREATE INDEX dst
ON cdr
USING btree
(dst);
CREATE INDEX src
ON cdr
USING btree
(src);

NOTE: If you are going to do lots of lookups on the table, consider creating some other indexes.

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
(
id serial NOT NULL,
"name" character varying(80) NOT NULL,
accountcode character varying(20),
amaflags character varying(7),
callgroup character varying(10),
callerid character varying(80),
directmedia character varying(3) DEFAULT 'yes',
context character varying(80) DEFAULT 'default',
defaultip character varying(15),
dtmfmode character varying(7),
fromuser character varying(80),
fromdomain character varying(80),
host character varying(31) NOT NULL DEFAULT 'dynamic',
insecure character varying(4),
"language" character varying(2),
mailbox character varying(50),
md5secret character varying(80),
nat character varying(5) NOT NULL DEFAULT 'no',
permit character varying(95),
deny character varying(95),
mask character varying(95),
pickupgroup character varying(10),
port character varying(5),
qualify character varying(3),
restrictcid character varying(1),
rtptimeout character varying(3),
rtpholdtimeout character varying(3),
secret character varying(80),
"type" character varying NOT NULL DEFAULT 'friend',
username character varying(80),
disallow character varying(100) DEFAULT 'all',
allow character varying(100) DEFAULT 'alaw,ulaw',
musiconhold character varying(100),
regseconds integer NOT NULL DEFAULT 0,
ipaddr character varying(15),
regexten character varying(80),
cancallforward character varying(3) DEFAULT 'yes',
lastms character varying(80),
useragent character varying(100),
defaultuser character varying(100),
fullcontact character varying(100),
regserver character varying(100),
CONSTRAINT sip_conf_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
CREATE UNIQUE INDEX "name" ON sip USING btree (name);



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


So, to see the Asterisk config extconfig.conf that uses this, here is my testing one:

[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.


Lastly, to get the whole thing to work, I added this to the Asterisk Extensions:

[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


This page accessed 76748 times since creation on Mon 26 of Dec, 2005 (12:04).
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


Cdr


CREATE TABLE cdr
(
id serial NOT NULL,
calldate timestamp with time zone NOT NULL DEFAULT now(),
clid character varying(80),
src character varying(80),
dst character varying(80),
dcontext character varying(80),
channel character varying(80),
dstchannel character varying(80),
lastapp character varying(80),
lastdata character varying(80),
duration integer NOT NULL DEFAULT 0,
billsec integer NOT NULL DEFAULT 0,
disposition character varying(45),
amaflags integer NOT NULL DEFAULT 0,
accountcode character varying(20),
uniqueid character varying(150),
userfield character varying(255),
CONSTRAINT cdr_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
CREATE INDEX billsec
ON cdr
USING btree
(billsec);
CREATE INDEX calldate
ON cdr
USING btree
(calldate);
CREATE INDEX dst
ON cdr
USING btree
(dst);
CREATE INDEX src
ON cdr
USING btree
(src);

NOTE: If you are going to do lots of lookups on the table, consider creating some other indexes.

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
(
id serial NOT NULL,
"name" character varying(80) NOT NULL,
accountcode character varying(20),
amaflags character varying(7),
callgroup character varying(10),
callerid character varying(80),
directmedia character varying(3) DEFAULT 'yes',
context character varying(80) DEFAULT 'default',
defaultip character varying(15),
dtmfmode character varying(7),
fromuser character varying(80),
fromdomain character varying(80),
host character varying(31) NOT NULL DEFAULT 'dynamic',
insecure character varying(4),
"language" character varying(2),
mailbox character varying(50),
md5secret character varying(80),
nat character varying(5) NOT NULL DEFAULT 'no',
permit character varying(95),
deny character varying(95),
mask character varying(95),
pickupgroup character varying(10),
port character varying(5),
qualify character varying(3),
restrictcid character varying(1),
rtptimeout character varying(3),
rtpholdtimeout character varying(3),
secret character varying(80),
"type" character varying NOT NULL DEFAULT 'friend',
username character varying(80),
disallow character varying(100) DEFAULT 'all',
allow character varying(100) DEFAULT 'alaw,ulaw',
musiconhold character varying(100),
regseconds integer NOT NULL DEFAULT 0,
ipaddr character varying(15),
regexten character varying(80),
cancallforward character varying(3) DEFAULT 'yes',
lastms character varying(80),
useragent character varying(100),
defaultuser character varying(100),
fullcontact character varying(100),
regserver character varying(100),
CONSTRAINT sip_conf_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
CREATE UNIQUE INDEX "name" ON sip USING btree (name);



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


So, to see the Asterisk config extconfig.conf that uses this, here is my testing one:

[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.


Lastly, to get the whole thing to work, I added this to the Asterisk Extensions:

[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


This page accessed 76748 times since creation on Mon 26 of Dec, 2005 (12:04).
Created by: blankman, Last modification: Fri 12 of Mar, 2010 (19:42 UTC) by mfdutra
Please update this page with new information, just login and click on the "Edit" or "Discussion" tab. Get a free login here: Register Thanks! - Find us on Google+