My company asked me to write a custom application that allowed users to perform ad hoc queries against a flat-file database. The users of this app were your typical Joe Businessman types. They are not programmers, and its unlikely they have ever seen an SQL statement in their lives.
As a result, I was tasked to develop a friendly userinterface that would allow users to select columns, tables, conditions, etc to build up a query. This is challenging because I can represent the SQL statement in the UI without first creating an abstract representation of it in memory.
The first iteration was written in C#. I created a boatload classes to represent the abstract syntax of an SQL statement, which resulted in a really cumbersome object model:
- a Join class, a Joins collection class
- a WhereClause class, a WhereClauses collection class
- a SelectedColumn class, SelectedColumns collection class
- an OrderBy class, OrderBy collection collections class
- an SqlStatement class that grouped all of the aforemtioned classes together
Converting an SqlStatement instance to a string was gloriously painful, ugly, and buggy. Moving the oppositive direction, from string to SqlStatement, was even worse, as it broke apart the pieces of an SQL string using lots of regex and string manipulation.
I hacked together the system, produced an application that worked, but I wasn't very happy with it. I especially wasn't happen when the business requirements of the app changed on me, which forced me to revisit my C# code.
Just as an experiment, I rewrote my SqlStatement in F# and represented it as a union:
type dir = Asc | Desc
type op = Eq | Gt | Gte | Lt | Lte
type join = Inner | Left | Right
type sqlStatement =
| SelectedColumns of string list
| Joins of (string * join) list
| Wheres of (string * op * string) list
| OrderBys of (string * dir) list
type query = SelectedColumns * Joins * Wheres * OrderBys
That small amount of code replaced a few hundred lines of C# and a dozen or so classes. More importantly, pattern matching simplified the process required to convert abstract representation into an SQL string.
The fun part was converting an SQL string back into a query object using fslex/fsyacc.
If I remember correctly, the original C# code totalled 600 lines and around a dozen classes, lots of messy regex, and requied two days to write and test. By comparison, the F# code consisted of one .fs file of around 40 lines, 100 lines or so to implement the lexer/parser, and consumed a few hours out of my day to test.
Seriously, writing this part of the app in F# felt like cheating, that's how trivially easy it was.