qbuilder.pas

TxQuery is an SQL engine implemented in a TDataSet descendant component, that can parse SQL syntax,

     '>',
     '>=',
     '<',
     '<=',
     '<>');

  sInnerOuterJoin : array [0..3] of string =
    ( ' INNER JOIN ',
      ' LEFT  JOIN ',
      ' RIGHT JOIN ',
      ' OUTER JOIN ');

  Hand = 15;
  Hand2 = 12;

  QBSignature = '# QBuilder';

{ TOQBFilter }

constructor TOQBFilter.Create(FilterList : TOQBFilterList);
begin
   inherited Create;
   FFilterList := FilterList;
end;

function TOQBFilter.GetData(Index: Integer): String;
begin
   Result := FData[Index];
end;

procedure TOQBFilter.SetData(Index: Integer; const Value: String);
begin
   FData[Index] := Value;
end;

function TOQBFilter.IsFilterEmpty(Index: Integer): Boolean;
begin
   Result := (Length(FData[Index])=0);
end;

function TOQBFilter.FilterVerb: String;
var
   i : integer;
begin
   Result:='';
   case FFilterAction of
      faIsEqualTo, faIsLike, faIsNotEqualTo, faIsNotLike:
         for i:= 1 to 5 do
            if Length(Trim(FData[i]))>0 then
               if Length(Result)=0 then
                  Result:=sFilterActionVerb[FFilterAction] + FData[i]
               else
                  Result:=Result+' Or ' + FData[i];
      faIsBetween, faIsNotBetween:
         Result:= sFilterActionVerb[FFilterAction]+FData[1]+' And '+FData[2];
      faIsGreaterThan, faIsGreaterEqualTo, faIsLessThan, faIsLessEqualTo:
         Result:= sFilterActionVerb[FFilterAction]+FData[1];
   end;
end;

{ TOQBFilterList }
constructor TOQBFilterList.Create;
begin
   inherited Create;
   FItems:= TList.Create;
end;

destructor TOQBFilterList.Destroy;
begin
   Clear;
   FItems.Free;
   inherited Destroy;
end;

function TOQBFilterList.GetCount;
begin
   Result := FItems.Count;
end;

function TOQBFilterList.GetItem(Index: Integer): TOQBFilter;
begin
   Result := FItems[Index];
end;

function TOQBFilterList.Add: TOQBFilter;
begin
   Result := TOQBFilter.Create(Self);
   FItems.Add(Result);
end;

procedure TOQBFilterList.Clear;
var
   I: Integer;
begin
   for I:= 0 to FItems.Count - 1 do
      TOQBFilter(FItems[I]).Free;
   FItems.Clear;
end;

procedure TOQBFilterList.Delete(Index: Integer);
begin
   TOQBFilter(FItems[Index]).Free;
   FItems.Delete(Index);
end;

{ TOQBField }
constructor TOQBField.Create(FieldList : TOQBFieldList);
var
   i: Integer;
begin
   inherited Create;
   FFieldList := FieldList;               { belongs to }
   FFilters   := TOQBFilterList.Create;
   for i := 1 to 5 do    { 5 filters for every column }
       FFilters.Add;
end;

destructor TOQBField.Destroy;
begin
   FFilters.Free;
   inherited Destroy;
end;

procedure TOQBField.Assign(Source: TOQBField);
var
   i, j : integer;
begin
   FTable := Source.FTable;
   FTableAlias := Source.FTableAlias;
   FFieldName := Source.FFieldName;
   FAlias := SOurce.FAlias;
   FSortType := SOurce.FSortType;
   FShowAction := Source.FShowAction;
   for i := 0 to FFilters.Count - 1 do
   begin
      FFilters[i].FCustomExpres := Source.Filters[i].FCustomExpres;
      FFilters[i].FilterAction := Source.Filters[i].FilterAction;
      for j := 1 to 5 do
         FFilters[i].Data[j] := Source.Filters[i].Data[j];
   end;
end;

procedure TOQBField.SaveToStream(Stream:TStream);
var
   i,
   n,
   j : integer;
   s : string;
   OQBFilter : TOQBFilter ;

   procedure WriteString(const s: string);
   var
      n: integer;
   begin
      n := Length(s);
      Stream.Write(n,sizeof(n));
      if n > 0 then
         Stream.Write(s[1],n);
   end;

begin
   WriteString(FTable);
   WriteString(FTableAlias);
   WriteString(FFieldName);
   WriteString(FAlias);
   Stream.Write(FSortType,sizeof(FSortType));
   Stream.Write(FShowAction,sizeof(FShowAction));
   for i:= 0 to 4 do
   begin
      OQBFilter := FFilters[i];
      Stream.Write(OQBFilter.FFilterAction,SizeOf(OQBFilter.FFilterAction));
      WriteString(OQBFilter.FCustomExpres);
      for j := 1 to 5 do
         WriteString(OQBFilter.Data[j]);
   end;
end;

procedure TOQBField.LoadFromStream(Stream:TStream);
var
   i,
   j,
   n : integer;
   s : string;
   OQBFilter : TOQBFilter ;

   function ReadString: String;
   var
      n: integer;
   begin
      Stream.Read(n,sizeof(n));
      Result:='';
      if n > 0 then
      begin
         SetLength(Result,n);
         Stream.Read(Result[1],n);
      end;
   end;

begin
   FTable := ReadString;
   FTableAlias := ReadString;
   FFieldName := ReadString;
   FAlias:= ReadString;
   Stream.Read(FSortType,sizeof(FSortType));
   Stream.Read(FShowAction,sizeof(FShowAction));
   for i:= 0 to 4 do
   begin
      OQBFilter := FFilters[i];
      Stream.Read(OQBFilter.FFilterAction,SizeOf(OQBFilter.FFilterAction));
      OQBFilter.FCustomExpres := ReadString;
      for j:= 1 to 5 do
         OQBFilter.Data[j] := ReadString;
   end;
end;

{ TOQBFieldList }
constructor TOQBFieldList.Create;
begin
   inherited Create;
   FItems := TList.Create;
end;

destructor TOQBFieldList.Destroy;
begin
   Clear;
   FItems.Free;
   inherited Destroy;
end;

function TOQBFieldList.GetCount;
begin
   Result := FItems.Count;
end;

function TOQBFieldList.GetItem(Index: Integer): TOQBField;
begin
   Result := FItems[Index];
end;

function TOQBFieldList.Add: TOQBField;
begin
   Result := TOQBField.Create(Self);
   FItems.Add(Result);
end;

function TOQBFieldList.Insert(Index: Integer): TOQBField;
begin
   Result := TOQBField.Create(Self);
   FItems.Insert(Index,Result);
end;

procedure TOQBFieldList.Clear;
var
   I: Integer;
begin
   for I:= 0 to FItems.Count - 1 do
      TOQBField(FItems[I]).Free;
   FItems.Clear;
end;

procedure TOQBFieldList.Delete(Index: Integer);
begin
   TOQBField(FItems[Index]).Free;
   FItems.Delete(Index);
end;

procedure TOQBFieldList.ColumnMoved(FromIndex,ToIndex: Integer);
var
   P : Pointer;
begin
   P := FItems[FromIndex];
   FItems.Delete(FromIndex);
   FItems.Insert(ToIndex,P);
end;

procedure TOQBFieldList.Exchange(FromIndex,ToIndex: Integer);
begin
   FItems.Exchange(FromIndex,ToIndex);
end;

procedure TOQBFieldList.LoadFromStream(Stream: TStream);
var
   n,
   i : integer;
begin
   Clear;
   Stream.Read(n,sizeof(n));
   for i:= 0 to n - 1 do
      with Add do
         LoadFromStream(Stream);
end;

procedure TOQBFieldList.SaveToStream(Stream: TStream);
var
   i,
   n : integer;
begin
   n := Count;
   Stream.Write(n,sizeof(n));
   for i := 0 to n - 1 do
      Items[i].SaveToStream(Stream);
end;

{ TQueryBuilderDialog}

constructor TOQBuilderDialog.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  FShowButtons:=[bOpenDialog, bSaveDialog, bRunQuery];
  FSQL:=TStringList.Create;
end;

destructor TOQBuilderDialog.Destroy;
begin
  if FSQL<>nil then FSQL.Free;
  FOQBEngine:=nil;
  inherited Destroy;
end;

function TOQBuilderDialog.Execute: Boolean;
begin
  Result:=false;
  if (not Assigned(FOQBForm)) and Assigned((FOQBEngine)) then
  begin
    TOQBForm(FOQBForm):=TOQBForm.Create(Application);
    TOQBForm(FOQBForm).QBDialog:=Self;
    TOQBForm(FOQBForm).btnOpen.Visible:=bOpenDialog in FShowButtons;
    TOQBForm(FOQBForm).btnSave.Visible:=bSaveDialog in FShowButtons;
    TOQBForm(FOQBForm).btnResults.Visible:=bRunQuery in FShowButtons;
    { open the database }
    TOQBForm(FOQBForm).OpenDatabase;

    if TOQBForm(FOQBForm).ShowModal=mrOk then
    begin
      FSQL.Assign(TOQBForm(FOQBForm).MemoSQL.Lines);
      Result:=true;
    end;
    OQBEngine.CloseResultQuery;
    FOQBForm.Free;
    FOQBForm:=nil;
  end;
end;

procedure TOQBuilderDialog.Notification(AComponent: TComponent;
  Operation: TOperation);
begin
  inherited Notification(AComponent, Operation);
  if (AComponent=FOQBEngine) and (Operation=opRemove) then
    FOQBEngine:=nil;
end;

procedure TOQBuilderDialog.SetOQBEngine(const Value: TOQBEngine);
begin
  if FOQBEngine<>nil then
    FOQBEngine.FOQBDialog:=nil;
  FOQBEngine := Value;
  if FOQBEngine<>nil then
  begin
    FOQBEngine.FOQBDialog:=Self;
    FOQBEngine.FreeNotification(Self);
  end;
end;

procedure TOQBuilderDialog.SetShowButtons(const Value: TOQBbuttons);
begin
  if Value <> FShowButtons then
  begin
    FShowButtons := Value;
  end;
end;

{ TOQBEngine }

constructor TOQBEngine.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  FTableList:=TStringList.Create;
  FAliasList:=TStringList.Create;
  FFieldList:=TStringList.Create;
  FSQL:=TStringList.Create;
  FSQLcolumns:=TStringList.Create;
  FSQLcolumns_table:=TStringList.Create;
  FSQLcolumns_func:=TStringList.Create;
  FSQLfrom:=TStringList.Create;
  FSQLwhere:=TStringList.Create;
  FSQLgroupby:=TStringList.Create;
  FSQLorderby:=TStringList.Create;
  FUseTableAliases:=true;

end;

destructor TOQBEngine.Destroy;
begin
  FSQL.Free;
  FSQLcolumns.Free;
  FSQLcolumns_table.Free;
  FSQLcolumns_func.Free;
  FSQLfrom.Free;
  FSQLwhere.Free;
  FSQLgroupby.Free;
  FSQLorderby.Free;
  FFieldList.Free;
  FAliasList.Free;
  FTableList.Free;
  FreeNotification(Self);

  inherited;
end;

procedure TOQBEngine.Notification(AComponent: TComponent;
  Operation: TOperation);
begin
  inherited Notification(AComponent, Operation);
  if (AComponent=FOQBDialog) and (Operation=opRemove) then
    FOQBDialog:=nil;
end;

procedure TOQBEngine.SetDatabaseName(const Value: string);
begin
  TableList.Clear;
  FDatabaseName := Value;
  if ResultQuery.Active then
    ResultQuery.Close;
end;

procedure TOQBEngine.SetUserName(const Value: string);
begin
  FUserName := Value;
end;

procedure TOQBEngine.SetPassword(const Value: string);
begin
  FPassword := Value;
end;

procedure TOQBEngine.GenerateAliases;
var
  i,j  : integer;
  s,s1 : string;
begin
  FAliasList.Clear;
  for i:=0 to FTableList.Count-1 do
  begin
    s:=' ';
    s[1]:=FTableList[i][1]; // get the first character [1] of the table name [i]
    if FAliasList.IndexOf(s)=-1 then
      FAliasList.Add(s)
    else
      begin
        j:=1;
        repeat
          Inc(j);
          s1:=s+IntToStr(j);
        until FAliasList.IndexOf(s1)=-1;
        FAliasList.Add(s1);
      end;
  end;
end;

function TOQBEngine.GenerateSQL : string;
var
  s : string;
  i : integer;
  tbl : string;
begin
  SQL.Clear;

  s:='  SELECT ';
  for i:=0 to SQLcolumns.Count-1 do
  begin
    if SQLcolumns_func[i]=EmptyStr then
      s:=s+SQLcolumns[i]                              { a field or expression }
    else
      s:=s+SQLcolumns_func[i]+'('+SQLcolumns[i]+')';  { an aggregate function}
    if (i<SQLcolumns.Count-1) then
      s:=s+', ';
  end;
  SQL.Add(s);

  s:='    FROM ';
  for i:=0 to SQLfrom.Count-1 do