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
#	User	Rev	Content
1	tony	5	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