SQLite Server

the.weavster · Post by **the.weavster** » Sun Feb 26, 2006 5:00 pm

I have created a little server based on SQLite. It communicates with it's clients using TCP sockets and the following control codes (subject to revision):

Client Control Codes
Codes Sent To Server
Chr(3) SQL Statement Separator
Chr(4) End Of Transmission
Chr(5) User Name and Password Should Precede This Code Separated By Chr(31)
Chr(20) The Buffer Contains SELECT Statements
Chr(21) The Buffer Contains DELETE, INSERT or UPDATE Statements

Codes Received From Server
Chr(3) Separator Used To Delimit The Responses To Multiple SELECT Statements
Chr(4) End Of Transmission
Chr(6) Successful Update
Chr(20) Record Separator
Chr(21) Error
Chr(31) Field Separator

Server Control Codes
Codes Sent To Client
Chr(3) Separator Used To Delimit The Responses To Multiple SELECT Statements
Chr(4) End Of Transmission
Chr(6) Successful Update
Chr(20) Record Separator
Chr(21) Error
Chr(31) Field Separator

Codes Received From Client
Chr(3) SQL Statement Separator
Chr(4) End Of Transmission
Chr(5) User Name and Password Should Precede This Code Separated By Chr(31)
Chr(20) The Buffer Contains SELECT Statements
Chr(21) The Buffer Contains DELETE, INSERT or UPDATE Statements

If you'd like a go with a free beta version (max 3 active sockets) please send me a private message and I will e-mail it to you.
Once any bugs are ironed out I plan to release a version that will avail >25 active sockets for a nominal fee.

The server does not provide any locking over and above that provided by SQLite, if it receives simultaneous requests to update it simply queues them and deals with them one at a time. Simultaneous reads are no problem.

Because the course grain locking provided by SQLite is so simplistic, i.e. the whole database, it doesn't add much overhead and updates are extremely quick. When you have a small workgroup of users this method is usually quicker than allowing multiple simultaneous updates using complicated field-level or even record-level locking algorithms.

You can also set-up the server to only accept connections from a list of IP addresses, or from a list of users with passwords or a combination of the two. If you don't set-up these lists it will accept connections from any client who sends a request on the specified port.

When you send a group of updates, separated by Chr(3) and terminated by Chr(4), they are automatically encapsulated into a transaction. If the update fails it will be rolledback you will receive the error control code Chr(21) along with the database engine error message and the sql statement that caused the exception separated by Chr(20).

You can also submit multiple select statements separated in the same way which is great for lookups.

It's been a while since I used PureBasic, but if it still has the 64K string length limit you may need to take this into consideration when requesting large recordsets.

CSAUER · Post by **CSAUER** » Mon Feb 27, 2006 1:35 pm

Sounds interessting.
Do you want to share a download?

the.weavster · Post by **the.weavster** » Mon Feb 27, 2006 5:01 pm

I don't currently have any web space which is why I've said anybody interested should send me a message.

If you're offering to provide a link for me I would appreciate it, I would also like to be able to show a few screenshots.

the.weavster · Post by **the.weavster** » Tue Feb 28, 2006 11:07 pm

CSAUER has very kindly offered to provide some links for downloading and for a few screenshots.

Versions are available for Win32, Linux and Mac OSX (although the Linux and Mac versions are completely untested).

I have also created a demo client in PureBasic, the source will be included in the download.

Please check back after the weekend.

Thanks.
Weave

the.weavster · Post by **the.weavster** » Tue Mar 07, 2006 2:14 pm

Here are the download links for the SQLite server:

Windows version http://www.xideas.de/purebasic/S4SWin.zip
Linux version http://www.xideas.de/purebasic/S4SLinux.zip
Mac version http://www.xideas.de/purebasic/S4SMac.zip

Thanks to CSAUER for providing these.

GedB · Post by **GedB** » Tue Mar 07, 2006 4:29 pm

the.weavster,

Just so you know: the 64k string limit is removed in version 4.

http://freak.purearea.net/v4/ReadMe.html

Post by **flaith** » Tue Mar 07, 2006 4:29 pm

Great work, thank you

the.weavster · Post by **the.weavster** » Tue Mar 07, 2006 5:08 pm

flaith wrote:Great work, thank you

No problem, if anyone has any good ideas for tweaks post them here and I'll try to implement them.

the.weavster · Post by **the.weavster** » Sat Mar 11, 2006 7:42 pm

GedB wrote:Just so you know: the 64k string limit is removed in version 4.

Just in case this caused any confusion, GedB was making reference to a comment in the source code of the PB client demo.
Requests to and responses from the server are only limited by system resources.

Straker · Post by **Straker** » Wed Jan 17, 2007 4:53 pm

Has anyone tried this in a true multi-user environment?

And, why aren't you selling this weav? Everyone on the web seems to believe that SQLite is single-user only. Its one of its main drawbacks, but this seems to solve that problem, and be multi-platform! Am I correct in this assumption?

EDIT:

I found these guys, but they are Windows only and are not even selling it yet:

http://www.terrainformatica.com/sqlited ... /main.whtm

and the another you have to get with a "different" basic package:

http://www.realsoftware.com/news/pr/2005/sqlabs

the.weavster · Post by **the.weavster** » Thu Jan 18, 2007 9:40 am

Straker wrote:why aren't you selling this weav?

I didn't get much feedback so I didn't push it. For nearly a year now I've been working on a project that's much more important to me - a business information system to compete with SAP BusinessOne or Sage MMS.

Straker wrote:Everyone on the web seems to believe that SQLite is single-user only. Its one of its main drawbacks, but this seems to solve that problem, and be multi-platform! Am I correct in this assumption?

Right on both counts, multi-user and multi-platform.

The SQLabs server you mentioned has been bought out by REALbasic and I believe it now only works with RB (and it's a ridiculous price).

I did make some improvements on the version I originally made available for download here; a way of handling Insert IDs and a command so you could use the server locally and tell it to quit when you're done.

I also made a client library (a *.dll with a PureBasic wrapper) which I tried (and failed) to tailbite into a proper PB library, maybe I'll give that another go.

If I get a chance I'll post a new link over the weekend.

dige · Post by **dige** » Thu Jan 18, 2007 9:54 am

sound very interesting!

Post by **Kiffi** » Thu Jan 18, 2007 10:45 am

dige wrote:sound very interesting!

indeed!

@the.weavster:

> Right on both counts, multi-user and multi-platform.

i am also interested in your SQLite-Server.

Greetings ... Kiffi

Fangbeast · Post by **Fangbeast** » Thu Jan 18, 2007 12:32 pm

I'm interested too. I remember asking you about it ages ago but I wasn't advanced enough in Sqlite to use it but am comfortable enough with Sqlite now to give it a go.

blueb · Post by **blueb** » Thu Jan 18, 2007 2:47 pm

I'm interested also.

We use Sage's BusinessVision and I've been toying with the idea of an alternative package.

I recently purchased Michael Owen's "The Definitve Guide to SQLITE" and it is teaming with good information.

I've downloaded the zip and will try it out.

Thanks,

--blueb