Creating Umbraco Tables with Indexed Columns

I was just working on adding the ability to store submissions to Formulate. However, I was having trouble finding out how to create a database table with an indexed column using PetaPoco. Spoiler alert: it’s totally possible.

All you need to do is decorate your model with the IndexAttribute attribute:

You can then create the table based on your model in the usual way, which you can see in action here: https://github.com/rhythmagency/whodunit/blob/4f21afce54ef14d97b77af41fffa2a09fd1d2e47/src/Whodunit.app/Events/CreateTables.cs#L55

Your options for index type are: Clustered, NonClustered, and UniqueNonClustered. I have chosen the latter in this case, as I plan to have another primary key to cluster by, and these generated GUID’s should be unique (i.e., each row will have a different one).

Migration Alternative

The alternative way I was considering doing this was with a migration. Essentially, a migration in Umbraco allows you to run arbitrary code to migrate between versions of your plugin (i.e., upgrade and downgrade). That arbitrary code can include database modifications.

You can find an example of how to do this in the core of Umbraco: https://github.com/umbraco/Umbraco-CMS/blob/5397f2c53acbdeb0805e1fe39fda938f571d295a/src/Umbraco.Core/Persistence/Migrations/Upgrades/TargetVersionSevenTwoZero/AddMissingForeignKeyForContentType.cs#L65

That line is creating an index on the cmsContentType table for the icon column. Interestingly, it seems to have a few extra options that are not available with the IndexAttribute approach. For example, you can specify ascending or descending indexing (I wouldn’t expect the need for that to be very common). However, since I don’t need that robust of a solution, I’ll stick with the attribute approach in this instance.

Definitions

Here are definitions of some of the above terms in case they are unfamiliar to you:

• Formulate. A plugin for Umbraco that allows non-technical users to create forms (e.g., contact forms).
• PetaPoco. Essentially, an ORM included with Umbraco. It allows you to interact with the database in an abstract way so your code works with MySql, SQL Server CE, and SQL Server.
• Umbraco. An open source CMS (content management system) built primarily with .Net.