frasqledit.pas

FIBPlus is a component suite intended for work with InterBase. It is direct, fast and flexible Inter

     begin
       TableName:='';
       AliasName:='';
       FieldName:=CurrentInput;
     end;
     if AliasName='' then
     begin
      ts:=TStringList.Create;
      try
       AllTables(CompleteProposal.Editor.Text,ts,True);
       for i:=0 to ts.Count-1 do
       begin
         PointPos:=PosCh(' ',ts[i]);
         if PointPos>0 then
          AliasName:=Trim(Copy(ts[i],PointPos+1,MaxInt))+'.'
         else
          AliasName:=Trim(ts[i])+'.';
            

         CompleteProposal.AddItem(
            Format(CMPFORMAT,[' :', ts[i]])
           ,
              AliasName
         );

       end;
      finally
       ts.Free
      end;
     end
     else
     begin
       qryTabFields1.Params[0].asString:=TableName;
       qryTabFields1.CloseOpen(False);
       while not qryTabFields1.eof do
       begin
        if (FieldName='') or
          (PosCI(FieldName, qryTabFields1.Fields[0].asString)=1)
        then
         ObjName:=FormatIdentifier(FDatabase.SQLDialect,qryTabFields1.Fields[0].asString);
         CompleteProposal.AddItem(
            Format(CMPFORMAT,['Field :', ObjName])
           ,
              AliasName+ObjName
         );
         qryTabFields1.Next
       end;
     end;
   end;



  end;

//  CanExecute:=TSynBaseCompletionProposal(Sender).ItemList.Count>0
end;
{$ENDIF}
{$ENDIF}

procedure TfSQLEdit.cmbTabsNameViewsChange(Sender: TObject);
begin
 case cmbTabsNameViews.ItemIndex of
   0:
   begin
    qryAllTables.Close;
    qrySPs.Close;
   end;
   1:
   begin
      qrySPFields.DataSource :=nil;
      qryTabFields.DataSource:=ds;
      ds.DataSet:=qryAllTables;
      ds.DataSet.Open;
      dsFields.DataSet:=qryTabFields;
   end;
   2:
   begin
      qrySPFields.DataSource :=ds;
      qryTabFields.DataSource:=nil;

      ds.DataSet:=qrySPs;
      ds.DataSet.Open;
      dsFields.DataSet:=qrySPFields;      
   end;
 end;
end;

procedure TfSQLEdit.qrySPsFilterRecord(DataSet: TDataSet;
  var Accept: Boolean);
begin
 Accept :=true;
 with DataSet do       
  if Trim(Edit1.Text)<>'' then
   Accept :=PosCI(Trim(Edit1.Text),Fields[0].AsString)<>0;
end;

procedure TfSQLEdit.Edit1Change(Sender: TObject);
begin
// qryAllTables.RefreshClientFields
   qryAllTables.RefreshFilters;
   qrySPs.RefreshFilters;
end;

procedure TfSQLEdit.DBGrid1DblClick(Sender: TObject);
begin
 GenTemplate(cmbTabsNameViews.ItemIndex=2,chOnlySelFields.Checked);
end;



function GetLinePosition(SQL:TStrings;Position:integer):integer;
var L:integer;
begin
 Result:=0; L:=0;
 while (L<Position) and (Result<SQL.Count) do
 begin
   L:=L+Length(SQL[Result])+2;
   Inc(Result);
 end;
end;

procedure TfSQLEdit.GenTemplate(IsSP,OnlySelFields: boolean);
var DefSyn:string;
    DefSyn1:string;
    Dest:TStrings;
    DFrom:TPoint;
    SysTab:TFIBDataSet;
    LineFrom:integer;
    fn,pars:string;
    IsExecSP:boolean;
    CurMetaObj:string;
    i,c:integer;
    s:string;
    p,p1:integer;
procedure AddField(const FName:string);
begin
  with Dest do
  begin
      fn:=FormatIdentifier(Dialect,FName);
      if chReplaceSQL.Checked then
       Add(SpaceStr+DefSyn1+fn+',')
      else
      begin
       Dest[LineFrom-1]:=Dest[LineFrom-1]+',';
       Insert(LineFrom,SpaceStr+DefSyn1+fn);
       Inc(LineFrom)
      end;
  end;
end;

procedure AddUpdateField(const FName:string);
begin
  with Dest do
  begin
      fn:=FormatIdentifier(Dialect,FName);
      Add(SpaceStr+fn+'='+vParamSymb+fn+',')
  end;
end;

begin
 LineFrom:=0;
 if DBGrid1.DataSource.DataSet.FieldCount=0 then Exit;
 CurMetaObj:=DBGrid1.DataSource.DataSet.Fields[0].asString;
 if LbTableSyn.Checked and (cmbKindSQL.ItemIndex=0)  then
 begin
   DefSyn:=FormatIdentifier(FDatabase.SQLDialect, Copy(CurMetaObj,1,3));
   if not InputQuery('Enter alias for table :'+CurMetaObj,'',DefSyn) then
    DefSyn:=''
 end
 else
  DefSyn:='';
 if DefSyn<>'' then
  DefSyn1:=DefSyn+'.'
 else
  DefSyn1:='';
 Dest:=OutPutTxt;
 if chReplaceSQL.Checked then Dest.Clear;
 if isSP then SysTab:=qrySPparams else SysTab:=qryTabFields;
 with SysTab,Dest do
 begin
{   if Params[0].AsString<>CurMetaObj then
   begin
    Params[0].AsString :=CurMetaObj;
    Close;    Open;
   end;
 }
   if Params.ByName['NAME'].AsString<>CurMetaObj then
   begin
    Params.ByName['NAME'].AsString :=CurMetaObj;
    Close;    Open;
   end;
   
   if RecordCount=0 then
   begin
    if isSP then
    begin
     Add('EXECUTE PROCEDURE '+ FormatIdentifier(Dialect,CurMetaObj));
    end;
    Exit;
   end;

   IsExecSP:=IsSP and ((Fields[2].asInteger=0) or (cmbKindSQL.ItemIndex=4));
   if IsExecSP then
   begin
     Add('EXECUTE PROCEDURE '+ FormatIdentifier(Dialect,CurMetaObj));
     DefSyn:=' ';
   end;
   if (chReplaceSQL.Checked) or (cmbKindSQL.ItemIndex<>0) then
   begin
    if not IsExecSP then
    case cmbKindSQL.ItemIndex of
     0:Add('SELECT');
     1:
     begin
       DefSyn:='';
       Add('INSERT INTO '+FormatIdentifier(Dialect,CurMetaObj)+' (');
     end;

     2:Add('UPDATE '+FormatIdentifier(Dialect,CurMetaObj)+' SET ');
     3:Add('DELETE FROM '+FormatIdentifier(Dialect,CurMetaObj));
    end;
   end
   else
   begin
    DFrom:=DispositionFrom(Dest.Text);
    if DFrom.X=0 then raise Exception.Create('Clause "FROM" not found')
    else
    begin
     s:=Dest.Text;
     if Copy(s,DFrom.Y-DFrom.X-1,2)<>ForceNewStr then
      Dest.Text:=Copy(s,1,DFrom.X-1)+#13#10+
                Copy(s,DFrom.X,DFrom.Y-DFrom.X)+ForceNewStr+
                Copy(s,DFrom.Y+1,MaxInt)
     else
      Dest.Text:=Copy(s,1,DFrom.X-1)+#13#10+
                Copy(s,DFrom.X,DFrom.Y-DFrom.X)+
                Copy(s,DFrom.Y+1,MaxInt);
     LineFrom:=GetLinePosition(Dest,DFrom.X+2)-1;
    end;
   end;

   if not OnlySelFields then
   begin
     First;
     while not eof and not IsExecSP and (not IsSP or (Fields[2].asInteger<>0)) do
     begin
       case cmbKindSQL.ItemIndex of
        0,1,4: AddField(Fields[0].asString);
        2: AddUpdateField(Fields[0].asString);
       end;
      Next
     end;
   end
   else
   with DBGrid2,DBGrid2.DataSource.DataSet do
   begin
    if SelectedRows.Count>0 then
      for i:=0 to Pred(SelectedRows.Count) do
      begin
        Bookmark:=SelectedRows[i];
       case cmbKindSQL.ItemIndex of
        0,1,4: AddField(Fields[0].asString);
        2: AddUpdateField(Fields[0].asString);
       end;
      end
     else
     begin
       case cmbKindSQL.ItemIndex of
        0,1,4: AddField(Fields[0].asString);
        2: AddUpdateField(Fields[0].asString);
       end;
     end;
   end;
   pars:='';
   if IsSP then
   begin
     //Input SP params
       while not eof  do
       begin
        if Fields[2].asInteger=0 then
        begin
         if pars<>'' then
          pars:=pars+','+ForceNewStr;
          pars:=pars+vParamSymb+Trim(Fields[0].asString);
        end;
        Next
       end;
       if pars<>'' then         
        pars:='('+pars+')';
   end;

   if chReplaceSQL.Checked then
   case cmbKindSQL.ItemIndex of
   0:begin
      Strings[Pred(Count)]:=Copy(Strings[Pred(Count)],1,Length(Strings[Pred(Count)])-1);
      if not IsExecSP then
       Add('FROM');
      Add(SpaceStr+ FormatIdentifier(Dialect,CurMetaObj)+pars+' '+DefSyn)
     end;
   1:begin
      Strings[Pred(Count)]:=Copy(Strings[Pred(Count)],1,Length(Strings[Pred(Count)])-1);
      Add(') VALUES (');
      c:=Count-2;
      for i:=1 to c do
      begin
        Add(SpaceStr+vParamSymb+Trim(Strings[i]));
      end;
      Add(')');
     end;
    2:begin
       Strings[Pred(Count)]:=Copy(Strings[Pred(Count)],1,Length(Strings[Pred(Count)])-1);
       Add('WHERE');

       s:=DBPrimaryKeyFields(CurMetaObj,trTransaction);
       if s<>'' then
       begin
        p1:=1;
        p:=PosCh(';',s);
        while p>0 do
        begin
         pars:=FormatIdentifier(Dialect,Copy(s,p1,p-p1));
         Add(SpaceStr+pars+'='+vParamSymb+pars + ' AND ') ;
         p1:=p+1;
         p:=PosCh1(';',s,p1);
        end;
        pars:=FormatIdentifier(Dialect,Copy(s,p1,Length(s)-p1+1));
        Add(SpaceStr+pars+'='+vParamSymb+pars) ;
       end
       else
       begin
         c:=Count-3;
         for i:=1 to c do
         begin
            Add(Copy(Strings[i],1,Length(Strings[i])-1)+' AND ') ;
         end;
         Add(Strings[c+1]) ;
       end;
      end;
    3:
     begin
       Add('WHERE');
       s:=DBPrimaryKeyFields(CurMetaObj,trTransaction);
       if s<>'' then
       begin
        p1:=1;
        p:=PosCh(';',s);
        while p>0 do
        begin
         pars:=FormatIdentifier(Dialect,Copy(s,p1,p-p1));
         Add(SpaceStr+pars+'='+vParamSymb+pars + ' AND ') ;
         p1:=p+1;
         p:=PosCh1(';',s,p1);
        end;
        pars:=FormatIdentifier(Dialect,Copy(s,p1,Length(s)-p1+1));
        Add(SpaceStr+pars+'='+vParamSymb+pars) ;
       end
     end;
    4:  Add(pars) ;
   end
   else
   begin
    DFrom:=DispositionFrom(Dest.Text);
    LineFrom :=GetLinePosition(Dest,DFrom.Y)-1;
    if (Trim(Dest[LineFrom-1])='') and (Length(Dest[LineFrom-1])>0) then
     Dest[LineFrom-1]:=SpaceStr+ 'JOIN '+
      Trim(FormatIdentifier(Dialect,CurMetaObj))+pars+' '+DefSyn+ ' ON ( )'
    else
    Insert(LineFrom,SpaceStr+ 'JOIN '+
      Trim(FormatIdentifier(Dialect,CurMetaObj))+pars+' '+DefSyn+ ' ON ( )')
   end;
   DeleteEmptyStr(Dest);
 end;
end;

procedure TfSQLEdit.SpeedButton1Click(Sender: TObject);
begin
 with FontDialog1.Font do
 begin
   Name :=viewSQL.Font.Name;
   Size :=viewSQL.Font.Size;
   Color:=viewSQL.Font.Color;
   Style:=viewSQL.Font.Style;
   CharSet:=viewSQL.Font.CharSet;
 end;
 if FontDialog1.Execute then
  viewSQL.Font:=FontDialog1.Font
end;

procedure TfSQLEdit.SpeedButton2Click(Sender: TObject);
begin
 FindDialog1.Execute
end;

procedure TfSQLEdit.FindDialog1Find(Sender: TObject);
begin
  Search(False);
end;

procedure TfSQLEdit.Search(Again:boolean);
var L,p,j:integer;
    curL:integer;
    lv:string;
    sStart:integer;
  {$IFDEF USE_SYN_EDIT}
    CurMemo:TSynMemo;
  {$ELSE}
    CurMemo:TMemo;
  {$ENDIF}
begin
  //湾觐