Testing a query builder

Question

I am building a query builder that I want to unit test.
I don't know how to go about it though.

It (currently) consists of two parts: the QueryBuilder itself which provides a fluent interface for constructing queries. And a SqlConstructor that takes care of constructing the actual SQL.

So basically, how do I test 'correctness'? Should I just test for the existence of keywords? (Like, select being the first keyword in a select-type query?) I think to test correctly, there are a whole lot of things that are important, like the order in which keywords appear etc. etc.

Answer 1

You test that for a given input, there is an expected output.

If I understand correctly, your QueryBuilder is collecting query parts, so make sure the datastructure holding these parts actually contains them when you add them through the QueryBuilder's method. If it has a addWhereClause method or something like that, check that the method actually does, what you wrote into the method body, e.g. write a test like

public function testWhereMethodAddsExpressionToPartsArray()
{
    $expression = 'foo = "bar"';
    $this->sut->where($expression);
    $parts = $this->sut->getParts('where');
    $this->assertContains($expression, $parts);
}

For the SqlConstructor do the same, test that the input it gets from the datastructure you filled with the QueryBuilder (you might wanna mock it for that) produces the expected output.

If you want to test for actual validity of the SQL, write a separate testcase for that. Keep in mind that it's not the purpose of a UnitTest to ensure the SQL is correct, but that your SQLGenerator generates the SQL in the way you told it to generate it.

The problem when validating the SQL is, SQL is complex. It has a formal grammar. How much of that grammar would your test cases cover? Writing a parser for SQL doesn't sound too feasible to me, let alone one that is complete.

Related:

• Test Cases for Zend_Db_Select

Answer 2

You can do like this. In sqlConstructor make one function for eg. verifySqlSyntax()

extract first keyword Apply switch case for keyword.

switch($keyword)
{
       case "select":
           $this->verifySelectStatement($sqlQuery);
           break;
       case "delete":
           $this->verifyDeleteStatement($sqlQuery);
           break;
}

In each verify*OPERATION*Statement you can check whether all other required keywords(from,where) are present or not and order by , group by orders and other syntax for that query. If you find IN keyword you can further call main function again for query inside IN(). or you cal also use INHandler.

DELETE * from users where userId in (select userId from user_status where status = 'inactive');

where you have to pass sqlQyery1 inside IN() and queryType(DELETE in eg) in which keyword IN resides $subQuery = "select userId from user_status where status = 'inactive'"; means call InHandler('DELETE' , $subQuery);

You can further process like this all possible cases in sqlQuery..