Runtime SQL Query Builder v3

There’s a whole new SQL Query Builder available on GitHub. I have basically rebuilt it from scratch as the previous “architecture” (it does not actually deserve to be called that) was just slapping strings together and that approach had reached its limits.

Query builder is now implemented as three units – GpSQLBuilder.AST implements an abstract syntax tree for SQL queries, GpSQLBuilder.Serialize knows how to serialize this tree to a string and GpSQLBuilder creates the tree and calls the serializer when necessary. Now it will be much simpler to a) add support for new SQL keywords and b) create different serializers, for example a ‘pretty print’ version. (If anybody wants to tackle this last task, please go ahead – I’ll be happy to merge a pretty print serializer into the master branch.)

In addition to that I have added support for different Joins: .InnerJoin, .RightJoin, and .FullJoin are now supported in addition to .LeftJoin.

A .&Case method was redesigned a bit. It now returns an interface which can be sent as a parameter to a .Select, .Column, and .OrderBy methods. You can use it like this:

  query := CreateGpSQLBuilder
    .Select(
      CreateGpSQLBuilder.&Case
        .When([COL_2, '< 0']).&Then('0')
        .When([COL_2, '> 100']).&Then('2')
        .&Else('1')
      .&End)
    .From(DB_TEST);

and

  query := CreateGpSQLBuilder;
  query
    .Select.All
    .From(DB_TEST)
    .OrderBy(
      query.&Case
        .When([COL_2, '< 0']).&Then(COL_3)
        .&Else(COL_4)
      .&End);

New version also supports the .Distinct select flag.

I’ve created a unit test suite (actually I did that before the redesign so that I could refactor my code in piece) which now includes 51 tests, all passing.

Portable OmniThreadLibrary

If you care about running OmniThreadLibrary on mobile devices and on OS X, please join the OmniThreadLibrary-For-Mobile-DevelopmentProject page on Google+ where Sean has started to document his progress on doing exactly that – breaking the chains of Windows and bringing OmniThreadLibrary to all supported platforms!

Go, Sean!

Value Never Used – Or Is It?

Sometimes, dcc32 is just plain stupid …

I have this code in the OmniThreadLibrary:

function TOmniThreadPool.Cancel(taskID: int64): boolean;
var
  res: TOmniWaitableValue;
begin
  {$IF CompilerVersion >= 22}
  Result := false; // not really used
  {$IFEND}
  res := TOmniWaitableValue.Create;
  try
    otpWorkerTask.Invoke(@TOTPWorker.Cancel, [taskID, res]);
    res.WaitFor(INFINITE);
    Result := res.Value;
  finally FreeAndNil(res); end;
end;

The IFDEF part is there because otherwise compiler complains about result being potentially undefined.

[DCC Warning] OtlThreadPool.pas(1315): W1035 Return value of function 'TOmniThreadPool.Cancel' might be undefined

I’ve first observed this with the XE and it was fixed after the XE2 (can’t repeat this with XE8, but I didn’t check every compiler inbetween.)

New Home for OmniThreadLibrary

OmniThreadLibrary has been moved from Google Code to GitHub.

I still have to move existing issues as they were not moved automatically.

New Home for My Units

My collection of open-sourced units has been moved from Google Code to GitHub.

XE8 is out …

… and OmniThreadLibrary works fine with it. (That’s it. Just wanted to let you know.)

Runtime SQL Query Builder

I don’t want to use long SQL strings in the code.

Really.

Firstly, it is a pain to write long multiline strings in Object Pascal. (Embarcadero, are you listening? Can we please get multiline strings in Delphi? Please?)

Secondly, I’m very OCD when it comes to compiler watching my every step. I absolutely hate runtime errors and I want every problem to be detected during the compilation. That includes typos in database field names and changes attributed to the entropy (i.e. changes in the database structure).

On a tangent, that’s why we are creating and managing tables and databases in the code. We have all fields defined as constants, all SQL statements created in runtime and everything stored under version control.Upgrading a database to a version required by the program is therefore a very simple operation. (Downgrading is still a problem. Downgrading is hard.)

Thirdly, concatenating query strings together when you are dynamically building a query string based on the conditions in the program is a mess.

That’s why long years ago I created a simple runtime query builder. It was dormant (in the “good enough for me” state) for a long time but recently I required more functionality from it and I extended it further. It is now in a state which may be useful for somebody else but me so I’m releasing it as an open source under a BSD license.

For impatient: Get GpSQLBuilder on GitHub.