From 192d73d617601a8d9a6100537497ecda56c734ca Mon Sep 17 00:00:00 2001 From: Anton Tananaev Date: Sat, 8 Aug 2015 21:42:46 +1200 Subject: Merge linux and macos directories --- setup/unix/traccar.xml | 321 +++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 321 insertions(+) create mode 100644 setup/unix/traccar.xml (limited to 'setup/unix') diff --git a/setup/unix/traccar.xml b/setup/unix/traccar.xml new file mode 100644 index 000000000..70270b7d1 --- /dev/null +++ b/setup/unix/traccar.xml @@ -0,0 +1,321 @@ + + + + + + + + + true + 8082 + /opt/traccar/web + + true + google + + true + all + /opt/traccar/logs/tracker-server.log + + + + org.h2.Driver + jdbc:h2:/opt/traccar/data/database + sa + + + traccar + + + SELECT * FROM traccar; + + + + CREATE TABLE "user" ( + id INT PRIMARY KEY AUTO_INCREMENT, + name VARCHAR(128) NOT NULL, + email VARCHAR(128) NOT NULL UNIQUE, + hashedPassword VARCHAR(128) NOT NULL, + salt VARCHAR(128) DEFAULT '' NOT NULL, + readonly BIT DEFAULT 0 NOT NULL, + admin BIT DEFAULT 0 NOT NULL, + map VARCHAR(128), + language VARCHAR(128), + distanceUnit VARCHAR(128), + speedUnit VARCHAR(128), + latitude FLOAT DEFAULT 0 NOT NULL, + longitude FLOAT DEFAULT 0 NOT NULL, + zoom INT DEFAULT 0 NOT NULL); + + CREATE TABLE device ( + id INT PRIMARY KEY AUTO_INCREMENT, + name VARCHAR(128) NOT NULL, + uniqueId VARCHAR(128) NOT NULL UNIQUE, + status VARCHAR(128), + lastUpdate TIMESTAMP, + positionId INT, + dataId INT); + + CREATE TABLE user_device ( + userId INT NOT NULL, + deviceId INT NOT NULL, + "read" BIT DEFAULT 1 NOT NULL, + "write" BIT DEFAULT 1 NOT NULL, + FOREIGN KEY (userId) REFERENCES "user" (id) ON DELETE CASCADE, + FOREIGN KEY (deviceId) REFERENCES device (id) ON DELETE CASCADE); + + CREATE INDEX user_device_userId ON user_device(userId); + + CREATE TABLE position ( + id INT PRIMARY KEY AUTO_INCREMENT, + protocol VARCHAR(128), + deviceId INT NOT NULL, + serverTime TIMESTAMP NOT NULL, + deviceTime TIMESTAMP NOT NULL, + fixTime TIMESTAMP NOT NULL, + valid BIT NOT NULL, + latitude FLOAT NOT NULL, + longitude FLOAT 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, + 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 TABLE server ( + id INT PRIMARY KEY AUTO_INCREMENT, + registration BIT NOT NULL, + map VARCHAR(128), + language VARCHAR(128), + distanceUnit VARCHAR(128), + speedUnit VARCHAR(128), + latitude FLOAT DEFAULT 0 NOT NULL, + longitude FLOAT 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); + + + + SELECT * FROM server; + + + + INSERT INTO server (registration, latitude, longitude, zoom) + VALUES (:registration, :latitude, :longitude, :zoom); + + + + UPDATE server SET + registration = :registration, + map = :map, + language = :language, + distanceUnit = :distanceUnit, + speedUnit = :speedUnit, + latitude = :latitude, + longitude = :longitude, + zoom = :zoom + WHERE id = :id; + + + + SELECT * FROM "user" + WHERE email = :email; + + + + SELECT * FROM "user" + WHERE id = :id; + + + + SELECT * FROM "user"; + + + + INSERT INTO "user" (name, email, hashedPassword, salt, admin) + VALUES (:name, :email, :hashedPassword, :salt, :admin); + + + + UPDATE "user" SET + name = :name, + email = :email, + admin = :admin, + map = :map, + language = :language, + distanceUnit = :distanceUnit, + speedUnit = :speedUnit, + latitude = :latitude, + longitude = :longitude, + zoom = :zoom + WHERE id = :id; + + + + UPDATE "user" SET hashedPassword = :hashedPassword, salt = :salt WHERE id = :id; + + + + DELETE FROM "user" WHERE id = :id; + + + + 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); + + + + SELECT * FROM position WHERE deviceId = :deviceId AND fixTime BETWEEN :from AND :to; + + + + 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); + + + + SELECT * FROM position WHERE id IN (SELECT positionId FROM device); + + + + UPDATE device SET positionId = :id WHERE id = :deviceId; + + + + + 5001 + 5002 + 5003 + 5004 + 5005 + 5006 + false + 5007 + 5008 + 5009 + 5010 + 5011 + 5012 + 5013 + 5014 + 5015 + 5016 + 5017 + 5018 + 5019 + 5020 + 5021 + 5022 + 5023 + 5024 + 5025 + 5026 + 5027 + 5028 + 5029 + 5030 + 5031 + 5032 + 5033 + 5034 + 5035 + 5036 + 5037 + 5038 + 5039 + 5040 + 5041 + 5042 + 5043 + 5044 + 5045 + 5046 + 5047 + 5048 + 5049 + 5050 + 5051 + 5052 + 5053 + 5054 + 5055 + 5056 + 5057 + 5058 + 5059 + 5060 + 5061 + 5062 + 5063 + 5064 + 5065 + 5066 + 5067 + 5068 + 5069 + 5070 + 5071 + 5072 + 5073 + 5074 + 5075 + 5076 + 5077 + 5078 + 5079 + 5080 + 5081 + 5082 + 5083 + 5084 + 5085 + 5086 + 5087 + 5088 + + -- cgit v1.2.3