Move schema query to config

2 files changed, 86 insertions, 96 deletions

diff --git a/debug.xml b/debug.xml
index d0ce8c9e4..c6ac1d859 100644
--- a/debug.xml
+++ b/debug.xml
@@ -4,6 +4,8 @@
 
 <properties>
 
+    <!-- DATABASE CONFIG -->
+
     <!-- Global configuration -->
     <!--<entry key='database.driverFile'>hsqldb.jar</entry>-->
     <entry key='database.driver'>org.h2.Driver</entry>
@@ -12,38 +14,100 @@
     <entry key='database.password'></entry>
     <entry key='database.mock'>true</entry>
 
-    <!-->
-        id - Long
-        imei - String
-    <-->
+    <entry key='database.createSchema'>
+        CREATE TABLE user (
+        id INT PRIMARY KEY AUTO_INCREMENT,
+        name VARCHAR(1024) NOT NULL,
+        email VARCHAR(1024) NOT NULL UNIQUE,
+        password VARCHAR(1024) NOT NULL,
+        salt VARCHAR(1024) NOT NULL,
+        readonly BOOLEAN DEFAULT false NOT NULL,
+        admin BOOLEAN DEFAULT false NOT NULL,
+        map VARCHAR(1024) DEFAULT 'osm' NOT NULL,
+        language VARCHAR(1024) DEFAULT 'en' NOT NULL,
+        distanceUnit VARCHAR(1024) DEFAULT 'km' NOT NULL,
+        speedUnit VARCHAR(1024) DEFAULT 'kmh' NOT NULL,
+        latitude DOUBLE DEFAULT 0 NOT NULL,
+        longitude DOUBLE DEFAULT 0 NOT NULL,
+        zoom INT DEFAULT 0 NOT NULL);
+
+        CREATE TABLE device (
+        id INT PRIMARY KEY AUTO_INCREMENT,
+        name VARCHAR(1024) NOT NULL,
+        uniqueId VARCHAR(1024) NOT NULL UNIQUE,
+        status VARCHAR(1024),
+        lastUpdate TIMESTAMP,
+        positionId INT,
+        dataId INT);
+
+        CREATE TABLE user_device (
+        userId INT NOT NULL,
+        deviceId INT 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);
+
+        CREATE INDEX user_device_user_id ON user_device(userId);
+
+        CREATE TABLE position (
+        id INT PRIMARY KEY AUTO_INCREMENT,
+        protocol VARCHAR(1024),
+        deviceId INT NOT NULL,
+        serverTime TIMESTAMP NOT NULL,
+        deviceTime TIMESTAMP NOT NULL,
+        fixTime TIMESTAMP NOT NULL,
+        valid BOOLEAN NOT NULL,
+        latitude DOUBLE NOT NULL,
+        longitude DOUBLE NOT NULL,
+        altitude DOUBLE NOT NULL,
+        speed DOUBLE NOT NULL,
+        course DOUBLE NOT NULL,
+        address VARCHAR(1024),
+        other VARCHAR(8192) NOT NULL,
+        FOREIGN KEY (deviceId) REFERENCES device(id) ON DELETE CASCADE);
+
+        CREATE TABLE data (
+        id INT PRIMARY KEY AUTO_INCREMENT,
+        protocol VARCHAR(1024),
+        deviceId INT NOT NULL,
+        serverTime TIMESTAMP NOT NULL,
+        deviceTime TIMESTAMP NOT NULL,
+        other VARCHAR(8192) NOT NULL,
+        FOREIGN KEY (deviceId) REFERENCES device(id));
+
+        ALTER TABLE device ADD
+        FOREIGN KEY (positionId) REFERENCES position(id);
+
+        ALTER TABLE device ADD
+        FOREIGN KEY (dataId) REFERENCES data(id);
+
+        CREATE TABLE server (
+        id INT PRIMARY KEY AUTO_INCREMENT,
+        registration BOOLEAN NOT NULL,
+        latitude DOUBLE NOT NULL,
+        longitude DOUBLE NOT NULL,
+        zoom INT NOT NULL);
+
+        CREATE TABLE traccar1 (
+        id INT PRIMARY KEY AUTO_INCREMENT);
+    </entry>
+
     <entry key='database.selectDeviceAll'>
         SELECT * FROM device;
     </entry>
 
-    <!-->
-        device_id - Long
-        time - Date
-        valid - Boolean
-        latitude - Double
-        longitude - Double
-        speed - Double
-        course - Double
-        power - Double (NULL for some protocols)
-        extended_info - String (XML)
-    <-->
     <entry key='database.insertPosition'>
         INSERT INTO position (deviceId, serverTime, deviceTime, fixTime, valid, latitude, longitude, altitude, speed, course, address, other)
         VALUES (:deviceId, NOW(), :time, :time, :valid, :latitude, :longitude, :altitude, :speed, :course, :address, :other);
     </entry>
 
-    <!-->
-        device_id - Long
-        id - Long
-    <-->
     <entry key='database.updateLatestPosition'>
         UPDATE device SET positionId = :id WHERE id = :deviceId;
     </entry>
 
+    <!-- SERVER CONFIG -->
+
     <!-- Web interface -->
     <entry key='http.enable'>true</entry>
     <!--<entry key='http.address'></entry>-->
@@ -71,6 +135,8 @@
     <entry key='logger.level'>all</entry>
     <entry key='logger.file'>target/tracker-server.log</entry>
 
+    <!-- PROTOCOL CONFIG -->
+
     <!-- Protocol detector -->
     <entry key='detector.enable'>true</entry>
     <entry key='detector.port'>5000</entry>
diff --git a/src/org/traccar/database/DataManager.java b/src/org/traccar/database/DataManager.java
index d1c10ec87..8ca89d558 100644
--- a/src/org/traccar/database/DataManager.java
+++ b/src/org/traccar/database/DataManager.java
@@ -156,83 +156,7 @@ public class DataManager {
             return;
         }
         
-        QueryBuilder.create(dataSource,
-                "CREATE TABLE user (" +
-                "id INT PRIMARY KEY AUTO_INCREMENT," +
-                "name VARCHAR(1024) NOT NULL," +
-                "email VARCHAR(1024) NOT NULL UNIQUE," +
-                "password VARCHAR(1024) NOT NULL," +
-                "salt VARCHAR(1024) NOT NULL," +
-                "readonly BOOLEAN DEFAULT false NOT NULL," +
-                "admin BOOLEAN DEFAULT false NOT NULL," +
-                "map VARCHAR(1024) DEFAULT 'osm' NOT NULL," +
-                "language VARCHAR(1024) DEFAULT 'en' NOT NULL," +
-                "distanceUnit VARCHAR(1024) DEFAULT 'km' NOT NULL," +
-                "speedUnit VARCHAR(1024) DEFAULT 'kmh' NOT NULL," +
-                "latitude DOUBLE DEFAULT 0 NOT NULL," +
-                "longitude DOUBLE DEFAULT 0 NOT NULL," +
-                "zoom INT DEFAULT 0 NOT NULL);" +
-
-                "CREATE TABLE device (" +
-                "id INT PRIMARY KEY AUTO_INCREMENT," +
-                "name VARCHAR(1024) NOT NULL," +
-                "uniqueId VARCHAR(1024) NOT NULL UNIQUE," +
-                "status VARCHAR(1024)," +
-                "lastUpdate TIMESTAMP," +
-                "positionId INT," +
-                "dataId INT);" +
-
-                "CREATE TABLE user_device (" +
-                "userId INT NOT NULL," +
-                "deviceId INT 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);" +
-
-                "CREATE INDEX user_device_user_id ON user_device(userId);" +
-
-                "CREATE TABLE position (" +
-                "id INT PRIMARY KEY AUTO_INCREMENT," +
-                "protocol VARCHAR(1024)," +
-                "deviceId INT NOT NULL," +
-                "serverTime TIMESTAMP NOT NULL," +
-                "deviceTime TIMESTAMP NOT NULL," +
-                "fixTime TIMESTAMP NOT NULL," +
-                "valid BOOLEAN NOT NULL," +
-                "latitude DOUBLE NOT NULL," +
-                "longitude DOUBLE NOT NULL," +
-                "altitude DOUBLE NOT NULL," +
-                "speed DOUBLE NOT NULL," +
-                "course DOUBLE NOT NULL," +
-                "address VARCHAR(1024)," +
-                "other VARCHAR(8192) NOT NULL," +
-                "FOREIGN KEY (deviceId) REFERENCES device(id) ON DELETE CASCADE);" +
-
-                "CREATE TABLE data (" +
-                "id INT PRIMARY KEY AUTO_INCREMENT," +
-                "protocol VARCHAR(1024)," +
-                "deviceId INT NOT NULL," +
-                "serverTime TIMESTAMP NOT NULL," +
-                "deviceTime TIMESTAMP NOT NULL," +
-                "other VARCHAR(8192) NOT NULL," +
-                "FOREIGN KEY (deviceId) REFERENCES device(id));" +
-
-                "ALTER TABLE device ADD " +
-                "FOREIGN KEY (positionId) REFERENCES position(id);" +
-
-                "ALTER TABLE device ADD " +
-                "FOREIGN KEY (dataId) REFERENCES data(id);" +
-
-                "CREATE TABLE server (" +
-                "id INT PRIMARY KEY AUTO_INCREMENT," +
-                "registration BOOLEAN NOT NULL," +
-                "latitude DOUBLE NOT NULL," +
-                "longitude DOUBLE NOT NULL," +
-                "zoom INT NOT NULL);" +
-
-                "CREATE TABLE traccar1 (" +
-                "id INT PRIMARY KEY AUTO_INCREMENT);").executeUpdate();
+        QueryBuilder.create(dataSource, properties.getProperty("database.createSchema")).executeUpdate();
         
         User admin = new User();
         admin.setName("admin");