Click here to Skip to main content
Click here to Skip to main content

A SQLite Server/Client Implementation

, 23 Jan 2008
Rate this:
Please Sign up or sign in to vote.
Implementing server/client architecture for the great SQLite!

Introduction / Background

Recently, I had to choose SQL Server for a big project I'm working on, and to be honest - I did not like my options. First, there were all those "x vs. y" performance tests on the web, which did not give very good grades for MS-SQL Server (not to mention the costs, or weight of that hog), and then there are also MySQL, PostGRE, and others, but none of them satisfied me. What I wanted was something that allows me to choose exactly what I want from my server, to use a database engine as good as the SQLite... and at no cost.

Which means - I have to write one...

I started out by searching the web, and there was only one serious result, which was Alex K.'s project, from 2004 I think (his project can be found here).

I got his permission to use his code (actually, I asked him right before posting this article...), and I have adopted some of his code, and wrote a lot of new code. Some of the function names may sound familiar for those who were ever introduced to his code.

Anyway, his code has four main problems that I did not like:

  1. He was passing handles (!) over network.
  2. He was compiling statements and stepping each time.
  3. He was actually wrapping SQLite 2 and not SQlite 3...
  4. VC2005 found leaks in the threads!!!

The problem with no. 1 is that it is pretty clear that the communication between the client and the server must be transparent, no handles and such. The problem with no. 2 is that it generates too much overhead. You should execute one SQL query, get the results at once, and that's it! (Anyway, that is how I see it...) The problem with no. 3 is that most people, most importantly me, like to update their SQLite engine when an update comes out and says in big letters "A minor bug was fixed". The problem with no. 4 is eh... Well, I dunno... Do you like memory leaks?

Basically, what I did is this:

I took the sockets handling function, tweaked it a bit, and replaced gethostbyname with getaddrinfo so it can support IPv6. IPv4 is pretty much deprecated. I rewrote the thread handling classes, the same architecture, some differences here and there, but I just had to be sure there are no memory leaks. The stack classes are almost unharmed; the main thing I changed for them is the naming... I also rewrote the handler class, with the same basic architecture and even function names. Also, added support for SQL execution and SELECT queries! Smile | :)

On the client side, I added an interface, which I took completely from my SQLite wrapper classes, so you do not have to parse the tables' results and convert from UTF8... It does it all for you. One more important thing - I have added support for user authentication on the server.

Well, I'm not really going to tell you each and every word of code I wrote there, so you will just have to download and see for yourself. I'm also not planning on writing any kind of documentation here! I do not have the time for it. Besides, the code is pretty much self-explanatory.

Note: If you are planning on compiling for Win2K/Me/98/95, you will have to include wspiapi.h because of my use of getaddrinfo. Seems like before WinXP, IPv6 was not yet the standard as stated in MSDN.

P.S. - If anyone is serious enough and wants to take this SQLite server/client thing forward, be my guest. It will be nice to post on SourceForge or something like that, keep track of things, documentation, etc... I just do not have the time for it...

Using the code

While writing the libraries, I had to write a demo application to test them... so they are attached in the same solution. The solution basically includes: server classes, client classes, server app, and a client test app. If you want to use the code, just include the relevant library. Also, to compile the server library, you have to include the SQLite3 library/DLL/sources, which can be obtained from here. (The sqlite3.h file is expected at the subfolder ./SQLite/.)

Update 21/01/2008: Now the ZIP file comes with SQLite3 sources as well, because it seems like many users here just do not know how to go to the SQLite website, download sources, and include it in the project... I mean, guys! You cannot do that, but you can use SQL???

For the security part: the default mode of the server library is such that no authentication is required. Unless you write an authentication class and attach it to the server. What I did was write an interface class, which you should derive. In the server demo application, I created a class called CINIAuth, which manages users/passwords/permission in an INI file. Of course, this is not the most secure way, but if you need more security, you can easily write a class which encrypts the passwords and store them wherever you like. (Heck! Even in a SQLite DB!)

Update 21/01/2008: The server now encrypts the passwords with SHA512!

Update 21/01/2008: Now, the server can run as a service. It works as a tray icon, and accepts command line arguments:

Arguments:

  • /instsvc - Install as a service.
  • /remsvc - Uninstall service.
  • /minimize - Minimize to tray on startup.
  • /start - Start listening on startup.
  • /port:[port_num] - Set server listen port to [port_num]. (Will not save this port to settings.ini.)
  • /? or /help - This message.

The way I see it, all you have to do now is take a look at the code, and try it... So, go on!

Points of interest

"Did you learn anything interesting/fun/annoying while writing the code? Did you do anything particularly clever or wild or zany?" (www.codeproject.com)

Ouch, I hate that article-model of CodeProject. It is like they are forcing me to answer a stupid question like in those tests in high-school. Well, yes! I did learn something interesting - Win2K does not support IPv6 by default! And yes, I did learn something fun - I love SQLite! And another yes - the annoying thing is other people's memory leaks... Smile | :)

Just kiddin', CodeProject, I love ya'!

TODO:

  1. In the connections list, show connected username and open DB (if any is open).
  2. Allow to define DB aliases instead of using file paths (two different modes...).
  3. Enhance security: Add encryption for all communication. (Allow client to request encrypted/non-encrypted communication, and server to force encryption or configure the default mode).
  4. Allow client to request DBs' aliases available. (If the client has permission to request so, and if we are not in direct filenames mode.)
  5. Find the time to actually do these "TODO"s!

History

  • 21/01/2008:

    Now the server demo application is much more than a demo!

    • You can run it as a service...
    • It works as a tray icon.
    • It accepts command line arguments in case you do not want it as an NT Service...
  • 23/01/2008:
    • Server app: Fixed a bug with reading the passwords from the INI...
    • Server app: Now determines correctly when a client has disconnected.
    • Server app: Now lists the connected clients and allows you to kill them one-by-one! :-P

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Daniel Cohen Gindi
Software Developer (Senior)
Israel Israel
No Biography provided

Comments and Discussions

 
QuestionBug found Pinmemberszomi22-Dec-13 6:20 
SuggestionNew version, wellcome :) Pinmemberinfal29-Dec-11 0:22 
GeneralRe: New version, wellcome :) Pinmemberwoj11719-Jan-12 5:56 
GeneralRe: New version, wellcome :) PinmemberNicolas Mohamed1-Feb-12 1:54 
GeneralRe: New version, wellcome :) Pinmemberjlmw4-Feb-12 14:26 
GeneralRe: New version, wellcome :) Pinmembertigerml9-May-12 17:21 
GeneralRe: New version, wellcome :) Pinmembersingwg22-Jul-12 3:12 
GeneralRe: New version, wellcome :) PinmemberDaniel Cohen Gindi22-Jul-12 3:40 
GeneralRe: New version, wellcome :) Pinmembersingwg22-Jul-12 18:41 
GeneralRe: New version, wellcome :) Pinmemberhullihulli16-Apr-13 3:14 
BugRevised version of ThreadProc Pinmemberinfal28-Dec-11 13:04 
GeneralRe: Revised version of ThreadProc PinmemberDaniel Cohen Gindi28-Dec-11 19:46 
BugOther bug's in code and thread termination Pinmemberinfal27-Dec-11 12:57 
GeneralRe: Other bug's in code and thread termination PinmemberDaniel Cohen Gindi28-Dec-11 19:43 
BugSome Improvements! Daniel, how I can contact you? Pinmemberinfal25-Dec-11 11:57 
GeneralRe: Some Improvements! Daniel, how I can contact you? PinmemberDaniel Cohen Gindi28-Dec-11 19:42 
GeneralThanks PinmemberJerry Evans13-Jul-10 5:02 
GeneralSQL Server Compact Edition PinmemberHkan Nilsson11-Mar-10 20:38 
GeneralYou showed very nice example of using SQLite PinmemberKhusniddin28-Feb-10 4:40 
Generalbug when transmission packet is fragmented Pinmembernearperfect15-Jan-10 11:07 
GeneralRe: bug when transmission packet is fragmented PinmemberDaniel Cohen Gindi16-Jan-10 9:34 
GeneralClient Lib not working On Windows CE PinmemberCüneyt ELÝBOL11-Apr-08 22:07 
GeneralTODOs / Requests / BUGs - Here please! PinmemberDaniel Cohen Gindi24-Jan-08 5:33 
Questionhonoring data types? PinmemberUnruled Boy24-Jan-08 2:40 
Questionrole-based per db auth? PinmemberUnruled Boy23-Jan-08 22:43 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 23 Jan 2008
Article Copyright 2007 by Daniel Cohen Gindi
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid