Multi-Format Image Library

Objective

Create an image library that supports multiple image formats. AIDB natively supports JPEG, PNG, WEBP, GIF, and BMP formats with explicit type declarations.

Step 1: Create Multi-Format Image Table

Create a table that can store images in different formats.

CREATE TABLE image_library (
    id INTEGER PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    category VARCHAR(100),
    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 Format-Specific Tables

For better organization, create separate tables per format.

-- JPEG photos (best for photographs)
CREATE TABLE jpeg_photos (
    id INTEGER PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    photo IMAGE(JPEG) NOT NULL,
    quality_level VARCHAR(20) DEFAULT 'high',
    file_size BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- PNG images (best for graphics with transparency)
CREATE TABLE png_graphics (
    id INTEGER PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    graphic IMAGE(PNG) NOT NULL,
    has_transparency BOOLEAN DEFAULT FALSE,
    file_size BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- WEBP images (modern web format)
CREATE TABLE webp_images (
    id INTEGER PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    image IMAGE(WEBP) NOT NULL,
    is_animated BOOLEAN DEFAULT FALSE,
    file_size BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- GIF animations
CREATE TABLE gif_animations (
    id INTEGER PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    animation IMAGE(GIF) NOT NULL,
    frame_count INTEGER,
    duration_ms INTEGER,
    file_size BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 3: Insert Sample Records

Insert records for each format type.

INSERT INTO jpeg_photos (id, name, quality_level, file_size) VALUES
    (1, 'landscape_mountain.jpg', 'high', 2048000),
    (2, 'portrait_studio.jpg', 'high', 1536000),
    (3, 'product_photo.jpg', 'medium', 512000);

INSERT INTO png_graphics (id, name, has_transparency, file_size) VALUES
    (1, 'company_logo.png', TRUE, 45000),
    (2, 'app_icon.png', TRUE, 12000),
    (3, 'diagram_architecture.png', FALSE, 156000);

INSERT INTO webp_images (id, name, is_animated, file_size) VALUES
    (1, 'hero_banner.webp', FALSE, 85000),
    (2, 'product_gallery.webp', FALSE, 62000),
    (3, 'animated_loading.webp', TRUE, 24000);

INSERT INTO gif_animations (id, name, frame_count, duration_ms, file_size) VALUES
    (1, 'loading_spinner.gif', 12, 1000, 8000),
    (2, 'success_checkmark.gif', 24, 800, 15000),
    (3, 'tutorial_demo.gif', 60, 5000, 450000);

Step 4: Query Images by Format

Retrieve images from specific format tables.

-- All high-quality JPEGs
SELECT name, file_size
FROM jpeg_photos
WHERE quality_level = 'high';

-- PNGs with transparency
SELECT name, file_size
FROM png_graphics
WHERE has_transparency = TRUE;

-- Animated WEBPs
SELECT name, file_size
FROM webp_images
WHERE is_animated = TRUE;

Step 5: Unified Format View

Query across all formats using metadata table.

INSERT INTO image_library (id, name, category, format, file_size) VALUES
    (1, 'mountain_photo', 'photography', 'JPEG', 2048000),
    (2, 'company_logo', 'branding', 'PNG', 45000),
    (3, 'hero_banner', 'marketing', 'WEBP', 85000),
    (4, 'loading_animation', 'ui', 'GIF', 8000),
    (5, 'product_shot', 'ecommerce', 'JPEG', 512000);

SELECT name, category, format, file_size
FROM image_library
ORDER BY category, format;

Step 6: Format Statistics

Analyze image library by format.

SELECT
    format,
    COUNT(*) as image_count,
    SUM(file_size) as total_size,
    AVG(file_size) as avg_size
FROM image_library
GROUP BY format
ORDER BY image_count DESC;

Step 7: Find Large Images

Identify images that may need optimization.

SELECT
    name,
    format,
    file_size,
    CASE
        WHEN file_size > 1000000 THEN 'Large - Consider optimizing'
        WHEN file_size > 500000 THEN 'Medium'
        ELSE 'Small - Good'
    END as size_recommendation
FROM image_library
WHERE file_size > 100000
ORDER BY file_size DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS image_library;
DROP TABLE IF EXISTS jpeg_photos;
DROP TABLE IF EXISTS png_graphics;
DROP TABLE IF EXISTS webp_images;
DROP TABLE IF EXISTS gif_animations;

Expected Outcomes

• Multiple image format tables created
• Format-specific metadata tracked
• Queries filter by format characteristics
• Size analysis identifies optimization candidates

Format Comparison

Key Concepts Learned

• Format-specific IMAGE types
• Organizing by format category
• Format-appropriate metadata
• Size optimization queries