SQL Dump Table Schema

BMW · 04-14-2007, 01:29 PM

I apologize if this is already somewhere, but I have not been able to track it down.

Does someone know where a document with the table schema for the 2007 SQL dump output?

Much of it is easy to figure out, but the definition of fields like "IPF" on the fielding table elude me. I searched this board in particular, and three separate charts of stat abbreviations, and I cannot figure out what it is.

Some are easily deduced, like the CASE statements I need to write for League_Levels (1=ML, 2=AAA, 3=AA, etc...)

But does anyone have documentation for this?

Thanks,
Bryan

tysok · 04-14-2007, 02:33 PM

Don't know where it is.... but IPF is the innings pitched or played fraction.

If your pitcher threw (or player played) 1 1/3 innings... the stats line will show IP=1 and IPF=1
1 2/3 innings... IP=1 IPF=2

We know what most, if not all, of the categories mean... I just don't know where the chart is. If you have specific questions post them and someone should know what they are.

Tony M · 04-15-2007, 05:45 AM

Cheers. I'd always wondered what that field was (and why the IP field in the database was always an int32 and IPF was only ever 0,1 or 2)

In the pitching stats there are the following

RS (int32) - Run Support?
RA (int32) - Runs Allowed or Relief Appearances?
SA (int32) - Singles Allowed?
PIG (double) - Pitches per Game?
GFP (double) - Ground/Fly Percent? but if so is
GBP (double) - Ground Ball Percent? and therefore isn't GFP = GBP/(1-GBP)

Are my understandings correct there?

Comedian2004 · 04-15-2007, 11:38 AM

Here is a file you may find useful that I created.

www.planetootp.com/fields.txt

exoverge · 04-15-2007, 12:42 PM

Quote:

Originally Posted by Comedian2004

Here is a file you may find useful that I created.

www.planetootp.com/fields.txt

Great resource. Thanks for sharing!

Muzamba · 04-15-2007, 12:48 PM

I'm confused. Why are there so many fields that are unknown? Is this some esoteric information that only Markus is privy to, or is it part of the framework for future versions that no one within the inner sanctum may divulge?

Raidergoo · 04-15-2007, 12:51 PM

Quote:

Originally Posted by Muzamba

Is this some esoteric information that only Markus is privy to, or is it part of the framework for future versions that no one within the inner sanctum may divulge?

1) yes
2) think so
3) everything that Larry learns via experimentation or drags out of Markus gets documented.

BMW · 04-15-2007, 12:59 PM

Quote:

Originally Posted by Comedian2004

Here is a file you may find useful that I created.

www.planetootp.com/fields.txt

That's great, just what I need. I'm sure we could figure it out, but if you already know... For the injury frequency fields, which number is worse? High or Low?

Comedian2004 · 04-15-2007, 01:06 PM

Some of the fields are used internally in the game, why they are in the dump, I have no idea.

The CSV dump, IMHO, was a low priority during the beta. I started compiling this data early on in the beta, but then they changed some of the dumps on me and I kenw that I would have to do the whole thing over again towards the end of beta, so I put it off until then.

By then, all hell had broke loose, as we were trying to get major bugs fixed and make the game the best it could be, so I did not get a lot of my questions answered or issues changed in the files that I suggested.

Then to top things off, I spent at least 8 hours putting together this file and they did not even include in the game or the documents or even sticky it somewhere. They did the same thing with the almanac documentation that I spent quite a bit of time working on and experimenting with.

Tony M · 04-15-2007, 02:36 PM

I remember Football Manager / Championship Manager days when they produced the database structure so that editors / other software could be written to tap into the massive data sources used.

Don't know if the same is true of the latest version of FM, a google search lists FM2005 database structure/header files on the si website.

It's like the three unknown strategy options in the situational strategy of teams.dat or the section of 8 lineups that appears after the 4 depth charts (I thought it might have been seven day lineups but I can't put it together like that).

Roster sizes are limited to 50, but each of the 'active', 'secondary', 'disabled' and 'all' has space for 200.

Depth charts have 13 sets of data for each of vRHPwoDH, vLHPwoDH, vRHPwDH, vLHPwDH (nine for catcher, 1B, 2B, 3B, SS, LF, CF, RF, DH and then four other records that are 'blank'.

I haven't tried this out but has anyone got more than 11 affiliates to one major league team (it could well be 10)

I think I need to move away from working with databases as part of my actual work (you start to get good at seeing how they're put together) - though it's a very well designed database structure I have to say.

04-14-2007, 01:29 PM	#1
BMW Hall Of Famer Join Date: May 2003 Location: New Jersey Posts: 2,030	SQL Dump Table Schema I apologize if this is already somewhere, but I have not been able to track it down. Does someone know where a document with the table schema for the 2007 SQL dump output? Much of it is easy to figure out, but the definition of fields like "IPF" on the fielding table elude me. I searched this board in particular, and three separate charts of stat abbreviations, and I cannot figure out what it is. Some are easily deduced, like the CASE statements I need to write for League_Levels (1=ML, 2=AAA, 3=AA, etc...) But does anyone have documentation for this? Thanks, Bryan

04-14-2007, 02:33 PM	#2
tysok Hall Of Famer Join Date: Aug 2003 Posts: 4,925	Don't know where it is.... but IPF is the innings pitched or played fraction. If your pitcher threw (or player played) 1 1/3 innings... the stats line will show IP=1 and IPF=1 1 2/3 innings... IP=1 IPF=2 We know what most, if not all, of the categories mean... I just don't know where the chart is. If you have specific questions post them and someone should know what they are. __________________ I don't know about you, but as for me, the question has already been answered: Should we be here? Yes! Jack Buck, September 17, 2001 It's what you learn after you know it all that counts. I firmly believe that any man's finest hour... is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious. (Vince Lombardi) I don't measure a man's success by how high he climbs but how high he bounces when he hits bottom. (George S. Patton)

04-15-2007, 02:36 PM	#10
Tony M Global Moderator Join Date: Feb 2006 Location: Here Posts: 6,156	I remember Football Manager / Championship Manager days when they produced the database structure so that editors / other software could be written to tap into the massive data sources used. Don't know if the same is true of the latest version of FM, a google search lists FM2005 database structure/header files on the si website. It's like the three unknown strategy options in the situational strategy of teams.dat or the section of 8 lineups that appears after the 4 depth charts (I thought it might have been seven day lineups but I can't put it together like that). Roster sizes are limited to 50, but each of the 'active', 'secondary', 'disabled' and 'all' has space for 200. Depth charts have 13 sets of data for each of vRHPwoDH, vLHPwoDH, vRHPwDH, vLHPwDH (nine for catcher, 1B, 2B, 3B, SS, LF, CF, RF, DH and then four other records that are 'blank'. I haven't tried this out but has anyone got more than 11 affiliates to one major league team (it could well be 10) I think I need to move away from working with databases as part of my actual work (you start to get good at seeing how they're put together) - though it's a very well designed database structure I have to say. Last edited by Tony M; 04-15-2007 at 02:40 PM.

04-15-2007, 05:45 AM	#3
Tony M Global Moderator Join Date: Feb 2006 Location: Here Posts: 6,156	Cheers. I'd always wondered what that field was (and why the IP field in the database was always an int32 and IPF was only ever 0,1 or 2) In the pitching stats there are the following RS (int32) - Run Support? RA (int32) - Runs Allowed or Relief Appearances? SA (int32) - Singles Allowed? PIG (double) - Pitches per Game? GFP (double) - Ground/Fly Percent? but if so is GBP (double) - Ground Ball Percent? and therefore isn't GFP = GBP/(1-GBP) Are my understandings correct there?

04-15-2007, 11:38 AM	#4
Comedian2004 Hall Of Famer Join Date: Nov 2004 Location: In a house in Saint Cloud, Florida. Posts: 7,085	Here is a file you may find useful that I created. www.planetootp.com/fields.txt

04-15-2007, 12:48 PM	#6
Muzamba Hall Of Famer Join Date: Apr 2002 Location: St. Louis, MO Posts: 2,325	I'm confused. Why are there so many fields that are unknown? Is this some esoteric information that only Markus is privy to, or is it part of the framework for future versions that no one within the inner sanctum may divulge?

04-15-2007, 01:06 PM	#9
Comedian2004 Hall Of Famer Join Date: Nov 2004 Location: In a house in Saint Cloud, Florida. Posts: 7,085	Some of the fields are used internally in the game, why they are in the dump, I have no idea. The CSV dump, IMHO, was a low priority during the beta. I started compiling this data early on in the beta, but then they changed some of the dumps on me and I kenw that I would have to do the whole thing over again towards the end of beta, so I put it off until then. By then, all hell had broke loose, as we were trying to get major bugs fixed and make the game the best it could be, so I did not get a lot of my questions answered or issues changed in the files that I suggested. Then to top things off, I spent at least 8 hours putting together this file and they did not even include in the game or the documents or even sticky it somewhere. They did the same thing with the almanac documentation that I spent quite a bit of time working on and experimenting with.