diff options
| -rw-r--r-- | .gitignore | 4 | ||||
| -rw-r--r-- | debug.xml | 70 | ||||
| -rw-r--r-- | src/org/traccar/database/DataManager.java | 50 |
3 files changed, 89 insertions, 35 deletions
diff --git a/.gitignore b/.gitignore new file mode 100644 index 000000000..3fe98582b --- /dev/null +++ b/.gitignore @@ -0,0 +1,4 @@ +/target +.classpath +.project +.settings/ @@ -117,9 +117,78 @@ id INT PRIMARY KEY AUTO_INCREMENT); </entry> + <entry key='database.insertServer'> + INSERT INTO server (registration, latitude, longitude, zoom) + VALUES (:registration, :latitude, :longitude, :zoom); + </entry> + + <entry key='database.selectServer'> + SELECT * FROM server; + </entry> + + <entry key='database.updateServer'> + UPDATE server SET registration = :registration WHERE id = :id; + </entry> + + <entry key='database.loginUser'> + SELECT * + FROM user + WHERE email = :email AND password = CAST(HASH('SHA256', STRINGTOUTF8(:password), 1000) AS VARCHAR); + </entry> + + <entry key='database.selectUsersAll'> + SELECT * FROM user; + </entry> + + <entry key='database.insertUser'> + INSERT INTO user (name, email, password, admin) + VALUES (:name, :email, CAST(HASH('SHA256', STRINGTOUTF8(:password), 1000) AS VARCHAR), :admin); + </entry> + + <entry key='database.updateUser'> + UPDATE user SET + name = :name, + email = :email, + admin = :admin, + password = CASEWHEN((SELECT password FROM user WHERE id = :id) = :password, :password, CAST(HASH('SHA256', STRINGTOUTF8(:password), 1000) AS VARCHAR)) + WHERE id = :id; + </entry> + + <entry key='database.deleteUser'> + DELETE FROM user WHERE id = :id; + </entry> + + <entry key='database.getPermissions'> + "SELECT userId, deviceId FROM user_device;" + </entry> + <entry key='database.selectDeviceAll'> SELECT * FROM device; </entry> + + <entry key='database.selectDevices'> + SELECT * FROM device WHERE id IN (SELECT deviceId FROM user_device WHERE 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.removeDevice'> + DELETE FROM device WHERE id = :id; + </entry> + + <entry key='database.linkDevice'> + INSERT INTO user_device (userId, deviceId) VALUES (:userId, :deviceId); + </entry> + + <entry key='database.selectPositions'> + SELECT * FROM position WHERE deviceId = :deviceId AND fixTime BETWEEN :from AND :to; + </entry> <entry key='database.insertPosition'> INSERT INTO position (deviceId, serverTime, deviceTime, fixTime, valid, latitude, longitude, altitude, speed, course, address, other) @@ -130,6 +199,7 @@ UPDATE device SET positionId = :id WHERE id = :deviceId; </entry> + <!-- PROTOCOL CONFIG --> <entry key='detector.port'>5000</entry> diff --git a/src/org/traccar/database/DataManager.java b/src/org/traccar/database/DataManager.java index db863e8f4..7c2505f77 100644 --- a/src/org/traccar/database/DataManager.java +++ b/src/org/traccar/database/DataManager.java @@ -167,9 +167,7 @@ public class DataManager { Server server = new Server(); server.setRegistration(true); - QueryBuilder.create(dataSource, - "INSERT INTO server (registration, latitude, longitude, zoom)" + - "VALUES (:registration, :latitude, :longitude, :zoom);") + QueryBuilder.create(dataSource, properties.getProperty("database.insertServer")) .setObject(server) .executeUpdate(); @@ -213,46 +211,37 @@ public class DataManager { } public User login(String email, String password) throws SQLException { - return QueryBuilder.create(dataSource, - "SELECT * FROM user WHERE email = :email AND " + - "password = CAST(HASH('SHA256', STRINGTOUTF8(:password), 1000) AS VARCHAR);") + return QueryBuilder.create(dataSource, properties.getProperty("database.loginUser")) .setString("email", email) .setString("password", password) .executeQuerySingle(new User()); } public Collection<User> getUsers() throws SQLException { - return QueryBuilder.create(dataSource, - "SELECT * FROM user;") + return QueryBuilder.create(dataSource, properties.getProperty("database.selectUsersAll")) .executeQuery(new User()); } public void addUser(User user) throws SQLException { - user.setId(QueryBuilder.create(dataSource, - "INSERT INTO user (name, email, password, admin) " + - "VALUES (:name, :email, CAST(HASH('SHA256', STRINGTOUTF8(:password), 1000) AS VARCHAR), :admin);") + user.setId(QueryBuilder.create(dataSource, properties.getProperty("database.insertUser")) .setObject(user) .executeUpdate()); } public void updateUser(User user) throws SQLException { - QueryBuilder.create(dataSource, - "UPDATE user SET name = :name, email = :email, admin = :admin," + - "password = CASEWHEN((SELECT password FROM user WHERE id = :id) = :password, :password, CAST(HASH('SHA256', STRINGTOUTF8(:password), 1000) AS VARCHAR)) WHERE id = :id;") + QueryBuilder.create(dataSource, properties.getProperty("database.updateUser")) .setObject(user) .executeUpdate(); } public void removeUser(User user) throws SQLException { - QueryBuilder.create(dataSource, - "DELETE FROM user WHERE id = :id;") + QueryBuilder.create(dataSource, properties.getProperty("database.deleteUser")) .setObject(user) .executeUpdate(); } public Collection<Permission> getPermissions() throws SQLException { - return QueryBuilder.create(dataSource, - "SELECT userId, deviceId FROM user_device;") + return QueryBuilder.create(dataSource, properties.getProperty("database.getPermissions")) .executeQuery(new Permission()); } @@ -262,45 +251,38 @@ public class DataManager { } public Collection<Device> getDevices(long userId) throws SQLException { - return QueryBuilder.create(dataSource, - "SELECT * FROM device WHERE id IN (" + - "SELECT deviceId FROM user_device WHERE userId = :userId);") + return QueryBuilder.create(dataSource, properties.getProperty("database.selectDevices")) .setLong("userId", userId) .executeQuery(new Device()); } public void addDevice(Device device) throws SQLException { - device.setId(QueryBuilder.create(dataSource, - "INSERT INTO device (name, uniqueId) VALUES (:name, :uniqueId);") + device.setId(QueryBuilder.create(dataSource, properties.getProperty("database.insertDevice")) .setObject(device) .executeUpdate()); } public void updateDevice(Device device) throws SQLException { - QueryBuilder.create(dataSource, - "UPDATE device SET name = :name, uniqueId = :uniqueId WHERE id = :id;") + QueryBuilder.create(dataSource, properties.getProperty("database.updateDevice")) .setObject(device) .executeUpdate(); } public void removeDevice(Device device) throws SQLException { - QueryBuilder.create(dataSource, - "DELETE FROM device WHERE id = :id;") + QueryBuilder.create(dataSource, properties.getProperty("database.removeDevice")) .setObject(device) .executeUpdate(); } public void linkDevice(long userId, long deviceId) throws SQLException { - QueryBuilder.create(dataSource, - "INSERT INTO user_device (userId, deviceId) VALUES (:userId, :deviceId);") + QueryBuilder.create(dataSource, properties.getProperty("database.linkDevice")) .setLong("userId", userId) .setLong("deviceId", deviceId) .executeUpdate(); } public Collection<Position> getPositions(long userId, long deviceId, Date from, Date to) throws SQLException { - return QueryBuilder.create(dataSource, - "SELECT * FROM position WHERE deviceId = :deviceId AND fixTime BETWEEN :from AND :to;") + return QueryBuilder.create(dataSource, properties.getProperty("database.selectPositions")) .setLong("deviceId", deviceId) .setDate("from", from) .setDate("to", to) @@ -318,14 +300,12 @@ public class DataManager { } public Server getServer() throws SQLException { - return QueryBuilder.create(dataSource, - "SELECT * FROM server;") + return QueryBuilder.create(dataSource, properties.getProperty("database.selectServer")) .executeQuerySingle(new Server()); } public void updateServer(Server server) throws SQLException { - QueryBuilder.create(dataSource, - "UPDATE server SET registration = :registration WHERE id = :id;") + QueryBuilder.create(dataSource, properties.getProperty("database.updateServer")) .setObject(server) .executeUpdate(); } |