PHP Script to convert SP from mssql to mysql

2014-03-10
2014-03-10
  • 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 "
    

    /

    MODE = $mode

    OUTPUT = $output

    SHOW TECHNICAL INFORMATION = $techinfo

    /









    ";

    $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 ;";
    }