Database connection

1 files changed, 65 insertions, 0 deletions

diff --git a/server/migrations/eyeballs.sql b/server/migrations/eyeballs.sql
new file mode 100644
index 0000000..d8163b2
--- /dev/null
+++ b/server/migrations/eyeballs.sql
@@ -0,0 +1,65 @@
+CREATE TABLE IF NOT EXISTS projects (
+       id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
+       title VARCHAR(1024) NOT NULL,
+       description MEDIUMTEXT NOT NULL DEFAULT ''
+);
+
+CREATE TABLE IF NOT EXISTS users (
+       id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
+       username VARCHAR(256) NOT NULL,
+       name VARCHAR(1024) NOT NULL DEFAULT '',
+       active BOOLEAN NOT NULL DEFAULT 1
+);
+
+CREATE TABLE IF NOT EXISTS project_users (
+       project BIGINT UNSIGNED NOT NULL,
+       user BIGINT UNSIGNED NOT NULL,
+       PRIMARY KEY (project, user),
+
+       CONSTRAINT `fk_project_users_project`
+         FOREIGN KEY (project) REFERENCES projects (id)
+         ON DELETE CASCADE
+         ON UPDATE RESTRICT,
+
+       CONSTRAINT `fk_project_users_user`
+         FOREIGN KEY (user) REFERENCES users (id)
+         ON DELETE CASCADE
+         ON UPDATE RESTRICT
+);
+
+CREATE TABLE IF NOT EXISTS reviews (
+       id BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
+       project BIGINT UNSIGNED NOT NULL,
+       owner BIGINT UNSIGNED NOT NULL,
+       title VARCHAR(1024) NOT NULL,
+       description MEDIUMTEXT NOT NULL DEFAULT '',
+       state TINYINT UNSIGNED NOT NULL DEFAULT 0,
+       progress FLOAT NOT NULL DEFAULT 0,
+
+       CONSTRAINT `fk_reviews_project`
+         FOREIGN KEY (project) REFERENCES projects (id)
+         ON DELETE CASCADE
+         ON UPDATE RESTRICT,
+
+       CONSTRAINT `fk_reviews_owner`
+         FOREIGN KEY (owner) REFERENCES users (id)
+         ON DELETE CASCADE
+         ON UPDATE RESTRICT
+);
+
+CREATE TABLE IF NOT EXISTS review_users (
+       review BIGINT UNSIGNED NOT NULL,
+       user BIGINT UNSIGNED NOT NULL,
+       role TINYINT UNSIGNED NOT NULL DEFAULT 0,
+       PRIMARY KEY (review, user),
+
+       CONSTRAINT `fk_review_users_review`
+         FOREIGN KEY (review) REFERENCES reviews (id)
+         ON DELETE CASCADE
+         ON UPDATE RESTRICT,
+
+       CONSTRAINT `fk_review_users_user`
+         FOREIGN KEY (user) REFERENCES users (id)
+         ON DELETE CASCADE
+         ON UPDATE RESTRICT
+);