Multi-Column Unique Constraints in SQLite.NET

While working on my blog engine project to learn ASP.NET MVC and Entity Framework, I encountered a common scenario.  I’m using SQLite on the back-end, and I needed a unique constraint across two columns.  As demonstrated in this stackoverflow question, you can specify a unique table constraint immediately after the column definitions when using the CREATE TABLE command.  However, for the purpose of rapid prototyping, I prefer using SQLite.NET and the graphical Server Explorer interface in Visual Studio rather than explicit DDL to define my tables.

The problem is that the GUI-based designer is still in development and doesn’t support unique table constraints.  One solution is to create a unique index instead.  Right-click one of the row headers in the table designer and select Indexes/Keys…

Table design context menu

In the Index Editor, add a new index.  Under the Misc category, set the Unique property to True, and under the Source category, list the columns you want in the index, separated by commas.

Index editor

This works because, as stated in the SQLite documentation:

INTEGER PRIMARY KEY columns aside, both UNIQUE and PRIMARY KEY constraints are implemented by creating an index in the database (in the same way as a “CREATE UNIQUE INDEX” statement would).

Using the Index Editor to create an index with Unique = True generates a “CREATE UNIQUE INDEX” statement behind the scenes, which can be verified by using the Generate Change Script… feature.

In summary, if you’re thinking about using Server Explorer to create a multi-column unique constraint in SQLite, just go with a unique index, since that’s how it’s implemented anyway.


How to Survive Your Technical Presentation

A few weeks ago I did a presentation on F# at a local user group meeting.  It was an opportunity to learn something new, network with other developers, and improve my communication skills.  Here are some of the things I learned from the experience.

My F# presentation

Know your subject well in advance

Building slides, writing code snippets and demos, and coming up with talking points are all difficult enough, not to mention the time it takes to practice your presentation thoroughly.  Don’t complicate matters by trying to learn a new language or technology from scratch.

Make friends with the experts in the room

Get knowledgeable attendees on your side right away.  During my F# talk, I turned to them when confronted with questions I couldn’t answer, and they bailed me out a couple of times.

Know your equipment inside and out

Try to keep things as simple as possible.  The fewer tools you have to manage, the less chance something will go wrong.  Arrive early so you can test your laptop, projector, and all your software.  Make sure to have fresh batteries for your presentation remote and microphone.  If at all possible, do a dry run with the actual equipment you’ll be using during your talk.

Don’t go over the allotted time

At one point while I was talking, a meeting attendee rose from his seat and left.  It wasn’t until then that I realized I had gone several minutes too long.  The room was packed, but everyone was too polite to mention that it was time to leave.

Ironically, I had worked very hard to fill that time with content.  But the important thing to remember is that talking at a user group meeting isn’t about filling dead air with noise; it’s about teaching and sharing information.  Only say what is necessary and relevant, and then let your audience go home.

Don’t be boring

This is probably easier said than done, but if you want to hold your audience’s attention, you can’t be dull and predictable.  Own your presentation and make it unique.  Record a video of yourself speaking and make sure you don’t look like a robot, with monotonous speech and stiff movements.

Read Confessions of a Public Speaker by Scott Berkun

I didn’t have to learn all these lessons on my own, and neither do you.  In his book, Scott captures years worth of public speaking wisdom in two hundred brief and entertaining pages.  I only wish I had read it earlier.

Book Review: Professional ASP.NET MVC 2

ASP.NET MVC is part of the broader ASP.NET framework, and serves as an alternative development model to that of ASP.NET Web Forms.  It adheres to the Model-View-Controller architectural pattern, and offers developers greater control by bringing them closer to the underlying HTTP protocol.  ASP.NET MVC leaves out many of the abstractions that Web Forms uses to hide the stateless nature of HTTP, such as ViewState and events.  This is bewildering to some developers who are comfortable with the high-level, drag-and-drop nature of Web Forms, but it’s liberating to others who feel the Web Form philosophy is too restrictive, or even deceptive.

Several months ago, ASP.NET MVC 2 was released, and about a month ago, Professional ASP.NET MVC 2 hit the shelves.  Some of the authors actually work on the ASP.NET team, which gives this book an authoritative feel.  I decided to pick it up when I chose ASP.NET MVC as the core technology for my new blog engine.

The first chapter is by far the longest, and gives readers a detailed tour of a fully-functioning, nontrivial sample application called “NerdDinner”.  It introduces most of the important ASP.NET MVC concepts without being too overwhelming.  Later chapters dive into these topics in more detail.  Source code for the application is hosted at CodePlex.  You can also download this chapter for free from the publisher’s Web site, or view the app running live.

In Chapter 7, “AJAX”, I was surprised to find the considerable attention paid to disabled-scripting scenarios.  At first I was concerned about spending too much time supporting a very small percentage of users, but as it turns out, ASP.NET MVC lightens the load a lot with IsAjaxRequest().

Chapter 9, which covers security, has some interesting stories and anecdotes, and I’m sure it will be a great reference when I’m making a security checklist for my own apps.  After Chapter 1 (NerdDinner), Chapter 9 is the most important chapter in the book — don’t skip it!

I was impressed with a side note in Chapter 10 that addressed a pet peeve of mine.  There is a code snippet that uses string concatenation with the + operator, rather than using a StringBuilder.  The snippet is correct, but the authors went out of their way to explain to inexperienced developers why StringBuilder should not have been used.  This is nothing new; Google developer Jon Skeet wrote an article about it years ago, but I still die a little inside every time I see code like this:

StringBuilder sb = new StringBuilder();

sb.Append("SELECT * ");
sb.Append("FROM customer ");
sb.Append("LEFT JOIN order ");
sb.Append("ON (customer.customer_id = order.customer_id ");
sb.Append("AND YEAR(order.order_date) = 2010) ");
sb.Append("WHERE order.order_id IS NULL");

string s = sb.ToString();

Chapter 11, “Testable Design Patterns”, wants desperately to promote testability, but is afraid to offend anyone by doing so.  The entire first page is an apology to developers for recommending that they test their code.  Everybody has an opinion — if you think people should be writing testable apps, just come out and say it.

Chapter 12 discusses using Web Forms and MVC together.  With the exception of master pages, I didn’t find it particularly useful.  I’m sure there are teams out there that want to migrate an existing app from Web Forms to MVC one piece at a time, but not me.  The more I read about MVC, the more Web Forms feels like Web development with training wheels.

Throughout the text, the authors mentioned several tools they thought were useful, such as:

  • AntiXSS provides a myriad of encoding functions for user input, including HTML, HTML attributes, XML, CSS and JavaScript.
  • WaitiN is developed in C# and aims to bring you an easy way to automate your tests with Internet Explorer and FireFox using .Net.
  • moq is designed to be a very practical, unobtrusive and straight-forward way to quickly setup dependencies for your tests.

There are some errata in the book.  Some are minor technical errors, while others are typos or simple grammatical problems.  Several appear to be cut-and-paste mistakes that occurred in sections that were updated from MVC 1.0 to MVC 2.  In total, I found about 30 non-verified errata.  I’ve submitted a few to the Wrox Web site, and I’ll be uploading the rest soon.

Overall, Professional ASP.NET MVC 2 is a thorough and engaging book on an alternative Web development framework that’s full of controversy.  It’s not afraid to step outside the boundaries of library functions and markup syntax, and explores many of the related tools and techniques needed to get the most out of the MVC approach.  I recommend it.

Quplo’s “Redesign a Website” Feature

Last month I evaluated a preview of a new Web prototyping tool called Quplo and wrote about it here.  Since that time, Quplo beta has gone live, and the development team has added several new features and bug fixes.  Perhaps the most significant of these is a feature they call “redesign a website”, which imports external HTML from a URL, cleans it, and creates a new sheet.  This gives designers an alternative way to create prototypes, and comes in handy when clients have an existing Website they want tweaked.

To test Quplo’s redesign capability, I chose the GNU Compiler Collection Web site, because it’s simple and contains mostly text.  Importing the main page was quick and painless.  Quplo created a sheet called “Redesign” with the HTML.  The following message was inserted at the top:

<!-- This page contains the HTML from
 We've added a <base href=...> element to the <head> section.
 CSS and images below it will load from the original site.
 Put your custom CSS above it to mockup the site. -->

What this means is that resources like CSS files and images are not imported with the site’s markup.  They are requested from the server of the original site when you view your mockup.  Slightly further down the sheet is a comment that denotes the cutoff point for your new CSS.  Anything above that line will override the original site’s CSS.

<page url="/">
 <meta content="text/html; charset=iso-8859-1" />
 <!-- Place custom CSS above this line. -->

After the import, it’s time to start making changes.  First, I decided to alter the menu headers on the right-hand side.  Instead of a thin, dark border, I wanted a dark background color.  I added my custom CSS link above the cutoff line.

<page url="/">
 <meta content="text/html; charset=iso-8859-1" />
 <link rel="stylesheet" type="text/css" href="custom.css" />
 <!-- Place custom CSS above this line. -->

Then I added a new sheet called CustomCSS with the overriding style.

<page url="/custom.css">
td.td_title {
 border-style: none;
 color: #3366cc;
 background-color: #d2d2d9;
 font-weight: bold;

The next thing I did was add a splash of color to the News and Status sections by adding the background-color attribute to the existing inline styles.  I also changed the padding to create an even border of color around the text.

<td style="width: 50%; padding: 8px; background-color: #20b0f0;" valign="top">
<td style="width: 50%; padding: 12px; border-left: #3366cc thin solid; background-color: #f0b020;" valign="top">

Finally, I moved the GCC logo from the right-hand side to the left by removing the deprecated align attribute and adding an inline style.

<img src="img/gccegg-65.png" alt="" style="float: left;" />

Here are the final results:

GCC Site Before
GCC Site Before
GCC Prototype After
GCC Prototype After

And there you have it.  My apologies to the GCC team and anyone with any design sense whatsoever.

While not groundbreaking, Quplo’s new Web site redesign feature is a time saver that fits within their model of designing prototypes quickly.  Give it a try if you have access to the preview, or sign up today.

GCC Website content Copyright (C) Free Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110, USA.

Taking the Rob Conery Challenge

Last August, Tekpub co-founder and ex-Microsoft employee Rob Conery presented a challenge to all developers: build your own blog.  He suggested that a self-built blog engine would serve perfectly as a résumé, and that makes a lot of sense to me.

Most of the code I’ve written over the years is either:

a) proprietary, and owned by the company I was working for when I wrote it

– or –

b) embarrassing throw-away code that helped me learn some concept, but isn’t good enough for anyone else to see.

Writing a blog engine is a great idea because once it matures I can start dogfooding, migrate my existing blog to it, and build in the features I want the most.  It’s also an opportunity to practice and learn new skills.  Here are some things I haven’t tried yet, or would like to use more but haven’t had the chance:

  • Mercurial
  • TDD
  • ASP.NET MVC 2.0
  • Entity Framework 4.0
  • jQuery

Therefore, I’m announcing here and now that I’m taking the challenge.  I plan on hosting the project on so anyone can see or use it.  I’ll post updates here as I make progress.

Spotting the International Space Station

Under the right conditions, you can see the International Space Station passing overhead, even without a telescope or binoculars.  In fact, the ISS travels so fast at 17,000 mph that magnification can make it difficult to track.

Last night we stepped outside at about 10:30pm to see if we could catch a glimpse of it, and I managed to capture a photo.  I took this, hand-held, with a Nikon D60 and the Nikkor AF-S 70-300mm lens with Vibration Reduction turned on:

International Space Station

I expected to get a featureless, round blob, but you can just start to make out the form in this image.  Maybe next time we’ll try getting some pictures with the telescope.

If you want to see the ISS, you need to look up the predicted times for visible passes in your area.  I use, but some people prefer Heavens-Above.  The station travels roughly west to east, and looks like a steady, fast-moving star.

It’s pretty cool to look up at an object in the sky without using any special equipment and realize that there are probably astronauts on board.

Goodbye Rochester, Hello Austin

It’s official: after careful consideration, my wife and I are leaving Rochester, New York in September and moving to Austin, Texas.

A few reasons for the move are:

  • The cost of living is lower.
  • The taxes are lower (including no state income tax).
  • There are more IT companies, and hopefully, more job opportunities for an experienced software developer.
  • Most of my family members are moving or have already moved to other states for better work.
Texas breakfast
My wife's hotel breakfast during our visit to the Austin area

In the past decade, there has been an exodus from Rochester.  Declining local businesses and clueless state lawmakers, who believe that raising taxes is the way out of our recession woes,  offer little incentive for anyone to stay.

I’ve read and heard so many great things about Austin that we decided to pay a visit in April.  Everyone was incredibly friendly, the city was easy to drive (I normally hate city driving), and the food was amazing.  Most Austinites seem proud of where they live, and I like that.

Here are some recent news articles contrasting New York State and Texas:

Barbecue, here we come.