File: /home/mostafedeg/public_html/erp/controllers/syncstructure.php
<?php
/* ## while using output_buffering=off in php.ini it wil show reult line by line
ob_start();
echo 'Begin ...<br />';
for ($i = 0; $i < 10; $i++) {
echo $i . '<br />';
flush();
ob_flush();
sleep(1);
}
echo 'End ...<br />';
die(); */
error_reporting(E_ERROR);
//ini_set('display_errors', 1);
//ini_set('display_startup_errors', 1);
//error_reporting(E_ALL);
//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');
//Programsetting
$Programsetting = new Programsetting();
$ProgramsettingDAO = new ProgramsettingsMySqlDAO();
$ProgramsettingEX = new ProgramsettingsMySqlExtDAO();
$nochangeColumns = array("productionrateproduct" => "quantity", "taskorder" => "num", "taskorderproducts" => "quantity"
, "productionexecution" => "num", "productionexecutionoutput" => "num", "productionexecutionproduct" => "quantity,actualquantity", "productionexecutionwaste" => "num"
, "storemovement" => "transferproductamount", "storedetail" => "productquantity", "sizecolorstoredetail" => "quantity", "storereport" => "productquantity,productbefore,productafter"
, "restaurantrawdestruction" => "quantity");
$notExcuted = "";
$oneCommand = "";
$noOfsuccess = 0;
$noOfDuplicateColumnName = 0;
$noOfExcutedWithError = 0;
$noOfUnknownColumn = 0;
$laterSql = "";
#################################################
//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); //for keys
$line = $line . "++"; //for keys
}
$oneCommand .= $line;
//echo $line . "<br/>";
if ($lastChars == ";") {
##--new-----------------
prepareSync($oneCommand);
$oneCommand = "";
}
}
}
}
##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);
}
//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) - $time_start);
echo 'execution time is: ' . round($time_end / 60, 2) . ' Mins';
##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.
}
###############################################################
function runCommand($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) {
} elseif (stripos($errorMessage, "check that column/key exists") !== false) {
//try to drop index that is not there its ok
} 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;
global $nochangeColumns;
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 . '"';
$allResult = runCommand2($dbhost, $dbuser, $dbpassword, "information_schema", $sqlCheckTable);
$result = $allResult[0];
$rowsDataOfSelect = $allResult[1];
if ($result->num_rows == "1") {
//this condition is to skip creat table that come with "view"
//if (strpos($tablename, 'view') === false) {
if ($rowsDataOfSelect[0]["TABLE_TYPE"] === "BASE TABLE") {
//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
$secondExplode = str_replace("++", ",", $secondExplode); //for keys
$colums = explode(",", $secondExplode);
foreach ($colums as $oneColumn) {
$oneColumn = replace_first_after_word("-", ",", "decimal", $oneColumn);
$colData = explode("`", $oneColumn);
$columName = $colData[1];
$columProp = $colData[2];
if (!empty($columName)) {
//if key do no thing it do exist in first table insert
if (strpos($oneColumn, 'PRIMARY KEY') === 0) {
//later
} else if (strpos($oneColumn, 'UNIQUE KEY') === 0) {
//later
$oneColumn = str_replace("**", ",", $oneColumn); //for keys
} else if (strpos($oneColumn, 'FOREIGN KEY') === 0) {
//later
} else if (strpos($oneColumn, 'KEY') === 0) {
$oneColumn = str_replace("**", ",", $oneColumn); //for keys
$oneColumn = str_replace("KEY", "", $oneColumn);
$oneColumn = str_replace(")", "", $oneColumn);
$oneColumn = explode("(", $oneColumn);
$sqlCol = ' ALTER TABLE ' . $tablename . ' DROP INDEX ' . trim($oneColumn[0]) . ';';
runCommand($dbhost, $dbuser, $dbpassword, $dbname, $sqlCol);
$sqlCol = ' ALTER TABLE ' . $tablename . ' ADD INDEX ' . trim($oneColumn[0]) . ' (' . trim($oneColumn[1]) . ');'; //name of key is optional but preferred
runCommand($dbhost, $dbuser, $dbpassword, $dbname, $sqlCol);
} else {
if (isset($nochangeColumns[$tablename]) && in_array($columName, explode(',', $nochangeColumns[$tablename]))) {
//this id no change column do nothing
} else {
$sqlCheckColumn = 'SELECT * FROM information_schema.`COLUMNS` WHERE `TABLE_SCHEMA` = "' . $dbname . '" and `TABLE_NAME` = "' . $tablename . '" and `COLUMN_NAME`="' . $columName . '"';
$allResult = runCommand2($dbhost, $dbuser, $dbpassword, "information_schema", $sqlCheckColumn);
$result = $allResult[0];
$rowsDataOfSelect = $allResult[1];
if ($result->num_rows == "1") {
//column exist
$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 = str_replace("++", "", $oneCommand); //for keys
$oneCommand = replace_first_after_word("-", ",", "decimal", $oneCommand);
$oneCommand = str_replace("`**`", "`,`", $oneCommand); //for keys
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);
}
}
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;
$result = $conn->query($sql);
$allRows = array();
while ($row = mysqli_fetch_assoc($result)) {//mysqli_fetch_field
array_push($allRows, $row);
}
return array($result, $allRows);
}
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;
}
?>