SQL Relay 2.0.0 Release Announcement

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 Libraries

SQL 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 Modules

The 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 Modules

SQL 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 Module

The 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 Module

The 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 Methods

SQL 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 CRUD

The 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 Collections

SQLR 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 sqlrexport

The 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.

Rudiments 2.0.0 Release Announcement

Version 2.0.0 of Rudiments, the C++ class library for developing systems and applications, is now available.

This release contains a tremendous number of changes and improvements...

Object

Much like Java, nearly every class now inherits from a base object class. This eliminates the need for some awkward void pointers, simplifies a bunch of internals, and facilitates a wastebasket class. Speaking of which...

Wastebasket

Ever need to create an arbitrary collection of objects, down in the depths of some body of code, and hang on to them until some higher level code is done with them? If that sounds abstract... In an MVC architecture, have you ever created a container full of objects way down in a DAO implementation and needed them to hang around until the view was done with them? In languages with garbage collection, they'll just hang around on their own until nobody needs them any more, but it's more complex than that in C++. The wastebasket class allows you to attach objects (children of the object class) to the wastebasket for bulk disposal later. You can create an arbitrary set of objects, throw them to the wastebasket, pass them back up to wherever, and continue to use them until you empty the wastebasket, at which point they'll all be destroyed.

MVC Framework

Speaking of DAO implementations and views... Rudiments 2.0.0 provides a basic MVC framework, which provides base classes for building out MVC applications. For example, there's an mvcsecurity class, an httpbasicsecurity implementation, an mvcproperites class that provides support for java-style properties files, mvccontroller, mvcview, mvcservice, and mvcdao classes, an mvcresult class for passing data up from mvcdao implementations, and an mvccrud class that defines an interface for classes that implement the CRUD paradigm. A resourcepool class is also provided for managing shared pools of resources.

Collection Overhaul

The collection classes (lists, trees, dictionaries, arrays, etc.) have undergone a major overhaul. Everything inherits from a base collection class now, which implements a bunch of functionality common to all containers. You can set a comparator to be used in sorting (eg. to sort in a non-sequential, "natural", or reverse order). Collections can be read-only, block/unit-based, or sequential-only.

Most notably though, a collection can now either "manage" its values or not. A collection that manages its values deletes them when the collection itself is deleted, and in the case of node-based collections like lists and trees, values are deleted when nodes are removed. In a dictionary, keys can also be managed. In rudiments 1.4, there were methods that you could call to delete or array-delete keys and values before deleting the collection, but this is a cleaner strategy.

There are also 2 new collections: table and scalar. The table container implements a table, with rows, columns, and column names. The scalar collection is a degenerate collection that only stores one value - useful when you have a single value but want to use a class that only operates on collections.

Character Set Improvements

Rudiments 1.4 had wcharacter/wcharstring/wstringbuffer classes. Rudiments 2.0 improves them and adds ucs2character/ucs2charstring/ucs2stringbuffer classes. Translation between character sets is improved as well. For example you can wcharstring::copy() or wcharstring::duplicate() an ascii or utf2 string, and vice-versa. The filedescriptor class provides methods for reading and writing wchar and ucs2 characters and strings. There's an iconvert class for translation of characters and strings between character sets. There's also a new locale class for managing locales and locale components.

Shutdown Flag

When an app crashes or receives a signal telling it to shut down, the signal handler shoudn't really be responsible for actually shutting down the app. Signal handlers really shouldn't do very much, at all! Arguably, a signal handler ought to just set a flag, the rest of the app should check for that flag in various places, and shut the app down gracefully if the flag was set. I've implemented that paradigm independently a dozen times, and finally decided to move it up into rudiments.

The process class now provides a set of methods like setShutDownFlagOnCrashOrShutDown() which just set a flag if a signal is caught that would crash or shut down the process, and also provides a getShutDownFlag() method to query that flag. Rudiments methods that retry on EINTR check this flag, and bail if it was set.

Block-Aligned and mmap() Buffering

The filedescriptor class can now differentiate between streams and storage, and buffer differently depending on which the descriptor is. If setIsStream(true) is called (the default) then it buffers the way it always has. If setIsStream(false) is called (for storage), then it aligns its buffers with block boundaries, and will use mmap(), when possible, if setMmapBufferingEnabled(true) is called. The various setPositionRelativeTo*() methods also now work as expected when buffering of storage is enabled.

byte_t

Historically, rudiments used unsigned char to represent a byte. Now there's a proper byte_t type that represents a byte.

Verbs

Methods tend to do things, and, as such ought to be verbs. Lots of methods have been renamed such that they "do" things now - eg. getIntegerLength() instead of integerLength(). Other methods that just describe a state or condition at least start with "is" - eg. isSupported() instead of supported().

Size, Length, and Count

For a long time, Rudiments (along with many other software projects) have confused size, length, and count. In Rudiments 2.0.0, size refers to the number of bytes, length to the number of characters, and count to the number of items. Eg. a ucs2 string might have a size of 10 but a length of 5. A linked list containing 5 ucs2 strings would have a count of 5. Method names and parameters have been overhauled, project-wide.

Bug Fixes and Improvements

The list of bug fixes and internal improvements could go on for days. Nearly evey class has been overhauled, updated, examined, or at least touched in some way. See the ChangeLog enclosed with the source distribution for the gory details.