94 lines
2.6 KiB
MySQL
94 lines
2.6 KiB
MySQL
|
BEGIN;
|
|||
|
|
|
|||
|
|
-- 1. Insert or Update Sirius System
|
|||
|
|
WITH new_system AS (
|
|||
|
|
INSERT INTO "public"."star_systems" (
|
|||
|
|
"name",
|
|||
|
|
"name_zh",
|
|||
|
|
"host_star_name",
|
|||
|
|
"distance_pc",
|
|||
|
|
"distance_ly",
|
|||
|
|
"ra",
|
|||
|
|
"dec",
|
|||
|
|
"position_x",
|
|||
|
|
"position_y",
|
|||
|
|
"position_z",
|
|||
|
|
"spectral_type",
|
|||
|
|
"magnitude",
|
|||
|
|
"color",
|
|||
|
|
"description",
|
|||
|
|
"planet_count"
|
|||
|
|
) VALUES (
|
|||
|
|
'Sirius',
|
|||
|
|
'天狼星',
|
|||
|
|
'Sirius A',
|
|||
|
|
2.64,
|
|||
|
|
8.6,
|
|||
|
|
101.287,
|
|||
|
|
-16.716,
|
|||
|
|
-0.495,
|
|||
|
|
2.479,
|
|||
|
|
-0.759,
|
|||
|
|
'A1V',
|
|||
|
|
-1.46,
|
|||
|
|
'#FFFFFF',
|
|||
|
|
'天狼星（Sirius，α CMa）是夜空中最亮的恒星，距离太阳系约 8.6 光年。它是一个联星系统，包含一颗蓝矮星（天狼星 A）和一颗白矮星（天狼星 B）。',
|
|||
|
|
0
|
|||
|
|
)
|
|||
|
|
ON CONFLICT (name) DO UPDATE SET
|
|||
|
|
distance_pc = EXCLUDED.distance_pc,
|
|||
|
|
distance_ly = EXCLUDED.distance_ly,
|
|||
|
|
ra = EXCLUDED.ra,
|
|||
|
|
dec = EXCLUDED.dec,
|
|||
|
|
position_x = EXCLUDED.position_x,
|
|||
|
|
position_y = EXCLUDED.position_y,
|
|||
|
|
position_z = EXCLUDED.position_z,
|
|||
|
|
spectral_type = EXCLUDED.spectral_type,
|
|||
|
|
magnitude = EXCLUDED.magnitude,
|
|||
|
|
color = EXCLUDED.color,
|
|||
|
|
description = EXCLUDED.description
|
|||
|
|
RETURNING id
|
|||
|
|
)
|
|||
|
|
-- 2. Insert Celestial Bodies (Sirius A and Sirius B) linked to the system
|
|||
|
|
INSERT INTO "public"."celestial_bodies" (
|
|||
|
|
"id",
|
|||
|
|
"name",
|
|||
|
|
"name_zh",
|
|||
|
|
"type",
|
|||
|
|
"system_id",
|
|||
|
|
"description",
|
|||
|
|
"extra_data",
|
|||
|
|
"is_active"
|
|||
|
|
)
|
|||
|
|
SELECT
|
|||
|
|
'sirius_a',
|
|||
|
|
'Sirius A',
|
|||
|
|
'天狼星 A',
|
|||
|
|
'star',
|
|||
|
|
id,
|
|||
|
|
'天狼星 A 是天狼星系统的主星，是一颗光谱型 A1V 的蓝矮星，其质量约为太阳的 2 倍，光度约为太阳的 25 倍。',
|
|||
|
|
'{"spectral_type": "A1V", "radius_solar": 1.71, "mass_solar": 2.06, "temperature_k": 9940}'::jsonb,
|
|||
|
|
true
|
|||
|
|
FROM new_system
|
|||
|
|
UNION ALL
|
|||
|
|
SELECT
|
|||
|
|
'sirius_b',
|
|||
|
|
'Sirius B',
|
|||
|
|
'天狼星 B',
|
|||
|
|
'star',
|
|||
|
|
id,
|
|||
|
|
'天狼星 B 是天狼星 A 的伴星，是一颗微弱的白矮星。它是人类发现的第一颗白矮星，质量与太阳相当，但体积仅与地球相当。',
|
|||
|
|
'{"spectral_type": "DA2", "radius_solar": 0.0084, "mass_solar": 1.02, "temperature_k": 25200}'::jsonb,
|
|||
|
|
true
|
|||
|
|
FROM new_system
|
|||
|
|
ON CONFLICT (id) DO UPDATE SET
|
|||
|
|
system_id = EXCLUDED.system_id,
|
|||
|
|
name = EXCLUDED.name,
|
|||
|
|
name_zh = EXCLUDED.name_zh,
|
|||
|
|
type = EXCLUDED.type,
|
|||
|
|
description = EXCLUDED.description,
|
|||
|
|
extra_data = EXCLUDED.extra_data,
|
|||
|
|
is_active = EXCLUDED.is_active;
|
|||
|
|
|
|||
|
|
COMMIT;
|