I have been asked to build what is essentially a query builder for a reporting application.
The variety of objects to query, potential modifiers, number of conditions, and so forth to be reported on have made me conclude that a query builder would be the best way to go about this task.
I am trying to decide the data model to back the storage of query parameters.
EDIT: Here are some facts about the technology and requirements
- I am working with C# and .NET Framework 4.5 (using ASP.NET MVC for presentation)
- I am using Oracle Database 10g
- I have Dapper and LinqToDB for Oracle available for data access
I would like to design a schema that can accommodate the following:
- Allows for queries to be stored in terms of their constituent parts, where certain parts (like constraints/predicates) may be hard-coded as actual SQL or SQL snippets in the case of particularly complex logic that should be encapsulated
- Allows for constraints to be formed in a "group", "constraint" format, where a query has a single root group, each group is equipped with AND/OR, constraints belong to groups, and groups can have subgroups
- Minimizes need to explicitly define joins as part of query (so that they can be inferred by the subjects of constraints or minimally identified where necessary)
- Can store the appropriate editor and source of editor values if appropriate for a given field