Specifications for Previous Releases

  • Draft 22, 3/17/2009
    • Added stat_membership_type and stat_membership_id fields to stats table
    • Added indexes for stat_coverage_type and stat_coverage_id fields of stats table
    • Added sportsml_blob field to document_contents table, for users who prefer to store entire SportsML source files, rather than just pathnames to the files
    • Added fields to ice_hockey_action_plays table:
      • penalty_type
      • penalty_length
      • penalty_code
      • recipient_type
      • team_id
      • strength
      • location_x
      • location_y
      • location_zone
      • shootout_shot_order
      • goal_order
      • shot_type
      • shot_distance
      • goal_zone
      • empty_net
      • goal_awarded
    • Added fields to ice_hockey_action_participants table:
      • goals_to_date
      • assists_to_date
      • points_to_date
  • Draft 21 (unreleased, interim build), 11/21/2008
    • New table called rankings, for NCAA polls, etc.
    • New table called penalty_stats
    • Added fields goals_allowed and shots_allowed to ice_hockey_defensive_stats
    • Adjusted length of comment fields in wagering tables
    • Added time_certainty field to events table
    • Folded all fields from core_stats_player table into core_stats table
  • Draft 20, 09/18/2008
    • Schematic Diagram of Tables (JPEG)
    • Generated Documentation (rather sparse for now, but some fields filled in)
    • HF (Happy Fish format)
    • SQL: MySQL 3 | SQL Server 2000 | PostgreSQL 8
    • What's New (in Draft 20):
      • Renamed soccer_action_penalties.penalty_value to soccer_action_penalties.penalty_level
      • Fixed typo in ice_hockey_action_participants.ice_hockey_action_play_id field
      • Added indexes to useful fields (such as the event_states context and sequence_number fields)
      • Added new generic event state and event action tables:
        • event_states
        • event_action_fouls
        • event_action_plays
        • event_action_participants
        • event_action_penalties
      • Added new tennis tables and fields:
        • tennis_service_stats
        • tennis_return_stats
        • tennis_set_stats
        • tennis_team_stats
        • tennis_player_stats
      • Added events.event_number and events.round_number fields
      • Added new fields to ice_hockey_defensive_stats table: goaltender_wins, goaltender_losses, goaltender_ties
      • Added person_phases.duration field
      • Increased length of documents.revision_id and latest_revisions.revision_id fields
      • Other maintenance adjustments concerning Foreign Keys
    • Patches for moving from SportsDB 19 to 20: MySQL 3 | SQL Server 2000 | PostgreSQL 8
    • Transition SQL from SportsDB 19 to 20: MySQL 3 | SQL Server 2000 | PostgreSQL 8
  • Draft 19, 05/27/2008
    • Schematic Diagram of Tables (JPEG)
    • Generated Documentation (rather sparse for now, but some fields filled in)
    • HF (Happy Fish format)
    • SQL: MySQL 3 | SQL Server 2000 | PostgreSQL 8
    • What's New (in Draft 19):
      • Lengthened comment column for these tables:
        • american_football_action_plays
        • baseball_action_plays
        • baseball_action_substitutions
        • baseball_action_contact_details
        • ice_hockey_action_plays
      • Created new tables:
        • awards
        • records
        • soccer_action_fouls
        • soccer_action_plays
        • soccer_action_participants
        • soccer_action_penalties
        • soccer_action_substitutions
      • Added columns to
        • baseball_action_plays: out_type
        • participants_events: result_effect, score_attempts
        • periods: label, score_attempts, rank
        • sub_periods: score_attempts
      • Adjusted length of column sites.site_key
      • Adjusted several columns in standings, standing_subgroup, and outcome_totals tables
      • Adjusted columns baseball_action_plays.runner_on_*_advance columns to be VARCHAR
    • Transition SQL from SportsDB 18 to 19: MySQL 3 | SQL Server 2000 | PostgreSQL 8
  • Draft 18, 03/30/2008
    • Schematic Diagram of Tables (JPEG)
    • Generated Documentation (rather sparse for now, but some fields filled in)
    • HF (Happy Fish format)
    • SQL: MySQL 3 | SQL Server 2000 | PostgreSQL 8
    • What's New (in Draft 17):
      • lengthened positions.abbreviation field
      • added ice_hockey_action_plays and ice_hockey_action_participants tables
      • added new columns to person_phases: entry_reason, exit_reason, selection_level, selection_sublevel, selection_overall
      • modified person_phases.regular_position_id to allow NULLs; lengthened person_phases.regular_position_depth
    • What's New (in Draft 18):
      • modified seasons.league_id to allow NULLs
      • added indexes to display_names.entity_id and entity_type columns (very critical for performance!)
      • renamed team_american_football_stats table to be called american_football_team_stats (for consistency)
    • Transition SQL from SportsDB 16 to 17 (do this prior to moving up to 18): MySQL 3 | SQL Server 2000 | PostgreSQL 8
    • Transition SQL from SportsDB 17 to 18: MySQL 3 | SQL Server 2000 | PostgreSQL 8
  • Draft 17 (not publicly released; changes listed within Draft 18)
  • Draft 16, 1/8/2008
  • Draft 15, 10/12/2007
    • JPG
    • Generated Documentation (rather sparse for now, but some fields filled in)
    • HF (Happy Fish format)
    • SQL: MySQL 3 | SQL Server 2000 | PostgreSQL 7
    • What's New:
      • fixed foreign-key problem with injury_phases
      • Allowed NULL for injury_phases.season_id column
      • Added injury_phases.phase_type and injury_phases.injury_side columns
      • Added american_football_action_participants.score_credit and american_football_action_participants.yards_gained columns
      • Added context column to these tables:
        • american_football_event_states
        • baseball_event_states
        • basketball_event_states
        • ice_hockey_event_states
        • motor_racing_event_states
        • soccer_event_states
        • tennis_event_states
      • Made american_football_action_plays.comment and baseball_action_plays.comment columns longer
      • Allowed NULL for baseball_action_substitutions.person_original_id and person_original_position_id columns
    • Transition SQL from XTOSS14: MySQL 3 | SQL Server 2000 | PostgreSQL 7
  • Pre-release 14, 7/22/2007
    • added column to person_phases: regular_position_depth
    • added column to person_event_metadata: health
    • added columns to baseball_pitching_stats: singles_allowed, doubles_allowed, triples_allowed
    • RENAMED team_affiliation_phases to be just team_phases
    • REMOVED teams.league_id (redundant with team_phases data)
    • REMOVED key_aliases.alias_key -- redundant
    • ALLOWED start_season_id and end_season_id in person_phases and team_phases to be NULL
    • ALLOWED role_id and regular_position_id in person_phases to be NULL. Made membership_type field be NOT NULL.
  • Pre-release 13, 5/17/2007
    • JPG (mirrored JPG -- may load faster)
    • Generated Documentation (rather sparse for now, but some fields filled in)
    • HF (Happy Fish format)
    • What's New:
      • SQL: MySQL 3 | SQL Server 2000 | PostgreSQL 7
      • Removed ice_hockey_event_states.publisher_key
      • Added columns to ice_hockey_defensive_stats: goals-shootout-allowed, shots-shootout-allowed, goals-empty-net-allowed, goals-short-handed-allowed
      • New table ice_hockey_player_stats with one column: plus_minus
      • New table basketball_team_stats with columns timeouts_left, largest_lead, fouls_total, turnover_margin
      • Changed core_person_stats.time_played to core_person_stats.time_played_event
      • Added columns to core_person_stats: time_played_total, time_played_event_average
      • Added columns to display_names: prefix, suffix
      • Changed baseball_action_plays.comment and american_football_action_plays.comment from VARCHAR 100 to VARCHAR 200
      • Added columns to person_event_metadata: lineup-slot and lineup-slot-sequence
      • Remove columns from affiliations: start_season_id, start_date_time, end_season_id, end_date_time
      • New table affiliation_phases with columns: id, affiliation_id, ancestor_affiliation_id, start_season_id, start_date_time, end_season_id, end_date_time
  • Pre-release Draft 12, 3/21/2007
    • JPG (mirrored JPG -- may load faster)
    • Generated Documentation (rather sparse for now, but some fields filled in)
    • HF (Happy Fish format)
    • What's New in Draft 12
      • SQL: MySQL 3 | SQL Server 2000 | PostgreSQL 7
      • Added db_info table, to store XTOSS Schema Version Number (and other future "sysinfo" properties)
      • Changed affiliations.tier field to be to affilations.affiliation_type
      • Added (optional) team_id FK to person_event_metadata
      • Dropped current_injury_phase_id, current_team_phase_id, current_league_phase_id from persons table
      • Modified datatypes for three baseball_*_stats tables, to be more granular
      • Made *_event_states tables more consistent, with event_id, current_state, and sequence_number
  • Pre-release Draft 11, 3/12/2007
    • JPG (mirrored JPG -- may load faster)
    • Generated Documentation (rather sparse for now, but some fields filled in)
    • HF (Happy Fish format)
    • SQL: MySQL 3 | SQL Server 2000 | PostgreSQL 7
    • What's New in Draft 11
      • Included indexes on most all relevant fields
      • Included Foreign Key enforcement on most all relevant fields
      • Expanded baseball play-by-play
      • Made baseball and american-football event_state tables cleaner (will do same for other sports soon)
      • changed "stats.final" boolean to a stats.context varchar
      • Combined teams_phases and person_league_phases to person_phases; made it polymorphic
      • Added phase_status to teams_affiliations-phases -- active | inactive
      • Added publisher_id to affiliations
      • Allow teams.league_id to be NULL
      • Added events.last_update
      • Add role_id to team_affiliation_phases (e.g., identifies the relationship between a minor league team and parent MLB ballclub)
      • Added event_id and person_id FK fields to person_event_metadata table
      • Added rank to participant_events
      • Changed bookmaker_key to bookmaker_id FK
      • Added bookmakers table (id | bookmaker_key | publisher_id | location_id) # can also have display_names
      • Added Soccer Stats and Event State
      • Added Motor Racing Stats and Event State
      • other various improvements
  • Pre-release Draft 10, 6/26/2006
  • Pre-release Draft 9, 6/12/2006
    • JPG (mirrored JPG -- may load faster)
    • Generated Documentation (rather sparse for now, but some fields filled in)
    • HF (Happy Fish format)
    • SQL (MySQL)
    • What was new:
      • New table: baseball_defensive_players:
        		id
        		baseball_defensive_group_id
        		player_id
        		position_id
        
        		Resolved: Keep pitchers OUT of this new table.
        
        	New table: baseball_defensive_group:
        		id
        
        	New column for table baseball_action_plays
        		and baseball_action_pitches: 
        			baseball_defensive_group_id
        
        	New table: display_names (for multilingual person, team and 
        		conference names)
        			id
        			entity_type == person | team | affiliation
        					| site | position
        			entity_id == (either a person_id value or 
        					a team_id value or etc.)
        			language
        			full_name
        			first_name
        			middle_name
        			last_name
        			alias
        			abbreviation
        			short_name
        
        	Removed person_names table (subsumed by new "display_names" table)
        
        	Removed these columns from teams table:
        		first_name
        		last_name
        		full_name
        		alias
        		abbreviation
        		short_name
        
        	Removed these columns from affiliations table:
        		name
        		alias
        		abbreviation
        
        	Removed these columns from sites table:
        		name
        		alias
        
        	Removed this columns from events table:
        		name
        
        	Removed these columns from positions table:
        		abbreviation
        		name
        
        	Firebird DB Compatibility (per this list)
        		Shortened all table names to 32 characters or less
        			american_football_* changed to am_football_*
        			american_football_action_play_participants to  
        				am_football_action_participants
        
        		Changed from generic field names that are reserved Firebird terms
        			(type, role, size, value, set) as follows:
        
        		TABLE				OLD FIELD	NEW FIELD
        		-----------------------------	-------------	---------------------
        		media     			type     	media_type
        		sub-season     			type     	sub_season_type
        		standings     			type     	standings_type
        		person_league_phases		role     	person_role
        		am_football_action_participants	role     	participant_role
        		media_captions     		size     	caption_size
        		media_contents     		size     	file_size
        		periods     			value  		period_value
        		sub_periods     		value  		sub_period_value
        		wagering_runlines     		value		line_value
        		wagering_straight_spread_lines	value     	line_value
        		wagering_straight_spread_lines	value_opening   line_value_opening
        	
    • Pre-release Draft 8, 3/22/2006
      • JPG (mirrored JPG -- may load faster)
      • Generated Documentation (rather sparse for now, but some fields filled in)
      • HF (Happy Fish format)
      • SQL (MySQL)
      • What was new:
        • Standings tables
        • Document Package tables
        • Redid many of the Events tables
          • Unifies tables
          • Allows for IDs to identify periods and sub_periods
        • Added media tables
    • Pre-release Draft 7, 3/2/2006
      • JPG (mirrored JPG -- may load faster)
      • PNG (smaller; loads faster; less crisp to read)
      • HF (Happy Fish format)
      • SQL (MySQL)
      • What Was New
        • Moved entire site to Sourceforge & now saving drafts within Subversion (version control)
        • Various table and field renamings
          • Named affiliation_phases table back to affiliations.
            • When it's named @affiliation_phases@ it suggests that there's another table called @affiliations@ which it references, which is not the case.
            • Change type field to tier in affiliations table, to be more descriptive
          • Renamed doc_classes table over to document_classes, to reflect the sportsml naming.
          • Renamed fixtures table to document_fixtures
            • Despite the sportsml naming, because "fixtures" is too general, and this jives better with "document_classes". Also, "fixtures" is a loaded Ruby / database term.
          • Changed name of events_teams_subscores table to teams_events_subscores, to match teams_events.
        • Added sub_season_key to sub_seasons table. Maps to @season-type@ sports content code.
        • Add recurring_events table, and added recurring_event_id to events (for recurring event names like "Daytona 500" and "Wimbledon")
        • Added document_contents table
          • To hold the actual sportsml, and an option abstract
        • Combined player_stats, team_stats, and affiliation_stats tables into one fancy "stats" table
          • Thrice polymorphic!
          • Per Ruby and polymorphism conventions, stat_type now holds a table name, and stat_id holds an id value
          • Renamed the old stat_type to be stat_stage (for distinguishing between intermediate results and post-game results, for example)
          • See "How Stats are Tied In" section for more details
        • Normalized all location values
          • For multilingualness's sake, locations table contains only language-independent information
            • id
            • timezone
            • latitude
            • longitude
            • country_code
          • Added birth_location_id, hometown_location_id, residence_location_id, death_location_id to persons table
            • Removed birth_city, birth_state, birth_country
          • Added location_id to sites table
            • Replaces country, state_province, city, postal_code, postal_code_extension, timezone, address_1, address_2
          • A location can have zero or more address records:
            • id
            • language
            • suite (for apartment number, suite, Post Office Box)
            • floor
            • building
            • street_number
            • street_prefix
            • street
            • street_suffix
            • neighborhood
            • district
            • locality
            • county
            • region
            • postal_code
            • country
            • address1
            • address2
    • Pre-release Draft 6, 2/2/2006
      • JPG | HF (Happy Fish format) | SQL (MySQL)
      • What Was New
        • Renamed "player" to "person" everywhere; added roles table
        • Added person_event_metadata table
        • Added trajectory_coordinates and trajectory_formula fields to baseball_actions_pitches table
        • Added key_aliases table
        • Added baseball_action_contact_details table
        • Added position_id to core_person_stats, to allow one to record how long the person was at this position
    • Pre-release Draft 5, 11/7/2005: JPG | HF | SQL (MySQL)
    • Pre-release Draft 4, 5/27/2005: PNG | HF | SQL
    • Pre-release Draft 3, 4/26/2005: JPG | HF | SQL
    • Pre-release Draft 2, 4/18/2005: JPG
    • Pre-release Draft 1, 4/14/2005: JPG | HF | SQL