398 lines
14 KiB
Transact-SQL
398 lines
14 KiB
Transact-SQL
-- =============================================
|
|
-- 文件管理系统 - SQL Server 初始化脚本
|
|
-- =============================================
|
|
|
|
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'seeyon_fs')
|
|
BEGIN
|
|
CREATE DATABASE seeyon_fs;
|
|
END
|
|
GO
|
|
|
|
USE seeyon_fs;
|
|
GO
|
|
|
|
-- ========== 用户与权限 ==========
|
|
|
|
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'fs_user')
|
|
CREATE TABLE fs_user (
|
|
id BIGINT PRIMARY KEY,
|
|
username NVARCHAR(64) UNIQUE NOT NULL,
|
|
password NVARCHAR(256) NOT NULL,
|
|
email NVARCHAR(128),
|
|
nickname NVARCHAR(64),
|
|
avatar NVARCHAR(512),
|
|
role NVARCHAR(32) DEFAULT 'user',
|
|
dept_id BIGINT DEFAULT 0,
|
|
status TINYINT DEFAULT 1,
|
|
storage_quota BIGINT DEFAULT 10737418240,
|
|
used_storage BIGINT DEFAULT 0,
|
|
must_change_pwd TINYINT DEFAULT 0,
|
|
created_at DATETIME2 DEFAULT GETDATE(),
|
|
updated_at DATETIME2 DEFAULT GETDATE()
|
|
);
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'fs_role')
|
|
CREATE TABLE fs_role (
|
|
id BIGINT PRIMARY KEY,
|
|
name NVARCHAR(64) UNIQUE NOT NULL,
|
|
display_name NVARCHAR(64) NOT NULL,
|
|
description NVARCHAR(256),
|
|
is_system TINYINT DEFAULT 0,
|
|
status TINYINT DEFAULT 1,
|
|
created_at DATETIME2 DEFAULT GETDATE(),
|
|
updated_at DATETIME2 DEFAULT GETDATE()
|
|
);
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'fs_permission')
|
|
CREATE TABLE fs_permission (
|
|
id BIGINT PRIMARY KEY,
|
|
code NVARCHAR(64) UNIQUE NOT NULL,
|
|
name NVARCHAR(64) NOT NULL,
|
|
resource NVARCHAR(32) NOT NULL,
|
|
action NVARCHAR(32) NOT NULL,
|
|
description NVARCHAR(256)
|
|
);
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'fs_role_permission')
|
|
CREATE TABLE fs_role_permission (
|
|
role_id BIGINT NOT NULL,
|
|
permission_id BIGINT NOT NULL,
|
|
PRIMARY KEY (role_id, permission_id)
|
|
);
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'fs_user_role')
|
|
CREATE TABLE fs_user_role (
|
|
user_id BIGINT NOT NULL,
|
|
role_id BIGINT NOT NULL,
|
|
PRIMARY KEY (user_id, role_id)
|
|
);
|
|
GO
|
|
|
|
-- ========== 组织架构 ==========
|
|
|
|
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'fs_department')
|
|
CREATE TABLE fs_department (
|
|
id BIGINT PRIMARY KEY,
|
|
name NVARCHAR(128) NOT NULL,
|
|
code NVARCHAR(64) UNIQUE,
|
|
parent_id BIGINT DEFAULT 0,
|
|
path NVARCHAR(1024) NOT NULL,
|
|
leader_id BIGINT DEFAULT 0,
|
|
sort_order INT DEFAULT 0,
|
|
status TINYINT DEFAULT 1,
|
|
created_at DATETIME2 DEFAULT GETDATE(),
|
|
updated_at DATETIME2 DEFAULT GETDATE()
|
|
);
|
|
GO
|
|
|
|
-- ========== SSO ==========
|
|
|
|
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'fs_sso_provider')
|
|
CREATE TABLE fs_sso_provider (
|
|
id BIGINT PRIMARY KEY,
|
|
name NVARCHAR(64) NOT NULL,
|
|
type NVARCHAR(32) NOT NULL,
|
|
display_name NVARCHAR(64) NOT NULL,
|
|
icon NVARCHAR(512),
|
|
config NVARCHAR(MAX),
|
|
auto_create TINYINT DEFAULT 1,
|
|
default_role NVARCHAR(32) DEFAULT 'user',
|
|
status TINYINT DEFAULT 1,
|
|
sort_order INT DEFAULT 0,
|
|
created_at DATETIME2 DEFAULT GETDATE(),
|
|
updated_at DATETIME2 DEFAULT GETDATE()
|
|
);
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'fs_sso_session')
|
|
CREATE TABLE fs_sso_session (
|
|
id BIGINT PRIMARY KEY,
|
|
user_id BIGINT NOT NULL,
|
|
provider_id BIGINT NOT NULL,
|
|
external_id NVARCHAR(256) NOT NULL,
|
|
access_token NVARCHAR(1024),
|
|
refresh_token NVARCHAR(1024),
|
|
expires_at DATETIME2,
|
|
last_login_at DATETIME2,
|
|
created_at DATETIME2 DEFAULT GETDATE()
|
|
);
|
|
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_sso_user')
|
|
CREATE INDEX idx_sso_user ON fs_sso_session(user_id);
|
|
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_sso_provider_ext')
|
|
CREATE INDEX idx_sso_provider_ext ON fs_sso_session(provider_id, external_id);
|
|
GO
|
|
|
|
-- ========== 文件管理 ==========
|
|
|
|
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'fs_folder')
|
|
CREATE TABLE fs_folder (
|
|
id BIGINT PRIMARY KEY,
|
|
biz_id NVARCHAR(128) DEFAULT '',
|
|
name NVARCHAR(256) NOT NULL,
|
|
parent_id BIGINT DEFAULT 0,
|
|
owner_id BIGINT NOT NULL,
|
|
path NVARCHAR(1024) NOT NULL,
|
|
status TINYINT DEFAULT 1,
|
|
version INT DEFAULT 1,
|
|
created_at DATETIME2 DEFAULT GETDATE(),
|
|
updated_at DATETIME2 DEFAULT GETDATE()
|
|
);
|
|
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_folder_parent')
|
|
CREATE INDEX idx_folder_parent ON fs_folder(parent_id);
|
|
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_folder_owner')
|
|
CREATE INDEX idx_folder_owner ON fs_folder(owner_id);
|
|
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_folder_biz')
|
|
CREATE INDEX idx_folder_biz ON fs_folder(biz_id);
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'fs_file')
|
|
CREATE TABLE fs_file (
|
|
id BIGINT PRIMARY KEY,
|
|
biz_id NVARCHAR(128) DEFAULT '',
|
|
name NVARCHAR(256) NOT NULL,
|
|
extension NVARCHAR(32),
|
|
folder_id BIGINT NOT NULL,
|
|
owner_id BIGINT NOT NULL,
|
|
size BIGINT DEFAULT 0,
|
|
md5 NVARCHAR(32),
|
|
sha256 NVARCHAR(64),
|
|
storage_key NVARCHAR(512) NOT NULL,
|
|
storage_policy_id BIGINT DEFAULT 1,
|
|
mime_type NVARCHAR(128),
|
|
is_favorite TINYINT DEFAULT 0,
|
|
status TINYINT DEFAULT 1,
|
|
download_count INT DEFAULT 0,
|
|
version INT DEFAULT 1,
|
|
created_at DATETIME2 DEFAULT GETDATE(),
|
|
updated_at DATETIME2 DEFAULT GETDATE()
|
|
);
|
|
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_file_biz')
|
|
CREATE INDEX idx_file_biz ON fs_file(biz_id);
|
|
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_file_folder')
|
|
CREATE INDEX idx_file_folder ON fs_file(folder_id);
|
|
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_file_owner')
|
|
CREATE INDEX idx_file_owner ON fs_file(owner_id);
|
|
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_file_md5')
|
|
CREATE INDEX idx_file_md5 ON fs_file(md5);
|
|
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_file_policy')
|
|
CREATE INDEX idx_file_policy ON fs_file(storage_policy_id);
|
|
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_file_status')
|
|
CREATE INDEX idx_file_status ON fs_file(status);
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'fs_file_permission')
|
|
CREATE TABLE fs_file_permission (
|
|
id BIGINT PRIMARY KEY,
|
|
file_id BIGINT NOT NULL,
|
|
user_id BIGINT DEFAULT 0,
|
|
role_id BIGINT DEFAULT 0,
|
|
perm_level NVARCHAR(16) DEFAULT 'read',
|
|
grant_by BIGINT NOT NULL,
|
|
created_at DATETIME2 DEFAULT GETDATE()
|
|
);
|
|
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_fp_user_file')
|
|
CREATE INDEX idx_fp_user_file ON fs_file_permission(user_id, file_id);
|
|
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_fp_role_file')
|
|
CREATE INDEX idx_fp_role_file ON fs_file_permission(role_id, file_id);
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'fs_file_association')
|
|
CREATE TABLE fs_file_association (
|
|
id BIGINT PRIMARY KEY,
|
|
biz_id NVARCHAR(128) NOT NULL,
|
|
biz_type NVARCHAR(64) NOT NULL,
|
|
file_id BIGINT NOT NULL,
|
|
created_at DATETIME2 DEFAULT GETDATE()
|
|
);
|
|
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_assoc_biz')
|
|
CREATE INDEX idx_assoc_biz ON fs_file_association(biz_id, biz_type);
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'fs_share')
|
|
CREATE TABLE fs_share (
|
|
id BIGINT PRIMARY KEY,
|
|
file_id BIGINT NOT NULL,
|
|
owner_id BIGINT NOT NULL,
|
|
share_code NVARCHAR(32) UNIQUE NOT NULL,
|
|
password NVARCHAR(64),
|
|
expire_at DATETIME2,
|
|
max_download INT DEFAULT 0,
|
|
download_count INT DEFAULT 0,
|
|
allow_preview TINYINT DEFAULT 1,
|
|
allow_download TINYINT DEFAULT 1,
|
|
status TINYINT DEFAULT 1,
|
|
created_at DATETIME2 DEFAULT GETDATE()
|
|
);
|
|
GO
|
|
|
|
-- ========== 存储策略 ==========
|
|
|
|
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'fs_storage_policy')
|
|
CREATE TABLE fs_storage_policy (
|
|
id BIGINT PRIMARY KEY,
|
|
name NVARCHAR(64) NOT NULL,
|
|
type NVARCHAR(32) NOT NULL,
|
|
policy_type NVARCHAR(32) DEFAULT 'hot',
|
|
config NVARCHAR(MAX),
|
|
status TINYINT DEFAULT 1,
|
|
created_at DATETIME2 DEFAULT GETDATE()
|
|
);
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'fs_storage_assignment')
|
|
CREATE TABLE fs_storage_assignment (
|
|
id BIGINT PRIMARY KEY,
|
|
policy_id BIGINT NOT NULL,
|
|
user_id BIGINT DEFAULT 0,
|
|
group_id BIGINT DEFAULT 0,
|
|
is_default TINYINT DEFAULT 0,
|
|
priority INT DEFAULT 0,
|
|
storage_quota BIGINT DEFAULT 0,
|
|
access_mode NVARCHAR(32),
|
|
status TINYINT DEFAULT 1,
|
|
created_at DATETIME2 DEFAULT GETDATE(),
|
|
updated_at DATETIME2 DEFAULT GETDATE()
|
|
);
|
|
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_assign_policy')
|
|
CREATE INDEX idx_assign_policy ON fs_storage_assignment(policy_id);
|
|
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_assign_user')
|
|
CREATE INDEX idx_assign_user ON fs_storage_assignment(user_id);
|
|
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_assign_group')
|
|
CREATE INDEX idx_assign_group ON fs_storage_assignment(group_id);
|
|
GO
|
|
|
|
-- ========== 开放平台 ==========
|
|
|
|
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'fs_app')
|
|
CREATE TABLE fs_app (
|
|
id BIGINT PRIMARY KEY,
|
|
app_id NVARCHAR(64) UNIQUE NOT NULL,
|
|
app_secret NVARCHAR(128) NOT NULL,
|
|
app_name NVARCHAR(128) NOT NULL,
|
|
status TINYINT DEFAULT 1,
|
|
created_at DATETIME2 DEFAULT GETDATE(),
|
|
updated_at DATETIME2 DEFAULT GETDATE()
|
|
);
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'fs_app_user')
|
|
CREATE TABLE fs_app_user (
|
|
id BIGINT PRIMARY KEY,
|
|
app_id BIGINT NOT NULL,
|
|
user_id BIGINT NOT NULL,
|
|
created_at DATETIME2 DEFAULT GETDATE()
|
|
);
|
|
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_app_user_app')
|
|
CREATE INDEX idx_app_user_app ON fs_app_user(app_id);
|
|
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_app_user_user')
|
|
CREATE INDEX idx_app_user_user ON fs_app_user(user_id);
|
|
GO
|
|
|
|
-- ========== 分片上传 ==========
|
|
|
|
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'fs_upload_session')
|
|
CREATE TABLE fs_upload_session (
|
|
id NVARCHAR(64) PRIMARY KEY,
|
|
file_id BIGINT DEFAULT 0,
|
|
file_name NVARCHAR(256) NOT NULL,
|
|
file_size BIGINT NOT NULL,
|
|
chunk_size BIGINT NOT NULL,
|
|
total_chunks INT NOT NULL,
|
|
uploaded_chunks NVARCHAR(MAX),
|
|
md5 NVARCHAR(32),
|
|
sha256 NVARCHAR(64),
|
|
folder_id BIGINT NOT NULL,
|
|
owner_id BIGINT NOT NULL,
|
|
policy_id BIGINT DEFAULT 1,
|
|
status TINYINT DEFAULT 1,
|
|
temp_dir NVARCHAR(512),
|
|
created_at DATETIME2 DEFAULT GETDATE(),
|
|
updated_at DATETIME2 DEFAULT GETDATE()
|
|
);
|
|
GO
|
|
|
|
-- ========== 用户会话(多端登录) ==========
|
|
|
|
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'fs_user_session')
|
|
CREATE TABLE fs_user_session (
|
|
id BIGINT IDENTITY(1,1) PRIMARY KEY,
|
|
user_id BIGINT NOT NULL,
|
|
token NVARCHAR(512) NOT NULL,
|
|
device NVARCHAR(128),
|
|
ip NVARCHAR(64),
|
|
expires_at DATETIME2 NOT NULL,
|
|
created_at DATETIME2 DEFAULT GETDATE()
|
|
);
|
|
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_session_user')
|
|
CREATE INDEX idx_session_user ON fs_user_session(user_id);
|
|
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_session_expires')
|
|
CREATE INDEX idx_session_expires ON fs_user_session(expires_at);
|
|
GO
|
|
|
|
-- ========== 操作日志 ==========
|
|
|
|
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'fs_operation_log')
|
|
CREATE TABLE fs_operation_log (
|
|
id BIGINT PRIMARY KEY,
|
|
user_id BIGINT DEFAULT 0,
|
|
username NVARCHAR(64),
|
|
action NVARCHAR(32) NOT NULL,
|
|
resource NVARCHAR(32),
|
|
detail NVARCHAR(512),
|
|
ip NVARCHAR(64),
|
|
user_agent NVARCHAR(256),
|
|
status INT DEFAULT 1,
|
|
created_at DATETIME2 DEFAULT GETDATE()
|
|
);
|
|
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_log_user')
|
|
CREATE INDEX idx_log_user ON fs_operation_log(user_id);
|
|
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_log_action')
|
|
CREATE INDEX idx_log_action ON fs_operation_log(action);
|
|
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_log_created')
|
|
CREATE INDEX idx_log_created ON fs_operation_log(created_at);
|
|
GO
|
|
|
|
-- ========== 备份 ==========
|
|
|
|
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'fs_backup_policy')
|
|
CREATE TABLE fs_backup_policy (
|
|
id BIGINT PRIMARY KEY,
|
|
name NVARCHAR(128) NOT NULL,
|
|
backup_type NVARCHAR(32) NOT NULL,
|
|
target_type NVARCHAR(32) NOT NULL,
|
|
target_config NVARCHAR(MAX),
|
|
include_db TINYINT DEFAULT 1,
|
|
include_files TINYINT DEFAULT 1,
|
|
cron_expr NVARCHAR(64),
|
|
max_backups INT DEFAULT 7,
|
|
status TINYINT DEFAULT 1,
|
|
last_run DATETIME2,
|
|
created_at DATETIME2 DEFAULT GETDATE()
|
|
);
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'fs_backup_log')
|
|
CREATE TABLE fs_backup_log (
|
|
id BIGINT PRIMARY KEY,
|
|
policy_id BIGINT NOT NULL,
|
|
policy_name NVARCHAR(128),
|
|
backup_type NVARCHAR(32),
|
|
status NVARCHAR(32) DEFAULT 'running',
|
|
file_path NVARCHAR(512),
|
|
file_size BIGINT DEFAULT 0,
|
|
db_size BIGINT DEFAULT 0,
|
|
files_count INT DEFAULT 0,
|
|
error_msg NVARCHAR(512),
|
|
started_at DATETIME2,
|
|
finished_at DATETIME2,
|
|
duration INT DEFAULT 0,
|
|
created_at DATETIME2 DEFAULT GETDATE()
|
|
);
|
|
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_backup_policy')
|
|
CREATE INDEX idx_backup_policy ON fs_backup_log(policy_id);
|
|
GO
|