128 lines
4.6 KiB
SQL
Executable File
128 lines
4.6 KiB
SQL
Executable File
-- 高句丽视频平台数据库初始化脚本
|
|
|
|
-- 用户表
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username VARCHAR(50) UNIQUE NOT NULL,
|
|
password VARCHAR(255) NOT NULL,
|
|
email VARCHAR(100),
|
|
avatar VARCHAR(255),
|
|
role VARCHAR(20) DEFAULT 'user',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 视频表
|
|
CREATE TABLE IF NOT EXISTS videos (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
title VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
video_url VARCHAR(500) NOT NULL,
|
|
file_path VARCHAR(500),
|
|
cover_url VARCHAR(500),
|
|
cover_image VARCHAR(500),
|
|
duration INTEGER DEFAULT 0,
|
|
file_size INTEGER DEFAULT 0,
|
|
views INTEGER DEFAULT 0,
|
|
likes INTEGER DEFAULT 0,
|
|
category INTEGER,
|
|
tags TEXT,
|
|
status VARCHAR(20) DEFAULT 'active',
|
|
user_id INTEGER,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id),
|
|
FOREIGN KEY (category) REFERENCES categories(id)
|
|
);
|
|
|
|
-- 视频分类表
|
|
CREATE TABLE IF NOT EXISTS categories (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name VARCHAR(50) UNIQUE NOT NULL,
|
|
description TEXT,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 视频标签表
|
|
CREATE TABLE IF NOT EXISTS tags (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name VARCHAR(50) UNIQUE NOT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 视频标签关联表
|
|
CREATE TABLE IF NOT EXISTS video_tags (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
video_id INTEGER NOT NULL,
|
|
tag_id INTEGER NOT NULL,
|
|
FOREIGN KEY (video_id) REFERENCES videos(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE,
|
|
UNIQUE(video_id, tag_id)
|
|
);
|
|
|
|
-- 用户点赞表
|
|
CREATE TABLE IF NOT EXISTS user_likes (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
video_id INTEGER NOT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (video_id) REFERENCES videos(id) ON DELETE CASCADE,
|
|
UNIQUE(user_id, video_id)
|
|
);
|
|
|
|
-- 播放历史表
|
|
CREATE TABLE IF NOT EXISTS play_history (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER,
|
|
video_id INTEGER NOT NULL,
|
|
play_time INTEGER DEFAULT 0,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (video_id) REFERENCES videos(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- 视频统计表
|
|
CREATE TABLE IF NOT EXISTS video_stats (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
video_id INTEGER UNIQUE NOT NULL,
|
|
views INTEGER DEFAULT 0,
|
|
likes INTEGER DEFAULT 0,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (video_id) REFERENCES videos(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- 插入默认管理员用户
|
|
INSERT OR IGNORE INTO users (username, password, email, role)
|
|
VALUES ('admin', '$2b$10$rQZ8kHWKtGOZvlKJ5mXzKOqGYvKqGYvKqGYvKqGYvKqGYvKqGYvKq', 'admin@goguryeo.com', 'admin');
|
|
|
|
-- 插入默认分类
|
|
INSERT OR IGNORE INTO categories (name, description) VALUES
|
|
('历史文化', '高句丽历史文化相关视频'),
|
|
('传统艺术', '传统艺术表演和展示'),
|
|
('语言学习', '高句丽语言学习教程'),
|
|
('考古发现', '考古发现和文物介绍'),
|
|
('民俗风情', '传统民俗和风土人情'),
|
|
('其他', '其他相关内容');
|
|
|
|
-- 插入默认标签
|
|
INSERT OR IGNORE INTO tags (name) VALUES
|
|
('高句丽'),
|
|
('历史'),
|
|
('文化'),
|
|
('传统'),
|
|
('艺术'),
|
|
('语言'),
|
|
('考古'),
|
|
('民俗'),
|
|
('教育'),
|
|
('纪录片');
|
|
|
|
-- 插入示例视频数据
|
|
INSERT OR IGNORE INTO videos (title, description, video_url, file_path, cover_url, cover_image, duration, file_size, views, likes, category, user_id) VALUES
|
|
('高句丽历史概述', '介绍高句丽王朝的兴起与发展历程', '/videos/sample1.mp4', '/videos/sample1.mp4', '/covers/sample1.jpg', '/covers/sample1.jpg', 1800, 52428800, 1250, 89, 1, 1),
|
|
('传统舞蹈表演', '展示高句丽传统舞蹈的魅力', '/videos/sample2.mp4', '/videos/sample2.mp4', '/covers/sample2.jpg', '/covers/sample2.jpg', 900, 31457280, 856, 67, 2, 1),
|
|
('古代建筑艺术', '探索高句丽古代建筑的独特风格', '/videos/sample3.mp4', '/videos/sample3.mp4', '/covers/sample3.jpg', '/covers/sample3.jpg', 1200, 41943040, 743, 45, 1, 1),
|
|
('语言学习入门', '高句丽语言基础教程第一课', '/videos/sample4.mp4', '/videos/sample4.mp4', '/covers/sample4.jpg', '/covers/sample4.jpg', 1500, 52428800, 432, 23, 3, 1),
|
|
('考古新发现', '最新的高句丽考古发现解读', '/videos/sample5.mp4', '/videos/sample5.mp4', '/covers/sample5.jpg', '/covers/sample5.jpg', 2100, 73400320, 321, 18, 4, 1); |