Feat: Add initial tables

1 files changed, 67 insertions, 0 deletions

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)
+);