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()
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
- etc. pp.
Things I thought about:
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.
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?