def getColorsAfterTs(params): conn = connection.Connection() sql = """ SELECT segment, color, ts, is_removed FROM traffic.segment_colors WHERE ts > %(ts)s """ conn.openAll() nowTs = str(cur_time.getTs(conn)) conn.execute(sql, {'ts': urllib.parse.unquote(params['ts'])}) colors = conn.fetchAll() conn.closeAll() response = {'ts': nowTs, 'colors': []} for color in colors: response['colors'].append({ 'id': color[0], 'color': color[1], 'ts': str(color[2]), 'isRemoved': color[3] }) return json.dumps(response)
def __main(self): sqlSelect = """ SELECT t.segment, t.prev_state, s.id_state FROM traffic.traffic_jam AS t LEFT JOIN traffic.segments AS s ON t.segment = s.id WHERE EXTRACT(epoch FROM %(curTs)s - ts) >= %(age)s """ sqlDelete = """ DELETE FROM traffic.traffic_jam WHERE EXTRACT(epoch FROM %(curTs)s - ts) >= %(age)s """ while True: curTs = cur_time.getTs(self.mapsDB) self.mapsDB.execute(sqlSelect, {'age': config.TRAFFIC_JAM_LIFETIME, 'curTs': curTs}) trafficJams = self.mapsDB.fetchAll() self.mapsDB.execute(sqlDelete, {'age': config.TRAFFIC_JAM_LIFETIME, 'curTs': curTs}) for (segmentId, prevState, curState) in trafficJams: # Если текущее состояние сегмента - не пробка, то кто-то внёс изменение, откатывать не нужно if curState == 7: funcs.updateSegmentState(segmentId, prevState) self.mapsDB.commit() time.sleep(config.REMOVE_TRAFFIC_JAM_TIMEOUT)
def getColors(): conn = connection.Connection() sql = """ SELECT segment, color, ts, is_removed FROM traffic.segment_colors """ conn.openAll() nowTs = str(cur_time.getTs(conn)) conn.execute(sql) colors = conn.fetchAll() conn.closeAll() response = {'ts': nowTs, 'colors': []} for color in colors: response['colors'].append({ 'id': color[0], 'color': color[1], 'ts': str(color[2]), 'isRemoved': color[3] }) return json.dumps(response)
def __sqlAvgMode(self): sqlDelete = """ DELETE FROM traffic.segment_speeds_tmp WHERE (SELECT extract(epoch FROM %(now)s - ts)) > %(lifetime)s """ self.baseDB.execute(sqlDelete, {'now': cur_time.getTs(self.baseDB), 'lifetime': self.lifetime})
def __main1(self): track = self.__getNextTrack() while track: for segment in track: segment.calcSpeed() sql = """ SELECT traffic.upd_speed_segment(%(segmentId)s, %(speed)s, %(speed)s, %(source)s, %(did)s, %(curTs)s, %(area)s); """ self.baseDB.execute(sql, { 'segmentId': segment.id, 'speed': segment.speed, 'source': track.source, 'did': track.deviceId, 'curTs': cur_time.getTs(self.baseDB), 'area': self.area }) logger.logByCond(self.isLog, 'Update speed (%d km/h) on segment with id %d' % (segment.speed, segment.id)) self.baseDB.commit() if self.isExportTrafficJam: self.trafficJamBuffer.put(track) if self.isStatsCollection: self.statsBuffer.put(track) track = self.__getNextTrack()
def __main2(self): track = self.__getNextTrack() while True: for segment in track: segment.calcSpeed() sql = """ INSERT INTO traffic.segment_speeds_tmp (segment, source, did, speed, ts) VALUES (%(segmentId)s, %(source)s, %(did)s, %(speed)s, %(curTs)s) """ self.baseDB.execute(sql, { 'segmentId': segment.id, 'speed': segment.speed, 'source': track.source, 'did': track.deviceId, 'curTs': cur_time.getTs(self.baseDB) }) self.baseDB.commit() logger.logByCond(self.isLog, 'Add speed (%d km/h) to segment with id %d' % (segment.speed, segment.id)) if self.isExportTrafficJam: self.trafficJamBuffer.put(track) if self.isStatsCollection: self.statsBuffer.put(track) track = self.__getNextTrack()
def __updateStorage(self): if self.deviceFlag and self.busFlag: sql = """ SELECT id, udid, lon, lat, speed, bearing, created_at AS ts, 'device' FROM tracking.points WHERE created_at > %(prevTs)s AND created_at <= %(now)s AND speed IS NOT NULL AND bearing IS NOT NULL UNION SELECT id, udid, lon, lat, speed, bearing, ts, 'bus' FROM bus.points WHERE ts > %(prevTs)s AND ts <= %(now)s AND speed IS NOT NULL AND bearing IS NOT NULL ORDER BY ts ASC """ elif self.deviceFlag: sql = """ SELECT id, udid, lon, lat, speed, bearing, created_at AS ts, 'device' FROM tracking.points WHERE created_at > %(prevTs)s AND created_at <= %(now)s AND speed IS NOT NULL AND bearing IS NOT NULL ORDER BY ts ASC """ elif self.busFlag: sql = """ SELECT id, udid, lon, lat, speed, bearing, ts, 'bus' FROM bus.points WHERE ts > %(prevTs)s AND ts <= %(now)s AND speed IS NOT NULL AND bearing IS NOT NULL ORDER BY ts ASC """ prevTs = cur_time.getTs(self.conn) time.sleep(0.5) while self.isAlive: nowTs = cur_time.getTs(self.conn) self.conn.execute(sql, {'prevTs': prevTs, 'now': nowTs}) logger.logByCond(self.isLog, 'point storage was updated [current time - %s]' % nowTs) points = self.conn.fetchAll() for p in points: source = p[7] speed = float(p[4]) * 3.6 if source == 'device' else float(p[4]) self.storage.append(Point(source, p[0], p[1], p[2], p[3], speed, p[5], p[6])) prevTs = nowTs time.sleep(config.POINT_STORAGE_UPDATE_TIMEOUT)
def __main(self): while True: time.sleep(self.timeout) for track in self.trackStorage: if self.isOnlyForceExport and track.isForceExport: self.__removeTrackFromStorage(track.deviceId) self.__exportTrack(track) elif not self.isOnlyForceExport: if track.isForceExport or track.getWaitingTime(cur_time.getTs(self.baseDB)) > config.TRACK_MAX_WAITING_TIME: self.__removeTrackFromStorage(track.deviceId) self.__exportTrack(track)
def __main(self): sqlUpdate = """ UPDATE traffic.segment_colors SET is_removed = true, ts = %(now)s WHERE is_removed = false AND (SELECT extract(epoch FROM %(now)s - ts)) > %(lifetime)s """ sqlDelete = """ DELETE FROM traffic.segment_colors WHERE is_removed = true AND (SELECT extract(epoch FROM %(now)s - ts)) > %(delta)s """ while True: time.sleep(self.interval) curTs = cur_time.getTs(self.baseDB) self.baseDB.execute(sqlUpdate, {'now': curTs, 'lifetime': self.lifetime}) self.baseDB.execute(sqlDelete, {'now': curTs, 'delta': self.deleteDelta}) self.baseDB.commit()
def getSpeeds(params): conn = connection.Connection() conn.openAll() nowTs = str(cur_time.getTs(conn)) if params['area'] == 'track_view': sql = """ SELECT segment, speed, source, did, ts, false FROM traffic.segments_speeds2 """ conn.execute(sql) speeds = conn.fetchAll() else: conn.execute('SELECT sources FROM traffic.allowed_sources WHERE name = \'view\'') allowedSources = conn.fetch()[0] sql = makeSqlRequest(allowedSources, params.get('ts', None)) conn.execute(sql) speeds = conn.fetchAll() conn.closeAll() response = {'ts': nowTs, 'speeds': []} for speed in speeds: response['speeds'].append({ 'id': speed[0], 'speed': speed[1], 'source': speed[2], 'did': speed[3], 'ts': str(speed[4]), 'isRemoved': speed[5] }) return json.dumps(response)
def setColor(data): conn = connection.Connection() data = json.loads(data) sql = """ SELECT traffic.set_color(%(segmentId)s, %(color)s, %(ts)s) """ conn.openAll() conn.execute(sql, { 'segmentId': data['segmentId'], 'color': data['color'], 'ts': cur_time.getTs(conn) }) conn.commit() conn.closeAll() return json.dumps({'status': 'ok'})
def getTrafficJam(params): conn = connection.Connection() conn.openAll() sql = """ SELECT s.segment, speed, ts, device_range FROM traffic.""" + funcs.getAreaTable('main') + """ AS s LEFT JOIN traffic.speed_ranges AS r ON s.segment = r.segment WHERE ts > %(ts)s AND NOT is_removed """ nowTs = str(cur_time.getTs(conn)) conn.execute(sql, {'ts': urllib.parse.unquote(params['ts'])}) speeds = conn.fetchAll() conn.execute('SELECT device_range FROM traffic.default_speed_range') defaultSpeedRange = conn.fetch()[0] conn.closeAll() # construct JSON response response = {'ts': nowTs, 'traffic-jam': []} for speed in speeds: speedRange = defaultSpeedRange if speed[3] is None else speed[3] if not 0 <= speed[1] < speedRange[0]: continue response['traffic-jam'].append({ 'id': speed[0], 'ts': str(speed[2]), }) return json.dumps(response)
def __sqlLastMode(self): sqlUpdate = """ UPDATE traffic.segment_speeds SET all_is_removed = true, all_ts = %(now)s WHERE all_is_removed = false AND all_ts IS NOT NULL AND (SELECT extract(epoch FROM %(now)s - all_ts)) > %(lifetime)s; UPDATE traffic.segment_speeds SET device_is_removed = true, device_ts = %(now)s WHERE device_is_removed = false AND device_ts IS NOT NULL AND (SELECT extract(epoch FROM %(now)s - device_ts)) > %(lifetime)s; UPDATE traffic.segment_speeds SET bus_is_removed = true, bus_ts = %(now)s WHERE bus_is_removed = false AND bus_ts IS NOT NULL AND (SELECT extract(epoch FROM %(now)s - bus_ts)) > %(lifetime)s; """ sqlDelete = """ DELETE FROM traffic.segment_speeds WHERE all_is_removed = true AND device_is_removed = true AND bus_is_removed = true AND %(delta)s < extract(epoch FROM %(now)s - all_ts) AND %(delta)s < extract(epoch FROM %(now)s - device_ts) AND %(delta)s < extract(epoch FROM %(now)s - bus_ts) """ curTs = cur_time.getTs(self.baseDB) self.baseDB.execute(sqlUpdate, {'now': curTs, 'lifetime': self.lifetime}) self.baseDB.execute(sqlDelete, {'now': curTs, 'delta': self.deleteDelta})