Screenshot Archive

Objective

Create a system for storing and organizing application screenshots. Screenshots are valuable for bug tracking, documentation, and UI change history.

Step 1: Create Screenshots Table

Create a table for storing screenshots with metadata.

CREATE TABLE screenshots (
    id INTEGER PRIMARY KEY,
    filename VARCHAR(255) NOT NULL,
    screenshot IMAGE(PNG),
    application VARCHAR(100) NOT NULL,
    screen_name VARCHAR(100),
    description TEXT,
    resolution VARCHAR(20),
    platform VARCHAR(50),
    browser VARCHAR(50),
    captured_by VARCHAR(100),
    captured_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Insert Sample Screenshots

Add sample screenshot records.

INSERT INTO screenshots (id, filename, application, screen_name, description, resolution, platform, browser, captured_by) VALUES
    (1, 'login_page_v2.png', 'WebApp', 'Login', 'New login page design with OAuth options', '1920x1080', 'Windows', 'Chrome', 'dev_team'),
    (2, 'dashboard_main.png', 'WebApp', 'Dashboard', 'Main dashboard with analytics widgets', '1920x1080', 'Windows', 'Chrome', 'dev_team'),
    (3, 'mobile_home.png', 'MobileApp', 'Home', 'Home screen on iPhone 14', '390x844', 'iOS', 'Safari', 'qa_team'),
    (4, 'error_500.png', 'WebApp', 'Error', 'Server error page appearance', '1920x1080', 'Windows', 'Firefox', 'qa_team'),
    (5, 'settings_dark.png', 'WebApp', 'Settings', 'Settings page in dark mode', '1920x1080', 'macOS', 'Safari', 'design_team'),
    (6, 'checkout_flow.png', 'WebApp', 'Checkout', 'Payment form step in checkout', '1920x1080', 'Windows', 'Chrome', 'dev_team'),
    (7, 'android_menu.png', 'MobileApp', 'Menu', 'Navigation menu on Android', '412x915', 'Android', 'Chrome', 'qa_team'),
    (8, 'tablet_grid.png', 'WebApp', 'Products', 'Product grid on tablet view', '1024x768', 'iPadOS', 'Safari', 'design_team');

Step 3: Create Bug Screenshots Table

Create a table linking screenshots to bug reports.

CREATE TABLE bug_screenshots (
    id INTEGER PRIMARY KEY,
    bug_id VARCHAR(20) NOT NULL,
    screenshot IMAGE(PNG),
    description TEXT,
    step_number INTEGER,
    expected_behavior TEXT,
    actual_behavior TEXT,
    severity VARCHAR(20),
    captured_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO bug_screenshots (id, bug_id, description, step_number, expected_behavior, actual_behavior, severity) VALUES
    (1, 'BUG-1234', 'Button misaligned on mobile', 1, 'Button centered in container', 'Button overflows to the right', 'medium'),
    (2, 'BUG-1234', 'After scrolling down', 2, 'Button stays in view', 'Button disappears completely', 'medium'),
    (3, 'BUG-1235', 'Form validation error', 1, 'Red border on invalid field', 'No visual indication of error', 'high'),
    (4, 'BUG-1236', 'Dark mode contrast issue', 1, 'Text readable on dark background', 'Text barely visible', 'medium'),
    (5, 'BUG-1237', 'Broken image placeholder', 1, 'Placeholder icon shown', 'Empty space with no indication', 'low');

Step 4: Query Screenshots by Application

Find all screenshots for a specific application.

SELECT
    filename,
    screen_name,
    description,
    resolution,
    captured_at
FROM screenshots
WHERE application = 'WebApp'
ORDER BY screen_name, captured_at DESC;

Step 5: Screenshots by Platform

Group screenshots by platform.

SELECT
    platform,
    COUNT(*) as screenshot_count,
    COUNT(DISTINCT application) as apps_covered,
    COUNT(DISTINCT screen_name) as screens_covered
FROM screenshots
GROUP BY platform
ORDER BY screenshot_count DESC;

Step 6: Bug Report Screenshots

Get all screenshots for a bug report.

SELECT
    bug_id,
    step_number,
    description,
    expected_behavior,
    actual_behavior,
    severity
FROM bug_screenshots
WHERE bug_id = 'BUG-1234'
ORDER BY step_number;

Step 7: Create Version History Table

Track UI changes over time with screenshots.

CREATE TABLE ui_version_history (
    id INTEGER PRIMARY KEY,
    screen_name VARCHAR(100) NOT NULL,
    version VARCHAR(20) NOT NULL,
    screenshot IMAGE(PNG),
    change_description TEXT,
    released_at DATE,
    captured_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO ui_version_history (id, screen_name, version, change_description, released_at) VALUES
    (1, 'Login', 'v1.0', 'Initial login design', '2023-01-15'),
    (2, 'Login', 'v1.1', 'Added social login buttons', '2023-03-20'),
    (3, 'Login', 'v2.0', 'Complete redesign with new branding', '2024-01-10'),
    (4, 'Dashboard', 'v1.0', 'Initial dashboard layout', '2023-01-15'),
    (5, 'Dashboard', 'v1.5', 'Added analytics widgets', '2023-06-01'),
    (6, 'Dashboard', 'v2.0', 'New card-based design', '2024-01-10');

Step 8: Version Comparison Query

Find version history for a screen.

SELECT
    screen_name,
    version,
    change_description,
    released_at
FROM ui_version_history
WHERE screen_name = 'Login'
ORDER BY released_at DESC;

Step 9: Recent Screenshots

Find recently captured screenshots.

SELECT
    filename,
    application,
    screen_name,
    captured_by,
    captured_at
FROM screenshots
ORDER BY captured_at DESC
LIMIT 10;

Step 10: Screenshots Missing for Screens

Find screens that may need screenshots.

CREATE TABLE app_screens (
    screen_name VARCHAR(100) PRIMARY KEY,
    application VARCHAR(100) NOT NULL,
    requires_screenshot BOOLEAN DEFAULT TRUE
);

INSERT INTO app_screens (screen_name, application) VALUES
    ('Login', 'WebApp'),
    ('Dashboard', 'WebApp'),
    ('Settings', 'WebApp'),
    ('Profile', 'WebApp'),
    ('Checkout', 'WebApp'),
    ('Help', 'WebApp');

SELECT
    a.screen_name,
    a.application,
    CASE
        WHEN s.id IS NOT NULL THEN 'Has Screenshot'
        ELSE 'Missing Screenshot'
    END as status
FROM app_screens a
LEFT JOIN screenshots s ON a.screen_name = s.screen_name AND a.application = s.application
ORDER BY status, a.screen_name;

Step 11: Bug Severity Distribution

Analyze bug screenshots by severity.

SELECT
    severity,
    COUNT(*) as bug_count,
    COUNT(DISTINCT bug_id) as unique_bugs
FROM bug_screenshots
GROUP BY severity
ORDER BY
    CASE severity
        WHEN 'critical' THEN 1
        WHEN 'high' THEN 2
        WHEN 'medium' THEN 3
        WHEN 'low' THEN 4
        ELSE 5
    END;

Cleanup (Optional)

DROP TABLE IF EXISTS app_screens;
DROP TABLE IF EXISTS ui_version_history;
DROP TABLE IF EXISTS bug_screenshots;
DROP TABLE IF EXISTS screenshots;

Expected Outcomes

• Screenshots organized by application
• Bug reports linked to screenshots
• Version history tracked visually
• Platform coverage analyzed
• Missing documentation identified

Key Concepts Learned

• IMAGE(PNG) for lossless screenshots
• Metadata for screenshot context
• Bug tracking integration
• Version history tracking
• Coverage analysis queries