· 6 years ago · Apr 02, 2019, 08:00 AM
1private class SQLiteHelper(context: Context) :
2 SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {
3
4 private val log = PlatformUtil.getLog(SQLiteHelper::class.java)
5
6 override fun onCreate(db: SQLiteDatabase) {
7 db.execSQL(TIMELINE_TABLE_CREATE)
8 db.execSQL("CREATE INDEX IF NOT EXISTS $DATE_INDEX ON $TIMELINE_TABLE_NAME (\"$COL_TIME\" DESC);")
9 db.execSQL(BUFFER_TABLE_CREATE)
10 }
11
12 override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
13 db.execSQL(TIMELINE_TABLE_DELETE)
14 db.execSQL(BUFFER_TABLE_DELETE)
15 onCreate(db)
16 }
17
18 internal fun addBufferedMessage(message: BufferMessage) {
19 writableDatabase?.execSQL(BUFFER_TABLE_INSERT,
20 arrayOf(message.chatId, message.lat, message.lon, message.altitude, message.speed,
21 message.hdop, message.bearing, message.time, message.type, message.deviceName))
22 }
23
24 internal fun addLocationMessage(message: LocationMessage) {
25 writableDatabase?.execSQL(TIMELINE_TABLE_INSERT,
26 arrayOf(message.userId, message.chatId, message.lat, message.lon, message.altitude, message.speed,
27 message.hdop, message.bearing, message.time, message.type, message.messageId, message.distanceFromPrev, message.deviceName))
28 }
29
30 internal fun getMessagesForUser(userId: Int, start: Long, end: Long): List<LocationMessage> {
31 val res = arrayListOf<LocationMessage>()
32 readableDatabase?.rawQuery(
33 "$TIMELINE_TABLE_SELECT WHERE $COL_USER_ID = ? AND $COL_TIME BETWEEN $start AND $end ORDER BY $COL_CHAT_ID ASC, $COL_TYPE DESC, $COL_TIME ASC ",
34 arrayOf(userId.toString()))?.apply {
35 if (moveToFirst()) {
36 do {
37 res.add(readLocationMessage(this@apply))
38 } while (moveToNext())
39 }
40 close()
41 }
42 return res
43 }
44
45 internal fun getIngoingMessages(currentUserId: Int, start: Long, end: Long): List<LocationMessage> {
46 val res = arrayListOf<LocationMessage>()
47 readableDatabase?.rawQuery(
48 "$TIMELINE_TABLE_SELECT WHERE $COL_USER_ID != ? AND $COL_TIME BETWEEN $start AND $end ORDER BY $COL_USER_ID, $COL_CHAT_ID, $COL_TYPE DESC, $COL_TIME ",
49 arrayOf(currentUserId.toString()))?.apply {
50 if (moveToFirst()) {
51 do {
52 res.add(readLocationMessage(this@apply))
53 } while (moveToNext())
54 }
55 close()
56 }
57 return res
58 }
59
60 internal fun getIngoingUserLocations(start: Long, end: Long): List<UserLocations> {
61 val res = arrayListOf<UserLocations>()
62 readableDatabase?.rawQuery("$TIMELINE_TABLE_SELECT WHERE $COL_TIME BETWEEN $start AND $end ORDER BY $COL_USER_ID, $COL_CHAT_ID, $COL_DEVICE_NAME, $COL_TYPE DESC, $COL_TIME ", null)?.apply {
63 if (moveToFirst()) {
64 var userId: Int
65 var chatId: Long
66 var deviceName: String
67 var userLocations: UserLocations? = null
68 var userLocationsMap: MutableMap<Int, MutableList<UserTrkSegment>>? = null
69 var segment: UserTrkSegment? = null
70 do {
71 val locationMessage = readLocationMessage(this@apply)
72 userId = locationMessage.userId
73 chatId = locationMessage.chatId
74 deviceName = locationMessage.deviceName
75 if (userLocations == null || userLocations.userId != userId ||
76 userLocations.chatId != chatId || userLocations.deviceName != deviceName) {
77 userLocationsMap = mutableMapOf()
78 userLocations = UserLocations(userId, chatId, deviceName, userLocationsMap)
79 res.add(userLocations)
80 segment = null
81 }
82 if (segment == null || segment.type != locationMessage.type || locationMessage.time - segment.maxTime > 30 * 1000 * 60) {
83 segment = UserTrkSegment(mutableListOf(), 0.0, locationMessage.type, locationMessage.time, locationMessage.time)
84 if (userLocationsMap!![segment.type] == null) {
85 userLocationsMap[segment.type] = mutableListOf()
86 }
87 userLocationsMap[segment.type]!!.add(segment)
88 }
89 if (segment.points.size > 0) {
90 segment.distance += MapUtils.getDistance(locationMessage.lat, locationMessage.lon, segment.points.last().lat, segment.points.last().lon)
91 }
92 segment.maxTime = locationMessage.time
93 segment.points.add(locationMessage)
94 } while (moveToNext())
95 }
96 close()
97 }
98 return res
99 }
100
101 internal fun getIngoingUserLocationsInChat(userId: Int, chatId: Long, deviceName: String,start: Long, end: Long): UserLocations? {
102 val userLocationsMap: MutableMap<Int, MutableList<UserTrkSegment>> = mutableMapOf()
103 val userLocations = UserLocations(userId,chatId,deviceName,userLocationsMap)
104 val whereDeviceQuery = if (deviceName.isNotEmpty()) "AND $COL_DEVICE_NAME = ?" else ""
105 val args = if (deviceName.isNotEmpty()) arrayOf(userId.toString(), chatId.toString(), deviceName) else arrayOf(userId.toString(), chatId.toString())
106 readableDatabase?.rawQuery("$TIMELINE_TABLE_SELECT WHERE $COL_USER_ID = ? AND $COL_CHAT_ID = ? $whereDeviceQuery AND $COL_TIME BETWEEN $start AND $end ORDER BY $COL_TYPE DESC, $COL_TIME ", args)?.apply {
107 if (moveToFirst()) {
108 var segment: UserTrkSegment? = null
109 do {
110 val locationMessage = readLocationMessage(this@apply)
111 if (segment == null || segment.type != locationMessage.type || locationMessage.time - segment.maxTime > 30 * 1000 * 60) {
112 segment = UserTrkSegment(mutableListOf(), 0.0, locationMessage.type, locationMessage.time, locationMessage.time)
113 if (userLocationsMap[segment.type] == null) {
114 userLocationsMap[segment.type] = mutableListOf<UserTrkSegment>()
115 }
116 userLocationsMap[segment.type]?.add(segment)
117 }
118 if (segment.points.size > 0) {
119 segment.distance += MapUtils.getDistance(locationMessage.lat, locationMessage.lon, segment.points.last().lat, segment.points.last().lon)
120 }
121 segment.maxTime = locationMessage.time
122 segment.points.add(locationMessage)
123 } while (moveToNext())
124 }
125 close()
126 }
127 return userLocations
128 }
129
130 internal fun getMessagesForUserInChat(userId: Int, chatId: Long, deviceName: String, start: Long, end: Long): List<LocationMessage> {
131 val res = arrayListOf<LocationMessage>()
132 val whereDeviceQuery = if (deviceName.isNotEmpty()) "AND $COL_DEVICE_NAME = ?" else ""
133 val args = if (deviceName.isNotEmpty()) arrayOf(userId.toString(), chatId.toString(), deviceName) else arrayOf(userId.toString(), chatId.toString())
134 readableDatabase?.rawQuery(
135 "$TIMELINE_TABLE_SELECT WHERE $COL_USER_ID = ? AND $COL_CHAT_ID = ? $whereDeviceQuery AND $COL_TIME BETWEEN $start AND $end ORDER BY $COL_TYPE DESC, $COL_TIME ", args)?.apply {
136 if (moveToFirst()) {
137 do {
138 res.add(readLocationMessage(this@apply))
139 } while (moveToNext())
140 }
141 close()
142 }
143 return res
144 }
145
146 internal fun getBufferedMessages(): List<BufferMessage> {
147 val res = arrayListOf<BufferMessage>()
148 readableDatabase?.rawQuery(BUFFER_TABLE_SELECT, null)?.apply {
149 if (moveToFirst()) {
150 do {
151 res.add(readBufferMessage(this@apply))
152 } while (moveToNext())
153 }
154 close()
155 }
156 return res
157 }
158
159 internal fun getLastMessages(): MutableList<LocationMessage> {
160 val res = arrayListOf<LocationMessage>()
161 readableDatabase?.rawQuery("$TIMELINE_TABLE_SELECT_LAST_LOCATIONS GROUP BY $COL_USER_ID, $COL_CHAT_ID, $COL_DEVICE_NAME, $COL_TYPE", null)?.apply {
162 if (moveToFirst()) {
163 do {
164 val locationMessage = readLocationMessage(this@apply)
165 res.add(locationMessage)
166 log.debug("add last location message - $locationMessage")
167 } while (moveToNext())
168 }
169 close()
170 }
171 return res
172 }
173
174 internal fun readLocationMessage(cursor: Cursor): LocationMessage {
175 val userId = cursor.getInt(0)
176 val chatId = cursor.getLong(1)
177 val lat = cursor.getDouble(2)
178 val lon = cursor.getDouble(3)
179 val altitude = cursor.getDouble(4)
180 val speed = cursor.getDouble(5)
181 val hdop = cursor.getDouble(6)
182 val bearing = cursor.getDouble(7)
183 val date = cursor.getLong(8)
184 val type = cursor.getInt(9)
185 val messageId = cursor.getLong(10)
186 val distanceFromPrev = cursor.getDouble(11)
187 val botName = cursor.getString(12)
188
189 return LocationMessage(userId, chatId, lat, lon, altitude, speed, hdop, bearing, date, type, messageId, distanceFromPrev, botName)
190 }
191
192 internal fun readBufferMessage(cursor: Cursor): BufferMessage {
193 val chatId = cursor.getLong(0)
194 val lat = cursor.getDouble(1)
195 val lon = cursor.getDouble(2)
196 val altitude = cursor.getDouble(3)
197 val speed = cursor.getDouble(4)
198 val hdop = cursor.getDouble(5)
199 val bearing = cursor.getDouble(6)
200 val date = cursor.getLong(7)
201 val type = cursor.getInt(8)
202 val botName = cursor.getString(9)
203
204 return BufferMessage(chatId, lat, lon, altitude, speed, hdop, bearing, date, type, botName)
205 }
206
207 internal fun clearBufferedMessages() {
208 writableDatabase?.execSQL(BUFFER_TABLE_CLEAR)
209 }
210
211 internal fun removeBufferedMessage(message: BufferMessage) {
212
213 writableDatabase?.execSQL(
214 BUFFER_TABLE_REMOVE,
215 arrayOf(
216 message.chatId,
217 message.lat,
218 message.lon,
219 message.altitude,
220 message.speed,
221 message.hdop,
222 message.bearing,
223 message.time,
224 message.type,
225 message.deviceName
226 )
227 )
228 }
229
230 companion object {
231
232 private const val DATABASE_NAME = "location_messages"
233 private const val DATABASE_VERSION = 6
234
235 private const val TIMELINE_TABLE_NAME = "timeline"
236 private const val BUFFER_TABLE_NAME = "buffer"
237
238 private const val COL_USER_ID = "user_id"
239 private const val COL_CHAT_ID = "chat_id"
240 private const val COL_TIME = "time"
241 private const val COL_LAT = "lat"
242 private const val COL_LON = "lon"
243 private const val COL_ALTITUDE = "altitude"
244 private const val COL_SPEED = "speed"
245 private const val COL_HDOP = "hdop"
246 private const val COL_BEARING = "bearing"
247 private const val COL_TYPE = "type" // 0 = user map message, 1 = user text message, 2 = bot map message, 3 = bot text message
248 private const val COL_MESSAGE_ID = "message_id"
249 private const val COL_DISTANCE_FROM_PREV = "distance_from_prev"
250 private const val COL_DEVICE_NAME = "device_name"
251
252 private const val DATE_INDEX = "date_index"
253
254 // Timeline messages table
255 private const val TIMELINE_TABLE_INSERT =
256 ("INSERT INTO $TIMELINE_TABLE_NAME ($COL_USER_ID, $COL_CHAT_ID, $COL_LAT, $COL_LON, $COL_ALTITUDE, $COL_SPEED, $COL_HDOP, $COL_BEARING, $COL_TIME, $COL_TYPE, $COL_MESSAGE_ID, $COL_DISTANCE_FROM_PREV, $COL_DEVICE_NAME) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
257
258 private const val TIMELINE_TABLE_CREATE =
259 ("CREATE TABLE IF NOT EXISTS $TIMELINE_TABLE_NAME ($COL_USER_ID long, $COL_CHAT_ID long,$COL_LAT double, $COL_LON double, $COL_ALTITUDE double, $COL_SPEED float, $COL_HDOP double, $COL_BEARING double, $COL_TIME long, $COL_TYPE int, $COL_MESSAGE_ID long, $COL_DISTANCE_FROM_PREV double, $COL_DEVICE_NAME TEXT NOT NULL DEFAULT '')")
260
261 private const val TIMELINE_TABLE_SELECT =
262 "SELECT $COL_USER_ID, $COL_CHAT_ID, $COL_LAT, $COL_LON, $COL_ALTITUDE, $COL_SPEED, $COL_HDOP, $COL_BEARING, $COL_TIME, $COL_TYPE, $COL_MESSAGE_ID, $COL_DISTANCE_FROM_PREV, $COL_DEVICE_NAME FROM $TIMELINE_TABLE_NAME"
263
264 private const val TIMELINE_TABLE_SELECT_LAST_LOCATIONS =
265 "SELECT $COL_USER_ID, $COL_CHAT_ID, $COL_LAT, $COL_LON, $COL_ALTITUDE, $COL_SPEED, $COL_HDOP, $COL_BEARING, $COL_TIME, $COL_TYPE, $COL_MESSAGE_ID, $COL_DISTANCE_FROM_PREV, $COL_DEVICE_NAME, MAX($COL_TIME) FROM $TIMELINE_TABLE_NAME"
266
267 private const val TIMELINE_TABLE_CLEAR = "DELETE FROM $TIMELINE_TABLE_NAME"
268
269 private const val TIMELINE_TABLE_DELETE = "DROP TABLE IF EXISTS $TIMELINE_TABLE_NAME"
270
271 // Buffer messages table
272 private const val BUFFER_TABLE_INSERT =
273 ("INSERT INTO $BUFFER_TABLE_NAME ($COL_CHAT_ID, $COL_LAT, $COL_LON, $COL_ALTITUDE, $COL_SPEED, $COL_HDOP, $COL_BEARING, $COL_TIME, $COL_TYPE, $COL_DEVICE_NAME) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
274
275 private const val BUFFER_TABLE_CREATE =
276 ("CREATE TABLE IF NOT EXISTS $BUFFER_TABLE_NAME ($COL_CHAT_ID long, $COL_LAT double, $COL_LON double, $COL_ALTITUDE double, $COL_SPEED float, $COL_HDOP double, $COL_BEARING double, $COL_TIME long, $COL_TYPE int, $COL_DEVICE_NAME TEXT NOT NULL DEFAULT '')")
277
278 private const val BUFFER_TABLE_SELECT =
279 "SELECT $COL_CHAT_ID, $COL_LAT, $COL_LON, $COL_ALTITUDE, $COL_SPEED, $COL_HDOP, $COL_BEARING, $COL_TIME, $COL_TYPE, $COL_DEVICE_NAME FROM $BUFFER_TABLE_NAME"
280
281 private const val BUFFER_TABLE_CLEAR = "DELETE FROM $BUFFER_TABLE_NAME"
282
283 private const val BUFFER_TABLE_REMOVE = "DELETE FROM $BUFFER_TABLE_NAME WHERE $COL_CHAT_ID = ? AND $COL_LAT = ? AND $COL_LON = ? AND $COL_ALTITUDE = ? AND $COL_SPEED = ? AND $COL_HDOP = ? AND $COL_BEARING = ? AND $COL_TIME = ? AND $COL_TYPE = ? AND $COL_DEVICE_NAME = ?"
284
285 private const val BUFFER_TABLE_DELETE = "DROP TABLE IF EXISTS $BUFFER_TABLE_NAME"
286 }
287 }
288
289 data class LocationMessage(
290 val userId: Int,
291 val chatId: Long,
292 val lat: Double,
293 val lon: Double,
294 val altitude: Double,
295 val speed: Double,
296 val hdop: Double,
297 val bearing: Double,
298 val time: Long,
299 val type: Int,
300 val messageId: Long,
301 val distanceFromPrev: Double,
302 val deviceName: String)
303
304 data class BufferMessage (
305 val chatId: Long,
306 val lat: Double,
307 val lon: Double,
308 val altitude: Double,
309 val speed: Double,
310 val hdop: Double,
311 val bearing: Double,
312 val time: Long,
313 val type: Int,
314 val deviceName: String)
315
316 data class UserLocations(
317 val userId: Int,
318 val chatId: Long,
319 val deviceName: String,
320 val locationsByType: Map<Int, List<UserTrkSegment>>
321 ) {
322 fun getUniqueSegments(): List<UserTrkSegment> {
323 val list = mutableListOf<UserTrkSegment>()
324 if (locationsByType.containsKey(TYPE_MY_LOCATION)) {
325 return locationsByType[TYPE_MY_LOCATION] ?: list
326 }
327 list.addAll(locationsByType[TYPE_TEXT] ?: emptyList())
328 val mapList = locationsByType[TYPE_MAP] ?: emptyList()
329 mapList.forEach {
330 var ti = 0
331 while (ti < list.size && list[ti].maxTime < it.minTime) {
332 ti++
333 }
334 if (ti < list.size && list[ti].minTime > it.maxTime) {
335 list.add(ti, it)
336 } else if (ti == list.size) {
337 list.add(it)
338 }
339 }
340
341
342 return list
343 }
344 }
345
346 data class UserTrkSegment(
347 val points: MutableList<LocationMessage>,
348 var distance: Double,
349 var type: Int,
350 var minTime: Long,
351 var maxTime: Long
352 ) {
353 fun newer(other: UserTrkSegment): Boolean {
354 return other.maxTime < maxTime
355 }
356
357 fun overlap(other: UserTrkSegment): Boolean {
358 return if (other.maxTime < maxTime) {
359 other.maxTime > minTime
360 } else {
361 other.minTime < maxTime
362 }
363 }
364 }
365
366 companion object {
367
368 const val TYPE_MAP = 0
369 const val TYPE_TEXT = 1
370 const val TYPE_MY_LOCATION = 3
371 }