78 lines
12 KiB
SQL
78 lines
12 KiB
SQL
-- ============================================================
|
||
-- 桃育种·单株鉴定性状字典种子（权威来源：育种管理系统操作手册）
|
||
-- 结构：7 标签页；「基本信息」为 tree 实体（不入性状），此处仅种入
|
||
-- 基本鉴定 / 果实外观 / 果皮 / 果实大小 / 果肉 / 果核 六个标签页性状；
|
||
-- 「其它」标签页由用户自定义追加 bre_trait。
|
||
-- data_type: numeric(数值,可统计) / categorical(分类,下拉) / date(日期)
|
||
-- is_core='1' 仅 7 个数值型（统计引擎消费）；scale_json 存分类选项(JSON 字符串数组)
|
||
-- direction: desc=越大越好(默认) / asc=越小越好（裂果率、病害级别等）
|
||
-- into_ebv: 1=选种目标(进 EBV/指数/决策候选) / 0=仅记录
|
||
-- default_h2:先验遗传力 h²（指数无实测 h² 时兜底；桃经典先验，可在性状字典修改）
|
||
-- 幂等：以 trait_code 冲突则更新（不动 uuid/created_time）
|
||
-- ============================================================
|
||
|
||
INSERT INTO bre_trait
|
||
(trait_code, trait_name, category, data_type, unit, is_core, scale_json, uuid, is_deleted, created_time,
|
||
direction, into_ebv, default_h2)
|
||
VALUES
|
||
-- ① 基本鉴定 -------------------------------------------------
|
||
('growth_vigor', '生长势', '基本鉴定', 'categorical', NULL, '0', '["强","中","弱"]', md5(random()::text||'growth_vigor'), false, now(), 'desc', '0', NULL),
|
||
('growth_type', '生长型', '基本鉴定', 'categorical', NULL, '0', '["普通","半矮","矮"]', md5(random()::text||'growth_type'), false, now(), 'desc', '0', NULL),
|
||
('first_flower_date', '始花日期', '基本鉴定', 'date', NULL, '0', NULL, md5(random()::text||'first_flower_date'), false, now(), 'desc', '0', NULL),
|
||
('flower_type', '花型', '基本鉴定', 'categorical', NULL, '0', '["蔷薇型","铃型"]', md5(random()::text||'flower_type'), false, now(), 'desc', '0', NULL),
|
||
('pollen', '花粉', '基本鉴定', 'categorical', NULL, '0', '["无","少","中","多"]', md5(random()::text||'pollen'), false, now(), 'desc', '0', NULL),
|
||
('load_amount', '负载量', '基本鉴定', 'categorical', NULL, '0', '["高","中","低"]', md5(random()::text||'load_amount'), false, now(), 'desc', '0', NULL),
|
||
('maturity_uniformity', '成熟一致性', '基本鉴定', 'categorical', NULL, '0', '["一致","较一致","不一致"]', md5(random()::text||'maturity_uniformity'), false, now(), 'desc', '0', NULL),
|
||
-- ② 果实外观 -------------------------------------------------
|
||
('fruit_shape', '果形', '果实外观', 'categorical', NULL, '0', '["扁平","扁圆","圆","椭圆","卵圆"]', md5(random()::text||'fruit_shape'), false, now(), 'desc', '0', NULL),
|
||
('apex_shape', '果顶', '果实外观', 'categorical', NULL, '0', '["突出","稍突出","圆平","稍凹陷","凹陷"]', md5(random()::text||'apex_shape'), false, now(), 'desc', '0', NULL),
|
||
('stem_cavity_depth', '梗洼深度', '果实外观', 'categorical', NULL, '0', '["浅","中","深"]', md5(random()::text||'stem_cavity_depth'), false, now(), 'desc', '0', NULL),
|
||
('stem_cavity_width', '梗洼宽度', '果实外观', 'categorical', NULL, '0', '["窄","中","宽"]', md5(random()::text||'stem_cavity_width'), false, now(), 'desc', '0', NULL),
|
||
('fruit_base', '果基', '果实外观', 'categorical', NULL, '0', '["正","稍偏","偏"]', md5(random()::text||'fruit_base'), false, now(), 'desc', '0', NULL),
|
||
('suture_line', '缝合线', '果实外观', 'categorical', NULL, '0', '["浅","中","深"]', md5(random()::text||'suture_line'), false, now(), 'desc', '0', NULL),
|
||
('symmetry', '对称性', '果实外观', 'categorical', NULL, '0', '["对称","较对称","不对称"]', md5(random()::text||'symmetry'), false, now(), 'desc', '0', NULL),
|
||
-- ③ 果皮 -----------------------------------------------------
|
||
('pubescence', '茸毛', '果皮', 'categorical', NULL, '0', '["无","少","中","多"]', md5(random()::text||'pubescence'), false, now(), 'desc', '0', NULL),
|
||
('skin_base_color', '底色', '果皮', 'categorical', NULL, '0', '["淡绿","绿白","白","浅黄","深黄"]', md5(random()::text||'skin_base_color'), false, now(), 'desc', '0', NULL),
|
||
('coloration_area', '着色面积', '果皮', 'categorical', NULL, '0', '["无","少","中","多","全"]', md5(random()::text||'coloration_area'), false, now(), 'desc', '0', NULL),
|
||
('coloration_depth', '着色深度', '果皮', 'categorical', NULL, '0', '["浅","中","深"]', md5(random()::text||'coloration_depth'), false, now(), 'desc', '0', NULL),
|
||
('coloration_brightness', '着色亮度', '果皮', 'categorical', NULL, '0', '["暗","中","亮"]', md5(random()::text||'coloration_brightness'), false, now(), 'desc', '0', NULL),
|
||
('coloration_pattern', '着色形态', '果皮', 'categorical', NULL, '0', '["晕","条","斑"]', md5(random()::text||'coloration_pattern'), false, now(), 'desc', '0', NULL),
|
||
('skin_spot', '果面斑点', '果皮', 'categorical', NULL, '0', '["少","中","多"]', md5(random()::text||'skin_spot'), false, now(), 'desc', '0', NULL),
|
||
('skin_peelability', '果皮剥离度', '果皮', 'categorical', NULL, '0', '["不能","难","易"]', md5(random()::text||'skin_peelability'), false, now(), 'desc', '0', NULL),
|
||
-- ④ 果实大小（★数值型 core）---------------------------------
|
||
('max_fruit_weight', '最大果重', '果实大小', 'numeric', 'g', '1', NULL, md5(random()::text||'max_fruit_weight'), false, now(), 'desc', '1', 0.5),
|
||
('avg_fruit_weight', '平均果重', '果实大小', 'numeric', 'g', '1', NULL, md5(random()::text||'avg_fruit_weight'), false, now(), 'desc', '1', 0.5),
|
||
('longitudinal_dia', '纵径', '果实大小', 'numeric', 'mm', '1', NULL, md5(random()::text||'longitudinal_dia'), false, now(), 'desc', '1', 0.4),
|
||
('transverse_dia', '横径', '果实大小', 'numeric', 'mm', '1', NULL, md5(random()::text||'transverse_dia'), false, now(), 'desc', '1', 0.4),
|
||
('lateral_dia', '侧径', '果实大小', 'numeric', 'mm', '1', NULL, md5(random()::text||'lateral_dia'), false, now(), 'desc', '1', 0.4),
|
||
-- ⑤ 果肉 -----------------------------------------------------
|
||
('flesh_thickness', '果肉厚度', '果肉', 'numeric', 'mm', '1', NULL, md5(random()::text||'flesh_thickness'), false, now(), 'desc', '1', 0.3),
|
||
('flesh_color', '肉色', '果肉', 'categorical', NULL, '0', '["淡绿","白","黄白","黄","橙黄","红","紫红"]', md5(random()::text||'flesh_color'), false, now(), 'desc', '0', NULL),
|
||
('flesh_firmness', '硬度', '果肉', 'categorical', NULL, '0', '["很软","软","中","硬","很硬"]', md5(random()::text||'flesh_firmness'), false, now(), 'desc', '0', NULL),
|
||
('subepidermal_anthocyanin','皮下花色苷', '果肉', 'categorical', NULL, '0', '["无","少","中","多"]', md5(random()::text||'subepidermal_anthocyanin'),false, now(), 'desc', '0', NULL),
|
||
('flesh_anthocyanin', '果肉花色苷', '果肉', 'categorical', NULL, '0', '["无","少","中","多"]', md5(random()::text||'flesh_anthocyanin'), false, now(), 'desc', '0', NULL),
|
||
('stone_cavity_anthocyanin','核窝花色苷', '果肉', 'categorical', NULL, '0', '["无","少","中","多"]', md5(random()::text||'stone_cavity_anthocyanin'),false, now(), 'desc', '0', NULL),
|
||
('ssc', 'SSC', '果肉', 'numeric', '%', '1', NULL, md5(random()::text||'ssc'), false, now(), 'desc', '1', 0.5),
|
||
('fiber', '纤维', '果肉', 'categorical', NULL, '0', '["少","中","多"]', md5(random()::text||'fiber'), false, now(), 'desc', '0', NULL),
|
||
('juice', '汁液', '果肉', 'categorical', NULL, '0', '["少","中","多"]', md5(random()::text||'juice'), false, now(), 'desc', '0', NULL),
|
||
('sweet_acidity', '甜酸度', '果肉', 'categorical', NULL, '0', '["淡甜","甜","浓甜","酸甜","甜酸适中","甜酸","酸"]', md5(random()::text||'sweet_acidity'), false, now(), 'desc', '0', NULL),
|
||
('aroma', '香气', '果肉', 'categorical', NULL, '0', '["淡","中","多"]', md5(random()::text||'aroma'), false, now(), 'desc', '0', NULL),
|
||
('quality_overall', '品质综合', '果肉', 'categorical', NULL, '0', '["上","中","下"]', md5(random()::text||'quality_overall'), false, now(), 'desc', '0', NULL),
|
||
-- ⑥ 果核 -----------------------------------------------------
|
||
('stone_adherence', '粘离性', '果核', 'categorical', NULL, '0', '["粘核","半离","离核"]', md5(random()::text||'stone_adherence'), false, now(), 'desc', '0', NULL),
|
||
('stone_size', '核大小', '果核', 'categorical', NULL, '0', '["小","中","大"]', md5(random()::text||'stone_size'), false, now(), 'desc', '0', NULL),
|
||
('stone_shape', '核形', '果核', 'categorical', NULL, '0', '["扁平","近圆","椭圆","倒卵圆","卵圆"]', md5(random()::text||'stone_shape'), false, now(), 'desc', '0', NULL),
|
||
('stone_cracking', '裂核', '果核', 'categorical', NULL, '0', '["无","少","中","多"]', md5(random()::text||'stone_cracking'), false, now(), 'desc', '0', NULL)
|
||
ON CONFLICT (trait_code) DO UPDATE SET
|
||
trait_name = EXCLUDED.trait_name,
|
||
category = EXCLUDED.category,
|
||
data_type = EXCLUDED.data_type,
|
||
unit = EXCLUDED.unit,
|
||
is_core = EXCLUDED.is_core,
|
||
scale_json = EXCLUDED.scale_json,
|
||
direction = EXCLUDED.direction,
|
||
into_ebv = EXCLUDED.into_ebv,
|
||
default_h2 = EXCLUDED.default_h2,
|
||
updated_time = now();
|