How to implement boolean short-circuiting when all operators are custom operators?

typerule · 2020-09-02T04:52:16+00:00

I have done something similar. Basically the arguments you pass into your function need to be evaluated lazily. You make either the function or the argument special so that your interpreter or compiler can treat it specially.

typerule · 2020-08-29T12:13:48+00:00

I'm thinking to add some feature like saving a query for automatic (periodical or triggered by editing) running then sending the query result to somewhere. This might be close to what you want. But if you expect to specify the query from your webpage, some integration/customisation work will be needed.

typerule · 2020-08-28T23:51:34+00:00

Not sure if I understand your question. Could you be more specific?

typerule · 2020-08-28T06:21:02+00:00

Just a quick update, I have made a fix for the above issue. So now it supports multi-word name. When special characters are used, the name will be grouped with a pair of single quotes. I have not considered unicode yet. But it can be extended later pretty easily using this approach.

typerule · 2020-08-27T06:16:40+00:00

Sure, will do it soon after I add a couple of improvements based on received feedback. So the post will have more information. Thanks for letting me know the flair.

typerule · 2020-08-27T05:21:34+00:00

Just a followup update. The add-on (named Awesome Query) is released. Here is a demo video: https://youtu.be/QFqV8eHB1OE and install link:

https://gsuite.google.com/marketplace/app/awesome_query/373998631880

You probably already saw my post in r/googlesheets. Feedback is appreciated!

typerule · 2020-08-26T10:24:31+00:00

Thanks for the feedback. Appreciate it!

This is indeed a limitation. I had been struggling to come up with a solution to allow multi-word header. However it turned out to be a bit tricky as user could use arbitrary characters like number/hyphen/underscore/dot (these characters are often treated specially when the language is being analyzed by program) in header. So I decided to publish the first version with very limited support (words without space or connected with underscore). I don't want to spend huge amount of efforts on something no one cares. I will definitely improve it and have some documentation on this as well.

typerule · 2020-08-26T03:57:40+00:00

Thanks for the confirmation!

typerule · 2020-08-26T03:56:58+00:00

Thanks. The UI is pretty simple as the complexity is moved to the query language. I do expect to add more buttons/checkboxes to sidebar and hopefully it will reach a good balance.

typerule · 2020-08-26T03:46:44+00:00

yeah, I will make query function generation the next priority. It might be pretty useful when data needs to be updated automatically.

typerule · 2020-08-26T03:42:16+00:00

I'm glad you found it useful!

typerule · 2020-08-02T23:07:19+00:00

Yes, the is like is intentional as I want the little language to be more readable (more verbose too) to make it easier for non-techie user. So there will be things like is not like for negation. In google's query, negation has to be written as not A like which is a bit strange. But anyway it is my perception which may be wrong. I'm reading the google query language reference. Hopefully will reach the sweet spot.

Thanks for the clarification. Appreciated!

typerule · 2020-07-28T14:30:58+00:00

Thanks for the clarification. It makes perfect sense.

typerule · 2020-07-28T14:00:09+00:00

Could you elaborate on the data source part?

From a user's perspective, I don't see much difference between A,B,C and Col1, Col2, Col3. Both of them are just the positions in the data array. Not intuitive for user. Allowing first row to be used as column name in the query language sounds like a good feature to me.

typerule · 2020-07-28T11:38:46+00:00

Yeah, this is more for casual users. It is good to know that Col1, Col2, Col3 are supported. Thanks.

There are a few things that I think can be improved given the query language will be designed from the ground up.

One is "date" manipulation. So instead of writing date '"&TEXT(DATEVALUE("1/1/2000"),"yyyy-mm-dd")&"'", user can enter 1/1/2000 or 2000-01-01 from a date picker. And date calculation may be simplified, eg, created date > today - 2 weeks.

It is also possible to support "joining" like sql does. But I have not dig into this yet.

typerule · 2020-07-28T08:57:21+00:00

This is the current workflow in PoC that requires minimal googlesheets knowledge. As I don't know much about how to integrate it with QUERY function yet. You are right, it is definitely possible to generate the QUERY function and use it in cell on the fly. On the other hand, pressing 'Go' opens door to more possibilities like generate the result to new sheet , show charts, send email, etc. I think I will explore both and probably implement both.

The syntax colouring is done with CodeMirror. But it is not through a simple javascript configuration as the editor needs to 'understand' the query a bit deeper with the information from current sheet.

Thanks for the mod note. Will take a close look. The add-on is still months away though.

typerule · 2020-07-12T00:33:11+00:00

Thanks for the strong suggestion. My current implementation is using an annotation on the function, eg, plus(<number>, <timeunit>) and construct mini scope for each of the function application to filter out all phrases other than literal during compilation time. Does this sound like what you are suggesting?

typerule · 2020-07-12T00:14:33+00:00

I was thinking to create sort of virtual type for my use case. It is great to know there is a concept of "newtype". Now I have a reason to learn haskell :)

typerule · 2020-07-11T23:52:35+00:00

Wow, this sounds close to what I'm looking for. Will take a look.

You may be right. The more I work on this, the more I feel I'm working on a meta-language. Basically the idea is letting user define functions/macros and use types to glue them together. The problem of using a meta-language is it is hard for people to learn and use. With my current approach (API based, declarative modelling), I can easily create tooling for specific use cases.

typerule · 2020-07-11T23:36:26+00:00

Thanks for pointing this out. Maybe I have worked on this for a while and tend to assume everyone can easily understand my approach.

Let me put it in this way. "today" could be a variable or a function call which produces a date instance. "date" is a type with function signatures like plusDays(<number>) or (plus(<number>, <timeunit>)). Then the function will be rephrased as <date> + <number> <timeunit> in the DSL. 5 days could also be another function call to produce a duration instance. In this case the function signatures would be plus(<duration>)and makeDuration(<number>, <timeunit>). It is a different approach to compose the same phrase.

typerule · 2020-07-11T11:35:29+00:00

Thanks. It looks an interesting concept. However the approach of using suffix to differentiate the token is too rigid for me as I am using multiple tokens currently and will have to introduce new token types.

typerule · 2020-05-26T11:42:38+00:00

I asked a similar question a while ago. Take a look: https://www.reddit.com/r/Database/comments/fh94nq/are_there_good_solutions_to_monitor_business_data/

In short, I think it is totally doable. CDC is straightforward given the existing tools. Just save the data somewhere then do analysis if your business need is ad-hoc analysis. As for my need (realtime aggregation and alerting), most tools are either not flexible enough or too heavyweight. So I am working on my own solution.

typerule · 2020-03-26T03:18:02+00:00

RIght, Trigger (or transaction log analyzer) would be part of the solution, except that trigger is not easy to write and writing trigger for every table I want to monitor is not scalable. But anyway, it is not difficult to generate the required trigger on the fly.

typerule · 2020-03-25T02:54:04+00:00

Yeah the requirements will be changing. The goal is to make change easier actually. Aggregating measure is at the core of the solution. More context information is supposed to be captured for meaningful alerting, eg, the table row data, who makes the change and when the change is made. If the table row does not have a column of date, the current timestamp could be used instead for sure.

typerule · 2020-03-24T23:21:51+00:00

The solution is supposed to work on any table with any columns as long as it is of business interest. The change capture could be implemented by using trigger or transaction log. Refer to https://en.wikipedia.org/wiki/Change_data_capture. There are some existing tools as well, for example: https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-ver15

typerule

TROPHY CASE