The Erasing SQL type provider (codename John)

:: fsharp, type providers, sqlprovider

Introducing my latest type provider, designed to query general SQL databases. This is very much an alpha cut, it is by no means feature complete, is still a mess in various areas and has only been tested on a very limited selection of databases. I will provide a brief overview of its main features and lack thereof, followed by a fairly detailed explanation of the most prominent features. There's other features not discussed here, hopefully there will be some followups to this in the coming weeks, along with some proper documentation in the github repository.

What the provider does

  • Provide an IQueryable interface over a SQL database’s tables and views
  • Supports the use of F# query expressions
  • Erases types down to a common runtime type – there is no code generation here, which means you can connect it up to massive schemas
  • The provider is designed around System.Data. The current single concrete implementation is for Microsoft SQL Server, however you can add additional providers by implementing the ISqlProvider interface and writing a bunch of code that will, on demand, extract the relevant schema data and generate a SQL query from a distilled version of the LINQ query in question – most databases will be able to use the Microsoft SQL Server implementation as a template for a solid starting point (most of the hard work has already been done). You can then select your database provider target with a static parameter.
  • Schema exploration – instead of needing to know the database structure and constraints in order to write effective queries between objects, constraint information is displayed as relationships on entities. You can simply enumerate these, query off of them directly or use the select-many syntax. Join data is inferred for you. This makes your life way easier for both exploring unknown databases and removing the tedium of writing explicit joins everywhere.
  • Individuals - The provider will additionally query the top x rows of each table and bring the data directly into the F# programming language. You are able to view said individuals in intellisense using your choice of column name. Once you have an individual you can use it just like any other entity including enumerating its relationships and querying from it.
  • Basic stored procedure support. This has only just been added and is fairly weak presently, and pretty much not tested at all.
  • Produces Highly Non-Optimised SQL ™

What the provider does not do (yet)

  • Make the tea
  • Support updates to data
  • Have lazy features (tip – don’t select 100mb of data)
  • Support any custom types or table value parameters
  • Support OUT parameters on sprocs
  • Have explicit support for nullable types
  • Support grouping, System.Math functions, or outer joins (fret not though you can indeed outer join if you use the select-many syntax!)
  • Have anything to do with Squirrels
  • A bunch of other stuff

But Why!

People from F# land will know that there already exists an official SQL type provider. This begs the question, why am I crazy enough to tackle such a massive job, surely not just for the fun of it! The answer to this is several reasons, with the primary one being that the existing type provider uses SQLMetal.exe in the background to generate code. Unfortunately this means that you end up with the entire database schema in a huge amount of code files, which in extreme cases will not work at all. F# type providers give us the ability to provide erased types. You can think of these types as appearing to be real static types at compile-time, but at runtime they are erased down to some common type. This nets us all the benefits of static typing, with none of the overhead of the generated code - plus if the database schema changes, your program will no longer compile, in contrast to a generated code solution which would be unaware of schema changes unless the code is re-generated at every compilation, which is generally not a viable approach - and stale generated code can have disastrous consequences at runtime.

In addition to this, the existing provider is not open source and it can only work with MS SQL Server (to my knowledge). Part of my aim with this work was to introduce an extensible and open source SQL type provider for the community which in the future can target SQL Lite, Oracle, Postgre and so on. Finally, I wanted to provide a much simpler way of navigating around an unknown database schema, which is surfaced in the ability to traverse the data based on the constraint metadata. I have spent too much time in the past having to dig around in SQL to find which tables join to what others and how, and having to explicitly provide all that information every time I write a query - only to forget it 2 months later and repeat the process. Simplifying the data access in this manner makes programming easier, faster and more maintainable.

First Steps

Once you have built the the shiny new dll from my github, you should create a new F# project and a fsx script file. You can then reference it like so

1
#r @"F:\dropbox\SqlProvider\bin\Debug\FSharp.Data.SqlProvider.dll"

You might get a security dialog warning of impending doom but you can safely ignore that. The next stage is to create a type alias using a connection string.

1
type sql = SqlDataProvider<"Data Source=localhost;Initial Catalog=Northwind;Integrated Security=true;">

This is moment of truth #1. If it blows up here with red squigglies, it’s either a connection issue or something that I have messed up due to not being able to test it with many databases. For reference I have tested it with Northwind, both the AdventureWorks databases, and some simple databases that exist in my current place of employment. All servers were running 2008R2. I will use the Northwind schema for all examples henceforth.

The base schema would have be generated from the connection string in the static parameter; to actually use the provider you will need to create a data context like so:

1
let ctx = sql.GetDataContext()

You can pass in a runtime connection string to this method if it is different to the one in the static parameter (the static one must be constant and available at compile time)

You will now be able to see all tables / views directly from the data context object, and also a property called Stored Procedures which is amazingly enough where the stored procs live.

Note that I don’t currently support any non-native sql types. For tables /views you just won’t see those columns, and stored procs that have said types as parameters simply won’t be generated. OUT parameters and table values types are also not supported – sprocs with those will also not appear.

Querying

At its simplest you can enumerate an entire table for all records like so:

1
let customers = ctx.``[dbo].[Customers]`` |> Seq.toArray

if you were to now index that array you should see strongly typed properties corresponding to the columns (and some other stuff)

clip_image002

To actually query you use the query syntax as such

1
2
query { for c in ctx..``[dbo].[Customers]`` do
        select e } |> Seq.toArray

I currently support the following LINQ functions; select, select many(for), join, distinct, where, take (top), exactlyOne (single), sortBy, sortByDescending, thenBy, thenByDescending

Explicit joins look like this

1
2
3
4
query{ for cus in ctx.``[dbo].[Customers]`` do
       join ord in ctx.``[dbo].[Orders]`` on (cus.CustomerID = ord.CustomerID)
       join ship in ctx.``[dbo].[Shippers]`` on (ord.ShipVia = ship.ShipperID)
       select (cus.CompanyName,ord.ShipName) } |> Seq.toArray

Here the projection creates a tuple of company name and ship name; the provider supports arbitrarily complex projections, you can in theory do anything in the projection. The projection is also analysed to establish which fields to select for performance reasons – in this case the query will only select the two field names projected, if you select entire entities then it will select all their columns.

The previous example showed an inner join. Outer joins are not currently supported in this syntax because that requires me to implement group join which is somewhat epic and I have been avoiding it wherever possible. You can however perform outer joins by using the select-many syntax. This syntax allows you to navigate around the database constraints without prior knowledge of its schema (woop!). You will see constraints (relationships) as properties of the table you are querying. You can simply “for” (select many) over them and the join information will be inferred for you – Intellisense will helpfully tell you the details about the relationship so you can understand which is the primary and foreign side.

1
2
3
4
5
query{ for cus in ctx.``[dbo].[Customers]`` do
       for ord in cus.FK_Orders_Customers do
       for ship in ord.FK_Orders_Shippers do
       for emp in ord.FK_Orders_Employees do
select (cus.CompanyName,ord.ShipName,emp) |> Seq.toArray

You can make these outer joins (which will perform a left or right join depending on which way around the relationship is) by using the (!!) operator before the table name.

1
2
3
4
5
query{ for cus in ctx.``[dbo].[Customers]`` do
       for ord in (!!) cus.FK_Orders_Customers do
       for ship in (!!) ord.FK_Orders_Shippers do
       for emp in ord.FK_Orders_Employees do
       select (cus.CompanyName,ord.ShipName,emp.FirstName) |> Seq.toArray

Criteria applied with where clauses can be used anywhere in the query subsequent to the relevant table identifiers being bound. Multiple where clauses in a query are AND’d together. You should be able to use all logical operators and basic direct predicates that you would expect to work – you won’t be able to call other functions inside a where clause though. The algorithm should be able to deal with any level of criteria complexity with multiple nested / bracketed AND / ORs. You can also use the following special operators : =% (like) <>% (not like) |=| (In) |<>| (Not In). With the In operators you must provide an inline array of options, eg [|”John”;”Juan”|] fsharp query{ for emp in ctx.``[dbo].[Employees]`` do where (emp.Address =% "%g%") join order in ctx.``[dbo].[Orders]`` on (emp.EmployeeID = order.EmployeeID) where (emp.FirstName |=| [|"Steven";"Janet"|]) distinct select (emp.Address, emp.FirstName + " " + emp.LastName, emp.BirthDate ) } |> Seq.toArray

Individuals

If a table has a non-composite primary key, you are able to project its SQL entities directly into the IDE via its “Individuals” property. This will contain the first 1000 rows of the table (this is configurable via a static parameter). By default it will list them using whatever the primary key text is as such:

1
let nancy = ctx.``[dbo].[Employees]``.Individuals.``1``.FirstName

Obviously, with an integer as a primary key this is not very helpful in identifying / exploring the records, so instead, each column of the table has an associated property which when navigated to will re-project the individuals into Intellisense with the value of the chosen column name, eg

Note this will now fail to compile if someone changes Nancy’s first name in the database! (cool huh!)

Given any instance of a SQL entity, whether it be from an individuals property or some query, as long as it contains its primary key you can then navigate directly off the relationships used in the select many syntax (how awesome is this!) for example, you can find out Nancy’s orders by simply doing this :

1
nancy.FK_Orders_Employees |> Seq.toArray

this means you can defer execution of additional joins from a prior query, because you might want to only process further information on selected objects, perhaps at a much later time. Because this is creating another query behind the scenes, you can even query directly off a relationship like this

1
2
query { for order in nancy.FK_Orders_Employees do
         where (order.CustomerID =% "V%") } |> Seq.toArray

which I think we can agree is fairly awesome :)

Stored Procs

Sprocs have only just been added and are pretty much untested. It doesn't support complicated sprocs with multiple result sets or differing result sets depending on branching logic. It basically executes the proc with default parameters using the SchemaOnly command type, and infers a new type based on the return metadata. You will always be forced to supply all parameters even if they are optional. MS SQL Server 2012 supports some better mechanics for getting metadata about stored procedures, but I have not implemented support for those yet.

Final Words

Here’s a final query that shows some of the other keywords for syntax reference

1
2
3
4
5
6
7
8
9
query{ for emp in ctx.``[dbo].[Employees]`` do
       for order in emp.FK_Orders_Employees do
       join ship in ctx.``[dbo].[Shippers]``  on (order.ShipVia = ship.ShipperID)
       for cust in order.FK_Orders_Customers do 
       join sub  in ctx.``[dbo].[Order Subtotals]`` on (order.OrderID = sub.OrderID) 
       where (emp.LastName =% "F%" && ship.CompanyName <>% "%owl%" && sub.Subtotal >= 1629.9M )
       sortByDescending cust.CompanyName
       take 10
       select (order.OrderID, order.OrderDate, cust.CompanyName, emp.LastName, ship.CompanyName, sub.Subtotal ) } |> Seq.toArray

A word on nullable types – I do not currently support them :) what this means is that for a nullable column that has a null value, you will simply be given default of its type, with a special case for string allowing it to return string.Empty. Set phasers to null. (No nulls should be produced in the usage of this software)

There's other stuff going on such as data binding, debugging event hooks that will show you the LINQ expressions triggering and the generated SQL that is to be executed, and various smaller things that I have probably long since forgotten about. The design is heavily based on my Dynamics CRM type provider so if you are lucky you might even find some references in the code to CRM :)

Hopefully it doesn't immediately blow up for you, although I wouldn't be surprised given the very small amount of databases it has been tested against. The provider is large, complex, and LINQ is really crazy!

Choose Your Own Adventure Type Provider

:: fsharp, type providers

That's right.. the type provider everyone has been waiting for, the choose your own adventure type provider!

This is completely pointless and silly, something I just wrote this afternoon. I had various discussions with Phil Trelford about this and finally decided to do it.

Unfortunately none of the real CYOA books are out of copyright and there appears to be very little in the way of other free ones that I can find. I did however find this onerather silly story on smashwords which is free, it's not quite normal CYOA fare and you can't lose, but it serves to illustrate the provider.

The provider reads a data file which contains the contents of each page on a line, the index of the page and any choices the page has on it along with the index that each choice points to. This then creates a type system with properties that you can navigate via intellisense.

1
2
3
4
5
6
#r @"F:\dropbox\CYOAProvider\CYOAProvider\bin\Debug\CYOAProvider.dll "
type adventure = CYOAProvider.CYOAProvider&lt; @"F:\dropbox\CYOAProvider\data.dat"&gt;

let a = adventure()

a.Intro  // start your adventure here!

You can find this ridiculous type provider on GitHub here.

To create the data file I wrote an amazing C# GUI, if you want to create a file yourself and would like the program to work with then mail me :)

Last-Fi

:: digital logic, electronics, fsharp, raspberry pi

Introduction

This article will introduce Last-Fi – an F# powered internet radio player that uses a Raspberry Pi and Last.Fm services. The motivation behind this work was to both build something fun and useful for the Raspberry Pi that interfaces with various pieces of hardware, but can also show off some features that F# is great at within a hardware context.

The result is Last-Fi – a highly asynchronous and stable music player. It is based around Last.Fm, but because it uses MPDto play music it only requires a few minor adjustments that will allow it to work with any music source, whilst retaining the ability to obtain artist information from Last.Fm. In terms of the hardware, you are able to control various aspects of Last.Fi with a controller from the 1980’s Nintendo Entertainment System. Various information is displayed on a 2x16 back-lit LCD display. In addition to this, the program also hosts a ServiceStack webservice, and uses FunScript, an F# to JavaScript compiler, in order to host a website that shows various information and allows direct control from any device on the same network.

I presented a talk on this work at SkillsMatter in London (here's the video), and you can find the slide deck here. The code is all open source and you can find it on GitHub here (disclaimer: this code is highly subject to change and is largely a toy project of mine)

Last-Fi illustrates an extremely powerful programming language doing some of what it does best, the entire stack from the HTTP Last.FM API, to the hardware programming and even the JavaScript is written in F# code. Not only that, it is almost all concurrent and completely immutable – I have yet to experience a single problem with any of the software. The entire project is less than 800 lines of F#. If you are new to F# be sure to check out www.fsharp.org and you can find instructions on how to get the Pi running with F# in a previous post I made here.

Microsoft Dynamics CRM Type Provider preview

:: fsharp, type providers, xrm

Are you tired of generating strongly typed classes with millions of lines of C# using crmsvcutil.exe that often crashes? Fed up with having to re-generate the classes every time something in the schema changes? Feeling restricted by the LINQ provider's limitations? Ever wonder why you should need to know what attribute joins to what in your relationships to perform your joins? F# to the rescue!

I am working on a F# type provider than aims to solve a lot of these pains, and more besides! This is just a sneak preview with what is to come, I have not released any of this yet. First, here is an example of a rather silly but fairly complex query using the new type provider and the F# query syntax :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
type XRM = Common.XRM.TypeProvider.XrmDataProvider<"http://something/Organization.svc">

let dc = XRM.GetDataContext()

type TestRecord = 
    { x : string; 
      y : int }

let test = { x = "John%"; y = 42 }

let q =
    query { for s in dc.new_squirrel do                            
            where (s.new_name <>% test.x || s.new_age = test.y)
            for f in s.``N:1 <- new_new_forest_new_squirrel`` do
            for o in f.``N:1 <- owner_new_forest`` do
            where (s.new_colour |=| [|"Pink";"Red"|])
            where (f.new_name = "Sherwood")
            where (o.name = "PEZI THE OWNER!")
            select (s, f.new_name, f, o) }

Monads to help with COM cleanup

:: fsharp

I currently do a lot of Office type automation work where I scan a bunch of email from exchange, download excel attachments, open and transform a bunch of data from them, reconcile these against databases using FLinq, produce graphs and charts with the results using FSharpChart, and so forth.

(p.s - as a side note, F# is awesomeat doing this kind of thing, I can knock all kinds of stuff out super fast. p.p.s - Active patterns with Excel = win)

As anyone who has done any office automation will know, cleaning up the COM objects is a right pain. Any object you bind to a value be it a workbook, sheet, cell, range or anything at all has to be explicitly freed with Marshal.FinalReleaseComObject. If you are not really careful with this you will end up with memory leaks, and EXCEL.EXE (or whatever) running forever in the background even once your program has shut down. The problem actually goes further than this, if you use the . . . notation to traverse the object models, all the intermediate objects are also bound and need freeing up. However, these ones as they were not explicitly bound you can get away with calling GC.Collect() andGC.WaitForPendingFinalizers().

You can see any number of articles on the interwebs about this issue and all solutions are ugly. F# to the rescue! Here is a simple computation expression I use to deal with this problem. It is not optimal but the interop is horribly slow anyway so it makes no difference.

PezHack–Abstracting flow control with monads

:: fsharp, game programming, roguelike

It's been forever since I last posted, I worked quite a bit on PezHack and then stopped for a while. I'm back to it now. In this post I will describe a technique I used to greatly reduce the amount of code and abstract away some repetitive imperative code.

The Problem

PezHack is a turn based game. The screen does not re-render itself all the time like a real-time game, but only when something changes and at the end of a the player's turn. The agent-based approach I used to separate the various sub systems of the game allow me to provide isolation around the graphics processing. The graphics agent is totally responsible for knowing what it needs to draw and will draw it on demand when it receives the relevant message. It does not really know anything else about the game state at all except the visual data of the various tiles that are visible, some player data that allow it to draw the various data about the player, and any menus / other UI that it needs to draw. Other systems can send it messages to inform it of some new visual state it should care about.

Most actions that Pezi can perform require some form of additional input and conditional flow logic. For example, when you press the 'e' key to Eat, first a menu is displayed that shows the stuff in your inventory that is edible, if possible. If not it will display a message and not end the player's turn. Assuming the menu is displayed, the player then presses the key that relates to the item they wish to eat from the menu. If this key is invalid a relevant message is displayed, else the item in question is eaten. What then happens is dependent on the item, it might provide sustenance, or if it's a mushroom it could perform one of various kinds of effects, and then probably end the player's turn

This is a common pattern that appears everywhere, and at each stage the graphics need to be re-drawn to show the various messages, menus and so on. At each stage it might continue or it might not depending the player's input, and the returns differs at each stage (end the player turn, or not, for example). In an imperative language we would almost certainly model this control flow with a bunch of nested if/then/else statements which quickly gets ugly. Because I am using the agent based approach for the graphics, I would also need to post a request to the graphics agent each and every time I needed the changes to show on the screen, so suddenly the actions become a list of imperative statements peppered with common code to keep the screen updated.

PezHack–A Functional Roguelike

:: fsharp, game programming, roguelike

In my quest to learn the functional paradigm, one thing I have struggled with is game development. Assuming I mostly stick to the functional style of having little to no mutable state, how do you go about writing games? Games are pretty much ALL mutable state. Obviously, in a multi-paradigm language like F# you can have mutable state - and if used judiciously this can be very effective (not to mention offer some significant performance improvements). The blend of imperative and functional styles can indeed work well, I wrote a few small games with XNA and F# using this approach. However, I am still more interested for educational value to stick with a more pure functional approach. Along they way I have wrote a few small games such as a functional console based Tetris in about 300 lines, and a two player console based PONG clone (using the libtcod library as I will introduce in a bit) that uses the Windows Kinect as the input (was cool!) In these programs I would tend to have the game state formed with an F# record that is copied/modified on each game cycle, passing the new state back into the loop. This works well and both of these games used no mutable state at all. This approach soon falls down with something more ambitious though, you can't realistically propagate the entire game state through one loop.

The Roguelike

I decided to create something a lot more complex whilst attempting to stick with the functional guns. If you don't know what a roguelike is you can read all about them here and a great set of development resources with links to many on-going roguelike efforts here. I used to play these games a lot. Stemmed from D&D back in the days where the only computers were the terminal sort in colleges in universities (before I was alive!), a roguelike traditionally uses just ASCII characters as its graphics, leaving the rest to the player's imagination. If you haven't tried this before I highly recommend you try one, Nethack is probably the biggest most complicated one out there, but you can also play the original Rouge (where the roguelike genre name comes from) online here. A few things that make a roguelike;

  • Random procedurally generated dungeons - every time you play there is a new dungeon
  • Randomness in item drops - until things have been identified, you don't know what they are. The "clear potion" might be a healing potion in one game and a potion that causes severe hallucinations in the next
  • Peramadeath and hard difficulty. These games are hard. Expect to die lots, and when you die you have to start again. Often the objective isn't to finish the game, but just see how long you can survive
  • Roguelikes are usually turn-based affairs - although there is some variation with this
  • Complexity - these games are amazingly complex and deep. The developer(s) don't have to worry about impressive graphics engines, so they can focus a lot more on interesting game mechanics. You will be amazed at some of the stuff you can do in a game like Nethack.

Here's a picture from Nethack to illustrate how a typical roguelike looks :

Nethack-kernigh-22oct2005-25_thumb2

Getting at non-public property setters in a nice way :)

:: fsharp

I’m sure every seasoned .NET developer has been in the situation at one stage or another, probably in testing code, where they need to access a non-public setter of a property (or maybe a private member), and it can’t be mocked.  We all know the (somewhat scary) reflection trick to get at the said setter method and invoke it.   I just hit this problem today trying to mock some response messages from a Microsoft Dynamics XRM 2011 OrganizationService.  Thankfully F# gives us cool things like Quotations and symbolic functions (custom operators) to make this process more succinct.  Instead of writing a method to reflect on a type, get the relevant method, then finally invoke the method and pass in both the original object instance and the new value being set, we can do the following:

1
2
3
4
5
6
let (<~) property value =
	match property with 
	| PropertyGet(Some(Value(x)),ri,_) -> 
	    ri.GetSetMethod(true).Invoke(fst x,[|value|]) |> ignore 
	| _ -> failwith "unsupported expression"