Click here to Skip to main content
14,600,948 members

A SQLite Server/Client Implementation

Rate this:
4.93 (30 votes)
Please Sign up or sign in to vote.
4.93 (30 votes)
23 Jan 2008CPOL
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! :-)

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... :-)

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)

Share

About the Author

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

Comments and Discussions

 
GeneralRe: so far best sqlite server Pin
Huisheng Chen6-Jul-09 2:45
MemberHuisheng Chen6-Jul-09 2:45 
GeneralGuys there's a major update! Pin
Daniel Cohen Gindi21-Jan-08 1:19
MemberDaniel Cohen Gindi21-Jan-08 1:19 
GeneralSQLITE_BUSY is an error that just sucks Pin
Djalma R. dos Santos Filho4-Jan-08 0:26
MemberDjalma R. dos Santos Filho4-Jan-08 0:26 
GeneralRe: SQLITE_BUSY is an error that just sucks Pin
Daniel Cohen Gindi21-Jan-08 1:22
MemberDaniel Cohen Gindi21-Jan-08 1:22 
GeneralHelp for C not C/C++ Pin
symons6528-Dec-07 0:42
Membersymons6528-Dec-07 0:42 
GeneralRe: Help for C not C/C++ Pin
Daniel Cohen Gindi2-Jan-08 20:35
MemberDaniel Cohen Gindi2-Jan-08 20:35 
GeneralRe: Help for C not C/C++ Pin
symons659-Jan-08 21:36
Membersymons659-Jan-08 21:36 
GeneralThank You! Pin
Member 238825724-Dec-07 15:18
MemberMember 238825724-Dec-07 15:18 
Thank You very much for the code. As I mentioned in your previous article, I had been looking for various implementation of client/server of SQLite. Could you please clarify the terms of use for your code? ie the licensing model.
GeneralRe: Thank You! Pin
Daniel Cohen Gindi25-Dec-07 4:43
MemberDaniel Cohen Gindi25-Dec-07 4:43 
Generalsome points Pin
andyj11524-Dec-07 5:29
Memberandyj11524-Dec-07 5:29 
GeneralRe: some points Pin
Daniel Cohen Gindi24-Dec-07 6:57
MemberDaniel Cohen Gindi24-Dec-07 6:57 
GeneralRe: some points Pin
andyj11524-Dec-07 8:16
Memberandyj11524-Dec-07 8:16 
GeneralRe: some points Pin
Daniel Cohen Gindi24-Dec-07 8:33
MemberDaniel Cohen Gindi24-Dec-07 8:33 
GeneralRe: some points Pin
Robert Veira4-Nov-08 13:15
MemberRobert Veira4-Nov-08 13:15 

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

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

Article
Posted 24 Dec 2007

Tagged as

Stats

236.1K views
6.3K downloads
127 bookmarked