diff options
Diffstat (limited to 'internal/database')
| -rw-r--r-- | internal/database/queries_test.go | 441 |
1 files changed, 441 insertions, 0 deletions
diff --git a/internal/database/queries_test.go b/internal/database/queries_test.go new file mode 100644 index 0000000..435f8b0 --- /dev/null +++ b/internal/database/queries_test.go @@ -0,0 +1,441 @@ +package database + +import ( + "context" + "database/sql" + "fmt" + "testing" + "time" + + "git.tjp.lol/punchcard/internal/queries" + _ "modernc.org/sqlite" +) + +// setupTestDB creates an in-memory SQLite database for testing +func setupTestDB(t *testing.T) (*queries.Queries, *sql.DB, func()) { + db, err := sql.Open("sqlite", ":memory:") + if err != nil { + t.Fatalf("Failed to open in-memory sqlite db: %v", err) + } + + // Initialize with the full schema + if err := InitializeDB(db); err != nil { + t.Fatalf("Failed to initialize database: %v", err) + } + + q := queries.New(db) + + cleanup := func() { + if err := db.Close(); err != nil { + t.Logf("error closing database: %v", err) + } + } + + return q, db, cleanup +} + +func TestSQLQueryTimezoneConsistency(t *testing.T) { + // Test that SQL queries handle timezone boundaries correctly + tests := []struct { + name string + setupData func(*sql.DB, int64) error + queryTest func(*queries.Queries, int64) error + }{ + { + name: "today summary query handles timezone boundaries", + setupData: func(db *sql.DB, clientID int64) error { + now := time.Now() + + // Insert entries for today, yesterday, and tomorrow in various timezone scenarios + entries := []struct { + start, end string + desc string + }{ + // Entry clearly in "today" regardless of timezone + {now.Add(-2 * time.Hour).Format("2006-01-02 15:04:05"), now.Add(-1 * time.Hour).Format("2006-01-02 15:04:05"), "today_clear"}, + + // Entry at the edge of today/yesterday boundary + {now.Format("2006-01-02") + " 00:30:00", now.Format("2006-01-02") + " 01:30:00", "today_early"}, + + // Entry at the edge of today/tomorrow boundary + {now.Format("2006-01-02") + " 22:30:00", now.Format("2006-01-02") + " 23:30:00", "today_late"}, + + // Entry from yesterday + {now.AddDate(0, 0, -1).Format("2006-01-02") + " 12:00:00", now.AddDate(0, 0, -1).Format("2006-01-02") + " 13:00:00", "yesterday"}, + } + + for _, entry := range entries { + _, err := db.Exec(` + INSERT INTO time_entry (start_time, end_time, client_id, description) + VALUES (?, ?, ?, ?) + `, entry.start, entry.end, clientID, entry.desc) + if err != nil { + return err + } + } + return nil + }, + queryTest: func(q *queries.Queries, clientID int64) error { + // Test GetTodaySummary - returns total seconds as int64 + totalSeconds, err := q.GetTodaySummary(context.Background()) + if err != nil { + return err + } + + // We should get some seconds from "today" entries + // The exact amount depends on the test data, could be 0 if no entries match today + if totalSeconds < 0 { + t.Errorf("Expected non-negative total seconds, got %d", totalSeconds) + } + + t.Logf("GetTodaySummary returned %d total seconds", totalSeconds) + return nil + }, + }, + { + name: "week summary query handles week boundaries", + setupData: func(db *sql.DB, clientID int64) error { + now := time.Now() + + // Calculate this week's Monday + weekday := int(now.Weekday()) + if weekday == 0 { + weekday = 7 // Sunday = 7 + } + thisMonday := now.AddDate(0, 0, -(weekday - 1)) + + entries := []struct { + start, end string + desc string + }{ + // Entry clearly in this week + {thisMonday.AddDate(0, 0, 1).Format("2006-01-02") + " 10:00:00", thisMonday.AddDate(0, 0, 1).Format("2006-01-02") + " 11:00:00", "this_week"}, + + // Entry from last week + {thisMonday.AddDate(0, 0, -2).Format("2006-01-02") + " 10:00:00", thisMonday.AddDate(0, 0, -2).Format("2006-01-02") + " 11:00:00", "last_week"}, + + // Entry at the week boundary (this Monday) + {thisMonday.Format("2006-01-02") + " 00:30:00", thisMonday.Format("2006-01-02") + " 01:30:00", "week_boundary"}, + } + + for _, entry := range entries { + _, err := db.Exec(` + INSERT INTO time_entry (start_time, end_time, client_id, description) + VALUES (?, ?, ?, ?) + `, entry.start, entry.end, clientID, entry.desc) + if err != nil { + return err + } + } + return nil + }, + queryTest: func(q *queries.Queries, clientID int64) error { + // Test GetWeekSummaryByProject + summaryRows, err := q.GetWeekSummaryByProject(context.Background()) + if err != nil { + return err + } + + // Should get entries from this week + if len(summaryRows) == 0 { + t.Logf("Warning: GetWeekSummaryByProject returned no entries") + } + + for _, s := range summaryRows { + if s.TotalSeconds <= 0 { + t.Errorf("Expected positive duration, got %d", s.TotalSeconds) + } + } + + return nil + }, + }, + { + name: "mixed timezone modifiers work correctly", + setupData: func(db *sql.DB, clientID int64) error { + // Test the mixed 'localtime' and 'utc' usage in queries + now := time.Now() + + // Insert entries that test the boundary conditions of mixed timezone usage + _, err := db.Exec(` + INSERT INTO time_entry (start_time, end_time, client_id, description) VALUES + (?, ?, ?, 'boundary_test_1'), + (?, ?, ?, 'boundary_test_2') + `, + now.Format("2006-01-02 15:04:05"), now.Add(time.Hour).Format("2006-01-02 15:04:05"), clientID, + now.Add(-24*time.Hour).Format("2006-01-02 15:04:05"), now.Add(-23*time.Hour).Format("2006-01-02 15:04:05"), clientID, + ) + return err + }, + queryTest: func(q *queries.Queries, clientID int64) error { + // Test both today and week summaries to ensure consistent timezone handling + todayTotal, err := q.GetTodaySummary(context.Background()) + if err != nil { + return err + } + + weekSummaryRows, err := q.GetWeekSummaryByProject(context.Background()) + if err != nil { + return err + } + + // Calculate week total from summary rows + var weekTotal int64 + for _, s := range weekSummaryRows { + weekTotal += s.TotalSeconds + } + + // Week total should be >= today total (assuming both have entries) + if weekTotal > 0 && todayTotal > weekTotal { + t.Errorf("Week total (%d) should be >= today total (%d)", weekTotal, todayTotal) + } + + t.Logf("Today total: %d seconds, Week total: %d seconds", todayTotal, weekTotal) + return nil + }, + }, + } + + for _, tt := range tests { + t.Run(tt.name, func(t *testing.T) { + q, db, cleanup := setupTestDB(t) + defer cleanup() + + // Create test client + client, err := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "TestClient", + }) + if err != nil { + t.Fatalf("Failed to create client: %v", err) + } + + // Setup test data + if err := tt.setupData(db, client.ID); err != nil { + t.Fatalf("Failed to setup test data: %v", err) + } + + // Run the query test + if err := tt.queryTest(q, client.ID); err != nil { + t.Fatalf("Query test failed: %v", err) + } + }) + } +} + +func TestTimezoneQueryBoundaryEdgeCases(t *testing.T) { + // Test specific edge cases around timezone boundaries + q, db, cleanup := setupTestDB(t) + defer cleanup() + + // Create test client + client, err := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "EdgeCaseClient", + }) + if err != nil { + t.Fatalf("Failed to create client: %v", err) + } + + // Test case: entry that starts in one day (UTC) but is in another day (local) + // This tests the edge case where localtime and UTC disagree about the date + now := time.Now() + + // Find a time where local and UTC dates might differ + // For example, if it's currently 2 AM local but 8 AM UTC (in a UTC+6 timezone) + testTime := time.Date(now.Year(), now.Month(), now.Day(), 1, 0, 0, 0, time.Local) // 1 AM local + utcTestTime := testTime.UTC() + + // Insert entry at this boundary time + _, err = db.Exec(` + INSERT INTO time_entry (start_time, end_time, client_id, description) + VALUES (?, ?, ?, 'boundary_edge_case') + `, testTime.Format("2006-01-02 15:04:05"), testTime.Add(time.Hour).Format("2006-01-02 15:04:05"), client.ID) + if err != nil { + t.Fatalf("Failed to insert test entry: %v", err) + } + + t.Logf("Test entry: Local time %s, UTC time %s", testTime.Format("2006-01-02 15:04:05"), utcTestTime.Format("2006-01-02 15:04:05")) + + // Test today summary + todaySummary, err := q.GetTodaySummary(context.Background()) + if err != nil { + // GetTodaySummary might fail if there are no entries for today (returns NULL) + t.Logf("GetTodaySummary failed (likely no entries for today): %v", err) + return + } + + // The entry should appear in today's summary if the query uses localtime correctly + // Note: GetTodaySummary returns total seconds, so we just check if it's positive + found := todaySummary > 0 + + // Log result for manual verification - exact behavior depends on current timezone + t.Logf("Today summary %s entry for boundary case (local: %s, UTC: %s, total seconds: %d)", + map[bool]string{true: "found", false: "did not find"}[found], + testTime.Format("2006-01-02"), utcTestTime.Format("2006-01-02"), todaySummary) +} + +func TestSQLiteTimezoneFunction(t *testing.T) { + // Test that SQLite's timezone functions work as expected + _, db, cleanup := setupTestDB(t) + defer cleanup() + + tests := []struct { + name string + query string + expected string // Expected format or pattern + }{ + { + name: "datetime now UTC", + query: "SELECT datetime('now', 'utc')", + expected: "UTC timestamp format", + }, + { + name: "datetime now localtime", + query: "SELECT datetime('now', 'localtime')", + expected: "Local timestamp format", + }, + { + name: "date conversion from UTC to localtime", + query: "SELECT date('2024-08-22 12:00:00', 'localtime')", + expected: "Date format", + }, + { + name: "weekday calculation", + query: "SELECT date('now', 'localtime', 'weekday 0', '-6 days')", + expected: "Monday of this week", + }, + } + + for _, tt := range tests { + t.Run(tt.name, func(t *testing.T) { + var result string + err := db.QueryRow(tt.query).Scan(&result) + if err != nil { + t.Fatalf("Query failed: %v", err) + } + + // Verify result is not empty and has reasonable format + if result == "" { + t.Errorf("Expected non-empty result for %s", tt.name) + } + + t.Logf("%s result: %s", tt.name, result) + + // Basic format validation + switch tt.name { + case "datetime now UTC", "datetime now localtime": + // Should be YYYY-MM-DD HH:MM:SS format + if _, err := time.Parse("2006-01-02 15:04:05", result); err != nil { + t.Errorf("Result %s not in expected datetime format: %v", result, err) + } + case "date conversion from UTC to localtime": + // Should be YYYY-MM-DD format + if _, err := time.Parse("2006-01-02", result); err != nil { + t.Errorf("Result %s not in expected date format: %v", result, err) + } + case "weekday calculation": + // Should be a date, and should be a Monday + date, err := time.Parse("2006-01-02", result) + if err != nil { + t.Errorf("Result %s not in expected date format: %v", result, err) + } else if date.Weekday() != time.Monday { + t.Errorf("Expected Monday, got %v for result %s", date.Weekday(), result) + } + } + }) + } +} + +func TestQueryResultTimezoneConsistency(t *testing.T) { + // Test that queries return timestamps in a consistent format + q, db, cleanup := setupTestDB(t) + defer cleanup() + + // Create test data + client, err := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "ConsistencyTestClient", + }) + if err != nil { + t.Fatalf("Failed to create client: %v", err) + } + + // Insert entry with known UTC time + utcTime := "2024-08-22 14:30:00" + _, err = db.Exec(` + INSERT INTO time_entry (start_time, end_time, client_id, description) + VALUES (?, ?, ?, 'consistency_test') + `, utcTime, "2024-08-22 16:30:00", client.ID) + if err != nil { + t.Fatalf("Failed to insert test entry: %v", err) + } + + // Test different query methods return consistent timestamp formats + tests := []struct { + name string + queryFunc func() (time.Time, error) + }{ + { + name: "GetMostRecentTimeEntry", + queryFunc: func() (time.Time, error) { + entry, err := q.GetMostRecentTimeEntry(context.Background()) + if err != nil { + return time.Time{}, err + } + return entry.StartTime, nil + }, + }, + { + name: "Direct SQL query", + queryFunc: func() (time.Time, error) { + var startTime string + err := db.QueryRow("SELECT start_time FROM time_entry ORDER BY id DESC LIMIT 1").Scan(&startTime) + if err != nil { + return time.Time{}, err + } + // Parse as stored format - try different formats since SQLite might return different formats + formats := []string{ + "2006-01-02 15:04:05", // Standard format + "2006-01-02T15:04:05Z", // ISO format with Z + "2006-01-02T15:04:05", // ISO format without Z + time.RFC3339, // RFC3339 format + } + for _, format := range formats { + if t, err := time.Parse(format, startTime); err == nil { + return t, nil + } + } + return time.Time{}, fmt.Errorf("unable to parse time %q with any known format", startTime) + }, + }, + } + + var timestamps []time.Time + for _, tt := range tests { + t.Run(tt.name, func(t *testing.T) { + timestamp, err := tt.queryFunc() + if err != nil { + t.Fatalf("Query failed: %v", err) + } + + timestamps = append(timestamps, timestamp) + + // Verify timestamp is not zero + if timestamp.IsZero() { + t.Errorf("Got zero timestamp from %s", tt.name) + } + + t.Logf("%s returned: %s (location: %v)", tt.name, timestamp.Format("2006-01-02 15:04:05"), timestamp.Location()) + }) + } + + // Verify all queries returned equivalent times (accounting for timezone differences) + if len(timestamps) >= 2 { + first := timestamps[0].UTC() + for i, ts := range timestamps[1:] { + second := ts.UTC() + if !first.Equal(second) { + t.Errorf("Timestamp inconsistency: query 0 returned %v, query %d returned %v (both in UTC)", first, i+1, second) + } + } + } +} + |