Proposed feature changes

[alasql]

Proposed feature changes

[PotOfCoffee2Go]

@mathiasrw - Thanks for your continued interest and support! - It is my wish to ultimately change that -develop in the version to -production :)

---

Good snag on my .xls record count issue - thx!

Xls thing: as default alasql will assume first row is header. Could that have any thing to do with it?

My counting of the records did not take in account the header (or lack of) record, obviously the count changes depending on if the header is present or not. That was it!

---

sql format - recommend depreciate

Would anyone be adverse to depreciating the SQL format from ala file in/out for now?
Unless someone can show a working flow (which is doubtful, since I can find no way to send the required table name to it) it think should be depreciated.

If the consensus is yes, I will post a issue to add sql format support as a feature and see if we get any thumbs up.

Please advise
@PotOfCoffee2Go

[mathiasrw]

As long as we change the sem ver. version accordingly depreciating a function makes good sense - if that does not serve the philosophy any more.

When you PR to develop I will make sure it gets pushed to master and npm repos as version "1.0.0" to indicate the maturity your test and development have given this plugin

[mathiasrw]

Thank you for very well described input. It all sounds as good inputs.

At the moment we dont have much time, but we would love to receive a Pull request and promise to get it to npm as soon as its merged :)

[PotOfCoffee2Go]

Update of work on Issue #7

I noticed that the default git branch is develop. That is the branch that I am using as a base. Hopefully, that is correct!?

Checked items are complete - most of what remains is addressing unsatisfactory results from the Flow Test Suite that I have created. Some issues do not pertain to this issue - but want to fix things so have successful test results.

The rest of this post is TLTR; - but contains current progress.

---

Implement msg.filename to be passed into Ala file in/out nodes to work as drop-in replacement for the core file in/out nodes.

• ala file in/out - ability to pass name of in/out filename in msg.filename.

While building flows that use ala file in/out - it is handy to have the node-red editor inform devs that the payload contains data and display name of the file read/output.

• Check, display, and insure the filename and payload objects are defined upon node entry.

The ala file out is shown before the ala file in node with-in the node-red palette - should be after ala file in.

• Reorder in/out scripts in ala-file.html so ala file in displays before ala file out in the node-red editor palette.

Visually, the ala file out should have the alasql icon on the right side of the node display. By convention, nodes with with an input connector but no output connector (an output node) normally locates the icon on the right side of the node display.

• Move the alasql icon in ala file out node from the left to the right side of the node display

Node background colors for file in/out in the node-red editor palette should be the same. The alasql node background (under advanced tab) to remain a slightly darker color to be visually different from the ala file nodes.

• Make both ala file in/out nodes to the same background color.

During development, the node-red editor should display the filename of the file read or written.

• Display a status message on the editor showing the filename ala file in/out read/wrote.

---

Additional items discovered

The ./icon/alasql.png icon is 75x75. Node-red requires an aspect ratio of 2:3 to display icon properly in the node when on the editor canvas. Paste, postion, and merge the current image onto a transparent image sized 75x113 thus creating a 2:3 aspect ratio.

• Copy ./icon/alasql.png to ./icon/alasql_solid.png
• Change alasql.png to 2:3 aspect ratio (using transparency) to display correctly on editor canvas

Add logic for the ala file in/out to process the node-red .on('close', ...) event to clean up and remove the status info when the Deploy button is pressed.

• Cleanup when nodes are closed.

---

Flow Test Suite

Create a set of test flows to verify changes to ala file in/out are working as expected.

• Create a flows directory in the project directory
• Comment nodes describing flow usage - getting there - slow but sure
• Create and export following test flows to ./flows/alafile.json
  • Setting global variable that contains the working directory path
  • Error Handler
  • Status Handler
  • Verify display of filepath under node
    • Does not display the extension - append .ext to filename displayed
  • Test File name configuration parameter works
  • Test File name passed in via msg.filename works
  • Error displays when No filename given in either msg.filename or configuration.
  • Error displays when No msg.payload
  • Flow that displays record counts
  • Test all file formats (create output then input them back)
    • xlsx
    • xls (record counts do not match)
    • csv
    • json
    • tsv (file extension naming issues - see below)
    • txt
    • sql (fails - with alasql error)

Test results

XLS - Test flows revealed that .xls files produced then read by alasql are missing a record. This is probably a bug in alasql, will investigate further.

• Investigate record count discrepancy in .xls files and open issue (and fix/ PR) with alasql.

TSV - The alasql option autoExt: true (which is the default) creates format TSV with the extension .tab :( Since all other formats are named the same as the extension - change TSV to TAB in the format dropdown.

• Change the format TSV dropdown text to TAB.
• Add logic to submit to alasql 'INTO TSV({filepath})' when format is TAB

SQL - ala file out gets "Error: Table for INSERT TO is not defined." I will consult the alasql documentation about the SQL format which (unlike the other formats) I assume requires a table name. Will figure out a way to pass the table name

• Pass table name required by alasql to produce the SQL insert into
• Dynamically show Table name config parameter when format SQL selected
• Add error checking if the table name is not passed when SQL format
• Logic to handle the SQL format special requirements

Misc

Need to push changes to my fork and directions to clone/install my fork for review

• Squash commits into single commit
• Push to https://github.com/PotOfCoffee2Go/node-red-contrib-alasql
• Directions to create local node-red user directory
• Directions to install PotOfCoffee2Go/node-red-contrib-alasql in user directory

Change version in package.json from 0.2.3-develop to ??? (please advise)

• Update node-red-contrib-alasql version
• Add myself as a contributor
• Submit Pull Request

[PotOfCoffee2Go]

Hi @mathiasrw ,

Have completed most of items on this issue. Am also implementing node-red flows to test released of alasql nodes, which is still a work in progress; delaying this release. Can expect a PR in a week or two.

SQL Format issues

The issue with the sql format is going to be a little involved since the current ala file in/out nodes use a high level SELECT INTO command - which does not have an option parameter - required to provide the TABLE NAME. To implement the sql format would require a lower level call to alasql.js - see alasql.into.sql to pass the table name. Will create another issue at a later date to address that in/out that format.

XLS Format issues

Have still not investigated the differing record count on the out then in of the .xls format. Again will generate another issue for that. I will dog it down in alasql.js later.

Kick the tires

If you wish to review what I got (assuming you have node-red -g , git, nodejs, npm, installed) :

git clone https://github.com/PotOfCoffee2Go/node-red-contrib-alasql.git
cd node-red-contrib-alasql
git checkout --track origin/poc2go
npm install
npm test

A node-red-contrib-alasql/test/flotsam directory is created containing a node-red user directory with a set of flows you can use to test the new ala file in/out nodes.

To fire up the node-red server on Linux

test/startup.sh

Or on Windows

npm link & test\startup.bat & cd ..\..\..

@PotOfCoffee2Go

[mathiasrw]

Most well documented issue/future PR ever.

I really like your testsuite. We only lightly wrapped AlaSQL but never really used node-red on production - so it's so nice to see all the thoughts you are putting into making it better!

Input:

• Xls thing: as default alasql will assume first row is header. Could that have any thing to do with it?

[mathiasrw]

And develop is the correct branch to clone and PR as we use the git-flow work flow

[mathiasrw]

The tab/tsv thing is an AlaSQL thing. Easy to be fixed. Please make an issue (or PR) in the alasql repo