Friday, August 7, 2015

SQL Relay 0.62 release announcement

Version 0.62 of SQL Relay, the powerful connection pool, load balancer, query router, and database proxy is now available

Yes, that's right 0.62, not 0.61. 0.61 has been skipped for administrative reasons.

This release includes some significant new features.

node.js Client API

The most notable new feature is a client API for node.js. It's now possible to access any database supported by SQL Relay, from a node.js application. That includes Oracle, Microsoft SQL Server, Sybase/SAP, IBM DB2, MySQL, PostgreSQL, Firebird, SQLite, as well as any database accessible via ODBC.

See the Programming Guide for step-by-step instructions and examples, and the API Reference for details on the classes and methods.

Filters, Translations, and other Modules

SQL Relay has long had password encryption and logger modules, but new frameworks have been added for authentication, query and result set translation and query filtering. Some default modules have been included as well.

For more details on these frameworks, the included modules, and how to use them, see:

Firebird Updates

SQL Relay pre-allocates buffers to fetch result sets into, before sending the result set along to the client. It is now possible to tune the size of these buffers, and in-effect, memory usage, when using the Firebird database. The parameters involved are included in the database connect string, and are: maxitembuffersize, maxselectlistsize, and maxbindcount.

These parameters have long been supported with Oracle, Sybase/SAP and IBM DB2. Now they are supported with Firebird too.

Non-Lazy Connect

By default, SQL Relay does lazy connects. I.e. no connection is established to the SQL Relay server when the connection object is created. The connect doesn't occur until the first query (or other database operation) is run.

This is inconsistent with the behavior of most PDO and Perl DBI drivers though. Some apps rely on "new PDO" or "DBI->connect" failing if the database is inaccessible. Neither of those classically fail with SQL Relay though. The database can't be determined to be inaccessible until the first query is run. This behavior can cause problems when trying to insert SQL Relay between an existing app and the database that it used to connect to directly.

To address the issue, a new connect string variable has been added to the PHP PDO and Perl DBI drivers: lazyconnect.

If set to 1 (the default), then the default behavior is observed. If lazyconnect=0 is used, then a connection will be established immediately and "new PDO" and "DBI->connect" will fail if the database is inaccessible.

See the PHO PDO driver and Perl DBI driver documentation for more info.

Old PostgreSQL

Some apps rely on being able to send multiple, semicolon-delimited queries to PostgreSQL, in a single statement. However, changes to the PostgreSQL API in PostgreSQL 8, to support native bind variables, disabled this ability. SQL Relay's configure script detects PostgreSQL 8 and uses the modern API if it's available. This can cause problems for apps that needed the old behavior, when placing SQL Relay between them and the database using the drop-in replacement library for PostgreSQL. To remedy this problem, a --disable-postgresql8-api option has been added to the configure script. Using it disables the new API and makes it possible to send multiple, semicolon-delimited queries to PostgreSQL, in a single statement.

License Update

The documentation and examples given in the documentation are now explicitly covered by licenses. The documentation itself is GPL'ed and the examples are covered by the FSF Unlimited License:

The FSF Unlimited License

This software is free software; you have unlimited permission
to copy, distribute and modify it.

The SQLRelay Server, as defined in the file COPYING at the root of the source distribution, now carries an explicit exception that unquestionably allows it to be used with various non-GPL-compatible libraries. This was the intent all along, but modern interpretation of the GPL called it into question. There should be no question now. See a much longer discussion about this for more info.

Bug Fixes

The rest of the changes are just bug fixes.

Full ChangeLog follows:

  • added query filter plugin framework
  • added regex, string and pattern filters
  • added normalization translation
  • added reformatdatetime result set translation
  • fixed error in sqlrconfigfile class that could cause sqlr-start to crash if one instance defines addresses but the next doesn't
  • added query status flag to sqlrservercursor
  • tweaked odbc driver's SQLGetTypeInfo()
  • field name is passed into runResultSetTranslations() now
  • added support for maxitembuffersize, maxselectlistsize, and maxbindcount for firebird
  • added native api for node.js
  • added recognition of bigint, ubigint and uniqueidentifier fields in freetds/sybase
  • added nullsasnulls command to sqlrsh
  • added workaround for freetds empty (but non-null) text fields not getting properly converted to null-terminated empty strings
  • fetchatonce, maxitembuffersize and maxselectlistsize are set to defaulting values if invalid values are given for them
  • added lazyconnect option to PHP PDO and Perl DBI drivers (defaults to 1)
  • direct Transact SQL which returns a result set is now supported with Sybase/SAP/MSSQLServer/FreeTDS
  • added docs for authentication, query translation, query filter and result set translation modules
  • added -fPIC to sqlrserver-config --cflags, if supported
  • the "default" auth module is now called "userlist"
  • added database auth module that is analagous to authtier="database"
  • replaced passwordencryption attribute with passwordencryptionid
  • added --disable-postgresql8-api configure option
  • tweaked -Werror detection in configure script
  • refactored sqlr-bench build to work on windows
  • documentation, examples and tests are now covered by a license
  • reflecting modern interpretation of the GPL, The license for The SQLRelay Server (as designated in COPYING) license now carries an exception, explicitly allowing it to be linked with various non-GPL compatible libraries
  • license is installed now