Compsoft Flexible Specialists

Compsoft plc

Compsoft Weblog Compsoft Website News Archive Privacy Policy Contact Us  

Monday, April 27, 2009

Migrating MySQL Unix timestamps to MSSQL date time

Just this week I've been migrating data held in a MySQL database to a Microsoft SQL database, and I've run into a bit of a headache with temporal values being stored as integers.

MySQL is a great database for the web. It has some nice features, one of which being the ability to export the table structure and all the data into one script you can download. It can even be zipped on the way. This makes transfering data super simple.

Super simple that is unless dates and times are stored as Unix timestamps, and its going into a MSSQL database.

Okay, so there is a certain amount of cleanup needed in the scripts going from one database to the other, but thats fairly easily done with an amount of regex.

However, dates and times stored as int are in Unix time (or POSIX time). So to get the actual date time of the value in MySQL you need to do something like:


There isn't an equivalent of FROM_UNIXTIME in MSSQL so to get at the date time value from there you need to add to the value the number of seconds since the Unix epoch (1st January 1970 00:00:00), like this:

SELECT DateAdd(ss, date_added, '1970-01-01') FROM NewsStory

Et viola! Something sensible you can use.

Labels: , , , ,

Thursday, April 23, 2009

Logging all LINQ to SQL queries used in a single page lifecycle

We absolutely love LINQ to SQL here at Compsoft. It's the best ORM we've used and it's made a marked speed increase in both our application development and application performance.

A concern we had initially was the amount of "magic" involved. Occasionally we'd find that LINQ was running rather more queries than we'd expected (the DataLoadOptions weren't always behaving quite as we'd anticipated), or the queries were unexpectedly complicated. Also, with all the ease of data access that LINQ gives you, it's easy to get lazy and forget quite how many trips you're making to the database to generate a single web page.

We felt we needed an easy way to see what was going on under the hood. We didn't want to have to go a long way out of our way to find out which pages took a lot of database effort: we wanted to be warned about this up-front.

So here's what we made: when in debug mode, this appears in the top corner of every page we're working on:

... which expands to this when clicked:

The more queries it takes to make the page, the redder the little "DB hits" box appears, drawing our attention to pages that accidentally take a lot of work to produce.

LINQ to SQL makes it easy to capture this sort of information. The Log property of DataContext is a TextWriter to which all queries are written, together with the values of any query parameters. Setting this to a new StringWriter in the constructor of our application's DataContext catches all the queries it performs.

We typically use one DataContext per page lifecycle, so it would seem it would be sufficient to dump the contents of the log to the page late in the page lifecycle. However there are occasions where we create additional DataContexts for particular tasks, and we want to know what they are up to too.

Here's the solution we came up with:

The data context class for the application inherits from this BaseDataContext class, which lives in our reusable code libraries:

// codesnippet:4963E640-80C0-4577-A2B2-F0F6A8D41E56

public class BaseDataContext : DataContext


#if (DEBUG)

    private static TextWriter logWriter;


    private static StringBuilder _Logger;


    public static StringBuilder Logger


        get { return _Logger; }



            _Logger = value;

            logWriter = new StringWriter(value);




    static BaseDataContext()


        BaseDataContext.Logger =

            new StringBuilder();




    public BaseDataContext(string connectionString)

        : base(connectionString)


#if (DEBUG)

        this.Log = logWriter;




[Aside: we like Jeff Attwood's idea for flagging code snippets with GUIDs.]

As you can see, the log is static, so all queries from all DataContexts will be written to the same log. This would be nightmarish if the server were handling numerous requests concurrently, but this is just a development tool for when the application is running on your local machine (which is partly why the logging only happens when the application's in debug mode).

That takes care of logging the queries; next we have to display them. We have this in the class that all our pages inherit from:

#if (DEBUG)

protected override void Render(HtmlTextWriter writer)


    // Snip - write the contents of the log

    // to the page



    BaseDataContext.Logger = new StringBuilder();




This ensures the log is cleared down right at the very end of the page lifecycle, ready to start logging queries for the next page. Also, as the log is only cleared after it's written out, no queries get lost if the page lifecycle ends prematurely in a redirect.

The snipped code is a bit lengthy, but it basically renders the database hit count with a little jQuery to expand the full details of the queries when clicked, as in the screenshots above.

Astonishingly you can't do this sort of thing at all in LINQ to Entities - it doesn't have this logging support - so for all the flexibility that it offers, it's still missing some features that seem pretty crucial to us.

Labels: , , ,

Monday, April 20, 2009

jQuery plugins - A treasure trove of awesome

In order to provide a richer, cross-browser, more interactive experience for our client web applications, we make use of jQuery. jQuery is a robust javascript framework that takes a lot of the complexity out of writing client side javascript.

One of the most powerful features of the framework is that it's been developed with a highly extensible plugin model.

We recently decided to add some interactivity to our partner site Bluesulphur to replace some of the flash content we currently have. As part of that, I looked at some of the plugins currently available.
These are some of the nicest plugins I found.

Facebook search engine - A Facebook style autosuggest plugin using jQuery. It's particularly pretty and although it currently uses a php source for data, this isn't a big limitation, these can be swapped out to use .NET web services as a provider quite easily.

coda bubble is a mac like popup that could be put to use in many situations.

jQuery Tablesorter is a cute little plugin that makes any table sortable

Fancybox is a popup plugin that you can use for popup image galleries, the popups have nice transition effects as well as inbuilt navigation and grouping functionality.

I thought for the last example, I'd show an html editor and I really like markitup, it's both simple and sexy. It looks like it's an easy integrator too, which is always a plus.

So, to sum up, there are a lot jQuery plugins out there, more than 2500 just on the jQuery plugin site alone. New and awesome plugins are being released every day, check them out!

Oh, and Compsoft are a great choice for integrating jQuery interactivity and plugins into your site too! *wink*

Labels: , , ,

Thursday, April 09, 2009

SQL Server Express Automated Backup

SQL Server Express does not come with the SQL agent support which means there is no easy way to schedule backups of databases.

After some googling, a great article by Jasper Smith came up. He has built a wonderful stored procedure that backups all the databases on the instance and even produces a report for the backup.

Really nice feature of the script is that it will even allows you to manage how long the backups are kept for!

Read the original article