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

[dvddb] / create-v4

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