aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorAnton Tananaev <anton@traccar.org>2022-10-15 06:47:59 -0700
committerAnton Tananaev <anton@traccar.org>2022-10-15 06:47:59 -0700
commit3f01f92125851e325b8e3e4ef679d832fe3dee21 (patch)
tree710b512a5b777696769427f1c6be7e37353f7210
parent85cef19d557b3aa0e306c3efe0bdcf8020042775 (diff)
downloadtrackermap-server-3f01f92125851e325b8e3e4ef679d832fe3dee21.tar.gz
trackermap-server-3f01f92125851e325b8e3e4ef679d832fe3dee21.tar.bz2
trackermap-server-3f01f92125851e325b8e3e4ef679d832fe3dee21.zip
Fix MS SQL limit (fix #4958)
-rw-r--r--src/main/java/org/traccar/database/CommandsManager.java4
-rw-r--r--src/main/java/org/traccar/handler/FilterHandler.java4
-rw-r--r--src/main/java/org/traccar/storage/DatabaseStorage.java28
-rw-r--r--src/main/java/org/traccar/storage/query/Order.java12
-rw-r--r--src/main/java/org/traccar/storage/query/Request.java6
5 files changed, 28 insertions, 26 deletions
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<String, Object> 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;
- }
-
}