trunk/design/ibupdatesqleditor.pas

unit IBUpdateSQLEditor; 

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, FileUtil, LResources, Forms, Controls, Graphics, Dialogs,
  ComCtrls, StdCtrls, ExtCtrls, IBSystemTables, IBUpdateSQL;

type

  { TIBUpdateSQLEditorForm }

  TIBUpdateSQLEditorForm = class(TForm)
    Button1: TButton;
    CancelButton: TButton;
    FieldsPage: TTabSheet;
    GenerateButton: TButton;
    GroupBox1: TGroupBox;
    PrimaryKeyList: TListBox;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    OkButton: TButton;
    PageControl: TPageControl;
    QuoteFields: TCheckBox;
    SQLMemo: TMemo;
    SQLPage: TTabSheet;
    StatementType: TRadioGroup;
    FieldList: TListBox;
    TableNamesCombo: TComboBox;
    procedure Button1Click(Sender: TObject);
    procedure FormClose(Sender: TObject; var CloseAction: TCloseAction);
    procedure FormShow(Sender: TObject);
    procedure GenerateButtonClick(Sender: TObject);
    procedure SQLMemoChange(Sender: TObject);
    procedure SQLPageShow(Sender: TObject);
    procedure StatementTypeClick(Sender: TObject);
    procedure TableNamesComboCloseUp(Sender: TObject);
  private
    { private declarations }
    FUpdateObject: TIBUpdateSQL;
    FIBSystemTables: TIBSystemTables;
    FDirty: boolean;
    FCurrentStatement: integer;
    FModifySQL: TStringList;
    FInsertSQL: TStringList;
    FDeleteSQL: TStringList;
    FRefreshSQL: TStringList;
    procedure UpdateSQLMemo;
  public
    { public declarations }
    constructor Create(TheOwner: TComponent); override;
    destructor Destroy; override;
    procedure SetUpdateObject(AObject: TIBUpdateSQL);
  end;

var
  IBUpdateSQLEditorForm: TIBUpdateSQLEditorForm;

function EditIBUpdateSQL(UpdateObject: TIBUpdateSQL): boolean;

implementation

uses IBQuery;

function EditIBUpdateSQL(UpdateObject: TIBUpdateSQL): boolean;
begin
  Result := false;
  if assigned(UpdateObject.DataSet) and assigned(UpdateObject.DataSet.Database) then
  begin
    if not assigned(UpdateObject.DataSet.Transaction) then
    begin
      ShowMessage('No Default Transaction!');
      Exit
    end;

    UpdateObject.DataSet.Database.Connected := true;
  end;

  with TIBUpdateSQLEditorForm.Create(Application) do
  try
    SetUpdateObject(UpdateObject);
    Result := ShowModal = mrOK
  finally
    Free
  end;

end;

{ TIBUpdateSQLEditorForm }

procedure TIBUpdateSQLEditorForm.FormShow(Sender: TObject);
var TableName: string;
begin
  PageControl.ActivePage := FieldsPage;
  FModifySQL.Assign(FUpdateObject.ModifySQL);
  FInsertSQL.Assign(FUpdateObject.InsertSQL);
  FDeleteSQL.Assign(FUpdateObject.DeleteSQL);
  FRefreshSQL.Assign(FUpdateObject.RefreshSQL);
  FCurrentStatement := -1;
  TableNamesCombo.Items.Clear;
  FIBSystemTables.GetTableNames(TableNamesCombo.Items);
  if TableNamesCombo.Items.Count > 0 then
  begin
    TableNamesCombo.ItemIndex := 0;
    if assigned(FUpdateObject.DataSet) and (FUpdateObject.DataSet is TIBQuery) and
       ((FUpdateObject.DataSet as TIBQuery).SQL.Text <> '') then
    try
       FIBSystemTables.GetTableAndColumns((FUpdateObject.DataSet as TIBQuery).SQL.Text,TableName,nil);
       TableNamesCombo.ItemIndex := TableNamesCombo.Items.IndexOf(TableName);
    except  //ignore
    end;
    FIBSystemTables.GetFieldNames(TableNamesCombo.Text,FieldList.Items,false);
    FIBSystemTables.GetPrimaryKeys(TableNamesCombo.Text,PrimaryKeyList.Items);
  end;
end;

procedure TIBUpdateSQLEditorForm.FormClose(Sender: TObject;
  var CloseAction: TCloseAction);
begin
  if ModalResult = mrOK then
  begin
    UpdateSQLMemo;
    FUpdateObject.ModifySQL.Assign(FModifySQL);
    FUpdateObject.InsertSQL.Assign(FInsertSQL);
    FUpdateObject.DeleteSQL.Assign(FDeleteSQL);
    FUpdateObject.RefreshSQL.Assign(FRefreshSQL);
  end;
end;

procedure TIBUpdateSQLEditorForm.Button1Click(Sender: TObject);
begin
  if SQLMemo.Lines.Text <> '' then
    FIBSystemTables.TestSQL(SQLMemo.Lines.Text);
end;

procedure TIBUpdateSQLEditorForm.GenerateButtonClick(Sender: TObject);
var FieldNames: TStringList;
    I: integer;
begin
  FUpdateObject.RefreshSQL.Clear;
  FIBSystemTables.GenerateRefreshSQL(TableNamesCombo.Text,QuoteFields.Checked,FRefreshSQL);
  FIBSystemTables.GenerateDeleteSQL(TableNamesCombo.Text,QuoteFields.Checked,FDeleteSQL);
  if FieldList.SelCount = 0 then
  begin
    FIBSystemTables.GenerateModifySQL(TableNamesCombo.Text,QuoteFields.Checked,
      FieldList.Items,FModifySQL);
    FIBSystemTables.GenerateInsertSQL(TableNamesCombo.Text,QuoteFields.Checked,
      FieldList.Items,FInsertSQL);

  end
  else
  begin
    FieldNames := TStringList.Create;
    try
      for I := 0 to FieldList.Items.Count - 1 do
        if FieldList.Selected[I] then
          FieldNames.Add(FieldList.Items[I]);
      FIBSystemTables.GenerateModifySQL(TableNamesCombo.Text,QuoteFields.Checked,
        FieldNames,FModifySQL);
    FIBSystemTables.GenerateInsertSQL(TableNamesCombo.Text,QuoteFields.Checked,
      FieldNames,FInsertSQL);
    finally
      FieldNames.Free
    end;
  end;
  FDirty := false;
  PageControl.ActivePage := SQLPage;
end;

procedure TIBUpdateSQLEditorForm.SQLMemoChange(Sender: TObject);
begin
  FDirty := true
end;

procedure TIBUpdateSQLEditorForm.SQLPageShow(Sender: TObject);
begin
  UpdateSQLMemo
end;

procedure TIBUpdateSQLEditorForm.StatementTypeClick(Sender: TObject);
begin
  UpdateSQLMemo
end;

procedure TIBUpdateSQLEditorForm.TableNamesComboCloseUp(Sender: TObject);
begin
  FIBSystemTables.GetFieldNames(TableNamesCombo.Text,FieldList.Items,false);
  FIBSystemTables.GetPrimaryKeys(TableNamesCombo.Text,PrimaryKeyList.Items);
end;

procedure TIBUpdateSQLEditorForm.UpdateSQLMemo;
begin
  if FDirty then
    case FCurrentStatement of
    0: //Modify
        FModifySQL.Assign(SQLMemo.Lines);
    1: //Insert
        FInsertSQL.Assign(SQLMemo.Lines);
    2: // Delete
        FDeleteSQL.Assign(SQLMemo.Lines);
    3: //Refresh
        FRefreshSQL.Assign(SQLMemo.Lines);
    end;
  FDirty := false;
  case StatementType.ItemIndex of
  0: // Modify
    SQLMemo.Lines.Assign(FModifySQL)  ;
  1: //Insert
    SQLMemo.Lines.Assign(FInsertSQL)  ;
  2: // Delete
    SQLMemo.Lines.Assign(FDeleteSQL)  ;
  3: //Refresh
    SQLMemo.Lines.Assign(FRefreshSQL)  ;
  end;
  FCurrentStatement := StatementType.ItemIndex;
end;

constructor TIBUpdateSQLEditorForm.Create(TheOwner: TComponent);
begin
  inherited Create(TheOwner);
  FIBSystemTables := TIBSystemTables.Create;
  FModifySQL := TStringList.Create;
  FInsertSQL := TStringList.Create;
  FDeleteSQL := TStringList.Create;
  FRefreshSQL := TStringList.Create;
end;

destructor TIBUpdateSQLEditorForm.Destroy;
begin
  if assigned(FIBSystemTables) then FIBSystemTables.Free;
  if assigned(FModifySQL) then FModifySQL.Free;
  if assigned(FInsertSQL) then FInsertSQL.Free;
  if assigned(FDeleteSQL) then FDeleteSQL.Free;
  if assigned(FRefreshSQL) then FRefreshSQL.Free;
  inherited Destroy;
end;

procedure TIBUpdateSQLEditorForm.SetUpdateObject(AObject: TIBUpdateSQL);
begin
  FUpdateObject := AObject;
  if assigned(FUpdateObject.DataSet) then
    FIBSystemTables.SelectDatabase(FUpdateObject.DataSet.Database,FUpdateObject.DataSet.Transaction);
end;


initialization
  {$I ibupdatesqleditor.lrs}

end.

Revision:	5
Committed:	Fri Feb 18 16:26:16 2011 UTC (13 years, 2 months ago) by tony
Content type:	text/x-pascal
File size:	7212 byte(s)
Log Message:	Committing updates for Release pre-release
#	Content
1	unit IBUpdateSQLEditor;
2
3	{$mode objfpc}{$H+}
4
5	interface
6
7	uses
8	Classes, SysUtils, FileUtil, LResources, Forms, Controls, Graphics, Dialogs,
9	ComCtrls, StdCtrls, ExtCtrls, IBSystemTables, IBUpdateSQL;
10
11	type
12
13	{ TIBUpdateSQLEditorForm }
14
15	TIBUpdateSQLEditorForm = class(TForm)
16	Button1: TButton;
17	CancelButton: TButton;
18	FieldsPage: TTabSheet;
19	GenerateButton: TButton;
20	GroupBox1: TGroupBox;
21	PrimaryKeyList: TListBox;
22	Label1: TLabel;
23	Label2: TLabel;
24	Label3: TLabel;
25	Label4: TLabel;
26	OkButton: TButton;
27	PageControl: TPageControl;
28	QuoteFields: TCheckBox;
29	SQLMemo: TMemo;
30	SQLPage: TTabSheet;
31	StatementType: TRadioGroup;
32	FieldList: TListBox;
33	TableNamesCombo: TComboBox;
34	procedure Button1Click(Sender: TObject);
35	procedure FormClose(Sender: TObject; var CloseAction: TCloseAction);
36	procedure FormShow(Sender: TObject);
37	procedure GenerateButtonClick(Sender: TObject);
38	procedure SQLMemoChange(Sender: TObject);
39	procedure SQLPageShow(Sender: TObject);
40	procedure StatementTypeClick(Sender: TObject);
41	procedure TableNamesComboCloseUp(Sender: TObject);
42	private
43	{ private declarations }
44	FUpdateObject: TIBUpdateSQL;
45	FIBSystemTables: TIBSystemTables;
46	FDirty: boolean;
47	FCurrentStatement: integer;
48	FModifySQL: TStringList;
49	FInsertSQL: TStringList;
50	FDeleteSQL: TStringList;
51	FRefreshSQL: TStringList;
52	procedure UpdateSQLMemo;
53	public
54	{ public declarations }
55	constructor Create(TheOwner: TComponent); override;
56	destructor Destroy; override;
57	procedure SetUpdateObject(AObject: TIBUpdateSQL);
58	end;
59
60	var
61	IBUpdateSQLEditorForm: TIBUpdateSQLEditorForm;
62
63	function EditIBUpdateSQL(UpdateObject: TIBUpdateSQL): boolean;
64
65	implementation
66
67	uses IBQuery;
68
69	function EditIBUpdateSQL(UpdateObject: TIBUpdateSQL): boolean;
70	begin
71	Result := false;
72	if assigned(UpdateObject.DataSet) and assigned(UpdateObject.DataSet.Database) then
73	begin
74	if not assigned(UpdateObject.DataSet.Transaction) then
75	begin
76	ShowMessage('No Default Transaction!');
77	Exit
78	end;
79
80	UpdateObject.DataSet.Database.Connected := true;
81	end;
82
83	with TIBUpdateSQLEditorForm.Create(Application) do
84	try
85	SetUpdateObject(UpdateObject);
86	Result := ShowModal = mrOK
87	finally
88	Free
89	end;
90
91	end;
92
93	{ TIBUpdateSQLEditorForm }
94
95	procedure TIBUpdateSQLEditorForm.FormShow(Sender: TObject);
96	var TableName: string;
97	begin
98	PageControl.ActivePage := FieldsPage;
99	FModifySQL.Assign(FUpdateObject.ModifySQL);
100	FInsertSQL.Assign(FUpdateObject.InsertSQL);
101	FDeleteSQL.Assign(FUpdateObject.DeleteSQL);
102	FRefreshSQL.Assign(FUpdateObject.RefreshSQL);
103	FCurrentStatement := -1;
104	TableNamesCombo.Items.Clear;
105	FIBSystemTables.GetTableNames(TableNamesCombo.Items);
106	if TableNamesCombo.Items.Count > 0 then
107	begin
108	TableNamesCombo.ItemIndex := 0;
109	if assigned(FUpdateObject.DataSet) and (FUpdateObject.DataSet is TIBQuery) and
110	((FUpdateObject.DataSet as TIBQuery).SQL.Text <> '') then
111	try
112	FIBSystemTables.GetTableAndColumns((FUpdateObject.DataSet as TIBQuery).SQL.Text,TableName,nil);
113	TableNamesCombo.ItemIndex := TableNamesCombo.Items.IndexOf(TableName);
114	except //ignore
115	end;
116	FIBSystemTables.GetFieldNames(TableNamesCombo.Text,FieldList.Items,false);
117	FIBSystemTables.GetPrimaryKeys(TableNamesCombo.Text,PrimaryKeyList.Items);
118	end;
119	end;
120
121	procedure TIBUpdateSQLEditorForm.FormClose(Sender: TObject;
122	var CloseAction: TCloseAction);
123	begin
124	if ModalResult = mrOK then
125	begin
126	UpdateSQLMemo;
127	FUpdateObject.ModifySQL.Assign(FModifySQL);
128	FUpdateObject.InsertSQL.Assign(FInsertSQL);
129	FUpdateObject.DeleteSQL.Assign(FDeleteSQL);
130	FUpdateObject.RefreshSQL.Assign(FRefreshSQL);
131	end;
132	end;
133
134	procedure TIBUpdateSQLEditorForm.Button1Click(Sender: TObject);
135	begin
136	if SQLMemo.Lines.Text <> '' then
137	FIBSystemTables.TestSQL(SQLMemo.Lines.Text);
138	end;
139
140	procedure TIBUpdateSQLEditorForm.GenerateButtonClick(Sender: TObject);
141	var FieldNames: TStringList;
142	I: integer;
143	begin
144	FUpdateObject.RefreshSQL.Clear;
145	FIBSystemTables.GenerateRefreshSQL(TableNamesCombo.Text,QuoteFields.Checked,FRefreshSQL);
146	FIBSystemTables.GenerateDeleteSQL(TableNamesCombo.Text,QuoteFields.Checked,FDeleteSQL);
147	if FieldList.SelCount = 0 then
148	begin
149	FIBSystemTables.GenerateModifySQL(TableNamesCombo.Text,QuoteFields.Checked,
150	FieldList.Items,FModifySQL);
151	FIBSystemTables.GenerateInsertSQL(TableNamesCombo.Text,QuoteFields.Checked,
152	FieldList.Items,FInsertSQL);
153
154	end
155	else
156	begin
157	FieldNames := TStringList.Create;
158	try
159	for I := 0 to FieldList.Items.Count - 1 do
160	if FieldList.Selected[I] then
161	FieldNames.Add(FieldList.Items[I]);
162	FIBSystemTables.GenerateModifySQL(TableNamesCombo.Text,QuoteFields.Checked,
163	FieldNames,FModifySQL);
164	FIBSystemTables.GenerateInsertSQL(TableNamesCombo.Text,QuoteFields.Checked,
165	FieldNames,FInsertSQL);
166	finally
167	FieldNames.Free
168	end;
169	end;
170	FDirty := false;
171	PageControl.ActivePage := SQLPage;
172	end;
173
174	procedure TIBUpdateSQLEditorForm.SQLMemoChange(Sender: TObject);
175	begin
176	FDirty := true
177	end;
178
179	procedure TIBUpdateSQLEditorForm.SQLPageShow(Sender: TObject);
180	begin
181	UpdateSQLMemo
182	end;
183
184	procedure TIBUpdateSQLEditorForm.StatementTypeClick(Sender: TObject);
185	begin
186	UpdateSQLMemo
187	end;
188
189	procedure TIBUpdateSQLEditorForm.TableNamesComboCloseUp(Sender: TObject);
190	begin
191	FIBSystemTables.GetFieldNames(TableNamesCombo.Text,FieldList.Items,false);
192	FIBSystemTables.GetPrimaryKeys(TableNamesCombo.Text,PrimaryKeyList.Items);
193	end;
194
195	procedure TIBUpdateSQLEditorForm.UpdateSQLMemo;
196	begin
197	if FDirty then
198	case FCurrentStatement of
199	0: //Modify
200	FModifySQL.Assign(SQLMemo.Lines);
201	1: //Insert
202	FInsertSQL.Assign(SQLMemo.Lines);
203	2: // Delete
204	FDeleteSQL.Assign(SQLMemo.Lines);
205	3: //Refresh
206	FRefreshSQL.Assign(SQLMemo.Lines);
207	end;
208	FDirty := false;
209	case StatementType.ItemIndex of
210	0: // Modify
211	SQLMemo.Lines.Assign(FModifySQL) ;
212	1: //Insert
213	SQLMemo.Lines.Assign(FInsertSQL) ;
214	2: // Delete
215	SQLMemo.Lines.Assign(FDeleteSQL) ;
216	3: //Refresh
217	SQLMemo.Lines.Assign(FRefreshSQL) ;
218	end;
219	FCurrentStatement := StatementType.ItemIndex;
220	end;
221
222	constructor TIBUpdateSQLEditorForm.Create(TheOwner: TComponent);
223	begin
224	inherited Create(TheOwner);
225	FIBSystemTables := TIBSystemTables.Create;
226	FModifySQL := TStringList.Create;
227	FInsertSQL := TStringList.Create;
228	FDeleteSQL := TStringList.Create;
229	FRefreshSQL := TStringList.Create;
230	end;
231
232	destructor TIBUpdateSQLEditorForm.Destroy;
233	begin
234	if assigned(FIBSystemTables) then FIBSystemTables.Free;
235	if assigned(FModifySQL) then FModifySQL.Free;
236	if assigned(FInsertSQL) then FInsertSQL.Free;
237	if assigned(FDeleteSQL) then FDeleteSQL.Free;
238	if assigned(FRefreshSQL) then FRefreshSQL.Free;
239	inherited Destroy;
240	end;
241
242	procedure TIBUpdateSQLEditorForm.SetUpdateObject(AObject: TIBUpdateSQL);
243	begin
244	FUpdateObject := AObject;
245	if assigned(FUpdateObject.DataSet) then
246	FIBSystemTables.SelectDatabase(FUpdateObject.DataSet.Database,FUpdateObject.DataSet.Transaction);
247	end;
248
249
250	initialization
251	{$I ibupdatesqleditor.lrs}
252
253	end.
254