aboutsummaryrefslogtreecommitdiffstats
path: root/doc/voicemail_odbc_postgresql.txt
blob: d2e5ab9f24aa99c33c29485c3d83ced2ec47a98d (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
GETTING ODBC STORAGE WITH POSTGRESQL WORKING WITH VOICEMAIL

1) Install PostgreSQL, PostgreSQL-devel, unixODBC, and unixODBC-devel, and
PostgreSQL-ODBC.  Make sure PostgreSQL is running and listening on a TCP socket.

2) Log into your server as root, and then type:

[root@localhost ~]# su - postgres

This will log you into the system as the "postgres" user, so that you can
create a new role and database within the PostgreSQL database system.  At the
new prompt, type:

$ createuser -s -D -R -l -P -e asterisk
Enter password for new role: 
Enter it again: 

Obviously you should enter a password when prompted.  This creates the
database role (or user).

Next we need to create the asterisk database.  Type:

$ createdb -O asterisk -e asterisk

This creates the database and sets the owner of the database to the asterisk
role.


Next, make sure that
you are using md5 authentication for the database user.  The line in my
/var/lib/pgsql/data/pg_hba.conf looks like:

# "local" is for Unix domain socket connections only
local   asterisk    asterisk                           md5
local   all         all                               ident sameuser
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5

As soon as you're done editing that file, log out as the postgres user.

3) Make sure you have the PostgreSQL odbc driver setup in /etc/odbcinst.ini.
Mine looks like:

[PostgreSQL]
Description     = ODBC for PostgreSQL
Driver          = /usr/lib/libodbcpsql.so
Setup           = /usr/lib/libodbcpsqlS.so
FileUsage       = 1

You can confirm that unixODBC is seeing the driver by typing:

[jsmith2@localhost tmp]$ odbcinst -q -d
[PostgreSQL]


4) Setup a DSN in /etc/odbc.ini, pointing at the PostgreSQL database and
driver.  Mine looks like:

[testing]
Description           = ODBC Testing
Driver                = PostgreSQL
Trace                 = No
TraceFile             = sql.log
Database              = asterisk
Servername            = 127.0.0.1
UserName              = asterisk
Password              = supersecret
Port                  = 5432
ReadOnly              = No
RowVersioning         = No
ShowSystemTables      = No
ShowOidColumn         = No
FakeOidIndex          = No
ConnSettings          =

You can confirm that unixODBC sees your DSN by typing:

[jsmith2@localhost tmp]$ odbcinst -q -s
[testing]


5) Test your database connectivity through ODBC.  If this doesn't work,
something is wrong with your ODBC setup.

[jsmith2@localhost tmp]$ echo "select 1" | isql -v testing
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> +------------+
| ?column?   |
+------------+
| 1          |
+------------+
SQLRowCount returns 1
1 rows fetched

If your ODBC connectivity to PostgreSQL isn't working, you'll see an error
message instead, like this:

[jsmith2@localhost tmp]$ echo "select 1" | isql -v testing
[S1000][unixODBC]Could not connect to the server;
Could not connect to remote socket.
[ISQL]ERROR: Could not SQLConnect
bash: echo: write error: Broken pipe

6) Compile Asterisk with support for ODBC voicemail.  Go to your Asterisk
source directory and run `make menuselect`.  Under "Voicemail Build Options",
enable "ODBC_STORAGE".
# See doc/README.odbcstorage for more information

Recompile Asterisk and install the new version.


7) Once you've recompiled and re-installed Asterisk, check to make sure
res_odbc.so has been compiled.

localhost*CLI> show modules like res_odbc.so
Module                         Description                              Use Count 
res_odbc.so                    ODBC Resource                            0         
1 modules loaded


8) Now it's time to get Asterisk configured.  First, we need to tell Asterisk
about our ODBC setup.  Open /etc/asterisk/res_odbc.conf and add the following:

[postgres]
enabled => yes
dsn => testing
pre-connect => yes

9) At the Asterisk CLI, unload and then load the res_odbc.so module.  (You
could restart Asterisk as well, but this way makes it easier to tell what's
happening.)  Notice how it says it's connected to "postgres", which is our ODBC
connection as defined in res_odbc.conf, which points to the "testing" DSN in
ODBC.

localhost*CLI> unload res_odbc.so
Jan  2 21:19:36 WARNING[8130]: res_odbc.c:498 odbc_obj_disconnect: res_odbc: disconnected 0 from postgres [testing]
Jan  2 21:19:36 NOTICE[8130]: res_odbc.c:589 unload_module: res_odbc unloaded.
localhost*CLI> load res_odbc.so
 Loaded /usr/lib/asterisk/modules/res_odbc.so => (ODBC Resource)
  == Parsing '/etc/asterisk/res_odbc.conf': Found
Jan  2 21:19:40 NOTICE[8130]: res_odbc.c:266 load_odbc_config: Adding ENV var: INFORMIXSERVER=my_special_database
Jan  2 21:19:40 NOTICE[8130]: res_odbc.c:266 load_odbc_config: Adding ENV var: INFORMIXDIR=/opt/informix
Jan  2 21:19:40 NOTICE[8130]: res_odbc.c:295 load_odbc_config: registered database handle 'postgres' dsn->[testing]
Jan  2 21:19:40 NOTICE[8130]: res_odbc.c:555 odbc_obj_connect: Connecting postgres
Jan  2 21:19:40 NOTICE[8130]: res_odbc.c:570 odbc_obj_connect: res_odbc: Connected to postgres [testing]
Jan  2 21:19:40 NOTICE[8130]: res_odbc.c:600 load_module: res_odbc loaded.

You can also check the status of your ODBC connection at any time from the
Asterisk CLI:

localhost*CLI> odbc show
Name: postgres 
DSN: testing
Connected: yes

10) Now we can setup our voicemail table in PostgreSQL.  Log into PostgreSQL and
type (or copy and paste) the following:

--
-- First, let's create our large object type, called "lo"
--
CREATE FUNCTION loin (cstring) RETURNS lo AS 'oidin' LANGUAGE internal IMMUTABLE STRICT;
CREATE FUNCTION loout (lo) RETURNS cstring AS 'oidout' LANGUAGE internal IMMUTABLE STRICT;
CREATE FUNCTION lorecv (internal) RETURNS lo AS 'oidrecv' LANGUAGE internal IMMUTABLE STRICT;
CREATE FUNCTION losend (lo) RETURNS bytea AS 'oidrecv' LANGUAGE internal IMMUTABLE STRICT;

CREATE TYPE lo ( INPUT = loin, OUTPUT = loout, RECEIVE = lorecv, SEND = losend, INTERNALLENGTH = 4, PASSEDBYVALUE );
CREATE CAST (lo AS oid) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (oid AS lo) WITHOUT FUNCTION AS IMPLICIT;

--
-- If we're not already using plpgsql, then let's use it!
--
CREATE TRUSTED LANGUAGE plpgsql;

--
-- Next, let's create a trigger to cleanup the large object table
-- whenever we update or delete a row from the voicemessages table
--

CREATE FUNCTION vm_lo_cleanup() RETURNS "trigger"
    AS $$
    declare
      msgcount INTEGER;
    begin
      --    raise notice 'Starting lo_cleanup function for large object with oid %',old.recording;
      -- If it is an update action but the BLOB (lo) field was not changed, dont do anything
      if (TG_OP = 'UPDATE') then
        if ((old.recording = new.recording) or (old.recording is NULL)) then
          raise notice 'Not cleaning up the large object table, as recording has not changed';
          return new;
        end if;
      end if;
      if (old.recording IS NOT NULL) then
        SELECT INTO msgcount COUNT(*) AS COUNT FROM voicemessages WHERE recording = old.recording;
        if (msgcount > 0) then
          raise notice 'Not deleting record from the large object table, as object is still referenced';
          return new;
        else
          perform lo_unlink(old.recording);
          if found then
            raise notice 'Cleaning up the large object table';
            return new;
          else
            raise exception 'Failed to cleanup the large object table';
            return old;
          end if;
        end if;
      else
        raise notice 'No need to cleanup the large object table, no recording on old row';
        return new;
      end if;
    end$$
    LANGUAGE plpgsql;

--
-- Now, let's create our voicemessages table
-- This is what holds the voicemail from Asterisk
--

CREATE TABLE voicemessages
(
  uniqueid serial PRIMARY KEY,
  msgnum int4,
  dir varchar(80),
  context varchar(80),
  macrocontext varchar(80),
  callerid varchar(40),
  origtime varchar(40),
  duration varchar(20),
  flag varchar(8),
  mailboxuser varchar(80),
  mailboxcontext varchar(80),
  recording lo,
  label varchar(30),
  "read" bool DEFAULT false
);

--
-- Let's not forget to make the voicemessages table use the trigger
--

CREATE TRIGGER vm_cleanup AFTER DELETE OR UPDATE ON voicemessages FOR EACH ROW EXECUTE PROCEDURE vm_lo_cleanup();


11) Just as a sanity check, make sure you check the voicemessages table via the
isql utility.

[jsmith2@localhost ODBC]$ echo "SELECT id, msgnum, dir, duration FROM voicemessages WHERE msgnum = 1" | isql testing
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> +------------+------------+---------------------------------------------------------------------------------+---------------------+
| id         | msgnum     | dir                                                                             | duration            |
+------------+------------+---------------------------------------------------------------------------------+---------------------+
+------------+------------+---------------------------------------------------------------------------------+---------------------+
SQLRowCount returns 0


12) Now we can finally configure voicemail in Asterisk to use our database.
Open /etc/asterisk/voicemail.conf, and look in the [general] section.  I've
changed the format to gsm (as I can't seem to get WAV or wav working), and
specify both the odbc connection and database table to use.

[general]
; Default formats for writing Voicemail
;format=g723sf|wav49|wav
format=gsm
odbcstorage=postgres
odbctable=voicemessages

You'll also want to create a new voicemail context called "odbctest" to do some
testing, and create a sample mailbox inside that context.  Add the following to
the very bottom of voicemail.conf:

[odbctest]
101 => 5555,Example Mailbox


13) Once you've updated voicemail.conf, let's make the changes take effect:

localhost*CLI> unload app_voicemail.so
  == Unregistered application 'VoiceMail'
  == Unregistered application 'VoiceMailMain'
  == Unregistered application 'MailboxExists'
  == Unregistered application 'VMAuthenticate'
localhost*CLI> load app_voicemail.so
 Loaded /usr/lib/asterisk/modules/app_voicemail.so => (Comedian Mail (Voicemail System))
  == Registered application 'VoiceMail'
  == Registered application 'VoiceMailMain'
  == Registered application 'MailboxExists'
  == Registered application 'VMAuthenticate'
  == Parsing '/etc/asterisk/voicemail.conf': Found

You can check to make sure your new mailbox exists by typing:

localhost*CLI> show voicemail users for odbctest 
Context    Mbox  User                      Zone       NewMsg
odbctest   101   Example Mailbox                           0


14) Now, let's add a new context called "odbc" to extensions.conf.  We'll use
these extensions to do some testing:

[odbc]
exten => 100,1,Voicemail(101@odbctest)
exten => 200,1,VoicemailMain(101@odbctest)


15) Next, we need to point a phone at the odbc context.  In my case, I've got a
SIP phone called "linksys" that is registering to Asterisk, so I'm setting its
context to the [odbc] context we created in the previous step.  The relevant
section of my sip.conf file looks like:

[linksys]
type=friend
secret=verysecret
disallow=all
allow=ulaw
allow=gsm
context=odbc
host=dynamic
qualify=yes

I can check to see that my linksys phone is registered with Asterisk correctly:

localhost*CLI> sip show peers like linksys
Name/username              Host            Dyn Nat ACL Port     Status    
linksys/linksys            192.168.0.103    D          5060     OK (9 ms) 
1 sip peers [1 online , 0 offline]


16) At last, we're finally ready to leave a voicemail message and have it
stored in our database!  (Who'd have guessed it would be this much trouble?!?)
Pick up the phone, dial extension 100, and leave yourself a voicemail message.
In my case, this is what appeared on the Asterisk CLI:

localhost*CLI> 
    -- Executing VoiceMail("SIP/linksys-10228cac", "101@odbctest") in new stack
    -- Playing 'vm-intro' (language 'en')
    -- Playing 'beep' (language 'en')
    -- Recording the message
    -- x=0, open writing:  /var/spool/asterisk/voicemail/odbctest/101/tmp/dlZunm format: gsm, 0x101f6534
    -- User ended message by pressing #
    -- Playing 'auth-thankyou' (language 'en')
  == Parsing '/var/spool/asterisk/voicemail/odbctest/101/INBOX/msg0000.txt': Found

Now, we can check the database and make sure the record actually made it into
PostgreSQL, from within the psql utility.

[jsmith2@localhost ~]$ psql
Password: 
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

asterisk=# SELECT * FROM voicemessages;
 id | msgnum |                       dir                        | context | macrocontext |       callerid        |  origtime  | duration | mailboxuser | mailboxcontext | recording | label | read | sip_id | pabx_id | iax_id 
----+--------+--------------------------------------------------+---------+--------------+-----------------------+------------+----------+-------------+----------------+-----------+-------+------+--------+---------+--------
 26 |      0 | /var/spool/asterisk/voicemail/odbctest/101/INBOX | odbc    |              | "linksys" <linksys> | 1167794179 | 7        | 101         | odbctest       | 16599     |       | f    |        |         |       
(1 row)

Did you notice the the recording column is just a number?  When a recording
gets stuck in the database, the audio isn't actually stored in the
voicemessages table.  It's stored in a system table called the large object
table.  We can look in the large object table and verify that the object
actually exists there:

asterisk=# \lo_list
    Large objects
  ID   | Description 
-------+-------------
 16599 | 
(1 row)

In my case, the OID is 16599.  Your OID will almost surely be different.  Just
make sure the OID number in the recording column in the voicemessages table
corresponds with a record in the large object table.  (The trigger we added to
our voicemessages table was designed to make sure this is always the case.)

We can also pull a copy of the voicemail message back out of the database and
write it to a file, to help us as we debug things:

asterisk=# \lo_export 16599 /tmp/odcb-16599.gsm
lo_export

We can even listen to the file from the Linux command line:

[jsmith2@localhost tmp]$ play /tmp/odcb-16599.gsm

Input Filename : /tmp/odcb-16599.gsm
Sample Size    : 8-bits
Sample Encoding: gsm
Channels       : 1
Sample Rate    : 8000

Time: 00:06.22 [00:00.00] of 00:00.00 (  0.0%) Output Buffer: 298.36K

Done.


17) Last but not least, we can pull the voicemail message back out of the
database by dialing extension 200 and entering "5555" at the password prompt.
You should see something like this on the Asterisk CLI:

localhost*CLI> 
    -- Executing VoiceMailMain("SIP/linksys-10228cac", "101@odbctest") in new stack
    -- Playing 'vm-password' (language 'en')
    -- Playing 'vm-youhave' (language 'en')
    -- Playing 'digits/1' (language 'en')
    -- Playing 'vm-INBOX' (language 'en')
    -- Playing 'vm-message' (language 'en')
    -- Playing 'vm-onefor' (language 'en')
    -- Playing 'vm-INBOX' (language 'en')
    -- Playing 'vm-messages' (language 'en')
    -- Playing 'vm-opts' (language 'en')
    -- Playing 'vm-first' (language 'en')
    -- Playing 'vm-message' (language 'en')
  == Parsing '/var/spool/asterisk/voicemail/odbctest/101/INBOX/msg0000.txt': Found
    -- Playing 'vm-received' (language 'en')
    -- Playing 'digits/at' (language 'en')
    -- Playing 'digits/10' (language 'en')
    -- Playing 'digits/16' (language 'en')
    -- Playing 'digits/p-m' (language 'en')
    -- Playing '/var/spool/asterisk/voicemail/odbctest/101/INBOX/msg0000' (language 'en')
    -- Playing 'vm-advopts' (language 'en')
    -- Playing 'vm-repeat' (language 'en')
    -- Playing 'vm-delete' (language 'en')
    -- Playing 'vm-toforward' (language 'en')
    -- Playing 'vm-savemessage' (language 'en')
    -- Playing 'vm-helpexit' (language 'en')
    -- Playing 'vm-goodbye' (language 'en')

That's it!

Jared Smith
2 Jan 2006
(updated 11 Mar 2007)