1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201

package main

import (
	"context"
	"os"
	"testing"
)

// devDSN returns the development database URL, or skips the test.
//
// These tests run against the restored copy of the production database (see
// whatcd-resolver.1, section DEVELOPMENT DATABASE); they are skipped when it is
// not available, so `go test ./...` still works on a fresh checkout.
func devDSN(t *testing.T) string {
	t.Helper()
	dsn := os.Getenv("WHATCD_RESOLVER_DATABASE_URL")
	if dsn == "" {
		t.Skip("WHATCD_RESOLVER_DATABASE_URL not set, skipping database test")
	}
	return dsn
}

// TestMigrateIdempotent is the check that matters for the port: the schema is
// applied at every startup against a database that already has data in it, and
// it must not disturb that data.
//
// The specific hazard is `seeding_weight` and `artist_ids`, which are STORED
// generated columns computed by plpgsql. If a migration were to drop and re-add
// them, or if calc_seeding_weight were subtly changed, every torrent's ranking
// would move without any error being raised.
func TestMigrateIdempotent(t *testing.T) {
	ctx := context.Background()
	pool, err := connectDB(ctx, devDSN(t))
	if err != nil {
		t.Fatal(err)
	}
	defer pool.Close()

	type snapshot struct {
		torrents, groups, artists, favourites int64
		weightSum, artistIDSum                int64
	}
	take := func() snapshot {
		var s snapshot
		q := func(sql string, dst *int64) {
			if err := pool.QueryRow(ctx, sql).Scan(dst); err != nil {
				t.Fatalf("%s: %v", sql, err)
			}
		}
		q(`SELECT count(*) FROM redacted.torrents_json`, &s.torrents)
		q(`SELECT count(*) FROM redacted.torrent_groups`, &s.groups)
		q(`SELECT count(*) FROM redacted.artists`, &s.artists)
		q(`SELECT count(*) FROM redacted.artist_favourites`, &s.favourites)
		// The generated columns: a checksum over all rows catches any change.
		q(`SELECT COALESCE(sum(seeding_weight)::bigint, 0) FROM redacted.torrents_json`, &s.weightSum)
		q(`SELECT COALESCE(sum(x)::bigint, 0) FROM (SELECT unnest(artist_ids) x FROM redacted.torrents_json) _`, &s.artistIDSum)
		return s
	}

	before := take()
	if before.torrents == 0 {
		t.Fatal("development database appears empty; restore it first")
	}

	// Applying twice in a row must be a no-op both times.
	for i := 0; i < 2; i++ {
		if err := migrate(ctx, pool); err != nil {
			t.Fatalf("migration run %d failed: %v", i+1, err)
		}
	}

	after := take()
	if before != after {
		t.Errorf("migration changed the database:\n before = %+v\n after  = %+v", before, after)
	}
}

// TestSchemaShape verifies that the objects the queries depend on exist with the
// properties the queries assume: the generated columns must be STORED (the
// queries ORDER BY seeding_weight, which would be unusably slow otherwise) and
// artist_ids must have its GIN index (used by the `@>` and `&&` lookups).
func TestSchemaShape(t *testing.T) {
	ctx := context.Background()
	pool, err := connectDB(ctx, devDSN(t))
	if err != nil {
		t.Fatal(err)
	}
	defer pool.Close()

	if err := migrate(ctx, pool); err != nil {
		t.Fatal(err)
	}

	for _, c := range []struct{ column, want string }{
		{"seeding_weight", "s"}, // 's' = stored
		{"artist_ids", "s"},
	} {
		var generated string
		err := pool.QueryRow(ctx, `
			SELECT attgenerated FROM pg_attribute
			WHERE attrelid = 'redacted.torrents_json'::regclass AND attname = $1`,
			c.column).Scan(&generated)
		if err != nil {
			t.Fatalf("looking up %s: %v", c.column, err)
		}
		if generated != c.want {
			t.Errorf("%s attgenerated = %q, want %q (STORED)", c.column, generated, c.want)
		}
	}

	var hasGIN bool
	err = pool.QueryRow(ctx, `
		SELECT EXISTS (
			SELECT 1 FROM pg_index i
			JOIN pg_class c ON c.oid = i.indexrelid
			JOIN pg_am am ON am.oid = c.relam
			WHERE i.indrelid = 'redacted.torrents_json'::regclass AND am.amname = 'gin')`).Scan(&hasGIN)
	if err != nil {
		t.Fatal(err)
	}
	if !hasGIN {
		t.Error("expected a GIN index on redacted.torrents_json(artist_ids)")
	}

	// The `torrents` view is what most queries actually read from.
	var hasView bool
	if err := pool.QueryRow(ctx, `
		SELECT EXISTS (SELECT 1 FROM pg_views WHERE schemaname='redacted' AND viewname='torrents')`).Scan(&hasView); err != nil {
		t.Fatal(err)
	}
	if !hasView {
		t.Error("expected the redacted.torrents view to exist")
	}
}

// TestSeedingWeightFormula pins the ranking function, which is the single most
// behaviour-defining piece of SQL in the project: it decides which torrent of a
// group is "the best" one and therefore what the UI offers to download.
//
// The cases below are computed by hand from the formula in migrationSQL.
func TestSeedingWeightFormula(t *testing.T) {
	ctx := context.Background()
	pool, err := connectDB(ctx, devDSN(t))
	if err != nil {
		t.Fatal(err)
	}
	defer pool.Close()

	if err := migrate(ctx, pool); err != nil {
		t.Fatal(err)
	}

	cases := []struct {
		name string
		json string
		want int
	}{
		{
			// 3*10 + 5 = 35, no multipliers apply
			name: "plain",
			json: `{"seeders":10,"snatches":5,"encoding":"320","remasterTitle":""}`,
			want: 35,
		},
		{
			// 35 * 3 (remaster)
			name: "remaster tripled",
			json: `{"seeders":10,"snatches":5,"encoding":"320","remasterTitle":"2011 Remaster"}`,
			want: 105,
		},
		{
			// 35 * 2 (V0 preferred over 320 CBR)
			name: "V0 doubled",
			json: `{"seeders":10,"snatches":5,"encoding":"V0 (VBR)","remasterTitle":""}`,
			want: 70,
		},
		{
			// 24bit is zeroed out: too big to be worth downloading
			name: "24bit zeroed",
			json: `{"seeders":10,"snatches":5,"encoding":"24bit Lossless","remasterTitle":""}`,
			want: 0,
		},
		{
			// 35 / 5 (lossless discounted so mp3 wins when available); integer division
			name: "lossless discounted",
			json: `{"seeders":10,"snatches":5,"encoding":"Lossless","remasterTitle":""}`,
			want: 7,
		},
	}

	for _, c := range cases {
		t.Run(c.name, func(t *testing.T) {
			var got int
			if err := pool.QueryRow(ctx, `SELECT calc_seeding_weight($1::jsonb)`, c.json).Scan(&got); err != nil {
				t.Fatal(err)
			}
			if got != c.want {
				t.Errorf("calc_seeding_weight(%s) = %d, want %d", c.json, got, c.want)
			}
		})
	}
}