This program is designed to update the databases automatically.
In a folder named “sql” insert 3 folders named as your database.
for ex:
(inside old/3.3.5a)
mv world.sql /sql/db_world/
mv characters.sql /sql/db_characters/
Within these folders enter file.sql to be launched (in the “world” included all the patches you need to launch world)
once this is done run the program in the terminal.
<?php
/*
* Php Auto Sql Launcher
*
* Copyright (C) 2008-2011 Talecraft
*
* This program is free software; you can redistribute it and/or modify it
* under the terms of the GNU General Public License as published by the
* Free Software Foundation; either version 2 of the License, or (at your
* option) any later version.
*
* This program is distributed in the hope that it will be useful, but WITHOUT
* ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
* FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for
* more details.
*
* You should have received a copy of the GNU General Public License along
* with this program. If not, see <http://www.gnu.org/licenses/>.
*
* Author: Sandro Cantagallo
* Date: 07/02/2011
* Version: 2.0
*
* Installation: This is a php script to use in shell (command line). Simple write right data under "configuration var" and than run the script.
* You must put all sql patch that you want to lanch inside e directory whit the same name of the database.
*
* For example if you want to run some patch in a db called trinity_word you must put this patch inside a directory called "trinity_world" that are inside the path directory write in this var: $path_patch_sql
*/
/*
* Configuration Var
* $path_patch_sql = Full path of directory where you will put inside the subdirectory whit sql patch
* $path_mysql = You can leave it empty if ./mysql command work (but if you use program like xammp on mac it can be: /Applications/xampp/xamppfiles/bin/./
* $user_mysql = mysql username
* $pass_mysql = password of mysql username
*/
//$path_patch_sql = "/Applications/xampp/htdocs/cmswow/crontab/sql";
$path_patch_sql = "/sql"; //directory where you put directory with sql patch to run
//$path_mysql = "/Applications/xampp/xamppfiles/bin/./"; // leave empty if ./mysql command work on your machine
$path_mysql = "";
$user_mysql = "";
$pass_mysql = "";
/**
* funzione che legge all'interno di una directory le sotto directory e i file presenti. Utilizza le sotto directory per creare degli array bidimensionali e inserisce
* come valore dell'array soltanto i file con estenzione sql
*
* @param unknown_type $path
* @return unknown
*/
function read_directory($path) {
if(!isset($path)){$path="sql/";}//se non esiste la variabile la associo alla directory principale
$f=opendir($path);//apro la directory
while(false!==($g=readdir($f))){//ciclo while che legge dentro la directory fino a quando non trova un valore false
if($g!="." && $g!=".." && $g != ".svn") {//elimino il punto ed i doppi punti e le cartelle .svn nascoste
if(is_dir($path."/".$g)){//creo un array con le directory trovate
$newpath = $path."/".$g;
$array_dir[$g] = array();
$array_dir[$g] = read_directory($newpath);
}
if(is_file($path."/".$g)){//creo un array con i file trovati
$extension = explode(".", $path."/".$g);
if ($extension[1] == 'sql') {
$array_dir[]=$g;;
//$numg++;//numero di file trovati
}
}
}
}
closedir($f);
return $array_dir;
}
/**
* Questa funzione ordina un array di patch da lanciare in maniera crescente sul database mysql
*
* @param unknown_type $ay_sql
* @return unknown
*/
function sort_sql_patchs($ay_sql) {
$ay_sql_to_run = array();
// creao un array ordinato di tutte le trinity version presenti nelle patch
foreach ($ay_sql as $key=>$val) {
if (!is_numeric($key)) {
foreach ($val as $file_sql) {
$numero = substr($file_sql, 0, 5);
if (is_numeric($numero)) {
$ay_sql_to_run[$key]['2010_12_31'][$numero] = '';
} else {
$numero = substr($file_sql, 11, 2);
//echo $numero."\n";
$data = substr($file_sql, 0, 10);
$ay_sql_to_run[$key][$data][$numero] = '';
}
ksort($ay_sql_to_run[$key][$data]);
}
// ordino i dati inseriti per versione di trinitycore in maniera crescente
ksort($ay_sql_to_run[$key]);
}
}
// ora che l'array di ritorno e ordinato per trinity version lo popolo con i file sql da lanciare
foreach ($ay_sql as $key=>$val) {
if (!is_numeric($key)) {
foreach ($val as $file_sql) {
$numero = substr($file_sql, 0, 5);
if (is_numeric($numero)) {
$ay_sql_to_run[$key]['2010_12_31'][$numero][] = $file_sql;
} else {
$numero = substr($file_sql, 11, 2);
$data = substr($file_sql, 0, 10);
$ay_sql_to_run[$key][$data][$numero][] = $file_sql;
}
}
}
}
return $ay_sql_to_run;
}
/**
* Questa funzione lancia le patch sql salvate all'interno dell'array passato per argomento all'interno del db avente nome uguale alla key principale dell'array
*
* @param unknown_type $path la directory dove sono salvate le patch sql
* @param unknown_type $ay_sql l'array dove sono salvate tutte le patch sql dal lanciare
* @param unknown_type $path_mysql la directory dove presente il programma mysql
* @param unknown_type $user_mysql il nome utente che ha accesso al database
* @param unknown_type $pass_mysql la password per accedere al database mysqkl
*/
function launch_sql_from_array($path, $ay_sql, $path_mysql, $user_mysql, $pass_mysql) {
//controllo se un array la var passata come argomento
if (is_array($ay_sql)) {
//listo il contenuto dell'array
foreach ($ay_sql as $key=>$val) {
if (!is_numeric($key)) {
echo " \n";
echo "----------------------------------------------------------------------------- \n ";
echo "- Now Work On: ".$key." DB Please Wait \n";
echo "----------------------------------------------------------------------------- \n ";
echo " \n ";
//foreach ($val as $patch) {
//echo "----------------------------------------------------------------------------- \n ";
//echo "- Update DB ".$key." with ".$patch." Sql Patch \n ";
//exec($path_mysql."mysql --user=".$user_mysql." --password=".$pass_mysql." ".$key." < ".$path."/".$key."/".$patch);
//}
foreach ($val as $patch_data=>$patch) {
echo "----------------------------------------------------------------------------- \n ";
//echo "<br />";
//echo "- Update DB ".$key." at ".$db_update." TrinityCore Version \n ";
echo "- Update DB ".$key." with patch make in this data ".$patch_data." \n ";
//echo "<br />";
echo "----------------------------------------------------------------------------- \n ";
//echo "<br />";
foreach ($patch as $db_update=>$file_sql) {
echo "----------------------------------------------------------------------------- \n ";
echo "- Serial Number Of This Date: ".$db_update." \n ";
echo "----------------------------------------------------------------------------- \n ";
foreach ($file_sql as $file){
echo "- Run: ".$file." On Db:".$key." \n ";
}
//echo "<br />";
exec($path_mysql."mysql --user=".$user_mysql." --password=".$pass_mysql." ".$key." < ".$path."/".$key."/".$file);
}
}
}
}
}
}
// creo array con i file da lanciare nei rispettivi database
$ay_sql = read_directory($path_patch_sql);
$ay_sql = sort_sql_patchs($ay_sql);
// ordino l'array in ordine crescente di version trinitycore in maniera tale da lanciare le patch nella giusta sequenza
//$ay_sql = sort_sql_patchs($ay_sql);
// lancio le patch su mysql
launch_sql_from_array($path_patch_sql, $ay_sql, $path_mysql, $user_mysql, $pass_mysql);
?>
this is the “feedback” that program write:
- Now Work On: myth_world_old DB Please Wait
- Update DB myth_world_old with patch make in this data 2011_02_03
- Serial Number Of This Date: 00
- Run: 2011_02_03_00_world_instance_encounters.sql On Db:myth_world_old
- Serial Number Of This Date: 01
- Run: 2011_02_03_01_world_command.sql On Db:myth_world_old
- Serial Number Of This Date: 02
- Run: 2011_02_03_02_world_lfg_dungeon_encounters.sql On Db:myth_world_old
- Serial Number Of This Date: 03
- Run: 2011_02_03_03_world_spell_dbc.sql On Db:myth_world_old
- Update DB myth_world_old with patch make in this data 2011_02_04
- Serial Number Of This Date: 00
-
Run: 2011_02_04_00_world_spell_script_names.sql On Db:myth_world_old
-
Run: 2011_02_04_00_world_quest_template.sql On Db:myth_world_old
-
Run: 2011_02_04_00_world_game_event.sql On Db:myth_world_old
-
Run: 2011_02_04_00_world_item_loot_template.sql On Db:myth_world_old
-
Run: 2011_02_04_00_world_sai.sql On Db:myth_world_old
-
Run: 2011_02_04_00_world_instance_pit_of_saron.sql On Db:myth_world_old
-
Run: 2011_02_04_00_world_creature_template.sql On Db:myth_world_old
-
Run: 2011_02_04_00_world_creature_631.sql On Db:myth_world_old
-
Run: 2011_02_04_00_world_creature_questrelation.sql On Db:myth_world_old
-
Run: 2011_02_04_00_world_battleground_isle_of_conquest.sql On Db:myth_world_old
-
Run: 2011_02_04_00_world_conditions.sql On Db:myth_world_old
-
Run: 2011_02_04_00_world_mail_loot_template.sql On Db:myth_world_old
-
Run: 2011_02_04_00_world_spell_target_position.sql On Db:myth_world_old
-
Run: 2011_02_04_00_world_gameobject_questrelation.sql On Db:myth_world_old
- Serial Number Of This Date: 01
-
Run: 2011_02_04_01_world_game_event.sql On Db:myth_world_old
-
Run: 2011_02_04_01_world_sai.sql On Db:myth_world_old
-
Run: 2011_02_04_01_world_quest_template.sql On Db:myth_world_old
[…]
you can use this script for exeple in a crontab for have every day your db update with last revision.
In this way you also can easy find “error”.