OpenSER to Asterisk Realtime Views for MySQL

This is the easiest way to integrate them, in the future I will change this to use groups.

USE openser;
ALTER TABLE subscriber
ADD vmail_password varchar(40) NULL,
ADD vmail BOOL DEFAULT TRUE;

CREATE DATABASE asterisk;
USE asterisk;

This allows asterisk to log in per your settings in the asterisk configuration.

GRANT ALL ON asterisk.* to asterisk@yourhostoriphere IDENTIFIED BY 'passwordhere';

This creates a view that maps every user with the vmail column set to true in openser.subscriber have access to voicemail.

CREATE VIEW voicemail AS
SELECT  phplib_id as uniqueid,
 username as customer_id,
 'default' as context,
 username as mailbox,
 vmail_password as password,
 CONCAT(first_name,' ',last_name) as fullname,
 email_address as email,
 NULL as pager,
 datetime_created as stamp 
FROM openser.subscriber  WHERE vmail = TRUE;

Note: for openser 1.2.0 I had to change “SELECT phplib_id as uniqueid”, to “SELECT ha1 as uniqueid” for this to work, as that table does not exist with the new db schema.

In this view, you can change the type from a static 'friend' to whatever you need this to be. You can do the same to other options.

CREATE VIEW sip AS
SELECT  username as name,
 username,
 'friend' as type,
 NULL as secret,
 'dynamic' as host,
 CONCAT(rpid, ' ','<',username,'>') as callerid,
 'default' as context,
 username as mailbox,
 'no' as nat,
 'no' as qualify,
 NULL as fromuser,
 NULL as authuser,
 NULL as fromdomain,
 NULL as insecure,
 'no' as canreinvite,
 NULL as disallow,
 NULL as allow,
 NULL as restrictcid,
 NULL as ipaddr,
 NULL as port,
 NULL as regseconds
FROM openser.subscriber;

Mail me at mikebwilliams@gmail.com.