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;

Hierarchical Data with FOR XML

I was playing with selection of hierarchical XML using FOR XML clause of Microsoft SQL Server. I was using AdventureWorks2008 database and wanted to select something like

<Root>
  <Group Name="Europe">
    <Country Code="DE">
      <Area>Germany</Area>
    </Country>
    <Country Code="FR">
      <Area>France</Area>
    </Country>
    <Country Code="GB">
      <Area>United Kingdom</Area>
    </Country>
  </Group>
  <Group Name="North America">
    <Country Code="CA">
      <Area>Canada</Area>
    </Country>
    <Country Code="US">
      <Area>Northwest</Area>
      <Area>Northeast</Area>
      <Area>Central</Area>
      <Area>Southwest</Area>
      <Area>Southeast</Area>
    </Country>
  </Group>
  <Group Name="Pacific">
    <Country Code="AU">
      <Area>Australia</Area>
    </Country>
  </Group>
</Root>

from Sales.SalesTerritory table.

FOR XML PATH

First, I tried to do that using FOR XML PATH. It was nothing particularly difficult. The only thing that is worth mentioning is that subqueries must be used to allow for grouping. (With grouping, I mean having multiple children inside a single parent node.)

SELECT [Group] [@Name],
        (SELECT [CountryRegionCode] [@Code],
                (SELECT t3.[Name] [text()]
                FROM Sales.SalesTerritory t3
                WHERE t3.[Group] = t2.[Group] 
                AND t3.[CountryRegionCode] = t2.[CountryRegionCode]
                ORDER BY [Group], [CountryRegionCode], [Name]
                FOR XML PATH('Area'), TYPE)
        FROM Sales.SalesTerritory t2
        WHERE t1.[Group] = t2.[Group]
        GROUP BY t2.[Group], t2.[CountryRegionCode]
        ORDER BY t2.[Group], t2.[CountryRegionCode]
        FOR XML PATH('Country'), TYPE)
FROM Sales.SalesTerritory t1
GROUP BY [Group]
ORDER BY [Group]
FOR XML PATH ('Group'), ROOT('Root')

FOR XML AUTO

It is not possible to mix attributes and elements when using FOR XML AUTO. However, if we do not mind having <Area Name="Germany" /> instead of <Area>Germany</Area>, it is still an option.

An interesting thing is that grouping is done automatically when selecting from joined tables. (Let me remind that FOR XML PATH requires subqueries for that to work.)

SELECT [Group].[Group] [Name]
 , [Country].[CountryRegionCode] [Code]
 , [Area].[Name]
FROM Sales.SalesTerritory [Group]
JOIN Sales.SalesTerritory [Country] 
 ON [Group].[Group] = [Country].[Group]
JOIN Sales.SalesTerritory [Area] 
 ON [Country].[Group] = [Area].[Group] 
 AND [Country].[CountryRegionCode] = [Area].[CountryRegionCode]
GROUP BY [Group].[Group], [Country].[CountryRegionCode], [Area].[Name]
ORDER BY [Group].[Group], [Code], [Area].[Name]
FOR XML AUTO, ROOT('Root')

FOR XML EXPLICIT

This is rather specific mode. The proper ordering is essential. (Note the Tag in ORDER BY clause.) That said, it works all right.

SELECT 1 Tag
 , NULL Parent
 , NULL [Root!1!!element]
 , NULL [Group!2!Name]
 , NULL [Country!3!Code]
 , NULL [Area!4!!element]
UNION ALL
SELECT 2, 1, NULL, [Group], NULL, NULL
FROM Sales.SalesTerritory
GROUP BY [Group]
UNION ALL
SELECT 3, 2, NULL, [Group], [CountryRegionCode], NULL
FROM Sales.SalesTerritory
GROUP BY [Group],[CountryRegionCode]
UNION ALL
SELECT 4, 3, NULL, [Group], [CountryRegionCode], [Name]
FROM Sales.SalesTerritory
ORDER BY [Group!2!Name], [Country!3!Code], Tag
FOR XML EXPLICIT

Conclusion

Both FOR XML PATH and FOR XML EXPLICIT can provide the required XML. The output of FOR XML AUTO would require an additional transformation, as it provides all data as attributes.

While FOR XML PATH is in general the most recommended mode, I did not like the fact I needed to use correlated subqueries. This very case is perhaps too tiny (10 rows, that is), yet the IO statistics are remarkable.

mode	scans	reads
EXPLICIT	3	6
AUTO	3	104
PATH	10(!)	140

That is, if you need to select XML and performance matters, it is always a good idea to try multiple FOR XML modes.

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.

Dynamic Positioning of Overlay

jQuery Tools Overlay can be positioned statically when used out of the box, by setting top and left properties.

The good news is that the configuration can be overwritten in onBeforeLoad event handler. This is how an overlay can be positioned next to its trigger:

jQuery('.overlayTrigger').overlay({
   ...

   onBeforeLoad: function() {
      var overlay = this.getOverlay();
      var trigger = this.getTrigger();
      var overlayConfig = this.getConf();

      //position overlay
      overlayConfig.top = trigger.offset().top 
            - overlay.outerHeight() 
            - jQuery(document).scrollTop();
      overlayConfig.left = trigger.offset().left 
            - overlay.outerWidth() 
            - jQuery(document).scrollLeft();

      //... flip popup over when appropriate
      if (overlayConfig.top < 0)
          overlayConfig.top = trigger.offset().top 
                + trigger.outerHeight() 
                - jQuery(document).scrollTop();
      if (overlayConfig.left < 0)
          overlayConfig.left = trigger.offset().left 
                + trigger.outerWidth() 
                - jQuery(document).scrollLeft();
   }
});

Rendering of HyperLink

I have been using HyperLink control recently and have found its MSDN documentation incomplete.

The HyperLink control can be displayed as text or an image. Use the Text property to specify the text to display for the HyperLink control.

If both the Text and ImageUrl properties are set, the ImageUrl property takes precedence. If the image is unavailable, the text in the Text property is displayed. In browsers that support ToolTip functionality, the Text property also becomes the ToolTip.

I needed to insert some other controls into the child Controls of HyperLink and that is what is not covered in MSDN.

There are actually three mutually exclusive branches in rendering of HyperLink.

1. If ImageUrl is set, an Image is rendered (Text property possibly used for an alternate text),
2. children Controls are rendered when available, (there is presumably more to it, but I have not analyzed it further,)
3. and finally plain Text is output otherwise.

By the way, ILSpy has proved useful in revealing the HyperLink internals.

Pex and unit testing

Everyone says that Pex generates unit tests. I am not sure if it holds. Pex knows what does your code do, but it definitely does not know what it should do.

Still I see three situations Pex can be really useful with.

• It can report you exceptions caused by inputs you have not thought of.


• When your code coverage is lower than you would expect, you may use Pex to see what paths you have not considered.


• When you want to refactor a component that is not covered with unit tests, you can use unit tests generated by Pex to ensure you do not break the original functionality.

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.