ViewVC Help
View File | Revision Log | Show Annotations | Download File | View Changeset | Root Listing
root/public/ibx/trunk/design/ibsqleditor.pas
Revision: 33
Committed: Sat Jul 18 12:30:52 2015 UTC (8 years, 8 months ago) by tony
Content type: text/x-pascal
File size: 12535 byte(s)
Log Message:
Committing updates for Release R1-3-1

File Contents

# Content
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 GenerateParams: TCheckBox;
44 GenerateBtn: TButton;
45 IBTransaction1: TIBTransaction;
46 Label5: TLabel;
47 SelectProcedure: TLabel;
48 TestBtn: TButton;
49 IncludePrimaryKeys: TCheckBox;
50 Label1: TLabel;
51 Label10: TLabel;
52 Label11: TLabel;
53 Label12: TLabel;
54 Label14: TLabel;
55 Label15: TLabel;
56 Label16: TLabel;
57 Label17: TLabel;
58 Label18: TLabel;
59 Label2: TLabel;
60 Label3: TLabel;
61 Label4: TLabel;
62 Label8: TLabel;
63 Label9: TLabel;
64 SelectFieldsList: TListBox;
65 ProcOutputList: TListBox;
66 SelectPrimaryKeys: TListBox;
67 InsertFieldsList: TListBox;
68 ModifyFieldsList: TListBox;
69 ModifyPrimaryKeys: TListBox;
70 DeletePrimaryKeys: TListBox;
71 ProcInputList: TListBox;
72 PageControl: TPageControl;
73 QuoteFields: TCheckBox;
74 SQLText: TMemo;
75 SelectPage: TTabSheet;
76 InsertPage: TTabSheet;
77 ModifyPage: TTabSheet;
78 DeletePage: TTabSheet;
79 ExecutePage: TTabSheet;
80 SelectTableNames: TComboBox;
81 InsertTableNames: TComboBox;
82 ModifyTableNames: TComboBox;
83 DeleteTableNames: TComboBox;
84 ProcedureNames: TComboBox;
85 procedure GenerateBtnClick(Sender: TObject);
86 procedure TestBtnClick(Sender: TObject);
87 procedure DeletePageShow(Sender: TObject);
88 procedure DeleteTableNamesCloseUp(Sender: TObject);
89 procedure ExecutePageShow(Sender: TObject);
90 procedure FormShow(Sender: TObject);
91 procedure IncludePrimaryKeysClick(Sender: TObject);
92 procedure InsertPageShow(Sender: TObject);
93 procedure Label13Click(Sender: TObject);
94 procedure ModifyPageShow(Sender: TObject);
95 procedure ModifyTableNamesCloseUp(Sender: TObject);
96 procedure ProcedureNamesCloseUp(Sender: TObject);
97 procedure SelectFieldsListDblClick(Sender: TObject);
98 procedure SelectPageShow(Sender: TObject);
99 procedure SelectTableNamesCloseUp(Sender: TObject);
100 procedure InsertTableNamesCloseUp(Sender: TObject);
101 private
102 { private declarations }
103 FTableName: string;
104 FIBSystemTables: TIBSystemTables;
105 FExecuteOnly: boolean;
106 public
107 { public declarations }
108 constructor Create(TheOwner: TComponent); override;
109 destructor Destroy; override;
110 procedure SetDatabase(Database: TIBDatabase);
111 end;
112
113 var
114 IBSQLEditorForm: TIBSQLEditorForm;
115
116 function EditIBSQL(DataSet: TIBSQL): boolean;
117
118 implementation
119
120 {$R *.lfm}
121
122 uses InterfaceBase;
123
124 function EditIBSQL(DataSet: TIBSQL): boolean;
125 begin
126 Result := false;
127 if assigned(DataSet.Database) then
128 try
129 DataSet.Database.Connected := true;
130 except on E: Exception do
131 ShowMessage(E.Message)
132 end;
133
134 with TIBSQLEditorForm.Create(Application) do
135 try
136 SetDatabase(DataSet.Database);
137 SQLText.Lines.Assign(DataSet.SQL);
138 GenerateParams.Checked := DataSet.GenerateParamNames;
139 Result := ShowModal = mrOK;
140 if Result then
141 begin
142 DataSet.SQL.Assign(SQLText.Lines);
143 DataSet.GenerateParamNames := GenerateParams.Checked
144 end;
145 finally
146 Free
147 end;
148
149 end;
150
151 { TIBSQLEditorForm }
152
153 procedure TIBSQLEditorForm.FormShow(Sender: TObject);
154 var IsProcedureName: boolean;
155 begin
156 if WidgetSet.LCLPlatform = lpGtk2 then
157 PageControl.TabPosition := tpLeft
158 else
159 PageControl.TabPosition := tpTop;
160 GenerateBtn.Enabled := (IBTransaction1.DefaultDatabase <> nil) and IBTransaction1.DefaultDatabase.Connected;
161 TestBtn.Enabled := (IBTransaction1.DefaultDatabase <> nil) and IBTransaction1.DefaultDatabase.Connected;
162 if Trim(SQLText.Text) <> '' then
163 begin
164 case FIBSystemTables.GetStatementType(SQLText.Text,IsProcedureName) of
165 SQLSelect:
166 if IsProcedureName then
167 PageControl.ActivePage := ExecutePage
168 else
169 PageControl.ActivePage := SelectPage;
170 SQLInsert: PageControl.ActivePage := InsertPage;
171 SQLUpdate: PageControl.ActivePage := ModifyPage;
172 SQLDelete: PageControl.ActivePage := DeletePage;
173 SQLExecProcedure: PageControl.ActivePage := ExecutePage;
174 else
175 PageControl.ActivePage := SelectPage;
176 end;
177 FIBSystemTables.GetTableAndColumns(SQLText.Text,FTableName,nil)
178 end;
179 end;
180
181 procedure TIBSQLEditorForm.IncludePrimaryKeysClick(Sender: TObject);
182 begin
183 FIBSystemTables.GetFieldNames(ModifyTableNames.Text,ModifyFieldsList.Items,IncludePrimaryKeys.checked);
184 FIBSystemTables.GetPrimaryKeys(ModifyTableNames.Text,ModifyPrimaryKeys.Items);
185 end;
186
187 procedure TIBSQLEditorForm.DeletePageShow(Sender: TObject);
188 var TableName: string;
189 begin
190 FIBSystemTables.GetTableNames(DeleteTableNames.Items);
191 if Trim(SQLText.Text) = '' then
192 begin
193 if FTableName <> '' then
194 DeleteTableNames.ItemIndex := DeleteTableNames.Items.IndexOf(FTableName)
195 else
196 if DeleteTableNames.Items.Count > 0 then
197 DeleteTableNames.ItemIndex := 0
198 end
199 else
200 begin
201 FIBSystemTables.GetTableAndColumns(SQLText.Text,TableName,nil);
202 DeleteTableNames.ItemIndex := DeleteTableNames.Items.IndexOf(TableName);
203 end;
204 FIBSystemTables.GetPrimaryKeys(DeleteTableNames.Text,DeletePrimaryKeys.Items);
205
206 end;
207
208 procedure TIBSQLEditorForm.GenerateBtnClick(Sender: TObject);
209 var FieldNames: TStrings;
210 begin
211 FieldNames := nil;
212 if PageControl.ActivePage = SelectPage then
213 begin
214 FieldNames := FIBSystemTables.GetFieldNames(SelectFieldsList);
215 FIBSystemTables.GenerateSelectSQL(SelectTableNames.Text,QuoteFields.Checked,FieldNames,SQLText.Lines);
216 end
217 else
218 if PageControl.ActivePage = InsertPage then
219 begin
220 FieldNames := FIBSystemTables.GetFieldNames(InsertFieldsList);
221 FIBSystemTables.GenerateInsertSQL(InsertTableNames.Text,QuoteFields.Checked,FieldNames,SQLText.Lines);
222 end
223 else
224 if PageControl.ActivePage = ModifyPage then
225 begin
226 FieldNames := FIBSystemTables.GetFieldNames(ModifyFieldsList);
227 FIBSystemTables.GenerateModifySQL(ModifyTableNames.Text,QuoteFields.Checked,FieldNames,SQLText.Lines);
228 end
229 else
230 if PageControl.ActivePage = DeletePage then
231 FIBSystemTables.GenerateDeleteSQL(DeleteTableNames.Text,QuoteFields.Checked,SQLText.Lines)
232 else
233 if PageControl.ActivePage = ExecutePage then
234 FIBSystemTables.GenerateExecuteSQL(ProcedureNames.Text,QuoteFields.Checked, FExecuteOnly,
235 ProcInputList.Items,ProcOutputList.Items,SQLText.Lines);
236
237 if FieldNames <> nil then
238 FieldNames.Free
239 end;
240
241 procedure TIBSQLEditorForm.TestBtnClick(Sender: TObject);
242 begin
243 FIBSystemTables.TestSQL(SQLText.Text,GenerateParams.Checked);
244 end;
245
246 procedure TIBSQLEditorForm.DeleteTableNamesCloseUp(Sender: TObject);
247 begin
248 FTableName := DeleteTableNames.Text;
249 FIBSystemTables.GetPrimaryKeys(DeleteTableNames.Text,DeletePrimaryKeys.Items);
250 end;
251
252 procedure TIBSQLEditorForm.ExecutePageShow(Sender: TObject);
253 var ProcName: string;
254 IsProcedureName: boolean;
255 begin
256 FIBSystemTables.GetProcedureNames(ProcedureNames.Items);
257 if ProcedureNames.Items.Count > 0 then
258 begin
259 if (FIBSystemTables.GetStatementType(SQLText.Text,IsProcedureName) = SQLExecProcedure) or IsProcedureName then
260 begin
261 FIBSystemTables.GetTableAndColumns(SQLText.Text,ProcName,nil);
262 ProcedureNames.ItemIndex := ProcedureNames.Items.IndexOf(ProcName)
263 end
264 else
265 ProcedureNames.ItemIndex := 0;
266 end;
267 FIBSystemTables.GetProcParams(ProcedureNames.Text,FExecuteOnly,ProcInputList.Items,ProcOutputList.Items);
268 SelectProcedure.Visible := not FExecuteOnly;
269 end;
270
271 procedure TIBSQLEditorForm.InsertPageShow(Sender: TObject);
272 var TableName: string;
273 begin
274 FIBSystemTables.GetTableNames(InsertTableNames.Items);
275 if Trim(SQLText.Text) = '' then
276 begin
277 if FTableName <> '' then
278 InsertTableNames.ItemIndex := InsertTableNames.Items.IndexOf(FTableName)
279 else
280 if InsertTableNames.Items.Count > 0 then
281 InsertTableNames.ItemIndex := 0
282 end
283 else
284 begin
285 FIBSystemTables.GetTableAndColumns(SQLText.Text,TableName,nil);
286 InsertTableNames.ItemIndex := InsertTableNames.Items.IndexOf(TableName);
287 end;
288 FIBSystemTables.GetFieldNames(InsertTableNames.Text,InsertFieldsList.Items);
289
290 end;
291
292 procedure TIBSQLEditorForm.Label13Click(Sender: TObject);
293 begin
294 FIBSystemTables.GetFieldNames(ModifyTableNames.Text,ModifyFieldsList.Items,IncludePrimaryKeys.checked);
295 FIBSystemTables.GetPrimaryKeys(ModifyTableNames.Text,ModifyPrimaryKeys.Items);
296 end;
297
298 procedure TIBSQLEditorForm.ModifyPageShow(Sender: TObject);
299 var TableName: string;
300 begin
301 FIBSystemTables.GetTableNames(ModifyTableNames.Items);
302 if Trim(SQLText.Text) = '' then
303 begin
304 if FTableName <> '' then
305 ModifyTableNames.ItemIndex := ModifyTableNames.Items.IndexOf(FTableName)
306 else
307 if ModifyTableNames.Items.Count > 0 then
308 ModifyTableNames.ItemIndex := 0;
309 end
310 else
311 begin
312 FIBSystemTables.GetTableAndColumns(SQLText.Text,TableName,nil);
313 ModifyTableNames.ItemIndex := ModifyTableNames.Items.IndexOf(TableName);
314 end;
315 FIBSystemTables.GetFieldNames(ModifyTableNames.Text,ModifyFieldsList.Items,IncludePrimaryKeys.checked,false);
316 FIBSystemTables.GetPrimaryKeys(ModifyTableNames.Text,ModifyPrimaryKeys.Items);
317 end;
318
319 procedure TIBSQLEditorForm.ModifyTableNamesCloseUp(Sender: TObject);
320 begin
321 FTableName := ModifyTableNames.Text;
322 FIBSystemTables.GetFieldNames(ModifyTableNames.Text,ModifyFieldsList.Items,IncludePrimaryKeys.checked,false);
323 FIBSystemTables.GetPrimaryKeys(ModifyTableNames.Text,ModifyPrimaryKeys.Items);
324 end;
325
326 procedure TIBSQLEditorForm.ProcedureNamesCloseUp(Sender: TObject);
327 begin
328 FIBSystemTables.GetProcParams(ProcedureNames.Text,FExecuteOnly,ProcInputList.Items,ProcOutputList.Items);
329 SelectProcedure.Visible := not FExecuteOnly
330 end;
331
332 procedure TIBSQLEditorForm.SelectFieldsListDblClick(Sender: TObject);
333 begin
334 SQLText.SelText:= (Sender as TListBox).Items[(Sender as TListBox).ItemIndex];
335 end;
336
337 procedure TIBSQLEditorForm.SelectPageShow(Sender: TObject);
338 var TableName: string;
339 begin
340 FIBSystemTables.GetTableNames(SelectTableNames.Items);
341 if Trim(SQLText.Text) = '' then
342 begin
343 if FTableName <> '' then
344 SelectTableNames.ItemIndex := SelectTableNames.Items.IndexOf(FTableName)
345 else
346 if SelectTableNames.Items.Count > 0 then
347 SelectTableNames.ItemIndex := 0;
348 end
349 else
350 begin
351 FIBSystemTables.GetTableAndColumns(SQLText.Text,TableName,nil);
352 SelectTableNames.ItemIndex := SelectTableNames.Items.IndexOf(TableName);
353 end;
354 FIBSystemTables.GetFieldNames(SelectTableNames.Text,SelectFieldsList.Items);
355 FIBSystemTables.GetPrimaryKeys(SelectTableNames.Text,SelectPrimaryKeys.Items);
356 end;
357
358 procedure TIBSQLEditorForm.SelectTableNamesCloseUp(Sender: TObject);
359 begin
360 FTableName := SelectTableNames.Text;
361 try
362 FIBSystemTables.GetFieldNames(SelectTableNames.Text,SelectFieldsList.Items);
363 FIBSystemTables.GetPrimaryKeys(SelectTableNames.Text,SelectPrimaryKeys.Items);
364 except {ignore} end;
365 end;
366
367 procedure TIBSQLEditorForm.InsertTableNamesCloseUp(Sender: TObject);
368 begin
369 FTableName := InsertTableNames.Text;
370 FIBSystemTables.GetFieldNames(InsertTableNames.Text,InsertFieldsList.Items);
371 end;
372
373 constructor TIBSQLEditorForm.Create(TheOwner: TComponent);
374 begin
375 inherited Create(TheOwner);
376 FIBSystemTables := TIBSystemTables.Create;
377 end;
378
379 destructor TIBSQLEditorForm.Destroy;
380 begin
381 if assigned(FIBSystemTables) then FIBSystemTables.Free;
382 inherited Destroy;
383 end;
384
385 procedure TIBSQLEditorForm.SetDatabase(Database: TIBDatabase);
386 begin
387 IBTransaction1.DefaultDatabase := Database;
388 FIBSystemTables.SelectDatabase(Database,IBTransaction1)
389 end;
390
391 end.