209 lines
11 KiB
PL/PgSQL
209 lines
11 KiB
PL/PgSQL
------------------------------- 数据库初始化 -------------------------------
|
||
-- 删除现有数据库并新建UTF8编码数据库
|
||
DROP DATABASE IF EXISTS jingsaisystem;
|
||
CREATE DATABASE jingsaisystem ENCODING 'UTF8';
|
||
|
||
------------------------------- 禁用隐式提交 -------------------------------
|
||
BEGIN;
|
||
|
||
------------------------------- 建表语句 (兼容PostgreSQL语法) -------------------------------
|
||
-- AI竞赛助手聊天记录表
|
||
CREATE TABLE ai_competition_chat (
|
||
chat_id BIGSERIAL PRIMARY KEY,
|
||
user_id BIGINT NOT NULL,
|
||
user_question TEXT NOT NULL,
|
||
ai_response TEXT NOT NULL,
|
||
chat_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
competition_type VARCHAR(50),
|
||
is_deleted SMALLINT DEFAULT 0
|
||
);
|
||
COMMENT ON TABLE ai_competition_chat IS 'AI竞赛助手聊天记录表';
|
||
COMMENT ON COLUMN ai_competition_chat.chat_id IS '会话ID';
|
||
COMMENT ON COLUMN ai_competition_chat.is_deleted IS '是否删除(0-未删除，1-已删除)';
|
||
|
||
-- 文章表
|
||
CREATE TABLE article_table (
|
||
article_id BIGSERIAL PRIMARY KEY,
|
||
user_id BIGINT NOT NULL,
|
||
article_title TEXT NOT NULL,
|
||
brief_content TEXT,
|
||
article_content TEXT NOT NULL,
|
||
competition_id BIGINT NOT NULL,
|
||
article_type VARCHAR(50) NOT NULL,
|
||
publish_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- 学院表
|
||
CREATE TABLE college_table (
|
||
college_id BIGSERIAL PRIMARY KEY,
|
||
college_name VARCHAR(255)
|
||
);
|
||
|
||
-- 评论表
|
||
CREATE TABLE comment_table (
|
||
comment_id BIGSERIAL PRIMARY KEY,
|
||
user_id BIGINT NOT NULL,
|
||
article_id BIGINT NOT NULL,
|
||
comment_content TEXT NOT NULL,
|
||
publish_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- 竞赛表（使用微秒时间精度）
|
||
CREATE TABLE competition_table (
|
||
competition_id BIGSERIAL PRIMARY KEY,
|
||
competition_name VARCHAR(50) NOT NULL,
|
||
user_id BIGINT NOT NULL,
|
||
registration_start_time TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3),
|
||
registration_end_time TIMESTAMP(3),
|
||
announcement_link VARCHAR(255),
|
||
competition_status INT DEFAULT 0
|
||
);
|
||
|
||
-- 好友表
|
||
CREATE TABLE friend (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
user_id BIGINT,
|
||
friend_id BIGINT,
|
||
friend_name VARCHAR(20),
|
||
delete_flag SMALLINT DEFAULT 1
|
||
);
|
||
|
||
-- 信息表
|
||
CREATE TABLE information_table (
|
||
profile_id BIGSERIAL PRIMARY KEY,
|
||
user_id BIGINT NOT NULL,
|
||
competition_id BIGINT NOT NULL,
|
||
personal_info TEXT,
|
||
related_data TEXT
|
||
);
|
||
|
||
-- 专业表
|
||
CREATE TABLE major_table (
|
||
major_table_id BIGSERIAL PRIMARY KEY,
|
||
major_table_name VARCHAR(255),
|
||
college_id BIGINT
|
||
);
|
||
|
||
-- 消息表
|
||
CREATE TABLE message (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
session_id BIGINT,
|
||
user_id BIGINT,
|
||
content TEXT,
|
||
create_time TIMESTAMP
|
||
);
|
||
|
||
-- 报名表（MyISAM转换）
|
||
CREATE TABLE registration_table (
|
||
registration_id SERIAL PRIMARY KEY,
|
||
student_id INT NOT NULL,
|
||
competition_id INT NOT NULL,
|
||
team_leader_id INT,
|
||
competition_type VARCHAR(50) DEFAULT '校级',
|
||
award_level VARCHAR(50),
|
||
registration_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
registration_status VARCHAR(50) DEFAULT '等待学校审核',
|
||
certificate_path VARCHAR(255),
|
||
additional_info VARCHAR(255)
|
||
);
|
||
|
||
-- 会话信息表
|
||
CREATE TABLE session_massage (
|
||
id SERIAL PRIMARY KEY,
|
||
last_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- 用户会话关系表
|
||
CREATE TABLE user_session (
|
||
id SERIAL PRIMARY KEY,
|
||
user_id INT,
|
||
session_id INT
|
||
);
|
||
|
||
-- 用户主表（调整自增方式）
|
||
CREATE TABLE user_table (
|
||
user_id SERIAL PRIMARY KEY,
|
||
user_name VARCHAR(50) NOT NULL,
|
||
s_t_id VARCHAR(20) NOT NULL,
|
||
user_password VARCHAR(255) NOT NULL,
|
||
user_privileges INT DEFAULT 1,
|
||
email VARCHAR(50) NOT NULL,
|
||
college_id INT NOT NULL,
|
||
major_table_id INT,
|
||
phone VARCHAR(20),
|
||
teacher_title VARCHAR(50),
|
||
birthdate TIMESTAMP,
|
||
gender VARCHAR(10),
|
||
user_status INT DEFAULT 0,
|
||
position VARCHAR(50)
|
||
);
|
||
|
||
-- 用户副本表（仅供示例）
|
||
CREATE TABLE user_table_renaissance (LIKE user_table INCLUDING ALL);
|
||
|
||
------------------------------- 数据插入 -------------------------------
|
||
-- 文章表数据
|
||
INSERT INTO article_table (user_id, article_title, brief_content, article_content, competition_id, article_type)
|
||
VALUES (1, '计算机设计大赛', '666', '666', 1, '公告');
|
||
|
||
-- 学院数据批量插入
|
||
INSERT INTO college_table (college_name) VALUES
|
||
(NULL), ('会计学院'), ('经济贸易学院'), ('电信工程学院'),
|
||
('服装与技术贸易学院'), ('医药工程学院'), ('人文与艺术学院'),
|
||
('计算机学院'), ('国际教育学院'), ('镐京书院'),
|
||
('外语教育部'), ('思政部'), ('体育部');
|
||
ALTER SEQUENCE college_table_college_id_seq RESTART WITH 14;
|
||
|
||
-- 评论数据
|
||
INSERT INTO comment_table (user_id, article_id, comment_content)
|
||
VALUES (276, 64, '1111');
|
||
|
||
-- 竞赛数据
|
||
INSERT INTO competition_table (competition_name, user_id, registration_start_time, registration_end_time, announcement_link) VALUES
|
||
('挑战杯', 277, '2024-06-19 22:19:25.000', '2024-06-27 22:19:28.000', '中国大学生创业计划大赛'),
|
||
('互联网+', 277, '2024-04-09 22:38:21.000', '2024-04-25 14:38:23.000', '中国国际互联网+'),
|
||
('天梯赛', 277, '2024-04-25 14:03:02.000', '2024-04-26 14:03:04.000', '111');
|
||
|
||
-- 好友数据
|
||
INSERT INTO friend (user_id, friend_id, friend_name) VALUES (1, 2, 'zhangsan');
|
||
|
||
-- 个人资料数据
|
||
INSERT INTO information_table (user_id, competition_id, personal_info, related_data) VALUES
|
||
(100, 29, 'http://cloudreveplus.oss-cn-beijing.aliyuncs.com/2024/06/22344f8a88044e4a4e906340480dee7e1a奖状.png', '这是一段文件描述');
|
||
|
||
-- 专业数据批量插入
|
||
INSERT INTO major_table (major_table_name, college_id) VALUES
|
||
('税收学', 2), ('会计学', 2), ('审计学', 2), ('金融工程', 2), ('财务管理', 2),
|
||
('国际经济与贸易', 3), ('市场营销', 3), ('工商管理', 3), ('人力资源管理', 3),
|
||
('行政管理', 3), ('电子商务', 3), ('电气工程及其自动化', 4), ('电子信息工程', 4),
|
||
('服装设计', 5), ('管理运营', 5), ('服装科技', 5), ('健康服务与管理', 7),
|
||
('药物制剂', 7), ('计算机科学与技术', 8), ('网络工程', 8), ('物联网工程', 8), ('智能科学与技术', 8);
|
||
ALTER SEQUENCE major_table_major_table_id_seq RESTART WITH 24;
|
||
|
||
-- 消息数据（修正年份错误）
|
||
INSERT INTO message (session_id, user_id, content, create_time)
|
||
VALUES (1, 2, '随便', '2005-05-01 17:09:00'); -- 原数据年份2000改为2005
|
||
|
||
-- 报名数据
|
||
INSERT INTO registration_table (student_id, competition_id, team_leader_id, competition_type)
|
||
VALUES (276, 9, 33, '院级');
|
||
|
||
-- 用户数据（密码SHA256加密）
|
||
INSERT INTO user_table (user_name, s_t_id, user_password, user_privileges, email, college_id, phone) VALUES
|
||
('学生用户1', 'student', '36ab5e3ebd13982b1237194172459925b32b9963aef34a68b4d74f53c694d44e', 2, '1270540423@qq.com', 7, '17791641989'),
|
||
('Admin', 'Admin', '36ab5e3ebd13982b1237194172459925b32b9963aef34a68b4d74f53c694d44e', 0, 'wwww', 4, '17791641989');
|
||
|
||
------------------------------- 索引构建 -------------------------------
|
||
CREATE INDEX idx_ai_user_time ON ai_competition_chat (user_id, chat_time);
|
||
CREATE INDEX article_user_idx ON article_table (user_id);
|
||
CREATE INDEX article_competition_idx ON article_table (competition_id);
|
||
ALTER TABLE comment_table ADD CONSTRAINT fk_article_comment FOREIGN KEY (article_id) REFERENCES article_table(article_id);
|
||
|
||
------------------------------- 事务提交 -------------------------------
|
||
COMMIT;
|
||
|
||
------------------------------- 权限校验（参考语句） -------------------------------
|
||
-- 创建用户并授权（根据实际需求添加）
|
||
-- CREATE USER app_user WITH PASSWORD 'secure_password';
|
||
-- GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_user;
|