Multimedia Column Types

Objective

Create a table with AIDB's native multimedia column types. AIDB supports storing audio, video, images, and PDF documents directly in the database with format-specific type declarations.

Step 1: Create a Multimedia Content Table

Create a table showcasing all multimedia types with explicit format declarations.

CREATE TABLE multimedia_content (
    id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,

    -- Image formats
    photo IMAGE(JPEG),
    screenshot IMAGE(PNG),
    web_graphic IMAGE(WEBP),

    -- Audio formats
    music_track AUDIO(MP3),
    voice_memo AUDIO(WAV),
    podcast AUDIO(OGG),

    -- Video formats
    video_clip VIDEO(MP4),
    legacy_video VIDEO(AVI),
    web_video VIDEO(WEBM),

    -- Document
    document PDF,

    -- Metadata
    file_size BIGINT,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Separate Media Tables

For better organization, create dedicated tables for each media type.

-- Image-specific table
CREATE TABLE image_library (
    id INTEGER PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    jpeg_image IMAGE(JPEG),
    png_image IMAGE(PNG),
    webp_image IMAGE(WEBP),
    gif_animation IMAGE(GIF),
    bmp_image IMAGE(BMP),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Audio-specific table
CREATE TABLE audio_library (
    id INTEGER PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    mp3_file AUDIO(MP3),
    wav_file AUDIO(WAV),
    flac_file AUDIO(FLAC),
    aac_file AUDIO(AAC),
    ogg_file AUDIO(OGG),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Video-specific table
CREATE TABLE video_library (
    id INTEGER PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    mp4_file VIDEO(MP4),
    avi_file VIDEO(AVI),
    mkv_file VIDEO(MKV),
    webm_file VIDEO(WEBM),
    mov_file VIDEO(MOV),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Document table
CREATE TABLE document_library (
    id INTEGER PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    pdf_document PDF,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 3: Insert Metadata Records

Insert records with metadata (actual binary content would be loaded via API).

INSERT INTO multimedia_content (id, title, description, file_size) VALUES
    (1, 'Product Photo', 'Main product image for catalog', 2048000),
    (2, 'Tutorial Video', 'Getting started video guide', 52428800),
    (3, 'Podcast Episode', 'Weekly tech podcast episode 42', 31457280),
    (4, 'User Manual', 'Complete product documentation', 5242880);

Step 4: Query Multimedia Records

Query the multimedia content table.

SELECT
    id,
    title,
    description,
    file_size,
    uploaded_at
FROM multimedia_content
ORDER BY uploaded_at DESC;

Step 5: Filter by File Size

Find large media files.

SELECT
    title,
    file_size,
    CASE
        WHEN file_size > 50000000 THEN 'Large'
        WHEN file_size > 10000000 THEN 'Medium'
        ELSE 'Small'
    END as size_category
FROM multimedia_content
WHERE file_size > 1000000
ORDER BY file_size DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS multimedia_content;
DROP TABLE IF EXISTS image_library;
DROP TABLE IF EXISTS audio_library;
DROP TABLE IF EXISTS video_library;
DROP TABLE IF EXISTS document_library;

Expected Outcomes

• Tables created with multimedia column types
• Each format type properly declared
• Metadata stored alongside media references
• Queries work on multimedia tables

Supported Multimedia Formats

Key Concepts Learned

• AIDB native multimedia types
• Format-specific type declarations
• Organizing media in separate tables
• Storing metadata alongside media content