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
- The script reads in all the text in the Source file into an array using
System.IO.File.ReadAllLines()
function. - Iterating through each line —
- It waits for the first delimiter start line (“
delimiter ;;
”) - 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. - 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. - It continues reading lines and adding them to the current routine’s buffer until the delimiter end line (“
delimiter ;
”) is encountered. - If
IncludeDelimiterLines
is True
, the delimiter end line is added to the buffer. The entire buffer is then saved to the file. - Variables are cleared and the iteration starts for the next routine.
Now the Script
<
.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
[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!