ViewVC Help

View File | Revision Log | Show Annotations | Download File | View Changeset | Root Listing

root/public/ibx/trunk/design/ibupdatesqleditor.pas

Comparing ibx/trunk/design/ibupdatesqleditor.pas (file contents):
Revision 7 by tony, Sun Aug 5 18:28:19 2012 UTC vs.
Revision 105 by tony, Thu Jan 18 14:37:32 2018 UTC

#	Line 15 | Line 15
15		*
16		*  The Initial Developer of the Original Code is Tony Whyman.
17		*
18	<	*  The Original Code is (C) 2011 Tony Whyman, MWA Software
18	>	*  The Original Code is (C) 2011-17 Tony Whyman, MWA Software
19		*  (http://www.mwasoftware.co.uk).
20		*
21		*  All Rights Reserved.
#	Line 23 | Line 23
23		*  Contributor(s): ______________________________________.
24		*
25		*)
26	<	unit IBUpdateSQLEditor;
26	>
27	>	unit ibupdatesqleditor;
28
29		{$mode objfpc}{$H+}
30
31		interface
32
33		uses
34	<	Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs,
35	<	ComCtrls, StdCtrls, ExtCtrls, IBSystemTables, IBUpdateSQL, IBDatabase;
34	>	Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs, ComCtrls,
35	>	StdCtrls, ExtCtrls,  IBSQLEditFrame, IBCustomDataSet,
36	>	IBDatabase, IBLookupComboEditBox, IBDynamicGrid, Types, IBUpdateSQL;
37
38		type
39
40		{ TIBUpdateSQLEditorForm }
41
42		TIBUpdateSQLEditorForm = class(TForm)
43	+	FieldNamesGrid: TIBDynamicGrid;
44	+	GenerateParams: TCheckBox;
45	+	IBSQLEditFrame1: TIBSQLEditFrame;
46	+	IdentityGrid: TIBDynamicGrid;
47	+	IncludeSysTables: TCheckBox;
48	+	Label5: TLabel;
49	+	Label6: TLabel;
50	+	PrimaryKeysGrid: TIBDynamicGrid;
51	+	ReadOnlyGrid: TIBDynamicGrid;
52	+	SelectSelectAll: TCheckBox;
53	+	SelectTableNames: TIBLookupComboEditBox;
54		TestBtn: TButton;
55		CancelButton: TButton;
56		FieldsPage: TTabSheet;
57	<	GenerateBtn: TButton;
57	>	GenerateButton: TButton;
58		GroupBox1: TGroupBox;
46	–	IBTransaction1: TIBTransaction;
59		IncludePrimaryKeys: TCheckBox;
48	–	PrimaryKeyList: TListBox;
60		Label1: TLabel;
61		Label2: TLabel;
62		Label3: TLabel;
#	Line 53 | Line 64 | type
64		OkButton: TButton;
65		PageControl: TPageControl;
66		QuoteFields: TCheckBox;
56	–	SQLMemo: TMemo;
67		SQLPage: TTabSheet;
68		StatementType: TRadioGroup;
69	<	FieldList: TListBox;
70	<	TableNamesCombo: TComboBox;
69	>	procedure IncludeSysTablesChange(Sender: TObject);
70	>	procedure SelectSelectAllClick(Sender: TObject);
71		procedure TestBtnClick(Sender: TObject);
72		procedure FormClose(Sender: TObject; var CloseAction: TCloseAction);
73		procedure FormShow(Sender: TObject);
74	<	procedure GenerateBtnClick(Sender: TObject);
74	>	procedure GenerateButtonClick(Sender: TObject);
75		procedure SQLMemoChange(Sender: TObject);
76		procedure SQLPageShow(Sender: TObject);
77		procedure StatementTypeClick(Sender: TObject);
68	–	procedure TableNamesComboCloseUp(Sender: TObject);
78		private
79		{ private declarations }
80		FUpdateObject: TIBUpdateSQL;
72	–	FIBSystemTables: TIBSystemTables;
81		FDirty: boolean;
82		FCurrentStatement: integer;
83		FModifySQL: TStringList;
#	Line 77 | Line 85 | type
85		FDeleteSQL: TStringList;
86		FRefreshSQL: TStringList;
87		procedure UpdateSQLMemo;
88	+	procedure HandleUserTablesOpened(Sender: TObject);
89	+	protected
90	+	procedure Loaded; override;
91		public
92		{ public declarations }
93		constructor Create(TheOwner: TComponent); override;
94		destructor Destroy; override;
84	–	procedure SetUpdateObject(AObject: TIBUpdateSQL);
95		end;
96
97		var
98		IBUpdateSQLEditorForm: TIBUpdateSQLEditorForm;
99
100	<	function EditIBUpdateSQL(UpdateObject: TIBUpdateSQL): boolean;
100	>	function EditIBUpdateSQL(UpdateObject: TIBUpdateSQL): boolean;
101
102		implementation
103
94	–	uses IBQuery;
95	–
104		{$R *.lfm}
105
106		function EditIBUpdateSQL(UpdateObject: TIBUpdateSQL): boolean;
107		begin
108		Result := false;
109	<	if assigned(UpdateObject.DataSet) and assigned(UpdateObject.DataSet.Database) then
110	<	try
111	<	UpdateObject.DataSet.Database.Connected := true;
112	<	except on E: Exception do
113	<	ShowMessage(E.Message)
114	<	end;
109	>	if assigned(UpdateObject) and assigned(UpdateObject.DataSet) and assigned(UpdateObject.DataSet.Database) then
110	>	try
111	>	UpdateObject.DataSet.Database.Connected := true;
112	>	except on E: Exception do
113	>	ShowMessage(E.Message)
114	>	end;
115
116		with TIBUpdateSQLEditorForm.Create(Application) do
117		try
118	<	SetUpdateObject(UpdateObject);
119	<	Result := ShowModal = mrOK
118	>	if assigned(UpdateObject) and assigned(UpdateObject.DataSet) then
119	>	begin
120	>	IBSQLEditFrame1.Database := UpdateObject.DataSet.Database;
121	>	GenerateParams.Checked := UpdateObject.DataSet.GenerateParamNames;
122	>	end;
123	>	FUpdateObject := UpdateObject;
124	>	Result := ShowModal = mrOK;
125	>	if Result then
126	>	UpdateObject.DataSet.GenerateParamNames := GenerateParams.Checked
127		finally
128		Free
129		end;
#	Line 120 | Line 135 | end;
135		procedure TIBUpdateSQLEditorForm.FormShow(Sender: TObject);
136		var TableName: string;
137		begin
123	–	GenerateBtn.Enabled := (IBTransaction1.DefaultDatabase <> nil) and IBTransaction1.DefaultDatabase.Connected;
124	–	TestBtn.Enabled := (IBTransaction1.DefaultDatabase <> nil) and IBTransaction1.DefaultDatabase.Connected;
138		PageControl.ActivePage := FieldsPage;
139		FModifySQL.Assign(FUpdateObject.ModifySQL);
140		FInsertSQL.Assign(FUpdateObject.InsertSQL);
141		FDeleteSQL.Assign(FUpdateObject.DeleteSQL);
142		FRefreshSQL.Assign(FUpdateObject.RefreshSQL);
143	+	GenerateButton.Enabled := (IBSQLEditFrame1.Database <> nil) and IBSQLEditFrame1.Database.Connected;
144	+	TestBtn.Enabled := (IBSQLEditFrame1.Database <> nil) and IBSQLEditFrame1.Database.Connected;
145		FCurrentStatement := -1;
146	<	TableNamesCombo.Items.Clear;
132	<	FIBSystemTables.GetTableNames(TableNamesCombo.Items);
133	<	if TableNamesCombo.Items.Count > 0 then
146	>	if (IBSQLEditFrame1.Database <> nil) and IBSQLEditFrame1.Database.Connected then
147		begin
148	<	TableNamesCombo.ItemIndex := 0;
149	<	if assigned(FUpdateObject.DataSet) and (FUpdateObject.DataSet is TIBQuery) and
137	<	((FUpdateObject.DataSet as TIBQuery).SQL.Text <> '') then
138	<	try
139	<	FIBSystemTables.GetTableAndColumns((FUpdateObject.DataSet as TIBQuery).SQL.Text,TableName,nil);
140	<	TableNamesCombo.ItemIndex := TableNamesCombo.Items.IndexOf(TableName);
141	<	except  //ignore
142	<	end;
143	<	FIBSystemTables.GetFieldNames(TableNamesCombo.Text,FieldList.Items,IncludePrimaryKeys.checked,false);
144	<	FIBSystemTables.GetPrimaryKeys(TableNamesCombo.Text,PrimaryKeyList.Items);
148	>	IBSQLEditFrame1.UserTables.Active := true;
149	>	IBSQLEditFrame1.SyncQueryBuilder(FRefreshSQL);
150		end;
151		end;
152
#	Line 160 | Line 165 | end;
165
166		procedure TIBUpdateSQLEditorForm.TestBtnClick(Sender: TObject);
167		begin
168	<	if SQLMemo.Lines.Text <> '' then
164	<	FIBSystemTables.TestSQL(SQLMemo.Lines.Text);
168	>	IBSQLEditFrame1.TestSQL(GenerateParams.Checked);
169		end;
170
171	<	procedure TIBUpdateSQLEditorForm.GenerateBtnClick(Sender: TObject);
168	<	var FieldNames: TStringList;
169	<	I: integer;
171	>	procedure TIBUpdateSQLEditorForm.IncludeSysTablesChange(Sender: TObject);
172		begin
173	<	FieldNames := TStringList.Create;
174	<	try
173	<	FUpdateObject.RefreshSQL.Clear;
174	<	FIBSystemTables.GetFieldNames(TableNamesCombo.Text,FieldNames);
175	<	FIBSystemTables.GenerateRefreshSQL(TableNamesCombo.Text,QuoteFields.Checked,FieldNames,FRefreshSQL);
176	<	FIBSystemTables.GenerateDeleteSQL(TableNamesCombo.Text,QuoteFields.Checked,FDeleteSQL);
177	<	FieldNames.Clear;
178	<	FIBSystemTables.GetFieldNames(TableNamesCombo.Text,FieldNames,true,false);
179	<	FIBSystemTables.GenerateInsertSQL(TableNamesCombo.Text,QuoteFields.Checked,
180	<	FieldNames,FInsertSQL);
181	<	if FieldList.SelCount = 0 then
182	<	begin
183	<	FIBSystemTables.GenerateModifySQL(TableNamesCombo.Text,QuoteFields.Checked,
184	<	FieldList.Items,FModifySQL);
173	>	IBSQLEditFrame1.IncludeSystemTables := IncludeSysTables.Checked;
174	>	end;
175
176	<	end
177	<	else
178	<	begin
179	<	FieldNames.Clear;
180	<	for I := 0 to FieldList.Items.Count - 1 do
181	<	if FieldList.Selected[I] then
182	<	FieldNames.Add(FieldList.Items[I]);
183	<	FIBSystemTables.GenerateModifySQL(TableNamesCombo.Text,QuoteFields.Checked,
184	<	FieldNames,FModifySQL);
185	<	end;
186	<	FDirty := false;
187	<	PageControl.ActivePage := SQLPage;
188	<	finally
199	<	FieldNames.Free
200	<	end;
176	>	procedure TIBUpdateSQLEditorForm.SelectSelectAllClick(Sender: TObject);
177	>	begin
178	>	IBSQLEditFrame1.SelectAllFields(SelectSelectAll.Checked);
179	>	end;
180	>
181	>	procedure TIBUpdateSQLEditorForm.GenerateButtonClick(Sender: TObject);
182	>	begin
183	>	IBSQLEditFrame1.GenerateRefreshSQL(QuoteFields.Checked,FRefreshSQL);
184	>	IBSQLEditFrame1.GenerateDeleteSQL(QuoteFields.Checked,FDeleteSQL);
185	>	IBSQLEditFrame1.GenerateInsertSQL(QuoteFields.Checked,FInsertSQL);
186	>	IBSQLEditFrame1.GenerateModifySQL(QuoteFields.Checked,FModifySQL, not IncludePrimaryKeys.Checked);
187	>	FDirty := false;
188	>	PageControl.ActivePage := SQLPage;
189		end;
190
191		procedure TIBUpdateSQLEditorForm.SQLMemoChange(Sender: TObject);
#	Line 215 | Line 203 | begin
203		UpdateSQLMemo
204		end;
205
218	–	procedure TIBUpdateSQLEditorForm.TableNamesComboCloseUp(Sender: TObject);
219	–	begin
220	–	FIBSystemTables.GetFieldNames(TableNamesCombo.Text,FieldList.Items,IncludePrimaryKeys.checked,false);
221	–	FIBSystemTables.GetPrimaryKeys(TableNamesCombo.Text,PrimaryKeyList.Items);
222	–	end;
223	–
206		procedure TIBUpdateSQLEditorForm.UpdateSQLMemo;
207		begin
208		if FDirty then
209		case FCurrentStatement of
210		0: //Modify
211	<	FModifySQL.Assign(SQLMemo.Lines);
211	>	FModifySQL.Assign(IBSQLEditFrame1.SQLText.Lines);
212		1: //Insert
213	<	FInsertSQL.Assign(SQLMemo.Lines);
213	>	FInsertSQL.Assign(IBSQLEditFrame1.SQLText.Lines);
214		2: // Delete
215	<	FDeleteSQL.Assign(SQLMemo.Lines);
215	>	FDeleteSQL.Assign(IBSQLEditFrame1.SQLText.Lines);
216		3: //Refresh
217	<	FRefreshSQL.Assign(SQLMemo.Lines);
217	>	FRefreshSQL.Assign(IBSQLEditFrame1.SQLText.Lines);
218		end;
219		FDirty := false;
220		case StatementType.ItemIndex of
221		0: // Modify
222	<	SQLMemo.Lines.Assign(FModifySQL)  ;
222	>	IBSQLEditFrame1.SQLText.Lines.Assign(FModifySQL)  ;
223		1: //Insert
224	<	SQLMemo.Lines.Assign(FInsertSQL)  ;
224	>	IBSQLEditFrame1.SQLText.Lines.Assign(FInsertSQL)  ;
225		2: // Delete
226	<	SQLMemo.Lines.Assign(FDeleteSQL)  ;
226	>	IBSQLEditFrame1.SQLText.Lines.Assign(FDeleteSQL)  ;
227		3: //Refresh
228	<	SQLMemo.Lines.Assign(FRefreshSQL)  ;
228	>	IBSQLEditFrame1.SQLText.Lines.Assign(FRefreshSQL)  ;
229		end;
230		FCurrentStatement := StatementType.ItemIndex;
231		end;
232
233	+	procedure TIBUpdateSQLEditorForm.HandleUserTablesOpened(Sender: TObject);
234	+	begin
235	+	SelectSelectAll.Checked := true;
236	+	end;
237	+
238	+	procedure TIBUpdateSQLEditorForm.Loaded;
239	+	begin
240	+	inherited Loaded;
241	+	if IBSQLEditFrame1 <> nil then
242	+	begin
243	+	IBSQLEditFrame1.OnUserTablesOpened := @HandleUserTablesOpened;
244	+	if SelectTableNames <> nil then
245	+	SelectTableNames.ListSource :=  IBSQLEditFrame1.UserTableSource;
246	+	if FieldNamesGrid <> nil then
247	+	FieldNamesGrid.DataSource := IBSQLEditFrame1.FieldsSource;
248	+	if PrimaryKeysGrid <> nil then
249	+	PrimaryKeysGrid.DataSource := IBSQLEditFrame1.PrimaryKeySource;
250	+	if IdentityGrid <> nil then
251	+	IdentityGrid.DataSource := IBSQLEditFrame1.IdentityColsSource;
252	+	if ReadOnlyGrid <> nil then
253	+	ReadOnlyGrid.DataSource := IBSQLEditFrame1.ReadOnlyFieldsSource;
254	+	end;
255	+	end;
256	+
257		constructor TIBUpdateSQLEditorForm.Create(TheOwner: TComponent);
258		begin
259		inherited Create(TheOwner);
254	–	FIBSystemTables := TIBSystemTables.Create;
260		FModifySQL := TStringList.Create;
261		FInsertSQL := TStringList.Create;
262		FDeleteSQL := TStringList.Create;
#	Line 260 | Line 265 | end;
265
266		destructor TIBUpdateSQLEditorForm.Destroy;
267		begin
263	–	if assigned(FIBSystemTables) then FIBSystemTables.Free;
268		if assigned(FModifySQL) then FModifySQL.Free;
269		if assigned(FInsertSQL) then FInsertSQL.Free;
270		if assigned(FDeleteSQL) then FDeleteSQL.Free;
#	Line 268 | Line 272 | begin
272		inherited Destroy;
273		end;
274
271	–	procedure TIBUpdateSQLEditorForm.SetUpdateObject(AObject: TIBUpdateSQL);
272	–	begin
273	–	FUpdateObject := AObject;
274	–	if assigned(FUpdateObject.DataSet) then
275	–	begin
276	–	IBTransaction1.DefaultDatabase := FUpdateObject.DataSet.Database;
277	–	FIBSystemTables.SelectDatabase(FUpdateObject.DataSet.Database,IBTransaction1);
278	–	end;
279	–	end;
280	–
281	–
275		end.
283	–

Diff Legend

–	Removed lines
+	Added lines
<	Changed lines
>	Changed lines