Implement SQL requests construction

13 files changed, 299 insertions, 461 deletions

diff --git a/schema/changelog-3.14.xml b/schema/changelog-3.14.xml
index 7965bc020..f6cda4c1f 100644
--- a/schema/changelog-3.14.xml
+++ b/schema/changelog-3.14.xml
@@ -61,5 +61,7 @@
     <addForeignKeyConstraint baseTableName="device_driver" baseColumnNames="deviceid" constraintName="fk_device_driver_deviceid" referencedTableName="devices" referencedColumnNames="id" onDelete="CASCADE" />
     <addForeignKeyConstraint baseTableName="device_driver" baseColumnNames="driverid" constraintName="fk_device_driver_driverid" referencedTableName="drivers" referencedColumnNames="id" onDelete="CASCADE" />
 
+    <renameTable oldTableName="server" newTableName="servers" />
+
   </changeSet>
-</databaseChangeLog>
\ No newline at end of file
+</databaseChangeLog>
diff --git a/setup/default.xml b/setup/default.xml
index f0f9ef4a3..ea8c32fbd 100644
--- a/setup/default.xml
+++ b/setup/default.xml
@@ -29,163 +29,16 @@
     <entry key='database.ignoreUnknown'>true</entry>
 
     <entry key='database.changelog'>./schema/changelog-master.xml</entry>
-
-    <entry key='database.selectServers'>
-        SELECT * FROM server
-    </entry>
-
-    <entry key='database.updateServer'>
-        UPDATE server SET
-        registration = :registration,
-        readonly = :readonly,
-        deviceReadonly = :deviceReadonly,
-        map = :map,
-        bingKey = :bingKey,
-        mapUrl = :mapUrl,
-        distanceUnit = :distanceUnit,
-        speedUnit = :speedUnit,
-        latitude = :latitude,
-        longitude = :longitude,
-        zoom = :zoom,
-        twelveHourFormat = :twelveHourFormat,
-        coordinateFormat = :coordinateFormat,
-        forceSettings = :forceSettings,
-        timezone = :timezone,
-        attributes = :attributes
-        WHERE id = :id
-    </entry>
-
+ 
     <entry key='database.loginUser'>
         SELECT * FROM users
         WHERE email = :email
     </entry>
 
-    <entry key='database.selectUsers'>
-        SELECT * FROM users
-    </entry>
-
-    <entry key='database.insertUser'>
-        INSERT INTO users (name, email, phone, hashedPassword, salt, readonly, admin, map, distanceUnit, speedUnit, latitude, longitude, zoom, twelveHourFormat, coordinateFormat, disabled, expirationTime, deviceLimit, userLimit, deviceReadonly, token, timezone, attributes)
-        VALUES (:name, :email, :phone, :hashedPassword, :salt, :readonly, :admin, :map, :distanceUnit, :speedUnit, :latitude, :longitude, :zoom, :twelveHourFormat, :coordinateFormat, :disabled, :expirationTime, :deviceLimit, :userLimit, :deviceReadonly, :token, :timezone, :attributes)
-    </entry>
-
-    <entry key='database.updateUser'>
-        UPDATE users SET
-        name = :name,
-        email = :email,
-        phone = :phone,
-        readonly = :readonly,
-        admin = :admin,
-        map = :map,
-        distanceUnit = :distanceUnit,
-        speedUnit = :speedUnit,
-        latitude = :latitude,
-        longitude = :longitude,
-        zoom = :zoom,
-        twelveHourFormat = :twelveHourFormat,
-        coordinateFormat = :coordinateFormat,
-        disabled = :disabled,
-        expirationTime = :expirationTime,
-        deviceLimit = :deviceLimit,
-        userLimit = :userLimit,
-        deviceReadonly = :deviceReadonly,
-        token = :token,
-        timezone = :timezone,
-        attributes = :attributes
-        WHERE id = :id
-    </entry>
-
-    <entry key='database.updateUserPassword'>
-        UPDATE users SET hashedPassword = :hashedPassword, salt = :salt WHERE id = :id
-    </entry>
-
-    <entry key='database.deleteUser'>
-        DELETE FROM users WHERE id = :id
-    </entry>
-
-    <entry key='database.selectUserDevices'>
-        SELECT userId, deviceId FROM user_device
-    </entry>
-
-    <entry key='database.selectUserGroups'>
-        SELECT userId, groupId FROM user_group
-    </entry>
-
-    <entry key='database.selectDevices'>
-        SELECT * FROM devices
-    </entry>
-
-    <entry key='database.insertDevice'>
-        INSERT INTO devices (name, uniqueId, groupId, attributes, phone, model, contact, category)
-        VALUES (:name, :uniqueId, :groupId, :attributes, :phone, :model, :contact, :category)
-    </entry>
-
-    <entry key='database.updateDevice'>
-        UPDATE devices SET
-        name = :name,
-        uniqueId = :uniqueId,
-        groupId = :groupId,
-        attributes = :attributes,
-        phone = :phone,
-        model = :model,
-        contact = :contact,
-        category = :category
-        WHERE id = :id
-    </entry>
-
-    <entry key='database.updateDeviceStatus'>
-        UPDATE devices SET lastUpdate = :lastUpdate WHERE id = :id
-    </entry>
-
-    <entry key='database.deleteDevice'>
-        DELETE FROM devices WHERE id = :id
-    </entry>
-
-    <entry key='database.linkUserDevice'>
-        INSERT INTO user_device (userId, deviceId) VALUES (:userId, :deviceId)
-    </entry>
-
-    <entry key='database.unlinkUserDevice'>
-        DELETE FROM user_device WHERE userId = :userId AND deviceId = :deviceId
-    </entry>
-
-    <entry key='database.selectGroups'>
-        SELECT * FROM groups
-    </entry>
-
-    <entry key='database.insertGroup'>
-        INSERT INTO groups (name, groupId, attributes) VALUES (:name, :groupId, :attributes)
-    </entry>
-
-    <entry key='database.updateGroup'>
-        UPDATE groups SET name = :name, groupId = :groupId, attributes = :attributes WHERE id = :id
-    </entry>
-
-    <entry key='database.deleteGroup'>
-        DELETE FROM groups WHERE id = :id
-    </entry>
-
-    <entry key='database.linkUserGroup'>
-        INSERT INTO user_group (userId, groupId) VALUES (:userId, :groupId)
-    </entry>
-
-    <entry key='database.unlinkUserGroup'>
-        DELETE FROM user_group WHERE userId = :userId AND groupId = :groupId
-    </entry>
-
     <entry key='database.selectPositions'>
         SELECT * FROM positions WHERE deviceId = :deviceId AND fixTime BETWEEN :from AND :to ORDER BY fixTime
     </entry>
 
-    <entry key='database.selectPosition'>
-        SELECT * FROM positions WHERE id = :id
-    </entry>
-
-    <entry key='database.insertPosition'>
-        INSERT INTO positions (deviceId, protocol, serverTime, deviceTime, fixTime, valid, latitude, longitude, altitude, speed, course, address, attributes, accuracy, network)
-        VALUES (:deviceId, :protocol, :now, :deviceTime, :fixTime, :valid, :latitude, :longitude, :altitude, :speed, :course, :address, :attributes, :accuracy, :network)
-    </entry>
-
     <entry key='database.selectLatestPositions'>
         SELECT positions.* FROM positions INNER JOIN devices ON positions.id = devices.positionid;
     </entry>
@@ -194,102 +47,10 @@
         UPDATE devices SET positionId = :id WHERE id = :deviceId
     </entry>
 
-    <entry key='database.selectEvent'>
-        SELECT * FROM events WHERE id = :id
-    </entry>
-
-    <entry key='database.insertEvent'>
-        INSERT INTO events (type, serverTime, deviceId, positionId, geofenceId, attributes)
-        VALUES (:type, :serverTime, :deviceId, :positionId, :geofenceId, :attributes)
-    </entry>
-
     <entry key='database.selectEvents'>
         SELECT * FROM events WHERE deviceId = :deviceId AND serverTime BETWEEN :from AND :to ORDER BY serverTime
     </entry>
 
-    <entry key='database.selectGeofences'>
-        SELECT * FROM geofences
-    </entry>
-
-    <entry key='database.insertGeofence'>
-        INSERT INTO geofences (name, description, calendarid, area, attributes)
-        VALUES (:name, :description, :calendarid, :area, :attributes)
-    </entry>
-
-    <entry key='database.updateGeofence'>
-        UPDATE geofences SET
-        name = :name,
-        description = :description,
-        calendarid = :calendarid,
-        area = :area,
-        attributes = :attributes
-        WHERE id = :id
-    </entry>
-
-    <entry key='database.deleteGeofence'>
-        DELETE FROM geofences WHERE id = :id
-    </entry>
-
-    <entry key='database.selectUserGeofences'>
-        SELECT userId, geofenceId FROM user_geofence
-    </entry>
-
-    <entry key='database.linkUserGeofence'>
-        INSERT INTO user_geofence (userId, geofenceId) VALUES (:userId, :geofenceId)
-    </entry>
-
-    <entry key='database.unlinkUserGeofence'>
-        DELETE FROM user_geofence WHERE userId = :userId AND geofenceId = :geofenceId
-    </entry>
-
-    <entry key='database.selectGroupGeofences'>
-        SELECT groupId, geofenceId FROM group_geofence
-    </entry>
-
-    <entry key='database.linkGroupGeofence'>
-        INSERT INTO group_geofence (groupId, geofenceId) VALUES (:groupId, :geofenceId)
-    </entry>
-
-    <entry key='database.unlinkGroupGeofence'>
-        DELETE FROM group_geofence WHERE groupId = :groupId AND geofenceId = :geofenceId
-    </entry>
-
-    <entry key='database.selectDeviceGeofences'>
-        SELECT deviceId, geofenceId FROM device_geofence
-    </entry>
-
-    <entry key='database.linkDeviceGeofence'>
-        INSERT INTO device_geofence (deviceId, geofenceId) VALUES (:deviceId, :geofenceId)
-    </entry>
-
-    <entry key='database.unlinkDeviceGeofence'>
-        DELETE FROM device_geofence WHERE deviceId = :deviceId AND geofenceId = :geofenceId
-    </entry>
-
-    <entry key='database.selectNotifications'>
-        SELECT * FROM notifications
-    </entry>
-
-    <entry key='database.insertNotification'>
-        INSERT INTO notifications (userId, type, web, mail, sms, attributes)
-        VALUES (:userId, :type, :web, :mail, :sms, :attributes)
-    </entry>
-
-    <entry key='database.updateNotification'>
-        UPDATE notifications SET
-        userId = :userId,
-        type = :type,
-        web = :web,
-        mail = :mail,
-        sms = :sms,
-        attributes = :attributes
-        WHERE id = :id
-    </entry>
-
-    <entry key='database.deleteNotification'>
-        DELETE FROM notifications WHERE id = :id
-    </entry>
-
     <entry key='database.deletePositions'>
         DELETE FROM positions WHERE serverTime &lt; :serverTime AND id NOT IN (SELECT positionId FROM devices)
     </entry>
@@ -323,171 +84,6 @@
         SELECT * FROM statistics WHERE captureTime BETWEEN :from AND :to ORDER BY captureTime
     </entry>
 
-    <entry key='database.insertStatistics'>
-        INSERT INTO statistics (captureTime, activeUsers, activeDevices, requests, messagesReceived, messagesStored, mailSent, smsSent, geocoderRequests, geolocationRequests, attributes)
-        VALUES (:captureTime, :activeUsers, :activeDevices, :requests, :messagesReceived, :messagesStored, :mailSent, :smsSent, :geocoderRequests, :geolocationRequests, :attributes)
-    </entry>
-    
-    <entry key='database.selectCalendars'>
-        SELECT * FROM calendars
-    </entry>
-
-    <entry key='database.insertCalendar'>
-        INSERT INTO calendars (name, data, attributes)
-        VALUES (:name, :data, :attributes)
-    </entry>
-
-    <entry key='database.updateCalendar'>
-        UPDATE calendars SET
-        name = :name,
-        data = :data,
-        attributes = :attributes
-        WHERE id = :id
-    </entry>
-
-    <entry key='database.deleteCalendar'>
-        DELETE FROM calendars WHERE id = :id
-    </entry>
-
-    <entry key='database.selectUserCalendars'>
-        SELECT userId, calendarId FROM user_calendar
-    </entry>
-
-    <entry key='database.linkUserCalendar'>
-        INSERT INTO user_calendar (userId, calendarId) VALUES (:userId, :calendarId)
-    </entry>
-
-    <entry key='database.unlinkUserCalendar'>
-        DELETE FROM user_calendar WHERE userId = :userId AND calendarId = :calendarId
-    </entry>
-    
-    <entry key='database.selectUserUsers'>
-        SELECT userId, managedUserId FROM user_user
-    </entry>
-
-    <entry key='database.linkUserManagedUser'>
-        INSERT INTO user_user (userId, managedUserId) VALUES (:userId, :managedUserId)
-    </entry>
-
-    <entry key='database.unlinkUserManagedUser'>
-        DELETE FROM user_user WHERE userId = :userId AND managedUserId = :managedUserId
-    </entry>
-
-    <entry key='database.selectAttributes'>
-        SELECT * FROM attributes
-    </entry>
-
-    <entry key='database.insertAttribute'>
-        INSERT INTO attributes (description, type, attribute, expression)
-        VALUES (:description, :type, :attribute, :expression)
-    </entry>
-
-    <entry key='database.updateAttribute'>
-        UPDATE attributes SET
-        description = :description,
-        type = :type,
-        attribute = :attribute,
-        expression = :expression
-        WHERE id = :id
-    </entry>
-
-    <entry key='database.deleteAttribute'>
-        DELETE FROM attributes WHERE id = :id
-    </entry>
-    
-        <entry key='database.selectUserAttributes'>
-        SELECT userId, attributeId FROM user_attribute
-    </entry>
-
-    <entry key='database.linkUserAttribute'>
-        INSERT INTO user_attribute (userId, attributeId) VALUES (:userId, :attributeId)
-    </entry>
-
-    <entry key='database.unlinkUserAttribute'>
-        DELETE FROM user_attribute WHERE userId = :userId AND attributeId = :attributeId
-    </entry>
-
-    <entry key='database.selectGroupAttributes'>
-        SELECT groupId, attributeId FROM group_attribute
-    </entry>
-
-    <entry key='database.linkGroupAttribute'>
-        INSERT INTO group_attribute (groupId, attributeId) VALUES (:groupId, :attributeId)
-    </entry>
-
-    <entry key='database.unlinkGroupAttribute'>
-        DELETE FROM group_attribute WHERE groupId = :groupId AND attributeId = :attributeId
-    </entry>
-
-    <entry key='database.selectDeviceAttributes'>
-        SELECT deviceId, attributeId FROM device_attribute
-    </entry>
-
-    <entry key='database.linkDeviceAttribute'>
-        INSERT INTO device_attribute (deviceId, attributeId) VALUES (:deviceId, :attributeId)
-    </entry>
-
-    <entry key='database.unlinkDeviceAttribute'>
-        DELETE FROM device_attribute WHERE deviceId = :deviceId AND attributeId = :attributeId
-    </entry>
-
-    <entry key='database.selectDrivers'>
-        SELECT * FROM drivers
-    </entry>
-
-    <entry key='database.insertDriver'>
-        INSERT INTO drivers (name, uniqueId, attributes)
-        VALUES (:name, :uniqueId, :attributes)
-    </entry>
-
-    <entry key='database.updateDriver'>
-        UPDATE drivers SET
-        name = :name,
-        uniqueId = :uniqueId,
-        attributes = :attributes
-        WHERE id = :id
-    </entry>
-
-    <entry key='database.deleteDriver'>
-        DELETE FROM drivers WHERE id = :id
-    </entry>
-    
-    <entry key='database.selectUserDrivers'>
-        SELECT userId, driverId FROM user_driver
-    </entry>
-
-    <entry key='database.linkUserDriver'>
-        INSERT INTO user_driver (userId, driverId) VALUES (:userId, :driverId)
-    </entry>
-
-    <entry key='database.unlinkUserDriver'>
-        DELETE FROM user_driver WHERE userId = :userId AND driverId = :driverId
-    </entry>
-
-    <entry key='database.selectGroupDrivers'>
-        SELECT groupId, driverId FROM group_driver
-    </entry>
-
-    <entry key='database.linkGroupDriver'>
-        INSERT INTO group_driver (groupId, driverId) VALUES (:groupId, :driverId)
-    </entry>
-
-    <entry key='database.unlinkGroupDriver'>
-        DELETE FROM group_driver WHERE groupId = :groupId AND driverId = :driverId
-    </entry>
-
-    <entry key='database.selectDeviceDrivers'>
-        SELECT deviceId, driverId FROM device_driver
-    </entry>
-
-    <entry key='database.linkDeviceDriver'>
-        INSERT INTO device_driver (deviceId, driverId) VALUES (:deviceId, :driverId)
-    </entry>
-
-    <entry key='database.unlinkDeviceDriver'>
-        DELETE FROM device_driver WHERE deviceId = :deviceId AND driverId = :driverId
-    </entry>
-
     <!-- PROTOCOL CONFIG -->
 
     <entry key='gps103.port'>5001</entry>
diff --git a/src/org/traccar/Config.java b/src/org/traccar/Config.java
index 0bc3cafaa..a0f3f8c2e 100644
--- a/src/org/traccar/Config.java
+++ b/src/org/traccar/Config.java
@@ -96,4 +96,8 @@ public class Config {
         return key.replaceAll("\\.", "_").replaceAll("(\\p{Lu})", "_$1").toUpperCase();
     }
 
+    public void setString(String key, String value) {
+        properties.putIfAbsent(key, value);
+    }
+
 }
diff --git a/src/org/traccar/api/resource/EventResource.java b/src/org/traccar/api/resource/EventResource.java
index b7fda6f73..a7cf9edbd 100644
--- a/src/org/traccar/api/resource/EventResource.java
+++ b/src/org/traccar/api/resource/EventResource.java
@@ -23,7 +23,7 @@ public class EventResource extends BaseResource {
     @Path("{id}")
     @GET
     public Event get(@PathParam("id") long id) throws SQLException {
-        Event event = Context.getDataManager().getEvent(id);
+        Event event = Context.getDataManager().getObject(Event.class, id);
         Context.getPermissionsManager().checkDevice(getUserId(), event.getDeviceId());
         if (event.getGeofenceId() != 0) {
             Context.getPermissionsManager().checkPermission(Geofence.class, getUserId(), event.getGeofenceId());
diff --git a/src/org/traccar/api/resource/PositionResource.java b/src/org/traccar/api/resource/PositionResource.java
index 6dab51744..c031b842f 100644
--- a/src/org/traccar/api/resource/PositionResource.java
+++ b/src/org/traccar/api/resource/PositionResource.java
@@ -54,7 +54,7 @@ public class PositionResource extends BaseResource {
         if (!positionIds.isEmpty()) {
             ArrayList<Position> positions = new ArrayList<>();
             for (Long positionId : positionIds) {
-                Position position = Context.getDataManager().getPosition(positionId);
+                Position position = Context.getDataManager().getObject(Position.class, positionId);
                 Context.getPermissionsManager().checkDevice(getUserId(), position.getDeviceId());
                 positions.add(position);
             }
diff --git a/src/org/traccar/database/DataManager.java b/src/org/traccar/database/DataManager.java
index 07ad0be44..a753c311b 100644
--- a/src/org/traccar/database/DataManager.java
+++ b/src/org/traccar/database/DataManager.java
@@ -21,8 +21,11 @@ import java.net.URL;
 import java.net.URLClassLoader;
 import java.sql.SQLException;
 import java.text.SimpleDateFormat;
+import java.util.Arrays;
 import java.util.Collection;
 import java.util.Date;
+import java.util.HashSet;
+import java.util.Set;
 
 import javax.naming.InitialContext;
 import javax.sql.DataSource;
@@ -58,6 +61,12 @@ import com.zaxxer.hikari.HikariDataSource;
 
 public class DataManager {
 
+    public static final String ACTION_SELECT_ALL = "selectAll";
+    public static final String ACTION_SELECT = "select";
+    public static final String ACTION_INSERT = "insert";
+    public static final String ACTION_UPDATE = "update";
+    public static final String ACTION_DELETE = "delete";
+
     private final Config config;
 
     private DataSource dataSource;
@@ -115,6 +124,73 @@ public class DataManager {
         }
     }
 
+    public static String constructObjectQuery(String action, Class<?> clazz, boolean additional) {
+        switch (action) {
+            case ACTION_INSERT:
+            case ACTION_UPDATE:
+                StringBuilder result = new StringBuilder();
+                StringBuilder fields = new StringBuilder();
+                StringBuilder values = new StringBuilder();
+
+                Set<Method> methods = new HashSet<>(Arrays.asList(clazz.getMethods()));
+                methods.removeAll(Arrays.asList(Object.class.getMethods()));
+                methods.removeAll(Arrays.asList(BaseModel.class.getMethods()));
+                for (Method method : methods) {
+                    if (method.getName().startsWith("get") && method.getParameterTypes().length == 0
+                            && (additional ? method.isAnnotationPresent(QueryAdditional.class)
+                                : !method.isAnnotationPresent(QueryIgnore.class)
+                                && !method.isAnnotationPresent(QueryAdditional.class))) {
+                        String name = method.getName().substring(3, 4).toLowerCase()
+                                + method.getName().substring(4);
+                        if (action.equals(ACTION_INSERT)) {
+                            fields.append(name).append(", ");
+                            values.append(":").append(name).append(", ");
+                        } else {
+                            fields.append(name).append(" = :").append(name).append(",\n");
+                        }
+                    }
+                }
+                fields.setLength(fields.length() - 2);
+                if (action.equals(ACTION_INSERT)) {
+                    values.setLength(values.length() - 2);
+                    result.append("INSERT INTO ").append(getObjectsTableName(clazz)).append(" (");
+                    result.append(fields).append(")\n");
+                    result.append("VALUES (").append(values).append(")");
+                } else {
+                    result.append("UPDATE ").append(getObjectsTableName(clazz)).append(" SET\n");
+                    result.append(fields);
+                    result.append("\nWHERE id = :id");
+                }
+                return result.toString();
+            case ACTION_SELECT_ALL:
+                return "SELECT * FROM " + getObjectsTableName(clazz);
+            case ACTION_SELECT:
+                return "SELECT * FROM " + getObjectsTableName(clazz) + " WHERE id = :id";
+            case ACTION_DELETE:
+                return "DELETE FROM " + getObjectsTableName(clazz) + " WHERE id = :id";
+            default:
+                throw new IllegalArgumentException("Unknown action");
+            }
+    }
+
+    public static String constructPermissionQuery(String action, Class<?> owner, Class<?> property) {
+        switch (action) {
+        case ACTION_SELECT_ALL:
+            return "SELECT " + makeNameId(owner) + ", " + makeNameId(property) + " FROM "
+                    + getPermissionsTableName(owner, property);
+        case ACTION_INSERT:
+            return "INSERT INTO " + getPermissionsTableName(owner, property)
+            + " (" + makeNameId(owner) + ", " + makeNameId(property) + ") VALUES (:"
+            + makeNameId(owner) + ", :" + makeNameId(property) + ")";
+        case ACTION_DELETE:
+            return "DELETE FROM " + getPermissionsTableName(owner, property)
+            + " WHERE " + makeNameId(owner) + " = :" + makeNameId(owner)
+            + " AND " + makeNameId(property) + " = :" + makeNameId(property);
+        default:
+            throw new IllegalArgumentException("Unknown action");
+    }
+    }
+
     private String getQuery(String key) {
         String query = config.getString(key);
         if (query == null) {
@@ -123,6 +199,59 @@ public class DataManager {
         return query;
     }
 
+    public String getQuery(String action, Class<?> clazz) {
+        return getQuery(action, clazz, false);
+    }
+
+    public String getQuery(String action, Class<?> clazz, boolean additional) {
+        String queryName;
+        if (action.equals(ACTION_SELECT_ALL)) {
+            queryName = "database.select" + clazz.getSimpleName() + "s";
+        } else {
+            queryName = "database." + action.toLowerCase() + clazz.getSimpleName();
+        }
+        String query = config.getString(queryName);
+        if (query == null) {
+            query = constructObjectQuery(action, clazz, additional);
+            config.setString(queryName, query);
+        }
+
+        return query;
+    }
+
+    public String getQuery(String action, Class<?> owner, Class<?> property) {
+        String queryName;
+        if (action.equals(ACTION_SELECT_ALL)) {
+            queryName = "database.select" + owner.getSimpleName() + property.getSimpleName() + "s";
+        } else if (action.equals(ACTION_INSERT)) {
+            queryName = "database.link" + owner.getSimpleName() + property.getSimpleName();
+        } else {
+            queryName = "database.unlink" + owner.getSimpleName() + property.getSimpleName();
+        }
+        String query = config.getString(queryName);
+        if (query == null) {
+            query = constructPermissionQuery(action, owner, property.equals(User.class) ? ManagedUser.class : property);
+            config.setString(queryName, query);
+        }
+
+        return query;
+    }
+
+    private static String getPermissionsTableName(Class<?> owner, Class<?> property) {
+        String ownerName = owner.getSimpleName();
+        String propertyName = property.getSimpleName();
+        if (propertyName.equals("ManagedUser")) {
+            propertyName = "User";
+        }
+        return ownerName.substring(0, 1).toLowerCase() + ownerName.substring(1) + "_"
+            + propertyName.substring(0, 1).toLowerCase() + propertyName.substring(1);
+    }
+
+    private static String getObjectsTableName(Class<?> clazz) {
+        String name = clazz.getSimpleName();
+        return name.substring(0, 1).toLowerCase() + name.substring(1) + "s";
+    }
+
     private void initDatabaseSchema() throws SQLException, LiquibaseException {
 
         if (config.hasKey("database.changelog")) {
@@ -155,19 +284,8 @@ public class DataManager {
         }
     }
 
-    public void updateUser(User user) throws SQLException {
-        QueryBuilder.create(dataSource, getQuery("database.updateUser"))
-                .setObject(user)
-                .executeUpdate();
-        if (user.getHashedPassword() != null) {
-            QueryBuilder.create(dataSource, getQuery("database.updateUserPassword"))
-                .setObject(user)
-                .executeUpdate();
-        }
-    }
-
     public void updateDeviceStatus(Device device) throws SQLException {
-        QueryBuilder.create(dataSource, getQuery("database.updateDeviceStatus"))
+        QueryBuilder.create(dataSource, getQuery(ACTION_UPDATE, Device.class, true))
                 .setObject(device)
                 .executeUpdate();
     }
@@ -180,16 +298,10 @@ public class DataManager {
                 .executeQuery(Position.class);
     }
 
-    public Position getPosition(long positionId) throws SQLException {
-        return QueryBuilder.create(dataSource, getQuery("database.selectPosition"))
-                .setLong("id", positionId)
-                .executeQuerySingle(Position.class);
-    }
-
     public void addPosition(Position position) throws SQLException {
-        position.setId(QueryBuilder.create(dataSource, getQuery("database.insertPosition"), true)
-                .setDate("now", new Date())
+        position.setId(QueryBuilder.create(dataSource, getQuery(ACTION_INSERT, Position.class), true)
                 .setObject(position)
+                .setDate("serverTime", new Date())
                 .executeUpdate());
     }
 
@@ -220,16 +332,10 @@ public class DataManager {
     }
 
     public Server getServer() throws SQLException {
-        return QueryBuilder.create(dataSource, getQuery("database.selectServers"))
+        return QueryBuilder.create(dataSource, getQuery(ACTION_SELECT_ALL, Server.class))
                 .executeQuerySingle(Server.class);
     }
 
-    public Event getEvent(long eventId) throws SQLException {
-        return QueryBuilder.create(dataSource, getQuery("database.selectEvent"))
-                .setLong("id", eventId)
-                .executeQuerySingle(Event.class);
-    }
-
     public Collection<Event> getEvents(long deviceId, Date from, Date to) throws SQLException {
         return QueryBuilder.create(dataSource, getQuery("database.selectEvents"))
                 .setLong("deviceId", deviceId)
@@ -268,12 +374,6 @@ public class DataManager {
                 .executeQuery(Statistics.class);
     }
 
-    public void addStatistics(Statistics statistics) throws SQLException {
-        statistics.setId(QueryBuilder.create(dataSource, getQuery("database.insertStatistics"), true)
-                .setObject(statistics)
-                .executeUpdate());
-    }
-
     public static Class<?> getClassByName(String name) throws ClassNotFoundException {
         switch (name.toLowerCase().replace("id", "")) {
             case "device":
@@ -302,44 +402,50 @@ public class DataManager {
         return name.substring(0, 1).toLowerCase() + name.substring(1) + (name.indexOf("Id") == -1 ? "Id" : "");
     }
 
+    public Collection<Permission> getPermissions(Class<? extends BaseModel> owner, Class<? extends BaseModel> property)
+            throws SQLException, ClassNotFoundException {
+        return QueryBuilder.create(dataSource, getQuery(ACTION_SELECT_ALL, owner, property))
+                .executePermissionsQuery();
+    }
+
     public void linkObject(Class<?> owner, long ownerId, Class<?> property, long propertyId, boolean link)
             throws SQLException {
-        String query = "database." + (link ? "link" : "unlink") + owner.getSimpleName() + property.getSimpleName();
-        QueryBuilder queryBuilder = QueryBuilder.create(dataSource, getQuery(query));
-
-        queryBuilder.setLong(makeNameId(owner), ownerId);
-        queryBuilder.setLong(makeNameId(property), propertyId);
-        queryBuilder.executeUpdate();
+        QueryBuilder.create(dataSource, getQuery(link ? ACTION_INSERT : ACTION_DELETE, owner, property))
+                .setLong(makeNameId(owner), ownerId)
+                .setLong(makeNameId(property), propertyId)
+                .executeUpdate();
     }
 
-    public <T> Collection<T> getObjects(Class<T> clazz) throws SQLException {
-        String query = "database.select" + clazz.getSimpleName() + "s";
-        return QueryBuilder.create(dataSource, getQuery(query)).executeQuery(clazz);
+    public <T extends BaseModel> T getObject(Class<T> clazz, long entityId) throws SQLException {
+        return QueryBuilder.create(dataSource, getQuery(ACTION_SELECT, clazz))
+                .setLong("id", entityId)
+                .executeQuerySingle(clazz);
     }
 
-    public Collection<Permission> getPermissions(Class<? extends BaseModel> owner,
-            Class<? extends BaseModel> property) throws SQLException, ClassNotFoundException {
-        String query = "database.select" + owner.getSimpleName() + property.getSimpleName() + "s";
-        return QueryBuilder.create(dataSource, getQuery(query)).executePermissionsQuery();
+    public <T extends BaseModel> Collection<T> getObjects(Class<T> clazz) throws SQLException {
+        return QueryBuilder.create(dataSource, getQuery(ACTION_SELECT_ALL, clazz))
+                .executeQuery(clazz);
     }
 
     public void addObject(BaseModel entity) throws SQLException {
-        String query = "database.insert" + entity.getClass().getSimpleName();
-        entity.setId(QueryBuilder.create(dataSource, getQuery(query), true)
+        entity.setId(QueryBuilder.create(dataSource, getQuery(ACTION_INSERT, entity.getClass()), true)
                 .setObject(entity)
                 .executeUpdate());
     }
 
     public void updateObject(BaseModel entity) throws SQLException {
-        String query = "database.update" + entity.getClass().getSimpleName();
-        QueryBuilder.create(dataSource, getQuery(query))
+        QueryBuilder.create(dataSource, getQuery(ACTION_UPDATE, entity.getClass()))
                 .setObject(entity)
                 .executeUpdate();
+        if (entity instanceof User && ((User) entity).getHashedPassword() != null) {
+            QueryBuilder.create(dataSource, getQuery(ACTION_UPDATE, User.class, true))
+                    .setObject(entity)
+                    .executeUpdate();
+        }
     }
 
     public void removeObject(Class<? extends BaseModel> clazz, long entityId) throws SQLException {
-        String query = "database.delete" + clazz.getSimpleName();
-        QueryBuilder.create(dataSource, getQuery(query))
+        QueryBuilder.create(dataSource, getQuery(ACTION_DELETE, clazz))
                 .setLong("id", entityId)
                 .executeUpdate();
     }
diff --git a/src/org/traccar/database/QueryAdditional.java b/src/org/traccar/database/QueryAdditional.java
new file mode 100644
index 000000000..7a42c1875
--- /dev/null
+++ b/src/org/traccar/database/QueryAdditional.java
@@ -0,0 +1,27 @@
+/*
+ * Copyright 2017 Anton Tananaev (anton@traccar.org)
+ * Copyright 2017 Andrey Kunitsyn (andrey@traccar.org)
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.traccar.database;
+
+import java.lang.annotation.ElementType;
+import java.lang.annotation.Retention;
+import java.lang.annotation.RetentionPolicy;
+import java.lang.annotation.Target;
+
+@Target(ElementType.METHOD)
+@Retention(RetentionPolicy.RUNTIME)
+public @interface QueryAdditional {
+}
diff --git a/src/org/traccar/database/StatisticsManager.java b/src/org/traccar/database/StatisticsManager.java
index 5b42416ad..06a3e7b35 100644
--- a/src/org/traccar/database/StatisticsManager.java
+++ b/src/org/traccar/database/StatisticsManager.java
@@ -61,7 +61,7 @@ public class StatisticsManager {
             statistics.setGeolocationRequests(geolocationRequests);
 
             try {
-                Context.getDataManager().addStatistics(statistics);
+                Context.getDataManager().addObject(statistics);
             } catch (SQLException e) {
                 Log.warning(e);
             }
diff --git a/src/org/traccar/model/Device.java b/src/org/traccar/model/Device.java
index 6a13c2b77..cd5dd12c4 100644
--- a/src/org/traccar/model/Device.java
+++ b/src/org/traccar/model/Device.java
@@ -18,6 +18,9 @@ package org.traccar.model;
 import java.util.Date;
 import java.util.List;
 
+import org.traccar.database.QueryAdditional;
+import org.traccar.database.QueryIgnore;
+
 public class Device extends ExtendedModel {
 
     private String name;
@@ -46,6 +49,7 @@ public class Device extends ExtendedModel {
 
     private String status;
 
+    @QueryIgnore
     public String getStatus() {
         return status != null ? status : STATUS_OFFLINE;
     }
@@ -56,6 +60,7 @@ public class Device extends ExtendedModel {
 
     private Date lastUpdate;
 
+    @QueryAdditional
     public Date getLastUpdate() {
         if (lastUpdate != null) {
             return new Date(lastUpdate.getTime());
@@ -74,6 +79,7 @@ public class Device extends ExtendedModel {
 
     private long positionId;
 
+    @QueryIgnore
     public long getPositionId() {
         return positionId;
     }
@@ -94,6 +100,7 @@ public class Device extends ExtendedModel {
 
     private List<Long> geofenceIds;
 
+    @QueryIgnore
     public List<Long> getGeofenceIds() {
         return geofenceIds;
     }
diff --git a/src/org/traccar/model/Position.java b/src/org/traccar/model/Position.java
index 4412b012c..66a6f91ce 100644
--- a/src/org/traccar/model/Position.java
+++ b/src/org/traccar/model/Position.java
@@ -17,6 +17,8 @@ package org.traccar.model;
 
 import java.util.Date;
 
+import org.traccar.database.QueryIgnore;
+
 public class Position extends Message {
 
     public static final String KEY_ORIGINAL = "raw";
@@ -196,6 +198,7 @@ public class Position extends Message {
 
     private boolean outdated;
 
+    @QueryIgnore
     public boolean getOutdated() {
         return outdated;
     }
@@ -294,4 +297,10 @@ public class Position extends Message {
         this.network = network;
     }
 
+    @Override
+    @QueryIgnore
+    public String getType() {
+        return super.getType();
+    }
+
 }
diff --git a/src/org/traccar/model/Server.java b/src/org/traccar/model/Server.java
index cd5b0624e..246be0b4f 100644
--- a/src/org/traccar/model/Server.java
+++ b/src/org/traccar/model/Server.java
@@ -17,10 +17,12 @@ package org.traccar.model;
 
 import java.util.TimeZone;
 
+import org.traccar.database.QueryIgnore;
 import org.traccar.helper.Log;
 
 public class Server extends ExtendedModel {
 
+    @QueryIgnore
     public String getVersion() {
         return Log.getAppVersion();
     }
diff --git a/src/org/traccar/model/User.java b/src/org/traccar/model/User.java
index 6e6a079c2..72b53ee5d 100644
--- a/src/org/traccar/model/User.java
+++ b/src/org/traccar/model/User.java
@@ -16,6 +16,9 @@
 package org.traccar.model;
 
 import com.fasterxml.jackson.annotation.JsonIgnore;
+
+import org.traccar.database.QueryAdditional;
+import org.traccar.database.QueryIgnore;
 import org.traccar.helper.Hashing;
 
 import java.util.Date;
@@ -228,6 +231,7 @@ public class User extends ExtendedModel {
         }
     }
 
+    @QueryIgnore
     public String getPassword() {
         return null;
     }
@@ -243,6 +247,7 @@ public class User extends ExtendedModel {
     private String hashedPassword;
 
     @JsonIgnore
+    @QueryAdditional
     public String getHashedPassword() {
         return hashedPassword;
     }
@@ -254,6 +259,7 @@ public class User extends ExtendedModel {
     private String salt;
 
     @JsonIgnore
+    @QueryAdditional
     public String getSalt() {
         return salt;
     }
diff --git a/test/org/traccar/database/DataManagerTest.java b/test/org/traccar/database/DataManagerTest.java
new file mode 100644
index 000000000..3383c3d22
--- /dev/null
+++ b/test/org/traccar/database/DataManagerTest.java
@@ -0,0 +1,79 @@
+package org.traccar.database;
+
+import org.junit.Assert;
+import org.junit.Test;
+import org.traccar.model.Attribute;
+import org.traccar.model.Device;
+import org.traccar.model.Driver;
+import org.traccar.model.Geofence;
+import org.traccar.model.Group;
+import org.traccar.model.ManagedUser;
+import org.traccar.model.Position;
+import org.traccar.model.User;
+
+public class DataManagerTest {
+
+    @Test
+    public void constructObjectQuery() {
+        Assert.assertEquals("SELECT * FROM users",
+                DataManager.constructObjectQuery(DataManager.ACTION_SELECT_ALL, User.class, false));
+        Assert.assertEquals("DELETE FROM groups WHERE id = :id",
+                DataManager.constructObjectQuery(DataManager.ACTION_DELETE, Group.class, false));
+        Assert.assertEquals("SELECT * FROM positions WHERE id = :id",
+                DataManager.constructObjectQuery(DataManager.ACTION_SELECT, Position.class, false));
+
+        String insertDevice = DataManager.constructObjectQuery(DataManager.ACTION_INSERT, Device.class, false);
+        Assert.assertFalse(insertDevice.contains("class"));
+        Assert.assertFalse(insertDevice.contains("id"));
+        Assert.assertFalse(insertDevice.contains("status"));
+        Assert.assertFalse(insertDevice.contains("lastUpdate"));
+        Assert.assertFalse(insertDevice.contains("geofenceIds"));
+
+        String updateDeviceStatus = DataManager.constructObjectQuery("update", Device.class, true);
+        Assert.assertTrue(updateDeviceStatus.contains("lastUpdate"));
+
+        String updateUser = DataManager.constructObjectQuery(DataManager.ACTION_UPDATE, User.class, false);
+        Assert.assertFalse(updateUser.contains("class"));
+        Assert.assertFalse(updateUser.contains("password"));
+        Assert.assertFalse(updateUser.contains("salt"));
+
+        String updateUserPassword = DataManager.constructObjectQuery(DataManager.ACTION_UPDATE, User.class, true);
+        Assert.assertFalse(updateUserPassword.contains("name"));
+        Assert.assertTrue(updateUserPassword.contains("hashedPassword"));
+        Assert.assertTrue(updateUserPassword.contains("salt"));
+
+        String insertPosition = DataManager.constructObjectQuery(DataManager.ACTION_INSERT, Position.class, false);
+        Assert.assertFalse(insertPosition.contains("type"));
+        Assert.assertFalse(insertPosition.contains("outdated"));
+
+    }
+
+    @Test
+    public void constructPermissionsQuery() {
+        Assert.assertEquals("SELECT userId, deviceId FROM user_device",
+                DataManager.constructPermissionQuery(DataManager.ACTION_SELECT_ALL, User.class, Device.class));
+
+        Assert.assertEquals("SELECT userId, managedUserId FROM user_user",
+                DataManager.constructPermissionQuery(DataManager.ACTION_SELECT_ALL, User.class, ManagedUser.class));
+
+        Assert.assertEquals("SELECT deviceId, driverId FROM device_driver",
+                DataManager.constructPermissionQuery(DataManager.ACTION_SELECT_ALL, Device.class, Driver.class));
+
+        Assert.assertEquals("SELECT groupId, geofenceId FROM group_geofence",
+                DataManager.constructPermissionQuery(DataManager.ACTION_SELECT_ALL, Group.class, Geofence.class));
+
+        Assert.assertEquals("INSERT INTO user_device (userId, deviceId) VALUES (:userId, :deviceId)",
+                DataManager.constructPermissionQuery(DataManager.ACTION_INSERT, User.class, Device.class));
+
+        Assert.assertEquals("DELETE FROM user_user WHERE userId = :userId AND managedUserId = :managedUserId",
+                DataManager.constructPermissionQuery(DataManager.ACTION_DELETE, User.class, ManagedUser.class));
+
+        Assert.assertEquals("INSERT INTO device_geofence (deviceId, geofenceId) VALUES (:deviceId, :geofenceId)",
+                DataManager.constructPermissionQuery(DataManager.ACTION_INSERT, Device.class, Geofence.class));
+
+        Assert.assertEquals("DELETE FROM group_attribute WHERE groupId = :groupId AND attributeId = :attributeId",
+                DataManager.constructPermissionQuery(DataManager.ACTION_DELETE, Group.class, Attribute.class));
+
+    }
+
+}