60 lines
2.1 KiB
SQL
60 lines
2.1 KiB
SQL
-- 创建数据库
|
|
CREATE DATABASE IF NOT EXISTS project_management CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- 创建用户并授权
|
|
CREATE USER IF NOT EXISTS 'project_user'@'localhost' IDENTIFIED BY 'project_password';
|
|
GRANT ALL PRIVILEGES ON project_management.* TO 'project_user'@'localhost';
|
|
FLUSH PRIVILEGES;
|
|
|
|
-- 使用数据库
|
|
USE project_management;
|
|
|
|
-- 创建用户表
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(50) UNIQUE NOT NULL,
|
|
password VARCHAR(100) NOT NULL,
|
|
department VARCHAR(50) NOT NULL,
|
|
role VARCHAR(20) NOT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 创建项目表
|
|
CREATE TABLE IF NOT EXISTS projects (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
description TEXT,
|
|
created_by INT NOT NULL,
|
|
department VARCHAR(50) NOT NULL,
|
|
budget INT,
|
|
start_date DATETIME,
|
|
end_date DATETIME,
|
|
status VARCHAR(20) DEFAULT 'pending',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (created_by) REFERENCES users(id)
|
|
);
|
|
|
|
-- 创建项目历史记录表
|
|
CREATE TABLE IF NOT EXISTS project_histories (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
project_id INT NOT NULL,
|
|
changed_by INT NOT NULL,
|
|
change_field VARCHAR(100) NOT NULL,
|
|
old_value TEXT,
|
|
new_value TEXT,
|
|
change_description TEXT,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (project_id) REFERENCES projects(id),
|
|
FOREIGN KEY (changed_by) REFERENCES users(id)
|
|
);
|
|
|
|
-- 插入初始用户数据
|
|
INSERT INTO users (username, password, department, role) VALUES
|
|
('admin', '$2b$12$EixZaYVK1fsbw1ZfbX3OXePaWxn96p36WQoeG6Lruj3vjPGga31lW', 'IT', 'admin'),
|
|
('marketing', '$2b$12$EixZaYVK1fsbw1ZfbX3OXePaWxn96p36WQoeG6Lruj3vjPGga31lW', 'Marketing', 'marketing'),
|
|
('other', '$2b$12$EixZaYVK1fsbw1ZfbX3OXePaWxn96p36WQoeG6Lruj3vjPGga31lW', 'Other', 'other');
|
|
|
|
-- 密码说明：上面的密码是 '123456' 的 bcrypt 哈希值
|