Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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.
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement