53 lines
2.1 KiB
SQL
53 lines
2.1 KiB
SQL
--
|
|
-- VideoDetect Database Schema
|
|
-- Version: 1.0.0
|
|
-- Created: 2026-08-03
|
|
--
|
|
|
|
-- Create database (if not exists)
|
|
CREATE DATABASE IF NOT EXISTS videodetect
|
|
CHARACTER SET utf8mb4
|
|
COLLATE utf8mb4_unicode_ci;
|
|
|
|
USE videodetect;
|
|
|
|
-- -----------------------------------------------------
|
|
-- Table: videos
|
|
-- Stores metadata for each video file in the corpus
|
|
-- -----------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS videos (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
|
file_path VARCHAR(2048) NOT NULL,
|
|
file_size BIGINT NOT NULL COMMENT 'Size of the file in bytes',
|
|
file_hash CHAR(64) COMMENT 'SHA-256 hash of file',
|
|
resolution_w INT DEFAULT NULL,
|
|
resolution_h INT DEFAULT NULL,
|
|
codec VARCHAR(50) DEFAULT NULL,
|
|
duration FLOAT DEFAULT NULL COMMENT 'Duration in seconds',
|
|
last_scan_time DATETIME DEFAULT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
|
|
UNIQUE KEY uk_path (file_path),
|
|
INDEX idx_videos_last_scan (last_scan_time),
|
|
INDEX idx_videos_file_path (file_path(255))
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
|
|
CREATE TABLE IF NOT EXISTS tasks (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
|
task_type VARCHAR(16) NOT NULL,
|
|
video_id BIGINT NOT NULL,
|
|
status ENUM('PENDING', 'IN_PROGRESS', 'COMPLETED', 'FAILED') NOT NULL DEFAULT 'PENDING',
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
assign_key VARCHAR(64) DEFAULT NULL COMMENT 'Key to identify the worker assigned to this task',
|
|
assigned_at DATETIME DEFAULT NULL COMMENT 'Timestamp when the task was assigned to a worker',
|
|
results JSON DEFAULT NULL COMMENT 'Task-specific results or metadata',
|
|
FOREIGN KEY (video_id) REFERENCES videos(id) ON DELETE CASCADE,
|
|
INDEX idx_tasks_video (video_id),
|
|
INDEX idx_tasks_status (status),
|
|
INDEX idx_tasks_type_status (task_type, status),
|
|
UNIQUE KEY uk_tasks_video_type (video_id, task_type)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|