SQL Relay Configuration Reference


Example

The SQL Relay configuration file is usually located at /usr/local/firstworks/etc/sqlrelay.conf

Additional configuration files may be created under the configuration directory, usually /usr/local/firstworks/etc/sqlrelay.conf.d

Files in the configuration directory may be given any name, but it is conventional to give them a .conf suffix.

Each configuration file may define any number of SQL Relay instances.

A comprehensive example follows.

<?xml version="1.0"?>
<instances>

        <!-- Regular SQL Relay Instance -->
        <instance id="example" enabled="yes" dbase="oracle"
                port="9000" socket="/tmp/example.socket"
                connections="3" maxconnections="15" maxqueuelength="5" growby="1" ttl="60" softttl="0"
                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" maxbindvars="256" 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 -->
        <instance id="routerexample" enabled="yes" dbase="router"
                port="9001" socket="/tmp/example.socket"
                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>
                      <!-- send any other queries to host2 -->
                      <route host="host2" port="9000" socket="" user="host2user" password="host2password">
                             <query pattern=".*"/>
                      </route>
               </router>
        </instance>

</instances>

Tags

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

Each instance tag defines an instance of SQL Relay. You can define as many of these as you like in each configuration file.

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 database connection configurations used by the instance.

Each connection tag defines a database connection configuration. In most cases, there will be only 1 of these tags. In cases where clustered or replicated databases are used, there would likely be more than 1 line here. See Load Balancing and Failover for more information.

The router tag sets up the instance to route queries to other SQL Relay instances. If this tag is present, the instance should contain no connections tags. See Query Routing for detailed information about configuring SQL Relay to route 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 query tag defines a regular expression which the route 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 when it connects to the SQL Relay server.

If the authtier attribute is set to "connection", then SQL Relay will compare the user/password to the list of user/passwords in a configuration file and accept or reject the client connection.

If the authtier attribute is set to "database", then SQL Relay will authenticate the user against the database itself rather than against the list of user/passwords in a configuration 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, it switches users without logging out. The database connection 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: Database authentication should not be used in an instance where dbase="router". It's OK for the instances that the router uses to use database authentication but not the router instance itself. If database authentication is used for that instance, authentication will fail.


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 a configuration file and each module has its own tags and attributes. The following types of modules are currently supported: