Wednesday, January 07, 2009

Embedded database - SQLite

First of all .... H A P P Y N E W Y E A R ! ! !

I whish you dear readers the best of all during this 2009 (Although It didn't start good in Middle East and Africa).

-- Political crap start here --
My whish for 2009 is that all religion Gods meet some day and kick some bad humans asses (i.e. Mr. B, Mr. Ch and Mr. Uribe)!
-- Political crap end here --

When building applications, one of the main issues is the data storage. If you take a look at C++, persistence is not part of the specification so, use third party components. Serialization is a good way to convert objects directly to a binary data and saving in files or being transferred through a network.

Building systems doesn't mean only storing data. Storing seem to be an easy task for C++ but, what about data management? That's a different subject and, if you want to administer data II can think of two ways:

- Build your own code to read/write and manage files (add, remove, and link objects). Moreover, you have to create your own file format and data structures.

- Use a relational database. If you are not aware of it, in most cases these databases need to be installed and take hundredths of megabytes in storage and take up system memory. Not to say that some require license and their price is high.

Of course, relational databases are powerful, but they are targeted to applications with tons of data and mostly client-server applications.

Well, not everything is lost. Fortunately the open source development community is working hard to provide quality software for most needs.

SQLite is one of the best relational-data management solutions to be embedded in your applications. Now you will say -Never heard of it ... is it really good? Has been tested? Is it used by any commercial package?- Take a look at this page and you will find some answers:

SQLite uses the SQL (Structured Query Language) to manage data. If you already know SQL you are aware how useful this language is.

Databases are stored in single cross-platform files. Meaning that if you need to backup your data or move it to a different machine, all you have to do is to copy/move this file. Being cross-platform means that you can move that file to any other os/hardware (i.e. from your embedded device to windows xp) without having to perform any conversion related to endianness.

You can use it as a linked DLL or include the source code in your project.

Before we continue, you have to create a database. SQLite includes a Command-line application to manage databases. This application can be found in the Download page.

You can follow this link for a quick guide using the SQLite application:

Method 1: Including Source Code

This is the easiest way to include SQLite in your project. All you have to do is download the source code (Please download the "Amalgamation" source) and include it in your project.
I will use Visual Studio 2005 to show you how easy this can be accomplished.
Create a new win32 console project without precompile headers.

Include the following files in your project:
- sqlite3.c
- sqlite3.h

You can add the code in the quick introduction following this link:

In MS Visual Studio you will have to include also the stdlib in order to use the exit function. Otherwise you will get an error.
and build the project.

Method 2: Linking to a DLL

First of all you have to download the shared library (there are versions for Linux and windows). It contains 2 files:
- sqlite3.dll
- sqlite3.def

As you can see there's still missing the .lib and .h files.
- The .h file can be obtained by downloading the source code from the SQLite downloads page.
- the .lib file can be generated from the dll and the def files by using lib.exe command for visual studio. This will create the .lib file to be linked to your project. Lib command syntax is like this:

lib /DEF:sqlite3.def

Now you have all files to create the application. Create a new win32 console application project, without precompiled headers.

Again, copy the code from the introduction and include the stdlib.h header.

Finally, setup the linking library to the project:

a. Set the .lib file location (directory)

b. Add the .lib dependence to the project.

When you build the application, it should work with no issues.

If you have tried both methods you will note that using a DLL will generate smaller code (about 40kb), but if you include the source code the file will grow up to (600kb). Both methods are valid it's up to you to decide which one to use.

If you are working in an environment where you are sure that the dll is installed in the system or you are working with embedded devices, then I suggest the second method.

I hope using SQLite improve your development cycle by reducing the effort in creating data management interfaces.

1 comment:

Ibrahim said...

Please can you write complete tutorials about both methods?