create-v4, report, update: Keep track of which box each disc is in.

[dvddb] / create-v4

diff --git a/create-v4 b/create-v4
new file mode 100755 (executable)
index 0000000..d8ddaf5
--- /dev/null
+++ b/create-v4
@@ -0,0 +1,90 @@
+#! /usr/bin/perl -w
+
+use open ":utf8";
+use strict;
+
+use DBI;
+use Encode qw{encode_utf8 decode_utf8};
+use File::Find;
+
+BEGIN { binmode STDOUT, ":utf8"; }
+
+my $ROOT = "/mnt/dvd/archive";
+my $DB = DBI->connect("dbi:Pg:host=roadstar", "", "",
+                     { AutoCommit => 0,
+                       RaiseError => 1 });
+
+$DB->do("
+       CREATE SEQUENCE dvd_set_id MINVALUE 0;
+       CREATE TABLE dvd_set
+               (id INTEGER PRIMARY KEY NOT NULL
+                       DEFAULT (nextval('dvd_set_id')),
+                name TEXT UNIQUE NOT NULL,
+                n_disc INTEGER NOT NULL);
+       CREATE TABLE dvd_disc
+               (set_id INTEGER NOT NULL
+                disc INTEGER NOT NULL,
+                path TEXT NOT NULL UNIQUE DEFERRABLE INITIALLY DEFERRED,
+                disc_id TEXT DEFAULT NULL UNIQUE,
+                box TEXT DEFAULT NULL,
+                PRIMARY KEY (set_id, disc),
+                FOREIGN KEY (set_id) REFERENCES dvd_set(id)
+                       ON DELETE CASCADE
+                       ON UPDATE CASCADE);
+       CREATE INDEX dvd_disc_by_box ON dvd_disc (box);
+
+       CREATE TABLE series
+               (name TEXT PRIMARY KEY NOT NULL,
+                title TEXT NOT NULL);
+
+       CREATE SEQUENCE media_id MINVALUE 0;
+       CREATE TABLE media
+               (id INTEGER PRIMARY KEY NOT NULL
+                       DEFAULT (nextval('media_id')),
+                path TEXT NOT NULL,
+                title_number INTEGER NOT NULL DEFAULT (-1),
+                start_chapter INTEGER NOT NULL DEFAULT (-1),
+                end_chapter INTEGER NOT NULL DEFAULT (-1),
+                duration REAL NOT NULL,
+                title TEXT NOT NULL,
+                series_name TEXT NOT NULL,
+                UNIQUE (path, title_number, start_chapter, end_chapter),
+                FOREIGN KEY (series_name) REFERENCES series(name)
+                       ON DELETE CASCADE
+                       ON UPDATE CASCADE);
+
+       CREATE TABLE playlist
+               (name TEXT UNIQUE NOT NULL,
+                n_entry INTEGER NOT NULL);
+       CREATE TABLE playlist_entry
+               (list_name TEXT NOT NULL,
+                entry INTEGER NOT NULL,
+                media_id INTEGER NOT NULL,
+                PRIMARY KEY (list_name, entry),
+                FOREIGN KEY (list_name) REFERENCES playlist(name)
+                       ON DELETE CASCADE
+                       ON UPDATE CASCADE,
+                FOREIGN KEY (media_id) REFERENCES media(id)
+                       ON DELETE CASCADE
+                       ON UPDATE CASCADE);
+
+       CREATE TABLE playlist_group
+               (name TEXT PRIMARY KEY NOT NULL);
+       CREATE TABLE playlist_position
+               (group_name TEXT NOT NULL,
+                list_name TEXT NOT NULL,
+                next_entry INTEGER NOT NULL,
+                PRIMARY KEY (group_name, list_name),
+                FOREIGN KEY (group_name) REFERENCES playlist_group(name)
+                       ON DELETE CASCADE
+                       ON UPDATE CASCADE,
+                FOREIGN KEY (list_name) REFERENCES playlist(name)
+                       ON DELETE CASCADE
+                       ON UPDATE CASCADE,
+                FOREIGN KEY (list_name, next_entry)
+                       REFERENCES playlist_entry(list_name, entry)
+                       ON DELETE CASCADE
+                       ON UPDATE CASCADE);
+");
+
+$DB->commit; $DB->disconnect;