Recursive Lazy SQL Sequences In F#

Over the past few months I having been doing a lot of SQL development at Global Back Office. For my personal open source portfolio analysis tool I’m working on, I encountered a scenario where I had to get only the latest item from a data set, and potentially later the top three or four items. So the whole theme of SQL (Structured Query Language) and functional programming was on top of my mind, and made me think, wouldn’t it be cool if I can create a F# sequence around a SQL data set, so that only the current record being evaluated is fetched from the database? For example if I executed the following statement:

StockRepository.getStockPriceByStock “TWTR:NYQ”|> Seq.take 5

What I want is for getStockPriceByStock to generate a sequence for Twitter’s stock prices, and only evaluate each select statement for the current iteration of the loop. This means you can fetch any number of stock prices without specifying the actual number of items in the SQL select statement. Obviously we are exchanging efficiency for flexibility with this approach. It’s a lot more efficient to just execute the SQL statement once for the top few items. But, with this approach you can use all F# sequence comprehension functions to query and filter your data any way you like, all based on this one single little SQL statement. Hence the flexibility.

So let’s examine getStockPriceByStock’s implementation:

type StockPrice = {
      id:         int64 option;
      stockId:    int64 option;
      date:       DateTime; 
      openp:      decimal;
      high:       decimal; 
      low:        decimal;
      close:      decimal;
      adjClose:   decimal;
      volume:     int64 }
let getStockPriceByStock (stockId: int64) = 
 let rec getStockPriceByStock’ (stockId: int64) 
                               (stockPrice: StockPrice option) =
   seq {
     if stockPrice.IsSome
     then yield stockPrice.Value
     use db = new Db()
     use cmd = db?Sql <- "SELECT  id,
                                  stockId,
                                  date,
                                  openp,
                                  high,
                                  low,
                                  close,
                                  volume,
                                  adjClose
                            FROM  StockPrice
                            WHERE stockId = :stockId
                            AND   (:id IS NULL
                            OR    id < :id)
                            ORDER BY id DESC
                            LIMIT 1"
      db.Open()
      cmd?stockId <- stockId
      cmd?id <- match stockPrice with
                | Some { id = Some id’ } -> Nullable<int64>(id’)
                | _ -> Nullable<int64>()
 
      use rdr = cmd.ExecuteReader()
      if rdr.Read()
      then 
        let stockPrice = { id = Some rdr?id;
                           stockId = Some rdr?stockId;
                           date = rdr?date;
                           openp = rdr?openp;
                           high = rdr?high;
                           low = rdr?low;
                           close = rdr?close;
                           adjClose = rdr?adjClose;
                           volume = rdr?volume }
        yield! getStockPriceByStock’ stockId (Some stockPrice)
    }
  getStockPriceByStock’ stockId None

Okay, so there are several interesting things going on here. Let’s go into the detail of each one.

rdr?date
cmd?stockId

Here we are making use of F#’s dynamic operator to create a wrapper around ADO.NET, that provides a convenient way to work with it. As Thomas Petrick describes:

Dynamic operator (there are actually two of them) are a simple way of supporting dynamic invoke in F#. We can use it to write code that looks almost like an ordinary method call or property access, but is resolved dynamically at runtime (using the name of the method or property).

I adapted Thomas’s code to work with SQLite (instead of SQL Server), and did some customizations specific to my project.

SELECT  id,
        stockId ...
FROM    StockPrice
WHERE   stockId = :stockId
AND     (:id IS NULL
OR      id < :id)
ORDER BY id DESC
LIMIT 1

Let’s take a look at the SQL statement we’re using. Here we have to do the following:

1. Check if the previous :id IS NULL, to cater for the first iteration of the loop, when it gets evaluated for the first time. For the rest of the records make sure that the current :id is less than the previous iteration. In other words, this makes sure we decrease the :id with each iteration.
2. Reverse the order of the records from the table, so that we start with the highest/latest :id first.
3. Only return one record using SQLite’s LIMIT operation (TOP in T-SQL/SQL Server).

Nullable<int64>

First, we are using a nullable type so that we can pass a null value to SQL for the :id parameter (the 1st evaluation will pass an :id of null). Secondly, unfortunately F# doesn’t have the same syntactic sugar as C#’s long? for nullable types. Therefore we are forced to declare nullable types in F# directly using System.Nullable<int64>.

cmd?id <- match stockPrice with
          | Some { id = Some id’ } -> Nullable<int64>(id’)
          | _ -> Nullable<int64>()

A F# pattern match, that sets the SqliteCommand’s id SqliteParameter to null, if the current stockPrice record is null on the 1st evaluation, or for the rest, extracts the id’s value from the Option type using a nested pattern match, and converts it into a nullable type (to ensure a consistent return type for the match expression).

yield! getStockPriceByStock’ stockId (Some stockPrice)

Finally we get to the last piece of the puzzle. Here we use F#’s yield bang (yield!) to unwrap a recursive call to getStockPriceByStock’. Remember the return type of getStockPriceByStock’ is a sequence of StockPrices. Therefore if we used the usual yield we’ll get a nested sequence structure like:

seq [ seq [StockPrice]; seq [StockPrice]; seq [StockPrice]; seq [StockPrice] ]

Similar to Linq’s IEnumerable.SelectMany, yield! bang unwraps the nested sequence into a flat sequence:

seq [ StockPrice; StockPrice; StockPrice; StockPrice; ]

And wallah! We are now able to query and filter our table with F#’s various sequence comprehension functions, without having to write any specific SQL queries. Depending on your use case, it will most likely not be the most efficient way to interrogate your data, but it’s an interesting experiment, and suits some queries perfectly, without having to write additional SQL queries.