vmhSjZBA

1CREATE TABLE IF NOT EXISTS `swp`.`competition` (
2  `id` INT NOT NULL,
3  `country_id` INT NULL,
4  `name` VARCHAR(255) NULL,
5  `category` INT NULL,
6  PRIMARY KEY (`id`),
7  INDEX `id_idx` (`country_id` ASC),
8  INDEX `FK_competition_types_competition_type_id_idx` (`category` ASC),
9  CONSTRAINT `FK_country_competition_country_id`
10    FOREIGN KEY (`country_id`)
11    REFERENCES `swp`.`country` (`id`)
12    ON DELETE NO ACTION
13    ON UPDATE NO ACTION,
14  CONSTRAINT `FK_competition_categories_competition_category_id`
15    FOREIGN KEY (`category`)
16    REFERENCES `swp`.`competition_categories` (`id`)
17    ON DELETE NO ACTION
18    ON UPDATE NO ACTION)
19ENGINE = InnoDB;
20
21
22CREATE TABLE IF NOT EXISTS `swp`.`competition_seasons` (
23  `id` INT NOT NULL AUTO_INCREMENT COMMENT 'Rappresenta la chiave primaria della stagione, al contrario di season_id, questa chiave non può essere NULL.',
24  `competition_id` INT NOT NULL,
25  `season_id` INT NULL,
26  `name` VARCHAR(45) NOT NULL,
27  `update_at` DATETIME NULL,
28  PRIMARY KEY (`id`),
29  INDEX `FK_competition_competition_seasons_competition_id_idx` (`competition_id` ASC),
30  CONSTRAINT `FK_competition_competition_seasons_competition_id`
31    FOREIGN KEY (`competition_id`)
32    REFERENCES `swp`.`competition` (`id`)
33    ON DELETE NO ACTION
34    ON UPDATE NO ACTION)
35ENGINE = InnoDB;
36
37CREATE TABLE IF NOT EXISTS `swp`.`competition_rounds` (
38  `id` INT NOT NULL AUTO_INCREMENT,
39  `round_id` INT NULL COMMENT 'Rappresenta il riferimento estratto dal sito.',
40  `season_id` INT NOT NULL,
41  `name` VARCHAR(255) NULL,
42  PRIMARY KEY (`id`),
43  INDEX `FK_competition_seasons_competition_rounds_season_id_idx` (`season_id` ASC),
44  CONSTRAINT `FK_competition_seasons_competition_rounds_season_id`
45    FOREIGN KEY (`season_id`)
46    REFERENCES `swp`.`competition_seasons` (`id`)
47    ON DELETE NO ACTION
48    ON UPDATE NO ACTION)
49ENGINE = InnoDB;
50
51-- -----------------------------------------------------
52-- Table `swp`.`match`
53-- -----------------------------------------------------
54CREATE TABLE IF NOT EXISTS `swp`.`match` (
55  `id` INT NOT NULL,
56  `round_id` INT NOT NULL,
57  `group_id` INT NULL,
58  `datetime` DATETIME NULL,
59  `status` INT NULL,
60  `gameweek` INT NULL,
61  `home_team_id` INT NULL,
62  `home_team_half_time_score` INT NULL,
63  `home_team_score` INT NULL,
64  `home_extra_time` INT NULL,
65  `home_penalties` INT NULL,
66  `away_team_id` INT NULL,
67  `away_team_half_time_score` INT NULL,
68  `away_team_score` INT NULL,
69  `away_extra_time` INT NULL,
70  `away_penalties` INT NULL,
71  `venue_id` INT NULL,
72  `venue_attendance` INT NULL,
73  `aggregate_match_id` INT NULL,
74  PRIMARY KEY (`id`),
75  INDEX `home_team_id_idx` (`home_team_id` ASC),
76  INDEX `away_team_id_idx` (`away_team_id` ASC),
77  INDEX `venue_id_idx` (`venue_id` ASC),
78  INDEX `match_status_id_idx` (`status` ASC),
79  INDEX `FK_competition_rounds_match_round_id_idx` (`round_id` ASC),
80  INDEX `FK_match_match_aggregate_match_id_idx` (`aggregate_match_id` ASC),
81  INDEX `FK_competition_groups_match_group_id_idx` (`group_id` ASC),
82  CONSTRAINT `FK_team_match_home_team_id`
83    FOREIGN KEY (`home_team_id`)
84    REFERENCES `swp`.`team` (`id`)
85    ON DELETE NO ACTION
86    ON UPDATE NO ACTION,
87  CONSTRAINT `FK_team_match_away_team_id`
88    FOREIGN KEY (`away_team_id`)
89    REFERENCES `swp`.`team` (`id`)
90    ON DELETE NO ACTION
91    ON UPDATE NO ACTION,
92  CONSTRAINT `FK_venue_match_venue_id`
93    FOREIGN KEY (`venue_id`)
94    REFERENCES `swp`.`venue` (`id`)
95    ON DELETE NO ACTION
96    ON UPDATE NO ACTION,
97  CONSTRAINT `FK_match_status_match_status_id`
98    FOREIGN KEY (`status`)
99    REFERENCES `swp`.`match_status` (`id`)
100    ON DELETE NO ACTION
101    ON UPDATE NO ACTION,
102  CONSTRAINT `FK_competition_rounds_match_round_id`
103    FOREIGN KEY (`round_id`)
104    REFERENCES `swp`.`competition_rounds` (`id`)
105    ON DELETE NO ACTION
106    ON UPDATE NO ACTION,
107  CONSTRAINT `FK_match_match_aggregate_match_id`
108    FOREIGN KEY (`aggregate_match_id`)
109    REFERENCES `swp`.`match` (`id`)
110    ON DELETE NO ACTION
111    ON UPDATE NO ACTION,
112  CONSTRAINT `FK_competition_groups_match_group_id`
113    FOREIGN KEY (`group_id`)
114    REFERENCES `swp`.`competition_groups` (`id`)
115    ON DELETE NO ACTION
116    ON UPDATE NO ACTION)
117ENGINE = InnoDB;