aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorDemian <demianalonso@gmail.com>2015-06-09 13:10:50 -0300
committerDemian <demianalonso@gmail.com>2015-06-09 13:11:38 -0300
commitb70876b7a3b852d0f0b9aade30d6a9405c054ac5 (patch)
treea08191b13ea2faaf3eec125f98c0c57d494ae487
parentee4c07115d3bc3e58b92310f54bee7bd616ec669 (diff)
downloadtraccar-server-b70876b7a3b852d0f0b9aade30d6a9405c054ac5.tar.gz
traccar-server-b70876b7a3b852d0f0b9aade30d6a9405c054ac5.tar.bz2
traccar-server-b70876b7a3b852d0f0b9aade30d6a9405c054ac5.zip
Moved all the SQL queries to the configuration file
-rw-r--r--.gitignore4
-rw-r--r--debug.xml70
-rw-r--r--src/org/traccar/database/DataManager.java50
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/
diff --git a/debug.xml b/debug.xml
index 6ca0c224a..bd747c97f 100644
--- a/debug.xml
+++ b/debug.xml
@@ -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();
}