xapix-io/axel-f

Friendly language for data manipulation inspired by Microsoft Excel ™

axel-f's Issues

Field reference with space results in exception

According to the docs, I can use a field reference with a space by wrapping into #''. I tried this and received an exception:

((axel-f/compile "SUM(1, #'my var')")
  {"my var" 3})
Execution error (ExceptionInfo) at axel_f.runtime$eval3129$fn__3132/doInvoke (runtime.cljc:230).
Error in function `SUM`

Simplify parser

Output from the parser should be in sexp format

LEN method returns wrong result with an empty values

I have a problem:

const context = {
  name: null
}
expect(axel.compile('LEN(name)')(context)).toEqual(0) // error here with result - 4

I found that it is because of converting of nil values to "NULL" string instead of "" an empty string in case of excel functions which accepts string as an argument.

I've found that the excel-string function used in LEN func implementation and guessing that we should add an additional (if (nil? text) "" text) to avoid nil in excel-string function call.

What should I do?
It would be nice if you adjust the LEN function to accept nil values as an "" empty string instead of "NULL" string.

Expand available functions list

• SUM
• IF
• MIN
• MAX
• ROUND
• COUNT
• CONCATENATE
• AVERAGE
• AND
• OR

tbc.

var lookup doesn't work with `#` in identifier

(let [u "localhost-api-users#cache-lookup"]
  (axel-f/run u {u {:body 12}})) => nil

Autocomplete function

Remove internal references from functions description

How to create a new function just as an alias of one that already exists.

This is not a bug request, it is a feature request.

Due to language differences, o platforms to run Spreadsheets functions, I would like to ask about to implement an "alias mechanism" , for example, I need the function CONCAT(...) which is exactly the same as CONCATENATE, having a way to aliasing the function name, would make easier to run AXEL-F in different context, like Google Spreadsheets.

I would like to try to implement it, but first I want some pointers, o opinion about where in the code should be the best place to add this feature.

Determine whether a variable is a local or global variable

The WITH function is quite useful. I'd be interested in a way to determine if a variable used in a formula is a local variable (i.e., defined using WITH) or a global variable (i.e., expected to be passed in when the formula function is called).

For example, take this formula "WITH(a, 1, a + b)". a would be a local variable and b would be a global variable.

Thinking the best way to do this for now is to use the ast, collecting local variables defined with WITH.

Add ability to write custom functions with JS

I would like to have something like

const functions = {
    JOIN: (arr) => arr.join(', ')
}
const parser = compile('COUNT({1, 2, 3`, functions)({})
/// 1, 2, 3

geo functions

add functions to easily work with lat/lang, something like:

• convert lat/long into geo object
• convert geo object into lat/long
• calculate distance between two geo objects
• add distance to geo objects

Remove chesire from runtime dependencies

Cheshire is currently a runtime dependency of axel-f. It is only used in the namespace axel-f.excel.json. Because Cheshire is such a massive dependency, bringing in Jackson, it'd be great to make it optional. If a user requires the axel-f.excel.json namespace, they will need to have cheshire on their classpath.

update wiki to reflect namespaces + new functions

see changes in #44 and update docs

Connect wiki pages with cljdoc

Priority of operators

Simple arithmetic operators

• *,/ are performed before operators +,-
• Expressions in parentheses are executed before all other operators

Examples:
1 + 3 * 7 -> 22
(1 + 3) * 7 -> 28

Order of operations incorrect

This answer should be 0.3 and it is 0.09999999999999998.

((axel-f/compile "MAX(0.0, (0.6 * (1.0 - 0.5)))"))
=> 0.09999999999999998

Multi-line formulas

Are multi-line formulas something you would be interested in supporting?

MAP doesn't accept function body as it used to before 2.0

Possible blocker:

(let [f "WITH(a, {11,12,13}, i, {0,1,2}, MAP(FN(_), i))"]
  ((compile f) {})) -> ({} {} {})

(let [f "WITH(a, {11,12,13}, i, {0,1,2}, MAP(a[_], i))"]
  ((compile f) {})) -> NPE ( core.clj: 2753  clojure.core/map/fn)

(let [f "WITH(a, {11,12,13}, i, {0,1,2}, MAP(FN(a[_]), i))"]
  ((compile f) {})) -> (nil nil nil)

Also, WITH definition should fail after f FN here i think:

(let [f "WITH(a, {11,12,13}, f, FN(a[_]) i , {0,1,2}, a)"]
  ((compile f) {})) -> (11 12 13)

Formula with just a constant inside can not be precompiled and executed.

For the constant like number ("1", "2.3"), string ("'foo'", ""bar""), boolean ("true", "False") precompilation return incorrect structure which confuses runner.

(run (compile "'foo'")) ;; => nil
(run (compile "\"bar\"")) ;; => nil

Implement excel text functions

• CLEAN

"Removes all nonprintable characters from text. Use CLEAN on text imported from other applications that contains characters that may not print with your operating system. For example, you can use CLEAN to remove some low-level computer code that is frequently at the beginning and end of data files and cannot be printed."

• CODE

"Returns a numeric code for the first character in a text string."

• EXACT

"Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. EXACT is case-sensitive but ignores formatting differences."

• FIND

"FIND locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string."

• FIXED

"Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text."

• LEFT

"LEFT returns the first character or characters in a text string, based on the number of characters you specify."

• LEN

"LEN returns the number of characters in a text string."

• LOWER

"Converts all uppercase letters in a text string to lowercase."

• MID

"MID returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify."

• NUMBERVALUE

"Converts text to a number"

• PROPER

"Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters."

• REGEXEXTRACT

• REGEXREPLACE

• REGEXMATCH

• REPLACE

"REPLACE replaces part of a text string, based on the number of characters you specify, with a different text string."

• REPT

"Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string."

• RIGHT

"RIGHT returns the last character or characters in a text string, based on the number of characters you specify."

• SEARCH

• SPLIT

• SUBSTITUTE

"Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string."

• TRIM

"Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing."

• UPPER

"Converts text to uppercase."

How to generate using FN

I got how to generate subarrays

MAP(FN(fr, {fr.name, fr.color}), fruits[*])
// [["apple", "red"], ["banana", "yellow"], ["orange", "orange"], ["grape", "purple"], ["pear", "green"]]

But I need

[{label: red, value: 'apple'}, {label: yellow, value: 'banana'}]

introduce `.` as divider for namespace and name for function names

Integration with Javelin, examples

https://github.com/hoplon/javelin

SEARCH method issue

The problem is the SEARCH method is not working as described in wiki here, or something is missed - https://github.com/xapix-io/axel-f/wiki/SEARCH

When I try to use this method I get the 1 result even if the arg1 string was not found in arg2 string..

Here is an example:

axel.compile('SEARCH("text", "nothing")')({}) // returns 1 but should be null here of #VALUE! error

Publish / opensource

prepare JS release

Getting vals from hashmaps with squarebrackets doesn't work in 2.0

possible blocker, used to work before:

(let [f "WITH(foo, OBJECT.NEW({{'foo', 1}}), k, 'foo', foo[k])"]
  ((compile f) {})) -> nil

Integration with firebird

ROUND should convert to a long, not an int

The ROUND function coerces its return value into an int (source). To match Java's Math/round, it really should coerce into a long.

Initial parser for excel-like expressions by using [instaparse](https://github.com/Engelberg/instaparse)

instaparse

We should cover excel math, string and list functions; operators like +, -, /, etc.

Groomed out from xapix-io/firebird-next#128

FILTER ignores variables from the context

    const context = {
      prices: [
        { model: 'BMW', price: 60000 },
        { model: 'Audi', price: 50000 }
      ],
      selectedModel: 'BMW'
    }
    expect(axel.compile('FILTER(_.model = "BMW", prices)')(context)).toEqual([{ model: 'BMW', price: 60000 }])
    expect(axel.compile('FILTER(_.model = selectedModel, prices)')(context)).toEqual([{ model: 'BMW', price: 60000 }])

The first one works fine, the second doesn't work :(

What do I do wrong?

More detailed exceptions during compile time.

Cannot use a negative number

I'm using axel-f 2.0.3. Negative numbers in formulas result in a NPE.

((axel-f/compile "1 + -1") {})
Execution error (NullPointerException) at axel-f.excel.operators/sub* (operators.cljc:21).
null

((axel-f/compile "1 - 1") {})
=> 0

((axel-f/compile "1 * -1") {})
Execution error (NullPointerException) at axel-f.excel.operators/mult* (operators.cljc:31).
null

((axel-f/compile "1 / -1") {})
Execution error (NullPointerException) at axel-f.excel.operators/div* (operators.cljc:40).
null

devision clj vs cljs: ratio

server side:

(let [formula "14325 / 241"
      f (axel-f/compile formula {})]
  (f {})) => 14325/241

browser:

14325 / 241 => 59.439834024896264

Relax rule for SYMBOL_FIELD in object references

NULL is considered a var

analyze considers NULL a variable, which seems incorrect to me.

(axel-f/analyze "NULL")
=> {:vars (("NULL"))}

I would've expected {:vars ()} as the result. Is this a bug or an expected behavior?

Analyzer does not find variables in parentheses

(require '[axel-f.core :as axel-f])
(axel-f/analyze "(example + 1)")
=> {:vars ()}

I would've expected the same result as no parentheses.

(axel-f/analyze "example + 1")
=> {:vars (("example"))}

object references can not parse another object references inside of array references

Example:

"foo.bar[foo.baz]"

soap-ws parts are served from insecure repo

Leiningen and boot complains about dependencies coming from non-https repositories.

• Discuss with soap-ws author about possibilities to move dependencies to secure storage.
• Reimplement features from soap-ws with the only clojure
  • Collect all the schema files from given WSDL
  • Parse data type definitions from schema files
  • Build empty messages same way as soap-ws

Error handling during formula execution

UNIQUE method is not working with axel-f

The problem is the UNIQUE method is not working as described in wiki here - https://github.com/xapix-io/axel-f/wiki/UNIQUE

When I try to use this method I get Unknown function UNIQUE error.

Here is an example:

const context = {
  names: ['Mike', 'John', 'Max', 'Mike', 'Max']
}
expect(axel.compile('UNIQUE(names)')(context)).toEqual(['Mike', 'John', 'Max']) // Unknown function UNIQUE here

Object functions

"Wrong argument type" exceptions in runtime (compile time?)

base64 encoding / decoding functions

Context transfer and selectors

• run function must accept object as a context of execution.
• select operators like foo.bar.baz should select correct value from execution context

Vectors as arguments

Some functions like SUM, LEN or CONCATENATE accept vectors as an arguments.

Example:
Context:

{:foo {:bar {:baz [4 5 6]}}}

SUM(1, foo.bar.baz, 5)
LEN(foo.bar.baz)
LEN(["x", "c", "v"])