Friday, October 16, 2015

SQL Relay 0.64 Release Announcement

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

This release features support for Informix databases, support for remote and centralized configuration files, a better configure script for building from source on Windows, improvements to the drop-in replacement library for PostgreSQL, and a "softttl" parameter for dynamically spawned database connections.


Informix

SQL Relay now supports Informix databases. It can compile against fairly modern versions (probably 10.X and up) and access versions older than that. Informix may not be the most modern database, but it has a large user base and free developer versions have been available from IBM for a while now. It's high time SQL Relay supported it.

To configure SQL Relay to talk to an Informix database, use an sqlrelay.conf file like this:

<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">
<instances>
 <instance id="informixtest" dbase="informix">
  <users>
   <user user="test" password="test"/>
  </users>
  <connections>
   <connection
        string="informixdir=/opt/informix;servername=ol_informix1210;
                db=testdb;user=testdbuser;password=testdbpass"/>
  </connections>
 </instance>
</instances>

(Actually, the connection tag should be all-on-one-line, I just had to split it up to it would fit on the page)

Then you can access it with sqlrsh like:

[dmuse@windows7 ~]$ sqlrsh -id informixtest
SQLRShell - Version 0.64
        Connected to: localhost:9000 as test

        type help; for help.

0> use sysmaster;
0> show tables;
tabname
=================
flags_text
logmessage
smi_build_status
sysactptnhdr
sysadtinfo
sysaqt
...
systxptab
sysuserthreads
sysvplst
sysvpprof
sysxatab
        Rows Returned   : 196
        Fields Returned : 196
        System time     : 8651

0>

Informix!

Remote and Centralized Configuration Files

The -config option that all of SQL Relay's command line programs take can now be a local file, directory, or URL.

For example:

sqlr-start -id oracleexample -config \
/home/myuser/mysqlrelay.conf

sqlr-start -id oracleexample -config \
file:///home/myuser/mysqlrelay.conf

sqlr-start -id oracleexample -config \
dir:///home/myuser/mysqlrelay.conf.d

sqlr-start -id oracleexample -config \
http://configserver.mydomain.com/sqlrconfig/sqlrelay.conf

sqlr-start -id oracleexample -config \
http://myuser:mypassword@configserver.mydomain.com/sqlrconfig/sqlrelay.conf

sqlr-start -id oracleexample -config \
http://[/usr/local/firstworks/etc/sqlruserpwd.txt]@configserver.mydomain.com/sqlrconfig/sqlrelay.conf

(of course, the commands can be all-on-one-line, I just split them up here for readability)

In the case of a dir:// URL, all files under the given directory will be parsed as config files.

In the last http:// URL, the file between the square brackets is a user-password file, which can contain a single line like:

myuser:mypassword

Using a file rather than embedding the user/password in the URL helps keep the user/password out of process listings and init scripts.

If Rudiments (the library upon which SQL Relay is built) was built with support for libcurl, the https, ftp, scp, and sftp urls can be used as well.

The -config option can now take a comma-separated list of URL's too. For example:

sqlr-start -id oracleexample -config \
/home/myuser/mysqlrelay-1.conf,\
/home/myuser/mysqlrelay-2.conf

sqlr-start -id oracleexample -config \
/home/myuser/mysqlrelay-1.conf,\
/home/myuser/mysqlrelay-2.conf,\
dir:///home/myuser/mysqrelay.conf.d

sqlr-start -id oracleexample \
-config /home/myuser/mysqlrelay-1.conf,\
/home/myuser/mysqlrelay-2.conf,\
dir:///home/myuser/mysqrelay-1.conf.d,\
dir:///home/myuser/mysqrelay-2.conf.d

sqlr-start -id oracleexample \
-config http://configserver1.mydomain.com/sqlrconfig/sqlrelay.conf,\
http://configserver2.mydomain.com/sqlrconfig/sqlrelay.conf,\
http://configserver3.mydomain.com/sqlrconfig/sqlrelay.conf

(again, I just split the commands over multiple lines for readability)

Any combination of local files, directories and remote URL's can be specified. Files will be processed in the order that they are specified.

SQL Relay now supports "link files" too. Instead of containing an XML configuration, a "link file" just contains the URL's of other configuration files. For example:

# oracle configuration
http://myuser:mypassword@configserver.mydomain.com/sqlrconfig/oracle.conf

# sap/sybase configuration
http://myuser:mypassword@configserver.mydomain.com/sqlrconfig/sap.conf

# db2 configuration
http://myuser:mypassword@configserver.mydomain.com/sqlrconfig/db2.conf

Any url that would be valid on the command line is valid in a link file. Link files may simply refer to other link files too.

Link files can be used to centralize configuration. For example, if you have several SQL Relay servers, rather than distributing configuration files across the servers, you could create an identical sqlrelay.conf file on each of them like:

http://myuser:mypassword@configserver.mydomain.com/sqlrconfig/sqlrelay.conf 

And then, on configserver.mydomain.com, host an sqlrelay.conf file like:

http://myuser:mypassword@configserver.mydomain.com/sqlrconfig/oracle.conf
http://myuser:mypassword@configserver.mydomain.com/sqlrconfig/sap.conf
http://myuser:mypassword@configserver.mydomain.com/sqlrconfig/db2.conf

The files oracle.conf, sap.conf, and db2.conf could then be hosted by and maintained on that server as well.

See the SQL Relay Configuration Guide for more information. In particular, the sections on Remote Configuration Files and Link Files.

A Better Configure Script for Windows

In the past, the Windows configure.vbs script didn't do much configuring. It basically detected what compiler and version of Windows you were using, and set all the right options for those, but didn't do much to detect different versions of databases or languages.

It does a lot more now. It's not as full featured as its linux/unix counterpart yet, but it's a lot better than it was in the past. It can now detect various versions of databases and languages, provided they're installed in reasonably standard locations. It also has options allowing you to specify locations and versions, in case you did install something in an odd location, and it provides feedback about what it could and couldn't find, like its linux/unix counterpart.

For example:

C:\Users\dmuse\src\sqlrelay>cscript /nologo configure.vbs

***** Platform ***************
Visual C++ Version: 16
Visual C++ Architecture: x64
Windows Version: 0x0601
******************************

***** Oracle *****************
Oracle includes... /I "C:\Program Files\Oracle\instantclient_12_1\sdk\include"
Oracle libs... /LIBPATH:"C:\Program Files\Oracle\instantclient_12_1\sdk\lib\msvc
" oci.lib
******************************

***** MySQL ******************
MySQL includes... /I "C:\Program Files\MySQL\MySQL Connector.C 6.1\include"
MySQL libs... /LIBPATH:"C:\Program Files\MySQL\MySQL Connector.C 6.1\lib" libmys
ql.lib
******************************

***** PostgreSQL *************
PostgreSQL includes... /I "C:\Program Files\PostgreSQL\9.4\include"
PostgreSQL libs... /LIBPATH:"C:\Program Files\PostgreSQL\9.4\lib" libpq.lib
******************************

***** SAP/Sybase *************
SAP/SYBASE includes... /I "C:\SAP\OCS-16_0\include"
SAP/SYBASE libs... /LIBPATH:"C:\SAP\OCS-16_0\lib" libsybct64.lib libsybblk64.lib
 libsybcs64.lib
******************************

***** ODBC *******************
ODBC includes...
ODBC libs... user32.lib gdi32.lib odbc32.lib odbccp32.lib
******************************

***** DB2 ********************
DB2 includes... /I "C:\Program Files\IBM\SQLLIB\include"
DB2 libs... /LIBPATH:"C:\Program Files\IBM\SQLLIB\lib" db2api.lib
******************************

***** Firebird ***************
Firebird includes... /I "C:\Program Files\Firebird\Firebird_2_5\include"
Firebird libs... /LIBPATH:"C:\Program Files\Firebird\Firebird_2_5\lib" fbclient_
ms.lib
******************************

***** Informix ***************
Informix includes... /I "C:\Program Files\IBM Informix Software Bundle\incl\cli"

Informix libs... /LIBPATH:"C:\Program Files\IBM Informix Software Bundle\lib" ic
lit09b.lib
******************************

***** C# *********************
******************************

***** Perl *******************
Perl prefix... C:\Perl64
Perl version... 520
******************************

***** Python *****************
Python prefix... C:\Python27
Python version... 27
******************************

***** Ruby *******************
Ruby prefix... C:\Ruby
Ruby version... 2.2.0
******************************

***** PHP ********************
PHP prefix... C:\PHP
******************************

***** Java *******************
Java prefix... C:\Program Files\Java\jdk1.8.0_25
******************************

***** TCL ********************
TCL prefix... C:\Tcl
******************************

***** node.js ****************
node.js prefix... C:\Program Files\nodejs
******************************

***** Summary ***********************************************
 Version      : 0.64

 APIs         : C/C++       yes           Perl       yes
                Python      yes           Ruby       yes
                PHP         yes           Java       yes
                PHP PDO     yes           ODBC       yes
                TCL         yes           C#         yes
                node.js     yes

 Databases    : Oracle8     yes           MySQL      yes
                PostgreSQL  yes           SAP/Sybase yes
                ODBC        yes           DB2        yes
                Firebird    yes           Informix   yes
*************************************************************

If you expected a Database or API that doesn't show up in the Summary
then the configure script probably couldn't find a package it needed to
build it.  You can manually specify package locations using command line
options.

Type:  cscript /nologo configure.vbs --help   for a list of options.
Drop-In Replacement Library for PostgreSQL

The drop-in replacement library for PostgreSQL has been improved somewhat. PQprepare/PQexecPrepared and bind variables are now supported.

"softttl"

There's also a new, "softttl" parameter for dynamically spawned connections.

From the Configuration Reference:

softttl - The total number of seconds that a dynamically spawned connection intends to live. When the connection notices that it has been alive for this number of seconds, it voluntarily shuts down, but it only checks after each client session. Thus, the connection will ignore this parameter until it has handled at least one client session, and it could live longer than this time if a client session takes a long time, or if it sits idle for a long time between client sessions. Setting this parameter to 0 disables it. Defaults to 0 (disabled).

It's just another way to prevent dynamically spawned connections from living too long. It can be used as an alternative to maxsessioncount.

Of course, this release features a few bug fixes too.

Full ChangeLog follows:

  • fixed bug in sqlrsh that caused it to filter out non-ascii characters
  • added informix connection module
  • added shutdown loop detection in sqlr-listener
  • fixed dbhostname/dbipaddress requests
  • added bind variable format validation to php pdo driver
  • added softttl parameter
  • added db/language detection to configure.vbs
  • added passwordfunction parameter to "sqlrelay" authentication module
  • added -disable-crash-handler command line option to server processes
  • postgresql connection module PQclear()'s the result of PQprepare() now, fixing a memory leak
  • updated postgresql connection module to handle invalid bind variables properly, and without crash
  • added various missing postgresql functions to the postgresql drop-in library, most notably PQprepare, PQexecPrepared, PQexecParams
  • simplified sqlrpaths/sqlrcmdline classes a bit
  • renamed sqlrconfigfile to sqlrconfig
  • implemented a modular configuration framework
  • config files can be loaded over protocols supported by libcurl now or http if rudiments compiled withougt libcurl support
  • config files can now be "link files" which just refer to other local config files, local config directories, or remote config files
  • updated sqlr-start and sqlr-scaler not to pass the default -config value if sqlr-start wasn't explicitly started with -config
  • fixed a bug that could cause sqlrsh to act funny if the last character prior to the command delimiter was a single or double quote
  • fixed a stacked result-set translation module bug
  • applied George Carrette's patch to fix potential PDO null-dereferences

Rudiments 0.54 Release Announcement

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

There are a few bug fixes in this release, and one or two new features.

URL Class

The most significant feature in this release is the addition of a url class, and incorporation of it into the XML classes.

The url class inherits from file and implements read-only access to arbitrary URL's. HTTP URL's, with basic-authentication are supported on all platforms. On Linux/Unix, if you have a modern enough version of libcurl, then Rudiments will use it and some URL's supported by libcurl will also be supported.

See the url class documentation for more info.

The url class has been integrated into the xmlsax class. So, xmlsax::parseFile() can now be passed a url, rather than just a local file. Since the xmldom class uses xmlsax, then xmldom::parseFile() can also take a url.

Other New Features

The leftTrim(), rightTrim() and bothTrim() methods of the charstring class trim all whitespace now, not just spaces.

There's a charstring::isNullOrEmpty() method now which returns true if the string is either NULL or "" and false otherwise.

The filedescriptor::read() methods which read until a specified terminator is found now have variants with a maxbytes option, which sets the maximum number of bytes that can be read before giving up if no terminator is found.

Full ChangeLog follows:

  • tweaked code in configure script to remove -Werror* to be non-greedy
  • updated semaphoreset/sharedmemory destructors to remove the semaphoreset/sharedmemory segment last
  • added url class that uses libcurl (read-only for now)
  • added support for urls to xmlsax (and thus xmldom and codetree)
  • added http support to the url class for platforms that lack an appropriate libcurl
  • charstring 1-argument trim methods trim all whitespace now, not just spaces
  • added charstring::isNullOrEmpty()
  • added read-until-terminator methods with maxbytes option
  • applied George Carrette's listener class patch to not close invalid-epoll filedescriptors

Monday, August 31, 2015

SQL Relay 0.63 Release Announcement

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

This release mainly focused on platform-compatibility, fixing failures to detect or build against various dependencies on modern versions of Fedora, Debian, Solaris, FreeBSD, and NetBSD.

A few bugs have been fixed as well, and there are a few changes.

Changes

The "oracle8" database type has been updated to just plain "oracle". So, in your sqlrelay.conf file, you should use dbase="oracle" rather than dbase="oracle8". "oracle8" is still supported, but the docs all say to use "oracle" now. Oracle 8i, 9i, 10g, 11g, and 12c have all come out since the driver was first written, and OCI hasn't undergone any massive rewrites in all of that time, so the name change definitely seems overdue.

Similarly, the "sybase" database type has been updated to "sap". SAP bought out Sybase a few years ago and ASE 16.0 is marketed as SAP ASE, not Sybase ASE. "sybase" still works, but the docs all say to use "sap" now.

There is also now an "id" parameter that can be set in the database connect string. If it's set, then if a client calls the identify() method or the sqlrcon_identify() function, it will return whatever value "id" is set to.

This is useful, for example, if an app requires that SQL Relay identify the database as "sybase" or "oracle8", as the name changes cause them to identify themselves as "sap" and "oracle" now, by default. It's also useful when connecting to an ODBC database. By default, SQL Relay identifies an ODBC database as "odbc", but it might be useful to the app to know what actual type of database is being accessed, rather than just "odbc". Similarly, FreeTDS connections can be id'ed as "sap", "sybase", "mssql", etc. rather than "freetds".

For example, with this configuration:

<instance id="mssqlexample" dbase="freetds" ... >
    ...
    <connections>
        <connection string="user=...;password=...;id=mssql;..." ...>
    </connections>
</instance>

In this code:

    ...
    sqlrconnection sqlrc("sqlrserver",9000,NULL,"...","...",0,1);
    ...
    identity=sqlrc.identify();
    ...

"identity" would be "mssql".

Bug Fixes

The default "maxitembuffersize" for Firebird is 32768 now, bumped up from 4096. Firebird has supported 32768-byte varchars for quite some time now. This should have been the default for all that time.

A few of the ADO.NET Data Provider classes implement the IDisposable pattern, but were missing destructors. The missing destructors have been added.

The setAuthTimeout() and setResponseTimeout() methods weren't properly exposed in the Ruby API. They are now.

And a few other things too... Full ChangeLog follows:

  • suppressed ruby configure warning
  • fixed glib detection if pkg-config isn't present
  • fixed mono detection on freebsd
  • fixed node.js yes/no misreport bug in configure script
  • added freetds tcl test
  • fixed slashes in tcl tests for windows
  • updated docs to mention firebird buffer size parameters
  • updated MAX_ITEM_BUFFER_SIZE to 32768 for firebird
  • fixed ruby detection on fedora 22
  • fixed missing export for setAuth/ResponseTimeout in ruby api
  • added manual include of inttypes.h in php api to work around issue with define/undef games, revealed on openbsd 5.7
  • tweaked ruby cflags script for debian 8
  • refactored mysql detection
  • tweaked unixodbc detection to also look in /usr/include/odbc
  • renamed "oracle8" connection plugin to "oracle"
  • renamed "sybase" connection plugin to "sap"
  • updated identity tests in api's/cmdline clients to use charstring::contains() rather than !charstring::compare()
  • implemented missing destructors for ADO.NET Data Provider

Rudiments 0.53 Release Announcement

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

This release features a bit of clean-up, and a few new features.

XML Improvements

The XML classes xmlsax, xmldom, and xmldomnode now support tag namespaces. The namespace is now an element of the xmldomnode and the various methods for navigating, adding, updating, and deleting nodes, all support namespaces now.

The xmldomnode class also features new appendXml/insertXml and appendXmlFile/insertXmlFile methods which allow a string or file containing XML to be parsed and inserted or appended directly into an existing tree.

codetreegrammar Class Exposed

The codetreegrammar class is now exposed. This allows the codetree class to use the same already-parsed grammar for successive parse or write passes without having to re-parse it.

DTD Class Removed

The outdated and long-unused dtd class has been removed.

Serial-port Classes Disabled By Default

The serialport, serialportprofile, modemserver and modemclient classes are omitted from the default build now, but can be enabled using the configure script's command line options.

The full ChangeLog follows:

  • exposed codetreegrammar class and updated codetree class so that successive parses/writes can use the same grammar without having to reload it
  • added insert/appendXml(File) methods to xmldomnode to parse and insert/append xml strings or files to a node
  • fixed FILE->fileno detection for solaris 11.2, which doesn't have it at all
  • llabs() is detected and preferred to abs()
  • added partial xml namespace support (namespaces for tags)
  • added partial support for extended ascii character set to character class
  • added setgroups() call before setuid() call to drop extraneous groups
  • updated rudiments-config man page, slightly
  • removed outdated and unused dtd class
  • updated file::resolveSymbolicLink() to use directory::maxPathLength(filename) as a starting point
  • updated directory::getCurrentDirectory() to use MAX_PATH as a starting point
  • serialport/serialportprofile, modemserver/modemclient classes are disabled in the default build now
  • updated intro docs

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

Rudiments 0.52 is out

Rudiments version 0.52 is now available.

The ChangeLog is fairly short though each item involved more work, on average, than usual.

Over the past few years, I've done some trans-compiler work. The codetree class is one result of all that. The compiler class is another. So are the new xmldomnode methods. The compiler class is really just a framework for plugins. The plugins, codetree, and xmldomnode do all the real work.

The rpcentry class has been removed. I only originally included it for the sake of completeness, but have had no occasion to use it in 15 years. The rest are bug fixes and cleanup.

Full (though short) ChangeLog follows:

  • tweaked codetree's recursive break logic a bit
  • added compiler class
  • added case-insensitive methods to xmldomnode
  • added methods to xmldomnode that operate on descendents (as opposed to just operating on direct children)
  • added methods to xmldomnode for deleting, wrapping, unwrapping
  • fixed dynamiclib::getError() bug on windows
  • tweaked -Werror detection in configure script
  • removed rpcentry class
  • added file::setPermissions() methods

Wednesday, August 5, 2015

SQL Relay License Update

Background

SQL Relay is made up of a bunch of different parts: the server, the command line clients, API's for various languages, tests, documentation, etc. The server and command line clients have always been licensed under the GPL. The API's have been licensed under various licenses, as appropriate to each API - LGPL, Artistic License, Creative Commons, etc.

In the late 90's, when I first decided to use the GPL for the server, the open source community seemed to interpret the GPL as allowing dynamic linking of non-GPL-compatible libraries/plugins/modules/drivers into GPL-licensed programs. The Linux kernel was the poster boy for this interpretation, as hardware vendors provided (and still provide) closed-source drivers, under various non-GPL-compatible licenses. In the database arena, GPL-licensed programs like sqsh and tora illustrated this same interpretation.

The rationale seemed to be that the GPL-licensed program included instructions for loading and interfacing with a non-GPL library, but not the library itself. Headers generally "didn't count" so presumably neither did whatever bits of the library the linker needed to examine.

The GPL-licensed program and non-GPL library/plugin/module/driver were considered separate. They were only combined to create a derivative work at runtime. Copies of the pages of memory that composed that work could not be lawfully distributed, but distribution as separate components was fine.

That seemed to be conventional wisdom at the time.

I wasn't totally ambivalent to the politics associated with the GPL, but really, I needed to choose a license, the GPL was popular, and seemed to fit.

Since then, debates have raged. GNU has made their position abundantly clear: programs that are capable of sharing the same address space with a library/module/plugin/driver are a derivative work of the two, retroactively. Community opinion seems to be divided. The Linux development community is said to have a "gentlemen's agreement" with hardware manufacturers, but nothing in writing. There have been a few court cases and they've gone both ways.

These developments put projects like SQL Relay in an interesting position. Obviously it is the intention of the authors to allow linking with various non-GPL-compatible libraries, but under modern interpretation, the license doesn't explicitly allow that.

As of the next release, 0.61, it will.

The License Exception

For cases like this, GNU recommends a "license exception". The SQLRelay Server, as defined in the file COPYING, at the root of the source distribution, will carry the following exception:

Linking The SQLRelay Server statically or dynamically with other modules is
making a combined work based on The SQLRelay Server. Thus, the terms and
conditions of the GNU General Public License cover the whole combination.

In addition, as a special exception, the copyright holders of The SQLRelay
Server give you permission to combine The SQLRelay Server with free software
programs or libraries that are released under the GNU LGPL and with code
included in the standard release of the following libraries (or modified
versions of such code, with unchanged license):

* OpenSSL and its dependencies.
* Perl-Compatible Regular Expressions (PCRE) and its dependencies.
* IBM DB2 database client libraries and their dependencies.
* Firebird database client libraries and their dependencies.
* FreeTDS client libraries and their dependencies.
* MDB Tools client libraries and their dependencies.
* MySQL database client libraries and their dependencies.
* ODBC client libraries and their dependencies.
* Oracle database client libraries and their dependencies.
* PostgreSQL database client libraries and their dependencies.
* SQLite client libraries and their dependencies.
* Sybase/SAP client libraries and their dependencies.
* Any other programs or libraries as designated, in writing, now or in the
  future, by David Muse, his authorized agents, authorized heirs, or authorized
  agents of his authorized heirs.

You may copy and distribute such a system following the terms of the GNU GPL
for The SQLRelay Server and the licenses of the other code concerned{, provided
that you include the source code of that other code when and as the GNU GPL
requires distribution of source code}.

Note that people who make modified versions of The SQLRelay Server are not
obligated to grant this special exception for their modified versions; it is
their choice whether to do so. The GNU General Public License gives permission
to release a modified version without this exception; this exception also makes
it possible to release a modified version which carries forward this exception. 

Some of the named libraries are either LGPL already or otherwise GPL-compatible, but they are listed in case that changes in the future. If I missed something, the "Any other programs or libraries..." clause makes it possible to add whatever I missed too.

The big surprise: OpenSSL. Apparently it's licensed in a way that makes it non-GPL-compatible. Who knew? Apparently the authors of wget did. Did anyone else?

Modules

In addition to making it clear that those specific libraries are OK, there is another motivation to the license update.

SQL Relay has several module frameworks. You can do all kinds of things with modules - logging, password encryption, alternative authentication mechanisms, query filtering, query translation, result set translation, and more... And additional frameworks are planned.

Without the license update, module development is sketchy. For example:

Can I write a connection module for Informix? Good question.

OpenLDAP has an odd license, is it legal to develop an OpenLDAP authentication module? Who knows?

Can a software development firm write a module for a client that interfaces with some system that the client already uses via a non-GPL-compatible library? Maybe.

Can a software vendor write a module to interface with some proprietary system they developed? Also hard to say.

What about me, personally? Can I, personally, write proprietary plugins? As the primary copyright holder, I'd think so. But can I really?

The "Any other programs or libraries..." clause in the license update provides a path to these kinds of things. New libraries can be approved. If in doubt, just ask david.muse@firstworks.com and get a definitive answer, in writing.

As a guideline, the answer will almost certainly be yes for "client libraries" and their dependencies. By client libraries, I mean, there's some system running in its own address space and there's a library who's primary function is to provide access to it. That library is a "client library."

For other libraries, it would depend on the details.

Authority

By what authority do I change the license?

Mostly my own, partly that of the open source community, and partly that which was granted by other contributors.

I am the primary copyright holder for The SQLRelay Server. I'm not exactly sure what percentage of it I either wrote or own, but I'd bet that it's over 99%. The API's were largely contributed, but they're not affected by this change.

Over the years, patches from various contributors were applied to The SQLRelay Server, but the vast majority were "trivial bugfixes." While important, they were short and didn't demonstrate novel insight. I.e. the patch affected fewer than ten lines, the bug was easy to find, the bug is obvious now that it's been pointed out, and the fix couldn't have been implemented much differently. If the patch was short and met 2 out of 3 of the other criteria, then I deemed it trivial. Trivial bugfixes are likely not subject to copyright. This analysis and action seem consistent with those of the open source community in license update scenarios.

Some non-trivial patches were also integrated. But, I was able to get approval from the current copyright holders to make the license change. For good measure, I also got approval from all the trivial bugfixers that I could get a hold of too.

I did some contract jobs to integrate non-trivial patches against older versions into the then-current version of SQL Relay, but the language of the contract entitled me to the results.

There were also a few non-trivial patches that were once integrated into The SQLRelay Server, but have since been completely removed.

If anyone that I haven't received approval from can demonstrate that they contributed non-trivial code to The SQLRelay Server, a derivative of which still exists in the current version, and disagrees with the license change, please contact me at david.muse@firstworks.com and we'll work something out.

Ramifications

So, what does the license change mean to you?

Most likely nothing at all. The license is likely now just more consistent with what you assumed already. The GPL, in general, still applies. It is just definitively legal to use SQL Relay as it was intended.

The only practical difference is that if you want to write a module that integrates with some other system via a non-GPL-compatible client library, then it is very likely possible to do so.

"But this license change undermines the purpose of the GPL!"

Perhaps. SQL Relay was not written to crusade for the GPL. I don't intend to undermine the GPL though. In fact, rather than argue over how to interpret the GPL, I'm doing this in deference to GNU, who recommend exactly this action.

The GPL is great. Free software wouldn't be where it is without the GPL. With this update, it's still a good fit for this project.

Friday, July 31, 2015

Sourceforge is back

About a week and a half ago, SourceForge suffered a storage outage that took down just about everything they host.

SQL Relay, Rudiments, and a few other firstworks.com projects are hosted at SourceForge, and during the outage, the project websites, downloads, and CVS access were all down for periods of time. The SourceForge folks worked pretty tirelessly on getting everything restored, and as of this evening, everything appears to be back to normal.

CVS access to SQL Relay and Rudiments source was the last thing to be restored. Apparently they're hosted on non-Allura-backed CVS, the oldest platform that SourceForge has. I guess that makes sense, given that both projects began being hosted by SourceForge in 1999.

This has been the only outage that these projects have been affected by in all that time, so for now, I'm sticking with SourceForge. However, the source code repositories will likely be moved from CVS to Git sometime in the next few months. I'd been meaning to do that anyway. I guess this is a good reminder to get on it.

Saturday, June 20, 2015

SQL Relay 0.60 is out

This release fixes various obscure (though potentially crippling) bugs and shortcomings, and issues with specific compilers.

The most notable fix is, on Windows, that you can now successfully specify an installation path when using the installer, and it will actually work. In previous releases, you could specify a path, and everything would be installed there, but it was largely ignored by the applications, which looked for files in and tried to write files to the default path.

Full ChangeLog follows...

  • fixed true->false transposition in sqlrservercontroller::interceptQuery that could lead to a reLogIn loop
  • disabled -Werror for gcc < 2.7
  • applied George Carrette's patch to fix PDO connectstring options
  • migrated directory/file paths info into sqlrpaths class
  • removed undocumented and not-so-relevent-these-days MAX_CONNECTIONS/overridemaxconnections failsafe in sqlr-start
  • fixed java header detection for javac located in /usr/bin
  • updated java api to support non-null-tolerant implementations of environment::NewStringUTF()
  • the perl api builds correctly on OSR5 again
  • the postgresql sslmode is omitted entirely from the connect string now, if it's disabled, to prevent problems with older versions of postgresql that don't support the parameter at all
  • mono 2.8 or greater is required now
  • added a datedelimiters attribute to the instance tag in sqlrelay.conf to limit what date delimiters are used when translating dates
  • re-added mssqlserver detection, which had been accidentally removed
  • fixed statically-linked build
  • fixed mysql lob field bug that could cause a crash

Rudiments 0.51 is out

The 0.51 release of rudiments is largely a clean-up release. Regressions, inconsistencies, and issues with specific compilers have been addressed. Work on support for OpenVMS has begun as well.

Full ChangeLog follows...

  • added missing inline qualifiers to dynamicarray/staticarray method implementations
  • added test for clock_settime, datetime class uses it if other set-time functions aren't available, returns false and sets errno=ENOSYS if no set-time function is available
  • directory::fpathConf now falls back to MAX_PATH code if fpathconf exists but there's dirfd() or anything like it
  • file::createFifo returns false and sets errno=ENOSYS on platforms that don't support fifo's now
  • file::generateKey returns -1 and sets errno=ENOSYS on VMS now
  • file::createHardLink returns -1 and sets errno=ENOSYS on platforms don't support hard links now
  • fixed missing breaks in stdio::flush
  • userentry::getPassword/getRealName return NULL on VMS now
  • unix sockets are faked on VMS now
  • threadmutex is built and installed now whether or not threads are supported, but if threads are not supported, its methods are degenerate and return success
  • filesystem::getCurrentProperties() returns false and sets errno=ENOSYS on VMS now
  • implemented degenerate groupentry for VMS
  • fixed file::getBlockCount() crash that could occur if the filesystem's block size was reported as 0
  • tweaked copy constructors and = operators in staticarray/dynamicarray classes to work with older compilers
  • disabled -Werror for gcc < 2.7
  • filedescriptor::printf uses fprintf or fdopen/vfprintf when possible, in leiu of falling back to charstring::printf
  • charstring::printf tries the null device before falling back to a scratch file
  • the rate at which charstring::printf expands its buffer is now exponential up to 1024 bytes
  • added sys::getDirectorySeparator()
  • fixed trailing-slash-followed-by-quote issue when building windows command lines
  • added --enable-enosys-notify configure option for runtime notifcations when methods set ENOSYS
  • cleared errno before each EINTR loop
  • fixed sys::sync() segfault
  • fixed ssl tests
  • removed intervaltimer class
  • updated sys::getPhysicalPageCount/getAvailablePhysicalPageCount for Windows
  • added sys::getAllocationGranularity, updated code to use it instead of getPageSize, in most cases
  • fixed copy-constructor/=-operator bugs in dynamicarray class
  • added config_vs2013.h for VS 2013
  • fixed several permissions-related issues for WinNT 4
  • allowShortReads/useBlockingMode are set by default for stdinput now
  • added wrapper for atexit()
  • changed process::getRealUser/GroupId to getUser/GroupId to match setUser/Groupid
  • added process::setEffectiveUser/Group methods
  • fixed and documented auto-resume behavior in snooze methods
  • fixed detection of double-dashed command line parameters without values
  • removed redundant charstring::rightPad()
  • renamed charstring::padString() to charstring::pad()
  • added safePrint methods that take unsigned char arguments
  • fixed bytestring::findFirst() with unsigned char needle, when needle is 0
  • fixed bugs in linkedlist::moveBefore/moveAfter
  • fixed bugs in singlylinkedlist::removeAll()
  • removed arg parameter from thread::setFunction(), added thread::setArgument(), and added thread::create(arg)
  • changed thread::create() to thread::run()
  • wrote lots of example code and updated the programming docs
  • removed unused shmfile class
  • fixed several LocalFree's that should have been delete[]'s

Friday, April 24, 2015

SQL Relay 0.59 is out

SQL Relay version 0.59 is now available!

Significant New Features

There are a few...

Improved Support for Windows

This is the main new feature in this release.

Support for Ruby and TCL have been added. Now, on Windows, you can develop in C, C++, any .NET-supported language, Java, Perl, Python, PHP, Ruby and TCL.

Support for many additional databases has been added. SQL Relay can now be run on Windows, against Oracle, Sybase/SAP, DB2, Firebird, Postgresql, Mysql and ODBC-supported databases. The query router is also supported now too.

Performance isn't as good on Windows as it is on Unix/Linux yet, but it works and it appears to be stable.

On the development side, the build process has been completely refactored to use nmake and now supports a wide variety of versions of Visual Studio. It has been tested with VS2005, 2008, 2010, 2012 and 2013. VS6 and VS5 mostly work, but not entirely, yet. The build takes infinitely less time and it's now possible to fix something and nmake and nmake install from within the directory of the modified file, like one would be accustomed to on Unix/Linux. The Windows build process isn't yet as capable as the Unix/Linux build process, but it is greatly improved. See Installation on Windows for more information.

ODBC Improvements

Arguably this falls under Improved Support for Windows, but technically some of these improvements apply to UnixODBC/iODBC as well.

ODBC has long worked on Windows, but configuring it required manual modifications to the registry. No longer! The ODBC driver manager dialog exists now. On Windows, it should now be as easy to configure an ODBC connection to SQL Relay as it is to any other database.

The driver supports getting and setting many more attributes, and SQLTables works now. This enables many, many more ODBC applications to work with SQL Relay. It's been tested against a long list of apps with good success. If you've had trouble with it in the past, try this version.

MySQL Improvements

The MySQL database connection configuration now supports the maxselectlistsize and maxitembuffersize parameters, similar to Oracle, Sybase, DB2 and ODBC. This enables tuning of memory usage at runtime. See Tuning SQL Relay - Memory Usage for details.

LOB support is much improved with MySQL now too. In the past, the maximum number of bytes that could be fetched from a LOB column was limited by a macro in the source code - the same macro that defined the maximum size of a non-lob column. LOB's are now fetched in chunks and the amount of data that can be fetched is unlimited.

Support for Mono

SQL Relay has long had a C# API and ADO.NET driver, but they were classically only supported on Windows. These API's now work on Unix and Linux platforms as well, using Mono.

Less Significant, But Still Interesting New Features

The number of bind variables per-query was originally limited by a macro in the source code. Now, on the client-side, the number of bind variables is unlimited. Well, it's only limited by memory. On the server side, there's a new maxbindvars parameter in the sqlrelay.conf file that can be used to limit the number of bind variables that the server will accept. maxbindvars defaults to 256, but if you have an application that needs to send 1000 bind variables, you can just set it to 1000.

Login warnings are now printed on the screen, but not interpreted as errors. For example, if the database password is going to expire soon, but hasn't yet expired, the warning is displayed, but no longer interpreted as an error.

The sqlrsh command line client now supports the "cache" and "opencache" commands to cache result sets to a file and fetch result sets from a file. Not sure how many people actually use that feature, but sqlrsh supports fiddling with it now.

The logger modules have an enabled parameter which can be set to "yes" or "no". It's no longer necessary to comment out or remove the logger configuration to disable logging, you can just set enabled="no". See Logger Modules for more information.

Firebird recently added support for global temporary tables. Their implementation is very similar to Oracle's. SQL Relay now manages global temporary tables identically for Oracle and Firebird. There is even a droptemptables parameter in the Firebird connection configuration, analogous to the same parameter for Oracle.

In fact, support for global temporary tables has been generally improved. In the past, tables created during the current SQL Relay session were tracked and truncated at the end of the session, but global temporary tables created during previous runs of SQL Relay or outside of SQL Relay altogether were ignored, leading to those global temporary tables not being truncated at the end of the session. The Oracle and Firebird connections now support a globaltemptables parameter that can be set to a list of tables that should be truncated at the end of each session, or to "%", which causes SQL Relay to query the database for the list of global temp tables to truncate.

The Ruby DBI driver has been retired. I guess that's not a new feature, but it's something that was done in this release. Ruby DBI seems to have fallen out of favor and the driver was becoming increasingly difficult to maintain.

Tweaks and Fixes

Lots of small things have been tweaked, fixed, or improved as well

  • Binding a NULL to an integer works with db2 now.
  • Added a semaphore to ensure that the listener doesn't hand off the client to the connection until the connection is ready, eliminating a race condition on the handoff socket that could occur if the connection timed out waiting for the listener just after the listener had decided to use that connection.
  • Oracle temp tables that need to be truncated at the end of the session are truncated with "truncate table xxx" now rather than "delete from xxx".
  • Oracle temp tables that need to be dropped at the end of the session are truncated first, rather than the connection re-logging in.
  • An ora-14452 error (basically indicating that a temp table can only be dropped after being truncated, or if the current session ends) does not automatically trigger a re-login any more.
  • Updated cachemanager to use directory::read() directly instead of directory::getChildName(index).
  • Made cache ttl a 64-bit number.
  • Updated odbc connection code to use new/delete and rudiments methods rather than malloc/free and native calls.
  • Fixed command line client crash when using -id "instance" with an instance that uses authtier="database".
  • Fixed bugs that could make reexecuted db2 selects fail and cause a database re-login loop.
  • Tweaked spec file to remove empty directories on uninstall.
  • Fixed typo that could sometimes cause a listener crash.
  • Postgresql and MDBTools return error code of 1 rather than 0 for all errors now.
  • Fixed bugs related to autocommit with db's that support transaction blocks.
  • Fixed various bugs in sqlrconfigfile that caused sqlr-start with no -id to crash or behave strangely sometimes.
  • Updated the slow query logger to show the date/time that the query was executed.
  • Consolidated c, c++ and server source/includes down a few levels.
  • Fixed a misspelling in sqlrelay.dtd.
  • Swapped order of init directory detection, looking for /etc/init.d ahead of /etc/rc.d/init.d to resolve conflict with dkms on SuSE Enterprise.
  • sqlr-start spawns a new window on Windows now.
  • updated mysql connection to allow mysql_init to allocate a mysql struct on platforms that support mysql_init, rather than using a static struct.
  • Fixed subtle noon/midnight-related bugs in date/time translation.
  • Updated mysql connection to get affected rows when not using the statement api.
  • Updated mysql connection not to use the statement API on windows, for now.
  • Disabled mysql_change_user, for now.
  • Fixed blob-input binds on firebird.

Give it a try, report any issues!

Rudiments 0.50 is out

Rudiments 0.50 is now available!

The most significant feature in this release is a refactored, extent-based implementation of the variablebuffer and stringbuffer classes. The original implementation was crude, sufficient for storing fairly short strings, small in code size, but scaled poorly. The new implementation scales well while the code size still remains small.

There are also new dynamicarray and staticarray classes. Dynamicarray implements an array that expands as needed. It's also extent-based and scales well. Staticarry is just a thin wrapper around a C array. It only exists because you might want a dynamicarray who's members are static arrays, and there's no good way to do that in C++ without a wrapper. Or, at least none that I'm aware of.

This release also includes improved support for Windows and various other odd platforms, as well as various tweaks and fixes.

Full ChangeLog follows:

0.50 - added dictionary::detach and dictionary::getValue(key)
        added xmldom::getNullNode()
        improved xml printing for text nodes
        signalmanager::waitForSignal(NULL) works as expected now
        updated charstring::httpEscape not to escape legal symbols
        added flush() method to stdio classes
        added detection and support for vsnprintf_s
        fixed serviceentry, protocolentry and rpcentry on windows
        updated *entry classes for NULL and uninitialization safety
        implemented filesystem::initialize(fd) for Windows
        updated filesystem::initialize() with NULL and -1 to behave in
                predictable ways now, updated documentation for them too
        abstracted _get_osfhandle calls and added an invalid parameter callback
        updated filedescriptor to use bit fields for all of its flags
        implemented file::changeOwner() for windows
        removed xattrs stuff
        removed SSL-related ifdef's from headers and implemented degenerate
                SSL routines for when SSL isn't supported
        timezone conversion works on windows now
        refactored bytebuffer and stringbuffer to be extent-based
        fixed a subtle stringbuffer with initialcontents bug
        refactored stringbuffer to use bytebuffer::write/appendFormatted
                directly instead of charstring::parseNumber
        implemented dynamicarray and staticarray classes
        refactored build process to use nmake and be compatible with many
                different versions of MS Visual Studio
        updated vsnprintf_s calls for platforms where it's not defined
        added configure test for bool and true/false and definitions for
                platforms that don't define them
        updated timed semaphore waits - if seconds or nanoseconds are negative
                then the timed wait acts like an un-timed wait
        added support for getsockopt with size_t optlen parameter
        fixed strncasecmp on windows when str2 is longer than str1
        xmlsax uses memorymap on Windows now
        fixed file::lockFile() and file::unlockFile() on Windows
        fixed charstring::printf on Solaris 2.5.1
        fixed datetime::getSystemDateAndTime() for Windows without 64-bit
                integers
        fixed potential double-CloseHandle in thread class

Wednesday, April 1, 2015

SQL Relay 0.58.1 is out

It appears that 0.58 shipped with a bug that caused a few people to scratch their heads for quite a while.

The bug caused sqlr-start to crash, sometimes, under very specific circumstances.

The first is if an instance is defined with no port and no socket (ie. falling back to the default port of 9000). This would fairly reliably crash.

The second is very intermittent. Basically, if you call sqlr-start by itself, with no id, expecting that it will start all instances where enabled="yes" is configured, it will crash. Sometimes. Depending on a lot of random things. It turned out that a few variables were uninitialized, and random crashing a fairly classic behavior in that case.

Both issues are fixed in the 0.58.1 release. So, if you've had problems like those with 0.58, then give 0.58.1 a try.

It can be downloaded here.

Thursday, March 12, 2015

ODBC Progress

SQL Relay has long supported ODBC to some extent, both on the server and client side. Ironically though, the client-side has always worked a lot better on Unix/Linux than Windows.

That's just a terrible state of affairs. Windows is where ODBC is most commonly used. Yeah, ADO.NET's the in-thing these days, but there's still a whole world of ODBC apps out there for Windows. You ought to be able to aim them at SQL Relay.

A week or so ago, I got the driver working well enough to be usable if you don't mind manually editing .reg files and loading them using regedit. A good step, but not good enough for the average user. The driver needed a real configuration interface.

Last night I made some good progress though.

I guess I could have drawn a better looking UI using modern tools, but I kind-of want to maximize compatibility, and it didn't seem right to pull in the .NET framework just to be able to configure an ODBC DSN.

It's been a looong time since I did anything with the WinAPI directly, and it was an interesting challenge... "How do I get the background colors to match on the labels again?" "What's the trick to getting tab-navigation to work if your edit controls are attached to a child window?"

But, I got it going, and that plain little window works pretty well.

Apps are working pretty well too.

Little-by-little, they're revealing discrepancies, but it's been a while since anything just didn't work. Things are looking good.

When 0.58 is out, you ought to be able to aim your Windows-based ODBC apps at Relay.