Exit early instead of deciding to exit after processing the message.
[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 run `make menuselect`.  Under "Voicemail Build Options",
88 enable "ODBC_STORAGE".
89 # See doc/README.odbcstorage for more information
90
91 Recompile Asterisk and install the new version.
92
93
94 6) Once you've recompiled and re-installed Asterisk, check to make sure
95 res_odbc.so has been compiled.
96
97 localhost*CLI> show modules like res_odbc.so
98 Module                         Description                              Use Count 
99 res_odbc.so                    ODBC Resource                            0         
100 1 modules loaded
101
102
103 7) Now it's time to get Asterisk configured.  First, we need to tell Asterisk
104 about our ODBC setup.  Open /etc/asterisk/res_odbc.conf and add the following:
105
106 [postgres]
107 enabled => yes
108 dsn => testing
109 pre-connect => yes
110
111 8) At the Asterisk CLI, unload and then load the res_odbc.so module.  (You
112 could restart Asterisk as well, but this way makes it easier to tell what's
113 happening.)  Notice how it says it's connected to "postgres", which is our ODBC
114 connection as defined in res_odbc.conf, which points to the "testing" DSN in
115 ODBC.
116
117 localhost*CLI> unload res_odbc.so
118 Jan  2 21:19:36 WARNING[8130]: res_odbc.c:498 odbc_obj_disconnect: res_odbc: disconnected 0 from postgres [testing]
119 Jan  2 21:19:36 NOTICE[8130]: res_odbc.c:589 unload_module: res_odbc unloaded.
120 localhost*CLI> load res_odbc.so
121  Loaded /usr/lib/asterisk/modules/res_odbc.so => (ODBC Resource)
122   == Parsing '/etc/asterisk/res_odbc.conf': Found
123 Jan  2 21:19:40 NOTICE[8130]: res_odbc.c:266 load_odbc_config: Adding ENV var: INFORMIXSERVER=my_special_database
124 Jan  2 21:19:40 NOTICE[8130]: res_odbc.c:266 load_odbc_config: Adding ENV var: INFORMIXDIR=/opt/informix
125 Jan  2 21:19:40 NOTICE[8130]: res_odbc.c:295 load_odbc_config: registered database handle 'postgres' dsn->[testing]
126 Jan  2 21:19:40 NOTICE[8130]: res_odbc.c:555 odbc_obj_connect: Connecting postgres
127 Jan  2 21:19:40 NOTICE[8130]: res_odbc.c:570 odbc_obj_connect: res_odbc: Connected to postgres [testing]
128 Jan  2 21:19:40 NOTICE[8130]: res_odbc.c:600 load_module: res_odbc loaded.
129
130 You can also check the status of your ODBC connection at any time from the
131 Asterisk CLI:
132
133 localhost*CLI> odbc show
134 Name: postgres 
135 DSN: testing
136 Connected: yes
137
138 9) Now we can setup our voicemail table in PostgreSQL.  Log into PostgreSQL and
139 type (or copy and paste) the following:
140
141 --
142 -- First, let's create our large object type, called "lo"
143 --
144 CREATE FUNCTION loin (cstring) RETURNS lo AS 'oidin' LANGUAGE internal IMMUTABLE STRICT;
145 CREATE FUNCTION loout (lo) RETURNS cstring AS 'oidout' LANGUAGE internal IMMUTABLE STRICT;
146 CREATE FUNCTION lorecv (internal) RETURNS lo AS 'oidrecv' LANGUAGE internal IMMUTABLE STRICT;
147 CREATE FUNCTION losend (lo) RETURNS bytea AS 'oidrecv' LANGUAGE internal IMMUTABLE STRICT;
148
149 CREATE TYPE lo ( INPUT = loin, OUTPUT = loout, RECEIVE = lorecv, SEND = losend, INTERNALLENGTH = 4, PASSEDBYVALUE );
150 CREATE CAST (lo AS oid) WITHOUT FUNCTION AS IMPLICIT;
151 CREATE CAST (oid AS lo) WITHOUT FUNCTION AS IMPLICIT;
152
153 --
154 -- If we're not already using plpgsql, then let's use it!
155 --
156 CREATE TRUSTED LANGUAGE plpgsql;
157
158 --
159 -- Next, let's create a trigger to cleanup the large object table
160 -- whenever we update or delete a row from the voicemessages table
161 --
162
163 CREATE FUNCTION vm_lo_cleanup() RETURNS "trigger"
164     AS $$
165     declare
166       msgcount INTEGER;
167     begin
168       --    raise notice 'Starting lo_cleanup function for large object with oid %',old.recording;
169       -- If it is an update action but the BLOB (lo) field was not changed, dont do anything
170       if (TG_OP = 'UPDATE') then
171         if ((old.recording = new.recording) or (old.recording is NULL)) then
172           raise notice 'Not cleaning up the large object table, as recording has not changed';
173           return new;
174         end if;
175       end if;
176       if (old.recording IS NOT NULL) then
177         SELECT INTO msgcount COUNT(*) AS COUNT FROM voicemessages WHERE recording = old.recording;
178         if (msgcount > 0) then
179           raise notice 'Not deleting record from the large object table, as object is still referenced';
180           return new;
181         else
182           perform lo_unlink(old.recording);
183           if found then
184             raise notice 'Cleaning up the large object table';
185             return new;
186           else
187             raise exception 'Failed to cleanup the large object table';
188             return old;
189           end if;
190         end if;
191       else
192         raise notice 'No need to cleanup the large object table, no recording on old row';
193         return new;
194       end if;
195     end$$
196     LANGUAGE plpgsql;
197
198 --
199 -- Now, let's create our voicemessages table
200 -- This is what holds the voicemail from Asterisk
201 --
202
203 CREATE TABLE voicemessages
204 (
205   uniqueid serial PRIMARY KEY,
206   msgnum int4,
207   dir varchar(80),
208   context varchar(80),
209   macrocontext varchar(80),
210   callerid varchar(40),
211   origtime varchar(40),
212   duration varchar(20),
213   mailboxuser varchar(80),
214   mailboxcontext varchar(80),
215   recording lo,
216   label varchar(30),
217   "read" bool DEFAULT false
218 );
219
220 --
221 -- Let's not forget to make the voicemessages table use the trigger
222 --
223
224 CREATE TRIGGER vm_cleanup AFTER DELETE OR UPDATE ON voicemessages FOR EACH ROW EXECUTE PROCEDURE vm_lo_cleanup();
225
226
227 10) Just as a sanity check, make sure you check the voicemessages table via the
228 isql utility.
229
230 [jsmith2@localhost ODBC]$ echo "SELECT id, msgnum, dir, duration FROM voicemessages WHERE msgnum = 1" | isql testing
231 +---------------------------------------+
232 | Connected!                            |
233 |                                       |
234 | sql-statement                         |
235 | help [tablename]                      |
236 | quit                                  |
237 |                                       |
238 +---------------------------------------+
239 SQL> +------------+------------+---------------------------------------------------------------------------------+---------------------+
240 | id         | msgnum     | dir                                                                             | duration            |
241 +------------+------------+---------------------------------------------------------------------------------+---------------------+
242 +------------+------------+---------------------------------------------------------------------------------+---------------------+
243 SQLRowCount returns 0
244
245
246 11) Now we can finally configure voicemail in Asterisk to use our database.
247 Open /etc/asterisk/voicemail.conf, and look in the [general] section.  I've
248 changed the format to gsm (as I can't seem to get WAV or wav working), and
249 specify both the odbc connection and database table to use.
250
251 [general]
252 ; Default formats for writing Voicemail
253 ;format=g723sf|wav49|wav
254 format=gsm
255 odbcstorage=postgres
256 odbctable=voicemessages
257
258 You'll also want to create a new voicemail context called "odbctest" to do some
259 testing, and create a sample mailbox inside that context.  Add the following to
260 the very bottom of voicemail.conf:
261
262 [odbctest]
263 101 => 5555,Example Mailbox
264
265
266 12) Once you've updated voicemail.conf, let's make the changes take effect:
267
268 localhost*CLI> unload app_voicemail.so
269   == Unregistered application 'VoiceMail'
270   == Unregistered application 'VoiceMailMain'
271   == Unregistered application 'MailboxExists'
272   == Unregistered application 'VMAuthenticate'
273 localhost*CLI> load app_voicemail.so
274  Loaded /usr/lib/asterisk/modules/app_voicemail.so => (Comedian Mail (Voicemail System))
275   == Registered application 'VoiceMail'
276   == Registered application 'VoiceMailMain'
277   == Registered application 'MailboxExists'
278   == Registered application 'VMAuthenticate'
279   == Parsing '/etc/asterisk/voicemail.conf': Found
280
281 You can check to make sure your new mailbox exists by typing:
282
283 localhost*CLI> show voicemail users for odbctest 
284 Context    Mbox  User                      Zone       NewMsg
285 odbctest   101   Example Mailbox                           0
286
287
288 13) Now, let's add a new context called "odbc" to extensions.conf.  We'll use
289 these extensions to do some testing:
290
291 [odbc]
292 exten => 100,1,Voicemail(101@odbctest)
293 exten => 200,1,VoicemailMain(101@odbctest)
294
295
296 14) Next, we need to point a phone at the odbc context.  In my case, I've got a
297 SIP phone called "linksys" that is registering to Asterisk, so I'm setting its
298 context to the [odbc] context we created in the previous step.  The relevant
299 section of my sip.conf file looks like:
300
301 [linksys]
302 type=friend
303 secret=verysecret
304 disallow=all
305 allow=ulaw
306 allow=gsm
307 context=odbc
308 host=dynamic
309 qualify=yes
310
311 I can check to see that my linksys phone is registered with Asterisk correctly:
312
313 localhost*CLI> sip show peers like linksys
314 Name/username              Host            Dyn Nat ACL Port     Status    
315 linksys/linksys            192.168.0.103    D          5060     OK (9 ms) 
316 1 sip peers [1 online , 0 offline]
317
318
319 15) At last, we're finally ready to leave a voicemail message and have it
320 stored in our database!  (Who'd have guessed it would be this much trouble?!?)
321 Pick up the phone, dial extension 100, and leave yourself a voicemail message.
322 In my case, this is what appeared on the Asterisk CLI:
323
324 localhost*CLI> 
325     -- Executing VoiceMail("SIP/linksys-10228cac", "101@odbctest") in new stack
326     -- Playing 'vm-intro' (language 'en')
327     -- Playing 'beep' (language 'en')
328     -- Recording the message
329     -- x=0, open writing:  /var/spool/asterisk/voicemail/odbctest/101/tmp/dlZunm format: gsm, 0x101f6534
330     -- User ended message by pressing #
331     -- Playing 'auth-thankyou' (language 'en')
332   == Parsing '/var/spool/asterisk/voicemail/odbctest/101/INBOX/msg0000.txt': Found
333
334 Now, we can check the database and make sure the record actually made it into
335 PostgreSQL, from within the psql utility.
336
337 [jsmith2@localhost ~]$ psql
338 Password: 
339 Welcome to psql 8.1.4, the PostgreSQL interactive terminal.
340
341 Type:  \copyright for distribution terms
342        \h for help with SQL commands
343        \? for help with psql commands
344        \g or terminate with semicolon to execute query
345        \q to quit
346
347 jsmith2=# SELECT * FROM voicemessages;
348  id | msgnum |                       dir                        | context | macrocontext |       callerid        |  origtime  | duration | mailboxuser | mailboxcontext | recording | label | read | sip_id | pabx_id | iax_id 
349 ----+--------+--------------------------------------------------+---------+--------------+-----------------------+------------+----------+-------------+----------------+-----------+-------+------+--------+---------+--------
350  26 |      0 | /var/spool/asterisk/voicemail/odbctest/101/INBOX | odbc    |              | "linksys" <linksys> | 1167794179 | 7        | 101         | odbctest       | 16599     |       | f    |        |         |       
351 (1 row)
352
353 Did you notice the the recording column is just a number?  When a recording
354 gets stuck in the database, the audio isn't actually stored in the
355 voicemessages table.  It's stored in a system table called the large object
356 table.  We can look in the large object table and verify that the object
357 actually exists there:
358
359 jsmith2=# \lo_list
360     Large objects
361   ID   | Description 
362 -------+-------------
363  16599 | 
364 (1 row)
365
366 In my case, the OID is 16599.  Your OID will almost surely be different.  Just
367 make sure the OID number in the recording column in the voicemessages table
368 corresponds with a record in the large object table.  (The trigger we added to
369 our voicemessages table was designed to make sure this is always the case.)
370
371 We can also pull a copy of the voicemail message back out of the database and
372 write it to a file, to help us as we debug things:
373
374 jsmith2=# \lo_export 16599 /tmp/odcb-16599.gsm
375 lo_export
376
377 We can even listen to the file from the Linux command line:
378
379 [jsmith2@localhost tmp]$ play /tmp/odcb-16599.gsm
380
381 Input Filename : /tmp/odcb-16599.gsm
382 Sample Size    : 8-bits
383 Sample Encoding: gsm
384 Channels       : 1
385 Sample Rate    : 8000
386
387 Time: 00:06.22 [00:00.00] of 00:00.00 (  0.0%) Output Buffer: 298.36K
388
389 Done.
390
391
392 16) Last but not least, we can pull the voicemail message back out of the
393 database by dialing extension 200 and entering "5555" at the password prompt.
394 You should see something like this on the Asterisk CLI:
395
396 localhost*CLI> 
397     -- Executing VoiceMailMain("SIP/linksys-10228cac", "101@odbctest") in new stack
398     -- Playing 'vm-password' (language 'en')
399     -- Playing 'vm-youhave' (language 'en')
400     -- Playing 'digits/1' (language 'en')
401     -- Playing 'vm-INBOX' (language 'en')
402     -- Playing 'vm-message' (language 'en')
403     -- Playing 'vm-onefor' (language 'en')
404     -- Playing 'vm-INBOX' (language 'en')
405     -- Playing 'vm-messages' (language 'en')
406     -- Playing 'vm-opts' (language 'en')
407     -- Playing 'vm-first' (language 'en')
408     -- Playing 'vm-message' (language 'en')
409   == Parsing '/var/spool/asterisk/voicemail/odbctest/101/INBOX/msg0000.txt': Found
410     -- Playing 'vm-received' (language 'en')
411     -- Playing 'digits/at' (language 'en')
412     -- Playing 'digits/10' (language 'en')
413     -- Playing 'digits/16' (language 'en')
414     -- Playing 'digits/p-m' (language 'en')
415     -- Playing '/var/spool/asterisk/voicemail/odbctest/101/INBOX/msg0000' (language 'en')
416     -- Playing 'vm-advopts' (language 'en')
417     -- Playing 'vm-repeat' (language 'en')
418     -- Playing 'vm-delete' (language 'en')
419     -- Playing 'vm-toforward' (language 'en')
420     -- Playing 'vm-savemessage' (language 'en')
421     -- Playing 'vm-helpexit' (language 'en')
422     -- Playing 'vm-goodbye' (language 'en')
423
424 That's it!
425
426 Jared Smith
427 2 Jan 2006