Sterling is an object-oriented database that I created to facilitate ease of serialization of data in both Silverlight 4 (soon 5) and Windows Phone 7 projects. Sterling was designed with a few goals in mind. It is not a true relational database and not intended to replace one. I’m confident certain offerings such as SQLite or even a trimmed down version of SQL Server will make their way to the phone eventually, backed by experienced teams and highly optimized code.
Instead, I set out with a few fundamental goals:
- Keep it as lightweight as possible — no one wants to add a huge DLL just to facilitate some functionality
- Keep it extensible and flexible to address as many needs as possible
- Take advantage of LINQ to Objects to provide keys and indexes for ultra-fast querying of data and lazy-deserialization from isolated storage
- Keep it non-intrusive — users should not have to modify their existing types to use the database system, such as inheriting from a base class
I believe I’ve successfully addressed those needs and Sterling is quickly being adopted in major projects. The community has contributed some excellent suggestions and even modifications to allow for a very rich feature set. Sterling currently supports:
- Out-of-the-box support for most native value types
- Automatic enum support
- Tuple and Lazy support on the phone
- Support for Nullable<T>
- Handles ICollection, IList, and IDictionary
- Supports base classes (will serialize/deserialize the derived types)
- Provides keys and indexes (covered indexes means in-memory querying)
- Lazy loading of serialized values
- Multiple database support (for versioning or partitioning of data in complex applications)
- Custom serialization for types not supported out of the box
- Custom logging of database events
- In spite of all of these features, Sterling still holds it own for speed and because it uses a binary format, is quite compact on disk
This post is intended to introduce you to Sterling and also to provide guidance for using it on the Windows Phone 7. I am not supplying a full project as this is being built for the full 1.0 release, but I’ve received numerous requests for clarification and guidance on the phone, so this post is intended to fill that gap until the full Sterling documentation is released.
Download Sterling
Your first step will be to download Sterling. As of this post, you’ll want to grab the latest source as there have been numerous bug fixes and optimizations that were not in the latest formal release. These will be integrated into the 1.0 RTM which is slated for early 2011. You can visit this link and choose “Download” in the box to the upper right that indicates “Latest Version.”
Build Sterling
Navigate to the SterlingSln directory and choose the WindowsPhoneSterlingSln.sln file to open the source code for Windows Phone 7. It is up to you whether you want to build the project and include Wintellect.Sterling.WindowsPhone.dll in your project, or simply use a project reference. As of this blog post, the DLL weighs in at a light 72 kilobytes on disk.
Sterling Under the Covers
Before you wire Sterling into your Windows Phone 7 application, it helps to understand how it works under the covers. Below is a diagram of the rough directory structure that Sterling uses to serialize data:
Sterling creates an aptly named Sterling folder at the root of isolated storage. It contains a file called db.data which simply maps database names to folders that are named sequentially. This shortens the folder length but also helps to avoid collisions with type names in the folder structure and/or name.
The first database receives a subfolder named 0. The root of that folder contains a tables.dat file that maps type names for classes that Sterling is using to another set of subfolders, one for each type. The first class to be referenced for the database will receive a subfolder 0 as well.
To put things in perspective, if you have a database named “Contacts” and a type called “ContactEntity” then the path to the data for the ContactEntity is Sterling where 0 represents the first database and 0 represents the first type. A second database will reside at Sterling1 and a second type in the first database will reside at Sterling1.
Within the type folder, there are several files. There is always a keys.dat file that maps key values to ordinals. For example, if you have a key that is a GUID, this file will map the GUID value to “0” for the first record, “1” for the second record, etc. Each row in the table is stored as a separate file for fast access. If you defined any indexes for the table, the indexes are mapped to keys in a file named {indexname}.dat.
Sterling serializes the rows as soon as they are saved. For efficiency, Sterling does not flush the keys each time. Imagine saving 1,000 records. If Sterling serialized the keys each time, it would have to serialize a 1 row record, then overwrite that with a 2 row record, etc. until the 1,000 rows were written. Instead, Sterling will wait until you call the Flush method before writing the keys and indexes to disk.
As a best practice, you should flush after serializing types. If you are saving or updating a single type, flush after the save. If you are performing a bulk operation, wait unti
l after all of the types are serialized and then call flush. Doing so ensures the integrity of the database. Sterling tracks a “dirty flag” on the keys and indexes, and when the application tombstones, it will flush any keys or indexes that haven’t already been saved. You will speed the tombstone process if the keys/indexes have already been flushed. No flush is required after queries or other read-only operations.
Creating a Sterling Database
Once you are ready to begin using Sterling, you will need to define a database. Typically each application will have a single database that supports multiple types. Sterling provides the facility to use multiple databases for ease of versioning and for data partitioning. A database is simply a collection of types that will be persisted.
A Sterling database inherits from the BaseDatabaseInstance. There are two required overrides. The first defines the name of the database and should be a unique name within your application. The second defines what types the database will support. To define a type, simply use the base CreateTableDefinition. You must supply the type that will be serialized, along with the type of the unique key. Keys can be any value supported by Sterling. Keys that are not directly supported, or are based on complex classes, must have a serializer defined (this will be discussed later). For now, assume a simple integer key. The base method takes one parameter, and that is a lambda expression that instructs Sterling how to get the key value for a given type.
Here is an item view model, taken and modified from the default Windows Phone 7 project:
public class ItemViewModel : INotifyPropertyChanged
{
private int _id;
public int Id
{
get { return _id; }
set
{
if (value != _id)
{
_id = value;
NotifyPropertyChanged("Id");
}
}
}
private string _lineOne;
public string LineOne
{
get
{
return _lineOne;
}
set
{
if (value != _lineOne)
{
_lineOne = value;
NotifyPropertyChanged("LineOne");
}
}
}
private string _lineTwo;
public string LineTwo
{
get
{
return _lineTwo;
}
set
{
if (value != _lineTwo)
{
_lineTwo = value;
NotifyPropertyChanged("LineTwo");
}
}
}
private string _lineThree;
public string LineThree
{
get
{
return _lineThree;
}
set
{
if (value != _lineThree)
{
_lineThree = value;
NotifyPropertyChanged("LineThree");
}
}
}
public event PropertyChangedEventHandler PropertyChanged;
private void NotifyPropertyChanged(String propertyName)
{
PropertyChangedEventHandler handler = PropertyChanged;
if (null != handler)
{
handler(this, new PropertyChangedEventArgs(propertyName));
}
}
}
To notify Sterling about this type is as simple as the following code, which defines a database to store the item:
public class ItemDatabase : BaseDatabaseInstance
{
public override string Name
{
get { return "ItemDatabase"; }
}
protected override System.Collections.Generic.List<ITableDefinition> _RegisterTables()
{
return new System.Collections.Generic.List<ITableDefinition>
{
CreateTableDefinition<ItemViewModel,int>(i=>i.Id)
};
}
}
Supporting other types is as simple as adding a comma after the CreateTableDefinition call and using the same method to register another type.
Adding Indexes
Sterling allows the definition of indexes as well. Indexes are stored in memory, so you must take care when deciding what indexes will be used – the trade-off is memory vs. the speed of serialization/de-serialization. Indexes are intended to provide fast in-memory lookup of data. Once the data is filtered and selected, the full type can be de-serialized from disk. This lazy loading ensures that queries and look ups are fast and efficient.
To add an index to a type registration, use the WithIndex extension method. Indexes always include the key. The format for an index is {classType,indexType,keyType}. Sterling supports indexes with one or two indexed properties (technically, anything you can access and serialize with a lambda expression is available as an index). Here is an example index using the LineOne property of the ItemViewModel. It can be referred to anywhere in code using the “LineOneIndex” name:
return new System.Collections.Generic.List<ITableDefinition>
{
CreateTableDefinition<ItemViewModel,int>(i=>i.Id)
.WithIndex<ItemViewModel,string,int>("LineOneIndex",i=>i.LineOne)
};
Setting up Sterling for Tombstoning
Now that you’ve defined a database, some types and an index, it’s time to integrate the database with your Windows Phone 7 application. The majority of integration will happen in the App.xaml.cs to allow for hooking into the phone tombstone events.
First, add references to the top of the class for the components that are used by the Sterling database engine:
private static ISterlingDatabaseInstance _database = null; private static SterlingEngine _engine = null; private static SterlingDefaultLogger _logger = null;
It is also suggested you expose a static property to make it easy to reference the database from anywhere within your application:
public static ISterlingDatabaseInstance Database
{
get
{
return _database;
}
}
Next, create two methods. The first is designed to activate the database when the application is first launched, or when the phone wakes up from a tombstone event. The second will deactivate the engine when the application is exited or tombstoned.
private void _ActivateEngine()
{
_engine = new SterlingEngine();
_logger = new SterlingDefaultLogger(SterlingLogLevel.Information);
_engine.Activate();
_database = _engine.SterlingDatabase.RegisterDatabase<ItemDatabase>();
}
private void _DeactivateEngine()
{
_logger.Detach();
_engine.Dispose();
_database = null;
_engine = null;
}
Notice the log level. This example uses the built-in Sterling logger, which simply dumps output to the debug window. Refer to the documentation for instructions on how to write our own logger and register it with the Sterling engine.
Now you can hook these events into the App.xaml.cs code behind using the phone lifecycle events:
private void Application_Launching(object sender, LaunchingEventArgs e)
{
_ActivateEngine();
}
private void Application_Activated(object sender, ActivatedEventArgs e)
{
_ActivateEngine();
if (!App.ViewModel.IsDataLoaded)
{
App.ViewModel.LoadData();
}
}
private void Application_Deactivated(object sender, DeactivatedEventArgs e)
{
_DeactivateEngine();
}
private void Application_Closing(object sender, ClosingEventArgs e)
{
_DeactivateEngine();
}
That’s all that is required to have the engine ready and waiting for your application.
Example Sterling Operations
Saving Objects
Saving objects in Sterling is straightforward. As long as the type has been defined, you simply call the Save method on the database instance. New records will be automatically inserted, and existing records will be automatically updated. In this example, sampleData contains a list of ItemViewModel. The code snippet iterates the list, setting a unique identifier, and saves them. Note the flush for the keys that is called after the individual items are saved.
foreach (var item in sampleData)
{
idx++;
item.Id = idx;
App.Database.Save(item);
}
App.Database.Flush();
Direct Loading
To directly load an instance, simply pass the type and the key. For example, this code will load the item with an id of 2:
var itemViewModel = App.Database.Load<ItemViewModel>(2);
Querying Keys
The phone does not directly support the IQueryable interface. Keys on the phone are stored in memory and exposed via an IEnumerable interface. To avoid corruption of the keys, you cannot directly manipulate the keys. For filtering and sorting, you should first cast the keys to a list, like this:
var keyList = from key in App.Database.Query<ItemViewModel, int>().ToList()
where key.Key > 5
select key;
Note the syntax: the query method is passed the type of the class and the type of key. After casting to a list, the resulting dataset can be queried, sequenced, or any other operation performed that is supported by LINQ to Objects on the Windows Phone 7.
The key automatically provides a lazy-loaded reference to the entire class. The following query is similar to the previous example, but will lazily de-serialize the actual class instance instead of supplying a list of keys:
var instanceList = from key in App.Database.Query<ItemViewModel, int>().ToList()
where key.Key > 5
select key.LazyValue.Value;
Using Indexes
Using indexes is very similar to using keys. You must specify the type of the index as well as the name of the index. In this example, the index is used to provide a list of identifiers for the item view models, sorted in the order of the text in the LineOne property:
var sortedByLineOne = from index in App.Database.Query<ItemViewModel, string, int>("LineOneIndex").ToList()
orderby index.Index
select index.Key;
Complex Queries
Sterling supports any type of query that is supported by LINQ to Objects. In the online documentation, there is an example query that joins two different indexes and creates a new anonymous type with the merged values – this is how it would look on the phone:
return from n in CurrentFoodDescription.Nutrients
join nd in
SterlingService.Current.Database.Query<NutrientDefinition, string, string, int>(
FoodDatabase.NUTR_DEFINITION_UNITS_DESC).ToList()
on n.NutrientDefinitionId equals nd.Key
join nd2 in
SterlingService.Current.Database.Query<NutrientDefinition, int, int>(
FoodDatabase.NUTR_DEFINITION_SORT).ToList()
on nd.Key equals nd2.Key
orderby nd2.Index
select new NutrientDescription
{
Amount = n.AmountPerHundredGrams,
Description = nd.Index.Item2,
UnitOfMeasure = nd.Index.Item1
};
Custom Serialization with Sterling
The last thing to cover with Sterling is custom serialization. Sterling has been updated to support as many types “out of the box” as possible. Support for null values, enums, base types, etc. has been baked in. Version 1.0 will also support complex object graphs as long as the nested properties are able to be serialized. Inevitably, whether because you need to manipulate the data or because you have a specific type not supported, you may need to provide a custom serializer. With Sterling this requires only two steps.
The first step is to inherit from the BaseSerializer class. One custom serializer can handle multiple types, and must implement a method to indicate which types are supported. In this example, the serializer supports a custom struct:
public class FoodSerializer : BaseSerializer
{
public override bool CanSerialize(Type targetType)
{
return targetType.Equals(typeof (NutrientDataElement));
}
public override void Serialize(object target, BinaryWriter writer)
{
var data = (NutrientDataElement)target;
writer.Write(data.NutrientDefinitionId);
writer.Write(data.AmountPerHundredGrams);
}
public override object Deserialize(Type type, BinaryReader reader)
{
return new NutrientDataElement
{
NutrientDefinitionId = reader.ReadInt32(),
AmountPerHundredGrams = reader.ReadDouble()
};
}
}
Note that the serializer indicates what type it supports, then provides the steps for serializing and de-serializing those types.
The second step is to simply register the custom serializer with the database before it is activated. That step looks like this:
_engine.SterlingDatabase.RegisterSerializer<FoodSerializer>(); _engine.Activate();
Conclusion
As you can see, Sterling is flexible, lightweight, and easy to use. The community continues to drive new features and version 1.0 will release in early 2011. Consider using Sterling to help with tombstoning and persistence of data in your upcoming Windows Phone 7 project. If you have any questions, don’t hesitate to ask in our forums (that’s a great place to post your success stories as well) and be sure to log any feature requests in our Issue Tracker database. Thanks!
