ServQLite - SQLite Server for PureBasic 0.0.5

[jamirokwai]

Informationen about ServQLite, a platform-independant SQLite-Server written in PureBasic.

Features (latest version)
- create database, table
- write data into table (INSERT)
- read data from table (SELECT)
- change data in table (UPDATE)
- delete data from table (DELETE)
- ask for single cells of a row of data
- get column-count of a row
- get names, sizes, and type of a column
- supports at types supported by PureBasic
- fallback to integrated SQLite when server not available (may be selected manually)
- TrayIcon to show if development-server is running
- Timeout for clients

- (hard-coded) IP (127.0.0.1), Port (6832)
- memory footprint about 2 - 3 mb, CPU-load about 1 - 2 % for one client

Know bugs
- network-transfer of blobs is buggy
- slow (needs a lot of optimization)

todo
- scheduled backup/restore/optimization
- external configuration
- user-rights (write/read/change)
- one writer/several readers (owser)
- internal caching
- (optional) encryption
- logging
- maximum simultaneous connections
- ServOIP (Server Over IP)

0.0.4 - 21/May/2010 - initial release
i for Mac OS X 10.6, Windows XP

0.0.5 - 29/May/2010 - compiled with PB 4.50 RC 1
i for Linux, Mac OS X 10.6, Windows XP
+ added SysTray-Icon for testing purposes and easy quitting of ServQLite (just click and say yes)
+ 2nd TestClient which creates a database, writes some data into it and reads it back (50 times), with log-file (you'll find these files at / on Mac OS X) - this should need about 30 seconds.

---- download and disclaimer

You try this on your own risk! Although I tested this piece of software.
I cannot be made responsible for data-loss, etc.
Download here: http://pb.quadworks.de/ServQLite.zip (about 1.2 mb).

TestClient 1 puts some data into the database, changes it, reads it back, and displays the result. Piece of cake...
TestClient 2 puts 50 bytes into the database (one at a time), and creates a log-file about this. Another piece of cake...

Included in the download are the server-executables for Linux, Mac OS X 10.6, and Windows XP and 2 (Linux: 1) testclients. Please start the server prior to the testclients. Both Clients should let the server quit itself.

[jamirokwai]

Dear board,

last year, I was fiddling around to code a Server for SQLite (yes, normally you wouldn't need this kind of server...)
I ran into several issues, which seem to be fixed with PB4.50b. So here we go...

It's an early development snapshot for Windows XP and Mac OS X 10.6 (snowie). The Server itself and the client with API and optional fallback to embedded SQLite consist of about 500 lines of code each at the moment. On Snow Leopard the server will grab about 2.8 mb Ram (due to a needed waitwindowevent; without it would be 1.4 mb) and app. 2 % CPU while serving. In idle mode it consumes about 0.3 - 0.5 % (Core 2 Duo 2.26 GHz) - Windows not tested, as Mac OS is my target system.

There is also a testclient-application which will invoke the generation of a single sqlite-file, put some data into it, change the data, and quit itself and the server. The server is rather slow at the moment, as there is no caching, no optimization and such at all.

Please start the server first, after a second you may run the testclient. Use the same machine, as 127.0.0.1 is hard-coded atm (can be changed in sources). And please post your experiences

--- my very first post on this is located above, as I will use it for information about SerQLite ---

Why an SQLite-Server? It's an embedded database after all...

1. Market reasons: Real Software offers their RealSQL-Server using SQLite for 400 bucks (uhm, Euros). From my experience, this piece of software sucks as it is not stable (on Mac OS X). As I suppose not because of SQLite but because of this horrendous RealBasic-stuff. This is my personal opinion.
And you won't need to pay 400 bucks for something that does not work with PureBasic (and does not work perfectly at all)

2. Configuration: You could add a self-configuring network-server using pure PureBasic for your networked database-needs. Nifty, eh?

3. Self-confidence: Because we can.

4. KISS: Because I'd like to have a simple, small and performant Database-Server without having to install 50mb + for mySQL, nor having to pay 400+ Euros for other solutions not playing well (if at all) with PureBasic.

[flaith]

Hi Jamirokwai

i've tested it, it works ok, well done
when the server is started, could you just put a systray icon ?

thanks

[jamirokwai]

Hi Jamirokwai

i've tested it, it works ok, well done
when the server is started, could you just put a systray icon ?

thanks

Hi flaith,

thanks I put it in my to-do-list.
I thought about indicating how many clients are connected to the server using the systray icon, and also quitting the server via right-click.
Can't work on this during the weekend because I take a 3 day off from my Mac ...

[infratec]

Hi JamiroKwai,

tested it on Windows XP:

Server uses 2.284 MByte and uses nearly 0% CPU time.
Even in work, I can not see a CPU load.
To test this you should implement a big loop with insert and delete,
which runs more than 3 seconds.
Something like:

Code: Select all

StartTime = EllapsedMilliSeconds()
repeat
  insert someting
  delete it
until EllapsedMilliSeconds() - StartTime > 3000

Best regards,

Bernd

[DoctorLove]

Cool stuff!

I want to test it on linux. is it possible?
And what are the plans of this server thingie? Opensource or Paid?
If this all works out well, you can hit Realbasic very hard

[jamirokwai]

Hi,

added a systray-icon which is used to show the server is running, and to quit the server.
A Linux-version is only a matter of time. Hopefully, I will have enough time this week to put together a 0.0.5 with Linux-support and some little tweaks. My to-do is about 1 page long
Stress test is working already. I will add this to the 0.0.5 to test ...
There is a lot of room for tweaks, I fear

I'd like to wait for PB4.5 to go further. Especially regarding some tweaks for the SysTray-Lib on Mac OS X.

Probably, I am going to put this into Open-Source...

[HwyStar]

jamirokwai: Is the biggest reason your are doing this related to running the queries on the server and not the client?

I have really enjoyed using SQLite "as is" using the clients to do the work but I also have MSSql in-house so I understand the SQL Server/Client relationships. We have Windows 2003 Enterprise Edtion servers and when I get the chance I will test your software.

[jamirokwai]

jamirokwai: Is the biggest reason your are doing this related to running the queries on the server and not the client?

That is only one reason. The other reason is to move the database to a central server without the need to setup mySQL and such (also for my potential customers). The file-based databases of SQLite make it easier to cope with, I think. Think of backups. It's as easy as copying a file. So, if well done, you can setup a whole ServQLite-System on Linux, Mac, Windows with several clients (Linux, Mac, Windows) in only a few minutes.

I have really enjoyed using SQLite "as is" using the clients to do the work but I also have MSSql in-house so I understand the SQL Server/Client relationships. We have Windows 2003 Enterprise Edtion servers and when I get the chance I will test your software.

Yes, me too. I am using mySQL on my MacBook for coding PHP/mySQL. The installation-file is about 65 mb (Intel) and needs at least 10 minutes to finish. And then, you need to set it up. ServQLite can be less than 1 mb EXE and is installed and ready to run in about 30 seconds (plus configuration)

The server-idea came from a - now stopped - project, where I needed a central, server-based database for several clients. They should have used the same data without the need of mySQL and without doubling data or files including sync-errors.

[DoctorLove]

Hi,

added a systray-icon which is used to show the server is running, and to quit the server.
A Linux-version is only a matter of time. Hopefully, I will have enough time this week to put together a 0.0.5 with Linux-support and some little tweaks. My to-do is about 1 page long
Stress test is working already. I will add this to the 0.0.5 to test ...
There is a lot of room for tweaks, I fear

I'd like to wait for PB4.5 to go further. Especially regarding some tweaks for the SysTray-Lib on Mac OS X.

Probably, I am going to put this into Open-Source...

Cool, if you need some testing for linux, just hit me

[flaith]

...I will have enough time this week to put together a 0.0.5 with Linux-support and some little tweaks...

cool

Probably, I am going to put this into Open-Source...

cool again

[PureLeo]

Really cool stuff!

And your right.. the very basic Real Server is about $300, which means something like R$540 (my country)

You should make this a competitive commercial software! Really!
Good work deserves to be paid... (doesn't mean you should charge 300 bucks for it )

[jamirokwai]

Really cool stuff!

And your right.. the very basic Real Server is about $300, which means something like R$540 (my country)

You should make this a competitive commercial software! Really!
Good work deserves to be paid... (doesn't mean you should charge 300 bucks for it )

Probably, I will open-source it for personal use and development, as for PB-coders for example.
Commercial usage would result it the need of getting a license. Did not think about this, but will have to ...

But first, I'd like it to run perfectly and competitive. A lot to do ...

[Karbon]

With no disrespect intended to the author, I would caution anyone using SQLite in any way in a full multi-user environment (read/write). SQLite just wasn't designed for that and it's stability and speed go out the door as soon as you try to use it for that. I'm not saying it isn't possible, just that you shouldn't assume it will "just work".

Unless you're operating on the database as a read-only DB you're going to run into a LOT of unexpected problems. SQLite is a remarkably stable, fast and reliable single-user (or multi-user read-only) database. It is a remarkably UN-stable multi-user read/write database in any implementation simply because it was never designed to serve the multi-user read/write need. If you ask Richard Hipp about multi-user read/write access in SQLite he'll politely and simply tell you not to do it

If you do choose to go down the road by using this server or just mapping network file systems, every developer needs to understand how SQLite locks and what the consequences are of (trying to) support multiple users writing to the database at the same time.

http://www.sqlite.org/atomiccommit.html
http://www.sqlite.org/lockingv3.html

[jamirokwai]

Unless you're operating on the database as a read-only DB you're going to run into a LOT of unexpected problems. SQLite is a remarkably stable, fast and reliable single-user (or multi-user read-only) database. It is a remarkably UN-stable multi-user read/write database in any implementation simply because it was never designed to serve the multi-user read/write need. If you ask Richard Hipp about multi-user read/write access in SQLite he'll politely and simply tell you not to do it

I fear, that is the problem of RealServer. As we already stated, it is far from being stable. From my experience: in the last 6 months, while using it at a customers office for bookkeeping on 2 clients, it crashed the whole database twice.

As you stated, it depends on the way, you use such a server. If there is only one client with write, and several with read-only access, it should work as expected. This way, the usage could be narrowed down to some kind of information central. Such applications could be internal distribution of information with one central client to add data.

Probably a good way around the multi-user-write-access-problem will be simply not allowing it...

@Karbon: so you say, if several clients read simultaneously, it works. If several clients write simultaneously to the database, it may result in data loss? *shudder* Have to sleep about it, and will get back here on tuesday!