Wednesday, May 24, 2006

SQLite as an Embeddable Database

These days I'm involved in designing a ‘Smart Client Application’ architecture for one of our customers. These smart client applications require fair amount of offline capabilities, fast data access and low network utilization. To achieve the objectives most of the data is cached and persisted on the client side. This required us to look at data manipulation, persisting and synchronization mechanisms at the client side.

The first alternative we had was to maintain XML serialized objects on the client side to cache data. This had the problems like Data querying difficulties, Security issues, Thread safe access issues, High memory consumption and Implementation difficulties.

Next we have decided to use an embeddable database for storing data. Our first candidate was MS SQL Express database, as our project is mainly based on VB.NET with MS technologies. Even though it has some limitations, SQL Express is a very feature rich database which can be invoked in the similar way you invoke SQL Server database (with SPs, Views, Functions, full ADO.NET support, etc…). This was a major plus point for us as we could reuse the same data access layer components on the client side. It also supported database replication so that the cached data syncing could be done even at the database level.

But the problem was the high installation requirements of the SQL Express database. Also configuration of the database seemed to be fairly complex for our requirements.

Next alternative we looked at was the open source database SQLite. It has an incredibly small foot print of less than 400Kb and performance was impressive as a small single user database. ADO.NET 2.0 data provider was also available for SQLite and can be integrated to Visual Studio 2005 easily. It has implemented most of the SQL92 standard with few exceptions. SQLite is really a zero configuration single-file database which runs in-process of your application.

Having said the plus points, it is now the time to look at the limitations also. One main problem is that, the only locking level it supports is at database level. This can be a problem when multiple processes accessing the database simultaneously. Since smart clients are generally used by a single user, this shouldn’t be a big problem as an embedded database. But in our case, the smart client application might be deployed on a Cytrix Server and may have many users connecting over consoles.

Having honored by “2005 Open Source Award from Google and O'Reilly”, I think we should seriously consider using SQLite as an embedded database for our smart client architectures.

96 comments:

88Pro said...

any thoughts on

http://www.oracle.com/technology/products/lite/index.html

Hasith Yaggahavita said...

Even though Oracle is not a choise for the mentioned project as out customer has a large existing SQL Server deployment base, Wew have also looked in to SQL Lite and SQL XE.

Oracle XE is comparble to SQL Server Express edition. We considered that also to be bit too heavy for a simple deployment model.

Oracle Lite comprises of cool synchronization techniques and require Oracle EE/PE to be it's back end parent. With the 'Change Log' approach, I think Oracle Lite is more suitable for 'Temporary Disconnecting' clients rather than for 'Occationally Connected' (thick?) clients.

Also Oracle Lite suggests the synchronization approach to be 'data centric' rather than 'service centric' which we think not very appropriate for our model.

Anonymous said...
This comment has been removed by a blog administrator.
Anonymous said...

Thank you!
My homepage | Please visit

Anonymous said...

Thank you!
http://athtataa.com/viqm/qmqk.html | http://otwzqgfz.com/varr/wmgs.html

louisharison0542583364 said...
This comment has been removed by a blog administrator.
Anonymous said...

Have you had a look at SQL Server Compact by Microsoft? It is a free, light weight embedded database.

http://www.microsoft.com/sql/editions/compact/default.mspxhttp://www.microsoft.com/sql/editions/compact/default.mspxhttp://www.microsoft.com/sql/editions/compact/default.mspx

Hasith Yaggahavita said...

Yeah, SQL Compact will be promissing specially if we can share the same programming model as other 2005 databases.

Buy Levitra said...

Great article! Thanks.

Phentermine said...

Thanks for interesting article.

Payday loans said...

Nice Blog!

Phentermine said...

Thank You! Very interesting article. Do you can write anything else about it?

buy Levitra Online said...

Very interesting site. Blog is very good. I am happy that I think the same!

Anonimous said...

Nice! Nice site! Good resources here. I will bookmark!

Anonimous said...

Excellent website. Good work. Very useful. I will bookmark!

Maxwells said...

I see first time your site guys. I like you :)

PaydayLoans said...

zQs4IS You have a talant! Write more!

Auto insurance company said...

TTc7Gm Nice Article.

Hydrocodone said...

O36TF8 The best blog you have!

shannons insurance australia said...

FWtika Good job!

allied arts studio tour said...

Magnific!

fioricet message boards said...

Magnific!

is celebrex bad for you said...

Good job!

memorandum of insurance explained said...

Nice Article.

buy phentermine tablets buy phentermine said...

Hello all!

can take tramadol with subo said...

Nice Article.

search xenical online said...

Please write anything else!

information vicodin said...

Nice Article.

buy dosage orxc com from link phentermine html via said...

Please write anything else!

JohnBraun said...

NDa768 write more, thanks.

coleman sex principal said...

Wonderful blog.

domination sex callbacks said...

Good job!

aniaml sex comics said...

Magnific!

lesbian oral sex said...

Good job!

fuckin anal whores said...

Magnific!

set of sex said...

actually, that's brilliant. Thank you. I'm going to pass that on to a couple of people.

erotic lover sex said...

Hello all!

oral sex uk said...

Nice Article.

bard yard sex said...

Good job!

bed sex tied said...

Thanks to author.

craigslist albuquerque sex said...

actually, that's brilliant. Thank you. I'm going to pass that on to a couple of people.

hot sex israel said...

Please write anything else!

public sex porno said...

Thanks to author.

mama fat sex said...

Change is inevitable, except from a vending machine.

nudist sex art said...

Thanks to author.

doctor doing sex said...

Magnific!

free mature porn clips said...

When there's a will, I want to be in it.

brazilian carnival sex said...

Build a watch in 179 easy steps - by C. Forsberg.

brazil lesbian sex said...

Save the whales, collect the whole set

adult sex hp1 said...

C++ should have been called B

inuyasha sex show said...

Oops. My brain just hit a bad sector.

hydrocodone next day said...

If ignorance is bliss, you must be orgasmic.

cialis personal review said...

Nice Article.

phenterm said...

Beam me aboard, Scotty..... Sure. Will a 2x10 do?

it picture sex said...

Oops. My brain just hit a bad sector.

bisexual teen sex said...

Give me ambiguity or give me something else.

german sex festival said...

Save the whales, collect the whole set

asian sex stories said...

I'm not a complete idiot, some parts are missing!

alt sex moderated said...

Oops. My brain just hit a bad sector.

sex blowjobs xxx said...

Beam me aboard, Scotty..... Sure. Will a 2x10 do?

death by sex said...

actually, that's brilliant. Thank you. I'm going to pass that on to a couple of people.

on sex swing said...

Energizer Bunny Arrested! Charged with battery.

clonidine sustained said...

Energizer Bunny Arrested! Charged with battery.

name said...

Thanks to author.

florida porn law said...

Calvin, we will not have an anatomically correct snowman!

power puff porn said...

A lot of people mistake a short memory for a clear conscience.

animai porn said...

Ever notice how fast Windows runs? Neither did I.

lesbian sex rpg said...

I'm not a complete idiot, some parts are missing!

atlantis toon sex said...

A lot of people mistake a short memory for a clear conscience.

gay college guys porn said...

Beam me aboard, Scotty..... Sure. Will a 2x10 do?

cortoon porn of the simpsont said...

The gene pool could use a little chlorine.

grayvee com free porn amateur couple fu said...

The gene pool could use a little chlorine.

asiasexo lindas asi ticas sexo anal said...

Suicidal twin kills sister by mistake!

frot sex pictures said...

When there's a will, I want to be in it.

porn remastered said...

What is a free gift ? Aren't all gifts free?

helpless sex oral said...

What is a free gift ? Aren't all gifts free?

ib said...

actually, that's brilliant. Thank you. I'm going to pass that on to a couple of people.

celebs naked sex said...

Suicidal twin kills sister by mistake!

photo sex arab said...

Change is inevitable, except from a vending machine.

brittany spares sex said...

A flashlight is a case for holding dead batteries.

candle sex wax said...

Clap on! , Clap off! clap@#&$NO CARRIER

forced sex rape said...

Oops. My brain just hit a bad sector.

jane seymour porn said...

The gene pool could use a little chlorine.

interactive sex games said...

C++ should have been called B

violent porn trailers said...

Build a watch in 179 easy steps - by C. Forsberg.

josh lucas sex said...

actually, that's brilliant. Thank you. I'm going to pass that on to a couple of people.

polish sex blooper said...

C++ should have been called B

best porn sites said...

actually, that's brilliant. Thank you. I'm going to pass that on to a couple of people.

kerala sex picture said...

Build a watch in 179 easy steps - by C. Forsberg.

cell phone porn said...

Give me ambiguity or give me something else.

norwegian teen anal said...

Energizer Bunny Arrested! Charged with battery.

pernilla qvist sex said...

Change is inevitable, except from a vending machine.

phentermine no prescription cheap said...

Oops. My brain just hit a bad sector.

phentermine purchase on line said...

I don't suffer from insanity. I enjoy every minute of it.

buy phentermine w out a prescription said...

Hello all!

huge sex tits said...

A lot of people mistake a short memory for a clear conscience.