Configuring SQL Relay

Example

The SQL Relay configuration file (usually /usr/local/firstworks/etc/sqlrelay.conf) is complex and is best explained with an example:

<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">

<instances>

         <!-- Regular SQL Relay Instance -->
         <instance id="example" port="9000" socket="/tmp/example.socket" dbase="oracle8"
                  connections="3" maxconnections="15" maxqueuelength="5" growby="1" ttl="60"
                  maxsessioncount="1000" endofsession="commit" sessiontimeout="600"
                  runasuser="nobody" runasgroup="nobody" cursors="5" maxcursors="10" cursors_growby="1"
                  authtier="connection" sessionhandler="process" handoff="pass" deniedips="" allowedips=""
                  maxquerysize="65536" maxstringbindvaluelength="4000" maxlobbindvaluelength="71680"
                  idleclienttimeout="-1" maxlisteners="-1" listenertimeout="0" reloginatstart="no"
                  fakeinputbindvariables="no" translatebindvariables="no" isolationlevel="read committed"
                  ignoreselectdatabase="no" waitfordowndatabase="yes">
                <users>
                        <user user="user1" password="password1"/>
                        <user user="user2" password="password2"/>
                        <user user="user3" password="password3"/>
                </users>
                <session>
                        <start>
                                <runquery>alter session set nls_date_format='MM-DD-YYYY HH24:MI:SS'</runquery>
                        </start>
                        <end>
                                <runquery>alter session set nls_date_format='DD-MON-YYYY'</runquery>
                        </end>
                </session>
                <connections>
                        <connection connectionid="db1"
                           string="user=exampleuser1;password=examplepassword1;oracle_sid=EXAMPLE1;"
                           metric="1" behindloadbalancer="no"/>
                        <connection connectionid="db2"
                           string="user=exampleuser2;password=examplepassword2;oracle_sid=EXAMPLE2;"
                           metric="3" behindloadbalancer="no"/>
                        <connection connectionid="db3"
                           string="user=exampleuser3;password=examplepassword3;oracle_sid=EXAMPLE3;"
                           metric="5" behindloadbalancer="no"/>
                        <connection connectionid="db4"
                           string="user=exampleuser4;password=examplepassword4;oracle_sid=EXAMPLE4;"
                           metric="6" behindloadbalancer="no"/>
                </connections>
        </instance>


        <!-- Query Router/Filter -->
        <instance id="routerexample" port="9001" socket="/tmp/example.socket" dbase="router"
                  connections="3" maxconnections="15" maxqueuelength="5" growby="1" ttl="60"
                  maxsessioncount="1000" endofsession="commit" sessiontimeout="600"
                  runasuser="nobody" runasgroup="nobody" cursors="5"
                  authtier="connection" sessionhandler="process" handoff="pass" deniedips="" allowedips=""
                  maxquerysize="65536" maxstringbindvaluelength="4000" maxlobbindvaluelength="71680"
                  idleclienttimeout="-1" maxlisteners="-1" listenertimeout="0" reloginatstart="no">
                  fakeinputbindvariables="no" translatebindvariables="no" isolationlevel="read committed"
                  ignoreselectdatabase="no" waitfordowndatabase="yes">
                <users>
                        <user user="user1" password="password1"/>
                        <user user="user2" password="password2"/>
                        <user user="user3" password="password3"/>
                </users>
                <router>
                        <!-- send all queries for table1 to host1 -->
                        <route host="host1" port="9000" socket="" user="host1user" password="host1password">
                                <query pattern="^\s*select\s+.*\s+from\s+table1"/>
                                <query pattern="^\s*insert\s+into\s+table1"/>
                                <query pattern="^\s*update\s+table1"/>
                                <query pattern="^\s*delete\s+from\s+table1"/>
                                <query pattern="^\s*drop\s+table\s+table1"/>
                                <query pattern="^\s*create\s+table\s+table1"/>
                        </route>
                        <!-- filter out any queries for table2 -->
                        <filter>
                                <query pattern="^\s*select\s+.*\s+from\s+table2"/>
                                <query pattern="^\s*insert\s+into\s+table2"/>
                                <query pattern="^\s*update\s+table2"/>
                                <query pattern="^\s*delete\s+from\s+table2"/>
                                <query pattern="^\s*drop\s+table\s+table2"/>
                                <query pattern="^\s*create\s+table\s+table2"/>
                        </filter>
                        <!-- send any other queries to host2 -->
                        <route host="host2" port="9000" socket="" user="host2user" password="host2password">
                                <query pattern=".*"/>
                        </route>
                </router>
        </instance>

</instances>

As you can see, it's an XML file. Below is its DTD.

<!ELEMENT instances (instance*)>
<!ELEMENT instance (users,connections?,router?)>
        <!ATTLIST instance id CDATA "defaultid">
        <!ATTLIST instance addresses CDATA "0.0.0.0">
        <!ATTLIST instance port CDATA "9000">
        <!ATTLIST instance socket CDATA "/tmp/sqlrelay.socket">
        <!ATTLIST instance dbase (oracle8|mysql|postgresql|sqlite|freetds|sybase|odbc|db2|firebird|mdbtools|router) "oracle8">
        <!ATTLIST instance connections CDATA "1">
        <!ATTLIST instance maxconnections CDATA "1">
        <!ATTLIST instance maxqueuelength CDATA "0">
        <!ATTLIST instance growby CDATA "1">
        <!ATTLIST instance ttl CDATA "60">
        <!ATTLIST instance maxsessioncount CDATA "0">
        <!ATTLIST instance endofsession (commit|rollback) "commit">
        <!ATTLIST instance sessiontimeout CDATA "60">
        <!ATTLIST instance runasuser CDATA "nobody">
        <!ATTLIST instance runasgroup CDATA "nobody">
        <!ATTLIST instance cursors CDATA "5">
        <!ATTLIST instance maxcursors CDATA "1300">
        <!ATTLIST instance cursors_growby CDATA "5">
        <!ATTLIST instance authtier (connection|database) "connecton">
        <!ATTLIST instance sessionhandler (process|thread) "process">
        <!ATTLIST instance handoff (pass|proxy) "pass">
        <!ATTLIST instance deniedips CDATA "">
        <!ATTLIST instance allowedips CDATA "">
        <!ATTLIST instance maxquerysize CDATA "65536">
        <!ATTLIST instance maxstringbindvaluelength CDATA "4000">
        <!ATTLIST instance maxlobbindvaluelength CDATA "71680">
        <!ATTLIST instance idleclienttimeout CDATA "-1">
        <!ATTLIST instance maxlisteners CDATA "-1">
        <!ATTLIST instance listenertimeout CDATA "0">
        <!ATTLIST instance reloginatstart (yes|no) "no">
        <!ATTLIST instance fakeinputbindvariables (yes|no) "no">
        <!ATTLIST instance translatebindvariables (yes|no) "no">
        <!ATTLIST instance isolationlevel CDATA "read committed">
        <!ATTLIST instance ignoreselectdatabase (yes|no) "no">
        <!ATTLIST instance waitfordowndatabase (yes|no) "yes">
        <!ATTLIST instance datetimeformat CDATA "">
        <!ATTLIST instance dateformat CDATA "">
        <!ATTLIST instance timeformat CDATA "">
        <!ATTLIST instance dateddmm (yes|no) "no">
        <!ATTLIST instance dateyyyyddmm (yes|no) "no">

<!ELEMENT users (user*)>
<!ELEMENT user EMPTY>
        <!ATTLIST user user CDATA "user">
        <!ATTLIST user password CDATA "password">

<!ELEMENT session (start,end)>
<!ELEMENT start (runquery*)>
<!ELEMENT end (runquery*)>
<!ELEMENT runquery (#PCDATA)>

<!ELEMENT connections (connection*)>
<!ELEMENT connection EMPTY>
        <!ATTLIST connection connectionid CDATA "defaultid">
        <!ATTLIST connection string CDATA "user=scott;password=tiger">
        <!ATTLIST connection metric CDATA "1">
        <!ATTLIST connection behindloadbalancer (yes|no) "no">

<!ELEMENT router ((route*|filter*)*)>
<!ELEMENT route (query*)>
        <!ATTLIST route host CDATA "">
        <!ATTLIST route port CDATA "">
        <!ATTLIST route socket CDATA "">
        <!ATTLIST route user CDATA "">
        <!ATTLIST route password CDATA "">

<!ELEMENT filter (query*)>

<!ELEMENT query EMPTY>
        <!ATTLIST query pattern CDATA "">

Tags

So what do all these tags do?

The instances tag is just the root tag. The sqlrelay.conf file should have only one of these tags surrounding all other tags.

Each instance tag defines an instance of SQL Relay. An instance consists of one sqlr-listener and one or more sqlr-connection daemons. You can define as many of these as you like.

The users tag surrounds the list of users that may connect to the instance.

Each user tag defines a user/password combination that may be used to connect to the instance. The password is generally stored in plain text but it is possible to encrypt the password using a loadable module. See Password Encryption Modules

The session tag surrounds a set of queries to be run at the start and end of each session. The entire session block is optional.

The start tag surrounds a set of queries to be run at the the start of each session and the end tag surrounds a set of qeries to be run at the end of each session. Both are optional.

The runquery tag surrounds a query to be run at the beginning or end of a session. Any number (including 0) of these may be specified.

The connections tag surrounds the list of sqlr-connection daemon configurations used by the instance.

Each connection tag defines a sqlr-connection daemon configuration. In most cases, there will be only 1 of these tags. In cases where clustered or replicated databases are used, the sqlr-connection daemons may need to be able to connect to multiple machines. In that case, there would be more than 1 line here. See SQL Relay Load Balancing and Failover for more information.

The router tag sets up the instance to route and filter queries to other SQL Relay instances. If this tag is present, the instance should contain no connections tags. See Routing and Filtering Queries with SQL Relay for detailed information about configuring SQL Relay to route and filter queries.

The route tag designates an instance of SQL Relay to route queries to. It should contain a set of query tags defining which queries to route to the designated instance. This tag is only valid inside a router tag.

The filter tag defines a set of queries to be filtered out (ie. if a client sends one of these queries, the SQL Relay server won't run it against the database and will simply return an error). This tag is only valid inside a router tag.

The query tag defines a regular expression which the route and filter tags use to match queries.


Attributes

Below is a description of the attributes for each tag.


Complex Attributes

Most of the tag attributes are straightforward. The most complex one are the metric attribute of the connection tag and the authtier and handoff attributes of the instance tag.


Metric

The metric attribute doesn't define how many connections are started, the connections attribute of the instance tag defines that. The metric attribute influences how many of the total connections will be of that connection type. The higher the metric relative to the other metrics, the more of that connection type will be started.


Authtier

The client will send a user and password to the sqlr-connection daemon when it connects to it.

If the authtier attribute is set to "connection", the sqlr-connection daemon will compare the user/password to the list of user/passwords in the sqlrelay.conf file and accept or reject the client connection.

If the authtier attribute is set to "database", the sqlr-connection daemon will authenticate the user against the database itself rather than against the list of user/passwords in the sqlrelay.conf file. This causes SQL Relay to switch which user it is logged into the database as.

When SQL Relay is used with Oracle 8i or higher, the sqlr-connection daemon switches users without logging out. The sqlr-connection daemon must be configured to log into the database as a user that can proxy other users and the client must attempt to log in to SQL Relay as one of the users that can be proxied. See this document for more information including instructions for configuring Oracle.

When SQL Relay is used with any other database, it simply logs out and logs back in as a different user. This is somewhat ineffecient and defeats the value of keeping persistent database connections but might be useful for particular applications.

NOTE: authtier="database" can't be used in an instance where dbase="router". It's OK for the instances that the router uses to use authtier="database" but not the router instance itself. If authtier="database" is set on that instance, it will be overridden to authier="connection".


Handoff

When an SQL Relay client needs to talk to the database, it connects to a listener process which queues it up until a database connection daemon is available. When a daemon is available, the client is "handed off" to it. This "handoff" can be done in one of two ways. The file descriptor of the connected client can be passed from the listener to the connection daemon, or the listener can proxy the client, ferrying data back and forth between it and the connection daemon. These two methods are referred to as "pass" and "proxy". "proxy" works on every platform. "pass" works on most platforms but not all. "pass" is faster and lighter than "proxy" and should be used if possible. Cygwin and Linux kernels prior to 2.2 don't support "pass" though, and on those platforms, even if you specify "pass", "proxy" will be used instead and a warning will be displayed. Other platforms may not support "pass" as well but those are the only known ones and the only ones where "proxy" is forced.


Extension Modules

SQL Relay can be extended via modules. To load and enable a module, you must add additional tags and attributes to the config file and each module has its own tags and attributes. Currently Password Encryption and Logger modules are supported.