diff options
| -rw-r--r-- | schema/changelog-3.14.xml | 4 | ||||
| -rw-r--r-- | setup/default.xml | 406 | ||||
| -rw-r--r-- | src/org/traccar/Config.java | 4 | ||||
| -rw-r--r-- | src/org/traccar/api/resource/EventResource.java | 2 | ||||
| -rw-r--r-- | src/org/traccar/api/resource/PositionResource.java | 2 | ||||
| -rw-r--r-- | src/org/traccar/database/DataManager.java | 216 | ||||
| -rw-r--r-- | src/org/traccar/database/QueryExtended.java | 27 | ||||
| -rw-r--r-- | src/org/traccar/database/StatisticsManager.java | 2 | ||||
| -rw-r--r-- | src/org/traccar/model/Device.java | 7 | ||||
| -rw-r--r-- | src/org/traccar/model/Position.java | 9 | ||||
| -rw-r--r-- | src/org/traccar/model/Server.java | 2 | ||||
| -rw-r--r-- | src/org/traccar/model/User.java | 6 | ||||
| -rw-r--r-- | test/org/traccar/database/DataManagerTest.java | 79 |
13 files changed, 304 insertions, 462 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 < :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..43f4632da 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.put(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..63d98818d 100644 --- a/src/org/traccar/database/DataManager.java +++ b/src/org/traccar/database/DataManager.java @@ -15,14 +15,18 @@ */ package org.traccar.database; +import java.beans.Introspector; import java.io.File; import java.lang.reflect.Method; 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 +62,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 +125,76 @@ public class DataManager { } } + public static String constructObjectQuery(String action, Class<?> clazz, boolean extended) { + 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) { + boolean skip; + if (extended) { + skip = !method.isAnnotationPresent(QueryExtended.class); + } else { + skip = method.isAnnotationPresent(QueryIgnore.class) + || method.isAnnotationPresent(QueryExtended.class); + } + if (!skip && method.getName().startsWith("get") && method.getParameterTypes().length == 0) { + String name = Introspector.decapitalize(method.getName().substring(3)); + if (action.equals(ACTION_INSERT)) { + fields.append(name).append(", "); + values.append(":").append(name).append(", "); + } else { + fields.append(name).append(" = :").append(name).append(", "); + } + } + } + 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(") "); + result.append("VALUES (").append(values).append(")"); + } else { + result.append("UPDATE ").append(getObjectsTableName(clazz)).append(" SET "); + result.append(fields); + result.append(" WHERE 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 +203,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 extended) { + String queryName; + if (action.equals(ACTION_SELECT_ALL)) { + queryName = "database.select" + clazz.getSimpleName() + "s"; + } else { + queryName = "database." + action.toLowerCase() + clazz.getSimpleName(); + if (extended) { + queryName += "Extended"; + } + } + String query = config.getString(queryName); + if (query == null) { + query = constructObjectQuery(action, clazz, extended); + 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 propertyName = property.getSimpleName(); + if (propertyName.equals("ManagedUser")) { + propertyName = "User"; + } + return Introspector.decapitalize(owner.getSimpleName()) + "_" + Introspector.decapitalize(propertyName); + } + + private static String getObjectsTableName(Class<?> clazz) { + return Introspector.decapitalize(clazz.getSimpleName()) + "s"; + } + private void initDatabaseSchema() throws SQLException, LiquibaseException { if (config.hasKey("database.changelog")) { @@ -155,19 +288,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 +302,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 +336,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 +378,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": @@ -299,47 +403,53 @@ public class DataManager { public static String makeNameId(Class<?> clazz) { String name = clazz.getSimpleName(); - return name.substring(0, 1).toLowerCase() + name.substring(1) + (name.indexOf("Id") == -1 ? "Id" : ""); + return Introspector.decapitalize(name) + (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/QueryExtended.java b/src/org/traccar/database/QueryExtended.java new file mode 100644 index 000000000..07bc2c211 --- /dev/null +++ b/src/org/traccar/database/QueryExtended.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 QueryExtended { +} 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..c8a28404c 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.QueryExtended; +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; + @QueryExtended 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..b71666f87 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.QueryExtended; +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 + @QueryExtended public String getHashedPassword() { return hashedPassword; } @@ -254,6 +259,7 @@ public class User extends ExtendedModel { private String salt; @JsonIgnore + @QueryExtended 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)); + + } + +} |