Home | Webstore
Latest News: OOTP Update 20.5.52 - OOTP 20 Available - FHM 5 Available

MLB All-Star Game Special: Save 50% on OOTP 20!

  

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

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

Reply
 
Thread Tools
Old 06-25-2006, 02:51 PM   #1
fantom1979
Hall Of Famer
 
fantom1979's Avatar
 
Join Date: Jul 2002
Location: Detroit, MI
Posts: 3,497
Thanks: 45
Thanked 50x in 27 posts
Need some Mysql help

Using SQLyog, I have to process each batch file individually. This takes quite a bit of user interaction (54 batch files). Is there a way to combine the batch files to load them all at once or is there a way to create a new batch file that automatically uploads all of the other batch files. Sorry, I am a bit of a newb here.

Thanks
fantom1979 is offline   Reply With Quote
Old 06-25-2006, 07:34 PM   #2
DaveHorn
All Star Starter
 
DaveHorn's Avatar
 
Join Date: Aug 2003
Posts: 1,146
Thanks: 2
Thanked 1x in 1 post
Do you have PHP on your server?

This will load all tables for you at once. There are others out there as well.
__________________
Dave
HFTC Commish
DaveHorn is offline   Reply With Quote
Old 06-25-2006, 11:27 PM   #3
fantom1979
Hall Of Famer
 
fantom1979's Avatar
 
Join Date: Jul 2002
Location: Detroit, MI
Posts: 3,497
Thanks: 45
Thanked 50x in 27 posts
Dave,

I see your thread about the importer, but I get a tad lost with the PEAR stuff. I am running a home server on Windows 2000 with Apache, Mysql, and PHP. I created it just to make website and a message board. I have done some simple PHP scripts, but for the most part I am still new at this. I use SQLyog and PHPmyAdmin to connect to MySQL. SQLyog allows me to import a batch file, but only one at a time. If there was just a way to create a batch file that would load all of the other batch files, that would be the ideal solution for me.
fantom1979 is offline   Reply With Quote
Old 06-26-2006, 01:13 AM   #4
fantom1979
Hall Of Famer
 
fantom1979's Avatar
 
Join Date: Jul 2002
Location: Detroit, MI
Posts: 3,497
Thanks: 45
Thanked 50x in 27 posts
Ok, i think i might be on to something here. I downloaded Text Magician (freeware http://www.david-pye.com/ ) and combined all of the .sql files into one big .sql file. I will run a test of the actual import tonight and see if it works.
fantom1979 is offline   Reply With Quote
Old 06-26-2006, 11:46 AM   #5
DaveHorn
All Star Starter
 
DaveHorn's Avatar
 
Join Date: Aug 2003
Posts: 1,146
Thanks: 2
Thanked 1x in 1 post
If you installed your win install using one of the packages that install Apache/MySQL/PHP all in one shot, it may of already contained the Pear packages. At the very least it intalled the Pear files for you. Then all you need to do is type the following from your php installed directory

go-pear
pear install DB


But simply merging all files into one and then loading it will work as well. Just more steps to perform everytime you load tables.
__________________
Dave
HFTC Commish
DaveHorn is offline   Reply With Quote
Old 06-26-2006, 01:10 PM   #6
fantom1979
Hall Of Famer
 
fantom1979's Avatar
 
Join Date: Jul 2002
Location: Detroit, MI
Posts: 3,497
Thanks: 45
Thanked 50x in 27 posts
Quote:
Originally Posted by DaveHorn
If you installed your win install using one of the packages that install Apache/MySQL/PHP all in one shot, it may of already contained the Pear packages. At the very least it intalled the Pear files for you. Then all you need to do is type the following from your php installed directory

go-pear
pear install DB


But simply merging all files into one and then loading it will work as well. Just more steps to perform everytime you load tables.
Thanks for your help Dave, I will check out PEAR more in the next couple of weeks, I am very interested.
fantom1979 is offline   Reply With Quote
Old 06-26-2006, 01:12 PM   #7
fantom1979
Hall Of Famer
 
fantom1979's Avatar
 
Join Date: Jul 2002
Location: Detroit, MI
Posts: 3,497
Thanks: 45
Thanked 50x in 27 posts
Quote:
Originally Posted by fantom1979
Ok, i think i might be on to something here. I downloaded Text Magician (freeware http://www.david-pye.com/ ) and combined all of the .sql files into one big .sql file. I will run a test of the actual import tonight and see if it works.
This worked for me, it takes some time to combine the files and then to import the now huge batch file, but now I can import the file in two steps instead of the 54 steps that was required before.
fantom1979 is offline   Reply With Quote
Old 06-28-2006, 08:34 AM   #8
Andreas Raht
Administrator
 
Andreas Raht's Avatar
 
Join Date: Jun 2002
Location: Hollern/Stade/Germany
Posts: 8,834
Thanks: 855
Thanked 4,829x in 1,561 posts
If you're having shell access to the server, you might also try this:

mysql -uusername -ppassword -t database_name < path_to_file/filename.sql

This will execute all the commands from the .sql file "directly". Should be very fast, and doesn't require any 3rd party lib.

Unix geeks will easily tell us how to import all sql files this way with a single line command using find and pipe or however that's called

You could also write a Perl script which creates a list of sql files and makes system calls for each file. I'll post such a script here ASAP.
Andreas Raht is online now   Reply With Quote
Old 06-28-2006, 09:18 PM   #9
fantom1979
Hall Of Famer
 
fantom1979's Avatar
 
Join Date: Jul 2002
Location: Detroit, MI
Posts: 3,497
Thanks: 45
Thanked 50x in 27 posts
Thanks Andreas,

I have had no problem loading the sql files by themselves using a 3rd party or batch file. I just didnt want to have to sit in front of my computer to load each of the 54 batch files. By combining them using a text editor I only have to touch my computer to merge the files and to load the one large .sql batch file. A Perl script would be great as well
fantom1979 is offline   Reply With Quote
Old 06-30-2006, 04:57 AM   #10
Andreas Raht
Administrator
 
Andreas Raht's Avatar
 
Join Date: Jun 2002
Location: Hollern/Stade/Germany
Posts: 8,834
Thanks: 855
Thanked 4,829x in 1,561 posts
Here is the promised Perl code to import the sql files on a server with Perl and MySQL available.
This code has not been tested and we do not offer any support for it. Use it at your own risk!

It just reads a directory into a list, then skips to the list, looking for files with the wanted file name extension, then calls mysql for each file which it finds. BTW you could also add another line to unlink (delete) the file after importing it, then add the script to your crontab to be executed every 30 minutes, which will automate the process a little bit. Just an idea.

Code:
#!/usr/bin/perl
use strict;
use CGI::Carp qw(fatalsToBrowser);

my $path="/usr/local/data/whatever/";
opendir(DIRHANDLE, $path) || die "Cannot opendir /some/path: $path!";
my @dirlist=readdir(DIRHANDLE);
my $name;
foreach $name(@dirlist) 
{
 if (index($name,".mysql.sql") > 0)
 {
  print "Importing ".$path.$name."...\n";
  system("mysql -uusername -ppassword -t database_name < ".$path.$name);
  
 }
 
}
closedir(DIRHANDLE);
Andreas Raht is online now   Reply With Quote
Old 06-30-2006, 10:01 PM   #11
fantom1979
Hall Of Famer
 
fantom1979's Avatar
 
Join Date: Jul 2002
Location: Detroit, MI
Posts: 3,497
Thanks: 45
Thanked 50x in 27 posts
Quote:
Originally Posted by Andreas Raht
Here is the promised Perl code to import the sql files on a server with Perl and MySQL available.
This code has not been tested and we do not offer any support for it. Use it at your own risk!

It just reads a directory into a list, then skips to the list, looking for files with the wanted file name extension, then calls mysql for each file which it finds. BTW you could also add another line to unlink (delete) the file after importing it, then add the script to your crontab to be executed every 30 minutes, which will automate the process a little bit. Just an idea.

Code:
#!/usr/bin/perl
use strict;
use CGI::Carp qw(fatalsToBrowser);

my $path="/usr/local/data/whatever/";
opendir(DIRHANDLE, $path) || die "Cannot opendir /some/path: $path!";
my @dirlist=readdir(DIRHANDLE);
my $name;
foreach $name(@dirlist) 
{
 if (index($name,".mysql.sql") > 0)
 {
  print "Importing ".$path.$name."...\n";
  system("mysql -uusername -pp***word -t database_name < ".$path.$name);
  
 }
 
}
closedir(DIRHANDLE);
Thank you
fantom1979 is offline   Reply With Quote
Reply

Bookmarks

Thread Tools

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 08:14 AM.

 

Major League Baseball trademarks and copyrights are used with permission of MLB Advanced Media, L.P. Minor League Baseball trademarks and copyrights are used with the permission of Minor League Baseball. All rights reserved.

The Major League Baseball Players Association (www.MLBPLAYERS.com ) is the collective bargaining representative for all professional baseball players of the thirty Major League Baseball teams and serves as the exclusive group licensing agent for commercial and licensing activities involving active Major League baseball players. On behalf of its members, it operates the Players Choice licensing program and the Players Choice Awards, which benefit the needy through the Major League Baseball Players Trust, a charitable foundation established and run entirely by Major League baseball players. Follow: @MLB_Players; @MLBPAClubhouse; @MLBPlayersTrust

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 © 2017 OUT OF THE PARK DEVELOPMENTS. ALL RIGHTS RESERVED.

 

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