Creating a general SQL Query builder

what would be the best way to create a Query builder in delphi?

I have devexpress quantum grid, so I can dis开发者_StackOverflowplay data easily.

I assume you need a Query Builder to compose an SQL statement to a database and not a filter on the Quantum Grid (wich has an excellent filtering system on its own.)

We have been using a SQLGenerator object for several years now. We have inherited this object from another project. While it does what it is supposed to do, I always have to look up an example if I am to create a new statement, to me an indication things should be simplified (simplified at least to my level of understanding).

Several ideas have passed the revue.

from each SQL keyword being an object in itself

Node := where.AddNode;
Node.Condition := cndOR
Node.Add(a);
Node.Add(b)

to open arrays adding conditions

And([a, Or([b])]);

but today I would probably implement this as

SQLBuilder
  .where
    .condition(a)
  .and
    .condition(b)

The SQLBuilder object might look like this

  ISQLBuilder = interface
    function select: ISQLBuilder;
    function from: ISQLBuilder;
    function innerJoin: ISQLBuilder;
    function where: ISQLBuilder;

and it should be possible to write

  sqlStatement := SQLBuilder
    .select
      .column('o.name')
      .column('oi.price')
    .from
      .table('orders o')
      .innerjoin('orderitems oi').on('o.orderid').equals('oi.orderid')
    .where
      .condition('oi.price < 100')
      .and.condition('o.name').equals('LatestOrder')
    .Build;

I am still trying to figure out what this all buys me besides writing the statement in plain text.

At least as of a few years ago, best query building component for Delphi was "Simple Query", an inexpensive component from a Russian toolmaker. Great component, features are much deeper and better thought out than they might at first appear. I used it with good success to integrate with data in QuantumGrid. (Since Simple Query merely generates an SQL string there isn't really much to integrate that way. But I did also tailor the visual appearance so it looked consistent with the QG.) http://devtools.korzh.com/query-builder-delphi/

Note that the interface for Simple Query (and their "Easy Query" .NET component) is geared towards making it simple for users to generate valid searches without having any concept of tables, joins, or SQL. Takes a bit of foresight by developer to configure interface perfectly that way, but it's a nice feature. Also can be used as "full strength" solution for database experts.

Also, I assume you're aware that QG has its own integrated visual query system. I think my main problem with that was that it was mostly geared towards filtering the existing dataset, but in any case Simple Query was far more flexible.

Another thing, under the covers Simple Query has a number of objects that are used to generate the final SQL string, analogous to the code you posted and are asking about. Main purpose for this was just as intermediate stage between visual description and final output as SQL.

I have used Active Query Builder (http://www.activequerybuilder.com/product_vcl.html) as the UI for users to build queries. It includes an option to match the look and feel of the Dev Express grid, if you buy the source code.

It really depends on how complicated and intuitive you want the query builder to be. I wrote a cheap and cheerful SQL Query Form into an app in Delphi 5, using a TMemo component only allowing the Select commands to be run. There are various sql queries you can run to gather information from SQL on schema so you can get the tables and fields in a database and populate a treeview component or something like that.

Why write your own when there already is a solution ready to be compiled into your application? The folks at fast-reports have a nice query builder that includes complete source. There also is an open source version, although you would have to update it yourself to support Unicode.

The most important thing is to understand what which value the constructor will have for the end user. Usually it is data. You either build a wall for the a user, which he himself will surmountsurpass, as he can, or offer a convenient staircase and elevator. An advanced user may need to work with subqueries. A less advanced user one will handle a filter builder built into the QuantiumGrid. It may be reasonable to use ready-made solutions for Delphi: Active Query Builder, FastQueryBuilder, Advanced Query Builder.

If you are creating your own design, then you need to decide on three components: UI, engine, and metadata. Make a list of requirements for each component: which servers to work with, how to work with parameters, how to prevent SQL injection, etc.

UI To create a query builder for Delphi, the UI functionality needs is to be understood. If the user interface is simple, it will be limited in terms of functionality. Such an approach is used in EasyQuery (only if you have a QuantiumGrid;, then all the EQ functionality is available to you out of from the box right in QG). Moreover, if the functionality is expanded, the complexity of the UI geometrically increases geometrically.

Engine To write or to select the engine that will build a random table doesn’t seem difficult. It was smooth on paper. In practice, many complicated tasks need are to be solved, such as organizing “How to make objects correctly in a the FROM expression, keeping in mind minding all the connections that the a user has createdinvented, and most importantly, corresponding that this corresponds to what the user expects to receive.?” The Uuser should not have to think about how the request will be made, but should strictly follow its instructions. In addition, one should not just make a request, but make it right. The task of a properly written engine is to control and gently correct the user

Metadata It's great when a constructor helps not only to build a query, but to suggests where the necessary data is for the user to quickly build the a query.