Golang dbconn.AcquireLock函数代码示例

/* Returns the expected arrival times given vehicleId and naptanId pairs */
func SelectNextBusesByVehicleStopPairs(vehiclesStopsMap map[string]string) []NextBus {
	var nextBuses []NextBus

	innerQueries := make([]string, 0)

	for vehicleId, naptanId := range vehiclesStopsMap {
		innerQueries = append(innerQueries,
			fmt.Sprintf("SELECT line, bound, naptan_id, vehicle_id, expected_arrival_time, already_departed "+
				"FROM next_buses "+
				"WHERE vehicle_id='%v' AND naptan_id='%v'",
				vehicleId, naptanId))
	}

	dbconn.AcquireLock()
	err := db.Select(&nextBuses, strings.Join(innerQueries, " UNION ALL "))
	dbconn.ReleaseLock()

	if err != nil {
		logger.GetLogger().Panic(err)
	}

	/* Set time zone to UTC */
	for i, v := range nextBuses {
		nextBuses[i].ExpectedArrivalTime = v.ExpectedArrivalTime.In(timezone.Get())
	}
	return nextBuses
}

/* Returns the naptan ids of the terminuses of given line bound combinations,
   as two separate maps */
func SelectTerminuses(lineBoundCombinations map[string]map[string]bool) map[string]map[string]string {
	var stopPoints []StopPoint

	innerQueries := make([]string, 0)
	for line, details := range lineBoundCombinations {
		for bound, _ := range details {
			innerQueries = append(innerQueries,
				fmt.Sprintf(
					"(SELECT line_id, bound, naptan_id FROM line_stop_points WHERE line_id='%v' AND bound='%v' ORDER BY stop_seq DESC LIMIT 1)",
					line, bound))
		}
	}

	dbconn.AcquireLock()
	err := db.Select(&stopPoints, strings.Join(innerQueries, " UNION ALL "))
	dbconn.ReleaseLock()

	if err != nil {
		logger.GetLogger().Panic(err)
	}

	result := make(map[string]map[string]string)
	for _, stopPoint := range stopPoints {
		_, exists := result[stopPoint.LineId]
		if !exists {
			result[stopPoint.LineId] = make(map[string]string)
		}
		result[stopPoint.LineId][stopPoint.Bound] = stopPoint.NaptanId
	}
	return result
}

/* Returns intermediate stops between (end points inclusive) fromId and toId of line and direction*/
func SelectIntermediateStops(line, bound, fromId, toId string) []string {
	var stopPoints []StopPoint

	dbconn.AcquireLock()
	err := db.Select(&stopPoints,
		"SELECT naptan_id FROM line_stop_points "+
			"WHERE line_id=$1 AND bound=$2 AND stop_seq >="+
			"(SELECT stop_seq FROM line_stop_points WHERE "+
			"line_id=$1 AND bound=$2 AND naptan_id=$3 "+
			"ORDER BY stop_seq LIMIT 1) "+
			"AND stop_seq <= "+
			"(SELECT stop_seq FROM line_stop_points WHERE "+
			"line_id=$1 AND bound=$2 AND naptan_id=$4 "+
			"ORDER BY stop_seq DESC LIMIT 1) "+
			"ORDER BY stop_seq ASC",
		line, bound, fromId, toId)
	dbconn.ReleaseLock()

	if err != nil {
		logger.GetLogger().Panic(err)
	}

	if len(stopPoints) <= 1 {
		logger.GetLogger().Panicf("No intermediate stops between %v and %v for line %v (%v)",
			fromId, toId, line, bound)
	}

	result := make([]string, len(stopPoints))
	for i, v := range stopPoints {
		result[i] = v.NaptanId
	}
	logger.GetLogger().Info("Get intermediate stops (inclusive) from line_stop_points returned: %v", result)
	return result
}

/* Returns the next buses that are approaching a stop given a line and bound after a certain time */
func SelectNextBuses(lineBoundCombination map[string]string, naptanId string, arrivingAfter time.Time) []NextBus {
	var nextBuses []NextBus

	innerQueries := make([]string, 0)

	for line, bound := range lineBoundCombination {
		innerQueries = append(innerQueries,
			fmt.Sprintf("SELECT line, bound, naptan_id, vehicle_id, expected_arrival_time, already_departed "+
				"FROM next_buses "+
				"WHERE naptan_id='%v' AND line='%v' AND bound='%v' AND expected_arrival_time>=TIMESTAMP '%v'",
				naptanId, line, bound, toPsqlTimeStamp(arrivingAfter)))
	}

	sql := fmt.Sprintf("SELECT line, bound, naptan_id, vehicle_id, expected_arrival_time, already_departed "+
		"FROM (%v) AS all_next_buses ORDER BY expected_arrival_time",
		strings.Join(innerQueries, " UNION ALL "))

	logger.GetLogger().Info(sql)
	dbconn.AcquireLock()
	err := db.Select(&nextBuses, sql)
	dbconn.ReleaseLock()

	if err != nil {
		logger.GetLogger().Panic(err)
	}

	/* Set time zone to UTC */
	for i, v := range nextBuses {
		nextBuses[i].ExpectedArrivalTime = v.ExpectedArrivalTime.In(timezone.Get())
	}
	return nextBuses
}

/* Returns intermediate stops between (end points inclusive) fromIcs and toIcs of line and direction
   NOTE requires the use of table "stops" */
func SelectIntermediateStopsWithIcsCode(line, bound, fromIcs, toIcs string) ([]string, error) {
	var stopPoints []StopPoint

	dbconn.AcquireLock()
	err := db.Select(&stopPoints,
		"SELECT naptan_id FROM line_stop_points "+
			"WHERE line_id=$1 AND bound=$2 AND stop_seq >="+
			"(SELECT stop_seq FROM line_stop_points JOIN stops ON line_stop_points.naptan_id=stops.id "+
			"WHERE line_id=$1 AND bound=$2 AND icscode=$3 "+
			"ORDER BY stop_seq LIMIT 1) "+
			"AND stop_seq <= "+
			"(SELECT stop_seq FROM line_stop_points JOIN stops ON line_stop_points.naptan_id=stops.id "+
			"WHERE line_id=$1 AND bound=$2 AND icscode=$4 "+
			"ORDER BY stop_seq DESC LIMIT 1) "+
			"ORDER BY stop_seq ASC",
		line, bound, fromIcs, toIcs)
	dbconn.ReleaseLock()

	if err != nil {
		return nil, err
	}

	if len(stopPoints) <= 1 {
		return nil, errors.New(
			fmt.Sprintf("No intermediate stops between %v and %v for line %v (%v)",
				fromIcs, toIcs, line, bound))
	}

	result := make([]string, len(stopPoints))
	for i, v := range stopPoints {
		result[i] = v.NaptanId
	}
	logger.GetLogger().Info("Get intermediate stops (inclusive) with icscode from line_stop_points returned: %v", result)
	return result, nil
}

func insertRouteDurationsIntoDB(routeDurations map[string]map[string]map[string]time.Duration,
	tflEstimates map[string]map[string]time.Duration) {
	if len(routeDurations) == 0 {
		logger.GetLogger().Error("Lack of route durations. Cycle skipped...")
		return
	}

	values := make([]string, 0)
	for line, lineDetails := range routeDurations {
		for bound, boundDetails := range lineDetails {
			duration := boundDetails["duration"]
			historicDuration := boundDetails["historicDuration"]
			realTimeDuration := boundDetails["realTimeDuration"]
			tflDuration := tflEstimates[line][bound]

			values = append(values, fmt.Sprintf("('%v', '%v', INTERVAL '%v seconds', INTERVAL '%v seconds', INTERVAL '%v seconds', INTERVAL '%v seconds')",
				line, bound, duration.Seconds(), historicDuration.Seconds(), realTimeDuration.Seconds(), tflDuration.Seconds()))
		}
	}

	valuesCommaSep := strings.Join(values, ",")

	dbconn.AcquireLock()
	db.MustExec("DELETE FROM delayed_routes")
	db.MustExec(
		fmt.Sprintf("INSERT INTO delayed_routes (line, bound, estimate, historic_estimate, real_time_estimate, tfl_estimate) "+
			"VALUES %v", valuesCommaSep))
	dbconn.ReleaseLock()
}

/* Returns a map of current delays
   fromStop => (toStop =>
   		("historicEstimate" => seconds (int64)
   		 "realTimeEstimate" => seconds (int64)
   		 "lastUpdated" => millis in Unix Time (int64))) */
func SelectDelays() map[string]map[string]map[string]int64 {
	var delays []CurrentEstimate

	dbconn.AcquireLock()
	err := db.Select(&delays,
		"SELECT from_id, to_id, last_updated, delay_count, "+
			"EXTRACT (epoch FROM historic_estimate) AS historic_estimate, "+
			"EXTRACT (epoch FROM real_time_estimate) AS real_time_estimate "+
			"FROM current_estimates "+
			"WHERE delay_count >= 3 "+
			"AND last_updated=(SELECT MAX(last_updated) FROM current_estimates)")
	dbconn.ReleaseLock()

	if err != nil {
		logger.GetLogger().Panic(err)
	}

	results := make(map[string]map[string]map[string]int64)
	for _, record := range delays {
		fromStopDetails, exists := results[record.FromStop]
		if !exists {
			fromStopDetails = make(map[string]map[string]int64)
		}
		fromStopDetails[record.ToStop] = map[string]int64{
			"historicEstimate": record.HistoricEstimate,
			"realTimeEstimate": record.RealTimeEstimate,
			"delayCount":       record.DelayCount,
			"lastUpdated":      record.LastUpdated.Unix(),
		}
		results[record.FromStop] = fromStopDetails
	}
	return results
}

/* Returns the stop sequence number, given the line, direction and naptanId of the stop
   Should there be ties, an error is returned */
func SelectStopSeq(line, bound, naptanId string) (int, error) {
	var stopPoints []StopPoint

	dbconn.AcquireLock()
	err := db.Select(&stopPoints,
		"SELECT stop_seq FROM line_stop_points WHERE line_id=$1 AND bound=$2 AND naptan_id=$3",
		line, bound, naptanId)
	dbconn.ReleaseLock()

	if err != nil {
		logger.GetLogger().Panic(err)
	}

	if len(stopPoints) == 0 {
		logger.GetLogger().Error("Failed to find stop sequence number for stop %v of line %v (%v)",
			naptanId, line, bound)
		return -1, errors.New(
			fmt.Sprintf("Failed to find stop sequence number for stop %v of line %v (%v)",
				naptanId, line, bound))
	}

	if len(stopPoints) > 1 {
		logger.GetLogger().Error("Expected unique stop sequence number for stop %v of line %v (%v), but got: %v",
			naptanId, line, bound, stopPoints)
		return -1, errors.New(
			fmt.Sprintf("Expected unique stop sequence number for stop %v of line %v (%v), but got: %v",
				naptanId, line, bound, stopPoints))
	}

	result := stopPoints[0].StopSeq
	return result, nil
}

func createMedian() {
	createFinalMedianSql := "CREATE OR REPLACE FUNCTION _final_median(NUMERIC[]) " +
		"RETURNS NUMERIC AS " +
		"$$ " +
		"SELECT AVG(val) " +
		"FROM ( " +
		"SELECT val " +
		"FROM unnest($1) val " +
		"ORDER BY 1 " +
		"LIMIT  2 - MOD(array_upper($1, 1), 2) " +
		"OFFSET CEIL(array_upper($1, 1) / 2.0) - 1 " +
		") sub; " +
		"$$ " +
		"LANGUAGE 'sql' IMMUTABLE"
	createMedianSql := "CREATE AGGREGATE median(NUMERIC) ( " +
		"SFUNC=array_append, " +
		"STYPE=NUMERIC[], " +
		"FINALFUNC=_final_median, " +
		"INITCOND='{}' " +
		")"

	dbconn.AcquireLock()
	tx, err := db.Begin()
	tx.Exec(createFinalMedianSql)
	tx.Exec(createMedianSql)
	err = tx.Commit()
	dbconn.ReleaseLock()

	if err != nil {
		logger.GetLogger().Warning(err.Error())
		return
	}
	logger.GetLogger().Info("Function 'median' created successfully")
}

/* Returns all current estimates of bus travel times
   CATCH: Not all stop pairs may be present, especially if estimate is not recently generated
          e.g. routes served by night buses in the day*/
func SelectAllCurrentEstimates() map[string]map[string]time.Duration {
	var currentEstimates []CurrentEstimate

	dbconn.AcquireLock()
	err := db.Select(&currentEstimates,
		"SELECT from_id, to_id, last_updated, "+
			"EXTRACT(epoch FROM estimate) AS estimate "+
			"FROM current_estimates "+
			"WHERE last_updated=(SELECT MAX(last_updated) FROM current_estimates)")
	dbconn.ReleaseLock()

	if err != nil {
		logger.GetLogger().Panic(err)
	}

	result := make(map[string]map[string]time.Duration)
	for _, currentEstimate := range currentEstimates {
		fromStop := currentEstimate.FromStop
		toStop := currentEstimate.ToStop
		estimate := time.Duration(currentEstimate.Estimate) * time.Second

		/* Depending on whether from stop exists or not, decide whether there
		   is need to create a new map in results */
		toStopMap, exists := result[fromStop]
		if !exists {
			toStopMap = make(map[string]time.Duration)
		}
		toStopMap[toStop] = estimate
		result[fromStop] = toStopMap
	}
	return result
}

func replaceTimetable(line, bound, origin string, newTimetable map[time.Weekday]map[selectdb.LocalTime]bool) {
	values := make([]string, 0)
	for weekday, departureTimes := range newTimetable {
		for departureTime, _ := range departureTimes {
			values = append(values, fmt.Sprintf("('%v', '%v', '%v', %v, TIME '%02d:%02d:00')",
				line, bound, origin, int(weekday), departureTime.Hour, departureTime.Minute))
		}
	}

	dbconn.AcquireLock()
	tx := db.MustBegin()
	tx.MustExec(fmt.Sprintf("DELETE FROM timetable WHERE line_id='%v' AND bound='%v' AND naptan_id='%v'", line, bound, origin))

	logger.GetLogger().Info(fmt.Sprintf("INSERT INTO timetable (line_id, bound, naptan_id, weekday, departure_time) VALUES %s",
		strings.Join(values, ",")))
	tx.MustExec(
		fmt.Sprintf("INSERT INTO timetable (line_id, bound, naptan_id, weekday, departure_time) VALUES %s",
			strings.Join(values, ",")))
	err := tx.Commit()
	dbconn.ReleaseLock()

	if err != nil {
		logger.GetLogger().Error(err.Error())
	}
	logger.GetLogger().Info("Replaced timetable for line %v (%v), stop %v", line, bound, origin)
}

/* Returns sequences of all naptanIds, grouped by line and bound
   map (line => (bound => []naptanIds)) */
func SelectAllLineStopPoints() map[string]map[string][]string {
	var stopPoints []StopPoint

	dbconn.AcquireLock()
	err := db.Select(&stopPoints,
		"SELECT line_id, bound, stop_seq, naptan_id "+
			"FROM line_stop_points "+
			"ORDER BY line_id, bound, stop_seq")
	dbconn.ReleaseLock()

	if err != nil {
		logger.GetLogger().Panic(err)
	}

	result := make(map[string]map[string][]string)
	for _, v := range stopPoints {
		lineDetails, exists := result[v.LineId]
		if !exists {
			lineDetails = make(map[string][]string)
		}

		orderedStopPoints, exists := lineDetails[v.Bound]
		if !exists {
			orderedStopPoints = make([]string, 0)
		}

		orderedStopPoints = append(orderedStopPoints, v.NaptanId)
		lineDetails[v.Bound] = orderedStopPoints
		result[v.LineId] = lineDetails
	}
	logger.GetLogger().Info("Get existing line stop points returned: %v", result)
	return result
}

/* Returns timetable for specified line, bound and stop in the form of a map
   map (weekday => []("hour" => hour
                    "minute" => minute)) */
func SelectTimetable(line, bound, naptanId string) map[time.Weekday]map[LocalTime]bool {
	var timetable []TimetableEntry

	dbconn.AcquireLock()
	err := db.Select(&timetable,
		"SELECT weekday, departure_time FROM timetable WHERE line_id=$1 AND bound=$2 AND naptan_id=$3 ORDER BY weekday,departure_time",
		line, bound, naptanId)
	dbconn.ReleaseLock()

	if err != nil {
		logger.GetLogger().Panic(err)
	}

	result := make(map[time.Weekday]map[LocalTime]bool)
	for _, entry := range timetable {
		weekday := time.Weekday(entry.Weekday)
		weekdayMap, exists := result[weekday]
		if !exists {
			weekdayMap = make(map[LocalTime]bool, 0)
			result[weekday] = weekdayMap
		}
		weekdayMap[LocalTime{Hour: entry.DepartureTime.Hour(), Minute: entry.DepartureTime.Minute()}] = true
	}
	logger.GetLogger().Info("Get timetable for line %v (%v), stop %v returned: %v", line, bound, naptanId, result)
	return result
}

/* Returns all available routes stored in tfl_predictions_history
   []map ("line" => line,
   		  "bound" => bound,
   		  "fromStop" => fromStop,
   		  "toStop" => toStop)
*/
func SelectAvailableRoutesTflPredictions() []map[string]string {
	type AvailableRoute struct {
		Line     string `db:"line"`
		Bound    string `db:"bound"`
		FromStop string `db:"from_id"`
		ToStop   string `db:"to_id"`
	}

	var availableRoutes []AvailableRoute

	dbconn.AcquireLock()
	err := db.Select(&availableRoutes, "SELECT DISTINCT line, bound, from_id, to_id FROM tfl_predictions_history")
	dbconn.ReleaseLock()

	if err != nil {
		logger.GetLogger().Panic(err)
	}

	result := make([]map[string]string, len(availableRoutes))
	for i, v := range availableRoutes {
		result[i] = map[string]string{
			"line":     v.Line,
			"bound":    v.Bound,
			"fromStop": v.FromStop,
			"toStop":   v.ToStop,
		}
	}
	return result
}

/* Inserts expected departures found above into table "expected_departures".
   To supply map of expected departures
   map (vehicleId =>
	("line" => line,
	 "bound" => bound,
	 "naptanId" => naptanId of terminus,
	 "expectedDeparture" => expectedDeparture in TfL format,
	))
*/
func insertExpectedDeparturesIntoDB(expectedDepartures map[string]map[string]string) {
	values := make([]string, 0)
	for vehicleId, details := range expectedDepartures {
		values = append(values,
			fmt.Sprintf("('%v', '%v', '%v', '%v', TIMESTAMP '%v')",
				details["line"], details["bound"], details["naptanId"], vehicleId,
				toPsqlTimeStamp(details["expectedDeparture"])))
	}

	valuesCommaSep := strings.Join(values, ",")

	dbconn.AcquireLock()
	db.MustExec(
		fmt.Sprintf("UPDATE terminus_departures AS entry "+
			"SET line=data.line, bound=data.bound, naptan_id=data.naptan_id, expected_departure_time=data.expected_departure_time, last_updated=DEFAULT "+
			"FROM (VALUES %s) AS data(line, bound, naptan_id, vehicle_id, expected_departure_time) "+
			"WHERE data.vehicle_id=entry.vehicle_id",
			valuesCommaSep))

	db.MustExec(
		fmt.Sprintf("WITH data(line, bound, naptan_id, vehicle_id, expected_departure_time) AS (VALUES %s) "+
			"INSERT INTO terminus_departures(line, bound, naptan_id, vehicle_id, expected_departure_time) "+
			"SELECT data.line, data.bound, data.naptan_id, data.vehicle_id, data.expected_departure_time FROM data WHERE NOT EXISTS "+
			"(SELECT 1 FROM terminus_departures entry "+
			"WHERE data.vehicle_id=entry.vehicle_id)",
			valuesCommaSep))
	dbconn.ReleaseLock()
}