aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--debug.xml2
-rw-r--r--setup/unix/traccar.xml64
-rw-r--r--setup/windows/traccar.xml64
-rw-r--r--src/org/traccar/database/DataManager.java2
4 files changed, 58 insertions, 74 deletions
diff --git a/debug.xml b/debug.xml
index 67ae05c41..47f17bdae 100644
--- a/debug.xml
+++ b/debug.xml
@@ -120,7 +120,7 @@
CREATE TABLE traccar (
version INT DEFAULT 0 NOT NULL);
- INSERT INTO traccar (version) VALUES (301);
+ INSERT INTO traccar (version) VALUES (302);
</entry>
<entry key='database.selectServers'>
diff --git a/setup/unix/traccar.xml b/setup/unix/traccar.xml
index b5b110f4c..eb4321bd2 100644
--- a/setup/unix/traccar.xml
+++ b/setup/unix/traccar.xml
@@ -43,8 +43,8 @@
language VARCHAR(128),
distanceUnit VARCHAR(128),
speedUnit VARCHAR(128),
- latitude FLOAT DEFAULT 0 NOT NULL,
- longitude FLOAT DEFAULT 0 NOT NULL,
+ latitude DOUBLE PRECISION DEFAULT 0 NOT NULL,
+ longitude DOUBLE PRECISION DEFAULT 0 NOT NULL,
zoom INT DEFAULT 0 NOT NULL);
CREATE TABLE device (
@@ -53,8 +53,7 @@
uniqueId VARCHAR(128) NOT NULL UNIQUE,
status VARCHAR(128),
lastUpdate TIMESTAMP,
- positionId INT,
- dataId INT);
+ positionId INT);
CREATE TABLE user_device (
userId INT NOT NULL,
@@ -74,47 +73,34 @@
deviceTime TIMESTAMP NOT NULL,
fixTime TIMESTAMP NOT NULL,
valid BIT NOT NULL,
- latitude FLOAT NOT NULL,
- longitude FLOAT NOT NULL,
+ latitude DOUBLE PRECISION NOT NULL,
+ longitude DOUBLE PRECISION NOT NULL,
altitude FLOAT NOT NULL,
speed FLOAT NOT NULL,
course FLOAT NOT NULL,
address VARCHAR(512),
- other VARCHAR(4096) NOT NULL,
- FOREIGN KEY (deviceId) REFERENCES device (id) ON DELETE CASCADE);
-
- CREATE INDEX position_deviceId_fixTime ON position (deviceId, fixTime);
-
- CREATE TABLE data (
- id INT PRIMARY KEY AUTO_INCREMENT,
- protocol VARCHAR(128),
- deviceId INT NOT NULL,
- serverTime TIMESTAMP NOT NULL,
- deviceTime TIMESTAMP NOT NULL,
- other VARCHAR(4096) NOT NULL,
+ attributes VARCHAR(4096) NOT NULL,
FOREIGN KEY (deviceId) REFERENCES device (id) ON DELETE CASCADE);
- ALTER TABLE device ADD
- FOREIGN KEY (positionId) REFERENCES position (id) ON DELETE CASCADE;
-
- ALTER TABLE device ADD
- FOREIGN KEY (dataId) REFERENCES data (id) ON DELETE CASCADE;
+ CREATE INDEX position_deviceId_fixTime ON position (deviceId, fixTime);
CREATE TABLE server (
id INT PRIMARY KEY AUTO_INCREMENT,
registration BIT NOT NULL,
map VARCHAR(128),
+ bingKey VARCHAR(128),
+ mapUrl VARCHAR(128),
language VARCHAR(128),
distanceUnit VARCHAR(128),
speedUnit VARCHAR(128),
- latitude FLOAT DEFAULT 0 NOT NULL,
- longitude FLOAT DEFAULT 0 NOT NULL,
+ latitude DOUBLE PRECISION DEFAULT 0 NOT NULL,
+ longitude DOUBLE PRECISION DEFAULT 0 NOT NULL,
zoom INT DEFAULT 0 NOT NULL);
CREATE TABLE traccar (
version INT DEFAULT 0 NOT NULL);
- INSERT INTO traccar (version) VALUES (301);
+ INSERT INTO traccar (version) VALUES (302);
</entry>
<entry key='database.selectServers'>
@@ -130,6 +116,8 @@
UPDATE server SET
registration = :registration,
map = :map,
+ bingKey = :bingKey,
+ mapUrl = :mapUrl,
language = :language,
distanceUnit = :distanceUnit,
speedUnit = :speedUnit,
@@ -176,7 +164,7 @@
<entry key='database.updateUserPassword'>
UPDATE "user" SET hashedPassword = :hashedPassword, salt = :salt WHERE id = :id;
</entry>
-
+
<entry key='database.deleteUser'>
DELETE FROM "user" WHERE id = :id;
</entry>
@@ -184,38 +172,42 @@
<entry key='database.getPermissionsAll'>
SELECT userId, deviceId FROM user_device;
</entry>
-
+
<entry key='database.selectDevicesAll'>
SELECT * FROM device;
</entry>
-
+
<entry key='database.selectDevices'>
SELECT * FROM device d INNER JOIN user_device ud ON d.id = ud.deviceId WHERE ud.userId = :userId;
</entry>
-
+
<entry key='database.insertDevice'>
INSERT INTO device (name, uniqueId) VALUES (:name, :uniqueId);
</entry>
-
+
<entry key='database.updateDevice'>
UPDATE device SET name = :name, uniqueId = :uniqueId WHERE id = :id;
</entry>
-
+
<entry key='database.deleteDevice'>
DELETE FROM device WHERE id = :id;
</entry>
-
+
<entry key='database.linkDevice'>
INSERT INTO user_device (userId, deviceId) VALUES (:userId, :deviceId);
</entry>
+ <entry key='database.unlinkDevice'>
+ DELETE FROM user_device WHERE userId = :userId AND deviceId = :deviceId;
+ </entry>
+
<entry key='database.selectPositions'>
- SELECT * FROM position WHERE deviceId = :deviceId AND fixTime BETWEEN :from AND :to;
+ SELECT * FROM position WHERE deviceId = :deviceId AND fixTime BETWEEN :from AND :to ORDER BY fixTime;
</entry>
<entry key='database.insertPosition'>
- INSERT INTO position (deviceId, protocol, serverTime, deviceTime, fixTime, valid, latitude, longitude, altitude, speed, course, address, other)
- VALUES (:deviceId, :protocol, CURRENT_TIMESTAMP(), :time, :time, :valid, :latitude, :longitude, :altitude, :speed, :course, :address, :other);
+ INSERT INTO position (deviceId, protocol, serverTime, deviceTime, fixTime, valid, latitude, longitude, altitude, speed, course, address, attributes)
+ VALUES (:deviceId, :protocol, CURRENT_TIMESTAMP(), :time, :time, :valid, :latitude, :longitude, :altitude, :speed, :course, :address, :attributes);
</entry>
<entry key='database.selectLatestPositions'>
diff --git a/setup/windows/traccar.xml b/setup/windows/traccar.xml
index b00177c0e..419299a04 100644
--- a/setup/windows/traccar.xml
+++ b/setup/windows/traccar.xml
@@ -43,8 +43,8 @@
language VARCHAR(128),
distanceUnit VARCHAR(128),
speedUnit VARCHAR(128),
- latitude FLOAT DEFAULT 0 NOT NULL,
- longitude FLOAT DEFAULT 0 NOT NULL,
+ latitude DOUBLE PRECISION DEFAULT 0 NOT NULL,
+ longitude DOUBLE PRECISION DEFAULT 0 NOT NULL,
zoom INT DEFAULT 0 NOT NULL);
CREATE TABLE device (
@@ -53,8 +53,7 @@
uniqueId VARCHAR(128) NOT NULL UNIQUE,
status VARCHAR(128),
lastUpdate TIMESTAMP,
- positionId INT,
- dataId INT);
+ positionId INT);
CREATE TABLE user_device (
userId INT NOT NULL,
@@ -74,47 +73,34 @@
deviceTime TIMESTAMP NOT NULL,
fixTime TIMESTAMP NOT NULL,
valid BIT NOT NULL,
- latitude FLOAT NOT NULL,
- longitude FLOAT NOT NULL,
+ latitude DOUBLE PRECISION NOT NULL,
+ longitude DOUBLE PRECISION NOT NULL,
altitude FLOAT NOT NULL,
speed FLOAT NOT NULL,
course FLOAT NOT NULL,
address VARCHAR(512),
- other VARCHAR(4096) NOT NULL,
- FOREIGN KEY (deviceId) REFERENCES device (id) ON DELETE CASCADE);
-
- CREATE INDEX position_deviceId_fixTime ON position (deviceId, fixTime);
-
- CREATE TABLE data (
- id INT PRIMARY KEY AUTO_INCREMENT,
- protocol VARCHAR(128),
- deviceId INT NOT NULL,
- serverTime TIMESTAMP NOT NULL,
- deviceTime TIMESTAMP NOT NULL,
- other VARCHAR(4096) NOT NULL,
+ attributes VARCHAR(4096) NOT NULL,
FOREIGN KEY (deviceId) REFERENCES device (id) ON DELETE CASCADE);
- ALTER TABLE device ADD
- FOREIGN KEY (positionId) REFERENCES position (id) ON DELETE CASCADE;
-
- ALTER TABLE device ADD
- FOREIGN KEY (dataId) REFERENCES data (id) ON DELETE CASCADE;
+ CREATE INDEX position_deviceId_fixTime ON position (deviceId, fixTime);
CREATE TABLE server (
id INT PRIMARY KEY AUTO_INCREMENT,
registration BIT NOT NULL,
map VARCHAR(128),
+ bingKey VARCHAR(128),
+ mapUrl VARCHAR(128),
language VARCHAR(128),
distanceUnit VARCHAR(128),
speedUnit VARCHAR(128),
- latitude FLOAT DEFAULT 0 NOT NULL,
- longitude FLOAT DEFAULT 0 NOT NULL,
+ latitude DOUBLE PRECISION DEFAULT 0 NOT NULL,
+ longitude DOUBLE PRECISION DEFAULT 0 NOT NULL,
zoom INT DEFAULT 0 NOT NULL);
CREATE TABLE traccar (
version INT DEFAULT 0 NOT NULL);
- INSERT INTO traccar (version) VALUES (301);
+ INSERT INTO traccar (version) VALUES (302);
</entry>
<entry key='database.selectServers'>
@@ -130,6 +116,8 @@
UPDATE server SET
registration = :registration,
map = :map,
+ bingKey = :bingKey,
+ mapUrl = :mapUrl,
language = :language,
distanceUnit = :distanceUnit,
speedUnit = :speedUnit,
@@ -176,7 +164,7 @@
<entry key='database.updateUserPassword'>
UPDATE "user" SET hashedPassword = :hashedPassword, salt = :salt WHERE id = :id;
</entry>
-
+
<entry key='database.deleteUser'>
DELETE FROM "user" WHERE id = :id;
</entry>
@@ -184,38 +172,42 @@
<entry key='database.getPermissionsAll'>
SELECT userId, deviceId FROM user_device;
</entry>
-
+
<entry key='database.selectDevicesAll'>
SELECT * FROM device;
</entry>
-
+
<entry key='database.selectDevices'>
SELECT * FROM device d INNER JOIN user_device ud ON d.id = ud.deviceId WHERE ud.userId = :userId;
</entry>
-
+
<entry key='database.insertDevice'>
INSERT INTO device (name, uniqueId) VALUES (:name, :uniqueId);
</entry>
-
+
<entry key='database.updateDevice'>
UPDATE device SET name = :name, uniqueId = :uniqueId WHERE id = :id;
</entry>
-
+
<entry key='database.deleteDevice'>
DELETE FROM device WHERE id = :id;
</entry>
-
+
<entry key='database.linkDevice'>
INSERT INTO user_device (userId, deviceId) VALUES (:userId, :deviceId);
</entry>
+ <entry key='database.unlinkDevice'>
+ DELETE FROM user_device WHERE userId = :userId AND deviceId = :deviceId;
+ </entry>
+
<entry key='database.selectPositions'>
- SELECT * FROM position WHERE deviceId = :deviceId AND fixTime BETWEEN :from AND :to;
+ SELECT * FROM position WHERE deviceId = :deviceId AND fixTime BETWEEN :from AND :to ORDER BY fixTime;
</entry>
<entry key='database.insertPosition'>
- INSERT INTO position (deviceId, protocol, serverTime, deviceTime, fixTime, valid, latitude, longitude, altitude, speed, course, address, other)
- VALUES (:deviceId, :protocol, CURRENT_TIMESTAMP(), :time, :time, :valid, :latitude, :longitude, :altitude, :speed, :course, :address, :other);
+ INSERT INTO position (deviceId, protocol, serverTime, deviceTime, fixTime, valid, latitude, longitude, altitude, speed, course, address, attributes)
+ VALUES (:deviceId, :protocol, CURRENT_TIMESTAMP(), :time, :time, :valid, :latitude, :longitude, :altitude, :speed, :course, :address, :attributes);
</entry>
<entry key='database.selectLatestPositions'>
diff --git a/src/org/traccar/database/DataManager.java b/src/org/traccar/database/DataManager.java
index 8be6aec42..24a07a05c 100644
--- a/src/org/traccar/database/DataManager.java
+++ b/src/org/traccar/database/DataManager.java
@@ -172,7 +172,7 @@ public class DataManager implements IdentityManager {
version = schema.getVersion();
}
- if (version != 301) {
+ if (version != 302) {
Log.error("Wrong database schema version (" + version + ")");
throw new RuntimeException();
}
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-25 06:11:09 +0000