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
Anything 'not written yet' is subject to change. Entries are listed in a somewhat logical order for their content, roughly proceeding from 'big picture stuff' to 'technical details', but I am not promising to actually write them in that order. :-)
Microsoft apparently considers SQLite to be the replacement for SQL Server Compact.
A big distinction between SQL Server and SQLite is visible in the fact that only one of them has the word 'server' in its name.
In a number of ways, you can tell that SQLite was not originally written specifically for Windows folks.
(not written yet) Community
The SQLite community, insofar as such a thing exists, is going to seem very different from the .NET community where you live now.
(not written yet) Visual Studio
People have done some things to make using SQLite with Visual Studio a more pleasant experience.
(not written yet) System.Data.SQLite
The SQLite team supports an ADO.NET-compatible API.
EF6 on Xamarin: Progress (or lack thereof)
Not ready for prime time.
(not written yet) SQLite-Net
SQLite-Net is a popular SQLite wrapper for C#, including a simple ORM.
(not written yet) Encryption
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).
Issues with sync and constraints
In a replicate-and-sync app, it would be really nice if the SQLite database on the mobile device had exactly the same constraints as the SQL Server database on the backend.
(not written yet) Language
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.
(not written yet) Cars and Clocks
I won't apologize for my claim that you will be better off if you understand how things work 'under the hood'.
(not written yet) API
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.
(not written yet) Locks
More than anything else, the reason for the word Lite in SQLite is because of the simpler way it handles locking.
At the SQL language level, the biggest difference with SQLite is the way it deals with data types.
SQLite has no distinction between CHAR and NCHAR. Everything is Unicode.
(not written yet) Collations
SQLite supports several built-in collations, plus the ability to define new ones.
(not written yet) uniqueidentifier
SQLite doesn't have a uniqueidentifier type, but it does have ways of dealing with them.
(not written yet) IDENTITY
The equivalent of IDENTITY in SQLite is INTEGER PRIMARY KEY, which, believe it or not, is different from INT PRIMARY KEY.
(not written yet) Blobs
SQLite has excellent support for blobs.
(not written yet) Dates and Times
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.
(not written yet) Indexes
SQLite supports indexes, with generally fewer limitations than SQL Server.
SQL Server treats NULL as a value. SQLite treats it as the absence of a value.
(not written yet) Triggers
SQLite's support for triggers is surprisingly strong, but there are significant differences from SQL Server.
(not written yet) Views
SQLite has basic support for views, but they are read-only.
SQLite has excellent support for foreign keys, including the ability to defer them until commit time, which I wish that SQL Server could do.
(not written yet) Joins
SQLite supports inner join and left outer join.
(not written yet) Common Table Expressions
As of version 3.8.3, SQLite supports common table expressions and recursive queries.
(not written yet) ALTER TABLE
SQLite's support for ALTER TABLE is very limited.
(not written yet) Temporary tables
SQLite has good support for temporary tables.
(not written yet) SELECT INTO
SQLite can create a temp table from a SELECT result, but the syntax is different.
(not written yet) MERGE
SQLite does not support the MERGE statement, but it does have some similar features which will cover many related use cases.
(not written yet) Control Structures
SQLite doesn't support T-SQL stuff like conditionals or loops, but it has workarounds for some common cases.
(not written yet) PRAGMA
SQLite's PRAGMA statement is a way to access all kinds of configuration settings that are specific to SQLite.
(not written yet) EXPLAIN
SQLite's EXPLAIN statement can show you what its query planner intends to do.
(not written yet) Full Text Search
SQLite includes an excellent full text search engine.
(not written yet) Performance
In many cases, SQLite is significantly faster than SQL Server. But not always.