chiark - git - mdw - dvddb/blob - create-v3

   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;