For our Team Trivia Game Management application I needed a local database to store data to fulfill my requirement of being able to support the application regardless of connectivity. I looked at a variety of database systems but with the additional requirement that the app run on a Mac my options were limited.
I had heard about Jeremy Likness’s Sterling project a year ago or so and always fascinating. I have actually, myself, built a similar conceptual idea using Isolated Storage on Windows Phone 7. However, Jeremy’s project takes into account and handles many more of the complicated scenarios that a basic wrapper would not. And because it essentially functions as a file system wrapper to give the impression of a database it is platform independent and I felt fulfilled my needs nicely.
Building the Engine
Sterling operates with the notion of Engine. The Engine is the driving force behind your database and is responsible for creating the database. Ideally we would like the Engine to be created automatically once our application starts. This will remove the need for specialized code. In .NET we can use a Lifetime object to carry out this. Lifetime objects are creating by having a class implement the following interfaces which allow it to interact at different points during runtime:
Implementing these two interfaces will allow you to carry out operations at different points during the operation. For our purposes we only care about Starting and Exiting. To get the application to actually care about this class you need to specify it as a Lifetime object. The best way to do this is to add it to App.xaml. The XAML code to do this is shown below:
Composing the Database
Within the Engine, you must register your database and build up the table definitions. In Sterling there is no SQL, the tables are built from classes which represent the rows contained within the table. This being said, before you can define your database, first compose your entities. For my application, we are starting with two entities: Team and Venue. The code is shown below:
Once you have your entities, you define a custom class representing your database which inherits from BaseDatabaseInstance. The class representing my database is shown below:
When you inherit from this class, you are required to implement the abstract method RegisterTables. In this class, as you can see above, we specify our class names through the CreateTableDefinition call. The second generic type specifies the type of the key. Sterling doesn’t support super complex keys, nor do I think you would want them.
With this code in place we can safely know that our database will be saved and loaded from the disk when the application starts. Our next step is deciding how we will access our data.
Creating the Repositories
I am a big fan of the repository pattern. I think it lends itself very well to modern application design where objects are used heavily to represent rows in databases. Using objects gives you a number of advantages, such as dirty tracking and abstraction. This allows finite control over how Entities are constructed and operate.
When I implement the pattern I like to create a RepositoryBase abstract class to contain the common used methods for my repositories. This might include a general save method which looks at the items in the repository, determines which are dirty, saves the items, and resets the dirty flag.
Next, because I am a huge fan of Dependency Injection as a means to stitch together the data layer of application while continuing to maintain proper decoupling between classes while enforcing “design by contract” principles and making testing easier. To facilitate this I usually create the IRepository interface which defines the common methods for all repositories. The code is below:
This gives us an adequate template to build all repositories, which allowing RepositoryBase gives all the support we need to interact with the database. I decided to use Ninject for Dependency Injection. To make the binding easier I created the IVenueRepository interface which inherits from IRepository and provides Venue specific methods.
Interacting with the Data Layer
In a simple case for interaction, I might call on the Repository directly:
InjectionKernel is a property I defined in ChildWindowBase which refers to my Ninject Injection container. Calling Get specifying the interface I want gives me the concrete class mapped to the interface via the Ninject Module.
For more complicated operations, I might define a service which can encapsulate the interactions between repositories. For simpler pages, you might refer to the repository directly, as shown above.
This data layer is not totally complete and contains a number of things I would like to improve upon. For example, we can utilize Sterling Triggers to support an AutoNumber feature for keys. The problem with this is shown below:
Within the Engine the RegisterDatabase call returns our ISterlingDatabaseInstance which is what we will use to save/load data. The problem is, the IdentityTrigger requires this database instance so that it can determine the starting index for new instances. I personally hate seeing code where an object method is called and the same object is passed in the same call. I feel that DI can solve this, but I ran into problems with double initialization of the database.
I also still have to develop the services layer which allows the encapsulation of operations involving multiple repositories.