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