00001 {*********************************************************}
00002 { }
00003 { Zeos Database Objects }
00004 { SQL Monitor component }
00005 { }
00006 { Originally written by Sergey Seroukhov }
00007 { }
00008 {*********************************************************}
00009
00010 {@********************************************************}
00011 { Copyright (c) 1999-2006 Zeos Development Group }
00012 { }
00013 { License Agreement: }
00014 { }
00015 { This library is distributed in the hope that it will be }
00016 { useful, but WITHOUT ANY WARRANTY; without even the }
00017 { implied warranty of MERCHANTABILITY or FITNESS FOR }
00018 { A PARTICULAR PURPOSE. See the GNU Lesser General }
00019 { Public License for more details. }
00020 { }
00021 { The source code of the ZEOS Libraries and packages are }
00022 { distributed under the Library GNU General Public }
00023 { License (see the file COPYING / COPYING.ZEOS) }
00024 { with the following modification: }
00025 { As a special exception, the copyright holders of this }
00026 { library give you permission to link this library with }
00027 { independent modules to produce an executable, }
00028 { regardless of the license terms of these independent }
00029 { modules, and to copy and distribute the resulting }
00030 { executable under terms of your choice, provided that }
00031 { you also meet, for each linked independent module, }
00032 { the terms and conditions of the license of that module. }
00033 { An independent module is a module which is not derived }
00034 { from or based on this library. If you modify this }
00035 { library, you may extend this exception to your version }
00036 { of the library, but you are not obligated to do so. }
00037 { If you do not wish to do so, delete this exception }
00038 { statement from your version. }
00039 { }
00040 { }
00041 { The project web site is located on: }
00042 { http:
00043 { http:
00044 { svn:
00045 { }
00046 { http:
00047 { http:
00048 { }
00049 { }
00050 { }
00051 { Zeos Development Group. }
00052 {********************************************************@}
00053
00054 unit ZQuerySQLEditor;
00055
00056 {$I ZComponent.inc}
00057
00058 interface
00059
00060 uses
00061 {$IFDEF WIN32}
00062 Windows, Messages,
00063 {$ENDIF}
00064 {$IFDEF FPC}
00065 LCLIntf,
00066 {$ENDIF}
00067 SysUtils, Classes, Graphics, Controls, Forms, Dialogs, Graphtype,
00068 StdCtrls, ExtCtrls, ComCtrls, Buttons, ZDataset, Menus, ZMessages;
00069
00070
00071
00072
00073
00074
00075
00076
00077
00078 type
00079
00080 { TZQuerySQLEditorForm }
00081
00082 TZQuerySQLEditorForm = class(TForm)
00083 private
00084 mnuSave: TMenuItem;
00085 mnuLoad: TMenuItem;
00086 dlgOpen: TOpenDialog;
00087 PopupMenu1: TPopupMenu;
00088 dlgSave: TSaveDialog;
00089 btnOk: TBitBtn;
00090 btnCancel: TBitBtn;
00091 Panel2: TPanel;
00092 tbsSelect: TTabSheet;
00093 Panel1: TPanel;
00094 Splitter2: TSplitter;
00095 ListBoxRelations: TListBox;
00096 ListBoxFields: TListBox;
00097 Splitter1: TSplitter;
00098 Panel3: TPanel;
00099 lblAlias: TLabel;
00100 edtAlias: TEdit;
00101 btnGenerate: TButton;
00102 btnCheck: TButton;
00103 chkReplace: TCheckBox;
00104 btnTest: TButton;
00105 edtSelect: TMemo;
00106
00107
00108
00109
00110
00111
00112
00113
00114
00115
00116
00117 FDS: TZQuery;
00118 FSaveConnected: Boolean;
00119 procedure FormClose(Sender: TObject; var Action: TCloseAction);
00120 procedure ListBoxRelationsDblClick(Sender: TObject);
00121 procedure ListBoxRelationsSelectionChange(Sender: TObject; User: boolean);
00122 procedure PageControl1Change(Sender: TObject);
00123 procedure ZeosQuerySQLEditorDestroy(Sender: TObject);
00124 procedure btnGenerateClick(Sender: TObject);
00125 procedure btnTestClick(Sender: TObject);
00126 procedure mnuLoadClick(Sender: TObject);
00127 procedure mnuSaveClick(Sender: TObject);
00128 procedure LoadTableList;
00129 procedure FillIdentifier;
00130 function ActiveEditor:TMemo;
00131
00132
00133
00134
00135
00136
00137
00138
00139
00140
00141
00142
00143 public
00144 PageControl1: TPageControl;
00145 constructor CreateEditor(ADS:TZQuery);
00146 end;
00147
00148 var
00149 ZeosQuerySQLEditorForm: TZQuerySQLEditorForm;
00150
00151 implementation
00152 uses
00153 ZSqlTestForm, ZDbcIntfs, ZCompatibility;
00154
00155 const
00156 SQLDefaultFilter = 'SQL files (*.sql)|*.sql';
00157
00158 { TZeosROSQLEditor }
00159
00160 constructor TZQuerySQLEditorForm.CreateEditor(ADS: TZQuery);
00161 begin
00162 inherited CreateNew(Application,0);
00163
00164 Height := 613;
00165 Left := 315;
00166 Top := 142;
00167 TextHeight := 13;
00168 Width := 768;
00169 Position := poDesktopCenter;
00170 Caption := sFormEditor;
00171 OnClose := FormClose;
00172 OnDestroy := ZeosQuerySQLEditorDestroy;
00173 btnOk := TBitBtn.Create(self);
00174 with btnOk do
00175 begin
00176 Parent := self;
00177 Anchors := [akRight, akBottom];
00178 Default := True;
00179 Height := 25;
00180 Kind := bkOk;
00181 Left := 593;
00182 ModalResult := mrOk;
00183 TabOrder := 3;
00184 TabStop := True;
00185 Top := 552;
00186 Width := 75;
00187 Caption := SButtonOk;
00188 end;
00189 btnCancel := TBitBtn.Create(self);
00190 with btnCancel do
00191 begin
00192 Parent := self;
00193 Anchors := [akRight, akBottom];
00194 Default := True;
00195 Height := 25;
00196 Kind := bkCancel;
00197 Left := 677;
00198 ModalResult := mrCancel;
00199 TabOrder := 4;
00200 TabStop := True;
00201 Top := 552;
00202 Width := 75;
00203 Caption := SButtonCancel;
00204 end;
00205 btnGenerate := TButton.Create(self);
00206 with btnGenerate do
00207 begin
00208 Parent := self;
00209 Anchors := [akLeft,akBottom];
00210 Caption := SButtonGenerate;
00211 Height := 25;
00212 Left := 8;
00213 OnClick := btnGenerateClick;
00214 TabOrder := 0;
00215 TabStop := True;
00216 Top := 552;
00217 Width := 75;
00218 end;
00219 btnCheck := TButton.Create(self);
00220 with btnCheck do
00221 begin
00222 Parent := self;
00223 Anchors := [akLeft,akBottom];
00224 Caption := SButtonCheck;
00225 Enabled := False;
00226 Height := 25;
00227 Left := 88;
00228 TabOrder := 1;
00229 TabStop := True;
00230 Top := 552;
00231 Visible := False;
00232 Width := 75;
00233 end;
00234 btnTest := TButton.Create(self);
00235 with btnTest do
00236 begin
00237 Parent := self;
00238 Anchors := [akLeft,akBottom];
00239 Caption := SButtonTest;
00240 Height := 25;
00241 Left := 168;
00242 OnClick := btnTestClick;
00243 TabOrder := 2;
00244 TabStop := True;
00245 Top := 552;
00246 Width := 75;
00247 end;
00248 dlgOpen := TOpenDialog.Create(self);
00249 with dlgOpen do
00250 begin
00251 FilterIndex :=0;
00252 Options := [ofEnableSizing,ofViewDetail];
00253 Title := SDialogOpenTitle;
00254 Filter := SQLDefaultFilter;
00255 end;
00256 dlgSave := TSaveDialog.Create(self);
00257 with dlgSave do
00258 begin
00259 FilterIndex :=0;
00260 Options := [ofEnableSizing,ofViewDetail];
00261 Title := SDialogSaveTitle;
00262 Filter := SQLDefaultFilter;
00263 end;
00264 mnuLoad := TMenuItem.Create(self);
00265 with mnuLoad do
00266 begin
00267 Caption := SMenuLoad;
00268 OnClick := mnuLoadClick;
00269 end;
00270 mnuSave := TMenuItem.Create(self);
00271 with mnuSave do
00272 begin
00273 Caption := SMenuSave;
00274 OnClick := mnuSaveClick;
00275 end;
00276 PopupMenu1 := TPopupMenu.Create(self);
00277 with PopupMenu1 do
00278 begin
00279 AutoPopup := true;
00280 Items.Add(mnuLoad);
00281 Items.Add(mnuSave);
00282 end;
00283 Panel2 := TPanel.Create(self);
00284 with Panel2 do
00285 begin
00286 Parent := self;
00287 Align := alTop;
00288 Anchors := [akTop,akLeft,akRight,akBottom];
00289 FullRepaint := False;
00290 Height := 545;
00291 Left := 0;
00292 TabOrder := 5;
00293 TabStop := False;
00294 Top := 0;
00295 Width := 768;
00296 end;
00297 Splitter1 := TSplitter.Create(self);
00298 with Splitter1 do
00299 begin
00300 Parent := Panel2;
00301 Align := alRight;
00302 Anchors := [akTop,akRight,akBottom];
00303 Height := 543;
00304 Left := 518;
00305 Top := 1;
00306 Width := 5;
00307 end;
00308 PageControl1 := TPageControl.Create(self);
00309 with PageControl1 do
00310 begin
00311 Parent := Panel2;
00312 Align := alClient;
00313 Anchors := [akTop,akLeft,akRight,akBottom];
00314 Height := 543;
00315 Left := 1;
00316 OnChange := PageControl1Change;
00317 TabIndex := 0;
00318 TabOrder := 0;
00319 TabPosition := tpTop;
00320 TabStop := True;
00321 Top := 1;
00322 Width := 517;
00323 end;
00324 tbsSelect := TTabSheet.Create(self);
00325 with tbsSelect do
00326 begin
00327 Parent := PageControl1;
00328 Caption := STabSheetSelect;
00329 Height := 513;
00330 Left := 2;
00331 Top := 28;
00332 Width := 513;
00333 end;
00334 Panel1 := TPanel.Create(self);
00335 with Panel1 do
00336 begin
00337 Parent := Panel2;
00338 Align := alRight;
00339 Anchors := [akTop,akRight,akBottom];
00340 FullRepaint := false;
00341 Height := 543;
00342 Left := 523;
00343 TabOrder := 1;
00344 TabStop := False;
00345 Top := 1;
00346 Width := 244;
00347 end;
00348 Splitter2 := TSplitter.Create(self);
00349 with Splitter2 do
00350 begin
00351 Parent := Panel1;
00352 Align := alBottom;
00353 Anchors := [akLeft,akRight,akBottom];
00354 Cursor := crVSplit;
00355 Height := 9;
00356 Left := 1;
00357 Top := 271;
00358 Width := 242;
00359 end;
00360 ListBoxRelations := TListBox.Create(self);
00361 with ListBoxRelations do
00362 begin
00363 Parent := Panel1;
00364 Align := alClient;
00365 Anchors := [akTop,akLeft,akRight,akBottom];
00366 Height := 198;
00367 ItemHeight := 13;
00368 Left := 1;
00369 OnDblClick := ListBoxRelationsDblClick;
00370 OnSelectionChange := ListBoxRelationsSelectionChange;
00371 TabOrder := 0;
00372 Top := 73;
00373 Width := 242
00374 end;
00375 ListBoxFields := TListBox.Create(self);
00376 with ListBoxFields do
00377 begin
00378 Parent := Panel1;
00379 Align := alBottom;
00380 Anchors := [akLeft,akRight,akBottom];
00381 Height := 262;
00382 ItemHeight := 13;
00383 Left := 1;
00384 MultiSelect := True;
00385 TabOrder := 1;
00386 Top := 280;
00387 Width := 242;
00388 end;
00389 Panel3 := TPanel.Create(self);
00390 with Panel3 do
00391 begin
00392 Parent := Panel1;
00393 Align := alTop;
00394 Anchors := [akTop,akLeft,akRight,akBottom];
00395 BevelOuter := bvNone;
00396 FullRepaint := False;
00397 Height := 72;
00398 Left := 1;
00399 TabOrder := 2;
00400 TabStop := False;
00401 Top := 1;
00402 Width := 242;
00403 end;
00404 edtAlias := TEdit.Create(self);
00405 with edtAlias do
00406 begin
00407 Parent := Panel3;
00408 Anchors := [akTop,akLeft];
00409 Height := 21;
00410 Left := 80;
00411 TabOrder := 0;
00412 Top := 8;
00413 Width := 153;
00414 end;
00415 lblAlias := TLabel.Create(self);
00416 with lblAlias do
00417 begin
00418 Parent := Panel3;
00419 Anchors := [akTop, akLeft];
00420 Caption := STableAlias;
00421 FocusControl := edtAlias;
00422 Height := 13;
00423 Left := 8;
00424 Top := 16;
00425 Width := 68;
00426 end;
00427 chkReplace := TCheckBox.Create(self);
00428 with chkReplace do
00429 begin
00430 Parent := Panel3;
00431 Anchors := [akTop,akLeft];
00432 Caption := SReplaceSQL;
00433 Checked := True;
00434 Height := 17;
00435 Left := 8;
00436 TabOrder := 1;
00437 TabStop := True;
00438 Top := 40;
00439 Width := 97;
00440 end;
00441 edtSelect := TMemo.Create(self);
00442 with edtSelect do
00443 begin
00444 Parent := tbsSelect;
00445 Align := alClient;
00446 Anchors := [akTop,akLeft,akRight,akBottom];
00447 Font.Height := -14;
00448 {$IFDEF WIN32}
00449 Font.Name := 'Courier New';
00450 {$ENDIF}
00451 Font.Pitch := fpFixed;
00452 Height := 513;
00453 Left := 0;
00454 PopupMenu := PopupMenu1;
00455 TabOrder := 0;
00456 TabStop := True;
00457 Top := 0;
00458 Width := 513;
00459 end;
00460
00461
00462
00463
00464
00465
00466
00467
00468
00469
00470
00471
00472
00473
00474
00475
00476
00477
00478
00479
00480
00481
00482
00483
00484
00485
00486
00487
00488
00489
00490
00491
00492
00493
00494
00495
00496
00497
00498
00499
00500
00501
00502 FSaveConnected := True;
00503 FDS:=ADS;
00504 if Assigned(FDS) then
00505 begin
00506 if not FDS.Connection.Connected then
00507 begin
00508 FSaveConnected := False;
00509 FDS.Connection.Connected := True;
00510 end;
00511 edtSelect.Lines.Text:=FDS.SQL.Text;
00512 LoadTableList;
00513 FillIdentifier;
00514 end;
00515
00516 end;
00517
00518 procedure TZQuerySQLEditorForm.FillIdentifier;
00519 var
00520 i:integer;
00521 begin
00522
00523
00524
00525
00526
00527
00528
00529 end;
00530
00531 procedure TZQuerySQLEditorForm.FormClose(Sender: TObject;
00532 var Action: TCloseAction);
00533 begin
00534 if (ModalResult=mrOk) and Assigned(FDS) then
00535 begin
00536 FDS.SQL.Text:=edtSelect.Lines.Text;
00537 end;
00538 end;
00539
00540 procedure TZQuerySQLEditorForm.LoadTableList;
00541 var
00542 Metadata: IZDatabaseMetadata;
00543 TableTypes: TStringDynArray;
00544 begin
00545 if Assigned(FDS.Connection) and FDS.Connection.Connected then
00546 begin
00547 Metadata := FDS.Connection.DbcConnection.GetMetadata;
00548 SetLength(TableTypes, 2);
00549 TableTypes[0] := 'TABLE';
00550 TableTypes[1] := 'VIEW';
00551 FDS.Connection.ShowSQLHourGlass;
00552 ListBoxRelations.Items.Clear;
00553 with Metadata.GetTables(FDS.Connection.Catalog, '', '', TableTypes) do
00554 try
00555 while Next do
00556 if ListBoxRelations.Items.IndexOf(trim(GetString(3))) = -1 then
00557 ListBoxRelations.Items.Add(trim(GetString(3)));
00558 finally
00559 Close;
00560 end;
00561 FDS.Connection.HideSQLHourGlass;
00562 end;
00563 end;
00564
00565 function TZQuerySQLEditorForm.ActiveEditor:TMemo;
00566
00567
00568 begin
00569 case PageControl1.ActivePageIndex of
00570 0:Result:=edtSelect;
00571 end;
00572 end;
00573
00574 procedure TZQuerySQLEditorForm.ListBoxRelationsDblClick(
00575 Sender: TObject);
00576 var
00577 i:integer;
00578 Als, FieldsStr:string;
00579 begin
00580 if ActiveEditor<>nil then
00581 with ActiveEditor do
00582 begin
00583 if chkReplace.Checked then Lines.Clear;
00584 if ListBoxFields.SelCount=0 then
00585 begin
00586 Lines.Add('select');
00587 Lines.Add(' *');
00588 Lines.Add('from');
00589 Lines.Add(' '+ListBoxRelations.Items[ListBoxRelations.ItemIndex]+' '+edtAlias.Text);
00590 end
00591 else
00592 begin
00593 Lines.Add('select');
00594 if edtAlias.Text<>'' then Als:=edtAlias.Text
00595 else Als:=ListBoxRelations.Items[ListBoxRelations.ItemIndex];
00596 FieldsStr:='';
00597 for i:=0 to ListBoxFields.Items.Count-1 do
00598 begin
00599 if ListBoxFields.Selected[i] then
00600 begin
00601 if FieldsStr<>'' then
00602 Lines.Add(FieldsStr+',');
00603 FieldsStr:=' '+Als+'.'+ListBoxFields.Items[i];
00604 end;
00605 end;
00606 if FieldsStr<>'' then Lines.Add(FieldsStr);
00607 Lines.Add('from');
00608 Lines.Add(' '+ListBoxRelations.Items[ListBoxRelations.ItemIndex]+' '+edtAlias.Text);
00609 end;
00610 end;
00611 end;
00612
00613 procedure TZQuerySQLEditorForm.ListBoxRelationsSelectionChange(Sender: TObject;
00614 User: boolean);
00615 var
00616 Metadata: IZDatabaseMetadata;
00617 TPName: string;
00618 StrEsc: string;
00619 begin
00620 if Assigned(FDS.Connection) and FDS.Connection.Connected then
00621 begin
00622 FDS.Connection.ShowSQLHourGlass;
00623 Metadata := FDS.Connection.DbcConnection.GetMetadata;
00624 with ListBoxFields do begin
00625 ItemIndex := -1;
00626 Items.BeginUpdate;
00627 Items.Clear;
00628 Items.EndUpdate;
00629 end;
00630 if ListBoxRelations.ItemIndex >= 0 then
00631 begin
00632 TPName := ListBoxRelations.Items[ListBoxRelations.ItemIndex];
00633 with Metadata.GetColumns(FDS.Connection.Catalog,'', TPName,'') do
00634 try
00635 while Next do
00636 if ListBoxFields.Items.IndexOf(GetStringByName('COLUMN_NAME')) = -1 then
00637 ListBoxFields.Items.Add(GetStringByName('COLUMN_NAME'));
00638 finally
00639 Close;
00640 end;
00641 end;
00642 FDS.Connection.HideSQLHourGlass;
00643 end;
00644 end;
00645
00646 procedure TZQuerySQLEditorForm.PageControl1Change(Sender: TObject);
00647 begin
00648 case PageControl1.ActivePageIndex of
00649 0:edtSelect.SetFocus;
00650 end;
00651 end;
00652
00653 procedure TZQuerySQLEditorForm.ZeosQuerySQLEditorDestroy(Sender: TObject);
00654 begin
00655 if not FSaveConnected then
00656 FDS.Connection.Connected := False;
00657 end;
00658
00659 procedure TZQuerySQLEditorForm.btnGenerateClick(Sender: TObject);
00660 begin
00661 ListBoxRelationsDblClick(nil);
00662 end;
00663
00664 procedure TZQuerySQLEditorForm.btnTestClick(Sender: TObject);
00665 begin
00666 if Assigned(FDS.Connection) and FDS.Connection.Connected then
00667 begin
00668 ZeosSQLEditorTestForm:=TZeosSQLEditorTestForm.Create(Application);
00669 with ZeosSQLEditorTestForm do
00670 begin
00671 try
00672 ZeosSQL.Connection:=FDS.Connection;
00673 ZeosSQL.SQL.Text:=ActiveEditor.Lines.Text;
00674 ZeosSQL.Active := true;
00675 ShowModal
00676 except
00677 on E:Exception do
00678 ShowMessage(E.Message);
00679 end;
00680 Free;
00681 end;
00682 end;
00683 end;
00684
00685 procedure TZQuerySQLEditorForm.mnuLoadClick(Sender: TObject);
00686 begin
00687 if (dlgOpen.Execute) then
00688 ActiveEditor.Lines.LoadFromFile(dlgOpen.FileName);
00689 end;
00690
00691 procedure TZQuerySQLEditorForm.mnuSaveClick(Sender: TObject);
00692 begin
00693 if dlgSave.Execute then
00694 ActiveEditor.Lines.SaveToFile(dlgSave.FileName);
00695 end;
00696
00697
00698
00699
00700
00701
00702
00703
00704
00705
00706
00707
00708
00709
00710
00711
00712
00713
00714
00715
00716
00717
00718
00719
00720
00721
00722
00723
00724
00725
00726
00727
00728
00729
00730
00731
00732
00733
00734
00735
00736
00737
00738
00739
00740
00741
00742
00743
00744
00745
00746
00747
00748
00749
00750
00751
00752
00753
00754
00755
00756
00757
00758
00759
00760
00761
00762
00763
00764
00765
00766
00767
00768
00769 end.
00770