go-bot/test/update_message_seq.go

package main

import (
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"io"
	"log"
	"net/http"

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

// 这个工具用来修复旧数据库没有 message_seq 字段的问题
func main() {
	// Open SQLite database
	db, err := sql.Open("sqlite3", "../data.db")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	// Create messages table if not exists
	_, err = db.Exec(`
		CREATE TABLE IF NOT EXISTS messages (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			message_id INTEGER UNIQUE NOT NULL,
			message_seq INTEGER
		)
	`)
	if err != nil {
		log.Fatal(err)
	}

	// Fetch messageIDs from database
	rows, err := db.Query("SELECT message_id FROM messages WHERE message_seq IS NULL")
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	var messageIDs []int
	for rows.Next() {
		var messageID int
		err := rows.Scan(&messageID)
		if err != nil {
			log.Fatal(err)
		}
		messageIDs = append(messageIDs, messageID)

	}
	if err := rows.Err(); err != nil {
		log.Fatal(err)
	}
	for _, messageID := range messageIDs {
		messageSeq, err := fetchMessageSeq(messageID)
		if err != nil {
			log.Fatal(err)
			continue
		}
		err = insertOrUpdateMessageSeq(db, messageID, messageSeq)
		if err != nil {
			log.Fatal(err)
			continue
		}
		fmt.Printf("MessageSeq for message_id %d is %d\n", messageID, messageSeq)

	}

}

func fetchMessageSeq(messageID int) (int, error) {
	url := fmt.Sprintf("http://127.0.0.1:5700/get_msg?message_id=%d", messageID)
	fmt.Println("URL:", url)
	resp, err := http.Get(url)
	if err != nil {
		return 0, err
	}
	defer resp.Body.Close()

	body, err := io.ReadAll(resp.Body)
	if err != nil {
		return 0, err
	}
	println("Body:", string(body))

	var messageResp map[string]interface{}
	err = json.Unmarshal(body, &messageResp)
	if err != nil {
		return 0, err
	}
	data, ok := messageResp["data"].(map[string]interface{})
	if !ok {
		return 0, errors.New("data is not a map")
	}
	message_seq := int(data["message_seq"].(float64))
	// println(message_seq)
	return message_seq, nil
}

func insertOrUpdateMessageSeq(db *sql.DB, messageID, messageSeq int) error {
	// Try to insert messageSeq
	// _, err := db.Exec("INSERT INTO messages (message_id, message_seq) VALUES (?, ?)", messageID, messageSeq)
	// if err != nil {
	// If messageID already exists, update messageSeq
	_, err := db.Exec("UPDATE messages SET message_seq = ? WHERE message_id = ?", messageSeq, messageID)
	if err != nil {
		return err
		// }
	}

	return nil
}