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)