DBTEXT Module

Daniel-Constantin Mierla

   <miconda@gmail.com>

Edited by

Ovidiu Sas

   <osas@voipembedded.com>

Daniel-Constantin Mierla

   <miconda@gmail.com>

Olle E. Johansson

   <oej@edvina.net>

   Copyright © 2003, 2004 FhG FOKUS
     __________________________________________________________________

   Table of Contents

   1. Admin Guide

        1. Overview

              1.1. Design of dbtext engine
              1.2. Internal format of a dbtext table

        2. Known limitations
        3. Dependencies

              3.1. Kamailio modules
              3.2. External libraries or applications

        4. Parameters

              4.1. db_mode (integer)
              4.2. db_delim (string)
              4.3. default_connection (string)
              4.4. emptystring (integer)
              4.5. file_buffer_size (integer)
              4.6. max_result_rows (integer)

        5. Exported RPC Functions

              5.1. db_text.dump
              5.2. db_text.query

        6. Installation and Running

              6.1. Using db_text with a basic Kamailio configuration

   2. Developer Guide

   List of Examples

   1.1. Sample of a dbtext table
   1.2. Minimal Kamailio location dbtext table definition
   1.3. Minimal Kamailio subscriber dbtext table example
   1.4. Set db_mode parameter
   1.5. Set db_mode parameter
   1.6. Set default_connection parameter
   1.7. Set emptystring parameter
   1.8. Set file_buffer_size parameter
   1.9. Set max_result_rows parameter
   1.10. Load the dbtext module
   1.11. Definition of 'subscriber' table (one line)
   1.12. Definition of 'location' and 'aliases' tables (one line)
   1.13. Definition of 'version' table and sample records
   1.14. Configuration file

Chapter 1. Admin Guide

   Table of Contents

   1. Overview

        1.1. Design of dbtext engine
        1.2. Internal format of a dbtext table

   2. Known limitations
   3. Dependencies

        3.1. Kamailio modules
        3.2. External libraries or applications

   4. Parameters

        4.1. db_mode (integer)
        4.2. db_delim (string)
        4.3. default_connection (string)
        4.4. emptystring (integer)
        4.5. file_buffer_size (integer)
        4.6. max_result_rows (integer)

   5. Exported RPC Functions

        5.1. db_text.dump
        5.2. db_text.query

   6. Installation and Running

        6.1. Using db_text with a basic Kamailio configuration

1. Overview

   1.1. Design of dbtext engine
   1.2. Internal format of a dbtext table

   The module implements a simplified database engine based on text files.
   It can be used by Kamailio DB interface instead of other database
   module (like MySQL).

   The module is meant for use in demos or small devices that do not
   support other DB modules. It keeps everything in memory and if you deal
   with large amount of data you may run out of memory quickly. Also, it
   does not implement all standard database facilities (like order by), it
   includes minimal functionality to work properly (who knows ?!?) with
   Kamailio.

   NOTE: the timestamp is printed in an integer value from time_t
   structure. If you use it in a system that cannot do this conversion, it
   will fail (support for such situation is in to-do list).

   NOTE: even when db_text is in non-caching mode, the module does not
   write back to hard drive after changes. In this mode, the module checks
   if the corresponding file on disk has changed, and reloads it. The
   write to disk happens at Kamailio shut down. If db_text is in caching
   mode, many "reload" functions in various modules will not work.

1.1. Design of dbtext engine

   The dbtext database system architecture:
     * A database is represented by a directory in the local file system.
       NOTE: when you use dbtext in Kamailio, the database URL for modules
       must be the path to the directory where the table-files are
       located, prefixed by “text://”, e.g., “text:///var/dbtext/ser”. If
       there is no “/” after “text://” then “CFG_DIR/” is inserted at the
       beginning of the database path. So, either you provide an absolute
       path to database directory or a relative one to “CFG_DIR”
       directory.
       Do not forget that all databases in Kamailio needs the “version”
       table.
     * A table is represented by a text file inside database directory.

1.2. Internal format of a dbtext table

   The first line is the definition of the columns. Each column must be
   declared in the following format:
     * the name of column must not include white spaces.
     * the format of a column definition is: name(type,attr).
     * between two column definitions must be a white space, e.g.,
       “first_name(str) last_name(str)”.
     * the type of a column can be:
          + int - integer numbers.
          + double - real numbers with two decimals.
          + str - strings with maximum size of 4KB.
     * a column can have one of the attributes:
          + auto - only for 'int' columns, the maximum value in that
            column is incremented and stored in this field if it is not
            provided in queries.
          + null - accept null values in column fields.
          + if no attribute is set, the fields of the column cannot have
            null value.
     * each other line is a row with data. The line ends with “\n”.
     * the fields are separated by “:”.
     * no value between two ':' (or between ':' and start/end of a row)
       means “null” value. If the parameter "emptystring" is enabled,
       db_text sets a NULL string to an empty string value.
     * next characters must be escaped in strings: “\n”, “\r”, “\t”, “:”.
     * 0 -- the zero value must be escaped too.

   Example 1.1. Sample of a dbtext table
...
id(int,auto) name(str) flag(double) desc(str,null)
1:nick:0.34:a\tgood\: friend
2:cole:-3.75:colleague
3:bob:2.50:
...

   Example 1.2. Minimal Kamailio location dbtext table definition
...
username(str) contact(str) expires(int) q(double) callid(str) cseq(int)
...

   Example 1.3. Minimal Kamailio subscriber dbtext table example
...
username(str) password(str) ha1(str) domain(str) ha1b(str)
suser:supasswd:xxx:alpha.org:xxx
...

2. Known limitations

   This database interface does not support data insertion with default
   values. All such values specified in the database template are ignored.
   So its advisable to specify all data for a column at insertion
   operations.

3. Dependencies

   3.1. Kamailio modules
   3.2. External libraries or applications

3.1. Kamailio modules

   These modules must be loaded before this module:
     * none.

3.2. External libraries or applications

   These libraries or applications must be installed before running
   Kamailio with this module:
     * none.

4. Parameters

   4.1. db_mode (integer)
   4.2. db_delim (string)
   4.3. default_connection (string)
   4.4. emptystring (integer)
   4.5. file_buffer_size (integer)
   4.6. max_result_rows (integer)

4.1. db_mode (integer)

   Set caching mode (0) or non-caching mode (1). In caching mode, data is
   loaded at startup. In non-caching mode, the module check every time a
   table is requested whether the corresponding file on disk has changed,
   and if yes, will re-load the table from file.

   Default value is “0”.

   Example 1.4. Set db_mode parameter
...
modparam("db_text", "db_mode", 1)
...

4.2. db_delim (string)

   Set the delimiter inside the db_text file.

   Default value is “:”.

   Example 1.5. Set db_mode parameter
...
modparam("db_text", "db_delim", "|")
...

4.3. default_connection (string)

   connection for use with rpc query command.

   Default value is “none” (off).

   Example 1.6. Set default_connection parameter
                    ...
                    modparam("db_text", "default_connection", "text:///var/db/ka
mailio/dbtext")
                    ...

4.4. emptystring (integer)

   db_text by default handles an empty string as a NULL value. Some
   modules, like the dialplan module, does not accept NULL strings. If you
   enable emptystring an empty string will not be NULL, but an empty
   string.

   Default value is “0” (off).

   Example 1.7. Set emptystring parameter
...
modparam("db_text", "emptystring", 1)
...

4.5. file_buffer_size (integer)

   size of the buffer used to read the text file. Some presence tables
   have columns with large content.

   Default value is “16384”.

   Example 1.8. Set file_buffer_size parameter
...
modparam("db_text", "file_buffer_size", 8192)
...

4.6. max_result_rows (integer)

   number of rows to read from the text file.

   Default value is “100000”.

   Example 1.9. Set max_result_rows parameter
...
modparam("db_text", "max_result_rows", 1000000)
...

5. Exported RPC Functions

   5.1. db_text.dump
   5.2. db_text.query

5.1.  db_text.dump

   Write back to hard drive all modified tables.

   Name: db_text.dump

   Parameters: none

   RPC Command Format:
        kamcmd db_text.dump

5.2.  db_text.query

   run sql command

   Name: db_text.query

   Parameters: sqlcmd

   RPC Command Format:
            kamcmd db_text.query 'select * from location where username="xxx"'

6. Installation and Running

   6.1. Using db_text with a basic Kamailio configuration

   Compile the module and load it instead of mysql or other DB modules.

   REMINDER: when you use text in Kamailio, the database URL for modules
   must be the path to the directory where the table-files are located,
   prefixed by “text://”, e.g., “text:///var/dbtext/ser”. If there is no
   “/” after “text://” then “CFG_DIR/” is inserted at the beginning of the
   database path. So, either you provide an absolute path to database
   directory or a relative one to “CFG_DIR” directory.

   Example 1.10. Load the dbtext module
...
loadmodule "/path/to/kamailio/modules_k/db_text.so"
...
modparam("module_name", "db_url", "text:///path/to/dbtext/database")
...

6.1. Using db_text with a basic Kamailio configuration

   Here are definitions for the most important tables as well as a basic
   configuration file to use db_text with Kamailio. The table structures
   may change in time and you will have to adjust these examples. Check
   the source code directory “utils/kamctl/dbtext/kamailio” for current
   definitions.

   You have to populate the table 'subscriber' by hand with user profiles
   in order to have authentication. To use with the given configuration
   file, the table files must be placed in the '/tmp/serdb' directory.

   Example 1.11. Definition of 'subscriber' table (one line)
...
username(str) domain(str) password(str) first_name(str) last_name(str) phone(str
) email_address(str) datetime_created(int) datetime_modified(int) confirmation(s
tr) flag(str) sendnotification(str) greeting(str) ha1(str) ha1b(str) perms(str)
allow_find(str) timezone(str,null) rpid(str,null)
...

   Example 1.12. Definition of 'location' and 'aliases' tables (one line)
...
username(str) domain(str,null) contact(str,null) received(str) expires(int,null)
 q(double,null) callid(str,null) cseq(int,null) last_modified(str) flags(int) us
er_agent(str) socket(str)
...

   Example 1.13. Definition of 'version' table and sample records
...
table_name(str) table_version(int)
subscriber:3
location:6
aliases:6
...

   Example 1.14. Configuration file
...
#
#
# simple quick-start config script with dbtext
#

# ----------- global configuration parameters ------------------------

#debug=3         # debug level (cmd line: -ddd)
#fork=yes
#log_stderror=no        # (cmd line: -E)

check_via=no    # (cmd. line: -v)
dns=no          # (cmd. line: -r)
rev_dns=no      # (cmd. line: -R)
children=4

listen=10.100.100.1
port=5060

# ------------------ module loading ----------------------------------

# use dbtext database
loadmodule "modules/dbtext/dbtext.so"

loadmodule "modules/sl/sl.so"
loadmodule "modules/tm/tm.so"
loadmodule "modules/rr/rr.so"
loadmodule "modules/maxfwd/maxfwd.so"
loadmodule "modules/usrloc/usrloc.so"
loadmodule "modules/registrar/registrar.so"
loadmodule "modules/textops/textops.so"
loadmodule "modules/jonrpcs/jsonrpcs.so"

# modules for digest authentication
loadmodule "modules/auth/auth.so"
loadmodule "modules/auth_db/auth_db.so"

# ----------------- setting module-specific parameters ---------------

# -- usrloc params --

# use dbtext database for persistent storage
modparam("usrloc", "db_mode", 2)
modparam("usrloc|auth_db", "db_url", "db_text:///tmp/kamailiodb")

# -- auth params --
#
modparam("auth_db", "calculate_ha1", 1)
modparam("auth_db", "password_column", "password")
modparam("auth_db", "user_column", "username")
modparam("auth_db", "domain_column", "domain")

# -- rr params --
# add value to ;lr param to make some broken UAs happy
modparam("rr", "enable_full_lr", 1)

# -------------------------  request routing logic -------------------

# main routing logic

route{
    # initial sanity checks -- messages with
    # max_forwards==0, or excessively long requests
    if (!mf_process_maxfwd_header("10")) {
        sl_send_reply("483","Too Many Hops");
        exit;
    };
    if (msg:len >=  max_len ) {
        sl_send_reply("513", "Message too big");
        exit;
    };

    # we record-route all messages -- to make sure that
    # subsequent messages will go through our proxy; that's
    # particularly good if upstream and downstream entities
    # use different transport protocol
    if (!method=="REGISTER") record_route();

    # subsequent messages withing a dialog should take the
    # path determined by record-routing
    if (loose_route()) {
        # mark routing logic in request
        append_hf("P-hint: rr-enforced\r\n");
        route(1);
        exit;
    };

    if (!uri==myself) {
        # mark routing logic in request
        append_hf("P-hint: outbound\r\n");
        route(1);
        exit;
    };

    # if the request is for other domain use UsrLoc
    # (in case, it does not work, use the following command
    # with proper names and addresses in it)
    if (uri==myself) {
        if (method=="REGISTER") {
            # digest authentication
            if (!www_authorize("", "subscriber")) {
                www_challenge("", "0");
                exit;
            };

            save("location");
            exit;
        };

        lookup("aliases");
        if (!uri==myself) {
            append_hf("P-hint: outbound alias\r\n");
            route(1);
            exit;
        };

        # native SIP destinations are handled using our USRLOC DB
        if (!lookup("location")) {
            sl_send_reply("404", "Not Found");
            exit;
        };
    };
    append_hf("P-hint: usrloc applied\r\n");
    route(1);
}

route[1]
{
    # send it out now; use stateful forwarding as it works reliably
    # even for UDP2TCP
    if (!t_relay()) {
        sl_reply_error();
    };
}


...

Chapter 2. Developer Guide

   Once you have the module loaded, you can use the API specified by
   Kamailio DB interface.