7 use Encode
qw{encode_utf8 decode_utf8
};
10 BEGIN { binmode STDOUT
, ":utf8"; }
12 my $ROOT = "/mnt/dvd/archive";
13 my $DB = DBI
->connect("dbi:Pg:host=roadstar", "", "",
18 CREATE SEQUENCE dvd_set_id MINVALUE 0;
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);
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)
35 (name TEXT PRIMARY KEY NOT NULL,
38 CREATE SEQUENCE media_id MINVALUE 0;
40 (id INTEGER PRIMARY KEY NOT NULL
41 DEFAULT (nextval('media_id')),
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,
48 series_name TEXT NOT NULL,
49 UNIQUE (path, title_number, start_chapter, end_chapter),
50 FOREIGN KEY (series_name) REFERENCES series(name)
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)
65 FOREIGN KEY (media_id) REFERENCES media(id)
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)
79 FOREIGN KEY (list_name) REFERENCES playlist(name)
82 FOREIGN KEY (list_name, next_entry)
83 REFERENCES playlist_entry(list_name, entry)
88 $DB->commit; $DB->disconnect;