program SQLWorkbenchExplain; | |
{$APPTYPE CONSOLE} | |
uses | |
SysUtils, Classes, JclSysUtils, IniFiles; | |
type | |
TConfig = record | |
LogFile: string; | |
WinscpPath: string; | |
WinscpUser: string; | |
WinscpPass: string; | |
WinscpServer: string; | |
WorkbenchPath: string; | |
WorkbenchProfile: string; | |
end; | |
var | |
log: TextFile; | |
config: TConfig; | |
procedure LoadConfig; | |
var | |
ini: TMemIniFile; | |
section: TStrings; | |
begin | |
ini := TMemIniFile.Create('SqlWorkbenchExplain.ini'); | |
section := TStringList.Create; | |
try | |
ini.ReadSectionValues('Config', section); | |
config.LogFile := section.Values['LogFile']; | |
section.Clear; | |
ini.ReadSectionValues('Winscp', section); | |
config.WinscpPath := section.Values['Path']; | |
config.WinscpUser := section.Values['User']; | |
config.WinscpPass := section.Values['Pass']; | |
config.WinscpServer := section.Values['Server']; | |
section.Clear; | |
ini.ReadSectionValues('Workbench', section); | |
config.WorkbenchPath := section.Values['Path']; | |
config.WorkbenchProfile := section.Values['Profile']; | |
finally | |
section.Free; | |
ini.Free; | |
end; | |
end; | |
procedure SetExplainOn(pSqlFile, pOutputFile: string); | |
var | |
fInput: TStringList; | |
begin | |
fInput := TStringList.Create; | |
try | |
fInput.LoadFromFile(pSqlFile); | |
fInput.Insert(0, 'set explain on avoid_execute;' + sLineBreak); | |
fInput.Add(';' + sLineBreak + 'set explain off;'); | |
fInput.SaveToFile(pOutputFile); | |
Writeln(log, fInput.Text); | |
finally | |
fInput.Free; | |
end; | |
end; | |
procedure ExecuteSql(pSqlFile: string); | |
var | |
commandOutput: string; | |
command: string; | |
begin | |
command := config.WorkbenchPath + 'sqlwbconsole.exe -profile='''+ config.WorkbenchProfile + ''' -script=''' + pSqlFile + ''''; | |
Writeln(log, command); | |
Execute(command, commandOutput, True); | |
Writeln(log, commandOutput); | |
end; | |
procedure DownloadExplainFile; | |
var | |
commandOutput: string; | |
command: string; | |
script: TStringList; | |
begin | |
script := TStringList.Create; | |
try | |
script.Add('open ' + config.WinscpUser + ':' + config.WinscpPass + '@' + config.WinscpServer); | |
script.Add('get sqexplain.out sqexplain.out'); | |
script.Add('rm sqexplain.out'); | |
script.Add('exit'); | |
script.SaveToFile('winscpcommand.tmp'); | |
command := config.WinscpPath + 'WinScp.com /SCRIPT=winscpcommand.tmp'; | |
Writeln(log, command); | |
Execute(command, commandOutput, True); | |
Writeln(log, commandOutput); | |
DeleteFile('winscpcommand.tmp'); | |
finally | |
script.Free; | |
end; | |
end; | |
procedure SaveExplain(pSqlFile: string); | |
var | |
explainFile: TStringList; | |
sqlFile: TStringList; | |
begin | |
sqlFile := TStringList.Create; | |
explainFile := TStringList.Create; | |
try | |
sqlFile.LoadFromFile(pSqlFile); | |
explainFile.LoadFromFile('sqexplain.out'); | |
Writeln(log, 'Explain output: ' + sLineBreak + explainFile.Text); | |
sqlFile.Append('/*'); | |
sqlFile.Append(sLineBreak + explainFile.Text + sLineBreak); | |
sqlFile.Append('*/'); | |
sqlFile.SaveToFile(pSqlFile); | |
finally | |
sqlFile.Free; | |
explainFile.Free; | |
end; | |
end; | |
var | |
fInput, fOutput: string; | |
begin | |
if ParamCount < 2 then | |
begin | |
fInput := 'test.sql'; | |
fOutput := 'test_out.sql'; | |
end | |
else | |
begin | |
fInput := ParamStr(1); | |
fOutput := ParamStr(2); | |
end; | |
LoadConfig; | |
AssignFile(log, config.LogFile); | |
try | |
try | |
Rewrite(log); | |
Writeln(log, fInput); | |
Writeln(log, fOutput); | |
SetExplainOn(fInput, fOutput); | |
ExecuteSql(fOutput); | |
DownloadExplainFile; | |
SaveExplain(fOutput); | |
except | |
on E: Exception do | |
Writeln(log, 'Exception: ' + E.Message); | |
end; | |
finally | |
CloseFile(log); | |
end; | |
end. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment