Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
You can’t perform that action at this time.