frasqledit.pas

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

unit fraSQLEdit;

interface

{$I FIBPlus.inc}
{$I pFIBPropEd.inc}
uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Menus, Forms, Dialogs,
  Buttons, ExtCtrls, StdCtrls, Grids, DBGrids, DB, FIBDatabase,FIBDataSet, pFIBDataSet,
  pFIBDatabase,FIBQuery,pFIBQuery,pFIBProps {$IFDEF D6+},Variants{$ENDIF}, ComCtrls, DBCtrls
  {$IFDEF USE_SYN_EDIT}
   ,SynEditHighlighter, SynHighlighterSQL, SynEdit, SynMemo,SynCompletionProposal,
  Mask
  {$ENDIF}

  ;




type
  TfSQLEdit = class(TFrame)
    Panel1: TPanel;
    Panel10: TPanel;
    SpeedButton1: TSpeedButton;
    SpeedButton2: TSpeedButton;
    qryAllTables: TpFIBDataSet;
    qrySPs: TpFIBDataSet;
    Panel6: TPanel;
    Panel7: TGroupBox;
    LbTableSyn: TCheckBox;
    chReplaceSQL: TCheckBox;
    Splitter2: TSplitter;
    ds: TDataSource;
    trTransaction: TpFIBTransaction;
    qryTabFields: TpFIBDataSet;
    qrySPparams: TpFIBDataSet;
    FontDialog1: TFontDialog;
    FindDialog1: TFindDialog;
    dsFields: TDataSource;
    chOnlySelFields: TCheckBox;
    qrySPFields: TpFIBDataSet;
    btnCheck: TSpeedButton;
    GroupBox1: TGroupBox;
    Panel2: TPanel;
    Splitter1: TSplitter;
    DBGrid1: TDBGrid;
    DBGrid2: TDBGrid;
    Panel3: TPanel;
    Label4: TLabel;
    Edit1: TEdit;
    cmbTabsNameViews: TComboBox;
    btnGenSQL: TSpeedButton;
    cmbKindSQL: TComboBox;
    Label1: TLabel;
    splPlan: TSplitter;
    menuPlan: TPopupMenu;
    miHidePlan: TMenuItem;
    Label2: TLabel;
    cmbParamSymbol: TComboBox;
    btnReplaceZv: TSpeedButton;
    StatusBar1: TStatusBar;
    qryTabFields1: TpFIBDataSet;
    qryAllTables1: TpFIBDataSet;
    btnShowInCodeEditor: TSpeedButton;
    qryAllGenerators: TpFIBDataSet;
    Panel4: TPanel;
    edDomain: TDBEdit;
    Label3: TLabel;
    Label5: TLabel;
    edFieldType: TEdit;
    chNotNull: TDBCheckBox;
    procedure Panel7Resize(Sender: TObject);
    procedure cmbTabsNameViewsChange(Sender: TObject);
    procedure qrySPsFilterRecord(DataSet: TDataSet; var Accept: Boolean);
    procedure Edit1Change(Sender: TObject);
    procedure DBGrid1DblClick(Sender: TObject);
    procedure SpeedButton1Click(Sender: TObject);
    procedure SpeedButton2Click(Sender: TObject);
    procedure FindDialog1Find(Sender: TObject);
    procedure DBGrid1KeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure btnGenSQLClick(Sender: TObject);
    procedure Panel10Resize(Sender: TObject);
    procedure btnCheckClick(Sender: TObject);
    procedure miHidePlanClick(Sender: TObject);
    procedure cmbParamSymbolChange(Sender: TObject);
    procedure DBGrid1MouseMove(Sender: TObject; Shift: TShiftState; X,
      Y: Integer);
    procedure btnReplaceZvClick(Sender: TObject);
    procedure qryTabFieldsBeforeOpen(DataSet: TDataSet);
    procedure btnShowInCodeEditorClick(Sender: TObject);
    procedure qryTabFieldsAfterScroll(DataSet: TDataSet);
    procedure qrySPFieldsAfterOpen(DataSet: TDataSet);
  private
    OutPutTxt:TStrings;
    Dialect  :integer;
    vParamSymb:Char;
    FDatabase  :TFIBDatabase;
    FCanConnect:boolean;
    FConnectForced:boolean;
    FExistField_ForPrecision:integer;
    procedure GenTemplate(IsSP,OnlySelFields:boolean);
    procedure viewSQLKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure viewSQLKeyUp(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure viewSQLMouseUp(Sender: TObject; Button: TMouseButton;
      Shift: TShiftState; X, Y: Integer);


    procedure ShowHideTemplateSupport;
    procedure CheckSQL;
    function GetSQLText: string;
    procedure SetSQLText(const Value: string);
    procedure EditorDragOver(Sender, Source: TObject; X, Y: Integer;
     State: TDragState; var Accept: Boolean);
    procedure EditorDragDrop(Sender, Source: TObject; X, Y: Integer);
    function GetModified: boolean;
  public
    vEdComponent:TFIBQuery;
   {$IFDEF USE_SYN_EDIT}
    viewSQL: TSynMemo;
    viewPlan:TSynMemo;
    SynSQLSyn1: TSynSQLSyn;
    CompleteProposal:TSynCompletionProposal;
    {$IFNDEF  SYNEDIT_08_2001}
     procedure CompleteProposalExecute(Kind: SynCompletionType;
      Sender: TObject; var CurrentInput: String; var x, y: Integer;
      var CanExecute: Boolean
     );
    {$ENDIF}
   {$ELSE}
    viewSQL: TMemo;
    viewPlan: TMemo;
   {$ENDIF}
    procedure Search(Again:boolean);
  public
    destructor Destroy; override;
    procedure ShowEditorStatus;
    procedure ReadOptions;
    procedure SaveOptions;

    procedure PrepareFrame(Database:TFIBDatabase);
    property  SQLText:string read GetSQLText write SetSQLText;
    property  CanConnect: boolean read FCanConnect;
    property  Modified:boolean read GetModified;
  end;

implementation

{$R *.dfm}

uses
 {$IFNDEF NO_REGISTRY} RegUtils, {$ENDIF}
SqlTxtRtns,FIBConsts,StrUtil,pFIBDataInfo,ToCodeEditorIntfs;

const
   RegFIBSQLEdOptions='SQLEditorFrame';

   
procedure TfSQLEdit.Panel7Resize(Sender: TObject);
begin
 DBGrid1.Columns[0].Width:=Panel7.Width-30;
 Edit1.Width:=Panel7.Width-104;
 cmbTabsNameViews.Width:=Panel7.Width-7;
 DBGrid2.Columns[0].Width:=DBGrid1.Columns[0].Width;
end;

procedure TfSQLEdit.PrepareFrame(Database: TFIBDatabase);

//var Splitter1:TSplitter;

procedure PrepareDataSet(DS:TpFIBDataSet);
begin
   DS.DataBase         :=DataBase;
   DS.Transaction      :=trTransaction;
   DS.UpdateTransaction:=trTransaction;
end;

begin
   FDatabase:=Database;
   if Assigned(Database) then
   begin
     Dialect:=Database.SQLDialect;
     if Database.Connected then
      FCanConnect:=True
     else
     try
      Database.Connected:=True;
      FCanConnect   :=True;
      FConnectForced:=True;
//      Database.Connected:=False
     except
     end;
   end
   else
   begin
    Dialect:=1;
    Panel6.Visible:=False;
    Splitter2.Visible:=False;
    btnCheck.Enabled :=False;
    btnGenSQL.Visible:=False;
   end;
   vParamSymb:=':';
   {$IFDEF USE_SYN_EDIT}

    SynSQLSyn1 :=TSynSQLSyn.Create(Self);
    with SynSQLSyn1 do
    begin
      DefaultFilter:= 'SQL files (*.sql)|*.sql';
      CommentAttri.Foreground := clBlue;
      NumberAttri.Foreground  := clRed ;
      StringAttri.Background  := clInactiveBorder;
      SQLDialect              := sqlInterbase6;
    end;
    viewSQL    :=TSynMemo.Create(Self);
    viewSQL.Highlighter:=SynSQLSyn1;
    viewSQL.Gutter.ShowLineNumbers:=True;
    viewPlan   :=TSynMemo.Create(Self);
    viewPlan.Highlighter:=SynSQLSyn1;

    {$IFNDEF  SYNEDIT_08_2001}
    CompleteProposal:=TSynCompletionProposal.Create(Self);
    CompleteProposal.Editor:=viewSQL;
    CompleteProposal.OnExecute:=CompleteProposalExecute;

    CompleteProposal.ShortCut:=ShortCut(VK_SPACE,[ssCtrl]);
//    CompleteProposal.EndOfTokenChr:='()/*,+-=<> ';
    CompleteProposal.EndOfTokenChr:=')/*,+-=<> ';
    CompleteProposal.Options:=
     CompleteProposal.Options+[scoUsePrettyText,scoUseInsertList,scoUseBuiltInTimer];
    {$ENDIF}
   {$ELSE}
    viewSQL:=TMemo.Create(Self);
    viewSQL.ScrollBars:=ssBoth;
    viewPlan:=TMemo.Create(Self);
    viewPlan.ScrollBars:=ssBoth;
   {$ENDIF}
   OutPutTxt :=viewSQL.Lines;
   with viewSQL do
   begin
     Font.Name:='Courier New';
     Font.Size:=10;
     Parent:=Panel1;
     Align:=alClient;
     SendToBack;

     OnKeyDown:=viewSQLKeyDown;
     OnKeyUp:=viewSQLKeyUp;
     OnMouseUp:=viewSQLMouseUp;
     OnDragOver:=EditorDragOver;
     OnDragDrop:=EditorDragDrop;
   end;
   with viewPlan do
   begin
     Font.Name:='Courier New';
     Font.Size:=10;
     Parent:=Panel1;
     Align:=alBottom;
     SendToBack;
     Visible:=False;
     Height:=80;
     PopupMenu:= menuPlan;
     ReadOnly:=True
   end;


   if FCanConnect then
   begin
     trTransaction.DefaultDatabase :=DataBase;
     PrepareDataSet(qryAllTables);
     PrepareDataSet(qrySPs);
     PrepareDataSet(qrySPparams);
     PrepareDataSet(qryTabFields);
     PrepareDataSet(qrySPFields);
     PrepareDataSet(qryTabFields1);
     PrepareDataSet(qryAllTables1);
     PrepareDataSet(qryAllGenerators)
   end;

  cmbTabsNameViews.Items.Add(SGenSQLView1);
  cmbTabsNameViews.Items.Add(SGenSQLView2);
  cmbTabsNameViews.Items.Add(SGenSQLView3);

  if FCanConnect then
  begin
    cmbTabsNameViews.ItemIndex:=1;
    cmbTabsNameViewsChange(cmbTabsNameViews);
  end
  else
  begin
   cmbTabsNameViews.ItemIndex:=0;
  end;
  cmbKindSQL.ItemIndex:=0;
  DBGrid1.Align:=alTop;
  Splitter1.Height:=3;
  cmbParamSymbol.ItemIndex:=0;
end;

{$IFDEF USE_SYN_EDIT}
{$IFNDEF  SYNEDIT_08_2001}
const
  CMPFORMAT = '\color{clBlue}%s \style{+B}\color{clBlack}%s\style{-B} ';

procedure TfSQLEdit.CompleteProposalExecute(Kind: SynCompletionType;
  Sender: TObject; var CurrentInput: String; var x, y: Integer;
  var CanExecute: Boolean);
var
   AliasName,TableName,FieldName:string;
   PointPos:integer;
   PointBracket:integer;
   PosIn:TSQLSections;
   InFromClause: boolean;
   ObjName:string;
   PosInText:integer;
   ts:TStrings;
   i:integer;
begin
  CompleteProposal.ClearList;
  if FCanConnect then
  begin 
   SetLength(PosIn,0);
   with CompleteProposal.Editor do
   begin
    PosInText:=(CaretX-Length(Lines[CaretY-1]));
    if PosInText>0 then
     PosInText:=SelStart-PosInText
    else
    PosInText:=SelStart ;
    PosIn:=PosInSections(Text,PosInText);
   end;

   InFromClause :=(Length(PosIn)>0) and
    (PosIn[Length(PosIn)-1]=stFrom);
   PointPos:=PosCh('.',CurrentInput);
   if PointPos=0 then
    PointBracket:=PosCh('(',CurrentInput)
   else
    PointBracket:=0;
   if (PointPos=0) and (PointBracket=0) and InFromClause then
   begin
    qryAllTables1.CloseOpen(False);
    if (Length(CurrentInput)>0) and (CurrentInput[1]='"') then
     CurrentInput:=Copy(CurrentInput,2,MaxInt);
    while not qryAllTables1.eof do
    begin
      if (CurrentInput='') or
       (PosCI(CurrentInput,qryAllTables1.Fields[0].AsString)=1)
      then
      begin
       case qryAllTables1.Fields[1].AsInteger of
        0:ObjName:='Table';
        1:ObjName:='View';
        2:ObjName:='Procedure';
       else
         ObjName:='Unknown object'; 
       end;

       if not NeedQuote(qryAllTables1.Fields[0].asString) then
        CompleteProposal.AddItem(
          Format(CMPFORMAT,[ObjName+' :', qryAllTables1.Fields[0].asString])
         ,
            qryAllTables1.Fields[0].asString
        )
       else
        CompleteProposal.AddItem(
          Format(CMPFORMAT,[ObjName+' :','"'+qryAllTables1.Fields[0].asString+'"'])
         ,
           '"'+ qryAllTables1.Fields[0].asString+'"'
        )


      end;

      qryAllTables1.Next;
    end;
   end
   else
   begin
     if PointBracket>0 then
     begin
      if FastUpperCase(Copy(CurrentInput,1,PointBracket-1))='GEN_ID' then
      begin
//         qryAllGenerators.Close;
         qryAllGenerators.Open;
         qryAllGenerators.First;
         while not qryAllGenerators.eof do
         begin
          ObjName:=FormatIdentifier(FDatabase.SQLDialect,qryAllGenerators.Fields[0].asString);
          CompleteProposal.AddItem(
            Format(CMPFORMAT,['Generator :',ObjName])
           ,
              'GEN_ID('+ ObjName+',1)'
          );
          qryAllGenerators.Next
         end;
      end;
     end;
//     else
   // Not in from clause
     if PointPos>0 then
     begin
       FieldName:=Copy(CurrentInput,PointPos+1,MaxInt);
       AliasName:=Copy(CurrentInput,1,PointPos-1);
       TableName:=TableByAlias(viewSQL.Text,AliasName);
       AliasName:=AliasName+'.';
       if (Length(TableName)>0) and (TableName[1]='"') then
        TableName:=Copy(TableName,2,Length(TableName)-2);
     end
     else