photography/docs/v1/database/数据库设计文档.md

摄影作品集网站 - 数据库设计文档

1. 数据库概述

1.1 设计目标

• 高性能: 支持大量图片数据的快速查询和检索
• 可扩展: 支持未来功能扩展和数据增长
• 完整性: 确保数据一致性和引用完整性
• 安全性: 支持用户权限管理和数据安全

1.2 技术选型

• 主数据库: PostgreSQL 15+
• 缓存数据库: Redis 7+
• 搜索引擎: PostgreSQL 全文搜索 (可选ElasticSearch)
• 文件存储: MinIO/AWS S3 + 本地存储

1.3 数据库命名规范

• 表名: 使用复数形式，小写字母，下划线分隔
• 字段名: 小写字母，下划线分隔，避免保留字
• 索引名: idx_表名_字段名 格式
• 外键名: fk_表名_字段名 格式

2. 数据库架构设计

2.1 数据库分层架构

┌─────────────────────────────────────────────────────────┐
│ 应用层 (Golang Backend)                                 │
├─────────────────────────────────────────────────────────┤
│ 缓存层 (Redis)                                          │
│ ├─ 会话缓存 (Session Storage)                          │
│ ├─ 数据缓存 (Data Cache)                               │
│ ├─ 队列系统 (Task Queue)                               │
│ └─ 计数器 (Counters)                                   │
├─────────────────────────────────────────────────────────┤
│ 数据层 (PostgreSQL)                                     │
│ ├─ 核心业务表 (Photos, Categories, Tags)               │
│ ├─ 用户管理表 (Users, Roles)                           │
│ ├─ 系统配置表 (Settings)                               │
│ └─ 日志审计表 (Logs)                                   │
├─────────────────────────────────────────────────────────┤
│ 存储层 (MinIO/S3)                                       │
│ ├─ 原始文件存储                                        │
│ ├─ 处理后文件存储                                      │
│ └─ 缓存文件存储                                        │
└─────────────────────────────────────────────────────────┘

2.2 数据库连接配置

# database.yml
development:
  host: localhost
  port: 5432
  database: photography_dev
  username: postgres
  password: password
  sslmode: disable
  max_open_conns: 25
  max_idle_conns: 5
  conn_max_lifetime: 3600

production:
  host: ${DB_HOST}
  port: ${DB_PORT}
  database: photography_prod
  username: ${DB_USER}
  password: ${DB_PASSWORD}
  sslmode: require
  max_open_conns: 100
  max_idle_conns: 10
  conn_max_lifetime: 3600

3. 核心数据表设计

3.1 照片主表 (photos)

3.1.1 表结构

CREATE TABLE photos (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    slug VARCHAR(255) UNIQUE,
    
    -- 文件信息
    original_filename VARCHAR(255),
    file_size BIGINT,
    mime_type VARCHAR(100),
    
    -- 状态管理
    status VARCHAR(20) DEFAULT 'published' CHECK (status IN ('published', 'draft', 'archived', 'processing')),
    visibility VARCHAR(20) DEFAULT 'public' CHECK (visibility IN ('public', 'private', 'password')),
    sort_order INTEGER DEFAULT 0,
    
    -- 时间信息
    taken_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- EXIF 元数据
    camera VARCHAR(100),
    lens VARCHAR(100),
    iso INTEGER,
    aperture VARCHAR(10),
    shutter_speed VARCHAR(20),
    focal_length VARCHAR(20),
    
    -- 位置信息
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    location_name VARCHAR(255),
    country VARCHAR(100),
    city VARCHAR(100),
    
    -- 统计信息
    view_count INTEGER DEFAULT 0,
    like_count INTEGER DEFAULT 0,
    download_count INTEGER DEFAULT 0,
    
    -- 全文搜索
    search_vector TSVECTOR,
    
    -- 元数据JSON (扩展信息)
    metadata JSONB,
    
    -- 索引
    CONSTRAINT photos_title_not_empty CHECK (LENGTH(title) > 0),
    CONSTRAINT photos_valid_coordinates CHECK (
        (latitude IS NULL AND longitude IS NULL) OR
        (latitude IS NOT NULL AND longitude IS NOT NULL AND 
         latitude >= -90 AND latitude <= 90 AND
         longitude >= -180 AND longitude <= 180)
    )
);

-- 索引优化
CREATE INDEX idx_photos_status ON photos(status);
CREATE INDEX idx_photos_visibility ON photos(visibility);
CREATE INDEX idx_photos_created_at ON photos(created_at);
CREATE INDEX idx_photos_taken_at ON photos(taken_at);
CREATE INDEX idx_photos_status_created_at ON photos(status, created_at);
CREATE INDEX idx_photos_location ON photos(latitude, longitude);
CREATE INDEX idx_photos_search_vector ON photos USING gin(search_vector);
CREATE INDEX idx_photos_metadata ON photos USING gin(metadata);

-- 更新时间触发器
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_photos_updated_at 
    BEFORE UPDATE ON photos 
    FOR EACH ROW 
    EXECUTE FUNCTION update_updated_at_column();

3.1.2 字段说明

字段名	类型	说明	示例
id	SERIAL	主键，自增	1
title	VARCHAR(255)	照片标题	"城市夜景"
description	TEXT	详细描述	"拍摄于上海外滩..."
slug	VARCHAR(255)	URL友好的唯一标识	"city-night-view-001"
original_filename	VARCHAR(255)	原始文件名	"DSC_0001.jpg"
file_size	BIGINT	文件大小(字节)	2048576
mime_type	VARCHAR(100)	MIME类型	"image/jpeg"
status	VARCHAR(20)	发布状态	"published"
visibility	VARCHAR(20)	可见性	"public"
taken_at	TIMESTAMP	拍摄时间	"2024-01-15 18:30:00"
camera	VARCHAR(100)	相机型号	"Canon EOS R5"
lens	VARCHAR(100)	镜头信息	"RF 24-70mm f/2.8L"
iso	INTEGER	ISO值	800
aperture	VARCHAR(10)	光圈值	"f/2.8"
shutter_speed	VARCHAR(20)	快门速度	"1/125"
focal_length	VARCHAR(20)	焦距	"50mm"
latitude	DECIMAL(10,8)	纬度	31.23037000
longitude	DECIMAL(11,8)	经度	121.47370000
location_name	VARCHAR(255)	地点名称	"上海外滩"
search_vector	TSVECTOR	全文搜索向量	自动生成
metadata	JSONB	扩展元数据	{"weather": "sunny"}

3.2 照片格式表 (photo_formats)

3.2.1 表结构

CREATE TABLE photo_formats (
    id SERIAL PRIMARY KEY,
    photo_id INTEGER NOT NULL REFERENCES photos(id) ON DELETE CASCADE,
    format_type VARCHAR(20) NOT NULL CHECK (format_type IN (
        'original', 'raw', 'jpg', 'webp', 'avif',
        'thumb_small', 'thumb_medium', 'thumb_large',
        'display', 'watermark'
    )),
    
    -- 文件信息
    file_path VARCHAR(500) NOT NULL,
    file_size BIGINT,
    file_hash VARCHAR(64), -- SHA256哈希
    
    -- 图片属性
    width INTEGER,
    height INTEGER,
    quality INTEGER,
    
    -- 处理信息
    processing_status VARCHAR(20) DEFAULT 'completed' CHECK (processing_status IN (
        'pending', 'processing', 'completed', 'failed'
    )),
    processing_error TEXT,
    
    -- 存储信息
    storage_type VARCHAR(20) DEFAULT 'local' CHECK (storage_type IN ('local', 's3', 'minio')),
    storage_bucket VARCHAR(100),
    storage_path VARCHAR(500),
    
    -- 时间信息
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 唯一约束
    UNIQUE(photo_id, format_type),
    
    -- 检查约束
    CONSTRAINT photo_formats_positive_dimensions CHECK (
        width > 0 AND height > 0
    ),
    CONSTRAINT photo_formats_valid_quality CHECK (
        quality IS NULL OR (quality >= 1 AND quality <= 100)
    )
);

-- 索引优化
CREATE INDEX idx_photo_formats_photo_id ON photo_formats(photo_id);
CREATE INDEX idx_photo_formats_format_type ON photo_formats(format_type);
CREATE INDEX idx_photo_formats_processing_status ON photo_formats(processing_status);
CREATE INDEX idx_photo_formats_storage_type ON photo_formats(storage_type);
CREATE INDEX idx_photo_formats_hash ON photo_formats(file_hash);

3.2.2 格式类型说明

格式类型	说明	尺寸	用途
original	原始上传文件	原始尺寸	备份和再处理
raw	RAW格式文件	原始尺寸	专业编辑
jpg	高质量JPEG	最大1920px	主要展示
webp	WebP格式	最大1920px	现代浏览器优化
avif	AVIF格式	最大1920px	次世代格式
thumb_small	小缩略图	150x150	列表预览
thumb_medium	中缩略图	300x300	网格展示
thumb_large	大缩略图	600x600	详情预览
display	展示版本	1200px	灯箱展示
watermark	水印版本	1920px	带水印展示

3.3 分类表 (categories)

3.3.1 表结构

CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    
    -- 层级结构
    parent_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
    level INTEGER DEFAULT 0,
    path VARCHAR(500), -- 存储完整路径，如 "1.2.3"
    
    -- 展示信息
    cover_photo_id INTEGER REFERENCES photos(id) ON DELETE SET NULL,
    color VARCHAR(7), -- 十六进制颜色代码
    icon VARCHAR(50), -- 图标类名
    
    -- 排序和状态
    sort_order INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    is_featured BOOLEAN DEFAULT FALSE,
    
    -- 统计信息
    photo_count INTEGER DEFAULT 0,
    direct_photo_count INTEGER DEFAULT 0, -- 直接关联的照片数
    
    -- SEO信息
    seo_title VARCHAR(255),
    seo_description TEXT,
    seo_keywords VARCHAR(500),
    
    -- 时间信息
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 检查约束
    CONSTRAINT categories_name_not_empty CHECK (LENGTH(name) > 0),
    CONSTRAINT categories_valid_color CHECK (
        color IS NULL OR color ~ '^#[0-9A-Fa-f]{6}$'
    ),
    CONSTRAINT categories_no_self_parent CHECK (id != parent_id)
);

-- 索引优化
CREATE INDEX idx_categories_parent_id ON categories(parent_id);
CREATE INDEX idx_categories_slug ON categories(slug);
CREATE INDEX idx_categories_sort_order ON categories(sort_order);
CREATE INDEX idx_categories_is_active ON categories(is_active);
CREATE INDEX idx_categories_is_featured ON categories(is_featured);
CREATE INDEX idx_categories_level ON categories(level);
CREATE INDEX idx_categories_path ON categories(path);

3.3.2 层级结构管理

-- 创建层级结构维护函数
CREATE OR REPLACE FUNCTION update_category_path()
RETURNS TRIGGER AS $$
DECLARE
    parent_path VARCHAR(500);
BEGIN
    IF NEW.parent_id IS NULL THEN
        NEW.level = 0;
        NEW.path = NEW.id::VARCHAR;
    ELSE
        SELECT level + 1, path || '.' || NEW.id::VARCHAR
        INTO NEW.level, NEW.path
        FROM categories
        WHERE id = NEW.parent_id;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_category_path_trigger
    BEFORE INSERT OR UPDATE ON categories
    FOR EACH ROW
    EXECUTE FUNCTION update_category_path();

3.4 标签表 (tags)

3.4.1 表结构

CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    slug VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    
    -- 展示信息
    color VARCHAR(7), -- 十六进制颜色代码
    icon VARCHAR(50), -- 图标类名
    
    -- 分类信息
    tag_group VARCHAR(50), -- 标签分组，如 "技术", "风格", "地点"
    
    -- 统计信息
    usage_count INTEGER DEFAULT 0,
    trend_score DECIMAL(5,2) DEFAULT 0, -- 趋势评分
    
    -- 状态信息
    is_active BOOLEAN DEFAULT TRUE,
    is_featured BOOLEAN DEFAULT FALSE,
    
    -- SEO信息
    seo_title VARCHAR(255),
    seo_description TEXT,
    
    -- 时间信息
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_used_at TIMESTAMP,
    
    -- 检查约束
    CONSTRAINT tags_name_not_empty CHECK (LENGTH(name) > 0),
    CONSTRAINT tags_valid_color CHECK (
        color IS NULL OR color ~ '^#[0-9A-Fa-f]{6}$'
    ),
    CONSTRAINT tags_usage_count_positive CHECK (usage_count >= 0)
);

-- 索引优化
CREATE INDEX idx_tags_name ON tags(name);
CREATE INDEX idx_tags_slug ON tags(slug);
CREATE INDEX idx_tags_usage_count ON tags(usage_count DESC);
CREATE INDEX idx_tags_tag_group ON tags(tag_group);
CREATE INDEX idx_tags_is_active ON tags(is_active);
CREATE INDEX idx_tags_is_featured ON tags(is_featured);
CREATE INDEX idx_tags_trend_score ON tags(trend_score DESC);

3.4.2 标签分组说明

分组名	说明	示例标签
style	摄影风格	黑白、彩色、复古、现代
subject	拍摄主题	人像、风景、建筑、街拍
technique	拍摄技法	长曝光、微距、HDR、全景
location	地理位置	北京、上海、户外、室内
time	时间相关	日出、日落、夜景、春天
mood	情绪氛围	宁静、热闹、忧郁、欢快
equipment	器材相关	广角、长焦、无人机、手机

3.5 关联表设计

3.5.1 照片分类关联表 (photo_categories)

CREATE TABLE photo_categories (
    photo_id INTEGER NOT NULL REFERENCES photos(id) ON DELETE CASCADE,
    category_id INTEGER NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
    
    -- 关联信息
    sort_order INTEGER DEFAULT 0,
    is_primary BOOLEAN DEFAULT FALSE, -- 是否为主分类
    
    -- 时间信息
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 主键
    PRIMARY KEY (photo_id, category_id),
    
    -- 检查约束
    CONSTRAINT photo_categories_unique_primary_per_photo 
        EXCLUDE (photo_id WITH =) WHERE (is_primary = TRUE)
);

-- 索引优化
CREATE INDEX idx_photo_categories_photo_id ON photo_categories(photo_id);
CREATE INDEX idx_photo_categories_category_id ON photo_categories(category_id);
CREATE INDEX idx_photo_categories_is_primary ON photo_categories(is_primary);

3.5.2 照片标签关联表 (photo_tags)

CREATE TABLE photo_tags (
    photo_id INTEGER NOT NULL REFERENCES photos(id) ON DELETE CASCADE,
    tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    
    -- 关联信息
    confidence DECIMAL(3,2) DEFAULT 1.0, -- 标签置信度 (0.0-1.0)
    source VARCHAR(20) DEFAULT 'manual' CHECK (source IN ('manual', 'auto', 'ai')),
    
    -- 时间信息
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 主键
    PRIMARY KEY (photo_id, tag_id),
    
    -- 检查约束
    CONSTRAINT photo_tags_valid_confidence CHECK (
        confidence >= 0.0 AND confidence <= 1.0
    )
);

-- 索引优化
CREATE INDEX idx_photo_tags_photo_id ON photo_tags(photo_id);
CREATE INDEX idx_photo_tags_tag_id ON photo_tags(tag_id);
CREATE INDEX idx_photo_tags_confidence ON photo_tags(confidence DESC);
CREATE INDEX idx_photo_tags_source ON photo_tags(source);

4. 用户管理表设计

4.1 用户表 (users)

4.1.1 表结构

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    
    -- 个人信息
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    display_name VARCHAR(100),
    bio TEXT,
    avatar_url VARCHAR(500),
    
    -- 角色权限
    role VARCHAR(20) DEFAULT 'user' CHECK (role IN ('super_admin', 'admin', 'editor', 'user')),
    permissions JSONB DEFAULT '[]',
    
    -- 状态信息
    is_active BOOLEAN DEFAULT TRUE,
    is_verified BOOLEAN DEFAULT FALSE,
    is_2fa_enabled BOOLEAN DEFAULT FALSE,
    
    -- 登录信息
    last_login_at TIMESTAMP,
    last_login_ip INET,
    login_count INTEGER DEFAULT 0,
    failed_login_attempts INTEGER DEFAULT 0,
    locked_until TIMESTAMP,
    
    -- 设置信息
    timezone VARCHAR(50) DEFAULT 'UTC',
    language VARCHAR(10) DEFAULT 'en',
    theme VARCHAR(20) DEFAULT 'auto',
    
    -- 时间信息
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 检查约束
    CONSTRAINT users_username_not_empty CHECK (LENGTH(username) > 0),
    CONSTRAINT users_email_format CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    CONSTRAINT users_failed_attempts_positive CHECK (failed_login_attempts >= 0),
    CONSTRAINT users_login_count_positive CHECK (login_count >= 0)
);

-- 索引优化
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_role ON users(role);
CREATE INDEX idx_users_is_active ON users(is_active);
CREATE INDEX idx_users_last_login_at ON users(last_login_at);
CREATE INDEX idx_users_permissions ON users USING gin(permissions);

4.1.2 角色权限说明

角色	权限描述	功能范围
super_admin	超级管理员	所有功能 + 系统管理
admin	管理员	内容管理 + 用户管理
editor	编辑者	内容编辑 + 发布
user	普通用户	基本浏览权限

4.2 会话表 (sessions)

4.2.1 表结构

CREATE TABLE sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    
    -- 会话信息
    token_hash VARCHAR(255) NOT NULL,
    refresh_token_hash VARCHAR(255),
    
    -- 设备信息
    device_type VARCHAR(20), -- 'desktop', 'mobile', 'tablet'
    device_name VARCHAR(100),
    browser VARCHAR(50),
    os VARCHAR(50),
    
    -- 网络信息
    ip_address INET,
    user_agent TEXT,
    location VARCHAR(100),
    
    -- 状态信息
    is_active BOOLEAN DEFAULT TRUE,
    
    -- 时间信息
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_used_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NOT NULL,
    
    -- 检查约束
    CONSTRAINT sessions_expires_future CHECK (expires_at > created_at)
);

-- 索引优化
CREATE INDEX idx_sessions_user_id ON sessions(user_id);
CREATE INDEX idx_sessions_token_hash ON sessions(token_hash);
CREATE INDEX idx_sessions_is_active ON sessions(is_active);
CREATE INDEX idx_sessions_expires_at ON sessions(expires_at);
CREATE INDEX idx_sessions_last_used_at ON sessions(last_used_at);

5. 系统配置表设计

5.1 系统设置表 (settings)

5.1.1 表结构

CREATE TABLE settings (
    key VARCHAR(50) PRIMARY KEY,
    value TEXT,
    description VARCHAR(255),
    
    -- 类型信息
    type VARCHAR(20) DEFAULT 'string' CHECK (type IN ('string', 'number', 'boolean', 'json', 'array')),
    category VARCHAR(50) DEFAULT 'general',
    
    -- 验证信息
    validation_rules JSONB,
    default_value TEXT,
    
    -- 权限信息
    is_public BOOLEAN DEFAULT FALSE,
    required_role VARCHAR(20) DEFAULT 'admin',
    
    -- 时间信息
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 检查约束
    CONSTRAINT settings_key_not_empty CHECK (LENGTH(key) > 0)
);

-- 索引优化
CREATE INDEX idx_settings_category ON settings(category);
CREATE INDEX idx_settings_type ON settings(type);
CREATE INDEX idx_settings_is_public ON settings(is_public);

5.1.2 预设配置数据

-- 网站基本配置
INSERT INTO settings (key, value, description, type, category, is_public) VALUES
('site_title', '摄影作品集', '网站标题', 'string', 'general', TRUE),
('site_description', '专业摄影师作品展示平台', '网站描述', 'string', 'general', TRUE),
('site_keywords', '摄影,作品集,艺术,创作', '网站关键词', 'string', 'seo', TRUE),
('site_author', '摄影师姓名', '网站作者', 'string', 'general', TRUE),
('site_email', 'contact@example.com', '联系邮箱', 'string', 'general', TRUE),
('site_language', 'zh-CN', '默认语言', 'string', 'general', TRUE),
('site_timezone', 'Asia/Shanghai', '默认时区', 'string', 'general', FALSE);

-- 上传配置
INSERT INTO settings (key, value, description, type, category, validation_rules) VALUES
('upload_max_file_size', '52428800', '最大文件大小(字节)', 'number', 'upload', '{"min": 1048576, "max": 104857600}'),
('upload_allowed_types', '["image/jpeg", "image/png", "image/raw", "image/heic"]', '允许的文件类型', 'json', 'upload', NULL),
('upload_max_files_per_batch', '50', '批量上传最大文件数', 'number', 'upload', '{"min": 1, "max": 100}'),
('upload_auto_publish', 'false', '自动发布上传的照片', 'boolean', 'upload', NULL),
('upload_generate_thumbnails', 'true', '自动生成缩略图', 'boolean', 'upload', NULL);

-- 图片处理配置
INSERT INTO settings (key, value, description, type, category, validation_rules) VALUES
('image_quality_jpg', '85', 'JPEG质量', 'number', 'image', '{"min": 1, "max": 100}'),
('image_quality_webp', '80', 'WebP质量', 'number', 'image', '{"min": 1, "max": 100}'),
('image_max_width', '1920', '最大宽度', 'number', 'image', '{"min": 100, "max": 4096}'),
('image_max_height', '1080', '最大高度', 'number', 'image', '{"min": 100, "max": 4096}'),
('image_watermark_enabled', 'false', '启用水印', 'boolean', 'image', NULL),
('image_watermark_text', '© 摄影师姓名', '水印文字', 'string', 'image', NULL);

-- 显示配置
INSERT INTO settings (key, value, description, type, category, is_public) VALUES
('display_photos_per_page', '20', '每页显示照片数', 'number', 'display', TRUE),
('display_thumbnail_size', '300', '缩略图尺寸', 'number', 'display', TRUE),
('display_theme_primary_color', '#d4af37', '主题主色调', 'string', 'display', TRUE),
('display_theme_secondary_color', '#2d2d2d', '主题辅助色', 'string', 'display', TRUE),
('display_enable_dark_mode', 'true', '启用深色模式', 'boolean', 'display', TRUE),
('display_enable_animations', 'true', '启用动画效果', 'boolean', 'display', TRUE);

-- 缓存配置
INSERT INTO settings (key, value, description, type, category) VALUES
('cache_enabled', 'true', '启用缓存', 'boolean', 'cache'),
('cache_ttl_photos', '3600', '照片缓存时间(秒)', 'number', 'cache'),
('cache_ttl_categories', '7200', '分类缓存时间(秒)', 'number', 'cache'),
('cache_ttl_tags', '3600', '标签缓存时间(秒)', 'number', 'cache'),
('cache_ttl_settings', '86400', '设置缓存时间(秒)', 'number', 'cache');

5.2 操作日志表 (admin_logs)

5.2.1 表结构

CREATE TABLE admin_logs (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    
    -- 操作信息
    action VARCHAR(50) NOT NULL,
    resource_type VARCHAR(50),
    resource_id INTEGER,
    
    -- 详细信息
    details JSONB,
    old_values JSONB,
    new_values JSONB,
    
    -- 请求信息
    ip_address INET,
    user_agent TEXT,
    request_method VARCHAR(10),
    request_url VARCHAR(500),
    
    -- 结果信息
    status VARCHAR(20) DEFAULT 'success' CHECK (status IN ('success', 'error', 'warning')),
    error_message TEXT,
    
    -- 时间信息
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 检查约束
    CONSTRAINT admin_logs_action_not_empty CHECK (LENGTH(action) > 0)
);

-- 索引优化
CREATE INDEX idx_admin_logs_user_id ON admin_logs(user_id);
CREATE INDEX idx_admin_logs_action ON admin_logs(action);
CREATE INDEX idx_admin_logs_resource_type ON admin_logs(resource_type);
CREATE INDEX idx_admin_logs_resource_id ON admin_logs(resource_id);
CREATE INDEX idx_admin_logs_created_at ON admin_logs(created_at);
CREATE INDEX idx_admin_logs_status ON admin_logs(status);
CREATE INDEX idx_admin_logs_details ON admin_logs USING gin(details);

5.2.2 操作类型说明

操作类型	描述	示例
photo.create	创建照片	上传新照片
photo.update	更新照片	修改照片信息
photo.delete	删除照片	删除照片
photo.publish	发布照片	发布草稿照片
photo.archive	归档照片	归档照片
category.create	创建分类	新建分类
category.update	更新分类	修改分类信息
category.delete	删除分类	删除分类
tag.create	创建标签	新建标签
tag.update	更新标签	修改标签信息
tag.delete	删除标签	删除标签
user.login	用户登录	管理员登录
user.logout	用户登出	管理员登出
settings.update	更新设置	修改系统设置
system.backup	系统备份	数据库备份

6. 数据库函数和存储过程

6.1 照片统计函数

6.1.1 更新分类照片数量

CREATE OR REPLACE FUNCTION update_category_photo_count()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE categories 
        SET photo_count = photo_count + 1,
            direct_photo_count = direct_photo_count + 1
        WHERE id = NEW.category_id;
        
        -- 更新父级分类的photo_count
        UPDATE categories 
        SET photo_count = photo_count + 1
        WHERE id IN (
            SELECT DISTINCT unnest(string_to_array(path, '.'))::INTEGER
            FROM categories 
            WHERE id = NEW.category_id
              AND parent_id IS NOT NULL
        );
        
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE categories 
        SET photo_count = photo_count - 1,
            direct_photo_count = direct_photo_count - 1
        WHERE id = OLD.category_id;
        
        -- 更新父级分类的photo_count
        UPDATE categories 
        SET photo_count = photo_count - 1
        WHERE id IN (
            SELECT DISTINCT unnest(string_to_array(path, '.'))::INTEGER
            FROM categories 
            WHERE id = OLD.category_id
              AND parent_id IS NOT NULL
        );
        
        RETURN OLD;
    END IF;
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER update_category_photo_count_trigger
    AFTER INSERT OR DELETE ON photo_categories
    FOR EACH ROW
    EXECUTE FUNCTION update_category_photo_count();

6.1.2 更新标签使用次数

CREATE OR REPLACE FUNCTION update_tag_usage_count()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE tags 
        SET usage_count = usage_count + 1,
            last_used_at = CURRENT_TIMESTAMP
        WHERE id = NEW.tag_id;
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE tags 
        SET usage_count = usage_count - 1
        WHERE id = OLD.tag_id;
        RETURN OLD;
    END IF;
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER update_tag_usage_count_trigger
    AFTER INSERT OR DELETE ON photo_tags
    FOR EACH ROW
    EXECUTE FUNCTION update_tag_usage_count();

6.2 全文搜索函数

6.2.1 更新搜索向量

CREATE OR REPLACE FUNCTION update_photo_search_vector()
RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector := 
        setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') ||
        setweight(to_tsvector('english', COALESCE(NEW.description, '')), 'B') ||
        setweight(to_tsvector('english', COALESCE(NEW.location_name, '')), 'C') ||
        setweight(to_tsvector('english', COALESCE(NEW.camera, '')), 'D') ||
        setweight(to_tsvector('english', COALESCE(NEW.lens, '')), 'D');
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER update_photo_search_vector_trigger
    BEFORE INSERT OR UPDATE ON photos
    FOR EACH ROW
    EXECUTE FUNCTION update_photo_search_vector();

6.2.2 搜索函数

CREATE OR REPLACE FUNCTION search_photos(
    search_query TEXT,
    limit_count INTEGER DEFAULT 20,
    offset_count INTEGER DEFAULT 0
)
RETURNS TABLE(
    id INTEGER,
    title VARCHAR(255),
    description TEXT,
    rank REAL
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        p.id,
        p.title,
        p.description,
        ts_rank(p.search_vector, plainto_tsquery('english', search_query)) AS rank
    FROM photos p
    WHERE p.search_vector @@ plainto_tsquery('english', search_query)
      AND p.status = 'published'
      AND p.visibility = 'public'
    ORDER BY rank DESC
    LIMIT limit_count
    OFFSET offset_count;
END;
$$ LANGUAGE plpgsql;

6.3 数据清理函数

6.3.1 清理过期会话

CREATE OR REPLACE FUNCTION cleanup_expired_sessions()
RETURNS INTEGER AS $$
DECLARE
    deleted_count INTEGER;
BEGIN
    DELETE FROM sessions 
    WHERE expires_at < CURRENT_TIMESTAMP
       OR last_used_at < CURRENT_TIMESTAMP - INTERVAL '30 days';
    
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    
    RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;

-- 创建定时任务 (需要pg_cron扩展)
-- SELECT cron.schedule('cleanup-sessions', '0 2 * * *', 'SELECT cleanup_expired_sessions();');

6.3.2 清理孤立文件记录

CREATE OR REPLACE FUNCTION cleanup_orphaned_formats()
RETURNS INTEGER AS $$
DECLARE
    deleted_count INTEGER;
BEGIN
    DELETE FROM photo_formats 
    WHERE photo_id NOT IN (SELECT id FROM photos);
    
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    
    RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;

7. 数据库优化策略

7.1 分区表设计

7.1.1 按时间分区日志表

-- 创建分区表
CREATE TABLE admin_logs_partitioned (
    LIKE admin_logs INCLUDING ALL
) PARTITION BY RANGE (created_at);

-- 创建分区
CREATE TABLE admin_logs_2024_01 PARTITION OF admin_logs_partitioned
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE admin_logs_2024_02 PARTITION OF admin_logs_partitioned
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- 创建默认分区
CREATE TABLE admin_logs_default PARTITION OF admin_logs_partitioned
    DEFAULT;

-- 创建自动分区函数
CREATE OR REPLACE FUNCTION create_monthly_partition(table_name TEXT, start_date DATE)
RETURNS VOID AS $$
DECLARE
    partition_name TEXT;
    end_date DATE;
BEGIN
    partition_name := table_name || '_' || to_char(start_date, 'YYYY_MM');
    end_date := start_date + INTERVAL '1 month';
    
    EXECUTE format('CREATE TABLE %I PARTITION OF %I
                   FOR VALUES FROM (%L) TO (%L)',
                   partition_name, table_name, start_date, end_date);
END;
$$ LANGUAGE plpgsql;

7.2 索引优化

7.2.1 复合索引优化

-- 照片查询常用复合索引
CREATE INDEX idx_photos_status_visibility_created_at 
    ON photos(status, visibility, created_at DESC);

CREATE INDEX idx_photos_taken_at_status 
    ON photos(taken_at DESC, status) 
    WHERE status = 'published';

-- 部分索引 (只索引已发布的照片)
CREATE INDEX idx_photos_published_taken_at 
    ON photos(taken_at DESC) 
    WHERE status = 'published' AND visibility = 'public';

-- 表达式索引
CREATE INDEX idx_photos_title_lower 
    ON photos(LOWER(title));

-- JSON索引
CREATE INDEX idx_photos_metadata_gin 
    ON photos USING gin(metadata);

7.2.2 索引使用监控

-- 查看索引使用情况
CREATE VIEW index_usage_stats AS
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_tup_read,
    idx_tup_fetch,
    idx_scan,
    idx_tup_read::DECIMAL / NULLIF(idx_scan, 0) AS avg_tuples_per_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- 查看未使用的索引
CREATE VIEW unused_indexes AS
SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND NOT indisunique
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

7.3 查询优化

7.3.1 查询计划分析

-- 分析慢查询
CREATE VIEW slow_queries AS
SELECT 
    query,
    calls,
    total_time,
    mean_time,
    rows,
    100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
WHERE mean_time > 1000  -- 平均执行时间超过1秒
ORDER BY mean_time DESC;

-- 创建查询优化函数
CREATE OR REPLACE FUNCTION explain_query(query_text TEXT)
RETURNS TABLE(plan TEXT) AS $$
BEGIN
    RETURN QUERY
    EXECUTE 'EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ' || query_text;
END;
$$ LANGUAGE plpgsql;

7.4 缓存策略

7.4.1 Redis缓存键设计

# 缓存键命名规范
photo:detail:{photo_id}                 # 照片详情
photo:list:{status}:{page}:{limit}      # 照片列表
category:tree                           # 分类树
category:stats                          # 分类统计
tag:cloud                              # 标签云
tag:suggestions:{query}                # 标签建议
user:session:{user_id}                 # 用户会话
settings:all                           # 系统设置
timeline:{year}                        # 时间线数据

7.4.2 缓存更新策略

-- 创建缓存失效函数
CREATE OR REPLACE FUNCTION invalidate_cache_keys(key_pattern TEXT)
RETURNS VOID AS $$
BEGIN
    -- 这里需要配合应用层实现
    -- 通过Redis发布/订阅机制通知应用层清除缓存
    PERFORM pg_notify('cache_invalidate', key_pattern);
END;
$$ LANGUAGE plpgsql;

-- 创建缓存失效触发器
CREATE OR REPLACE FUNCTION photos_cache_invalidate_trigger()
RETURNS TRIGGER AS $$
BEGIN
    -- 清除相关缓存
    PERFORM invalidate_cache_keys('photo:*');
    PERFORM invalidate_cache_keys('category:*');
    PERFORM invalidate_cache_keys('timeline:*');
    
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER photos_cache_invalidate
    AFTER INSERT OR UPDATE OR DELETE ON photos
    FOR EACH ROW
    EXECUTE FUNCTION photos_cache_invalidate_trigger();

8. 数据库备份与恢复

8.1 备份策略

8.1.1 自动备份脚本

#!/bin/bash
# backup_database.sh

# 配置
DB_NAME="photography"
DB_USER="postgres"
DB_HOST="localhost"
DB_PORT="5432"
BACKUP_DIR="/var/backups/postgresql"
RETENTION_DAYS=30

# 创建备份目录
mkdir -p $BACKUP_DIR

# 备份文件名
BACKUP_FILE="$BACKUP_DIR/photography_$(date +%Y%m%d_%H%M%S).sql.gz"

# 执行备份
pg_dump -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME | gzip > $BACKUP_FILE

# 检查备份是否成功
if [ $? -eq 0 ]; then
    echo "Backup successful: $BACKUP_FILE"
    
    # 删除过期备份
    find $BACKUP_DIR -name "photography_*.sql.gz" -mtime +$RETENTION_DAYS -delete
    
    # 记录备份日志
    echo "$(date): Backup completed successfully" >> /var/log/postgresql_backup.log
else
    echo "Backup failed!"
    echo "$(date): Backup failed" >> /var/log/postgresql_backup.log
    exit 1
fi

8.1.2 增量备份

#!/bin/bash
# incremental_backup.sh

# WAL归档备份
ARCHIVE_DIR="/var/backups/postgresql/wal"
mkdir -p $ARCHIVE_DIR

# 配置postgresql.conf
# wal_level = replica
# archive_mode = on
# archive_command = 'test ! -f /var/backups/postgresql/wal/%f && cp %p /var/backups/postgresql/wal/%f'

# 基础备份
pg_basebackup -D /var/backups/postgresql/base -Ft -z -P -U postgres

8.2 恢复策略

8.2.1 完整恢复

#!/bin/bash
# restore_database.sh

BACKUP_FILE=$1
DB_NAME="photography"
DB_USER="postgres"

if [ -z "$BACKUP_FILE" ]; then
    echo "Usage: $0 <backup_file>"
    exit 1
fi

# 停止应用服务
systemctl stop photography-backend

# 删除现有数据库
dropdb -U $DB_USER $DB_NAME

# 创建新数据库
createdb -U $DB_USER $DB_NAME

# 恢复数据
gunzip -c $BACKUP_FILE | psql -U $DB_USER -d $DB_NAME

# 启动应用服务
systemctl start photography-backend

echo "Database restored successfully"

8.2.2 时间点恢复

#!/bin/bash
# point_in_time_recovery.sh

RECOVERY_TIME=$1
BASE_BACKUP="/var/backups/postgresql/base"
WAL_ARCHIVE="/var/backups/postgresql/wal"

# 恢复到指定时间点
pg_ctl stop -D /var/lib/postgresql/data

# 恢复基础备份
rm -rf /var/lib/postgresql/data/*
tar -xzf $BASE_BACKUP/base.tar.gz -C /var/lib/postgresql/data/

# 创建recovery.conf
cat > /var/lib/postgresql/data/recovery.conf << EOF
restore_command = 'cp $WAL_ARCHIVE/%f %p'
recovery_target_time = '$RECOVERY_TIME'
recovery_target_action = 'promote'
EOF

# 启动数据库
pg_ctl start -D /var/lib/postgresql/data

9. 性能监控

9.1 性能指标视图

9.1.1 数据库性能监控

-- 数据库连接统计
CREATE VIEW database_connections AS
SELECT 
    datname,
    numbackends,
    xact_commit,
    xact_rollback,
    blks_read,
    blks_hit,
    tup_returned,
    tup_fetched,
    tup_inserted,
    tup_updated,
    tup_deleted
FROM pg_stat_database
WHERE datname = 'photography';

-- 表操作统计
CREATE VIEW table_stats AS
SELECT 
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch,
    n_tup_ins,
    n_tup_upd,
    n_tup_del,
    n_tup_hot_upd,
    n_live_tup,
    n_dead_tup,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
ORDER BY seq_scan + idx_scan DESC;

9.1.2 查询性能监控

-- 安装pg_stat_statements扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 慢查询分析
CREATE VIEW slow_queries_detailed AS
SELECT 
    query,
    calls,
    total_time,
    mean_time,
    max_time,
    min_time,
    stddev_time,
    rows,
    100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent,
    100.0 * (shared_blks_hit + shared_blks_read) / nullif(calls, 0) AS avg_blocks_per_call
FROM pg_stat_statements
WHERE mean_time > 100  -- 平均执行时间超过100ms
ORDER BY total_time DESC;

9.2 自动化监控

9.2.1 监控脚本

#!/bin/bash
# monitor_database.sh

# 配置
DB_NAME="photography"
DB_USER="postgres"
ALERT_EMAIL="admin@example.com"
LOG_FILE="/var/log/postgresql_monitor.log"

# 检查数据库连接
check_database_connection() {
    psql -U $DB_USER -d $DB_NAME -c "SELECT 1;" > /dev/null 2>&1
    if [ $? -ne 0 ]; then
        echo "$(date): Database connection failed" >> $LOG_FILE
        send_alert "Database connection failed"
        return 1
    fi
    return 0
}

# 检查慢查询
check_slow_queries() {
    SLOW_QUERY_COUNT=$(psql -U $DB_USER -d $DB_NAME -t -c "
        SELECT COUNT(*) FROM pg_stat_statements 
        WHERE mean_time > 1000;
    ")
    
    if [ $SLOW_QUERY_COUNT -gt 10 ]; then
        echo "$(date): Too many slow queries: $SLOW_QUERY_COUNT" >> $LOG_FILE
        send_alert "Too many slow queries detected: $SLOW_QUERY_COUNT"
    fi
}

# 检查数据库大小
check_database_size() {
    DB_SIZE=$(psql -U $DB_USER -d $DB_NAME -t -c "
        SELECT pg_size_pretty(pg_database_size('$DB_NAME'));
    ")
    
    echo "$(date): Database size: $DB_SIZE" >> $LOG_FILE
}

# 发送警报
send_alert() {
    MESSAGE=$1
    echo "$MESSAGE" | mail -s "Database Alert - Photography" $ALERT_EMAIL
}

# 执行检查
check_database_connection
check_slow_queries
check_database_size

echo "$(date): Database monitoring completed" >> $LOG_FILE

10. 数据库迁移

10.1 迁移脚本

10.1.1 版本1.0.0 - 初始化

-- migrations/001_initial_schema.sql
-- 创建基础表结构

-- 照片表
CREATE TABLE photos (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    slug VARCHAR(255) UNIQUE,
    original_filename VARCHAR(255),
    file_size BIGINT,
    mime_type VARCHAR(100),
    status VARCHAR(20) DEFAULT 'published',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 分类表
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 标签表
CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    slug VARCHAR(50) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 关联表
CREATE TABLE photo_categories (
    photo_id INTEGER REFERENCES photos(id) ON DELETE CASCADE,
    category_id INTEGER REFERENCES categories(id) ON DELETE CASCADE,
    PRIMARY KEY (photo_id, category_id)
);

CREATE TABLE photo_tags (
    photo_id INTEGER REFERENCES photos(id) ON DELETE CASCADE,
    tag_id INTEGER REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (photo_id, tag_id)
);

-- 基础索引
CREATE INDEX idx_photos_status ON photos(status);
CREATE INDEX idx_photos_created_at ON photos(created_at);

10.1.2 版本1.1.0 - 添加EXIF支持

-- migrations/002_add_exif_support.sql
-- 添加EXIF元数据字段

ALTER TABLE photos ADD COLUMN camera VARCHAR(100);
ALTER TABLE photos ADD COLUMN lens VARCHAR(100);
ALTER TABLE photos ADD COLUMN iso INTEGER;
ALTER TABLE photos ADD COLUMN aperture VARCHAR(10);
ALTER TABLE photos ADD COLUMN shutter_speed VARCHAR(20);
ALTER TABLE photos ADD COLUMN focal_length VARCHAR(20);
ALTER TABLE photos ADD COLUMN taken_at TIMESTAMP;

-- 创建照片格式表
CREATE TABLE photo_formats (
    id SERIAL PRIMARY KEY,
    photo_id INTEGER NOT NULL REFERENCES photos(id) ON DELETE CASCADE,
    format_type VARCHAR(20) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size BIGINT,
    width INTEGER,
    height INTEGER,
    quality INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(photo_id, format_type)
);

CREATE INDEX idx_photo_formats_photo_id ON photo_formats(photo_id);
CREATE INDEX idx_photo_formats_format_type ON photo_formats(format_type);

10.1.3 版本1.2.0 - 用户管理

-- migrations/003_add_user_management.sql
-- 添加用户管理功能

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(20) DEFAULT 'user',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    token_hash VARCHAR(255) NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_sessions_user_id ON sessions(user_id);
CREATE INDEX idx_sessions_token_hash ON sessions(token_hash);

10.2 迁移工具

10.2.1 迁移管理器

#!/bin/bash
# migrate.sh

DB_NAME="photography"
DB_USER="postgres"
MIGRATIONS_DIR="migrations"
MIGRATIONS_TABLE="schema_migrations"

# 创建迁移记录表
create_migrations_table() {
    psql -U $DB_USER -d $DB_NAME -c "
        CREATE TABLE IF NOT EXISTS $MIGRATIONS_TABLE (
            version VARCHAR(50) PRIMARY KEY,
            executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
    "
}

# 获取当前版本
get_current_version() {
    psql -U $DB_USER -d $DB_NAME -t -c "
        SELECT version FROM $MIGRATIONS_TABLE ORDER BY version DESC LIMIT 1;
    " | tr -d ' '
}

# 执行迁移
run_migration() {
    local migration_file=$1
    local version=$(basename $migration_file .sql)
    
    echo "Running migration: $version"
    
    # 开始事务
    psql -U $DB_USER -d $DB_NAME -c "BEGIN;"
    
    # 执行迁移
    psql -U $DB_USER -d $DB_NAME -f $migration_file
    
    if [ $? -eq 0 ]; then
        # 记录迁移
        psql -U $DB_USER -d $DB_NAME -c "
            INSERT INTO $MIGRATIONS_TABLE (version) VALUES ('$version');
        "
        
        # 提交事务
        psql -U $DB_USER -d $DB_NAME -c "COMMIT;"
        
        echo "Migration $version completed successfully"
    else
        # 回滚事务
        psql -U $DB_USER -d $DB_NAME -c "ROLLBACK;"
        echo "Migration $version failed"
        exit 1
    fi
}

# 主函数
main() {
    create_migrations_table
    
    current_version=$(get_current_version)
    echo "Current version: $current_version"
    
    # 执行待处理的迁移
    for migration_file in $MIGRATIONS_DIR/*.sql; do
        version=$(basename $migration_file .sql)
        
        # 检查是否已执行
        executed=$(psql -U $DB_USER -d $DB_NAME -t -c "
            SELECT COUNT(*) FROM $MIGRATIONS_TABLE WHERE version = '$version';
        " | tr -d ' ')
        
        if [ $executed -eq 0 ]; then
            run_migration $migration_file
        else
            echo "Migration $version already executed, skipping"
        fi
    done
    
    echo "All migrations completed"
}

# 运行
main

11. 数据库安全

11.1 权限管理

11.1.1 角色权限设计

-- 创建角色
CREATE ROLE photography_admin;
CREATE ROLE photography_editor;
CREATE ROLE photography_viewer;

-- 管理员权限 (完全权限)
GRANT ALL PRIVILEGES ON DATABASE photography TO photography_admin;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO photography_admin;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO photography_admin;

-- 编辑者权限 (读写权限，不能删除)
GRANT CONNECT ON DATABASE photography TO photography_editor;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO photography_editor;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO photography_editor;
REVOKE DELETE ON photos, categories, tags FROM photography_editor;

-- 查看者权限 (只读权限)
GRANT CONNECT ON DATABASE photography TO photography_viewer;
GRANT SELECT ON photos, categories, tags, photo_categories, photo_tags, photo_formats TO photography_viewer;

-- 创建用户并分配角色
CREATE USER admin_user WITH PASSWORD 'secure_password';
GRANT photography_admin TO admin_user;

CREATE USER editor_user WITH PASSWORD 'secure_password';
GRANT photography_editor TO editor_user;

11.2 数据加密

11.2.1 敏感数据加密

-- 创建加密扩展
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- 加密函数
CREATE OR REPLACE FUNCTION encrypt_sensitive_data(data TEXT, key TEXT)
RETURNS TEXT AS $$
BEGIN
    RETURN encode(encrypt(data::bytea, key::bytea, 'aes'), 'base64');
END;
$$ LANGUAGE plpgsql;

-- 解密函数
CREATE OR REPLACE FUNCTION decrypt_sensitive_data(encrypted_data TEXT, key TEXT)
RETURNS TEXT AS $$
BEGIN
    RETURN convert_from(decrypt(decode(encrypted_data, 'base64'), key::bytea, 'aes'), 'UTF8');
END;
$$ LANGUAGE plpgsql;

-- 使用示例
UPDATE users SET 
    email = encrypt_sensitive_data(email, 'encryption_key')
WHERE id = 1;

11.3 审计日志

11.3.1 详细审计配置

-- 创建审计触发器函数
CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO admin_logs (
            action,
            resource_type,
            resource_id,
            old_values,
            ip_address,
            user_agent
        ) VALUES (
            TG_TABLE_NAME || '.delete',
            TG_TABLE_NAME,
            OLD.id,
            row_to_json(OLD),
            inet_client_addr(),
            current_setting('application_name', true)
        );
        RETURN OLD;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO admin_logs (
            action,
            resource_type,
            resource_id,
            old_values,
            new_values,
            ip_address,
            user_agent
        ) VALUES (
            TG_TABLE_NAME || '.update',
            TG_TABLE_NAME,
            NEW.id,
            row_to_json(OLD),
            row_to_json(NEW),
            inet_client_addr(),
            current_setting('application_name', true)
        );
        RETURN NEW;
    ELSIF TG_OP = 'INSERT' THEN
        INSERT INTO admin_logs (
            action,
            resource_type,
            resource_id,
            new_values,
            ip_address,
            user_agent
        ) VALUES (
            TG_TABLE_NAME || '.insert',
            TG_TABLE_NAME,
            NEW.id,
            row_to_json(NEW),
            inet_client_addr(),
            current_setting('application_name', true)
        );
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 为关键表创建审计触发器
CREATE TRIGGER photos_audit_trigger
    AFTER INSERT OR UPDATE OR DELETE ON photos
    FOR EACH ROW
    EXECUTE FUNCTION audit_trigger_function();

CREATE TRIGGER categories_audit_trigger
    AFTER INSERT OR UPDATE OR DELETE ON categories
    FOR EACH ROW
    EXECUTE FUNCTION audit_trigger_function();

CREATE TRIGGER users_audit_trigger
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW
    EXECUTE FUNCTION audit_trigger_function();

12. 总结

这个数据库设计文档提供了摄影作品集网站的完整数据库架构，包括：

🎯 设计亮点

• 完整的数据模型: 支持照片、分类、标签、用户管理等所有功能
• 性能优化: 合理的索引设计、分区表、缓存策略
• 可扩展性: 支持层级分类、多格式文件、扩展元数据
• 安全性: 权限管理、数据加密、审计日志

🔧 技术特性

• PostgreSQL 15+: 利用最新特性提升性能
• JSONB支持: 灵活的元数据存储
• 全文搜索: 内置的搜索功能
• 触发器自动化: 数据一致性保证

📊 监控运维

• 性能监控: 慢查询分析、索引使用统计
• 自动化备份: 完整备份和增量备份
• 迁移管理: 版本化的数据库迁移

这个设计为Golang后端提供了强大的数据支撑，可以满足摄影作品集网站的所有需求。