v0.16.0 import PGN, delete games, two-layout web client

internal/server/storage/game.go

@@ -17,7 +17,8 @@ const gameSelectColumns = `
 	g.white_player_id, g.white_type, g.white_level, g.white_search_time, g.white_claimed_by, g.white_name,
 	g.black_player_id, g.black_type, g.black_level, g.black_search_time, g.black_claimed_by, g.black_name,
 	g.result, g.termination, g.start_time_utc, g.end_time_utc,
-	g.concession_result, g.concession_termination, g.concession_ply, g.concession_time_utc`
+	g.concession_result, g.concession_termination, g.concession_ply, g.concession_time_utc,
+	g.imported_by, g.event, g.site, g.round`
 
 // claimantName snapshots a claimant's username inside the writing
 // transaction; NULL when the slot is unclaimed or the account is gone.
@@ -265,7 +266,7 @@ func (s *Store) DeleteAnonymousGames(cutoff time.Time, live []string) error {
 	}
 	return s.enqueue("delete_anonymous_games", "", func(ctx context.Context, tx *sql.Tx) error {
 		result, err := tx.ExecContext(ctx, `DELETE FROM games g
-			WHERE g.white_claimed_by IS NULL AND g.black_claimed_by IS NULL
+			WHERE g.white_claimed_by IS NULL AND g.black_claimed_by IS NULL AND g.imported_by IS NULL
 				AND g.start_time_utc < $1
 				AND (g.end_time_utc IS NULL OR g.end_time_utc < $1)
 				AND NOT EXISTS (
@@ -302,7 +303,7 @@ func (s *Store) QueryGames(gameID, playerID string) ([]GameRecord, error) {
 		}
 		args = append(args, playerID)
 		query += fmt.Sprintf(` AND $%d IN (g.white_player_id, g.black_player_id,
-			g.white_claimed_by, g.black_claimed_by)`, len(args))
+			g.white_claimed_by, g.black_claimed_by, g.imported_by)`, len(args))
 	}
 	query += " ORDER BY g.start_time_utc DESC, g.game_id DESC"
 
@@ -423,7 +424,7 @@ func (s *Store) QueryGamesForUser(userID string, q UserGamesQuery) ([]GameSummar
 	var where string
 	switch q.Color {
 	case "":
-		where = "(g.white_claimed_by = $1 OR g.black_claimed_by = $1)"
+		where = "(g.white_claimed_by = $1 OR g.black_claimed_by = $1 OR g.imported_by = $1)"
 	case "w":
 		where = "g.white_claimed_by = $1"
 	case "b":
@@ -541,6 +542,7 @@ type rowScanner interface {
 func scanGame(scanner rowScanner, record *GameRecord, extra ...any) error {
 	var whiteClaimed, whiteName, blackClaimed, blackName, result, termination sql.NullString
 	var concessionResult, concessionTermination sql.NullString
+	var importedBy, event, site, round sql.NullString
 	var concessionPly sql.NullInt64
 	var endTime, concessionTime sql.NullTime
 	dest := []any{
@@ -551,6 +553,7 @@ func scanGame(scanner rowScanner, record *GameRecord, extra ...any) error {
 		&blackClaimed, &blackName,
 		&result, &termination, &record.StartTimeUTC, &endTime,
 		&concessionResult, &concessionTermination, &concessionPly, &concessionTime,
+		&importedBy, &event, &site, &round,
 	}
 	if err := scanner.Scan(append(dest, extra...)...); err != nil {
 		return err
@@ -567,6 +570,10 @@ func scanGame(scanner rowScanner, record *GameRecord, extra ...any) error {
 	record.ConcessionTermination = concessionTermination.String
 	record.ConcessionPly = int(concessionPly.Int64)
 	record.ConcessionTimeUTC = utcPointer(concessionTime)
+	record.ImportedBy = importedBy.String
+	record.Event = event.String
+	record.Site = site.String
+	record.Round = round.String
 	return nil
 }
 

internal/server/storage/import.go

@@ -0,0 +1,127 @@
+package storage
+
+import (
+	"context"
+	"database/sql"
+	"errors"
+	"fmt"
+	"log/slog"
+)
+
+// ImportGame stores a game read from PGN with its whole move list in one
+// transaction, behind every gameplay write already queued, and returns once
+// it has committed. The record's ImportedBy names the owner; names, event,
+// site and round come from the PGN's tags.
+func (s *Store) ImportGame(record GameRecord, moves []MoveRecord) error {
+	if record.GameID == "" || record.InitialFEN == "" || record.WhitePlayerID == "" || record.BlackPlayerID == "" {
+		return errors.New("game ID, initial FEN, and player IDs are required")
+	}
+	if !validUUID(record.ImportedBy) {
+		return errors.New("an imported game needs its importer")
+	}
+	if err := validateOutcome(record.Result, record.Termination, record.EndTimeUTC); err != nil {
+		return err
+	}
+	for i, move := range moves {
+		if move.GameID != record.GameID || move.MoveNumber != i+1 || move.MoveUCI == "" || move.FENAfterMove == "" ||
+			(move.PlayerColor != "w" && move.PlayerColor != "b") {
+			return fmt.Errorf("move %d is malformed", i+1)
+		}
+	}
+
+	ctx, cancel := context.WithTimeout(context.Background(), flushTimeout+writeTimeout)
+	defer cancel()
+	return s.enqueueWait(ctx, "import_game", func(ctx context.Context, tx *sql.Tx) error {
+		if _, err := tx.ExecContext(ctx, `INSERT INTO games (
+			game_id, initial_fen,
+			white_player_id, white_type, white_level, white_search_time, white_claimed_by, white_name,
+			black_player_id, black_type, black_level, black_search_time, black_claimed_by, black_name,
+			start_time_utc, result, termination, end_time_utc,
+			imported_by, event, site, round
+		) VALUES ($1, $2, $3, $4, 0, 0, NULL, $5, $6, $7, 0, 0, NULL, $8, $9, $10, $11, $12, $13, $14, $15, $16)`,
+			record.GameID, record.InitialFEN,
+			record.WhitePlayerID, record.WhiteType, nullableString(record.WhiteName),
+			record.BlackPlayerID, record.BlackType, nullableString(record.BlackName),
+			record.StartTimeUTC, nullableString(record.Result), nullableString(record.Termination), record.EndTimeUTC,
+			record.ImportedBy, nullableString(record.Event), nullableString(record.Site), nullableString(record.Round),
+		); err != nil {
+			return err
+		}
+		for _, move := range moves {
+			if _, err := tx.ExecContext(ctx, `INSERT INTO moves (
+				game_id, move_number, move_uci, fen_after_move, player_color, move_time_utc
+			) VALUES ($1, $2, $3, $4, $5, $6)`,
+				move.GameID, move.MoveNumber, move.MoveUCI, move.FENAfterMove, move.PlayerColor, move.MoveTimeUTC,
+			); err != nil {
+				return err
+			}
+		}
+		slog.Debug("storage game imported", "game_id", record.GameID, "plies", len(moves), "user_id", record.ImportedBy)
+		return nil
+	})
+}
+
+// RemovalOutcome says what RemoveGameForUser did.
+type RemovalOutcome string
+
+const (
+	// RemovedGame: the game and its moves are gone.
+	RemovedGame RemovalOutcome = "game"
+	// RemovedClaim: another user still claims a side, so only the caller's
+	// claim and name were cleared; the game stays with the other player.
+	RemovedClaim RemovalOutcome = "claim"
+)
+
+// RemoveGameForUser deletes a stored game the user alone holds (as sole
+// claimant or importer), or, when another user claims a side, releases the
+// user's own claim so the game leaves their list but not the other's. It
+// returns sql.ErrNoRows when the user holds nothing in the game. Ordered
+// behind queued writes, it returns once committed.
+func (s *Store) RemoveGameForUser(gameID, userID string) (RemovalOutcome, error) {
+	if !validUUID(gameID) || !validUUID(userID) {
+		return "", sql.ErrNoRows
+	}
+	var outcome RemovalOutcome
+	ctx, cancel := context.WithTimeout(context.Background(), flushTimeout+writeTimeout)
+	defer cancel()
+	// A missing row or a game the user holds nothing in is an answer, not a
+	// failed write: the transaction then changes nothing and outcome stays
+	// empty, so the writer is never degraded by it.
+	err := s.enqueueWait(ctx, "remove_game_for_user", func(ctx context.Context, tx *sql.Tx) error {
+		var white, black, importer sql.NullString
+		err := tx.QueryRowContext(ctx,
+			`SELECT white_claimed_by, black_claimed_by, imported_by FROM games WHERE game_id = $1 FOR UPDATE`,
+			gameID).Scan(&white, &black, &importer)
+		if errors.Is(err, sql.ErrNoRows) {
+			return nil
+		}
+		if err != nil {
+			return err
+		}
+		if white.String != userID && black.String != userID && importer.String != userID {
+			return nil
+		}
+		others := (white.Valid && white.String != userID) || (black.Valid && black.String != userID)
+		if others {
+			_, err := tx.ExecContext(ctx, `UPDATE games SET
+				white_claimed_by = CASE WHEN white_claimed_by = $2 THEN NULL ELSE white_claimed_by END,
+				white_name       = CASE WHEN white_claimed_by = $2 THEN NULL ELSE white_name END,
+				black_claimed_by = CASE WHEN black_claimed_by = $2 THEN NULL ELSE black_claimed_by END,
+				black_name       = CASE WHEN black_claimed_by = $2 THEN NULL ELSE black_name END
+				WHERE game_id = $1`, gameID, userID)
+			outcome = RemovedClaim
+			return err
+		}
+		_, err = tx.ExecContext(ctx, `DELETE FROM games WHERE game_id = $1`, gameID)
+		outcome = RemovedGame
+		return err
+	})
+	if err != nil {
+		return "", err
+	}
+	if outcome == "" {
+		return "", sql.ErrNoRows
+	}
+	slog.Debug("storage game removed for user", "game_id", gameID, "user_id", userID, "outcome", outcome)
+	return outcome, nil
+}

internal/server/storage/integrity.go

@@ -62,9 +62,10 @@ func (s *Store) GameIDsAfter(after string, limit int) ([]string, error) {
 	return ids, collectIDs(rows, func(id string) { ids = append(ids, id) })
 }
 
-// OrphanedGames returns games that registered users claimed but whose
-// claimants were all deleted, idle since cutoff by the anonymous-game rule
-// (start, last move, and end all before it), excluding live games.
+// OrphanedGames returns games that registered users claimed or imported but
+// whose claimants and importer were all deleted, idle since cutoff by the
+// anonymous-game rule (start, last move, and end all before it), excluding
+// live games.
 func (s *Store) OrphanedGames(cutoff time.Time, live []string) ([]string, error) {
 	if live == nil {
 		live = []string{}
@@ -72,10 +73,10 @@ func (s *Store) OrphanedGames(cutoff time.Time, live []string) ([]string, error)
 	ctx, cancel := opContext()
 	defer cancel()
 	rows, err := s.db.QueryContext(ctx, `SELECT g.game_id FROM games g
-		WHERE (g.white_claimed_by IS NOT NULL OR g.black_claimed_by IS NOT NULL)
+		WHERE (g.white_claimed_by IS NOT NULL OR g.black_claimed_by IS NOT NULL OR g.imported_by IS NOT NULL)
 			AND NOT EXISTS (
 				SELECT 1 FROM users u
-				WHERE u.user_id IN (g.white_claimed_by, g.black_claimed_by)
+				WHERE u.user_id IN (g.white_claimed_by, g.black_claimed_by, g.imported_by)
 			)
 			AND g.start_time_utc < $1
 			AND (g.end_time_utc IS NULL OR g.end_time_utc < $1)

internal/server/storage/schema.go

@@ -47,6 +47,20 @@ type GameRecord struct {
 	ConcessionTermination string
 	ConcessionPly         int
 	ConcessionTimeUTC     *time.Time
+	// ImportedBy is the account that imported the game from PGN; empty for
+	// a game played here. Event, Site and Round come from its tags.
+	ImportedBy string
+	Event      string
+	Site       string
+	Round      string
+}
+
+// Origin names how a game came to be stored.
+func (r GameRecord) Origin() string {
+	if r.ImportedBy != "" {
+		return "imported"
+	}
+	return "played"
 }
 
 // GameSummaryRecord is a game row plus its replay extent. MoveCount and
@@ -81,7 +95,7 @@ type MovePersistence struct {
 // schemaVersion is the newest schema this binary understands. Migrations are
 // applied in order inside one transaction; PostgreSQL DDL is transactional, so
 // a failed upgrade leaves the previous version intact.
-const schemaVersion = 4
+const schemaVersion = 5
 
 // migrations[v-1] upgrades the schema from version v-1 to v. Never edit a
 // released migration; append a new one.
@@ -227,6 +241,23 @@ CREATE TABLE password_resets (
 	CONSTRAINT password_resets_token_hash_check CHECK (octet_length(token_hash) = 32)
 );
 CREATE INDEX password_resets_expires_at_idx ON password_resets (expires_at);
+`,
+
+	// v5: imported games. A game read from PGN belongs to the account that
+	// imported it (imported_by), has no claims, and keeps the PGN's event,
+	// site and round for its export. Imported games are not anonymous, so
+	// the purge index excludes them.
+	`
+ALTER TABLE games
+	ADD COLUMN imported_by uuid,
+	ADD COLUMN event text CHECK (char_length(event) BETWEEN 1 AND 200),
+	ADD COLUMN site text CHECK (char_length(site) BETWEEN 1 AND 200),
+	ADD COLUMN round text CHECK (char_length(round) BETWEEN 1 AND 40);
+DROP INDEX games_anonymous_start_idx;
+CREATE INDEX games_anonymous_start_idx ON games (start_time_utc)
+	WHERE white_claimed_by IS NULL AND black_claimed_by IS NULL AND imported_by IS NULL;
+CREATE INDEX games_imported_by_idx ON games (imported_by, start_time_utc DESC, game_id DESC)
+	WHERE imported_by IS NOT NULL;
 `,
 }
 

internal/server/storage/storage_test.go

@@ -341,7 +341,7 @@ func TestQueryPlansUsePurposeBuiltIndexes(t *testing.T) {
 		{
 			name: "anonymous purge candidates",
 			query: `SELECT game_id FROM games g
-				WHERE g.white_claimed_by IS NULL AND g.black_claimed_by IS NULL
+				WHERE g.white_claimed_by IS NULL AND g.black_claimed_by IS NULL AND g.imported_by IS NULL
 				AND g.start_time_utc < $1`,
 			args: []any{time.Now()},
 			want: []string{"games_anonymous_start_idx"},

internal/server/storage/user.go

@@ -150,8 +150,9 @@ func (s *Store) DeleteAccount(userID string) error {
 				white_claimed_by = CASE WHEN white_claimed_by = $1 THEN NULL ELSE white_claimed_by END,
 				white_name       = CASE WHEN white_claimed_by = $1 THEN NULL ELSE white_name END,
 				black_claimed_by = CASE WHEN black_claimed_by = $1 THEN NULL ELSE black_claimed_by END,
-				black_name       = CASE WHEN black_claimed_by = $1 THEN NULL ELSE black_name END
-			WHERE white_claimed_by = $1 OR black_claimed_by = $1`, userID); err != nil {
+				black_name       = CASE WHEN black_claimed_by = $1 THEN NULL ELSE black_name END,
+				imported_by      = CASE WHEN imported_by = $1 THEN NULL ELSE imported_by END
+			WHERE white_claimed_by = $1 OR black_claimed_by = $1 OR imported_by = $1`, userID); err != nil {
 			return err
 		}
 		result, err := tx.ExecContext(ctx, `DELETE FROM users WHERE user_id = $1`, userID)