Being able to play Mine Sweeper inside your IDE via intellisense, that is what you have always wanted right? Well, I’m always willing to lend a hand! With this fantastic new type provider you can pretend you are working when really you are avoiding mines. To get started simply clone and build the provider from here. Reference your new shiny type provider library from a script file and create a type alias and then an instance of it like so:
Introducing my latest top-o-the-line type provider that everyone wants and needs. The Squirrelify provider! This very useful type provider will create an INFINITE type system and show you random pictures of ASCII art in intellisense.
It turns out that intellisense was not really designed for this and it struggles with various formatting and layout, but the provider tries the best it can. It also doesn't have many images as I couldn't find a webservice for them and had to do it manually.
"Wow Ross!" I hear you cry. "How can we get started with this??"
It's easy! simply go and build the source from here, then reference the library it produces in a script file as shown in the picture below. Once you have done that, you will need to alias a type and call create() on it. The resulting type will have an infinite series of properties named``Squirrelify!``which will display said ASCII art.
Note that this is the LITE version of the type provider. They PRO pay-for version includes a static parameter "keyword" which is used to search google images, convert said images to ASCII and display them.
edit - this is not just squirrels. You can expect other delights such as unicorns, snowmen, and various other things you will not be able to identify
This post is intended to show how easy it is to use the F# programming language in order to explore new libraries and get stuff going quickly. It also shows the usage of various great F# features such as Record Types, Discriminated Unions, Computation Expressions and Async workflows, whilst also using a bit of mutable state, integrating with various other 3rd party .NET libraries (including use of an XBOX pad and even a Kinect!), and even some low level bit shifting and masking stuff.
F# is definitely NOT just for number crunching programs!
So, the step-son got a toy robotic arm for Christmas (no, not from me), which he has largely ignored. Partly this was due to the lack of batteries in it, which we addressed recently. However, he still wasn't really interested in it, so I figured I would have a play with it instead (a couple of weeks ago). It was either that or write documentation for the SQLProvider.
After my talk last night, several people expressed an interest in the script I was using to draw the LINQ expression trees. I have uploadedhere on github.
This is just a script I use in development. It doesn't visualize every node by a long shot, and does a fair bit of name replacing to make some of the very long generic type names readable. You will need GraphVizinstalled to use this script. You might need to point the function that does the generation at the location where you've installed it.
You can use it like this from FSI :
1 2 3 |
#load "GraphViz.fsx"
FSharp.Data.Sql.Common.QueryEvents.LinqExpressionEvent
|> Observable.add GraphViz.toGraph
|
Now, whenever you evaluate a query expression, it will pop up a bunch of images with whatever your machine uses to view SVG files, like this rather lovely one below. Have fun.
edit; this will spam your temp directory with svg files!
The latest version of the SQL provider can be found on githubor at nuget. (EDIT: MYSQL and Oacle are now a supported as well!)
The provider now has support for connecting to PostgreSQL databases! How exciting. Like SQLite, this new mode uses dynamic assembly loading and is based on the .NET connector libraries here.
The SqlProvidernow supports experimental SQLite access.
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.
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.
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.
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)
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
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 :)
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.
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!
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< @"F:\dropbox\CYOAProvider\data.dat">
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 :)
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.
