165 lines
6.8 KiB
SQL
165 lines
6.8 KiB
SQL
-- =========================================================
|
||
-- variety.sql 🎉
|
||
-- 无物化视图 | 超可视提示 | 可重复执行
|
||
-- PostgreSQL 17.4+ 👍
|
||
-- =========================================================
|
||
|
||
-- 1️⃣ 开始 🚀
|
||
DO $$
|
||
BEGIN
|
||
RAISE NOTICE '🚀============ variety 部署开始 ============🚀';
|
||
END $$;
|
||
|
||
-- 2️⃣ 连接目标库
|
||
\c postgres;
|
||
|
||
-- 3️⃣ 表结构 -----------------------------------
|
||
DO $$
|
||
BEGIN
|
||
-- variety 主表
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'variety') THEN
|
||
CREATE TABLE variety (
|
||
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
||
deleted BOOLEAN NOT NULL DEFAULT FALSE,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
RAISE NOTICE '1️⃣✅ variety 主表已创建';
|
||
ELSE
|
||
RAISE NOTICE '1️⃣⏩ variety 主表已存在，跳过';
|
||
END IF;
|
||
|
||
-- variety_exchange
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'variety_exchange') THEN
|
||
CREATE TABLE variety_exchange (
|
||
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
||
variety_id UUID NOT NULL,
|
||
exchange_id VARCHAR(50) NOT NULL,
|
||
exchange_name VARCHAR(50) NOT NULL,
|
||
deleted BOOLEAN NOT NULL DEFAULT FALSE,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
CREATE TRIGGER update_variety_exchange_updated_at
|
||
BEFORE UPDATE ON variety_exchange
|
||
FOR EACH ROW EXECUTE FUNCTION update_data_modified_column();
|
||
RAISE NOTICE '2️⃣✅ variety_exchange 子表已创建';
|
||
ELSE
|
||
RAISE NOTICE '2️⃣⏩ variety_exchange 子表已存在，跳过';
|
||
END IF;
|
||
|
||
-- variety_name
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'variety_name') THEN
|
||
CREATE TABLE variety_name (
|
||
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
||
variety_id UUID NOT NULL,
|
||
name VARCHAR(50) NOT NULL,
|
||
deleted BOOLEAN NOT NULL DEFAULT FALSE,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
CREATE TRIGGER update_variety_name_updated_at
|
||
BEFORE UPDATE ON variety_name
|
||
FOR EACH ROW EXECUTE FUNCTION update_data_modified_column();
|
||
RAISE NOTICE '3️⃣✅ variety_name 子表已创建';
|
||
ELSE
|
||
RAISE NOTICE '3️⃣⏩ variety_name 子表已存在，跳过';
|
||
END IF;
|
||
|
||
-- variety_code
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'variety_code') THEN
|
||
CREATE TABLE variety_code (
|
||
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
||
variety_id UUID NOT NULL,
|
||
code VARCHAR(50) NOT NULL,
|
||
deleted BOOLEAN NOT NULL DEFAULT FALSE,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
CREATE TRIGGER update_variety_code_updated_at
|
||
BEFORE UPDATE ON variety_code
|
||
FOR EACH ROW EXECUTE FUNCTION update_data_modified_column();
|
||
RAISE NOTICE '4️⃣✅ variety_code 子表已创建';
|
||
ELSE
|
||
RAISE NOTICE '4️⃣⏩ variety_code 子表已存在，跳过';
|
||
END IF;
|
||
|
||
-- variety_tick
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'variety_tick') THEN
|
||
CREATE TABLE variety_tick (
|
||
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
||
variety_id UUID NOT NULL,
|
||
tick NUMERIC(12,6),
|
||
deleted BOOLEAN NOT NULL DEFAULT FALSE,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
CREATE TRIGGER update_variety_tick_updated_at
|
||
BEFORE UPDATE ON variety_tick
|
||
FOR EACH ROW EXECUTE FUNCTION update_data_modified_column();
|
||
RAISE NOTICE '5️⃣✅ variety_tick 子表已创建';
|
||
ELSE
|
||
RAISE NOTICE '5️⃣⏩ variety_tick 子表已存在，跳过';
|
||
END IF;
|
||
|
||
-- variety_tick_price
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'variety_tick_price') THEN
|
||
CREATE TABLE variety_tick_price (
|
||
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
||
variety_id UUID NOT NULL,
|
||
price NUMERIC(12,6),
|
||
deleted BOOLEAN NOT NULL DEFAULT FALSE,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
CREATE TRIGGER update_variety_tick_price_updated_at
|
||
BEFORE UPDATE ON variety_tick_price
|
||
FOR EACH ROW EXECUTE FUNCTION update_data_modified_column();
|
||
RAISE NOTICE '6️⃣✅ variety_tick_price 子表已创建';
|
||
ELSE
|
||
RAISE NOTICE '6️⃣⏩ variety_tick_price 子表已存在，跳过';
|
||
END IF;
|
||
END $$;
|
||
|
||
-- 4️⃣ 视图 ------------------------------------
|
||
DROP VIEW IF EXISTS variety_info_view;
|
||
CREATE OR REPLACE VIEW variety_info_view AS
|
||
SELECT
|
||
v.id AS variety_id,
|
||
vn.name,
|
||
vc.code,
|
||
ve.exchange_name,
|
||
format_numeric_to_original(vt.tick) AS tick,
|
||
format_numeric_to_original(vtp.price) AS tick_price,
|
||
vt.tick AS tick_original,
|
||
vtp.price AS tick_price_original
|
||
FROM variety v
|
||
LEFT JOIN variety_name vn ON v.id = vn.variety_id AND vn.deleted = FALSE
|
||
LEFT JOIN variety_code vc ON v.id = vc.variety_id AND vc.deleted = FALSE
|
||
LEFT JOIN variety_exchange ve ON v.id = ve.variety_id AND ve.deleted = FALSE
|
||
LEFT JOIN variety_tick vt ON v.id = vt.variety_id AND vt.deleted = FALSE
|
||
LEFT JOIN variety_tick_price vtp ON v.id = vtp.variety_id AND vtp.deleted = FALSE
|
||
WHERE v.deleted = FALSE;
|
||
|
||
DO $$
|
||
BEGIN
|
||
RAISE NOTICE '7️⃣✅ variety_info_view 已创建/更新';
|
||
END $$;
|
||
|
||
-- 5️⃣ 性能索引 ------------------------------------------------
|
||
CREATE INDEX IF NOT EXISTS idx_variety_exchange_variety_id_deleted ON variety_exchange(variety_id, deleted);
|
||
CREATE INDEX IF NOT EXISTS idx_variety_name_variety_id_deleted ON variety_name(variety_id, deleted);
|
||
CREATE INDEX IF NOT EXISTS idx_variety_code_variety_id_deleted ON variety_code(variety_id, deleted);
|
||
CREATE INDEX IF NOT EXISTS idx_variety_tick_variety_id_deleted ON variety_tick(variety_id, deleted);
|
||
CREATE INDEX IF NOT EXISTS idx_variety_tick_price_variety_id_deleted ON variety_tick_price(variety_id, deleted);
|
||
|
||
DO $$
|
||
BEGIN
|
||
RAISE NOTICE '8️⃣✅ 全部索引已确保存在';
|
||
END $$;
|
||
|
||
-- 6️⃣ 完成 🎉
|
||
DO $$
|
||
BEGIN
|
||
RAISE NOTICE '🎉============ variety 部署完成 ============🎉';
|
||
END $$; |