PlanetSquires Forums

Please login or register.

Login with username, password and session length
Advanced search  

Author Topic: SQLite Client Server  (Read 517 times)

Richard Kelly

  • FireFly3 Registered User
  • Senior FireFly Member
  • *
  • Posts: 318
SQLite Client Server
« on: April 08, 2017, 11:57:54 AM »

I'm designing a new application. I like SQLite as an application file format, and, of course, want to seamlessly support both single and network versions of my application. I took a long look at SQLightening and hit a crucial spot. The latest versions of SQLite support write ahead logging or WAL mode. WAL has a of improvements for concurrency, and, in the documentation it says:

All processes using a database must be on the same host computer; WAL does not work over a network filesystem.

From this I conclude that to use WAL in a network scenario, the database and the process invoking the SQLite API must be on the same physical computer. I can't see how to use SQLitening in this scenario. What am I missing/not understanding?

If SQLitening is not suitable, then a TCP server is needed, possibily returning results in an XML/JSON format. Thoughts?

Rick Kelly
Logged

David Kenny

  • FireFly3 Registered User
  • Senior FireFly Member
  • *
  • Posts: 434
  • Windows 7
Re: SQLite Client Server
« Reply #1 on: April 10, 2017, 04:43:56 AM »

I have used SQLightening over a network quite successfully before.  It was over 5 years ago though. I don't know if anything changes since then, but SQLightening was super easy to setup and use.  I remember thinking that it was going to be a bear to set up but was pleasantly surprised.

SQLightening's "server" component (don't remember the actual name) is the piece that resides on your server.  The "client" piece is compiled with your software and communicates with said "SQLightening server component". SQLightening is what that allows your program, residing on another machine, to submit SQL queries to and to receive the results from the program (SQLightening server component) that is running on the server (with the database) as you stated.

Basically SQLightening is the middle man between your program and the remote DB.  I wrote my own client/server for SQLight, that worked pretty well and abandoned it immediately when SQLightening came out.  It had so much more features and was so simple.  Can't remember now, but it was probably faster too.

It's late and I'm very tired.  If that wasn't clear, ask more questions and I will get back to you.  :) 

David
Logged

Richard Kelly

  • FireFly3 Registered User
  • Senior FireFly Member
  • *
  • Posts: 318
Re: SQLite Client Server
« Reply #2 on: April 10, 2017, 07:39:05 PM »

Thank you for time on the reply. I'm following Paul closely on his direction with WinFBE and the IDE followup and that is my intended target environment. Rather than trying to port SQLitening to FB, I can easily build socket, client and server classes that should make it easy later on.

Rick
Logged

Paul Squires

  • Administrator
  • Master FireFly Member
  • *****
  • Posts: 8090
  • Windows 10
    • PlanetSquires Software
Re: SQLite Client Server
« Reply #3 on: April 10, 2017, 09:45:54 PM »

Hi Rick, I had also started to write a Sqlitening replacement using FreeBasic. The basics are done but I have switched to trying to get WinFBE and the visual designer done. I think those tools should be my priority and then the grid and database tools.
Logged
Paul Squires
PlanetSquires Software
FireFly Visual Designer, WinFBE Editor

Richard Kelly

  • FireFly3 Registered User
  • Senior FireFly Member
  • *
  • Posts: 318
Re: SQLite Client Server
« Reply #4 on: April 10, 2017, 10:16:56 PM »

Hi Rick, I had also started to write a Sqlitening replacement using FreeBasic. The basics are done but I have switched to trying to get WinFBE and the visual designer done. I think those tools should be my priority and then the grid and database tools.

I'll post my classes, and perhaps, make a contribution to your efforts that you could possibly bake in. I'm going on SDK using the socket api's directly. My plans include having the socket class support UDP and TCP simultaneously with the idea that the client can use UDP to 'find' the server. I'm also planning on including a two way authentication handshake with every server connection.  (HMAC/SHA256?) How much of the SQLite api to support is still uncertain although the server will be built around a connection pool and multi threaded WAL/SHM, perhaps as a windows service. I'm leaning to returning results in XML with both the client and server classes having methods to support that.

As I will post things as they go along, other ideas/contributions may come up.

Rick
Logged

Paul Squires

  • Administrator
  • Master FireFly Member
  • *****
  • Posts: 8090
  • Windows 10
    • PlanetSquires Software
Re: SQLite Client Server
« Reply #5 on: April 11, 2017, 11:24:16 AM »

That's great news Rick, I would be happy to help as much as possible. Using FreeBasic will at least ensure that a 64 bit version will be available. I decided to go with an awesome networking package made by FB'er Martin Wiemann (DeltaLab's Germany) called "TSNE_V3 - TCP Socket Networking"

I think JSON or BSON would be a better choice than XML.....
Logged
Paul Squires
PlanetSquires Software
FireFly Visual Designer, WinFBE Editor