Initial support for translation reviews

1 files changed, 88 insertions, 0 deletions

diff --git a/server/migrations/1_initial_eyeballs.sql b/server/migrations/1_initial_eyeballs.sql
index a39202d..3762c83 100644
--- a/server/migrations/1_initial_eyeballs.sql
+++ b/server/migrations/1_initial_eyeballs.sql
@@ -83,3 +83,91 @@ CREATE TABLE IF NOT EXISTS user_keys (
          ON DELETE CASCADE
          ON UPDATE RESTRICT
 );
+
+CREATE TABLE IF NOT EXISTS translation_reviews (
+       id BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
+       project VARCHAR(128) NOT NULL,
+       owner VARCHAR(128) 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,
+       archived BOOLEAN NOT NULL DEFAULT 0,
+       base VARCHAR(40) NOT NULL,
+       head VARCHAR(40) NOT NULL,
+
+       CONSTRAINT `fk_translation_reviews_project`
+         FOREIGN KEY (project) REFERENCES projects (id)
+         ON DELETE CASCADE
+         ON UPDATE RESTRICT,
+
+       CONSTRAINT `fk_translation_reviews_owner`
+         FOREIGN KEY (owner) REFERENCES users (id)
+         ON DELETE CASCADE
+         ON UPDATE RESTRICT
+);
+
+CREATE TABLE IF NOT EXISTS translation_review_users (
+       translation_review BIGINT UNSIGNED NOT NULL,
+       user VARCHAR(128) NOT NULL,
+       role TINYINT UNSIGNED NOT NULL DEFAULT 0,
+       PRIMARY KEY (translation_review, user),
+
+       CONSTRAINT `fk_translation_review_users_translation_review`
+         FOREIGN KEY (translation_review) REFERENCES translation_reviews (id)
+         ON DELETE CASCADE
+         ON UPDATE RESTRICT,
+
+       CONSTRAINT `fk_translation_review_users_user`
+         FOREIGN KEY (user) REFERENCES users (id)
+         ON DELETE CASCADE
+         ON UPDATE RESTRICT
+);
+
+CREATE TABLE IF NOT EXISTS localization_strings (
+       id BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
+       translation_review BIGINT UNSIGNED NOT NULL,
+       name VARCHAR(512) NOT NULL,
+       file VARCHAR(512) NOT NULL,
+       description MEDIUMTEXT NOT NULL DEFAULT '',
+       meaning VARCHAR(512) NOT NULL DEFAULT '',
+       source MEDIUMTEXT NOT NULL,
+       placeholder_offsets VARCHAR(512) NOT NULL DEFAULT '',
+
+       CONSTRAINT `fk_localization_strings_translation_review`
+         FOREIGN KEY (translation_review) REFERENCES translation_reviews (id)
+         ON DELETE CASCADE
+         ON UPDATE RESTRICT
+);
+
+CREATE TABLE IF NOT EXISTS localization_placeholders (
+       id BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
+       localization_string BIGINT UNSIGNED NOT NULL,
+       name VARCHAR(128) NOT NULL,
+       content VARCHAR(256) NOT NULL,
+       example VARCHAR(256) NOT NULL DEFAULT '',
+
+       CONSTRAINT `fk_localization_placeholder_localization_string`
+         FOREIGN KEY (localization_string) REFERENCES localization_strings (id)
+         ON DELETE CASCADE
+         ON UPDATE RESTRICT
+);
+
+CREATE TABLE IF NOT EXISTS translation_strings (
+       localization_string BIGINT UNSIGNED NOT NULL,
+       language VARCHAR(10) NOT NULL,
+       base_translation MEDIUMTEXT,
+       base_placeholder_offsets VARCHAR(512),
+       head_translation MEDIUMTEXT NOT NULL,
+       head_placeholder_offsets VARCHAR(512) NOT NULL DEFAULT '',
+       state TINYINT UNSIGNED NOT NULL DEFAULT 0,
+       comment MEDIUMTEXT NOT NULL DEFAULT '',
+       reviewer VARCHAR(128),
+
+       CONSTRAINT `fk_translation_string_localization_string`
+         FOREIGN KEY (localization_string) REFERENCES localization_strings (id)
+         ON DELETE CASCADE
+         ON UPDATE RESTRICT,
+
+       PRIMARY KEY (localization_string, language)
+);