PowerShell Script to Split Single-file Routines Export from MySQL Server

MySQL allows you to dump out (export) the code for stored procedures and functions into a single file, only. Even their GUI tool (MySQL Workbench) and the popular web GUI (phpMyAdmin) provide identical export options.

In a certain project, I needed each routine to be in a separate file. After hunting around the web for a while, I came to the conclusion that I needed to write my own for best results. Since PowerShell now runs in both Windows and Linux, I decided on using PowerShell as my script-engine of choice. Note though that I have made use of what’s essentially .NET Framework’s System.IO namespace classes and functions to do all the actual work within the PowerShell script!

The script takes three command-line parameters:

• Source – Full path to the file that contains the routines (file must exist)
• TargetPath – Full path to a directory where the split files are to be saved to (directory will be created if not exists, but script runner must have permission to do so)
• IncludeDelimiterLines – (false by default) Sets if the “DELIMITER ;” and “DELIMITER ;;” lines in the SQL dump will be carried into the split files.

DELIMITER lines – These lines are required to be present only in certain circumstances. For example, my project was executing each file using an ODBC connection and the presence of these lines would throw an exception saying I had a syntax error.

Algorithm of the Script

1. The script reads in all the text in the Source file into an array using System.IO.File.ReadAllLines() function.
2. Iterating through each line —
   1. It waits for the first delimiter start line (“delimiter ;;”)
   2. If it is a delimiter start line, it signals itself that the next line is going to be a CREATE statement. If IncludeDelimiterLines is True, this line is added to the current routine’s buffer.
   3. It parses the CREATE statement line and extracts the name of the function or procedure and sets the target file name by combining this with the TargetPath. Line is added to the current routine’s buffer.
   4. It continues reading lines and adding them to the current routine’s buffer until the delimiter end line (“delimiter ;”) is encountered.
   5. If IncludeDelimiterLines is True, the delimiter end line is added to the buffer. The entire buffer is then saved to the file.
   6. Variables are cleared and the iteration starts for the next routine.

Now the Script

# **************************************************************
#
# Split routines exported from MySQL Server into distinct files
#
# **************************************************************

&lt#  
.SYNOPSIS  
    MySQL exports Routines as a single file. 
    This script splits that file into individual files for each routine.

.DESCRIPTION  
    Creates one .sql file with the name of the routine for every routine found in the file.

.EXAMPLE
    
    SplitMySQLExport -Source "routines.sql" -TargetPath "C:\output"

.NOTES  
    File Name      : SplitMySQLExport.ps1  
    Author         : Sujay V Sarma sujay@live.in
    Prerequisite   : PowerShell V2 over Windows 8 or higher
    Copyright 2014 : Sujay V Sarma    
#gt;

[CmdletBinding()]
param (
    [Parameter(Mandatory=$True)]
    [string]$Source, 
    
    [Parameter(Mandatory=$True)]
    [string]$TargetPath,

    [Parameter(Mandatory=$False)]
    [bool]$IncludeDelimiterLines = $False
)

if ( -not [System.IO.File]::Exists($Source)) {
    Write-Error "Source file does not exist."
    exit
}

if ( -not [System.IO.Directory]::Exists($TargetPath)) {
    [System.IO.Directory]::CreateDirectory($TargetPath)
}


$routinesLines = [System.IO.File]::ReadAllLines($Source);

[string]$routineName = ""
[string]$routineFileName = ""
[string]$routineText = ""
[bool]$nextLineIsSprocStart = $false

foreach($line in $routinesLines) {
    if (($routineName -eq "") -and ($nextLineIsSprocStart -eq $false)) {
        if ($line -ne "DELIMITER ;;") {
            continue
        }

        $nextLineIsSprocStart = $True
        if ($IncludeDelimiterLines -eq $True) {
            $routineText = $line
        }
        continue
    }

    if ($line -eq "DELIMITER ;") {
        if ($IncludeDelimiterLines -eq $True) {
            $routineText = $routineText + [Environment]::NewLine + $line
        }

        [System.IO.File]::WriteAllText($routineFileName, $routineText)
        $routineName = ""
        $routineText = ""
        $nextLineIsSprocStart = $false

        Write-Host "Wrote file for routine $routineName to $routineFileName" 
        continue
    }

    if ($nextLineIsSprocStart -eq $True) {
        $routineNameStart = $line.IndexOf("``") + 1
        $routineNameEnd = $line.LastIndexOf("``")
        $routineName = 
            $line.Substring($routineNameStart, ($routineNameEnd - $routineNameStart))
        $routineFileName = [System.IO.Path]::Combine($TargetPath, $routineName + ".sql")

        $routineText = $routineText + [Environment]::NewLine + $line
        $nextLineIsSprocStart = $false

        Write-Host "Picked up routine $routineName"
        continue
    }

    $routineText = $routineText + [Environment]::NewLine + $line
}

Write-Host "Finished process"

Do let me know of any bugs you may encounter via the comments feature below!