Simple PHP Blog / Discussion / General Discussion: PHP Script to convert SP from mssql to mysql

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 ;";
}
PHP Script to convert SP from mssql to mysql

Forums

Help

PHP Script to convert SP from mssql to mysql

PHP Script to convert SP from mssql to mysql

Forums

Help

PHP Script to convert SP from mssql to mysql document.SUBSCRIPTION_OPTIONS = { "thing": "topic", "subscribed": false, "url": "subscribe", "icon": { "css": "fa fa-envelope-o" } };

PHP Script to convert SP from mssql to mysql
