Playlist support, other randomness.

[dvddb] / create-v3

diff --git a/create-v3 b/create-v3
index 7207622..8e68193 100755 (executable)
--- a/create-v3
+++ b/create-v3
@@ -16,24 +16,73 @@ my $DB = DBI->connect("dbi:Pg:host=roadstar", "", "",
 
 $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;