Create magic with APE

I would like to present to you, “APE”. APE stands for “Awesome Property Extractor” and is simply a framework for extracting properties and their values from arbitrary data and turn them into strongly typed C# properties and values.

To begin with, we have created a package for Umbraco called APE.Umbraco.

What is APE.Umbraco

APE.Umbraco is a nuget package for extracting Umbraco properties and dictionary items and transform them into strongly typed C# properties that can be accessed using “dot-notation”.

APE accesses the database and retrieves all dictionary items, doctypes and their properties and data types, and builds a bunch of strongly typed classes.

These classes give you access to all the values without ever opening the Umbraco back office, just to look what a given alias or dictionary key was called. All you need to know is what doctype the property is on, and you can access all the properties, said doctype has, even those that has been inherited. You can even “dot” your way to all the doctypes, using a common static class called DocTypes, we even have a class called Dictionary!

We have created two releases of this package:

  • APE.Umbraco v1.0, this release is built for Umbraco 6 and does not support Umbraco 7, for that we have,
  • APE.Umbraco v2.0, this release is built for Umbraco 7, and does not work with Umbraco 6!

1.0 is released to support “old” sites and will not be updated unless a bug is reported. 2.0+ is the one we will build upon and add features to.

What’s in the package

The package contains the following:

APE.Umbraco.dll, this is the “magic box”. This assembly is merely an implementation of the method described in my earlier post.

This folder tree:

  • Classes
    • APE
    • Cms

The APE folder contains a FileManager.ttinclude file. This file allows us to split the generated classes into multiple files, improving readability. It will be released with each APE.* release.

The Cms folder contains two T4 templates: Umbraco.Dictionary.tt. This template, extracts all dictionary items from the database using the umbracoDbDSN connection string and generates a class containing all dictionary keys and a class for getting dictionary values using said keys. The other file is Umbraco.DocTypes.tt. It uses the same connection string to extract all document types and their properties, maps the Umbraco data into C# classes and properties into a class pattern like the one I described in my previous post, “Umbraco and .NET magic”.

The future

In the future we will have an APE.UCommerce package and many other APE packages. You can help us making APE the best alternative to Code First on platforms not build to support Code first, like Umbraco and uCommerce. A long with the release of APE.Umbraco, we are making the whole deal open source on GitHub:https://github.com/LAITDK/AwesomePropertyExtractor. This is also the place to find documentation and code examples, post bug reports and make pull requests.

It’s awesome! Where can I get it?!

As this package is intended for developers it is only released through NuGet. There will not be an Umbraco Package. It just makes no sense to create an Umbraco package for something that needs Visual Studio to work, (I don’t know if it works in WebMatrix).

To get the package, look no further. https://www.nuget.org/packages/APE.Umbraco/

To see examples on what APE can do, take a look at the wiki: https://github.com/LAITDK/AwesomePropertyExtractor/wiki

Hope you like the package and will help making this the goto package for any Umbraco project.

Database design on user defined properties

As a developer, I often get across some database designs, that are quite complex caused by a developer not quite understanding the problem (we’ve all been there!), and therefore cannot solve it correctly.

The problem is as follows (or similar):

You have 2+ tables:

No references

Initial tables – no references

It’s quite simple, you have two types of data (media and documents), that you want to store in your database. I get that, I would too.

Now the requirements are as follows:

Both media and document has a set of user defined properties.

Said properties must store the following values: Type and Value, and a reference to both Media and Document.

There are a couple of ways to solve this lil’ problem, one (the one I encounter the most):

References(1) - Database Inheritance

Property-table added and References are added to media and document.

In this setup the Property-table knows about both media and document. We could make the two foreign keys nullable, either way we depend heavily on our code to keep media and document properties separated. And what happens if we add an other type (say Users), then we have to add a new foreign key to the property-table, and expand our code even more.

An other approach is this:

<img class="size-full wp-image-762" alt="References(2) – Database Inheritance" src="http://ndesoft.dk/wp-content/uploads/2013/04/media_document_properties_take2 cialis overnight shipping.png” width=”404″ height=”444″ srcset=”http://ndesoft.dk/wp-content/uploads/2013/04/media_document_properties_take2.png 404w, http://ndesoft.dk/wp-content/uploads/2013/04/media_document_properties_take2-272×300.png 272w” sizes=”(max-width: 404px) 100vw, 404px” />

Media and document property references are stored in separate tables.

I must admit, I have done this one as well as the other one, and just admit it, so have you at some point!

So what are the pro’s and con’s of this setup: Well the pros are simple, neither media or document are referenced in the property table, we can have as many properties as we want per media and document, and we can quite simple add other types, such as Users. BUT:

When we have this setup, we must rely heavily on our code to help us not to have the same property on more than one media, and to ensure we don’t mix media properties with documents and users. And if we add an other type (Users) we must create, not only one, but two new tables, and still expand a complex code to handle that new type as well as the other types.

So how can we solve this problem?

We have Media, Documents and more types, that has dynamic  properties without the other types must know about it, we could do this:

References(3) – Database Inheritance

Each type now has its own set of properties

Yeah, I’ve also done this one. And this is almost, (I wrote, almost), as bad as the other ones. Well no property can be on more than one media (or document, or whatever), and no property can be on both media and document, so whats the problem?!

Well, for starters, we have to tables instead of one, per type. If we add an other field to our properties, we must add them to all of our *Property-tables. And if we want to list all properties, including the media/document/user/whatever it is attached to, it’s nearly impossible.

So here’s the solution, I find most fitting for the problem:

References, Inheritance – Database Inheritance

Added a Node-table, with the shared fields from Media and Document. Removed ID- and Name-fields from Media and Document, added a NodeID field, as both PK and FK. Added a Property-table, that references the Node-table.

So, this is my solution. I have added a Node-table, with the shared fields from Media and Document (ID and Name). Removed ID- and Name-fields from Media and Document, added a NodeID field, as both primary key and foreign key, this field must NOT be autoincremented! It will not work, then I added a Property-table, that references the Node-table.

The pros and cons: The pros are easy, One table per type, each type gets its ID from the Node-table, all properties are stored in one table, referencing the Node-table, so a Document can get its properties, using only its primary key. No property can ever be on two entities at once, and no entity knows about other entities or properties, except its own.

The cons are, that we must have some code that handles the inheritance. When I make a SELECT * FROM Media, I must make a JOIN on the Node-table as well. If you’re a .NET developer, like I, then you should take a look at the Entity Framework, as it handles this smoothly. I will write a post on that later on.