Wednesday, October 16, 2013

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.


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.