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
Posted by at

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
Posted by at
Subscribe to: Posts (Atom)