ViewVC Help
View File | Revision Log | Show Annotations | Download File | View Changeset | Root Listing
root/public/ibx/trunk/design/ibsqleditor.pas
Revision: 17
Committed: Sat Dec 28 19:22:24 2013 UTC (6 years, 6 months ago) by tony
File size: 12716 byte(s)
Log Message:
Committing updates for Release R1-0-5
Line File contents
1 (*
2 * IBX For Lazarus (Firebird Express)
3 *
4 * The contents of this file are subject to the Initial Developer's
5 * Public License Version 1.0 (the "License"); you may not use this
6 * file except in compliance with the License. You may obtain a copy
7 * of the License here:
8 *
9 * http://www.firebirdsql.org/index.php?op=doc&id=idpl
10 *
11 * Software distributed under the License is distributed on an "AS
12 * IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
13 * implied. See the License for the specific language governing rights
14 * and limitations under the License.
15 *
16 * The Initial Developer of the Original Code is Tony Whyman.
17 *
18 * The Original Code is (C) 2011 Tony Whyman, MWA Software
19 * (http://www.mwasoftware.co.uk).
20 *
21 * All Rights Reserved.
22 *
23 * Contributor(s): ______________________________________.
24 *
25 *)
26 unit IBSQLEditor;
27
28 {$mode objfpc}{$H+}
29
30 interface
31
32 uses
33 Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs,
34 StdCtrls, ExtCtrls, ComCtrls, IBSystemTables, IBSQL, IBDatabase;
35
36 type
37
38 { TIBSQLEditorForm }
39
40 TIBSQLEditorForm = class(TForm)
41 Button1: TButton;
42 Button2: TButton;
43 GenerateBtn: TButton;
44 IBTransaction1: TIBTransaction;
45 Label5: TLabel;
46 SelectProcedure: TLabel;
47 TestBtn: TButton;
48 IncludePrimaryKeys: TCheckBox;
49 Label1: TLabel;
50 Label10: TLabel;
51 Label11: TLabel;
52 Label12: TLabel;
53 Label14: TLabel;
54 Label15: TLabel;
55 Label16: TLabel;
56 Label17: TLabel;
57 Label18: TLabel;
58 Label2: TLabel;
59 Label3: TLabel;
60 Label4: TLabel;
61 Label8: TLabel;
62 Label9: TLabel;
63 SelectFieldsList: TListBox;
64 ProcOutputList: TListBox;
65 SelectPrimaryKeys: TListBox;
66 InsertFieldsList: TListBox;
67 ModifyFieldsList: TListBox;
68 ModifyPrimaryKeys: TListBox;
69 DeletePrimaryKeys: TListBox;
70 ProcInputList: TListBox;
71 PageControl: TPageControl;
72 QuoteFields: TCheckBox;
73 SQLText: TMemo;
74 SelectPage: TTabSheet;
75 InsertPage: TTabSheet;
76 ModifyPage: TTabSheet;
77 DeletePage: TTabSheet;
78 ExecutePage: TTabSheet;
79 SelectTableNames: TComboBox;
80 InsertTableNames: TComboBox;
81 ModifyTableNames: TComboBox;
82 DeleteTableNames: TComboBox;
83 ProcedureNames: TComboBox;
84 procedure GenerateBtnClick(Sender: TObject);
85 procedure TestBtnClick(Sender: TObject);
86 procedure DeletePageShow(Sender: TObject);
87 procedure DeleteTableNamesCloseUp(Sender: TObject);
88 procedure ExecutePageShow(Sender: TObject);
89 procedure FormShow(Sender: TObject);
90 procedure IncludePrimaryKeysClick(Sender: TObject);
91 procedure InsertPageShow(Sender: TObject);
92 procedure Label13Click(Sender: TObject);
93 procedure ModifyPageShow(Sender: TObject);
94 procedure ModifyTableNamesCloseUp(Sender: TObject);
95 procedure ProcedureNamesCloseUp(Sender: TObject);
96 procedure SelectFieldsListDblClick(Sender: TObject);
97 procedure SelectPageShow(Sender: TObject);
98 procedure SelectTableNamesCloseUp(Sender: TObject);
99 procedure InsertTableNamesCloseUp(Sender: TObject);
100 private
101 { private declarations }
102 FTableName: string;
103 FIBSystemTables: TIBSystemTables;
104 FExecuteOnly: boolean;
105 public
106 { public declarations }
107 constructor Create(TheOwner: TComponent); override;
108 destructor Destroy; override;
109 procedure SetDatabase(Database: TIBDatabase);
110 end;
111
112 var
113 IBSQLEditorForm: TIBSQLEditorForm;
114
115 function EditIBSQL(DataSet: TIBSQL): boolean;
116
117 implementation
118
119 {$R *.lfm}
120
121 uses InterfaceBase;
122
123 function EditIBSQL(DataSet: TIBSQL): boolean;
124 begin
125 Result := false;
126 if assigned(DataSet.Database) then
127 try
128 DataSet.Database.Connected := true;
129 except on E: Exception do
130 ShowMessage(E.Message)
131 end;
132
133 with TIBSQLEditorForm.Create(Application) do
134 try
135 SetDatabase(DataSet.Database);
136 SQLText.Lines.Assign(DataSet.SQL);
137 Result := ShowModal = mrOK;
138 if Result then
139 DataSet.SQL.Assign(SQLText.Lines)
140 finally
141 Free
142 end;
143
144 end;
145
146 { TIBSQLEditorForm }
147
148 procedure TIBSQLEditorForm.FormShow(Sender: TObject);
149 var IsProcedureName: boolean;
150 begin
151 if WidgetSet.LCLPlatform = lpGtk2 then
152 PageControl.TabPosition := tpLeft
153 else
154 PageControl.TabPosition := tpTop;
155 GenerateBtn.Enabled := (IBTransaction1.DefaultDatabase <> nil) and IBTransaction1.DefaultDatabase.Connected;
156 TestBtn.Enabled := (IBTransaction1.DefaultDatabase <> nil) and IBTransaction1.DefaultDatabase.Connected;
157 if Trim(SQLText.Text) <> '' then
158 begin
159 case FIBSystemTables.GetStatementType(SQLText.Text,IsProcedureName) of
160 SQLSelect:
161 if IsProcedureName then
162 PageControl.ActivePage := ExecutePage
163 else
164 PageControl.ActivePage := SelectPage;
165 SQLInsert: PageControl.ActivePage := InsertPage;
166 SQLUpdate: PageControl.ActivePage := ModifyPage;
167 SQLDelete: PageControl.ActivePage := DeletePage;
168 SQLExecProcedure: PageControl.ActivePage := ExecutePage;
169 else
170 PageControl.ActivePage := SelectPage;
171 end;
172 FIBSystemTables.GetTableAndColumns(SQLText.Text,FTableName,nil)
173 end;
174 end;
175
176 procedure TIBSQLEditorForm.IncludePrimaryKeysClick(Sender: TObject);
177 begin
178 FIBSystemTables.GetFieldNames(ModifyTableNames.Text,ModifyFieldsList.Items,IncludePrimaryKeys.checked);
179 FIBSystemTables.GetPrimaryKeys(ModifyTableNames.Text,ModifyPrimaryKeys.Items);
180 end;
181
182 procedure TIBSQLEditorForm.DeletePageShow(Sender: TObject);
183 var TableName: string;
184 begin
185 FIBSystemTables.GetTableNames(DeleteTableNames.Items);
186 if Trim(SQLText.Text) = '' then
187 begin
188 if FTableName <> '' then
189 DeleteTableNames.ItemIndex := DeleteTableNames.Items.IndexOf(FTableName)
190 else
191 if DeleteTableNames.Items.Count > 0 then
192 DeleteTableNames.ItemIndex := 0
193 end
194 else
195 begin
196 FIBSystemTables.GetTableAndColumns(SQLText.Text,TableName,nil);
197 DeleteTableNames.ItemIndex := DeleteTableNames.Items.IndexOf(TableName);
198 end;
199 FIBSystemTables.GetPrimaryKeys(DeleteTableNames.Text,DeletePrimaryKeys.Items);
200
201 end;
202
203 procedure TIBSQLEditorForm.GenerateBtnClick(Sender: TObject);
204 var FieldNames: TStrings;
205 begin
206 FieldNames := nil;
207 if PageControl.ActivePage = SelectPage then
208 begin
209 FieldNames := FIBSystemTables.GetFieldNames(SelectFieldsList);
210 FIBSystemTables.GenerateSelectSQL(SelectTableNames.Text,QuoteFields.Checked,FieldNames,SQLText.Lines);
211 end
212 else
213 if PageControl.ActivePage = InsertPage then
214 begin
215 FieldNames := FIBSystemTables.GetFieldNames(InsertFieldsList);
216 FIBSystemTables.GenerateInsertSQL(InsertTableNames.Text,QuoteFields.Checked,FieldNames,SQLText.Lines);
217 end
218 else
219 if PageControl.ActivePage = ModifyPage then
220 begin
221 FieldNames := FIBSystemTables.GetFieldNames(ModifyFieldsList);
222 FIBSystemTables.GenerateModifySQL(ModifyTableNames.Text,QuoteFields.Checked,FieldNames,SQLText.Lines);
223 end
224 else
225 if PageControl.ActivePage = DeletePage then
226 FIBSystemTables.GenerateDeleteSQL(DeleteTableNames.Text,QuoteFields.Checked,SQLText.Lines)
227 else
228 if PageControl.ActivePage = ExecutePage then
229 FIBSystemTables.GenerateExecuteSQL(ProcedureNames.Text,QuoteFields.Checked, FExecuteOnly,
230 ProcInputList.Items,ProcOutputList.Items,SQLText.Lines);
231
232 if FieldNames <> nil then
233 FieldNames.Free
234 end;
235
236 procedure TIBSQLEditorForm.TestBtnClick(Sender: TObject);
237 begin
238 FIBSystemTables.TestSQL(SQLText.Text);
239 end;
240
241 procedure TIBSQLEditorForm.DeleteTableNamesCloseUp(Sender: TObject);
242 begin
243 FTableName := DeleteTableNames.Text;
244 FIBSystemTables.GetPrimaryKeys(DeleteTableNames.Text,DeletePrimaryKeys.Items);
245 end;
246
247 procedure TIBSQLEditorForm.ExecutePageShow(Sender: TObject);
248 var ProcName: string;
249 IsProcedureName: boolean;
250 begin
251 FIBSystemTables.GetProcedureNames(ProcedureNames.Items);
252 if ProcedureNames.Items.Count > 0 then
253 begin
254 if (FIBSystemTables.GetStatementType(SQLText.Text,IsProcedureName) = SQLExecProcedure) or IsProcedureName then
255 begin
256 FIBSystemTables.GetTableAndColumns(SQLText.Text,ProcName,nil);
257 ProcedureNames.ItemIndex := ProcedureNames.Items.IndexOf(ProcName)
258 end
259 else
260 ProcedureNames.ItemIndex := 0;
261 end;
262 FIBSystemTables.GetProcParams(ProcedureNames.Text,FExecuteOnly,ProcInputList.Items,ProcOutputList.Items);
263 SelectProcedure.Visible := not FExecuteOnly;
264 end;
265
266 procedure TIBSQLEditorForm.InsertPageShow(Sender: TObject);
267 var TableName: string;
268 begin
269 FIBSystemTables.GetTableNames(InsertTableNames.Items);
270 if Trim(SQLText.Text) = '' then
271 begin
272 if FTableName <> '' then
273 InsertTableNames.ItemIndex := InsertTableNames.Items.IndexOf(FTableName)
274 else
275 if InsertTableNames.Items.Count > 0 then
276 InsertTableNames.ItemIndex := 0
277 end
278 else
279 begin
280 FIBSystemTables.GetTableAndColumns(SQLText.Text,TableName,nil);
281 InsertTableNames.ItemIndex := InsertTableNames.Items.IndexOf(TableName);
282 end;
283 FIBSystemTables.GetFieldNames(InsertTableNames.Text,InsertFieldsList.Items);
284
285 end;
286
287 procedure TIBSQLEditorForm.Label13Click(Sender: TObject);
288 begin
289 FIBSystemTables.GetFieldNames(ModifyTableNames.Text,ModifyFieldsList.Items,IncludePrimaryKeys.checked);
290 FIBSystemTables.GetPrimaryKeys(ModifyTableNames.Text,ModifyPrimaryKeys.Items);
291 end;
292
293 procedure TIBSQLEditorForm.ModifyPageShow(Sender: TObject);
294 var TableName: string;
295 begin
296 FIBSystemTables.GetTableNames(ModifyTableNames.Items);
297 if Trim(SQLText.Text) = '' then
298 begin
299 if FTableName <> '' then
300 ModifyTableNames.ItemIndex := ModifyTableNames.Items.IndexOf(FTableName)
301 else
302 if ModifyTableNames.Items.Count > 0 then
303 ModifyTableNames.ItemIndex := 0;
304 end
305 else
306 begin
307 FIBSystemTables.GetTableAndColumns(SQLText.Text,TableName,nil);
308 ModifyTableNames.ItemIndex := ModifyTableNames.Items.IndexOf(TableName);
309 end;
310 FIBSystemTables.GetFieldNames(ModifyTableNames.Text,ModifyFieldsList.Items,IncludePrimaryKeys.checked,false);
311 FIBSystemTables.GetPrimaryKeys(ModifyTableNames.Text,ModifyPrimaryKeys.Items);
312 end;
313
314 procedure TIBSQLEditorForm.ModifyTableNamesCloseUp(Sender: TObject);
315 begin
316 FTableName := ModifyTableNames.Text;
317 FIBSystemTables.GetFieldNames(ModifyTableNames.Text,ModifyFieldsList.Items,IncludePrimaryKeys.checked,false);
318 FIBSystemTables.GetPrimaryKeys(ModifyTableNames.Text,ModifyPrimaryKeys.Items);
319 end;
320
321 procedure TIBSQLEditorForm.ProcedureNamesCloseUp(Sender: TObject);
322 begin
323 FIBSystemTables.GetProcParams(ProcedureNames.Text,FExecuteOnly,ProcInputList.Items,ProcOutputList.Items);
324 SelectProcedure.Visible := not FExecuteOnly
325 end;
326
327 procedure TIBSQLEditorForm.SelectFieldsListDblClick(Sender: TObject);
328 begin
329 SQLText.SelText:= (Sender as TListBox).Items[(Sender as TListBox).ItemIndex];
330 end;
331
332 procedure TIBSQLEditorForm.SelectPageShow(Sender: TObject);
333 var TableName: string;
334 begin
335 FIBSystemTables.GetTableNames(SelectTableNames.Items);
336 if Trim(SQLText.Text) = '' then
337 begin
338 if FTableName <> '' then
339 SelectTableNames.ItemIndex := SelectTableNames.Items.IndexOf(FTableName)
340 else
341 if SelectTableNames.Items.Count > 0 then
342 SelectTableNames.ItemIndex := 0;
343 end
344 else
345 begin
346 FIBSystemTables.GetTableAndColumns(SQLText.Text,TableName,nil);
347 SelectTableNames.ItemIndex := SelectTableNames.Items.IndexOf(TableName);
348 end;
349 FIBSystemTables.GetFieldNames(SelectTableNames.Text,SelectFieldsList.Items);
350 FIBSystemTables.GetPrimaryKeys(SelectTableNames.Text,SelectPrimaryKeys.Items);
351 end;
352
353 procedure TIBSQLEditorForm.SelectTableNamesCloseUp(Sender: TObject);
354 begin
355 FTableName := SelectTableNames.Text;
356 try
357 FIBSystemTables.GetFieldNames(SelectTableNames.Text,SelectFieldsList.Items);
358 FIBSystemTables.GetPrimaryKeys(SelectTableNames.Text,SelectPrimaryKeys.Items);
359 except {ignore} end;
360 end;
361
362 procedure TIBSQLEditorForm.InsertTableNamesCloseUp(Sender: TObject);
363 begin
364 FTableName := InsertTableNames.Text;
365 FIBSystemTables.GetFieldNames(InsertTableNames.Text,InsertFieldsList.Items);
366 end;
367
368 constructor TIBSQLEditorForm.Create(TheOwner: TComponent);
369 begin
370 inherited Create(TheOwner);
371 FIBSystemTables := TIBSystemTables.Create;
372 end;
373
374 destructor TIBSQLEditorForm.Destroy;
375 begin
376 if assigned(FIBSystemTables) then FIBSystemTables.Free;
377 inherited Destroy;
378 end;
379
380 procedure TIBSQLEditorForm.SetDatabase(Database: TIBDatabase);
381 begin
382 IBTransaction1.DefaultDatabase := Database;
383 FIBSystemTables.SelectDatabase(Database,IBTransaction1)
384 end;
385
386 end.
387