Syntax Trees using Composite/Intepreter Pattern Part 3

Part 1

Part 2

Where we left off last time we had 2 leaf nodes that peformed operations against two operands (LessThanWhere & EqualToWhere) now I want to add a composite class to perform some logical operations.

Creating the Composite Class

Like before we will drive out the requirements TDD style:

[Test]
public void Should_add_multiple_and_where_clause_with_string_value_on_BuildQuery()
{
	string expected = "WHERE ((col1 = 'test') AND (col2 = 'test2'))";
	SelectQueryBuilder queryBuilder = new SelectQueryBuilder();
	EqualToWhere where1 = new EqualToWhere("col1", "test");
	EqualToWhere where2 = new EqualToWhere("col2", "test2");
	queryBuilder.AddWhere(new AndWhere(where1, where2));

	string output = queryBuilder.WhereSection();
	Assert.AreEqual(expected, output);
}

Now we need to add the new class to get compilation:

 public class AndWhere : AbstractWhere
{
	public AndWhere(AbstractWhere left, AbstractWhere right)
	{
	}

	public override string Output()
	{
		return "";
	}
}

It should now compile so we run the tests and get a fail, lets go and make them pass:

public class AndWhere : AbstractWhere
{
	AbstractWhere left;
	AbstractWhere right;

	public AndWhere(AbstractWhere left, AbstractWhere right)
	{
		this.left = left;
		this.right = right;
	}

	public override string Output()
	{
		StringBuilder output = new StringBuilder();
		output.Append("(");
		output.Append(left.Output());
		output.Append(" AND ");
		output.Append(right.Output());
		output.Append(")");

		return output.ToString();
	}
}

This class expects 2 operands to be passed in, the only constraint being that they derive from the AbstractWhere class, when the SQL is built in the Output method the ANDWhere class simply delegates off to the left and right operands respectively, We should now have a green tick for the test above.

Full control over SQL produced

I won’t show the implmentation for an ORWhere class as I’m sure you can guess how it can be implemented instead I will show how we can use the classes we have now to produce SQL under our control i.e. nested SQL:

[Test]
public void Should_add_multiple_and_nested_or_where_causes_with_string_values_on_BuildQuery()
{
	string expected = "WHERE ((col1 = 'test') OR ((col2 = 'test2') AND (col3 = 'test3')))";

	SelectQueryBuilder queryBuilder = new SelectQueryBuilder();

	EqualToWhere where1 = new EqualToWhere("col1","test");
	EqualToWhere where2 = new EqualToWhere("col2", "test2");
	EqualToWhere where3 = new EqualToWhere("col3", "test3");
	AbstractWhere andWhere = new AndWhere(where2, where3);
	queryBuilder.AddWhere(new OrWhere(where1, andWhere));

	string output = queryBuilder.WhereSection();
	Assert.AreEqual(expected, output);
}

We can now build up a nice tree of the syntax we would like to use as our SQL without having to worry about levels, What we have from the above can be viewed as:

SQL Syntax Tree Example

Handling other kinds of Operands

In part 1 I made reference to a question that was posted on the SelectQueryBuilder codeproject page in which the poster asked if it would be possible to use functions as part of the where clause:

WHERE DATEDIFF(dd,col1,'20080101') >= 0

One problem is that this function is a SQL server function making the above SQL constrained to SQL server, so before implementing the above into our code we need to keep in mind that we are limiting the SQL generation to SQL server, so if we accept this constraint we could try the following:

[Test]
public void Should_add_single_explicit_function_with_equals_on_BuildQuery()
{
	string expected = "WHERE (DATEDIFF(dd,col1,'20070101') = 0)";
	SelectQueryBuilder queryBuilder = new SelectQueryBuilder();

	FunctionWhere functionWhere = new FunctionWhere("DATEDIFF", "dd", "col1", "'20070101'");
	EqualToWhere equalFunctionWhere = new EqualToWhere(functionWhere, 0);
	queryBuilder.AddWhere(equalFunctionWhere);

	string output = queryBuilder.WhereSection();
	Assert.AreEqual(expected, output);
}

Ok, so lets get that test compiling, firstly we need to add the new FunctionWhere class:

public class FunctionWhere : AbstractWhere
{
	public FunctionWhere(string name, params string[] args)
	{
	}

	public override string Output()
	{
		return "";
	}
}

Next we also need to allow EqualToWhere to accept an overloaded constructor that can accept an AbstractWhere:

public EqualToWhere(AbstractWhere where, object rightSide)
	: this(where.Output(), rightSide)
{
}

This neat we can simply chain the constructor by calling the Output method on the where object passed in.Now to get the test to pass we need to make create the implementation code in FunctionWhere:

public class FunctionWhere : AbstractWhere
{
	private string name;
	private string[] args;

	public FunctionWhere(string name, params string[] args)
	{
		this.name = name;
		this.args = args;
	}

	public override string Output()
	{
		StringBuilder output = new StringBuilder();
		output.Append(name);
		output.Append("(");

		foreach (string arg in args)
		{
			output.Append(arg);
			output.Append(',');
		}

		output.Remove(output.Length - 1, 1); // strip last comma
		output.Append(")");

		return output.ToString();
	}
}

I have gone for the approach of simply passing the function name then passing the arguments in, this could easily be changed to be more specific i.e. using a DateDiffWhere however the FunctionWhere performs what we need to for now.

Closing Notes

I hope this series of articles have provided some help on understanding how we can use patterns to refactor existing code one thign to note is that throughout all these changes I have not once had to re-open the SelectQueryBuilder class all the modifications where made externally which is inline with the OCP coined by Bertrand Meyer.

If your after a great book on refactoring to patterns I recommend Refactoring to Patterns by Joshua Kerievsky, I have recently read it and it is what inspired me to create this series.

Advertisements

2 thoughts on “Syntax Trees using Composite/Intepreter Pattern Part 3

  1. Pingback: Syntax Trees using Composite/Intepreter Pattern Part 2 | Journal of a software dev
  2. Pingback: Syntax Trees using Composite/Intepreter Pattern Part 1 | Journal of a software dev

Comments are closed.