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