Google

Introduction to SQL Relay

The original motivation for SQL Relay stems from the following challenges:

  • The API that Oracle provides along with it's database is obscure.
  • Oracle databases can take a relatively long time to log into, reducing the snappyness of transient programs such as CGI's.
  • Transient programs need to be small and statically linked to be efficient. The Oracle libraries are large and one of them (libclntsh.so) is only available as a shared object library on Linux.
  • It would be nice to be able to run a pool of web-servers using heterogenous hardware and operating systems against an Oracle database, but not be limited to Oracle-supported platforms for the web-servers.
  • Web-based applications that display reports need to display different segments of the same query's result set over multiple individual runs of a given program. It would be nice to be able to cache that result set rather than have to re-run the same query over and over, discarding most of it each time.

The following solution addresses these challenges:

  • A set of connection daemons maintain multiple open connections to Oracle.
  • A listener daemon listens on a TCP port for client connections.
  • The connection daemons advertise themselves to the listener daemon when they are available.
  • A client establishes a session with the listner daemon using a lightweight, statically linked C++ API library which can be compiled for any OS, independantly of the daemons.
  • The listener hands off the session to an available session daemon.
  • The connection daemon receives queries from the client over the network, executes the queries and returning the result sets back over the network to the client.
  • The client may then operate on the result set using API calls or cache it for use by another client.
  • The session framework allows transactional operations like commits and rollbacks to work without having to auto-commit each query.
  • A cache manager daemon maintains the query cache and removes stale result sets.

SQL Relay aims at implementing that solution.

Though SQL Relay is a fairly solid piece of software, it can still be enhanced. Below are some planned enhancements.

  • Support for more databases.
  • Encrypted transmissions.
  • Replication facilitation.
  • Server-side result set caching.
  • TCL API's.
  • Ports for non-unix based platforms.

If you would like to contribute to this project, please contact developer@firstworks.com.