5

Situation

I'm designing a database abstraction layer for sql (mysql, sqlite) and mongoDb. The goal is to give the user/developer a library which is able to create queries for different databases. Without even knowing which type of database is queried.

Let's say we have a class which is able to create for example the where part (condition) of a query like (in case of sql):

name = 'Toni' AND age = 42 AND (status = 'ok' OR status = 'maybe-ok')

The way to create this string is calling different methods of an object in a certain sequence.

The class which the object is derived from could look like (pseudo):

class ConditionCreator:
    private-field condition


    public-function and:
        self.condition += ' AND '
        return self
    end

    public-function or:
        self.condition += ' OR '
        return self
    end

    public-function block:
        self.condition += '('
        return self
    end

    public-function blockend:
        self.condition += ')'
        return self
    end

    public-function comparison(key, value):
        self.condition += key + ' = ' + value
        return self
    end

end

And the calls to is should look like:

creator = new ConditinoCreator
creator.comparison('name', "'Toni'")
    .and()
    .comparison('age', 42)
    .and()
    .block()
        .comparison('status', "'ok'")
        .or()
        .comparison('status', "maybe-ok")
    .blockend()

Problem

How would one make sure that the calls are in the correct order?

  • No comparison after a comparison
  • No operator (or, and) after an operator(or, and)
  • No operator after a block (
  • No comparison after a blockend )
  • The same number of blockend's ) and block's (
  • etc. pp.

Things I thought about:

Regex

My first thought was to add a second class variable to which every method (and, or, block, blockend, comparison) adds a character so that the sample would produce this string caca(coc) after this string is generated one could do some regex magic on it to verify that the condition is valid.

Program it

As the title says one should program the rules from the Problem section manually. If this is the way to go how would this be done? Would one create a string like the one in the Regex solution and than test it against the rules?

Rely on the underlying database implementation

Do not test/validate the condition and rely on the database to throw errors (which is horrible to debug since every database gives a different level of output details and giving clear error messages is really hard).

Is there more?

Is there anything I'm missing completely?

  • 5
    My advice (not an answer): Don't use "AND" and "OR", instead use groups/blocks with "ALL" and "ANY", where these can be blocks of 1 (or even zero). This will eliminate the user having to know operator order and precedence rules. By forcing everything to be a group you can handle them more easily and treat all blocks (whether single blocks of blocks with sub-blocks) the same which allows for easier execution. – Eterm Aug 24 '15 at 14:13
  • 1
    ORMs are notably complex and for good reason. You could manage a simple subset, but sql queries can be very complicated indeed. I concur with Eterm to simplify the interface as much as possible. You could also assume "and" relationship and require that "or" be represented by passing several conditions as a parameter. – Neil Aug 24 '15 at 14:22
  • 1
    If you want to control the order in which they can be called you can't return this. You've gone from a simple fluent builder pattern to an iDSL. JOOQ does this so you're in good company. Allthough I haven't applied it to SQL I've toyed with iDSL builders myself as you can see here and here – candied_orange Aug 24 '15 at 14:36
  • 5
    Be aware that you're actually defining a language at this point. When I was working on this I found bottlecaps.de/rr/ui to be a very useful way to plan and diagram the language. – candied_orange Aug 24 '15 at 14:52
  • 2
    I will agree that with so many fluent SQL solutions out there that this smacks of reinventing the wheel. However, the main strength of any DSL doesn't come from it being maturely developed. It comes from being well tuned to the task at hand. @aichingm may have particular needs, and if so, rolling his own may be a good idea. – candied_orange Aug 25 '15 at 3:29
12

The fluent interface for a inner domain specific language is built on top of the Builder pattern. Ultimately, you are building up a representation that is to be used.

The solution is to then use a stateful builder (related: How should I handle incompatible configurations with the Builder pattern?). The object that you return needs to be restricted to the set of methods that are appropriate to call on it.

For the code example:

creator = new ConditinoCreator
creator.comparison('name', "'Toni'")
    .and()
    .comparison('age', 42)
    .and()
    .block()
        .comparison('status', "'ok'")
        .or()
        .comparison('status', "maybe-ok")
    .blockend()

the object returned by comparison only has the methods blockend, and and or. Likewise, the object returned by and and or only has the methods block and comparison.

No, this will not completely handle all situations - you can still have issues with calling blockend when there is no block (you could make the block a special type of object too, but then you get into the fun of nested blocks) or the possibility of having a dangling and or or, but it will handle the majority of the situations and enforce some formality to the structure. This is in effect using the type system of the language to do the work for you. The amount of ability you have to control this depends on the type system.

Technically, the way I've described the types above is that of a finite state table (and a regular language) and thus it can't do arbitrary nesting depth (which the corresponding inner language appears to support). Looking at this as a state table can help you understand how to use it though. Depending on the language, however, it may be possible to get a more powerful system. Some languages have a much more powerful type system that allow you to do crazy things or may be Turing complete, others are much more restrained. Now, if you want to try to take advantage of a full Turing machine to recognize the inner language in the type system, it sounds like a good challenge, though one that has the danger of being too clever.

To avoid some of these problems:

  • Have a .build() at the end that returns the appropriate finalized type. This can avoid the dangling .and() or .or().

  • Instead of a .block().comparison().or().comparison().blockend() have .block(Comparison) be the method signature which uses an already existing comparison object (possibly one created in there). Since each block is complete, arbitrary nesting isn't something that the type system needs to handle.

Taking both of these points, the sample code would look like:

Comparison comparitor = (new ComparisonBuilder())
    .comparison('name', "'Toni'")
    .and()
    .comparison('age', 42)
    .and()
    .block((new ComparisonCreator()).
        .comparison('status', "'ok'")
        .or()
        .comparison('status', "maybe-ok")
        .build()
    ).build()
  • Arbitrary nesting can't be checked statically but it can certainly be checked at run time. Also, you may find this easier to implement if you require the traditional final method to a fluent builder: build() – candied_orange Aug 24 '15 at 21:35
  • @CandiedOrange if one has a Turing complete type system (such as Scala or whatever it was that Eric did with example), one could likely get the arbitrary depth nesting problem solved. To me, this goes against the practicality principle I strive for in my code. It is avoidable by making the block its own object - and().block(Condition.Builder.this().or().that().build) – user40980 Aug 24 '15 at 21:39
  • @CandiedOrange does that look better? Arbitrary nesting can be solved if the nesting is a complete object itself rather than 'part of the language.' – user40980 Aug 24 '15 at 21:53
  • I do believe that solves the arbitrary nesting problem statically. Nicely done. – candied_orange Aug 25 '15 at 3:19
  • @MichaelT very very interesting! I really appreciate your efforts to answer my question! Especially your last example looks promising. – aichingm Aug 25 '15 at 14:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.