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?
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