NotAShelf
e02c15490e
Signed-off-by: NotAShelf <raf@notashelf.dev> Change-Id: I2d1f04f13970d21c36067f30bc04a9176a6a6964
15 lines
880 B
SQL
15 lines
880 B
SQL
-- V13: Enhanced photo metadata support
|
|
-- Add photo-specific fields to media_items table
|
|
|
|
ALTER TABLE media_items ADD COLUMN date_taken TIMESTAMPTZ;
|
|
ALTER TABLE media_items ADD COLUMN latitude DOUBLE PRECISION;
|
|
ALTER TABLE media_items ADD COLUMN longitude DOUBLE PRECISION;
|
|
ALTER TABLE media_items ADD COLUMN camera_make TEXT;
|
|
ALTER TABLE media_items ADD COLUMN camera_model TEXT;
|
|
ALTER TABLE media_items ADD COLUMN rating INTEGER CHECK (rating >= 0 AND rating <= 5);
|
|
|
|
-- Indexes for photo queries
|
|
CREATE INDEX idx_media_date_taken ON media_items(date_taken) WHERE date_taken IS NOT NULL;
|
|
CREATE INDEX idx_media_location ON media_items(latitude, longitude) WHERE latitude IS NOT NULL AND longitude IS NOT NULL;
|
|
CREATE INDEX idx_media_camera ON media_items(camera_make) WHERE camera_make IS NOT NULL;
|
|
CREATE INDEX idx_media_rating ON media_items(rating) WHERE rating IS NOT NULL;
|