$DB->do("
CREATE SEQUENCE dvd_set_id MINVALUE 0;
-
CREATE TABLE dvd_set
- (id INTEGER
- PRIMARY KEY
- NOT NULL
+ (id INTEGER PRIMARY KEY NOT NULL
DEFAULT (nextval('dvd_set_id')),
- name TEXT,
- ndisc TEXT);
- CREATE UNIQUE INDEX dvd_set_by_name ON dvd_set (name);
-
+ name TEXT UNIQUE NOT NULL,
+ n_disc INTEGER NOT NULL);
CREATE TABLE dvd_disc
- (set_id INTEGER NOT NULL,
+ (set_id INTEGER NOT NULL
disc INTEGER NOT NULL,
+ path TEXT NOT NULL UNIQUE DEFERRABLE INITIALLY DEFERRED,
+ disc_id TEXT DEFAULT NULL UNIQUE,
+ PRIMARY KEY (set_id, disc),
+ FOREIGN KEY (set_id) REFERENCES dvd_set(id)
+ ON DELETE CASCADE
+ ON UPDATE CASCADE);
+
+ CREATE TABLE series
+ (name TEXT PRIMARY KEY NOT NULL,
+ title TEXT NOT NULL);
+
+ CREATE SEQUENCE media_id MINVALUE 0;
+ CREATE TABLE media
+ (id INTEGER PRIMARY KEY NOT NULL
+ DEFAULT (nextval('media_id')),
path TEXT NOT NULL,
- disc_id TEXT DEFAULT NULL,
- PRIMARY KEY (set_id, disc));
- CREATE INDEX dvd_disc_by_path ON dvd_disc (path);
- CREATE INDEX dvd_disc_by_id ON dvd_disc (disc_id);
+ title_number INTEGER NOT NULL DEFAULT (-1),
+ start_chapter INTEGER NOT NULL DEFAULT (-1),
+ end_chapter INTEGER NOT NULL DEFAULT (-1),
+ duration REAL NOT NULL,
+ title TEXT NOT NULL,
+ series_name TEXT NOT NULL,
+ UNIQUE (path, title_number, start_chapter, end_chapter),
+ FOREIGN KEY (series_name) REFERENCES series(name)
+ ON DELETE CASCADE
+ ON UPDATE CASCADE);
+
+ CREATE TABLE playlist
+ (name TEXT UNIQUE NOT NULL,
+ n_entry INTEGER NOT NULL);
+ CREATE TABLE playlist_entry
+ (list_name TEXT NOT NULL,
+ entry INTEGER NOT NULL,
+ media_id INTEGER NOT NULL,
+ PRIMARY KEY (list_name, entry),
+ FOREIGN KEY (list_name) REFERENCES playlist(name)
+ ON DELETE CASCADE
+ ON UPDATE CASCADE,
+ FOREIGN KEY (media_id) REFERENCES media(id)
+ ON DELETE CASCADE
+ ON UPDATE CASCADE);
+
+ CREATE TABLE playlist_group
+ (name TEXT PRIMARY KEY NOT NULL);
+ CREATE TABLE playlist_position
+ (group_name TEXT NOT NULL,
+ list_name TEXT NOT NULL,
+ next_entry INTEGER NOT NULL,
+ PRIMARY KEY (group_name, list_name),
+ FOREIGN KEY (group_name) REFERENCES playlist_group(name)
+ ON DELETE CASCADE
+ ON UPDATE CASCADE,
+ FOREIGN KEY (list_name) REFERENCES playlist(name)
+ ON DELETE CASCADE
+ ON UPDATE CASCADE,
+ FOREIGN KEY (list_name, next_entry)
+ REFERENCES playlist_entry(list_name, entry)
+ ON DELETE CASCADE
+ ON UPDATE CASCADE);
");
$DB->commit; $DB->disconnect;