From 4d160762aeef531208f8cf6094a0e58a9e7d53ec Mon Sep 17 00:00:00 2001 From: Peter Stone Date: Sun, 20 Sep 2026 07:57:36 +0000 Subject: Add .ics calendar feed support MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit doot reads Google Calendar through a service account, by calendar ID. That cannot consume an .ics URL, and it cannot see a calendar subscribed to inside a personal Google account either, because Google does not allow re-sharing a subscribed calendar. Published schedules — sports fixtures, school terms, bin collections — are distributed almost exclusively as .ics, so none of them could be added at all. Feeds are now fetched on the normal calendar refresh and merged into calendar_events alongside Google events, so the timeline, widget and everything downstream keep seeing one list and needed no changes. Failure handling is deliberately asymmetric. A Google error is still returned as an error, unchanged, so an outage there surfaces. A single failing feed is recorded against that feed and skipped, because one dead URL should not take the whole calendar down — and last_error is shown in settings, since a feed that has quietly stopped resolving otherwise looks identical to a team with no games left. The parser handles what real feeds actually contain: line unfolding, CRLF and LF, UTC / TZID / VALUE=DATE times, and escaped text. Two details worth naming: a TZID time is wall-clock in that zone, and reading it as UTC would put a 1:15pm Pacific kickoff on the timeline at 1:15pm local — wrong by the offset but entirely plausible-looking. And non-calendar content is a hard error rather than zero events, because an HTML error page from a moved feed URL is the common failure and "no events" reads as success. RRULE is deliberately unsupported: every schedule feed this targets expands recurrence itself, one VEVENT per fixture, and a correct expansion is a large amount of subtle code. A recurring event appears once rather than wrongly. Settings gains an add/enable/disable/remove UI. webcal:// links are accepted and rewritten, since that is how feeds are usually advertised. 16 parser tests, 5 store tests. Verified against the real Seattle Seahawks feed: 21 events, all with valid times. Co-Authored-By: Claude Opus 5 Claude-Session: https://claude.ai/code/session_018AwnALZL6bD4VBtATDVv3B --- internal/store/ics_feeds.go | 95 ++++++++++++++++++++++++++ internal/store/ics_feeds_test.go | 144 +++++++++++++++++++++++++++++++++++++++ 2 files changed, 239 insertions(+) create mode 100644 internal/store/ics_feeds.go create mode 100644 internal/store/ics_feeds_test.go (limited to 'internal/store') diff --git a/internal/store/ics_feeds.go b/internal/store/ics_feeds.go new file mode 100644 index 0000000..6495e13 --- /dev/null +++ b/internal/store/ics_feeds.go @@ -0,0 +1,95 @@ +package store + +import ( + "database/sql" + "time" +) + +// ICSFeed is a subscribed iCalendar feed. +type ICSFeed struct { + ID int64 + Name string + URL string + Enabled bool + LastFetchedAt *time.Time + LastError string + EventCount int + CreatedAt time.Time +} + +// AddICSFeed stores a feed. Re-adding an existing URL updates its name and +// re-enables it rather than failing on the unique constraint — "add it again" +// is the obvious way to try to fix a feed that was turned off. +func (s *Store) AddICSFeed(name, url string) error { + _, err := s.db.Exec(` + INSERT INTO ics_feeds (name, url, enabled) + VALUES (?, ?, 1) + ON CONFLICT(url) DO UPDATE SET name = excluded.name, enabled = 1 + `, name, url) + return err +} + +// GetICSFeeds returns every feed, enabled or not, newest name-order first. +func (s *Store) GetICSFeeds() ([]ICSFeed, error) { + return s.queryICSFeeds(` + SELECT id, name, url, enabled, last_fetched_at, COALESCE(last_error, ''), event_count, created_at + FROM ics_feeds ORDER BY name`) +} + +// GetEnabledICSFeeds returns only the feeds that should be fetched. +func (s *Store) GetEnabledICSFeeds() ([]ICSFeed, error) { + return s.queryICSFeeds(` + SELECT id, name, url, enabled, last_fetched_at, COALESCE(last_error, ''), event_count, created_at + FROM ics_feeds WHERE enabled = 1 ORDER BY name`) +} + +func (s *Store) queryICSFeeds(query string) ([]ICSFeed, error) { + rows, err := s.db.Query(query) + if err != nil { + return nil, err + } + defer func() { _ = rows.Close() }() + + var feeds []ICSFeed + for rows.Next() { + var f ICSFeed + var lastFetched sql.NullTime + if err := rows.Scan(&f.ID, &f.Name, &f.URL, &f.Enabled, &lastFetched, + &f.LastError, &f.EventCount, &f.CreatedAt); err != nil { + return nil, err + } + if lastFetched.Valid { + t := lastFetched.Time + f.LastFetchedAt = &t + } + feeds = append(feeds, f) + } + return feeds, rows.Err() +} + +// SetICSFeedEnabled turns a feed on or off without losing it. +func (s *Store) SetICSFeedEnabled(id int64, enabled bool) error { + _, err := s.db.Exec(`UPDATE ics_feeds SET enabled = ? WHERE id = ?`, enabled, id) + return err +} + +// DeleteICSFeed removes a feed entirely. +func (s *Store) DeleteICSFeed(id int64) error { + _, err := s.db.Exec(`DELETE FROM ics_feeds WHERE id = ?`, id) + return err +} + +// RecordICSFeedResult stores the outcome of a fetch. A successful fetch clears +// any previous error, so a feed that recovers stops being reported as broken. +func (s *Store) RecordICSFeedResult(id int64, eventCount int, fetchErr error) error { + errText := "" + if fetchErr != nil { + errText = fetchErr.Error() + } + _, err := s.db.Exec(` + UPDATE ics_feeds + SET last_fetched_at = CURRENT_TIMESTAMP, last_error = ?, event_count = ? + WHERE id = ? + `, errText, eventCount, id) + return err +} diff --git a/internal/store/ics_feeds_test.go b/internal/store/ics_feeds_test.go new file mode 100644 index 0000000..5b8889c --- /dev/null +++ b/internal/store/ics_feeds_test.go @@ -0,0 +1,144 @@ +package store + +import ( + "database/sql" + "errors" + "path/filepath" + "testing" + + _ "github.com/mattn/go-sqlite3" +) + +func setupICSStore(t *testing.T) *Store { + t.Helper() + db, err := sql.Open("sqlite3", filepath.Join(t.TempDir(), "ics.db")) + if err != nil { + t.Fatalf("open: %v", err) + } + db.SetMaxOpenConns(1) + if _, err := db.Exec(` + CREATE TABLE ics_feeds ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + name TEXT NOT NULL, + url TEXT NOT NULL UNIQUE, + enabled INTEGER NOT NULL DEFAULT 1, + last_fetched_at DATETIME, + last_error TEXT, + event_count INTEGER NOT NULL DEFAULT 0, + created_at DATETIME DEFAULT CURRENT_TIMESTAMP + );`); err != nil { + t.Fatalf("schema: %v", err) + } + return &Store{db: db} +} + +func TestICSFeeds_AddAndList(t *testing.T) { + s := setupICSStore(t) + defer func() { _ = s.Close() }() + + if err := s.AddICSFeed("Seahawks", "https://example.com/sea.ics"); err != nil { + t.Fatalf("add: %v", err) + } + feeds, err := s.GetICSFeeds() + if err != nil { + t.Fatalf("list: %v", err) + } + if len(feeds) != 1 || feeds[0].Name != "Seahawks" || !feeds[0].Enabled { + t.Fatalf("got %+v", feeds) + } + if feeds[0].LastFetchedAt != nil { + t.Error("a feed that has never been fetched should have no timestamp") + } +} + +// Re-adding the same URL should update and re-enable rather than error, since +// "add it again" is the obvious way to try to fix a disabled feed. +func TestICSFeeds_ReAddingUpdatesAndReEnables(t *testing.T) { + s := setupICSStore(t) + defer func() { _ = s.Close() }() + + _ = s.AddICSFeed("Old name", "https://example.com/sea.ics") + feeds, _ := s.GetICSFeeds() + _ = s.SetICSFeedEnabled(feeds[0].ID, false) + + if err := s.AddICSFeed("Seahawks", "https://example.com/sea.ics"); err != nil { + t.Fatalf("re-add: %v", err) + } + feeds, _ = s.GetICSFeeds() + if len(feeds) != 1 { + t.Fatalf("expected 1 feed, got %d", len(feeds)) + } + if feeds[0].Name != "Seahawks" || !feeds[0].Enabled { + t.Errorf("got %+v, want renamed and re-enabled", feeds[0]) + } +} + +func TestICSFeeds_DisabledAreExcludedFromFetchList(t *testing.T) { + s := setupICSStore(t) + defer func() { _ = s.Close() }() + + _ = s.AddICSFeed("On", "https://example.com/on.ics") + _ = s.AddICSFeed("Off", "https://example.com/off.ics") + all, _ := s.GetICSFeeds() + for _, f := range all { + if f.Name == "Off" { + _ = s.SetICSFeedEnabled(f.ID, false) + } + } + + enabled, err := s.GetEnabledICSFeeds() + if err != nil { + t.Fatalf("enabled: %v", err) + } + if len(enabled) != 1 || enabled[0].Name != "On" { + t.Errorf("got %+v, want only the enabled feed", enabled) + } +} + +// A failure must be recorded, and a later success must clear it — otherwise a +// feed that recovers still shows as broken forever. +func TestICSFeeds_RecordResultClearsPreviousError(t *testing.T) { + s := setupICSStore(t) + defer func() { _ = s.Close() }() + + _ = s.AddICSFeed("Seahawks", "https://example.com/sea.ics") + feeds, _ := s.GetICSFeeds() + id := feeds[0].ID + + if err := s.RecordICSFeedResult(id, 0, errors.New("feed returned HTTP 404")); err != nil { + t.Fatalf("record failure: %v", err) + } + feeds, _ = s.GetICSFeeds() + if feeds[0].LastError == "" { + t.Fatal("expected the failure to be recorded") + } + if feeds[0].LastFetchedAt == nil { + t.Error("a failed fetch is still a fetch attempt and should be timestamped") + } + + if err := s.RecordICSFeedResult(id, 21, nil); err != nil { + t.Fatalf("record success: %v", err) + } + feeds, _ = s.GetICSFeeds() + if feeds[0].LastError != "" { + t.Errorf("a successful fetch should clear the error, got %q", feeds[0].LastError) + } + if feeds[0].EventCount != 21 { + t.Errorf("EventCount = %d, want 21", feeds[0].EventCount) + } +} + +func TestICSFeeds_Delete(t *testing.T) { + s := setupICSStore(t) + defer func() { _ = s.Close() }() + + _ = s.AddICSFeed("Seahawks", "https://example.com/sea.ics") + feeds, _ := s.GetICSFeeds() + if err := s.DeleteICSFeed(feeds[0].ID); err != nil { + t.Fatalf("delete: %v", err) + } + feeds, _ = s.GetICSFeeds() + if len(feeds) != 0 { + t.Errorf("got %d feeds after delete", len(feeds)) + } +} -- cgit v1.2.3