118 lines
3.0 KiB
Go
118 lines
3.0 KiB
Go
package database
|
||
|
||
import (
|
||
"database/sql"
|
||
"fmt"
|
||
"os"
|
||
"path/filepath"
|
||
|
||
_ "modernc.org/sqlite"
|
||
)
|
||
|
||
// DB 数据库实例
|
||
type DB struct {
|
||
*sql.DB
|
||
}
|
||
|
||
// InitDB 初始化数据库
|
||
func InitDB(dbPath string) (*DB, error) {
|
||
// 确保数据库目录存在
|
||
dbDir := filepath.Dir(dbPath)
|
||
if err := os.MkdirAll(dbDir, 0755); err != nil {
|
||
return nil, fmt.Errorf("创建数据库目录失败: %v", err)
|
||
}
|
||
|
||
// 打开数据库连接（使用modernc.org/sqlite驱动）
|
||
db, err := sql.Open("sqlite", dbPath)
|
||
if err != nil {
|
||
return nil, fmt.Errorf("打开数据库失败: %v", err)
|
||
}
|
||
|
||
// 测试连接
|
||
if err := db.Ping(); err != nil {
|
||
return nil, fmt.Errorf("数据库连接测试失败: %v", err)
|
||
}
|
||
|
||
// 创建表
|
||
if err := createTables(db); err != nil {
|
||
return nil, fmt.Errorf("创建数据表失败: %v", err)
|
||
}
|
||
|
||
fmt.Println("✅ 数据库初始化成功:", dbPath)
|
||
return &DB{db}, nil
|
||
}
|
||
|
||
// createTables 创建数据表
|
||
func createTables(db *sql.DB) error {
|
||
// 公众号表
|
||
officialAccountTable := `
|
||
CREATE TABLE IF NOT EXISTS official_accounts (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
biz TEXT NOT NULL UNIQUE,
|
||
nickname TEXT NOT NULL,
|
||
homepage TEXT,
|
||
description TEXT,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
CREATE INDEX IF NOT EXISTS idx_biz ON official_accounts(biz);
|
||
CREATE INDEX IF NOT EXISTS idx_nickname ON official_accounts(nickname);
|
||
`
|
||
|
||
// 文章表
|
||
articleTable := `
|
||
CREATE TABLE IF NOT EXISTS articles (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
official_id INTEGER NOT NULL,
|
||
title TEXT NOT NULL,
|
||
author TEXT,
|
||
link TEXT UNIQUE,
|
||
publish_time TEXT,
|
||
create_time TEXT,
|
||
comment_id TEXT,
|
||
read_num INTEGER DEFAULT 0,
|
||
like_num INTEGER DEFAULT 0,
|
||
share_num INTEGER DEFAULT 0,
|
||
content_preview TEXT,
|
||
paragraph_count INTEGER DEFAULT 0,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (official_id) REFERENCES official_accounts(id)
|
||
);
|
||
CREATE INDEX IF NOT EXISTS idx_official_id ON articles(official_id);
|
||
CREATE INDEX IF NOT EXISTS idx_title ON articles(title);
|
||
CREATE INDEX IF NOT EXISTS idx_publish_time ON articles(publish_time);
|
||
CREATE INDEX IF NOT EXISTS idx_link ON articles(link);
|
||
`
|
||
|
||
// 文章内容表
|
||
articleContentTable := `
|
||
CREATE TABLE IF NOT EXISTS article_contents (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
article_id INTEGER NOT NULL UNIQUE,
|
||
html_content TEXT,
|
||
text_content TEXT,
|
||
paragraphs TEXT,
|
||
images TEXT,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE
|
||
);
|
||
CREATE INDEX IF NOT EXISTS idx_article_id ON article_contents(article_id);
|
||
`
|
||
|
||
// 执行创建表语句
|
||
tables := []string{officialAccountTable, articleTable, articleContentTable}
|
||
for _, table := range tables {
|
||
if _, err := db.Exec(table); err != nil {
|
||
return err
|
||
}
|
||
}
|
||
|
||
return nil
|
||
}
|
||
|
||
// Close 关闭数据库连接
|
||
func (db *DB) Close() error {
|
||
return db.DB.Close()
|
||
}
|