To configure SQL Relay, edit the XML file /usr/local/firstworks/etc/sqlrelay.conf

An example:

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

<sqlrelay:instances>

	<sqlrelay:instance id="example" port="8000" dbase="oracle" connections="3" maxconnections="15" maxqueuelength="5" growby="1" ttl="60" endofsession="commit" sessiontimeout="600">
		<sqlrelay:users>
			<sqlrelay:user user="user1" password="password1"/>
			<sqlrelay:user user="user2" password="password2"/>
			<sqlrelay:user user="user3" password="password3"/>
		</sqlrelay:users>
		<sqlrelay:connections>
			<sqlrelay:connection connectionid="db1" string="user=exampleuser1;password=examplepassword1;oracle_sid=EXAMPLE1;" metric="1"/>
			<sqlrelay:connection connectionid="db1" string="user=exampleuser2;password=examplepassword2;oracle_sid=EXAMPLE2;" metric="3"/>
			<sqlrelay:connection connectionid="db1" string="user=exampleuser3;password=examplepassword3;oracle_sid=EXAMPLE3;" metric="5"/>
			<sqlrelay:connection connectionid="db1" string="user=exampleuser4;password=examplepassword4;oracle_sid=EXAMPLE4;" metric="6"/>
		</sqlrelay:connections>
	</sqlrelay:instance>

</sqlrelay:instances>

The DTD:

<!ELEMENT sqlrelay:instances (sqlrelay:instance*)>
<!ELEMENT sqlrelay:instance (sqlrelay:users,sqlrelay:connections)>
	<!ATTLIST sqlrelay:instance id CDATA #REQUIRED>
	<!ATTLIST sqlrelay:instance port CDATA>
	<!ATTLIST sqlrelay:instance unixport CDATA>
	<!ATTLIST sqlrelay:instance endofsession (commit, rollback) #REQUIRED>
	<!ATTLIST sqlrelay:instance sessiontimeout CDATA #REQUIRED>
	<!ATTLIST sqlrelay:instance dbase (oracle, mysql, msql, postgresql, sqlite, lago, freetds) #REQUIRED>
	<!ATTLIST sqlrelay:instance connections CDATA #REQUIRED>
	<!ATTLIST sqlrelay:instance maxconnections CDATA #REQUIRED>
	<!ATTLIST sqlrelay:instance maxqueuelength CDATA #REQUIRED>
	<!ATTLIST sqlrelay:instance growby CDATA #REQUIRED>
	<!ATTLIST sqlrelay:instance ttl CDATA #REQUIRED>

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

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

Tag descriptions:

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

Each sqlrelay: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 sqlrelay:users tag surrounds the list of users that may connect to the instance.

Each sqlrelay:user tag defines a user/password combination that may be used to connect to the instance.

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

Each sqlrelay: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.

Attribute descriptions:

• sqlrelay: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: oracle, mysql, msql, postgresql, sqlite, lago or freetds
  • connections - the number of sqlr-connection daemons to start up.
  • 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.
• sqlrclient:user
  • user - the user name required to connect to the listener
  • password - the password required to connect to the listener
• sqlrclient:connection
  • connectionid - the ID of this connection
  • string - the database connect string the connection daemon should use
  • For sqlr-connection-oracle, the connect string syntax is "user=USER;password=PASSWORD;oracle_sid=ORACLE_SID;"
  • For sqlr-connection-mysql, the connect string syntax is "user=USER;password=PASSWORD;db=DB;host=HOST;port=PORT;socket=SOCKET;"
  • For sqlr-connection-msql, the connect string syntax is "host=HOST;db=DB;"
  • For sqlr-connection-postgresql, the connect string syntax is "user=USER;password=PASSWORD;db=DB;host=HOST;port=PORT;options=OPTIONS;tty=TTY;"
  • For sqlr-connection-sqlite, the connect string syntax is "db=DB;"
  • For sqlr-connection-lago, the connect string syntax is "user=USER;password=PASSWORD;db=DB;host=HOST;port=PORT;"
  • For sqlr-connection-freetds, the connect string syntax is "user=USER;password=PASSWORD;server=SERVER;charset=CHARSER;language=LANGUAGE;encryption=ENCRYPTION;hostname=HOSTNAME;packetsize=PACKETSIZE;"
  • metric - a number that influences how many of this connection should be started

The SQL Relay daemons are:

• sqlr-connection-oracle -id ID -config CONFIGFILE -connectionid CONNECTIONID
• sqlr-connection-mysql -id ID -config CONFIGFILE -connectionid CONNECTIONID
• sqlr-connection-msql -id ID -config CONFIGFILE -connectionid CONNECTIONID
• sqlr-connection-postgresql -id ID -config CONFIGFILE -connectionid CONNECTIONID
• sqlr-connection-sqlite -id ID -config CONFIGFILE -connectionid CONNECTIONID
• sqlr-connection-lago -id ID -config CONFIGFILE -connectionid CONNECTIONID
• sqlr-connection-freetds -id ID -config CONFIGFILE -connectionid CONNECTIONID
• sqlr-listener -id ID -config CONFIGFILE
• sqlr-cachemanager [INTERVAL]

The utility programs are:

• sqlr-start -id ID -config CONFIGFILE
• killsqlr

The command line interfaces are:

• query HOST PORT SOCKET USER PASSWORD QUERY
• fields HOST PORT SOCKET USER PASSWORD TABLE
• backupschema HOST PORT SOCKET USER PASSWORD
• sqlrsh HOST PORT SOCKET USER PASSWORD

The C++ API consists of a single class: sqlrclient.

The C API consists of a set of functions: sqlrclientwrapper.h.

There are two Perl API's: Firstworks::SQLRClient and DBD::SQLRelay.

The Python API consists of two sets of functions: PySQLRClient.py and PySQLRDB.py.

The Zope Adapter, ZSQLRelayDA, uses the Python API.

The PHP API consists of a set of functions: sql_relay.so.