Introduction to SQL Relay
The original motivation for SQL Relay stemmed 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 heterogeneous
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 listener daemon using a
lightweight, statically linked C++ API library which can be compiled for any
OS, independently 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.
- Ports for non-unix based platforms.
If you would like to contribute to this project, please contact dmuse@firstworks.com.