Friday, August 10, 2012

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.

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:

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:

13 comments:

Have you done any perf. testing between sqlite and sqlce 4? I run a website with very fast response times using sqlite but the write operations are defintely blocking to all readers. the site is about 80% read and 20% write. I was contemplating moving over to sqlce as our number of visitors have increased - lack of support for windows 8 has me a bit worried.

Erik, I've used your utility before and successfully been able to generate sqlite databases from the scripts generated by using your tool against my existing sdf database. However, months later, I am trying the same thing again and I keep getting a syntax error when running this command:

Thank you for your response. It wasn't in the Path, so I added it. But the error won't go! I remember not using the semicolon before, but I tried it anyways. If I don't use the semi colon, it prompts me to do something more. (All my files are in the same directory)

Erik, I am running it directly from the sqlite utility (not from any app).

I apologize for my amateur questions - but I was of the opinion that the file is created and I only have to specify the file name. I've forgotten - I did the perfectly before. Let me refresh my steps, please let me know if you see something wrong:

1. Run your utility2. Connect to my sdf database from the utility (mytest.sdf of size 10 MB)3. Right click on the database and then select "Script Database Schema and Data for SQLite (beta)"4. Next it popped up the window then asked me for a location to save5. I provided the file name as Scripts6. It generated two files: Scripts_0.sqlce and Scripts_1.sqlce7. I appended (copied) all the contents from Scripts_1 into Scripts_0 to generate one file8. Finally, I opened the sqlite executable and typed that command9. For the first parameter, I specified file name as mydb.sqlite - this file doesn't exist10. I was expecting it to get created. Is that what I am doing wrong?