X-Git-Url: https://git.distorted.org.uk/~mdw/dvddb/blobdiff_plain/14acb11fbceab6626284efa4a1b250f988a31076..HEAD:/create-v3

diff --git a/create-v3 b/create-v3
index 7207622..8e68193 100755
--- 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;