SQLite. Getting ready for Windows 10 (Universal App Platform)

Hello, dear All!

I want to talk about a small library for working with SQLite in Windows Phone 8.0 Silverlight, Windows Phone 8.1, Windows 8.1 and now also for Windows 10 UAP. The library is already more than a year old. problems with it for all the time did not arise, then, I believe, you can tell others about it.

Why all this?
The library as part of Windows Phone 8.0 Silverlight, Windows Phone 8.1, Windows 8.1 has existed for more than a year. The question is: why now I decided to tell about it? The fact is that now it's time to port your applications to Windows 10, but there is no official SQLite SDK yet.
Here is the SDK for Windows Phone 8 , Windows Phone 8.1 , Windows 8 and Windows 8.1 . But not for Windows 10 .
And most likely will not be until the release of Windows 10. Therefore, it makes sense to look at this library.
')
Go
The library is called SQLite.WinRT .

I am not its 100% author. I just collected many pieces of code wandering around the internet together. So if you think that I somehow violated your copyright, then let me know - we will agree.

Source codes can be viewed here .
You can install the library using NuGet. There are 5 packages:

• SQLite Library Core
• SQLite Library for Windows UAP
• SQLite Library for Windows Phone 8.0
• SQLite Library for Windows Phone 8.1
• SQLite Library for Windows 8.1

For successful compilation, you have to get rid of Any CPU and build the project separately for each platform: x86, x64, ARM.

For Windows 10 UAP applications, you need to add a link to the following SDKs:

• Microsoft Visual C ++ 14 AppLocal Runtime Package for Windows UAP

For Windows 8.1 applications, you need to add a link to the following SDKs:

• Microsoft Visual C ++ 2013 Runtime Package for Windows
• SQLite for Windows Runtime (Windows 8.1)

For Windows Phone 8.1 applications, you need to add a link to the following SDKs:

• Microsoft Visual C ++ 2013 Runtime Package for Windows Phone
• SQLite for Windows Phone 8.1

For Windows Phone 8.0 applications, you need to add a link to the following SDKs:

• SQLite for Windows Phone

How to use the library?
For example, we need the following classes:

public sealed class DatabaseContext : BaseDatabaseContext { private DatabaseContext(SQLiteConnection connection): base(connection) { } public async static Task<DatabaseContext> CreateContext() { const string dbName = "db.sqlite"; var folder = CorePlatform.Current.LocalFolder; var connectionString = new SQLiteConnectionString(Path.Combine(folder, dbName), true); var connection = SQLiteConnectionPool.Shared.GetConnection(connectionString); var ctx = new DatabaseContext(connection); await ctx.CreateSchemeAsync(); await ctx.UpdateSchemeAsync(); return ctx; } public IEntityTable<Item> Items { get { return provider.GetTable<Item>(); } } public IEntityTable<Category> Categories { get { return provider.GetTable<Category>(); } } } [Table("Items")] public class Item { [PrimaryKey, AutoIncrement] public int ItemID { get; set; } public int CategoryID { get; set; } public string Title { get; set; } } [Table("Categories")] public class Category { [PrimaryKey, AutoIncrement] public int CategoryID { get; set; } public string Name { get; set; } } 

Adding record

 var category = new Category(); category.Name = "category"; await db.Categories.InsertAsync(category); 

Record Update

 category.Name = "category2"; await db.Categories.UpdateAsync(category); 

All fields except the main key are updated.
If you really need, you can do this:

 var count = db.Categories .Update() .Set(t => t.Name).EqualTo("test name") .Where(t => t.CategoryID).IsBetweenAnd(3, 4) .Execute(); 

In this case, only what is indicated will be updated.
Delete record

 var count = db.Items.Delete() .Where(t => t.CategoryID).IsLessThanOrEqualTo(3) .And(t => t.Title).IsEqualTo("item0") .Execute(); 

or so:

 var category = ...; await db.Categories.DeleteAsync(category); 

Data retrieval
LINQ will help us here:

 var query = from c in db.Categories join i in db.Items on c.CategoryID equals i.CategoryID select i; var items = await query.ToListAsync(); 

Data migration
The attentive reader noticed 2 interesting challenges:

 await ctx.CreateSchemeAsync(); await ctx.UpdateSchemeAsync(); 

The first creates or updates tables based on the declared properties of the context. Columns in the table can only be added. You can not delete.
The second runs a list of migrations, the version of which is less than the database version.
Migrations are declared like this:

  [DatabaseUpdate(typeof(DbChangeset1))] [DatabaseUpdate(typeof(DbChangeset2))] [DatabaseUpdate(typeof(DbChangeset3))] [DatabaseUpdate(typeof(DbChangeset4))] public sealed class DatabaseContext : BaseDatabaseContext { ... } 

Migration example:

  public class DbChangeset1 : IDatabaseChangeset { public int Version { get { return 1; } } public void Update(IEntityProvider provider) { //     ,     . } } 

Something like that. More information can be found in unit tests.

And now I am ready to listen to your comments, suggestions and objections.