From aebd01540cc09724f68366709281d2350f758f8c Mon Sep 17 00:00:00 2001 From: WriterV2 Date: Sat, 26 Oct 2024 20:53:10 +0200 Subject: Feat: Add initial tables --- migrations/20241026164427_init.sql | 67 ++++++++++++++++++++++++++++++++++++++ 1 file changed, 67 insertions(+) create mode 100644 migrations/20241026164427_init.sql (limited to 'migrations/20241026164427_init.sql') diff --git a/migrations/20241026164427_init.sql b/migrations/20241026164427_init.sql new file mode 100644 index 0000000..be6ffca --- /dev/null +++ b/migrations/20241026164427_init.sql @@ -0,0 +1,67 @@ +CREATE TABLE IF NOT EXISTS product +( + id INTEGER PRIMARY KEY NOT NULL, + name TEXT NOT NULL UNIQUE, + description TEXT NOT NULL UNIQUE, + uploaddate INTEGER NOT NULL, + updatedate INTEGER NOT NULL +); + +CREATE TABLE IF NOT EXISTS story +( + id INTEGER PRIMARY KEY NOT NULL, + language TEXT NOT NULL, + pdf BLOB NOT NULL, + epub BLOB NOT NULL, + pid INTEGER NOT NULL, + FOREIGN KEY (pid) REFERENCES product(id) +); + +CREATE TABLE IF NOT EXISTS tool +( + id INTEGER PRIMARY KEY NOT NULL, + repolink TEXT NOT NULL, + pid INTEGER NOT NULL, + FOREIGN KEY (pid) REFERENCES product(id) +); + +CREATE TABLE IF NOT EXISTS game +( + id INTEGER PRIMARY KEY NOT NULL, + repolink TEXT NOT NULL, + pid INTEGER NOT NULL, + FOREIGN KEY (pid) REFERENCES product(id) +); + +CREATE TABLE IF NOT EXISTS tag +( + id INTEGER PRIMARY KEY NOT NULL, + name TEXT NOT NULL +); + +CREATE TABLE IF NOT EXISTS storytag +( + sid INTEGER NOT NULL, + tgid INTEGER NOT NULL, + FOREIGN KEY (sid) REFERENCES story(id), + FOREIGN KEY (tgid) REFERENCES tag(id), + PRIMARY KEY (sid, tgid) +); + +CREATE TABLE IF NOT EXISTS tooltag +( + tid INTEGER NOT NULL, + tgid INTEGER NOT NULL, + FOREIGN KEY (tid) REFERENCES tool(id), + FOREIGN KEY (tgid) REFERENCES tag(id), + PRIMARY KEY (tid, tgid) +); + +CREATE TABLE IF NOT EXISTS gametag +( + gid INTEGER NOT NULL, + tgid INTEGER NOT NULL, + FOREIGN KEY (gid) REFERENCES game(id), + FOREIGN KEY (tgid) REFERENCES tag(id), + PRIMARY KEY (gid, tgid) +); -- cgit v1.2.3