StoreBoss goes No SQL!

local storage
You can see the code in action here

I took a bit of a dive into IndexedDB today.  Talk about the callback shuffle, but I can see why they designed it that way.

It is going to take a lot of study and JS prototyping, but I think I have an idea how I might be able to develop an app on the desktop that does not need to use to use node or npm in order to do its job.

I’m certainly not the first person to think that IndexedDB might be useful in that regard.

With that in mind, I “registered” an SQL query into local storage.

Initially, I was thinking of Local Storage as a mini repository of useful queries that could run against SQLite.

Then I began to think… why not ditch SQLLite entirely?

Why depend on a bloated server/binding npm-node apparatus to persist simple e-commerce transactions, when IndexedDB (IDB) already provides this capability in a browser?  When I can just use JS to access my data locally in a fast and secure way….

What is the sense of implementing StoreBoss using third party (who are these Githubbers anyway, and will they still be supporting their buggy plugins tomorrow?) adapters over which I have no control whatsoever and cannot have a reasonable expectation of being able to rely on long-term?  Why not build StoreBoss around the free, and W3C controlled IDB, but with convenient wrappers to suit?

If the transactional data in Stripe can be stored in a practical way in IDB — which itself is built on the concept of a transaction —  and if what is of primary importance to a real-world store owner is the notion of a transaction (or Sale, or Order: call it what you will), then IDBCursor looks like it might play an alluring role in making this metaphor work.

But let’s be clear.

What I mean by a transaction is a retail event that took place at some point that is already in the past.  For example, the sale of a tee-shirt.  Such an event has temporal attributes, as well as other description, such as value.  This event may also be rolled back, by, say, a refund.

This is not precisely what is meant by transaction in IDB.

What IDB means by a transaction ae notions of db integrity when a record that is inserted or deleted in the database in real-time and maybe something goes wrong.

These are two very different things — particularly since StoreBoss is an analytic system, that is to say, a means of reviewing and analyzing virtual events that have taken place in external, production systems — such as WooCommerce, Stripe, or a bank’s mainframe batch processing of merchant DDAs.

Deep dives coming up, but first, a simple example of how you can store a SQL query in the browser.

Interesting how Chrome complains about this, while Opera and FF don’t — providing you allow cookies to be set.  Chrome forces you to allow third-party cookies, for reasons I cannot fathom at the moment. but will have to examine more closely down the road, as I do a forensic analysis of security in IDB.

The gallery below is a trivial proof that a query can readily be stored in a kind of query Filofax.

No so trivial is that if I can find a way to hierarchically associate “queries” by store (in an IDB sense), then I might have something.  IMS was before my time, but I’m going to see if I can learn something from old school data sublanguages.

This slideshow requires JavaScript.

 

Advertisements

ODBC bindings for SQLite/Node.js

 

html page
The birth of StoreBoss

So here’s the latest on my plan to come up with the easiest, simplest way to write an intuitive, nice looking Woo-related browser app that’s able to connect in a very simple way to a local datastore on the desktop.

There is only one problem: unlike, say, C, JS has no built-in file I/O capabilities.

In the case of wanting to access SQLite, you have to use SQLite3 + node + npm,  All this to accomplish what can be done in one line of code in C or a C++ program that uses ODBC to connect to SQL Server.

And if you ever had to write a JS script that uses SQLite3, you will soon begin to rue the absolutely maddening error-prone tedium of the unnecessary complexities of error handling when using SQLite3.

All I want to be able to tell JS to (1) connect to SQLite then (2) open a DB then (3) retrieve data from a table.

This is not exactly rocket science. I don’t think the user would mind waiting for a split second while a few hundred rows are retrieved in blocking, synchronous mode.

But… no.

Instead of just being able to connect to SQLite synchronously, as you would if you were writing your app in C, you’re forced to deal with SQLite3 + node.js + npm in order to access SQLite.

Adding all these massive dependencies (over which you have little control, results in a situation where you have no idea what is really going on on your machine behind the scenes): massive security hole, obviously.

Why on earth was SQLite3 not built to also support synchronous I/O?

After all, remember the SQLite mantra: “open-sourcezero-configurationself-containedstand-alone… ” etc etc?

And remember, too, that the intended number of simultaneous end users is exactly 1. In this scenario, it is RIDICULOUS to be forced to make asynchronous calls to a local db.

Plus which, when it comes to SQLite3, does it ever mentioned (gulp)

MUTEX THRASHING

in the documentation? Well, does it, punk?

This NJS (NPM + JS + SQLITE3) Rube Golberg jive is the antithesis of that stated SQLite ideal, or am I wrong?

 

ruth golberg stamp

Then, I thought… why not ODBC?

For a moment, I allowed myself to fondly remember those MS Access days wayback in the 90s when I was able to use ADO to grab data from almost any source, no fuss, no muss, and thank you very much.

My mood began to turn increasingly dark, at the unfairness of Life, as I googled frantically.  Then, the clouds parted, and I found… this.

sqlite odbc driver

I found it!

An ODBC driver that’s MIT licensed and recommended by no less a reputable source than Microsoft.

I quickly added it to my ODBC data sources in the Administrative Tools of the Control Panel in Windows.

Libre Base

Then I tested it with Libre Base.  It worked, with some minor fiddling (see below), like a charm.

Then I tested it again, this time with Microsoft Power BI (which I noticed, by the way, is developing connectivity to Stripe as a data source).

Power BI connect

That worked fine, also.

So the driver ran as advertised.

But I was not out of the woods yet.

Since Javascript cannot work with ODBC directly for a variety of technical and security reasons, I had to find the….

MISSING LINK.

And I did.

On Github.

Here.

So all I had to do now is figure out how to get my JS StoreBoss DB function library to work like this.

ODBC bindings for node

Now that is what I call beautiful. Except that it turned out to be a really bad idea (see Postword below).

Postword:  After installing this complex JS chromium runtime extension (which requires you to use an elevated Admin CLI), I could not get the example code to work, and left a call for help in its git page.  I say bag this native extension stuff; too much of a headache. But if you can follow what Gabriel Schulhof says about native add-ons in this YouTube bid, please leave a comment!

UPDATE #1:  After several re-installs (since this native extension trashed my node/npm setup) of npm + node + SQLite3, I was able — finally! — to get node + sqlite3 to work together.  I tested this by entered my js statements into the node command line, one by one  Great way to debug connectivity issues to SQLite via SQLite 3, when you are first setting things up, btw.  End result: I was able to connect to my sandbox db on SQLite, and output a result set from a table I had created earlier and populated using DB Browser.  Tomorrow I will try to get node to run a simple .js file that selects data from SQLite and displays it in the console asynchronously. Meanwhile, time to study the Node.js chapter in Eloquent Javascript. The guy who wrote this classic has deep reservations about node, btw.

UPDATE #2:  Hello, hello.  What’s this?  Let’s first look at the Trouble Shooting guide.  On Windows, as opposed to UNIX derivatives, things are more complicated (as always).  Let’s see… hmmm.

  1. Start an Admin PowerShell: Right-click the start icon, then pick Windows PowerShell (Admin)
  2. Install both vs2015 and vs2017 libraries. Each line will take ~5-10 minutes.
    npm install --global --production --vs2015 --add-python-to-path windows-build-tools
    npm install --global --production --add-python-to-path windows-build-tools node-gyp

That is what I had to with the native extension, and it trashed my setup.  I’ll not be installing anything again that forces me to use Admin privileges, so bag that.  Too dangerous, and what if an end-user has to change his or her machine’s configuration to get this package to run?  StoreBoss would be dead in the water. Troubling issues page, too.

UPDATE #3  The odd thing is, Javascript is actually synchronous at its core.  Promises and Callbacks were contortions that were added later, to avoid code having to wait for things like  long-running db queries to return results. Async/Await is Promises syntactic sugar that aims to make this mess more human-readable.  See Hopehne articles here and here, with lots of useful background reading references included.

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!