clipper/backend/sql/migrations/20211031210317_create_media_sets_table.up.sql

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE media_sets (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  youtube_id CHARACTER VARYING(32) NOT NULL,
  audio_youtube_itag int NOT NULL,
  audio_channels int NOT NULL,
  audio_frames_approx bigint NOT NULL,
  audio_frames bigint,
  audio_sample_rate int NOT NULL,
  audio_s3_bucket CHARACTER VARYING(256),
  audio_s3_key CHARACTER VARYING(256),
  audio_s3_uploaded_at TIMESTAMP WITH TIME ZONE,
  audio_mime_type_encoded CHARACTER VARYING(256) NOT NULL,
  video_youtube_itag int NOT NULL,
  video_s3_bucket CHARACTER VARYING(256),
  video_s3_key CHARACTER VARYING(256),
  video_s3_uploaded_at TIMESTAMP WITH TIME ZONE,
  video_mime_type CHARACTER VARYING(256) NOT NULL,
  video_duration_nanos bigint NOT NULL,
  video_thumbnail_s3_bucket CHARACTER VARYING(256),
  video_thumbnail_s3_key CHARACTER VARYING(256),
  video_thumbnail_s3_uploaded_at TIMESTAMP WITH TIME ZONE,
  video_thumbnail_mime_type CHARACTER VARYING(256),
  video_thumbnail_width int DEFAULT 0,
  video_thumbnail_height int DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE UNIQUE INDEX index_media_sets_on_youtube_id ON media_sets (youtube_id);

ALTER TABLE media_sets ADD CONSTRAINT check_audio_youtube_itag_gt_0 CHECK (audio_youtube_itag > 0);
ALTER TABLE media_sets ADD CONSTRAINT check_audio_frames_gt_0 CHECK (audio_frames > 0);
ALTER TABLE media_sets ADD CONSTRAINT check_audio_frames_approx_gt_0 CHECK (audio_frames_approx > 0);
ALTER TABLE media_sets ADD CONSTRAINT check_audio_channels_gt_0 CHECK (audio_channels > 0);
ALTER TABLE media_sets ADD CONSTRAINT check_audio_sample_rate_gt_0 CHECK (audio_sample_rate > 0);
ALTER TABLE media_sets ADD CONSTRAINT check_video_youtube_itag_gt_0 CHECK (video_youtube_itag > 0);
ALTER TABLE media_sets ADD CONSTRAINT check_video_duration_nanos_gt_0 CHECK (video_duration_nanos > 0);