-
Notifications
You must be signed in to change notification settings - Fork 0
/
database_scheme.sql
224 lines (215 loc) · 6.31 KB
/
database_scheme.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
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
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
CREATE TABLE summoners (
accountId varchar(56) PRIMARY KEY,
summonerId varchar(63) NOT NULL,
puuid varchar(78) NOT NULL,
name varchar(16) NOT NULL,
summonerLevel int NOT NULL,
profileIconId int NOT NULL,
revisionDate bigint NOT NULL,
timestamp timestamp with time zone NOT NULL
);
CREATE TABLE matches (
gameId bigint NOT NULL,
platformId varchar(16) NOT NULL,
gameCreation bigint NOT NULL,
gameDuration int NOT NULL,
queueId int NOT NULL,
mapId int NOT NULL,
seasonId int NOT NULL,
gameVersion varchar(32) NOT NULL,
gameMode varchar(16) NOT NULL,
gameType varchar(32) NOT NULL,
PRIMARY KEY (gameId)
);
CREATE TABLE summoner_matches (
accountId varchar(56) NOT NULL,
gameId bigint NOT NULL,
FOREIGN KEY (accountId) REFERENCES summoners(accountId),
FOREIGN KEY (gameId) REFERENCES matches(gameId),
PRIMARY KEY (gameId, accountId)
);
CREATE TABLE teams (
teamId int NOT NULL,
gameId bigint NOT NULL,
win varchar(16) NOT NULL,
firstBlood bool NOT NULL,
firstTower bool NOT NULL,
firstInhibitor bool NOT NULL,
firstBaron bool NOT NULL,
firstDragon bool NOT NULL,
firstRiftHerald bool NOT NULL,
towerKills int NOT NULL,
inhibitorKills int NOT NULL,
baronKills int NOT NULL,
dragonKills int NOT NULL,
riftHeraldKills int NOT NULL,
bans int[] NOT NULL,
FOREIGN KEY (gameId) REFERENCES matches(gameId),
PRIMARY KEY (teamId, gameId)
);
CREATE TABLE champions (
championId int NOT NULL,
name varchar(16) NOT NULL,
classes varchar(20)[] NOT NULL,
PRIMARY KEY (championId)
);
CREATE TABLE timelines(
timelineId varchar(36) NOT NULL,
creepsPerMinDeltas text NOT NULL,
xpPerMinDeltas text NOT NULL,
goldPerMinDeltas text NOT NULL,
csDiffPerMinDeltas text NOT NULL,
xpDiffPerMinDeltas text NOT NULL,
damageTakenPerMinDeltas text NOT NULL,
damageTakenDiffPerMinDeltas text NOT NULL,
PRIMARY KEY (timelineId)
);
CREATE TABLE stats(
statId varchar(36) NOT NULL,
win bool NOT NULL,
items int[] NOT NULL,
kills int NOT NULL,
deaths int NOT NULL,
assists int NOT NULL,
largestKillingSpree int NOT NULL,
largestMultiKill int NOT NULL,
killingSprees int NOT NULL,
longestTimeSpentLiving int NOT NULL,
doubleKills int NOT NULL,
tripleKills int NOT NULL,
quadraKills int NOT NULL,
pentaKills int NOT NULL,
totalDamageDealt int NOT NULL,
magicDamageDealt int NOT NULL,
physicalDamageDealt int NOT NULL,
trueDamageDealt int NOT NULL,
largestCriticalStrike int NOT NULL,
totalDamageDealtToChampions int NOT NULL,
magicDamageDealtToChampions int NOT NULL,
physicalDamageDealtToChampions int NOT NULL,
trueDamageDealtToChampions int NOT NULL,
totalHeal int NOT NULL,
totalUnitsHealed int NOT NULL,
damageSelfMitigated int NOT NULL,
damageDealtToObjectives int NOT NULL,
damageDealtToTurrets int NOT NULL,
visionScore int NOT NULL,
timeCCingOthers int NOT NULL,
totalDamageTaken int NOT NULL,
magicDamageTaken int NOT NULL,
physicalDamageTaken int NOT NULL,
trueDamageTaken int NOT NULL,
goldEarned int NOT NULL,
goldSpent int NOT NULL,
turretKills int NOT NULL,
inhibitorKills int NOT NULL,
totalMinionsKilled int NOT NULL,
neutralMinionsKilledTeamJungle int,
neutralMinionsKilledEnemyJungle int,
totalTimeCrowdControlDealt int NOT NULL,
champLevel int NOT NULL,
visionWardsBoughtInGame int NOT NULL,
sightWardsBoughtInGame int NOT NULL,
wardsPlaced int,
wardsKilled int,
firstBloodKill bool,
firstBloodAssist bool,
firstTowerKill bool,
firstTowerAssist bool,
firstInhibitorKill bool,
firstInhibitorAssist bool,
combatPlayerScore int NOT NULL,
objectivePlayerScore int NOT NULL,
totalPlayerScore int NOT NULL,
totalScoreRank int NOT NULL,
perk0 int NOT NULL,
perk0Var1 int NOT NULL,
perk0Var2 int NOT NULL,
perk0Var3 int NOT NULL,
perk1 int NOT NULL,
perk1Var1 int NOT NULL,
perk1Var2 int NOT NULL,
perk1Var3 int NOT NULL,
perk2 int NOT NULL,
perk2Var1 int NOT NULL,
perk2Var2 int NOT NULL,
perk2Var3 int NOT NULL,
perk3 int NOT NULL,
perk3Var1 int NOT NULL,
perk3Var2 int NOT NULL,
perk3Var3 int NOT NULL,
perk4 int NOT NULL,
perk4Var1 int NOT NULL,
perk4Var2 int NOT NULL,
perk4Var3 int NOT NULL,
perk5 int NOT NULL,
perk5Var1 int NOT NULL,
perk5Var2 int NOT NULL,
perk5Var3 int NOT NULL,
perkPrimaryStyle int NOT NULL,
perkSubStyle int NOT NULL,
statPerk0 int NOT NULL,
statPerk1 int NOT NULL,
statPerk2 int NOT NULL,
PRIMARY KEY (statId)
);
CREATE TABLE participants (
participantId varchar(36) NOT NULL,
gameId bigint NOT NULL,
accountId varchar(56) NOT NULL,
championId int NOT NULL,
statId varchar(36) NOT NULL,
teamId int NOT NULL,
timelineId varchar(36) NOT NULL,
spell1Id int NOT NULL,
spell2Id int NOT NULL,
role varchar(11) NOT NULL,
lane varchar(6) NOT NULL,
PRIMARY KEY (participantId),
FOREIGN KEY (gameId) REFERENCES matches(gameId),
FOREIGN KEY (accountId) REFERENCES summoners(accountId),
FOREIGN KEY (championId) REFERENCES champions(championId),
FOREIGN KEY (timelineId) REFERENCES timelines(timelineId),
FOREIGN KEY (statId) REFERENCES stats(statId),
FOREIGN KEY (teamId, gameId) REFERENCES teams(teamId, gameId),
UNIQUE (participantId, accountId)
);
CREATE TABLE participant_frame (
participantId varchar(36) NOT NULL,
timestamp bigint NOT NULL,
minionsKilled int,
teamScore int,
totalGold int,
level int,
xp int,
currentGold int,
position point,
jungleMinionsKilled int,
FOREIGN KEY (participantId) REFERENCES participants(participantId),
PRIMARY KEY (participantId, timestamp)
);
CREATE TABLE events (
participantId varchar(36),
timestamp bigint NOT NULL,
laneType varchar(32),
skillSlot int,
ascendedType varchar(32),
creatorId int,
afterId int,
eventType varchar(32),
type varchar(32),
levelUpType varchar(32),
wardType varchar(32),
towerType varchar(32),
itemId int,
beforeId int,
monsterType varchar(32),
monsterSubType varchar(32),
teamId int,
position point,
killerId varchar(36),
assistingParticipantIds varchar(36)[],
buildingType varchar(32),
victimId varchar(36),
FOREIGN KEY (participantId) REFERENCES participants(participantId)
);