Gists Add new dyna function

Adding a new dyna function

Add a class property

Class meta::relational::tests::mapping::sqlFunction::model::domain::SqlFunctionDemo
{
  ... other properties
  myCustomValue: Integer[1]
}

Add a mapping - Map myCustomValue to a dyna function myCustomSQLFunction that will be invoked with a float and int

Mapping meta::relational::tests::mapping::sqlFunction::model::mapping::testMapping
(
    SqlFunctionDemo: Relational
    {
       scope([myDB] dataTable)
       (
          ... other mappings
          myCustomValue: myCustomSQLFunction(float1, int1)
       )

    }

Implement the dyna function

During SQL generation, we map the myCustomSQLFunction dyna function to a SQL function ```my_custom_function`` with the appropriate params.

function <<access.private>> meta::relational::functions::sqlQueryToString::getDynaFunctionMappings(dbType:DatabaseType[1]):meta::pure::metamodel::function::Function<{DynaFunction[1], GenerationState[1]->DynaFunctionToSql[1]}>[1]
{
   // ... code 
    
   let dispatch = $dbType->createDynafunctionMap([
      forDynafunction('myCustomSQLFunction',   [ choice(DatabaseType.H2,  $allStates,  ^ToSql(format='my_custom_function(%s, %s)'))]),

   // ... more code
}

Finally, use the function

function <<test.Test>> meta::relational::tests::mapping::sqlFunction::custom::testProject():Boolean[1]
{
    let result = meta::relational::functions::sqlstring::toSQLString(
        |SqlFunctionDemo.all()->project([s | $s.myCustomValue ], ['mycustomfunction']), 
        testMapping, 
        meta::relational::runtime::DatabaseType.H2,
        meta::pure::router::extension::defaultRelationalExtensions());
    assertEq('select my_custom_function("root".float1, "root".int1) as "mycustomfunction" from dataTable as "root"', $result);
    //let result = execute(|SqlFunctionDemo.all()->project([s | $s.myCustomValue ], ['mycustomfunction']), testMapping, testDataTypeMappingRuntime(), meta::pure::router::extension::defaultRelationalExtensions());
}

Patch

diff --git a/legend-engine-xt-relationalStore-pure/src/main/resources/core_relational/relational/sqlQueryToString/SQLQueryToString.pure b/legend-engine-xt-relationalStore-pure/src/main/resources/core_relational/relational/sqlQueryToString/SQLQueryToString.pure
index 297b03d4..0676f610 100644
--- a/legend-engine-xt-relationalStore-pure/src/main/resources/core_relational/relational/sqlQueryToString/SQLQueryToString.pure
+++ b/legend-engine-xt-relationalStore-pure/src/main/resources/core_relational/relational/sqlQueryToString/SQLQueryToString.pure
@@ -1154,7 +1154,6 @@ Class <<access.private>> meta::relational::functions::sqlQueryToString::DynaFunc
    {
       let processedParams = $parameters->zip($generationStates)->map(p | $p.first->processOperation($dbConfig, $format, $p.second, $config, $extensions));
       let params = if($this.toSql.transform->isEmpty(), | $processedParams, | $this.toSql.transform->toOne()->eval($processedParams));
-
       format($this.toSql.format, $params);
    }:String[1];
 }
@@ -1300,6 +1299,7 @@ function <<access.private>> meta::relational::functions::sqlQueryToString::getDy
    let notSelectOutsideWhen = [$selectInsideWhen, $whereInsideWhen, $whereOutsideWhen];
 
    let dispatch = $dbType->createDynafunctionMap([
+      forDynafunction('myCustomSQLFunction',   [ choice(DatabaseType.H2,           $allStates,            ^ToSql(format='my_custom_function(%s, %s)'))]),
       forDynafunction('abs',                   [ choice($allTypes,                 $allStates,            ^ToSql(format='abs(%s)'))]),
       forDynafunction('acos',                  [ choice($allTypes,                 $allStates,            ^ToSql(format='acos(%s)'))]),
       forDynafunction('add',                   [ choice($allTypes,                 $allStates,            ^ToSql(format='%s',  transform=getTransformForAddPlus()))]),
diff --git a/legend-engine-xt-relationalStore-pure/src/main/resources/core_relational/relational/tests/mapping/sqlFunction/testSqlFunctionsInMapping.pure b/legend-engine-xt-relationalStore-pure/src/main/resources/core_relational/relational/tests/mapping/sqlFunction/testSqlFunctionsInMapping.pure
index 65bfe8a5..33589486 100644
--- a/legend-engine-xt-relationalStore-pure/src/main/resources/core_relational/relational/tests/mapping/sqlFunction/testSqlFunctionsInMapping.pure
+++ b/legend-engine-xt-relationalStore-pure/src/main/resources/core_relational/relational/tests/mapping/sqlFunction/testSqlFunctionsInMapping.pure
@@ -348,6 +348,17 @@ function <<test.Test>> meta::relational::tests::mapping::sqlFunction::ceiling::t
     assertEquals('select ceiling("root".float1) as "ceiling" from dataTable as "root"',$result->sqlRemoveFormatting());
 }
 
+function <<test.Test>> meta::relational::tests::mapping::sqlFunction::custom::testProject():Boolean[1]
+{
+    let result = meta::relational::functions::sqlstring::toSQLString(
+        |SqlFunctionDemo.all()->project([s | $s.myCustomValue ], ['mycustomfunction']), 
+        testMapping, 
+        meta::relational::runtime::DatabaseType.H2,
+        meta::pure::router::extension::defaultRelationalExtensions());
+    assertEq('select my_custom_function("root".float1, "root".int1) as "mycustomfunction" from dataTable as "root"', $result);
+    //let result = execute(|SqlFunctionDemo.all()->project([s | $s.myCustomValue ], ['mycustomfunction']), testMapping, testDataTypeMappingRuntime(), meta::pure::router::extension::defaultRelationalExtensions());
+}
+
 function <<test.Test>> meta::relational::tests::mapping::sqlFunction::ceiling::testFilter():Boolean[1]
 {
     let result = execute(|SqlFunctionDemo.all()->filter(s | $s.float1Ceiling == 2)->project([s | $s.float1Ceiling], ['ceiling']), testMapping, testDataTypeMappingRuntime(), meta::pure::router::extension::defaultRelationalExtensions());
@@ -1176,6 +1187,8 @@ Class meta::relational::tests::mapping::sqlFunction::model::domain::SqlFunctionD
    month : Integer[1];
    week : Integer[1];
    date : Date[1];
+
+   myCustomValue: Integer[1];
 }
 
 Class meta::relational::tests::mapping::sqlFunction::model::domain::SqlFunctionDemoForPresto
@@ -1274,7 +1287,8 @@ Mapping meta::relational::tests::mapping::sqlFunction::model::mapping::testMappi
           hour : hour(dateTime),
           month : monthNumber(dateTime),
           week : weekOfYear(dateTime),
-          date : datePart(dateTime)
+          date : datePart(dateTime),
+          myCustomValue: myCustomSQLFunction(float1, int1)
        )
 
     }
diff --git a/legend-engine-xt-relationalStore-pure/src/main/resources/core_relational/relational/transform/fromPure/toSQLString.pure b/legend-engine-xt-relationalStore-pure/src/main/resources/core_relational/relational/transform/fromPure/toSQLString.pure
index f4da41a6..64d7c188 100644
--- a/legend-engine-xt-relationalStore-pure/src/main/resources/core_relational/relational/transform/fromPure/toSQLString.pure
+++ b/legend-engine-xt-relationalStore-pure/src/main/resources/core_relational/relational/transform/fromPure/toSQLString.pure
@@ -32,6 +32,11 @@ function meta::relational::functions::sqlstring::toSQLStringPretty(f:FunctionDef
    toSQLString($f, $mapping, $databaseType, [], [], ^Format(newLine='\n', indent='\t'), $extensions, noDebug())
 }
 
+function meta::relational::functions::sqlstring::toSQLStringPrettyWithDebug(f:FunctionDefinition<{->Any[*]}>[1], mapping:Mapping[1], databaseType:DatabaseType[1], extensions:RouterExtension[*]):String[1]
+{
+   toSQLString($f, $mapping, $databaseType, [], [], ^Format(newLine='\n', indent='\t'), $extensions, debug())
+}
+
 function meta::relational::functions::sqlstring::toSQLStringPretty(f:FunctionDefinition<{->Any[*]}>[1], mapping:Mapping[1], runtime:Runtime[1], extensions:RouterExtension[*]):String[1]
 {
    let databaseConnection = $runtime.connections->toOne()->cast(@DatabaseConnection);
@@ -57,12 +62,12 @@ function meta::relational::functions::sqlstring::toSQLStringPretty(f:FunctionDef
 
 function meta::relational::functions::sqlstring::toSQLString(f:FunctionDefinition<{->Any[*]}>[1], mapping:Mapping[1], databaseType:DatabaseType[1], extensions:RouterExtension[*]):String[1]
 {
-   toSQLString($f, $mapping, $databaseType, [], [], ^Format(newLine='', indent=''), $extensions, noDebug())
+  toSQLString($f, $mapping, $databaseType, [], [], ^Format(newLine='', indent=''), $extensions, noDebug());
 }
 
 function meta::relational::functions::sqlstring::toSQLString(f:FunctionDefinition<{->Any[*]}>[1], mapping:Mapping[1], databaseType:DatabaseType[1], extensions:RouterExtension[*], debug:DebugContext[1]):String[1]
 {
-   toSQLString($f, $mapping, $databaseType, [], [], ^Format(newLine='', indent=''), $extensions, $debug)
+   toSQLString($f, $mapping, $databaseType, [], [], ^Format(newLine='', indent=''), $extensions, $debug);
 }
 
 function meta::relational::functions::sqlstring::toSQLString(f:FunctionDefinition<{->Any[*]}>[1], mapping:Mapping[1], databaseType:DatabaseType[1], dbTimeZone:String[0..1], extensions:RouterExtension[*]):String[1]
@@ -93,7 +98,6 @@ function <<access.private>> meta::relational::functions::sqlstring::toSQL(f:Func
    toSQL($routed.functions.expressionSequence->evaluateAndDeactivate()->cast(@ClusteredValueSpecification), $f->openVariableValues(), $mapping, $databaseType, $dbTimeZone, $sqlQueryPostProcessors, $extensions, $debug, ^SQLResult(shouldWarn=false, extensions=$extensions));
 }
 
-
 function <<access.private>> meta::relational::functions::sqlstring::toSQL(clusters:ClusteredValueSpecification[*], vars:Map<String, List<Any>>[1], mapping:Mapping[1], databaseType:DatabaseType[1], dbTimeZone:String[0..1], sqlQueryPostProcessors: Function<{SelectSQLQuery[1]->Result<SelectSQLQuery|1>[1]}>[*], extensions:RouterExtension[*], debug:DebugContext[1], sqlResult:SQLResult[1]):SQLResult[1]
 {
    let clusterBeforeAggregateRewrite = $clusters->head();
@@ -146,5 +150,4 @@ Class meta::relational::functions::sqlstring::SQLResult
       if ($this.shouldWarn, | $sql + '\nWarning: Results only shown for first relational query. Other SQL statements could not be computed because they require results from the execution of the previous expression.' , | $sql);
 
    }:String[1];
-
 }