Register | Login

Stacking Code

public interface IBlog { string Dump(Stream consciousness); }

Data Migration and Archive Screens

Saturday, 11 December, 2010 @ 8:08 AM < Adam Boddington
Tags: Architecture, Building Neno, Linq, NHibernate

This is post #18 in the Building Neno series. Please click here for a description of the Building Neno project and instructions on how to access the source code for this post.

The application has been hooked up to the database for a while, ever since Spring was configured. Remember this?

Configuring Spring, Success

That was over a week ago! It's time to import my posts from posts.xml and perhaps build some screens I can use.

Post Changes

First of all, I need a couple small changes to my Post class. I have a small problem with dates. I'm in Brisbane, Australia, my hosting server is somewhere in California, and RSS wants publish dates in UTC format. I need to store my publish date as a DateTimeOffset instead of a DateTime. New in .NET 3.5, this lets me store the offset for my timezone with the dates, making it easy to display the "local" time (Brisbane time, not California time) as well as UTC time.

In addition, I want to enforce a couple new constraints. (For now I'll do this in the database -- validation rules will come later.)

  1. A unique constraint on publish date (the date and time). This is essential to my sorting scenarios.
  2. A unique constraint for publish date (the date, not the time) and slug. The publish date and slug uniquely identifies a single post in my URL schema.

I'm not sure how DateTimeOffset is stored in the database. Is 2010-01-01 07:00 +01:00 the same as 2010-01-01 08:00 +02:00? Would they violate the unique constraint? EDIT: A quick test in SQL Server confirms they do, which is perfect.

The changes to the Post class look like this...

[DisplayName("Publish Date, Time and Offset")]
public virtual DateTimeOffset PublishDateTimeOffset
    get { return _publishDateTimeOffset; }
    set { _publishDateTimeOffset = value; }

[DisplayName("Publish Date")]
public virtual DateTime PublishDate
    get { return PublishDateTimeOffset.Date; }
    private set
        // Do nothing.

The do nothing private setter is a trick I use with NHibernate. It lets me calculate a value for it in the class and continue to have NHibernate persist it as a property. There are probably better ways to set that up, but this works for me.

The changes to the table look like this...

[PublishDate]           DATE           NOT NULL,

The DATE data type lets me set a unique index on just the date and ignore the time.

CREATE UNIQUE INDEX [UX_Neno_Post_PublishDate_Slug]
    ON [dbo].[Neno_Post] (

Date Migration

I'll reuse the DatabaseController class which was used earlier to create the schema.

public ActionResult Import()
        string uri = string.Format("{0}://{1}{2}", Request.Url.Scheme, Request.Url.Authority, Url.Content("~/content/import-posts.xml"));

        IEnumerable<Post> posts = XDocument.Load(uri)
            .OrderBy(element => (DateTime)element.Element("publishDateUtc"))
            .Select(post =>
                new Post(DomainModel.User.Current)
                    PublishDateTimeOffset = (DateTimeOffset)post.Element("publishDateUtc"),
                    IsPublished = true,
                    Title = (string)post.Element("title"),
                    Slug = (string)post.Element("slug"),
                    Text = (string)post.Element("text")

        Messages.Add(string.Format("{0} posts read and converted.", posts.Count()));
        Messages.Add(string.Format("{0} posts imported.", posts.Count()));
    catch (Exception exception)

    return RedirectToAction("index", "home");

It's not too hard to generate post objects from the XML file. Once I have them though, I want to create all of them or none of them. Calling IPostService.CreatePost multiple times won't achieve that. This is a unit of work by itself and a candidate for a new service method.

public class PostService : Service, IPostService
    // ...

    public void ImportPosts(IEnumerable<Post> posts)
        ITransaction transaction = Context.BeginTransaction();

            foreach (Post post in posts)



    // ...

Putting it here lets me reuse it in another screen, maybe a text file upload screen -- or even in another UI, like a WPF data loader application. It's also an example of a service layer method using a transaction across multiple repository method calls.

The StringClob Gotcha

Running through my data I notice a lot of my posts have been truncated. The Text column in my post table is defined as NVARCHAR (MAX), it should be able to hold as much text as I can write. So what went wrong?

The problem is in my NHibernate mappings. NHibernate uses reflection to figure out the type of each property and takes a best guess at the type of the column it's mapped to. In the case of strings, NHibernate will allow up to 8,000 bytes of data (4,000 unicode characters), the maximum for any SQL Server column, unless I tell it specifically that it is NVARCHAR (MAX) (the new NTEXT). I do that by specifying a type in my mapping file.

<property name="Text" type="StringClob" />

No more truncation. (By the way, a CLOB is a Character Large OBject.)

Older Newer Navigation

My posts are uploaded, but my default screen is still showing them all in a massive dump. I want to cut that down to just one and put in some navigation to get to the older posts. To handle the navigation, I'm going to pass a new view model to my post screens.

public abstract class OlderNewer
    protected OlderNewer(IEnumerable<DateTimeOffset> publishDateTimeOffsets)
        publishDateTimeOffsets = publishDateTimeOffsets.OrderBy(pdto => pdto);

        if (publishDateTimeOffsets.Count() > 0)
            Older = Container.Get<IPostService>().GetPosts()
                .Where(post => post.PublishDateTimeOffset < publishDateTimeOffsets.First())
                .OrderByDescending(post => post.PublishDateTimeOffset)

            Newer = Container.Get<IPostService>().GetPosts()
                .Where(post => post.PublishDateTimeOffset > publishDateTimeOffsets.Last())
                .OrderBy(post => post.PublishDateTimeOffset)

    public Post Older { get; private set; }
    public Post Newer { get; private set; }

public abstract class OlderNewer<T> : OlderNewer
    protected OlderNewer(T payload, IEnumerable<DateTimeOffset> publishDateTimeOffsets)
        : base(publishDateTimeOffsets)
        Payload = payload;

    public T Payload { get; private set; }

public class OlderNewerPost : OlderNewer<Post>
    public OlderNewerPost(Post post)
        : base(post, post != null ? new[] { post.PublishDateTimeOffset } : new DateTimeOffset[0])

// ... and so on

This will give each screen access to the next oldest and the next newest posts.

Archive Screens

I want a decent number of archive screens. In an old Movable Type blog, my archive screens were a function of URL reduction, which is something I'd like to do again. For example:

I like this approach because it can help me with bad links also. If a post isn't found, I can fall back to the daily archive (perhaps the slug changed). If there's nothing for a particular day, I can fall back to the monthly archive (perhaps the publish date changed). If there's nothing for the month, I can fall back to the yearly archive, and so on.

Not Found

That's enough for today. I still don't have post new and edit screens, so I'll have to upload this post through /database/import.

There are 0 comments.


Leave a Comment

Please register or login to leave a comment.

Validation with NHibernate and Data Annotations

Post Screens

Validation with NHibernate and Data Annotations

Post Screens

browse with Pivot



Building Neno

Recent Posts

Codility Nitrogenium Challenge
OS X Lock
HACT '13
Codility Challenges
Priority Queue


Architecture (13)
Brisbane Flood (1)
Building Neno (38)
C# (4)
Challenges (3)
Collections (1)
Communicator (1)
Concurrency Control (2)
Configuration (1)
CSS (5)
DataAnnotations (2)
Database (1)
DotNetOpenAuth (2)
Entity Framework (1)
FluentNHibernate (2)
Inversion of Control (5)
JavaScript (1)
jQuery (4)
Kata (2)
Linq (7)
Markdown (4)
Mercurial (5)
NHibernate (20)
Ninject (2)
OpenID (3)
OS X (1)
Pivot (6)
PowerShell (8)
Prettify (2)
RSS (1)
Spring (3)
SQL Server (5)
T-SQL (2)
Validation (2)
Vim (1)
Visual Studio (2)
Windows Forms (3)
Windows Service (1)


Powered by Neno, ASP.NET MVC, NHibernate, and small furry mammals. Copyright 2010 - 2011 Adam Boddington.
Version 1.0 Alpha (d9e7e4b68c07), Build Date Sunday, 30 January, 2011 @ 11:37 AM