Working with SQLite and Visual Studio 2013

SQLite is nice project to store low amount of data in applications. Babel windows desktop project uses it to store client data on client computer. SQLite is lightweight database with excellent portability link. 

Advantage of this solution is that we have SQL available. Selection, projection, insertions, transactions etc. are ready to use. Of course there is gap between objects and relational database. SQLite is relational so we need to use some ORM framework which provides an easy access to database from code. Babel windows desktop client uses sqlite-net. This library allows Code-First approach.

Code-First creates database schema by defining model as classes in code, which will be used as entities. Standard data anotations on properties in these entity classes can define constraints for columns:

public class Stock
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    [MaxLength(8)]
    public string Symbol { get; set; }
}

When there is database, there is also need to solve migrations. If we release new version of program, we also might need to update database schema. Sqllite-net ORM doesn't provide migrations. This must be done by our code. We use SQLite Expert To write and debug migration SQL script. It is Free for personal and commercial use! This standalone program is not integrated to visual studio and allows us to see data in database, export DDL for table, run custom SQL commands on live database. This great tool miss just one functionality, but very important to write context for unit test especially for testing database schema migrations. This function is to export insert scripts for data, which ale already in database.

For that we use Visual Studio extension SQLite Toolbox. This extension will adds panel, where we can connect to database and use its functions. To export mentioned insert scripts navigate to SQL Server Compact Toolbx panel -> Right click on database connection -> Script Database -> Script database data -> check tables which are relevant -> select file to save SQL script

Warning!

SQLite Toolbox generates wrong names for named constraints and indexes in DDL scripts. So be careful using this export. Next DDL exports are from same database

SQLite Toolbox export:

CREATE INDEX [Address_ContactId_Address] ON [Address] ([ContactId] ASC);

SQLite Expert export:

CREATE INDEX "Address_ContactId" on "Address"("ContactId");

XMPP in .NET

Project Babel is based on XMPP protocol, so there is need for suitable comunication layer. Our solution contains two projects, which are participated in this comunication.

First project is classic windows desktop project, which has pretty much everything, what we wish for.

Second project is PCL (Portable class library) which can be compiled for silverlight, windows phone and even Windows RT. The bigger part of entire solution logic is situated in this PCL project, the better for us developers, because we can simply use this implementation on more platforms.

First approach to build this communication layer was to use some third party library, which can be added to PCL. There is one! A MatriX XMPP SDK. This is very nice library, which comes with binaries, that are compiled for all mentioned platforms and it even supports Xamarin. Everything is cool until it comes to a pricing. It is quite expensive one. So we tried to find another way.

A person with nick smiley22 wrote an opensource solution for XMPP protocol. It is called a S22.Xmpp. There are source codes on github so it looks pretty well. Last commit to this project is 27 Dec 2013 so it is more than year old. It can be both a very good sign of stable library which has implemented all features of XMPP and a bad sign of dead unfinished project without community. 

S22.Xmpp miss anonymous authentication and it has dependencies on classes, that are not available on PCL project, so we need to split implementations for platforms. Babel extends this S22 implementation with anonymous authentication and moves this logic from PCL to other platform dependent project.

For windows phone implementation we can use matrix library, which allows us to buy license for specific platform. Another way is to write our own implementation that compiles on windows phone.