Add support for UDF-Skripts
Closed this issue · 1 comments
Background
Exasol database supports to create scripts. Scripts follow a similar concept as stored procedures in Oracle databases for example.
Exasol supports the following script types (see documentation for details):
- Scripting programs only support Lua, are executed via EXECUTE SCRIPT. See https://docs.exasol.com/db/latest/database_concepts/scripting.htm
- Already supported by TDBJ ✅
- UDF scripts can be written in Lua, Python, Java etc and are used in SELECT statements, see https://docs.exasol.com/db/latest/database_concepts/udf_scripts.htm
- Already supported by TDBJ ✅
- Adapter scripts can be written in Lua, Python, Java etc and are used in virtual schemas, see https://docs.exasol.com/db/latest/database_concepts/virtual_schemas.htm
- Already supported by TDBJ ✅
Exasol UDF scripts can use different programming languages: for example lua, python, java. Depending on the language different SQL directives are required and different parameters, and configuration options apply.
User defined functions (UDF) can have different results
- return a single scalar value, e.g. row count
- emit an SQL table with rows and columns
Situation
At the moment createScript and createScriptBuilder can only be used to register Lua Scripting programs.
We need support for UDF scripts with language selection to also be able to use this feature for Java, Python and R. This is analogous to the createAdapterScriptBuider.
Please note virtual schemas are often in Java and are using so-called "adapter scripts" which are something different as usual UDF scripts, see https://docs.exasol.com/db/latest/sql/create_script.htm. However some of the document-virtual-schemas are using adapter scripts as well as SET scripts in a kind of two-fold architecture.
Additional Enhancements
Currently test-db-builder-java does not support to specify the data types of the columns for emit results.
Acceptance Criteria
- API users can create UDF scripts.
- API users can select other languages than Lua for creating UDF scripts.
- TDBJ generates correct SQL for the selected script language
Language support is not necessary for methods createScriptBuilder() and createScript() as "Scripting programs" only support language Lua. SET and SCALAR scripts already support other languages.