Wednesday, June 20, 2012

Introducing SQLite

During the Windows 8 Metro App demos, there have been questions on SQLite. Until I started using it for my Windows 8 App, even I was not aware of the gem called SQLite. Thought of blogging additional information for everyone.

What was my requirement?

A lightweight database to store my contacts information for my Win 8 App. Need to ensure database is present on every machine or tablet which uses the app, so SQL Server / Oracle were immediately ruled out. Need an database which is lightweight, easy to install, and there are no licensing implications.

What did I use?

SQLite

What is SQLite?

SLite.Org describes it as follows: SQLite is a in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private. SQLite is currently found in more applications than we can count, including several high-profile projects.

SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file. The database file format is cross-platform - you can freely copy a database between 32-bit and 64-bit systems or between big-endian and little-endian architectures. These features make SQLite a popular choice as an Application File Format.

Alternatives:

SQLite not as a replacement for SQL Server/Oracle, but a lightweight database. The other feasible alternative was to store the information in an XML file and insert/update the XML file using .Net framework classes.

SQLite for Windows 8:

SQLite team has announced that they are going to release an WinRT(Win 8) version of SQLite. It will be hopefully out before the RTM of Windows 8, and simplify the process of using it within an metro style app. However, it is not yet released as of today (19th June 2012).

So how did I use SQLite for my Windows 8 App?

I used the native SQLite DLL, and a .Net wrapper on top of it, which I got from my contact at Microsoft.

There are other .Net SQLite wrappers on CodePlex, but I haven’t been able to evaluate them. Ensure they are compatible with Win 8 consumer/release preview and pass the certification.

How big is my LibSQLite.dll?

756 KB.

I initially found it hard to believe that you can have an entire database engine in less than 1 MB.

How do I install?

This is the best part. It does not require any installation on end user machine. All I need to do is include it in my Visual Studio project, to ensure its present in my bin folder after code compilation.

Who else uses SQLite?

Countless apps. If you have a smart phone using Android, iPhone OS, Samsung Bada or Windows Phone OS, there are high chances that you have already have an app on your phone which uses SQLite.

Drawbacks/Limitations/Performance:

Inserting contact information for my 450 (and growing) Facebook friends, to my local SQLite database, is relatively slow and takes about 2 minutes.

There are ways and blogs to optimize SQLite performance which I need to look into.

Disclaimer: I had issues with running the app on tablets, and not sure what is the cause. SQLite can be one reason, but I have seen other Win 8 apps using SQLite running without any issues on tablets. So it’s likely that I have picked a wrong version of SQLite, or something else is causing the issue.

I am bad at SQL Data Definition Language syntax. How to I test my SQL JOIN queries?

If you have forgotten the SQL syntax that your probably learnt in college, no worries. You can use open source tools like  “SQLite Database Browser”. It’s a smaller, light weight version of SQL Server Management Studio(that we usually use for our enterprise projects). I create the tables and insert data using GUI, and then use the tool to create the equivalent SQL scripts for me. I also use the tool to test my SQL Queries and joins against the database, before using them in my code.

To know more:

SQLite:

http://www.sqlite.org/

Search on Google, and there are enough reference links and blogs on SQLite.

Though this email is written with SQLite for Win8 App as the background, the information is really generic and can be applied to other scenarios as well.

My experience with SQLite or App development is limited. If there is anything incorrect what I stated above, or better alternatives, do let me know.

No comments: