SQL Relay - 0.54 is out

SQL Relay version 0.54 is now available!

This release mainly fixes a lot of subtle or obscure bugs. It also fixes problems detecting a bunch of things on a bunch of odd platforms - for example TCL is now properly detected on multi-arch systems like Debian GNU/Linux and 64-bit Sybase and DB2 are properly detected now too. The most significant new features though are a much improved PHP PDO driver and experimental support for a multi-thread (as opposed to multi-process) listener.

To use the new multi-threaded listener, set sessionhandler="thread" (as opposed to sessionhandler="process", which is the default) in the sqlrelay.conf file. There are a few caveats to using this feature though. Currently listenertimeout="0" and handoff="proxy" are not supported when using threads. Those issues will be remedied in a future release.

A good bit of behind-the-scenes work has been done as well. One of the long term goals is multi-protocol support. Ie. the ability to aim a MySQL (or other) app directly at SQL Relay rather than having to port the app to the SQL Relay API or use the drop-in replacement library. The groundwork for this has been laid in this release. The client-server communication has been separated and abstracted and the SQL Relay native API has been refactored as an implementation of the interface.

I'm also working towards complete support on Windows. Currently the C++, C, C# and ADO.NET client API's work on Windows but none of the rest of the API's, nor any of the server components work yet. A lot of the Rudiments library has been ported to Windows in it's most recent release though, and virtually all of the Posix API calls have been replaced with Rudiments calls. There are a few left in the ODBC connection class, but that's all. So, basically when the rest of Rudiments is ported, SQL Relay should work on Windows, and there are only a few things left to port. Some of them are tricky though, like signals, so it could still be a little while.

The full ChangeLog for this release follows:

• fixed bugs that caused problems when unopened result sets were closed
• added protocol identification phase to client/server protocol
• split client protocol handling out into its own class and abstracted it
• updated oracle code not to relogin when dropping tables unless a temp table with an "on commit preserve rows" was run
• updated server to run table-drop and session-end queries after the session-end commit/rollback, as some queries on some platforms (including drop-table on oracle) cause an implicit commit and the rollback needs to be run first
• replaced gettimeofday calls with calls to datetime::getSystemDateAndTime
• fixed a bug in slow query logger that caused it to display the wrong query time
• a few mingw32 tweaks
• fixed a few PDO driver bugs related to affected row counts and last insert id
• implemented PDO exceptions
• implemented support for PDO::ATTR_EMULATE_PREPARES by using substitution variables
• added foundrows and ignorespace options to mysql connect string
• fixed a bug where affectedRows() would return -1 for MySQL selects instead of the same value as rowCount()
• updated sqlr-connection code not to get db host name and ip address at startup unless logging is enabled to work around issues where people fail to put the db host name in dns
• fixed a subtle bind format translation bug that caused errors when translating from oracle-style binds to mysql-style if the varaibles were out of order with the placeholders in the query
• fixed bugs in the mysql and postgresql connection code that assumed that the bind variables were bound in order, independent of their names
• resolved a paradox involved in detecting whether the current mysql query needs to have its bind variables faked or not
• added support for client-side debug-to-file
• added debug-to-file support to command line clients
• tweaked server processes to use process::spawn instead of system() and process::fork()/process::exec()
• added another error string to detect down mysql database
• added support for a multi-threaded listener
• fixed a bug that could cause a crash when logging connection-level errors
• added back x64-detection so sybase and db2 libraries will be detected properly
• added support for forward-only cursors to the PDO driver
• fixed a bug that could cause one (and only one) dynamically spawned connection daemon to ignore its ttl
• added support for get/set result set buffer size to the PDO driver
• updated configure test for xsubpp
• updated configure test for tcl to support multi-arch
• updated configure test for python to support multi-arch
• added custom PDO driver attributes to access db type, version, host name, ip address and bind format
• added custom PDO driver attribute to get/set current database
• added custom PDO driver attribute to set whether or not to get column info when fetching the result set
• updated db2 to re-login when it sees error -30081

Rudiments - 0.45 is out

Rudiments version 0.45 is now available!

Support for microseconds has been added to the datetime class. There's a new thread class too. A significant bug that caused all socket connect() calls with timeouts to fail has been also been fixed.

The most significant feature of this release though is that most of the classes have been ported to Windows. There are still some that haven't but a lot have.

Here's the full Changelog.

• got the directory class working on windows
• got the semaphoreset class working on windows
• implemented passwd/groupentry::getSid and an internal id-name-sid map for windows
• removed meaningless groupentry::getPassword
• improved filesystem class implementation for Windows significantly
• added a test for the memorymap class
• got the memorymap class implementation working on Windows
• got the sharedmemory class working on windows
• added support for microseconds to the datetime class
• build-tweaks for mingw32
• added process::spawn that maps more cleanly on windows than fork/exec
• added thread class
• added send/receiveSocket methods to filedescriptor class for systems like Windows that distinguish between sockets and other file descriptors
• improved file class implementation for Windows
• fixed a bug that caused all connect-with-timeout calls to fail
• fixed a bug that caused permissions::setFilePermissions to fail on directories

Consulting and Development Services

Just a reminder...

Consulting, Support and Integration Services for SQL Relay or other firstworks technologies are available and General Consulting and Software Development Services are also available.

• I have expertise in:
• C/C++ on Linux/Unix
• Embedded Linux systems
• Database-Driven Applications
• System and platform migration
• Systems and applications for small businesses
• And experience with:
• Java, PHP, Python, C#, Visual Basic and other languages.
• Windows and Mac OS X

So how can I help you?

• Need help converting your existing apps to use SQL Relay?
• Need help developing new apps that use SQL Relay?
• Need a feature that SQL Relay doesn't currently provide?
• Need SQL Relay to run on a new platform?

I'm your guy.

• Need a custom app for your small business?
• Need a legacy app migrated to a modern platform?
• Need to squeeze some life out of an old app or older Unix platform?
• Need to integrate some odd set of dissimilar applications?
• Need help with an Embedded Linux app?

I've done plenty of that, all of that, for clients all over the world.  I might be your guy.

Send me an email or give me a ring.  For contact info, click here.

.1 Updates

Hmm, it appears that I was a little too hasty in that last release of Rudiments and SQL Relay.

It turns out there were some documentation errors and they didn't build successfully on every platform in my build farm.  Most significantly though, there was an error in one of the Makefiles that cased a "make clean" to abort partway through the build.  This could cause problems for automated build systems.

So, .1 updates of Rudiments and SQL Relay are now out - 0.44.1 and 0.53.1 respectively.

Enjoy!

SQL Relay - 0.53 is out

SQL Relay 0.53 is now available!

This release contains several significant new features.

The most significant is the inclusion of a PHP PDO driver.  PDO is the most popular abstraction layer for PHP and SQL Relay has been without a driver for it for too long.  Give it a try:
http://sqlrelay.sourceforge.net/sqlrelay/programming/phppdo.html
It is new though, there could be bugs.  Report them to david.muse@firstworks.com if you find any.

Also significant is support for Multiarch systems.  Debian and Ubuntu systems have supported Multiarch for a while now and packages weren't successfully detected on those systems.  They are now, even on non x86/x64 platforms.

The init scripts have been refactored as well.  After installed, SQL Relay should integrate properly with the init processes on most Linux and Unix systems and OS X.

The SQLite Statement API is now supported, both versions of it.  Platforms that can take advantage of it should perform a little better and use less memory.


A few bugs have been fixed too, including a long-standing issue where IPC and socket-related files were left lying around, owned by whoever last ran SQL Relay.  This caused problems if a different user tried to run it.  They are removed now and things work as expected.


The PHP Pear DB and Zope drivers have been removed too.  Packages aren't even available for them on most systems.  Zope is still well-used and I might resurrect the driver some day.  PHP Pear DB seems to have fallen so out of fashion that it's hard to even test it any more.

The sqlr-start program no longer attempts to start the sqlr-cachemanager.  Honestly, it's quirky that it ever did.  There are separate init scripts for them now as well.

What else..

Fixes for a few unlikely-to-be-encountered memory leaks and a few more obscure features.

Complete ChangeLog follows:

• added support for sqlite statement api and native binds
• fixed some leaks related to using sys::getHostName()
• added multiarch detection
• added PHP PDO driver
• fixed a bind variable translation bug where output binds followed by := would not be detected
• dropped zope support (for now)
• dropped PHP Pear DB support
• refactored init script - one script should work on all platforms now
• updated init script installation - should work on virtually all unixes
• added OS X launchd configuration
• updated the sqlr-listener to clean up files related to ipc, sockets and marking whether the db is up or down on exit
• sqlr-start no longer starts the cache manager
• added a second init script for the cache manager
• plugins are statically linked into libsqlrserver if the platform doesn't support shared libraries (or if --disable-shared is specified at configure time)
• the perl API should build with old versions of perl (5.00X) on older platforms (redhat 4.2, 5.2, 6.2, etc.) now
• updated postgresql bind docs
• added dateyyyyddmm parameter
• added yyyyddmm parameter to translatedates translation
• added SQLR_MYSQL_DATE_YYYYDDMM envrionment variable to mysql drop-in library
• added SQLR_ODBC_DATE_YYYYDDMM envrionment variable to odbc driver

Rudiments - 0.44 is out

For immediate release!

Rudiments 0.44

This release has lots of small fixes, optimizations and compatibility improvements.  There are no major changes but lots of refactoring and... fiddling around.

The most significant change is support for multiarch platforms like Ubuntu and Debian.  OpenSSL and PCRE should be detected properly on those platforms now.

Here's the complete ChangeLog for this release.

• fixed inet_aton test to attempt link, not just compile
• fixed vsnprintf test to work on arm linux
• filedescriptor::printf now uses vdprintf, if available, if writes are not being buffered, and vasprintf, if available, if writes are being buffered
• fixed a memory leak in filedescriptor::printf
• added multiarch detection
• applied Simon Martin's getenv-related patch to reset errno and allow getenv to return NULL - fixed a situation where an infinte loop could occur if getenv returned NULL and the most recent error from another system call was EINTR
• added missing print() for const char *'s in linkedlistutils
• tweaks for OSR505
• a few xmlsax optimization fixes
• renamed *Data methods to *Value in linkedlist and dictionary classes
• removed print methods and unlikely-to-be-used static methods from *entry classes
• refactored the static convenience methods of the *entry classes
• refactored xattr code a little to make it smaller
• removed static methods from filesystem class to make it smaller
• removed some static methods from file class to make it smaller
• removed the clientserverfactory class
• slight refactoring of linkedlist and dictionary classes
• various process class fixes for Windows

The Horizon...

Looking ahead...

I like to look ahead.  It's so exciting!  Sometimes at least.  Hopefully this look ahead will be exciting to you.

Coming soon to SQL Relay...

• PHP PDO Support
• SQLite Statement API Support
• Multiarch Support

The next release will include all three.  Exciting?  Maybe.  I guess that depends on your interests.

I've been meaning to implement these things for long time and just now getting around to doing it.

PDO

The PDO support will probably have the greatest impact.  PDO is the most popular PHP database abstraction layer these days and SQL Relay has long lacked support for it.  It's funny, for as popular as it is, it is a little quirky.  There's no obvious way to bind a floating point number, for example.  It looks like you just can't do it.  It doesn't appear to differentiate between Clobs and Blobs either.  They're both just LOB's.  That's actually giving me problems right now.  And it doesn't appear to support out-variables.  It supports in-binds and in/out-binds but not just plain out-binds.  SQL Relay supports in-binds and out-binds but not in/out-binds, so I guess I can't be too particular about another API's lack of support for something, but it's odd.  I'm currently mapping SQL Relay's out-binds to PDO's in/out's.  I hope that works out.

SQLite Statement API

This is an under-the hood change that won't likely have any noticeable effect, other than maybe subtle performance or memory-usage.  SQLite has long had a statement-oriented API and supported bind-variables natively, but SQL Relay has always used the older API and faked bind variables by rewriting the query.

No longer!

The SQLite Statement API is now supported.  Actually both versions of the statement API are supported.  There appears to have been an initial effort and then a follow-up where _v2 was appended to some of the functions and error handling was improved.  Both statement API's are now supported, as is the old sqlite3 API and the even older sqlite API.  So, it shouldn't matter what version of SQLite you're using, they're all supported.

From the outside, there are only two noticeable differences.

The statement API exposes column types, so column types will now be visible and not just "UNKNOWN".

Also, the statement API provides methods for fetching one row at a time, while the older API's either required you to register a callback to handle a row or returned the entire result set at once.  I opted to use the return-the-entire-result-set method and as a result, knew how many rows there were in the result set.  When using the statement API though, the total number of rows in the result set is unknown until they've all been fetched.  So, going forward, if you use setResultSetBufferSize() on the client-side, rowCount() will be unknown.

It's possible that performance will be improved using the statement API but I haven't run any tests to see.  It's likely that memory usage will be better on the server-side, as the sqlr-connection daemon doesn't buffer the entire result set any more.  Depending on the size of the result set, this could improve performance too.  Again, I haven't run any tests to see.

Multiarch Support

Debian and Ubuntu have recently embraced Multiarch.  It's a great idea but it creates headaches for configure scripts.

What is Multiarch?

Some architectures support multiple binary formats.  For example, an x86_64 CPU can run code compiled for x86_64 or x86.  An ARM chip with hardware floating point support can run ARM binaries that depend on hardware floating point support, or soft-float binaries.  Many ARM chips can run thumb code as well.  A MIPS64 chip can run 32 or 64-bit MIPS binaries.  Similar for SPARC64.

In the past, support for multiple architectures on the same system has been shoehorned in by creating /lib and /lib64 directories and putting 32-bit libs in /lib and 64-bit libs in /lib64.  BTW, this also created its share of headaches for my configure scripts, but happened so long ago that I'd forgotten entirely about it.

Multiarch aims to support more than just 32 vs. 64 bit variants.  On a Multiarch system, under /lib and /usr/lib, there are directories like i386-linux-gnu, amd64-linux-gnu, mipsel-linux-gnu and arm-linux-gnueabihf with architecture-specific libraries in them.  An amd64 machine might have both amd64-linux-gnu and i386-linux-gnu directories, enabling it to run 32 and 64-bit binaries.

This presented a challenge.  The configure script has to look in the appropriate subdirectory but the arch command doesn't help.  For example, arch might return i586 or armv7l rather than i386 or arm.  And how should we know to look in arm-linux-gnueabihf?  Where does that gnueabihf come from?  This plagued me until I discovered that on newer multiarch systems,  gcc -print-multiarch will return the multiarch tuple.  Woohoo!

So, now the configure scripts look in the appropriate directory.  If you use gcc -m32 on an x86_64 system, it should look in the i386-linux-gnu subdirectory.  If you use gcc -m64 (or leave out the -m option), it'll look in the x86_64-linux-gnu or amd64-linux-gnu subdirectory, as appropriate.

What does this mean for the user?  Basically on modern Debian and Ubuntu releases, The configure script will "just work" and correctly detect everything installed on the system rather than requiring painful manual intervention.

That's half of the challenge.  The other half is installing my libraries in the right places.  For now, I'm just still installing them in the lib directory.  Currently, the --libdir option to the configure script can be used to install them in the appropriate multiarch subdirectory.  I'll have to see what the debian package build scripts are doing.  They might just be using --libdir and I may not have to do anything.

So, that's what's on the horizon.

There will, of course, be a few bug fixes, and it's possible that the ODBC driver might have some improvements too, but I wouldn't hold my breath.