yixiaogao/backend/tools/view_db.go

package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"log"

	_ "modernc.org/sqlite"
)

func main() {
	// 打开数据库
	db, err := sql.Open("sqlite", "../../data/wechat_articles.db")
	if err != nil {
		log.Fatal("打开数据库失败:", err)
	}
	defer db.Close()

	fmt.Println("=" + repeatStr("=", 80))
	fmt.Println("📊 微信公众号文章数据库内容查看")
	fmt.Println("=" + repeatStr("=", 80))

	// 查询公众号
	fmt.Println("\n📢 【公众号列表】")
	fmt.Println(repeatStr("-", 80))
	queryOfficialAccounts(db)

	// 查询文章
	fmt.Println("\n📝 【文章列表】")
	fmt.Println(repeatStr("-", 80))
	queryArticles(db)

	// 查询文章内容
	fmt.Println("\n📄 【文章详细内容】")
	fmt.Println(repeatStr("-", 80))
	queryArticleContents(db)

	fmt.Println("\n" + repeatStr("=", 80))
}

func queryOfficialAccounts(db *sql.DB) {
	rows, err := db.Query(`
		SELECT id, biz, nickname, homepage, description, created_at, updated_at
		FROM official_accounts
		ORDER BY id
	`)
	if err != nil {
		log.Printf("查询公众号失败: %v\n", err)
		return
	}
	defer rows.Close()

	count := 0
	for rows.Next() {
		var id int
		var biz, nickname, homepage, description, createdAt, updatedAt string
		err := rows.Scan(&id, &biz, &nickname, &homepage, &description, &createdAt, &updatedAt)
		if err != nil {
			log.Printf("读取数据失败: %v\n", err)
			continue
		}
		count++

		fmt.Printf("\n🔹 公众号 #%d\n", id)
		fmt.Printf("   名称: %s\n", nickname)
		fmt.Printf("   BIZ: %s\n", biz)
		fmt.Printf("   主页: %s\n", homepage)
		fmt.Printf("   简介: %s\n", description)
		fmt.Printf("   创建时间: %s\n", createdAt)
		fmt.Printf("   更新时间: %s\n", updatedAt)
	}

	if count == 0 {
		fmt.Println("   暂无数据")
	} else {
		fmt.Printf("\n总计: %d 个公众号\n", count)
	}
}

func queryArticles(db *sql.DB) {
	rows, err := db.Query(`
		SELECT a.id, a.official_id, a.title, a.author, a.link, a.publish_time,
		       a.read_num, a.like_num, a.share_num, a.paragraph_count,
		       a.content_preview, a.created_at, oa.nickname
		FROM articles a
		LEFT JOIN official_accounts oa ON a.official_id = oa.id
		ORDER BY a.id
	`)
	if err != nil {
		log.Printf("查询文章失败: %v\n", err)
		return
	}
	defer rows.Close()

	count := 0
	for rows.Next() {
		var id, officialID, readNum, likeNum, shareNum, paragraphCount int
		var title, author, link, publishTime, contentPreview, createdAt, officialName sql.NullString
		err := rows.Scan(&id, &officialID, &title, &author, &link, &publishTime,
			&readNum, &likeNum, &shareNum, &paragraphCount, &contentPreview, &createdAt, &officialName)
		if err != nil {
			log.Printf("读取数据失败: %v\n", err)
			continue
		}
		count++

		fmt.Printf("\n🔹 文章 #%d\n", id)
		fmt.Printf("   标题: %s\n", getStringValue(title))
		if officialName.Valid {
			fmt.Printf("   公众号: %s\n", officialName.String)
		}
		fmt.Printf("   作者: %s\n", getStringValue(author))
		fmt.Printf("   链接: %s\n", getStringValue(link))
		fmt.Printf("   发布时间: %s\n", getStringValue(publishTime))
		fmt.Printf("   阅读数: %d | 点赞数: %d | 分享数: %d\n", readNum, likeNum, shareNum)
		fmt.Printf("   段落数: %d\n", paragraphCount)
		if contentPreview.Valid && contentPreview.String != "" {
			preview := contentPreview.String
			if len(preview) > 100 {
				preview = preview[:100] + "..."
			}
			fmt.Printf("   内容预览: %s\n", preview)
		}
		fmt.Printf("   抓取时间: %s\n", getStringValue(createdAt))
	}

	if count == 0 {
		fmt.Println("   暂无数据")
	} else {
		fmt.Printf("\n总计: %d 篇文章\n", count)
	}
}

func queryArticleContents(db *sql.DB) {
	rows, err := db.Query(`
		SELECT ac.id, ac.article_id, ac.html_content, ac.text_content,
		       ac.paragraphs, ac.images, ac.created_at, a.title
		FROM article_contents ac
		LEFT JOIN articles a ON ac.article_id = a.id
		ORDER BY ac.id
	`)
	if err != nil {
		log.Printf("查询文章内容失败: %v\n", err)
		return
	}
	defer rows.Close()

	count := 0
	for rows.Next() {
		var id, articleID int
		var htmlContent, textContent, paragraphs, images, createdAt, title sql.NullString
		err := rows.Scan(&id, &articleID, &htmlContent, &textContent,
			&paragraphs, &images, &createdAt, &title)
		if err != nil {
			log.Printf("读取数据失败: %v\n", err)
			continue
		}
		count++

		fmt.Printf("\n🔹 内容 #%d (文章ID: %d)\n", id, articleID)
		if title.Valid {
			fmt.Printf("   文章标题: %s\n", title.String)
		}

		// HTML内容长度
		htmlLen := 0
		if htmlContent.Valid {
			htmlLen = len(htmlContent.String)
		}
		fmt.Printf("   HTML内容长度: %d 字符\n", htmlLen)

		// 文本内容
		if textContent.Valid && textContent.String != "" {
			text := textContent.String
			if len(text) > 200 {
				text = text[:200] + "..."
			}
			fmt.Printf("   文本内容: %s\n", text)
		}

		// 段落信息
		if paragraphs.Valid && paragraphs.String != "" {
			var paragraphList []interface{}
			if err := json.Unmarshal([]byte(paragraphs.String), &paragraphList); err == nil {
				fmt.Printf("   段落数量: %d\n", len(paragraphList))
			}
		}

		// 图片信息
		if images.Valid && images.String != "" {
			var imageList []interface{}
			if err := json.Unmarshal([]byte(images.String), &imageList); err == nil {
				fmt.Printf("   图片数量: %d\n", len(imageList))
				if len(imageList) > 0 {
					fmt.Printf("   图片URL:\n")
					for i, img := range imageList {
						if i >= 3 {
							fmt.Printf("      ... 还有 %d 张图片\n", len(imageList)-3)
							break
						}
						fmt.Printf("      %d. %v\n", i+1, img)
					}
				}
			}
		}

		fmt.Printf("   存储时间: %s\n", getStringValue(createdAt))
	}

	if count == 0 {
		fmt.Println("   暂无数据")
	} else {
		fmt.Printf("\n总计: %d 条详细内容\n", count)
	}
}

func getStringValue(s sql.NullString) string {
	if s.Valid {
		return s.String
	}
	return ""
}

func repeatStr(s string, n int) string {
	result := ""
	for i := 0; i < n; i++ {
		result += s
	}
	return result
}