Diff
Added: freeswitch/trunk/contrib/mod/xml_int/mod_xml_odbc/structure.pgsql.sql (0 => 16971)
--- freeswitch/trunk/contrib/mod/xml_int/mod_xml_odbc/structure.pgsql.sql (rev 0)
+++ freeswitch/trunk/contrib/mod/xml_int/mod_xml_odbc/structure.pgsql.sql 2010-03-12 09:03:47 UTC (rev 16971)
@@ -0,0 +1,99 @@
+-- directory domains
+
+CREATE TABLE dir_domains (
+ id SERIAL NOT NULL,
+ name VARCHAR(255) NOT NULL,
+ CONSTRAINT dir_domains_id PRIMARY KEY (id)
+);
+
+CREATE UNIQUE INDEX dir_domains_name_index
+ ON dir_domains (name);
+
+
+CREATE TABLE dir_domain_params (
+ id SERIAL NOT NULL,
+ dir_domain_id INTEGER NOT NULL REFERENCES dir_domains(id),
+ name VARCHAR(255) NOT NULL,
+ value VARCHAR(255) NOT NULL,
+ CONSTRAINT dir_domain_params_id PRIMARY KEY (id)
+);
+
+CREATE UNIQUE INDEX dir_domain_params_dir_domain_id_name_index
+ ON dir_domain_params (dir_domain_id, name);
+
+
+CREATE TABLE dir_domain_variables (
+ id SERIAL NOT NULL,
+ dir_domain_id INTEGER NOT NULL REFERENCES dir_domains(id),
+ name VARCHAR(255) NOT NULL,
+ value VARCHAR(255) NOT NULL,
+ CONSTRAINT dir_domain_variables_id PRIMARY KEY (id)
+);
+
+CREATE UNIQUE INDEX dir_domain_variables_dir_domain_id_name_index
+ ON dir_domain_variables (dir_domain_id, name);
+
+
+
+-- directory users
+
+CREATE TABLE dir_users (
+ id SERIAL NOT NULL,
+ dir_domain_id INTEGER NOT NULL REFERENCES dir_domains(id),
+ username VARCHAR(255) NOT NULL,
+ enabled BOOLEAN NOT NULL DEFAULT 'true',
+ cidr VARCHAR(255),
+ mailbox VARCHAR(255),
+ "number-alias" VARCHAR(255),
+ CONSTRAINT dir_users_id PRIMARY KEY (id)
+);
+
+CREATE UNIQUE INDEX dir_users_dir_domain_id_username_index
+ ON dir_users (dir_domain_id, username);
+
+
+CREATE TABLE dir_user_params (
+ id SERIAL NOT NULL,
+ dir_user_id INTEGER NOT NULL REFERENCES dir_users(id),
+ name VARCHAR(255) NOT NULL,
+ value VARCHAR(255) NOT NULL,
+ CONSTRAINT dir_user_params_id PRIMARY KEY (id)
+);
+
+CREATE UNIQUE INDEX dir_user_params_dir_user_id_name_index
+ ON dir_user_params (dir_user_id, name);
+
+
+CREATE TABLE dir_user_variables (
+ id SERIAL NOT NULL,
+ dir_user_id INTEGER NOT NULL,
+ name VARCHAR(255) NOT NULL,
+ value VARCHAR(255) NOT NULL,
+ CONSTRAINT dir_user_variables_id PRIMARY KEY (id)
+);
+
+CREATE UNIQUE INDEX dir_user_variables_dir_user_id_name_index
+ ON dir_user_variables (dir_user_id, name);
+
+
+
+-- directory groups
+
+CREATE TABLE dir_groups (
+ id SERIAL NOT NULL,
+ name VARCHAR(255) NOT NULL,
+ CONSTRAINT dir_groups_id PRIMARY KEY (id)
+);
+
+CREATE UNIQUE INDEX dir_groups_name_index
+ ON dir_groups (name);
+
+
+CREATE TABLE dir_groups_users (
+ dir_group_id INTEGER NOT NULL REFERENCES dir_groups(id),
+ dir_user_id INTEGER NOT NULL REFERENCES dir_users(id)
+);
+
+CREATE UNIQUE INDEX dir_groups_users_index
+ ON dir_groups_users (dir_group_id, dir_user_id);
+
Added: freeswitch/trunk/contrib/mod/xml_int/mod_xml_odbc/testdata.pgsql.sql (0 => 16971)
--- freeswitch/trunk/contrib/mod/xml_int/mod_xml_odbc/testdata.pgsql.sql (rev 0)
+++ freeswitch/trunk/contrib/mod/xml_int/mod_xml_odbc/testdata.pgsql.sql 2010-03-12 09:03:47 UTC (rev 16971)
@@ -0,0 +1,8 @@
+INSERT INTO dir_domains (id, name) VALUES (1, 'freeswitch-rocks.org');
+INSERT INTO dir_domain_params (id, dir_domain_id, name, value) VALUES (1, 1, 'dom_param_name', 'dom_param_value');
+INSERT INTO dir_domain_variables (id, dir_domain_id, name, value) VALUES (1, 1, 'dom_variable_name', 'dom_variable_value');
+INSERT INTO dir_groups (id, name) VALUES (1, 'prepay'), (2, 'annoying_customers');
+INSERT INTO dir_groups_users (dir_group_id, dir_user_id) VALUES (1, 1), (2, 1);
+INSERT INTO dir_users (id, dir_domain_id, username, enabled, cidr, mailbox, number-alias) VALUES (1, 1, 'someuser', 'true', NULL, 'test@test.com', '1234');
+INSERT INTO dir_user_params (id, dir_user_id, name, value) VALUES (1, 1, 'password', 'topsecret'), (2, 1, 'vm-password', '0000');
+INSERT INTO dir_user_variables (id, dir_user_id, name, value) VALUES (1, 1, 'accountcode', '1234'), (2, 1, 'user_context', 'default'), (3, 1, 'vm_extension', '1234');
Modified: freeswitch/trunk/contrib/mod/xml_int/mod_xml_odbc/xml_odbc.conf.xml (16970 => 16971)
--- freeswitch/trunk/contrib/mod/xml_int/mod_xml_odbc/xml_odbc.conf.xml 2010-03-11 20:41:46 UTC (rev 16970)
+++ freeswitch/trunk/contrib/mod/xml_int/mod_xml_odbc/xml_odbc.conf.xml 2010-03-12 09:03:47 UTC (rev 16971)
@@ -56,7 +56,11 @@
</document>
</template>
- <X-PRE-PROCESS cmd="include" data="xml_odbc_templates/*.xml"/>
+ <!-- There is some difference in queries to mysql or postgresql, for example:
+ mysql: tablename.`columnname-contains-hyphens`
+ postgresql: tablename."columnname-contains-hyphens"
+ Choose the database you're using in the include data (pgsql or mysql) -->
+ <X-PRE-PROCESS cmd="include" data="xml_odbc_templates/*.pgsql.xml"/>
</templates>
Deleted: freeswitch/trunk/contrib/mod/xml_int/mod_xml_odbc/xml_odbc_templates/dialplan.xml
Copied: freeswitch/trunk/contrib/mod/xml_int/mod_xml_odbc/xml_odbc_templates/directory-gateways.mysql.xml (from rev 16957, freeswitch/trunk/contrib/mod/xml_int/mod_xml_odbc/xml_odbc_templates/directory-gateways.xml) (0 => 16971)
--- freeswitch/trunk/contrib/mod/xml_int/mod_xml_odbc/xml_odbc_templates/directory-gateways.mysql.xml (rev 0)
+++ freeswitch/trunk/contrib/mod/xml_int/mod_xml_odbc/xml_odbc_templates/directory-gateways.mysql.xml 2010-03-12 09:03:47 UTC (rev 16971)
@@ -0,0 +1,17 @@
+<include>
+ <!-- purpose=gateways is not only called for getting gateways, but also for getting a list
+ of domains that will be aliased to a profile if <domain .. alias="true"/> is set -->
+ <template name="directory-gateways">
+ <document type="freeswitch/xml">
+ <section name="directory">
+ <xml-odbc-do name="set-event-header" to-name="where-clause" to-value="`${key_name}`='${key_value}'"/>
+ <xml-odbc-do name="set-event-header" if-name="where-clause" if-value="``=''" to-value="1"/>
+ <xml-odbc-do name="query" on-empty-result-break-to="not-found"
+ value="SELECT name AS domain_name FROM dir_domains WHERE ${where-clause};">
+ <domain name="${domain_name}"/>
+ <!-- select all gateways of users within this domain_name here, to make parse=true working -->
+ </xml-odbc-do>
+ </section>
+ </document>
+ </template>
+</include>
Added: freeswitch/trunk/contrib/mod/xml_int/mod_xml_odbc/xml_odbc_templates/directory-gateways.pgsql.xml (0 => 16971)
--- freeswitch/trunk/contrib/mod/xml_int/mod_xml_odbc/xml_odbc_templates/directory-gateways.pgsql.xml (rev 0)
+++ freeswitch/trunk/contrib/mod/xml_int/mod_xml_odbc/xml_odbc_templates/directory-gateways.pgsql.xml 2010-03-12 09:03:47 UTC (rev 16971)
@@ -0,0 +1,17 @@
+<include>
+ <!-- purpose=gateways is not only called for getting gateways, but also for getting a list
+ of domains that will be aliased to a profile if <domain .. alias="true"/> is set -->
+ <template name="directory-gateways">
+ <document type="freeswitch/xml">
+ <section name="directory"> <!-- not the most efficient way, but gets executed seldom, so let it slide... -->
+ <xml-odbc-do name="set-event-header" to-name="where-clause" to-value=""${key_name}"='${key_value}'"/>
+ <xml-odbc-do name="set-event-header" if-name="where-clause" if-value="""=''" to-value="true"/>
+ <xml-odbc-do name="query" on-empty-result-break-to="not-found"
+ value="SELECT name AS domain_name FROM dir_domains WHERE ${where-clause};">
+ <domain name="${domain_name}"/>
+ <!-- select all gateways of users within this domain_name here, to make parse=true working -->
+ </xml-odbc-do>
+ </section>
+ </document>
+ </template>
+</include>
Deleted: freeswitch/trunk/contrib/mod/xml_int/mod_xml_odbc/xml_odbc_templates/directory-gateways.xml
Copied: freeswitch/trunk/contrib/mod/xml_int/mod_xml_odbc/xml_odbc_templates/directory-user.mysql.xml (from rev 16957, freeswitch/trunk/contrib/mod/xml_int/mod_xml_odbc/xml_odbc_templates/directory-user.xml) (0 => 16971)
--- freeswitch/trunk/contrib/mod/xml_int/mod_xml_odbc/xml_odbc_templates/directory-user.mysql.xml (rev 0)
+++ freeswitch/trunk/contrib/mod/xml_int/mod_xml_odbc/xml_odbc_templates/directory-user.mysql.xml 2010-03-12 09:03:47 UTC (rev 16971)
@@ -0,0 +1,92 @@
+<include>
+ <template name="directory-user">
+ <document type="freeswitch/xml">
+ <xml-odbc-do name="set-event-header" if-name="key" if-value="id" to-value="username"/>
+ <xml-odbc-do name="query" on-empty-result-break-to="not-found" value="
+ SELECT
+ dir_domains.id AS domain_id,
+ dir_users.id AS user_id,
+ dir_users.username,
+ dir_users.cidr,
+ dir_users.mailbox,
+ dir_users.`number-alias`
+ FROM
+ dir_domains,
+ dir_users
+ WHERE
+ dir_users.`${key}` = '${user}' AND
+ dir_domains.name = '${domain}' AND
+ dir_users.dir_domain_id = dir_domains.id AND
+ dir_users.enabled = '1';"/>
+ <section name="directory">
+ <domain name="${domain}">
+ <params>
+ <xml-odbc-do name="query" value="
+ SELECT
+ name, value
+ FROM
+ dir_domain_params
+ WHERE
+ dir_domain_id = '${domain_id}';">
+ <param name="${name}" value="${value}"/>
+ </xml-odbc-do>
+ </params>
+ <variables>
+ <xml-odbc-do name="query" value="
+ SELECT
+ name, value
+ FROM
+ dir_domain_variables
+ WHERE
+ dir_domain_id = '${domain_id}';">
+ <variable name="${name}" value="${value}"/>
+ </xml-odbc-do>
+ </variables>
+ <groups>
+ <group name="default">
+ <users>
+ <user id="${username}" mailbox="${mailbox}" cidr="${cidr}" number-alias="${number-alias}">
+ <params>
+ <xml-odbc-do name="query" on-empty-result-break-to="not-found" value="
+ SELECT
+ name, value
+ FROM
+ dir_user_params
+ WHERE
+ dir_user_id = '${user_id}';">
+ <param name="${name}" value="${value}"/>
+ </xml-odbc-do>
+ </params>
+ <variables>
+ <xml-odbc-do name="query" value="
+ SELECT
+ name, value
+ FROM
+ dir_user_variables
+ WHERE
+ dir_user_id = '${user_id}';">
+ <variable name="${name}" value="${value}"/>
+ </xml-odbc-do>
+ </variables>
+ </user>
+ </users>
+ </group>
+ <xml-odbc-do name="query" value="
+ SELECT
+ name AS group_name
+ FROM
+ dir_groups,
+ dir_groups_users
+ WHERE
+ dir_groups_users.dir_group_id = dir_groups.id AND
+ dir_user_id = '${user_id}';">
+ <group name="${group_name}">
+ <user id="${username}" type="pointer"/>
+ </group>
+ </xml-odbc-do>
+ </groups>
+ </domain>
+ </section>
+ </document>
+ </template>
+</include>
Added: freeswitch/trunk/contrib/mod/xml_int/mod_xml_odbc/xml_odbc_templates/directory-user.pgsql.xml (0 => 16971)
--- freeswitch/trunk/contrib/mod/xml_int/mod_xml_odbc/xml_odbc_templates/directory-user.pgsql.xml (rev 0)
+++ freeswitch/trunk/contrib/mod/xml_int/mod_xml_odbc/xml_odbc_templates/directory-user.pgsql.xml 2010-03-12 09:03:47 UTC (rev 16971)
@@ -0,0 +1,92 @@
+<include>
+ <template name="directory-user">
+ <document type="freeswitch/xml">
+ <xml-odbc-do name="set-event-header" if-name="key" if-value="id" to-value="username"/>
+ <xml-odbc-do name="query" on-empty-result-break-to="not-found" value="
+ SELECT
+ dir_domains.id AS domain_id,
+ dir_users.id AS user_id,
+ dir_users.username,
+ dir_users.cidr,
+ dir_users.mailbox,
+ dir_users."number-alias"
+ FROM
+ dir_domains,
+ dir_users
+ WHERE
+ dir_users."${key}" = '${user}' AND
+ dir_domains.name = '${domain}' AND
+ dir_users.dir_domain_id = dir_domains.id AND
+ dir_users.enabled = '1';"/>
+ <section name="directory">
+ <domain name="${domain}">
+ <params>
+ <xml-odbc-do name="query" value="
+ SELECT
+ name, value
+ FROM
+ dir_domain_params
+ WHERE
+ dir_domain_id = '${domain_id}';">
+ <param name="${name}" value="${value}"/>
+ </xml-odbc-do>
+ </params>
+ <variables>
+ <xml-odbc-do name="query" value="
+ SELECT
+ name, value
+ FROM
+ dir_domain_variables
+ WHERE
+ dir_domain_id = '${domain_id}';">
+ <variable name="${name}" value="${value}"/>
+ </xml-odbc-do>
+ </variables>
+ <groups>
+ <group name="default">
+ <users>
+ <user id="${username}" mailbox="${mailbox}" cidr="${cidr}" number-alias="${number-alias}">
+ <params>
+ <xml-odbc-do name="query" on-empty-result-break-to="not-found" value="
+ SELECT
+ name, value
+ FROM
+ dir_user_params
+ WHERE
+ dir_user_id = '${user_id}';">
+ <param name="${name}" value="${value}"/>
+ </xml-odbc-do>
+ </params>
+ <variables>
+ <xml-odbc-do name="query" value="
+ SELECT
+ name, value
+ FROM
+ dir_user_variables
+ WHERE
+ dir_user_id = '${user_id}';">
+ <variable name="${name}" value="${value}"/>
+ </xml-odbc-do>
+ </variables>
+ </user>
+ </users>
+ </group>
+ <xml-odbc-do name="query" value="
+ SELECT
+ name AS group_name
+ FROM
+ dir_groups,
+ dir_groups_users
+ WHERE
+ dir_groups_users.dir_group_id = dir_groups.id AND
+ dir_user_id = '${user_id}';">
+ <group name="${group_name}">
+ <user id="${username}" type="pointer"/>
+ </group>
+ </xml-odbc-do>
+ </groups>
+ </domain>
+ </section>
+ </document>
+ </template>
+</include>
Deleted: freeswitch/trunk/contrib/mod/xml_int/mod_xml_odbc/xml_odbc_templates/directory-user.xml