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.

The query builder is implemented as an interface (IGpSQLBuilder). You call the global CreateGpSQLBuilder function to create a new instance. Then you call various SQL-like functions on that interface to set up the query. At the end you call the AsString method to convert the query into a string.

A simple, self-contained example:

const
  DBField_Deleted = 'dbi_deleted';
  DBField_ID      = 'dbi_id';
  DBT_SBF         = 'sub_files';
var
  query: IGpSQLBuilder;
begin
  query := CreateGpSQLBuilder
    .Select(DBField_ID).From(DBT_SBF)
    .Where([DBField_ID, '>= 0'])
      .AndE([DBField_Deleted, '= 0']);
  ShowMessage(query.AsString);
end;

The query builder is smart enough to put spaces and parenthesis where they are required and to convert numbers into strings if you provide the data as an open array (see Where and AndE above).

BTW, AndE means “and expression” and is used because I don’t like the &And approach too much. &And is, however, also implemented as an alias if you prefer to write the code that way.

A more complicated example below (taken straight from my code) shows that you don’t have to set up the complete query in one call and that you can return to any section at the later time. (For example, a Where is partially set first, then OrderBy and then Where is updated if some condition is met.)

query := CreateGpSQLBuilder.Select;
if FMaxSelect > 0 then
  query.First(FMaxSelect).Skip(0);
  query.From(DBT_PLY);
  if not (qoPlaylistOnly in Options) then begin
    query
      .LeftJoin(DBT_SBF)
        .&On([DBSBF_PLY_ID, '=', DBPLY_DBField_ID])
      .LeftJoin(DBT_STL)
        .&On([DBSBF_STL_ID, '=', DBSTL_DBField_ID]);
  end;
  query
    .Where([DBPLY_DBField_VerID, '>= 0'])
    .OrderBy(DBPLY_DATETIME);
  if FCutOldest then
    query.Desc;
  query.OrderBy
    .Column(DBPLY_CHN_ID)
    .Column(DBT_PLY, CDBField_ID);
  if not (qoSearchInDeleted in Options) then
    query.Where
      .&And([DBPLY_DBField_Deleted, '= 0']);

Another real-life example:

query := CreateGpSQLBuilder
  .Select(DBPLY_AUTOMATION_ID)
  .From(DBT_PLY)
  .Where([DBPLY_AUTOMATION_ID, '<> '''''])
  .GroupBy(DBPLY_AUTOMATION_ID)
  .Having(['Count(DISTINCT', CDBField_ID, ') > 1'])
  .OrderBy(DBPLY_AUTOMATION_ID);

  if FLastAutomation <> '' then
    query.Where.AndE([DBPLY_AUTOMATION_ID, '>', ':', CParamAutomationID]);

You can also clear a whole section and rebuild it from scratch if you need two very similar queries. (Create one, convert it to a string, change the part that needs to be changed, convert that one to a string too.)

strQueryCount := query.AsString;

query.Select.Clear;
query
  .Select
    .Column(CAliasPly1, CDBField_ID)
      .AsAlias(CAliasPlyID)
    .Column(CAliasPly1, DBPLY_AUTOMATION_ID)
      .AsAlias(CAliasPlyAutoID)
  .OrderBy
    .Column(CAliasPly1, CDBField_ID).Desc;

And the last example:

query := CreateGpSQLBuilder
  .Select
    .Column(['DISTINCT', DBPLY_DBField_ID])
    .&Case
       .When([DBSBF_DBField_CreatedTime, '< ''2010-01-01'''])
         .&Then('*')
       .&Else('')
    .&End
  .From(DBT_PLY)
  .LeftJoin(DBT_SBF)
    .&On([DBSBF_PLY_ID, '=', DBPLY_DBField_ID])
  .OrderBy(DBPLY_DBField_ID);

For now there’s no documentation. It will be probably added as a set of unit tests.

I’ll be happy of any contribution or feedback.