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 79 by tony, Sat Jul 18 12:30:52 2015 UTC vs.
Revision 80 by tony, Mon Jan 1 11:31:07 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	+	IncludeSysTables: TCheckBox;
47	+	PrimaryKeysGrid: TIBDynamicGrid;
48	+	SelectSelectAll: TCheckBox;
49	+	SelectTableNames: TIBLookupComboEditBox;
50		TestBtn: TButton;
51		CancelButton: TButton;
52		FieldsPage: TTabSheet;
53	<	GenerateBtn: TButton;
53	>	GenerateButton: TButton;
54		GroupBox1: TGroupBox;
47	–	IBTransaction1: TIBTransaction;
55		IncludePrimaryKeys: TCheckBox;
49	–	PrimaryKeyList: TListBox;
56		Label1: TLabel;
57		Label2: TLabel;
58		Label3: TLabel;
#	Line 54 | Line 60 | type
60		OkButton: TButton;
61		PageControl: TPageControl;
62		QuoteFields: TCheckBox;
57	–	SQLMemo: TMemo;
63		SQLPage: TTabSheet;
64		StatementType: TRadioGroup;
65	<	FieldList: TListBox;
66	<	TableNamesCombo: TComboBox;
65	>	procedure IncludeSysTablesChange(Sender: TObject);
66	>	procedure SelectSelectAllClick(Sender: TObject);
67		procedure TestBtnClick(Sender: TObject);
68		procedure FormClose(Sender: TObject; var CloseAction: TCloseAction);
69		procedure FormShow(Sender: TObject);
70	<	procedure GenerateBtnClick(Sender: TObject);
70	>	procedure GenerateButtonClick(Sender: TObject);
71		procedure SQLMemoChange(Sender: TObject);
72		procedure SQLPageShow(Sender: TObject);
73		procedure StatementTypeClick(Sender: TObject);
69	–	procedure TableNamesComboCloseUp(Sender: TObject);
74		private
75		{ private declarations }
76		FUpdateObject: TIBUpdateSQL;
73	–	FIBSystemTables: TIBSystemTables;
77		FDirty: boolean;
78		FCurrentStatement: integer;
79		FModifySQL: TStringList;
#	Line 78 | Line 81 | type
81		FDeleteSQL: TStringList;
82		FRefreshSQL: TStringList;
83		procedure UpdateSQLMemo;
84	+	procedure HandleUserTablesOpened(Sender: TObject);
85	+	protected
86	+	procedure Loaded; override;
87		public
88		{ public declarations }
89		constructor Create(TheOwner: TComponent); override;
90		destructor Destroy; override;
85	–	procedure SetUpdateObject(AObject: TIBUpdateSQL);
91		end;
92
93		var
94		IBUpdateSQLEditorForm: TIBUpdateSQLEditorForm;
95
96	<	function EditIBUpdateSQL(UpdateObject: TIBUpdateSQL): boolean;
96	>	function EditIBUpdateSQL(UpdateObject: TIBUpdateSQL): boolean;
97
98		implementation
99
95	–	uses IBQuery;
96	–
100		{$R *.lfm}
101
102		function EditIBUpdateSQL(UpdateObject: TIBUpdateSQL): boolean;
103		begin
104		Result := false;
105	<	if assigned(UpdateObject.DataSet) and assigned(UpdateObject.DataSet.Database) then
106	<	try
107	<	UpdateObject.DataSet.Database.Connected := true;
108	<	except on E: Exception do
109	<	ShowMessage(E.Message)
110	<	end;
105	>	if assigned(UpdateObject) and assigned(UpdateObject.DataSet) and assigned(UpdateObject.DataSet.Database) then
106	>	try
107	>	UpdateObject.DataSet.Database.Connected := true;
108	>	except on E: Exception do
109	>	ShowMessage(E.Message)
110	>	end;
111
112		with TIBUpdateSQLEditorForm.Create(Application) do
113		try
114	<	SetUpdateObject(UpdateObject);
115	<	if assigned(UpdateObject.DataSet) then
114	>	if assigned(UpdateObject) and assigned(UpdateObject.DataSet) then
115	>	begin
116	>	IBSQLEditFrame1.Database := UpdateObject.DataSet.Database;
117		GenerateParams.Checked := UpdateObject.DataSet.GenerateParamNames;
118	+	end;
119	+	FUpdateObject := UpdateObject;
120		Result := ShowModal = mrOK;
121	<	if Result and assigned(UpdateObject.DataSet) then
121	>	if Result then
122		UpdateObject.DataSet.GenerateParamNames := GenerateParams.Checked
123		finally
124		Free
#	Line 125 | Line 131 | end;
131		procedure TIBUpdateSQLEditorForm.FormShow(Sender: TObject);
132		var TableName: string;
133		begin
128	–	GenerateBtn.Enabled := (IBTransaction1.DefaultDatabase <> nil) and IBTransaction1.DefaultDatabase.Connected;
129	–	TestBtn.Enabled := (IBTransaction1.DefaultDatabase <> nil) and IBTransaction1.DefaultDatabase.Connected;
134		PageControl.ActivePage := FieldsPage;
135		FModifySQL.Assign(FUpdateObject.ModifySQL);
136		FInsertSQL.Assign(FUpdateObject.InsertSQL);
137		FDeleteSQL.Assign(FUpdateObject.DeleteSQL);
138		FRefreshSQL.Assign(FUpdateObject.RefreshSQL);
139	+	GenerateButton.Enabled := (IBSQLEditFrame1.Database <> nil) and IBSQLEditFrame1.Database.Connected;
140	+	TestBtn.Enabled := (IBSQLEditFrame1.Database <> nil) and IBSQLEditFrame1.Database.Connected;
141		FCurrentStatement := -1;
142	<	TableNamesCombo.Items.Clear;
143	<	FIBSystemTables.GetTableNames(TableNamesCombo.Items);
138	<	if TableNamesCombo.Items.Count > 0 then
139	<	begin
140	<	TableNamesCombo.ItemIndex := 0;
141	<	if assigned(FUpdateObject.DataSet) and (FUpdateObject.DataSet is TIBQuery) and
142	<	((FUpdateObject.DataSet as TIBQuery).SQL.Text <> '') then
143	<	try
144	<	FIBSystemTables.GetTableAndColumns((FUpdateObject.DataSet as TIBQuery).SQL.Text,TableName,nil);
145	<	TableNamesCombo.ItemIndex := TableNamesCombo.Items.IndexOf(TableName);
146	<	except  //ignore
147	<	end;
148	<	FIBSystemTables.GetFieldNames(TableNamesCombo.Text,FieldList.Items,IncludePrimaryKeys.checked,false);
149	<	FIBSystemTables.GetPrimaryKeys(TableNamesCombo.Text,PrimaryKeyList.Items);
150	<	end;
142	>	IBSQLEditFrame1.UserTables.Active := true;
143	>	IBSQLEditFrame1.SyncQueryBuilder(FRefreshSQL);
144		end;
145
146		procedure TIBUpdateSQLEditorForm.FormClose(Sender: TObject;
#	Line 165 | Line 158 | end;
158
159		procedure TIBUpdateSQLEditorForm.TestBtnClick(Sender: TObject);
160		begin
161	<	if SQLMemo.Lines.Text <> '' then
169	<	FIBSystemTables.TestSQL(SQLMemo.Lines.Text,GenerateParams.Checked);
161	>	IBSQLEditFrame1.TestSQL(GenerateParams.Checked);
162		end;
163
164	<	procedure TIBUpdateSQLEditorForm.GenerateBtnClick(Sender: TObject);
173	<	var FieldNames: TStringList;
174	<	I: integer;
164	>	procedure TIBUpdateSQLEditorForm.IncludeSysTablesChange(Sender: TObject);
165		begin
166	<	FieldNames := TStringList.Create;
167	<	try
178	<	FUpdateObject.RefreshSQL.Clear;
179	<	FIBSystemTables.GetFieldNames(TableNamesCombo.Text,FieldNames);
180	<	FIBSystemTables.GenerateRefreshSQL(TableNamesCombo.Text,QuoteFields.Checked,FieldNames,FRefreshSQL);
181	<	FIBSystemTables.GenerateDeleteSQL(TableNamesCombo.Text,QuoteFields.Checked,FDeleteSQL);
182	<	FieldNames.Clear;
183	<	FIBSystemTables.GetFieldNames(TableNamesCombo.Text,FieldNames,true,false);
184	<	FIBSystemTables.GenerateInsertSQL(TableNamesCombo.Text,QuoteFields.Checked,
185	<	FieldNames,FInsertSQL);
186	<	if FieldList.SelCount = 0 then
187	<	begin
188	<	FIBSystemTables.GenerateModifySQL(TableNamesCombo.Text,QuoteFields.Checked,
189	<	FieldList.Items,FModifySQL);
166	>	IBSQLEditFrame1.IncludeSystemTables := IncludeSysTables.Checked;
167	>	end;
168
169	<	end
170	<	else
171	<	begin
172	<	FieldNames.Clear;
173	<	for I := 0 to FieldList.Items.Count - 1 do
174	<	if FieldList.Selected[I] then
175	<	FieldNames.Add(FieldList.Items[I]);
176	<	FIBSystemTables.GenerateModifySQL(TableNamesCombo.Text,QuoteFields.Checked,
177	<	FieldNames,FModifySQL);
178	<	end;
179	<	FDirty := false;
180	<	PageControl.ActivePage := SQLPage;
181	<	finally
204	<	FieldNames.Free
205	<	end;
169	>	procedure TIBUpdateSQLEditorForm.SelectSelectAllClick(Sender: TObject);
170	>	begin
171	>	IBSQLEditFrame1.SelectAllFields(SelectSelectAll.Checked);
172	>	end;
173	>
174	>	procedure TIBUpdateSQLEditorForm.GenerateButtonClick(Sender: TObject);
175	>	begin
176	>	IBSQLEditFrame1.GenerateRefreshSQL(QuoteFields.Checked,FRefreshSQL);
177	>	IBSQLEditFrame1.GenerateDeleteSQL(QuoteFields.Checked,FDeleteSQL);
178	>	IBSQLEditFrame1.GenerateInsertSQL(QuoteFields.Checked,FInsertSQL);
179	>	IBSQLEditFrame1.GenerateModifySQL(QuoteFields.Checked,FModifySQL, not IncludePrimaryKeys.Checked);
180	>	FDirty := false;
181	>	PageControl.ActivePage := SQLPage;
182		end;
183
184		procedure TIBUpdateSQLEditorForm.SQLMemoChange(Sender: TObject);
#	Line 220 | Line 196 | begin
196		UpdateSQLMemo
197		end;
198
223	–	procedure TIBUpdateSQLEditorForm.TableNamesComboCloseUp(Sender: TObject);
224	–	begin
225	–	FIBSystemTables.GetFieldNames(TableNamesCombo.Text,FieldList.Items,IncludePrimaryKeys.checked,false);
226	–	FIBSystemTables.GetPrimaryKeys(TableNamesCombo.Text,PrimaryKeyList.Items);
227	–	end;
228	–
199		procedure TIBUpdateSQLEditorForm.UpdateSQLMemo;
200		begin
201		if FDirty then
202		case FCurrentStatement of
203		0: //Modify
204	<	FModifySQL.Assign(SQLMemo.Lines);
204	>	FModifySQL.Assign(IBSQLEditFrame1.SQLText.Lines);
205		1: //Insert
206	<	FInsertSQL.Assign(SQLMemo.Lines);
206	>	FInsertSQL.Assign(IBSQLEditFrame1.SQLText.Lines);
207		2: // Delete
208	<	FDeleteSQL.Assign(SQLMemo.Lines);
208	>	FDeleteSQL.Assign(IBSQLEditFrame1.SQLText.Lines);
209		3: //Refresh
210	<	FRefreshSQL.Assign(SQLMemo.Lines);
210	>	FRefreshSQL.Assign(IBSQLEditFrame1.SQLText.Lines);
211		end;
212		FDirty := false;
213		case StatementType.ItemIndex of
214		0: // Modify
215	<	SQLMemo.Lines.Assign(FModifySQL)  ;
215	>	IBSQLEditFrame1.SQLText.Lines.Assign(FModifySQL)  ;
216		1: //Insert
217	<	SQLMemo.Lines.Assign(FInsertSQL)  ;
217	>	IBSQLEditFrame1.SQLText.Lines.Assign(FInsertSQL)  ;
218		2: // Delete
219	<	SQLMemo.Lines.Assign(FDeleteSQL)  ;
219	>	IBSQLEditFrame1.SQLText.Lines.Assign(FDeleteSQL)  ;
220		3: //Refresh
221	<	SQLMemo.Lines.Assign(FRefreshSQL)  ;
221	>	IBSQLEditFrame1.SQLText.Lines.Assign(FRefreshSQL)  ;
222		end;
223		FCurrentStatement := StatementType.ItemIndex;
224		end;
225
226	+	procedure TIBUpdateSQLEditorForm.HandleUserTablesOpened(Sender: TObject);
227	+	begin
228	+	SelectSelectAll.Checked := true;
229	+	end;
230	+
231	+	procedure TIBUpdateSQLEditorForm.Loaded;
232	+	begin
233	+	inherited Loaded;
234	+	if IBSQLEditFrame1 <> nil then
235	+	begin
236	+	IBSQLEditFrame1.OnUserTablesOpened := @HandleUserTablesOpened;
237	+	if SelectTableNames <> nil then
238	+	SelectTableNames.ListSource :=  IBSQLEditFrame1.UserTableSource;
239	+	if FieldNamesGrid <> nil then
240	+	FieldNamesGrid.DataSource := IBSQLEditFrame1.FieldsSource;
241	+	if PrimaryKeysGrid <> nil then
242	+	PrimaryKeysGrid.DataSource := IBSQLEditFrame1.PrimaryKeySource;
243	+	end;
244	+	end;
245	+
246		constructor TIBUpdateSQLEditorForm.Create(TheOwner: TComponent);
247		begin
248		inherited Create(TheOwner);
259	–	FIBSystemTables := TIBSystemTables.Create;
249		FModifySQL := TStringList.Create;
250		FInsertSQL := TStringList.Create;
251		FDeleteSQL := TStringList.Create;
#	Line 265 | Line 254 | end;
254
255		destructor TIBUpdateSQLEditorForm.Destroy;
256		begin
268	–	if assigned(FIBSystemTables) then FIBSystemTables.Free;
257		if assigned(FModifySQL) then FModifySQL.Free;
258		if assigned(FInsertSQL) then FInsertSQL.Free;
259		if assigned(FDeleteSQL) then FDeleteSQL.Free;
#	Line 273 | Line 261 | begin
261		inherited Destroy;
262		end;
263
276	–	procedure TIBUpdateSQLEditorForm.SetUpdateObject(AObject: TIBUpdateSQL);
277	–	begin
278	–	FUpdateObject := AObject;
279	–	if assigned(FUpdateObject.DataSet) then
280	–	begin
281	–	IBTransaction1.DefaultDatabase := FUpdateObject.DataSet.Database;
282	–	FIBSystemTables.SelectDatabase(FUpdateObject.DataSet.Database,IBTransaction1);
283	–	end;
284	–	end;
285	–
286	–
264		end.

Diff Legend

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