hawkfish/olapscript

An OLAP engine for Google App Script

olapscript's Issues

how to run the tests in /test?

They aren't themselves functions that can be run, and it's not clear how to wire them up to be able to run them.

typeerror when running testDuplicateNames

Running testDuplicateNames in olapscript-tests-and-utils and get the following error:

TypeError: Cannot read property 'map' of undefined
(anonymous) @ olapscript.gs:1512
Table.groupby @ olapscript.gs:1511
testDuplicateNames @ olapscript-tests-and-utils.gs:136

Handle Duplicate column names

Unlike database tables, Sheets can have duplicate column names. We should handle this gracefully by numbering the duplicates.

Test Framework

Build out a mocha test framework.

Include formatting data in column loading

Remove quotes from RefExpr.alias

This is more of a toSQL method.

Not referencing the column correctly, apparently

Please refer to the code in "ELR code.gs" - it's choking on the "where" clause and I'm having difficulty understanding why it doesn't like my RefExpr in my FuncExpr declaration. (I realize you would likely not implement this routine quite this way, but it's nevertheless a good exercise.)

query request

Suppose you had a table that contained the following fields:
timestamp, firstName, lastName, email, date, field1, field2, invalidTimestamp,

and you wanted to implement:
select firstName, lastName, email, date, field1, field2 where
firstName = <fname> and
lastName = <lname> and
email = <email> and
date = <some date> and
invalidTimestamp <hasn't been set...so is "", or is null, or whatever the best way to indicate an unset timestamp would be>

What would that query look like?

Table should provide a getColumn method to read out the selected data

table.selection.map(selidx => table.namespace["Counties"].data[selidx]);

testAssignmentUnnesting produces no output

Only the header row is produced.

something's funky in testOrderBy

Running testOrderBy in olapscript-tests-and-utils. The result is correct, but row 1 is blank on the output sheet.

FromSheet should allow complex headers

• Multi-row
• Detached from the data
• Missing (F1...)

ReferenceError: Unknown column: Email

Please take a look at the mailchimp example in the ".pol" account. Are lines 18 and 25 correct? If so, the code throws the error in the subject line. If not, please indicate what needs to be different.

Joins should support import column renaming

How would I code up the following:

SELECT <fields>
FROM tableLeft L
LEFT JOIN tableRight R
ON L.key = R.key
WHERE R.key IS NULL

?

Cast operations

We should add a cast function that takes a type as the second argument.

FromSheet should strip trailing spaces from column names

This can be confusing when column names don't match...

Implement Schema enumeration

Case statements

Supporting CASE includes handling short circuits, which can be valuable.

FromSheet should allow data selection that is not top left

RefExpr should look for the closest column name match

This is an insanely useful way to generate error messages ("Did you mean X?")

Basic SQL expression parser

If we ignore infix operators (+, AND, etc.) it is pretty straightforward to build an expression parser for SQL. The infix operations will need to be expressed as functions (e.g., AND(condition1, ...).

The main syntactic issue will just be that in SQL double quotes are used to delimit identifiers/references and single quotes are used for strings. So if an expression needs both, it will have to be written using JavaScript's triple-quote mechanism.

Add arithmetic functions

Most notably the ones used by an expression parser:

• plus (will also concatenate strings)
• minus
• times
• divide
• div (integer division)
• mod
• power
• negate (unary minus)

Table should provide toRows (dual to fromRows)

toSheet should only clear the range to be written

The more I think about it, the more I think that .toSheet shouldn't clear an entire sheet; it should just clear the range that will be written, even if that range always starts at A1. At any rate, the utility of .toSheet is limited because it clears the entire sheet. That said, clearing the entire sheet also seems like useful behavior, so maybe we want .toSheetRange in addition to .toSheet.

BTW, I'd call this a close relative of #79, rather than a dupe, though you might see that differently.

New code in Wiki needs testing

Found and fixed one error (missing square bracket), but is there a problem with

.where(new FuncExpr(Expr.isnull, args: [name2]))

? Apps Script seems to think so.

Performance

Copying an entire sheet runs quite slowly on a sheet with ~180 rows and ~80 columns. Two possible culprits are:

const header = sheet.getRange(1, 1, 1, lastColumn);
this.ordinals.forEach((name, i) => header.getCell(1, i+1).setValue(name));

and

this.ordinals.forEach((name, cid) => that.selection.forEach((selid, rid) => range.getCell(rid + 1, cid + 1).setValue(that.namespace[name].data[selid])));

Table.getRow() is 0-based

...which would be fine, but it retrieves the first data row, so there isn't a way to retrieve the header row, should you want it.

ToSheet should have options for locating the output

Is there a way to specify where in a sheet a particular block of data (anything from a cell, to a row, to a column, to a 2-d range) gets written? It looks like .tosheet always starts at A1.

I guess this is another way of asking if there's the equivalent of setRow(s) and setColumn(s) methods.

Implement UNION ALL

Add left/right aliases for probe/build

Probe and build are internal terms for the two sides of a join. It would be more user friendly to accept left and right as aliases for them.

Missing aggregates: MIN, MAX, ARRAY_AGG and STRING_AGG

There are probably some more but MIN/MAX are basic and the concatenation aggregates are really useful for spreadsheet munging and data cleaning.

please provide a way to configure up front so timezone defaults to <desired time zone>

(in my case, Pacific)

consider re(un)factoring

The easiest way to incorporate this code into an Apps Script project is as a library, since it only needs to be maintained in one place that way. (Granted, you forfeit the ability to step into routines in the debugger.)

With Apps Script, each call to a library routine needs to be prefaced by the name of that library. This means that, if there are four files for olapscript, an Apps Script developer needs to be aware of which of the four files a particular routine lives in, so they can preface it with the correct library name.

For that reason, it might be preferable to have the code live in one file (say, "olapscript"), so that any call could be made by just prefacing it with that one prefix.

Test GROUP BY

It doesn't work until it has been tested...

Can I refer to a column by its letter, rather than its header?

I have a situation in "Assign by Polling Place" sheet where I'm allowing up to five polling places to be assigned, but since the remaining four are optional, I only have a header name ("Polling place(s)") on the first column, column I. Can I refer to the subsequent columns by their letter, or do I have to name those columns?

ToSheet should drive column count

Create function library

Scalar functions should be members of Expr. For starters, space trimming and current date/time will do.

Rudimentary workflow tasks

We need a couple of these:

• Run tests on commit.
• Run tests to prevent merge
• Generate olapscript.gs artifact on merge instead of checking it in.

something's funky with limit

Using the testLimit code in olapscript-tests-and-utils:

.limit(5, 1) returned:
blank row
row 5
row 6
row 2
row 8

.limit (5, 5) returned:
row 8
row 10
row 9
row 11
row 12

.limit (5) returned:
blank row
row 5
row 6
row 2
row 8

please label all code that is node-related

Would be helpful to know exactly what code is specific to node.js and has no other function in the code base.

FromSheet should allow specifying the row count using specific column contents

Demonstrate how to do it. (Per Hawkfish)

getColumn() can return a column of the wrong length

If there is a table with unequal column lengths - e.g. Column A has 5 (non-header) rows and Column B has one (non-header) row - if I call getColumn() on column B, I'll get an array of length 5, with one value and four empty strings. Of course it isn't high priority, but it doesn't seem like it should be the expected behavior.

The expression normalisers should expand functions to FuncExpr

It is easy to check for an object being a function in JavaScript - it has a call method.

please document how to handle duplicate column names

Three columns named "title" would be referred to, in order, as "title," "title1" and "title2" - is that correct? Would be helpful to have that in the documentation.

Add CONTAINS

Requested string function.

Aggregates should have initialise methods and explicit states

The current architecture is rather hacky and doesn't look like regular expression nodes. Fixing this will make it possible to add aggregate modifiers such as ORDER BY and DISTINCT.

THis should be done before any new aggregates are created.

Comparison Functions

Since Javascript doesn't have functional versions of the comparison operators, the need to be added to Expr. These should include is(not)distinct and is(not)null

Add logical functions

This can be useful for reasoning about join predicates.

Apps Script Connectivity

Build out some tooling for pushing and pulling scripts.

Implement the BETWEEN function

In a sheet that had, say, columns called date1 and date2, is there a "between" operator to get all rows where <date> is between date1 and date2?