From 3f01f92125851e325b8e3e4ef679d832fe3dee21 Mon Sep 17 00:00:00 2001 From: Anton Tananaev Date: Sat, 15 Oct 2022 06:47:59 -0700 Subject: Fix MS SQL limit (fix #4958) --- .../java/org/traccar/database/CommandsManager.java | 4 +--- .../java/org/traccar/handler/FilterHandler.java | 4 +--- .../java/org/traccar/storage/DatabaseStorage.java | 28 +++++++++++++--------- src/main/java/org/traccar/storage/query/Order.java | 12 +++++++--- .../java/org/traccar/storage/query/Request.java | 6 ----- 5 files changed, 28 insertions(+), 26 deletions(-) (limited to 'src/main/java') diff --git a/src/main/java/org/traccar/database/CommandsManager.java b/src/main/java/org/traccar/database/CommandsManager.java index 764ea637b..df399cd7a 100644 --- a/src/main/java/org/traccar/database/CommandsManager.java +++ b/src/main/java/org/traccar/database/CommandsManager.java @@ -31,7 +31,6 @@ import org.traccar.storage.Storage; import org.traccar.storage.StorageException; import org.traccar.storage.query.Columns; import org.traccar.storage.query.Condition; -import org.traccar.storage.query.Limit; import org.traccar.storage.query.Order; import org.traccar.storage.query.Request; @@ -102,8 +101,7 @@ public class CommandsManager implements BroadcastInterface { var commands = storage.getObjects(QueuedCommand.class, new Request( new Columns.All(), new Condition.Equals("deviceId", deviceId), - new Order(false, "id"), - new Limit(count))); + new Order("id", false, count))); for (var command : commands) { storage.removeObject(QueuedCommand.class, new Request( new Condition.Equals("id", command.getId()))); diff --git a/src/main/java/org/traccar/handler/FilterHandler.java b/src/main/java/org/traccar/handler/FilterHandler.java index 3722f2a22..994276bb6 100644 --- a/src/main/java/org/traccar/handler/FilterHandler.java +++ b/src/main/java/org/traccar/handler/FilterHandler.java @@ -30,7 +30,6 @@ import org.traccar.storage.Storage; import org.traccar.storage.StorageException; import org.traccar.storage.query.Columns; import org.traccar.storage.query.Condition; -import org.traccar.storage.query.Limit; import org.traccar.storage.query.Order; import org.traccar.storage.query.Request; @@ -90,8 +89,7 @@ public class FilterHandler extends BaseDataHandler { new Condition.And( new Condition.Equals("deviceId", deviceId), new Condition.Compare("fixTime", "<=", "time", date)), - new Order(true, "fixTime"), - new Limit(1))); + new Order("fixTime", true, 1))); } private boolean filterInvalid(Position position) { diff --git a/src/main/java/org/traccar/storage/DatabaseStorage.java b/src/main/java/org/traccar/storage/DatabaseStorage.java index 884c8fca8..a049a641c 100644 --- a/src/main/java/org/traccar/storage/DatabaseStorage.java +++ b/src/main/java/org/traccar/storage/DatabaseStorage.java @@ -24,7 +24,6 @@ import org.traccar.model.GroupedModel; import org.traccar.model.Permission; import org.traccar.storage.query.Columns; import org.traccar.storage.query.Condition; -import org.traccar.storage.query.Limit; import org.traccar.storage.query.Order; import org.traccar.storage.query.Request; @@ -43,12 +42,19 @@ public class DatabaseStorage extends Storage { private final Config config; private final DataSource dataSource; private final ObjectMapper objectMapper; + private final String databaseType; @Inject public DatabaseStorage(Config config, DataSource dataSource, ObjectMapper objectMapper) { this.config = config; this.dataSource = dataSource; this.objectMapper = objectMapper; + + try { + databaseType = dataSource.getConnection().getMetaData().getDatabaseProductName(); + } catch (SQLException e) { + throw new RuntimeException(e); + } } @Override @@ -62,7 +68,6 @@ public class DatabaseStorage extends Storage { query.append(" FROM ").append(getStorageName(clazz)); query.append(formatCondition(request.getCondition())); query.append(formatOrder(request.getOrder())); - query.append(formatLimit(request.getLimit())); try { QueryBuilder builder = QueryBuilder.create(config, dataSource, objectMapper, query.toString()); for (Map.Entry variable : getConditionVariables(request.getCondition()).entrySet()) { @@ -302,15 +307,16 @@ public class DatabaseStorage extends Storage { if (order.getDescending()) { result.append(" DESC"); } - } - return result.toString(); - } - - private String formatLimit(Limit limit) { - StringBuilder result = new StringBuilder(); - if (limit != null) { - result.append(" LIMIT "); - result.append(limit.getValue()); + if (order.getLimit() > 0) { + if (databaseType.equals("Microsoft SQL Server")) { + result.append(" OFFSET 0 ROWS FETCH FIRST "); + result.append(order.getLimit()); + result.append(" ROWS ONLY"); + } else { + result.append(" LIMIT "); + result.append(order.getLimit()); + } + } } return result.toString(); } diff --git a/src/main/java/org/traccar/storage/query/Order.java b/src/main/java/org/traccar/storage/query/Order.java index d12acc83b..b41f145e9 100644 --- a/src/main/java/org/traccar/storage/query/Order.java +++ b/src/main/java/org/traccar/storage/query/Order.java @@ -19,14 +19,16 @@ public class Order { private final String column; private final boolean descending; + private final int limit; public Order(String column) { - this(false, column); + this(column, false, 0); } - public Order(boolean descending, String column) { + public Order(String column, boolean descending, int limit) { this.column = column; - this.descending = descending; + this.descending = false; + this.limit = limit; } public String getColumn() { @@ -37,4 +39,8 @@ public class Order { return descending; } + public int getLimit() { + return limit; + } + } diff --git a/src/main/java/org/traccar/storage/query/Request.java b/src/main/java/org/traccar/storage/query/Request.java index 41aa37bf1..6e9cecc63 100644 --- a/src/main/java/org/traccar/storage/query/Request.java +++ b/src/main/java/org/traccar/storage/query/Request.java @@ -20,7 +20,6 @@ public class Request { private final Columns columns; private final Condition condition; private final Order order; - private final Limit limit; public Request(Columns columns) { this(columns, null, null); @@ -42,7 +41,6 @@ public class Request { this.columns = columns; this.condition = condition; this.order = order; - this.limit = limit; } public Columns getColumns() { @@ -57,8 +55,4 @@ public class Request { return order; } - public Limit getLimit() { - return limit; - } - } -- cgit v1.2.3