voicemail_odbc_postgresql.txt 16 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437
  1. GETTING ODBC STORAGE WITH POSTGRESQL WORKING WITH VOICEMAIL
  2. 1) Install PostgreSQL, PostgreSQL-devel, unixODBC, and unixODBC-devel, and
  3. PostgreSQL-ODBC. Make sure PostgreSQL is listening on a TCP socket, and that
  4. you are using md5 authentication for the database user. The line in my
  5. pg_hba.conf looks like:
  6. # "local" is for Unix domain socket connections only
  7. local jsmith2 jsmith2 md5
  8. local all all ident sameuser
  9. # IPv4 local connections:
  10. host all all 127.0.0.1/32 md5
  11. 2) Make sure you have the PostgreSQL odbc driver setup in /etc/odbcinst.ini.
  12. Mine looks like:
  13. [PostgreSQL]
  14. Description = ODBC for PostgreSQL
  15. Driver = /usr/lib/libodbcpsql.so
  16. Setup = /usr/lib/libodbcpsqlS.so
  17. FileUsage = 1
  18. You can confirm that unixODBC is seeing the driver by typing:
  19. [jsmith2@localhost tmp]$ odbcinst -q -d
  20. [PostgreSQL]
  21. 3) Setup a DSN in /etc/odbc.ini, pointing at the PostgreSQL database and
  22. driver. Mine looks like:
  23. [testing]
  24. Description = ODBC Testing
  25. Driver = PostgreSQL
  26. Trace = No
  27. TraceFile = sql.log
  28. Database = jsmith2
  29. Servername = 127.0.0.1
  30. UserName = jsmith2
  31. Password = supersecret
  32. Port = 5432
  33. ReadOnly = No
  34. RowVersioning = No
  35. ShowSystemTables = No
  36. ShowOidColumn = No
  37. FakeOidIndex = No
  38. ConnSettings =
  39. You can confirm that unixODBC sees your DSN by typing:
  40. [jsmith2@localhost tmp]$ odbcinst -q -s
  41. [testing]
  42. 4) Test your database connectivity through ODBC. If this doesn't work,
  43. something is wrong with your ODBC setup.
  44. [jsmith2@localhost tmp]$ echo "select 1" | isql -v testing
  45. +---------------------------------------+
  46. | Connected! |
  47. | |
  48. | sql-statement |
  49. | help [tablename] |
  50. | quit |
  51. | |
  52. +---------------------------------------+
  53. SQL> +------------+
  54. | ?column? |
  55. +------------+
  56. | 1 |
  57. +------------+
  58. SQLRowCount returns 1
  59. 1 rows fetched
  60. If your ODBC connectivity to PostgreSQL isn't working, you'll see an error
  61. message instead, like this:
  62. [jsmith2@localhost tmp]$ echo "select 1" | isql -v testing
  63. [S1000][unixODBC]Could not connect to the server;
  64. Could not connect to remote socket.
  65. [ISQL]ERROR: Could not SQLConnect
  66. bash: echo: write error: Broken pipe
  67. 5) Compile Asterisk with support for ODBC voicemail. Go to your Asterisk
  68. source directory and edit apps/Makefile, and uncomment the two lines as shown
  69. below:
  70. #
  71. # If you have UnixODBC you can use ODBC voicemail
  72. # storage
  73. #
  74. # Uncomment to use ODBC storage
  75. CFLAGS+=-DUSE_ODBC_STORAGE
  76. # Uncomment for extended ODBC voicemail storage
  77. CFLAGS+=-DEXTENDED_ODBC_STORAGE
  78. # See doc/README.odbcstorage for more information
  79. Recompile Asterisk and install the new version.
  80. 6) Once you've recompiled and re-installed Asterisk, check to make sure
  81. res_odbc.so has been compiled.
  82. localhost*CLI> show modules like res_odbc.so
  83. Module Description Use Count
  84. res_odbc.so ODBC Resource 0
  85. 1 modules loaded
  86. 7) Now it's time to get Asterisk configured. First, we need to tell Asterisk
  87. about our ODBC setup. Open /etc/asterisk/res_odbc.conf and add the following:
  88. [postgres]
  89. enabled => yes
  90. dsn => testing
  91. pre-connect => yes
  92. 8) At the Asterisk CLI, unload and then load the res_odbc.so module. (You
  93. could restart Asterisk as well, but this way makes it easier to tell what's
  94. happening.) Notice how it says it's connected to "postgres", which is our ODBC
  95. connection as defined in res_odbc.conf, which points to the "testing" DSN in
  96. ODBC.
  97. localhost*CLI> unload res_odbc.so
  98. Jan 2 21:19:36 WARNING[8130]: res_odbc.c:498 odbc_obj_disconnect: res_odbc: disconnected 0 from postgres [testing]
  99. Jan 2 21:19:36 NOTICE[8130]: res_odbc.c:589 unload_module: res_odbc unloaded.
  100. localhost*CLI> load res_odbc.so
  101. Loaded /usr/lib/asterisk/modules/res_odbc.so => (ODBC Resource)
  102. == Parsing '/etc/asterisk/res_odbc.conf': Found
  103. Jan 2 21:19:40 NOTICE[8130]: res_odbc.c:266 load_odbc_config: Adding ENV var: INFORMIXSERVER=my_special_database
  104. Jan 2 21:19:40 NOTICE[8130]: res_odbc.c:266 load_odbc_config: Adding ENV var: INFORMIXDIR=/opt/informix
  105. Jan 2 21:19:40 NOTICE[8130]: res_odbc.c:295 load_odbc_config: registered database handle 'postgres' dsn->[testing]
  106. Jan 2 21:19:40 NOTICE[8130]: res_odbc.c:555 odbc_obj_connect: Connecting postgres
  107. Jan 2 21:19:40 NOTICE[8130]: res_odbc.c:570 odbc_obj_connect: res_odbc: Connected to postgres [testing]
  108. Jan 2 21:19:40 NOTICE[8130]: res_odbc.c:600 load_module: res_odbc loaded.
  109. You can also check the status of your ODBC connection at any time from the
  110. Asterisk CLI:
  111. localhost*CLI> odbc show
  112. Name: postgres
  113. DSN: testing
  114. Connected: yes
  115. 9) Now we can setup our voicemail table in PostgreSQL. Log into PostgreSQL and
  116. type (or copy and paste) the following:
  117. --
  118. -- First, let's create our large object type, called "lo"
  119. --
  120. CREATE FUNCTION loin (cstring) RETURNS lo AS 'oidin' LANGUAGE internal IMMUTABLE STRICT;
  121. CREATE FUNCTION loout (lo) RETURNS cstring AS 'oidout' LANGUAGE internal IMMUTABLE STRICT;
  122. CREATE FUNCTION lorecv (internal) RETURNS lo AS 'oidrecv' LANGUAGE internal IMMUTABLE STRICT;
  123. CREATE FUNCTION losend (lo) RETURNS bytea AS 'oidrecv' LANGUAGE internal IMMUTABLE STRICT;
  124. CREATE TYPE lo ( INPUT = loin, OUTPUT = loout, RECEIVE = lorecv, SEND = losend, INTERNALLENGTH = 4, PASSEDBYVALUE );
  125. CREATE CAST (lo AS oid) WITHOUT FUNCTION AS IMPLICIT;
  126. CREATE CAST (oid AS lo) WITHOUT FUNCTION AS IMPLICIT;
  127. --
  128. -- If we're not already using plpgsql, then let's use it!
  129. --
  130. CREATE TRUSTED LANGUAGE plpgsql;
  131. --
  132. -- Next, let's create a trigger to cleanup the large object table
  133. -- whenever we update or delete a row from the voicemessages table
  134. --
  135. CREATE FUNCTION vm_lo_cleanup() RETURNS "trigger"
  136. AS $$
  137. declare
  138. msgcount INTEGER;
  139. begin
  140. -- raise notice 'Starting lo_cleanup function for large object with oid %',old.recording;
  141. -- If it is an update action but the BLOB (lo) field was not changed, dont do anything
  142. if (TG_OP = 'UPDATE') then
  143. if ((old.recording = new.recording) or (old.recording is NULL)) then
  144. raise notice 'Not cleaning up the large object table, as recording has not changed';
  145. return new;
  146. end if;
  147. end if;
  148. if (old.recording IS NOT NULL) then
  149. SELECT INTO msgcount COUNT(*) AS COUNT FROM voicemessages WHERE recording = old.recording;
  150. if (msgcount > 0) then
  151. raise notice 'Not deleting record from the large object table, as object is still referenced';
  152. return new;
  153. else
  154. perform lo_unlink(old.recording);
  155. if found then
  156. raise notice 'Cleaning up the large object table';
  157. return new;
  158. else
  159. raise exception 'Failed to cleanup the large object table';
  160. return old;
  161. end if;
  162. end if;
  163. else
  164. raise notice 'No need to cleanup the large object table, no recording on old row';
  165. return new;
  166. end if;
  167. end$$
  168. LANGUAGE plpgsql;
  169. --
  170. -- Now, let's create our voicemessages table
  171. -- This is what holds the voicemail from Asterisk
  172. --
  173. CREATE TABLE voicemessages
  174. (
  175. uniqueid serial PRIMARY KEY,
  176. msgnum int4,
  177. dir varchar(80),
  178. context varchar(80),
  179. macrocontext varchar(80),
  180. callerid varchar(40),
  181. origtime varchar(40),
  182. duration varchar(20),
  183. mailboxuser varchar(80),
  184. mailboxcontext varchar(80),
  185. recording lo,
  186. label varchar(30),
  187. "read" bool DEFAULT false
  188. );
  189. --
  190. -- Let's not forget to make the voicemessages table use the trigger
  191. --
  192. CREATE TRIGGER vm_cleanup AFTER DELETE OR UPDATE ON voicemessages FOR EACH ROW EXECUTE PROCEDURE vm_lo_cleanup();
  193. 10) Just as a sanity check, make sure you check the voicemessages table via the
  194. isql utility.
  195. [jsmith2@localhost ODBC]$ echo "SELECT id, msgnum, dir, duration FROM voicemessages WHERE msgnum = 1" | isql testing
  196. +---------------------------------------+
  197. | Connected! |
  198. | |
  199. | sql-statement |
  200. | help [tablename] |
  201. | quit |
  202. | |
  203. +---------------------------------------+
  204. SQL> +------------+------------+---------------------------------------------------------------------------------+---------------------+
  205. | id | msgnum | dir | duration |
  206. +------------+------------+---------------------------------------------------------------------------------+---------------------+
  207. +------------+------------+---------------------------------------------------------------------------------+---------------------+
  208. SQLRowCount returns 0
  209. 11) Now we can finally configure voicemail in Asterisk to use our database.
  210. Open /etc/asterisk/voicemail.conf, and look in the [general] section. I've
  211. changed the format to gsm (as I can't seem to get WAV or wav working), and
  212. specify both the odbc connection and database table to use.
  213. [general]
  214. ; Default formats for writing Voicemail
  215. ;format=g723sf|wav49|wav
  216. format=gsm
  217. odbcstorage=postgres
  218. odbctable=voicemessages
  219. You'll also want to create a new voicemail context called "odbctest" to do some
  220. testing, and create a sample mailbox inside that context. Add the following to
  221. the very bottom of voicemail.conf:
  222. [odbctest]
  223. 101 => 5555,Example Mailbox
  224. 12) Once you've updated voicemail.conf, let's make the changes take effect:
  225. localhost*CLI> unload app_voicemail.so
  226. == Unregistered application 'VoiceMail'
  227. == Unregistered application 'VoiceMailMain'
  228. == Unregistered application 'MailboxExists'
  229. == Unregistered application 'VMAuthenticate'
  230. localhost*CLI> load app_voicemail.so
  231. Loaded /usr/lib/asterisk/modules/app_voicemail.so => (Comedian Mail (Voicemail System))
  232. == Registered application 'VoiceMail'
  233. == Registered application 'VoiceMailMain'
  234. == Registered application 'MailboxExists'
  235. == Registered application 'VMAuthenticate'
  236. == Parsing '/etc/asterisk/voicemail.conf': Found
  237. You can check to make sure your new mailbox exists by typing:
  238. localhost*CLI> show voicemail users for odbctest
  239. Context Mbox User Zone NewMsg
  240. odbctest 101 Example Mailbox 0
  241. 13) Now, let's add a new context called "odbc" to extensions.conf. We'll use
  242. these extensions to do some testing:
  243. [odbc]
  244. exten => 100,1,Voicemail(101@odbctest)
  245. exten => 200,1,VoicemailMain(101@odbctest)
  246. 14) Next, we need to point a phone at the odbc context. In my case, I've got a
  247. SIP phone called "linksys" that is registering to Asterisk, so I'm setting its
  248. context to the [odbc] context we created in the previous step. The relevant
  249. section of my sip.conf file looks like:
  250. [linksys]
  251. type=friend
  252. secret=verysecret
  253. disallow=all
  254. allow=ulaw
  255. allow=gsm
  256. context=odbc
  257. host=dynamic
  258. qualify=yes
  259. I can check to see that my linksys phone is registered with Asterisk correctly:
  260. localhost*CLI> sip show peers like linksys
  261. Name/username Host Dyn Nat ACL Port Status
  262. linksys/linksys 192.168.0.103 D 5060 OK (9 ms)
  263. 1 sip peers [1 online , 0 offline]
  264. 15) At last, we're finally ready to leave a voicemail message and have it
  265. stored in our database! (Who'd have guessed it would be this much trouble?!?)
  266. Pick up the phone, dial extension 100, and leave yourself a voicemail message.
  267. In my case, this is what appeared on the Asterisk CLI:
  268. localhost*CLI>
  269. -- Executing VoiceMail("SIP/linksys-10228cac", "101@odbctest") in new stack
  270. -- Playing 'vm-intro' (language 'en')
  271. -- Playing 'beep' (language 'en')
  272. -- Recording the message
  273. -- x=0, open writing: /var/spool/asterisk/voicemail/odbctest/101/tmp/dlZunm format: gsm, 0x101f6534
  274. -- User ended message by pressing #
  275. -- Playing 'auth-thankyou' (language 'en')
  276. == Parsing '/var/spool/asterisk/voicemail/odbctest/101/INBOX/msg0000.txt': Found
  277. Now, we can check the database and make sure the record actually made it into
  278. PostgreSQL, from within the psql utility.
  279. [jsmith2@localhost ~]$ psql
  280. Password:
  281. Welcome to psql 8.1.4, the PostgreSQL interactive terminal.
  282. Type: \copyright for distribution terms
  283. \h for help with SQL commands
  284. \? for help with psql commands
  285. \g or terminate with semicolon to execute query
  286. \q to quit
  287. jsmith2=# SELECT * FROM voicemessages;
  288. id | msgnum | dir | context | macrocontext | callerid | origtime | duration | mailboxuser | mailboxcontext | recording | label | read | sip_id | pabx_id | iax_id
  289. ----+--------+--------------------------------------------------+---------+--------------+-----------------------+------------+----------+-------------+----------------+-----------+-------+------+--------+---------+--------
  290. 26 | 0 | /var/spool/asterisk/voicemail/odbctest/101/INBOX | odbc | | "linksys" <linksys> | 1167794179 | 7 | 101 | odbctest | 16599 | | f | | |
  291. (1 row)
  292. Did you notice the the recording column is just a number? When a recording
  293. gets stuck in the database, the audio isn't actually stored in the
  294. voicemessages table. It's stored in a system table called the large object
  295. table. We can look in the large object table and verify that the object
  296. actually exists there:
  297. jsmith2=# \lo_list
  298. Large objects
  299. ID | Description
  300. -------+-------------
  301. 16599 |
  302. (1 row)
  303. In my case, the OID is 16599. Your OID will almost surely be different. Just
  304. make sure the OID number in the recording column in the voicemessages table
  305. corresponds with a record in the large object table. (The trigger we added to
  306. our voicemessages table was designed to make sure this is always the case.)
  307. We can also pull a copy of the voicemail message back out of the database and
  308. write it to a file, to help us as we debug things:
  309. jsmith2=# \lo_export 16599 /tmp/odcb-16599.gsm
  310. lo_export
  311. We can even listen to the file from the Linux command line:
  312. [jsmith2@localhost tmp]$ play /tmp/odcb-16599.gsm
  313. Input Filename : /tmp/odcb-16599.gsm
  314. Sample Size : 8-bits
  315. Sample Encoding: gsm
  316. Channels : 1
  317. Sample Rate : 8000
  318. Time: 00:06.22 [00:00.00] of 00:00.00 ( 0.0%) Output Buffer: 298.36K
  319. Done.
  320. 16) Last but not least, we can pull the voicemail message back out of the
  321. database by dialing extension 200 and entering "5555" at the password prompt.
  322. You should see something like this on the Asterisk CLI:
  323. localhost*CLI>
  324. -- Executing VoiceMailMain("SIP/linksys-10228cac", "101@odbctest") in new stack
  325. -- Playing 'vm-password' (language 'en')
  326. -- Playing 'vm-youhave' (language 'en')
  327. -- Playing 'digits/1' (language 'en')
  328. -- Playing 'vm-INBOX' (language 'en')
  329. -- Playing 'vm-message' (language 'en')
  330. -- Playing 'vm-onefor' (language 'en')
  331. -- Playing 'vm-INBOX' (language 'en')
  332. -- Playing 'vm-messages' (language 'en')
  333. -- Playing 'vm-opts' (language 'en')
  334. -- Playing 'vm-first' (language 'en')
  335. -- Playing 'vm-message' (language 'en')
  336. == Parsing '/var/spool/asterisk/voicemail/odbctest/101/INBOX/msg0000.txt': Found
  337. -- Playing 'vm-received' (language 'en')
  338. -- Playing 'digits/at' (language 'en')
  339. -- Playing 'digits/10' (language 'en')
  340. -- Playing 'digits/16' (language 'en')
  341. -- Playing 'digits/p-m' (language 'en')
  342. -- Playing '/var/spool/asterisk/voicemail/odbctest/101/INBOX/msg0000' (language 'en')
  343. -- Playing 'vm-advopts' (language 'en')
  344. -- Playing 'vm-repeat' (language 'en')
  345. -- Playing 'vm-delete' (language 'en')
  346. -- Playing 'vm-toforward' (language 'en')
  347. -- Playing 'vm-savemessage' (language 'en')
  348. -- Playing 'vm-helpexit' (language 'en')
  349. -- Playing 'vm-goodbye' (language 'en')
  350. That's it!
  351. Jared Smith
  352. 2 Jan 2006