Home | Webstore
Latest News: OOTP 26 Available - FHM 11 Available - OOTP Go! Available

Out of the Park Baseball 26 Buy Now!

  

Go Back   OOTP Developments Forums > Out of the Park Baseball 26 > OOTP Mods > OOTP Mods - Database Tools

OOTP Mods - Database Tools Do you need to take a dump? SQL gurus welcome

Reply
 
Thread Tools
Old 07-02-2020, 11:29 AM   #1
cavebutter
All Star Starter
 
cavebutter's Avatar
 
Join Date: Dec 2005
Location: Los Angeles
Posts: 1,164
cavebutter's Gameday Utilities

VERSION 2.0 IS COMPLETE
Please see the V2.0 thread. The big change is that MySQL is no longer required! Just double-click the exe and go!

The Windows version of the utilities is now available for download here:
Gameday Utilities.zip

The whole project is open-source and available on Github:
https://github.com/cavebutter/ootp_gameday

The next few posts will go through the readme file and talk about some of the cool things, not so cool things, and some do's and don'ts.

A word about Macs. I will update this post when a Mac version is available. It's a different process turning code into a Mac app, and I have not gotten the hang of it yet. The code in the python files will work on a Mac (that's how I am using them), but I have not been able to wrap it all into a single app yet.
__________________
MySQL, MyStruggle - A self-indulgent blog about my attempts to roll my own MySQL Database with OOTP

Logo Gallery

Last edited by cavebutter; 07-09-2020 at 06:55 PM.
cavebutter is offline   Reply With Quote
Old 07-02-2020, 11:33 AM   #2
cavebutter
All Star Starter
 
cavebutter's Avatar
 
Join Date: Dec 2005
Location: Los Angeles
Posts: 1,164
Description and caveat

Description

OOTP Game Day Tools is a set of utilities to enhance the gameplay of Out of the Park Baseball. It was developed to work with OOTP 21 and forward, though it should work with version XX as well.

There are 3 utilities in this package:

Database - Creates and updates a MySQL database for your OOTP save

Depth Charts - Creates an Excel Workbook showing organizational depth at each position

Game Day Workbooks - Creates an Excel Workbook showing a team's stats against their next 7 probable starting pitchers, against their opponents' bullpens, and their current year L/R splits overall

NOTE: This is alpha software. I have tested it on Windows and Mac and nothing has blown up. However, I have not included much in the way of error handling. This means, essentially, that if you fat finger a selection or enter a wrong path to your data, the application will just abend and you'll have to start over. Nothing catastrophic, but just a heads up that this is less-than professional grade error handling at the moment.
__________________
MySQL, MyStruggle - A self-indulgent blog about my attempts to roll my own MySQL Database with OOTP

Logo Gallery
cavebutter is offline   Reply With Quote
Old 07-02-2020, 11:58 AM   #3
cavebutter
All Star Starter
 
cavebutter's Avatar
 
Join Date: Dec 2005
Location: Los Angeles
Posts: 1,164
Requirements

The only things you really need to run these utilities on a Windows machine are a spreadsheet program (I have only tested in Excel, but will most likely work with alternatives) and a MySQL database server.

This server should live on your local machine because the application will be creating and deleting databases, and you will also need to set a global variable on the server - things that are hard to do on remote servers.

The two easiest ways to get MySQL server on your Windows machine are either downloading it directly from Oracle or installing an application called MAMP. I have found that the Oracle distribution is a little confusing to get set up, while MAMP is straight-forward and simple. However, MAMP will also install a web-server on your machine. Not harmful, but totally unnecessary for our purposes.

Once you have MySQL installed, you will need login credentials. These consist of a host name, username, and password. If you installed using the Oracle distribution, the default credentials are:
host = 127.0.0.1
user = root
password = [something you define at setup]

With MAMP, they default to:
host = localhost
user = root
password = root

The other thing you'll need to do is set a global variable. OOTP data files are very big, and we need to configure the server to accept large amounts of data at once. We're going to change the
Code:
max_allowed_packet
variable. We do this by finding the my.ini file.

Find and Edit the my.ini File:
1. Start MySQL server
2. Hit Win-R (or search windows) and type 'services'.
3. Find MySQL in the list, Right Click on it and choose Properties
4. Find the Path to Executable (it should be something like: "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe")
5. Go to that location. If there is a file called my.ini or my.cnf there, then open it for editing. Otherwise, create a new text file called my.ini
6. Find or create a section of the document [mysqld]
7. In that section, enter the following text:
Code:
max_allowed_packet=999M
8. Save and close the document
9. Restart the server

Bit of a hassle, but the good news is you only have to do it once.
__________________
MySQL, MyStruggle - A self-indulgent blog about my attempts to roll my own MySQL Database with OOTP

Logo Gallery
cavebutter is offline   Reply With Quote
Old 07-02-2020, 12:38 PM   #4
cavebutter
All Star Starter
 
cavebutter's Avatar
 
Join Date: Dec 2005
Location: Los Angeles
Posts: 1,164
Download and Setup

This is pretty straightforward. Follow the link in the first post (not going to repeat it in case the link changes) to download the zip file. Extract that zip to anywhere you'd like.

There are a lot of files and folders in there. The following are the ones to be aware of:

_README.txt - This is a readme file that contains most of the information you see in this thread.

output - this is the directory where all of your depth charts and gameday workbooks will be saved.

START_HERE.exe - this is the application. You can copy a shortcut to your desktop for easy access or just double-click it in place.
__________________
MySQL, MyStruggle - A self-indulgent blog about my attempts to roll my own MySQL Database with OOTP

Logo Gallery
cavebutter is offline   Reply With Quote
Old 07-02-2020, 12:51 PM   #5
cavebutter
All Star Starter
 
cavebutter's Avatar
 
Join Date: Dec 2005
Location: Los Angeles
Posts: 1,164
Walkthru: Startup and Creating a database

Launch START_HERE.exe

You will be presented with 3 options:
Database Stuff
Depth Charts
Game Day Workbook

If you're just starting out, you will need to set up your database, so enter "1"

You'll be asked for the location of your OOTP data files. Just enter the full path. Data files refers to all of the csv's that the game generates when you export to CSV. (See the game manual for information on how to dump data - I encourage you to NOT split large files, and to make sure that OSA ratings are included, and to export all tables.
Mac and Windows allow you to select a directory and copy its path. I recommend you do this and paste it at the prompt.

Then you'll be asked for the database name. If the application doesn't have that database name stored, it will ask you for your connection information. If it finds the database name, then it will begin.


The application will proceed to create the database, make some changes to the csv files and load the data to the database.

The application will create run value tables and use those tables to calculate advanced batting and pitching stats.

You're done! You're now able to create depth charts, game day books, or just query the database to learn interesting things about your league.

IMPORTANT NOTE ABOUT THE CONFIG FILE: This file is not secure and is not meant to be. Anyone with access to your computer can find this application directory, read the config.ini file and get the credentials to your database server.
__________________
MySQL, MyStruggle - A self-indulgent blog about my attempts to roll my own MySQL Database with OOTP

Logo Gallery
cavebutter is offline   Reply With Quote
Old 07-02-2020, 12:56 PM   #6
cavebutter
All Star Starter
 
cavebutter's Avatar
 
Join Date: Dec 2005
Location: Los Angeles
Posts: 1,164
Walkthru: Depth Charts

Enter the name of your database.

Once you're connected, you will be asked to choose the league that your team is in. Enter the league ID. Entering anything other than a league ID will cause an error

Once you choose your league, you will be asked to choose your team. You may also see options for All-Star teams or Futures teams. Selecting these probably won't produce results. Again, enter the team id number only. Anything else will cause an error.

An Excel workbook with the name format: team name-YYYY-mm-dd_depth_chart.xlsx will be created in the output sub-directory.

All of charts on the depth chart tabs pull data from the first several rows of the tables on the left. These can be sorted by any field by (in Excel) clicking the Data section of the ribbon and choosing Sort. This will open a dialog box to choose the sorting fields.

Tip: You can highlight cells and then sort by highlight color.
Attached Images
Image Image 
__________________
MySQL, MyStruggle - A self-indulgent blog about my attempts to roll my own MySQL Database with OOTP

Logo Gallery
cavebutter is offline   Reply With Quote
Old 07-02-2020, 02:05 PM   #7
cavebutter
All Star Starter
 
cavebutter's Avatar
 
Join Date: Dec 2005
Location: Los Angeles
Posts: 1,164
Walkthru: Gameday Workbooks

Game Day Workbooks

Before you start, export the schedule for your team's league from the OOTP game. Make a note of, and copy, the location.

Enter the name of your database

Once you're connected to the database, you'll choose your team's league by entering the league id...

Then choose your team's id

Paste the location of the schedule file when prompted

The Gameday Workbooks show the selected team's next 7 days worth of games- shows the starter, your active roster's lifetime stats against that starter and his bullpen.

The last tab shows some advanced stats splits for your active roster for the current year.

An Excel workbook with the name format team name_mm_dd_YYYY-mm_dd_YYYY.xlsx will be created in the output subdirectory.
Attached Images
Image Image 
__________________
MySQL, MyStruggle - A self-indulgent blog about my attempts to roll my own MySQL Database with OOTP

Logo Gallery
cavebutter is offline   Reply With Quote
Old 07-02-2020, 02:10 PM   #8
cavebutter
All Star Starter
 
cavebutter's Avatar
 
Join Date: Dec 2005
Location: Los Angeles
Posts: 1,164
Thoughts and Considerations

MySQL Considerations
OOTP is a very data intensive game, and the OOTP development team makes much more efficient use of data within the application than it does with the data dumps it generates. This creates some complications and forces some choices between performance and completeness of data.

In testing on a modern Windows 10 laptop, for example, a test data load of 5 years worth of data for 2 major leagues with full minors took just over 3 minutes for a localhost. I was unable to complete a load or update on a remote host programmatically, becuase the packet size exceeded the server's limit and I do not have permission to change that limit.

This is the reason for the strong recommendation for a local MySQL server. The other utilities will work fine with a remote server, but you will have to get your data into it outside of this toolset.

Future versions of this application may make some concessions in terms of completeness to accommodate remote servers.

The database is currently set up with WAY more data than it needs for depth charts and gameday books- but I think it's worth it to have that data there for more intricate stuff later on. I am thinking of ways to streamline the load for ONLY depthcharts and gameday books, but the fact remains that some sort of load will be necessary each time the data is updated. It's tricky, especially when this utility is not tied into the database that serves an online league.

Lastly, I would love feedback on what works and what doesn't, and suggestions for new features.
__________________
MySQL, MyStruggle - A self-indulgent blog about my attempts to roll my own MySQL Database with OOTP

Logo Gallery
cavebutter is offline   Reply With Quote
Old 07-04-2020, 11:00 AM   #9
Ty Cobb
Hall Of Famer
 
Ty Cobb's Avatar
 
Join Date: Mar 2013
Location: Grayling, MI
Posts: 4,573
Going to have to give this a look in my Virtual Box...it's at least a path for us Mac folks to test it out.

Edit: It launches in Virtual Box under 7. Now, I need to explore a bit.

Advice for Mac guys interested in this or any other Windows utility:
1. Download Virtual Box from Oracle.
2. Download Windows 7 by following this LEGIT article about how to get it for FREE. https://www.howtogeek.com/186775/how...media-legally/
3. Create a virtual machine, disable its internet access...'cause you never can be too careful with Microsoft...
4. Start running Windows stuff on your Mac when needed.
5. Warning: Advanced stuff, like 3d/video/graphic intensive labor will at the least bog down like you're running your OS off a floppy. Most lighter weight stuff will be fine. As with all things, the more RAM you have and can allocate to the box...the better/faster.
__________________
"You could not live with your own failure. Where did that bring you? Back to me."
Thanos

Last edited by Ty Cobb; 07-04-2020 at 11:10 AM.
Ty Cobb is offline   Reply With Quote
Old 07-07-2020, 04:30 PM   #10
damoore46
Minors (Rookie Ball)
 
damoore46's Avatar
 
Join Date: Jul 2015
Posts: 49
Trying it out now...

Setting up on a Win 7 computer. MySQL runs, but gives a warning that Win 7 not supported (to be expected).

A couple of questions before I try and go too deep:

1) Do you need monthly dumps or will an annual one do OK?

2) Will this work with other year OOTP games or only 21?

As a side note, at least on Win 7, the MySQL server database function when starting your program does not allow a cut and paste into the command line window. I have a really long path, so will move my test files to the root on C-drive to make the path shorter.

Thanks for any responses, and thanks for writing this program.

David
__________________
damoore46 is offline   Reply With Quote
Old 07-08-2020, 11:06 AM   #11
cavebutter
All Star Starter
 
cavebutter's Avatar
 
Join Date: Dec 2005
Location: Los Angeles
Posts: 1,164
Quote:
Originally Posted by damoore46 View Post
Setting up on a Win 7 computer. MySQL runs, but gives a warning that Win 7 not supported (to be expected).

A couple of questions before I try and go too deep:

1) Do you need monthly dumps or will an annual one do OK?

2) Will this work with other year OOTP games or only 21?

As a side note, at least on Win 7, the MySQL server database function when starting your program does not allow a cut and paste into the command line window. I have a really long path, so will move my test files to the root on C-drive to make the path shorter.

Thanks for any responses, and thanks for writing this program.

David
1. For the Gameday feature, I would do weekly dumps. The reason is that Gameday looks forward 7 days and is keyed off the current date in the game at the time of the dump.

2. This should work on XX. I believe that 19 will throw an error because there was a small change in the fields between 19 and XX.

3. Also, I am in the middle of refactoring the whole shebang so that you won't need MySQL at all. More details on that when it's complete.
__________________
MySQL, MyStruggle - A self-indulgent blog about my attempts to roll my own MySQL Database with OOTP

Logo Gallery
cavebutter is offline   Reply With Quote
Old 07-08-2020, 10:33 PM   #12
sircrayon
Minors (Rookie Ball)
 
Join Date: Aug 2017
Posts: 25
Would you mind explaining where the data dump is in game?
sircrayon is offline   Reply With Quote
Old 07-09-2020, 11:55 AM   #13
cavebutter
All Star Starter
 
cavebutter's Avatar
 
Join Date: Dec 2005
Location: Los Angeles
Posts: 1,164
Quote:
Originally Posted by sircrayon View Post
Would you mind explaining where the data dump is in game?

If you haven't configured the data dump before, you'll have to do that first.


In the GAME menu, choose GAME SETTINGS...
DATABASE...
In the top right, click on DATABASE TOOLS
Choose "Configure data export to CSV files"
That will bring up a popup where you get to choose which tables to export and some rules you can define. My utilities work best with all tables selected and the following:
Insert Column Names/CSV heading - CHECKED
Show OSA Player Ratings - CHECKED
Split big files - UNCHECKED

(NOTE: you can select Show Real Player Ratings if you want an advantage - but you need one or the other. DO NOT CHECK "Show no player ratings"


Click save and you're set. When you want to export data, go to Database Tools and select "Export Data to CSV Files"


Your files will be exported to OOTP Baseball 21/saved_games/your_game_name/import_export/csv
__________________
MySQL, MyStruggle - A self-indulgent blog about my attempts to roll my own MySQL Database with OOTP

Logo Gallery
cavebutter is offline   Reply With Quote
Old 07-10-2020, 01:53 AM   #14
ALB123
All Star Reserve
 
Join Date: Jan 2020
Location: Massachusetts
Posts: 638
This is incredible! I can't wait to try it out. Thanks for sharing it.
__________________
"I'm on the side that's always lost against the side of Heaven. I'm on the side of snake-eyes tossed against the side of seven" - Leonard Cohen "The Captain"
ALB123 is offline   Reply With Quote
Old 05-14-2022, 06:10 PM   #15
Kafkaesque85
Minors (Rookie Ball)
 
Join Date: Apr 2022
Location: Lakewood, OH
Posts: 34
Hey, does this still work and can I use it for OOTP 23?

Last edited by Kafkaesque85; 05-14-2022 at 06:11 PM. Reason: Font is too small
Kafkaesque85 is offline   Reply With Quote
Reply

Bookmarks


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -4. The time now is 05:05 AM.

 

Major League and Minor League Baseball trademarks and copyrights are used with permission of Major League Baseball. Visit MLB.com and MiLB.com.

Officially Licensed Product – MLB Players, Inc.

Out of the Park Baseball is a registered trademark of Out of the Park Developments GmbH & Co. KG

Google Play is a trademark of Google Inc.

Apple, iPhone, iPod touch and iPad are trademarks of Apple Inc., registered in the U.S. and other countries.

COPYRIGHT © 2023 OUT OF THE PARK DEVELOPMENTS. ALL RIGHTS RESERVED.

 

Powered by vBulletin® Version 3.8.10
Copyright ©2000 - 2025, vBulletin Solutions, Inc.
Copyright © 2024 Out of the Park Developments