Basic Photo Gallery

Objective

Create a simple photo storage table using AIDB's native IMAGE type. This forms the foundation for building a photo gallery application.

Step 1: Create Photo Gallery Table

Create a table to store photos with essential metadata.

CREATE TABLE photo_gallery (
    id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    photo IMAGE(JPEG),
    photographer VARCHAR(100),
    taken_date DATE,
    is_public BOOLEAN DEFAULT TRUE,
    view_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Insert Photo Records

Insert photo records with metadata (binary content loaded via API).

INSERT INTO photo_gallery (id, title, description, photographer, taken_date, is_public) VALUES
    (1, 'Mountain Sunrise', 'Beautiful sunrise over the Rocky Mountains', 'John Smith', '2024-03-15', TRUE),
    (2, 'City Skyline', 'Downtown skyline at golden hour', 'Jane Doe', '2024-02-28', TRUE),
    (3, 'Ocean Waves', 'Crashing waves on the Pacific coast', 'Mike Johnson', '2024-01-20', TRUE),
    (4, 'Forest Path', 'Quiet trail through autumn forest', 'Sarah Williams', '2023-10-05', FALSE),
    (5, 'Desert Sunset', 'Vibrant colors over sand dunes', 'John Smith', '2024-04-10', TRUE);

Step 3: Query All Public Photos

Retrieve all publicly visible photos.

SELECT
    id,
    title,
    description,
    photographer,
    taken_date,
    view_count
FROM photo_gallery
WHERE is_public = TRUE
ORDER BY taken_date DESC;

Step 4: Search Photos by Photographer

Find all photos by a specific photographer.

SELECT
    title,
    description,
    taken_date
FROM photo_gallery
WHERE photographer = 'John Smith'
ORDER BY taken_date DESC;

Step 5: Update View Count

Increment view count when a photo is viewed.

UPDATE photo_gallery
SET view_count = view_count + 1
WHERE id = 1;

SELECT title, view_count
FROM photo_gallery
WHERE id = 1;

Step 6: Find Most Viewed Photos

Get the most popular photos by view count.

SELECT
    title,
    photographer,
    view_count
FROM photo_gallery
WHERE is_public = TRUE
ORDER BY view_count DESC
LIMIT 10;

Step 7: Photos by Date Range

Find photos taken within a date range.

SELECT
    title,
    photographer,
    taken_date
FROM photo_gallery
WHERE taken_date BETWEEN '2024-01-01' AND '2024-03-31'
ORDER BY taken_date;

Step 8: Gallery Statistics

Get aggregate statistics for the gallery.

SELECT
    COUNT(*) as total_photos,
    COUNT(CASE WHEN is_public = TRUE THEN 1 END) as public_photos,
    COUNT(CASE WHEN is_public = FALSE THEN 1 END) as private_photos,
    SUM(view_count) as total_views,
    COUNT(DISTINCT photographer) as unique_photographers
FROM photo_gallery;

Cleanup (Optional)

DROP TABLE IF EXISTS photo_gallery;

Expected Outcomes

• Photo gallery table created with IMAGE(JPEG) column
• Photos stored with metadata
• Public/private visibility control
• View tracking functionality
• Date-based and photographer queries work

Key Concepts Learned

• IMAGE(JPEG) type for photo storage
• Basic CRUD operations on gallery
• View count tracking pattern
• Public/private filtering
• Date range queries