SQL Relay Guide

Downloading SQL Relay:

You can download the most current source distribution of SQL Relay from http://www.firstworks.com/site/downloads/archive/sqlrelay-latest.tar.gz.

Unpacking SQL Relay:

To unpack the distribution, copy the file to a location on your machine that you have access to and execute the following commands:

This will create a sqlrelay-X.X directory with bin, include, lib, and src directories beneath it.

Compiling SQL Relay:

To compile SQL Relay, read the INSTALL file, make sure your ORACLE_HOME environment variable is set if you are going to compile with Oracle support, then execute the following commands:

SQL Relay should compile and function on unix-based systems using the GNU development system. Other compilers may work too but have not been tested on the current version.

The -pedantic flag must be used with newer GNU compilers.

Support for compiling against Oracle (7.3.x, 8.0.x and 8.1.x), MySQL, mSQL, PostgreSQL, SQLite and Lago databases and against FreeTDS for accessing Sybase and MS SQL Server databases is provided. Linking with your particular platform and version may take some tweaking.

The ORACLE_HOME environment variable must be set for Oracle connection support to be included in the build.

After a complete, sucessfull compilation, the lib directory should contain:

The bin directory should contain:

API libraries should be built for C++, C, PHP, Perl, Python and Zope.

Your particluar build may omit one or more of the sqlr-connection's depending on whether support for one was omitted in the build. Your particular build may also omit the PHP, Perl or Python API if you don't have PHP, Perl or Python on your system.

As of version 0.8, SQL Relay requires libxml version 1.8.7 or newer. Without it, only the API's and command line programs will be built.

Installing SQL Relay:

To install SQL Relay, become root and type:

By default, everything is installed under /usr/local/firstworks.

To uninstall SQL Relay, become root and type:

The Executables:

The SQL Relay daemons are:

The utility programs are:

The command line interfaces are:

The API's are:

The Config File

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

The file format is complicated and is best explained with 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>

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

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

So what do all these tags do?

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.

Below is a description of the attributes for each tag.

Most of the tag attributes are straightforward. The most complicated one is the metric attribute of the sqlrclient:connection tag. It doesn't define how many connections are started, the connections attribute of the sqlrelay: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.

Setting Up a Connection:

Connecting to a database is the first step in using SQL Relay. To estabish a connection, run the sqlr-connection-oracle, sqlr-connection-mysql, sqlr-connection-msql, sqlr-connection-postgresql, sqlr-connection-sqlite, sqlr-connection-lago or sqlr-connection-freetds daemon. The usage syntax is:

sqlr-connection-"dbase" -id ID -config CONFIGFILE -connectionid CONNECTIONID

Where "dbase" is one of oracle, mysql, msql, postgresql, sqlite, lago or freetds.

The CONFIGFILE argument is the filename of the configuration file to use when starting the daemon. If not specified, the default configuration file /usr/local/firstworks/etc/sqlrelay.conf will be used.

The ID argument is just an identifier. Starting multiple connections with the same ID make them available as a pool of connections. The more connections you start, the more queries can be executed in parallel. In addition, the ID argument specifies which config file entry to use.

The CONNECTIONID argument is another identifier. When using replicated or clustered databases, within a pool of connections, some connections will connected to 1 machine and some will connect to another. This argument tells the connection daemon which machine to connect to. When not using clustered or replicated databases, this id will be the same for every connection in the pool.

Setting Up a Listener:

The next step in using SQL Relay is setting up a listener. The job of the listener is to connect to a TCP port and wait for queries. When it gets one, it waits for an available connection daemon, hands off the query to it, waits for the result set, and hands the result set back to the client.

Run the sqlr-listener daemon to establish a listener. The usage syntax is:

sqlr-listener -id ID -config CONFIGFILE

The CONFIGFILE argument is the filename of the configuration file to use when starting the daemon. If not specified, the default configuration file /usr/local/firstworks/etc/sqlrelay.conf will be used.

The ID argument is the same as the ID argument in the sqlr-connection daemon and associates a listener with a pool of connections.

Only one listener needs to be established for a given ID.

Setting Up the Cache Manager:

The third and final step in using SQL Relay is running the cache manager. Clients can cache result sets with an expiration date on them. The cache manager goes through the cached result sets periodically and removes the expired ones.

Run the sqlr-cachemanager daemon to establish the cache manager. The usage syntax is:

sqlr-cachemanager [INTERVAL]

The optional INTERVAL argument specifies in seconds how often the cache manager scans the result sets. Each scan is scheduled from the end of the previous scan. If the argument is left off, the cache manager scans at a default interval, defined a compile time.

Only one cache manager needs to be started per machine.

Setting Up the Everything at Once:

The sqlr-start program is provided to simplify the startup process. It's usage syntax is:

sqlr-start -id ID -config CONFIGFILE

The sqlr-start program starts up one instance of sqlr-listener and starts up the number of sqlr-connection daemons specified in the config file for the given id.

The sqlr-start program also checks to see if a sqlr-cachemanager is running. If one is then it leaves it running. If not then it starts one.

Extending, Killing and Restarting:

Once the daemons are up and running, clients can use them. In the event that more connections are required, additional sqlr-connection-oracle daemons may be started from the command line using the same ID and CONFIGFILE and will be immediately available to clients.

Connection daemons should not be killed once they have been started without restarting everything with that ID.

The killsqlr command is provided to kill running daemons. It doesn't accept any arguments. Running it kills anything with "sqlr-" in it's name.

If you kill and restart the daemons and get the message: "bind failed." as the listener is starting, then there are 2 possibilities. First, all the daemons may not have been killed. In this case, kill them all and make sure they are dead by using "ps -efal | grep sqlr-" before restarting them. The second possibility is that the port the listener was listening on didn't get closed. Executing "netstat -a | grep PORTNUMBER" will reveal any connections still lingering on the port. If all the daemons are dead but the connections are still lingering, wait 2 minutes or so before restarting the daemons. The lingering connections should have timed out by then.

Using the Command Line Interfaces:

Three command line utilities are provided for use with SQL Relay: query, fields, backupschema and sqlrsh. The syntax for each is:

query HOST PORT SOCKET USER PASSWORD QUERY

fields HOST PORT SOCKET USER PASSWORD TABLE

backupschema HOST SOCKET PORT USER PASSWORD

sqlrsh HOST PORT SOCKET USER PASSWORD

The HOST, PORT, SOCKET, USER and PASSWORD arguments specify which listener to connect to and what authentication to use.

The query command executes the query specified in the QUERY parameter and returns the result set as a quote/comma/return seperated value list to standard output.

The fields command returns a comma seperated list of the fields in the table specified by the TABLE argument to standard output.

The backupschema command only works against oracle databases and uses the "select table_name from user_tables" query in combination with the query and fields commands to generate Oracle SQL Loader control files for all tables owned by the user a particular connection is logged in as. The command is useful for backing up an entire schema of data to a readily re-importable format.

sqlrsh is an interactive query tool similar to (though not as powerful as) SQL Plus, mysql, psql, isql and the like. Run it and type help; at the prompt for detailed usage instructions. When sqlrsh starts up, it reads and executes two rc files, the system rc file (most likely /usr/local/firstworks/etc/sqlrshrc) and a personal rc file .sqlrshrc in your home directory. These files should contain sqlrsh commands, ending in semicolons, seperated by carraige returns. Any commands may be used in the rc files, even queries.

Using the API's:

The C++ API:

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

It's header file for it is sqlrclient.h and archives are libsqlrclient.a and libsqlrclient.so.

Here is a simple program that executes a query and writes out the result set to standard output.

#include <iostream.h>
#include <sqlrclient.h>

int     main() {

	sqlrclient      *sqlrc=new sqlrclient("testhost",8000,NULL,"testuser","testpassword",0,1);

	sqlrc->sendQuery("select table_name from user_tables");
	sqlrc->endSession();

	for (int i=0; i<sqlrc->rowCount(); i++) {
		cout << sqlrc->getField(i,"table_name") << endl;
	}

	delete sqlrc;
}

This code establishes a connection with the sqlr-listener running on port 8000 on the local machine and logs in as "testuser/testpassword". It then executes the query "select table_name from user_tables", closes the connection to the sqlr-listener and steps through each row of the result set, writing out the "table_name" field. Ultimately the sqlrclient instance is deleted at which time the connection to the sqlr-listener is broken.

An alternative to the sendQuery()/endSession() methods is the sendFinalQuery() method. If no endSession()/sendFinalQuery() is sent, the SQL Relay session remains open, occupying one of the connection daemons unti the sqlrcleint instance is deleted.

Multiple sendQuery() calls can be made before the endSession() or sendFinalQuery() call is made. Any sendQuery() calls made afterward will not be executed and may have unpredictable results.

In sendQuery()/sendFinalQuery() methods are supplemented by the sendFileQuery()/sendFinalFileQuery() methods. These methods allow queries to be executed (with substitions) from external files.

The sendCachedQuery() method set instructs the sqlr-listener to cache the result set of the query and return the name of the file it's cached in. The openCachedResultSet() method may then be used to access the cached result set.

The count and get methods return record set values or metadata.

The debug methods toggle verbose debugging. To get verbose debugging without calling debugOn(), the SQL comment "-- debug" followed by a carriage return can be placed as the first 9 characters in a query.

The C API:

The SQL Relay C API, sqlrclientwrapper has functions analagous to the C++ API. In fact, it's just a wrapper for the C++ API. Yes, I know how unusual that is, most people wrap their C functions in C++, not the other way around. In this case, the C++ API came first. The naming convention is slightly different for some functions because C++ method names can be overloaded an C function names cannot.

The header file for the C api is sqlrclientwrapper.h and archives are libsqlrclientwrapper.a and libsqlrclientwrapper.so. Since the C API is implemented as a wrapper of the C++ API, at link time, both libsqlrclient and the C++ archive need to be linked in. In GNU development systems, the C++ archive is libstdc++. In non-GNU environments, it's usually called libC and may be located somewhere like /usr/lib/CC. Alternatively, the C++ compiler could just be used to link everything.

So, the link command may look something like one of these:

Here's the same program as the one above, only using the C API.

#include <stdio.h>
#include <sqlrclientwrapper.h>

int     main() {

	int     i;

	sqlrc   s=sqlrc_alloc("testhost",8000,NULL,"testuser","testpassword",0,1);

	sqlrc_sendQuery(s,"select table_name from user_tables");
	sqlrc_endSession(s);

	for (i=0; i<sqlrc_rowCount(s); i++) {
		printf("%s\n",sqlrc_getFieldByName(s,i,"table_name"));
	}

	sqlrc_free(s);
}
The Perl API's:

The Perl module is Firstworks::SQLRClient. Since it's just an XS wrapper of the C++ API, it has the same methods. The names are different for a few methods though, as Perl doesn't like overloading.

Here's the sample program again, using the PERL API.

#!/usr/local/bin/perl -w
use Firstworks::SQLRClient;

my $s=Firstworks::SQLRClient->;new("testhost",8000,"","testuser","testpassword",0,1);

$s->sendQuery("select table_name from user_tables");
$s->endSession();

for (my $i=0; $i<$s->rowCount(); $i++) {
	print $s->getFieldByName($i,"table_name"), "\n";
}

The Perl DBD module is DBD::SQLRelay. It provides the standard DBD functions.

The Python API:

Two python interfaces are provided. One that closley maps to the C++ API: PySQLRClient.sqlrclient, and PySQLRDB that is compatible with the python DB API specification.

For documentation on the DB API the best place to look is the specification - which you can find at the python database SIG pages.

Here is the example again using PySQLRClient.sqlrclient. 

from SQLRelay import PySQLRClient

s = PySQLRClient.sqlrclient(('testhost', 8000), 'testuser', 'testpassword', 0, 1)
s.sendQuery('select column_name from user_table')
s.endSession()

for i in range(s.rowCount()):
    print s.getField(i, 'column_name')

And here is a script using the DB API that is similar. The DB API doesn't have the concept of retrieving specific fields, so instead we just retrieve the first 99 rows and print the values. 

from SQLRelay import PySQLRDB

cursor = PySQLRDB.connect(('testhost', 8000), 'testuser', 'testpassword')
cursor.execute('select column_name from user_table')

# get the first 99 rows
for i in cursor.fetchmany(99):
    for field in i:
        print field
The PHP API:

The PHP module for SQL Relay is sql_relay.so. Its functions are similar to the C API.

Security Issues:

There are some known security issues in SQL Relay.

These issues will all be addressed in a future release.