32 KiB

Raw Permalink Blame History

Cosmo 数据库表结构设计文档

📋 文档目录

1. 数据库信息
2. 数据表索引
3. 核心业务表
4. 系统管理表
5. 缓存表
- 5.1 nasa_cache - NASA API缓存表
6. 数据关系图
7. 初始化脚本
8. 查询示例
9. 维护建议

1. 数据库信息

数据库类型: PostgreSQL 15+
数据库名称: cosmo_db
字符集: UTF8
时区: UTC
连接池: 20 (可配置)

2. 数据表索引

序号	表名	说明	记录数量级
1	celestial_bodies	天体基本信息	数百
2	positions	天体位置历史（时间序列）	百万级
3	orbits	轨道路径数据	数百
4	resources	资源文件管理	数千
5	static_data	静态天文数据	数千
6	star_systems	恒星系统信息	数千
7	interstellar_bodies	恒星际天体信息	数千
8	users	用户账号	数千
9	roles	角色定义	十位数
10	user_roles	用户角色关联	数千
11	menus	菜单配置	数十
12	role_menus	角色菜单权限	数百
13	system_settings	系统配置参数	数十
14	tasks	后台任务	数万
15	nasa_cache	NASA API缓存	数万

3. 核心业务表

3.1 celestial_bodies - 天体基本信息表

存储所有天体的基本信息和元数据。

CREATE TABLE celestial_bodies (
    id VARCHAR(50) PRIMARY KEY,        -- JPL Horizons ID 或自定义ID
    name VARCHAR(200) NOT NULL,        -- 英文名称
    name_zh VARCHAR(200),              -- 中文名称
    type VARCHAR(50) NOT NULL,         -- <20><><EFBFBD>体类型
    description TEXT,                  -- 描述
    details TEXT,                      -- 详细信息（Markdown格式）
    metadata JSONB,                    -- 扩展元数据
    is_active BOOLEAN DEFAULT TRUE,    -- 天体有效状态
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT chk_type CHECK (type IN (
        'star', 'planet', 'dwarf_planet', 'satellite',
        'probe', 'comet', 'asteroid'
    ))
);

-- 索引
CREATE INDEX idx_celestial_bodies_type ON celestial_bodies(type);
CREATE INDEX idx_celestial_bodies_name ON celestial_bodies(name);
CREATE INDEX idx_celestial_bodies_active ON celestial_bodies(is_active);

-- 注释
COMMENT ON TABLE celestial_bodies IS '天体基本信息表';
COMMENT ON COLUMN celestial_bodies.id IS 'JPL Horizons ID（如-31代表Voyager 1）或自定义ID';
COMMENT ON COLUMN celestial_bodies.type IS '天体类型：star(恒星), planet(行星), dwarf_planet(矮行星), satellite(卫星), probe(探测器), comet(彗星), asteroid(小行星)';
COMMENT ON COLUMN celestial_bodies.details IS '详细信息，支持Markdown格式，在详情面板中展示';
COMMENT ON COLUMN celestial_bodies.metadata IS 'JSON格式的扩展元数据';

metadata JSONB字段示例:

{
  "launch_date": "1977-09-05",
  "status": "active",
  "mass_kg": 722,
  "radius_km": 2575,
  "orbit_period_days": 365.25,
  "rotation_period_hours": 24,
  "discovery_date": "1930-02-18",
  "discoverer": "Clyde Tombaugh",
  "surface_temp_k": 288,
  "atmosphere": ["N2", "O2"],
  "moons": 1
}

3.2 positions - 位置历史表

存储天体的位置历史数据，支持历史查询和轨迹回放。这是一个时间序列表，数据量可达百万级。

CREATE TABLE positions (
    id BIGSERIAL PRIMARY KEY,
    body_id VARCHAR(50) NOT NULL REFERENCES celestial_bodies(id) ON DELETE CASCADE,
    time TIMESTAMP NOT NULL,           -- 位置时间点（UTC）
    x DOUBLE PRECISION NOT NULL,       -- X坐标（AU，日心坐标系）
    y DOUBLE PRECISION NOT NULL,       -- Y坐标（AU）
    z DOUBLE PRECISION NOT NULL,       -- Z坐标（AU）
    vx DOUBLE PRECISION,               -- X方向速度（AU/day，可选）
    vy DOUBLE PRECISION,               -- Y方向速度（可选）
    vz DOUBLE PRECISION,               -- Z方向速度（可选）
    source VARCHAR(50) DEFAULT 'nasa_horizons',  -- 数据来源
    created_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT chk_source CHECK (source IN (
        'nasa_horizons', 'calculated', 'user_defined', 'imported'
    ))
);

-- 索引（性能关键！）
CREATE INDEX idx_positions_body_time ON positions(body_id, time DESC);
CREATE INDEX idx_positions_time ON positions(time);
CREATE INDEX idx_positions_body_id ON positions(body_id);

-- 注释
COMMENT ON TABLE positions IS '天体位置历史表（时间序列数据）';
COMMENT ON COLUMN positions.body_id IS '关联celestial_bodies表的天体ID';
COMMENT ON COLUMN positions.time IS '该位置的观测/计算时间（UTC）';
COMMENT ON COLUMN positions.x IS 'X坐标，单位AU（天文单位），日心坐标系';
COMMENT ON COLUMN positions.source IS '数据来源：nasa_horizons(NASA API), calculated(计算), user_defined(用户定义), imported(导入)';

使用场景:

查询某天体在某时间点的位置
查询某天体在时间范围内的轨迹
支持时间旅行功能（回放历史位置）
轨迹可视化

3.3 orbits - 轨道路径表

存储预计算的轨道路径数据，用于3D可视化渲染。

CREATE TABLE orbits (
    id SERIAL PRIMARY KEY,
    body_id VARCHAR(50) NOT NULL REFERENCES celestial_bodies(id) ON DELETE CASCADE,
    points JSONB NOT NULL,             -- 轨道点数组 [{x, y, z}, ...]
    num_points INTEGER NOT NULL,       -- 轨道点数量
    period_days DOUBLE PRECISION,      -- 轨道周期（天）
    color VARCHAR(20),                 -- 轨道线颜色（HEX）
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT uq_orbits_body_id UNIQUE (body_id)
);

-- 索引
CREATE INDEX idx_orbits_body_id ON orbits(body_id);
CREATE INDEX idx_orbits_updated_at ON orbits(updated_at);

-- 注释
COMMENT ON TABLE orbits IS '轨道路径数据表';
COMMENT ON COLUMN orbits.points IS 'JSON数组格式的轨道点：[{"x": 1.0, "y": 0.0, "z": 0.0}, ...]';
COMMENT ON COLUMN orbits.num_points IS '轨道点数量，用于性能优化';
COMMENT ON COLUMN orbits.color IS '轨道线显示颜色，HEX格式，如#FF5733';

points JSONB字段示例:

[
  {"x": 1.0, "y": 0.0, "z": 0.0},
  {"x": 0.99, "y": 0.05, "z": 0.01},
  {"x": 0.97, "y": 0.10, "z": 0.02}
]

3.4 resources - 资源文件管理表

统一管理纹理、3D模型、图标等静态资源。

CREATE TABLE resources (
    id SERIAL PRIMARY KEY,
    body_id VARCHAR(50) REFERENCES celestial_bodies(id) ON DELETE CASCADE,
    resource_type VARCHAR(50) NOT NULL,  -- 资源类型
    file_path VARCHAR(500) NOT NULL,     -- 相对于upload目录的路径
    file_size INTEGER,                   -- 文<><E69687>大小（bytes）
    mime_type VARCHAR(100),              -- MIME类型
    metadata JSONB,                      -- 扩展信息
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT chk_resource_type CHECK (resource_type IN (
        'texture', 'model', 'icon', 'thumbnail', 'data'
    ))
);

-- 索引
CREATE INDEX idx_resources_body_id ON resources(body_id);
CREATE INDEX idx_resources_type ON resources(resource_type);

-- 注释
COMMENT ON TABLE resources IS '资源文件管理表（纹理、模型、图标等）';
COMMENT ON COLUMN resources.resource_type IS '资源类型：texture(纹理), model(3D模型), icon(图标), thumbnail(缩略图), data(数据文件)';
COMMENT ON COLUMN resources.file_path IS '相对路径，例如：textures/planets/earth_2k.jpg';
COMMENT ON COLUMN resources.metadata IS 'JSON格式元数据';

metadata JSONB字段示例:

{
  "width": 2048,
  "height": 1024,
  "format": "jpg",
  "color_space": "sRGB",
  "model_format": "glb",
  "polygon_count": 15000,
  "compression": "gzip"
}

3.5 static_data - 静态天文数据表

存储星座、星系、恒星等不需要动态计算的静态天文数据<EFBFBD><EFBFBD><EFBFBD>

CREATE TABLE static_data (
    id SERIAL PRIMARY KEY,
    category VARCHAR(50) NOT NULL,      -- 数据分类
    name VARCHAR(200) NOT NULL,         -- 名称
    name_zh VARCHAR(200),               -- 中文名称
    data JSONB NOT NULL,                -- 完整数据（JSON格式）
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT chk_category CHECK (category IN (
        'constellation', 'galaxy', 'star', 'nebula', 'cluster',
        'asteroid_belt', 'kuiper_belt', 'interstellar'
    )),
    CONSTRAINT uq_category_name UNIQUE (category, name)
);

-- 索引
CREATE INDEX idx_static_data_category ON static_data(category);
CREATE INDEX idx_static_data_name ON static_data(name);
CREATE INDEX idx_static_data_data ON static_data USING GIN(data);  -- JSONB索引

-- 注释
COMMENT ON TABLE static_data IS '静态天文数据表（星座、星系、恒星等）';
COMMENT ON COLUMN static_data.category IS '数据分类：constellation(星座), galaxy(星系), star(恒星), nebula(星云), cluster(星团), interstellar(恒星际/系外行星系统)';
COMMENT ON COLUMN static_data.data IS 'JSON格式的完整数据，结构根据category不同而不同';

data JSONB字段示例 - 星座:

{
  "stars": [
    {"name": "Betelgeuse", "ra": 88.79, "dec": 7.41},
    {"name": "Rigel", "ra": 78.63, "dec": -8.20}
  ],
  "lines": [[0, 1], [1, 2]],
  "mythology": "猎户座的神话故事..."
}

data JSONB字段示例 - 恒星际系统 (Phase 3 & 4):

{
  "distance_pc": 12.1,
  "ra": 346.5,
  "dec": -15.9,
  "position": {"x": 10.5, "y": -5.2, "z": 2.1},
  "spectral_type": "M4V",
  "planet_count": 3,
  "color": "#ffbd6f",
  "planets": [
    {
      "name": "Trappist-1 b",
      "semi_major_axis_au": 0.011,
      "period_days": 1.51,
      "eccentricity": 0.0,
      "radius_earth": 1.12,
      "temperature_k": 400
    }
  ]
}

data JSONB字段示例 - 恒星:

{
  "distance_ly": 4.37,
  "ra": 219.90,
  "dec": -60.83,
  "magnitude": -0.27,
  "color": "#FFF8E7",
  "spectral_type": "G2V",
  "mass_solar": 1.0,
  "radius_solar": 1.0
}

3.6 star_systems - 恒星系统表

存储恒星系统的基本信息（Phase 3 - 恒星际扩展）。包括太阳系和其他恒星系统。

CREATE TABLE star_systems (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,              -- 系统名称（如"Solar System"）
    name_zh VARCHAR(200),                    -- 中文名称（如"太阳系"）
    host_star_name VARCHAR(200) NOT NULL,    -- 主恒星名称
    distance_pc DOUBLE PRECISION,            -- 距离（秒差距）
    distance_ly DOUBLE PRECISION,            -- 距离（光年）
    ra DOUBLE PRECISION,                     -- 赤经（度）
    dec DOUBLE PRECISION,                    -- 赤纬（度）
    position_x DOUBLE PRECISION,             -- 笛卡尔坐标X（秒差距）
    position_y DOUBLE PRECISION,             -- 笛卡尔坐标Y（秒差距）
    position_z DOUBLE PRECISION,             -- 笛卡尔坐标Z（秒差距）
    spectral_type VARCHAR(20),               -- 光谱类型（如"G2V"）
    radius_solar DOUBLE PRECISION,           -- 恒星半径（太阳半径倍数）
    mass_solar DOUBLE PRECISION,             -- 恒星质量（太阳质量倍数）
    temperature_k DOUBLE PRECISION,          -- 表面温度（开尔文）
    magnitude DOUBLE PRECISION,              -- 视星等
    luminosity_solar DOUBLE PRECISION,       -- 光度（太阳光度倍数）
    color VARCHAR(20),                       -- 显示颜色（HEX格式）
    planet_count INTEGER DEFAULT 0,          -- 行星数量
    description TEXT,                        -- 系统描述
    details TEXT,                            -- 详细信息（Markdown格式）
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT uq_star_system_name UNIQUE (name)
);

-- 索引
CREATE INDEX idx_star_systems_name ON star_systems(name);
CREATE INDEX idx_star_systems_distance ON star_systems(distance_pc);
CREATE INDEX idx_star_systems_position ON star_systems(position_x, position_y, position_z);

-- 注释
COMMENT ON TABLE star_systems IS '恒星系统基本信息表（Phase 3）';
COMMENT ON COLUMN star_systems.distance_pc IS '距离（秒差距），1 pc ≈ 3.26 光年';
COMMENT ON COLUMN star_systems.spectral_type IS '恒星光谱分类，如G2V（太阳型）、M5.5V（红矮星）';
COMMENT ON COLUMN star_systems.position_x IS '以太阳为原点的笛卡尔坐标X（秒差距）';
COMMENT ON COLUMN star_systems.color IS '3D可视化中的恒星颜色，HEX格式';

使用场景:

Galaxy View（银河视图）的恒星系统展示
恒星系统搜索和筛选
系外行星系统管理
星际距离计算

3.7 interstellar_bodies - 恒星际天体表

存储恒星系统中的天体信息（行星、卫星等），与star_systems表关联。

CREATE TABLE interstellar_bodies (
    id SERIAL PRIMARY KEY,
    system_id INTEGER NOT NULL REFERENCES star_systems(id) ON DELETE CASCADE,
    name VARCHAR(200) NOT NULL,              -- 天体名称
    name_zh VARCHAR(200),                    -- 中文名称
    type VARCHAR(50) NOT NULL,               -- 天体类型
    description TEXT,                        -- 描述
    extra_data JSONB,                        -- 扩展数据
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT chk_interstellar_body_type CHECK (type IN (
        'planet', 'satellite', 'dwarf_planet', 'asteroid'
    ))
);

-- 索引
CREATE INDEX idx_interstellar_bodies_system ON interstellar_bodies(system_id);
CREATE INDEX idx_interstellar_bodies_type ON interstellar_bodies(type);
CREATE INDEX idx_interstellar_bodies_name ON interstellar_bodies(name);
CREATE INDEX idx_interstellar_bodies_extra_data ON interstellar_bodies USING GIN(extra_data);

-- 注释
COMMENT ON TABLE interstellar_bodies IS '恒星际天体信息表（Phase 3）';
COMMENT ON COLUMN interstellar_bodies.system_id IS '所属恒星系统ID（外键关联star_systems表）';
COMMENT ON COLUMN interstellar_bodies.type IS '天体类型：planet(行星), satellite(卫星), dwarf_planet(矮行星), asteroid(小行星)';
COMMENT ON COLUMN interstellar_bodies.extra_data IS 'JSON格式扩展数据，包含轨道参数、物理参数等';

extra_data JSONB字段示例:

{
  "semi_major_axis_au": 0.0172,
  "period_days": 1.51087,
  "eccentricity": 0.00622,
  "inclination_deg": 89.728,
  "radius_earth": 1.116,
  "mass_earth": 1.374,
  "temperature_k": 400,
  "discovery_year": 2017,
  "discovery_method": "Transit",
  "equilibrium_temp_k": 400,
  "density_gcc": 5.9
}

使用场景:

显示恒星系统的行星列表
系外行星数据管理
行星轨道参数查询
行星物理特性分析

4. 系统管理表

4.1 users - 用户表

存储用户账号信息。

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,   -- 用户名（唯一）
    password_hash VARCHAR(255) NOT NULL,    -- 密码哈希（bcrypt）
    email VARCHAR(255) UNIQUE,              -- 邮箱地址
    full_name VARCHAR(100),                 -- 全名
    is_active BOOLEAN DEFAULT TRUE NOT NULL, -- 账号状态
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    last_login_at TIMESTAMP,                -- 最后登录时间

    CONSTRAINT chk_username_length CHECK (LENGTH(username) >= 3)
);

-- 索引
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_active ON users(is_active);

-- 注释
COMMENT ON TABLE users IS '用户账号表';
COMMENT ON COLUMN users.password_hash IS '使用bcrypt加密的密码哈希';
COMMENT ON COLUMN users.is_active IS '账号激活状态，false表示禁用';

4.2 roles - 角色表

定义系统角色（如admin、user等）。

CREATE TABLE roles (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,       -- 角色名称（如'admin'）
    display_name VARCHAR(100) NOT NULL,     -- 显示名称
    description TEXT,                       -- 角色描述
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 索引
CREATE INDEX idx_roles_name ON roles(name);

-- 注释
COMMENT ON TABLE roles IS '角色定义表';
COMMENT ON COLUMN roles.name IS '角色标识符，如admin、user、guest';
COMMENT ON COLUMN roles.display_name IS '显示名称，如管理员、普通用户';

预置角色:

admin: 系统管理员（全部权限）
user: 普通用户（基础权限）

4.3 user_roles - 用户角色关联表

多对多关系：一个用户可以有多个角色，一个角色可以分配给多个用户。

CREATE TABLE user_roles (
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    role_id INTEGER REFERENCES roles(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT NOW(),

    PRIMARY KEY (user_id, role_id)
);

-- 索引
CREATE INDEX idx_user_roles_user_id ON user_roles(user_id);
CREATE INDEX idx_user_roles_role_id ON user_roles(role_id);

-- 注释
COMMENT ON TABLE user_roles IS '用户角色关联表（多对多）';

4.4 menus - 菜单表

后台管理菜单配置。

CREATE TABLE menus (
    id SERIAL PRIMARY KEY,
    parent_id INTEGER REFERENCES menus(id) ON DELETE CASCADE,  -- 父菜单ID
    name VARCHAR(100) NOT NULL,             -- 菜单名称
    title VARCHAR(100) NOT NULL,            -- 显示标题
    icon VARCHAR(100),                      -- 图标名称
    path VARCHAR(255),                      -- 路由路径
    component VARCHAR(255),                 -- 组件路径
    sort_order INTEGER DEFAULT 0 NOT NULL,  -- 显示顺序
    is_active BOOLEAN DEFAULT TRUE NOT NULL, -- 菜单状态
    description TEXT,                       -- 菜单描述
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 索引
CREATE INDEX idx_menus_parent_id ON menus(parent_id);
CREATE INDEX idx_menus_sort_order ON menus(sort_order);
CREATE INDEX idx_menus_active ON menus(is_active);

-- 注释
COMMENT ON TABLE menus IS '后台管理菜单配置表';
COMMENT ON COLUMN menus.parent_id IS '父菜单ID，NULL表示根菜单';
COMMENT ON COLUMN menus.path IS '前端路由路径，如/admin/celestial-bodies';
COMMENT ON COLUMN menus.component IS 'Vue/React组件路径';

4.5 role_menus - 角色菜单关联表

定义角色可访问的菜单（权限控制）。

CREATE TABLE role_menus (
    id SERIAL PRIMARY KEY,
    role_id INTEGER REFERENCES roles(id) ON DELETE CASCADE,
    menu_id INTEGER REFERENCES menus(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT uq_role_menu UNIQUE (role_id, menu_id)
);

-- 索引
CREATE INDEX idx_role_menus_role_id ON role_menus(role_id);
CREATE INDEX idx_role_menus_menu_id ON role_menus(menu_id);

-- 注释
COMMENT ON TABLE role_menus IS '角色菜单权限关联表';

4.6 system_settings - 系统配置表

存储平台配置参数，支持动态配置。

CREATE TABLE system_settings (
    id SERIAL PRIMARY KEY,
    key VARCHAR(100) UNIQUE NOT NULL,       -- 配置键
    value TEXT NOT NULL,                    -- 配置值
    value_type VARCHAR(20) NOT NULL DEFAULT 'string',  -- 值类型
    category VARCHAR(50) NOT NULL DEFAULT 'general',   -- 分类
    label VARCHAR(200) NOT NULL,            -- 显示标签
    description TEXT,                       -- 描述
    is_public BOOLEAN DEFAULT FALSE,        -- 是否前端可访问
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT chk_value_type CHECK (value_type IN (
        'string', 'int', 'float', 'bool', 'json'
    ))
);

-- 索引
CREATE INDEX idx_system_settings_key ON system_settings(key);
CREATE INDEX idx_system_settings_category ON system_settings(category);
CREATE INDEX idx_system_settings_public ON system_settings(is_public);

-- 注释
COMMENT ON TABLE system_settings IS '系统配置参数表';
COMMENT ON COLUMN system_settings.key IS '配置键，如timeline_interval_days';
COMMENT ON COLUMN system_settings.value_type IS '值类型：string, int, float, bool, json';
COMMENT ON COLUMN system_settings.is_public IS '是否允许前端访问该配置';

配置示例:

INSERT INTO system_settings (key, value, value_type, category, label, description, is_public) VALUES
('timeline_interval_days', '7', 'int', 'visualization', '时间轴播放间隔（天）', '时间轴播放模式下的时间间隔', true),
('max_orbit_points', '500', 'int', 'visualization', '最大轨道点数', '轨道可视化的最大点数', true),
('cache_ttl_hours', '24', 'int', 'cache', '缓存过期时间（小时）', 'Redis缓存的默认过期时间', false);

4.7 tasks - 后台任务表

记录后台异步任务的执行状态。

CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    task_type VARCHAR(50) NOT NULL,         -- 任务类型
    status VARCHAR(20) NOT NULL DEFAULT 'pending',  -- 任务状态
    description VARCHAR(255),               -- 任务描述
    params JSON,                            -- 输入参数
    result JSON,                            -- 输出结果
    progress INTEGER DEFAULT 0,             -- 进度（0-100）
    error_message TEXT,                     -- 错误信息
    created_by INTEGER,                     -- 创建用户ID
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    started_at TIMESTAMP,                   -- 开始时间
    completed_at TIMESTAMP,                 -- 完成时间

    CONSTRAINT chk_status CHECK (status IN (
        'pending', 'running', 'completed', 'failed', 'cancelled'
    )),
    CONSTRAINT chk_progress CHECK (progress >= 0 AND progress <= 100)
);

-- 索引
CREATE INDEX idx_tasks_status ON tasks(status);
CREATE INDEX idx_tasks_type ON tasks(task_type);
CREATE INDEX idx_tasks_created_at ON tasks(created_at DESC);

-- 注释
COMMENT ON TABLE tasks IS '后台任务表';
COMMENT ON COLUMN tasks.task_type IS '任务类型，如nasa_download、orbit_calculate';
COMMENT ON COLUMN tasks.status IS '任务状态：pending(待执行), running(执行中), completed(已完成), failed(失败), cancelled(已取消)';
COMMENT ON COLUMN tasks.progress IS '任务进度百分比（0-100）';

5. 缓存表

5.1 nasa_cache - NASA API缓存表

持久化NASA Horizons API的响应结果，减少API调用。

CREATE TABLE nasa_cache (
    cache_key VARCHAR(500) PRIMARY KEY,  -- 缓存键
    body_id VARCHAR(50),
    start_time TIMESTAMP,                -- 查询起始时间
    end_time TIMESTAMP,                  -- 查询结束时间
    step VARCHAR(10),                    -- 时间步长（如'1d'）
    data JSONB NOT NULL,                 -- 完整的API响应数据
    expires_at TIMESTAMP NOT NULL,       -- 过期时间
    created_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT chk_time_range CHECK (end_time >= start_time)
);

-- 索引
CREATE INDEX idx_nasa_cache_body_id ON nasa_cache(body_id);
CREATE INDEX idx_nasa_cache_expires ON nasa_cache(expires_at);
CREATE INDEX idx_nasa_cache_time_range ON nasa_cache(body_id, start_time, end_time);

-- 注释
COMMENT ON TABLE nasa_cache IS 'NASA Horizons API响应缓存表';
COMMENT ON COLUMN nasa_cache.cache_key IS '缓存键格式：{body_id}:{start}:{end}:{step}';
COMMENT ON COLUMN nasa_cache.data IS 'NASA API的完整JSON响应';
COMMENT ON COLUMN nasa_cache.expires_at IS '缓存过期时间，过期后自动失效';

6. 数据关系图

celestial_bodies (天体)
    ├── positions (1:N) - 天体位置历史
    ├── orbits (1:1) - 轨道路径
    └── resources (1:N) - 资源文件

users (用户)
    └── user_roles (N:M) ←→ roles (角色)
        └── role_menus (N:M) ←→ menus (菜单)

tasks (任务) - 独立表
system_settings (配置) - 独立表
static_data (静态数据) - 独立表
nasa_cache (缓存) - 独立表

7. 初始化脚本

创建数据库

# 使用Docker容器
docker exec -it cosmo_postgres psql -U postgres -c "CREATE DATABASE cosmo_db WITH ENCODING='UTF8';"

完整建表脚本

-- 按依赖顺序创建表

-- 1. 天体基本信息表
CREATE TABLE celestial_bodies (
    id VARCHAR(50) PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    name_zh VARCHAR(200),
    type VARCHAR(50) NOT NULL,
    description TEXT,
    details TEXT,
    metadata JSONB,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    CONSTRAINT chk_type CHECK (type IN ('star', 'planet', 'dwarf_planet', 'satellite', 'probe', 'comet', 'asteroid'))
);
CREATE INDEX idx_celestial_bodies_type ON celestial_bodies(type);
CREATE INDEX idx_celestial_bodies_name ON celestial_bodies(name);

-- 2. 位置历史表
CREATE TABLE positions (
    id BIGSERIAL PRIMARY KEY,
    body_id VARCHAR(50) NOT NULL REFERENCES celestial_bodies(id) ON DELETE CASCADE,
    time TIMESTAMP NOT NULL,
    x DOUBLE PRECISION NOT NULL,
    y DOUBLE PRECISION NOT NULL,
    z DOUBLE PRECISION NOT NULL,
    vx DOUBLE PRECISION,
    vy DOUBLE PRECISION,
    vz DOUBLE PRECISION,
    source VARCHAR(50) DEFAULT 'nasa_horizons',
    created_at TIMESTAMP DEFAULT NOW(),
    CONSTRAINT chk_source CHECK (source IN ('nasa_horizons', 'calculated', 'user_defined', 'imported'))
);
CREATE INDEX idx_positions_body_time ON positions(body_id, time DESC);

-- 3. 轨道路径表
CREATE TABLE orbits (
    id SERIAL PRIMARY KEY,
    body_id VARCHAR(50) NOT NULL REFERENCES celestial_bodies(id) ON DELETE CASCADE,
    points JSONB NOT NULL,
    num_points INTEGER NOT NULL,
    period_days DOUBLE PRECISION,
    color VARCHAR(20),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    CONSTRAINT uq_orbits_body_id UNIQUE (body_id)
);
CREATE INDEX idx_orbits_body_id ON orbits(body_id);

-- 4. 资源管理表
CREATE TABLE resources (
    id SERIAL PRIMARY KEY,
    body_id VARCHAR(50) REFERENCES celestial_bodies(id) ON DELETE CASCADE,
    resource_type VARCHAR(50) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size INTEGER,
    mime_type VARCHAR(100),
    metadata JSONB,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    CONSTRAINT chk_resource_type CHECK (resource_type IN ('texture', 'model', 'icon', 'thumbnail', 'data'))
);

-- 5. 静态数据表
CREATE TABLE static_data (
    id SERIAL PRIMARY KEY,
    category VARCHAR(50) NOT NULL,
    name VARCHAR(200) NOT NULL,
    name_zh VARCHAR(200),
    data JSONB NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    CONSTRAINT chk_category CHECK (category IN ('constellation', 'galaxy', 'star', 'nebula', 'cluster')),
    CONSTRAINT uq_category_name UNIQUE (category, name)
);

-- 6. 用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE,
    full_name VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    last_login_at TIMESTAMP
);

-- 7. 角色表
CREATE TABLE roles (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    display_name VARCHAR(100) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 8. 用户角色关联表
CREATE TABLE user_roles (
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    role_id INTEGER REFERENCES roles(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (user_id, role_id)
);

-- 9. 菜单表
CREATE TABLE menus (
    id SERIAL PRIMARY KEY,
    parent_id INTEGER REFERENCES menus(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    title VARCHAR(100) NOT NULL,
    icon VARCHAR(100),
    path VARCHAR(255),
    component VARCHAR(255),
    sort_order INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 10. 角色菜单关联表
CREATE TABLE role_menus (
    id SERIAL PRIMARY KEY,
    role_id INTEGER REFERENCES roles(id) ON DELETE CASCADE,
    menu_id INTEGER REFERENCES menus(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT NOW(),
    CONSTRAINT uq_role_menu UNIQUE (role_id, menu_id)
);

-- 11. 系统配置表
CREATE TABLE system_settings (
    id SERIAL PRIMARY KEY,
    key VARCHAR(100) UNIQUE NOT NULL,
    value TEXT NOT NULL,
    value_type VARCHAR(20) NOT NULL DEFAULT 'string',
    category VARCHAR(50) NOT NULL DEFAULT 'general',
    label VARCHAR(200) NOT NULL,
    description TEXT,
    is_public BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 12. 后台任务表
CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    task_type VARCHAR(50) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    description VARCHAR(255),
    params JSON,
    result JSON,
    progress INTEGER DEFAULT 0,
    error_message TEXT,
    created_by INTEGER,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    started_at TIMESTAMP,
    completed_at TIMESTAMP
);

-- 13. NASA缓存表
CREATE TABLE nasa_cache (
    cache_key VARCHAR(500) PRIMARY KEY,
    body_id VARCHAR(50),
    start_time TIMESTAMP,
    end_time TIMESTAMP,
    step VARCHAR(10),
    data JSONB NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

8. 查询示例

查询某天体的最新位置

SELECT b.name, b.name_zh, p.x, p.y, p.z, p.time
FROM celestial_bodies b
LEFT JOIN LATERAL (
    SELECT * FROM positions
    WHERE body_id = b.id
    ORDER BY time DESC
    LIMIT 1
) p ON true
WHERE b.id = '-31';

查询用户的所有菜单权限

SELECT DISTINCT m.id, m.name, m.title, m.path, m.icon
FROM users u
JOIN user_roles ur ON u.id = ur.user_id
JOIN role_menus rm ON ur.role_id = rm.role_id
JOIN menus m ON rm.menu_id = m.id
WHERE u.id = 1 AND m.is_active = true
ORDER BY m.sort_order;

查询所有运行中的任务

SELECT id, task_type, description, progress, started_at
FROM tasks
WHERE status = 'running'
ORDER BY started_at DESC;

9. 维护建议

定期清理

-- 清理过期缓存
DELETE FROM nasa_cache WHERE expires_at < NOW();

-- 清理旧任务记录（保留90天）
DELETE FROM tasks WHERE created_at < NOW() - INTERVAL '90 days' AND status IN ('completed', 'failed');

性能优化

-- 分析表
ANALYZE celestial_bodies;
ANALYZE positions;

-- 重建索引
REINDEX TABLE positions;

-- 清理死元组
VACUUM FULL positions;

备份策略

# 每日备份
pg_dump -U postgres cosmo_db > backup_$(date +%Y%m%d).sql

# 增量备份（推荐使用WAL归档）

文档版本

版本: 2.0
更新日期: 2025-12-05
对应阶段: Phase 2 完成
下一步: Phase 3 - 恒星际扩展

32 KiB Raw Permalink Blame History Unescape Escape

Cosmo 数据库表结构设计文档

📋 文档目录

1. 数据库信息

2. 数据表索引

3. 核心业务表

3.1 celestial_bodies - 天体基本信息表

3.2 positions - 位置历史表

3.3 orbits - 轨道路径表

3.4 resources - 资源文件管理表

3.5 static_data - 静态天文数据表

3.6 star_systems - 恒星系统表

3.7 interstellar_bodies - 恒星际天体表

4. 系统管理表

4.1 users - 用户表

4.2 roles - 角色表

4.3 user_roles - 用户角色关联表

4.4 menus - 菜单表

4.5 role_menus - 角色菜单关联表

4.6 system_settings - 系统配置表

4.7 tasks - 后台任务表

5. 缓存表

5.1 nasa_cache - NASA API缓存表

6. 数据关系图

7. 初始化脚本

创建数据库

完整建表脚本

8. 查询示例

查询某天体的最新位置

查询用户的所有菜单权限

查询所有运行中的任务

9. 维护建议

定期清理

性能优化

备份策略

文档版本

32 KiB

Raw Permalink Blame History