#! /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,
		 ndisc TEXT);
	CREATE UNIQUE INDEX dvd_set_by_name ON dvd_set (name);

	CREATE TABLE dvd_disc
		(set_id INTEGER NOT NULL,
		 disc INTEGER NOT NULL,
		 path TEXT NOT NULL,
		 disc_id TEXT DEFAULT NULL,
		 PRIMARY KEY (set_id, disc));
	CREATE INDEX dvd_disc_by_path ON dvd_disc (path);
	CREATE INDEX dvd_disc_by_id ON dvd_disc (disc_id);
");

$DB->commit; $DB->disconnect;