Commit | Line | Data |
---|---|---|
14acb11f MW |
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; | |
14acb11f | 19 | CREATE TABLE dvd_set |
b2a25885 | 20 | (id INTEGER PRIMARY KEY NOT NULL |
14acb11f | 21 | DEFAULT (nextval('dvd_set_id')), |
b2a25885 MW |
22 | name TEXT UNIQUE NOT NULL, |
23 | n_disc INTEGER NOT NULL); | |
14acb11f | 24 | CREATE TABLE dvd_disc |
b2a25885 | 25 | (set_id INTEGER NOT NULL |
14acb11f | 26 | disc INTEGER NOT NULL, |
b2a25885 MW |
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')), | |
14acb11f | 42 | path TEXT NOT NULL, |
b2a25885 MW |
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); | |
14acb11f MW |
86 | "); |
87 | ||
88 | $DB->commit; $DB->disconnect; |