mirror of
https://github.com/MeshCore-Beacon/beacon-server.git
synced 2026-10-16 07:19:58 +00:00
n30nex
cf5ff7a974
* feat(stats): compare distinct flood packets across observers * test(stats): use the migrated observation deduplication key
76 lines
2.2 KiB
Go
76 lines
2.2 KiB
Go
// Code generated by sqlc. DO NOT EDIT.
|
|
// versions:
|
|
// sqlc v1.31.1
|
|
// source: observer_comparison.sql
|
|
|
|
package db
|
|
|
|
import (
|
|
"context"
|
|
|
|
"github.com/google/uuid"
|
|
"github.com/jackc/pgx/v5/pgtype"
|
|
)
|
|
|
|
const getObserverComparison = `-- name: GetObserverComparison :one
|
|
WITH heard AS (
|
|
SELECT po.packet_hash,
|
|
bool_or(po.observer_id = $1::uuid) AS seen_a,
|
|
bool_or(po.observer_id = $2::uuid) AS seen_b
|
|
FROM packet_observations po
|
|
JOIN packets p ON p.packet_hash = po.packet_hash
|
|
WHERE po.observer_id IN ($1::uuid, $2::uuid)
|
|
AND po.heard_at >= $3::timestamptz
|
|
AND po.heard_at < $4::timestamptz
|
|
AND p.route_type IN (0, 1)
|
|
AND (COALESCE(cardinality($5::text[]), 0) = 0 OR po.iata = ANY($5::text[]))
|
|
GROUP BY po.packet_hash
|
|
)
|
|
SELECT count(*) AS total_packets,
|
|
count(*) FILTER (WHERE seen_a AND NOT seen_b) AS only_a,
|
|
count(*) FILTER (WHERE seen_b AND NOT seen_a) AS only_b,
|
|
count(*) FILTER (WHERE seen_a AND seen_b) AS both,
|
|
EXISTS (SELECT 1 FROM observers WHERE id = $1::uuid) AS known_a,
|
|
EXISTS (SELECT 1 FROM observers WHERE id = $2::uuid) AS known_b
|
|
FROM heard
|
|
`
|
|
|
|
type GetObserverComparisonParams struct {
|
|
ObserverA uuid.UUID `json:"observer_a"`
|
|
ObserverB uuid.UUID `json:"observer_b"`
|
|
Since pgtype.Timestamptz `json:"since"`
|
|
Until pgtype.Timestamptz `json:"until"`
|
|
Iatas []string `json:"iatas"`
|
|
}
|
|
|
|
type GetObserverComparisonRow struct {
|
|
TotalPackets int64 `json:"total_packets"`
|
|
OnlyA int64 `json:"only_a"`
|
|
OnlyB int64 `json:"only_b"`
|
|
Both int64 `json:"both"`
|
|
KnownA bool `json:"known_a"`
|
|
KnownB bool `json:"known_b"`
|
|
}
|
|
|
|
// Count packet identities, not reception rows: a broker repeat or another
|
|
// reception by the same observer must not inflate the comparison.
|
|
func (q *Queries) GetObserverComparison(ctx context.Context, arg GetObserverComparisonParams) (GetObserverComparisonRow, error) {
|
|
row := q.db.QueryRow(ctx, getObserverComparison,
|
|
arg.ObserverA,
|
|
arg.ObserverB,
|
|
arg.Since,
|
|
arg.Until,
|
|
arg.Iatas,
|
|
)
|
|
var i GetObserverComparisonRow
|
|
err := row.Scan(
|
|
&i.TotalPackets,
|
|
&i.OnlyA,
|
|
&i.OnlyB,
|
|
&i.Both,
|
|
&i.KnownA,
|
|
&i.KnownB,
|
|
)
|
|
return i, err
|
|
}
|