A SQLite Server/Client Implementation

• Download source code - 1.14 MB

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