— 1. 文档表设计
CREATE TABLE fgedu_documents (
id SERIAL PRIMARY KEY,
document_name VARCHAR(255) NOT NULL,
document_type VARCHAR(50) NOT NULL,
file_size BIGINT NOT NULL,
file_data OID NOT NULL,
category_id INTEGER REFERENCES fgedu_categories(id),
department_id INTEGER REFERENCES fgedu_departments(id),
created_by INTEGER REFERENCES fgedu_fgedus(id),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
version INTEGER DEFAULT 1,
status VARCHAR(20) DEFAULT ‘active’
);

— 2. 文档版本表
CREATE TABLE fgedu_document_versions (
id SERIAL PRIMARY KEY,
document_id INTEGER REFERENCES fgedu_documents(id),
version INTEGER NOT NULL,
file_data OID NOT NULL,
change_description TEXT,
created_by INTEGER REFERENCES fgedu_fgedus(id),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

— 3. 文档权限表
CREATE TABLE fgedu_document_permissions (
id SERIAL PRIMARY KEY,
document_id INTEGER REFERENCES fgedu_documents(id),
fgedu_id INTEGER REFERENCES fgedu_fgedus(id),
permission_type VARCHAR(20) NOT NULL, — read, write, delete
granted_by INTEGER REFERENCES fgedu_fgedus(id),
granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

— 4. 触发器：自动管理大对象
CREATE OR REPLACE FUNCTION manage_document_large_object()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = ‘DELETE’ THEN
— 删除旧版本的大对象
IF OLD.file_data IS NOT NULL THEN
PERFORM lo_unlink(OLD.file_data);
END IF;
RETURN OLD;
ELSIF TG_OP = ‘UPDATE’ THEN
— 如果文件数据变更，删除旧的大对象
IF OLD.file_data IS NOT NULL AND OLD.file_data != NEW.file_data THEN
PERFORM lo_unlink(OLD.file_data);
END IF;
RETURN NEW;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_manage_document_large_object
BEFORE DELETE OR UPDATE ON fgedu_documents
FOR EACH ROW
EXECUTE FUNCTION manage_document_large_object();

— 1. 媒体文件表
CREATE TABLE fgedu_media_files (
id SERIAL PRIMARY KEY,
file_name VARCHAR(255) NOT NULL,
original_name VARCHAR(255) NOT NULL,
file_type VARCHAR(50) NOT NULL, — image, video, audio
mime_type VARCHAR(100) NOT NULL,
file_size BIGINT NOT NULL,
file_data OID NOT NULL,
width INTEGER, — 图片/视频宽度
height INTEGER, — 图片/视频高度
duration INTEGER, — 音视频时长（秒）
thumbnail_oid OID, — 缩略图
uploaded_by INTEGER REFERENCES fgedu_fgedus(id),
uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
download_count INTEGER DEFAULT 0,
status VARCHAR(20) DEFAULT ‘active’
);

— 2. 媒体分类表
CREATE TABLE fgedu_media_categories (
id SERIAL PRIMARY KEY,
category_name VARCHAR(100) NOT NULL,
parent_id INTEGER REFERENCES fgedu_media_categories(id),
description TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

— 3. 媒体标签表
CREATE TABLE fgedu_media_tags (
id SERIAL PRIMARY KEY,
tag_name VARCHAR(50) NOT NULL UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

— 4. 媒体文件标签关联表
CREATE TABLE fgedu_media_file_tags (
media_id INTEGER REFERENCES fgedu_media_files(id),
tag_id INTEGER REFERENCES fgedu_media_tags(id),
PRIMARY KEY (media_id, tag_id)
);

— 5. 查询媒体文件
SELECT
m.id,
m.file_name,
m.file_type,
m.mime_type,
pg_size_pretty(m.file_size) as file_size,
m.width,
m.height,
m.duration,
m.uploaded_at,
u.fgeduname as uploaded_by
FROM fgedu_media_files m
JOIN fgedu_fgedus u ON m.uploaded_by = u.id
WHERE m.file_type = ‘image’
AND m.status = ‘active’
ORDER BY m.uploaded_at DESC
LIMIT 20;

— 1. 备份记录表
CREATE TABLE fgedu_backup_records (
id SERIAL PRIMARY KEY,
backup_name VARCHAR(255) NOT NULL,
backup_type VARCHAR(50) NOT NULL, — full, incremental, differential
fgedudb_name VARCHAR(100) NOT NULL,
file_size BIGINT NOT NULL,
file_data OID NOT NULL,
compression_ratio NUMERIC(5,2),
backup_start_time TIMESTAMP NOT NULL,
backup_end_time TIMESTAMP,
duration_seconds INTEGER,
status VARCHAR(20) DEFAULT ‘running’, — running, completed, failed
error_message TEXT,
created_by INTEGER REFERENCES fgedu_fgedus(id),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

— 2. 备份策略表
CREATE TABLE fgedu_backup_policies (
id SERIAL PRIMARY KEY,
policy_name VARCHAR(100) NOT NULL,
fgedudb_name VARCHAR(100) NOT NULL,
backup_type VARCHAR(50) NOT NULL,
schedule_cron VARCHAR(100) NOT NULL, — cron表达式
retention_days INTEGER NOT NULL,
compression_enabled BOOLEAN DEFAULT true,
encryption_enabled BOOLEAN DEFAULT false,
is_active BOOLEAN DEFAULT true,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

— 3. 查询备份记录
SELECT
id,
backup_name,
backup_type,
fgedudb_name,
pg_size_pretty(file_size) as file_size,
compression_ratio,
backup_start_time,
backup_end_time,
duration_seconds,
status
FROM fgedu_backup_records
WHERE fgedudb_name = ‘fgedudb’
AND status = ‘completed’
ORDER BY backup_start_time DESC
LIMIT 10;