Objective
Build a video content intelligence system that analyzes videos to extract scenes, detect objects, generate summaries, and enable semantic search across video libraries.
Step 1: Create Video Library Table
Store video content.
CREATE TABLE video_library (
id INTEGER PRIMARY KEY,
video_code VARCHAR(50) NOT NULL UNIQUE,
title VARCHAR(300),
description TEXT,
video_file VIDEO(MP4),
thumbnail IMAGE(JPEG),
duration_seconds INTEGER,
file_size_mb DECIMAL(10, 2),
resolution VARCHAR(20),
frame_rate DECIMAL(5, 2),
aspect_ratio VARCHAR(10),
video_type VARCHAR(50),
genre VARCHAR(100),
language VARCHAR(20) DEFAULT 'en',
content_rating VARCHAR(20),
description_embedding VECTOR(384),
processing_status VARCHAR(50) DEFAULT 'pending',
processed_at TIMESTAMP,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Step 2: Create Video Scenes Table
Store detected scenes.
CREATE TABLE video_scenes (
id INTEGER PRIMARY KEY,
video_id INTEGER NOT NULL,
scene_number INTEGER,
start_time_seconds DECIMAL(10, 3),
end_time_seconds DECIMAL(10, 3),
duration_seconds DECIMAL(10, 3),
scene_thumbnail IMAGE(JPEG),
scene_description TEXT,
scene_embedding VECTOR(384),
scene_type VARCHAR(50),
dominant_colors TEXT,
brightness_level VARCHAR(20),
motion_intensity VARCHAR(20),
FOREIGN KEY (video_id) REFERENCES video_library(id)
);
Step 3: Create Detected Objects Table
Track objects in scenes.
CREATE TABLE scene_objects (
id INTEGER PRIMARY KEY,
scene_id INTEGER NOT NULL,
object_type VARCHAR(100),
object_label VARCHAR(200),
confidence DECIMAL(3, 2),
bounding_box TEXT,
first_appearance_seconds DECIMAL(10, 3),
screen_time_seconds DECIMAL(10, 3),
FOREIGN KEY (scene_id) REFERENCES video_scenes(id)
);
Step 4: Create Video Transcripts Table
Store spoken content.
CREATE TABLE video_transcripts (
id INTEGER PRIMARY KEY,
video_id INTEGER NOT NULL,
full_transcript TEXT,
word_count INTEGER,
transcript_language VARCHAR(20),
transcript_embedding VECTOR(384),
has_captions BOOLEAN DEFAULT FALSE,
FOREIGN KEY (video_id) REFERENCES video_library(id)
);
Step 5: Create Video Analysis Table
Store AI insights.
CREATE TABLE video_analysis (
id INTEGER PRIMARY KEY,
video_id INTEGER NOT NULL,
summary TEXT,
key_moments TEXT,
topics TEXT,
sentiment_score DECIMAL(3, 2),
content_tags TEXT,
brand_mentions TEXT,
scene_count INTEGER,
face_count INTEGER,
analyzed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (video_id) REFERENCES video_library(id)
);
Step 6: Create Video Chapters Table
Define navigable sections.
CREATE TABLE video_chapters (
id INTEGER PRIMARY KEY,
video_id INTEGER NOT NULL,
chapter_number INTEGER,
chapter_title VARCHAR(200),
start_time_seconds INTEGER,
end_time_seconds INTEGER,
description TEXT,
chapter_embedding VECTOR(384),
thumbnail IMAGE(JPEG),
FOREIGN KEY (video_id) REFERENCES video_library(id)
);
Step 7: Insert Sample Videos
Add video catalog.
INSERT INTO video_library (id, video_code, title, description, duration_seconds, file_size_mb, resolution, frame_rate, aspect_ratio, video_type, genre, content_rating, processing_status) VALUES
(1, 'VID-001', 'Product Launch Keynote 2024', 'Annual product launch event featuring new product announcements and demonstrations', 5400, 2850.5, '4K', 30.0, '16:9', 'presentation', 'corporate', 'G', 'completed'),
(2, 'VID-002', 'Customer Success Story - TechCorp', 'Interview with TechCorp about their success using our platform', 1200, 580.2, '1080p', 24.0, '16:9', 'testimonial', 'marketing', 'G', 'completed'),
(3, 'VID-003', 'Platform Tutorial Series Ep.1', 'Getting started tutorial covering basic features and setup', 1800, 890.4, '1080p', 30.0, '16:9', 'tutorial', 'education', 'G', 'completed'),
(4, 'VID-004', 'Tech Conference Talk', 'Technical deep dive on architecture and scaling', 2700, 1250.8, '1080p', 30.0, '16:9', 'conference', 'technical', 'G', 'completed'),
(5, 'VID-005', 'Brand Story Documentary', 'Documentary about company history and mission', 3600, 1850.6, '4K', 24.0, '21:9', 'documentary', 'corporate', 'G', 'completed'),
(6, 'VID-006', 'Product Demo - Enterprise Features', 'Demonstration of enterprise-specific features', 900, 420.3, '1080p', 30.0, '16:9', 'demo', 'product', 'G', 'completed');
UPDATE video_library
SET description_embedding = EMBED(description)
WHERE description_embedding IS NULL;
Step 8: Insert Video Scenes
Add scene detection results.
INSERT INTO video_scenes (id, video_id, scene_number, start_time_seconds, end_time_seconds, duration_seconds, scene_description, scene_type, dominant_colors, brightness_level, motion_intensity) VALUES
(1, 1, 1, 0.0, 120.5, 120.5, 'Opening with company logo animation and title card', 'intro', 'blue,white', 'high', 'medium'),
(2, 1, 2, 120.5, 480.2, 359.7, 'CEO on stage introducing the event and company vision', 'speaker', 'blue,black', 'medium', 'low'),
(3, 1, 3, 480.2, 1200.8, 720.6, 'Product demonstration with live demo on large screen', 'demo', 'white,blue,green', 'high', 'high'),
(4, 1, 4, 1200.8, 1800.5, 599.7, 'Panel discussion with product team', 'panel', 'neutral', 'medium', 'low'),
(5, 2, 1, 0.0, 60.2, 60.2, 'Introduction and company overview B-roll', 'intro', 'blue,white', 'high', 'medium'),
(6, 2, 2, 60.2, 600.5, 540.3, 'Interview with customer executive discussing challenges', 'interview', 'neutral', 'medium', 'low'),
(7, 2, 3, 600.5, 1000.8, 400.3, 'Customer showing platform usage with screen recording', 'demo', 'white,blue', 'high', 'medium'),
(8, 3, 1, 0.0, 90.0, 90.0, 'Tutorial intro with instructor and overview', 'intro', 'blue,white', 'high', 'low'),
(9, 3, 2, 90.0, 600.5, 510.5, 'Step-by-step walkthrough of dashboard setup', 'tutorial', 'white,gray', 'high', 'medium'),
(10, 3, 3, 600.5, 1200.2, 599.7, 'Advanced configuration options demonstration', 'tutorial', 'white,blue', 'high', 'medium');
UPDATE video_scenes
SET scene_embedding = EMBED(scene_description)
WHERE scene_embedding IS NULL;