Subscriptions

Latest revision as of 17:05, 7 April 2015

This module permit to configure the database account name and queries used to retrieve and update users' roster.
You can use database stored procedures to make sure that the update is done for the existing rows that were already added.

Note: All database queries are done in PostgreSQL.

[edit] Example create tables

CREATE TABLE users (
    username text NOT NULL,
    password text,
);

CREATE TABLE roster (
      username text NOT NULL,
      contact text NOT NULL,
      subscription text,
      name text,
      groups text
);

CREATE TABLE subscriptions (
      notifier text NOT NULL,
      event text NOT NULL,
      subscriber text
);

[edit] Configuration file

subscription.conf

; This file configures the subscriptions module

[general]

; account: string: The name of the database account
;account=

; user_event_auth: string: Database query used to authenticate specific event
;  (other then presence) subscriptions e.g. SIP 'dialog' or 'message-summary'
;user_event_auth=SELECT * FROM subscriptions WHERE notifier='${notifier}' AND \
;subscriber='${subscriber}' AND event='${event}'

; user_roster_load_all: string: Database query used to load all users' roster when starting
; If not set user rosters will be loaded when needed
;user_roster_load_all=SELECT users.username, roster.* FROM users LEFT OUTER JOIN roster ON users.username=roster.username

; user_roster_load: string: Database query used to load an user's roster
; NOTE: The query must return an 'username' column and at least one data row
;  containing the username whose roster we want to load in that column to signal
;  user's existence
;user_roster_load=SELECT users.username, roster.* FROM users LEFT OUTER JOIN roster ON users.username=roster.username \
;WHERE users.username='${username}'

; user_roster_delete: string: Database query used to delete an user's roster
;user_roster_delete=DELETE FROM roster WHERE username='${username}'

; contact_load: string: Database query used to load a specific contact
;contact_load=SELECT * FROM roster WHERE username='${username}' AND contact='${contact}'

; contact_subscription_set: string: Database query used to set a contact's subscription
; The query should add a new entry in the user roster or update an existing one
;contact_subscription_set=SELECT * FROM subscription_set('${username}','${contact}','${subscription}')

; contact_set: string: Database query used to add a new contact or update an existing one
;  in user roster. Updated data should not include the subscription state
;contact_set=SELECT * FROM roster_set('${username}','${contact}','${name}','${groups}') AS \
;t(username text,contact text,name text,groups text)

; contact_set_full: string: Database query used to add a new contact or update an existing one
;  in user roster. The query should include the subscription state also
;contact_set_full=SELECT * FROM roster_set_full('${username}','${contact}','${name}','${groups}','${subscription}') \
;AS t(username text,contact text,name text,groups text,subscription text)

; contact_delete: string: Database query used to delete a specific contact
;contact_delete=DELETE FROM roster WHERE username='${username}' AND contact='${contact}'

[edit] Example for stored procedures

[edit] subscription_set

Used to add or update a roster item subscription. Return values will be the added/updated row.

CREATE FUNCTION subscription_set(text, text, text) RETURNS text
    AS $_$
DECLARE
    _username ALIAS FOR $1;
    _contact ALIAS FOR $2;
    _sub ALIAS FOR $3;
    data record;
BEGIN
    SELECT INTO data * FROM roster WHERE username=_username AND contact=_contact;
    IF not found THEN
        INSERT INTO roster (username,contact,subscription) VALUES (_username,_contact,_sub);
    ELSE
        UPDATE roster SET subscription=_sub WHERE username=_username AND contact=_contact;
    END IF;
    return '''';
END;
$_$
    LANGUAGE plpgsql;

[edit] roster_set

Used to add or update a roster item. Returns added/updated row.

CREATE FUNCTION roster_set(text, text, text, text) RETURNS SETOF record
   AS $_$
DECLARE
    _username ALIAS FOR $1;
    _contact ALIAS FOR $2;
    _name ALIAS FOR $3;
    _groups ALIAS FOR $4;
    data record;
BEGIN
    SELECT INTO data * FROM roster WHERE username=_username AND contact=_contact;
    IF not found THEN
        INSERT INTO roster (username,contact,name,groups) VALUES (_username,_contact,_name,_groups);
    ELSE
        UPDATE roster SET name=_name, groups=_groups WHERE username=_username AND contact=_contact;
    END IF;
    SELECT INTO data username,contact,name,groups FROM roster WHERE username=_username AND contact=_contact;
    return next data;
    return;
END;
$_$
   LANGUAGE plpgsql;

[edit] roster_set_full

Used to fully add or update a roster item (including subscription). Return added/updated row.

CREATE FUNCTION roster_set_full(text, text, text, text, text) RETURNS SETOF record
    AS $_$
DECLARE
    _username ALIAS FOR $1;
    _contact ALIAS FOR $2;
    _name ALIAS FOR $3;
    _groups ALIAS FOR $4;
    _sub ALIAS FOR $5;
    data record;
BEGIN
    SELECT INTO data * FROM roster WHERE username=_username AND contact=_contact;
    IF not found THEN
       INSERT INTO roster (username,contact,name,groups,subscription) VALUES (_username,_contact,_name,_groups,_sub);
   ELSE
       UPDATE roster SET name=_name, groups=_groups, subscription=_sub WHERE username=_username AND contact=_contact;
   END IF;
   SELECT INTO data username,contact,name,groups,subscription FROM roster WHERE username=_username AND contact=_contact;
   return next data;
   return;
END;
$_$
    LANGUAGE plpgsql;

See also

• Telephony
• Modules