Bugfixes for several attribute types in datapoint query (openremote#1029

) * Wrote several tests for the new datapoint queries. Included bugfixes for several attribute types, and now throws exception when invalid type is given. * Added support for booleans in Interval query (as was possible before the hotfix) * Attribute history now uses either LTTB or ALL depending on attribute type. * Minor edit in test, including some more reliable testing to make sure the LTTB algorithm works as intended. --------- Co-authored-by: Rich Turner <7072278+richturner@users.noreply.github.com>
black-device · Apr 14, 2023 · 4fe6a4f · 4fe6a4f
1 parent c517f54
commit 4fe6a4f
Show file tree

Hide file tree

Showing 7 changed files with 361 additions and 18 deletions.
diff --git a/manager/src/main/java/org/openremote/manager/datapoint/AbstractDatapointService.java b/manager/src/main/java/org/openremote/manager/datapoint/AbstractDatapointService.java
@@ -197,7 +197,14 @@ public ValueDatapoint<?>[] execute(Connection connection) throws SQLException {
                         boolean isNumber = Number.class.isAssignableFrom(attributeType);
                         boolean isBoolean = Boolean.class.isAssignableFrom(attributeType);
 
-                        try (PreparedStatement st = connection.prepareStatement(datapointQuery.getSQLQuery(getDatapointTableName(), attributeType))) {
+                        String query;
+                        try {
+                            query = datapointQuery.getSQLQuery(getDatapointTableName(), attributeType);
+                        } catch (IllegalStateException ise) {
+                            getLogger().log(Level.WARNING, ise.getMessage());
+                            throw ise;
+                        }
+                        try (PreparedStatement st = connection.prepareStatement(query)) {
 
                             if(parameters.size() > 0) {
                                 for(Map.Entry<Integer, Object> param : parameters.entrySet()) {

diff --git a/model/src/main/java/org/openremote/model/datapoint/query/AssetDatapointAllQuery.java b/model/src/main/java/org/openremote/model/datapoint/query/AssetDatapointAllQuery.java
@@ -19,14 +19,18 @@ public AssetDatapointAllQuery(LocalDateTime fromTime, LocalDateTime toTime) {
         this.toTime = toTime;
     }
 
-    public String getSQLQuery(String tableName, Class<?> attributeType) {
+    public String getSQLQuery(String tableName, Class<?> attributeType) throws IllegalStateException {
         boolean isNumber = Number.class.isAssignableFrom(attributeType);
+        boolean isBoolean = Boolean.class.isAssignableFrom(attributeType);
         if (isNumber) {
-            return "select timestamp, value::text::numeric from " + tableName + " where ENTITY_ID = ? and ATTRIBUTE_NAME = ? and TIMESTAMP >= ? and TIMESTAMP <= ? order by timestamp desc";
+            return "select timestamp as X, value::text::numeric as Y from " + tableName + " where ENTITY_ID = ? and ATTRIBUTE_NAME = ? and TIMESTAMP >= ? and TIMESTAMP <= ? order by timestamp desc";
+        } else if (isBoolean) {
+            return "select timestamp as X, (case when VALUE::text::boolean is true then 1 else 0 end) as Y from " + tableName + " where ENTITY_ID = ? and ATTRIBUTE_NAME = ? and TIMESTAMP >= ? and TIMESTAMP <= ? order by timestamp desc";
         } else {
-            return "select timestamp, (case when VALUE::text::boolean is true then 1 else 0 end) from " + tableName + " where ENTITY_ID = ? and ATTRIBUTE_NAME = ? and TIMESTAMP >= ? and TIMESTAMP <= ? order by timestamp desc";
+            return "select distinct timestamp as X, value as Y from " + tableName + " where ENTITY_ID = ? and ATTRIBUTE_NAME = ? and TIMESTAMP >= ? and TIMESTAMP <= ? order by timestamp desc";
         }
     }
+
     public HashMap<Integer, Object> getSQLParameters(AttributeRef attributeRef) {
         HashMap<Integer, Object> parameters = new HashMap<>();
         LocalDateTime fromTimestamp = (this.fromTime != null) ? this.fromTime : LocalDateTime.ofInstant(Instant.ofEpochMilli(this.fromTimestamp), ZoneId.systemDefault());

diff --git a/model/src/main/java/org/openremote/model/datapoint/query/AssetDatapointIntervalQuery.java b/model/src/main/java/org/openremote/model/datapoint/query/AssetDatapointIntervalQuery.java
@@ -35,13 +35,16 @@ public AssetDatapointIntervalQuery(LocalDateTime fromTime, LocalDateTime toTime,
     }
 
     @Override
-    public String getSQLQuery(String tableName, Class<?> attributeType) {
+    public String getSQLQuery(String tableName, Class<?> attributeType) throws IllegalStateException {
         boolean isNumber = Number.class.isAssignableFrom(attributeType);
+        boolean isBoolean = Boolean.class.isAssignableFrom(attributeType);
         String function = (gapFill ? "public.time_bucket_gapfill" : "public.time_bucket");
         if (isNumber) {
             return "select " + function + "(?::interval, timestamp) AS x, " + this.formula.toString().toLowerCase() + "(value::text::numeric) FROM " + tableName + " WHERE ENTITY_ID = ? and ATTRIBUTE_NAME = ? and TIMESTAMP >= ? and TIMESTAMP <= ? GROUP BY x;";
-        } else {
+        } else if (isBoolean) {
             return "select " + function + "(?::interval, timestamp) AS x, " + this.formula.toString().toLowerCase() + "(case when VALUE::text::boolean is true then 1 else 0 end) FROM " + tableName + " WHERE ENTITY_ID = ? and ATTRIBUTE_NAME = ? and TIMESTAMP >= ? and TIMESTAMP <= ? GROUP BY x;";
+        } else {
+            throw new IllegalStateException("Query of type Interval requires either a number or a boolean attribute.");
         }
     }
 

diff --git a/model/src/main/java/org/openremote/model/datapoint/query/AssetDatapointLTTBQuery.java b/model/src/main/java/org/openremote/model/datapoint/query/AssetDatapointLTTBQuery.java
@@ -24,12 +24,15 @@ public AssetDatapointLTTBQuery(LocalDateTime fromTime, LocalDateTime toTime, int
     }
 
     @Override
-    public String getSQLQuery(String tableName, Class<?> attributeType) {
+    public String getSQLQuery(String tableName, Class<?> attributeType) throws IllegalStateException {
         boolean isNumber = Number.class.isAssignableFrom(attributeType);
+        boolean isBoolean = Boolean.class.isAssignableFrom(attributeType);
         if (isNumber) {
             return "select * from public.unnest((select public.lttb(timestamp::timestamptz, value::double precision, ?) from " + tableName + " where ENTITY_ID = ? and ATTRIBUTE_NAME = ? and TIMESTAMP >= ? and TIMESTAMP <= ?))";
-        } else {
+        } else if (isBoolean) {
             return "select * from public.unnest((select public.lttb(timestamp::timestamptz, (case when VALUE::text::boolean is true then 1 else 0 end), ?) from " + tableName + " where ENTITY_ID = ? and ATTRIBUTE_NAME = ? and TIMESTAMP >= ? and TIMESTAMP <= ?))";
+        } else {
+            throw new IllegalStateException("Query of type LTTB requires either a number or a boolean attribute.");
         }
     }
 

diff --git a/model/src/main/java/org/openremote/model/datapoint/query/AssetDatapointQuery.java b/model/src/main/java/org/openremote/model/datapoint/query/AssetDatapointQuery.java
@@ -44,7 +44,7 @@ public abstract class AssetDatapointQuery implements Serializable {
     public LocalDateTime fromTime;
     public LocalDateTime toTime;
 
-    public String getSQLQuery(String tableName, Class<?> attributeType) {
+    public String getSQLQuery(String tableName, Class<?> attributeType) throws IllegalStateException {
         return null;
     }