Thursday, April 30, 2015

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.

8 comments:

  1. IMO - the readability is lost, and with that - the maintainability.
    I.e. - the intents are good - but the results are questionable.

    ReplyDelete
  2. I'm writing TONS (really) of SQL code in my Delphi projects, but I would never do what Primoz does, despite all the good parts of his intention. The problem with SQL code inside Delphi code is not about incorrect syntax for JOINs or WHERE Conditions, the problem is about weak typing, automatic type conversion, parameter or field type mismatch, wrong field or table names.
    Proposed approach will not bring С# LINQ functionality into Delphi, as will not bring "strong typing" or "strong syntax checking". And I still have to use string literals using this approach, so my code is still prone to runtime errors during SQL code execution.

    I think if you want to have something close to LINQ, you should write some external code-verifying tool, that will scan through *.pas files, find all SQL queries (if they are not "too much dynamically built") and verify them against database schema (check field and table names at least).

    ReplyDelete
  3. Anonymous00:49

    Hi,
    Stop wasting time with projects no one needs, and please spend time improving the documentation and examples, and overall readability and usefulness of the website(s) of OmniThreadLibrary.

    ReplyDelete
    Replies
    1. Now that, my friend, is terribly rude. You did not care to present yourself and you complain about the stuff you are getting for free.

      I revoke your permission to use OmniThreadLibrary as of this moment.

      Delete
  4. Anonymous21:10

    I'm thinking that a class might be a better option. I like the ability to dynamically generate SQL since I'm doing more and more of that. Something more iterative where I could say:

    MyQry := TQueryHelper.Create(MSSQL_Syntax);
    MyQry.SELECT_FROM(TableName, Alias);
    MyQry.SELECT_FIELD(FieldName or sub-select, Alias);
    MyQry.SELECT_FIELD(Another FieldName or sub-select, Alias);
    MyQry.SELECT_CASE(TestField,[Value,Result,Value,Result,Value,Result],Else,Result);
    MyQry.JOIN_INNER(TableToJoin, Aliias, HowItJoins);
    MyQry.ORDER_BY(FieldName, Direction);
    MyQry.ORDER_BY(FieldName, Direction);
    ShowMessage(MyQry.ShowSQL);

    The idea of using the object would allow me to make the calls out of order but the SQL is built properly.

    I'm just shooting from the hip. I'm not sure using the . operator makes it easier to use. I like the idea above but it appears to be just another way of writing SQL without using FORMAT commands.

    Hmmm... I've got UniDAC. I should see what it can do...

    ReplyDelete
  5. I like the library. I have desktop applications that, of course, run SQL statement based on user input. Parameters are good, but I'm always left to my old devices if the user opts to search for some things, but not others. And if I show the results in a grid, I have to redo the ORDER BY and figure out which SQL line index that's on. This library will allow me to easily change the ORDER BY at run-time, and dynamically build SQL statement. I won't use it for *every* new project or *every* query, but for those ones that change a lot.

    I also like that it uses interfaces instead of objects, so that they free themselves and can be passed around without a parent who needs to free it. I like the object-oriented approach, complete with design patterns, as a way to build SQL queries at compile-time and run-time.

    ReplyDelete
  6. Sorry, but I really don't see any point in this.

    Are you copying LINQ in some way? Because that is entirely different.

    If you are writing SQL queries, you should be able to do it manually, this to me seems just another syntax to learn and the expertise gained will not be transferable.

    ReplyDelete
    Replies
    1. It's quite simple - this is for people who like such approach.

      If you don't like it, don't use it. Nobody is forcing you.

      Delete