Translate Sitecore fast queries to SQL

Note: A german version of this blog post can be found here.

Last week I tried to figure out how the SQL queries look like that get generated from your Sitecore fast queries. Either I need to improve my Google skills or there is just not a lot of information out there. So I decided to takle the problem myself.

As it turns out, the fast queries get translated within the main data provider. So I created a new data provider which inherits from the main data provider. With a little help of my favorite decompiler, I've overwritten the QueryFast(...) method without changing the default behavior, but with some additional log output.

Disclaimer: I'm not affiliated with JetBrains, just really loving their work.

protected override IDList QueryFast(string query, CallContext context)
{
    var baseIdList = this.SelectIDs(query, context);
    if (baseIdList != null && baseIdList.Count > 0) return baseIdList;

    var parameters = new ParametersList();
    var sql = this.Translator.TranslateQuery(query, context, parameters);

    Log.Debug(string.Format("FastQuery: {0}", query), this);
    Log.Debug(string.Format("SQL Query: {0}", sql), this);

    if (sql == null) return null;

    var stopwatch = Stopwatch.StartNew();
    using (var reader = this.Api.CreateReader(sql, parameters.ToArray()))
    {
        var idList = new IDList();
        while (reader.Read())
        {
            idList.Add(this.Api.GetId(0, reader));
        }

        context.CurrentResult = idList;
    }

    Log.Debug(string.Format("Query Time: {0}ms", stopwatch.ElapsedMilliseconds), this);
    return null;
}

This is already nice, but the SQL query still had all its parameters instead of the actual values in it. As I wanted to copy and paste the query and run it directly against the database without replacing the parameters with the values manually, I had to write an additional helper method.

private string FormatSqlQuery(string sql, ParametersList parameterList)
{
    var parameters = parameterList.ToArray();
    for (var i = 0; i < parameters.Length; i = i + 2)
    {
        var parameterName = string.Format("@{0}", parameters[i]);
        var parameterValue = string.Format("'{0}'", parameters[i + 1]);

        sql = sql.Replace(parameterName, parameterValue);
    }

    return sql;
}

At this point I realized, that we use a lot of fast queries in some places and it was really hard to find the one that I'm interested in. So I added a list of keyword, that must be in the original fast query and only output the addional information when the criteria is met.

private bool IsTraceEnabled(string query)
{
    return this.Filters.Count == 0 || this.Filters.Any(query.Contains);
}

The configuration of the data provider is done via an include-file and patches the main data provider. Also the possiblity to define filters can be done through the configuration file.

<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/" >
  <sitecore>
    <dataProviders>
      <main  type="Loquacious.DataProvider.LoquaciousDataProvider, Loquacious.DataProvider" patch:instead="main">
        <param connectionStringName="$(1)" />
        <Name>$(1)</Name>
        <!--
        <filters hint="list">
          <filter>{E248FD78-B482-49FD-9151-F0A926448576}</filter>
        </filters>
        -->
      </main>
    </dataProviders>
  </sitecore>
</configuration>

I tried to leverage the Sitecore functionality by adding the filters with the hint="list" attribute. A filter can be any string that should be in the fast query, even a Sitecore ID. Be aware to either add a list with at least one entry or don't add the list at all. Otherwise, Sitecore will throw an exception.

To make it really easy to get started with this very loquacious data provider, I created a NuGet package. Just install the package and you're ready to go. In the package is a version for .NET 4.0 compiled against Sitecore 6 and a version for .NET 4.5 compiled against Sitecore 7.

The full source code and more documentation can be found on GitHub.

Any suggestions, improvements or questions are very welcome.

Note: Read this blog post by @retohugi about a very nice addition to see real-time log output http://sitecore.unic.com/de/2013/12/11/Log2Console (only available in german)

Story of my tech life: zip files

Working in the technical industrie can be funny sometimes. Some of the stories you wouldn't believe, if they didn't happen to yourself. Let me tell you a tiny story which happened today:

It's friday and we were waiting for a customer to upload a database dump to our FTP-Server. (This story happening on a friday itself is a classic). To restore the dump and adapt our codebase until monday morning is critical to the project to finish some features for the test release next thursday. (You know how it is: it's no fun, if you don't finish the implementation 5 minutes before deploying it to the customer).

So when the database dump finally arrived, it happened to be a text file. A text file, 13GB in size. Replacing the file extension with .zip was my first guess. And it seamed to work. When I tried to unzip it, it asked me for a password. Ok - let's check the e-mails...found it! Unzipping the text-zip-file somehow took about an hour after which the content was on the disk. Which was - again - a zip file. Hmm..

The system would not let me unzip it (even with the password ;) ), so I figured let's give the extension rename another try. After renaming the zip file in the text fiel to a .bak file let me restore it to the database server.

For our customer to be in the financial industry, maybe that is somekind of security feature. Or maybe it's totally obvious to store a backup file as a zip file inside a zip file dressed up as a text file. I'm not sure. Anyhow, story of my tech life..

From being a ghost to running on Ghost

I started this blog over a year ago and I managed to write two posts. Let me repeat that for you, but this time slowly: T-W-O

For a long time, I wanted to start a technical blog. Mainly for myself. But it took me years to decide on which platform to run it on. In the end, I told myself it doesn't really matter, as long as I finally just do it. So I installed Wordpress from the Windows Azure Marketplace, which was really easy. But Wordpress is a monster. Not in a bad way. But it's more of a CMS than just a blog engine. And all I wanted to do is write a blog.

When I discovered Ghost I was thrilled. Isn't that just what I was looking for? I don't know it yet, but let's give it a try. And this is it: Ghost running on Windows Azure. As simple to setup as Wordpress was. All my posts are migrated (seriously, it's really only two).

And this is how I feel about it:

/* by undokbeka */

Disclaimer: A niftier design might follow :)

How to embed code snippets with GitHub Gists

I started working for a new company almost three month ago. We do a lot of web development and we also have a blog where we write down some of our experiences. Last week I wrote my first article and I had a rather unpleasing experience inserting code snippets. Since I read a lot of tech blogs, I’m used to see most of the code samples with nice syntax highlightning. The obvious and maybe most common way to achive this is to use a syntax highlightning JavaScript library like SyntaxHighlighter and embed your code within a <pre> tag.

But it’s all about the cloud these days, isn’t it. So why not leverage the power of the cloud to show code snippets in your blog posts? GitHub has this thing called Gists. A Gist is a complete GitHub repository where you can put in your code snippets. It’s as easy as clicking “Create a new Gist” …

Create a new Gist

… and the copy and past your code

Copy and paste your code

See the code of your gist

To include the gist in your blog post, just copy the provided JavaScript call in your post where you want to show the snippet and boom. Good times.

Information on embedding gist

It’s also a great way to share your code, because people can fork your Gists or download them as zip files. And there is absolutely no setup required on your website to get going. The one and only thing you need is a GitHub account, but you have that already, right? (If not, go here and sign up now) The downside of Gists is that your code is loaded via JavaScript and therefore isn’t SEO friendly. But that shouldn’t matter in most cases.

Finally, this is how a Gist would look like in a blog post:

I will use GitHub Gists for all the code examples in this blog from now on to see how well it does in the everyday blogging life.

Hosting WordPress on Windows Azure Websites

Hosting WordPress on Windows Azure could not be any easier. It’s basically done in two steps:

  1. Create a Website in the Azure portal with the WordPress app from the gallery.
  2. Go to the newly created Url and follow the WordPress installation steps.

It took me about two or three minutes. Now the question is, why would I want to host a PHP blogging engine on a Microsoft powered plattform? The answer is simple: Because I can.

Setting up a custom domain for your new website is pretty straight forward, but you need a little bit of knowledge about DNS configuration. One downside of having a custom domain for your Windows Azure website is that you have to scale it up from a free to a shared mode. Which basically means your credit card will get involved. I will have to keep an eye on the bill, but since Windows Azure is supposed to charge you only for the resources you actually use, I should be fine.

There is already a lot of good information about Windows Azure websites out there, so I did not want to make another detailed how-to article. Nevertheless, if you feel like you could use another one, drop me a note in the comments.