About the app
MobiDB Database Designer is an application for creation relational databases. It is designed for business and for everyday use, supports synchronization and multi-user access. It allows to store different data types. It is a good fit for planning, project management, invoices, delivery management, inventory, keeping records of patients, etc.
Project idea
Project idea was to create a database designer with tables and the corresponding views/edit forms for touch devices. Database creation ought to be convenient and easy. As a result, it was decided to create database based on form. I.e. data entry form is designed, and a database structure and cards for data entry are designed based on this form. Adding/deleting of a control results in adding/deleting of the database column.
Selecting technology
The project was initially planned to be shipped to several mobile platforms (starting from and Android, we then planned to release a version for Windows Store). Writing native code meant writing code for all platforms from scratch. Cross-platform solutions with UI sharing were not desirable. We wanted to make the app “native” for every platform. Having experience in .NET development and having made a research of all possible cross-platform solutions, we chose Xamarin. It allowed writing shared code and different native UI for each platform, and it was exactly what we wanted. We regretted several times during development that we haven’t chosen native way of app building until we started implementing a windows store version. This article won’t describe how to deal with Xamarin, but will cover architectural concerns.
We started development from implementing data model, then we implemented data layer and lastly UI part. UI development won’t be covered in this article.
A database should be stored somewhere, client should work off-line and support all popular platforms. Considering that we couldn’t find a better solution than using SQLite.
Model
Our database implied availability of top-level data types, i.e. not only numeric and string, but also currency, email, rating, link, location, etc. In general this is not a problem: It is a common practice for many ORM-s to create model classes and map them to a database.
Java has Hibernate, Spring persistence; .Net has Entity Framework, Nhibernate. But we have to remember that we are talking about database editor and a user can change a model. So, table mapping and table adapters (as in .net dataset) didn’t met our requirements. Thus, we experienced data storage organizing dilemma. In our terms, “table scheme” supporting dynamic model is an analog to mapping.
Further, SQLite has a very limited set of types:
- TEXT
- NUMERIC
- INTEGER
- REAL
- BLOB
It is necessary to store data in this limited set somehow. We were making decisions based on the fact that currency field will have one and the same currency symbol, rating will have the same step and maximum for all rows.
If you store type description together with the value, there will be data redundancy, since such data will be equal for all rows. It is necessary to move this information to a separate entity. Furthermore, not all our types correspond to SQLite types. For example, Location field represents two numbers: latitude and longitude. Of course, we could store them in distinct columns, but it doesn’t make sense, since this is undividable information and cannot be interpreted separately. That is why it is necessary to convert from SQLite data types to model types. This entity was called MetaType; and model data was called MetaValue.
MetaType is responsible for conversion of SQLite data types to model types, and for storing column specific settings.
RatingMetaType class diagram is shown below as an example of the MetaType:
TimeMetaType implementation sample can be found below:
{
public override string ConvertToString(MetaValue value)
{
var timeValue = value as MetaValue<DateTime>;
if (timeValue == null)
{
throw new ArgumentException("MetaValue<long> was expected");
}
return string.Format("{0:t}", timeValue.Value);
}
public override ElementaryType GetElementaryType()
{
return ElementaryType.String;
}
public override MetaValue LoadFromElementaryType(object value)
{
var dateValue = DateTime.Parse((string)value);
return new MetaValue<DateTime>(this, new DateTime(1, 1, 1, dateValue.Hour, dateValue.Minute, dateValue.Second));
}
public override object SaveToElementaryType(MetaValue value)
{
var concreteValue = value as MetaValue<DateTime>;
if (concreteValue == null)
{
throw new ArgumentException("MetaValue<DateTime> was expected");
}
return concreteValue.Value.ToString("HH:mm:ss");
}
public override bool TryParse(string stringValue, out MetaValue value)
{
DateTime result;
value = null;
if (!DateTime.TryParse(stringValue, out result))
{
return false;
}
value = new MetaValue<DateTime>(this, result);
return true;
}
}
Every database in our application corresponds to a SQLite database. Data is stored in SQLite tables. Remember we were talking about a dynamic model that should also be stored in the database. It is stored in a row of a special system table. We named this entity a Template. The Template is able to save to binary data / load from binary data. It describes a set of table schemas, schemas describe a collection of columns and a column has a metatype.
Conversion of a template to byte[] is done by means of serialization and attributing classes/properties with custom attributes. We use our own xml- serializer. Of course, we could use a built-in .NET serializer.
So, we are done with metadata, let’s proceed to data itself. A table stores a collection of rows, a row stores values. Values are stored as MetaValue. You can get metatype from MetaValue and determine what metatype it is. A table has a scheme. This relation turns our raw data to more high-level data. They form a model in conjunction. Reading columns’ metadata we could iterate dataRow values.
Now we’re done with data and metadata, and look back to the idea that we wanted to create database structure based on forms. Since that scheme also stores editing form with controls:
Note that there is also a nested table form control. ParentId in a Table and DataRow instances are intended for dealing with a nested table.
As for parent table relation metatype, it is defined by table name and column name. RelationMetaType is able to get description of a related row and a related value. A related record is defined by RowId which is stored in MetaValue. In this way, knowing table name from RelationMetaType, we can get row from the related table by RowId and get access to all values of the related row.
Data Layer
As of the project start stage, there was no sufficient PCL support, and that is why we used Mono.android library. We rewrote datalayer as soon as SQLite PCL library appeared:
https://sqlitepcl.codeplex.com/
SQLite PCL has quite raw low level API. For more info visit the following link:
https://marcominerva.wordpress.com/2014/02/13/the-new-portable-class-library-for-sqlite/
We created a special IDatabaseManager abstraction for business logic. In our case, IDatabaseManager encapsulates data layer logic and gets / accepts model entities as input / output parameters:
Since data layer logic is not that sophisticated and too much code is involved, we won’t demonstrate SqliteDatabaseManager implementation which is based on SQLite PCL.
As a result, we got quite a powerful app where a user can create complex data structures according to his or her purposes. You can see the result by downloading the app from the stores:
Google Play:
https://play.google.com/store/apps/details?id=com.perpetuumsoft.mobidb.lite
Windows Store:
https://www.microsoft.com/store/apps/9nblggh1jp5v
