update documentation to reflect the changes in the way configure detects net-snmp.
[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   mailboxuser varchar(80),
239   mailboxcontext varchar(80),
240   recording lo,
241   label varchar(30),
242   "read" bool DEFAULT false
243 );
244
245 --
246 -- Let's not forget to make the voicemessages table use the trigger
247 --
248
249 CREATE TRIGGER vm_cleanup AFTER DELETE OR UPDATE ON voicemessages FOR EACH ROW EXECUTE PROCEDURE vm_lo_cleanup();
250
251
252 11) Just as a sanity check, make sure you check the voicemessages table via the
253 isql utility.
254
255 [jsmith2@localhost ODBC]$ echo "SELECT id, msgnum, dir, duration FROM voicemessages WHERE msgnum = 1" | isql testing
256 +---------------------------------------+
257 | Connected!                            |
258 |                                       |
259 | sql-statement                         |
260 | help [tablename]                      |
261 | quit                                  |
262 |                                       |
263 +---------------------------------------+
264 SQL> +------------+------------+---------------------------------------------------------------------------------+---------------------+
265 | id         | msgnum     | dir                                                                             | duration            |
266 +------------+------------+---------------------------------------------------------------------------------+---------------------+
267 +------------+------------+---------------------------------------------------------------------------------+---------------------+
268 SQLRowCount returns 0
269
270
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.
275
276 [general]
277 ; Default formats for writing Voicemail
278 ;format=g723sf|wav49|wav
279 format=gsm
280 odbcstorage=postgres
281 odbctable=voicemessages
282
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:
286
287 [odbctest]
288 101 => 5555,Example Mailbox
289
290
291 13) Once you've updated voicemail.conf, let's make the changes take effect:
292
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
305
306 You can check to make sure your new mailbox exists by typing:
307
308 localhost*CLI> show voicemail users for odbctest 
309 Context    Mbox  User                      Zone       NewMsg
310 odbctest   101   Example Mailbox                           0
311
312
313 14) Now, let's add a new context called "odbc" to extensions.conf.  We'll use
314 these extensions to do some testing:
315
316 [odbc]
317 exten => 100,1,Voicemail(101@odbctest)
318 exten => 200,1,VoicemailMain(101@odbctest)
319
320
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:
325
326 [linksys]
327 type=friend
328 secret=verysecret
329 disallow=all
330 allow=ulaw
331 allow=gsm
332 context=odbc
333 host=dynamic
334 qualify=yes
335
336 I can check to see that my linksys phone is registered with Asterisk correctly:
337
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]
342
343
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:
348
349 localhost*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
358
359 Now, we can check the database and make sure the record actually made it into
360 PostgreSQL, from within the psql utility.
361
362 [jsmith2@localhost ~]$ psql
363 Password: 
364 Welcome to psql 8.1.4, the PostgreSQL interactive terminal.
365
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
370        \q to quit
371
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    |        |         |       
376 (1 row)
377
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:
383
384 asterisk=# \lo_list
385     Large objects
386   ID   | Description 
387 -------+-------------
388  16599 | 
389 (1 row)
390
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.)
395
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:
398
399 asterisk=# \lo_export 16599 /tmp/odcb-16599.gsm
400 lo_export
401
402 We can even listen to the file from the Linux command line:
403
404 [jsmith2@localhost tmp]$ play /tmp/odcb-16599.gsm
405
406 Input Filename : /tmp/odcb-16599.gsm
407 Sample Size    : 8-bits
408 Sample Encoding: gsm
409 Channels       : 1
410 Sample Rate    : 8000
411
412 Time: 00:06.22 [00:00.00] of 00:00.00 (  0.0%) Output Buffer: 298.36K
413
414 Done.
415
416
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:
420
421 localhost*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')
448
449 That's it!
450
451 Jared Smith
452 2 Jan 2006
453 (updated 11 Mar 2007)