Toppers-Tasks/db/init.sql

-- MySQL Workbench Forward Engineering
SET @OLD_UNIQUE_CHECKS = @@UNIQUE_CHECKS,
  UNIQUE_CHECKS = 0;
SET @OLD_FOREIGN_KEY_CHECKS = @@FOREIGN_KEY_CHECKS,
  FOREIGN_KEY_CHECKS = 0;
SET @OLD_SQL_MODE = @@SQL_MODE,
  SQL_MODE = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema topperstasks
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema topperstasks
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `topperstasks` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
USE `topperstasks`;
-- -----------------------------------------------------
-- Table `topperstasks`.`users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `topperstasks`.`users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(64) NOT NULL,
  `passhash` VARCHAR(60) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL,
  `is_admin` TINYINT(1) NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `users_unique_1` (`username` ASC) VISIBLE
) ENGINE = InnoDB AUTO_INCREMENT = 4 DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;
-- -----------------------------------------------------
-- Table `topperstasks`.`tasks`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `topperstasks`.`tasks` (
  `tasktext` TEXT NOT NULL,
  `id` INT NOT NULL AUTO_INCREMENT,
  `created_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `due` DATETIME NULL DEFAULT NULL,
  `deleted` TINYINT(1) NULL DEFAULT '0',
  `completed` TINYINT(1) NULL DEFAULT '0',
  `created_by` INT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `tasks_users_FK` (`created_by` ASC) VISIBLE,
  CONSTRAINT `tasks_users_FK` FOREIGN KEY (`created_by`) REFERENCES `topperstasks`.`users` (`id`) ON DELETE
  SET NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 4 DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;
-- -----------------------------------------------------
-- Table `topperstasks`.`task_assignments`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `topperstasks`.`task_assignments` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `task_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `task_assignments_user_id_IDX` USING BTREE (`user_id`, `task_id`) VISIBLE,
  INDEX `task_assignments_tasks_FK` (`task_id` ASC) VISIBLE,
  CONSTRAINT `task_assignments_tasks_FK` FOREIGN KEY (`task_id`) REFERENCES `topperstasks`.`tasks` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `task_assignments_users_FK` FOREIGN KEY (`user_id`) REFERENCES `topperstasks`.`users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 9 DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;
SET SQL_MODE = @OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS = @OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS = @OLD_UNIQUE_CHECKS;
INSERT INTO `topperstasks`.`users` (`username`, `is_admin`)
values ('Pjotir', 1);