| 1 | #! /usr/bin/perl -w |
| 2 | |
| 3 | use open ":utf8"; |
| 4 | use strict; |
| 5 | |
| 6 | use DBI; |
| 7 | use Encode qw{encode_utf8 decode_utf8}; |
| 8 | use File::Find; |
| 9 | |
| 10 | BEGIN { binmode STDOUT, ":utf8"; } |
| 11 | |
| 12 | my $ROOT = "/mnt/dvd/archive"; |
| 13 | my $DB = DBI->connect("dbi:Pg:host=roadstar", "", "", |
| 14 | { AutoCommit => 0, |
| 15 | RaiseError => 1 }); |
| 16 | |
| 17 | $DB->do(" |
| 18 | CREATE SEQUENCE dvd_set_id MINVALUE 0; |
| 19 | |
| 20 | CREATE TABLE dvd_set |
| 21 | (id INTEGER |
| 22 | PRIMARY KEY |
| 23 | NOT NULL |
| 24 | DEFAULT (nextval('dvd_set_id')), |
| 25 | name TEXT, |
| 26 | ndisc TEXT); |
| 27 | CREATE UNIQUE INDEX dvd_set_by_name ON dvd_set (name); |
| 28 | |
| 29 | CREATE TABLE dvd_disc |
| 30 | (set_id INTEGER NOT NULL, |
| 31 | disc INTEGER NOT NULL, |
| 32 | path TEXT NOT NULL, |
| 33 | disc_id TEXT DEFAULT NULL, |
| 34 | PRIMARY KEY (set_id, disc)); |
| 35 | CREATE INDEX dvd_disc_by_path ON dvd_disc (path); |
| 36 | CREATE INDEX dvd_disc_by_id ON dvd_disc (disc_id); |
| 37 | "); |
| 38 | |
| 39 | $DB->commit; $DB->disconnect; |