Golang中SQLite函数测试遇到问题如何解决

从你的测试失败信息来看，主要问题在于数据库连接和表初始化的时机。让我分析一下具体问题和解决方案。

问题分析

在test1分支中，activities表不存在是因为表创建操作没有在测试中正确执行。在test2分支中，空指针解引用是因为数据库连接没有正确初始化。

解决方案

以下是修复后的测试代码示例：

package main

import (
    "database/sql"
    "os"
    "testing"
    "time"

    _ "github.com/mattn/go-sqlite3"
)

func TestMain(m *testing.M) {
    // 初始化测试数据库
    db, err := sql.Open("sqlite3", ":memory:")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    // 创建表结构
    _, err = db.Exec(`
        CREATE TABLE IF NOT EXISTS activities (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL UNIQUE
        );
        CREATE TABLE IF NOT EXISTS events (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            activity_id INTEGER NOT NULL,
            timestamp DATETIME NOT NULL,
            FOREIGN KEY (activity_id) REFERENCES activities (id)
        );
    `)
    if err != nil {
        panic(err)
    }

    // 预插入测试数据
    _, err = db.Exec("INSERT OR IGNORE INTO activities (name) VALUES (?)", "test_activity")
    if err != nil {
        panic(err)
    }

    // 运行测试
    code := m.Run()
    os.Exit(code)
}

func TestInsertEvents(t *testing.T) {
    db, err := sql.Open("sqlite3", ":memory:")
    if err != nil {
        t.Fatalf("Failed to open database: %v", err)
    }
    defer db.Close()

    // 初始化表
    initTestDB(db)

    testCases := []struct {
        name      string
        timestamp string
    }{
        {"2006-01-02_15:04:05+00:00", "2006-01-02T15:04:05Z"},
        {"2019-06-27_19:25:09-07:00", "2019-06-27T19:25:09-07:00"},
    }

    for _, tc := range testCases {
        t.Run(tc.name, func(t *testing.T) {
            ts, err := time.Parse(time.RFC3339, tc.timestamp)
            if err != nil {
                t.Fatalf("Failed to parse timestamp: %v", err)
            }

            // 插入活动
            _, err = db.Exec("INSERT OR IGNORE INTO activities (name) VALUES (?)", "test_activity")
            if err != nil {
                t.Fatalf("Failed to insert activity: %v", err)
            }

            // 插入事件
            _, err = db.Exec(
                "INSERT INTO events (activity_id, timestamp) VALUES ((SELECT id FROM activities WHERE name = ?), ?)",
                "test_activity", ts,
            )
            if err != nil {
                t.Fatalf("Failed to insert event: %v", err)
            }
        })
    }
}

func TestEvents(t *testing.T) {
    db, err := sql.Open("sqlite3", ":memory:")
    if err != nil {
        t.Fatalf("Failed to open database: %v", err)
    }
    defer db.Close()

    // 初始化表和数据
    initTestDB(db)
    insertTestData(db)

    t.Run("reviewing_1_day_back", func(t *testing.T) {
        // 查询事件
        rows, err := db.Query(`
            SELECT e.id, e.timestamp, a.name 
            FROM events e 
            JOIN activities a ON e.activity_id = a.id 
            WHERE e.timestamp >= datetime('now', '-1 day')
        `)
        if err != nil {
            t.Fatalf("Failed to query events: %v", err)
        }
        defer rows.Close()

        var count int
        for rows.Next() {
            count++
        }
        t.Logf("Found %d events from the last day", count)
    })
}

func initTestDB(db *sql.DB) {
    _, err := db.Exec(`
        CREATE TABLE IF NOT EXISTS activities (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL UNIQUE
        );
        CREATE TABLE IF NOT EXISTS events (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            activity_id INTEGER NOT NULL,
            timestamp DATETIME NOT NULL,
            FOREIGN KEY (activity_id) REFERENCES activities (id)
        );
    `)
    if err != nil {
        panic(err)
    }
}

func insertTestData(db *sql.DB) {
    // 插入测试活动
    activities := []string{"work", "exercise", "reading"}
    for _, activity := range activities {
        _, err := db.Exec("INSERT OR IGNORE INTO activities (name) VALUES (?)", activity)
        if err != nil {
            panic(err)
        }
    }

    // 插入测试事件
    _, err := db.Exec(`
        INSERT INTO events (activity_id, timestamp) 
        VALUES 
            ((SELECT id FROM activities WHERE name = 'work'), datetime('now')),
            ((SELECT id FROM activities WHERE name = 'exercise'), datetime('now', '-2 hours')),
            ((SELECT id FROM activities WHERE name = 'reading'), datetime('now', '-1 day'))
    `)
    if err != nil {
        panic(err)
    }
}

关键改进点

1. 使用内存数据库: sql.Open("sqlite3", ":memory:") 避免文件系统竞争条件
2. 确保表存在: 在每个测试中显式创建表结构
3. 事务支持: 对于更复杂的操作，使用事务确保数据一致性

func TestWithTransaction(t *testing.T) {
    db, err := sql.Open("sqlite3", ":memory:")
    if err != nil {
        t.Fatal(err)
    }
    defer db.Close()
    
    initTestDB(db)

    tx, err := db.Begin()
    if err != nil {
        t.Fatal(err)
    }
    defer tx.Rollback()

    // 在事务中执行操作
    _, err = tx.Exec("INSERT INTO activities (name) VALUES (?)", "transaction_test")
    if err != nil {
        t.Fatal(err)
    }

    err = tx.Commit()
    if err != nil {
        t.Fatal(err)
    }
}

这样修改后，测试应该能够稳定运行，不会出现表不存在或空指针的问题。