-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathcreate-schema-backup.sql
36 lines (36 loc) · 7.69 KB
/
create-schema-backup.sql
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
CREATE TABLE player (id BIGINT NOT NULL, birthday VARCHAR(255), height BIGINT, player_api_id BIGINT, player_name VARCHAR(255), weight BIGINT, PRIMARY KEY (id));
CREATE TABLE team_attributes (id BIGINT NOT NULL, buildupplaydribbling BIGINT, buildupplaydribblingclass VARCHAR(255), buildupplaypassing BIGINT, buildupplaypassingclass VARCHAR(255), buildupplaypositioningclass VARCHAR(255), buildupplayspeed BIGINT, buildupplayspeedclass VARCHAR(255), chancecreationcrossing BIGINT, chancecreationcrossingclass VARCHAR(255), chancecreationpassing BIGINT, chancecreationpassingclass VARCHAR(255), chancecreationpositioningclass VARCHAR(255), chancecreationshooting BIGINT, chancecreationshootingclass VARCHAR(255), defenceaggression BIGINT, defenceaggressionclass VARCHAR(255), defencedefenderlineclass VARCHAR(255), defencepressure BIGINT, defencepressureclass VARCHAR(255), defenceteamwidth BIGINT, defenceteamwidthclass VARCHAR(255), date VARCHAR(255), team_api_id BIGINT, team_fifa_api_id BIGINT, PRIMARY KEY (id));
CREATE TABLE country (id BIGINT NOT NULL, name VARCHAR(255), PRIMARY KEY (id));
CREATE TABLE team (id BIGINT NOT NULL, team_api_id BIGINT, team_long_name VARCHAR(255), team_short_name VARCHAR(255), PRIMARY KEY (id));
CREATE TABLE player_attributes (id BIGINT NOT NULL, acceleration BIGINT, aggression BIGINT, agility BIGINT, attacking_work_rate VARCHAR(255), balance BIGINT, ball_control BIGINT, crossing BIGINT, curve BIGINT, date VARCHAR(255), defensive_work_rate VARCHAR(255), dribbling BIGINT, finishing BIGINT, free_kick_accuracy BIGINT, gk_diving BIGINT, gk_handling BIGINT, gk_kicking BIGINT, gk_positioning BIGINT, gk_reflexes BIGINT, heading_accuracy BIGINT, interceptions BIGINT, jumping BIGINT, long_passing BIGINT, long_shots BIGINT, marking BIGINT, overall_rating BIGINT, penalties BIGINT, player_api_id BIGINT, player_fifa_api_id BIGINT, positioning BIGINT, potential BIGINT, preferred_foot VARCHAR(255), reactions BIGINT, short_passing BIGINT, shot_power BIGINT, sliding_tackle BIGINT, sprint_speed BIGINT, stamina BIGINT, standing_tackle BIGINT, strength BIGINT, vision BIGINT, volleys BIGINT, PRIMARY KEY (id));
CREATE TABLE league (id BIGINT NOT NULL, name VARCHAR(255), country_id BIGINT, PRIMARY KEY (id));
CREATE TABLE matches (id BIGINT NOT NULL, away_team_goal INTEGER, away_player_X1 INTEGER, away_player_X10 INTEGER, away_player_X11 INTEGER, away_player_X2 INTEGER, away_player_X3 INTEGER, away_player_X4 INTEGER, away_player_X5 INTEGER, away_player_X6 INTEGER, away_player_X7 INTEGER, away_player_X8 INTEGER, away_player_X9 INTEGER, away_player_Y1 INTEGER, away_player_Y10 INTEGER, away_player_Y11 INTEGER, away_player_Y2 INTEGER, away_player_Y3 INTEGER, away_player_Y4 INTEGER, away_player_Y5 INTEGER, away_player_Y6 INTEGER, away_player_Y7 INTEGER, away_player_Y8 INTEGER, away_player_Y9 INTEGER, B365A BIGINT, B365D BIGINT, B365H BIGINT, BSA BIGINT, BSD BIGINT, BSH BIGINT, BWA BIGINT, BWD BIGINT, BWH BIGINT, card LONGTEXT, corner LONGTEXT, `cross` LONGTEXT, `date` VARCHAR(255), foulcommit LONGTEXT, GBA BIGINT, GBD BIGINT, GBH BIGINT, goal LONGTEXT, home_team_goal INTEGER, home_player_X1 INTEGER, home_player_X10 INTEGER, home_player_X11 INTEGER, home_player_X2 INTEGER, home_player_X3 INTEGER, home_player_X4 INTEGER, home_player_X5 INTEGER, home_player_X6 INTEGER, home_player_X7 INTEGER, home_player_X8 INTEGER, home_player_X9 INTEGER, home_player_Y1 INTEGER, home_player_Y10 INTEGER, home_player_Y11 INTEGER, home_player_Y2 INTEGER, home_player_Y3 INTEGER, home_player_Y4 INTEGER, home_player_Y5 INTEGER, home_player_Y6 INTEGER, home_player_Y7 INTEGER, home_player_Y8 INTEGER, home_player_Y9 INTEGER, IWA BIGINT, IWD BIGINT, IWH BIGINT, LBA BIGINT, LBD BIGINT, LBH BIGINT, match_api_id INTEGER, possession LONGTEXT, PSA BIGINT, PSD BIGINT, PSH BIGINT, season VARCHAR(255), shotoff LONGTEXT, shoton LONGTEXT, SJA BIGINT, SJD BIGINT, SJH BIGINT, stage INTEGER, VCA BIGINT, VCD BIGINT, VCH BIGINT, WHA BIGINT, WHD BIGINT, WHH BIGINT, away_player_1 BIGINT, away_player_10 BIGINT, away_player_11 BIGINT, away_player_2 BIGINT, away_player_3 BIGINT, away_player_4 BIGINT, away_player_5 BIGINT, away_player_6 BIGINT, away_player_7 BIGINT, away_player_8 BIGINT, away_player_9 BIGINT, away_team_api_id BIGINT, country_id BIGINT, home_player_1 BIGINT, home_player_10 BIGINT, home_player_11 BIGINT, home_player_2 BIGINT, home_player_3 BIGINT, home_player_4 BIGINT, home_player_5 BIGINT, home_player_6 BIGINT, home_player_7 BIGINT, home_player_8 BIGINT, home_player_9 BIGINT, home_team_api_id BIGINT, league_id BIGINT, PRIMARY KEY (id));
ALTER TABLE team_attributes ADD CONSTRAINT FK_team_attributes_team_fifa_api_id FOREIGN KEY (team_fifa_api_id) REFERENCES team (id);
ALTER TABLE player_attributes ADD CONSTRAINT FK_player_attributes_player_fifa_api_id FOREIGN KEY (player_fifa_api_id) REFERENCES player (id);
ALTER TABLE league ADD CONSTRAINT FK_league_country_id FOREIGN KEY (country_id) REFERENCES country (id);
ALTER TABLE matches ADD CONSTRAINT FK_matches_away_player_11 FOREIGN KEY (away_player_11) REFERENCES player (id);
ALTER TABLE matches ADD CONSTRAINT FK_matches_away_player_10 FOREIGN KEY (away_player_10) REFERENCES player (id);
ALTER TABLE matches ADD CONSTRAINT FK_matches_home_team_api_id FOREIGN KEY (home_team_api_id) REFERENCES team (id);
ALTER TABLE matches ADD CONSTRAINT FK_matches_away_player_7 FOREIGN KEY (away_player_7) REFERENCES player (id);
ALTER TABLE matches ADD CONSTRAINT FK_matches_away_player_8 FOREIGN KEY (away_player_8) REFERENCES player (id);
ALTER TABLE matches ADD CONSTRAINT FK_matches_away_player_9 FOREIGN KEY (away_player_9) REFERENCES player (id);
ALTER TABLE matches ADD CONSTRAINT FK_matches_away_player_3 FOREIGN KEY (away_player_3) REFERENCES player (id);
ALTER TABLE matches ADD CONSTRAINT FK_matches_away_player_4 FOREIGN KEY (away_player_4) REFERENCES player (id);
ALTER TABLE matches ADD CONSTRAINT FK_matches_away_team_api_id FOREIGN KEY (away_team_api_id) REFERENCES team (id);
ALTER TABLE matches ADD CONSTRAINT FK_matches_away_player_5 FOREIGN KEY (away_player_5) REFERENCES player (id);
ALTER TABLE matches ADD CONSTRAINT FK_matches_away_player_6 FOREIGN KEY (away_player_6) REFERENCES player (id);
ALTER TABLE matches ADD CONSTRAINT FK_matches_country_id FOREIGN KEY (country_id) REFERENCES country (id);
ALTER TABLE matches ADD CONSTRAINT FK_matches_league_id FOREIGN KEY (league_id) REFERENCES league (id);
ALTER TABLE matches ADD CONSTRAINT FK_matches_home_player_9 FOREIGN KEY (home_player_9) REFERENCES player (id);
ALTER TABLE matches ADD CONSTRAINT FK_matches_home_player_8 FOREIGN KEY (home_player_8) REFERENCES player (id);
ALTER TABLE matches ADD CONSTRAINT FK_matches_home_player_7 FOREIGN KEY (home_player_7) REFERENCES player (id);
ALTER TABLE matches ADD CONSTRAINT FK_matches_home_player_6 FOREIGN KEY (home_player_6) REFERENCES player (id);
ALTER TABLE matches ADD CONSTRAINT FK_matches_home_player_5 FOREIGN KEY (home_player_5) REFERENCES player (id);
ALTER TABLE matches ADD CONSTRAINT FK_matches_home_player_4 FOREIGN KEY (home_player_4) REFERENCES player (id);
ALTER TABLE matches ADD CONSTRAINT FK_matches_away_player_1 FOREIGN KEY (away_player_1) REFERENCES player (id);
ALTER TABLE matches ADD CONSTRAINT FK_matches_away_player_2 FOREIGN KEY (away_player_2) REFERENCES player (id);
ALTER TABLE matches ADD CONSTRAINT FK_matches_home_player_3 FOREIGN KEY (home_player_3) REFERENCES player (id);
ALTER TABLE matches ADD CONSTRAINT FK_matches_home_player_2 FOREIGN KEY (home_player_2) REFERENCES player (id);
ALTER TABLE matches ADD CONSTRAINT FK_matches_home_player_1 FOREIGN KEY (home_player_1) REFERENCES player (id);
ALTER TABLE matches ADD CONSTRAINT FK_matches_home_player_11 FOREIGN KEY (home_player_11) REFERENCES player (id);
ALTER TABLE matches ADD CONSTRAINT FK_matches_home_player_10 FOREIGN KEY (home_player_10) REFERENCES player (id);