From .NET + SQL Server to SQLite on Mobile
This is a series of articles about using SQLite when developing mobile apps. The target audience is folks who are coming from the world of SQL Server and .NET.
Table of Contents
Friday, 18 April 2014
Tuesday, 8 April 2014
Friday, 14 March 2014
Friday, 7 March 2014
Wednesday, 26 February 2014
Friday, 21 February 2014
Thursday, 20 February 2014
Monday, 17 February 2014
Sunday, 16 February 2014
Stuff not written yet
(not written yet) Community
(not written yet) Visual Studio
(not written yet) System.Data.SQLite
(not written yet) SQLite-Net
(not written yet) Encryption
(not written yet) Language
(not written yet) Cars and Clocks
(not written yet) API
(not written yet) Locks
(not written yet) Collations
(not written yet) uniqueidentifier
(not written yet) IDENTITY
(not written yet) Blobs
(not written yet) Dates and Times
(not written yet) Indexes
(not written yet) Triggers
(not written yet) Views
(not written yet) Joins
(not written yet) Common Table Expressions
(not written yet) ALTER TABLE
(not written yet) Temporary tables
(not written yet) SELECT INTO
(not written yet) MERGE
(not written yet) Control Structures
(not written yet) PRAGMA
(not written yet) EXPLAIN
(not written yet) Full Text Search
(not written yet) Performance
The SQLite community, insofar as such a thing exists, is going to seem very different from the .NET community where you live now.
People have done some things to make using SQLite with Visual Studio a more pleasant experience.
The SQLite team supports an ADO.NET-compatible API.
SQLite-Net is a popular SQLite wrapper for C#, including a simple ORM.
Keeping data on a mobile device is a risk, brecause people lose their phones and tablets. A SQLite database file can be encrypted using the SQLite Encryption Extension ($) or SQLCipher (open source, but official/supported builds from Zetetic are $).
The most jarring part of the transition to mobile app development is changing from C# to Objective-C or Java. Xamarin's tools can help with that.
I won't apologize for my claim that you will be better off if you understand how things work 'under the hood'.
SQLite's primary API is in C, which is not the natural language for someone coming from the .NET world, nor is it the natural language for developing on mobile platforms.
More than anything else, the reason for the word Lite in SQLite is because of the simpler way it handles locking.
SQLite supports several built-in collations, plus the ability to define new ones.
SQLite doesn't have a uniqueidentifier type, but it does have ways of dealing with them.
The equivalent of IDENTITY in SQLite is INTEGER PRIMARY KEY, which, believe it or not, is different from INT PRIMARY KEY.
SQLite has excellent support for blobs.
SQLite has no specific types for dates or times, but it has a number of functions for dealing with them in textual or numeric format.
SQLite supports indexes, with generally fewer limitations than SQL Server.
SQLite's support for triggers is surprisingly strong, but there are significant differences from SQL Server.
SQLite has basic support for views, but they are read-only.
SQLite supports inner join and left outer join.
As of version 3.8.3, SQLite supports common table expressions and recursive queries.
SQLite's support for ALTER TABLE is very limited.
SQLite has good support for temporary tables.
SQLite can create a temp table from a SELECT result, but the syntax is different.
SQLite does not support the MERGE statement, but it does have some similar features which will cover many related use cases.
SQLite doesn't support T-SQL stuff like conditionals or loops, but it has workarounds for some common cases.
SQLite's PRAGMA statement is a way to access all kinds of configuration settings that are specific to SQLite.
SQLite's EXPLAIN statement can show you what its query planner intends to do.
SQLite includes an excellent full text search engine.
In many cases, SQLite is significantly faster than SQL Server. But not always.