structure

--
-- PostgreSQL database dump
--

-- Dumped from database version 14.6
-- Dumped by pg_dump version 14.6

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: set_mod_time_fun(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.set_mod_time_fun() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
  NEW.modified = NOW();
  RETURN NEW;
END;
$$;


ALTER FUNCTION public.set_mod_time_fun() OWNER TO postgres;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: MetaOST; Type: TABLE; Schema: public; Owner: bashiron
--

CREATE TABLE public."MetaOST" (
    vid_id integer NOT NULL,
    title character varying(256),
    ost character varying(128),
    franchise character varying(128),
    vibe character varying(32)[]
);


ALTER TABLE public."MetaOST" OWNER TO bashiron;

--
-- Name: TABLE "MetaOST"; Type: COMMENT; Schema: public; Owner: bashiron
--

COMMENT ON TABLE public."MetaOST" IS 'Data exclusive to Original Soundtrack type videos';


--
-- Name: COLUMN "MetaOST".title; Type: COMMENT; Schema: public; Owner: bashiron
--

COMMENT ON COLUMN public."MetaOST".title IS 'Original language title';


--
-- Name: MetaRaw; Type: TABLE; Schema: public; Owner: bashiron
--

CREATE TABLE public."MetaRaw" (
    vid_id integer NOT NULL,
    title character varying(100),
    description text,
    tags character varying(500),
    upload_date timestamp(0) with time zone,
    channel character varying(100),
    thumbnail bytea
);


ALTER TABLE public."MetaRaw" OWNER TO bashiron;

--
-- Name: TABLE "MetaRaw"; Type: COMMENT; Schema: public; Owner: bashiron
--

COMMENT ON TABLE public."MetaRaw" IS 'Videos'' raw data taken from the Youtube API';


--
-- Name: Playlists; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public."Playlists" (
    name character varying(64),
    aplid character varying(40) NOT NULL
);


ALTER TABLE public."Playlists" OWNER TO postgres;

--
-- Name: TABLE "Playlists"; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TABLE public."Playlists" IS 'Playlists the user is interested in saving';


--
-- Name: COLUMN "Playlists".name; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public."Playlists".name IS 'User given name for the playlist';


--
-- Name: COLUMN "Playlists".aplid; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public."Playlists".aplid IS 'Playlist ID in the Youtube database. Append after https://youtube.com/playlist?list= to get playlist URL. It has a max size of 40 chars because currently they are max 34 chars long but we extend it a bit just in case';


--
-- Name: vid_id_seq; Type: SEQUENCE; Schema: public; Owner: bashiron
--

CREATE SEQUENCE public.vid_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    MAXVALUE 2147483647
    CACHE 1;


ALTER TABLE public.vid_id_seq OWNER TO bashiron;

--
-- Name: Videos; Type: TABLE; Schema: public; Owner: bashiron
--

CREATE TABLE public."Videos" (
    id integer DEFAULT nextval('public.vid_id_seq'::regclass) NOT NULL,
    name character varying(256) NOT NULL,
    playlist character varying[],
    duration interval(0),
    fragment bytea,
    category character varying(32)[],
    summary text,
    alive boolean DEFAULT true,
    modified timestamp with time zone DEFAULT now() NOT NULL,
    created date DEFAULT now() NOT NULL,
    apvid character varying(15)
);


ALTER TABLE public."Videos" OWNER TO bashiron;

--
-- Name: TABLE "Videos"; Type: COMMENT; Schema: public; Owner: bashiron
--

COMMENT ON TABLE public."Videos" IS 'Videos'' primary data';


--
-- Name: COLUMN "Videos".id; Type: COMMENT; Schema: public; Owner: bashiron
--

COMMENT ON COLUMN public."Videos".id IS 'Generated ID';


--
-- Name: COLUMN "Videos".name; Type: COMMENT; Schema: public; Owner: bashiron
--

COMMENT ON COLUMN public."Videos".name IS 'Briefly distinguishes video from the rest. In case the entry is a music or OST type the name should be the name of the song followed by the name of the artist but it''s not really required, this column is informal and is mostly intended for user reading.';


--
-- Name: COLUMN "Videos".playlist; Type: COMMENT; Schema: public; Owner: bashiron
--

COMMENT ON COLUMN public."Videos".playlist IS 'Youtube playlists the video is assigned to';


--
-- Name: COLUMN "Videos".duration; Type: COMMENT; Schema: public; Owner: bashiron
--

COMMENT ON COLUMN public."Videos".duration IS 'Duration of the video';


--
-- Name: COLUMN "Videos".fragment; Type: COMMENT; Schema: public; Owner: bashiron
--

COMMENT ON COLUMN public."Videos".fragment IS '5 second piece of video for future recovery with search engine';


--
-- Name: COLUMN "Videos".category; Type: COMMENT; Schema: public; Owner: bashiron
--

COMMENT ON COLUMN public."Videos".category IS 'Categories the video belongs to. If properly mantained and updated, a row in this table should have one, and only one, associated row in each table representing each category. The raw info table will have data for all videos regardless of category';


--
-- Name: COLUMN "Videos".summary; Type: COMMENT; Schema: public; Owner: bashiron
--

COMMENT ON COLUMN public."Videos".summary IS 'Explains video details relevant to user and helps look for a new URL in case it gets taken down';


--
-- Name: COLUMN "Videos".alive; Type: COMMENT; Schema: public; Owner: bashiron
--

COMMENT ON COLUMN public."Videos".alive IS 'Represents whether the video is currently available at the URL';


--
-- Name: COLUMN "Videos".modified; Type: COMMENT; Schema: public; Owner: bashiron
--

COMMENT ON COLUMN public."Videos".modified IS 'Timestamp for when entry was last modified';


--
-- Name: COLUMN "Videos".created; Type: COMMENT; Schema: public; Owner: bashiron
--

COMMENT ON COLUMN public."Videos".created IS 'Date for when entry was created';


--
-- Name: COLUMN "Videos".apvid; Type: COMMENT; Schema: public; Owner: bashiron
--

COMMENT ON COLUMN public."Videos".apvid IS 'Video ID in the Youtube database. Append after https://youtu.be/ to get video URL. It has a max size of 14 chars because currently they are 11 chars long but we extend it a bit just in case';


--
-- Name: MetaOST ref_vid_id_pkey@ost; Type: CONSTRAINT; Schema: public; Owner: bashiron
--

ALTER TABLE ONLY public."MetaOST"
    ADD CONSTRAINT "ref_vid_id_pkey@ost" PRIMARY KEY (vid_id);


--
-- Name: MetaRaw ref_vid_id_pkey@raw; Type: CONSTRAINT; Schema: public; Owner: bashiron
--

ALTER TABLE ONLY public."MetaRaw"
    ADD CONSTRAINT "ref_vid_id_pkey@raw" PRIMARY KEY (vid_id);


--
-- Name: Playlists self_pl_aplid_pkey@playlists; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public."Playlists"
    ADD CONSTRAINT "self_pl_aplid_pkey@playlists" PRIMARY KEY (aplid);


--
-- Name: Videos self_vid_id_pkey@videos; Type: CONSTRAINT; Schema: public; Owner: bashiron
--

ALTER TABLE ONLY public."Videos"
    ADD CONSTRAINT "self_vid_id_pkey@videos" PRIMARY KEY (id);


--
-- Name: Videos vid_apvid_unique@videos; Type: CONSTRAINT; Schema: public; Owner: bashiron
--

ALTER TABLE ONLY public."Videos"
    ADD CONSTRAINT "vid_apvid_unique@videos" UNIQUE (apvid);


--
-- Name: Videos set_mod_time_trg; Type: TRIGGER; Schema: public; Owner: bashiron
--

CREATE TRIGGER set_mod_time_trg BEFORE UPDATE ON public."Videos" FOR EACH ROW EXECUTE FUNCTION public.set_mod_time_fun();


--
-- Name: MetaOST ref_vid_id_fkey@ost; Type: FK CONSTRAINT; Schema: public; Owner: bashiron
--

ALTER TABLE ONLY public."MetaOST"
    ADD CONSTRAINT "ref_vid_id_fkey@ost" FOREIGN KEY (vid_id) REFERENCES public."Videos"(id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: MetaRaw ref_vid_id_fkey@raw; Type: FK CONSTRAINT; Schema: public; Owner: bashiron
--

ALTER TABLE ONLY public."MetaRaw"
    ADD CONSTRAINT "ref_vid_id_fkey@raw" FOREIGN KEY (vid_id) REFERENCES public."Videos"(id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- PostgreSQL database dump complete
--