fpc/tests/utils/testsuite/tssql.pas

unit tssql;

{$mode ObjFPC}
{$h+}

interface

uses
  Classes, SysUtils, sqldb, tsdb, tsconsts;


Type

  { TQueryData }

  TQueryData = Class(TObject)
    PlatFormID,
    RunID,
    CompareRunID,
    PreviousRunID,
    NextRunID,
    Previous2RunID,
    Next2RunID : Int64;
    TestFileID,
    CPUID,
    AllCategoryID,
    CategoryID,
    OSID : Integer;
    VersionID : Integer;
    TestFileName,
    VersionBranch,
    Cond,
    Submitter,
    Machine,
    config : String;
    Date : TDateTime;
    Debug,
    ListAll,
    NoSkipped,
    OnlyFailed : Boolean;
    RunSkipCount,
    RunFailedCount,
    RunCount : Integer;
    Action,
    Limit : Integer;
    TestLastDays : Integer;
    procedure InitFromVars(aSQL: TTestSQL; aVars: TStrings);
  end;

  { TDBInfo }

  TDBInfo = Class (TObject)
    AllCategoryID : Integer;
    AllCPUID : Integer;
    AllOSID : Integer;
    AllVersionID : Integer;
    Function IsAllCPU(aCPUID : Integer) : boolean;
    Function IsAllOS(aOSID : Integer) : boolean;
    Function IsAllVersion(aVersionID : Integer) : boolean;
  end;

  { TTestSuiteSQL }

  TTestSuiteSQL = class(TObject)
    FVars : TQueryData;
    FSQL : TTestSQL;
    FInfo : TDBInfo;
    constructor create(aVars : TQueryData; aSQL : TTestSQL; aDBInfo: TDBInfo);
    function GetTestResultsSQL : String;
    function GetTestResults : TSQLQuery;
    function GetRunOverviewSQL : String;
    function GetCompareRunSQL : String;
    function GetSimpleTestResultsSQL : String;
  private
    class function FieldIs(aField: String; aValue: String): String;
    class function PointerIs(aField: String; aValue: Int64; aSkipValue: Int64=-1): String;
  end;



implementation

{ TQueryData }

procedure TQueryData.InitFromVars(aSQL : TTestSQL; aVars: TStrings);

  function GetVar(aName: string): string;

  begin
    Result:=aVars.Values[aName];
  end;

  function Int64Var(const aVar : String; const aVar2 : String = '') : Int64;

  begin
    Result:=StrToInt64Def(GetVar(aVar),-1);
    if (Result=-1) and (aVar2<>'') then
      Result:=StrToInt64Def(GetVar(aVar2),-1);
  end;

  function IntVar(const aVar : String; const aVar2 : String = '') : Integer;

  begin
    Result:=StrToIntDef(GetVar(aVar),-1);
    if (Result=-1) and (aVar2<>'') then
      Result:=StrToIntDef(GetVar(aVar2),-1);
  end;

  function StrVar(const aVar : String; const aVar2 : String = '') : string;
  begin
    Result:=GetVar(aVar);
    if (Result='') and (aVar2<>'') then
      Result:=GetVar(aVar2);
  end;

  function BoolVar(const aVar : String; const aVar2 : String = '') : Boolean;
  var
    S : string;
  begin
    S:=GetVar(aVar);
    if (S='') and (aVar2<>'') then
      S:=GetVar(aVar2);
    Result:=(S='1');
  end;

Var
  S : String;

begin
  S:=StrVar('action','TESTACTION');
  Case S of
    'View_history' : Action:=faction_show_history;
    'Show/Compare' : Action:=faction_show_run_results;
    'Compare_to_previous':  Action:=faction_compare_with_previous;
    'Compare_to_next' : Action:=faction_compare_with_next;
    'Compare_right_to_previous' : Action:=faction_compare2_with_previous;
    'Compare_right_to_next' : Action:=faction_compare2_with_next;
    'Compare_both_to_previous' : Action:=faction_compare_both_with_previous;
    'Compare_both_to_next' : Action:=faction_compare_both_with_next;
  else
    Action:=StrToIntDef(S,0);
  end;
  Limit:=IntVar('limit','TESTLIMIT');
  if Limit=-1 then
    Limit:=50;
  if Limit > MaxLimit then
    Limit:=MaxLimit;
  Submitter:=StrVar('submitter','TESTSUBMITTER');
  Machine:=StrVar('machine','TESTMACHINE');
  RunID:=Int64Var('run1id','TESTRUN');
  TestLastDays:=IntVar('lastdays','TESTLASTDAYS');
  if TestLastDays=-1 then
    TestLastDays:=31;
  S:=StrVar('date','TESTDATE');
  if Length(S) > 0 then
    try
      Self.Date:=StrToDate(S);
    except
      Self.Date:=0;
    end;
  OnlyFailed:=BoolVar('failedonly','TESTFAILEDONLY');
  NoSkipped:=BoolVar('noskipped','TESTNOSKIPPED');
  CompareRunID:=Int64Var('run2id');
  PreviousRunID:=Int64Var('previousrunid');
  NextRunID:=Int64Var('nextrunid');
  Previous2RunID:=Int64Var('previous2runid');
  Next2RunID:=Int64Var('next2runid');
  TestFileID:=Int64Var('testfileid');
  TestFileName:=StrVar('testfilename');
  RunCount:=IntVar('PIETOTAL');
  RunSkipCount:=IntVar('PIESKIPPED');
  RunFailedCount:=IntVar('PIEFAILED');
  Debug:=BoolVar('DEBUGCGI');
  ListAll:=BoolVar('listall');
  Cond:=StrVar('cond','TESTCOND');
  Config:=StrVar('comment','TESTCOMMENT');
  if Config='' then
    Config:=StrVar('config','TESTCONFIG');

  // For Version,OS,CPU,Category: try integer, else try string and convert to integer.
  VersionID:=IntVar('version','TESTVERSION');
  if VersionID=-1 then
    VersionID:=aSQL.GetVersionID(StrVar('version','TESTVERSION'));
  OSID:=IntVar('os','TESTOS');
  if OSID=-1 then
    OSID:=aSQL.GetOSID(StrVar('os','TESTOS'));
  CPUID:=IntVar('cpu','TESTCPU');
  if CPUID=-1 then
    CPUID:=aSQL.GetCPUID(StrVar('cpu','TESTCPU'));
  CategoryID:=IntVar('category','TESTCATEGORY');
  if CategoryID=-1 then
    CategoryID:=aSQL.GetCategoryID(StrVar('category','TESTCATEGORY'));
  if (TestFileID=-1) and (TestFileName<>'') then
    TestFileID:=aSQL.GetTestID(TestFileName);
  if (TestFileID<>-1) then
    TestFileName:=aSQL.GetTestFileName(TestFileID);
end;

{ TDBInfo }

function TDBInfo.IsAllCPU(aCPUID: Integer): boolean;
begin
  Result:=(aCPUID=-1) or (aCPUID=AllCPUID);
end;

function TDBInfo.IsAllOS(aOSID: Integer): boolean;
begin
  Result:=(aOSID=-1) or (aOSID=AllOSID);
end;

function TDBInfo.IsAllVersion(aVersionID: Integer): boolean;
begin
  Result:=(aVersionID=-1) or (aVersionID=AllVersionID);
end;

{ TTestSuiteSQL }

class function TTestSuiteSQL.PointerIs(aField: String; aValue: Int64; aSkipValue: Int64): String;

begin
  Result:='';
  if (aValue<0) or (aValue=aSkipValue) then
    exit;
  Result:=Format(' AND (%s=%d)',[aField,aValue]);
end;

class function TTestSuiteSQL.FieldIs(aField: String; aValue: String): String;
begin
  Result:='';
  if aValue='' then exit;
  Result:=Format('AND (%s = ''%s'')',[aField,TTestSQL.EscapeSQL(aValue)]);
end;

constructor TTestSuiteSQL.create(aVars: TQueryData; aSQL: TTestSQL; aDBInfo : TDBInfo);
begin
  FVars:=aVars;
  FSQL:=aSQL;
  FInfo:=aDBInfo;
end;

function TTestSuiteSQL.GetTestResultsSQL: String;

var
  S,SS : String;

begin
  SS:='SELECT TR_ID,TR_TESTRUN_FK AS Run, TR_TEST_FK, TR_OK AS OK'
    +', TR_SKIP As Skip,TR_RESULT  As Result'
  //S:='SELECT * '
    +',TC_NAME AS CPU, TV_VERSION AS Version, TO_NAME AS OS'
    +',TU_ID,TU_DATE AS Date,TU_SUBMITTER  AS Submitter'
    +',(TU_FAILEDTOCOMPILE + TU_FAILEDTOFAIL + TU_FAILEDTORUN) AS Fails'
    +',TP_MACHINE AS Machine,TP_CONFIG AS config'
    +',TU_COMPILERDATE As CompDate'
    +',TU_TESTSREVISION AS Tests_rev'
    +',TU_RTLREVISION AS RTL_rev'
    +',TU_COMPILERREVISION AS Compiler_rev'
    +',TU_PACKAGESREVISION AS Packages_rev'
    +',TO_ID,TC_ID,TV_ID'
    +' FROM TESTRUN '
    +' Inner join TESTPLATFORM ON (TU_PLATFORM_FK=TP_ID) '
    +' LEFT JOIN TESTRESULTS ON  (TR_TESTRUN_FK=TU_ID)'
    +' LEFT JOIN TESTOS ON  (TP_OS_FK=TO_ID)'
    +' LEFT JOIN TESTCPU ON  (TP_CPU_FK=TC_ID)'
    +' LEFT JOIN TESTVERSION ON  (TP_VERSION_FK=TV_ID)';
  S:='';
  S:=S+PointerIS('TR_TEST_FK',FVars.TestFileID);
  S:=S+PointerIs('TR_TESTRUN_FK',FVars.RunID);
  If FVars.OnlyFailed then
    S:=S+' AND (NOT TR_OK)';
  If FVars.NoSkipped then
    S:=S+' AND (NOT TR_SKIP)';
  If FVars.Cond<>'' then
    S:=S+' AND ('+FVars.Cond+')';
  S:=S+PointerIs('TP_CPU_FK',FVars.CPUID, FInfo.AllCPUID);
  S:=S+PointerIs('TP_VERSION_FK',FVars.VERSIONID,FInfo.AllVersionID);
  S:=S+PointerIs('TP_OS_FK',FVars.OSID,FInfo.AllOSID);
  S:=S+FieldIs('TP_MACHINE',FVars.Machine);
  S:=S+FieldIs('TP_CONFIG',FVars.Config);
  S:=S+FieldIs('TU_SUBMITTER',FVars.Submitter);
  if FVars.DATE<>0 then
    S:=S+Format(' AND (TU_DATE >= ''%s'')',[FormatDateTime('YYYY-MM-DD',FVars.Date)]);

  if S <> '' then
  begin
    Delete(S, 1, 4);
    S:=SS + ' WHERE '+ S;
  end
  else
    S:=SS;

  S:=S+' ORDER BY TU_ID DESC';
  if FVars.DATE=0 then
    S:=S+' LIMIT '+IntToStr(FVars.Limit)
  else
    S:=S+' LIMIT '+IntToStr(MaxLimit);
  Result:=S;
end;

function TTestSuiteSQL.GetTestResults: TSQLQuery;
begin
  Result:=FSQL.CreateQuery(GetTestResultsSQL);
end;

function TTestSuiteSQL.GetRunOverviewSQL: String;

Const
  SOverview = 'SELECT TU_ID as ID,TU_DATE as Date,TC_NAME as CPU, TO_NAME as OS,'+
               'TV_VERSION as Version, '+
               '(select count(*) from testresults where (TR_TESTRUN_FK=TU_ID)) as Count,'+
               'TU_COMPILERREVISION as CompRev,'+
               'TU_RTLREVISION as RTLRev,'+
               'TU_PACKAGESREVISION as PackRev,'+
               'TU_TESTSREVISION as TestsRev,'+

               '(TU_SUCCESSFULLYFAILED+TU_SUCCESFULLYCOMPILED+TU_SUCCESSFULLYRUN+TU_UNIT+TU_SKIPPINGRUNTEST) AS OK,'+

               '(TU_FAILEDTOCOMPILE+TU_FAILEDTORUN+TU_FAILEDTOFAIL) as Failed,'+

               '(TU_FAILEDTOCOMPILE+TU_SUCCESSFULLYFAILED+TU_FAILEDTOFAIL+TU_SUCCESFULLYCOMPILED+'+
               ' TU_FAILEDTORUN+TU_KNOWNPROBLEM+TU_SUCCESSFULLYRUN+TU_SKIPPEDGRAPHTEST+'+
               ' TU_SKIPPEDINTERACTIVETEST+TU_KNOWNBUG+TU_COMPILERVERIONTOOLOW+TU_COMPILERVERIONTOOHIGH+'+
               ' TU_OTHERCPU+TU_OTHERTARGET+TU_UNIT+TU_SKIPPINGRUNTEST) as Total,'+

               'TU_SUBMITTER as Submitter, TP_MACHINE as Machine, TP_CONFIG as Comment %s '+
              'FROM '+
               ' TESTRUN '+
               ' left join TESTPLATFORM on (TP_ID=TU_PLATFORM_FK) '+
               ' left join TESTCPU on (TC_ID=TP_CPU_FK) '+
               ' left join TESTOS on (TO_ID=TP_OS_FK) '+
               ' left join TESTVERSION on (TV_ID=TP_VERSION_FK) '+
               ' left join TESTCATEGORY on (TA_ID=TP_CATEGORY_FK) '+
              '%s'+
              'ORDER BY TU_ID DESC LIMIT %d';

Var
  SC,S : String;

begin
   S:='';
   S:=S+PointerIs('TP_CPU_FK',FVars.CPUID,FInfo.AllCPUID);
   S:=S+PointerIs('TP_CATEGORY_FK',FVars.CategoryID,FInfo.AllCategoryID);
   S:=S+PointerIs('TP_VERSION_FK',FVars.VersionID,FInfo.AllVersionID);
   S:=S+PointerIs('TP_OS_FK',FVars.OSID,FInfo.ALLOSID);
   If (Round(FVars.Date)<>0) then
     S:=S+Format(' AND (TU_DATE=''%s'')',[FormatDateTime('YYYY-MM-DD',FVars.Date)]);
   S:=S+FieldIs('TU_SUBMITTER',FVars.Submitter);
   S:=S+FieldIs('TP_MACHINE',FVars.Machine);
   S:=S+FieldIs('TP_CONFIG',FVars.Config);
   If FVars.Cond<>'' then
     S:=S+' AND ('+FVars.Cond+')';
   If (FSQL.GetCategoryName(FVars.CategoryID)<>'DB') then
     SC:=', CONCAT(TU_COMPILERREVISION,''/'',TU_RTLREVISION,''/'', '+
          'TU_PACKAGESREVISION,''/'',TU_TESTSREVISION) as rev'
   else
     SC:='';
   If (FVars.CategoryID=-1) or (FSQL.GetCategoryName(FVars.CategoryID)='All') then
     SC:=SC+', TA_NAME as Cat';


  if S <> '' then
  begin
    Delete(S, 1, 4);
    S:='WHERE '+ S + ' ';
  end;
  Result:=Format(SOverview,[SC,S,FVars.Limit]);
end;

function TTestSuiteSQL.GetCompareRunSQL: String;
var
  S,QRy : String;
begin
  If FVars.NoSkipped then
    begin
    Qry:='(((tr1.TR_SKIP) and (not tr2.TR_OK) and (not tr2.TR_SKIP)) or '
       +'((not tr1.TR_OK) and (not tr1.TR_SKIP) and (tr2.TR_SKIP)) or '
       +'((not tr1.TR_SKIP) and (not tr2.TR_SKIP))) and ';
    end
  else
    Qry:='';
  S:=Format(
     'with tr1 as (SELECT * FROM TESTRESULTS WHERE TR_TESTRUN_FK=%d), '+
     '     tr2 as (SELECT * FROM TESTRESULTS WHERE TR_TESTRUN_FK=%d) '+
     ' SELECT T_ID as id,T_NAME as Filename,tr1.TR_SKIP as Run1_Skipped,'
     +'tr2.TR_SKIP as Run2_Skipped,tr1.TR_OK as Run1_OK,'
     +'tr2.TR_OK as Run2_OK, tr1.TR_Result as Run1_Result,'
     +'tr2.TR_RESULT as Run2_Result '
     +'FROM TESTS, tr2 LEFT JOIN tr1 USING (TR_TEST_FK) '
     +'WHERE ((tr1.TR_SKIP IS NULL) or (tr2.TR_SKIP IS NULL) or '
     +' (%s (tr1.TR_Result<>tr2.TR_Result)))'
     +'and (T_ID=tr2.TR_TEST_FK)',[FVars.RunID,FVars.CompareRunID,Qry]);
  Result:=S;
end;

function TTestSuiteSQL.GetSimpleTestResultsSQL: String;

var
  S : String;

begin
  S:=Format('SELECT TR_ID, TR_TESTRUN_FK AS RUN, TR_TEST_FK, TR_OK, TR_SKIP, TR_RESULT '
            +' FROM TESTRESULTS '
            +' WHERE  (TR_TEST_FK=%d)',[FVars.TestFileID]);
  If FVars.OnlyFailed then
    S:=S+' AND (TR_OK=''f'')';
  if (FVars.comparerunid<>-1) then
     S:=S+Format(' AND ((TR_TESTRUN_FK=%d) OR (TR_TESTRUN_FK=%d))',[FVars.runid,FVars.comparerunid])
  else if (FVars.runid<>-1) then
     S:=S+Format(' AND (TR_TESTRUN_FK=%d)',[FVars.runid])
  else
     S:=S+' ORDER BY TR_TESTRUN_FK DESC LIMIT '+IntToStr(FVars.Limit);
  Result:=S;
end;

end.