-- phpMyAdmin SQL Dump
-- version 2.6.2-pl1
-- http://www.phpmyadmin.net
-- 
-- Host: localhost:3306
-- Generation Time: Jun 15, 2008 at 04:29 PM
-- Server version: 5.0.41
-- PHP Version: 5.2.3
-- 
-- Database: `pbp`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `atbats`
-- 

CREATE TABLE `atbats` (
  `ab_id` mediumint(9) unsigned NOT NULL auto_increment,
  `game_id` smallint(6) unsigned NOT NULL,
  `inning` tinyint(2) unsigned NOT NULL,
  `num` tinyint(3) unsigned NOT NULL,
  `ball` tinyint(1) unsigned NOT NULL,
  `strike` tinyint(1) unsigned NOT NULL,
  `outs` tinyint(1) unsigned NOT NULL,
  `batter` mediumint(6) unsigned NOT NULL,
  `pitcher` mediumint(6) unsigned NOT NULL,
  `stand` varchar(1) NOT NULL,
  `des` varchar(400) NOT NULL,
  `event` varchar(50) NOT NULL,
  `hit_x` float default NULL,
  `hit_y` float default NULL,
  `hit_type` varchar(1) default NULL,
  PRIMARY KEY  (`ab_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Play-by-play data' AUTO_INCREMENT=269134 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `game_types`
-- 

CREATE TABLE `game_types` (
  `id` tinyint(3) unsigned NOT NULL,
  `type` varchar(20) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

-- 
-- Table structure for table `games`
-- 

CREATE TABLE `games` (
  `game_id` smallint(5) unsigned NOT NULL auto_increment,
  `date` date NOT NULL,
  `home` varchar(7) NOT NULL,
  `away` varchar(7) NOT NULL,
  `game` tinyint(3) unsigned NOT NULL,
  `umpire` varchar(30) default NULL,
  `wind` tinyint(4) unsigned default NULL,
  `wind_dir` varchar(20) default NULL,
  `temp` tinyint(4) default NULL,
  `type` tinyint(3) unsigned NOT NULL default '1',
  `runs_home` tinyint(3) unsigned default NULL,
  `runs_away` tinyint(3) unsigned default NULL,
  `local_time` time default NULL,
  PRIMARY KEY  (`game_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3511 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `pitch_types`
-- 

CREATE TABLE `pitch_types` (
  `id` tinyint(2) NOT NULL,
  `pitch` varchar(25) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='list of pitch types';

-- --------------------------------------------------------

-- 
-- Table structure for table `pitches`
-- 

CREATE TABLE `pitches` (
  `pitch_id` mediumint(8) unsigned NOT NULL auto_increment,
  `ab_id` mediumint(8) unsigned NOT NULL,
  `des` varchar(30) NOT NULL,
  `type` varchar(1) NOT NULL,
  `id` smallint(5) unsigned NOT NULL,
  `x` float unsigned NOT NULL,
  `y` float unsigned NOT NULL,
  `start_speed` float unsigned default NULL,
  `end_speed` float unsigned default NULL,
  `sz_top` float unsigned default NULL,
  `sz_bot` float unsigned default NULL,
  `pfx_x` float default NULL,
  `pfx_z` float default NULL,
  `px` float default NULL,
  `pz` float default NULL,
  `x0` float default NULL,
  `y0` float default NULL,
  `z0` float default NULL,
  `vx0` float default NULL,
  `vy0` float default NULL,
  `vz0` float default NULL,
  `ax` float default NULL,
  `ay` float default NULL,
  `az` float default NULL,
  `break_y` float default NULL,
  `break_angle` float default NULL,
  `break_length` float default NULL,
  `ball` tinyint(3) unsigned default NULL,
  `strike` tinyint(3) unsigned default NULL,
  `on_1b` mediumint(8) unsigned default NULL,
  `on_2b` mediumint(8) unsigned default NULL,
  `on_3b` mediumint(8) unsigned default NULL,
  `sv_id` varchar(13) default NULL,
  `pitch_type` varchar(2) default NULL,
  `type_confidence` double default NULL,
  `my_pitch_type` tinyint(2) default NULL,
  PRIMARY KEY  (`pitch_id`),
  KEY `ab_id` (`ab_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1015447 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `players`
-- 

CREATE TABLE `players` (
  `eliasid` mediumint(6) unsigned NOT NULL,
  `first` varchar(20) NOT NULL,
  `last` varchar(20) NOT NULL,
  `lahmanid` varchar(10) default NULL,
  `throws` varchar(1) default NULL,
  PRIMARY KEY  (`eliasid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='MLB Player IDs and names';

-- --------------------------------------------------------

-- 
-- Table structure for table `umpires`
-- 

CREATE TABLE `umpires` (
  `ump_id` smallint(5) unsigned NOT NULL auto_increment,
  `first` varchar(20) NOT NULL,
  `last` varchar(20) NOT NULL,
  PRIMARY KEY  (`ump_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Home plate umpire names' AUTO_INCREMENT=95 ;