FujiNet SQL Proxy

[+Ripdubski]

I've been working on a way to use a remote SQL database from the A8 via FujiNet. I originally wanted to use cubeSQL since its free, and offers SQLite wrapped in a server. However I need full JSON to and from to make it work, and thats still WIP on the outbound side.

 

In the mean time, I've been writing my own SQLite wrapper in the form of a proxy using Perl. I wanted to use SQLite because the db's are small and it doesn't have any running process requirements. I chose Perl because its readily available, and has easy integration with both IP and DBI.

 

Since SQLite doesn't have user authentication compiled in (by default), and I want to keep it simple, so I added some basic authentication into the proxy itself (you'll maintain this DB directly with SQLite admin tools).

 

The proxy will listen for connections, accept commands, and return responses. Right now, it will open and close a database, and handles user authorization successfully. I'm working on the add/delete/update and query functions now. The first 3 should be fairly easy to implement, with delete/update also having a user perm tied to them. Select will be a little more complex in how it returns larger datasets back to the A8, and I need to think through that a bit more. My initial thought is to allow up to three 4K chunks to be returned - the first result would have only two (current 4K, and next 4K), once progressed to next 4K then there would be three (previous 4K, current 4K, and next 4K). Chunk sized could be changed depending on program requirements/memory restrictions on the A8. The other thought is to just return some # of results which could be asked through calls to the proxy, which would free up a lot of storage memory on the A8.

 

Here is a shot of the proxy server running, and a simulation client showing how the calls would be made. Just the basics right now.

[NISMOPC]

Nice. Might want to ask Admin to have this moved to the FUJINET section forum.

[+Ripdubski]

A8 SQL Proxy, the update. I finished the command statement process portion so inserts, updates and deletes are functioning. Since the results are so fast, I've decided on using a range request system for the selects, where only the rows in the given range will be returned. Thus the client will request 1 to 10, then 11 to 20, for example. I may change that after I write an A8 app that uses these calls depending on performance. Attached are some screen shots of the inserts, updates, and deletes being handled by the proxy, being issued using the sim client, and showing the data in the backend SQLite db.

 

Insert:

 

 

Update:

 

 

Deletes:

 

(I'll ask to have thread moved to FujiNet)

 

[MrFSL]

 

On 1/2/2021 at 1:33 PM, Ripdubski said:

I wanted to use SQLite because the db's are small and it doesn't have any running process requirements.

...but it does have running process requirements right? I mean at the very least you will have your perl process plus whatever service/supervisor gets it up and running. I mean if there will never be more than connection at a time I guess... but otherwise you might find your perl script growing by leaps and bounds. You've already had to roll in some sort of authentication and JSON to work in while there are already great server database systems out there that aren't hard to set up. I understand the proxy for handling the Fujinet/A8 limitations.

 

NOT that I disapprove! Any time you can use Perl to do something cool I approve! - And who cares about my approval anyways? Just suggesting not using SQLite.

[+Ripdubski]

16 hours ago, MrFSL said:

 

...but it does have running process requirements right? I mean at the very least you will have your perl process plus whatever service/supervisor gets it up and running. I mean if there will never be more than connection at a time I guess... but otherwise you might find your perl script growing by leaps and bounds. You've already had to roll in some sort of authentication and JSON to work in while there are already great server database systems out there that aren't hard to set up. I understand the proxy for handling the Fujinet/A8 limitations.

 

NOT that I disapprove! Any time you can use Perl to do something cool I approve! - And who cares about my approval anyways? Just suggesting not using SQLite.

 

I appreciate the input.  Don't read any of this the wrong way.  It's not an attack or a defense, but just a further explanation of why I am doing it the way I am.

 

The only process required will be the Perl proxy process, which could be spun up and down as needed and is very light overhead.  SQLite is not like MySQL, etc where a daemon runs nonstop.  Think of it as an xBase file on steroids.  You are right, there are other platforms, none will work without full JSON at the very least (which isn't there yet).  I'm not worried about multi-users too much since this is a niche program.  SQLite can handle multiple connections, my script will not (as it is today) but could easily be adapted to fork another process the way it is written.  SQLite *will* lockout a second request until the first completes in a multi connection contention scenario.

 

I know SQLite has limits as a DB server.  It is not one.  I want to use it because its use is similar in nature to using xBase, and previous reasons stated.  It's simple, small, and process-less.  I just don't want the overhead of running a DB server.  I did that for years and years on my NAS, migrated away from server based databases, and don't miss it. Since my perl script uses DBI, you could substitute another database driver and connect to MySQL, etc.

 

For the record, I am writing this for *me*.  As such, there will only be 1 thread executing at any point in time for me.  I will release it when done for anyone else to use should they so desire.  If no one does, I won't lose any sleep over it. At the end of the day, it's a learning exercise for me, and just something cool to do with the Atari.

 

[+Ripdubski]

Another update.  I got the foundations for the SELECT queries working.  Still need to implement the offset and limits on returned results.  You can't see it in the screenshot of the simulated client, but there is a non printable (char 31/unit separator) embedded between fields in each rows returned results.

 

I did find I need a trap condition for when the SQL sent from the client is fubar'd.  Bad things happen. ?

 

I also need to implement a COUNT interface, or something to handle functions like count where only 1 row is returned in format unlike SELECT.

 

[MrFSL]

16 minutes ago, Ripdubski said:

Don't read any of this the wrong way.  It's not an attack or a defense, but just a further explanation of why I am doing it the way I am.

Cool! I didn't/wouldn't and appreciate the conversation.

 

20 minutes ago, Ripdubski said:

I just don't want the overhead of running a DB server.  I did that for years and years on my NAS, migrated away from server based databases, and don't miss it. Since my perl script uses DBI, you could substitute another database driver and connect to MySQL, etc.

Yup. I get it. From an engineering perspective it just looked like re-inventing the wheel for the sake of efficiency or simplicity. In my experience, for the reasons why most people want a database server, it inevitably turns out that one can't make these things more efficient nor simplistic nor sercure, etc. Instead, you could flip a handful of the default switches and end up with something stripped down and similar to what you are after. Perhaps even leaner. Perhaps even more simplistic. Afterall the default configs and setups straight out of the repos aren't always the leanest. --- But you don't want a database server for reason that most people run database servers so my point is moot. 

 

24 minutes ago, Ripdubski said:

For the record, I am writing this for *me*.  As such, there will only be 1 thread executing at any point in time for me.  I will release it when done for anyone else to use should they so desire.  If no one does, I won't lose any sleep over it. At the end of the day, it's a learning exercise for me, and just something cool to do with the Atari.

Yup. I was only trying to be helpful though upon re-reading it might have sound too critical. I like what you are doing and it sounds like fun. Drive-on good sir/ma'am and I look forward in sharing in your progress!

 

Cheers~

 

[+Ripdubski]

A8 SQL Proxy, update 4. Finished off the SQL SELECT for multi row results. Decided to let client handle limit and offset as part of the query string, and gave it the ability to halt results. Also added handler for SQL SELECTS that return single values like SELECT COUNT. Screenshots show that in action. Now I'm ready to document the API and start an actual A8 app that uses it.

[+Ripdubski]

Shell of sample A8 program to use it is complete.  Working on underpinnings.  Will be a while before another update occurs.

 

[BradleyReed]

Hi there. I like your thinking about handling larger datasets. Using a remote SQL database from the A8 via FujiNet would be super useful. And the basic authentication in the proxy was a smart move, especially since SQLite doesn't have user authentication compiled in by default.

[BradleyReed]

12 hours ago, BradleyReed said:

Hi there. I like your thinking about handling larger datasets. Using a remote SQL database from the A8 via FujiNet would be super useful. And the basic authentication in the proxy was a smart move, especially since SQLite doesn't have user authentication compiled in by default.

Oh, and if you're still into using proxies for market research, check out  https://soax.com/market-research They have great resources and guides on how to effectively use proxies for market research. Keep us posted on the progress of your project!