Arsenal/create-match-database.py at master · Shadowen/Arsenal · 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
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
import sqlite3
import traceback

conn = sqlite3.connect('database.db')
c = conn.cursor()
try:
	c.execute('''CREATE TABLE match (
			id INTEGER PRIMARY KEY,
			region TEXT,
			queueType TEXT,
			version TEXT NOT NULL,
			duration INTEGER
			)''')
	c.execute('''CREATE TABLE team (
			matchId INTEGER REFERENCES match(id),
			id INTEGER,
			winner INTEGER NOT NULL,
			PRIMARY KEY (matchId, id)
			)''')
	c.execute('''CREATE TABLE ban (
			matchId INTEGER REFERENCES match(id),
			teamId INTEGER,
			championId INTEGER REFERENCES champion(id),
			pickTurn INTEGER,
			PRIMARY KEY (matchId, pickTurn),
			FOREIGN KEY (matchId, teamId) REFERENCES team(matchId, id)
			)''')
	c.execute('''CREATE TABLE participant (
			matchId INTEGER REFERENCES match(id),
			playerId INTEGER REFERENCES player(id),
			id INTEGER NOT NULL,
			teamId INTEGER REFERENCES team(id),
			championId INTEGER REFERENCES champion(id),
			champLevel INTEGER,
			role TEXT,
			lane TEXT,
			buildType TEXT,
			kills INTEGER,
			deaths INTEGER,
			assists INTEGER,
			soloKills INTEGER,
			assassinations INTEGER,
			firstBloodKill INTEGER,
			firstBloodAssist INTEGER,
			firstTowerKill INTEGER,
			firstTowerAssist INTEGER,
			totalTimeCrowdControlDealt INTEGER,
			damageDealt INTEGER,
			damageDealtToChampions INTEGER,
			physicalDamageDealt INTEGER,
			physicalDamageDealtToChampions INTEGER,
			magicDamageDealt INTEGER,
			magicDamageDealtToChampions INTEGER,
			trueDamageDealt INTEGER,
			trueDamageDealtToChampions INTEGER,
			totalFlatItemAp INT,
			totalPercentItemAp REAL,
			totalFlatRuneAp REAL,
			totalPercentRuneAp REAL,
			totalFlatMasteryAp REAL,
			totalPercentMasteryAp REAL,
			totalAp REAL,
			PRIMARY KEY (matchId, id)
			)''')
	c.execute('''CREATE INDEX [participant-match-id] ON participant (
					matchId,
					id
				);''')
	c.execute('''CREATE TABLE participantItem (
			matchId INTEGER,
			participantId INTEGER,
			itemId INTEGER,
			shortItemId INTEGER,
			timeBought INTEGER,
			finalStacks INTEGER,
			maxStacks INTEGER,
			stackAp INTEGER,
			goldThreshold INTEGER,
			buyOrder INTEGER,
			FOREIGN KEY (matchId, participantId) REFERENCES participant(matchId, id)
			)''')
	c.execute('''CREATE TABLE participantMastery (
			matchId INTEGER,
			participantId INTEGER,
			masteryId INTEGER REFERENCES mastery(id),
			rank INTEGER,
			PRIMARY KEY (matchId, participantId, masteryId),
			FOREIGN KEY (matchId, participantId) REFERENCES participant(matchId, id)
		)''')
	c.execute('''CREATE TABLE participantRune (
			matchId INTEGER REFERENCES match(id),
			participantId INTEGER,
			runeId INTEGER REFERENCES rune(id),
			rank INTEGER,
			FOREIGN KEY (matchId, participantId) REFERENCES participant(matchId, id)
		)''')
	c.execute('''CREATE TABLE participantFrame (
			matchId INTEGER REFERENCES match(id),
			timestamp INTEGER,
			participantId INTEGER REFERENCES participant(id),
			positionX INTEGER,
			positionY INTEGER,
			currentGold INTEGER,
			totalGold INTEGER,
			level INTEGER,
			minionsKilled INTEGER,
			jungleMinionsKilled INTEGER,
			PRIMARY KEY (matchId, timestamp, participantId)
			)''')
	c.execute('''CREATE TABLE event (
			matchId INTEGER REFERENCES match(id),
			frameTimestamp INTEGER,
			timestamp INTEGER NOT NULL,
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			type TEXT NOT NULL,
			itemId INTEGER REFERENCES item(itemId),
			participantId INTEGER,
			creatorId INTEGER,
			killerId INTEGER,
			victimId INTEGER,
			positionX INTEGER,
			positionY INTEGER,
			FOREIGN KEY (matchId, frameTimestamp) REFERENCES participantFrame(matchId, frameTimestamp)
			)''')
	c.execute('''CREATE INDEX [event-match-participant-frame] ON event (
				matchId,
				frameTimestamp,
				participantId
			);''')
	c.execute('''CREATE INDEX [event-match-time-type] ON event (
				matchId,
				timestamp,
				type
			);''')
	c.execute('''CREATE TABLE assist (
			matchId INTEGER REFERENCES match(id),
			eventId INTEGER,
			participantId INTEGER REFERENCES participant(id),
			FOREIGN KEY (matchId, eventId) REFERENCES event(matchId, id)
			)''')

	c.execute('''CREATE TABLE player (
			id INTEGER PRIMARY KEY,
			name TEXT NOT NULL,
			matchHistoryUri TEXT,
			profileIcon INTEGER
			)''')
	conn.commit()
except sqlite3.Error:
	traceback.print_exc()

conn.close()
print('Done!')
input()