+#! /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;