Storing data in your browser using SQL + pure JS

WEB SQL

This is is the world as it might have been, before certain unenlightened beings at Mozilla and MS decided peremptorily that developers should not be allowed to run SQL and persist large amounts of data via their browsers.

Well, the domain Webquery.org is still available! Maybe a few developers who have had enough of the npm / node / SQLite3 nonsense can band together and convince Opera to start a W3C revolution.

Meanwhile, click on the image to run the pen, then read the sad history of how SQL was killed during the browser wars  here.  The link and example code (that I modified slightly, since the original console bit didn’t work properly, and I am not sure if this is because the spec has been unsupported for a decade) were written by the brilliant Nolan Lawson.  Make sure you’re using Opera before heading to my Codepen.

See how simple and intuitive this all could have been?

Imagine wrapping all the WebSQL async callback funy business in JS functions and exposing only a simple-to-use SQL API to the data analyst, who would be able to use parameterized SQL on the fly, querying data on their local machine, without worrying about getting trapped in the land of a million parentheses — the way it was before the Web mutilated SQL!

“Web Query” is exactly how I would have liked to implement StoreBoss.

No npm.

No node.js.

No dance of a zillion nested parentheses and brackets.

Just vanilla JS and pure SQL in its original elegant nobility, with disk space on my comp where I securely encrypt my data and restrict access to it via icalcs.

I’ll have to try out the Dexie wrapper and see if I can up with something workable, using the brain-damaged IndexedDB.  I have some ideas already in that regard.

Advertisements

StoreBoss… the plugin!

storeboss(tm).io
StoreBoss: first sighting…

Okay… so it’s just a PHP file with header info, for now, but I think it looks right at home in WP admin.

I did some more data modeling and source data checking today to make sure that the “tick-and-tie” algorithm will work, given the test source data available from WC, Stripe, and Bank of America.

Much of my day was spent wrestling with the question as to whether I should implement part of StoreBoss on a desktop. I discounted MS Access and/or Libre Base as unsatisfactory options for a variety of technical and functional reasons.

So what other choices do I have?

For example, if I use SQLite as the DB engine, what language should I use for the GUI part?

Python has been all the rage for data analysis for some time, so Python/SQLite is an obvious choice.

But…

  1. I don’t know Python;
  2. I don’t really want to hassle with Tk or other Python frameworks to produce a desktop GUI that will probably look amateurish;
  3. I don’t care for some of the limitations of SQLite.

On the other hand…

  1. I am stunned — stunned! — at what PHP programmers have to go through if they wish to debug their apps by stepping through code; and;
  2. I want to avoid, as much as possible, being overly ensnared by the WP way of doing things;
  3. You can do a lot of cool thing in terms of data visualization with existing JS libraries.

So it looks like the split might be  SQL + basic PHP for extracting and prepping WC data for querying by parameterized stored procedures on the prod WP server. By focusing on doing much of this work via SQL, I can quickly create a few custom tables in WP, populate these via SQL, while providing value add transactional information via a bare-bones PHP script that (safely) calls the queries, then display results using JS razzmatazz.

StoreBoss would also provide a download facility to either a JS/SQLite SPA app (again, this is a lot of work for one person, especially for someone who is more of a data modeler/architect than a down-in-the-trenches developer who eats Laravel magical callbacks for breakfast) or EasyMorph (a bit on the expensive side, but fits right into the code-free data analysis mantra) or MS Power BI (more achievable, in a shorter time frame, with fewer GUI headaches, despite the learning curve) for sophisticated data analytics on the desktop.

This type of hybrid architecture is a simpler cousin to the sort of Big Data deployments ably discussed by Lockwood Lyon here.

I am eagerly awaiting, by the way, the short term release roadmap that is planned for Easymorph.

The “Actions for REST APIs” might allow for downloading of data from WC’s headless interface. The ability to pipeline data to Power BI in December ’19 is pretty exciting too — although its (meaning Power BI’s) notions of what constitutes a DSS data store and its associated BASE standards compliance requires further investigation, ditto the pros and cons of time spent mastering non-portable, non-transferable MS data manipulation sub-languages.

We’ll get to all that down the road. But for now, plain ol’ StoreBoss will live in the open-source WP cloud.

Initially, users will have to manually upload their bank file transaction data, and that’s a drag.

But that’s how it has to be, at least for now.

B of A (which is the bank I use for the e-commerce site I run) would not accept a wildcard SSL certificate comparable to the one used by SiteGround for my site:  so I would not be able to programmatically access B of A’s CashPro Account Info API from my hosted SG server.

I’ll have to figure out how to make the uploading (of banking data) to StoreBoss’s staging table as simple as possible;  there might be existing, open-source software out there that might be of help in that regard.

Maybe I can just lay out a sample Libre Calc template and my prospective customers can do the work of mapping CSV fields to the DB columns.  If explained clearly, and it’s not too complicated a file structure, the payoff might be worth the tedious hassle. But I don’t like this part of the solution… so we shall see how all this pans out. At the very least, I should be able to develop reusable template mappings for the top 3 US bank transactional files, and have those available out of the box.

Speaking of reusability, one of the interesting things (to me, at least) of this proposed StoreBoss architecture is that almost none of the code touches or relies on WP core.  What this means is that I can use JS libraries such as Datatables (which in turn depend on JQuery) that will greatly reduce the coding I have to come up with attractive tables for StoreBoss, and even if I end using other libraries (say for charting or date-fns for temporal calculations) that carry MIT licensing, it won’t matter, since I’m hitting the database the directly, and not depending directly on WP/ WC.  I will have to verify this with plug-in gods at WP, but I think I’m right.  Why re-invent the wheel?

Meanwhile, I am close to finalizing the sb_transaction_event table, and ran a few test analytics SQL queries against real-world data.

Boy was I unpleasantly surprised at some of the things I found! (Hint: certain online CC processors appear to charge over 3% in some cases, which I think is pretty rich, considering that my client’s in-store credit card processor charges less than 2).

Woah.

StoreBoss can’t come soon enough.

It shouldn’t be only big corporations that can shell out for automated systems that check this sort of thing.

What about the SMB WooCommerce store owner who can’t afford fancy cloud-based systems and an army of accountants and data analysts?

If smaller scale WC store owners are doing any kind of volume, you can bet there’s money probably slipping through the cracks. And even if there isn’t, would it be nice to know for sure?

Time to get cracking!