Merged revisions 51162 via svnmerge from
[asterisk/asterisk.git] / doc / voicemail_odbc_postgresql.txt
1 GETTING ODBC STORAGE WITH POSTGRESQL WORKING WITH VOICEMAIL
2
3
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:
8
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
14
15
16 2) Make sure you have the PostgreSQL odbc driver setup in /etc/odbcinst.ini.
17 Mine looks like:
18
19 [PostgreSQL]
20 Description     = ODBC for PostgreSQL
21 Driver          = /usr/lib/libodbcpsql.so
22 Setup           = /usr/lib/libodbcpsqlS.so
23 FileUsage       = 1
24
25 You can confirm that unixODBC is seeing the driver by typing:
26
27 [jsmith2@localhost tmp]$ odbcinst -q -d
28 [PostgreSQL]
29
30
31 3) Setup a DSN in /etc/odbc.ini, pointing at the PostgreSQL database and
32 driver.  Mine looks like:
33
34 [testing]
35 Description           = ODBC Testing
36 Driver                = PostgreSQL
37 Trace                 = No
38 TraceFile             = sql.log
39 Database              = jsmith2
40 Servername            = 127.0.0.1
41 UserName              = jsmith2
42 Password              = supersecret
43 Port                  = 5432
44 ReadOnly              = No
45 RowVersioning         = No
46 ShowSystemTables      = No
47 ShowOidColumn         = No
48 FakeOidIndex          = No
49 ConnSettings          =
50
51 You can confirm that unixODBC sees your DSN by typing:
52
53 [jsmith2@localhost tmp]$ odbcinst -q -s
54 [testing]
55
56
57 4) Test your database connectivity through ODBC.  If this doesn't work,
58 something is wrong with your ODBC setup.
59
60 [jsmith2@localhost tmp]$ echo "select 1" | isql -v testing
61 +---------------------------------------+
62 | Connected!                            |
63 |                                       |
64 | sql-statement                         |
65 | help [tablename]                      |
66 | quit                                  |
67 |                                       |
68 +---------------------------------------+
69 SQL> +------------+
70 | ?column?   |
71 +------------+
72 | 1          |
73 +------------+
74 SQLRowCount returns 1
75 1 rows fetched
76
77 If your ODBC connectivity to PostgreSQL isn't working, you'll see an error
78 message instead, like this:
79
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
85
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
88 below:
89
90 #
91 # If you have UnixODBC you can use ODBC voicemail
92 # storage
93 #
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
99
100 Recompile Asterisk and install the new version.
101
102
103 6) Once you've recompiled and re-installed Asterisk, check to make sure
104 res_odbc.so has been compiled.
105
106 localhost*CLI> show modules like res_odbc.so
107 Module                         Description                              Use Count 
108 res_odbc.so                    ODBC Resource                            0         
109 1 modules loaded
110
111
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:
114
115 [postgres]
116 enabled => yes
117 dsn => testing
118 pre-connect => yes
119
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
124 ODBC.
125
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.
138
139 You can also check the status of your ODBC connection at any time from the
140 Asterisk CLI:
141
142 localhost*CLI> odbc show
143 Name: postgres 
144 DSN: testing
145 Connected: yes
146
147 9) Now we can setup our voicemail table in PostgreSQL.  Log into PostgreSQL and
148 type (or copy and paste) the following:
149
150 --
151 -- First, let's create our large object type, called "lo"
152 --
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;
157
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;
161
162 --
163 -- If we're not already using plpgsql, then let's use it!
164 --
165 CREATE TRUSTED LANGUAGE plpgsql;
166
167 --
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
170 --
171
172 CREATE FUNCTION vm_lo_cleanup() RETURNS "trigger"
173     AS $$
174     declare
175       msgcount INTEGER;
176     begin
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';
182           return new;
183         end if;
184       end if;
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';
189           return new;
190         else
191           perform lo_unlink(old.recording);
192           if found then
193             raise notice 'Cleaning up the large object table';
194             return new;
195           else
196             raise exception 'Failed to cleanup the large object table';
197             return old;
198           end if;
199         end if;
200       else
201         raise notice 'No need to cleanup the large object table, no recording on old row';
202         return new;
203       end if;
204     end$$
205     LANGUAGE plpgsql;
206
207 --
208 -- Now, let's create our voicemessages table
209 -- This is what holds the voicemail from Asterisk
210 --
211
212 CREATE TABLE voicemessages
213 (
214   uniqueid serial PRIMARY KEY,
215   msgnum int4,
216   dir varchar(80),
217   context varchar(80),
218   macrocontext varchar(80),
219   callerid varchar(40),
220   origtime varchar(40),
221   duration varchar(20),
222   mailboxuser varchar(80),
223   mailboxcontext varchar(80),
224   recording lo,
225   label varchar(30),
226   "read" bool DEFAULT false
227 );
228
229 --
230 -- Let's not forget to make the voicemessages table use the trigger
231 --
232
233 CREATE TRIGGER vm_cleanup AFTER DELETE OR UPDATE ON voicemessages FOR EACH ROW EXECUTE PROCEDURE vm_lo_cleanup();
234
235
236 10) Just as a sanity check, make sure you check the voicemessages table via the
237 isql utility.
238
239 [jsmith2@localhost ODBC]$ echo "SELECT id, msgnum, dir, duration FROM voicemessages WHERE msgnum = 1" | isql testing
240 +---------------------------------------+
241 | Connected!                            |
242 |                                       |
243 | sql-statement                         |
244 | help [tablename]                      |
245 | quit                                  |
246 |                                       |
247 +---------------------------------------+
248 SQL> +------------+------------+---------------------------------------------------------------------------------+---------------------+
249 | id         | msgnum     | dir                                                                             | duration            |
250 +------------+------------+---------------------------------------------------------------------------------+---------------------+
251 +------------+------------+---------------------------------------------------------------------------------+---------------------+
252 SQLRowCount returns 0
253
254
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.
259
260 [general]
261 ; Default formats for writing Voicemail
262 ;format=g723sf|wav49|wav
263 format=gsm
264 odbcstorage=postgres
265 odbctable=voicemessages
266
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:
270
271 [odbctest]
272 101 => 5555,Example Mailbox
273
274
275 12) Once you've updated voicemail.conf, let's make the changes take effect:
276
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
289
290 You can check to make sure your new mailbox exists by typing:
291
292 localhost*CLI> show voicemail users for odbctest 
293 Context    Mbox  User                      Zone       NewMsg
294 odbctest   101   Example Mailbox                           0
295
296
297 13) Now, let's add a new context called "odbc" to extensions.conf.  We'll use
298 these extensions to do some testing:
299
300 [odbc]
301 exten => 100,1,Voicemail(101@odbctest)
302 exten => 200,1,VoicemailMain(101@odbctest)
303
304
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:
309
310 [linksys]
311 type=friend
312 secret=verysecret
313 disallow=all
314 allow=ulaw
315 allow=gsm
316 context=odbc
317 host=dynamic
318 qualify=yes
319
320 I can check to see that my linksys phone is registered with Asterisk correctly:
321
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]
326
327
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:
332
333 localhost*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
342
343 Now, we can check the database and make sure the record actually made it into
344 PostgreSQL, from within the psql utility.
345
346 [jsmith2@localhost ~]$ psql
347 Password: 
348 Welcome to psql 8.1.4, the PostgreSQL interactive terminal.
349
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
354        \q to quit
355
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    |        |         |       
360 (1 row)
361
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:
367
368 jsmith2=# \lo_list
369     Large objects
370   ID   | Description 
371 -------+-------------
372  16599 | 
373 (1 row)
374
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.)
379
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:
382
383 jsmith2=# \lo_export 16599 /tmp/odcb-16599.gsm
384 lo_export
385
386 We can even listen to the file from the Linux command line:
387
388 [jsmith2@localhost tmp]$ play /tmp/odcb-16599.gsm
389
390 Input Filename : /tmp/odcb-16599.gsm
391 Sample Size    : 8-bits
392 Sample Encoding: gsm
393 Channels       : 1
394 Sample Rate    : 8000
395
396 Time: 00:06.22 [00:00.00] of 00:00.00 (  0.0%) Output Buffer: 298.36K
397
398 Done.
399
400
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:
404
405 localhost*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')
432
433 That's it!
434
435 Jared Smith
436 2 Jan 2006