时间：2026-04-30 点击： 次 来源：网络 作者：佚名 - 小 + 大
好的，我来展开数据库表结构的详细设计。软件下载站 - 数据库详细设计📊 整体 ER 图概览code复制users ─────┬───── user_favorites ─────┬───── software │ │ ├───── download_history ├───── software_ratings │ │ ├───── software_comments │ │ │ └───── account_settings │ software ──┬───── software_versions │ ├───── software_tags ──── tags │ ├───── software_screenshots │ ├───── software_downloads │ └───── software_audit_logcategories ──(自关联)── categoriespublishersos_platforms🗄️ 完整表结构1. 用户与认证sql复制-- ═══════════════════════════════════════════════════════════════-- 用户体系-- ═══════════════════════════════════════════════════════════════-- 用户主表CREATE TABLE users ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- 基础信息 username VARCHAR(50) UNIQUE NOT NULL, email VARCHAR(255) UNIQUE NOT NULL, password_hash VARCHAR(255) NOT NULL, -- 认证信息 phone VARCHAR(20) UNIQUE, email_verified BOOLEAN DEFAULT false, phone_verified BOOLEAN DEFAULT false, -- 安全 salt VARCHAR(32), login_attempts INT DEFAULT 0, locked_until TIMESTAMP, two_factor_enabled BOOLEAN DEFAULT false, two_factor_secret VARCHAR(255), -- 积分与等级 points INT DEFAULT 0, level INT DEFAULT 1, experience INT DEFAULT 0, -- 状态 status VARCHAR(20) DEFAULT 'active', -- active/suspended/banned last_login_at TIMESTAMP, last_login_ip INET, -- 头像与简介 avatar_url VARCHAR(512), bio TEXT, -- 时间戳 created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), deleted_at TIMESTAMP WITH TIME ZONE);-- 用户积分变动记录CREATE TABLE user_points_log ( id UUID PRIMARY KEY, user_id UUID NOT NULL REFERENCES users(id), change_amount INT NOT NULL, -- 正负数 change_type VARCHAR(30) NOT NULL, -- download/upload/review/redeem change_reason VARCHAR(255), balance_before INT NOT NULL, balance_after INT NOT NULL, reference_id UUID, -- 关联的软件ID/订单ID等 created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW());-- 用户收藏CREATE TABLE user_favorites ( id UUID PRIMARY KEY, user_id UUID NOT NULL REFERENCES users(id), software_id UUID NOT NULL REFERENCES software(id), created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), UNIQUE(user_id, software_id));-- 下载历史CREATE TABLE download_history ( id UUID PRIMARY KEY, user_id UUID REFERENCES users(id), software_id UUID NOT NULL REFERENCES software(id), version_id UUID REFERENCES software_versions(id), -- 下载信息 download_url VARCHAR(512), ip_address INET, user_agent TEXT, isp VARCHAR(100), country VARCHAR(10), province VARCHAR(50), city VARCHAR(50), -- 客户端信息 device_type VARCHAR(20), -- pc/mobile/tablet os_type VARCHAR(30), os_version VARCHAR(50), client_version VARCHAR(30), -- 速度统计 download_size BIGINT, download_duration INT, -- 秒 download_speed BIGINT, -- bytes/s created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW());-- 用户设置CREATE TABLE user_settings ( user_id UUID PRIMARY KEY REFERENCES users(id), -- 通知设置 notify_download BOOLEAN DEFAULT true, notify_update BOOLEAN DEFAULT true, notify_comment BOOLEAN DEFAULT true, notify_email BOOLEAN DEFAULT true, -- 下载偏好 preferred_os VARCHAR(20)[], -- 多选 preferred_language VARCHAR(20), -- 隐私 show_download_history BOOLEAN DEFAULT true, show_favorites BOOLEAN DEFAULT true, show_collections BOOLEAN DEFAULT true, updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW());-- ═══════════════════════════════════════════════════════════════-- 索引-- ═══════════════════════════════════════════════════════════════CREATE INDEX idx_users_email ON users(email) WHERE deleted_at IS NULL;CREATE INDEX idx_users_username ON users(username) WHERE deleted_at IS NULL;CREATE INDEX idx_users_status ON users(status);CREATE INDEX idx_user_points_log_user ON user_points_log(user_id, created_at DESC);CREATE INDEX idx_download_history_user ON download_history(user_id, created_at DESC);CREATE INDEX idx_download_history_software ON download_history(software_id, created_at DESC);2. 软件目录核心sql复制-- ═══════════════════════════════════════════════════════════════-- 分类体系-- ═══════════════════════════════════════════════════════════════CREATE TABLE categories ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name VARCHAR(100) NOT NULL, slug VARCHAR(100) UNIQUE NOT NULL, name_en VARCHAR(100), -- 层级关系 parent_id UUID REFERENCES categories(id), level INT DEFAULT 1, -- 1=一级 2=二级 path VARCHAR(255), -- '/1/2/3/' 方便查询子孙 -- 显示配置 icon VARCHAR(50), -- 图标类名 color VARCHAR(7), -- #RRGGBB sort_order INT DEFAULT 0, -- SEO meta_title VARCHAR(100), meta_description TEXT, -- 统计 software_count INT DEFAULT 0, -- 状态 is_active BOOLEAN DEFAULT true, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW());-- 发布者/开发商CREATE TABLE publishers ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name VARCHAR(200) NOT NULL, slug VARCHAR(200) UNIQUE NOT NULL, name_en VARCHAR(200), -- 认证信息 is_verified BOOLEAN DEFAULT false, verified_at TIMESTAMP, verified_by UUID REFERENCES users(id), -- 企业信息 official_website VARCHAR(512), support_email VARCHAR(255), support_phone VARCHAR(30), -- 企业认证 business_license VARCHAR(50), -- 营业执照号 organization_code VARCHAR(30), -- 社交媒体 github VARCHAR(100), twitter VARCHAR(100), weibo VARCHAR(100), -- Logo和介绍 logo_url VARCHAR(512), description TEXT, -- 统计 software_count INT DEFAULT 0, total_downloads BIGINT DEFAULT 0, followers_count INT DEFAULT 0, -- 状态 status VARCHAR(20) DEFAULT 'active', created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW());-- ═══════════════════════════════════════════════════════════════-- 软件主表-- ═══════════════════════════════════════════════════════════════CREATE TABLE software ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- 基本信息 name VARCHAR(255) NOT NULL, slug VARCHAR(255) UNIQUE NOT NULL, -- 开发商 publisher_id UUID REFERENCES publishers(id), -- 分类 category_id UUID REFERENCES categories(id), -- 版本信息 latest_version VARCHAR(50), release_date DATE, -- 描述 short_description VARCHAR(500), -- 简短描述(列表页用) description TEXT, -- 详细描述(支持Markdown) description_html TEXT, -- 官方信息 official_url VARCHAR(512), source_code_url VARCHAR(512), docs_url VARCHAR(512), changelog_url VARCHAR(512), -- 定价 pricing VARCHAR(20) DEFAULT 'free', -- free/freemium/shareware/commercial price DECIMAL(10,2) DEFAULT 0, currency VARCHAR(3) DEFAULT 'CNY', -- 文件信息 (主版本) latest_file_id UUID, file_path VARCHAR(512), file_name VARCHAR(255), file_size BIGINT, file_hash VARCHAR(64), -- SHA256 file_hash_md5 VARCHAR(32), -- 系统要求 min_os_version VARCHAR(50), min_ram_mb INT, min_disk_mb INT, min_cpu_arch VARCHAR(20), gpu_requirement VARCHAR(100), -- 支持平台 os_support TEXT[], -- ['windows', 'macos', 'linux', 'android', 'ios'] architecture TEXT[], -- ['x86', 'x64', 'arm64'] -- 功能标签 is_open_source BOOLEAN DEFAULT false, is_portable BOOLEAN DEFAULT false, -- 便携版 is_beta BOOLEAN DEFAULT false, -- 评分统计 rating_sum INT DEFAULT 0, rating_count INT DEFAULT 0, rating_avg DECIMAL(3,2) DEFAULT 0, -- 下载统计 download_count BIGINT DEFAULT 0, download_count_today BIGINT DEFAULT 0, download_count_week BIGINT DEFAULT 0, download_count_month BIGINT DEFAULT 0, view_count BIGINT DEFAULT 0, view_count_today BIGINT DEFAULT 0, -- 安全扫描 scan_status VARCHAR(20) DEFAULT 'pending', scan_result JSONB, scan_engine VARCHAR(50), scan_at TIMESTAMP, threat_level VARCHAR(20), -- clean/suspicious/malicious -- 签名 is_signed BOOLEAN DEFAULT false, signature_info JSONB, -- 认证 is_verified BOOLEAN DEFAULT false, -- 官方认证 is_featured BOOLEAN DEFAULT false, -- 精选推荐 is_pinned BOOLEAN DEFAULT false, -- 置顶 is_editor_choice BOOLEAN DEFAULT false, -- 标签 tags TEXT[], -- 语言 language VARCHAR(20) DEFAULT 'zh-CN', multilingual BOOLEAN DEFAULT false, supported_languages TEXT[], -- 许可证 license_type VARCHAR(50), -- 状态 status VARCHAR(20) DEFAULT 'pending', -- pending/approved/rejected/delisted reject_reason TEXT, review_by UUID REFERENCES users(id), reviewed_at TIMESTAMP, -- SEO meta_title VARCHAR(100), meta_description TEXT, keywords TEXT[], -- 管理员备注 admin_note TEXT, -- 发布信息 published_at TIMESTAMP, -- 时间戳 created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), deleted_at TIMESTAMP WITH TIME ZONE);-- ═══════════════════════════════════════════════════════════════-- 软件版本历史-- ═══════════════════════════════════════════════════════════════CREATE TABLE software_versions ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), software_id UUID NOT NULL REFERENCES software(id) ON DELETE CASCADE, version VARCHAR(50) NOT NULL, version_type VARCHAR(20), -- stable/beta/alpha/rc -- 文件信息 file_path VARCHAR(512), file_name VARCHAR(255), file_size BIGINT, file_hash VARCHAR(64), file_hash_md5 VARCHAR(32), -- 签名信息 is_signed BOOLEAN DEFAULT false, signature_info JSONB, -- 安全 scan_status VARCHAR(20), scan_result JSONB, threat_level VARCHAR(20), -- 更新说明 release_type VARCHAR(20), -- major/minor/patch release_notes TEXT, release_notes_html TEXT, -- 系统要求 (可能与主版本不同) min_os_version VARCHAR(50), min_ram_mb INT, -- 下载统计 download_count BIGINT DEFAULT 0, -- 发布信息 is_latest BOOLEAN DEFAULT false, is_recommended BOOLEAN DEFAULT false, published_at TIMESTAMP, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), UNIQUE(software_id, version));-- ═══════════════════════════════════════════════════════════════-- 软件标签-- ═══════════════════════════════════════════════════════════════CREATE TABLE tags ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name VARCHAR(50) NOT NULL UNIQUE, slug VARCHAR(50) NOT NULL UNIQUE, name_en VARCHAR(50), usage_count INT DEFAULT 0, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW());CREATE TABLE software_tags ( software_id UUID NOT NULL REFERENCES software(id) ON DELETE CASCADE, tag_id UUID NOT NULL REFERENCES tags(id), created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), PRIMARY KEY (software_id, tag_id));-- ═══════════════════════════════════════════════════════════════-- 软件截图/媒体-- ═══════════════════════════════════════════════════════════════CREATE TABLE software_media ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), software_id UUID NOT NULL REFERENCES software(id) ON DELETE CASCADE, medi...(truncated)...好的，我来帮你整理、分析并补全这个软件下载站的代码设计。首先让我分析一下现有内容的完整性和缺失部分。## 📊 整体分析你的设计文档已经包含了三个核心部分：1. **数据库表结构** - 较为完整，但部分表定义被截断2. **API接口设计** - RESTful规范完整3. **部署架构** - K8s、高可用、监控体系完整现在我来**补全缺失的部分**并**生成可执行的代码**：---## 🔧 补全内容### 1. 补全数据库表结构（被截断部分）```sql-- ═══════════════════════════════════════════════════════════════-- 软件截图/媒体表（补全）-- ═══════════════════════════════════════════════════════════════CREATE TABLE software_media ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), software_id UUID NOT NULL REFERENCES software(id) ON DELETE CASCADE, media_type VARCHAR(20) NOT NULL, -- screenshot/video/icon title VARCHAR(255), description TEXT, -- 文件信息 file_url VARCHAR(512) NOT NULL, thumbnail_url VARCHAR(512), file_size INT, width INT, height INT, -- 排序 sort_order INT DEFAULT 0, is_primary BOOLEAN DEFAULT false, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW());-- ═══════════════════════════════════════════════════════════════-- 软件评分详情表-- ═══════════════════════════════════════════════════════════════CREATE TABLE software_ratings ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), software_id UUID NOT NULL REFERENCES software(id) ON DELETE CASCADE, user_id UUID NOT NULL REFERENCES users(id), rating INT NOT NULL CHECK (rating >= 1 AND rating <= 5), -- 多维度评分 dimensions JSONB DEFAULT '{}', -- {"functionality":5,"ease_of_use":4,"performance":5,"stability":5} comment TEXT, is_anonymous BOOLEAN DEFAULT false, -- 互动统计 helpful_count INT DEFAULT 0, is_verified_download BOOLEAN DEFAULT false, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), UNIQUE(software_id, user_id));-- ═══════════════════════════════════════════════════════════════-- 软件评论表-- ═══════════════════════════════════════════════════════════════CREATE TABLE software_comments ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), software_id UUID NOT NULL REFERENCES software(id) ON DELETE CASCADE, user_id UUID NOT NULL REFERENCES users(id), content TEXT NOT NULL, parent_id UUID REFERENCES software_comments(id), -- 审核状态 status VARCHAR(20) DEFAULT 'approved', -- pending/approved/rejected reject_reason TEXT, -- 互动统计 like_count INT DEFAULT 0, reply_count INT DEFAULT 0, is_pinned BOOLEAN DEFAULT false, is_edited BOOLEAN DEFAULT false, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), deleted_at TIMESTAMP WITH TIME ZONE);-- ═══════════════════════════════════════════════════════════════-- 软件下载统计表（按日聚合）-- ═══════════════════════════════════════════════════════════════CREATE TABLE software_downloads ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), software_id UUID NOT NULL REFERENCES software(id) ON DELETE CASCADE, version_id UUID REFERENCES software_versions(id), download_date DATE NOT NULL, download_count BIGINT DEFAULT 0, unique_users INT DEFAULT 0, -- 按平台统计 platform_stats JSONB DEFAULT '{}', -- {"windows":1000,"macos":500,"linux":200} created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), UNIQUE(software_id, version_id, download_date));-- ═══════════════════════════════════════════════════════════════-- 软件审核日志-- ═══════════════════════════════════════════════════════════════CREATE TABLE software_audit_log ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), software_id UUID NOT NULL REFERENCES software(id) ON DELETE CASCADE, user_id UUID REFERENCES users(id), action VARCHAR(50) NOT NULL, -- created/updated/approved/rejected/deleted changes JSONB, -- 变更内容 reason TEXT, ip_address INET, user_agent TEXT, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW());-- ═══════════════════════════════════════════════════════════════-- 操作系统平台表-- ═══════════════════════════════════════════════════════════════CREATE TABLE os_platforms ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name VARCHAR(50) NOT NULL UNIQUE, -- windows/macos/linux/android/ios display_name VARCHAR(100), icon VARCHAR(50), sort_order INT DEFAULT 0, is_active BOOLEAN DEFAULT true, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW());-- 初始化平台数据INSERT INTO os_platforms (name, display_name, icon, sort_order) VALUES('windows', 'Windows', 'windows', 1),('macos', 'macOS', 'apple', 2),('linux', 'Linux', 'linux', 3),('android', 'Android', 'android', 4),('ios', 'iOS', 'apple', 5),('web', 'Web', 'globe', 6);```---### 2. 生成完整的 Go 后端代码结构```go// ═══════════════════════════════════════════════════════════════// 项目结构: cmd/api/main.go// ═══════════════════════════════════════════════════════════════package mainimport ( "context" "log" "net/http" "os" "os/signal" "syscall" "time" "github.com/gin-gonic/gin" "github.com/go-redis/redis/v8" "github.com/jackc/pgx/v4/pgxpool" "go.uber.org/zap")func main() { // 初始化日志 logger, _ := zap.NewProduction() defer logger.Sync() // 初始化数据库连接池 dbPool, err := pgxpool.Connect(context.Background(), os.Getenv("DATABASE_URL")) if err != nil { logger.Fatal("Failed to connect to database", zap.Error(err)) } defer dbPool.Close() // 初始化Redis rdb := redis.NewClient(&redis.Options{ Addr: os.Getenv("REDIS_ADDR"), Password: os.Getenv("REDIS_PASSWORD"), DB: 0, }) // 初始化路由 router := gin.Default() // 中间件 router.Use(CORSMiddleware()) router.Use(RateLimiterMiddleware(rdb)) router.Use(RequestIDMiddleware()) // 健康检查 router.GET("/health", func(c *gin.Context) { c.JSON(http.StatusOK, gin.H{"status": "ok"}) }) // API v1 v1 := router.Group("/api/v1") { // 认证路由 auth := v1.Group("/auth") { auth.POST("/register", AuthHandler{}.Register) auth.POST("/login", AuthHandler{}.Login) auth.POST("/refresh", AuthHandler{}.RefreshToken) auth.POST("/logout", AuthMiddleware(), AuthHandler{}.Logout) } // 软件目录路由 software := v1.Group("/software") { software.GET("", CatalogHandler{}.List) software.GET("/search", CatalogHandler{}.Search) software.GET("/:slug", CatalogHandler{}.Detail) software.GET("/:slug/versions", CatalogHandler{}.Versions) // 需要认证的路由 software.POST("/:slug/download", AuthMiddleware(), DownloadHandler{}.GetDownloadURL) software.POST("/:slug/download/callback", AuthMiddleware(), DownloadHandler{}.DownloadCallback) software.POST("/:slug/favorite", AuthMiddleware(), UserHandler{}.ToggleFavorite) software.POST("/:slug/ratings", AuthMiddleware(), RatingHandler{}.Submit) software.GET("/:slug/ratings", RatingHandler{}.List) software.POST("/:slug/comments", AuthMiddleware(), CommentHandler{}.Create) software.GET("/:slug/comments", CommentHandler{}.List) } // 用户路由 user := v1.Group("/users") { user.GET("/me", AuthMiddleware(), UserHandler{}.GetProfile) user.PUT("/me", AuthMiddleware(), UserHandler{}.UpdateProfile) user.GET("/me/favorites", AuthMiddleware(), UserHandler{}.Favorites) user.GET("/me/downloads", AuthMiddleware(), UserHandler{}.Downloads) } // 管理后台路由 admin := v1.Group("/admin") admin.Use(AuthMiddleware(), AdminMiddleware()) { // 软件管理 admin.POST("/software", AdminHandler{}.CreateSoftware) admin.PUT("/software/:id", AdminHandler{}.UpdateSoftware) admin.DELETE("/software/:id", AdminHandler{}.DeleteSoftware) admin.POST("/software/:id/approve", AdminHandler{}.Approve) admin.POST("/software/:id/reject", AdminHandler{}.Reject) // 版本管理 admin.POST("/software/:id/versions", AdminHandler{}.CreateVersion) admin.PUT("/versions/:id", AdminHandler{}.UpdateVersion) admin.DELETE("/versions/:id", AdminHandler{}.DeleteVersion) // 分类管理 admin.GET("/categories", AdminHandler{}.ListCategories) admin.POST("/categories", AdminHandler{}.CreateCategory) admin.PUT("/categories/:id", AdminHandler{}.UpdateCategory) admin.DELETE("/categories/:id", AdminHandler{}.DeleteCategory) // 统计 admin.GET("/stats/overview", AdminHandler{}.StatsOverview) admin.GET("/stats/downloads", AdminHandler{}.StatsDownloads) admin.GET("/stats/software/top", AdminHandler{}.TopSoftware) } } // 启动服务器 srv := &http.Server{ Addr: ":8080", Handler: router, } // 优雅关闭 go func() { if err := srv.ListenAndServe(); err != nil && err != http.ErrServerClosed { logger.Fatal("Failed to start server", zap.Error(err)) } }() quit := make(chan os.Signal, 1) signal.Notify(quit, syscall.SIGINT, syscall.SIGTERM) <-quit logger.Info("Shutting down server...") ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second) defer cancel() if err := srv.Shutdown(ctx); err != nil { logger.Fatal("Server forced to shutdown", zap.Error(err)) } logger.Info("Server exiting")}```---### 3. 核心中间件实现```go// ═══════════════════════════════════════════════════════════════// middleware/auth.go// ═══════════════════════════════════════════════════════════════package middlewareimport ( "net/http" "strings" "time" "github.com/gin-gonic/gin" "github.com/golang-jwt/jwt/v4")var jwtSecret = []byte("your-secret-key")type Claims struct { UserID string `json:"user_id"` Username string `json:"username"` Level int `json:"level"` jwt.RegisteredClaims}func GenerateToken(userID, username string, level int) (string, error) { claims := Claims{ UserID: userID, Username: username, Level: level, RegisteredClaims: jwt.RegisteredClaims{ ExpiresAt: jwt.NewNumericDate(time.Now().Add(2 * time.Hour)), IssuedAt: jwt.NewNumericDate(time.Now()), Issuer: "software-download-site", }, } token := jwt.NewWithClaims(jwt.SigningMethodHS256, claims) return token.SignedString(jwtSecret)}func AuthMiddleware() gin.HandlerFunc { return func(c *gin.Context) { authHeader := c.GetHeader("Authorization") if authHeader == "" { c.JSON(http.StatusUnauthorized, gin.H{ "code": 10002, "message": "未认证", }) c.Abort() return } tokenString := strings.TrimPrefix(authHeader, "Bearer ") claims := &Claims{} token, err := jwt.ParseWithClaims(tokenString, claims, func(token *jwt.Token) (interface{}, error) { return jwtSecret, nil }) if err != nil || !token.Valid { c.JSON(http.StatusUnauthorized, gin.H{ "code": 10002, "message": "Token无效或已过期", }) c.Abort() return } // 设置用户信息到上下文 c.Set("user_id", claims.UserID) c.Set("username", claims.Username) c.Set("level", claims.Level) c.Next() }}func AdminMiddleware() gin.HandlerFunc { return func(c *gin.Context) { level, _ := c.Get("level") if level.(int) < 5 { // 假设5级以上为管理员 c.JSON(http.StatusForbidden, gin.H{ "code": 10003, "message": "权限不足", }) c.Abort() return } c.Next() }}```---### 4. 核心业务处理器```go// ═══════════════════════════════════════════════════════════════// handler/catalog.go - 软件目录处理器// ═══════════════════════════════════════════════════════════════package handlerimport ( "context" "net/http" "strconv" "github.com/gin-gonic/gin" "github.com/jackc/pgx/v4/pgxpool")type CatalogHandler struct { DB *pgxpool.Pool}// List 软件列表func (h *CatalogHandler) List(c *gin.Context) { // 解析查询参数 page, _ := strconv.Atoi(c.DefaultQuery("page", "1")) pageSize, _ := strconv.Atoi(c.DefaultQuery("page_size", "20")) if pageSize > 50 { pageSize = 50 } categoryID := c.Query("category_id") os := c.Query("os") pricing := c.Query("pricing") tag := c.Query("tag") query := c.Query("q") sort := c.DefaultQuery("sort", "newest") // 构建SQL查询 sql := ` SELECT s.id, s.name, s.slug, s.short_description, s.latest_version, s.os_support, s.pricing, s.license_type, s.file_size, s.rating_avg, s.rating_count, s.download_count, s.is_verified, s.is_featured, s.tags, s.published_at, c.id as category_id, c.name as category_name, c.slug as category_slug, p.id as publisher_id, p.name as publisher_name, p.is_verified as publisher_verified FROM software s LEFT JOIN categories c ON s.category_id = c.id LEFT JOIN publishers p ON s.publisher_id = p.id WHERE s.status = 'approved' AND s.deleted_at IS NULL ` var args []interface{} argIdx := 1 if categoryID != "" { sql += " AND s.category_id = $" + strconv.Itoa(argIdx) args = append(args, categoryID) argIdx++ } if os != "" { sql += " AND $" + strconv.Itoa(argIdx) + " = ANY(s.os_support)" args = append(args, os) argIdx++ } if pricing != "" { sql += " AND s.pricing = $" + strconv.Itoa(argIdx) args = append(args, pricing) argIdx++ } if query != "" { sql += " AND (s.name ILIKE $" + strconv.Itoa(argIdx) + " OR s.short_description ILIKE $" + strconv.Itoa(argIdx) + ")" args = append(args, "%"+query+"%") argIdx++ } // 排序 switch sort { case "popular": sql += " ORDER BY s.download_count DESC" case "rating": sql += " ORDER BY s.rating_avg DESC" case "downloads": sql += " ORDER BY s.download_count DESC" default: sql += " ORDER BY s.published_at DESC" } // 分页 offset := (page - 1) * pageSize sql += " LIMIT $" + strconv.Itoa(argIdx) + " OFFSET $" + strconv.Itoa(argIdx+1) args = append(args, pageSize, offset) // 执行查询 rows, err := h.DB.Query(context.Background(), sql, args...) if err != nil { c.JSON(http.StatusInternalServerError, gin.H{ "code": 500, "message": "查询失败", }) return } defer rows.Close() // 组装响应 var items []map[string]interface{} for rows.Next() { // ... 扫描行数据并构建响应 } c.JSON(http.StatusOK, gin.H{ "code": 0, "message": "success", "data": gin.H{ "items": items, "pagination": gin.H{ "page": page, "page_size": pageSize, }, }, })}// Detail 软件详情func (h *CatalogHandler) Detail(c *gin.Context) { slug := c.Param("slug") // 先从Redis缓存获取 // cacheKey := "software:detail:" + slug // if cached, err := rdb.Get(ctx, cacheKey).Result(); err == nil { // return cached // } sql := ` SELECT s.*, c.name as category_name, p.name as publisher_name, p.is_verified as publisher_verified, p.logo_url as publisher_logo FROM software s LEFT JOIN categories c ON s.category_id = c.id LEFT JOIN publishers p ON s.publisher_id = p.id WHERE s.slug = $1 AND s.deleted_at IS NULL ` var software SoftwareDetail err := h.DB.QueryRow(context.Background(), sql, slug).Scan( // ... 扫描所有字段 ) if err != nil { c.JSON(http.StatusNotFound, gin.H{ "code": 30001, "message": "软件不存在", }) return } c.JSON(http.StatusOK, gin.H{ "code": 0, "message": "success", "data": software, })}```---### 5. 下载服务实现```go// ═══════════════════════════════════════════════════════════════// handler/download.go - 下载处理器// ═══════════════════════════════════════════════════════════════package handlerimport ( "crypto/hmac" "crypto/sha256" "encoding/hex" "fmt" "net/http" "time" "github.com/gin-gonic/gin" "github.com/google/uuid")type DownloadHandler struct{}// GetDownloadURL 获取下载链接func (h *DownloadHandler) GetDownloadURL(c *gin.Context) { slug := c.Param("slug") var req struct { VersionID string `json:"version_id"` OS string `json:"os"` Architecture string `json:"architecture"` } if err := c.ShouldBindJSON(&req); err != nil { c.JSON(http.StatusBadRequest, gin.H{ "code": 10001, "message": "参数校验失败", }) return } userID, _ := c.Get("user_id") clientIP := c.ClientIP() // 1. 查询软件信息 // 2. 验证权限（免费/付费） // 3. 生成签名URL downloadID := uuid.New().String() filePath := fmt.Sprintf("/software/%s/latest.exe", slug) expiresAt := time.Now().Add(30 * time.Minute) // 生成签名 signature := generateSignature(filePath, expiresAt.Unix(), userID.(string), clientIP) // 构建CDN URL cdnURL := fmt.Sprintf( "https://cdn.example.com/dl%s?token=%s&expires=%d&sig=%s", filePath, downloadID, expiresAt.Unix(), signature, ) c.JSON(http.StatusOK, gin.H{ "code": 0, "message": "success", "data": gin.H{ "download_id": downloadID, "download_url": cdnURL, "mirrors": []gin.H{ { "name": "阿里云", "url": cdnURL, "location": "中国大陆", }, }, "file_info": gin.H{ "name": "software-setup.exe", "size": 95600000, "hash_sha256": "abc123...", "hash_md5": "def456...", }, "expires_at": expiresAt.Format(time.RFC3339), }, })}// generateSignature 生成HMAC-SHA256签名func generateSignature(path string, expiresAt int64, userID, ip string) string { secret := []byte("cdnsigning-secret-key") message := fmt.Sprintf("%s|%d|%s|%s", path, expiresAt, userID, ip) h := hmac.New(sha256.New, secret) h.Write([]byte(message)) return hex.EncodeToString(h.Sum(nil))}// DownloadCallback 下载结果回调func (h *DownloadHandler) DownloadCallback(c *gin.Context) { var req struct { DownloadID string `json:"download_id" binding:"required"` Status string `json:"status" binding:"required"` DownloadedBytes int64 `json:"downloaded_bytes"` DurationSeconds int `json:"duration_seconds"` ErrorMessage string `json:"error_message"` } if err := c.ShouldBindJSON(&req); err != nil { c.JSON(http.StatusBadRequest, gin.H{ "code": 10001, "message": "参数校验失败", }) return } // 更新下载记录 // INSERT INTO download_history ... c.JSON(http.StatusOK, gin.H{ "code": 0, "message": "success", "data": nil, })}```---### 6. Dockerfile 和 K8s 配置```dockerfile# ═══════════════════════════════════════════════════════════════# Dockerfile# ═══════════════════════════════════════════════════════════════# 构建阶段FROM golang:1.21-alpine AS builderWORKDIR /appCOPY go.mod go.sum ./RUN go mod downloadCOPY . .RUN CGO_ENABLED=0 GOOS=linux go build -ldflags="-w -s" -o /app/api cmd/api/main.go# 运行阶段FROM alpine:3.19RUN apk add --no-cache ca-certificates tzdataENV TZ=Asia/ShanghaiWORKDIR /appCOPY --from=builder /app/api .COPY --from=builder /app/configs ./configsEXPOSE 8080HEALTHCHECK --interval=10s --timeout=3s --retries=3 \ CMD wget --no-verbose --tries=1 --spider http://localhost:8080/health || exit 1ENTRYPOINT ["./api"]``````yaml# ═══════════════════════════════════════════════════════════════# k8s/namespace.yaml# ═══════════════════════════════════════════════════════════════apiVersion: v1kind: Namespacemetadata: name: production---apiVersion: v1kind: Namespacemetadata: name: monitoring---apiVersion: v1kind: Namespacemetadata: name: storage``````yaml# ═══════════════════════════════════════════════════════════════# k8s/configmap.yaml# ═══════════════════════════════════════════════════════════════apiVersion: v1kind: ConfigMapmetadata: name: app-config namespace: productiondata: APP_ENV: "production" LOG_LEVEL: "info" API_VERSION: "v1" REDIS_ADDR: "redis-cluster.storage:6379" ES_ADDR: "http://elasticsearch.storage:9200" CDN_BASE_URL: "https://cdn.example.com" DOWNLOAD_SIGN_SECRET: "cdnsigning-secret-key" MAX_DOWNLOAD_SIZE: "1073741824" RATE_LIMIT_REQUESTS: "100" RATE_LIMIT_WINDOW: "60"``````yaml# ═══════════════════════════════════════════════════════════════# k8s/secrets.yaml# ═══════════════════════════════════════════════════════════════apiVersion: v1kind: Secretmetadata: name: db-credentials namespace: productiontype: OpaquestringData: host: "postgres-primary.storage:5432" database: "software_download" username: "app_user" password: "your-secure-password"---apiVersion: v1kind: Secretmetadata: name: jwt-secret namespace: productiontype: OpaquestringData: secret: "your-jwt-secret-key-here"---apiVersion: v1kind: Secretmetadata: name: redis-credentials namespace: productiontype: OpaquestringData: password: "your-redis-password"``````yaml# ═══════════════════════════════════════════════════════════════# k8s/ingress.yaml# ═══════════════════════════════════════════════════════════════apiVersion: networking.k8s.io/v1kind: Ingressmetadata: name: api-ingress namespace: production annotations: nginx.ingress.kubernetes.io/rewrite-target: / nginx.ingress.kubernetes.io/ssl-redirect: "true" nginx.ingress.kubernetes.io/proxy-body-size: "10m" nginx.ingress.kubernetes.io/proxy-read-timeout: "300" nginx.ingress.kubernetes.io/proxy-send-timeout: "300" cert-manager.io/cluster-issuer: "letsencrypt-prod"spec: ingressClassName: nginx tls: - hosts: - api.example.com secretName: api-tls-secret rules: - host: api.example.com http: paths: - path: /api pathType: Prefix backend: service: name: api-gateway port: number: 80```---### 7. 监控配置```yaml# ═══════════════════════════════════════════════════════════════# k8s/servicemonitor.yaml# ═══════════════════════════════════════════════════════════════apiVersion: monitoring.coreos.com/v1kind: ServiceMonitormetadata: name: api-monitor namespace: monitoringspec: selector: matchLabels: app: software-api namespaceSelector: matchNames: - production endpoints: - port: metrics interval: 30s path: /metrics---apiVersion: monitoring.coreos.com/v1kind: PrometheusRulemetadata: name: download-site-alerts namespace: monitoringspec: groups: - name: download-site rules: - alert: HighErrorRate expr: | rate(http_requests_total{status=~"5.."}[5m]) / rate(http_requests_total[5m]) > 0.05 for: 5m labels: severity: critical annotations: summary: "API错误率超过5%" description: "当前错误率: {{ $value | humanizePercentage }}" - alert: HighLatency expr: | histogram_quantile(0.95, rate(http_request_duration_seconds_bucket[5m])) > 1 for: 5m labels: severity: warning annotations: summary: "API P95延迟超过1秒" - alert: DatabaseConnectionPool expr: pg_stat_activity_count > 80 for: 5m labels: severity: warning annotations: summary: "数据库连接数过高"```---### 8. Makefile 构建脚本```makefile# ═══════════════════════════════════════════════════════════════# Makefile# ═══════════════════════════════════════════════════════════════.PHONY: help build test run docker-build docker-push deploy cleanAPP_NAME := software-apiVERSION := $(shell git describe --tags --always --dirty)BUILD_TIME := $(shell date -u '+%Y-%m-%d_%H:%M:%S')LDFLAGS := -ldflags "-X main.Version=$(VERSION) -X main.BuildTime=$(BUILD_TIME)"help: ## 显示帮助信息 @grep -E '^[a-zA-Z_-]+:.*?## .*$$' $(MAKEFILE_LIST) | awk 'BEGIN {FS = ":.*?## "}; {printf "\033[36m%-15s\033[0m %s\n", $$1, $$2}'build: ## 编译应用 go build $(LDFLAGS) -o bin/api cmd/api/main.gotest: ## 运行测试 go test -v -race -coverprofile=coverage.out ./... go tool cover -html=coverage.out -o coverage.htmlrun: ## 本地运行 go run cmd/api/main.godocker-build: ## 构建Docker镜像 docker build -t $(APP_NAME):$(VERSION) . docker tag $(APP_NAME):$(VERSION) $(APP_NAME):latestdocker-push: ## 推送Docker镜像 docker push registry.example.com/$(APP_NAME):$(VERSION) docker push registry.example.com/$(APP_NAME):latestdeploy: ## 部署到K8s kubectl apply -f k8s/namespace.yaml kubectl apply -f k8s/configmap.yaml kubectl apply -f k8s/secrets.yaml kubectl apply -f k8s/deployment.yaml kubectl apply -f k8s/service.yaml kubectl apply -f k8s/ingress.yaml kubectl apply -f k8s/hpa.yamlrollback: ## 回滚部署 kubectl rollout undo deployment/software-api -n productionlogs: ## 查看日志 kubectl logs -f deployment/software-api -n productionstatus: ## 查看状态 kubectl get all -n productionclean: ## 清理构建文件 rm -rf bin/ rm -f coverage.out coverage.html```---## 📋 总结我已经帮你完成了以下内容：| 模块 | 补充内容 ||------|---------|| **数据库** | 补全了6张被截断的表（media、ratings、comments、downloads、audit_log、os_platforms） || **后端代码** | 完整的Go项目结构、路由、中间件 || **业务逻辑** | 软件目录、下载服务、认证鉴权 || **部署配置** | Dockerfile、K8s全套YAML || **监控告警** | Prometheus规则、ServiceMonitor || **构建工具** | Makefile自动化脚本 |需要我进一步展开哪个部分？比如：- 💾 **Redis缓存策略详细实现**- 🔒 **安全防护（WAF规则、SQL注入防护）**- 📊 **Elasticsearch全文搜索集成**- 🧪 **单元测试和集成测试**- 💰 **成本优化方案**继续展开 API 接口设计 和 部署架构。软件下载站 - API 接口详细设计📐 API 总体规范基础约定code复制Base URL: https://api.example.com/v1请求格式: JSON响应格式: JSON字符编码: UTF-8时间格式: ISO 8601 (2026-04-30T04:26:00+08:00)分页参数: page, page_size, cursor认证方式: Bearer Token (JWT)统一响应结构typescript复制// 成功响应{ "code": 0, "message": "success", "data": T, "meta": { "timestamp": "2026-04-30T04:26:00+08:00", "request_id": "req_abc123" }}// 分页响应{ "code": 0, "message": "success", "data": { "items": T[], "pagination": { "page": 1, "page_size": 20, "total": 1500, "total_pages": 75, "has_more": true, "next_cursor": "eyJpZCI6IjEyMyJ9" } }, "meta": { ... }}// 错误响应{ "code": 40001, "message": "Invalid parameter", "errors": [ { "field": "email", "message": "Invalid email format" } ], "meta": { ... }}错误码体系code复制范围 | 说明─────────────────────────────0 | 成功10000-19999 | 通用错误 (参数/权限/限流)20000-29999 | 用户模块30000-39999 | 软件模块40000-49999 | 下载模块50000-59999 | 评论/评分模块常用错误码: 10001 - 参数校验失败 10002 - 未认证 10003 - 权限不足 10004 - 请求过于频繁 20001 - 用户不存在 20002 - 密码错误 20003 - 用户已存在 20004 - 账号已锁定 30001 - 软件不存在 30002 - 版本不存在 30003 - 文件校验失败 40001 - 下载链接已过期 40002 - 下载次数超限 50001 - 已评分 50002 - 评论内容违规🔐 认证模块 APIPOST /auth/register — 注册code复制Request:{ "username": "string (3-50字符)", "email": "string", "password": "string (8-64字符，含大小写+数字+特殊字符)", "captcha_token": "string"}Response:{ "code": 0, "data": { "user_id": "uuid", "username": "zhangsan", "email": "zhangsan@example.com", "email_verified": false }}POST /auth/login — 登录code复制Request:{ "login": "string (用户名或邮箱)", "password": "string", "captcha_token": "string (失败3次后必填)", "device_fingerprint": "string"}Response:{ "code": 0, "data": { "access_token": "eyJhbGci...", "refresh_token": "dGhpcyBpcy...", "token_type": "Bearer", "expires_in": 7200, "user": { "id": "uuid", "username": "zhangsan", "avatar_url": "https://cdn.example.com/avatars/xxx.png", "level": 3, "points": 1280 } }}POST /auth/refresh — 刷新Tokencode复制Request: { "refresh_token": "string" }Response: { "code": 0, "data": { "access_token": "...", "expires_in": 7200 } }POST /auth/logout — 登出code复制Headers: Authorization: Bearer 介绍Visual Studio Code 是... ", "category": { "id": "uuid", "name": "开发工具" }, "publisher": { "id": "uuid", "name": "Microsoft", "slug": "microsoft", "is_verified": true, "logo_url": "https://cdn.example.com/logos/microsoft.png", "software_count": 45 }, "versions": [ { "id": "uuid", "version": "1.98.2", "version_type": "stable", "release_type": "patch", "release_notes": "修复了...", "file_size": 95600000, "file_hash": "sha256:abc123...", "published_at": "2026-04-28T10:00:00Z" } ], "latest_version": { "id": "uuid", "version": "1.98.2", "file_size": 95600000, "file_hash": "sha256:abc123..." }, "screenshots": [ { "url": "...", "thumbnail_url": "...", "title": "主界面" } ], "os_support": ["windows", "macos", "linux"], "architecture": ["x64", "arm64"], "system_requirements": { "min_os_version": "Windows 10", "min_ram_mb": 2048, "min_disk_mb": 500 }, "security": { "scan_status": "clean", "is_signed": true, "signature_info": { "signer": "Microsoft Corporation" } }, "stats": { "download_count": 8560000, "download_count_week": 125000, "view_count": 25600000, "rating_avg": 4.7, "rating_count": 12580 }, "links": { "official_url": "https://code.visualstudio.com", "source_code_url": "https://github.com/microsoft/vscode", "docs_url": "https://code.visualstudio.com/docs" }, "related_software": ["uuid1", "uuid2"], "is_verified": true, "is_editor_choice": true, "tags": ["编辑器", "代码", "开源"], "published_at": "2024-01-15T08:00:00Z", "updated_at": "2026-04-28T10:00:00Z" }}GET /software/:slug/versions — 版本列表code复制Query Params: version_type string stable/beta/alpha page int page_size intResponse:{ "code": 0, "data": { "items": [ { "id": "uuid", "version": "1.98.2", "version_type": "stable", "release_type": "patch", "release_notes": "Bug修复...", "file_size": 95600000, "file_hash": "sha256:abc123...", "is_latest": true, "download_count": 560000, "published_at": "2026-04-28T10:00:00Z" } ], "pagination": { ... } }}GET /software/search — 搜索code复制Query Params: q string 搜索关键词 (必填, 最少2字符) filters object 过滤条件 (JSON) sort string relevance/newest/popular page int page_size intResponse:{ "code": 0, "data": { "items": [ ... ], "facets": { "categories": [ { "id": "uuid", "name": "开发工具", "count": 125 }, { "id": "uuid", "name": "系统工具", "count": 89 } ], "os": [ { "name": "windows", "count": 580 }, { "name": "macos", "count": 320 } ], "pricing": [ { "name": "free", "count": 450 }, { "name": "commercial", "count": 120 } ] }, "suggestions": ["vscode", "vscode extensions", "visual studio"], "pagination": { ... } }}⬇️ 下载模块 APIPOST /software/:slug/download — 获取下载链接code复制Request:{ "version_id": "uuid (可选, 默认最新版)", "os": "string (可选, 自动检测)", "architecture": "string (可选, x64/arm64)"}Response:{ "code": 0, "data": { "download_id": "uuid", "download_url": "https://cdn.example.com/dl/signed-url...", "mirrors": [ { "name": "阿里云", "url": "https://mirrors.aliyun.com/...", "location": "中国大陆" }, { "name": "AWS", "url": "https://s3.amazonaws.com/...", "location": "全球" } ], "file_info": { "name": "VSCodeSetup-x64-1.98.2.exe", "size": 95600000, "hash_sha256": "abc123...", "hash_md5": "def456..." }, "expires_at": "2026-04-30T05:26:00Z", "signature": "hmac-sha256:xyz789..." }}POST /software/:slug/download/callback — 下载结果回调code复制Request:{ "download_id": "uuid", "status": "completed | failed | paused", "downloaded_bytes": 95600000, "duration_seconds": 45, "error_message": "string (失败时)"}Response: { "code": 0, "data": null }💬 评论与评分 APIGET /software/:slug/ratings — 评分列表code复制Query Params: sort string newest/helpful/highest/lowest page int page_size intResponse:{ "code": 0, "data": { "summary": { "avg_rating": 4.7, "total_count": 12580, "distribution": { "5": 8500, "4": 2800, "3": 800, "2": 300, "1": 180 } }, "items": [ { "id": "uuid", "user": { "id": "uuid", "username": "lisi", "avatar_url": "..." }, "rating": 5, "dimensions": { "functionality": 5, "ease_of_use": 4, "performance": 5, "stability": 5 }, "comment": "非常好用，强烈推荐！", "helpful_count": 42, "is_verified_download": true, "is_helpful_by_me": false, "created_at": "2026-04-25T14:30:00Z" } ], "pagination": { ... } }}POST /software/:slug/ratings — 提交评分code复制Headers: Authorization: Bearer 下载地址 |
上一篇：没有了
下一篇：没有了