invidious/config/sql/playlist_videos.sql

-- Table: public.playlist_videos

-- DROP TABLE public.playlist_videos;

CREATE TABLE IF NOT EXISTS public.playlist_videos
(
    title text,
    id text,
    author text,
    ucid text,
    length_seconds integer,
    published timestamptz,
    plid text references playlists(id),
    index int8,
    live_now boolean,
    PRIMARY KEY (index,plid)
);

GRANT ALL ON TABLE public.playlist_videos TO current_user;
Add support for custom playlists 2019-08-05 19:49:13 -04:00			`-- Table: public.playlist_videos`

			`-- DROP TABLE public.playlist_videos;`

Specify the public schema for public videos Unlike the other migrations, this one uses a bare table name to specify the table. This means it looks in the default search path. Unfortunately, the out of box search path is: 1. Schema with the same name as the user 2. Public Because of this, if a schema exists with the same name as the DB user, the playlist_videos table will get created in _that_ schema, rather than the public schema, and then the grant statement will fail with an error like below if check_table is enabled as it keeps trying to create the table but failing on the grant statement: ``` 2022-01-22 02:27:42 UTC [info] check_table: check_table: CREATE TABLE playlist_videos Unhandled exception: relation "public.playlist_videos" does not exist (PQ::PQError) from /usr/share/crystal/src/primitives.cr:266:3 in 'handle_error' from lib/pg/src/pq/connection.cr:184:9 in 'handle_async_frames' from lib/pg/src/pq/connection.cr:162:7 in 'read' from lib/pg/src/pq/query.cr:53:14 in 'exec_all' from lib/db/src/db/database.cr:126:9 in '__crystal_main' from /usr/share/crystal/src/crystal/main.cr:110:5 in 'main' from src/env/__libc_start_main.c:94:2 in 'libc_start_main_stage2' 2022-01-22 02:27:43 UTC [info] check_table: check_table: CREATE TABLE playlist_videos Unhandled exception: relation "public.playlist_videos" does not exist (PQ::PQError) from /usr/share/crystal/src/primitives.cr:266:3 in 'handle_error' from lib/pg/src/pq/connection.cr:184:9 in 'handle_async_frames' from lib/pg/src/pq/connection.cr:162:7 in 'read' from lib/pg/src/pq/query.cr:53:14 in 'exec_all' from lib/db/src/db/database.cr:126:9 in '__crystal_main' from /usr/share/crystal/src/crystal/main.cr:110:5 in 'main' from src/env/__libc_start_main.c:94:2 in 'libc_start_main_stage2' ``` 2022-01-21 21:32:13 -05:00			`CREATE TABLE IF NOT EXISTS public.playlist_videos`
Add support for custom playlists 2019-08-05 19:49:13 -04:00			`(`
			`title text,`
			`id text,`
			`author text,`
			`ucid text,`
			`length_seconds integer,`
			`published timestamptz,`
			`plid text references playlists(id),`
			`index int8,`
			`live_now boolean,`
			`PRIMARY KEY (index,plid)`
			`);`

Make migrations (mostly) idempotent. 2021-01-16 20:44:31 -05:00			`GRANT ALL ON TABLE public.playlist_videos TO current_user;`