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.