F# in SQL Server

SQL Server allows for routines written in .NET. There are plenty of examples everywhere. However, virtually all are written in C# or Visual Basic. I decided to give F# a try. I created a simple aggregate in F#, that provides a concatenation of input strings.

namespace _St

    [<SqlUserDefinedAggregateAttribute(Format.UserDefined, MaxByteSize = -1)>]
    type ConcatenationAggregate() =
       
       [<DefaultValue>] val mutable private agg : StringBuilder

       member private this.Append( s : string) =
           ignore <| this.agg.Append( if( this.agg.Length > 0) then ";" else "" ).Append( s )

       member public this.Init() = 
           this.agg <- StringBuilder()
       member public this.Accumulate(s : SqlString) =
           this.Append(s.Value)
       member public this.Merge(t2:ConcatenationAggregate) =
           this.Append(t2.agg.ToString())
       member public this.Terminate() =
           SqlString(this.agg.ToString())

       interface IBinarySerialize with
           member this.Read(r : BinaryReader) =
              this.agg <- StringBuilder( r.ReadString() )
           member this.Write(w : BinaryWriter) =
              w.Write( this.agg.ToString())

However, CREATE ASSEMBLY failed, because the referenced assembly fsharp.core was not present in the current database. So I tried importing the assembly to the database:

CREATE ASSEMBLY fsharp_core FROM 'c:\Program Files\FSharp-2.0.0.0\bin\FSharp.Core.dll'

but that failed too:

CREATE ASSEMBLY failed because type 'System.LazyFailure' in safe assembly 'FSharp.Core' has a static field 'undefined'. Attributes of static fields in safe assemblies must be marked readonly in Visual C#, ReadOnly in Visual Basic, or initonly in Visual C++ and intermediate language.

The solution was to import fsharp.core as UNSAFE assembly. Indeed, there was some more security stuff required for that. Luckily, there was a guidance on MSDN blogs.

CREATE ASYMMETRIC KEY CLR_SP_Key
FROM EXECUTABLE FILE = 'c:\Program Files\FSharp-2.0.0.0\bin\FSharp.Core.dll'
GO
CREATE LOGIN CLR_SP_Login
FROM ASYMMETRIC KEY CLR_SP_Key
GO
GRANT UNSAFE ASSEMBLY TO CLR_SP_Login
GO
USE MyTestDatabase;
GO
CREATE USER CLR_SP_Login FOR LOGIN CLR_SP_Login
GO
CREATE ASSEMBLY fsharp_core 
FROM 'c:\Program Files\FSharp-2.0.0.0\bin\FSharp.Core.dll'
WITH PERMISSION_SET = UNSAFE

Then it was rather straightforward.

CREATE ASSEMBLY ConcatenationAggregate
FROM 'C:\Test\ConcatenationAggregate.dll'
GO
CREATE AGGREGATE Test.CatAgg (@s nvarchar(max))
RETURNS nvarchar(max)
EXTERNAL NAME ConcatenationAggregate.[_St.ConcatenationAggregate]
GO
SELECT Test.CatAgg(EmailAddress) FROM Test.Person;

HtmlAgilityPack in F# Interactive

While using HtmlAgilityPack (version 1.4.0 Stable) in F# Interactive (of Visual Studio 2010), I was unable to access attributes of nodes. In order to debug, I converted my script into a compiled code and soon was notified that HtmlAgilityPack requires a reference to the System.Xml, Version=2.0.0.0 assembly. After switching the target framework of my project to .NET 3.5, I was able to reference the required assembly and all worked like a charm.

Learning F# with "Pex for fun"

Microsoft Research has published Pex for fun. It is a puzzle game. The only thing you are initially given is a signature of a function and you have to work out the implementation. There is an "Ask Pex!" button that will provide you some hints about how close you are. Wonderful!

There are couple of drawbacks though, such as that sometimes you are not given negative answers (inputs for which your implementation does not provide expected outputs), which is not very helpful. The most ugly thing I have found is how ChallengeFactorial puzzle behaves. Even the most stupid implementation (returning 0) is said to exceed the time-limit, thus effectively preventing you from succeeding.

Nevertheless, I like Pex for fun very much! And I find it especially useful when learning F# as it makes you explore the possibilities of the language.