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