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.

<!DOCTYPE instances SYSTEM "sqlrelay.dtd">

<instances>

        <instance id="example" port="9000" socket="/tmp/examplesocket" dbase="oracle8" connections="3" maxconnections="15" maxqueuelength="5" growby="1" ttl="60" endofsession="commit" sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener and connection">
                <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;oracle_home=/u01/app/oracle/product/8.1.7" metric="1"/>
                        <connection connectionid="db1" string="user=exampleuser2;password=examplepassword2;oracle_sid=EXAMPLE2;oracle_home=/u01/app/oracle/product/8.1.7" metric="3"/>
                        <connection connectionid="db1" string="user=exampleuser3;password=examplepassword3;oracle_sid=EXAMPLE3;oracle_home=/u01/app/oracle/product/8.1.7" metric="5"/>
                        <connection connectionid="db1" string="user=exampleuser4;password=examplepassword4;oracle_sid=EXAMPLE4;oracle_home=/u01/app/oracle/product/8.1.7" metric="6"/>
                </connections>
        </instance>

</instances>

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

<!DOCTYPE sqlrelay [
<!ELEMENT instances (instance*)>
<!ELEMENT instance (users,connections)>
        <!ATTLIST instance id CDATA #REQUIRED>
        <!ATTLIST instance port CDATA>
        <!ATTLIST instance socket CDATA>
        <!ATTLIST instance dbase (oracle7|oracle8|mysql|msql|postgresql|sqlite|lago|freetds|sybase|odbc|db2|interbase) #REQUIRED>
        <!ATTLIST instance connections CDATA>
        <!ATTLIST instance endofsession (commit|rollback)>
        <!ATTLIST instance maxconnections CDATA>
        <!ATTLIST instance maxqueuelength CDATA>
        <!ATTLIST instance growby CDATA>
        <!ATTLIST instance ttl CDATA>
        <!ATTLIST instance sessiontimeout CDATA>
        <!ATTLIST instance runasuser CDATA>
        <!ATTLIST instance runasgroup CDATA>
        <!ATTLIST instance cursors CDATA>
        <!ATTLIST instance authtier (listener|connection|listener and connection|database) #REQUIRED>
        <!ATTLIST instance handoff (pass|reconnect) #REQUIRED>
        <!ATTLIST instance deniedips CDATA>
        <!ATTLIST instance allowedips CDATA>
        <!ATTLIST instance debug (none|listener|connection|listener and connection)>

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

<!ELEMENT connections (connection*)>
<!ELEMENT connection EMPTY>
        <!ATTLIST connection connectionid CDATA #REQUIRED>
        <!ATTLIST connection string CDATA #REQUIRED>
        <!ATTLIST connection metric CDATA #REQUIRED>
]>

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.

Below is a description of the attributes for each tag.

• instance
  • id - the ID of this instance
  • port - the port the listener should listen on
  • socket - the unix socket (filename) the listener should listen on
  • dbase - the type of database the connection daemon should connect to. Should be one of: oracle7, oracle8, mysql, msql, postgresql, sqlite, lago, freetds, sybase, odbc, db2 or interbase
  • 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
  • allowedips - a regular expression indicating which IP addresses will be allowed access, overriding deniedips
  • debug - instructs the listener and/or connection daemons to log debugging information when sqlr-start is run with the -debug option, see note below
• 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;autocommit=yes/no;"
  • For oracle8 databases, the connect string syntax is "user=USER;password=PASSWORD;oracle_sid=ORACLE_SID;oracle_home=ORACLE_HOME;autocommit=yes/no;"
  • For mysql databases, the connect string syntax is "user=USER;password=PASSWORD;db=DB;host=HOST;port=PORT;socket=SOCKET;"
  • For msql databases, the connect string syntax is "host=HOST;db=DB;"
  • For postgresql databases, the connect string syntax is "user=USER;password=PASSWORD;db=DB;host=HOST;port=PORT;options=OPTIONS;tty=TTY;typemangling=MANGLING;" If the typemangling parameter is set to "yes" then postgresql column types are translated to standard types. By default, the postgresql type number is returned.
  • For sqlite databases, the connect string syntax is "db=DB;"
  • For lago databases, the connect string syntax is "user=USER;password=PASSWORD;db=DB;host=HOST;port=PORT;"
  • For freetds databases, the connect string syntax is "sybase=SYBASE;user=USER;password=PASSWORD;server=SERVER;db=DATABASE;charset=CHARSET;language=LANGUAGE;encryption=ENCRYPTION;hostname=HOSTNAME;packetsize=PACKETSIZE;" The sybase parameter is the directory containing the "interfaces" file.
  • For sybase databases, the connect string syntax is "sybase=SYBASE;user=USER;password=PASSWORD;server=SERVER;db=DATABASE;charset=CHARSET;language=LANGUAGE;encryption=ENCRYPTION;hostname=HOSTNAME;packetsize=PACKETSIZE;" The sybase parameter is the directory containing the "interfaces" file.
  • For odbc databases, the connect string syntax is "user=USER;password=PASSWORD;dsn=DSN;autocommit=yes/no;"
  • For db2 databases, the connect string syntax is "user=USER;password=PASSWORD;db=DB;autocommit=yes/no;"
  • For interbase databases, the connect string syntax is "user=USER;password=PASSWORD;db=DATABASE;dialect=DIALECT;autocommit=yes/no;"
  • metric - a number that influences how many of this connection should be started

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 will use database-specific methods for authenticating and proxying the user. Currently this is only supported by Oracle 8i. 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 an SQL Relay client needs to talk to the database, it connects to a listner 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.