Search the Community

Hi everybody. This script is not only for people who want to contribute. The company I work for recently embarked on writing a php script to convert from mssql to mysql. I want to share it because NO ONE online has ever posted such a script. So currently the script is very basic , and i will update it as much as possible. This script does the following: Convert Datatypes Checks and fixes IF statements Converts parameters Changes getdate() to NOW() Changes select into Changes ISNULL Changes Top n rows to LIMIT n if any one can improve or add something please feel free to do so. This code is intended as freeware <?php ini_set('max_execution_time', 120); // 120 2 minutes // 180 3 minutes // 240 4 minutes // 300 5 minutes function convert_variable($data_size, $data_type) { if (strtolower($data_type) == 'identity') { $data_type == 'auto_incriment'; } if (strtolower($data_type) == 'smalldatetime') { $data_type == 'datetime'; } if (strtolower($data_type) == 'money') { $data_type == 'decimal(19,4)'; } if (strtolower($data_type) == 'small money') { $data_type == 'decimal(10,4)'; } if (strtolower($data_type) == 'uniqueidentifier') { $data_type == 'binary(16)'; } if (strtolower($data_type) == 'sysname') { $data_type == 'char(256)'; } if ($data_size !== '0') { if (strtolower($data_type) == "nvarchar") { if (intval($data_size) >= 255 || strtolower($data_size) == 'max') { return "text"; } elseif (intval($data_size) <= 254) { return "varchar($data_size)"; } } if (strtolower($data_type) == "varchar") { if (intval($data_size) >= 255 || strtolower($data_size) == 'max') { return "text"; } } } else { return false; } } function get_size_from_variable($var) { $open_bracket_pos = strpos($var, "("); $close_bracket_pos = strpos($var, ")"); // if it wasnt found then no size was spesified if ($open_bracket_pos == false || $close_bracket_pos == false ) { return array('0', $var); } else { // return the size $size = substr($var, $open_bracket_pos + 1, $close_bracket_pos - $open_bracket_pos - 1); $data_type = substr($var, 0, $open_bracket_pos); return array($size, $data_type); } } function contains($haystack, $needle, $matchcase = false) { if (!$matchcase) { if (stripos($haystack, $needle) !== false) { return true; } else { return false; } } if ($matchcase) { if (strpos($haystack, $needle) !== false) { return true; } else { return false; } } } $query = array(); $parameter_default_values = array(); if (isset($_GET['output'])) { $output = $_GET['output']; } else { $output = "none"; } if (isset($_GET['mode'])) { $mode = $_GET['mode']; } else { $mode = "debug"; } if (isset($_GET['techinfo'])) { $techinfo = $_GET['techinfo']; } else { $techinfo = "show"; } /* $output = "none"; $mode = "debug"; $techinfo = "show"; */ echo " /*<br> MODE = $mode <br> OUTPUT = $output <br> SHOW TECHNICAL INFORMATION = $techinfo <br> */ <br> <br> <br> <br> <br>"; $blank = ""; $instring = false; $needsbegin = false; // count of all if statements found $ifcount = 0; // count of all begins after an if has been found $ifsbegin = 0; $file = fopen('files/sp.sql', "r"); while (!feof($file)) { $line = fgets($file); if ($output == 'html') { array_push($query, trim($line) . "<br>"); } else { array_push($query, trim($line)); } } fclose($file); $codelines = count($query) - 1; $currentline = 0; if ($mode == 'debug') { echo "DELIMITER /// <br>"; } else { echo "DELIMITER /// \r\n"; } while ($currentline <= $codelines) { // get query line // SHOULD ALWAYS BE FIRST LINE IN WHILE $codeline = $query[$currentline]; // do prelim weird character replacements $arrsearch = array( "1" => "''", "2" => "=", "3" => " ", ); $arrreplacement = array( "1" => "┴", "2" => " = ", "3" => " ", ); $codeline = str_ireplace($arrsearch, $arrreplacement, $codeline); if ($currentline >= 1) { $prevcodeline = trim($query[$currentline - 1]); } else { $prevcodeline = ''; } if ($currentline < $codelines) { $nextcodeline = trim($query[$currentline + 1]); } else { $nextcodeline = ''; } //$codeline = str_ireplace("''", "┴", $codeline); $codeline = str_ireplace(" ", " ", $codeline); // break down line $characters = str_split($codeline); $words = explode(" ", $codeline); $prevlinecharacters = str_split($prevcodeline); $prevlinewords = explode(" ", $prevcodeline); $nextlinecharacters = str_split($nextcodeline); $nextlinewords = explode(" ", $nextcodeline); // sized of break down $lastcharacter = count($characters) - 1; $lastword = count($words) - 1; $prevlastcharacter = count($prevlinecharacters); $prevlastword = count($prevlinewords) - 1; $nextlastcharacter = count($nextlinecharacters); $nextlastword = count($nextlinewords) - 1; /******************************************************************* * GET COMMENT *******************************************************************/ // checks if the line is a comment. If the whole line is commented out from the begining the line should be skipped if (strlen($codeline) >= 2) { if ($characters[0] . $characters[1] == '--') { $iscomment = true; if (isset($characters[2])) { if ($characters[2] !== " ") { $characters[1] .= " "; $words[0] = str_ireplace('--', '-- ', $words[0]); } } } else { $iscomment = false; } } /******************************************************************* * REPLACE NOCOUNT WITH COMMENT *******************************************************************/ if (stripos($codeline, "SET NOCOUNT ON") !== false) { //$codeline = str_ireplace("SET NOCOUNT ON","-- SET NOCOUNT ON",$codeline); $words[0] = "-- " . $words[0]; $iscomment = true; } // if it is a comment do nothing if (!$iscomment) { // add ')' to end of store proc declarations if (strtolower($codeline) == 'as begin' || (strtolower($codeline) == 'as' && strtolower($query[$currentline + 1]) == 'begin')) { $words[$lastword] = ') '; // . $words[$lastword]; $startparameters = false; $needsbegin = true; } /******************************************************************* * CHANGE ALTER TO CREATE *******************************************************************/ // checks for th words ALTER PROCEDURE or ALTER FUNCTION if (strtolower($words[0]) == 'alter' && (strtolower($words[1]) == 'procedure' || strtolower($words[1]) == 'function') ) { $words[0] = 'CREATE'; $words[$lastword] .= '('; $startparameters = true; } /******************************************************************* * DECLARE OF VARIABLES *******************************************************************/ // check if variable is being declared if (strtolower($words[0]) == 'declare') { // word 3 should be the data type // THE FIRST PART IS FOR IN SP VARIABLE DECLARAIONS $data_type = $words[2]; $data_size = get_size_from_variable($data_type); $in_var_declare = true; // if the data size is like VARCHAR(MAX) then the function will return datatype and size. else size will be 0 $size_return = get_size_from_variable($data_type); $data_size = $size_return[0]; $data_type = $size_return[1]; $converted_var = convert_variable($data_size, $data_type); if ($converted_var !== false) { $words[2] = convert_variable($data_size, $data_type); } if ($converted_var == false && $mode == 'debug') { //echo "<b>/* VARIABLE IS NOT CONVERTED */ </b>"; } // this statement will be true if the first character is a comma OR // declare has been called or its the start of the parameters } elseif ( ( // if first or last character is a comma ($characters[0] == ',' || $characters[$lastcharacter] == ',') || // or if the first and last character from this line isnt a comma but the last one IS // then return true ( ($characters[0] !== ',' || $characters[$lastcharacter] !== ',') && (substr($prevcodeline, strlen($prevcodeline) - 1, 1) == ',') ) ) && ($in_var_declare || $startparameters) ) { // word 2 should now be the data type // THE SECOND PART IS FOR IN SP PARAMETER DECLARATIONS $data_type = $words[1]; // if the data size is like VARCHAR(MAX) then the function will return datatype and size. else size will be 0 $size_return = get_size_from_variable($data_type); $data_size = $size_return[0]; $data_type = $size_return[1]; $converted_var = convert_variable($data_size, $data_type); if ($converted_var !== false) { $words[1] = convert_variable($data_size, $data_type); } } else { // its no longer declaring variables. // there isn't a declare or a comma after the declaration. $in_var_declare = false; } // variable declaration /******************************************************************* * CHECK IN STRING *******************************************************************/ if (stripos($codeline, "'") !== false && $instring == false ) { // check if string doenst also end in this line $charcount = stripos($codeline, "'"); // its in a string $instring = true; if ($mode == "debug" && $techinfo == "show") { echo "<b>instring set to true;</b>"; } // if another ' is found in the same line it will be unset from being in a string while ($charcount <= $lastcharacter && $charcount !== $lastcharacter) { if ($characters[$charcount] == "'") { $instring = false; if ($mode == "debug" && $techinfo == "show") { echo "<b>instring set to false;</b>"; } } $charcount++; } } elseif ($instring == true && stripos($codeline, "'")) { // check if string doenst also end in this line $charcount = stripos($codeline, "'"); // its in a string $instring = false; // if another ' is found in the same line it will be unset from being in a string. while ($charcount <= $lastcharacter) { if ($characters[$charcount] == "'") { $instring = true; } $charcount++; } } /******************************************************************* * ADD SEMI COLON ( *******************************************************************/ // LEAVE THIS DECLARATION HERE. LAST WORD IS REPLACED BY THE DATATYPE. // Add semi colon after declaration if (strtolower($nextlinewords[0]) == 'declare' && strlen($codeline) !== 0 && ($characters[0] = ',' || $prevlinecharacters[$prevlastcharacters] == ',') && $instring == false ) { $words[$lastword] .= ';'; } // Add semi colon after declaration if (strtolower($nextlinewords[0]) !== 'declare' && strtolower($prevlinewords[0]) == 'declare' && strlen($codeline) !== 0 && $instring == false ) { $words[$lastword] .= ';'; } /******************************************************************* * GET ALL IF's IN STATEMENT *******************************************************************/ if (stripos($codeline, 'if') !== false) { $wordcounter = 0; $ifwords = array(); while ($wordcounter <= $lastword) { if (strtolower($words[$wordcounter]) == 'if') { $ifcount++; } if ($ifcount >= 1 && strtolower($words[$wordcounter]) == 'begin') { $ifsbegin++; array_push($ifwords, $wordcounter); } $wordcounter++; } if (count($ifwords >= 1)) { foreach ($ifwords as $wordpos) { $words[$wordpos] = 'then'; } } unset($ifwords); } /******************************************************************* * ADD THEN AFTER IF *******************************************************************/ if (stripos($codeline, 'begin') !== false && $ifcount >= 1) { $wordcounter = 0; while ($wordcounter <= $lastword) { if (strtolower($words[$wordcounter]) == 'begin') { $words[$wordcounter] = 'then'; } $wordcounter++; } } if (stripos($codeline, 'begin') !== false && $ifcount >= 1 && $instring == false ) { $wordcounter = 0; while ($wordcounter <= $lastword) { if (strtolower($words[$wordcounter]) == 'begin') { $words[$wordcounter] = "then"; } $wordcounter++; } } /******************************************************************* * GET PARAMETER DEFAULT VALUES *******************************************************************/ if (stripos($codeline, 'begin') !== false && $needsbegin == true ) { // loop through all parameter default values $alldefaults = ""; if ($startparameters == false) { foreach ($parameter_default_values as $defaultvalue) { if ($mode = "debug") { $alldefaults .= "<br> if " . $defaultvalue['var'] . " = NULL then <br> " . $defaultvalue['statement'] . "<br> end if; <br>"; } else { $alldefaults .= "\r\n if " . $defaultvalue['var'] . " = NULL then \r\n " . $defaultvalue['statement'] . "\r\n end if; \r\n"; } } $words[$lastword] .= $alldefaults; } $needsbegin = false; } /******************************************************************* * ADD END IF; IF THERE IS AN IF *******************************************************************/ if (stripos($codeline, 'end') !== false && $ifcount >= 1 && $instring == false ) { $wordcounter = 0; while ($wordcounter <= $lastword && $ifcount >= 1) { if (strtolower($words[$wordcounter]) == 'end') { if ($wordcounter == $lastword && strtolower($nextlinewords[0]) !== 'else') { $words[$wordcounter] = $words[$wordcounter] . " if;"; $ifcount--; } elseif ($wordcounter < $lastword && strtolower($words[$wordcounter + 1]) !== 'else') { $words[$wordcounter] = $words[$wordcounter] . " if;"; $ifcount--; } } $wordcounter++; } } /******************************************************************* * CLEAR DEFAULT VALUES FROM PARAMETER DECLARATION *******************************************************************/ if ($startparameters == true && strpos($codeline, "=") !== false) { // add to list of default parameters if ($characters[$lastcharacter] == ',') { $codeline = substr($codeline, 0, strlen($codeline) - 1); } array_push($parameter_default_values, array("var" => $words[0], "statement" => "set $codeline;")); // remove default value from declaration // init loop variables $equalfound = false; $wordcounter = 0; while ($wordcounter <= $lastword) { if ($words[$wordcounter] == '=') { $equalfound = true; } if ($equalfound) { if (strpos($words[$wordcounter], ',') !== false) $words[$wordcounter] = ','; else $words[$wordcounter] = ''; } $wordcounter++; } } /******************************************************************* * REPLACE TOP n ROWS WITH limit n *******************************************************************/ if (isset($limitofquery)) { if (contains($codeline, "order by")) { if ($characters[$lastcharacter] !== ',' || $nextlinecharacters[0] !== ',') { $words[$lastword] .= ' limit ' . $limitofquery; unset($limitofquery); } } elseif (contains($codeline, "group by") && contains($nextcodeline, "order by") == false) { if ($characters[$lastcharacter] !== ',' || $nextlinecharacters[0] !== ',') { $words[$lastword] .= ' limit ' . $limitofquery; unset($limitofquery); } } elseif (contains($codeline, "where") && contains($nextcodeline, "order by") == false && contains($nextcodeline, "group by") == false ) { if ( (strtolower($words[$lastword]) !== 'and' && strtolower($words[$lastword]) !== 'or' ) && (strtolower($nextlinewords[0]) !== 'and' && strtolower($nextlinewords[0]) !== 'or' ) ) { $words[$lastword] .= ' limit ' . $limitofquery; unset($limitofquery); } } elseif ( ( strtolower($prevlinewords[$prevlastword]) == 'and' || strtolower($prevlinewords[$prevlastword]) == 'or' || strtolower($words[0]) == 'and' || strtolower($words[0]) == 'or' ) && strtolower($words[$lastword]) !== 'and' && strtolower($words[$lastword]) !== 'or' && strtolower($nextlinewords[0]) !== 'and' && strtolower($nextlinewords[0]) !== 'or' && contains($nextcodeline, "order by") == false && contains($nextcodeline, "group by") == false ) { $words[$lastword] .= ' limit ' . $limitofquery; unset($limitofquery); } elseif (contains($codeline, "from") && contains($nextcodeline, "where") == false) { if ($characters[$lastcharacter] !== ',' || $nextlinecharacters[0] !== ',') { $words[$lastword] .= ' limit ' . $limitofquery; unset($limitofquery); } } } /******************************************************************* * REPLACE ISNULL WITH IFNULL * REPLACE SELECT INTO WITH SELECT *******************************************************************/ $wordcounter = 0; while ($wordcounter <= $lastword) { // isnull(a,b) if (stripos($words[$wordcounter], 'ISNULL(') !== false) { $words[$wordcounter] = str_ireplace("ISNULL(", "ifnull(", $words[$wordcounter]); } // select into var = string if (strtolower($words[$wordcounter]) == 'select' && strtolower($words[$wordcounter + 1]) == 'into') { $words[$wordcounter + 1] = ''; } // getdate() if (stripos($words[$wordcounter], 'getdate()') !== false) { $words[$wordcounter] = str_ireplace("getdate()", "now()", $words[$wordcounter]); } // top n rows if (stripos($words[$wordcounter], 'top') !== false) { $words[$wordcounter] = ''; $limitofquery = $words[$wordcounter + 1]; $words[$wordcounter + 1] = ''; } $wordcounter++; } } // not is comment // create final output string $finalline = ''; foreach ($words as $word) { $finalline .= $word . " "; } // do final weird character replacements $arrsearch = array( "1" => "[dbo].[", "2" => "]", "3" => "┴", ); $arrreplacement = array( "1" => "dbo.", "2" => $blank, "3" => "''", ); $finalline = str_ireplace($arrsearch, $arrreplacement, $finalline); // final processing before output $query[$currentline] = $finalline; $codeline = $finalline; if ($mode == 'debug') echo $codeline . "<br>"; // increment current line $currentline++; } if ($mode == 'debug') { echo "/// <br> DELIMITER ;"; } else { echo "/// \r\n DELIMITER ;"; }