张翔
68 lines
2.6 KiB
PL/PgSQL
68 lines
2.6 KiB
PL/PgSQL
-- 测试数据库初始化脚本
|
|
|
|
-- 创建测试用户
|
|
CREATE OR REPLACE FUNCTION create_test_users() RETURNS VOID AS $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM sys_user WHERE username = 'test_admin') THEN
|
|
INSERT INTO sys_user (username, password, email, role, status, created_at, updated_at)
|
|
VALUES ('test_admin', '$2a$10$N.zmdr9k7uOCQb376NoUnuTJ8iAt6Z5EHsM8lE9lBOsl7iKTVKIUi', 'test_admin@example.com', 'ADMIN', 'ACTIVE', NOW(), NOW());
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys_user WHERE username = 'test_user') THEN
|
|
INSERT INTO sys_user (username, password, email, role, status, created_at, updated_at)
|
|
VALUES ('test_user', '$2a$10$N.zmdr9k7uOCQb376NoUnuTJ8iAt6Z5EHsM8lE9lBOsl7iKTVKIUi', 'test_user@example.com', 'USER', 'ACTIVE', NOW(), NOW());
|
|
END IF;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- 创建测试数据
|
|
CREATE OR REPLACE FUNCTION create_test_data() RETURNS VOID AS $$
|
|
BEGIN
|
|
-- 创建测试黄历数据
|
|
IF NOT EXISTS (SELECT 1 FROM almanac WHERE date = '2026-02-25') THEN
|
|
INSERT INTO almanac (date, year, month, day, lunar_year, lunar_month, lunar_day, ganzhi_year, ganzhi_month, ganzhi_day, created_at, updated_at)
|
|
VALUES ('2026-02-25', 2026, 2, 25, 2026, 1, 8, '丙午', '庚寅', '戊子', NOW(), NOW());
|
|
END IF;
|
|
|
|
-- 创建测试紫微斗数数据
|
|
IF NOT EXISTS (SELECT 1 FROM ziwei_chart WHERE user_id = 1) THEN
|
|
INSERT INTO ziwei_chart (user_id, birth_date, birth_time, gender, chart_data, created_at, updated_at)
|
|
VALUES (1, '1990-01-01', '12:00:00', 'MALE', '{"palaces": []}'::jsonb, NOW(), NOW());
|
|
END IF;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- 执行初始化
|
|
SELECT create_test_users();
|
|
SELECT create_test_data();
|
|
|
|
-- 创建测试视图
|
|
CREATE OR REPLACE VIEW test_user_summary AS
|
|
SELECT
|
|
id,
|
|
username,
|
|
email,
|
|
role,
|
|
status,
|
|
created_at
|
|
FROM sys_user
|
|
WHERE username LIKE 'test_%';
|
|
|
|
-- 创建测试存储过程
|
|
CREATE OR REPLACE PROCEDURE reset_test_data()
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
DELETE FROM fortune_history WHERE user_id IN (SELECT id FROM sys_user WHERE username LIKE 'test_%');
|
|
DELETE FROM ziwei_chart WHERE user_id IN (SELECT id FROM sys_user WHERE username LIKE 'test_%');
|
|
END;
|
|
$$;
|
|
|
|
GRANT EXECUTE ON PROCEDURE reset_test_data TO ${TEST_DB_USER:-test_user};
|
|
GRANT SELECT ON test_user_summary TO ${TEST_DB_USER:-test_user};
|
|
|
|
COMMENT ON FUNCTION create_test_users() IS '创建测试用户';
|
|
COMMENT ON FUNCTION create_test_data() IS '创建测试数据';
|
|
COMMENT ON PROCEDURE reset_test_data() IS '重置测试数据';
|
|
COMMENT ON VIEW test_user_summary IS '测试用户摘要视图';
|