My favorites | Sign in
Project Home Source
Checkout   Browse   Changes  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
unit copytable;


interface

uses
Windows, SysUtils, Classes, Graphics, Controls, Forms, Dialogs, StdCtrls,
dbconnection, VirtualTrees, SynEdit, SynMemo, Menus;

type
TCopyTableForm = class(TForm)
editNewTablename: TEdit;
lblNewTablename: TLabel;
btnCancel: TButton;
comboDatabase: TComboBox;
btnOK: TButton;
TreeElements: TVirtualStringTree;
MemoFilter: TSynMemo;
lblItems: TLabel;
lblWhere: TLabel;
btnRecentFilters: TButton;
popupRecentFilters: TPopupMenu;
procedure editNewTablenameChange(Sender: TObject);
procedure FormShow(Sender: TObject);
procedure btnOKClick(Sender: TObject);
procedure FormCreate(Sender: TObject);
procedure TreeElementsGetText(Sender: TBaseVirtualTree; Node: PVirtualNode;
Column: TColumnIndex; TextType: TVSTTextType; var CellText: string);
procedure TreeElementsInitNode(Sender: TBaseVirtualTree; ParentNode, Node: PVirtualNode;
var InitialStates: TVirtualNodeInitStates);
procedure TreeElementsGetImageIndex(Sender: TBaseVirtualTree; Node: PVirtualNode;
Kind: TVTImageKind; Column: TColumnIndex; var Ghosted: Boolean; var ImageIndex: Integer);
procedure TreeElementsInitChildren(Sender: TBaseVirtualTree; Node: PVirtualNode;
var ChildCount: Cardinal);
procedure FormDestroy(Sender: TObject);
procedure TreeElementsChecked(Sender: TBaseVirtualTree; Node: PVirtualNode);
procedure FormClose(Sender: TObject; var Action: TCloseAction);
procedure btnRecentFiltersClick(Sender: TObject);
procedure RecentFilterClick(Sender: TObject);
private
{ Private declarations }
FDBObj: TDBObject;
FColumns: TTableColumnList;
FKeys: TTableKeyList;
FForeignKeys: TForeignKeyList;
public
{ Public declarations }
end;


implementation

uses helpers, main;

const
nColumns = 0;
nKeys = 1;
nForeignKeys = 2;
nData = 3;

{$R *.DFM}
{$I const.inc}



procedure TCopyTableForm.FormCreate(Sender: TObject);
begin
InheritFont(Font);
Width := GetRegValue(REGNAME_COPYTABLE_WINWIDTH, Width);
Height := GetRegValue(REGNAME_COPYTABLE_WINHEIGHT, Height);
SetWindowSizeGrip(Handle, True);
MainForm.SetupSynEditors;
FixVT(TreeElements);
FColumns := TTableColumnList.Create;
FKeys := TTableKeyList.Create;
FForeignKeys := TForeignKeyList.Create;
end;


procedure TCopyTableForm.FormDestroy(Sender: TObject);
begin
// Save GUI stuff
MainReg.WriteInteger(REGNAME_COPYTABLE_WINWIDTH, Width);
MainReg.WriteInteger(REGNAME_COPYTABLE_WINHEIGHT, Height);
end;


procedure TCopyTableForm.FormShow(Sender: TObject);
var
Filter: String;
Dummy: String;
Obj: PDBObject;
Values: TStringList;
i, j: Integer;
Item: TMenuItem;
Tree: TVirtualStringTree;
begin
if Mainform.DBtree.Focused then
Tree := Mainform.DBtree
else
Tree := Mainform.ListTables;
Obj := Tree.GetNodeData(Tree.FocusedNode);
FDBObj := Obj^;
editNewTablename.Text := FDBObj.Name + '_copy';
editNewTablename.SetFocus;
lblNewTablename.Caption := 'Copy ''' + FDBObj.Name + ''' to new db.table:';
editNewTablename.SetFocus;

// Select TargetDatabase
comboDatabase.Items.Clear;
comboDatabase.Items.Assign(Mainform.ActiveConnection.AllDatabases);
comboDatabase.ItemIndex := comboDatabase.Items.IndexOf(Mainform.ActiveDatabase);
if comboDatabase.ItemIndex = -1 then
comboDatabase.ItemIndex := 0;

// Fetch columns and key structures from table or view
FColumns.Clear;
FKeys.Clear;
FForeignKeys.Clear;
case FDBObj.NodeType of
lntTable: FDBObj.Connection.ParseTableStructure(FDBObj.CreateCode, FColumns, FKeys, FForeignKeys);
lntView: FDBObj.Connection.ParseViewStructure(FDBObj.CreateCode, FDBObj.Name, FColumns, Dummy, Dummy, Dummy, Dummy, Dummy);
else raise Exception.Create('Neither table nor view: '+FDBObj.Name);
end;

// Reset options tree
TreeElements.Clear;
TreeElements.RootNodeCount := 4;

// Load recent WHERE clauses from registry into dropdown menu
popupRecentFilters.Items.Clear;
OpenRegistry;
Values := TStringList.Create;
MainReg.GetValueNames(Values);
j := 0;
for i:=0 to Values.Count-1 do begin
if Pos(REGPREFIX_COPYTABLE_FILTERS, Values[i]) <> 1 then
continue;
Inc(j);
Filter := MainReg.ReadString(Values[i]);
Item := TMenuItem.Create(popupRecentFilters);
Item.Caption := IntToStr(j) + ' ' + sstr(Filter, 100);
Item.Hint := Filter;
Item.OnClick := RecentFilterClick;
popupRecentFilters.Items.Add(Item);
end;

end;



procedure TCopyTableForm.FormClose(Sender: TObject; var Action: TCloseAction);
var
Node: PVirtualNode;
Option, Filter: String;
Values, NewValues: TStringList;
i: Integer;
begin
// Save first level node check options
Node := TreeElements.GetFirst;
while Assigned(Node) do begin
case Node.Index of
nColumns: Option := REGNAME_COPYTABLE_COLUMNS;
nKeys: Option := REGNAME_COPYTABLE_KEYS;
nForeignKeys: Option := REGNAME_COPYTABLE_FOREIGN;
nData: Option := REGNAME_COPYTABLE_DATA;
else raise Exception.Create(SUnhandledNodeIndex);
end;
if not (vsDisabled in Node.States) then
MainReg.WriteBool(Option, Node.CheckState in CheckedStates);
Node := TreeElements.GetNextSibling(Node);
end;
// Store recent filters
if MemoFilter.Enabled and (MemoFilter.GetTextLen > 0) then begin
OpenRegistry;
Values := TStringList.Create;
NewValues := TStringList.Create;
NewValues.Add(MemoFilter.Text);
MainReg.GetValueNames(Values);
for i:=0 to Values.Count-1 do begin
if Pos(REGPREFIX_COPYTABLE_FILTERS, Values[i]) <> 1 then
continue;
Filter := MainReg.ReadString(Values[i]);
if NewValues.IndexOf(Filter) = -1 then
NewValues.Add(Filter);
MainReg.DeleteValue(Values[i]);
end;
for i:=0 to NewValues.Count-1 do begin
if i = 20 then
break;
MainReg.WriteString(REGPREFIX_COPYTABLE_FILTERS+IntToStr(i), NewValues[i]);
end;
end;
Action := caFree;
end;


procedure TCopyTableForm.TreeElementsChecked(Sender: TBaseVirtualTree; Node: PVirtualNode);
begin
// Disable WHERE memo if "Data" was unselected
if (Sender.GetNodeLevel(Node) = 0) and (Node.Index = nData) then begin
MemoFilter.Enabled := Node.CheckState = csCheckedNormal;
btnRecentFilters.Enabled := MemoFilter.Enabled;
if MemoFilter.Enabled then begin
MemoFilter.Highlighter := MainForm.SynSQLSyn1;
MemoFilter.Color := clWindow;
end else begin
MemoFilter.Highlighter := nil;
MemoFilter.Color := clBtnFace;
end;
end;
end;


procedure TCopyTableForm.TreeElementsGetImageIndex(Sender: TBaseVirtualTree; Node: PVirtualNode;
Kind: TVTImageKind; Column: TColumnIndex; var Ghosted: Boolean; var ImageIndex: Integer);
begin
// Get node index
if not (Kind in [ikNormal, ikSelected]) then Exit;
case Sender.GetNodeLevel(Node) of
0: case Node.Index of
nColumns: ImageIndex := ICONINDEX_FIELD;
nKeys: ImageIndex := 13;
nForeignKeys: ImageIndex := ICONINDEX_FOREIGNKEY;
nData: ImageIndex := 41;
else raise Exception.Create(SUnhandledNodeIndex);
end;
1: case Node.Parent.Index of
nColumns: ImageIndex := ICONINDEX_FIELD;
nKeys: ImageIndex := GetIndexIcon(FKeys[Node.Index].IndexType);
nForeignKeys: ImageIndex := ICONINDEX_FOREIGNKEY;
else raise Exception.Create(SUnhandledNodeIndex);
end;
end;
end;


procedure TCopyTableForm.TreeElementsGetText(Sender: TBaseVirtualTree; Node: PVirtualNode;
Column: TColumnIndex; TextType: TVSTTextType; var CellText: string);
var
CheckedCount: Integer;
Child: PVirtualNode;
begin
// Get node text
case Sender.GetNodeLevel(Node) of
0: begin
case Node.Index of
nColumns: CellText := 'Columns';
nKeys: CellText := 'Indexes';
nForeignKeys: CellText := 'Foreign keys';
nData: CellText := 'Data ('+FormatNumber(FDBObj.Rows)+' rows)';
else raise Exception.Create(SUnhandledNodeIndex);
end;
if Node.Index <> nData then begin
CheckedCount := 0;
Child := Sender.GetFirstChild(Node);
while Assigned(Child) do begin
if Child.CheckState in CheckedStates then
Inc(CheckedCount);
Child := Sender.GetNextSibling(Child);
end;
CellText := CellText + ' ('+FormatNumber(CheckedCount)+'/'+FormatNumber(Sender.ChildCount[Node])+')';
end;
end;
1: case Node.Parent.Index of
nColumns: CellText := FColumns[Node.Index].Name;
nKeys: CellText := FKeys[Node.Index].Name;
nForeignKeys: CellText := FForeignKeys[Node.Index].KeyName;
else raise Exception.Create(SUnhandledNodeIndex);
end;
end;
end;


procedure TCopyTableForm.TreeElementsInitChildren(Sender: TBaseVirtualTree; Node: PVirtualNode;
var ChildCount: Cardinal);
begin
// Set child node count
case Sender.GetNodeLevel(Node) of
0: case Node.Index of
nColumns: ChildCount := FColumns.Count;
nKeys: ChildCount := FKeys.Count;
nForeignKeys: ChildCount := FForeignKeys.Count;
nData: ChildCount := 0;
else raise Exception.Create(SUnhandledNodeIndex);
end;
else ChildCount := 0;
end;
end;


procedure TCopyTableForm.TreeElementsInitNode(Sender: TBaseVirtualTree; ParentNode,
Node: PVirtualNode; var InitialStates: TVirtualNodeInitStates);
var
Option: String;
ChildCount: Integer;
begin
// First three upper nodes mostly have child nodes
Node.CheckType := ctTriStateCheckBox;
case Sender.GetNodeLevel(Node) of
0: begin
case Node.Index of
nColumns: begin Option := REGNAME_COPYTABLE_COLUMNS; ChildCount := FColumns.Count; end;
nKeys: begin Option := REGNAME_COPYTABLE_KEYS; ChildCount := FKeys.Count; end;
nForeignKeys: begin Option := REGNAME_COPYTABLE_FOREIGN; ChildCount := FForeignKeys.Count; end;
nData: begin Option := REGNAME_COPYTABLE_DATA; ChildCount := -1; end;
else raise Exception.Create(SUnhandledNodeIndex);
end;
if ChildCount > 0 then
Include(InitialStates, ivsHasChildren);
if (ChildCount = 0) or ((Node.Index = nData) and (FDBObj.Rows = 0)) then
Node.States := Node.States + [vsDisabled]
else if GetRegValue(Option, True) then
Node.CheckState := csCheckedNormal;
(Sender as TVirtualStringTree).OnChecked(Sender, Node);
end;

1: if Node.Parent.CheckState in CheckedStates then
Node.CheckState := csCheckedNormal;
end;
end;


procedure TCopyTableForm.btnRecentFiltersClick(Sender: TObject);
var
btn: TButton;
begin
// A split button does not drop its menu down when the normal button area is clicked. Do that by hand.
btn := Sender as TButton;
btn.DropDownMenu.Popup(btn.ClientOrigin.X, btn.ClientOrigin.Y+btn.Height);
end;


procedure TCopyTableForm.RecentFilterClick(Sender: TObject);
var
Item: TMenuItem;
begin
// Load recent filter
Item := Sender as TMenuItem;
MemoFilter.SelectAll;
MemoFilter.SelText := Item.Hint;
end;


procedure TCopyTableForm.editNewTablenameChange(Sender: TObject);
begin
// Disable OK button as long as table name is empty
btnOK.Enabled := editNewTablename.Text <> '';
end;


procedure TCopyTableForm.btnOKClick(Sender: TObject);
var
CreateCode, InsertCode, TargetTable, Clause, DataCols, TableExistance: String;
ParentNode, Node: PVirtualNode;
DoData: Boolean;
begin
// Compose and run CREATE query
TargetTable := FDBObj.Connection.QuoteIdent(comboDatabase.Text)+'.'+FDBObj.Connection.QuoteIdent(editNewTablename.Text);
TableExistance := FDBObj.Connection.GetVar('SHOW TABLES FROM '+FDBObj.Connection.QuoteIdent(comboDatabase.Text)+' LIKE '+esc(editNewTablename.Text));
if TableExistance <> '' then begin
if MessageDialog('Target table exists. Drop it and overwrite?', mtConfirmation, [mbYes, mbCancel]) = mrCancel then begin
ModalResult := mrNone;
Exit;
end;
FDBObj.Connection.Query('DROP TABLE '+TargetTable);
end;

Screen.Cursor := crHourglass;
MainForm.ShowStatusMsg('Generating SQL code ...');
DataCols := '';
DoData := False;
ParentNode := TreeElements.GetFirst;
while Assigned(ParentNode) do begin
Node := TreeElements.GetFirstChild(ParentNode);
while Assigned(Node) do begin
if Node.CheckState in CheckedStates then begin
case ParentNode.Index of
nColumns: begin
Clause := FColumns[Node.Index].SQLCode;
DataCols := DataCols + FDBObj.Connection.QuoteIdent(FColumns[Node.Index].Name) + ', ';
end;
nKeys: Clause := FKeys[Node.Index].SQLCode;
nForeignkeys: Clause := FForeignKeys[Node.Index].SQLCode(False);
else raise Exception.Create(SUnhandledNodeIndex);
end;
CreateCode := CreateCode + #9 + Clause + ',' + CRLF;
end;
Node := TreeElements.GetNextSibling(Node);
end;
if (ParentNode.Index = nData) then
DoData := ParentNode.CheckState in CheckedStates;
ParentNode := TreeElements.GetNextSibling(ParentNode);
end;
Delete(CreateCode, Length(CreateCode)-2, 3);
CreateCode := 'CREATE TABLE '+TargetTable+' ('+CRLF+CreateCode+CRLF+')'+CRLF;

// Add collation and engine clauses
if FDBObj.Collation <> '' then
CreateCode := CreateCode + ' COLLATE ' + esc(FDBObj.Collation);
if FDBObj.Engine <> '' then begin
if MainForm.ActiveConnection.ServerVersionInt < 40018 then
CreateCode := CreateCode + ' TYPE=' + FDBObj.Engine
else
CreateCode := CreateCode + ' ENGINE=' + FDBObj.Engine;
end;
if FDBObj.RowFormat <> '' then
CreateCode := CreateCode + ' ROW_FORMAT=' + FDBObj.RowFormat;
if FDBObj.AutoInc > -1 then
CreateCode := CreateCode + ' AUTO_INCREMENT=' + IntToStr(FDBObj.AutoInc);
CreateCode := CreateCode + ' COMMENT=' + esc(FDBObj.Comment);

// Add INSERT .. SELECT .. FROM OrgTable clause
InsertCode := '';
if DoData and (DataCols <> '') then begin
DataCols := Trim(DataCols);
Delete(DataCols, Length(DataCols), 1);
InsertCode := 'INSERT INTO '+TargetTable+' ('+DataCols+') SELECT ' + DataCols + ' FROM ' + FDBObj.QuotedName;
if MemoFilter.GetTextLen > 0 then
InsertCode := InsertCode + ' WHERE ' + MemoFilter.Text;
end;

// Run query and refresh list
try
MainForm.ShowStatusMsg('Creating table ...');
MainForm.ActiveConnection.Query(CreateCode);
if InsertCode <> '' then
MainForm.ActiveConnection.Query(InsertCode);
MainForm.actRefresh.Execute;
except
on E:EDatabaseError do begin
Screen.Cursor := crDefault;
ErrorDialog(E.Message);
ModalResult := mrNone;
end;
end;
MainForm.ShowStatusMsg;
Screen.Cursor := crDefault;
end;

end.

Change log

r4137 by ansgar.becker on May 1, 2012   Diff
Remove unused unit inclusions, detected by
CNPack/Uses cleaner
Go to: 

Older revisions

r4037 by ansgar.becker on Feb 3, 2012   Diff
Export whole CREATE VIEW code, not
only the VIEW body. Fixes  issue #2625 .
r3897 by ansgar.becker on Jun 27, 2011   Diff
Quote collation clause in all places.
Fixes  issue #2479 . Related:  issue
#1852 .
r3896 by ansgar.becker on Jun 26, 2011   Diff
Do not cache dialog instances any
longer. OnCreate code is mostly very
quick, and caching them adds quite
some pitfalls into the code. Not to
mention 30 lines less code in main.pas
...
All revisions of this file

File info

Size: 15333 bytes, 441 lines
Powered by Google Project Hosting