Jump to content

PHP Script to convert SP from mssql to mysql

jakekoekemoer

By jakekoekemoer
in PHP Coding Help

 Share

Recommended Posts

jakekoekemoer Newbie

jakekoekemoer

Posted
Posted

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 ;";
}
Link to comment
Share on other sites
jakekoekemoer Newbie

jakekoekemoer

Posted
  • Author
Posted (edited)

Script as updated: 11/03/2014

Added: Better inline function.

Added: Start of string Concat
Added: Some extra string manipulation functions
 
<?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;
        }
    }
}

function build_code_line_from_words($words)
{
    $codeline = '';

    foreach ($words as $word) {
        $codeline .= $word . " ";
    }

    return $codeline;
}

function break_code_line_to_words($codeline)
{
    $words = explode(" ", $codeline);
    return $words;
}

function build_code_line_from_characters($characters)
{
    $codeline = '';

    foreach ($characters as $character) {
        $codeline .= $character;
    }

    return $codeline;
}

/****************************************************
 *START THE ACTUAL CODE
 ***************************************************/

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

echo "
/*<br>
MODE = $mode <br>
OUTPUT = $output <br>
SHOW TECHNICAL INFORMATION = $techinfo<br>
*/<br>";

$blank = "";
$instring = false;
$needsbegin = false;
$execute_found = false;

$query = array();
$parameter_default_values = array();

// 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 = break_code_line_to_words($codeline);

    $prevlinecharacters = str_split($prevcodeline);
    $prevlinewords = break_code_line_to_words($prevcodeline);

    $nextlinecharacters = str_split($nextcodeline);
    $nextlinewords = break_code_line_to_words($nextcodeline);

    // sized of break down
    $lastcharacter = count($characters) - 1;
    $lastword = count($words) - 1;

    $prevlastcharacter = count($prevlinecharacters) - 1;
    $prevlastword = count($prevlinewords) - 1;

    $nextlastcharacter = count($nextlinecharacters) - 1;
    $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 (contains($codeline, "'" && !$instring)
        ) {
            $quote_count = substr_count($codeline, "'");
            if ($quote_count % 2 == 0) {
                // quote count is even. THERE FOR string does not carry over to another line
                $instring == false;
            } else {
                // quote count is not even there for string carries on to another line
                $instring == true;
            }
        }elseif (contains($codeline, "'" && $instring)
        ) {
            $quote_count = substr_count($codeline, "'");

            if ($quote_count % 2 == 0) {
                $instring == true;
            } else {
                $instring == false;
            }
        }

        /*******************************************************************
         * 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[$prevlastcharacter] == ',')
            && !$instring
        ) {
            $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++;
        }

        /*******************************************************************
         * PROCESS DYNAMIC SQL WITH [NO OUTPUT]
         *******************************************************************/

        if (contains($codeline, "sp_executesql")) {
            $execute_found = true;
        }

        if (
            (
                strlen($nextcodeline) == 0
                ||
                $nextcodeline[0] == " "
            )
            &&
            $execute_found == true
        ) {

        }

        /*******************************************************************
         * PROCESS STRING CONCATENATION
         *******************************************************************/
        if ($instring) {

        }

    } // not is comment

// create final output string
    $finalline = build_code_line_from_words($words);

// 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 ;";
}
Edited by jakekoekemoer
Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.