| 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 | CREATE TABLE dvd_set |
| 20 | (id INTEGER PRIMARY KEY NOT NULL |
| 21 | DEFAULT (nextval('dvd_set_id')), |
| 22 | name TEXT UNIQUE NOT NULL, |
| 23 | n_disc INTEGER NOT NULL); |
| 24 | CREATE TABLE dvd_disc |
| 25 | (set_id INTEGER NOT NULL |
| 26 | disc INTEGER NOT NULL, |
| 27 | path TEXT NOT NULL UNIQUE DEFERRABLE INITIALLY DEFERRED, |
| 28 | disc_id TEXT DEFAULT NULL UNIQUE, |
| 29 | box TEXT DEFAULT NULL, |
| 30 | PRIMARY KEY (set_id, disc), |
| 31 | FOREIGN KEY (set_id) REFERENCES dvd_set(id) |
| 32 | ON DELETE CASCADE |
| 33 | ON UPDATE CASCADE); |
| 34 | CREATE INDEX dvd_disc_by_box ON dvd_disc (box); |
| 35 | |
| 36 | CREATE TABLE series |
| 37 | (name TEXT PRIMARY KEY NOT NULL, |
| 38 | title TEXT NOT NULL); |
| 39 | |
| 40 | CREATE SEQUENCE media_id MINVALUE 0; |
| 41 | CREATE TABLE media |
| 42 | (id INTEGER PRIMARY KEY NOT NULL |
| 43 | DEFAULT (nextval('media_id')), |
| 44 | path TEXT NOT NULL, |
| 45 | title_number INTEGER NOT NULL DEFAULT (-1), |
| 46 | start_chapter INTEGER NOT NULL DEFAULT (-1), |
| 47 | end_chapter INTEGER NOT NULL DEFAULT (-1), |
| 48 | duration REAL NOT NULL, |
| 49 | title TEXT NOT NULL, |
| 50 | series_name TEXT NOT NULL, |
| 51 | UNIQUE (path, title_number, start_chapter, end_chapter), |
| 52 | FOREIGN KEY (series_name) REFERENCES series(name) |
| 53 | ON DELETE CASCADE |
| 54 | ON UPDATE CASCADE); |
| 55 | |
| 56 | CREATE TABLE playlist |
| 57 | (name TEXT UNIQUE NOT NULL, |
| 58 | n_entry INTEGER NOT NULL); |
| 59 | CREATE TABLE playlist_entry |
| 60 | (list_name TEXT NOT NULL, |
| 61 | entry INTEGER NOT NULL, |
| 62 | media_id INTEGER NOT NULL, |
| 63 | PRIMARY KEY (list_name, entry), |
| 64 | FOREIGN KEY (list_name) REFERENCES playlist(name) |
| 65 | ON DELETE CASCADE |
| 66 | ON UPDATE CASCADE, |
| 67 | FOREIGN KEY (media_id) REFERENCES media(id) |
| 68 | ON DELETE CASCADE |
| 69 | ON UPDATE CASCADE); |
| 70 | |
| 71 | CREATE TABLE playlist_group |
| 72 | (name TEXT PRIMARY KEY NOT NULL); |
| 73 | CREATE TABLE playlist_position |
| 74 | (group_name TEXT NOT NULL, |
| 75 | list_name TEXT NOT NULL, |
| 76 | next_entry INTEGER NOT NULL, |
| 77 | PRIMARY KEY (group_name, list_name), |
| 78 | FOREIGN KEY (group_name) REFERENCES playlist_group(name) |
| 79 | ON DELETE CASCADE |
| 80 | ON UPDATE CASCADE, |
| 81 | FOREIGN KEY (list_name) REFERENCES playlist(name) |
| 82 | ON DELETE CASCADE |
| 83 | ON UPDATE CASCADE, |
| 84 | FOREIGN KEY (list_name, next_entry) |
| 85 | REFERENCES playlist_entry(list_name, entry) |
| 86 | ON DELETE CASCADE |
| 87 | ON UPDATE CASCADE); |
| 88 | "); |
| 89 | |
| 90 | $DB->commit; $DB->disconnect; |