SIP in higher education

["Karl A. Krueger" <>]

I've attached the database design document and the corresponding SQL
DDL for the PostgreSQL database we're using for SER, Asterisk voice
mail, and phone configuration building.

The DDL is basically portable, with two exceptions:  First, there is a
little use of PostgreSQL rewrite rules (CREATE RULE statements) which
would have to be translated into SQL triggers.  Second, PostgreSQL
natively supports MAC addresses as a data type (MACADDR), which most
DBMSes do not -- they would need to be changed to VARCHAR or something
less truth-preserving in MySQL or the like.

Some other notes -- This is not a "read only" database.  Asterisk and
SER both need to write to it.  SER writes to the location table, while
Asterisk voicemail requires to be able to update people's voice mail
passwords.

-- 
Karl A. Krueger 
<>
Network Security -- Linux/Unix Systems Support -- Etc.
Woods Hole Oceanographic Institution

VOICE-OVER-IP DATABASE DESIGN
10 February 2005

The VoIP system stores several kinds of user information in a
PostgreSQL database hosted on mabell.whoi.edu.  This includes
information on:

        * Extensions (phone numbers)
        * Users (people)
        * Voice mail accounts
        * Telephones (SIP hard phones)

The "voip" database is owned by the "voip" PostgreSQL user, and is
accessed by THREE major software systems:

        * Asterisk "Comedian Mail" (app_voicemail.so)
        * SIP Express Router (SER)
        * Phone Configuration Builder (phonebuilder.pl)

Some of the database tables (such as "users" and "extensions") are used
by all of these applications.  Others (such as "extensions_ser_flags"
and "phones") are used by only one.  It is much more important to
protect the data integrity of the shared tables, as wayward modification
of these can break the whole VoIP system.  However, it is the shared
tables that must be written to by user-provisioning software.

The tables are as follows:

        * users                         (central)
        * extensions                    (central)
        * extension_voicemail           (voicemail, semi-dynamic)
        * extension_ser_flags           (ser)
        * location                      (ser, dynamic)
        * aliases                       (ser, dynamic, unused?)
        * phone_models                  (phone)
        * phones                        (phone)
        * phone_extensions              (phone)
        * phone_misc                    (phone)

There are also the following views (derived tables):

        * voicemail_users -- connects extensions to user
        * voicemail -- connects extension_voicemail to voicemail_users
        * ser_extensions -- connects extensions to extension_ser_flags
        * subscriber -- connects ser_extensions to users

The chief role of these views is to massage the relatively normalized
data in the main tables into the specific formats demanded by the
Asterisk and SER applications -- the "voicemail" and "subscriber" views,
specifically.  These are both basically data products, but there is a
rewrite rule to allow updates to "voicemail" to propagate back to
"extension_voicemail" so that Asterisk can update passwords for voice
mail accounts.

TABLE DETAIL

users -- the list of human beings (and roles) who use VoIP
        user_id -- a username; for people should be their LDAP uid
        first_name -- person's first name
        last_name -- person's last name
        email_address -- person's full email addrses
        pager -- person's pager (unused)
        time_created -- creation timestamp
        time_modified -- modification timestamp (not yet used)

extensions -- the list of valid VoIP extensions
        extension -- an extension name or number.  Any extension listed
                here will have voice mail and be able to register
                against SER, except as noted below.
        user_id -- OPTIONAL user (in the users table) who is associated
                with this extension.  If there is no user associated
                with an extension, it will not get voice mail email.
        sip_password -- SIP registration password for the user.
        sip_domain -- should be "whoi.edu" for almost everything.  May
                be set to "no_sip" to exclude from the subscriber table.
        time_created -- creation timestamp
        time_modified -- modification timestamp (not yet used)

extension_voicemail -- the list of valid voice mail accounts
        extension -- an extension number; the voice mail account number.
                DOES NOT have to be an extension in the extensions
                table; it is possible to have a voice mail account that
                does not have a SIP extension.  Should usually be
                numeric.
        context -- the Asterisk voice mail context.  Currently it seems
                that this must be "default".  If this is set to "no_vm"
                it means the account has no voice mail.
        vm_password -- the voice mail password.  For tolerably obvious
                reasons, this needs to be numeric.
        vm_options -- any Asterisk voice mail options string for the
                user.
        
extension_ser_flags -- basically unused SER account flags
        This table is not documented because, frankly, I have no idea
        what most of it does -- and the parts I do know, are basically
        unused.  However, it needs to exist so that the subscriber table
        can be created, and in case any of these settings are actually
        relevant in the future.

location -- dynamic SER table for location tracking
        When an extension is registered with SER, SER puts the location
        information in here.  The interesting bit is the relation
        between the "extension" field and the "contact" field -- the
        latter specifies where the extension is registered (an IP
        address and possibly other data).  Mine this to get the IP
        addresses of registered devices.  The rest is internal SER stuff
        that isn't too relevant to the administrator.

aliases -- dynamic SER table, identical to "location", unused?
        SER requires the existence of this table but does not seem to
        actually use it in our current configuration.  It has the same
        table layout as "location".

phone_models -- known types of SIP hard phones.
        model -- name of the model.  Currently 'snom190', 'cisco7940',
                and 'grandstream100' are listed.
        lines -- number of lines it can have.

phones -- known SIP hard phones
        phone_mac -- the Ethernet MAC address of a phone.
        user_id -- the user, if any, whose phone it is.  Used to set the
                display name on Cisco phones.
        model -- the brand and model of phone, from phone_models.

phone_extensions -- SIP extensions to place on each phone.
        This is an important one!  This is where you assign extensions
        to the hard phones.  It is really the core of the phone
        configuration system, since the interesting thing to do with
        phones is to have extensions ring on them.
        
        Extensions MUST be ordered, so that they will be mapped onto the
        right keys on the phone.  The order numbers you place here do
        not matter of themselves, but they have to be distinct.  You can
        have them be 100, 123, and 231 but they will still be mapped to
        the first three buttons on the phone.
        
        You can put as many extensions as you want on a phone here, but
        a Cisco 7940 only can accept two, and a SNOM 190 can only accept
        five (four, when one button is mapped to voice mail).
        
        phone_mac -- the MAC address of the phone.
        extension -- the extension to place on the phone.
        order -- the ordering of the extensions.

phone_misc -- "data-drivel------driven" phone configuration variables.
        This is for storing miscellaneous phone configuration lines,
        which will be dumped relatively raw into the phone configuration
        file.  Note that this is U_N_O_R_D_E_R_E_D_.
        phone_mac -- the MAC address yadda yadda.
        variable -- name of the config variable.
        value -- value of the config variable.


CREATE TABLE users (
        user_id         varchar(64)     primary key,
        first_name      varchar(25)     default '' not null,
        last_name       varchar(45)     default '' not null,
        email_address   varchar(50)     default '' not null,
        pager           varchar(79)     default '' not null,
        time_created    timestamp with time zone        default now(),
        time_modified   timestamp with time zone        default now()
);

CREATE RULE users_create_time AS ON INSERT TO users DO (
        UPDATE users SET time_created = now() WHERE user_id = new.user_id;
);

CREATE TABLE extensions (
        extension       varchar(64)     primary key,
        user_id         varchar(64)     references users,
        sip_password    varchar(25)     not null,
        sip_domain      varchar(128)    default 'whoi.edu' not null,
        time_created    timestamp with time zone        default now(),
        time_modified   timestamp with time zone        default now()
);

CREATE RULE extensions_create_time AS ON INSERT TO extensions DO
        UPDATE extensions SET time_created = now()
                WHERE extension = new.extension;
        
CREATE TABLE extension_ser_flags (
        extension       varchar(64)     references extensions
                                        on delete cascade
                                        on update cascade,
        phplib_id       varchar(32)     default '' not null,
        confirmation    varchar(64)     default '' not null,
        flag            char(1)         default 'o' not null,
        sendnotification        varchar(50)     default '' not null,
        greeting        varchar(50)     default '' not null,
        ha1             varchar(128)    default '' not null,
        ha1b            varchar(128)    default '' not null,
        perms           varchar(32),
        allow_find      char(1)         default '0' not null,
        timezone        varchar(128),
        rpid            varchar(128)
);

CREATE VIEW ser_extensions AS
        SELECT * FROM extensions NATURAL JOIN extension_ser_flags
        WHERE extensions.sip_domain != 'no_sip';

CREATE RULE extension_creates_ser_flags AS ON INSERT to extensions DO
        INSERT INTO extension_ser_flags ( extension ) VALUES ( new.extension 
);

CREATE TABLE extension_voicemail (
        extension       varchar(64)     unique not null,
        context         varchar(79)     not null default 'default',
        vm_password     varchar(25)     not null,
        vm_options      varchar(159)    not null default '',
        CONSTRAINT ev_pkey PRIMARY KEY ( extension, context )
);

CREATE VIEW voicemail_users AS SELECT 
        extensions.extension            AS extension,
        extensions.sip_domain           AS sip_domain,
        users.user_id                   AS user_id,
        users.email_address             AS email_address,
        users.pager                     AS pager,
        users.first_name || ' ' || users.last_name      AS full_name,
        users.time_modified             AS time_modified
FROM extensions LEFT JOIN users ON extensions.user_id = users.user_id;

CREATE VIEW voicemail AS SELECT
        extension_voicemail.context     AS context,
        extension_voicemail.extension   AS mailbox,
        extension_voicemail.vm_password AS password,
        voicemail_users.full_name       AS fullname,
        voicemail_users.email_address   AS email,
        voicemail_users.pager           AS pager,
        extension_voicemail.vm_options  AS options,
        voicemail_users.time_modified   AS datetime_modified
FROM extension_voicemail LEFT JOIN voicemail_users
        ON extension_voicemail.extension = voicemail_users.extension
        WHERE extension_voicemail.context != 'no_vm';

CREATE RULE update_voicemail AS ON UPDATE TO voicemail DO INSTEAD (
        UPDATE extension_voicemail SET
                context         = new.context,
                extension       = new.extension,
                vm_password     = new.password
        WHERE extension_voicemail.extension = old.extension;
);

CREATE VIEW subscriber AS SELECT
        ser_extensions.phplib_id        AS phplib_id,
        ser_extensions.extension        AS user_id,
        ser_extensions.sip_domain       AS domain,
        ser_extensions.sip_password     AS password,
        users.first_name                AS first_name,
        users.last_name                 AS last_name,
        ser_extensions.extension        AS phone,
        users.email_address             AS email_address,
        timestamptz_smaller(users.time_created, ser_extensions.time_created)
                                        AS datetime_created,
        timestamptz_larger(users.time_modified, ser_extensions.time_modified)
                                        AS datetime_modified,
        ser_extensions.confirmation     AS confirmation,
        ser_extensions.flag             AS flag,
        ser_extensions.greeting         AS greeting,
        ser_extensions.ha1              AS ha1,
        ser_extensions.ha1b             AS ha1b,
        ser_extensions.perms            AS perms,
        ser_extensions.allow_find       AS allow_find,
        ser_extensions.timezone         AS timezone,
        ser_extensions.rpid             AS rpid
FROM ser_extensions LEFT JOIN users
        ON ser_extensions.user_id = users.user_id;

CREATE TABLE location (
        user_id         varchar(64)     references extensions,
        domain          varchar(128)    default 'whoi.edu' not null,
        contact         varchar(255)    default '' not null,
        expires         timestamp with time zone,
        q               numeric(10,2),
        callid          varchar(255),
        cseq            numeric(11,0),
        last_modified   timestamp with time zone default now() not null,
        replicate       numeric(10,0),
        state           numeric(3,0),
        flags           numeric(11,0)   not null default '0',
        user_agent      varchar(128)    not null default ''
);

CREATE TABLE aliases (
        user_id         varchar(64)     references extensions,
        domain          varchar(128)    default 'whoi.edu' not null,
        contact         varchar(255)    default '' not null,
        expires         timestamp with time zone,
        q               numeric(10,2),
        callid          varchar(255),
        cseq            numeric(11,0),
        last_modified   timestamp with time zone default now() not null,
        replicate       numeric(10,0),
        state           numeric(3,0),
        flags           numeric(11,0)   not null default '0',
        user_agent      varchar(128)    not null default ''
);

CREATE TABLE phone_models (
        model           varchar(32)     primary key,
        lines           smallint        not null
);

CREATE TABLE phones (
        phone_mac       macaddr         primary key,
        user_id         varchar(64)     references users,
        model           varchar(32)     references phone_models
                                        on update cascade
);

CREATE TABLE phone_extensions (
        phone_mac       macaddr         references phones not null
                                        on update cascade,
        extension       varchar(64)     references extensions not null,
        ordering        smallint        not null default '0',
        CONSTRAINT extension_order_pkey PRIMARY KEY ( phone_mac, ordering )
);

CREATE TABLE phone_misc (
        phone_mac       macaddr         references phones not null
                                        on update cascade,
        variable        varchar(128)    not null,
        value           varchar(256)
);