editorquery.pas

DBDesigner 4 is a database design system that integrates database design, modelling, creation and ma

unit EditorQuery;

//----------------------------------------------------------------------------------------------------------------------
//
// This file is part of fabFORCE DBDesigner4.
// Copyright (C) 2002 Michael G. Zinner, www.fabFORCE.net
//
// DBDesigner4 is free software; you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation; either version 2 of the License, or
// (at your option) any later version.
//
// DBDesigner4 is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.

// You should have received a copy of the GNU General Public License
// along with DBDesigner4; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
//
//----------------------------------------------------------------------------------------------------------------------
//
// Unit EditorTableData.pas
// ------------------------
// Version 1.1, 31.03.2003, Mike
// Description
//   Editor for table data
//
// Changes:
//   Version 1.1, 31.03.2003, Mike
//     Added 'Load SQL Script from File' Menu to the SQLMemo Popup
//   Version 1.0, 13.03.2003, Mike
//     initial version, Mike
//
//----------------------------------------------------------------------------------------------------------------------

{$I DBDesigner4.inc}

interface

uses
  SysUtils, Types, Classes, Variants, QTypes, QGraphics, QControls, QForms,
  QDialogs, QStdCtrls, FMTBcd, DB, DBClient, Provider, SqlExpr, QButtons,
  QExtCtrls, QDBCtrls, QGrids, QDBGrids, QComCtrls, EERModel, IniFiles,
  QMenus, QClipbrd, {$IFDEF USE_SYNEDIT}QSynEdit, QSynHighlighterSQL, {$ENDIF}
  {$IFDEF MSWINDOWS}Windows, ShellAPI, {$ENDIF}
  Qt, QImgList, EmbeddedPdfDB;

type
  TEditorQueryForm = class(TForm)
    TopPnl: TPanel;
    BottomPnl: TPanel;
    TableLbl: TLabel;
    OutputQry: TSQLQuery;
    OutputDataSetProvider: TDataSetProvider;
    OutputClientDataSet: TClientDataSet;
    OutputDataSrc: TDataSource;
    Label1: TLabel;
    DBConnEd: TEdit;
    GetDBConnSBtn: TSpeedButton;
    TableCBox: TComboBox;
    CommitPnl: TPanel;
    QueryDockPnl: TPanel;
    LeftPnl: TPanel;
    RightPnl: TPanel;
    QueryMainPnl: TPanel;
    BlobSplitter: TSplitter;
    SQLSplitter: TSplitter;
    BlobPnl: TPanel;
    BlobPageControl: TPageControl;
    ClearSheet: TTabSheet;
    TextSheet: TTabSheet;
    DBMemo: TDBMemo;
    ImgSheet: TTabSheet;
    DBImage: TDBImage;
    BlobSheet: TTabSheet;
    Splitter1: TSplitter;
    BlobMemo: TMemo;
    BlobHexMemo: TMemo;
    BlobFuncPnl: TPanel;
    BlobBtnPnl: TPanel;
    BlobClearBtn: TSpeedButton;
    BlobOpenBtn: TSpeedButton;
    StoreBlobBtn: TSpeedButton;
    SQLPnl: TPanel;
    SQLFuncPnl: TPanel;
    SQLMemo: TMemo;
    GridPnl: TPanel;
    DBGrid: TDBGrid;
    GridTopPnl: TPanel;
    GridFuncPnl: TPanel;
    SubmitBtn: TSpeedButton;
    CancelBtn: TSpeedButton;
    Bevel1: TBevel;
    Bevel2: TBevel;
    Bevel3: TBevel;
    DBNav: TDBNavigator;
    DBNavigator1: TDBNavigator;
    DBNavigator2: TDBNavigator;
    DBNavigator3: TDBNavigator;
    SaveSQLBtn: TSpeedButton;
    ExecSQLBtn: TSpeedButton;
    SQLHeaderPnl: TPanel;
    SQLMemoPopupMenu: TPopupMenu;
    UndoMI: TMenuItem;
    RedoMI: TMenuItem;
    N1: TMenuItem;
    CopyMI: TMenuItem;
    CutMI: TMenuItem;
    PasteMI: TMenuItem;
    ClearMI: TMenuItem;
    N2: TMenuItem;
    SelectAllMI: TMenuItem;
    StoredSQLPnl: TPanel;
    StoredSQLSplitter: TSplitter;
    StoredSQLTreeView: TTreeView;
    StoredSQLHeaderPnl: TPanel;
    Splitter2: TSplitter;
    StoredSQLImageList: TImageList;
    StoredSQLMemoPnl: TPanel;
    StoredSQLFuncPnl: TPanel;
    StoredSQLEditBtn: TSpeedButton;
    StoredSQLExecuteBtn: TSpeedButton;
    Bevel6: TBevel;
    StoredSQLMemo: TMemo;
    StoredSQLBtn: TSpeedButton;
    StoredSQLPopupMenu: TPopupMenu;
    DeleteSQLCommandMI: TMenuItem;
    ExecuteSQLCommandMI: TMenuItem;
    EditSQLCommandsMI: TMenuItem;
    N3: TMenuItem;
    N4: TMenuItem;
    RefreshStoredSQLTreeMI: TMenuItem;
    Bevel7: TBevel;
    DBGridPopupMenu: TPopupMenu;
    ExportallrecordsMI: TMenuItem;
    Panel1: TPanel;
    HideBLOBImg: TImage;
    ShowBLOBImg: TImage;
    CopyforASPMI: TMenuItem;
    CopyforKylixDelphiMI: TMenuItem;
    CopyforPHPMI: TMenuItem;
    N5: TMenuItem;
    PasteremovingQuotesMI: TMenuItem;
    ShowSQLImg: TImage;
    HideSQLImg: TImage;
    Bevel8: TBevel;
    CopytoClipboardMI: TMenuItem;
    CopyallRecordsMI: TMenuItem;
    N6: TMenuItem;
    CopyFieldNames1: TMenuItem;
    CopyallRecordsasINSERTsMI: TMenuItem;
    N7: TMenuItem;
    LoadSQLScriptfromFileMI: TMenuItem;
    PrevCmd: TSpeedButton;
    NextCmd: TSpeedButton;
    SwitchQueryLayoutBtn: TSpeedButton;
    Bevel4: TBevel;
    TempSQLStorePnl: TPanel;
    Bevel5: TBevel;
    TempSQLStore1SBtn: TSpeedButton;
    TempSQLStore2SBtn: TSpeedButton;
    TempSQLStore3SBtn: TSpeedButton;
    TempSQLStore4SBtn: TSpeedButton;
    Bevel9: TBevel;
    TempSQLStore5SBtn: TSpeedButton;
    TempSQLStore6SBtn: TSpeedButton;
    TempSQLStore7SBtn: TSpeedButton;
    TempSQLStore8SBtn: TSpeedButton;
    PrintRecordstoPDFMI: TMenuItem;
    N8: TMenuItem;
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);

    function SetTable(theTable: TEERTable): Boolean;
    procedure ApplyChanges;
    procedure GetDBConnSBtnClick(Sender: TObject);
    procedure TableCBoxChange(Sender: TObject);
    procedure SubmitBtnClick(Sender: TObject);
    procedure CancelBtnClick(Sender: TObject);
    procedure DBGridColEnter(Sender: TObject);
    procedure FormResize(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure BlobClearBtnClick(Sender: TObject);
    procedure BlobOpenBtnClick(Sender: TObject);
    procedure StoreBlobBtnClick(Sender: TObject);
    procedure DBGridDblClick(Sender: TObject);
    procedure SaveSQLBtnClick(Sender: TObject);
    procedure ExecSQLBtnClick(Sender: TObject);

    procedure SizeGridCols;

    procedure ProcessKey(Key: Word; col: TEERColumn);

    procedure AddTableToSQLCommand(theTable: TEERTable; SQLCmdType, SQLCmdSelectJoinType: integer);
    procedure AddColumnToSQLCommand(columnPos: integer; col: TEERColumn);

    procedure UndoMIShow(Sender: TObject);
    procedure RedoMIShow(Sender: TObject);
    procedure UndoMIClick(Sender: TObject);
    procedure RedoMIClick(Sender: TObject);
    procedure CopyMIClick(Sender: TObject);
    procedure CutMIClick(Sender: TObject);
    procedure PasteMIClick(Sender: TObject);
    procedure CopyMIShow(Sender: TObject);
    procedure PasteMIShow(Sender: TObject);
    procedure ClearMIClick(Sender: TObject);
    procedure SelectAllMIClick(Sender: TObject);
    procedure SelectAllMIShow(Sender: TObject);

    procedure GetClauses(s: string; Clauses: TStringList);
    function GetClause(s, clause: string; Clauses: TStringList): string;
    procedure DBGridDragOver(Sender, Source: TObject; X, Y: Integer;
      State: TDragState; var Accept: Boolean);
    procedure StoredSQLBtnClick(Sender: TObject);

    procedure RefreshStoredSQLTreeView(theModel: TEERModel; ExpandedNodesList: TStringList = nil);
    procedure StoredSQLTreeViewChange(Sender: TObject; Node: TTreeNode);
    procedure StoredSQLTreeViewCustomDrawItem(Sender: TCustomViewControl;
      Item: TCustomViewItem; Canvas: TCanvas; const Rect: TRect;
      State: TCustomDrawState; Stage: TCustomDrawStage;
      var DefaultDraw: Boolean);
    procedure StoredSQLEditBtnClick(Sender: TObject);
    procedure StoredSQLExecuteBtnClick(Sender: TObject);
    procedure StoredSQLTreeViewEdited(Sender: TObject; Node: TTreeNode;
      var S: WideString);
    procedure StoredSQLTreeViewEditing(Sender: TObject; Node: TTreeNode;
      var AllowEdit: Boolean);
    procedure DeleteSQLCommandMIShow(Sender: TObject);
    procedure DeleteSQLCommandMIClick(Sender: TObject);
    procedure RefreshStoredSQLTreeMIClick(Sender: TObject);
    procedure DBGridDragDrop(Sender, Source: TObject; X, Y: Integer);
    procedure OutputDataSetProviderUpdateError(Sender: TObject;
      DataSet: TCustomClientDataSet; E: EUpdateError;
      UpdateKind: TUpdateKind; var Response: TResolverResponse);
    procedure ExportAllRecords(fname: string);
    procedure ExportallrecordsMIClick(Sender: TObject);
    procedure ShowSQLImgClick(Sender: TObject);
    procedure HideSQLImgClick(Sender: TObject);
    procedure ShowBLOBImgClick(Sender: TObject);
    procedure HideBLOBImgClick(Sender: TObject);
    procedure SQLMemoMouseMove(Sender: TObject; Shift: TShiftState; X,
      Y: Integer);
    procedure StoredSQLTreeViewDragOver(Sender, Source: TObject; X,
      Y: Integer; State: TDragState; var Accept: Boolean);
    procedure StoredSQLTreeViewDragDrop(Sender, Source: TObject; X,
      Y: Integer);

    procedure CopySQLToClipboard(Mode: Word);
    procedure CopyforASPMIClick(Sender: TObject);
    procedure CopyallRecordsMIClick(Sender: TObject);
    procedure CopytoClipboardMIShow(Sender: TObject);
    procedure CopyFieldNames1Click(Sender: TObject);
    procedure CopyallRecordsasINSERTsMIClick(Sender: TObject);
    procedure FormDeactivate(Sender: TObject);
    procedure PasteremovingQuotesMIClick(Sender: TObject);
    procedure FormKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure LoadSQLScriptfromFileMIClick(Sender: TObject);
    procedure PrevCmdClick(Sender: TObject);
    procedure SwitchQueryLayoutBtnClick(Sender: TObject);
    procedure TempSQLStore1SBtnDragOver(Sender, Source: TObject; X,
      Y: Integer; State: TDragState; var Accept: Boolean);
    procedure TempSQLStore1SBtnDragDrop(Sender, Source: TObject; X,
      Y: Integer);
    procedure TempSQLStore1SBtnClick(Sender: TObject);
    procedure RefreshTempSQLStoreBtns(theModel: TEERModel);
    procedure TempSQLStore1SBtnMouseEnter(Sender: TObject);
    procedure StoredSQLTreeViewItemEnter(Sender: TObject; Node: TTreeNode);

    procedure SetLayout(Layout: integer; StoreCurrentLayoutSettings: Boolean = True);
    procedure StoreLayout(Layout: integer);
    procedure StoredSQLTreeViewMouseMove(Sender: TObject;
      Shift: TShiftState; X, Y: Integer);
    procedure SQLMemoDragOver(Sender, Source: TObject; X, Y: Integer;
      State: TDragState; var Accept: Boolean);
    procedure SQLMemoDragDrop(Sender, Source: TObject; X, Y: Integer);
    procedure StoredSQLTreeViewItemExitViewportEnter(Sender: TObject);
    procedure DoHintPauseTmr(Sender: TObject);
    procedure TempSQLStore1SBtnMouseMove(Sender: TObject;
      Shift: TShiftState; X, Y: Integer);
    procedure StoredSQLPopupMenuPopup(Sender: TObject);
    procedure StoredSQLSplitterMoved(Sender: TObject);
    procedure DBGridDrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumn; State: TGridDrawState);
    procedure OutputClientDataSetAfterOpen(DataSet: TDataSet);
    procedure DoFieldGetText(Sender: TField; var Text: String; DisplayText: Boolean);

    procedure SetSQLMemoText(Text: string);
    function GetSQLMemoText: string;
    procedure PrintRecordstoPDFMIClick(Sender: TObject);
  private
    { Private declarations }
    theEERTable: TEERTable;
    theEERModel: TEERModel;

    //Used to track Items.Clear in onChange (might be a Delphi bug)
    ClearingStoredSQLTreeView: Boolean;

    currentSQLText: string;
    currentHistoryPos: integer;

    theHintWindow: THintWindow;
    theHintPauseTmr: TTimer;
  public
    { Public declarations }
{$IFDEF USE_SYNEDIT}
    SQLSynEdit: TSynEdit;
    SQLSynEditHighlighter: TSynSQLSyn;
{$ENDIF}
  end;

  const
    //Column Positions
    cpSelectClause=0;
    cpFromClause=1;
    cpWhereClause=2;
    cpGroupClause=3;
    cpHavingClause=4;
    cpOrderClause=5;

    cpSetClause=1;
    cpDelWhereClause=1;

    //SQL Command Types
    SQLctSELECT=0;
    SQLctUPDATE=1;
    SQLctINSERT=2;
    SQLctDELETE=3;

    //SQL Command Select Join Types
    SQLjtNONE=-1;
    SQLjtINNER=0;
    SQLjtLEFTOUTER=1;
    SQLjtRIGHTOUTER=2;

var
  EditorQueryForm: TEditorQueryForm;

implementation

uses DBDM, MainDM, GUIDM, EERDM, EER;

{$R *.xfm}

procedure TEditorQueryForm.FormCreate(Sender: TObject);
begin
  DMMain.InitForm(self, true);

  theEERModel:=nil;
  theEERTable:=nil;

  OutputQry.SQLConnection:=DMDB.SQLConn;
  BlobPageControl.Style:=tsNoTabs;

  BlobHexMemo.Text:='';

  SQLMemo.Lines.Clear;
  SQLMemo.PopupMenu:=SQLMemoPopupMenu;

  SQLMemo.Font.Name:=DMGUI.SQLTextFont;
  SQLMemo.Font.Size:=DMGUI.SQLTextFontSize;

  ClearingStoredSQLTreeView:=False;

  //Create the manual Hint Window
  theHintWindow:=THintWindow.Create(nil);
  theHintWindow.Color:=Application.HintColor;
  theHintPauseTmr:=TTimer.Create(nil);
  theHintPauseTmr.Interval:=300;
  theHintPauseTmr.Enabled:=False;
  theHintPauseTmr.OnTimer:=DoHintPauseTmr;

  StoredSQLTreeView.Columns[0].Caption:=
    DMMain.GetTranslatedMessage('Stored SQL Commands', 84);

{$IFDEF LINUX}
  DBGrid.Options:=DBGrid.Options + [dgAlwaysShowEditor];
{$ENDIF}

{$IFDEF USE_SYNEDIT}
  SQLSynEditHighlighter:=TSynSQLSyn.Create(self);
  SQLSynEditHighlighter.SQLDialect:=sqlMySQL;
  SQLSynEditHighlighter.KeyAttri.Foreground:=clBlue;

  SQLSynEdit:=TSynEdit.Create(self);
  SQLSynEdit.Parent:=SQLPnl;
  SQLSynEdit.Name:='SQLSynEdit';

  SQLSynEdit.Highlighter:=SQLSynEditHighlighter;
  SQLSynEdit.ScrollBars:=ssAutoBoth;
  SQLSynEdit.Gutter.Visible:=False;
{$IFDEF LINUX}
  SQLSynEdit.Font.Size:=12;
{$ELSE}
  SQLSynEdit.Font.Size:=9;
{$ENDIF}
  SQLSynEdit.Options:=[eoAutoIndent,
    eoEnhanceHomeKey, eoGroupUndo,
    eoShowScrollHint, eoScrollHintFollows,
    eoSmartTabs, eoTabsToSpaces,
    eoSmartTabDelete, eoHideShowScrollbars,
    eoTabsToSpaces{, eoHighlightCurrentLine}];

  SQLSynEdit.OnMouseMove:=SQLMemoMouseMove;
  SQLSynEdit.OnDragDrop:=SQLMemoDragDrop;
  SQLSynEdit.OnDragOver:=SQLMemoDragOver;
  SQLSynEdit.PopupMenu:=SQLMemoPopupMenu;

  SQLSynEdit.Align:=alClient;

  //Only use SynEdit when requested, otherwise set nil