0838a0a5ce
Addded ctf platform Flask app with basic auth functions register, login, logout Added ctf platform database design (MySQL Workbench) Added forward engineered SQL file Added wsgi startup script Installed various python dependencies README.md Updated .gitignore Updated
71 lines
2.4 KiB
SQL
71 lines
2.4 KiB
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='TRADITIONAL,ALLOW_INVALID_DATES';
|
|
|
|
-- -----------------------------------------------------
|
|
-- Schema ctf_main
|
|
-- -----------------------------------------------------
|
|
DROP SCHEMA IF EXISTS `ctf_main` ;
|
|
|
|
-- -----------------------------------------------------
|
|
-- Schema ctf_main
|
|
-- -----------------------------------------------------
|
|
CREATE SCHEMA IF NOT EXISTS `ctf_main` DEFAULT CHARACTER SET utf8 ;
|
|
USE `ctf_main` ;
|
|
|
|
-- -----------------------------------------------------
|
|
-- Table `ctf_main`.`challenge`
|
|
-- -----------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `ctf_main`.`challenge` (
|
|
`challenge_id` INT NOT NULL,
|
|
`challenge_name` VARCHAR(45) NOT NULL,
|
|
`challenge_description` VARCHAR(45) NULL,
|
|
`challenge_flag` VARCHAR(45) NOT NULL,
|
|
`challenge_score` INT NOT NULL,
|
|
PRIMARY KEY (`challenge_id`))
|
|
ENGINE = InnoDB;
|
|
|
|
|
|
-- -----------------------------------------------------
|
|
-- Table `ctf_main`.`user`
|
|
-- -----------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `ctf_main`.`user` (
|
|
`user_id` INT NOT NULL AUTO_INCREMENT,
|
|
`username` VARCHAR(45) NOT NULL,
|
|
`password` VARCHAR(160) NOT NULL,
|
|
`active` TINYINT NOT NULL DEFAULT 1,
|
|
`registered` DATETIME NULL DEFAULT NOW(),
|
|
`info` VARCHAR(500) NULL,
|
|
PRIMARY KEY (`user_id`),
|
|
UNIQUE INDEX `user_id_UNIQUE` (`user_id` ASC))
|
|
ENGINE = InnoDB;
|
|
|
|
|
|
-- -----------------------------------------------------
|
|
-- Table `ctf_main`.`solved_challenges`
|
|
-- -----------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `ctf_main`.`solved_challenges` (
|
|
`user_id` INT NOT NULL,
|
|
`challenge_id` INT NOT NULL,
|
|
`submission_timestamp` DATETIME NOT NULL,
|
|
PRIMARY KEY (`user_id`),
|
|
INDEX `fk_challenge_id_idx` (`challenge_id` ASC),
|
|
CONSTRAINT `fk_user_id`
|
|
FOREIGN KEY (`user_id`)
|
|
REFERENCES `ctf_main`.`user` (`user_id`)
|
|
ON DELETE NO ACTION
|
|
ON UPDATE NO ACTION,
|
|
CONSTRAINT `fk_challenge_id`
|
|
FOREIGN KEY (`challenge_id`)
|
|
REFERENCES `ctf_main`.`challenge` (`challenge_id`)
|
|
ON DELETE NO ACTION
|
|
ON UPDATE NO ACTION)
|
|
ENGINE = InnoDB;
|
|
|
|
|
|
SET SQL_MODE=@OLD_SQL_MODE;
|
|
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
|
|
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
|