Designing an OO System and Unit Test Friendly Query System
I am working on an application that allows dentists to collect information about certain clinical activities. While the app is not very customizable (no custom workflows or forms), it offers some rudimentary customization options; customers can choose to augment the predefined form fields with their own. There are about fifteen different types of fields that admins can create (i.e. Text, Date, Number, Dropout, etc.). We are using Entity-Attribute-Value (EAV) on the persistence side to model this functionality.
One of the other key features of the app is the ability to create custom queries against these custom fields. This is achieved using a user interface where any number of rules can be created (Date <= (Now - 5 Days), Text Like '444', DropDown == 'ICU'). All the rules are AND'ed together to create a query.
The current implementation (which I "inherited") is neither object-oriented nor verifiable. Essentially, there is one God class that will compile all types of plural rules directly into a complex dynamic SQL statement (ie, Inner Joins, Outer Joins, and Subqueries). This approach is troubling for several reasons:
- Testing individual rules in isolation is nearly impossible.
- This last point also means the addition of additional rule types in the future will most definitely violate the Open Close Principle.
- Business logic and persistence concerns are combined.
- Slow unit tests since a real database is required (SQLLite cannot parse T-SQL and mocking the parser would be uh ... difficult)
I'm trying to come up with a replacement design that is flexible, maintainable, and testable, while keeping query performance fairly fast. This last point is key, as I am assuming that the OOAD implementation will move at least some of the data filtering logic from the database server to the .NET application server.
I am considering a combination of Command and Chain-of-Responsibility patterns:
The Query class contains a set of abstract rule classes (DateRule, TextRule, etc.). and contains a reference to the DataSet class that contains the unfiltered dataset. The DataSet is modeled in a persistence agnostic aspect (i.e. no references or hooks to database types)
The rule has a single Filter () method that takes in the DataSet, filters it appropriately, and then returns it to the caller. The Query class rather than simply iterates over each rule, allowing each rule to filter the DataSet as it sees fit. Execution will stop as soon as all rules are met or when the DataSet has been filtered to zero.
The only thing that bothers me with this approach is the performance implications for parsing a potentially large unfiltered dataset in .NET. Surely there are some reliable approaches to solving just such a problem that strike a good balance between maintainability and performance?
One final note: management will not allow NHibernate to be used. Linq to SQL might be possible, but I'm not sure how applicable that technology would be related to this task.
Thanks a lot and I look forward to all the feedback!
Update: still looking for a solution on this.
source to share
I think LINQ to SQL would be a perfect solution, related possibly to Dynamic LINQ from VS2008 samples. Using LINQ, especially with IEnumerable / IQueryable extension methods, you can build your queries using standard and custom logic depending on the inputs you enter. I use this technique pretty much to apply filters efficiently on many of my MVC activities. Since it actually creates an expression tree, it uses it to generate SQL at the point where the query needs to be implemented, I think this would be ideal for your scenario, since most of the heavy lifting is still done by the SQL server. In cases where LINQ proves suboptimal queries are being generated, you can always use table functions or stored procedures added to your LINQ data context.as methods for using optimized queries.
Updated . You can also try using the PredicateBuilder from C # 3.0 in a nutshell.
Example: Find all books where the title contains one of the many search terms and the publisher is O'Reilly.
var predicate = PredicateBuilder.True<Book>();
predicate = predicate.And( b => b.Publisher == "O'Reilly" );
var titlePredicate = PredicateBuilder.False<Book>();
foreach (var term in searchTerms)
{
titlePredicate = titlePredicate.Or( b => b.Title.Contains( term ) );
}
predicate = predicate.And( titlePredicate );
var books = dc.Book.Where( predicate );
source to share
The way I did it was to create objects that simulate each of the conditions from which you want the user to be able to build their query, and create an object tree using them.
From the object tree, you should be able to recursively create an SQL statement that satisfies the query.
The main ones will be AND and OR objects, as well as model comparison objects such as EQUALS, LESSTHAN, etc. You might want to use an interface for these objects to make it easier to combine them in different ways.
A trivial example:
public interface IQueryItem
{
public String GenerateSQL();
}
public class AndQueryItem : IQueryItem
{
private IQueryItem _FirstItem;
private IQueryItem _SecondItem;
// Properties and the like
public String GenerateSQL()
{
StringBuilder builder = new StringBuilder();
builder.Append(_FirstItem.GenerateSQL());
builder.Append(" AND ");
builder.Append(_SecondItem.GenerateSQL());
return builder.ToString();
}
}
The implementation of this method should allow you easy Unit Test rules.
On the negative side, this solution still leaves the database to do most of the work, which sounds like you really don't want to do it.
source to share