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:
- IApplicationLifetimeAware
- IApplicationService
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:
1: public class Team : EntityBase, ISimpleEntity
2: {
3: public int TeamId { get; set; }
4: public string TeamName { get; set; }
5: public string LeagueId { get; set; }
6:
7: #region Implementation of ISimpleEntity
8:
9: public int Id
10: {
11: get { return TeamId; }
12: set { TeamId = value; }
13: }
14:
15: #endregion
16: }
1: public class Venue : EntityBase, ISimpleEntity
2: {
3: public int VenueId { get; set; }
4: public string VenueName { get; set; }
5: public string Address { get; set; }
6: public string City { get; set; }
7: public string State { get; set; }
8: public string ZipCode { get; set; }
9: public bool Active { get; set; }
10:
11: #region Implementation of ISimpleEntity
12:
13: public int Id { get; set; }
14:
15: #endregion
16: }
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:
1: public class TeamTriviaDatabase : BaseDatabaseInstance
2: {
3: #region Overrides of BaseDatabaseInstance
4:
5: public override string Name
6: {
7: get { return "TeamTrivia"; }
8: }
9:
10: protected override List RegisterTables()
11: {
12: return new List()
13: {
14: CreateTableDefinition<Venue, int>(m => m.VenueId),
15: CreateTableDefinition<Team, int>(m => m.TeamId)
16: };
17: }
18:
19: #endregion
20: }
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:
1: public interface IRepository where T : EntityBase
2: {
3: ///
4: /// Add an item to the repository
5: ///
6: /// The item to add to the repository
7: void Add(T item);
8:
9: ///
10: /// Get an item based on a predicate condition
11: ///
12: /// The condition to find the item with
13: /// An instance of T from the Repository or null
14: T Get(Func<T, bool> predicate);
15:
16: ///
17: /// Return a list of all items within the Repository
18: ///
19: ///
20: IList GetAll();
21:
22: ///
23: /// Save dirty items in the repository
24: ///
25: void Save();
26:
27: ///
28: /// Marks all instances within the Repository as Deleted
29: ///
30: void Clear();
31: }
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:
1: public partial class VenueSelect : ChildWindowBase
2: {
3: public VenueSelect()
4: {
5: InitializeComponent();
6: }
7:
8: private void OKButton_Click(object sender, RoutedEventArgs e)
9: {
10: var repository = InjectionKernel.Get();
11: repository.Add(new Venue());
12: }
13:
14: private void CancelButton_Click(object sender, RoutedEventArgs e)
15: {
16: var repository = InjectionKernel.Get();
17: repository.Clear();
18: }
19: }
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.
Future Plans
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:
1: ///
2: /// Called by an application immediately before the event occurs.
3: ///
4: public void Starting()
5: {
6: Activate();
7: Database = SterlingDatabase.RegisterDatabase();
8:
9: // Register the Triggers for the Database
10: Database.RegisterTrigger(new IdentityTrigger(Database));
11: Database.RegisterTrigger(new IdentityTrigger(Database));
12: }
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.
Random Blurbs and Things of this Nature 