Merge pull request #1240 from demianalonso/master

Move SQL queries to the config file

6 files changed, 160 insertions, 47 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..b058cd184 100644
--- a/debug.xml
+++ b/debug.xml
@@ -42,7 +42,7 @@
         CREATE TABLE user (
         id INT PRIMARY KEY AUTO_INCREMENT,
         name VARCHAR(1024) NOT NULL,
-        email VARCHAR(1024) NOT NULL UNIQUE,
+        email VARCHAR(256) NOT NULL UNIQUE,
         password VARCHAR(1024) NOT NULL,
         salt VARCHAR(1024) DEFAULT '' NOT NULL,
         readonly BOOLEAN DEFAULT false NOT NULL,
@@ -58,7 +58,7 @@
         CREATE TABLE device (
         id INT PRIMARY KEY AUTO_INCREMENT,
         name VARCHAR(1024) NOT NULL,
-        uniqueId VARCHAR(1024) NOT NULL UNIQUE,
+        uniqueId VARCHAR(256) NOT NULL UNIQUE,
         status VARCHAR(1024),
         lastUpdate TIMESTAMP,
         positionId INT,
@@ -67,8 +67,8 @@
         CREATE TABLE user_device (
         userId INT NOT NULL,
         deviceId INT NOT NULL,
-        read BOOLEAN DEFAULT true NOT NULL,
-        write BOOLEAN DEFAULT true NOT NULL,
+        `read` BOOLEAN DEFAULT true NOT NULL,
+        `write` BOOLEAN DEFAULT true NOT NULL,
         FOREIGN KEY (userId) REFERENCES user(id) ON DELETE CASCADE,
         FOREIGN KEY (deviceId) REFERENCES device(id) ON DELETE CASCADE);
 
@@ -101,7 +101,7 @@
         FOREIGN KEY (deviceId) REFERENCES device(id));
 
         ALTER TABLE device ADD
-        FOREIGN KEY (positionId) REFERENCES position(id);
+        FOREIGN KEY (positionId) REFERENCES `position`(id);
 
         ALTER TABLE device ADD
         FOREIGN KEY (dataId) REFERENCES data(id);
@@ -117,9 +117,81 @@
         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 = :password;
+    </entry>
+
+    <entry key='database.selectUsersAll'>
+        SELECT * FROM user;
+    </entry>
+
+    <entry key='database.insertUser'>
+        INSERT INTO user (name, email, password, admin) 
+        VALUES (:name, :email, :password, :admin);
+    </entry>
+        
+    <entry key='database.updateUser'>
+        UPDATE user SET 
+                name = :name, 
+                email = :email, 
+                admin = :admin
+        WHERE id = :id;
+    </entry>
+
+    <entry key='database.updateUserPassword'>
+        UPDATE user SET password = :password 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 +202,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..9633ada58 100644
--- a/src/org/traccar/database/DataManager.java
+++ b/src/org/traccar/database/DataManager.java
@@ -15,10 +15,12 @@
  */
 package org.traccar.database;
 
-import com.mchange.v2.c3p0.ComboPooledDataSource;
 import java.io.File;
+import java.io.UnsupportedEncodingException;
 import java.net.URL;
 import java.net.URLClassLoader;
+import java.security.MessageDigest;
+import java.security.NoSuchAlgorithmException;
 import java.sql.Connection;
 import java.sql.Driver;
 import java.sql.DriverManager;
@@ -30,10 +32,13 @@ import java.util.Date;
 import java.util.HashMap;
 import java.util.Map;
 import java.util.Properties;
+
 import javax.naming.InitialContext;
 import javax.sql.DataSource;
+
 import org.traccar.Context;
 import org.traccar.helper.DriverDelegate;
+import org.traccar.helper.Hashing;
 import org.traccar.helper.Log;
 import org.traccar.http.JsonConverter;
 import org.traccar.model.Device;
@@ -42,6 +47,8 @@ import org.traccar.model.Position;
 import org.traccar.model.Server;
 import org.traccar.model.User;
 
+import com.mchange.v2.c3p0.ComboPooledDataSource;
+
 public class DataManager {
 
     private static final long DEFAULT_REFRESH_DELAY = 300;
@@ -167,15 +174,13 @@ 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();
 
         mockData(admin.getId());
     }
-
+    
     private void mockData(long userId) {
         if (Boolean.valueOf(Context.getProps().getProperty("database.mock"))) {
             try {
@@ -213,46 +218,43 @@ 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)
+                .setBytes("password", Hashing.sha256(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();
+        
+        if(user.getPassword() != null) {
+            QueryBuilder.create(dataSource, properties.getProperty("database.updateUserPassword"))
+                .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 +264,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 +313,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();
     }
diff --git a/src/org/traccar/database/QueryBuilder.java b/src/org/traccar/database/QueryBuilder.java
index ff26221de..05ec3e35c 100644
--- a/src/org/traccar/database/QueryBuilder.java
+++ b/src/org/traccar/database/QueryBuilder.java
@@ -15,6 +15,7 @@
  */
 package org.traccar.database;
 
+import java.lang.reflect.Array;
 import java.lang.reflect.InvocationTargetException;
 import java.lang.reflect.Method;
 import java.sql.Connection;
@@ -31,7 +32,9 @@ import java.util.HashMap;
 import java.util.LinkedList;
 import java.util.List;
 import java.util.Map;
+
 import javax.sql.DataSource;
+
 import org.traccar.model.Factory;
 
 public class QueryBuilder {
@@ -211,6 +214,23 @@ public class QueryBuilder {
         return this;
     }
     
+    public QueryBuilder setBytes(String name, byte[] value) throws SQLException {
+        for (int i : indexes(name)) {
+            try {
+                if (value == null) {
+                    statement.setNull(i, Types.VARCHAR);
+                } else {
+                    statement.setBytes(i, value);
+                }
+            } catch (SQLException error) {
+                statement.close();
+                connection.close();
+                throw error;
+            }
+        }
+        return this;
+    }
+    
     public QueryBuilder setObject(Object object) throws SQLException {
         
         Method[] methods = object.getClass().getMethods();
@@ -231,6 +251,8 @@ public class QueryBuilder {
                         setString(name, (String) method.invoke(object));
                     } else if (method.getReturnType().equals(Date.class)) {
                         setDate(name, (Date) method.invoke(object));
+                    } else if (method.getReturnType().equals(byte[].class)) {
+                        setBytes(name, (byte[]) method.invoke(object));
                     }
                 } catch (IllegalAccessException error) {
                 } catch (InvocationTargetException error) {
diff --git a/src/org/traccar/helper/Hashing.java b/src/org/traccar/helper/Hashing.java
new file mode 100644
index 000000000..7e7579ede
--- /dev/null
+++ b/src/org/traccar/helper/Hashing.java
@@ -0,0 +1,20 @@
+package org.traccar.helper;
+
+import java.nio.charset.StandardCharsets;
+import java.security.MessageDigest;
+import java.security.NoSuchAlgorithmException;
+
+public class Hashing {
+
+    public static byte[] sha256(String text) {
+        try {
+            MessageDigest md = MessageDigest.getInstance("SHA-256");
+            md.update(text.getBytes(StandardCharsets.UTF_8));
+
+            return md.digest();
+        } catch (NoSuchAlgorithmException e) {
+            throw new RuntimeException(e);
+        }
+    }
+
+}
diff --git a/src/org/traccar/model/User.java b/src/org/traccar/model/User.java
index 2df5f276d..410bc4d74 100644
--- a/src/org/traccar/model/User.java
+++ b/src/org/traccar/model/User.java
@@ -15,6 +15,8 @@
  */
 package org.traccar.model;
 
+import org.traccar.helper.Hashing;
+
 public class User implements Factory {
 
     @Override
@@ -34,9 +36,9 @@ public class User implements Factory {
     public String getEmail() { return email; }
     public void setEmail(String email) { this.email = email; }
     
-    private String password;
-    public String getPassword() { return password; }
-    public void setPassword(String password) { this.password = password; }
+    private byte[] password;
+    public byte[] getPassword() { return password; }
+    public void setPassword(String password) { this.password = Hashing.sha256(password); }
     
     private boolean readonly;
     
@@ -57,5 +59,4 @@ public class User implements Factory {
     private double longitude;
     
     private int zoom;
-
 }