Phillip Trelford's Array

Last week Jon Harrop showed me some interesting data access code (Jon and I often share the same Cambridge to London train). The code used pattern matching to match records returned from a SQL command, for example.:

let values = [for index in 0..reader.FieldCount-1 -> reader.GetValue index]
match values with
| [Date date; Price o; Price h; Price l; Price c] -> 
    yield date, o, h, l, c
| _ -> invalidOp "Record mismatch"

The code fragment above matches open-high-low-close (OHLC) field values read from an IDataReader. The first line creates a list of field values; these values are then matched using a List pattern with Active Patterns to unbox the objects to strongly typed values, i.e:

let (|Date|) (value:obj) : DateTime = unbox value
let (|Price|) (value:obj) : float = unbox value

Instead of constructing a list each time a pre-allocated array can be employed using the IDataRecord.GetValues method and an Array pattern:

seq {
    let values = Array.zeroCreate reader.FieldCount
    while reader.Read() do       
        reader.GetValues values |> ignore
        match values with
        | [|Date date; Price o; Price h; Price l; Price c|] -> 
            yield date, o, h, l, c
        | _ -> invalidOp "Record mismatch"
}

The OHLC values are returned as a tuple from inside a sequence expression which is the equivalent of a C# iterator block.

Pattern Matching

I think this is another great example of the power of pattern matching, a feature common to functional programming languages. Somewhat strangely it’s a feature missing from older and more established object-orientated languages like C++, C# and Java. However there is nothing intrinsically stopping these OO languages from supporting pattern matching. Pattern matching has been successfully implemented in newer moustache/curly-brace based languages like Scala, Nemerle and Kotlin. Progress in Java seems to be severely hampered by committees, just look at how long it took to get to Java 7. C# seems to be making more progress but I think may in part be hampered by the compiler being implemented in C++, which Project Roslyn may fix in the future.

Ordinals

The pattern matching approach makes use of the order of the fields. Another approach employed in examples in both Jon’s F# for Scientists and Don’s Expert F# book, is to get the field values based on their ordinals, i.e.:

yield 
    reader.GetDateTime(0), 
    reader.GetDouble(1),
    reader.GetDouble(2),
    reader.GetDouble(3),
    reader.GetDouble(4) 

String Literals

Yet another approach, used as an example in Professional F# 2.0, is to use the field names:

yield 
    reader.["Date"]  :?> DateTime,
    reader.["Open"]  :?> float,
    reader.["High"]  :?> float,
    reader.["Low"]   :?> float,
    reader.["Close"] :?> float

Dynamic Lookup

And now for a one line ORM which simply overloads F#’s dynamic lookup operator:

let (?) (reader:IDataReader) (name:string) = unbox reader.[name]

This really helps reduce the boilerplate:

yield 
    reader?Date, 
    reader?Open, 
    reader?High, 
    reader?Low, 
    reader?Close

For more information on this approach see Tomas Petricek’s excellent post:

• Dynamic in F#: Reading data from SQL database

Plotting

Now we’ve explored 5 different ways of doing exactly the same thing it’s probably time to do something useful like plotting.

Again Tomas has a tutorial for that:

• Tutorial: Visualizing Stock Prices Using F# Charts

Plotting a chart from inside F# interactive using Carl Nolan’s new FSharpChart library:

#load "FSharpChart.fsx"
open MSDN.FSharp.Charting

[ for date,o,h,l,c in getHistoricalPrices "MSFT" -> date, (h,l,o,c) ]
|> List.filter (fun (date, _) -> date >= DateTime(2011,08,01))
|> FSharpChart.Candlestick

Gives us a Candlestick chart for MSFT stock since August:

Final Thought

With Type Providers in F# 3 a lot of complexity will disappear, as we get type safe data access with intellisense (rather than strings or ordinals) without the need for code generation!

• Walkthrough: Accessing a SQL Database by Using Type Providers (F#)