148 lines
4.0 KiB
Go
148 lines
4.0 KiB
Go
//go:build ignore
|
||
|
||
// 数据库完整性检查工具
|
||
package main
|
||
|
||
import (
|
||
"fmt"
|
||
"log"
|
||
|
||
"github.com/glebarez/sqlite"
|
||
"gorm.io/gorm"
|
||
)
|
||
|
||
func main() {
|
||
db, err := gorm.Open(sqlite.Open("./data/user_management.db"), &gorm.Config{})
|
||
if err != nil {
|
||
log.Fatal("open db:", err)
|
||
}
|
||
|
||
fmt.Println("=== 数据库完整性检查 ===\n")
|
||
|
||
// 1. 表存在性检查
|
||
tables := []string{"users", "roles", "permissions", "user_roles", "role_permissions",
|
||
"devices", "login_logs", "operation_logs", "social_accounts",
|
||
"webhooks", "webhook_deliveries", "password_histories"}
|
||
|
||
fmt.Println("[1] 表结构检查:")
|
||
for _, table := range tables {
|
||
var count int64
|
||
result := db.Raw("SELECT COUNT(*) FROM " + table).Scan(&count)
|
||
if result.Error != nil {
|
||
fmt.Printf(" ❌ %s: ERROR - %v\n", table, result.Error)
|
||
} else {
|
||
fmt.Printf(" ✅ %s: %d rows\n", table, count)
|
||
}
|
||
}
|
||
|
||
// 2. 用户数据完整性
|
||
fmt.Println("\n[2] 用户数据:")
|
||
var users []struct {
|
||
ID int64
|
||
Username string
|
||
Email *string
|
||
Status int
|
||
CreatedAt string
|
||
}
|
||
db.Raw("SELECT id, username, email, status, created_at FROM users").Scan(&users)
|
||
for _, u := range users {
|
||
email := "NULL"
|
||
if u.Email != nil {
|
||
email = *u.Email
|
||
}
|
||
fmt.Printf(" User[%d]: %s | email=%s | status=%d | created=%s\n",
|
||
u.ID, u.Username, email, u.Status, u.CreatedAt)
|
||
}
|
||
|
||
// 3. 角色-权限绑定
|
||
fmt.Println("\n[3] 角色-权限绑定:")
|
||
var rolePerms []struct {
|
||
RoleID int64
|
||
PermissionID int64
|
||
}
|
||
db.Raw("SELECT role_id, permission_id FROM role_permissions").Scan(&rolePerms)
|
||
if len(rolePerms) == 0 {
|
||
fmt.Println(" ⚠️ 没有角色-权限绑定数据")
|
||
} else {
|
||
for _, rp := range rolePerms {
|
||
fmt.Printf(" role_id=%d permission_id=%d\n", rp.RoleID, rp.PermissionID)
|
||
}
|
||
}
|
||
|
||
// 4. 操作日志（近5条）
|
||
fmt.Println("\n[4] 操作日志（最近5条）:")
|
||
var opLogs []struct {
|
||
ID int64
|
||
UserID int64
|
||
RequestMethod string
|
||
RequestPath string
|
||
ResponseStatus int
|
||
CreatedAt string
|
||
}
|
||
db.Raw("SELECT id, user_id, request_method, request_path, response_status, created_at FROM operation_logs ORDER BY id DESC LIMIT 5").Scan(&opLogs)
|
||
for _, l := range opLogs {
|
||
fmt.Printf(" [%d] user=%d %s %s status=%d time=%s\n",
|
||
l.ID, l.UserID, l.RequestMethod, l.RequestPath, l.ResponseStatus, l.CreatedAt)
|
||
}
|
||
|
||
// 5. 登录日志
|
||
fmt.Println("\n[5] 登录日志:")
|
||
var loginLogs []struct {
|
||
ID int64
|
||
UserID int64
|
||
IP string
|
||
Status int
|
||
CreatedAt string
|
||
}
|
||
db.Raw("SELECT id, user_id, ip, status, created_at FROM login_logs ORDER BY id DESC LIMIT 10").Scan(&loginLogs)
|
||
if len(loginLogs) == 0 {
|
||
fmt.Println(" ⚠️ 没有登录日志数据 - 登录时未记录！")
|
||
} else {
|
||
for _, l := range loginLogs {
|
||
fmt.Printf(" [%d] user=%d ip=%s status=%d time=%s\n",
|
||
l.ID, l.UserID, l.IP, l.Status, l.CreatedAt)
|
||
}
|
||
}
|
||
|
||
// 6. 密码历史
|
||
fmt.Println("\n[6] 密码历史:")
|
||
var pwdHistory []struct {
|
||
ID int64
|
||
UserID int64
|
||
CreatedAt string
|
||
}
|
||
db.Raw("SELECT id, user_id, created_at FROM password_histories ORDER BY id DESC LIMIT 5").Scan(&pwdHistory)
|
||
for _, ph := range pwdHistory {
|
||
fmt.Printf(" [%d] user=%d time=%s\n", ph.ID, ph.UserID, ph.CreatedAt)
|
||
}
|
||
|
||
// 7. 索引检查
|
||
fmt.Println("\n[7] 主要唯一约束验证:")
|
||
|
||
// 检查 users 邮箱唯一
|
||
var dupEmails []struct {
|
||
Email string
|
||
Count int64
|
||
}
|
||
db.Raw("SELECT email, COUNT(*) as count FROM users WHERE email IS NOT NULL GROUP BY email HAVING count > 1").Scan(&dupEmails)
|
||
if len(dupEmails) == 0 {
|
||
fmt.Println(" ✅ users.email 唯一性: OK")
|
||
} else {
|
||
fmt.Printf(" ❌ users.email 重复: %v\n", dupEmails)
|
||
}
|
||
|
||
// 检查 users 用户名唯一
|
||
var dupUsernames []struct {
|
||
Username string
|
||
Count int64
|
||
}
|
||
db.Raw("SELECT username, COUNT(*) as count FROM users GROUP BY username HAVING count > 1").Scan(&dupUsernames)
|
||
if len(dupUsernames) == 0 {
|
||
fmt.Println(" ✅ users.username 唯一性: OK")
|
||
} else {
|
||
fmt.Printf(" ❌ users.username 重复: %v\n", dupUsernames)
|
||
}
|
||
|
||
fmt.Println("\n=== 检查完成 ===")
|
||
}
|