Custom Functions

[lvca]

Discussed in https://github.com/ArcadeData/arcadedb/discussions/281

^{Originally posted by lvca January 13, 2022} OrientDB supports custom functions written in any language supported by the JVM (mostly Javascript using Nashorn), so other DBMSs allow the user to create their own functions you can call from the query language or HTTP.

In ArcadeDB we could support this feature, by using the underlying advanced GraalVM infrastructure that allows to compile and execute multiple scripting languages on top of the JVM. We did this job already with Arcade Trader (https://arcadetrader.com) with a minimal IDE to edit the functions, syntax highlight, inline documentation, etc. We could totally port that piece of IDE into Studio.

Requirements in order of implementation and status update:

• [x] Custom Functions can be written in multiple languages, Javascript and Java must be supported in the first release
• [x] Java and Polyglot query engines must protect from the execution of any Java classes
• [x] The execution of a function should be limited with a timeout. This avoids also blocking by writing a loop such as while(true);
• [x] New API to register functions. Examples:
• Javascript database.getSchema().registerFunction("js", "sum", "function sum(a,b){ return a + b; }"); and
• Java database.getSchema().registerFunction("java", "sum", "org.acme.Functions::sum");
• [ ] Create SQL statements to register and unregister functions
• [ ] Allow executing a SQL script to make the registration easy at startup with a new setting arcadedb.startupScript. Example: -Darcadedb.startupScript=/temp/myscript.sql
• [ ] Custom Functions should be automatically published on HTTP if requested by the user (in the IDE a select field with ["no published", "HTTP get", "HTTP post"])
• [ ] Create a tutorial about writing a recommendation system by using a custom function

They are not Stored Procedures, because they are not stored. If you want to use them, register in the database at startup or before using them. This avoids all the issues with where to store them (files, internet types/buckets?), security about who can write, read and execute them, replication, an editor from the studio, and more.