Repository: squashql/squashql
Pull request #327 (2024-05-06 08:27:11)

add trim and abs functions


Patch:
diff --git a/core/src/main/java/io/squashql/query/Functions.java b/core/src/main/java/io/squashql/query/Functions.java
index a9b1c83..a722c50 100644
--- a/core/src/main/java/io/squashql/query/Functions.java
+++ b/core/src/main/java/io/squashql/query/Functions.java
@@ -261,6 +261,14 @@ public class Functions {
     return new FunctionField("current_date");
   }
 
+  public static Field trim(Field field) {
+    return new FunctionField("trim", field);
+  }
+
+  public static Field abs(Field field) {
+    return new FunctionField("abs", field);
+  }
+
   // COMPARISONS
 
   public static PartialHierarchicalComparisonMeasure comparisonMeasureWithParentOfAxis(String alias,
diff --git a/core/src/main/java/io/squashql/query/compiled/CompiledPeriod.java b/core/src/main/java/io/squashql/query/compiled/CompiledPeriod.java
index 8ca4909..0ac02ff 100644
--- a/core/src/main/java/io/squashql/query/compiled/CompiledPeriod.java
+++ b/core/src/main/java/io/squashql/query/compiled/CompiledPeriod.java
@@ -5,7 +5,7 @@ import io.squashql.type.TypedField;
 
 import java.util.Set;
 
-public interface CompiledPeriod {
+public sealed interface CompiledPeriod permits CompiledPeriod.Month, CompiledPeriod.Quarter, CompiledPeriod.Semester, CompiledPeriod.Year {
 
   Set<TypedField> getTypedFields();
 
diff --git a/core/src/main/java/io/squashql/query/database/SqlFunctions.java b/core/src/main/java/io/squashql/query/database/SqlFunctions.java
index 27420e1..1a3879e 100644
--- a/core/src/main/java/io/squashql/query/database/SqlFunctions.java
+++ b/core/src/main/java/io/squashql/query/database/SqlFunctions.java
@@ -5,7 +5,7 @@ import java.util.Set;
 public final class SqlFunctions {
 
   public static final Set<String> SUPPORTED_DATE_FUNCTIONS = Set.of("YEAR", "QUARTER", "MONTH");
-  public static final Set<String> SUPPORTED_STRING_FUNCTIONS = Set.of("lower", "upper");
+  public static final Set<String> SUPPORTED_STRING_FUNCTIONS = Set.of("lower", "upper", "trim");
 
   private SqlFunctions() {
   }
diff --git a/documentation/QUERY.md b/documentation/QUERY.md
index 0a3b9f4..5616a7b 100644
--- a/documentation/QUERY.md
+++ b/documentation/QUERY.md
@@ -617,11 +617,13 @@ SELECT lower(myTable.a), upper(myTable.a), current_date() AS id, count(*) FROM m
 
 SQL functions:
 
-| function name | Description                  | Example        | Result     | Alias |
-|---------------|------------------------------|----------------|------------|-------|
-| upper         | Convert string to upper case | upper('Hello') | HELLO      | ucase | 
-| lower         | Convert string to lower case | lower('Hello') | hello      | lcase | 
-| current_date  | Current date                 | current_date() | 2024-10-25 |       | 
+| function name | Description                                       | Example          | Result     | Alias |
+|---------------|---------------------------------------------------|------------------|------------|-------|
+| upper         | Convert string to upper case                      | upper('Hello')   | HELLO      | ucase | 
+| lower         | Convert string to lower case                      | lower('Hello')   | hello      | lcase | 
+| trim          | Remove the space character from the start and end | trim(' Hello  ') | Hello      |       | 
+| abs           | Return the absolute value of a number             | abs(-1)          | 1          |       | 
+| current_date  | Current date                                      | current_date()   | 2024-10-25 |       | 
 
 
 ## Measures
diff --git a/js/typescript-library/src/field.ts b/js/typescript-library/src/field.ts
index bfffbd3..2591b8e 100644
--- a/js/typescript-library/src/field.ts
+++ b/js/typescript-library/src/field.ts
@@ -177,3 +177,11 @@ export function lower(field: Field): Field {
 export function upper(field: Field): Field {
   return new FunctionField("upper", field)
 }
+
+export function trim(field: Field): Field {
+  return new FunctionField("trim", field)
+}
+
+export function abs(field: Field): Field {
+  return new FunctionField("abs", field)
+}
diff --git a/js/typescript-library/src/index.ts b/js/typescript-library/src/index.ts
index 415c2de..8101936 100644
--- a/js/typescript-library/src/index.ts
+++ b/js/typescript-library/src/index.ts
@@ -91,7 +91,8 @@ export {
 
 export {
   TableField, ConstantField, Field, AliasedField, BinaryOperationField, FunctionField,
-  tableFields, tableField, lower, upper, currentDate
+  tableFields, tableField,
+  lower, upper, currentDate, trim, abs
 } from './field'
 export {
   default as Criteria


Test patch:
diff --git a/core/src/test/java/io/squashql/query/ATestSQLFunction.java b/core/src/test/java/io/squashql/query/ATestSQLFunction.java
index 732c318..0b1b6d4 100644
--- a/core/src/test/java/io/squashql/query/ATestSQLFunction.java
+++ b/core/src/test/java/io/squashql/query/ATestSQLFunction.java
@@ -44,8 +44,8 @@ public abstract class ATestSQLFunction extends ABaseTestQuery {
     this.tm.load(this.storeName, List.of(
             new Object[]{"0", "drink", 2d, this.now.minusMonths(1)},
             new Object[]{"1", "DrinK", 1d, this.now.plusMonths(1)},
-            new Object[]{"2", "food", 3d, this.now.plusMonths(2)},
-            new Object[]{"3", "cloth", 10d, this.now.plusMonths(3)}
+            new Object[]{"2", "food", -3d, this.now.plusMonths(2)}, // price here is negative to test abs function
+            new Object[]{"3", "     cloth  ", 10d, this.now.plusMonths(3)}
     ));
   }
 
@@ -164,4 +164,47 @@ public abstract class ATestSQLFunction extends ABaseTestQuery {
     //    Assertions.assertThat(result).containsExactly(List.of(this.now, 1L));
     Assertions.assertThat(result.count()).isEqualTo(1);
   }
+
+  /**
+   * Test {@link Functions#trim(Field)}
+   */
+  @Test
+  void testTrim() {
+    // trim in where
+    QueryDto query = Query
+            .from(this.storeName)
+            .where(criterion(trim(this.category), eq("cloth")))
+            .select(List.of(this.category), List.of(CountMeasure.INSTANCE))
+            .build();
+    Table result = this.executor.executeQuery(query);
+    Assertions.assertThat(result.headers().stream().map(Header::name)).containsExactly(SqlUtils.squashqlExpression(this.category), CountMeasure.INSTANCE.alias);
+    Assertions.assertThat(result).containsExactly(List.of("     cloth  ", 1L));
+
+    // trim in select and where
+    query = Query
+            .from(this.storeName)
+            .where(criterion(trim(this.category), eq("cloth")))
+            .select(List.of(trim(this.category)), List.of(CountMeasure.INSTANCE))
+            .build();
+    result = this.executor.executeQuery(query);
+    Assertions.assertThat(result.headers().stream().map(Header::name)).containsExactly("trim(" + SqlUtils.squashqlExpression(this.category) + ")", CountMeasure.INSTANCE.alias);
+    Assertions.assertThat(result).containsExactly(List.of("cloth", 1L));
+  }
+
+  /**
+   * Test {@link Functions#abs(Field)}
+   */
+  @Test
+  void testAbs() {
+    QueryDto query = Query
+            .from(this.storeName)
+            .select(List.of(abs(this.price)), List.of(CountMeasure.INSTANCE))
+            .build();
+    Table result = this.executor.executeQuery(query);
+    Assertions.assertThat(result).containsExactly(
+            List.of(1d, 1L),
+            List.of(2d, 1L),
+            List.of(3d, 1L),
+            List.of(10d, 1L));
+  }
 }