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.

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.

image

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)

15 comments:

Shoes For Little Feet said...

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.

ErikEJ said...

Shoes: No, and I have no plans to do so, as it will raise hell - se some attempts on Stackoverflow. SQL Compact 3.5 SP2 is fully supported on Windows 8 desktop, just not Windows 8 Metro environment

Ahmed Amin said...

Dear ErikEJ
I was Wondering if there is any way to change sqlite database to sql server compact edition database

ErikEJ said...

Ahmed: Not that I know of, other than scripting

John Marsing said...

With your SQL CE toolbox for VS (ver 3.2.0.0) and my .sdf database, I did a "Script Database Schema and Data for SQLite (beta)". This produced four .sqlce files.

My quesiton is how do I, using sqlite3.exe, import them into a new SQLite database? Do I do them all at once on the command line, one at a time, or what?


Here is what I did...

sqlite3 BibleWSA.db < BibleWSA_SQLite_0.sqlce

All this does is return a 1 and give me a BibleWSA.db file with with zero bytes

Thanks

John Marsing

Here's a reference I'm using
http://www.sqlite.org/sqlite.html

ErikEJ said...

John: I think you will have to combine the generated files to a single file (please note that this feature is currently in beta)

John Marsing said...

A simple solution that worked.

Thanks Erik

Ratatat said...

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:

sqlite3 mytest.sqlite < Scripts_0.sqlce;

I get- Error: near "sqlite3": syntax error

Would you please let me know if this is the correct way of doing it?

ErikEJ said...

Ratatat: Is sqlite3 in your path? (No semicolon at the end...)

Ratatat said...

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)

I pasted this from the command window:

SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> sqlite3 db.sqlite < Script_0.sqlce
...> ;
Error: near "sqlite3": syntax error
sqlite> sqlite3 mydb.sqlite < Script_0.sqlce
...>

ErikEJ said...

Ratatat: Is your file useable? Do NOT use semicolon. Are you tying the command or caaling sqlite3 from an app, if so, then maybe the command line is inproper.

Ratatat said...

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 utility
2. 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 save
5. I provided the file name as Scripts
6. It generated two files: Scripts_0.sqlce and Scripts_1.sqlce
7. I appended (copied) all the contents from Scripts_1 into Scripts_0 to generate one file
8. Finally, I opened the sqlite executable and typed that command
9. For the first parameter, I specified file name as mydb.sqlite - this file doesn't exist
10. I was expecting it to get created. Is that what I am doing wrong?

Again, thank you for your responses!

ErikEJ said...

Ratatat I think the issue is step 7, where you mess with/edit the generated files. As yu can read, sqlite3 is fuzzy with the file format. Try to skip that step...

pandabet said...

Why does the .sql file gets split into multiple 9MB files with suffix '_0000'?

ErikEJ said...

@pandabet For performance reasons