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 __saveTrafficJam(self, segmentId, curState): sql = """ INSERT INTO traffic.traffic_jam (segment, prev_state, ts) VALUES (%(segmentId)s, %(prevState)s, CURRENT_TIMESTAMP) """ self.mapsDB.execute(sql, {'segmentId': segmentId, 'prevState': curState}) funcs.updateSegmentState(segmentId, 7) self.mapsDB.commit()
def __updateTrafficJam(self, segmentId, prevState, curState): sql = """ UPDATE traffic.traffic_jam SET prev_state = %(prevState)s, ts = CURRENT_TIMESTAMP WHERE segment = %(segmentId)s """ self.mapsDB.execute(sql, { # Если сейчас на сегменте не пробка, то prev_state нужно обновить 'prevState': curState if curState != 7 else prevState, 'segmentId': segmentId }) funcs.updateSegmentState(segmentId, 7) self.mapsDB.commit()
def __removeTrafficJam(self, segmentId, prevState): self.mapsDB.execute('DELETE FROM traffic.traffic_jam WHERE segment = %s', (segmentId,)) funcs.updateSegmentState(segmentId, prevState) self.mapsDB.commit()