summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
Diffstat
-rw-r--r--src/invidious.cr3
-rw-r--r--src/invidious/migration.cr38
-rw-r--r--src/invidious/migrations/0000_create_channels_table.cr30
-rw-r--r--src/invidious/migrations/0001_create_videos_table.cr28
-rw-r--r--src/invidious/migrations/0002_create_channel_videos_table.cr35
-rw-r--r--src/invidious/migrations/0003_create_users_table.cr34
-rw-r--r--src/invidious/migrations/0004_create_session_ids_table.cr28
-rw-r--r--src/invidious/migrations/0005_create_nonces_table.cr27
-rw-r--r--src/invidious/migrations/0006_create_annotations_table.cr20
-rw-r--r--src/invidious/migrations/0007_create_playlists_table.cr47
-rw-r--r--src/invidious/migrations/0008_create_playlist_videos_table.cr27
-rw-r--r--src/invidious/migrator.cr41
12 files changed, 358 insertions, 0 deletions
diff --git a/src/invidious.cr b/src/invidious.cr
index 1ff70905..6ec5f3a5 100644
--- a/src/invidious.cr
+++ b/src/invidious.cr
@@ -34,6 +34,7 @@ require "./invidious/channels/*"
require "./invidious/user/*"
require "./invidious/routes/**"
require "./invidious/jobs/**"
+require "./invidious/migrations/*"
CONFIG = Config.load
HMAC_KEY = CONFIG.hmac_key || Random::Secure.hex(32)
@@ -111,6 +112,8 @@ end
OUTPUT = CONFIG.output.upcase == "STDOUT" ? STDOUT : File.open(CONFIG.output, mode: "a")
LOGGER = Invidious::LogHandler.new(OUTPUT, CONFIG.log_level)
+# Run migrations
+Invidious::Migrator.new(PG_DB).migrate
# Check table integrity
Invidious::Database.check_integrity(CONFIG)
diff --git a/src/invidious/migration.cr b/src/invidious/migration.cr
new file mode 100644
index 00000000..a4eec1c5
--- /dev/null
+++ b/src/invidious/migration.cr
@@ -0,0 +1,38 @@
+abstract class Invidious::Migration
+ macro inherited
+ Invidious::Migrator.migrations << self
+ end
+
+ @@version : Int64?
+
+ def self.version(version : Int32 | Int64)
+ @@version = version.to_i64
+ end
+
+ getter? completed = false
+
+ def initialize(@db : DB::Database)
+ end
+
+ abstract def up(conn : DB::Connection)
+
+ def migrate
+ # migrator already ignores completed migrations
+ # but this is an extra check to make sure a migration doesn't run twice
+ return if completed?
+
+ @db.transaction do |txn|
+ up(txn.connection)
+ track(txn.connection)
+ @completed = true
+ end
+ end
+
+ def version : Int64
+ @@version.not_nil!
+ end
+
+ private def track(conn : DB::Connection)
+ conn.exec("INSERT INTO #{Invidious::Migrator::MIGRATIONS_TABLE}(version) VALUES ($1)", version)
+ end
+end
diff --git a/src/invidious/migrations/0000_create_channels_table.cr b/src/invidious/migrations/0000_create_channels_table.cr
new file mode 100644
index 00000000..1f8f18e2
--- /dev/null
+++ b/src/invidious/migrations/0000_create_channels_table.cr
@@ -0,0 +1,30 @@
+module Invidious::Migrations
+ class CreateChannelsTable < Migration
+ version 0
+
+ def up(conn : DB::Connection)
+ conn.exec <<-SQL
+ CREATE TABLE IF NOT EXISTS public.channels
+ (
+ id text NOT NULL,
+ author text,
+ updated timestamp with time zone,
+ deleted boolean,
+ subscribed timestamp with time zone,
+ CONSTRAINT channels_id_key UNIQUE (id)
+ );
+ SQL
+
+ conn.exec <<-SQL
+ GRANT ALL ON TABLE public.channels TO current_user;
+ SQL
+
+ conn.exec <<-SQL
+ CREATE INDEX IF NOT EXISTS channels_id_idx
+ ON public.channels
+ USING btree
+ (id COLLATE pg_catalog."default");
+ SQL
+ end
+ end
+end
diff --git a/src/invidious/migrations/0001_create_videos_table.cr b/src/invidious/migrations/0001_create_videos_table.cr
new file mode 100644
index 00000000..cdc9993f
--- /dev/null
+++ b/src/invidious/migrations/0001_create_videos_table.cr
@@ -0,0 +1,28 @@
+module Invidious::Migrations
+ class CreateVideosTable < Migration
+ version 1
+
+ def up(conn : DB::Connection)
+ conn.exec <<-SQL
+ CREATE UNLOGGED TABLE IF NOT EXISTS public.videos
+ (
+ id text NOT NULL,
+ info text,
+ updated timestamp with time zone,
+ CONSTRAINT videos_pkey PRIMARY KEY (id)
+ );
+ SQL
+
+ conn.exec <<-SQL
+ GRANT ALL ON TABLE public.videos TO current_user;
+ SQL
+
+ conn.exec <<-SQL
+ CREATE UNIQUE INDEX IF NOT EXISTS id_idx
+ ON public.videos
+ USING btree
+ (id COLLATE pg_catalog."default");
+ SQL
+ end
+ end
+end
diff --git a/src/invidious/migrations/0002_create_channel_videos_table.cr b/src/invidious/migrations/0002_create_channel_videos_table.cr
new file mode 100644
index 00000000..737abad4
--- /dev/null
+++ b/src/invidious/migrations/0002_create_channel_videos_table.cr
@@ -0,0 +1,35 @@
+module Invidious::Migrations
+ class CreateChannelVideosTable < Migration
+ version 2
+
+ def up(conn : DB::Connection)
+ conn.exec <<-SQL
+ CREATE TABLE IF NOT EXISTS public.channel_videos
+ (
+ id text NOT NULL,
+ title text,
+ published timestamp with time zone,
+ updated timestamp with time zone,
+ ucid text,
+ author text,
+ length_seconds integer,
+ live_now boolean,
+ premiere_timestamp timestamp with time zone,
+ views bigint,
+ CONSTRAINT channel_videos_id_key UNIQUE (id)
+ );
+ SQL
+
+ conn.exec <<-SQL
+ GRANT ALL ON TABLE public.channel_videos TO current_user;
+ SQL
+
+ conn.exec <<-SQL
+ CREATE INDEX IF NOT EXISTS channel_videos_ucid_idx
+ ON public.channel_videos
+ USING btree
+ (ucid COLLATE pg_catalog."default");
+ SQL
+ end
+ end
+end
diff --git a/src/invidious/migrations/0003_create_users_table.cr b/src/invidious/migrations/0003_create_users_table.cr
new file mode 100644
index 00000000..d91cca8d
--- /dev/null
+++ b/src/invidious/migrations/0003_create_users_table.cr
@@ -0,0 +1,34 @@
+module Invidious::Migrations
+ class CreateUsersTable < Migration
+ version 3
+
+ def up(conn : DB::Connection)
+ conn.exec <<-SQL
+ CREATE TABLE IF NOT EXISTS public.users
+ (
+ updated timestamp with time zone,
+ notifications text[],
+ subscriptions text[],
+ email text NOT NULL,
+ preferences text,
+ password text,
+ token text,
+ watched text[],
+ feed_needs_update boolean,
+ CONSTRAINT users_email_key UNIQUE (email)
+ );
+ SQL
+
+ conn.exec <<-SQL
+ GRANT ALL ON TABLE public.users TO current_user;
+ SQL
+
+ conn.exec <<-SQL
+ CREATE UNIQUE INDEX IF NOT EXISTS email_unique_idx
+ ON public.users
+ USING btree
+ (lower(email) COLLATE pg_catalog."default");
+ SQL
+ end
+ end
+end
diff --git a/src/invidious/migrations/0004_create_session_ids_table.cr b/src/invidious/migrations/0004_create_session_ids_table.cr
new file mode 100644
index 00000000..9ef00f78
--- /dev/null
+++ b/src/invidious/migrations/0004_create_session_ids_table.cr
@@ -0,0 +1,28 @@
+module Invidious::Migrations
+ class CreateSessionIdsTable < Migration
+ version 4
+
+ def up(conn : DB::Connection)
+ conn.exec <<-SQL
+ CREATE TABLE IF NOT EXISTS public.session_ids
+ (
+ id text NOT NULL,
+ email text,
+ issued timestamp with time zone,
+ CONSTRAINT session_ids_pkey PRIMARY KEY (id)
+ );
+ SQL
+
+ conn.exec <<-SQL
+ GRANT ALL ON TABLE public.session_ids TO current_user;
+ SQL
+
+ conn.exec <<-SQL
+ CREATE INDEX IF NOT EXISTS session_ids_id_idx
+ ON public.session_ids
+ USING btree
+ (id COLLATE pg_catalog."default");
+ SQL
+ end
+ end
+end
diff --git a/src/invidious/migrations/0005_create_nonces_table.cr b/src/invidious/migrations/0005_create_nonces_table.cr
new file mode 100644
index 00000000..4b1220e6
--- /dev/null
+++ b/src/invidious/migrations/0005_create_nonces_table.cr
@@ -0,0 +1,27 @@
+module Invidious::Migrations
+ class CreateNoncesTable < Migration
+ version 5
+
+ def up(conn : DB::Connection)
+ conn.exec <<-SQL
+ CREATE TABLE IF NOT EXISTS public.nonces
+ (
+ nonce text,
+ expire timestamp with time zone,
+ CONSTRAINT nonces_id_key UNIQUE (nonce)
+ );
+ SQL
+
+ conn.exec <<-SQL
+ GRANT ALL ON TABLE public.nonces TO current_user;
+ SQL
+
+ conn.exec <<-SQL
+ CREATE INDEX IF NOT EXISTS nonces_nonce_idx
+ ON public.nonces
+ USING btree
+ (nonce COLLATE pg_catalog."default");
+ SQL
+ end
+ end
+end
diff --git a/src/invidious/migrations/0006_create_annotations_table.cr b/src/invidious/migrations/0006_create_annotations_table.cr
new file mode 100644
index 00000000..86f21dd9
--- /dev/null
+++ b/src/invidious/migrations/0006_create_annotations_table.cr
@@ -0,0 +1,20 @@
+module Invidious::Migrations
+ class CreateAnnotationsTable < Migration
+ version 6
+
+ def up(conn : DB::Connection)
+ conn.exec <<-SQL
+ CREATE TABLE IF NOT EXISTS public.annotations
+ (
+ id text NOT NULL,
+ annotations xml,
+ CONSTRAINT annotations_id_key UNIQUE (id)
+ );
+ SQL
+
+ conn.exec <<-SQL
+ GRANT ALL ON TABLE public.annotations TO current_user;
+ SQL
+ end
+ end
+end
diff --git a/src/invidious/migrations/0007_create_playlists_table.cr b/src/invidious/migrations/0007_create_playlists_table.cr
new file mode 100644
index 00000000..81217365
--- /dev/null
+++ b/src/invidious/migrations/0007_create_playlists_table.cr
@@ -0,0 +1,47 @@
+module Invidious::Migrations
+ class CreatePlaylistsTable < Migration
+ version 7
+
+ def up(conn : DB::Connection)
+ conn.exec <<-SQL
+ DO
+ $$
+ BEGIN
+ IF NOT EXISTS (SELECT *
+ FROM pg_type typ
+ INNER JOIN pg_namespace nsp ON nsp.oid = typ.typnamespace
+ WHERE nsp.nspname = 'public'
+ AND typ.typname = 'privacy') THEN
+ CREATE TYPE public.privacy AS ENUM
+ (
+ 'Public',
+ 'Unlisted',
+ 'Private'
+ );
+ END IF;
+ END;
+ $$
+ LANGUAGE plpgsql;
+ SQL
+
+ conn.exec <<-SQL
+ CREATE TABLE IF NOT EXISTS public.playlists
+ (
+ title text,
+ id text primary key,
+ author text,
+ description text,
+ video_count integer,
+ created timestamptz,
+ updated timestamptz,
+ privacy privacy,
+ index int8[]
+ );
+ SQL
+
+ conn.exec <<-SQL
+ GRANT ALL ON public.playlists TO current_user;
+ SQL
+ end
+ end
+end
diff --git a/src/invidious/migrations/0008_create_playlist_videos_table.cr b/src/invidious/migrations/0008_create_playlist_videos_table.cr
new file mode 100644
index 00000000..80fa6b5f
--- /dev/null
+++ b/src/invidious/migrations/0008_create_playlist_videos_table.cr
@@ -0,0 +1,27 @@
+module Invidious::Migrations
+ class CreatePlaylistVideosTable < Migration
+ version 8
+
+ def up(conn : DB::Connection)
+ conn.exec <<-SQL
+ 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)
+ );
+ SQL
+
+ conn.exec <<-SQL
+ GRANT ALL ON TABLE public.playlist_videos TO current_user;
+ SQL
+ end
+ end
+end
diff --git a/src/invidious/migrator.cr b/src/invidious/migrator.cr
new file mode 100644
index 00000000..dc6880b9
--- /dev/null
+++ b/src/invidious/migrator.cr
@@ -0,0 +1,41 @@
+class Invidious::Migrator
+ MIGRATIONS_TABLE = "invidious_migrations"
+
+ class_getter migrations = [] of Invidious::Migration.class
+
+ def initialize(@db : DB::Database)
+ end
+
+ def migrate
+ run_migrations = load_run_migrations
+ migrations = load_migrations.sort_by(&.version)
+ migrations_to_run = migrations.reject { |migration| run_migrations.includes?(migration.version) }
+ if migrations.empty?
+ puts "No migrations to run."
+ return
+ end
+
+ migrations_to_run.each do |migration|
+ puts "Running migration: #{migration.class.name}"
+ migration.migrate
+ end
+ end
+
+ private def load_migrations : Array(Invidious::Migration)
+ self.class.migrations.map(&.new(@db))
+ end
+
+ private def load_run_migrations : Array(Int64)
+ create_migrations_table
+ @db.query_all("SELECT version FROM #{MIGRATIONS_TABLE}", as: Int64)
+ end
+
+ private def create_migrations_table
+ @db.exec <<-SQL
+ CREATE TABLE IF NOT EXISTS #{MIGRATIONS_TABLE} (
+ id bigserial PRIMARY KEY,
+ version bigint NOT NULL
+ )
+ SQL
+ end
+end
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-17 18:00:07 +0000