Correo de voz en PostgreSQL

Nos conectamos nuevamente a nuestra base de datos.

asteriskpbx:/home/sistemas# psql -U asterisk -h localhost asteriskDB
Contraseña para usuario asterisk:
Bienvenido a psql 8.1.11, la terminal interactiva de PostgreSQL.

Digite:  \copyright para ver los términos de distribución
\h para ayuda de comandos SQL
\? para ayuda de comandos psql
\g o or termine con punto y coma para ejecutar una consulta
\q para salir

conexión SSL (cifrado: DHE-RSA-AES256-SHA, bits: 256)
asteriskDB=#

Ingresamos las siguientes sentencias para crear el tipo especial donde se almacenaran los mensajes.

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;

CREATE TRUSTED LANGUAGE plpgsql;

CREATE FUNCTION vm_lo_cleanup() RETURNS "trigger"
    AS $$
    declare
      msgcount INTEGER;
    begin
      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;

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),
mailboxuser varchar(80),
mailboxcontext varchar(80),
recording lo,
label varchar(30),
“read” bool DEFAULT false
);

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

asteriskDB=# \d voicemessages
Tabla «public.voicemessages»
Columna     |         Tipo          |                          Modificadores
—————-+———————–+——————————————————————
uniqueid       | integer               | not null default nextval(‘voicemessages_uniqueid_seq’::regclass)
msgnum         | integer               |
dir            | character varying(80) |
context        | character varying(80) |
macrocontext   | character varying(80) |
callerid       | character varying(40) |
origtime       | character varying(40) |
duration       | character varying(20) |
mailboxuser    | character varying(80) |
mailboxcontext | character varying(80) |
recording      | lo                    |
label          | character varying(30) |
read           | boolean               | default false
Índices:
«voicemessages_pkey» PRIMARY KEY, btree (uniqueid)
Triggers:
vm_cleanup AFTER DELETE OR UPDATE ON voicemessages FOR EACH ROW EXECUTE PROCEDURE vm_lo_cleanup()

asteriskDB=# \q

Verificamos que la conexión sea exitosa para lo anteriormente creado.

asteriskpbx:/home/sistemas# echo “SELECT uniqueid, msgnum, dir, duration FROM voicemessages WHERE msgnum = 1″ | isql asterisk-connector
+—————————————+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+—————————————+
SQL> +————+————+———————————————————————————+———————+
| uniqueid   | msgnum     | dir                                                                             | duration            |
+————+————+———————————————————————————+———————+
+————+————+———————————————————————————+———————+
SQLRowCount returns 0
SQL>

Agregamos las siguientes lineas en el archivo de configuración del correo de voz, donde especificamos el nombre del contexto de nuestra conexion y la tabla que contendra los mensajes, asi como la creacion de los buzones para los usuarios anteriormente creados.

asteriskpbx:/home/sistemas# vim /etc/asterisk/voicemail.conf

odbcstorage=asterisk
odbctable=voicemessages

[odbcmail]
5000 => 1984,Daneel,daneel@loquesea.com
5001 => 1984,Giskard,giskard@loquesea.com

Reiniciamos el modulo correspondiente,
*CLI> module unload app_voicemail.so
*CLI> module load app_voicemail.so

Visualizamos la creacion de los usuarios.

*CLI> voicemail show users for odbcmail
Context    Mbox  User                      Zone       NewMsg
odbcmail   5000  Daneel                                    0
odbcmail   5001  Giskard                                   0

Realizamos unos cambios a nuestro macro, para que despues de treinta segundos sonando salte al buzon de voz.
[macro-llamar]
exten => s,1,Dial(${ARG1},30)
exten => s,n,Goto(s-${DIALSTATUS},1)
exten => s-NOANSWER,1,Voicemail(${MACRO_EXTEN}@odbcmail,u)
exten => s-BUSY,n,Voicemail(${MACRO_EXTEN}@odbcmail,b)
exten => s-CHANUNAVAIL,1,Voicemail(${MACRO_EXTEN}@odbcmail,u)
exten => s-CONGESTION,n,Voicemail(${MACRO_EXTEN}@odbcmail,b)

Agregamos lo siguiente a nuestro contexto internal para poder acceder a los correos de voz.

exten => *86,1,VoiceMailMain(@odbcmail)