Jbfeatures

From Yate Documentation
(Difference between revisions)
Jump to: navigation, search
(Created page with " === Configuration == File jbfeatures.conf [general] ; account: string: Default database account ; This parameter can be overridden in section using the database ;acco...")
 
(offlinechat_add)
 
(9 intermediate revisions by 2 users not shown)
Line 1: Line 1:
  
 +
Module jbfeatures implements Jabber server features:
 +
* roster
 +
* vcard
 +
* private data
 +
* offline messages
 +
* in stream user management (create, delete, modify).
  
=== Configuration ==
+
==Database storage and access configuration==
 +
 
 +
The module pgsqldb(or mysqdb) can be used to make the connection between Yate and one or more PostgreSQL(or MySQL) databases.
 +
 
 +
See [[PostgreSQL]](or [[MySQL]]) for more configuration options.
 +
 
 +
'''Note:''' All database queries are done with PostgreSQL.
 +
 
 +
== Create tables==
 +
 
 +
CREATE TABLE users (
 +
    username text NOT NULL,
 +
    password text,
 +
);
 +
 +
CREATE TABLE offlinechat (
 +
    username text NOT NULL,
 +
    xml text NOT NULL,
 +
    "time" integer NOT NULL
 +
);
 +
 
 +
CREATE TABLE privatedata (
 +
  username text NOT NULL,
 +
  tag text NOT NULL,
 +
  xmlns text NOT NULL,
 +
  xml text NOT NULL
 +
);
 +
 
 +
== Configuration file ==
  
 
File jbfeatures.conf
 
File jbfeatures.conf
  
 
  [general]
 
  [general]
 
+
 
  ; account: string: Default database account
 
  ; account: string: Default database account
 
  ; This parameter can be overridden in section using the database
 
  ; This parameter can be overridden in section using the database
Line 122: Line 156:
 
  ; clear_user: string: Query used to delete all offline messages belonging to an user
 
  ; clear_user: string: Query used to delete all offline messages belonging to an user
 
  ;clear_user=DELETE FROM offlinechat WHERE username='${username}'
 
  ;clear_user=DELETE FROM offlinechat WHERE username='${username}'
 +
 +
==Example of stored procedures==
 +
 +
===privatedata_set===
 +
 +
Used to add or update user's private data.
 +
CREATE FUNCTION privatedata_set(text, text, text, text) RETURNS text
 +
    AS $_$
 +
DECLARE
 +
    _username ALIAS FOR $1;
 +
    _tag ALIAS FOR $2;
 +
    _xmlns ALIAS FOR $3;
 +
    _xml ALIAS FOR $4;
 +
    data record;
 +
BEGIN
 +
    SELECT INTO data xml FROM privatedata WHERE username=_username AND tag=_tag AND xmlns=_xmlns;
 +
    IF not found THEN
 +
        INSERT INTO privatedata (username,tag,xmlns,xml) VALUES (_username,_tag,_xmlns,_xml);
 +
    ELSE
 +
        UPDATE privatedata SET xml=_xml WHERE username=_username AND tag=_tag AND xmlns=_xmlns;
 +
    END IF;
 +
    return <nowiki>''''</nowiki>;
 +
END;
 +
$_$
 +
    LANGUAGE plpgsql;
 +
 +
 +
===offlinechat_add===
 +
 +
CREATE FUNCTION offlinechat_add(text, text, integer, integer) RETURNS integer
 +
    AS $_$
 +
DECLARE
 +
    _username ALIAS FOR $1;
 +
    _xml ALIAS FOR $2;
 +
    _time ALIAS FOR $3;
 +
    _max ALIAS FOR $4;
 +
    n integer;
 +
BEGIN
 +
    SELECT INTO n COUNT(*) FROM users WHERE username=_username;
 +
    IF n > 0 THEN
 +
    n := 1;
 +
    IF _max > 0 THEN
 +
        SELECT INTO n COUNT(*) FROM offlinechat WHERE username=_username;
 +
        IF n < _max THEN
 +
            n := 1;
 +
        ELSE
 +
            n := 0;
 +
        END IF;
 +
    END IF;
 +
    IF n = 1 THEN
 +
        INSERT INTO offlinechat (username,xml,time) VALUES (_username,_xml,_time);
 +
    END IF;
 +
    END IF;
 +
    RETURN n;
 +
END;
 +
$_$
 +
    LANGUAGE plpgsql;
 +
 +
 +
'''See also'''
 +
 +
* [[Telephony]]
 +
 +
[[Category:Jabber]] [[Category:Server]] [[Category:Database]]

Latest revision as of 11:22, 4 November 2013

Module jbfeatures implements Jabber server features:

  • roster
  • vcard
  • private data
  • offline messages
  • in stream user management (create, delete, modify).

Contents

[edit] Database storage and access configuration

The module pgsqldb(or mysqdb) can be used to make the connection between Yate and one or more PostgreSQL(or MySQL) databases.

See PostgreSQL(or MySQL) for more configuration options.

Note: All database queries are done with PostgreSQL.

[edit] Create tables

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

CREATE TABLE offlinechat (
   username text NOT NULL,
   xml text NOT NULL,
   "time" integer NOT NULL
);
CREATE TABLE privatedata (
  username text NOT NULL,
  tag text NOT NULL,
  xmlns text NOT NULL,
  xml text NOT NULL
);

[edit] Configuration file

File jbfeatures.conf

[general]

; account: string: Default database account
; This parameter can be overridden in section using the database
;account=

; groups_separator: string: Roster item groups list separator
; This parameter is used as groups list separator in messages dispatched by the module
; Two hexadecimal digits can be specified (like 09 for TAB or 20 for SPACE)
; If a longer string is set, only the first character will be handled
; Defaults to comma (,) if missing, empty or invalid
;groups_separator=,

; ignore_invalid_groups: boolean: Ignore group names containing the groups separator
; Roster updates received from users will be refused if a group contains the separator and
;  ignore_invalid_groups is disabled
; This parameter is applied on reload
; Default to yes
;ignore_invalid_groups=yes

; roster_query_hierarchical: boolean: Signal hierarchical result support in
;  user.roster query messages
; A module handling the user.roster query message may set contact's parameters list
;  in each contact parameter instead of adding them to the message
; If enabled the performance will grow but parameter altering in external modules
;  will be impossible
; This parameter is applied on reload
; Defaults to yes
;roster_query_hierarchical=yes


[register]
; This section configures in stream user management (add, change, delete)

; allow_management: boolean: Enable user add/remove
; Defaults to yes
;allow_management=yes

; allow_change: boolean: Enable existing authenticated user to change its password
; Defaults to yes
;allow_change=yes
 
; allow_unsecure: boolean: Enable user management on unsecure connections
; Defaults to no
;allow_unsecure=no

; url: string: URL to be sent to the user when requesting registration data
;  if in stream registration is not enabled
;url=

; intructions: string: Instructions to be sent with the URL
;intructions=


[vcard]
; User vcard management

; get: string: Query executed on user vcard get request
;get=SELECT vcard FROM users WHERE username='${username}'

; set: string: Query executed on user vcard set request
; The query must update the user's vcard
; Care must be taken if the user vcard is kept in the same table with the user: avoid
; inserting items when the user is not found
;set=UPDATE users SET vcard VALUES ('${vcard}') WHERE username='${username}'

; clear_user: string: Query used to delete an user's vcard
; This query must be set if an user's vcard is not stored in the users table
;clear_user=
 
 
[private_data]
; User private data management
; User private data chunk key is given by xml tag and namespace
; Data should be kept in a separate table with a one-to-many relationship from the users table
  
; get: string: Query executed on user private data get request
;get=SELECT xml FROM privatedata WHERE username='${username}' AND tag='${tag}' AND xmlns='${xmlns}'
  
; set: string: Query executed on user private data set request
; The query must update existing data or insert it if not found
;set=SELECT * FROM privatedata_set('${username}', '${tag}', '${xmlns}', '${xml}')
  
; clear_user: string: Query used to delete all private data belonging to an user
;clear_user=DELETE FROM privatedata WHERE username='${username}'
[offline_chat]
; Offline messages management

; maxcount: integer: Maximum number of chat messages saved for an user
; This parameter can be used to limit the number of chat messages stored on
;  behalf of an user
; This parameter is applied on reload
; Defaults to 0 (no limit) if missing or invalid
;maxcount=

; expires: integer: The time interval (in minutes) a saved chat message
;  will be kept in the database
; This parameter can be used to clear old messages
; This parameter is applied on reload
; Defaults to 0 (no limit) if missing or invalid
;expires=

; expire_query: string: Query used to expire offline messages
;expire_query=DELETE FROM offlinechat WHERE time<'${time}'

; get: string: Query used to retrieve an user's offline messages
;get=SELECT * FROM offlinechat WHERE username='${username}' ORDER BY time

; set: string: Query used to add an offline message addressed to an user
; The query should check if the user exists
; It should return a non 0 integer value indicating success or 0 on failure
; If 'maxcount' is greater then 0 the query should check for maximum
;  allowed number of chat messages
;add=SELECT * FROM offlinechat_add('${username}', '${xml}', ${time}, ${maxcount})

; clear_user: string: Query used to delete all offline messages belonging to an user
;clear_user=DELETE FROM offlinechat WHERE username='${username}'

[edit] Example of stored procedures

[edit] privatedata_set

Used to add or update user's private data.

CREATE FUNCTION privatedata_set(text, text, text, text) RETURNS text
    AS $_$
DECLARE
    _username ALIAS FOR $1;
    _tag ALIAS FOR $2;
    _xmlns ALIAS FOR $3;
    _xml ALIAS FOR $4;
    data record;
BEGIN
    SELECT INTO data xml FROM privatedata WHERE username=_username AND tag=_tag AND xmlns=_xmlns;
    IF not found THEN
        INSERT INTO privatedata (username,tag,xmlns,xml) VALUES (_username,_tag,_xmlns,_xml);
    ELSE
        UPDATE privatedata SET xml=_xml WHERE username=_username AND tag=_tag AND xmlns=_xmlns;
    END IF;
    return '''';
END;
$_$
    LANGUAGE plpgsql;

[edit] offlinechat_add

CREATE FUNCTION offlinechat_add(text, text, integer, integer) RETURNS integer
   AS $_$
DECLARE
   _username ALIAS FOR $1;
   _xml ALIAS FOR $2;
   _time ALIAS FOR $3;
   _max ALIAS FOR $4;
   n integer;
BEGIN
   SELECT INTO n COUNT(*) FROM users WHERE username=_username;
   IF n > 0 THEN
   n := 1;
   IF _max > 0 THEN
       SELECT INTO n COUNT(*) FROM offlinechat WHERE username=_username;
       IF n < _max THEN
           n := 1;
       ELSE
           n := 0;
       END IF;
   END IF;
   IF n = 1 THEN
       INSERT INTO offlinechat (username,xml,time) VALUES (_username,_xml,_time);
   END IF;
   END IF;
   RETURN n;
END;
$_$
   LANGUAGE plpgsql;


See also

Personal tools
Namespaces

Variants
Actions
Preface
Configuration
Administrators
Developers