You can use sqlr-config-gtk to configure SQL Relay, but you can also edit the configuration file (usually /usr/local/firstworks/etc/sqlrelay.conf) by hand.

The file format is complicated 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" endofsession="commit" sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener" handoff="pass" deniedips="" allowedips="" debug="none" maxquerysize="65536" maxstringbindvaluelength="4000" maxlobbindvaluelength="71680" idleclienttimeout="-1" maxlisteners="-1" listenertimeout="0">
                <users>
                        <user user="user1" password="password1"/>
                        <user user="user2" password="password2"/>
                        <user user="user3" password="password3"/>
                </users>
                <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" endofsession="commit" sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener" handoff="pass" deniedips="" allowedips="" debug="none" maxquerysize="65536" maxstringbindvaluelength="4000" maxlobbindvaluelength="71680" idleclienttimeout="-1" maxlisteners="-1" listenertimeout="0">
                <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 it's 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 (oracle7|oracle8|mysql|postgresql|sqlite|freetds|sybase|odbc|db2|interbase|mdbtools) "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 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 authtier (listener|connection|listener_and_connection|database) "listener">
        <!ATTLIST instance handoff (pass|reconnect) "reconnect">
        <!ATTLIST instance deniedips CDATA "">
        <!ATTLIST instance allowedips CDATA "">
        <!ATTLIST instance debug (none|listener|connection|listener_and_connection) "none">
        <!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">

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

<!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 "">

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 sqlrelay. 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 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 quries.

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.

Below is a description of the attributes for each tag.

• instance
  • id - the ID of this instance
  • addresses - A comma-delimited list of addresses that the sqlr-listener and sqlr-connection daemons should bind to. If absent or set to 0.0.0.0, they will bind to all addresses.
  • port - the port the listener should listen on (note, if multiple instances are configured with the same port or socket, they cannot be run at the same time)
  • socket - the unix socket (filename) the listener should listen on (note, if multiple instances are configured with the same port or socket, they cannot be run at the same time)
  • dbase - the type of database the connection daemon should connect to. Should be one of: oracle7, oracle8, mysql, postgresql, sqlite, freetds, sybase, odbc, db2, interbase or mdbtools
  • connections - the number of sqlr-connection daemons to start up when using sqlr-start.
  • maxconnections - the maximum number of sqlr-connection daemons to scale to.
  • maxqueuelength - the size the queue of waiting clients has to grow to before more connections will be spawned.
  • growby - the number of connections that will be started up when new connections are spawned.
  • ttl - the amount of time that an idle connection will stay alive after being spawned dynamically (does not apply to connections spawned by sqlr-start).
  • endofsession - the command to issue when a client ends it's session or dies. Should be either commit or rollback.
  • sessiontimeout - if a client leaves a session open for another client to pick up but no client picks it up, the session will time out after this interval.
  • runasuser - the user to run the sqlr-listener, sqlr-connections and sqlr-scaler as.
  • runasgroup - the group to run the sqlr-listener and sqlr-connections and sqlr-scaler as.
  • cursors - the number of open database cursors to maintain
  • authtier - where to authenticate, see note below
  • handoff - method for handing off a client from listener to connection, can be one of: reconnect or pass, see note below
  • deinedips - a regular expression indicating which IP addresses will be denied access (for example, to deny access to all clients: deniedips=".*")
  • allowedips - a regular expression indicating which IP addresses will be allowed access, overriding deniedips (for example, to allow access to clients from the 192.168.2.0 and 64.45.22.0 networks: allowedips="(192\.168\.2\..*|64\.45\.22\..*)")
  • debug - instructs the listener and/or connection daemons to log debugging information when sqlr-start is run with the -debug option, see note below
  • maxquerysize - sets the maximum query length that the SQL Relay server will accept, if a client tries to send a longer query, the server will close the connection (defaults to 64k bytes)
  • maxstringbindvaluelength - sets the maximum length of a string bind value that the SQL Relay server will accept, if the client tries to send a longer string bind value, the server will close the connection (defaults to 32k bytes)
  • maxlobbindvaluelength - sets the maximum length of a LOB/CLOB bind value that the SQL Relay server will accept, if the client tries to send a longer LOB/CLOB bind value, the server will close the connection (defaults to 70k bytes)
  • idleclienttimeout - sets the number of seconds that a client can sit idle while logged into the SQL Relay server before it will be disconnected (defaults to -1 which means forever)
  • maxlisteners - When a client connects to the listener but no connections are available, a child listener is forked off to wait for an available connection. Since these can pile up and consume system resources, this parameter allows you to limit the number of child listeners that can be running simultaneously before an error will be returned to the client. Set to -1 (the default) to run without a limit.
  • listenertimeout - sets the number of seconds that a listener will wait for an avaialable connection before giving up, 0 (the default) means wait forever
• user
  • user - the user name required to connect to the listener
  • password - the password required to connect to the listener
• connection
  • connectionid - the ID of this connection
  • string - the database connect string the connection daemon should use
  • For oracle7 databases, the connect string syntax is "user=USER;password=PASSWORD;oracle_sid=ORACLE_SID;oracle_home=ORACLE_HOME;nls_lang=NLS_LANG;autocommit=yes/no;"
    • user: The username SQL Relay should use to log into the database. Required.
    • password: The password SQL Relay should use to log into the database. Required.
    • oracle_sid: Which SID in the tnsnames.ora file to use. Optional if the ORACLE_SID environment variable is set. Overrides the ORACLE_SID environment variable.
    • oracle_home: The base directory of the oracle installation to use. Optional if the ORACLE_HOME environment variable is set. Overrides the ORACLE_HOME environment variable. The SID will be looked up in $ORACLE_HOME/network/admin/tnsnames.ora
    • nls_lang: The NLS_LANG to use. Optional if the NLS_LANG environment variable is set. Overrides the NLS_LANG environment variable.
    • autocommit: Whether to commit each insert, update or delete immediately or not. Optional, defaults to no.
  • For oracle8 databases, the connect string syntax is "user=USER;password=PASSWORD;oracle_sid=ORACLE_SID;oracle_home=ORACLE_HOME;nls_lang=NLS_LANG;autocommit=yes/no;fetchatonce=FETCHATONCE;maxselectlistsize=MAXSELECTLISTSIZE;maxitembuffersize=MAXITEMBUFFERSIZE"
    • user: The username SQL Relay should use to log into the database. Required.
    • password: The password SQL Relay should use to log into the database. Required.
    • oracle_sid: Which SID in the tnsnames.ora file to use. Optional if the ORACLE_SID environment variable is set.
    • oracle_home: The base directory of the oracle installation to use. Optional if the ORACLE_HOME environment variable is set. The SID will be looked up in $ORACLE_HOME/network/admin/tnsnames.ora
    • nls_lang: The NLS_LANG to use. Optional if the NLS_LANG environment variable is set. Overrides the NLS_LANG environment variable.
    • autocommit: Whether to commit each insert, update or delete immediately or not. Optional, defaults to no.
    • fetchatonce: The number of rows that SQL Relay fetches from the database in each round trip. Defaults to 10. (see here for more info on this parameter)
    • maxselectlistsize: The maximum number of columns that can be fetched in a query. Defaults to 256. (see here for more info on this parameter)
    • maxitembuffersize: The maximum size of a non-lob field. Fields longer than that will be truncated. Defaults to 32768. (see here for more info on this parameter)
  • For mysql databases, the connect string syntax is "user=USER;password=PASSWORD;db=DB;host=HOST;port=PORT;socket=SOCKET;fakebinds=FAKEBINDS"
    • user: The username SQL Relay should use to log into the database. Required.
    • password: The password SQL Relay should use to log into the database. Required.
    • db: The database to log into. Required.
    • host: The host to connect to. Required.
    • port: The port to connect to on a remote host. Optional, defaults to 3306.
    • socket: The unix socket to connect to. Optional if host and/or port are specified. Overrides host/port. host and port are optional if socket is specified.
    • fakebinds: MySQL 4.1.2 supports bind variables natively. Versions prior to 4.1.2 did not, and SQL Relay had to substitute the values of bind variables into the query itself (fake binds). In MySQL 4.1.2, bind variables are identified by ?'s in the query, while in versions prior to 4.1.2, bind variables are identified by :var1, :var2, :var3, etc. Converting queries and code which was written to use the older syntax to use MySQL 4.1.2 syntax can be a lot of work, so this parameter is provided to make code which was written to run against older versions of MySQL work against MySQL 4.1.2. This parameter may be set to "yes" (meaning allow only older syntax and fake binds) or "no" (meaning only allow MySQL 4.1.2 syntax and use native binds).
  • For postgresql databases, the connect string syntax is "user=USER;password=PASSWORD;db=DB;host=HOST;port=PORT;options=OPTIONS;typemangling=MANGLING;fakebinds=FAKEBINDS;"
    • user: The username SQL Relay should use to log into the database. Required.
    • password: The password SQL Relay should use to log into the database. Required.
    • db: The database to log into. Required.
    • host: The host to connect to. Required. If host begins with a slash (/) then it is assumed to be the directory containing a unix socket starting with .s.PGSQL.
    • port: The port to connect to on a remote host. Optional, defaults to 5432. If host refers to a unix socket, then port is the extension on the socket name (eg. /tmp/.s.PGSQL.5432).
    • options: Command line options to be sent to the server. Optional.
    • typemangling: If set to "yes" then column types are translated to standard types. If set to "lookup" then the pg_type table is queried at startup and column type names are returned as they appear in that table. By default (or if set to "no"), the type number is returned.
    • fakebinds: Postgresql 8 supports bind variables natively. Versions prior to 8 did not, and SQL Relay had to substitute the values of bind variables into the query itself (fake binds). In Postgresql 8, bind variables are identified by $1, $2, $3, etc. in the query, while in versions prior to 8, bind variables are identified by :var1, :var2, :var3, etc. Converting queries and code which was written to use Postgresql 7 syntax to use Postgresql 8 syntax can be a lot of work, so this parameter is provided to make code which was written to run against Postgresql 7 work against Postgresql 8. This parameter may be set to "yes" (meaning allow only Postgresql 7 syntax and fake binds) or "no" (meaning only allow Postgresql 8 syntax and use native binds).
  • For sqlite databases, the connect string syntax is "db=DB;"
    • db: The filename of the database open. Required.
  • For freetds databases, the connect string syntax is "sybase=SYBASE;user=USER;password=PASSWORD;server=SERVER;db=DATABASE;charset=CHARSET;language=LANGUAGE;hostname=HOSTNAME;packetsize=PACKETSIZE;"
    • sybase: The directory containing the "interfaces" or "freetds.conf" file. Optional if the SYBASE environment variable is set. Overrides the SYBASE environment variable.
    • user: The username SQL Relay should use to log into the database. Required.
    • password: The password SQL Relay should use to log into the database. Required.
    • server: The entry in the "interfaces" or "freetds.conf" file which specifies host, port and other database connection parameters.
    • db: The database to log into. Required.
    • charset: The character set to use. Optional.
    • language: The language to use. Optional.
    • hostname: The host to connect to. Optional, overrides the host in the "interfaces" or "freetds.conf" file.
    • packetsize: The packetsize to use. Optional.
  • For sybase databases, the connect string syntax is "sybase=SYBASE;lang=LANG;user=USER;password=PASSWORD;server=SERVER;db=DATABASE;charset=CHARSET;language=LANGUAGE;hostname=HOSTNAME;packetsize=PACKETSIZE;"
    • sybase: The directory containing the "interfaces" or "freetds.conf" file. Optional if the SYBASE environment variable is set. Overrides the SYBASE environment variable.
    • user: The username SQL Relay should use to log into the database. Required.
    • password: The password SQL Relay should use to log into the database. Required.
    • server: The entry in the "interfaces" or "freetds.conf" file which specifies host, port and other database connection parameters.
    • db: The database to log into. Required.
    • charset: The character set to use. Optional.
    • language: The language to use. Optional.
    • hostname: The host to connect to. Optional, overrides the host in the "interfaces" or "freetds.conf" file.
    • packetsize: The packetsize to use. Optional.
  • For odbc databases, the connect string syntax is "user=USER;password=PASSWORD;dsn=DSN;autocommit=yes/no;"
    • user: The username SQL Relay should use to log into the database. Required.
    • password: The password SQL Relay should use to log into the database. Required.
    • dsn: The entry in the the odbcinst.ini which specifies host, port and other database connection parameters.
    • autocommit: Whether to commit each insert, update or delete immediately or not. Optional, defaults to no.
  • For db2 databases, the connect string syntax is "user=USER;password=PASSWORD;db=DB;autocommit=yes/no;"
    • user: The username SQL Relay should use to log into the database. Required.
    • password: The password SQL Relay should use to log into the database. Required.
    • db: The server instance to connect to.
    • autocommit: Whether to commit each insert, update or delete immediately or not. Optional, defaults to no.
  • For interbase databases, the connect string syntax is "user=USER;password=PASSWORD;db=DATABASE;dialect=DIALECT;autocommit=yes/no;"
    • user: The username SQL Relay should use to log into the database. Required.
    • password: The password SQL Relay should use to log into the database. Required.
    • db: The filename of the database open. Required.
    • dialect: The database dialect to use. Optional, defaults to 3.
    • autocommit: Whether to commit each insert, update or delete immediately or not. Optional, defaults to no.
  • For mdbtools databases, the connect string syntax is "db=DATABASE;"
    • db: The filename of the database open. Required.
  • metric - a number that influences how many of this connection should be started
  • behindloadbalancer - Whether the database is really a pool of databases behind a load balancer or not. If the database is not a pool of databases behind a load balancer, then when a connection daemon determines that the database has gone down, it will cause all connection daemons connected to that database to log out and re-login. However, if the database is really a pool of databases behind a load balancer, and a connection daemon determines that the database has gone down, only that connection will log out and re-login as some or all of the remaining connections may not actually be connected to the same physical database machine, but rather to a clone which has not actually gone down.
• route
• host - The host that the SQL Relay server that queries will be routed to is running on.
• port - The port that the SQL Relay server that queries will be routed to is listening on.
• socket - The socket that the SQL Relay server that queries will be routed to is listening on. Only use this if the SQL Relay server that queries will be routed to is running on the same machine as the router.
• user - The user name to use to log into the SQL Relay server that queries will be routed to is running on.
• password - The password to use to log into the SQL Relay server that queries will be routed to is running on.
• query
• pattern - A regular expression. Any query matching this regular expression will be routed to the SQL Relay server specified in the enclosing route tag.

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

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.

The client will send a user and password to the sqlr-listener and sqlr-connection daemons when it connects to them. The authtier attribute indicates which daemon will actually pay attention to that user and password.

If the authtier attribute is set to "listener", the sqlr-listener will compare the user/password to the list of user/passwords in the sqlrelay.conf file defined in the users tag and accept or reject the client connection. If the client is accepted, it is handed off to the sqlr-connection-database daemon which assumes that it has already been authenticated and does not perform it's own authentication.

If the authtier attribute is set to "connection", the sqlr-listener will ignore the user/password and just hand off the client to the sqlr-connection-database daemon which 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 "listener_and_connection" then both daemons will perform the authentication. This is the most secure method and the default but is slower than "listener" or "connection" alone.

If the authtier attribute is set to "database", the sqlr-listener daemon will ignore the user/password and the sqlr-connection-database daemon may use database-specific methods for authenticating and proxying the user.

When SQL Relay is used with Oracle 8i/9i, the sqlr-connection-database daemon switches users without logging out. The sqlr-connection-database 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 quite ineffecient and defeats the value of keeping persistent database connections.

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="listener_and_connection".

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 client can disconnect from the listener and reconnect to the connection daemon, or the existing connection to the client can be passed from the listener to the connection daemon. These two methods are referred to as "reconnect" and "pass". "Reconnect" works on every platform. "Pass" works on most platforms but not all. SCO OpenServer and Linux kernels prior to 2.2 don't support "pass". Other platforms may not support "pass" as well but those certainly don't. If SQL Relay is being run on a different machine than it's clients, "pass" is substantially faster than "reconnect" and more lightweight in any case. It should be used if possible.

If the debug attribute is not set to "none", when sqlr-start is run, it runs special versions of the sqlr-listener and sqlr-connection daemons which log debug information to files in /usr/local/firstworks/var/sqlrelay/debug. When the daemons start up, they print out the exact filename that they are logging to. This filename will be listener.PID or connection.PID where PID is the process ID of the daemon. Note that the sqlr-listener forks each time it gets a client connection and generates a seperate file for each forked process as well as one for the main process.

The debug attribute can be one of: "none", "listener", "connection" or "listener_and_connection". If "none" is specified, no debugging information is logged. If "listener" is specified, then only the listener daemon logs debug information. If "connection" is specified, then only the connection daemons log debug information. If "listener_and_connection" is specified, all daemons log debug information.