Version 2.0.0 of SQL Relay, the powerful connection pool, load balancer, query router, and database proxy, is now available.
Rudiments 2.0.0 was just released as well, and since many of its classes underwent significant overhaul, it was necessary to overhaul parts of SQL Relay to take advantage of Rudiments' changes and new features. As such, most of the changes in SQL Relay 2.0.0 are internal. However, there are still quite a few significant user-facing features worth mentioning in this release.
Platform Support
As usual, support for modern versions of languages, operating systems, and databases has been included and wrung out - Ubuntu 24.04, Oracle 18c and 19c, and PDO in PHP 8.1. Support for a few more legacy platforms has been included as well - older versions of OS X, PHP and FreeTDS. Support for mdbtools has finally been dropped entirely.
On the server side...
Connect String Auth Module
Historically, authentication has been one of the biggest points of confusion with SQL Relay. If you include "user=scott;password=tiger" in the database connect string, then why don't you use scott/tiger when connecting to SQL Relay? Or if you want that to work, why do you have to create an entirely separate <users> tag elsewhere in the config file that just has scott/tiger in it again?
Well... Reasons. But not very good ones.
Starting in 2.0.0, the user/password that you use to log in to the database, is the user/password that you use to log in to SQL Relay, unless you specifically choose another user/password, or another authentication method. This is accomplished by the connect-string auth module, which is loaded by default, unless some other auth module is specifically chosen.
Protocol Modules vs. Drop-in-Replacement LibrariesSQL Relay 1.9 provided drop-in-replacement libraries for MySQL and PostgreSQL - libraries that reimplemented libmysqlclient and libpq with functions that called the functions of the SQL Relay client API. When LD_PRELOAD'ED, they enabled some MySQL and PostgreSQL applications to use SQL Relay without modification. This wasn't an ideal solution though. It required installation and configuration of every client machine, it didn't work at all on Windows, many apps (most notably PHP) were statically linked against libmysqlclient, and other apps just used pure reimplementations of the database protocols in Java, C#, or some other language.
To remedy this, SQL Relay 1.9 also provided server-side protocol modules for MySQL and PostgreSQL. With one of these modules enabled, apps could just be aimed at SQL Relay rather than at MySQL or PostgreSQL and work without modification. This was a better solution - it required no client-side changes, worked with windows clients, worked with PHP clients, and worked with non-C/C++ clients.
In SQL Relay 2.0.0, the server-side protocol modules are mature and the drop-in-replacement libraries are no more. If you want to drop SQL Relay in between your app and database, server-side protocol modules are the way to go.
Fledgling Protocol ModulesThe MySQL and PostgreSQL protocol modules are quite mature, but work has been in progress, off and on, for years, on protocol modules for other databases as well. Specifically, there are fledgling protocol modules for Oracle, MSSQL Server, Firebird, and Teradata.
The Teradata protocol is actually almost entirely complete, lacking only password encryption, for which I cannot find a detailed enough description to implement. The MSSQL Server module can kind-of perform some non-MARS tasks. The Oracle protocol can talk to an 8.0 client and return result sets from tables containing only varchars. Old enough Firebird clients can talk to the Firebird module and do similar things.
They could use some attention. Hopefully open-sourcing them will draw some.
Error Translation ModulesSQL Relay 2.0.0 provides a new error translation framework. If you're using a protocol module to make your PostgreSQL app think that it's talking to PostgreSQL, but it's really talking to Oracle via SQL Relay with some query translations, then that app may not understand what to do when it receives a native Oracle database error. Error translation modules allow you to translate error codes and messages on the back-end, similarly to how query translation modules allow you to translate queries on the front-end.
Upsert Trigger ModuleThe new upsert trigger module transparently converts inserts to updates if the insert fails with an error indicating that the row already exists.
Split Multi-Insert Trigger ModuleThe replay module automatically recovers from databse deadlocks by logging queries, rolling back, and replaying the query log if a deadlock is detected. However, multi-inserts (inserts that insert multiple rows at a time) into tables with an autoincrementing keys were always problematic for the replay module, as it was only every possible to get the last_insert_id of the final insert. The split multi-insert trigger module provides a solution for this by splitting multi-inserts into a set of single-inserts, for which the last_insert_id of each is available. It works well with the upsert module too.
Fully Commented Server-Side MethodsSQL Relay is highly modular, and the various module frameworks are fairly mature, but for the longest time, there has been no real documentation explaining how to develop modules, or how to use the myriad of server-side methods available to them. The first steps have been taken in that direction in 2.0.0. 100% of the server-side API has been commented, sufficient for doxygen to understand. A proper module-building guide and reference is forthcoming.
On the client side...
Nested Substititions
The SQL Relay client API has always supported substitution variables, both as an alternative to bind variables, and in cases where you can't use bind variables. You can, for example: "select * from sometable where $(WHERECLAUSE)" and then replace $(WHERECLAUSE) with an actual where clause. In SQL Relay 2.0.0, you can embed substitution variables into the values of other substitution variables. For example, in 2.0.0, $(WHERECLAUSE) can be "$(TABLE1).$(COLUMN1) = $(TABLE2).$(COUMN2)".
SQLR CRUDThe sqlrcrud class implements the new Rudiments mvccrud interface, providing doCreate(), doRead(), doUpdate(), and doDelete() methods against a SQL Relay backend. RUD methods that take a criteria argument support jsonlogic where clauses, and doRead() supports a JSON-formatted sort-by argument. Results can be fetched using various SQLR Collections. When paired with the Rudiments MVC framework, it's possible to build MVC apps in C++, with an SQL Relay backend.
SQLR CollectionsSQLR Collections implement Rudiments collections. sqlrrowlist and sqlrrowdictionary provide access to a row of the result set as if it were a read-only linked list or dictionary. sqlrresultsettable provides access to the entire result set as a read-only, sequential, block-based table.
Many MVC DAOs create lists, dictionaries, or tables containing entire result sets, then pass those up, ultimately to the view. This involves buffering entire result sets, creating nodes (and the pointers between them), and all too-often copying values, not to mention writing the code to populate the collections. The SQLR Collections eschew all of that by only buffering a block of results at a time, using API methods to navigate the result set, and only presenting the data as node-based collection, rather than actually creating and managing nodes. And the code is already-written and reusable.
sqlrimport and sqlrexportThe sqlrimport and sqlrexport classes (and their children) have existed for a while, but their implementations have always been inconsistent, and not especially usable by developers. They have been overhauled in 2.0.0 with a robust class hierarchy, consistent behavior across child classes, and well-commented and doxygened methods. A proper guide and reference are coming.
Overall...
Of course, there are many other improvements and bug fixes that were included in this release, but this announcement is already too long as it is. See the ChangeLog in the source distribution if you're interested in every single detail.
