/* * Copyright 2012 - 2017 Anton Tananaev (anton@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.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.Collection; import java.util.Date; import javax.naming.InitialContext; import javax.sql.DataSource; import liquibase.Contexts; import liquibase.Liquibase; import liquibase.database.Database; import liquibase.database.DatabaseFactory; import liquibase.exception.LiquibaseException; import liquibase.resource.FileSystemResourceAccessor; import liquibase.resource.ResourceAccessor; import org.traccar.Config; import org.traccar.helper.Log; import org.traccar.model.AttributeAlias; import org.traccar.model.AttributePermission; import org.traccar.model.Calendar; import org.traccar.model.CalendarPermission; import org.traccar.model.Attribute; import org.traccar.model.Device; import org.traccar.model.DeviceAttribute; import org.traccar.model.DevicePermission; import org.traccar.model.Event; import org.traccar.model.Geofence; import org.traccar.model.Group; import org.traccar.model.GroupAttribute; import org.traccar.model.GroupGeofence; import org.traccar.model.GroupPermission; import org.traccar.model.Notification; import org.traccar.model.Position; import org.traccar.model.Server; import org.traccar.model.Statistics; import org.traccar.model.User; import org.traccar.model.UserPermission; import org.traccar.model.DeviceGeofence; import org.traccar.model.GeofencePermission; import com.zaxxer.hikari.HikariConfig; import com.zaxxer.hikari.HikariDataSource; public class DataManager { private final Config config; private DataSource dataSource; public DataManager(Config config) throws Exception { this.config = config; initDatabase(); initDatabaseSchema(); } public DataSource getDataSource() { return dataSource; } private void initDatabase() throws Exception { String jndiName = config.getString("database.jndi"); if (jndiName != null) { dataSource = (DataSource) new InitialContext().lookup(jndiName); } else { String driverFile = config.getString("database.driverFile"); if (driverFile != null) { URLClassLoader classLoader = (URLClassLoader) ClassLoader.getSystemClassLoader(); Method method = URLClassLoader.class.getDeclaredMethod("addURL", URL.class); method.setAccessible(true); method.invoke(classLoader, new File(driverFile).toURI().toURL()); } String driver = config.getString("database.driver"); if (driver != null) { Class.forName(driver); } HikariConfig hikariConfig = new HikariConfig(); hikariConfig.setDriverClassName(config.getString("database.driver")); hikariConfig.setJdbcUrl(config.getString("database.url")); hikariConfig.setUsername(config.getString("database.user")); hikariConfig.setPassword(config.getString("database.password")); hikariConfig.setConnectionInitSql(config.getString("database.checkConnection", "SELECT 1")); hikariConfig.setIdleTimeout(600000); int maxPoolSize = config.getInteger("database.maxPoolSize"); if (maxPoolSize != 0) { hikariConfig.setMaximumPoolSize(maxPoolSize); } dataSource = new HikariDataSource(hikariConfig); } } private String getQuery(String key) { String query = config.getString(key); if (query == null) { Log.info("Query not provided: " + key); } return query; } private void initDatabaseSchema() throws SQLException, LiquibaseException { if (config.hasKey("database.changelog")) { ResourceAccessor resourceAccessor = new FileSystemResourceAccessor(); Database database = DatabaseFactory.getInstance().openDatabase( config.getString("database.url"), config.getString("database.user"), config.getString("database.password"), null, resourceAccessor); Liquibase liquibase = new Liquibase( config.getString("database.changelog"), resourceAccessor, database); liquibase.clearCheckSums(); liquibase.update(new Contexts()); } } public User login(String email, String password) throws SQLException { User user = QueryBuilder.create(dataSource, getQuery("database.loginUser")) .setString("email", email) .executeQuerySingle(User.class); if (user != null && user.isPasswordValid(password)) { return user; } else { return null; } } public Collection<User> getUsers() throws SQLException { return QueryBuilder.create(dataSource, getQuery("database.selectUsersAll")) .executeQuery(User.class); } public void addUser(User user) throws SQLException { user.setId(QueryBuilder.create(dataSource, getQuery("database.insertUser"), true) .setObject(user) .executeUpdate()); } 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 removeUser(long userId) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.deleteUser")) .setLong("id", userId) .executeUpdate(); } public Collection<DevicePermission> getDevicePermissions() throws SQLException { return QueryBuilder.create(dataSource, getQuery("database.selectDevicePermissions")) .executeQuery(DevicePermission.class); } public Collection<GroupPermission> getGroupPermissions() throws SQLException { return QueryBuilder.create(dataSource, getQuery("database.selectGroupPermissions")) .executeQuery(GroupPermission.class); } public Collection<Device> getAllDevices() throws SQLException { return QueryBuilder.create(dataSource, getQuery("database.selectDevicesAll")) .executeQuery(Device.class); } public void addDevice(Device device) throws SQLException { device.setId(QueryBuilder.create(dataSource, getQuery("database.insertDevice"), true) .setObject(device) .executeUpdate()); } public void updateDevice(Device device) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.updateDevice")) .setObject(device) .executeUpdate(); } public void updateDeviceStatus(Device device) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.updateDeviceStatus")) .setObject(device) .executeUpdate(); } public void removeDevice(long deviceId) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.deleteDevice")) .setLong("id", deviceId) .executeUpdate(); } public void linkDevice(long userId, long deviceId) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.linkDevice")) .setLong("userId", userId) .setLong("deviceId", deviceId) .executeUpdate(); } public void unlinkDevice(long userId, long deviceId) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.unlinkDevice")) .setLong("userId", userId) .setLong("deviceId", deviceId) .executeUpdate(); } public Collection<Group> getAllGroups() throws SQLException { return QueryBuilder.create(dataSource, getQuery("database.selectGroupsAll")) .executeQuery(Group.class); } public void addGroup(Group group) throws SQLException { group.setId(QueryBuilder.create(dataSource, getQuery("database.insertGroup"), true) .setObject(group) .executeUpdate()); } public void updateGroup(Group group) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.updateGroup")) .setObject(group) .executeUpdate(); } public void removeGroup(long groupId) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.deleteGroup")) .setLong("id", groupId) .executeUpdate(); } public void linkGroup(long userId, long groupId) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.linkGroup")) .setLong("userId", userId) .setLong("groupId", groupId) .executeUpdate(); } public void unlinkGroup(long userId, long groupId) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.unlinkGroup")) .setLong("userId", userId) .setLong("groupId", groupId) .executeUpdate(); } public Collection<Position> getPositions(long deviceId, Date from, Date to) throws SQLException { return QueryBuilder.create(dataSource, getQuery("database.selectPositions")) .setLong("deviceId", deviceId) .setDate("from", from) .setDate("to", to) .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()) .setObject(position) .executeUpdate()); } public void updateLatestPosition(Position position) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.updateLatestPosition")) .setDate("now", new Date()) .setObject(position) .executeUpdate(); } public Collection<Position> getLatestPositions() throws SQLException { return QueryBuilder.create(dataSource, getQuery("database.selectLatestPositions")) .executeQuery(Position.class); } public void clearHistory() throws SQLException { long historyDays = config.getInteger("database.historyDays"); if (historyDays != 0) { Date timeLimit = new Date(System.currentTimeMillis() - historyDays * 24 * 3600 * 1000); Log.debug("Clearing history earlier than " + new SimpleDateFormat(Log.DATE_FORMAT).format(timeLimit)); QueryBuilder.create(dataSource, getQuery("database.deletePositions")) .setDate("serverTime", timeLimit) .executeUpdate(); QueryBuilder.create(dataSource, getQuery("database.deleteEvents")) .setDate("serverTime", timeLimit) .executeUpdate(); } } public Server getServer() throws SQLException { return QueryBuilder.create(dataSource, getQuery("database.selectServers")) .executeQuerySingle(Server.class); } public void updateServer(Server server) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.updateServer")) .setObject(server) .executeUpdate(); } public Event getEvent(long eventId) throws SQLException { return QueryBuilder.create(dataSource, getQuery("database.selectEvent")) .setLong("id", eventId) .executeQuerySingle(Event.class); } public void addEvent(Event event) throws SQLException { event.setId(QueryBuilder.create(dataSource, getQuery("database.insertEvent"), true) .setObject(event) .executeUpdate()); } public Collection<Event> getEvents(long deviceId, Date from, Date to) throws SQLException { return QueryBuilder.create(dataSource, getQuery("database.selectEvents")) .setLong("deviceId", deviceId) .setDate("from", from) .setDate("to", to) .executeQuery(Event.class); } public Collection<Geofence> getGeofences() throws SQLException { return QueryBuilder.create(dataSource, getQuery("database.selectGeofencesAll")) .executeQuery(Geofence.class); } public void addGeofence(Geofence geofence) throws SQLException { geofence.setId(QueryBuilder.create(dataSource, getQuery("database.insertGeofence"), true) .setObject(geofence) .executeUpdate()); } public void updateGeofence(Geofence geofence) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.updateGeofence")) .setObject(geofence) .executeUpdate(); } public void removeGeofence(long geofenceId) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.deleteGeofence")) .setLong("id", geofenceId) .executeUpdate(); } public Collection<GeofencePermission> getGeofencePermissions() throws SQLException { return QueryBuilder.create(dataSource, getQuery("database.selectGeofencePermissions")) .executeQuery(GeofencePermission.class); } public void linkGeofence(long userId, long geofenceId) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.linkGeofence")) .setLong("userId", userId) .setLong("geofenceId", geofenceId) .executeUpdate(); } public void unlinkGeofence(long userId, long geofenceId) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.unlinkGeofence")) .setLong("userId", userId) .setLong("geofenceId", geofenceId) .executeUpdate(); } public Collection<GroupGeofence> getGroupGeofences() throws SQLException { return QueryBuilder.create(dataSource, getQuery("database.selectGroupGeofences")) .executeQuery(GroupGeofence.class); } public void linkGroupGeofence(long groupId, long geofenceId) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.linkGroupGeofence")) .setLong("groupId", groupId) .setLong("geofenceId", geofenceId) .executeUpdate(); } public void unlinkGroupGeofence(long groupId, long geofenceId) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.unlinkGroupGeofence")) .setLong("groupId", groupId) .setLong("geofenceId", geofenceId) .executeUpdate(); } public Collection<DeviceGeofence> getDeviceGeofences() throws SQLException { return QueryBuilder.create(dataSource, getQuery("database.selectDeviceGeofences")) .executeQuery(DeviceGeofence.class); } public void linkDeviceGeofence(long deviceId, long geofenceId) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.linkDeviceGeofence")) .setLong("deviceId", deviceId) .setLong("geofenceId", geofenceId) .executeUpdate(); } public void unlinkDeviceGeofence(long deviceId, long geofenceId) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.unlinkDeviceGeofence")) .setLong("deviceId", deviceId) .setLong("geofenceId", geofenceId) .executeUpdate(); } public Collection<Notification> getNotifications() throws SQLException { return QueryBuilder.create(dataSource, getQuery("database.selectNotifications")) .executeQuery(Notification.class); } public void addNotification(Notification notification) throws SQLException { notification.setId(QueryBuilder.create(dataSource, getQuery("database.insertNotification"), true) .setObject(notification) .executeUpdate()); } public void updateNotification(Notification notification) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.updateNotification")) .setObject(notification) .executeUpdate(); } public void removeNotification(Notification notification) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.deleteNotification")) .setLong("id", notification.getId()) .executeUpdate(); } public Collection<AttributeAlias> getAttributeAliases() throws SQLException { return QueryBuilder.create(dataSource, getQuery("database.selectAttributeAliases")) .executeQuery(AttributeAlias.class); } public void addAttributeAlias(AttributeAlias attributeAlias) throws SQLException { attributeAlias.setId(QueryBuilder.create(dataSource, getQuery("database.insertAttributeAlias"), true) .setObject(attributeAlias) .executeUpdate()); } public void updateAttributeAlias(AttributeAlias attributeAlias) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.updateAttributeAlias")) .setObject(attributeAlias) .executeUpdate(); } public void removeAttributeAlias(long attributeAliasId) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.deleteAttributeAlias")) .setLong("id", attributeAliasId) .executeUpdate(); } public Collection<Statistics> getStatistics(Date from, Date to) throws SQLException { return QueryBuilder.create(dataSource, getQuery("database.selectStatistics")) .setDate("from", from) .setDate("to", to) .executeQuery(Statistics.class); } public void addStatistics(Statistics statistics) throws SQLException { statistics.setId(QueryBuilder.create(dataSource, getQuery("database.insertStatistics"), true) .setObject(statistics) .executeUpdate()); } public Collection<Calendar> getCalendars() throws SQLException { return QueryBuilder.create(dataSource, getQuery("database.selectCalendarsAll")) .executeQuery(Calendar.class); } public void addCalendar(Calendar calendar) throws SQLException { calendar.setId(QueryBuilder.create(dataSource, getQuery("database.insertCalendar"), true) .setObject(calendar) .executeUpdate()); } public void updateCalendar(Calendar calendar) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.updateCalendar")) .setObject(calendar) .executeUpdate(); } public void removeCalendar(long calendarId) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.deleteCalendar")) .setLong("id", calendarId) .executeUpdate(); } public Collection<CalendarPermission> getCalendarPermissions() throws SQLException { return QueryBuilder.create(dataSource, getQuery("database.selectCalendarPermissions")) .executeQuery(CalendarPermission.class); } public void linkCalendar(long userId, long calendarId) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.linkCalendar")) .setLong("userId", userId) .setLong("calendarId", calendarId) .executeUpdate(); } public void unlinkCalendar(long userId, long calendarId) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.unlinkCalendar")) .setLong("userId", userId) .setLong("calendarId", calendarId) .executeUpdate(); } public Collection<UserPermission> getUserPermissions() throws SQLException { return QueryBuilder.create(dataSource, getQuery("database.selectUserPermissions")) .executeQuery(UserPermission.class); } public void linkUser(long userId, long managedUserId) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.linkUser")) .setLong("userId", userId) .setLong("managedUserId", managedUserId) .executeUpdate(); } public void unlinkUser(long userId, long managedUserId) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.unlinkUser")) .setLong("userId", userId) .setLong("managedUserId", managedUserId) .executeUpdate(); } public Collection<Attribute> getAttributes() throws SQLException { return QueryBuilder.create(dataSource, getQuery("database.selectAttributes")) .executeQuery(Attribute.class); } public void addAttribute(Attribute attribute) throws SQLException { attribute.setId(QueryBuilder.create(dataSource, getQuery("database.insertAttribute"), true) .setObject(attribute) .executeUpdate()); } public void updateAttribute(Attribute attribute) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.updateAttribute")) .setObject(attribute) .executeUpdate(); } public void removeAttribute(long computedAttributeId) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.deleteAttribute")) .setLong("id", computedAttributeId) .executeUpdate(); } public Collection<AttributePermission> getAttributePermissions() throws SQLException { return QueryBuilder.create(dataSource, getQuery("database.selectAttributePermissions")) .executeQuery(AttributePermission.class); } public void linkAttribute(long userId, long attributeId) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.linkAttribute")) .setLong("userId", userId) .setLong("attributeId", attributeId) .executeUpdate(); } public void unlinkAttribute(long userId, long attributeId) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.unlinkAttribute")) .setLong("userId", userId) .setLong("attributeId", attributeId) .executeUpdate(); } public Collection<GroupAttribute> getGroupAttributes() throws SQLException { return QueryBuilder.create(dataSource, getQuery("database.selectGroupAttributes")) .executeQuery(GroupAttribute.class); } public void linkGroupAttribute(long groupId, long attributeId) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.linkGroupAttribute")) .setLong("groupId", groupId) .setLong("attributeId", attributeId) .executeUpdate(); } public void unlinkGroupAttribute(long groupId, long attributeId) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.unlinkGroupAttribute")) .setLong("groupId", groupId) .setLong("attributeId", attributeId) .executeUpdate(); } public Collection<DeviceAttribute> getDeviceAttributes() throws SQLException { return QueryBuilder.create(dataSource, getQuery("database.selectDeviceAttributes")) .executeQuery(DeviceAttribute.class); } public void linkDeviceAttribute(long deviceId, long attributeId) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.linkDeviceAttribute")) .setLong("deviceId", deviceId) .setLong("attributeId", attributeId) .executeUpdate(); } public void unlinkDeviceAttribute(long deviceId, long attributeId) throws SQLException { QueryBuilder.create(dataSource, getQuery("database.unlinkDeviceAttribute")) .setLong("deviceId", deviceId) .setLong("attributeId", attributeId) .executeUpdate(); } }