Image Format Conversion

Objective

Use AIDB's CONVERT_FORMAT function to convert images between different formats. This enables format optimization for web delivery and compatibility requirements.

Step 1: Create Source Images Table

Create a table for storing original images.

CREATE TABLE source_images (
    id INTEGER PRIMARY KEY,
    filename VARCHAR(255) NOT NULL,
    original_format VARCHAR(10) NOT NULL,
    jpeg_image IMAGE(JPEG),
    png_image IMAGE(PNG),
    webp_image IMAGE(WEBP),
    gif_image IMAGE(GIF),
    bmp_image IMAGE(BMP),
    file_size BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Converted Images Table

Create a table for storing format conversions.

CREATE TABLE converted_images (
    id INTEGER PRIMARY KEY,
    source_id INTEGER NOT NULL,
    source_format VARCHAR(10) NOT NULL,
    target_format VARCHAR(10) NOT NULL,
    converted_image_jpeg IMAGE(JPEG),
    converted_image_png IMAGE(PNG),
    converted_image_webp IMAGE(WEBP),
    original_size BIGINT,
    converted_size BIGINT,
    size_reduction_percent DECIMAL(5, 2),
    converted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (source_id) REFERENCES source_images(id)
);

Step 3: Insert Sample Source Images

Add sample source images.

INSERT INTO source_images (id, filename, original_format, file_size) VALUES
    (1, 'photo_001.png', 'PNG', 5242880),
    (2, 'screenshot_001.bmp', 'BMP', 8388608),
    (3, 'graphic_001.gif', 'GIF', 1048576),
    (4, 'image_001.jpeg', 'JPEG', 2097152),
    (5, 'banner_001.png', 'PNG', 3145728);

Step 4: Convert PNG to JPEG

Convert PNG images to JPEG format.

INSERT INTO converted_images (id, source_id, source_format, target_format, original_size, converted_size)
SELECT
    id,
    id,
    'PNG',
    'JPEG',
    file_size,
    file_size * 0.3
FROM source_images
WHERE original_format = 'PNG';

UPDATE converted_images
SET converted_image_jpeg = CONVERT_FORMAT(
    (SELECT png_image FROM source_images WHERE source_images.id = converted_images.source_id),
    'JPEG'
)
WHERE source_format = 'PNG' AND target_format = 'JPEG';

Step 5: Convert to WebP for Web Optimization

Convert images to WebP for smaller file sizes.

INSERT INTO converted_images (id, source_id, source_format, target_format, original_size, converted_size)
VALUES
    (6, 1, 'PNG', 'WEBP', 5242880, 1310720),
    (7, 4, 'JPEG', 'WEBP', 2097152, 734003),
    (8, 5, 'PNG', 'WEBP', 3145728, 786432);

Step 6: Calculate Size Reduction

Update size reduction percentages.

UPDATE converted_images
SET size_reduction_percent =
    CASE
        WHEN original_size > 0 THEN ((original_size - converted_size) * 100.0 / original_size)
        ELSE 0
    END
WHERE size_reduction_percent IS NULL;

SELECT
    source_format,
    target_format,
    original_size,
    converted_size,
    size_reduction_percent
FROM converted_images
ORDER BY size_reduction_percent DESC;

Step 7: Create Format Conversion Queue

Track pending conversions.

CREATE TABLE conversion_queue (
    id INTEGER PRIMARY KEY,
    source_image_id INTEGER NOT NULL,
    source_format VARCHAR(10) NOT NULL,
    target_format VARCHAR(10) NOT NULL,
    priority INTEGER DEFAULT 5,
    status VARCHAR(20) DEFAULT 'pending',
    queued_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    started_at TIMESTAMP,
    completed_at TIMESTAMP,
    error_message TEXT
);

INSERT INTO conversion_queue (id, source_image_id, source_format, target_format, priority) VALUES
    (1, 1, 'PNG', 'JPEG', 1),
    (2, 1, 'PNG', 'WEBP', 2),
    (3, 2, 'BMP', 'JPEG', 1),
    (4, 2, 'BMP', 'PNG', 3),
    (5, 3, 'GIF', 'WEBP', 5);

Step 8: Process Conversion Queue

Update queue status as conversions complete.

-- Mark conversion as in progress
UPDATE conversion_queue
SET status = 'processing',
    started_at = CURRENT_TIMESTAMP
WHERE id = 1;

-- Mark conversion as complete
UPDATE conversion_queue
SET status = 'completed',
    completed_at = CURRENT_TIMESTAMP
WHERE id = 1;

SELECT
    source_format,
    target_format,
    status,
    queued_at,
    completed_at
FROM conversion_queue
ORDER BY priority, queued_at;

Step 9: Format Conversion Statistics

Analyze conversion results.

SELECT
    source_format,
    target_format,
    COUNT(*) as conversion_count,
    AVG(size_reduction_percent) as avg_reduction,
    SUM(original_size - converted_size) as total_savings
FROM converted_images
GROUP BY source_format, target_format
ORDER BY avg_reduction DESC;

Step 10: Best Format for Each Source

Determine optimal target format.

SELECT
    source_format,
    target_format,
    size_reduction_percent,
    'Recommended' as status
FROM converted_images c1
WHERE size_reduction_percent = (
    SELECT MAX(size_reduction_percent)
    FROM converted_images c2
    WHERE c1.source_id = c2.source_id
);

Step 11: Queue Status Summary

Get conversion queue status.

SELECT
    status,
    COUNT(*) as count,
    AVG(CASE
        WHEN completed_at IS NOT NULL AND started_at IS NOT NULL
        THEN 1
        ELSE NULL
    END) as has_timing
FROM conversion_queue
GROUP BY status;

Step 12: Pending High Priority Conversions

Find urgent pending conversions.

SELECT
    cq.id,
    si.filename,
    cq.source_format,
    cq.target_format,
    cq.priority,
    cq.queued_at
FROM conversion_queue cq
INNER JOIN source_images si ON cq.source_image_id = si.id
WHERE cq.status = 'pending'
  AND cq.priority <= 2
ORDER BY cq.priority, cq.queued_at;

Cleanup (Optional)

DROP TABLE IF EXISTS conversion_queue;
DROP TABLE IF EXISTS converted_images;
DROP TABLE IF EXISTS source_images;

Expected Outcomes

• CONVERT_FORMAT changes image formats
• Size reductions tracked
• Conversion queue manages workflow
• Statistics show optimization gains
• Best format recommendations available

Format Conversion Guide

Key Concepts Learned

• CONVERT_FORMAT function usage
• Size optimization tracking
• Conversion queue pattern
• Format comparison analysis
• Priority-based processing