1 GETTING ODBC STORAGE WITH POSTGRESQL WORKING WITH VOICEMAIL
4 1) Install PostgreSQL, PostgreSQL-devel, unixODBC, and unixODBC-devel, and
5 PostgreSQL-ODBC. Make sure PostgreSQL is listening on a TCP socket, and that
6 you are using md5 authentication for the database user. The line in my
7 pg_hba.conf looks like:
9 # "local" is for Unix domain socket connections only
10 local jsmith2 jsmith2 md5
11 local all all ident sameuser
12 # IPv4 local connections:
13 host all all 127.0.0.1/32 md5
16 2) Make sure you have the PostgreSQL odbc driver setup in /etc/odbcinst.ini.
20 Description = ODBC for PostgreSQL
21 Driver = /usr/lib/libodbcpsql.so
22 Setup = /usr/lib/libodbcpsqlS.so
25 You can confirm that unixODBC is seeing the driver by typing:
27 [jsmith2@localhost tmp]$ odbcinst -q -d
31 3) Setup a DSN in /etc/odbc.ini, pointing at the PostgreSQL database and
32 driver. Mine looks like:
35 Description = ODBC Testing
40 Servername = 127.0.0.1
42 Password = supersecret
51 You can confirm that unixODBC sees your DSN by typing:
53 [jsmith2@localhost tmp]$ odbcinst -q -s
57 4) Test your database connectivity through ODBC. If this doesn't work,
58 something is wrong with your ODBC setup.
60 [jsmith2@localhost tmp]$ echo "select 1" | isql -v testing
61 +---------------------------------------+
68 +---------------------------------------+
77 If your ODBC connectivity to PostgreSQL isn't working, you'll see an error
78 message instead, like this:
80 [jsmith2@localhost tmp]$ echo "select 1" | isql -v testing
81 [S1000][unixODBC]Could not connect to the server;
82 Could not connect to remote socket.
83 [ISQL]ERROR: Could not SQLConnect
84 bash: echo: write error: Broken pipe
86 5) Compile Asterisk with support for ODBC voicemail. Go to your Asterisk
87 source directory and edit apps/Makefile, and uncomment the two lines as shown
91 # If you have UnixODBC you can use ODBC voicemail
94 # Uncomment to use ODBC storage
95 CFLAGS+=-DUSE_ODBC_STORAGE
96 # Uncomment for extended ODBC voicemail storage
97 CFLAGS+=-DEXTENDED_ODBC_STORAGE
98 # See doc/README.odbcstorage for more information
100 Recompile Asterisk and install the new version.
103 6) Once you've recompiled and re-installed Asterisk, check to make sure
104 res_odbc.so has been compiled.
106 localhost*CLI> show modules like res_odbc.so
107 Module Description Use Count
108 res_odbc.so ODBC Resource 0
112 7) Now it's time to get Asterisk configured. First, we need to tell Asterisk
113 about our ODBC setup. Open /etc/asterisk/res_odbc.conf and add the following:
120 8) At the Asterisk CLI, unload and then load the res_odbc.so module. (You
121 could restart Asterisk as well, but this way makes it easier to tell what's
122 happening.) Notice how it says it's connected to "postgres", which is our ODBC
123 connection as defined in res_odbc.conf, which points to the "testing" DSN in
126 localhost*CLI> unload res_odbc.so
127 Jan 2 21:19:36 WARNING[8130]: res_odbc.c:498 odbc_obj_disconnect: res_odbc: disconnected 0 from postgres [testing]
128 Jan 2 21:19:36 NOTICE[8130]: res_odbc.c:589 unload_module: res_odbc unloaded.
129 localhost*CLI> load res_odbc.so
130 Loaded /usr/lib/asterisk/modules/res_odbc.so => (ODBC Resource)
131 == Parsing '/etc/asterisk/res_odbc.conf': Found
132 Jan 2 21:19:40 NOTICE[8130]: res_odbc.c:266 load_odbc_config: Adding ENV var: INFORMIXSERVER=my_special_database
133 Jan 2 21:19:40 NOTICE[8130]: res_odbc.c:266 load_odbc_config: Adding ENV var: INFORMIXDIR=/opt/informix
134 Jan 2 21:19:40 NOTICE[8130]: res_odbc.c:295 load_odbc_config: registered database handle 'postgres' dsn->[testing]
135 Jan 2 21:19:40 NOTICE[8130]: res_odbc.c:555 odbc_obj_connect: Connecting postgres
136 Jan 2 21:19:40 NOTICE[8130]: res_odbc.c:570 odbc_obj_connect: res_odbc: Connected to postgres [testing]
137 Jan 2 21:19:40 NOTICE[8130]: res_odbc.c:600 load_module: res_odbc loaded.
139 You can also check the status of your ODBC connection at any time from the
142 localhost*CLI> odbc show
147 9) Now we can setup our voicemail table in PostgreSQL. Log into PostgreSQL and
148 type (or copy and paste) the following:
151 -- First, let's create our large object type, called "lo"
153 CREATE FUNCTION loin (cstring) RETURNS lo AS 'oidin' LANGUAGE internal IMMUTABLE STRICT;
154 CREATE FUNCTION loout (lo) RETURNS cstring AS 'oidout' LANGUAGE internal IMMUTABLE STRICT;
155 CREATE FUNCTION lorecv (internal) RETURNS lo AS 'oidrecv' LANGUAGE internal IMMUTABLE STRICT;
156 CREATE FUNCTION losend (lo) RETURNS bytea AS 'oidrecv' LANGUAGE internal IMMUTABLE STRICT;
158 CREATE TYPE lo ( INPUT = loin, OUTPUT = loout, RECEIVE = lorecv, SEND = losend, INTERNALLENGTH = 4, PASSEDBYVALUE );
159 CREATE CAST (lo AS oid) WITHOUT FUNCTION AS IMPLICIT;
160 CREATE CAST (oid AS lo) WITHOUT FUNCTION AS IMPLICIT;
163 -- If we're not already using plpgsql, then let's use it!
165 CREATE TRUSTED LANGUAGE plpgsql;
168 -- Next, let's create a trigger to cleanup the large object table
169 -- whenever we update or delete a row from the voicemessages table
172 CREATE FUNCTION vm_lo_cleanup() RETURNS "trigger"
177 -- raise notice 'Starting lo_cleanup function for large object with oid %',old.recording;
178 -- If it is an update action but the BLOB (lo) field was not changed, dont do anything
179 if (TG_OP = 'UPDATE') then
180 if ((old.recording = new.recording) or (old.recording is NULL)) then
181 raise notice 'Not cleaning up the large object table, as recording has not changed';
185 if (old.recording IS NOT NULL) then
186 SELECT INTO msgcount COUNT(*) AS COUNT FROM voicemessages WHERE recording = old.recording;
187 if (msgcount > 0) then
188 raise notice 'Not deleting record from the large object table, as object is still referenced';
191 perform lo_unlink(old.recording);
193 raise notice 'Cleaning up the large object table';
196 raise exception 'Failed to cleanup the large object table';
201 raise notice 'No need to cleanup the large object table, no recording on old row';
208 -- Now, let's create our voicemessages table
209 -- This is what holds the voicemail from Asterisk
212 CREATE TABLE voicemessages
214 uniqueid serial PRIMARY KEY,
218 macrocontext varchar(80),
219 callerid varchar(40),
220 origtime varchar(40),
221 duration varchar(20),
222 mailboxuser varchar(80),
223 mailboxcontext varchar(80),
226 "read" bool DEFAULT false
230 -- Let's not forget to make the voicemessages table use the trigger
233 CREATE TRIGGER vm_cleanup AFTER DELETE OR UPDATE ON voicemessages FOR EACH ROW EXECUTE PROCEDURE vm_lo_cleanup();
236 10) Just as a sanity check, make sure you check the voicemessages table via the
239 [jsmith2@localhost ODBC]$ echo "SELECT id, msgnum, dir, duration FROM voicemessages WHERE msgnum = 1" | isql testing
240 +---------------------------------------+
247 +---------------------------------------+
248 SQL> +------------+------------+---------------------------------------------------------------------------------+---------------------+
249 | id | msgnum | dir | duration |
250 +------------+------------+---------------------------------------------------------------------------------+---------------------+
251 +------------+------------+---------------------------------------------------------------------------------+---------------------+
252 SQLRowCount returns 0
255 11) Now we can finally configure voicemail in Asterisk to use our database.
256 Open /etc/asterisk/voicemail.conf, and look in the [general] section. I've
257 changed the format to gsm (as I can't seem to get WAV or wav working), and
258 specify both the odbc connection and database table to use.
261 ; Default formats for writing Voicemail
262 ;format=g723sf|wav49|wav
265 odbctable=voicemessages
267 You'll also want to create a new voicemail context called "odbctest" to do some
268 testing, and create a sample mailbox inside that context. Add the following to
269 the very bottom of voicemail.conf:
272 101 => 5555,Example Mailbox
275 12) Once you've updated voicemail.conf, let's make the changes take effect:
277 localhost*CLI> unload app_voicemail.so
278 == Unregistered application 'VoiceMail'
279 == Unregistered application 'VoiceMailMain'
280 == Unregistered application 'MailboxExists'
281 == Unregistered application 'VMAuthenticate'
282 localhost*CLI> load app_voicemail.so
283 Loaded /usr/lib/asterisk/modules/app_voicemail.so => (Comedian Mail (Voicemail System))
284 == Registered application 'VoiceMail'
285 == Registered application 'VoiceMailMain'
286 == Registered application 'MailboxExists'
287 == Registered application 'VMAuthenticate'
288 == Parsing '/etc/asterisk/voicemail.conf': Found
290 You can check to make sure your new mailbox exists by typing:
292 localhost*CLI> show voicemail users for odbctest
293 Context Mbox User Zone NewMsg
294 odbctest 101 Example Mailbox 0
297 13) Now, let's add a new context called "odbc" to extensions.conf. We'll use
298 these extensions to do some testing:
301 exten => 100,1,Voicemail(101@odbctest)
302 exten => 200,1,VoicemailMain(101@odbctest)
305 14) Next, we need to point a phone at the odbc context. In my case, I've got a
306 SIP phone called "linksys" that is registering to Asterisk, so I'm setting its
307 context to the [odbc] context we created in the previous step. The relevant
308 section of my sip.conf file looks like:
320 I can check to see that my linksys phone is registered with Asterisk correctly:
322 localhost*CLI> sip show peers like linksys
323 Name/username Host Dyn Nat ACL Port Status
324 linksys/linksys 192.168.0.103 D 5060 OK (9 ms)
325 1 sip peers [1 online , 0 offline]
328 15) At last, we're finally ready to leave a voicemail message and have it
329 stored in our database! (Who'd have guessed it would be this much trouble?!?)
330 Pick up the phone, dial extension 100, and leave yourself a voicemail message.
331 In my case, this is what appeared on the Asterisk CLI:
334 -- Executing VoiceMail("SIP/linksys-10228cac", "101@odbctest") in new stack
335 -- Playing 'vm-intro' (language 'en')
336 -- Playing 'beep' (language 'en')
337 -- Recording the message
338 -- x=0, open writing: /var/spool/asterisk/voicemail/odbctest/101/tmp/dlZunm format: gsm, 0x101f6534
339 -- User ended message by pressing #
340 -- Playing 'auth-thankyou' (language 'en')
341 == Parsing '/var/spool/asterisk/voicemail/odbctest/101/INBOX/msg0000.txt': Found
343 Now, we can check the database and make sure the record actually made it into
344 PostgreSQL, from within the psql utility.
346 [jsmith2@localhost ~]$ psql
348 Welcome to psql 8.1.4, the PostgreSQL interactive terminal.
350 Type: \copyright for distribution terms
351 \h for help with SQL commands
352 \? for help with psql commands
353 \g or terminate with semicolon to execute query
356 jsmith2=# SELECT * FROM voicemessages;
357 id | msgnum | dir | context | macrocontext | callerid | origtime | duration | mailboxuser | mailboxcontext | recording | label | read | sip_id | pabx_id | iax_id
358 ----+--------+--------------------------------------------------+---------+--------------+-----------------------+------------+----------+-------------+----------------+-----------+-------+------+--------+---------+--------
359 26 | 0 | /var/spool/asterisk/voicemail/odbctest/101/INBOX | odbc | | "linksys" <linksys> | 1167794179 | 7 | 101 | odbctest | 16599 | | f | | |
362 Did you notice the the recording column is just a number? When a recording
363 gets stuck in the database, the audio isn't actually stored in the
364 voicemessages table. It's stored in a system table called the large object
365 table. We can look in the large object table and verify that the object
366 actually exists there:
371 -------+-------------
375 In my case, the OID is 16599. Your OID will almost surely be different. Just
376 make sure the OID number in the recording column in the voicemessages table
377 corresponds with a record in the large object table. (The trigger we added to
378 our voicemessages table was designed to make sure this is always the case.)
380 We can also pull a copy of the voicemail message back out of the database and
381 write it to a file, to help us as we debug things:
383 jsmith2=# \lo_export 16599 /tmp/odcb-16599.gsm
386 We can even listen to the file from the Linux command line:
388 [jsmith2@localhost tmp]$ play /tmp/odcb-16599.gsm
390 Input Filename : /tmp/odcb-16599.gsm
396 Time: 00:06.22 [00:00.00] of 00:00.00 ( 0.0%) Output Buffer: 298.36K
401 16) Last but not least, we can pull the voicemail message back out of the
402 database by dialing extension 200 and entering "5555" at the password prompt.
403 You should see something like this on the Asterisk CLI:
406 -- Executing VoiceMailMain("SIP/linksys-10228cac", "101@odbctest") in new stack
407 -- Playing 'vm-password' (language 'en')
408 -- Playing 'vm-youhave' (language 'en')
409 -- Playing 'digits/1' (language 'en')
410 -- Playing 'vm-INBOX' (language 'en')
411 -- Playing 'vm-message' (language 'en')
412 -- Playing 'vm-onefor' (language 'en')
413 -- Playing 'vm-INBOX' (language 'en')
414 -- Playing 'vm-messages' (language 'en')
415 -- Playing 'vm-opts' (language 'en')
416 -- Playing 'vm-first' (language 'en')
417 -- Playing 'vm-message' (language 'en')
418 == Parsing '/var/spool/asterisk/voicemail/odbctest/101/INBOX/msg0000.txt': Found
419 -- Playing 'vm-received' (language 'en')
420 -- Playing 'digits/at' (language 'en')
421 -- Playing 'digits/10' (language 'en')
422 -- Playing 'digits/16' (language 'en')
423 -- Playing 'digits/p-m' (language 'en')
424 -- Playing '/var/spool/asterisk/voicemail/odbctest/101/INBOX/msg0000' (language 'en')
425 -- Playing 'vm-advopts' (language 'en')
426 -- Playing 'vm-repeat' (language 'en')
427 -- Playing 'vm-delete' (language 'en')
428 -- Playing 'vm-toforward' (language 'en')
429 -- Playing 'vm-savemessage' (language 'en')
430 -- Playing 'vm-helpexit' (language 'en')
431 -- Playing 'vm-goodbye' (language 'en')