1 GETTING ODBC STORAGE WITH POSTGRESQL WORKING WITH VOICEMAIL
3 1) Install PostgreSQL, PostgreSQL-devel, unixODBC, and unixODBC-devel, and
4 PostgreSQL-ODBC. Make sure PostgreSQL is running and listening on a TCP socket.
6 2) Log into your server as root, and then type:
8 [root@localhost ~]# su - postgres
10 This will log you into the system as the "postgres" user, so that you can
11 create a new role and database within the PostgreSQL database system. At the
14 $ createuser -s -D -R -l -P -e asterisk
15 Enter password for new role:
18 Obviously you should enter a password when prompted. This creates the
19 database role (or user).
21 Next we need to create the asterisk database. Type:
23 $ createdb -O asterisk -e asterisk
25 This creates the database and sets the owner of the database to the asterisk
30 you are using md5 authentication for the database user. The line in my
31 /var/lib/pgsql/data/pg_hba.conf looks like:
33 # "local" is for Unix domain socket connections only
34 local asterisk asterisk md5
35 local all all ident sameuser
36 # IPv4 local connections:
37 host all all 127.0.0.1/32 md5
39 As soon as you're done editing that file, log out as the postgres user.
41 3) Make sure you have the PostgreSQL odbc driver setup in /etc/odbcinst.ini.
45 Description = ODBC for PostgreSQL
46 Driver = /usr/lib/libodbcpsql.so
47 Setup = /usr/lib/libodbcpsqlS.so
50 You can confirm that unixODBC is seeing the driver by typing:
52 [jsmith2@localhost tmp]$ odbcinst -q -d
56 4) Setup a DSN in /etc/odbc.ini, pointing at the PostgreSQL database and
57 driver. Mine looks like:
60 Description = ODBC Testing
65 Servername = 127.0.0.1
67 Password = supersecret
76 You can confirm that unixODBC sees your DSN by typing:
78 [jsmith2@localhost tmp]$ odbcinst -q -s
82 5) Test your database connectivity through ODBC. If this doesn't work,
83 something is wrong with your ODBC setup.
85 [jsmith2@localhost tmp]$ echo "select 1" | isql -v testing
86 +---------------------------------------+
93 +---------------------------------------+
102 If your ODBC connectivity to PostgreSQL isn't working, you'll see an error
103 message instead, like this:
105 [jsmith2@localhost tmp]$ echo "select 1" | isql -v testing
106 [S1000][unixODBC]Could not connect to the server;
107 Could not connect to remote socket.
108 [ISQL]ERROR: Could not SQLConnect
109 bash: echo: write error: Broken pipe
111 6) Compile Asterisk with support for ODBC voicemail. Go to your Asterisk
112 source directory and run `make menuselect`. Under "Voicemail Build Options",
113 enable "ODBC_STORAGE".
114 # See doc/README.odbcstorage for more information
116 Recompile Asterisk and install the new version.
119 7) Once you've recompiled and re-installed Asterisk, check to make sure
120 res_odbc.so has been compiled.
122 localhost*CLI> show modules like res_odbc.so
123 Module Description Use Count
124 res_odbc.so ODBC Resource 0
128 8) Now it's time to get Asterisk configured. First, we need to tell Asterisk
129 about our ODBC setup. Open /etc/asterisk/res_odbc.conf and add the following:
136 9) At the Asterisk CLI, unload and then load the res_odbc.so module. (You
137 could restart Asterisk as well, but this way makes it easier to tell what's
138 happening.) Notice how it says it's connected to "postgres", which is our ODBC
139 connection as defined in res_odbc.conf, which points to the "testing" DSN in
142 localhost*CLI> unload res_odbc.so
143 Jan 2 21:19:36 WARNING[8130]: res_odbc.c:498 odbc_obj_disconnect: res_odbc: disconnected 0 from postgres [testing]
144 Jan 2 21:19:36 NOTICE[8130]: res_odbc.c:589 unload_module: res_odbc unloaded.
145 localhost*CLI> load res_odbc.so
146 Loaded /usr/lib/asterisk/modules/res_odbc.so => (ODBC Resource)
147 == Parsing '/etc/asterisk/res_odbc.conf': Found
148 Jan 2 21:19:40 NOTICE[8130]: res_odbc.c:266 load_odbc_config: Adding ENV var: INFORMIXSERVER=my_special_database
149 Jan 2 21:19:40 NOTICE[8130]: res_odbc.c:266 load_odbc_config: Adding ENV var: INFORMIXDIR=/opt/informix
150 Jan 2 21:19:40 NOTICE[8130]: res_odbc.c:295 load_odbc_config: registered database handle 'postgres' dsn->[testing]
151 Jan 2 21:19:40 NOTICE[8130]: res_odbc.c:555 odbc_obj_connect: Connecting postgres
152 Jan 2 21:19:40 NOTICE[8130]: res_odbc.c:570 odbc_obj_connect: res_odbc: Connected to postgres [testing]
153 Jan 2 21:19:40 NOTICE[8130]: res_odbc.c:600 load_module: res_odbc loaded.
155 You can also check the status of your ODBC connection at any time from the
158 localhost*CLI> odbc show
163 10) Now we can setup our voicemail table in PostgreSQL. Log into PostgreSQL and
164 type (or copy and paste) the following:
167 -- First, let's create our large object type, called "lo"
169 CREATE FUNCTION loin (cstring) RETURNS lo AS 'oidin' LANGUAGE internal IMMUTABLE STRICT;
170 CREATE FUNCTION loout (lo) RETURNS cstring AS 'oidout' LANGUAGE internal IMMUTABLE STRICT;
171 CREATE FUNCTION lorecv (internal) RETURNS lo AS 'oidrecv' LANGUAGE internal IMMUTABLE STRICT;
172 CREATE FUNCTION losend (lo) RETURNS bytea AS 'oidrecv' LANGUAGE internal IMMUTABLE STRICT;
174 CREATE TYPE lo ( INPUT = loin, OUTPUT = loout, RECEIVE = lorecv, SEND = losend, INTERNALLENGTH = 4, PASSEDBYVALUE );
175 CREATE CAST (lo AS oid) WITHOUT FUNCTION AS IMPLICIT;
176 CREATE CAST (oid AS lo) WITHOUT FUNCTION AS IMPLICIT;
179 -- If we're not already using plpgsql, then let's use it!
181 CREATE TRUSTED LANGUAGE plpgsql;
184 -- Next, let's create a trigger to cleanup the large object table
185 -- whenever we update or delete a row from the voicemessages table
188 CREATE FUNCTION vm_lo_cleanup() RETURNS "trigger"
193 -- raise notice 'Starting lo_cleanup function for large object with oid %',old.recording;
194 -- If it is an update action but the BLOB (lo) field was not changed, dont do anything
195 if (TG_OP = 'UPDATE') then
196 if ((old.recording = new.recording) or (old.recording is NULL)) then
197 raise notice 'Not cleaning up the large object table, as recording has not changed';
201 if (old.recording IS NOT NULL) then
202 SELECT INTO msgcount COUNT(*) AS COUNT FROM voicemessages WHERE recording = old.recording;
203 if (msgcount > 0) then
204 raise notice 'Not deleting record from the large object table, as object is still referenced';
207 perform lo_unlink(old.recording);
209 raise notice 'Cleaning up the large object table';
212 raise exception 'Failed to cleanup the large object table';
217 raise notice 'No need to cleanup the large object table, no recording on old row';
224 -- Now, let's create our voicemessages table
225 -- This is what holds the voicemail from Asterisk
228 CREATE TABLE voicemessages
230 uniqueid serial PRIMARY KEY,
234 macrocontext varchar(80),
235 callerid varchar(40),
236 origtime varchar(40),
237 duration varchar(20),
238 mailboxuser varchar(80),
239 mailboxcontext varchar(80),
242 "read" bool DEFAULT false
246 -- Let's not forget to make the voicemessages table use the trigger
249 CREATE TRIGGER vm_cleanup AFTER DELETE OR UPDATE ON voicemessages FOR EACH ROW EXECUTE PROCEDURE vm_lo_cleanup();
252 11) Just as a sanity check, make sure you check the voicemessages table via the
255 [jsmith2@localhost ODBC]$ echo "SELECT id, msgnum, dir, duration FROM voicemessages WHERE msgnum = 1" | isql testing
256 +---------------------------------------+
263 +---------------------------------------+
264 SQL> +------------+------------+---------------------------------------------------------------------------------+---------------------+
265 | id | msgnum | dir | duration |
266 +------------+------------+---------------------------------------------------------------------------------+---------------------+
267 +------------+------------+---------------------------------------------------------------------------------+---------------------+
268 SQLRowCount returns 0
271 12) Now we can finally configure voicemail in Asterisk to use our database.
272 Open /etc/asterisk/voicemail.conf, and look in the [general] section. I've
273 changed the format to gsm (as I can't seem to get WAV or wav working), and
274 specify both the odbc connection and database table to use.
277 ; Default formats for writing Voicemail
278 ;format=g723sf|wav49|wav
281 odbctable=voicemessages
283 You'll also want to create a new voicemail context called "odbctest" to do some
284 testing, and create a sample mailbox inside that context. Add the following to
285 the very bottom of voicemail.conf:
288 101 => 5555,Example Mailbox
291 13) Once you've updated voicemail.conf, let's make the changes take effect:
293 localhost*CLI> unload app_voicemail.so
294 == Unregistered application 'VoiceMail'
295 == Unregistered application 'VoiceMailMain'
296 == Unregistered application 'MailboxExists'
297 == Unregistered application 'VMAuthenticate'
298 localhost*CLI> load app_voicemail.so
299 Loaded /usr/lib/asterisk/modules/app_voicemail.so => (Comedian Mail (Voicemail System))
300 == Registered application 'VoiceMail'
301 == Registered application 'VoiceMailMain'
302 == Registered application 'MailboxExists'
303 == Registered application 'VMAuthenticate'
304 == Parsing '/etc/asterisk/voicemail.conf': Found
306 You can check to make sure your new mailbox exists by typing:
308 localhost*CLI> show voicemail users for odbctest
309 Context Mbox User Zone NewMsg
310 odbctest 101 Example Mailbox 0
313 14) Now, let's add a new context called "odbc" to extensions.conf. We'll use
314 these extensions to do some testing:
317 exten => 100,1,Voicemail(101@odbctest)
318 exten => 200,1,VoicemailMain(101@odbctest)
321 15) Next, we need to point a phone at the odbc context. In my case, I've got a
322 SIP phone called "linksys" that is registering to Asterisk, so I'm setting its
323 context to the [odbc] context we created in the previous step. The relevant
324 section of my sip.conf file looks like:
336 I can check to see that my linksys phone is registered with Asterisk correctly:
338 localhost*CLI> sip show peers like linksys
339 Name/username Host Dyn Nat ACL Port Status
340 linksys/linksys 192.168.0.103 D 5060 OK (9 ms)
341 1 sip peers [1 online , 0 offline]
344 16) At last, we're finally ready to leave a voicemail message and have it
345 stored in our database! (Who'd have guessed it would be this much trouble?!?)
346 Pick up the phone, dial extension 100, and leave yourself a voicemail message.
347 In my case, this is what appeared on the Asterisk CLI:
350 -- Executing VoiceMail("SIP/linksys-10228cac", "101@odbctest") in new stack
351 -- Playing 'vm-intro' (language 'en')
352 -- Playing 'beep' (language 'en')
353 -- Recording the message
354 -- x=0, open writing: /var/spool/asterisk/voicemail/odbctest/101/tmp/dlZunm format: gsm, 0x101f6534
355 -- User ended message by pressing #
356 -- Playing 'auth-thankyou' (language 'en')
357 == Parsing '/var/spool/asterisk/voicemail/odbctest/101/INBOX/msg0000.txt': Found
359 Now, we can check the database and make sure the record actually made it into
360 PostgreSQL, from within the psql utility.
362 [jsmith2@localhost ~]$ psql
364 Welcome to psql 8.1.4, the PostgreSQL interactive terminal.
366 Type: \copyright for distribution terms
367 \h for help with SQL commands
368 \? for help with psql commands
369 \g or terminate with semicolon to execute query
372 asterisk=# SELECT * FROM voicemessages;
373 id | msgnum | dir | context | macrocontext | callerid | origtime | duration | mailboxuser | mailboxcontext | recording | label | read | sip_id | pabx_id | iax_id
374 ----+--------+--------------------------------------------------+---------+--------------+-----------------------+------------+----------+-------------+----------------+-----------+-------+------+--------+---------+--------
375 26 | 0 | /var/spool/asterisk/voicemail/odbctest/101/INBOX | odbc | | "linksys" <linksys> | 1167794179 | 7 | 101 | odbctest | 16599 | | f | | |
378 Did you notice the the recording column is just a number? When a recording
379 gets stuck in the database, the audio isn't actually stored in the
380 voicemessages table. It's stored in a system table called the large object
381 table. We can look in the large object table and verify that the object
382 actually exists there:
387 -------+-------------
391 In my case, the OID is 16599. Your OID will almost surely be different. Just
392 make sure the OID number in the recording column in the voicemessages table
393 corresponds with a record in the large object table. (The trigger we added to
394 our voicemessages table was designed to make sure this is always the case.)
396 We can also pull a copy of the voicemail message back out of the database and
397 write it to a file, to help us as we debug things:
399 asterisk=# \lo_export 16599 /tmp/odcb-16599.gsm
402 We can even listen to the file from the Linux command line:
404 [jsmith2@localhost tmp]$ play /tmp/odcb-16599.gsm
406 Input Filename : /tmp/odcb-16599.gsm
412 Time: 00:06.22 [00:00.00] of 00:00.00 ( 0.0%) Output Buffer: 298.36K
417 17) Last but not least, we can pull the voicemail message back out of the
418 database by dialing extension 200 and entering "5555" at the password prompt.
419 You should see something like this on the Asterisk CLI:
422 -- Executing VoiceMailMain("SIP/linksys-10228cac", "101@odbctest") in new stack
423 -- Playing 'vm-password' (language 'en')
424 -- Playing 'vm-youhave' (language 'en')
425 -- Playing 'digits/1' (language 'en')
426 -- Playing 'vm-INBOX' (language 'en')
427 -- Playing 'vm-message' (language 'en')
428 -- Playing 'vm-onefor' (language 'en')
429 -- Playing 'vm-INBOX' (language 'en')
430 -- Playing 'vm-messages' (language 'en')
431 -- Playing 'vm-opts' (language 'en')
432 -- Playing 'vm-first' (language 'en')
433 -- Playing 'vm-message' (language 'en')
434 == Parsing '/var/spool/asterisk/voicemail/odbctest/101/INBOX/msg0000.txt': Found
435 -- Playing 'vm-received' (language 'en')
436 -- Playing 'digits/at' (language 'en')
437 -- Playing 'digits/10' (language 'en')
438 -- Playing 'digits/16' (language 'en')
439 -- Playing 'digits/p-m' (language 'en')
440 -- Playing '/var/spool/asterisk/voicemail/odbctest/101/INBOX/msg0000' (language 'en')
441 -- Playing 'vm-advopts' (language 'en')
442 -- Playing 'vm-repeat' (language 'en')
443 -- Playing 'vm-delete' (language 'en')
444 -- Playing 'vm-toforward' (language 'en')
445 -- Playing 'vm-savemessage' (language 'en')
446 -- Playing 'vm-helpexit' (language 'en')
447 -- Playing 'vm-goodbye' (language 'en')
453 (updated 11 Mar 2007)