Mod xml odbc

From FreeSWITCH Wiki
Jump to: navigation, search

Contents

Disclaimer

This module will not be maintained / updated any further as there is a way better alternative now for generating dynamic XML through ODBC from inside FreeSWITCH by using Lua with freeswitch.Dbh. An example for generating user directory XML is shown here. Please have a look at it.


This module is in the contrib section, so it's not directly supported by the core developers.

Please feel free to try it out and let me know how it works for you (ledr on IRC).

Overview

mod_xml_odbc is a module that allows you to realtime render XML based on templates that may contain simple if- and break-to statements, together with SQL queries, of which you can loop through and re-use the results later in the template.


It works a bit the same as mod_xml_curl in the way that you can bind to several things that FreeSWITCH™ may search for, namely:

  • configuration (not tested yet)
  • dialplan (not tested yet)
  • directory
  • phrases (not tested yet)


Advantage of this module is that it's fast and you do not need to have a separate web server together with scripting language. Obvious downside is that you loose a lot of flexibility as this is not a full-fledged scripting language. Another option may be to use an embedded scripting language like mod_lua or mod_spidermonkey to generate the XML stubs for you, but you can't use the core ODBC from there (correct me if I'm wrong).

If you only want to generate (simple) user directory XML through core ODBC, then have a look at mod_xml_odbc_simple.

Installation

In order to use mod_xml_odbc you must uncomment the module in modules.conf in the root of your source tree and just configure, make and make install everything again.

(I have to check still whether / how this will copy the configuration files - so here's the manual command)

To copy the configuration, do the following:

cp -avr <your-fs-src-dir>/contrib/mod/xml_int/xml_odbc* <your-fs-install-dir>/conf/autoload_configs


Configuration

To enable this module, simply add the following line to your modules.conf.xml:

<load module="mod_xml_odbc"/>

The configuration of the module itself is stored in xml_odbc.conf.

A very minimal configuration is shown here:

<configuration name="xml_odbc.conf" description="XML ODBC Configuration">

  <settings>
    <param name="binding" value="directory"/>
  </settings>

  <templates>

    <template name="default">
      <document type="freeswitch/xml">
        <section name="directory">
          <domain name="192.168.0.2">
            <user id="some_user">
              <params>
                <param name="password" value="some_password"/>
              </params>
            </user>
          </domain>
        </section>
      </document>
    </template>

  </templates>

</configuration>


Settings

In this section you can define the following params:

binding

<param name="binding" value="directory"/>

odbc-dsn

<param name="odbc-dsn" value="your_dsn_name:your_db_user:your_db_password"/>

debug

<param name="debug" value="true"/>

keep-files-around

<param name="keep-files-around" value="true"/>

Templates

Whenever the search function in mod_xml_odbc is called, it tries to render the template with name default. Of course, the previous example will not do much, except return the same static user-directory-xml on each search.

To make things a bit more dynamic, you have to know that on each search, FreeSWITCH™ passes an event to mod_xml_odbc. Each event contains headers that are simply name/value pairs like this:

key_value = 192.168.0.2
key_name = name
tag_name = domain
section = directory
user = some_user
etc..

So, it is possible to match on those headers and take actions on them.


header expansion

One thing you can do, is replace strings within (most) attribute values based on the event headers, by using ${foo} inside them. For example, if you'd like to always return a user XML containing the same domain / user as is requested, you could do this:

<template name="default">
  <xml-odbc-do name="check-event-header" if-name="user">
    <document type="freeswitch/xml">
      <section name="directory">
        <domain name="${domain}">
          <user id="${user}">
          </user>
        </domain>
      </section>
    </document>
  </xml-odbc-do>
</template>


xml-odbc-do

To do special things within a template, a tag xml-odbc-do exists that is not just copied from the template into the XML that is returned by the modules search function.

This tag must always have one attribute called name and can have more, depending on its function.


check-event-header

If for example, you want to only render the template for a user, when a user header is provided. In that case you may do this:

<template name="default">
  <xml-odbc-do name="check-event-header" if-name="user">
    <document type="freeswitch/xml">
      <section name="directory">
        <domain name="192.168.0.2">
          <user id="some_user">
            <params>
              <param name="password" value="some_password"/>
            </params>
          </user>
        </domain>
      </section>
    </document>
  </xml-odbc-do>
</template>

You can also match on the header value, by adding if-value="foo" to the check-event-header. In that case BOTH criteria have to be met.


set-event-header

This is another function that enables you to reset header values, based on the same conditions as check-event-header.

The only extra attribute that can be set here, is to-value, which replaces the value of a header based again on the criteria if-name AND optionally if-value. This comes in handy later.

(perhaps it's nice to later extend this function to also create new headers and/or set header-names)


break-to

This function allows you to break off rendering where you are, clear everything that has been rendered so-far, and start over in another template with the name passed as a value to this function.

For example:

<template name="default">
  <xml-odbc-do name="break-to" value="another-template"/>
</template

<template name="another-template">
  <your-other-template-goes-here/>
</template>

Another attribute that can be given with this function is on-no-template-break-to, which gives an alternative if given template does not exist.

If no template can be found (and if the optionally given on-no-template-break-to template can't be found either), then the template with name not-found will be rendered as a last resort.

note that for now, the not-found template still has to be included in the configuration - I plan on moving it into the module later


query

This is the main feature of the module. It's functionality is pretty straight-forward:

<template name="directory">
  <document type="freeswitch/xml">
    <xml-odbc-do name="query" on-empty-result-break-to="not-found" value="
      SELECT enabled FROM users WHERE username = '${user}';"/>
    <domain name="${domain}">
      <user id="${user}">
        <params>
          <xml-odbc-do name="query" value="
            SELECT name, value FROM user_params WHERE username = '${user}';">
            <param name="${name}", value="${value}"/>
          </xml-odbc-do>
        </params>
      </user>
    </domain>
  </document>
</template>

This example shows all features that you may use in the query function.

First, the query MUST obviously have a value that contains the query to be executed. Again in this value, all ${foo} elements will be expanded from the headers that are in the given event.

The query can also have an optional argument named on-empty-result-break-to. The name says what it does: When no results (zero rows) are returned, then the rendering will be broken of and restarted from the template as given by the value of this attribute (just like the regular break-to).

Also, all data returned by the query is stored as event headers, where the column names become the name, and the value of that column/row will become the value.

xml-odbc-do function can contain child-tags that will be rendered for each row that is returned from the query. As you can see in the params section in the example, all the user-params for a certain user are queried. For each row that is returned by the query, it's values (in this case name and value) are stored as event headers. When rendering all child-tags under the query, these values are used to render a single <param> line.