Exporting SQL Server Compact to SQLite

The current available local relational database storage options on WinRT (Windows 8) are limited to SQLite (and maybe some others). Also on Windows Phone 8, both SQL Server Compact and SQLite will be available. So a natural path solutions currently based on SQL Server Compact will be to migrate to SQLite, and the first step would be to move the schema and data to a SQLite database.

I have therefore “bitten the bullet”, and the next version of the SQL Server Compact Toolbox, currently available for download in an alpha version, includes a feature to “dump” a SQL Server Compact database in SQLite .dump format.

In this post I will give you some insight in what was required to work with SQLite from the perspective of a SQL Server Compact developer.

The “home”of SQLite is http://www.sqlite.org and all required information is available there.The Downloads page contains a command line utility called sqlite3.exe, which can perform various operations against a SQLite database.The command line utility includes everything required to work with SQLite databases, as the “engine” code is embedded in the .exe – nice.

On of the features included is the ability to “dump” (export) an entire SQLite database to a .sql file, using the following command:

sqlite3 chinook.db .dump > chinook.sql

(Note that the Chinook sample database is also available in SQLIte format)

And the command to create a database file and load a dump file (import) is:

sqlite3 chinook.db < chinook.sql

The dump file has the following format:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE [Shippers] (
  [Shipper ID] INTEGER NOT NULL
, [Company Name] nvarchar(40) NOT NULL
, CONSTRAINT [Shippers_PK] PRIMARY KEY ([Shipper ID])
);
INSERT INTO [Shippers] ([Shipper ID],[Company Name]) VALUES (1,'Speedy Express');
INSERT INTO [Shippers] ([Shipper ID],[Company Name]) VALUES (2,'United Package');
INSERT INTO [Shippers] ([Shipper ID],[Company Name]) VALUES (3,'Federal Shipping');
… More table and data
… Indexes
COMMIT;

The PRAGMA command disables foreign keys constraint checking. And the BEGIN TRANSACTION command makes the process run fast!

So I order to create the SQLite export feature, I decided to create a script in dump file format for SQLite. During the implementation of this, I discovered the following, which you may (or may not) find helpful in your work with SQLite:

1: The dump file must be in UTF8 format, and an UTF8 BOM should not be included. This poses a challenge if you want to edit the file (using Notepad anyway, there are workarounds). I have therefore added the dummy SELECT 1; command to the to of the generated file, to allow you to edit it in notepad.

In order to create a UTF8 file without the 3 byte identifier, use code similar to the following:

            using (FileStream fs = new FileStream(fileLocation, FileMode.Create, FileAccess.Write, FileShare.Read))
            {
                System.Text.Encoding encoder = new System.Text.UTF8Encoding(false);
                if (!sqlite)
                    encoder = new System.Text.UnicodeEncoding();

                using (StreamWriter sw = new StreamWriter(fs, encoder))
                {
                    sw.WriteLine(script);
                    sw.Flush();
                }
            }

2: ALTER TABLE is limited, so constraints must be included in the CREATE TABLE statement

3: GO separator is not used, all statements must simply end with semicolon.

4: IDENTITY is implicitly supported, as described here: http://www.sqlite.org/lang_createtable.html#rowid

5: The “N” unicode prefix is not used in SQLite.

6: SQLite datetime data format: '2007-01-01 00:00:00'

7: SQLite binary data format: X’FFAABB’

8: I have decided to not include rowversion columns in the dump file, they do not appear to be supported by SQLite (let me know if I am wrong)

You can view the few code changes I had to make here:
http://exportsqlce.codeplex.com/SourceControl/changeset/93614
http://exportsqlce.codeplex.com/SourceControl/changeset/93619

If you know anything about SQLite, please try the tool/script and let me know what I have done wrong.

The Visual Studio 2010 ADO.NET based tooling for SQLite is available here. Go to the downloads page, and install sqlite-netFx40-setup-bundle-x86-2010-1.0.81.0.exe 

Once you have moved the database to SQLite format you can start using it with for example WinRT, there are a couple of good starter blog posts here:

Tim Heuer: http://timheuer.com/blog/archive/2012/08/07/updated-how-to-using-sqlite-from-windows-store-apps.aspx

Matteo Pagani: http://wp.qmatteoq.com/using-sqlite-in-your-windows-8-metro-style-applications

(Keep in mind that the tooling for this is currently evolving)