A Tale of Epic Epicness…

A tale of epic epicness…
An adventurous attempt at twisting Razor and abusing Sql Server
I have many hair-brained ideas, but none as mental as this one. I wanted to introduce the power of Razor to Sql Server, to allow for database-level template parsing! You’re probably asking what the hell I was thinking, but to understand fully I need to explain a few things about our corporate network migration, and the limitations it now provides.

As part of a large migration from our smaller network to a larger, globally managed network, a lot of new restrictions were put in place as to what applications and services can or can’t do. The biggest issue was that we can no longer send SMTP emails, only approved services can do 🙁 . Also, the sheer volume of logging/error emails being despatched has slowly been getting out of hand for a while now – so we were looking to introduce a centralised logging mechanism in a database, which handles throttling and esclation of logging data where required. Updating our existing applications/services to use this new logging tool is easy… we just take out the email logger, and plug in our shiny new database logger (thanks Chris and Andy!).

The database would be controlling how often items are reported, so it would need the ability to create the html emails we despatch. Different applications require and report different information, so it was important to me to provide a flexible templating solution for whatever is reported. And thats when I considered Razor.

Now, there are a number of challenges which will cause this dream to be a complete failure, and these include (but are not limited to)

Razor is a .NET 4.0 assembly, and Sql Server 2008 supports .NET 3.5
Compiling dynamic assemblies and loading them on Sql Server using Assembly.Load() fails
So here’s what I did…

Backporting System.Web.Razor
The first challenge that had to be overcome was the initial issue with the System.Web.Razor library. Razor was introduced with ASP.NET MVC 3, which of course is a .NET 4.0 assembly. To enable us to even consider using Razor on Sql Server we have to acknowledge that Sql Server 2008 (which was my target version) supports up to .NET 3.5 (with the .NET 2.0 runtime). This means the standard release System.Web.Razor.dll assembly would not work at all. We need to somehow backport this to .NET 3.5.

Luckily, the source code for the library is available at the ASP.NET codeplex site for us to abuse. First thing first, change the project target (to .NET 3.5 Client Profile) and then try to compile. Whoa, as expected a whole host of errors. Missing ISet? No Tuple?, no Task? Lots to try and fix. Let’s take them one step at a time…

Fixing ISet
The generic ISet interface was introduced in as part of the .NET 4.0 BCL, and doesn’t have a counterpart in the .NET 3.5 BCL. But, the standard HashSet does exist. Simple fix, replace all instances of ISet with HashSet.

Fixing Tuple
The ordered set type, Tuple was also first introduced as part of .NET 4.0. This was a quick type to chuck together:

public class Tuple
{
private readonly T1 _t1;
private readonly T2 _t2;
internal Tuple(T1 t1, T2 t2)
{
    _t1 = t1;
    _t2 = t2;
}

public T1 Item1 { get { return _t1; } }
public T2 Item2 { get { return _t2; } }
public class Tuple
{
public static Tuple Create(T1 t1, T2 t2)
{
return new Tuple(t1, t2);
}
}
Code language: PHP (php)

}

Fixing Task
Gah, the Task type… I don’t know enough about how it works internally to build a new Task type from scratch. After a bit of googling you can uncover the lineage of that particular type (Task Parallel Library), and a version comes bundled with the Reactive Extensions project. When Reactive was being developed, it was still pre .NET 4.0, so they were actively targeting .NET 3.5. This is good, it allows us to install the v1.0.2856.0 (this is important!) version of Rx for .NET 3.5 (you can find it here), and grab the System.Threading.dll assembly from C:\Program Files\Microsoft Cloud Programmability\Reactive Extensions\v1.0.2856.0\Net35. Add that as a reference to the project.

Other small fixes…
There are a few other fixes which you need to tackle, firstly String.IsNullOrWhiteSpace, and the Enum.HasFlag methods which don’t currently exist.. They are pretty easy to fix… and also StringBuilder.Clear. I wrapped all these up in a simple extension class:

static class Extensions
{
public static bool IsNullOrWhiteSpace(this string value)
{
return (string.IsNullOrEmpty(value)) || (string.IsNullOrEmpty(value.Trim()));
}
public static void Clear(this StringBuilder builder)
{
    builder.Length = 0;
}

public static bool HasFlag(this PartialParseResult result, PartialParseResult flag)
{
    return ((result & flag) == flag);
}

public static bool HasFlag(this RecoveryModes modes, RecoveryModes flag)
{
    return ((modes & flag) == flag);
}
Code language: JavaScript (javascript)

}
Once we’ve updated any method calls, we can try compiling again, and hazaah! We have now backported System.Web.Razor to .NET 3.5. There are oppotunities here for future projects, potentially including backporting the MVC RazorViewEngine to .NET 3.5 (any takers?) and also a .NET 3.5 version of the RazorEngine to target those scenarios where you can’t actually move on to using .NET 4.0 just yet (and believe me, sadly there are many!).

Shrinking RazorEngine for Sql Server
The RazorEngine project (current release v2 – v3 is still in the works, I just tend to get sidetracked with wacky ideas…) has been a great project to work on, from its inception (thanks Ben @BuildStarted) to where we are with it now. The only downside to it in this instance is that it is a bit complex to configure. What we need to do, is create a streamlined version of it. Now, Ben (@BuildStarted) had already done something similar (Git) when he was assisting with the awesome TinyWeb Razor View Engine. I took that as a starting point, and put together a severly cut down verison of RazorEngine (hereby called RazorEngineLite) that focused purely on the C# language and Html markup. No template isolation, no anonymous and dynamic types (which we couldn’t support on .NET 3.5 anyways). It’s a real barebones RazorEngine release. What I did want to do though, is I wanted to look ahead at how I might introduce a caching mechanism for templates that might take advantage of our database level access.

Introducing the ITemplateCache
RazorEngineLite has support for a dual-level caching mechanism. The Runner type that is included uses a RunnerCache type which is an in-memory cache. This is the primary location that RazorEngineLite will look for previously compiled template types. If the template type has not been previously cached, it will search through a possible series of second-level caching mechanims to try and grab an instance. The reason I wanted to support this, is to allow compiled types to be pushed back to the database, so in scenarios where the database is taken offline (or simply the AppDomain unloaded), when it starts again, it doesn’t have to recompile the template, it can just grab the cache details.

The baseline RazorEngineLite assembly bundles the default ITemplateCache instance which is automatically instantiated by the Runner type. Libraries can pass in their own instances to provide the second-level cache sources.

Also, like its v3 big brother, the caching mechanism supports a hashcode operation. Essentially when a template is cached, the current hashcode is cached alongside it. Should the template content change when we call to parse it again, the cached item will be invalidated (as the new and old hashcodes will be different) – this prompts a new compilation and a re-caching of the updated type.

We do some other peformance improvements too, and an idea spun from our codeplex project has evolved into us caching constructor delegates, so when we actually want to spin up new instances of our templates, we can just re-use our delegates instead. This is all handled by our TemplateCacheItem type:

public class TemplateCacheItem
{
public int HashCode { get; private set; }
public Type Type { get; private set; }
public Func Factory { get; private set; }
public TemplateCacheItem(int hashCode, Type type)
{
    HashCode = hashCode;
    Type = type;

    CreateFactory();
}

private void CreateFactory()
{
    var ctor = Type.GetConstructor(new Type[0]);

    Factory =  Expression.Lambda<Func<ITemplate>>(
        Expression.New(ctor)).Compile();
}
Code language: JavaScript (javascript)

}
The Sql Server Mountain
Right, here is where it gets really interesting. The Sql CLR is a sandboxed runtime that runs within Sql Server. Assemblies can be hosted by Sql Server per database, and the AppDomain in which they run is per owner. There are a number of restrictions to what you can and can’t do with hosted assemblies, and the SQL CLR sandbox is very good enforcing that.

When you register assemlies, you do so through the CREATE ASSEMBLY command, and you specify the permission set the assembly will conform to. Sql Server will verify the integrity of the assembly and ensure that it conforms the requirements of the requested permission set (SAFE, EXTERNAL ACCESS and UNSAFE). Using SAFE wouldn’t work for what we want to do, as we’re going to need to access the file system. Attempting to register our assembly with EXTERNAL ACCESS fails, as it doesn’t like static fields that are not marked as read-only (I did look to change these, but stumbled at the System.Threading.dll assembly, which would need to be recompiled…). This leaves us with UNSAFE.

Before we start registering anything, we need to make sure that we’ve done some additional configuration:

ALTER DATABASE [ErrorLogging] SET TRUSTWORTHY ON;
sp_configure 'clr enabled', 1
RECONFIGURE
Code language: JavaScript (javascript)

Quick sidebar: Instead of registering all three assemblies (RazorEngineLite, System.Web.Razor and System.Threading, I opted to ILMerge them into a single assembly, RazorEngineLite.Merged.dll)

CREATE ASSEMBLY [RazorEngineLite]
AUTHORIZATION [ErrorLoggingClient]
FROM 'C:\CLR\RazorEngineLite.Merged.dll'
WITH PERMISSION_SET = UNSAFE
Code language: JavaScript (javascript)

When the assembly is registered it is exposed to any SQL CLR projects that target the database. Our ErrorLogging project is one such project, so I can now add a reference to our hosted assembly.

Now we are in a position to try and start using Razor, but immediately we are thwarted. Firstly, we can’t add our referenced assembly to the compiler operation as the C# compiler expects an absolute path to the assembly so we can compile our dynamic templates, and after that, we can’t use Assembly.Load() as its explicitly forbidden (even when using the UNSAFE permission set). Frowning face.

Referencing our assemblies for compilation
The C# compiler requires explicit references to non GAC’d assemblies when it is building the metadata of a new assembly. Our hosted assemblies exist in Sql Server, so we can’t actually provide a folder path to them in their current state. What I did, was to write out the hosted assemblies back to the file system, and then reference them explicitly via the compiler. The first time our CLR stored procedure is accessed, the static constructor of it’s parent type enforces that we grab any hosted assemblies (that are user defined) and writes them back out:

CREATE PROCEDURE [re].[GetHostedAssemblies]
AS
BEGIN
SELECT a.[name] AS [HostedName], a.[clr_name] AS [AssemblyName], f.[name] as [Path], f.[content] as [Content]
FROM sys.assemblies a INNER JOIN sys.assembly_files f ON a.[assembly_id] = f.assembly_id
WHERE a.is_user_defined = 1
END
Code language: PHP (php)

private static void EnsureHostedAssemblies()
{
if (!Directory.Exists(_referenceDirectory))
Directory.CreateDirectory(_referenceDirectory);
using (var conn = new SqlConnection("Context Connection = true"))
{
    conn.Open();

    using (var command = new SqlCommand("re.GetHostedAssemblies", conn))
    {
        command.CommandType = CommandType.StoredProcedure;

        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                string filename = (string)reader["Path"];
                if (filename.Contains("\\"))
                    filename = filename.Substring(filename.LastIndexOf('\\') + 1);

                if (!filename.Contains("."))
                    filename += ".dll";

                byte[] data = (byte[])reader["Content"];
                using (var writer = new BinaryWriter(new FileStream(Path.Combine(_referenceDirectory, filename), FileMode.Create, FileAccess.ReadWrite)))
                {
                    writer.Write(data);
                }
            }
        }
    }
}
}
Code language: JavaScript (javascript)

This will pick up any assemblies that are hosted and defined by us, therefore, our RazorEngineLite.Merged assembly, and also the library we are currently debugging with Visual Studio (which incidentally is registered without a .dll suffix, which is why the code checks for a missing file extension). We build a list of these assemblies and pass them back into the RazorEngineLite’s Compiler type so we can manually reference our required assemblies.

Loading dynamic assemblies in Sql Server
One of the biggest problems with this whole plan of mine is that I didn’t realise that I couldn’t use Assembly.Load() anywhere in my code. Sql Server enforces that we can’t use this, even with our UNSAFE permission set. Thats a spanner in the works! My workaround for this feels dirty, but actually works really well. There are two parts to this workaround. Firstly, after we compile the dynamic assembly, we need to ensure we register that assembly with Sql Server. I call a procedure in my database which does this for me:

CREATE PROCEDURE [re].[CreateAssembly]
@name VARCHAR(1000),
@path VARCHAR(MAX)
AS
BEGIN
DECLARE @sql NVARCHAR(MAX)
SET @sql = N'CREATE ASSEMBLY [' + @name + '] AUTHORIZATION [ErrorLoggingClient] FROM ''' + @path + ''' WITH PERMISSION_SET = SAFE';    
EXECUTE sp_executesql @sql;
END
Code language: CSS (css)

By setting the generated assembly to be finalised on disk instead of memory, we now have a dynamically compiled assembly, which we can register with Sql Server before we attempt to load it.

Next, instead of explicity using Assembly.Load() (which we are indirectly calling when we access the CompiledAssembly property of the CompilerResults type [ref: RazorEngineLite’s Compiler type]), we can make use of Type.GetType(…) but pass in the fully qualified assembly name of the type.

<!-- wp:paragraph -->
<p>string typeName = "RazorEngineLite.Dynamic." + name + ", " + name + ", version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil";<br>Type type = Type.GetType(typeName);<br>When the AppDomain tries to resolve that type, it will find it, because the owning assembly has already been registered. No Assembly.Load(), just pure awesomenous!</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>Let’s look at an example:</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>[SqlProcedure]<br>public static int TestCompile(string name, string template, ref string result)<br>{<br>try<br>{<br>var person = new Person { Name = "Matt", Age = 27 };<br>var runner = new Runner(GetReferenceAssemblies(), CreateAssembly, new SqlTemplateCache());</p>
<!-- /wp:paragraph -->   
 result = runner.Parse(name, template, person);

    return 0;
}
catch (Exception ex)
{
    SqlContext.Pipe.Send(ex.Message);
    SqlContext.Pipe.Send(ex.StackTrace);

    result = null;
    return -1;
}
Code language: HTML, XML (xml)

}
In the above sample CLR procedure, we’re creating an instance of our model, Person, and creating a new Runner instance. The Runner instance will call to the Compiler to create the associated template, passing in our referenced assemblies, and a callback delegate which is actioned when we have created the dynamic assembly (this is the point we register it with Sql Server). We get our parsed template result back, merged with our model information (ala Razor), and return:

DECLARE @result VARCHAR(MAX)

EXEC dbo.TestCompile ‘test’, ‘Hello @Model.Name’, @result OUTPUT

PRINT @result
Which results in:

Hello Matt
Adding a second-level cache
I mentioned previously that the RazorEngineLite project supports a dual-level cache mechanism. Well, in this use case, I wanted to take advantage of Sql Server to provide an additional caching mechanism. The reason being, is that if the AppDomain is unloaded and restarted, there is no real point in recompiling a template that has already been registered with Sql Server. To support this second-level cache, I created a SqlTemplateCache type:

public class SqlTemplateCache : ITemplateCache
{
public void Add(string name, TemplateCacheItem item)
{
if (string.IsNullOrEmpty(name) || item == null)
return;
    
using (var conn = new SqlConnection("Context Connection = true"))
    {
        conn.Open();

        using (var command = new SqlCommand("re.Add", conn))
        {
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@name", name);
            command.Parameters.AddWithValue("@hashCode", item.HashCode);
            command.Parameters.AddWithValue("@typeName", item.Type.AssemblyQualifiedName);
            command.Parameters.AddWithValue("@assembly", item.Type.Assembly.GetName().Name);

            command.ExecuteNonQuery();
        }
    }
}

public TemplateCacheItem Find(string name)
{
    if (string.IsNullOrEmpty(name))
        return null;

    using (var conn = new SqlConnection("Context Connection = true"))
    {
        conn.Open();

        using (var command = new SqlCommand("re.Find", conn))
        {
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@name", name);

            using (var reader = command.ExecuteReader())
            {
                if (!reader.Read())
                    return null;

                int hashCode = (int)reader["HashCode"];
                string typeName = (string)reader["TypeName"];

                Type type = Type.GetType(typeName);

                if (type == null)
                    return null;

                return new TemplateCacheItem(hashCode, type);
            }
        }
    }
}

}

We store the type information for the registered template, so when an initial check for a compiled assembly that won’t be in memory, it can check this table for the secondary cache information, get the Type, and then promote it to the primary cache (in memory).

Wrapping it Up
This was probably my biggest challenge yet, and I’m glad I’ve got it done. But before you even consider it’s actual application in production use, a lot of time needs to be spent ensuring the code is performant and most importantly.. safe. Ensuring the stability of Sql Server should always be a priority when you start integrating CLR procedures into your database platform.

If you like what you see, heck if you don’t like what you see, I welcome any and all feedback!

For those interested in seeing it in action, the project(s) are attached, but you’ll need to do some work to prepare your database to support it.

RazorEngineLite/System.Web.Razor – after build, using \Build\Merge.bat to ILMerge assemblies into RazorEngineLite.Merged.dll
ErrorLogging
ErrorLoggingSql – baseline structure – make sure you create database, user and schema ([re]) first.