2048/database.sql at master · Stibila/2048 · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
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';

CREATE SCHEMA IF NOT EXISTS `2048` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `2048` ;

-- -----------------------------------------------------
-- Table `2048`.`player`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `2048`.`player` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `ip` BIGINT NOT NULL,
  `user_agent` TEXT NULL,
  `uuid` VARCHAR(45) NOT NULL,
  `birth_year` INT NULL,
  `experiences` INT NULL,
  `gender` CHAR NOT NULL,
  `weekly` INT NOT NULL,
  `action` TINYINT(4) NOT NULL,
  `shooter` TINYINT(4) NOT NULL,
  `adventure` TINYINT(4) NOT NULL,
  `rpg` TINYINT(4) NOT NULL,
  `simulation` TINYINT(4) NOT NULL,
  `strategy` TINYINT(4) NOT NULL,
  `sport` TINYINT(4) NOT NULL,
  `logical` TINYINT(4) NOT NULL,
  `timestamp` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `uuid_UNIQUE` (`uuid` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `2048`.`game`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `2048`.`game` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `player_id` BIGINT NOT NULL,
  `uuid` VARCHAR(45) NOT NULL,
  `timestamp` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`, `player_id`),
  UNIQUE INDEX `uuid_UNIQUE` (`uuid` ASC),
  INDEX `fk_game_player1_idx` (`player_id` ASC),
  CONSTRAINT `fk_game_player1`
    FOREIGN KEY (`player_id`)
    REFERENCES `2048`.`player` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `2048`.`move`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `2048`.`move` (
  `game_id` BIGINT NOT NULL,
  `move` INT NOT NULL,
  `direction` CHAR NULL,
  `score` INT NOT NULL,
  `timestamp` BIGINT NOT NULL,
  `b1` INT NULL,
  `b2` INT NULL,
  `b3` INT NULL,
  `b4` INT NULL,
  `b5` INT NULL,
  `b6` INT NULL,
  `b7` INT NULL,
  `b8` INT NULL,
  `b9` INT NULL,
  `b10` INT NULL,
  `b11` INT NULL,
  `b12` INT NULL,
  `b13` INT NULL,
  `b14` INT NULL,
  `b15` INT NULL,
  `b16` INT NULL,
  PRIMARY KEY (`game_id`, `move`),
  INDEX `fk_move_game_idx` (`game_id` ASC),
  CONSTRAINT `fk_move_game`
    FOREIGN KEY (`game_id`)
    REFERENCES `2048`.`game` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `2048`.`eyetracking`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `2048`.`eyetracking` (
  `move_game_id` BIGINT NOT NULL,
  `move_move` INT NOT NULL,
  `hits` INT NOT NULL,
  `hit_b1` VARCHAR(45) NULL,
  `hit_b2` VARCHAR(45) NULL,
  `hit_b3` VARCHAR(45) NULL,
  `hit_b4` VARCHAR(45) NULL,
  `hit_b5` VARCHAR(45) NULL,
  `hit_b6` VARCHAR(45) NULL,
  `hit_b7` VARCHAR(45) NULL,
  `hit_b8` VARCHAR(45) NULL,
  `hit_b9` VARCHAR(45) NULL,
  `hit_b10` VARCHAR(45) NULL,
  `hit_b11` VARCHAR(45) NULL,
  `hit_b12` VARCHAR(45) NULL,
  `hit_b13` VARCHAR(45) NULL,
  `hit_b14` VARCHAR(45) NULL,
  `hit_b15` VARCHAR(45) NULL,
  `hit_b16` VARCHAR(45) NULL,
  PRIMARY KEY (`move_game_id`, `move_move`),
  CONSTRAINT `fk_eyetracking_move1`
    FOREIGN KEY (`move_game_id` , `move_move`)
    REFERENCES `2048`.`move` (`game_id` , `move`)
    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;