77 lines
2.5 KiB
SQL
77 lines
2.5 KiB
SQL
-- init.sql
|
|
-- 用户表
|
|
CREATE TABLE users (
|
|
id INT PRIMARY KEY AUTO_INCREMENT COMMENT "用户id",
|
|
username VARCHAR(50) UNIQUE NOT NULL COMMENT "用户名",
|
|
password VARCHAR(255) NOT NULL COMMENT "密码",
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT "创建时间",
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT "更新时间"
|
|
);
|
|
|
|
-- 角色表
|
|
CREATE TABLE roles (
|
|
id INT PRIMARY KEY AUTO_INCREMENT COMMENT "角色id",
|
|
name VARCHAR(50) UNIQUE NOT NULL COMMENT "角色名称",
|
|
description TEXT COMMENT "角色描述"
|
|
);
|
|
|
|
-- 权限表
|
|
CREATE TABLE permissions (
|
|
id INT PRIMARY KEY AUTO_INCREMENT COMMENT "权限id",
|
|
name VARCHAR(100) UNIQUE NOT NULL COMMENT "权限名称",
|
|
description TEXT COMMENT "权限描述"
|
|
);
|
|
|
|
-- 用户-角色关联表
|
|
CREATE TABLE user_roles (
|
|
user_id INT COMMENT "用户id",
|
|
role_id INT COMMENT "角色id",
|
|
PRIMARY KEY (user_id, role_id),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- 角色-权限关联表
|
|
CREATE TABLE role_permissions (
|
|
role_id INT COMMENT "角色id",
|
|
permission_id INT COMMENT "权限id",
|
|
PRIMARY KEY (role_id, permission_id),
|
|
FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- 商品表
|
|
CREATE TABLE products (
|
|
id INT PRIMARY KEY AUTO_INCREMENT COMMENT "商品id",
|
|
name VARCHAR(255) NOT NULL COMMENT "商品名称",
|
|
description TEXT COMMENT "商品描述",
|
|
price DECIMAL(10, 2) NOT NULL COMMENT "商品价格",
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT "创建时间",
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT "更新时间"
|
|
);
|
|
|
|
-- 插入默认角色
|
|
INSERT INTO roles (name, description) VALUES
|
|
('USER', '普通用户'),
|
|
('ADMIN', '管理员');
|
|
|
|
-- 插入默认权限
|
|
INSERT INTO permissions (name, description) VALUES
|
|
('PRODUCT_READ', '读取商品信息'),
|
|
('PRODUCT_CREATE', '创建商品'),
|
|
('PRODUCT_UPDATE', '更新商品'),
|
|
('PRODUCT_DELETE', '删除商品');
|
|
|
|
-- 给 ADMIN 角色分配所有权限
|
|
INSERT INTO role_permissions (role_id, permission_id)
|
|
SELECT r.id, p.id
|
|
FROM roles r, permissions p
|
|
WHERE r.name = 'ADMIN';
|
|
|
|
|
|
-- 给 USER 角色分配读取权限
|
|
INSERT INTO role_permissions (role_id, permission_id)
|
|
SELECT r.id, p.id
|
|
FROM roles r, permissions p
|
|
WHERE r.name = 'USER' and p.name = 'PRODUCT_READ'
|