304 lines
10 KiB
Go
304 lines
10 KiB
Go
package repository
|
|
|
|
import (
|
|
"context"
|
|
"slices"
|
|
"strings"
|
|
"testing"
|
|
"time"
|
|
|
|
"fire-safety-ymd/internal/domain"
|
|
)
|
|
|
|
func TestOpenPostGISDoesNotExposeInvalidDSN(t *testing.T) {
|
|
secret := "do-not-leak"
|
|
_, err := OpenPostGIS(context.Background(), PostGISOptions{
|
|
DSN: "postgresql://user:" + secret + "%zz@localhost/database",
|
|
MaxConns: 1,
|
|
ConnectTimeout: time.Second,
|
|
QueryTimeout: time.Second,
|
|
})
|
|
if err == nil {
|
|
t.Fatal("OpenPostGIS() error = nil")
|
|
}
|
|
if strings.Contains(err.Error(), secret) {
|
|
t.Fatalf("OpenPostGIS() leaked DSN content: %v", err)
|
|
}
|
|
}
|
|
|
|
func TestValidateReadinessAcceptsExpectedGeometryContracts(t *testing.T) {
|
|
report := validReadinessReport()
|
|
if err := validateReadiness(report, 4326); err != nil {
|
|
t.Fatalf("validateReadiness() error = %v", err)
|
|
}
|
|
}
|
|
|
|
func TestValidateReadinessRejectsUnsafeSpatialMetadata(t *testing.T) {
|
|
tests := []struct {
|
|
name string
|
|
mutate func(*ReadinessReport)
|
|
want string
|
|
}{
|
|
{
|
|
name: "SRID zero",
|
|
mutate: func(report *ReadinessReport) {
|
|
report.Tables[0].SRIDs = []int{0}
|
|
},
|
|
want: "unexpected SRID 0",
|
|
},
|
|
{
|
|
name: "mixed SRID",
|
|
mutate: func(report *ReadinessReport) {
|
|
report.Tables[1].SRIDs = []int{4326, 3857}
|
|
},
|
|
want: "unexpected SRID 3857",
|
|
},
|
|
{
|
|
name: "unexpected geometry type",
|
|
mutate: func(report *ReadinessReport) {
|
|
report.Tables[2].GeometryTypes = []string{"POLYGON"}
|
|
},
|
|
want: "unexpected geometry type POLYGON",
|
|
},
|
|
{
|
|
name: "coordinate outside WGS84",
|
|
mutate: func(report *ReadinessReport) {
|
|
report.Tables[0].OutOfBoundsCount = 1
|
|
},
|
|
want: "outside WGS84 bounds",
|
|
},
|
|
}
|
|
|
|
for _, tt := range tests {
|
|
t.Run(tt.name, func(t *testing.T) {
|
|
report := validReadinessReport()
|
|
tt.mutate(&report)
|
|
err := validateReadiness(report, 4326)
|
|
if err == nil || !strings.Contains(err.Error(), tt.want) {
|
|
t.Fatalf("validateReadiness() error = %v, want %q", err, tt.want)
|
|
}
|
|
})
|
|
}
|
|
}
|
|
|
|
func TestValidateReadinessAllowsInvalidGeometriesThatQueriesExclude(t *testing.T) {
|
|
report := validReadinessReport()
|
|
report.Tables[5].InvalidCount = 4
|
|
report.Tables[6].InvalidCount = 4
|
|
report.Tables[7].InvalidCount = 27
|
|
|
|
if err := validateReadiness(report, 4326); err != nil {
|
|
t.Fatalf("validateReadiness() error = %v", err)
|
|
}
|
|
}
|
|
|
|
func TestTableAuditWarningsExposeExcludedInvalidGeometries(t *testing.T) {
|
|
audit := TableAudit{
|
|
Table: "st_2_fanghuowangge",
|
|
RowCount: 2,
|
|
NullGeometryCount: 1,
|
|
EmptyGeometryCount: 1,
|
|
InvalidCount: 1,
|
|
HasGISTIndex: false,
|
|
}
|
|
|
|
warnings := tableAuditWarnings(audit)
|
|
for _, want := range []string{
|
|
"st_2_fanghuowangge:null_geometries_excluded",
|
|
"st_2_fanghuowangge:empty_geometries_excluded",
|
|
"st_2_fanghuowangge:invalid_geometries_excluded",
|
|
"st_2_fanghuowangge:missing_gist_geometry_index",
|
|
} {
|
|
if !slices.Contains(warnings, want) {
|
|
t.Fatalf("warnings = %#v, want %q", warnings, want)
|
|
}
|
|
}
|
|
}
|
|
|
|
func TestSpatialQueriesKeepScopeAndBoundsParameterized(t *testing.T) {
|
|
nearbyQueries := []struct {
|
|
name string
|
|
query string
|
|
wantScopePredicates int
|
|
}{
|
|
{name: "water sources", query: nearbyWaterSourcesSQL, wantScopePredicates: 2},
|
|
{name: "command posts", query: commandPostCandidatesSQL, wantScopePredicates: 2},
|
|
{name: "access lines", query: nearbyAccessLinesSQL, wantScopePredicates: 1},
|
|
{name: "risk areas", query: nearbyRiskAreasSQL, wantScopePredicates: 2},
|
|
}
|
|
for _, tt := range nearbyQueries {
|
|
query := tt.query
|
|
for _, required := range []string{"$3::boolean", "ANY($4::text[])", "$5", "LIMIT $6", "OFFSET $7", "COUNT(*) OVER()::bigint", "ST_SRID", "ST_IsValid", "ST_IsEmpty", "ST_Expand"} {
|
|
if !strings.Contains(query, required) {
|
|
t.Fatalf("%s query lacks %q", tt.name, required)
|
|
}
|
|
}
|
|
if got := strings.Count(query, "($3::boolean OR"); got != tt.wantScopePredicates {
|
|
t.Fatalf("%s query has %d scope predicates, want %d", tt.name, got, tt.wantScopePredicates)
|
|
}
|
|
if got := strings.Count(query, "ANY($4::text[])"); got != tt.wantScopePredicates {
|
|
t.Fatalf("%s query has %d town predicates, want %d", tt.name, got, tt.wantScopePredicates)
|
|
}
|
|
}
|
|
for index, query := range []string{resolveIncidentContextSQL, responsibleUnitsSQL} {
|
|
for _, required := range []string{"$3::boolean", "ANY($4::text[])", "LIMIT $5", "OFFSET $6", "COUNT(*) OVER()::bigint", "ST_SRID", "ST_IsValid", "ST_IsEmpty", "ST_Covers"} {
|
|
if !strings.Contains(query, required) {
|
|
t.Fatalf("cover query %d lacks %q", index, required)
|
|
}
|
|
}
|
|
if got := strings.Count(query, "($3::boolean OR"); got != 1 {
|
|
t.Fatalf("cover query %d has %d scope predicates, want 1", index, got)
|
|
}
|
|
}
|
|
}
|
|
|
|
func TestPlaceCandidateSearchKeepsTermScopeAndLimitParameterized(t *testing.T) {
|
|
for _, required := range []string{
|
|
"lower($1::text)",
|
|
"($2::boolean OR",
|
|
"ANY($3::text[])",
|
|
"LIMIT $4",
|
|
"OFFSET $5",
|
|
"COUNT(*) OVER()::bigint",
|
|
"strpos",
|
|
"recorded_point",
|
|
"representative_point",
|
|
"ST_Force2D",
|
|
"ST_IsValid",
|
|
} {
|
|
if !strings.Contains(placeCandidateSearchSQL, required) {
|
|
t.Fatalf("place candidate query lacks %q", required)
|
|
}
|
|
}
|
|
if got := strings.Count(placeCandidateSearchSQL, "($2::boolean OR"); got != len(spatialTableSpecs) {
|
|
t.Fatalf("place candidate query has %d scope predicates, want %d", got, len(spatialTableSpecs))
|
|
}
|
|
if got := strings.Count(placeCandidateSearchSQL, "ST_IsValid("); got != len(spatialTableSpecs) {
|
|
t.Fatalf("place candidate query has %d validity filters, want %d", got, len(spatialTableSpecs))
|
|
}
|
|
for _, spec := range spatialTableSpecs {
|
|
if !strings.Contains(placeCandidateSearchSQL, "public."+spec.name) {
|
|
t.Fatalf("place candidate query does not include %s", spec.name)
|
|
}
|
|
}
|
|
}
|
|
|
|
func TestSpatialPaginationQueriesUseOneWindowCountAndStableOrdering(t *testing.T) {
|
|
tests := []struct {
|
|
name string
|
|
query string
|
|
wantLimit string
|
|
wantOffset string
|
|
orderBy string
|
|
}{
|
|
{name: "place candidates", query: placeCandidateSearchSQL, wantLimit: "LIMIT $4", wantOffset: "OFFSET $5", orderBy: "ORDER BY match_rank, town, village, name, place_type, source_record_id"},
|
|
{name: "incident context", query: resolveIncidentContextSQL, wantLimit: "LIMIT $5", wantOffset: "OFFSET $6", orderBy: "ORDER BY ST_Area(g.geom::geography), g.gid"},
|
|
{name: "water sources", query: nearbyWaterSourcesSQL, wantLimit: "LIMIT $6", wantOffset: "OFFSET $7", orderBy: "ORDER BY distance_meters, category, source_record_id"},
|
|
{name: "command posts", query: commandPostCandidatesSQL, wantLimit: "LIMIT $6", wantOffset: "OFFSET $7", orderBy: "ORDER BY distance_meters, facility_type, source_record_id"},
|
|
{name: "access lines", query: nearbyAccessLinesSQL, wantLimit: "LIMIT $6", wantOffset: "OFFSET $7", orderBy: "ORDER BY distance_meters, source_record_id"},
|
|
{name: "responsible units", query: responsibleUnitsSQL, wantLimit: "LIMIT $5", wantOffset: "OFFSET $6", orderBy: "ORDER BY ST_Area(g.geom::geography), g.gid"},
|
|
{name: "risk areas", query: nearbyRiskAreasSQL, wantLimit: "LIMIT $6", wantOffset: "OFFSET $7", orderBy: "ORDER BY covers_point DESC, distance_meters, risk_type, source_record_id"},
|
|
}
|
|
|
|
for _, tt := range tests {
|
|
t.Run(tt.name, func(t *testing.T) {
|
|
if got := strings.Count(tt.query, "COUNT(*) OVER()::bigint"); got != 1 {
|
|
t.Fatalf("window count occurrences = %d, want 1", got)
|
|
}
|
|
orderIndex := strings.Index(tt.query, tt.orderBy)
|
|
limitIndex := strings.Index(tt.query, tt.wantLimit)
|
|
offsetIndex := strings.Index(tt.query, tt.wantOffset)
|
|
if orderIndex < 0 || limitIndex < 0 || offsetIndex < 0 {
|
|
t.Fatalf("query lacks stable order or parameterized pagination: order=%d limit=%d offset=%d", orderIndex, limitIndex, offsetIndex)
|
|
}
|
|
if !(orderIndex < limitIndex && limitIndex < offsetIndex) {
|
|
t.Fatalf("pagination order is not ORDER BY, LIMIT, OFFSET")
|
|
}
|
|
})
|
|
}
|
|
}
|
|
|
|
func TestRecoverEmptyOffsetPageTotalProbesOnlyEmptyLaterPages(t *testing.T) {
|
|
tests := []struct {
|
|
name string
|
|
offset int
|
|
page domain.Page[string]
|
|
wantCalls int
|
|
wantTotal int64
|
|
}{
|
|
{name: "first page empty", offset: 0, page: domain.Page[string]{Items: []string{}}, wantTotal: 0},
|
|
{name: "later page has items", offset: 10, page: domain.Page[string]{Items: []string{"item"}, TotalCount: 11}, wantTotal: 11},
|
|
{name: "later page empty", offset: 20, page: domain.Page[string]{Items: []string{}}, wantCalls: 1, wantTotal: 12},
|
|
}
|
|
|
|
for _, tt := range tests {
|
|
t.Run(tt.name, func(t *testing.T) {
|
|
calls := 0
|
|
got, err := recoverEmptyOffsetPageTotal(context.Background(), tt.offset, tt.page, func(context.Context) (domain.Page[string], error) {
|
|
calls++
|
|
return domain.Page[string]{Items: []string{"first"}, TotalCount: 12}, nil
|
|
})
|
|
if err != nil {
|
|
t.Fatalf("recoverEmptyOffsetPageTotal() error = %v", err)
|
|
}
|
|
if calls != tt.wantCalls {
|
|
t.Fatalf("probe calls = %d, want %d", calls, tt.wantCalls)
|
|
}
|
|
if got.TotalCount != tt.wantTotal {
|
|
t.Fatalf("total count = %d, want %d", got.TotalCount, tt.wantTotal)
|
|
}
|
|
})
|
|
}
|
|
}
|
|
|
|
func TestSpatialQueriesDoNotSelectSensitiveContactColumns(t *testing.T) {
|
|
queries := strings.Join([]string{
|
|
placeCandidateSearchSQL,
|
|
resolveIncidentContextSQL,
|
|
nearbyWaterSourcesSQL,
|
|
commandPostCandidatesSQL,
|
|
nearbyAccessLinesSQL,
|
|
responsibleUnitsSQL,
|
|
nearbyRiskAreasSQL,
|
|
}, "\n")
|
|
for _, forbidden := range []string{
|
|
"bpld",
|
|
"bplddh",
|
|
"csj",
|
|
"csjdh",
|
|
"lxdh",
|
|
"zjwgfzr",
|
|
"zjfzrlxfs",
|
|
"fhzddc",
|
|
"fhzddclxfs",
|
|
"chzbry",
|
|
"shzbry",
|
|
"zbry",
|
|
".bz",
|
|
} {
|
|
if strings.Contains(strings.ToLower(queries), strings.ToLower(forbidden)) {
|
|
t.Fatalf("spatial SQL references sensitive contact column %s", forbidden)
|
|
}
|
|
}
|
|
}
|
|
|
|
func TestQuoteIdentifierEscapesEmbeddedQuotes(t *testing.T) {
|
|
if got, want := quoteIdentifier(`safe"name`), `"safe""name"`; got != want {
|
|
t.Fatalf("quoteIdentifier() = %q, want %q", got, want)
|
|
}
|
|
}
|
|
|
|
func validReadinessReport() ReadinessReport {
|
|
report := ReadinessReport{Tables: make([]TableAudit, 0, len(spatialTableSpecs))}
|
|
for _, spec := range spatialTableSpecs {
|
|
report.Tables = append(report.Tables, TableAudit{
|
|
Table: spec.name,
|
|
RowCount: 2,
|
|
GeometryTypes: []string{spec.geometryTypes[0]},
|
|
SRIDs: []int{4326},
|
|
HasGISTIndex: true,
|
|
})
|
|
}
|
|
return report
|
|
}
|