107 lines
5.7 KiB
SQL
107 lines
5.7 KiB
SQL
-- 统计决策地基增强（对应规格 §5 / §3.0 / §3.13 / §3.17 / §3.18）
|
||
-- 全部幂等，可在已有 dev 库上重复执行。create_all 只建新表，本脚本显式补齐。
|
||
|
||
-- ============ ANOVA 结果表（B1） ============
|
||
CREATE TABLE IF NOT EXISTS bre_anova_result (
|
||
id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
||
model_name varchar(128),
|
||
trait_id integer REFERENCES bre_trait(id),
|
||
method varchar(16),
|
||
result_json jsonb,
|
||
created_time timestamp with time zone NOT NULL DEFAULT now(),
|
||
updated_time timestamp with time zone,
|
||
is_deleted boolean NOT NULL DEFAULT false,
|
||
deleted_time timestamp with time zone,
|
||
created_id integer REFERENCES sys_user(id) ON UPDATE CASCADE ON DELETE SET NULL,
|
||
updated_id integer REFERENCES sys_user(id) ON UPDATE CASCADE ON DELETE SET NULL,
|
||
deleted_id integer REFERENCES sys_user(id) ON UPDATE CASCADE ON DELETE SET NULL,
|
||
uuid varchar(64) NOT NULL
|
||
);
|
||
CREATE UNIQUE INDEX IF NOT EXISTS ix_bre_anova_result_uuid ON bre_anova_result (uuid);
|
||
CREATE INDEX IF NOT EXISTS ix_bre_anova_result_created_deleted ON bre_anova_result (created_time, is_deleted);
|
||
CREATE INDEX IF NOT EXISTS ix_bre_anova_result_created_id ON bre_anova_result (created_id);
|
||
|
||
-- ============ 砧木字典（B5，规格 §3.17） ============
|
||
CREATE TABLE IF NOT EXISTS bre_rootstock (
|
||
id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
||
rootstock_code varchar(32),
|
||
rootstock_name varchar(128) NOT NULL,
|
||
remark varchar(512),
|
||
created_time timestamp with time zone NOT NULL DEFAULT now(),
|
||
updated_time timestamp with time zone,
|
||
is_deleted boolean NOT NULL DEFAULT false,
|
||
deleted_time timestamp with time zone,
|
||
created_id integer REFERENCES sys_user(id) ON UPDATE CASCADE ON DELETE SET NULL,
|
||
updated_id integer REFERENCES sys_user(id) ON UPDATE CASCADE ON DELETE SET NULL,
|
||
deleted_id integer REFERENCES sys_user(id) ON UPDATE CASCADE ON DELETE SET NULL,
|
||
uuid varchar(64) NOT NULL
|
||
);
|
||
CREATE UNIQUE INDEX IF NOT EXISTS ix_bre_rootstock_uuid ON bre_rootstock (uuid);
|
||
CREATE INDEX IF NOT EXISTS ix_bre_rootstock_created_deleted ON bre_rootstock (created_time, is_deleted);
|
||
CREATE INDEX IF NOT EXISTS ix_bre_rootstock_created_id ON bre_rootstock (created_id);
|
||
|
||
-- ============ 入选克隆（B7，规格 §3.0） ============
|
||
CREATE TABLE IF NOT EXISTS bre_clone (
|
||
id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
||
clone_code varchar(32) NOT NULL,
|
||
combination_id integer NOT NULL REFERENCES bre_cross_combination(id),
|
||
female_parent_id integer REFERENCES bre_germplasm(id),
|
||
male_parent_id integer REFERENCES bre_germplasm(id),
|
||
planting_year integer,
|
||
generation varchar(16),
|
||
status varchar(1) NOT NULL DEFAULT '1',
|
||
remark varchar(512),
|
||
created_time timestamp with time zone NOT NULL DEFAULT now(),
|
||
updated_time timestamp with time zone,
|
||
is_deleted boolean NOT NULL DEFAULT false,
|
||
deleted_time timestamp with time zone,
|
||
created_id integer REFERENCES sys_user(id) ON UPDATE CASCADE ON DELETE SET NULL,
|
||
updated_id integer REFERENCES sys_user(id) ON UPDATE CASCADE ON DELETE SET NULL,
|
||
deleted_id integer REFERENCES sys_user(id) ON UPDATE CASCADE ON DELETE SET NULL,
|
||
uuid varchar(64) NOT NULL
|
||
);
|
||
CREATE UNIQUE INDEX IF NOT EXISTS uq_bre_clone_code ON bre_clone (clone_code);
|
||
CREATE UNIQUE INDEX IF NOT EXISTS ix_bre_clone_uuid ON bre_clone (uuid);
|
||
CREATE INDEX IF NOT EXISTS ix_bre_clone_created_deleted ON bre_clone (created_time, is_deleted);
|
||
CREATE INDEX IF NOT EXISTS ix_bre_clone_combination_id ON bre_clone (combination_id);
|
||
CREATE INDEX IF NOT EXISTS ix_bre_clone_created_id ON bre_clone (created_id);
|
||
|
||
-- ============ 现有表加列（均幂等） ============
|
||
-- tree：MET 维度 + 砧木 + clone
|
||
ALTER TABLE bre_tree ADD COLUMN IF NOT EXISTS trial_study_id integer REFERENCES bre_trial_study(id);
|
||
ALTER TABLE bre_tree ADD COLUMN IF NOT EXISTS block_no integer;
|
||
ALTER TABLE bre_tree ADD COLUMN IF NOT EXISTS rootstock_id integer REFERENCES bre_rootstock(id);
|
||
ALTER TABLE bre_tree ADD COLUMN IF NOT EXISTS clone_id integer REFERENCES bre_clone(id);
|
||
|
||
-- trait_observation：观测所属 study + 负载量协变量
|
||
ALTER TABLE bre_trait_observation ADD COLUMN IF NOT EXISTS trial_study_id integer REFERENCES bre_trial_study(id);
|
||
ALTER TABLE bre_trait_observation ADD COLUMN IF NOT EXISTS crop_load integer;
|
||
|
||
-- pedigree：递归闭环 dam/sire（规格 §3.18，A 矩阵唯一权威）
|
||
ALTER TABLE bre_pedigree ADD COLUMN IF NOT EXISTS dam_id integer REFERENCES bre_germplasm(id);
|
||
ALTER TABLE bre_pedigree ADD COLUMN IF NOT EXISTS sire_id integer REFERENCES bre_germplasm(id);
|
||
|
||
-- ============ 统一性状值视图（B6，规格 §5 第1条） ============
|
||
DROP VIEW IF EXISTS v_trait_value;
|
||
CREATE VIEW v_trait_value AS
|
||
SELECT o.id AS obs_id,
|
||
o.tree_id AS tree_id,
|
||
t.tree_no AS tree_no,
|
||
o.combination_id,
|
||
c.combination_code,
|
||
t.clone_id AS clone_id,
|
||
o.trait_id AS trait_id,
|
||
tr.trait_code,
|
||
tr.trait_name,
|
||
tr.unit AS unit,
|
||
o.evaluate_year,
|
||
o.value_numeric,
|
||
o.value_text,
|
||
o.crop_load AS crop_load,
|
||
o.trial_study_id
|
||
FROM bre_trait_observation o
|
||
LEFT JOIN bre_tree t ON o.tree_id = t.id
|
||
LEFT JOIN bre_cross_combination c ON o.combination_id = c.id
|
||
LEFT JOIN bre_trait tr ON o.trait_id = tr.id
|
||
WHERE o.is_deleted = false AND o.value_numeric IS NOT NULL;
|