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})
