File: /home/mostafedeg/public_html/erp/controllers/syncstructureBulk.php
<?php
error_reporting(E_ERROR);
//include all DAO files
require_once('../models/sql/Connection.class.php');
require_once('../models/sql/ConnectionFactory.class.php');
require_once('../models/sql/ConnectionProperty.class.php');
require_once('../models/sql/QueryExecutor.class.php');
require_once('../models/sql/Transaction.class.php');
require_once('../models/sql/SqlQuery.class.php');
require_once('../models/core/ArrayList.class.php');
require_once('../models/dao/DAOFactory.class.php');
//Programsetting
require_once('../models/dao/ProgramsettingsDAO.class.php');
require_once('../models/dto/Programsetting.class.php');
require_once('../models/mysql/ProgramsettingsMySqlDAO.class.php');
require_once('../models/mysql/ext/ProgramsettingsMySqlExtDAO.class.php');
/////////////////////product////////////////////////
require_once('../models/dao/ProductDAO.class.php');
require_once('../models/dto/Product.class.php');
require_once('../models/mysql/ProductMySqlDAO.class.php');
require_once('../models/mysql/ext/ProductMySqlExtDAO.class.php');
//Programsetting
$Programsetting = new Programsetting();
$ProgramsettingDAO = new ProgramsettingsMySqlDAO();
$ProgramsettingEX = new ProgramsettingsMySqlExtDAO();
//
$ProductEX = new ProductMySqlExtDAO();
$notExcuted = "";
$oneCommand = "";
$noOfsuccess = 0;
$noOfDuplicateColumnName = 0;
$noOfExcutedWithError = 0;
$noOfUnknownColumn = 0;
$laterSql = "";
$allCommands = "";
#################################################
//check and use the condition that suite this action
$dbhost = ConnectionProperty::getHost();
$dbuser = ConnectionProperty::getUser();
$dbpassword = ConnectionProperty::getPassword();
$dbname = ConnectionProperty::getDatabase();
if ($_SESSION['dbname'] != "" && isset($_SESSION['dbname'])) {
$dbname = $_SESSION['dbname'];
}
$handle = fopen("../__db/syncStructure.sql", "r");
echo "<span style='color:blue'>note : colums with type that has definition like decimal(15,2) NOT handeled only decimal handled .call developer</span><br/>";
echo "<span style='color:yellowgreen'> read file done start executing... </span>";
if ($handle) {
$time_start = microtime(true);
while (($line = fgets($handle)) !== false) {
// process the line read.
$firstTowChars = substr($line, 0, 2);
if ($firstTowChars == "/*" || $firstTowChars == "##" || $firstTowChars == "//" || $firstTowChars == "--") {
} else {
$line = trim($line);
if (!empty($line)) {
$lastChars = substr($line, -1);
##change , in comments it is used to separate colums
$line = explode("COMMENT", $line);
$explodedLine_Count = count($line) - 1;
if ($explodedLine_Count > 0) {
$lastChars_2 = substr($line[$explodedLine_Count], -1);
$line[$explodedLine_Count] = str_replace(",", "-", $line[$explodedLine_Count]);
if ($lastChars_2 == ",") {
$line[$explodedLine_Count] = rtrim($line[$explodedLine_Count], "-");
$line[$explodedLine_Count].=",";
}
}
$line = implode("COMMENT", $line);
##
$line = replace_first_after_word(",", "-", "decimal", $line);
//if line is key replace `,` with `-` as we use , to know colums later in prepareSync()
if (strpos($line, 'PRIMARY KEY') === 0 || strpos($line, 'UNIQUE KEY') === 0 ||
strpos($line, 'FOREIGN KEY') === 0 || strpos($line, 'KEY') === 0) {
$line = str_replace("`,`", "`-`", $line);
}
$oneCommand.=$line;
//echo $line . "<br/>";
if ($lastChars == ";") {
##--new-----------------
prepareSync($oneCommand);
$oneCommand = "";
}
}
}
}
if (!empty($allCommands)) {
$ProductEX->updateBulk($allCommands);
// $command = "mysql --user=$db_user --password=$db_password --host=$host $dbname < $mysqlImportFilename ";
// $mysqlImportFilename = "..\db_backups\\" . $_SESSION['dbname'] . "_syncy_temp.sql";
// $myfile = fopen($mysqlImportFilename, "a+") or die("Unable to open file!");
// fwrite($myfile, $allCommands);
// fclose($myfile);
}
##reset auto increament
// $sql = explode(";;", $laterSql);
// $lastItem = count($sql) - 1;
// unset($sql[$lastItem]); //it is always empty
// foreach ($sql as $autoIncrementReseterSql) {
// runCommand($dbhost, $dbuser, $dbpassword, $dbname, $autoIncrementReseterSql);
// }
if (!empty($laterSql)) {
$ProductEX->updateBulk($laterSql);
}
//hide syncstructure icon from header
$programsettingsdata = $ProgramsettingDAO->load(1);
$programsettingsdata->syncstructure = 1;
//oldsync hash next line
$ProgramsettingDAO->update($programsettingsdata);
//draw header again to feel the syncstructure show hide
$fh = fopen('../temp__cashedheader/cashedheader_' . $_SESSION['dbname'] . '_' . $_SESSION['userid'] . '.html', 'w');
fclose($fh);
$time_end = microtime(true);
echo 'execution time in seconds: ' . round((microtime(true) - $time_start), 2) . ' seconds';
##end
fclose($handle);
echo "<span style='color:green'> DONE. </span><br/>";
print_r("<pre>");
echo "<div style='border:1px solid green;margin-right:60%'><span style='color:green'> no of queries excuted successfully : " . $noOfsuccess . " </span><br/>";
echo "<span style='color:#f57c00'> no of queries excuted with Duplicate Column Name error : " . $noOfDuplicateColumnName . " </span><br/>";
echo "<span style='color:#f57c00'> no of queries excuted with Unknown Column error : " . $noOfUnknownColumn . " </span><br/>";
echo "<span style='color:red'> no of queries gave errors : " . $noOfExcutedWithError . " </span><br/></div>";
print_r("</pre>");
} else {
// error opening the file.
}
###############################################################
//new way to save all sql to run it all at once
function runCommand($dbhost, $dbuser, $dbpassword, $dbname, $oneCommand) {
global $allCommands;
$allCommands.= $oneCommand . ";";
}
/* function runCommand_old_queryByQuery($dbhost, $dbuser, $dbpassword, $dbname, $oneCommand) {
global $noOfsuccess;
global $noOfDuplicateColumnName;
global $noOfUnknownColumn;
global $noOfExcutedWithError;
// Create connection
$conn = new mysqli($dbhost, $dbuser, $dbpassword, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = $oneCommand;
if ($conn->query($sql) === TRUE) {
//echo "success";
$noOfsuccess++;
} else {
//echo "Error: " . $conn->error . "<br/>";
$errorMessage = $conn->error;
if (stripos($errorMessage, "Duplicate column name") !== false) {
//now it is dublicate column doenot matter
$noOfDuplicateColumnName++;
} elseif (stripos($errorMessage, "Unknown column") !== false) {
$noOfUnknownColumn++;
} elseif (stripos($errorMessage, "Multiple primary key defined") !== false) {
} else {
$noOfExcutedWithError++;
//show error
print_r("<pre>");
print_r($sql . "<br/><span style='color:red'>Error: " . $errorMessage . "</sapn><hr>");
print_r("</pre>");
}
}
} */
function prepareSync($oneCommand) {
global $dbhost;
global $dbuser;
global $dbpassword;
global $dbname;
global $laterSql;
if (strpos($oneCommand, 'CREATE TABLE IF NOT EXISTS') !== false) {
$firstExplode = explode("CREATE TABLE IF NOT EXISTS", $oneCommand);
$secondExplode = explode("(", $firstExplode[1]);
$tablename = trim(str_replace("`", "", $secondExplode[0]));
$sqlCheckTable = 'SELECT * FROM information_schema.`TABLES` WHERE `TABLE_SCHEMA` = "' . $dbname . '" and`TABLE_NAME` = "' . $tablename . '"';
$result = runCommand2($dbhost, $dbuser, $dbpassword, "information_schema", $sqlCheckTable);
if ($result->num_rows == "1") {
//this condition is to skip creat table that come with "view"
if (strpos($tablename, 'view') === false) {
//tableexist
##remove first part of sql like => CREATE TABLE IF NOT EXISTS `xxx` (
$firstExplode = explode('(', $oneCommand); // split all parts
unset($firstExplode[0]);
$firstExplode = implode($firstExplode, '(');
##remove second part of sql like => ) ENGINE=InnoDB AUTO_INCREMENT=280 DEFAULT CHARSET=latin1;
$secondExplode = explode(')', $firstExplode); // split all parts
$secondExplodeCount = count($secondExplode) - 1;
unset($secondExplode[$secondExplodeCount]);
$secondExplode = implode($secondExplode, ')');
##get colums
$colums = explode(",", $secondExplode);
//print_r($secondExplode);
foreach ($colums as $oneColumn) {
$oneColumn = replace_first_after_word("-", ",", "decimal", $oneColumn);
$colData = explode("`", $oneColumn);
$columName = $colData[1];
$columProp = $colData[2];
//if key do no thing it do exist in first table insert
if (strpos($oneColumn, 'PRIMARY KEY') === 0) {
$sqlCheckColumn = 'SELECT COLUMN_NAME FROM information_schema.STATISTICS WHERE `TABLE_SCHEMA` = "' . $dbname . '" and `TABLE_NAME` = "' . $tablename . '" and INDEX_NAME = "PRIMARY";';
$result = runCommand3($dbhost, $dbuser, $dbpassword, "information_schema", $sqlCheckColumn);
// if (!empty($result)) {
// //there is primary key remove it first
// runCommand($dbhost, $dbuser, $dbpassword, $dbname, "ALTER TABLE $tablename MODIFY $columName INT NOT NULL;");
// runCommand($dbhost, $dbuser, $dbpassword, $dbname, "ALTER TABLE $tablename DROP PRIMARY KEY;");
// }
if (empty($result)) {
//there is no pk add primary key
//run command is okey but not working give db some times and run it in later sql
//runCommand($dbhost, $dbuser, $dbpassword, $dbname, "ALTER TABLE $tablename MODIFY $columName INT NOT NULL PRIMARY KEY AUTO_INCREMENT;");
$laterSql .= "ALTER TABLE $tablename MODIFY $columName INT NOT NULL PRIMARY KEY AUTO_INCREMENT;;";
}
} else if (strpos($oneColumn, 'UNIQUE KEY') === 0) {
$sqlCheckColumn = 'SELECT count(*) FROM information_schema.STATISTICS WHERE `TABLE_SCHEMA` = "' . $dbname . '" and `TABLE_NAME` = "' . $tablename . '" and INDEX_NAME = "' . $columName . '";';
$result = runCommand3($dbhost, $dbuser, $dbpassword, "information_schema", $sqlCheckColumn);
// if (!empty($result)) {
// //there is unique key with this name remove it first
// runCommand($dbhost, $dbuser, $dbpassword, $dbname, "ALTER TABLE $tablename DROP INDEX $columName;");
// }
if (empty($result)) {
$oneColumn = str_replace('`-`', '`,`', $oneColumn);
//add uniqu key
// run command is okey but not working give db some times and run it in later sql
//runCommand($dbhost, $dbuser, $dbpassword, $dbname, "ALTER TABLE $tablename ADD $oneColumn;");
$laterSql .= "ALTER TABLE $tablename ADD $oneColumn;;";
}
} else if (strpos($oneColumn, 'FOREIGN KEY') === 0) {
//later
} else if (strpos($oneColumn, 'KEY') === 0) {
$sqlCheckColumn = 'SELECT count(*) FROM information_schema.STATISTICS WHERE `TABLE_SCHEMA` = "' . $dbname . '" and `TABLE_NAME` = "' . $tablename . '" and INDEX_NAME = "' . $columName . '";';
$result = runCommand3($dbhost, $dbuser, $dbpassword, "information_schema", $sqlCheckColumn);
if (empty($result)) {
$oneColumn = str_replace('`-`', '`,`', $oneColumn);
$oneColumn = str_replace('INDEX', 'KEY', $oneColumn);
//add key
// run command is okey but not working give db some times and run it in later sql
//runCommand($dbhost, $dbuser, $dbpassword, $dbname, "ALTER TABLE $tablename ADD $oneColumn;");
$laterSql .= "ALTER TABLE $tablename ADD $oneColumn ;;";
}
} else {
if (!empty($columName)) {
$sqlCheckColumn = 'SELECT * FROM information_schema.`COLUMNS` WHERE `TABLE_SCHEMA` = "' . $dbname . '" and `TABLE_NAME` = "' . $tablename . '" and `COLUMN_NAME`="' . $columName . '"';
$result = runCommand2($dbhost, $dbuser, $dbpassword, "information_schema", $sqlCheckColumn);
if ($result->num_rows == "1") {
//column exist
$oneColumn = str_replace('AUTO_INCREMENT', '', $oneColumn);
$sqlCol = ' ALTER TABLE ' . $tablename . ' CHANGE `' . $columName . '` ' . $oneColumn . '';
} else {
//new column
$sqlCol = 'ALTER TABLE ' . $tablename . ' ADD ' . $oneColumn . '';
if ($columName == "clientStoreIds") {
$laterSql .= 'UPDATE `client` SET `clientStoreIds` = "-10";;';
}
}
runCommand($dbhost, $dbuser, $dbpassword, $dbname, $sqlCol);
}
}
}
}
} else {
//new table
$oneCommand = replace_first_after_word("-", ",", "decimal", $oneCommand);
runCommand($dbhost, $dbuser, $dbpassword, $dbname, $oneCommand);
$laterSql .= 'ALTER TABLE ' . $tablename . ' auto_increment = 1;;';
//runCommand($dbhost, $dbuser, $dbpassword, $dbname, $resetAutoIncrement);
}
} elseif (strpos($oneCommand, 'SQL SECURITY DEFINER VIEW') !== false) {
//this is view
$viewSelectAs = explode("SQL SECURITY DEFINER VIEW", $oneCommand);
$oneCommand = "CREATE OR REPLACE ALGORITHM = MERGE VIEW" . $viewSelectAs[1];
//runCommand($dbhost, $dbuser, $dbpassword, $dbname, $dropViewSQL);
runCommand($dbhost, $dbuser, $dbpassword, $dbname, $oneCommand);
} else {
if (strpos($oneCommand, 'CREATE VIEW') === 0) {
$oneCommand = str_replace('CREATE VIEW', 'CREATE OR REPLACE ALGORITHM = MERGE VIEW', $oneCommand);
}
runCommand($dbhost, $dbuser, $dbpassword, $dbname, $oneCommand);
}
}
//get statistics
function runCommand2($dbhost, $dbuser, $dbpassword, $dbname, $oneCommand) {
// Create connection
$conn = new mysqli($dbhost, $dbuser, $dbpassword, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = $oneCommand;
return $conn->query($sql);
}
//query single result
function runCommand3($dbhost, $dbuser, $dbpassword, $dbname, $oneCommand) {
// Create connection
$conn = new mysqli($dbhost, $dbuser, $dbpassword, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = $oneCommand;
$result = $conn->query($sql);
$row = mysqli_fetch_array($result);
return $row[0];
}
function replace_first_after_word($search, $replace, $wordToSearchAfter, $str) {
##change , in decimal it is used to separate colums
$line = $str;
if (strpos($str, $wordToSearchAfter) !== false) {
$loops = array_count_values(str_word_count($str, 1)); //substr_count($str, $search);
$loops = $loops[$wordToSearchAfter];
if ($loops > 0) {
$line = explode($wordToSearchAfter, $str);
for ($i = 1; $i <= $loops; $i++) {
$myPart = str_split($line[$i]);
for ($j = 0; $j < count($myPart); $j++) {
if ($myPart[$j] == $search) {
$myPart[$j] = $replace;
$myPart = implode("", $myPart);
break;
}
}
$line[$i] = $myPart;
}
$line = implode($wordToSearchAfter, $line);
}
}
return $line;
}
?>