Add flag here, too (as requested by jsmith)
[asterisk/asterisk.git] / doc / voicemail_odbc_postgresql.txt
1 GETTING ODBC STORAGE WITH POSTGRESQL WORKING WITH VOICEMAIL
2
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.
5
6 2) Log into your server as root, and then type:
7
8 [root@localhost ~]# su - postgres
9
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
12 new prompt, type:
13
14 $ createuser -s -D -R -l -P -e asterisk
15 Enter password for new role: 
16 Enter it again: 
17
18 Obviously you should enter a password when prompted.  This creates the
19 database role (or user).
20
21 Next we need to create the asterisk database.  Type:
22
23 $ createdb -O asterisk -e asterisk
24
25 This creates the database and sets the owner of the database to the asterisk
26 role.
27
28
29 Next, make sure that
30 you are using md5 authentication for the database user.  The line in my
31 /var/lib/pgsql/data/pg_hba.conf looks like:
32
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
38
39 As soon as you're done editing that file, log out as the postgres user.
40
41 3) Make sure you have the PostgreSQL odbc driver setup in /etc/odbcinst.ini.
42 Mine looks like:
43
44 [PostgreSQL]
45 Description     = ODBC for PostgreSQL
46 Driver          = /usr/lib/libodbcpsql.so
47 Setup           = /usr/lib/libodbcpsqlS.so
48 FileUsage       = 1
49
50 You can confirm that unixODBC is seeing the driver by typing:
51
52 [jsmith2@localhost tmp]$ odbcinst -q -d
53 [PostgreSQL]
54
55
56 4) Setup a DSN in /etc/odbc.ini, pointing at the PostgreSQL database and
57 driver.  Mine looks like:
58
59 [testing]
60 Description           = ODBC Testing
61 Driver                = PostgreSQL
62 Trace                 = No
63 TraceFile             = sql.log
64 Database              = asterisk
65 Servername            = 127.0.0.1
66 UserName              = asterisk
67 Password              = supersecret
68 Port                  = 5432
69 ReadOnly              = No
70 RowVersioning         = No
71 ShowSystemTables      = No
72 ShowOidColumn         = No
73 FakeOidIndex          = No
74 ConnSettings          =
75
76 You can confirm that unixODBC sees your DSN by typing:
77
78 [jsmith2@localhost tmp]$ odbcinst -q -s
79 [testing]
80
81
82 5) Test your database connectivity through ODBC.  If this doesn't work,
83 something is wrong with your ODBC setup.
84
85 [jsmith2@localhost tmp]$ echo "select 1" | isql -v testing
86 +---------------------------------------+
87 | Connected!                            |
88 |                                       |
89 | sql-statement                         |
90 | help [tablename]                      |
91 | quit                                  |
92 |                                       |
93 +---------------------------------------+
94 SQL> +------------+
95 | ?column?   |
96 +------------+
97 | 1          |
98 +------------+
99 SQLRowCount returns 1
100 1 rows fetched
101
102 If your ODBC connectivity to PostgreSQL isn't working, you'll see an error
103 message instead, like this:
104
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
110
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
115
116 Recompile Asterisk and install the new version.
117
118
119 7) Once you've recompiled and re-installed Asterisk, check to make sure
120 res_odbc.so has been compiled.
121
122 localhost*CLI> show modules like res_odbc.so
123 Module                         Description                              Use Count 
124 res_odbc.so                    ODBC Resource                            0         
125 1 modules loaded
126
127
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:
130
131 [postgres]
132 enabled => yes
133 dsn => testing
134 pre-connect => yes
135
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
140 ODBC.
141
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.
154
155 You can also check the status of your ODBC connection at any time from the
156 Asterisk CLI:
157
158 localhost*CLI> odbc show
159 Name: postgres 
160 DSN: testing
161 Connected: yes
162
163 10) Now we can setup our voicemail table in PostgreSQL.  Log into PostgreSQL and
164 type (or copy and paste) the following:
165
166 --
167 -- First, let's create our large object type, called "lo"
168 --
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;
173
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;
177
178 --
179 -- If we're not already using plpgsql, then let's use it!
180 --
181 CREATE TRUSTED LANGUAGE plpgsql;
182
183 --
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
186 --
187
188 CREATE FUNCTION vm_lo_cleanup() RETURNS "trigger"
189     AS $$
190     declare
191       msgcount INTEGER;
192     begin
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';
198           return new;
199         end if;
200       end if;
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';
205           return new;
206         else
207           perform lo_unlink(old.recording);
208           if found then
209             raise notice 'Cleaning up the large object table';
210             return new;
211           else
212             raise exception 'Failed to cleanup the large object table';
213             return old;
214           end if;
215         end if;
216       else
217         raise notice 'No need to cleanup the large object table, no recording on old row';
218         return new;
219       end if;
220     end$$
221     LANGUAGE plpgsql;
222
223 --
224 -- Now, let's create our voicemessages table
225 -- This is what holds the voicemail from Asterisk
226 --
227
228 CREATE TABLE voicemessages
229 (
230   uniqueid serial PRIMARY KEY,
231   msgnum int4,
232   dir varchar(80),
233   context varchar(80),
234   macrocontext varchar(80),
235   callerid varchar(40),
236   origtime varchar(40),
237   duration varchar(20),
238   flag varchar(8),
239   mailboxuser varchar(80),
240   mailboxcontext varchar(80),
241   recording lo,
242   label varchar(30),
243   "read" bool DEFAULT false
244 );
245
246 --
247 -- Let's not forget to make the voicemessages table use the trigger
248 --
249
250 CREATE TRIGGER vm_cleanup AFTER DELETE OR UPDATE ON voicemessages FOR EACH ROW EXECUTE PROCEDURE vm_lo_cleanup();
251
252
253 11) Just as a sanity check, make sure you check the voicemessages table via the
254 isql utility.
255
256 [jsmith2@localhost ODBC]$ echo "SELECT id, msgnum, dir, duration FROM voicemessages WHERE msgnum = 1" | isql testing
257 +---------------------------------------+
258 | Connected!                            |
259 |                                       |
260 | sql-statement                         |
261 | help [tablename]                      |
262 | quit                                  |
263 |                                       |
264 +---------------------------------------+
265 SQL> +------------+------------+---------------------------------------------------------------------------------+---------------------+
266 | id         | msgnum     | dir                                                                             | duration            |
267 +------------+------------+---------------------------------------------------------------------------------+---------------------+
268 +------------+------------+---------------------------------------------------------------------------------+---------------------+
269 SQLRowCount returns 0
270
271
272 12) Now we can finally configure voicemail in Asterisk to use our database.
273 Open /etc/asterisk/voicemail.conf, and look in the [general] section.  I've
274 changed the format to gsm (as I can't seem to get WAV or wav working), and
275 specify both the odbc connection and database table to use.
276
277 [general]
278 ; Default formats for writing Voicemail
279 ;format=g723sf|wav49|wav
280 format=gsm
281 odbcstorage=postgres
282 odbctable=voicemessages
283
284 You'll also want to create a new voicemail context called "odbctest" to do some
285 testing, and create a sample mailbox inside that context.  Add the following to
286 the very bottom of voicemail.conf:
287
288 [odbctest]
289 101 => 5555,Example Mailbox
290
291
292 13) Once you've updated voicemail.conf, let's make the changes take effect:
293
294 localhost*CLI> unload app_voicemail.so
295   == Unregistered application 'VoiceMail'
296   == Unregistered application 'VoiceMailMain'
297   == Unregistered application 'MailboxExists'
298   == Unregistered application 'VMAuthenticate'
299 localhost*CLI> load app_voicemail.so
300  Loaded /usr/lib/asterisk/modules/app_voicemail.so => (Comedian Mail (Voicemail System))
301   == Registered application 'VoiceMail'
302   == Registered application 'VoiceMailMain'
303   == Registered application 'MailboxExists'
304   == Registered application 'VMAuthenticate'
305   == Parsing '/etc/asterisk/voicemail.conf': Found
306
307 You can check to make sure your new mailbox exists by typing:
308
309 localhost*CLI> show voicemail users for odbctest 
310 Context    Mbox  User                      Zone       NewMsg
311 odbctest   101   Example Mailbox                           0
312
313
314 14) Now, let's add a new context called "odbc" to extensions.conf.  We'll use
315 these extensions to do some testing:
316
317 [odbc]
318 exten => 100,1,Voicemail(101@odbctest)
319 exten => 200,1,VoicemailMain(101@odbctest)
320
321
322 15) Next, we need to point a phone at the odbc context.  In my case, I've got a
323 SIP phone called "linksys" that is registering to Asterisk, so I'm setting its
324 context to the [odbc] context we created in the previous step.  The relevant
325 section of my sip.conf file looks like:
326
327 [linksys]
328 type=friend
329 secret=verysecret
330 disallow=all
331 allow=ulaw
332 allow=gsm
333 context=odbc
334 host=dynamic
335 qualify=yes
336
337 I can check to see that my linksys phone is registered with Asterisk correctly:
338
339 localhost*CLI> sip show peers like linksys
340 Name/username              Host            Dyn Nat ACL Port     Status    
341 linksys/linksys            192.168.0.103    D          5060     OK (9 ms) 
342 1 sip peers [1 online , 0 offline]
343
344
345 16) At last, we're finally ready to leave a voicemail message and have it
346 stored in our database!  (Who'd have guessed it would be this much trouble?!?)
347 Pick up the phone, dial extension 100, and leave yourself a voicemail message.
348 In my case, this is what appeared on the Asterisk CLI:
349
350 localhost*CLI> 
351     -- Executing VoiceMail("SIP/linksys-10228cac", "101@odbctest") in new stack
352     -- Playing 'vm-intro' (language 'en')
353     -- Playing 'beep' (language 'en')
354     -- Recording the message
355     -- x=0, open writing:  /var/spool/asterisk/voicemail/odbctest/101/tmp/dlZunm format: gsm, 0x101f6534
356     -- User ended message by pressing #
357     -- Playing 'auth-thankyou' (language 'en')
358   == Parsing '/var/spool/asterisk/voicemail/odbctest/101/INBOX/msg0000.txt': Found
359
360 Now, we can check the database and make sure the record actually made it into
361 PostgreSQL, from within the psql utility.
362
363 [jsmith2@localhost ~]$ psql
364 Password: 
365 Welcome to psql 8.1.4, the PostgreSQL interactive terminal.
366
367 Type:  \copyright for distribution terms
368        \h for help with SQL commands
369        \? for help with psql commands
370        \g or terminate with semicolon to execute query
371        \q to quit
372
373 asterisk=# SELECT * FROM voicemessages;
374  id | msgnum |                       dir                        | context | macrocontext |       callerid        |  origtime  | duration | mailboxuser | mailboxcontext | recording | label | read | sip_id | pabx_id | iax_id 
375 ----+--------+--------------------------------------------------+---------+--------------+-----------------------+------------+----------+-------------+----------------+-----------+-------+------+--------+---------+--------
376  26 |      0 | /var/spool/asterisk/voicemail/odbctest/101/INBOX | odbc    |              | "linksys" <linksys> | 1167794179 | 7        | 101         | odbctest       | 16599     |       | f    |        |         |       
377 (1 row)
378
379 Did you notice the the recording column is just a number?  When a recording
380 gets stuck in the database, the audio isn't actually stored in the
381 voicemessages table.  It's stored in a system table called the large object
382 table.  We can look in the large object table and verify that the object
383 actually exists there:
384
385 asterisk=# \lo_list
386     Large objects
387   ID   | Description 
388 -------+-------------
389  16599 | 
390 (1 row)
391
392 In my case, the OID is 16599.  Your OID will almost surely be different.  Just
393 make sure the OID number in the recording column in the voicemessages table
394 corresponds with a record in the large object table.  (The trigger we added to
395 our voicemessages table was designed to make sure this is always the case.)
396
397 We can also pull a copy of the voicemail message back out of the database and
398 write it to a file, to help us as we debug things:
399
400 asterisk=# \lo_export 16599 /tmp/odcb-16599.gsm
401 lo_export
402
403 We can even listen to the file from the Linux command line:
404
405 [jsmith2@localhost tmp]$ play /tmp/odcb-16599.gsm
406
407 Input Filename : /tmp/odcb-16599.gsm
408 Sample Size    : 8-bits
409 Sample Encoding: gsm
410 Channels       : 1
411 Sample Rate    : 8000
412
413 Time: 00:06.22 [00:00.00] of 00:00.00 (  0.0%) Output Buffer: 298.36K
414
415 Done.
416
417
418 17) Last but not least, we can pull the voicemail message back out of the
419 database by dialing extension 200 and entering "5555" at the password prompt.
420 You should see something like this on the Asterisk CLI:
421
422 localhost*CLI> 
423     -- Executing VoiceMailMain("SIP/linksys-10228cac", "101@odbctest") in new stack
424     -- Playing 'vm-password' (language 'en')
425     -- Playing 'vm-youhave' (language 'en')
426     -- Playing 'digits/1' (language 'en')
427     -- Playing 'vm-INBOX' (language 'en')
428     -- Playing 'vm-message' (language 'en')
429     -- Playing 'vm-onefor' (language 'en')
430     -- Playing 'vm-INBOX' (language 'en')
431     -- Playing 'vm-messages' (language 'en')
432     -- Playing 'vm-opts' (language 'en')
433     -- Playing 'vm-first' (language 'en')
434     -- Playing 'vm-message' (language 'en')
435   == Parsing '/var/spool/asterisk/voicemail/odbctest/101/INBOX/msg0000.txt': Found
436     -- Playing 'vm-received' (language 'en')
437     -- Playing 'digits/at' (language 'en')
438     -- Playing 'digits/10' (language 'en')
439     -- Playing 'digits/16' (language 'en')
440     -- Playing 'digits/p-m' (language 'en')
441     -- Playing '/var/spool/asterisk/voicemail/odbctest/101/INBOX/msg0000' (language 'en')
442     -- Playing 'vm-advopts' (language 'en')
443     -- Playing 'vm-repeat' (language 'en')
444     -- Playing 'vm-delete' (language 'en')
445     -- Playing 'vm-toforward' (language 'en')
446     -- Playing 'vm-savemessage' (language 'en')
447     -- Playing 'vm-helpexit' (language 'en')
448     -- Playing 'vm-goodbye' (language 'en')
449
450 That's it!
451
452 Jared Smith
453 2 Jan 2006
454 (updated 11 Mar 2007)