From 6005016eb89baa471feec29e2c7cda19f7dac3e6 Mon Sep 17 00:00:00 2001 From: Anton Tananaev Date: Sat, 14 Nov 2015 14:45:45 +1300 Subject: Update queries in config files --- debug.xml | 2 +- setup/unix/traccar.xml | 64 ++++++++++++++----------------- setup/windows/traccar.xml | 64 ++++++++++++++----------------- src/org/traccar/database/DataManager.java | 2 +- 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); 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); @@ -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 @@ UPDATE "user" SET hashedPassword = :hashedPassword, salt = :salt WHERE id = :id; - + DELETE FROM "user" WHERE id = :id; @@ -184,38 +172,42 @@ SELECT userId, deviceId FROM user_device; - + SELECT * FROM device; - + SELECT * FROM device d INNER JOIN user_device ud ON d.id = ud.deviceId WHERE ud.userId = :userId; - + INSERT INTO device (name, uniqueId) VALUES (:name, :uniqueId); - + UPDATE device SET name = :name, uniqueId = :uniqueId WHERE id = :id; - + DELETE FROM device WHERE id = :id; - + INSERT INTO user_device (userId, deviceId) VALUES (:userId, :deviceId); + + DELETE FROM user_device WHERE userId = :userId AND deviceId = :deviceId; + + - 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; - 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); 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); @@ -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 @@ UPDATE "user" SET hashedPassword = :hashedPassword, salt = :salt WHERE id = :id; - + DELETE FROM "user" WHERE id = :id; @@ -184,38 +172,42 @@ SELECT userId, deviceId FROM user_device; - + SELECT * FROM device; - + SELECT * FROM device d INNER JOIN user_device ud ON d.id = ud.deviceId WHERE ud.userId = :userId; - + INSERT INTO device (name, uniqueId) VALUES (:name, :uniqueId); - + UPDATE device SET name = :name, uniqueId = :uniqueId WHERE id = :id; - + DELETE FROM device WHERE id = :id; - + INSERT INTO user_device (userId, deviceId) VALUES (:userId, :deviceId); + + DELETE FROM user_device WHERE userId = :userId AND deviceId = :deviceId; + + - 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; - 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); 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(); } -- cgit v1.2.3