Class: PitchfxDbManager

Inherits:
Object
  • Object
show all
Defined in:
lib/pitchfx_db_manager.rb

Overview

This class is used to insert data into the pitchfx database

Instance Method Summary collapse

Constructor Details

#initialize(host, user, password, db_name) ⇒ PitchfxDbManager

Returns a new instance of PitchfxDbManager.



9
10
11
 
# File 'lib/pitchfx_db_manager.rb', line 9

def initialize(host, user, password, db_name)
  @db = Mysql.real_connect(host, user, password, db_name)
end

Instance Method Details

#find_or_create_atbat(atbat, game_id) ⇒ Object 



56
57
58
59
60
61
62
63
64
65
66
67
 
# File 'lib/pitchfx_db_manager.rb', line 56

def find_or_create_atbat(atbat, game_id)
  res = @db.query("select id from atbats where game_id = #{game_id} and num = #{atbat.num}")
  id=0
  if res.num_rows > 0
    res.each do |row|
      id = row[0]
    end
  else
    id = insert_atbat(atbat, game_id)
  end
  id
end

#find_or_create_game(game, visitor_id, home_id) ⇒ Object 



42
43
44
45
46
47
48
49
50
51
52
53
 
# File 'lib/pitchfx_db_manager.rb', line 42

def find_or_create_game(game, visitor_id, home_id)
  res = @db.query("select id from games where gid = '#{game.gid}'")
  id=0
  if res.num_rows > 0
    res.each do |row|
      id = row[0]
    end
  else
    id = insert_game(game, visitor_id, home_id)
  end
  id
end

#find_or_create_game_type(type) ⇒ Object 



113
114
115
116
117
118
119
120
121
122
123
124
 
# File 'lib/pitchfx_db_manager.rb', line 113

def find_or_create_game_type(type)
  res = @db.query("select id from game_types where id = #{type.id}")
  id=0
  if res.num_rows > 0
    res.each do |row|
      id = row[0]
    end 
  else
    id = insert_game_type(type)
  end
  id
end

#find_or_create_pitch(pitch, atbat_id) ⇒ Object 



70
71
72
73
74
75
76
77
78
79
80
81
 
# File 'lib/pitchfx_db_manager.rb', line 70

def find_or_create_pitch(pitch, atbat_id)
  res = @db.query("select id from pitches where pitch_id = '#{pitch.pitch_id}' and atbat_id = '#{atbat_id}'")
  id=0
  if res.num_rows > 0
    res.each do |row|
      id = row[0]
    end 
  else
    id = insert_pitch(pitch, atbat_id)
  end
  id
end

#find_or_create_pitch_type(type) ⇒ Object 



99
100
101
102
103
104
105
106
107
108
109
110
 
# File 'lib/pitchfx_db_manager.rb', line 99

def find_or_create_pitch_type(type)
  res = @db.query("select id from pitch_types where id = #{type.id}")
  id=0
  if res.num_rows > 0
    res.each do |row|
      id = row[0]
    end
  else
    id = insert_pitch_type(type)
  end
  id
end

#find_or_create_pitcher_line(pitcher, game) ⇒ Object 



171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
 
# File 'lib/pitchfx_db_manager.rb', line 171

def find_or_create_pitcher_line(pitcher, game)
  game_id = find_or_create_game(game, nil, nil)
  res = @db.query("select id from players where gameday_id = #{pitcher.pid}")
  pitcher_id=0
  res.each do |row|
    pitcher_id = row[0]
  end
  res = @db.query("select id from pitcher_lines where game_id = #{game_id} and pitcher_id = #{pitcher_id}")
  id=0
  if res.num_rows > 0
    res.each do |row|
      id = row[0]
    end 
  else
    id = insert_pitcher_line(pitcher, game_id, pitcher_id)
  end
  id
end

#find_or_create_player(player, team_id) ⇒ Object 



14
15
16
17
18
19
20
21
22
23
24
25
 
# File 'lib/pitchfx_db_manager.rb', line 14

def find_or_create_player(player, team_id)
  res = @db.query("select id from players where gameday_id = #{player.pid}")
  id=0
  if res.num_rows > 0
    res.each do |row|
      id = row[0]
    end
  else
    id = insert_player(player, team_id)
  end
  id
end

#find_or_create_roster_player(player, roster_id) ⇒ Object 



152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
 
# File 'lib/pitchfx_db_manager.rb', line 152

def find_or_create_roster_player(player, roster_id) 
  res = @db.query("select id from players where gameday_id = #{player.pid}")
  player_id=0
  res.each do |row|
    player_id = row[0]
  end
  res = @db.query("select id from roster_players where roster_id = #{roster_id} and player_id = #{player_id}")
  id=0
  if res.num_rows > 0
    res.each do |row|
      id = row[0]
    end 
  else
    id = insert_roster_player(roster_id, player, player_id)
  end
  id
end

#find_or_create_rosters(gid, away_id, home_id) ⇒ Object 



127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
 
# File 'lib/pitchfx_db_manager.rb', line 127

def find_or_create_rosters(gid, away_id, home_id)
  gameday_info = GamedayUtil.parse_gameday_id('gid_' + gid)
  active_date = "#{gameday_info['year']}/#{gameday_info['month']}/#{gameday_info['day']}"
  away_res = @db.query("select id from rosters where team_id = #{away_id} and active_date = #{active_date}")
  away_roster_id=0
  if away_res.num_rows > 0
    away_res.each do |row|
      away_roster_id = row[0]
    end
  else
    away_roster_id = insert_roster(away_id, active_date)
  end
  home_res = @db.query("select id from rosters where team_id = #{home_id} and active_date = #{active_date}")
  home_roster_id=0
  if home_res.num_rows > 0
    home_res.each do |row|
      home_roster_id = row[0]
    end
  else
    home_roster_id = insert_roster(home_id, active_date)
  end
  [away_roster_id, home_roster_id]
end

#find_or_create_team(team) ⇒ Object 



28
29
30
31
32
33
34
35
36
37
38
39
 
# File 'lib/pitchfx_db_manager.rb', line 28

def find_or_create_team(team)
  res = @db.query("select id from teams where abbreviation = '#{team.abrev}'")
  id=0
  if res.num_rows > 0
    res.each do |row|
      id = row[0]
    end
  else
    id = insert_team(team)
  end
  id
end

#find_or_create_umpire(name) ⇒ Object 



84
85
86
87
88
89
90
91
92
93
94
95
96
 
# File 'lib/pitchfx_db_manager.rb', line 84

def find_or_create_umpire(name)
  ump_name = @db.escape_string(name)
  res = @db.query("select id from umpires where name = '#{ump_name}'")
  id=0
  if res.num_rows > 0
    res.each do |row|
      id = row[0]
    end
  else
    id = insert_umpire(name)
  end
  id
end

#insert_atbat(atbat, game_id) ⇒ Object 



276
277
278
279
280
281
282
283
284
285
286
287
288
289
 
# File 'lib/pitchfx_db_manager.rb', line 276

def insert_atbat(atbat, game_id)
  desc = @db.escape_string("#{atbat.des}")
  @db.query("INSERT INTO atbats (game_id, inning, num, ball, strike, outs, batter_id,
         pitcher_id, stand, des, event, created_at) 
        VALUES ('#{game_id}','#{atbat.inning}','#{atbat.num}','#{atbat.b}','#{atbat.s}',
                '#{atbat.o}','#{atbat.batter_id}','#{atbat.pitcher_id}','#{atbat.stand}',
                '#{desc}','#{atbat.event}', '#{Time.now.strftime("%Y/%m/%d")}')")
  res = @db.query("select last_insert_id()")
  id = 0
  res.each do |row|
    id = row[0]
  end
  id
end

#insert_game(game, visitor_id, home_id) ⇒ Object 



318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
 
# File 'lib/pitchfx_db_manager.rb', line 318

def insert_game(game, visitor_id, home_id)
  ump_hid = find_or_create_umpire(game.get_umpires['home'])
  ump_1id = find_or_create_umpire(game.get_umpires['first'])
  ump_2id = find_or_create_umpire(game.get_umpires['second'])
  ump_3id = find_or_create_umpire(game.get_umpires['third'])
  
  if game.get_home_runs.to_i > game.get_away_runs.to_i
    winning_team_id = home_id
    losing_team_id = visitor_id
  elsif game.get_home_runs.to_i < game.get_away_runs.to_i
    winning_team_id = visitor_id
    losing_team_id = home_id
  end
  
  @db.query("INSERT INTO games (gid, date, home_id, away_id, game_num, umpire_hp_id, 
                                umpire_1b_id, umpire_2b_id, umpire_3b_id, wind,
                                wind_dir, temp, runs_home, runs_away, game_type, status, 
                                winning_team_id, losing_team_id, created_at)          
                VALUES ('#{game.gid}', '#{Date.parse(game.get_date).to_s}', '#{home_id}', '#{visitor_id}',
                         '#{game.game_number}', '#{ump_hid}', '#{ump_1id}', '#{ump_2id}', 
                         '#{ump_3id}', '#{game.get_wind_speed}', '#{game.get_wind_dir}', 
                         '#{game.get_temp}', '#{game.get_home_runs}', '#{game.get_away_runs}',
                         '#{game.game_type}', '#{game.get_boxscore.status_ind}', 
                         '#{winning_team_id}', '#{losing_team_id}', '#{Time.now.strftime("%Y/%m/%d")}') ")
                         
  res = @db.query("select id from games where gid='#{game.gid}'")
  id = 0
  res.each do |row|
    id = row[0]
  end
  id
end

#insert_game_type(type) ⇒ Object 



382
383
384
385
386
387
388
389
390
391
 
# File 'lib/pitchfx_db_manager.rb', line 382

def insert_game_type(type)
  @db.query("INSERT INTO game_types (name, created_at)
                 VALUES ('#{type}', '#{Time.now.strftime("%Y/%m/%d")}') ")
  res = @db.query("select id from game_types where name='#{type}'")
  id = 0
  res.each do |row|
    id = row[0]
  end
  id
end

#insert_pitch(pitch, atbat_id) ⇒ Object 



292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
 
# File 'lib/pitchfx_db_manager.rb', line 292

def insert_pitch(pitch, atbat_id)
  @db.query("INSERT INTO pitches (atbat_id, pitch_id, description, outcome, x, y, start_speed, end_speed,
                  sz_top, sz_bot, pfx_x, pfx_z, px, pz, x0, y0, z0, vx0, vy0, vz0, ax, ay, az,
                  break_y, break_angle, break_length, on_1b, on_2b, on_3b,
                  sv_id, pitch_type, type_confidence, spin_dir, spin_rate, created_at) 
                          VALUES ('#{atbat_id}','#{pitch.pitch_id}', '#{pitch.des}','#{pitch.type}',
                          '#{pitch.x}',
                          '#{pitch.y}','#{pitch.start_speed}','#{pitch.end_speed}',
                          '#{pitch.sz_top}','#{pitch.sz_bot}','#{pitch.pfx_x}',
                          '#{pitch.pfx_z}','#{pitch.px}','#{pitch.pz}','#{pitch.x0}',
                          '#{pitch.y0}','#{pitch.z0}','#{pitch.vx0}','#{pitch.vy0}',
                          '#{pitch.vz0}','#{pitch.ax}','#{pitch.ay}','#{pitch.az}',
                          '#{pitch.break_y}','#{pitch.break_angle}','#{pitch.break_length}',
                          '#{pitch.on_1b}','#{pitch.on_2b}',
                          '#{pitch.on_3b}','#{pitch.sv_id}','#{pitch.pitch_type}',
                          '#{pitch.type_confidence}', '#{pitch.spin_dir}', '#{pitch.spin_rate}',
                          '#{Time.now.strftime("%Y/%m/%d")}')")
  res = @db.query("select last_insert_id()")
  id = 0
  res.each do |row|
    id = row[0]
  end
  id
end

#insert_pitch_type(pitch) ⇒ Object 



370
371
372
373
374
375
376
377
378
379
 
# File 'lib/pitchfx_db_manager.rb', line 370

def insert_pitch_type(pitch)
  @db.query("INSERT INTO pitch_types (abbreviation, description, created_at)
                 VALUES ('#{pitch.type}', '#{pitch.des}', '#{Time.now.strftime("%Y/%m/%d")}') ")
  res = @db.query("select id from pitch_types where abbreviation='#{pitch.type}'")
  id = 0
  res.each do |row|
    id = row[0]
  end
  id
end

#insert_pitcher_line(pitcher, game_id, pitcher_id) ⇒ Object 



228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
 
# File 'lib/pitchfx_db_manager.rb', line 228

def insert_pitcher_line(pitcher, game_id, pitcher_id)
  #puts "INSERT INTO pitcher_lines (game_id, pitcher_id, name, pos, outs, bf, er, r, h, so, hr, bb, w, l, era, note) 
  #              VALUES ('#{game_id}', '#{pitcher_id}','#{pitcher.pitcher_name}','P', '#{pitcher.out}',
  #                     '#{pitcher.bf}','#{pitcher.er}','#{pitcher.r}','#{pitcher.h}','#{pitcher.so}','#{pitcher.hr}',
  #                     '#{pitcher.bb}','#{pitcher.w}','#{pitcher.l}','#{pitcher.era}','#{pitcher.note}')"
 name = @db.escape_string("#{pitcher.pitcher_name}")                    
 @db.query("INSERT INTO pitcher_lines (game_id, pitcher_id, name, pos, outs, bf, er, r, h, so, hr, bb, w, l, era, note) 
              VALUES ('#{game_id}', '#{pitcher_id}','#{name}','P', '#{pitcher.out}','#{pitcher.bf}',
                      '#{pitcher.er}','#{pitcher.r}','#{pitcher.h}','#{pitcher.so}','#{pitcher.hr}','#{pitcher.bb}',
                      '#{pitcher.w}','#{pitcher.l}','#{pitcher.era}','#{pitcher.note}')")
 res = @db.query("select last_insert_id()")
 id = 0
 res.each do |row|
   id = row[0]
 end
 id
end

#insert_player(player, team_id) ⇒ Object 



247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
 
# File 'lib/pitchfx_db_manager.rb', line 247

def insert_player(player, team_id)
  first = @db.escape_string("#{player.first}")
  last = @db.escape_string("#{player.last}")
  boxname = @db.escape_string("#{player.boxname}")
  @db.query("INSERT INTO players (team_id, gameday_id, first, last, number, boxname, position, throws) 
               VALUES ('#{team_id}', '#{player.pid}','#{first}','#{last}',
                       '#{player.num}','#{boxname}','#{player.position}',
                       '#{player.rl}')")
  res = @db.query("select last_insert_id()")
  id = 0
  res.each do |row|
    id = row[0]
  end
  id
end

#insert_roster(team_id, active_date) ⇒ Object 



191
192
193
194
195
196
197
198
199
 
# File 'lib/pitchfx_db_manager.rb', line 191

def insert_roster(team_id, active_date)
  @db.query("INSERT INTO rosters (team_id, active_date, created_at) VALUES ('#{team_id}','#{active_date}', '#{Time.now.strftime("%Y/%m/%d")}')")
  res = @db.query("select last_insert_id()")
  id = 0
  res.each do |row|
    id = row[0]
  end
  id
end

#insert_roster_player(roster_id, player, player_id) ⇒ Object 



202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
 
# File 'lib/pitchfx_db_manager.rb', line 202

def insert_roster_player(roster_id, player, player_id)
  @db.query("INSERT INTO roster_players (roster_id, player_id, number, position, status, avg, hr, rbi,
                                         wins, losses, era, bat_order, game_position, created_at) 
             VALUES ('#{roster_id}',
                     '#{player_id}', 
                     '#{player.num}',
                     '#{player.position}',
                     '#{player.status}',
                     '#{player.avg}',
                     '#{player.hr}',
                     '#{player.rbi}',
                     '#{player.wins}',
                     '#{player.losses}',
                     '#{player.era}',
                     '#{player.bat_order}',
                     '#{player.game_position}',
                     '#{Time.now.strftime("%Y/%m/%d")}')")
  res = @db.query("select last_insert_id()")
  id = 0
  res.each do |row|
    id = row[0]
  end
  id
end

#insert_team(team) ⇒ Object 



264
265
266
267
268
269
270
271
272
273
 
# File 'lib/pitchfx_db_manager.rb', line 264

def insert_team(team)
  @db.query("INSERT INTO teams (abbreviation, city, name, stadium, created_at) 
                        VALUES ('#{team.abrev}', '#{team.city}','#{team.name}','', '#{Time.now.strftime("%Y/%m/%d")}')")
  res = @db.query("select id from teams where name='#{team.name}'")
  id = 0
  res.each do |row|
    id = row[0]
  end
  id
end

#insert_umpire(umpire) ⇒ Object 



357
358
359
360
361
362
363
364
365
366
367
 
# File 'lib/pitchfx_db_manager.rb', line 357

def insert_umpire(umpire)
  name = @db.escape_string(umpire)
  @db.query("INSERT INTO umpires (name, created_at)
                 VALUES ('#{name}', '#{Time.now.strftime("%Y/%m/%d")}') ")                   
  res = @db.query("select last_insert_id()")
  id = 0
  res.each do |row|
    id = row[0]
  end
  id
end

#update_status_for_game(game) ⇒ Object 



352
353
354
 
# File 'lib/pitchfx_db_manager.rb', line 352

def update_status_for_game(game)
  @db.query("UPDATE games SET status='#{game.get_boxscore.status_ind}' WHERE gid='#{game.gid}'")
end