SqlWorkbenchExplain.pas

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.