My favorites | Sign in
Project Home Source
Checkout   Browse   Changes  
Changes to /trunk/source/tabletools.pas
r4158 vs. r4159 Compare: vs.  Format:
Revision r4159
Go to: 
/trunk/source/tabletools.pas   r4158 /trunk/source/tabletools.pas   r4159
1 unit tabletools; 1 unit tabletools;
2 2
3 3
4 // ------------------------------------- 4 // -------------------------------------
5 // Table-diagnostics 5 // Table-diagnostics
6 // ------------------------------------- 6 // -------------------------------------
7 7
8 8
9 interface 9 interface
10 10
11 uses 11 uses
12 Windows, SysUtils, Classes, Controls, Forms, StdCtrls, ComCtrls, Buttons, Dialogs, StdActns, 12 Windows, SysUtils, Classes, Controls, Forms, StdCtrls, ComCtrls, Buttons, Dialogs, StdActns,
13 VirtualTrees, ExtCtrls, Graphics, SynRegExpr, Math, Generics.Collections, 13 VirtualTrees, ExtCtrls, Graphics, SynRegExpr, Math, Generics.Collections,
14 dbconnection, helpers, Menus; 14 dbconnection, helpers, Menus;
15 15
16 type 16 type
17 TToolMode = (tmMaintenance, tmFind, tmSQLExport, tmBulkTableEdit); 17 TToolMode = (tmMaintenance, tmFind, tmSQLExport, tmBulkTableEdit);
18 TfrmTableTools = class(TForm) 18 TfrmTableTools = class(TForm)
19 btnCloseOrCancel: TButton; 19 btnCloseOrCancel: TButton;
20 pnlTop: TPanel; 20 pnlTop: TPanel;
21 TreeObjects: TVirtualStringTree; 21 TreeObjects: TVirtualStringTree;
22 spltHorizontally: TSplitter; 22 spltHorizontally: TSplitter;
23 pnlRight: TPanel; 23 pnlRight: TPanel;
24 ResultGrid: TVirtualStringTree; 24 ResultGrid: TVirtualStringTree;
25 tabsTools: TPageControl; 25 tabsTools: TPageControl;
26 tabMaintenance: TTabSheet; 26 tabMaintenance: TTabSheet;
27 comboOperation: TComboBox; 27 comboOperation: TComboBox;
28 lblOperation: TLabel; 28 lblOperation: TLabel;
29 chkQuick: TCheckBox; 29 chkQuick: TCheckBox;
30 chkFast: TCheckBox; 30 chkFast: TCheckBox;
31 chkMedium: TCheckBox; 31 chkMedium: TCheckBox;
32 chkExtended: TCheckBox; 32 chkExtended: TCheckBox;
33 chkChanged: TCheckBox; 33 chkChanged: TCheckBox;
34 chkUseFrm: TCheckBox; 34 chkUseFrm: TCheckBox;
35 lblOptions: TLabel; 35 lblOptions: TLabel;
36 btnHelp: TButton; 36 btnHelp: TButton;
37 tabFind: TTabSheet; 37 tabFind: TTabSheet;
38 lblFindText: TLabel; 38 lblFindText: TLabel;
39 memoFindText: TMemo; 39 memoFindText: TMemo;
40 comboDataTypes: TComboBox; 40 comboDataTypes: TComboBox;
41 lblDataTypes: TLabel; 41 lblDataTypes: TLabel;
42 tabSQLexport: TTabSheet; 42 tabSQLexport: TTabSheet;
43 chkExportDatabasesCreate: TCheckBox; 43 chkExportDatabasesCreate: TCheckBox;
44 chkExportDatabasesDrop: TCheckBox; 44 chkExportDatabasesDrop: TCheckBox;
45 chkExportTablesDrop: TCheckBox; 45 chkExportTablesDrop: TCheckBox;
46 chkExportTablesCreate: TCheckBox; 46 chkExportTablesCreate: TCheckBox;
47 lblExportData: TLabel; 47 lblExportData: TLabel;
48 comboExportData: TComboBox; 48 comboExportData: TComboBox;
49 lblExportOutputType: TLabel; 49 lblExportOutputType: TLabel;
50 comboExportOutputType: TComboBox; 50 comboExportOutputType: TComboBox;
51 comboExportOutputTarget: TComboBox; 51 comboExportOutputTarget: TComboBox;
52 lblExportDatabases: TLabel; 52 lblExportDatabases: TLabel;
53 lblExportTables: TLabel; 53 lblExportTables: TLabel;
54 lblExportOutputTarget: TLabel; 54 lblExportOutputTarget: TLabel;
55 btnExecute: TButton; 55 btnExecute: TButton;
56 btnExportOutputTargetSelect: TButton; 56 btnExportOutputTargetSelect: TButton;
57 tabBulkTableEdit: TTabSheet; 57 tabBulkTableEdit: TTabSheet;
58 chkBulkTableEditDatabase: TCheckBox; 58 chkBulkTableEditDatabase: TCheckBox;
59 comboBulkTableEditDatabase: TComboBox; 59 comboBulkTableEditDatabase: TComboBox;
60 chkBulkTableEditResetAutoinc: TCheckBox; 60 chkBulkTableEditResetAutoinc: TCheckBox;
61 chkBulkTableEditCollation: TCheckBox; 61 chkBulkTableEditCollation: TCheckBox;
62 comboBulkTableEditCollation: TComboBox; 62 comboBulkTableEditCollation: TComboBox;
63 chkBulkTableEditEngine: TCheckBox; 63 chkBulkTableEditEngine: TCheckBox;
64 comboBulkTableEditEngine: TComboBox; 64 comboBulkTableEditEngine: TComboBox;
65 chkBulkTableEditCharset: TCheckBox; 65 chkBulkTableEditCharset: TCheckBox;
66 comboBulkTableEditCharset: TComboBox; 66 comboBulkTableEditCharset: TComboBox;
67 btnSeeResults: TButton; 67 btnSeeResults: TButton;
68 chkCaseSensitive: TCheckBox; 68 chkCaseSensitive: TCheckBox;
69 lblCheckedSize: TLabel; 69 lblCheckedSize: TLabel;
70 popupTree: TPopupMenu; 70 popupTree: TPopupMenu;
71 menuCheckAll: TMenuItem; 71 menuCheckAll: TMenuItem;
72 menuCheckByType: TMenuItem; 72 menuCheckByType: TMenuItem;
73 menuCheckNone: TMenuItem; 73 menuCheckNone: TMenuItem;
74 procedure FormDestroy(Sender: TObject); 74 procedure FormDestroy(Sender: TObject);
75 procedure FormCreate(Sender: TObject); 75 procedure FormCreate(Sender: TObject);
76 procedure FormShow(Sender: TObject); 76 procedure FormShow(Sender: TObject);
77 procedure btnHelpClick(Sender: TObject); 77 procedure btnHelpClick(Sender: TObject);
78 procedure TreeObjectsGetText(Sender: TBaseVirtualTree; Node: PVirtualNode; Column: TColumnIndex; 78 procedure TreeObjectsGetText(Sender: TBaseVirtualTree; Node: PVirtualNode; Column: TColumnIndex;
79 TextType: TVSTTextType; var CellText: String); 79 TextType: TVSTTextType; var CellText: String);
80 procedure TreeObjectsInitNode(Sender: TBaseVirtualTree; ParentNode, Node: PVirtualNode; 80 procedure TreeObjectsInitNode(Sender: TBaseVirtualTree; ParentNode, Node: PVirtualNode;
81 var InitialStates: TVirtualNodeInitStates); 81 var InitialStates: TVirtualNodeInitStates);
82 procedure TreeObjectsGetImageIndex(Sender: TBaseVirtualTree; Node: PVirtualNode; Kind: TVTImageKind; 82 procedure TreeObjectsGetImageIndex(Sender: TBaseVirtualTree; Node: PVirtualNode; Kind: TVTImageKind;
83 Column: TColumnIndex; var Ghosted: Boolean; var ImageIndex: Integer); 83 Column: TColumnIndex; var Ghosted: Boolean; var ImageIndex: Integer);
84 procedure TreeObjectsInitChildren(Sender: TBaseVirtualTree; Node: PVirtualNode; var ChildCount: Cardinal); 84 procedure TreeObjectsInitChildren(Sender: TBaseVirtualTree; Node: PVirtualNode; var ChildCount: Cardinal);
85 procedure Execute(Sender: TObject); 85 procedure Execute(Sender: TObject);
86 procedure ResultGridInitNode(Sender: TBaseVirtualTree; ParentNode, Node: PVirtualNode; 86 procedure ResultGridInitNode(Sender: TBaseVirtualTree; ParentNode, Node: PVirtualNode;
87 var InitialStates: TVirtualNodeInitStates); 87 var InitialStates: TVirtualNodeInitStates);
88 procedure ResultGridGetNodeDataSize(Sender: TBaseVirtualTree; var NodeDataSize: Integer); 88 procedure ResultGridGetNodeDataSize(Sender: TBaseVirtualTree; var NodeDataSize: Integer);
89 procedure ResultGridGetText(Sender: TBaseVirtualTree; Node: PVirtualNode; Column: TColumnIndex; 89 procedure ResultGridGetText(Sender: TBaseVirtualTree; Node: PVirtualNode; Column: TColumnIndex;
90 TextType: TVSTTextType; var CellText: String); 90 TextType: TVSTTextType; var CellText: String);
91 procedure TreeObjectsChecked(Sender: TBaseVirtualTree; Node: PVirtualNode); 91 procedure TreeObjectsChecked(Sender: TBaseVirtualTree; Node: PVirtualNode);
92 procedure ResultGridHeaderClick(Sender: TVTHeader; HitInfo: TVTHeaderHitInfo); 92 procedure ResultGridHeaderClick(Sender: TVTHeader; HitInfo: TVTHeaderHitInfo);
93 procedure ResultGridCompareNodes(Sender: TBaseVirtualTree; Node1, Node2: PVirtualNode; 93 procedure ResultGridCompareNodes(Sender: TBaseVirtualTree; Node1, Node2: PVirtualNode;
94 Column: TColumnIndex; var Result: Integer); 94 Column: TColumnIndex; var Result: Integer);
95 procedure ResultGridPaintText(Sender: TBaseVirtualTree; const TargetCanvas: TCanvas; Node: PVirtualNode; 95 procedure ResultGridPaintText(Sender: TBaseVirtualTree; const TargetCanvas: TCanvas; Node: PVirtualNode;
96 Column: TColumnIndex; TextType: TVSTTextType); 96 Column: TColumnIndex; TextType: TVSTTextType);
97 procedure ValidateControls(Sender: TObject); 97 procedure ValidateControls(Sender: TObject);
98 procedure SaveSettings(Sender: TObject); 98 procedure SaveSettings(Sender: TObject);
99 procedure chkExportOptionClick(Sender: TObject); 99 procedure chkExportOptionClick(Sender: TObject);
100 procedure btnExportOutputTargetSelectClick(Sender: TObject); 100 procedure btnExportOutputTargetSelectClick(Sender: TObject);
101 procedure comboExportOutputTargetChange(Sender: TObject); 101 procedure comboExportOutputTargetChange(Sender: TObject);
102 procedure comboExportOutputTypeChange(Sender: TObject); 102 procedure comboExportOutputTypeChange(Sender: TObject);
103 procedure FormClose(Sender: TObject; var Action: TCloseAction); 103 procedure FormClose(Sender: TObject; var Action: TCloseAction);
104 procedure TreeObjectsPaintText(Sender: TBaseVirtualTree; const TargetCanvas: TCanvas; Node: PVirtualNode; 104 procedure TreeObjectsPaintText(Sender: TBaseVirtualTree; const TargetCanvas: TCanvas; Node: PVirtualNode;
105 Column: TColumnIndex; TextType: TVSTTextType); 105 Column: TColumnIndex; TextType: TVSTTextType);
106 procedure chkBulkTableEditCheckComboClick(Sender: TObject); 106 procedure chkBulkTableEditCheckComboClick(Sender: TObject);
107 procedure TreeObjectsChange(Sender: TBaseVirtualTree; Node: PVirtualNode); 107 procedure TreeObjectsChange(Sender: TBaseVirtualTree; Node: PVirtualNode);
108 procedure TreeObjectsChecking(Sender: TBaseVirtualTree; Node: PVirtualNode; var NewState: TCheckState; 108 procedure TreeObjectsChecking(Sender: TBaseVirtualTree; Node: PVirtualNode; var NewState: TCheckState;
109 var Allowed: Boolean); 109 var Allowed: Boolean);
110 procedure btnSeeResultsClick(Sender: TObject); 110 procedure btnSeeResultsClick(Sender: TObject);
111 procedure TreeObjectsGetNodeDataSize(Sender: TBaseVirtualTree; var NodeDataSize: Integer); 111 procedure TreeObjectsGetNodeDataSize(Sender: TBaseVirtualTree; var NodeDataSize: Integer);
112 procedure btnCloseOrCancelClick(Sender: TObject); 112 procedure btnCloseOrCancelClick(Sender: TObject);
113 procedure TreeObjectsBeforeCellPaint(Sender: TBaseVirtualTree; TargetCanvas: TCanvas; 113 procedure TreeObjectsBeforeCellPaint(Sender: TBaseVirtualTree; TargetCanvas: TCanvas;
114 Node: PVirtualNode; Column: TColumnIndex; CellPaintMode: TVTCellPaintMode; CellRect: TRect; 114 Node: PVirtualNode; Column: TColumnIndex; CellPaintMode: TVTCellPaintMode; CellRect: TRect;
115 var ContentRect: TRect); 115 var ContentRect: TRect);
116 procedure CheckAllClick(Sender: TObject); 116 procedure CheckAllClick(Sender: TObject);
117 private 117 private
118 { Private declarations } 118 { Private declarations }
119 FResults: TObjectList<TStringList>; 119 FResults: TObjectList<TStringList>;
120 FToolMode: TToolMode; 120 FToolMode: TToolMode;
121 FSecondExportPass: Boolean; // Set to True after everything is exported and final VIEWs need to be exported again 121 FSecondExportPass: Boolean; // Set to True after everything is exported and final VIEWs need to be exported again
122 FCancelled: Boolean; 122 FCancelled: Boolean;
123 ExportStream: TStream; 123 ExportStream: TStream;
124 ExportStreamStartOfQueryPos: Int64; 124 ExportStreamStartOfQueryPos: Int64;
125 ExportLastDatabase: String; 125 ExportLastDatabase: String;
126 FTargetConnection: TDBConnection; 126 FTargetConnection: TDBConnection;
127 FLastOutputSelectedIndex: Integer; 127 FLastOutputSelectedIndex: Integer;
128 FModifiedDbs: TStringList; 128 FModifiedDbs: TStringList;
129 FHeaderCreated: Boolean; 129 FHeaderCreated: Boolean;
130 FFindSeeResultSQL: TStringList; 130 FFindSeeResultSQL: TStringList;
131 ToFile, ToDir, ToClipboard, ToDb, ToServer: Boolean; 131 ToFile, ToDir, ToClipboard, ToDb, ToServer: Boolean;
132 FObjectSizes, FObjectSizesDone, FObjectSizesDoneExact: Int64; 132 FObjectSizes, FObjectSizesDone, FObjectSizesDoneExact: Int64;
133 procedure SetToolMode(Value: TToolMode); 133 procedure SetToolMode(Value: TToolMode);
134 procedure Output(SQL: String; IsEndOfQuery, ForFile, ForDir, ForDb, ForServer: Boolean); 134 procedure Output(SQL: String; IsEndOfQuery, ForFile, ForDir, ForDb, ForServer: Boolean);
135 procedure AddResults(SQL: String); 135 procedure AddResults(SQL: String);
136 procedure AddNotes(Col1, Col2, Col3, Col4: String); 136 procedure AddNotes(Col1, Col2, Col3, Col4: String);
137 procedure SetupResultGrid(Results: TDBQuery=nil); 137 procedure SetupResultGrid(Results: TDBQuery=nil);
138 procedure UpdateResultGrid; 138 procedure UpdateResultGrid;
139 procedure DoMaintenance(DBObj: TDBObject); 139 procedure DoMaintenance(DBObj: TDBObject);
140 procedure DoFind(DBObj: TDBObject); 140 procedure DoFind(DBObj: TDBObject);
141 procedure DoExport(DBObj: TDBObject); 141 procedure DoExport(DBObj: TDBObject);
142 procedure DoBulkTableEdit(DBObj: TDBObject); 142 procedure DoBulkTableEdit(DBObj: TDBObject);
143 public 143 public
144 { Public declarations } 144 { Public declarations }
145 PreSelectObjects: TDBObjectList; 145 PreSelectObjects: TDBObjectList;
146 property ToolMode: TToolMode read FToolMode write SetToolMode; 146 property ToolMode: TToolMode read FToolMode write SetToolMode;
147 end; 147 end;
148 148
149 149
150 implementation 150 implementation
151 151
152 uses main, mysql_structures; 152 uses main, mysql_structures;
153 153
154 const 154 const
155 STRSKIPPED: String = 'Skipped - '; 155 STRSKIPPED: String = 'Skipped - ';
156 OUTPUT_FILE = 'One big file'; 156 OUTPUT_FILE = 'One big file';
157 OUTPUT_CLIPBOARD = 'Clipboard'; 157 OUTPUT_CLIPBOARD = 'Clipboard';
158 OUTPUT_DIR = 'Directory - one file per object in database subdirectories'; 158 OUTPUT_DIR = 'Directory - one file per object in database subdirectories';
159 OUTPUT_DB = 'Database'; 159 OUTPUT_DB = 'Database';
160 OUTPUT_SERVER = 'Server: '; 160 OUTPUT_SERVER = 'Server: ';
161 DATA_NO = 'No data'; 161 DATA_NO = 'No data';
162 DATA_REPLACE = 'DELETE + INSERT (truncate existing data)'; 162 DATA_REPLACE = 'DELETE + INSERT (truncate existing data)';
163 DATA_INSERT = 'INSERT'; 163 DATA_INSERT = 'INSERT';
164 DATA_INSERTNEW = 'INSERT IGNORE (do not update existing)'; 164 DATA_INSERTNEW = 'INSERT IGNORE (do not update existing)';
165 DATA_UPDATE = 'REPLACE existing data'; 165 DATA_UPDATE = 'REPLACE existing data';
166 EXPORT_FILE_FOOTER = '/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '''') */;'+CRLF+ 166 EXPORT_FILE_FOOTER = '/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '''') */;'+CRLF+
167 '/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;'+CRLF+ 167 '/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;'+CRLF+
168 '/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;'+CRLF; 168 '/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;'+CRLF;
169 169
170 {$R *.DFM} 170 {$R *.DFM}
171 171
172 172
173 procedure TfrmTableTools.FormCreate(Sender: TObject); 173 procedure TfrmTableTools.FormCreate(Sender: TObject);
174 var 174 var
175 i: Integer; 175 i: Integer;
176 dtc: TDBDatatypeCategoryIndex; 176 dtc: TDBDatatypeCategoryIndex;
177 SessionNames: TStringList; 177 SessionNames: TStringList;
178 MenuItem: TMenuItem; 178 MenuItem: TMenuItem;
179 dt: TListNodeType; 179 dt: TListNodeType;
180 Obj: TDBObject; 180 Obj: TDBObject;
181 begin 181 begin
182 // Restore GUI setup 182 // Restore GUI setup
183 InheritFont(Font); 183 InheritFont(Font);
184 Width := GetRegValue(REGNAME_TOOLSWINWIDTH, Width); 184 Width := GetRegValue(REGNAME_TOOLSWINWIDTH, Width);
185 Height := GetRegValue(REGNAME_TOOLSWINHEIGHT, Height); 185 Height := GetRegValue(REGNAME_TOOLSWINHEIGHT, Height);
186 TreeObjects.Width := GetRegValue(REGNAME_TOOLSTREEWIDTH, TreeObjects.Width); 186 TreeObjects.Width := GetRegValue(REGNAME_TOOLSTREEWIDTH, TreeObjects.Width);
187 187
188 // Find text tab 188 // Find text tab
189 memoFindText.Text := GetRegValue(REGNAME_TOOLSFINDTEXT, ''); 189 memoFindText.Text := GetRegValue(REGNAME_TOOLSFINDTEXT, '');
190 comboDatatypes.Items.Add('All data types'); 190 comboDatatypes.Items.Add('All data types');
191 for dtc:=Low(DatatypeCategories) to High(DatatypeCategories) do 191 for dtc:=Low(DatatypeCategories) to High(DatatypeCategories) do
192 comboDatatypes.Items.Add(DatatypeCategories[dtc].Name); 192 comboDatatypes.Items.Add(DatatypeCategories[dtc].Name);
193 comboDatatypes.ItemIndex := GetRegValue(REGNAME_TOOLSDATATYPE, 0); 193 comboDatatypes.ItemIndex := GetRegValue(REGNAME_TOOLSDATATYPE, 0);
194 chkCaseSensitive.Checked := GetRegValue(REGNAME_TOOLSCASESENSITIVE, chkCaseSensitive.Checked); 194 chkCaseSensitive.Checked := GetRegValue(REGNAME_TOOLSCASESENSITIVE, chkCaseSensitive.Checked);
195 195
196 // SQL export tab 196 // SQL export tab
197 chkExportDatabasesCreate.Checked := GetRegValue(REGNAME_EXP_CREATEDB, chkExportDatabasesCreate.Checked); 197 chkExportDatabasesCreate.Checked := GetRegValue(REGNAME_EXP_CREATEDB, chkExportDatabasesCreate.Checked);
198 chkExportDatabasesDrop.Checked := GetRegValue(REGNAME_EXP_DROPDB, chkExportDatabasesDrop.Checked); 198 chkExportDatabasesDrop.Checked := GetRegValue(REGNAME_EXP_DROPDB, chkExportDatabasesDrop.Checked);
199 chkExportTablesCreate.Checked := GetRegValue(REGNAME_EXP_CREATETABLE, chkExportTablesCreate.Checked); 199 chkExportTablesCreate.Checked := GetRegValue(REGNAME_EXP_CREATETABLE, chkExportTablesCreate.Checked);
200 chkExportTablesDrop.Checked := GetRegValue(REGNAME_EXP_DROPTABLE, chkExportTablesDrop.Checked); 200 chkExportTablesDrop.Checked := GetRegValue(REGNAME_EXP_DROPTABLE, chkExportTablesDrop.Checked);
201 comboExportData.Items.Text := DATA_NO+CRLF +DATA_REPLACE+CRLF +DATA_INSERT+CRLF +DATA_INSERTNEW+CRLF +DATA_UPDATE; 201 comboExportData.Items.Text := DATA_NO+CRLF +DATA_REPLACE+CRLF +DATA_INSERT+CRLF +DATA_INSERTNEW+CRLF +DATA_UPDATE;
202 comboExportData.ItemIndex := GetRegValue(REGNAME_EXP_DATAHOW, 0); 202 comboExportData.ItemIndex := GetRegValue(REGNAME_EXP_DATAHOW, 0);
203 // Add hardcoded output options and session names from registry 203 // Add hardcoded output options and session names from registry
204 comboExportOutputType.Items.Text := OUTPUT_FILE+CRLF +OUTPUT_DIR+CRLF +OUTPUT_CLIPBOARD+CRLF +OUTPUT_DB; 204 comboExportOutputType.Items.Text := OUTPUT_FILE+CRLF +OUTPUT_DIR+CRLF +OUTPUT_CLIPBOARD+CRLF +OUTPUT_DB;
205 SessionNames := TStringList.Create; 205 SessionNames := TStringList.Create;
206 MainReg.OpenKey(RegPath + REGKEY_SESSIONS, True); 206 MainReg.OpenKey(RegPath + REGKEY_SESSIONS, True);
207 MainReg.GetKeyNames(SessionNames); 207 MainReg.GetKeyNames(SessionNames);
208 for i:=0 to SessionNames.Count-1 do begin 208 for i:=0 to SessionNames.Count-1 do begin
209 if SessionNames[i] <> Mainform.ActiveConnection.Parameters.SessionName then 209 if SessionNames[i] <> Mainform.ActiveConnection.Parameters.SessionName then
210 comboExportOutputType.Items.Add(OUTPUT_SERVER+SessionNames[i]); 210 comboExportOutputType.Items.Add(OUTPUT_SERVER+SessionNames[i]);
211 end; 211 end;
212 comboExportOutputTarget.Text := ''; 212 comboExportOutputTarget.Text := '';
213 213
214 // Various 214 // Various
215 SetWindowSizeGrip( Self.Handle, True ); 215 SetWindowSizeGrip( Self.Handle, True );
216 FixVT(TreeObjects); 216 FixVT(TreeObjects);
217 FixVT(ResultGrid); 217 FixVT(ResultGrid);
218 FResults := TObjectList<TStringList>.Create; 218 FResults := TObjectList<TStringList>.Create;
219 PreSelectObjects := TDBObjectList.Create(False); 219 PreSelectObjects := TDBObjectList.Create(False);
220 FModifiedDbs := TStringList.Create; 220 FModifiedDbs := TStringList.Create;
221 FModifiedDbs.Duplicates := dupIgnore; 221 FModifiedDbs.Duplicates := dupIgnore;
222 FFindSeeResultSQL := TStringList.Create; 222 FFindSeeResultSQL := TStringList.Create;
223 223
224 // Popup menu 224 // Popup menu
225 Obj := TDBObject.Create(nil); 225 Obj := TDBObject.Create(nil);
226 for dt:=lntTable to lntEvent do begin 226 for dt:=lntTable to lntEvent do begin
227 Obj.NodeType := dt; 227 Obj.NodeType := dt;
228 MenuItem := TMenuItem.Create(menuCheckByType); 228 MenuItem := TMenuItem.Create(menuCheckByType);
229 MenuItem.Caption := UpperCase(Obj.ObjType)+'s'; 229 MenuItem.Caption := UpperCase(Obj.ObjType)+'s';
230 MenuItem.ImageIndex := Obj.ImageIndex; 230 MenuItem.ImageIndex := Obj.ImageIndex;
231 MenuItem.OnClick := CheckAllClick; 231 MenuItem.OnClick := CheckAllClick;
232 MenuItem.Tag := Integer(dt); 232 MenuItem.Tag := Integer(dt);
233 menuCheckByType.Add(MenuItem); 233 menuCheckByType.Add(MenuItem);
234 end; 234 end;
235 Obj.Free; 235 Obj.Free;
236 end; 236 end;
237 237
238 238
239 procedure TfrmTableTools.FormDestroy(Sender: TObject); 239 procedure TfrmTableTools.FormDestroy(Sender: TObject);
240 begin 240 begin
241 // Save GUI setup 241 // Save GUI setup
242 OpenRegistry; 242 OpenRegistry;
243 MainReg.WriteInteger( REGNAME_TOOLSWINWIDTH, Width ); 243 MainReg.WriteInteger( REGNAME_TOOLSWINWIDTH, Width );
244 MainReg.WriteInteger( REGNAME_TOOLSWINHEIGHT, Height ); 244 MainReg.WriteInteger( REGNAME_TOOLSWINHEIGHT, Height );
245 MainReg.WriteInteger( REGNAME_TOOLSTREEWIDTH, TreeObjects.Width); 245 MainReg.WriteInteger( REGNAME_TOOLSTREEWIDTH, TreeObjects.Width);
246 end; 246 end;
247 247
248 248
249 procedure TfrmTableTools.FormShow(Sender: TObject); 249 procedure TfrmTableTools.FormShow(Sender: TObject);
250 var 250 var
251 Node, FirstChecked: PVirtualNode; 251 Node, FirstChecked: PVirtualNode;
252 idx: Integer; 252 idx: Integer;
253 DBObj: TDBObject; 253 DBObj: TDBObject;
254 begin 254 begin
255 // When this form is displayed the second time, databases may be deleted or filtered. 255 // When this form is displayed the second time, databases may be deleted or filtered.
256 // Also, checked nodes must be unchecked and unchecked nodes may need to be checked. 256 // Also, checked nodes must be unchecked and unchecked nodes may need to be checked.
257 TreeObjects.Clear; 257 TreeObjects.Clear;
258 TreeObjects.RootNodeCount := Mainform.DBtree.RootNodeCount; 258 TreeObjects.RootNodeCount := Mainform.DBtree.RootNodeCount;
259 259
260 FObjectSizes := 0; 260 FObjectSizes := 0;
261 261
262 // Init all objects in active database, so the tree does not just check the db node 262 // Init all objects in active database, so the tree does not just check the db node
263 // if we want the first child only. See issue #2267. 263 // if we want the first child only. See issue #2267.
264 Node := MainForm.FindDBNode(TreeObjects, MainForm.ActiveConnection, MainForm.ActiveDatabase); 264 Node := MainForm.FindDBNode(TreeObjects, MainForm.ActiveConnection, MainForm.ActiveDatabase);
265 Node := TreeObjects.GetFirstChild(Node); 265 Node := TreeObjects.GetFirstChild(Node);
266 while Assigned(Node) do 266 while Assigned(Node) do
267 Node := TreeObjects.GetNextSibling(Node); 267 Node := TreeObjects.GetNextSibling(Node);
268 for DBObj in PreSelectObjects do begin 268 for DBObj in PreSelectObjects do begin
269 Node := MainForm.FindDBObjectNode(TreeObjects, DBObj); 269 Node := MainForm.FindDBObjectNode(TreeObjects, DBObj);
270 if Assigned(Node) then 270 if Assigned(Node) then
271 TreeObjects.CheckState[Node] := csCheckedNormal; 271 TreeObjects.CheckState[Node] := csCheckedNormal;
272 end; 272 end;
273 273
274 FirstChecked := TreeObjects.GetFirstChecked; 274 FirstChecked := TreeObjects.GetFirstChecked;
275 if Assigned(FirstChecked) then 275 if Assigned(FirstChecked) then
276 SelectNode(TreeObjects, FirstChecked); 276 SelectNode(TreeObjects, FirstChecked);
277 // CHECKSUM available since MySQL 4.1.1 277 // CHECKSUM available since MySQL 4.1.1
278 idx := comboOperation.ItemIndex; 278 idx := comboOperation.ItemIndex;
279 if idx = -1 then idx := 0; 279 if idx = -1 then idx := 0;
280 comboOperation.Items.CommaText := 'Check,Analyze,Checksum,Optimize,Repair'; 280 comboOperation.Items.CommaText := 'Check,Analyze,Checksum,Optimize,Repair';
281 if Mainform.ActiveConnection.ServerVersionInt < 40101 then 281 if Mainform.ActiveConnection.ServerVersionInt < 40101 then
282 comboOperation.Items.Text := StringReplace(comboOperation.Items.Text, 'Checksum', 'Checksum ('+SUnsupported+')', [rfReplaceAll]); 282 comboOperation.Items.Text := StringReplace(comboOperation.Items.Text, 'Checksum', 'Checksum ('+SUnsupported+')', [rfReplaceAll]);
283 comboOperation.ItemIndex := idx; 283 comboOperation.ItemIndex := idx;
284 comboOperation.OnChange(Sender); 284 comboOperation.OnChange(Sender);
285 285
286 // Restore output option. Use Server preselection in tmSQLExport mode only, to avoid 286 // Restore output option. Use Server preselection in tmSQLExport mode only, to avoid
287 // unwanted connects in other modes. 287 // unwanted connects in other modes.
288 idx := GetRegValue(REGNAME_EXP_OUTPUT, 0); 288 idx := GetRegValue(REGNAME_EXP_OUTPUT, 0);
289 if (idx = -1) or (idx >= comboExportOutputType.Items.Count) then 289 if (idx = -1) or (idx >= comboExportOutputType.Items.Count) then
290 idx := 0; 290 idx := 0;
291 if (copy(comboExportOutputType.Items[idx], 1, Length(OUTPUT_SERVER)) = OUTPUT_SERVER) 291 if (copy(comboExportOutputType.Items[idx], 1, Length(OUTPUT_SERVER)) = OUTPUT_SERVER)
292 and (ToolMode <> tmSQLExport) then 292 and (ToolMode <> tmSQLExport) then
293 idx := 0; 293 idx := 0;
294 comboExportOutputType.ItemIndex := idx; 294 comboExportOutputType.ItemIndex := idx;
295 comboExportOutputType.OnChange(Sender); 295 comboExportOutputType.OnChange(Sender);
296 296
297 comboBulkTableEditDatabase.Items.Text := Mainform.ActiveConnection.AllDatabases.Text; 297 comboBulkTableEditDatabase.Items.Text := Mainform.ActiveConnection.AllDatabases.Text;
298 if comboBulkTableEditDatabase.Items.Count > 0 then 298 if comboBulkTableEditDatabase.Items.Count > 0 then
299 comboBulkTableEditDatabase.ItemIndex := 0; 299 comboBulkTableEditDatabase.ItemIndex := 0;
300 300
301 comboBulkTableEditEngine.Items := MainForm.ActiveConnection.TableEngines; 301 comboBulkTableEditEngine.Items := MainForm.ActiveConnection.TableEngines;
302 if comboBulkTableEditEngine.Items.Count > 0 then 302 if comboBulkTableEditEngine.Items.Count > 0 then
303 comboBulkTableEditEngine.ItemIndex := comboBulkTableEditEngine.Items.IndexOf(MainForm.ActiveConnection.TableEngineDefault); 303 comboBulkTableEditEngine.ItemIndex := comboBulkTableEditEngine.Items.IndexOf(MainForm.ActiveConnection.TableEngineDefault);
304 304
305 comboBulkTableEditCollation.Items := MainForm.ActiveConnection.CollationList; 305 comboBulkTableEditCollation.Items := MainForm.ActiveConnection.CollationList;
306 if comboBulkTableEditCollation.Items.Count > 0 then 306 if comboBulkTableEditCollation.Items.Count > 0 then
307 comboBulkTableEditCollation.ItemIndex := 0; 307 comboBulkTableEditCollation.ItemIndex := 0;
308 308
309 comboBulkTableEditCharset.Items := MainForm.ActiveConnection.CharsetList; 309 comboBulkTableEditCharset.Items := MainForm.ActiveConnection.CharsetList;
310 if comboBulkTableEditCharset.Items.Count > 0 then 310 if comboBulkTableEditCharset.Items.Count > 0 then
311 comboBulkTableEditCharset.ItemIndex := 0; 311 comboBulkTableEditCharset.ItemIndex := 0;
312 312
313 ValidateControls(Sender); 313 ValidateControls(Sender);
314 end; 314 end;
315 315
316 316
317 procedure TfrmTableTools.FormClose(Sender: TObject; var Action: TCloseAction); 317 procedure TfrmTableTools.FormClose(Sender: TObject; var Action: TCloseAction);
318 begin 318 begin
319 // Auto close temorary connection 319 // Auto close temorary connection
320 if Assigned(FTargetConnection) then 320 if Assigned(FTargetConnection) then
321 FreeAndNil(FTargetConnection); 321 FreeAndNil(FTargetConnection);
322 Action := caFree; 322 Action := caFree;
323 end; 323 end;
324 324
325 325
326 procedure TfrmTableTools.SaveSettings(Sender: TObject); 326 procedure TfrmTableTools.SaveSettings(Sender: TObject);
327 begin 327 begin
328 OpenRegistry; 328 OpenRegistry;
329 329
330 case ToolMode of 330 case ToolMode of
331 tmFind: begin 331 tmFind: begin
332 MainReg.WriteString(REGNAME_TOOLSFINDTEXT, memoFindText.Text); 332 MainReg.WriteString(REGNAME_TOOLSFINDTEXT, memoFindText.Text);
333 MainReg.WriteInteger(REGNAME_TOOLSDATATYPE, comboDatatypes.ItemIndex); 333 MainReg.WriteInteger(REGNAME_TOOLSDATATYPE, comboDatatypes.ItemIndex);
334 MainReg.WriteBool(REGNAME_TOOLSCASESENSITIVE, chkCaseSensitive.Checked); 334 MainReg.WriteBool(REGNAME_TOOLSCASESENSITIVE, chkCaseSensitive.Checked);
335 end; 335 end;
336 336
337 tmSQLExport: begin 337 tmSQLExport: begin
338 MainReg.WriteBool(REGNAME_EXP_CREATEDB, chkExportDatabasesCreate.Checked); 338 MainReg.WriteBool(REGNAME_EXP_CREATEDB, chkExportDatabasesCreate.Checked);
339 MainReg.WriteBool(REGNAME_EXP_DROPDB, chkExportDatabasesDrop.Checked); 339 MainReg.WriteBool(REGNAME_EXP_DROPDB, chkExportDatabasesDrop.Checked);
340 MainReg.WriteBool(REGNAME_EXP_CREATETABLE, chkExportTablesCreate.Checked); 340 MainReg.WriteBool(REGNAME_EXP_CREATETABLE, chkExportTablesCreate.Checked);
341 MainReg.WriteBool(REGNAME_EXP_DROPTABLE, chkExportTablesDrop.Checked); 341 MainReg.WriteBool(REGNAME_EXP_DROPTABLE, chkExportTablesDrop.Checked);
342 MainReg.WriteInteger(REGNAME_EXP_DATAHOW, comboExportData.ItemIndex); 342 MainReg.WriteInteger(REGNAME_EXP_DATAHOW, comboExportData.ItemIndex);
343 MainReg.WriteInteger(REGNAME_EXP_OUTPUT, comboExportOutputType.ItemIndex); 343 MainReg.WriteInteger(REGNAME_EXP_OUTPUT, comboExportOutputType.ItemIndex);
344 344
345 if comboExportOutputType.Text = OUTPUT_FILE then begin 345 if comboExportOutputType.Text = OUTPUT_FILE then begin
346 comboExportOutputTarget.Items.Insert(0, comboExportOutputTarget.Text); 346 comboExportOutputTarget.Items.Insert(0, comboExportOutputTarget.Text);
347 MainReg.WriteString(REGNAME_EXP_OUTFILES, comboExportOutputTarget.Items.Text); 347 MainReg.WriteString(REGNAME_EXP_OUTFILES, comboExportOutputTarget.Items.Text);
348 end else if comboExportOutputType.Text = OUTPUT_DIR then begin 348 end else if comboExportOutputType.Text = OUTPUT_DIR then begin
349 comboExportOutputTarget.Items.Insert(0, comboExportOutputTarget.Text); 349 comboExportOutputTarget.Items.Insert(0, comboExportOutputTarget.Text);
350 MainReg.WriteString(REGNAME_EXP_OUTDIRS, comboExportOutputTarget.Items.Text); 350 MainReg.WriteString(REGNAME_EXP_OUTDIRS, comboExportOutputTarget.Items.Text);
351 end else if comboExportOutputType.Text = OUTPUT_DB then begin 351 end else if comboExportOutputType.Text = OUTPUT_DB then begin
352 MainReg.WriteString(REGNAME_EXP_OUTDATABASE, comboExportOutputTarget.Text); 352 MainReg.WriteString(REGNAME_EXP_OUTDATABASE, comboExportOutputTarget.Text);
353 end else if copy(comboExportOutputType.Text, 1, Length(OUTPUT_SERVER)) = OUTPUT_SERVER then begin 353 end else if copy(comboExportOutputType.Text, 1, Length(OUTPUT_SERVER)) = OUTPUT_SERVER then begin
354 MainReg.WriteString(REGNAME_EXP_OUTSERVERDB, comboExportOutputTarget.Text); 354 MainReg.WriteString(REGNAME_EXP_OUTSERVERDB, comboExportOutputTarget.Text);
355 end; 355 end;
356 end; 356 end;
357 357
358 end; 358 end;
359 359
360 end; 360 end;
361 361
362 362
363 procedure TfrmTableTools.ValidateControls(Sender: TObject); 363 procedure TfrmTableTools.ValidateControls(Sender: TObject);
364 var 364 var
365 SomeChecked, OptionChecked: Boolean; 365 SomeChecked, OptionChecked: Boolean;
366 op: String; 366 op: String;
367 i: Integer; 367 i: Integer;
368 begin 368 begin
369 SomeChecked := TreeObjects.CheckedCount > 0; 369 SomeChecked := TreeObjects.CheckedCount > 0;
370 btnSeeResults.Visible := tabsTools.ActivePage = tabFind; 370 btnSeeResults.Visible := tabsTools.ActivePage = tabFind;
371 lblCheckedSize.Caption := 'Selected objects size: '+FormatByteNumber(FObjectSizes); 371 lblCheckedSize.Caption := 'Selected objects size: '+FormatByteNumber(FObjectSizes);
372 if tabsTools.ActivePage = tabMaintenance then begin 372 if tabsTools.ActivePage = tabMaintenance then begin
373 btnExecute.Caption := 'Execute'; 373 btnExecute.Caption := 'Execute';
374 btnExecute.Enabled := (Pos(SUnsupported, comboOperation.Text) = 0) and SomeChecked; 374 btnExecute.Enabled := (Pos(SUnsupported, comboOperation.Text) = 0) and SomeChecked;
375 // Only enable available options 375 // Only enable available options
376 op := LowerCase(comboOperation.Text); 376 op := LowerCase(comboOperation.Text);
377 chkQuick.Enabled := (op = 'check') or (op = 'checksum') or (op = 'repair'); 377 chkQuick.Enabled := (op = 'check') or (op = 'checksum') or (op = 'repair');
378 chkFast.Enabled := op = 'check'; 378 chkFast.Enabled := op = 'check';
379 chkMedium.Enabled := op = 'check'; 379 chkMedium.Enabled := op = 'check';
380 chkExtended.Enabled := (op = 'check') or (op = 'checksum') or (op = 'repair'); 380 chkExtended.Enabled := (op = 'check') or (op = 'checksum') or (op = 'repair');
381 chkChanged.Enabled := op = 'check'; 381 chkChanged.Enabled := op = 'check';
382 chkUseFrm.Enabled := op = 'repair'; 382 chkUseFrm.Enabled := op = 'repair';
383 // CHECKSUM's options are mutually exclusive 383 // CHECKSUM's options are mutually exclusive
384 if comboOperation.Text = 'Checksum' then begin 384 if comboOperation.Text = 'Checksum' then begin
385 if (Sender = chkExtended) and chkExtended.Checked then chkQuick.Checked := False 385 if (Sender = chkExtended) and chkExtended.Checked then chkQuick.Checked := False
386 else if chkQuick.Checked then chkExtended.Checked := False; 386 else if chkQuick.Checked then chkExtended.Checked := False;
387 end; 387 end;
388 end else if tabsTools.ActivePage = tabFind then begin 388 end else if tabsTools.ActivePage = tabFind then begin
389 btnExecute.Caption := 'Find'; 389 btnExecute.Caption := 'Find';
390 btnExecute.Enabled := SomeChecked and (memoFindText.Text <> ''); 390 btnExecute.Enabled := SomeChecked and (memoFindText.Text <> '');
391 // Enable "See results" button if there were results 391 // Enable "See results" button if there were results
392 btnSeeResults.Enabled := False; 392 btnSeeResults.Enabled := False;
393 if Assigned(FResults) then for i:=0 to FResults.Count-1 do begin 393 if Assigned(FResults) then for i:=0 to FResults.Count-1 do begin
394 if MakeInt(FResults[i][2]) > 0 then begin 394 if MakeInt(FResults[i][2]) > 0 then begin
395 btnSeeResults.Enabled := True; 395 btnSeeResults.Enabled := True;
396 break; 396 break;
397 end; 397 end;
398 end; 398 end;
399 end else if tabsTools.ActivePage = tabSQLExport then begin 399 end else if tabsTools.ActivePage = tabSQLExport then begin
400 btnExecute.Caption := 'Export'; 400 btnExecute.Caption := 'Export';
401 btnExecute.Enabled := SomeChecked and ((comboExportOutputTarget.Text <> '') or (not comboExportOutputTarget.Enabled)); 401 btnExecute.Enabled := SomeChecked and ((comboExportOutputTarget.Text <> '') or (not comboExportOutputTarget.Enabled));
402 end else if tabsTools.ActivePage = tabBulkTableEdit then begin 402 end else if tabsTools.ActivePage = tabBulkTableEdit then begin
403 btnExecute.Caption := 'Update'; 403 btnExecute.Caption := 'Update';
404 chkBulkTableEditCollation.Enabled := MainForm.ActiveConnection.IsUnicode; 404 chkBulkTableEditCollation.Enabled := MainForm.ActiveConnection.IsUnicode;
405 chkBulkTableEditCharset.Enabled := MainForm.ActiveConnection.IsUnicode; 405 chkBulkTableEditCharset.Enabled := MainForm.ActiveConnection.IsUnicode;
406 OptionChecked := chkBulkTableEditDatabase.Checked or chkBulkTableEditEngine.Checked or chkBulkTableEditCollation.Checked 406 OptionChecked := chkBulkTableEditDatabase.Checked or chkBulkTableEditEngine.Checked or chkBulkTableEditCollation.Checked
407 or chkBulkTableEditCharset.Checked or chkBulkTableEditResetAutoinc.Checked; 407 or chkBulkTableEditCharset.Checked or chkBulkTableEditResetAutoinc.Checked;
408 btnExecute.Enabled := SomeChecked and OptionChecked; 408 btnExecute.Enabled := SomeChecked and OptionChecked;
409 end; 409 end;
410 end; 410 end;
411 411
412 412
413 procedure TfrmTableTools.TreeObjectsBeforeCellPaint(Sender: TBaseVirtualTree; TargetCanvas: TCanvas; 413 procedure TfrmTableTools.TreeObjectsBeforeCellPaint(Sender: TBaseVirtualTree; TargetCanvas: TCanvas;
414 Node: PVirtualNode; Column: TColumnIndex; CellPaintMode: TVTCellPaintMode; CellRect: TRect; 414 Node: PVirtualNode; Column: TColumnIndex; CellPaintMode: TVTCellPaintMode; CellRect: TRect;
415 var ContentRect: TRect); 415 var ContentRect: TRect);
416 begin 416 begin
417 MainForm.DBtreeBeforeCellPaint(Sender, TargetCanvas, Node, Column, CellPaintMode, CellRect, ContentRect); 417 MainForm.DBtreeBeforeCellPaint(Sender, TargetCanvas, Node, Column, CellPaintMode, CellRect, ContentRect);
418 end; 418 end;
419 419
420 procedure TfrmTableTools.TreeObjectsChange(Sender: TBaseVirtualTree; Node: PVirtualNode); 420 procedure TfrmTableTools.TreeObjectsChange(Sender: TBaseVirtualTree; Node: PVirtualNode);
421 begin 421 begin
422 Mainform.DBtreeChange(Sender, Node); 422 Mainform.DBtreeChange(Sender, Node);
423 end; 423 end;
424 424
425 425
426 procedure TfrmTableTools.TreeObjectsChecked(Sender: TBaseVirtualTree; Node: PVirtualNode); 426 procedure TfrmTableTools.TreeObjectsChecked(Sender: TBaseVirtualTree; Node: PVirtualNode);
427 var 427 var
428 Obj: PDBObject; 428 Obj: PDBObject;
429 ObjSize: Int64; 429 ObjSize: Int64;
430 begin 430 begin
431 // Track sum of checked objects size 431 // Track sum of checked objects size
432 Obj := Sender.GetNodeData(Node); 432 Obj := Sender.GetNodeData(Node);
433 ObjSize := Max(Obj.Size, 0); 433 ObjSize := Max(Obj.Size, 0);
434 if Node.CheckState in CheckedStates then 434 if Node.CheckState in CheckedStates then
435 Inc(FObjectSizes, ObjSize) 435 Inc(FObjectSizes, ObjSize)
436 else 436 else
437 Dec(FObjectSizes, ObjSize); 437 Dec(FObjectSizes, ObjSize);
438 ValidateControls(Sender); 438 ValidateControls(Sender);
439 end; 439 end;
440 440
441 441
442 procedure TfrmTableTools.TreeObjectsChecking(Sender: TBaseVirtualTree; Node: PVirtualNode; 442 procedure TfrmTableTools.TreeObjectsChecking(Sender: TBaseVirtualTree; Node: PVirtualNode;
443 var NewState: TCheckState; var Allowed: Boolean); 443 var NewState: TCheckState; var Allowed: Boolean);
444 var 444 var
445 n: PVirtualNode; 445 n: PVirtualNode;
446 begin 446 begin
447 // Ensure to also toggle check state of not yet initialized nodes 447 // Ensure to also toggle check state of not yet initialized nodes
448 Allowed := True; 448 Allowed := True;
449 // Weird fix: Just iterate through all sub nodes for implicit initialization. Without this 449 // Weird fix: Just iterate through all sub nodes for implicit initialization. Without this
450 // loop a checkbox click on a parent node would only auto-check its visible children. 450 // loop a checkbox click on a parent node would only auto-check its visible children.
451 n := Sender.GetFirstChild(Node); 451 n := Sender.GetFirstChild(Node);
452 while Assigned(n) do 452 while Assigned(n) do
453 n := Sender.GetNextSibling(n); 453 n := Sender.GetNextSibling(n);
454 end; 454 end;
455 455
456 456
457 procedure TfrmTableTools.TreeObjectsGetImageIndex(Sender: TBaseVirtualTree; Node: PVirtualNode; 457 procedure TfrmTableTools.TreeObjectsGetImageIndex(Sender: TBaseVirtualTree; Node: PVirtualNode;
458 Kind: TVTImageKind; Column: TColumnIndex; var Ghosted: Boolean; var ImageIndex: Integer); 458 Kind: TVTImageKind; Column: TColumnIndex; var Ghosted: Boolean; var ImageIndex: Integer);
459 begin 459 begin
460 Mainform.DBtreeGetImageIndex(Sender, Node, Kind, Column, Ghosted, ImageIndex); 460 Mainform.DBtreeGetImageIndex(Sender, Node, Kind, Column, Ghosted, ImageIndex);
461 end; 461 end;
462 462
463 463
464 procedure TfrmTableTools.TreeObjectsGetNodeDataSize(Sender: TBaseVirtualTree; var NodeDataSize: Integer); 464 procedure TfrmTableTools.TreeObjectsGetNodeDataSize(Sender: TBaseVirtualTree; var NodeDataSize: Integer);
465 begin 465 begin
466 MainForm.DBtreeGetNodeDataSize(Sender, NodeDataSize); 466 MainForm.DBtreeGetNodeDataSize(Sender, NodeDataSize);
467 end; 467 end;
468 468
469 procedure TfrmTableTools.TreeObjectsGetText(Sender: TBaseVirtualTree; Node: PVirtualNode; 469 procedure TfrmTableTools.TreeObjectsGetText(Sender: TBaseVirtualTree; Node: PVirtualNode;
470 Column: TColumnIndex; TextType: TVSTTextType; var CellText: String); 470 Column: TColumnIndex; TextType: TVSTTextType; var CellText: String);
471 begin 471 begin
472 Mainform.DBtreeGetText(Sender, Node, Column, TextType, CellText); 472 Mainform.DBtreeGetText(Sender, Node, Column, TextType, CellText);
473 end; 473 end;
474 474
475 475
476 procedure TfrmTableTools.TreeObjectsInitChildren(Sender: TBaseVirtualTree; Node: PVirtualNode; 476 procedure TfrmTableTools.TreeObjectsInitChildren(Sender: TBaseVirtualTree; Node: PVirtualNode;
477 var ChildCount: Cardinal); 477 var ChildCount: Cardinal);
478 begin 478 begin
479 Mainform.DBtreeInitChildren(Sender, Node, ChildCount); 479 Mainform.DBtreeInitChildren(Sender, Node, ChildCount);
480 end; 480 end;
481 481
482 482
483 procedure TfrmTableTools.TreeObjectsInitNode(Sender: TBaseVirtualTree; ParentNode, Node: PVirtualNode; 483 procedure TfrmTableTools.TreeObjectsInitNode(Sender: TBaseVirtualTree; ParentNode, Node: PVirtualNode;
484 var InitialStates: TVirtualNodeInitStates); 484 var InitialStates: TVirtualNodeInitStates);
485 begin 485 begin
486 // Attach a checkbox to all nodes 486 // Attach a checkbox to all nodes
487 Mainform.DBtreeInitNode(Sender, ParentNode, Node, InitialStates); 487 Mainform.DBtreeInitNode(Sender, ParentNode, Node, InitialStates);
488 Node.CheckType := ctTriStateCheckBox; 488 Node.CheckType := ctTriStateCheckBox;
489 Node.CheckState := csUncheckedNormal; 489 Node.CheckState := csUncheckedNormal;
490 end; 490 end;
491 491
492 492
493 procedure TfrmTableTools.TreeObjectsPaintText(Sender: TBaseVirtualTree; const TargetCanvas: TCanvas; 493 procedure TfrmTableTools.TreeObjectsPaintText(Sender: TBaseVirtualTree; const TargetCanvas: TCanvas;
494 Node: PVirtualNode; Column: TColumnIndex; TextType: TVSTTextType); 494 Node: PVirtualNode; Column: TColumnIndex; TextType: TVSTTextType);
495 begin 495 begin
496 Mainform.DBtreePaintText(Sender, TargetCanvas, Node, Column, TextType); 496 Mainform.DBtreePaintText(Sender, TargetCanvas, Node, Column, TextType);
497 end; 497 end;
498 498
499 499
500 procedure TfrmTableTools.btnHelpClick(Sender: TObject); 500 procedure TfrmTableTools.btnHelpClick(Sender: TObject);
501 begin 501 begin
502 Mainform.CallSQLHelpWithKeyword(UpperCase(comboOperation.Text) + ' TABLE'); 502 Mainform.CallSQLHelpWithKeyword(UpperCase(comboOperation.Text) + ' TABLE');
503 end; 503 end;
504 504
505 505
506 procedure TfrmTableTools.Execute(Sender: TObject); 506 procedure TfrmTableTools.Execute(Sender: TObject);
507 var 507 var
508 SessionNode, DBNode, TableNode: PVirtualNode; 508 SessionNode, DBNode, TableNode: PVirtualNode;
509 Triggers, Views: TDBObjectList; 509 Triggers, Views: TDBObjectList;
510 DBObj: PDBObject; 510 DBObj: PDBObject;
511 i: Integer; 511 i: Integer;
512 Conn: TDBConnection; 512 Conn: TDBConnection;
513 513
514 procedure ProcessNode(DBObj: TDBObject); 514 procedure ProcessNode(DBObj: TDBObject);
515 begin 515 begin
516 try 516 try
517 case FToolMode of 517 case FToolMode of
518 tmMaintenance: DoMaintenance(DBObj); 518 tmMaintenance: DoMaintenance(DBObj);
519 tmFind: DoFind(DBObj); 519 tmFind: DoFind(DBObj);
520 tmSQLExport: DoExport(DBObj); 520 tmSQLExport: DoExport(DBObj);
521 tmBulkTableEdit: DoBulkTableEdit(DBObj); 521 tmBulkTableEdit: DoBulkTableEdit(DBObj);
522 end; 522 end;
523 except 523 except
524 on E:EDatabaseError do begin 524 on E:EDatabaseError do begin
525 // The above SQL can easily throw an exception, e.g. if a table is corrupted. 525 // The above SQL can easily throw an exception, e.g. if a table is corrupted.
526 // In such cases we create a dummy row, including the error message 526 // In such cases we create a dummy row, including the error message
527 AddNotes(DBObj.Database, DBObj.Name, 'error', E.Message) 527 AddNotes(DBObj.Database, DBObj.Name, 'error', E.Message)
528 end; 528 end;
529 on E:EFCreateError do begin 529 on E:EFCreateError do begin
530 // Occurs when export output file can not be created 530 // Occurs when export output file can not be created
531 ErrorDialog(E.Message); 531 ErrorDialog(E.Message);
532 FCancelled := True; 532 FCancelled := True;
533 end; 533 end;
534 end; 534 end;
535 end; 535 end;
536 536
537 begin 537 begin
538 Screen.Cursor := crHourGlass; 538 Screen.Cursor := crHourGlass;
539 // Disable critical controls so ProcessMessages is unable to do things while export is in progress 539 // Disable critical controls so ProcessMessages is unable to do things while export is in progress
540 btnExecute.Enabled := False; 540 btnExecute.Enabled := False;
541 btnCloseOrCancel.Caption := 'Cancel'; 541 btnCloseOrCancel.Caption := 'Cancel';
542 btnCloseOrCancel.ModalResult := mrNone; 542 btnCloseOrCancel.ModalResult := mrNone;
543 tabsTools.Enabled := False; 543 tabsTools.Enabled := False;
544 treeObjects.Enabled := False; 544 treeObjects.Enabled := False;
545 if tabsTools.ActivePage = tabMaintenance then 545 if tabsTools.ActivePage = tabMaintenance then
546 FToolMode := tmMaintenance 546 FToolMode := tmMaintenance
547 else if tabsTools.ActivePage = tabFind then 547 else if tabsTools.ActivePage = tabFind then
548 FToolMode := tmFind 548 FToolMode := tmFind
549 else if tabsTools.ActivePage = tabSQLExport then 549 else if tabsTools.ActivePage = tabSQLExport then
550 FToolMode := tmSQLExport 550 FToolMode := tmSQLExport
551 else if tabsTools.ActivePage = tabBulkTableEdit then 551 else if tabsTools.ActivePage = tabBulkTableEdit then
552 FToolMode := tmBulkTableEdit; 552 FToolMode := tmBulkTableEdit;
553 ResultGrid.Clear; 553 ResultGrid.Clear;
554 FResults.Clear; 554 FResults.Clear;
555 FFindSeeResultSQL.Clear; 555 FFindSeeResultSQL.Clear;
556 Triggers := TDBObjectList.Create(False); // False, so we can .Free that object afterwards without loosing the contained objects 556 Triggers := TDBObjectList.Create(False); // False, so we can .Free that object afterwards without loosing the contained objects
557 Views := TDBObjectList.Create(False); 557 Views := TDBObjectList.Create(False);
558 FHeaderCreated := False; 558 FHeaderCreated := False;
559 FCancelled := False; 559 FCancelled := False;
560 FObjectSizesDone := 0; 560 FObjectSizesDone := 0;
561 FObjectSizesDoneExact := 0; 561 FObjectSizesDoneExact := 0;
562 MainForm.EnableProgress(100); 562 MainForm.EnableProgress(100);
563 SessionNode := TreeObjects.GetFirstChild(nil); 563 SessionNode := TreeObjects.GetFirstChild(nil);
564 while Assigned(SessionNode) do begin 564 while Assigned(SessionNode) do begin
565 DBNode := TreeObjects.GetFirstChild(SessionNode); 565 DBNode := TreeObjects.GetFirstChild(SessionNode);
566 while Assigned(DBNode) do begin 566 while Assigned(DBNode) do begin
567 if not (DBNode.CheckState in [csUncheckedNormal, csUncheckedPressed]) then begin 567 if not (DBNode.CheckState in [csUncheckedNormal, csUncheckedPressed]) then begin
568 Triggers.Clear; 568 Triggers.Clear;
569 Views.Clear; 569 Views.Clear;
570 FSecondExportPass := False; 570 FSecondExportPass := False;
571 TableNode := TreeObjects.GetFirstChild(DBNode); 571 TableNode := TreeObjects.GetFirstChild(DBNode);
572 while Assigned(TableNode) do begin 572 while Assigned(TableNode) do begin
573 if (csCheckedNormal in [TableNode.CheckState, DBNode.CheckState]) and (TableNode.CheckType <> ctNone) then begin 573 if (csCheckedNormal in [TableNode.CheckState, DBNode.CheckState]) and (TableNode.CheckType <> ctNone) then begin
574 DBObj := TreeObjects.GetNodeData(TableNode); 574 DBObj := TreeObjects.GetNodeData(TableNode);
575 // Triggers have to be exported at the very end 575 // Triggers have to be exported at the very end
576 if (FToolMode = tmSQLExport) and (DBObj.NodeType = lntTrigger) then 576 if (FToolMode = tmSQLExport) and (DBObj.NodeType = lntTrigger) then
577 Triggers.Add(DBObj^) 577 Triggers.Add(DBObj^)
578 else begin 578 else begin
579 ProcessNode(DBObj^); 579 ProcessNode(DBObj^);
580 FObjectSizesDone := FObjectSizesDone + Max(DBObj.Size, 0); 580 FObjectSizesDone := FObjectSizesDone + Max(DBObj.Size, 0);
581 FObjectSizesDoneExact := FObjectSizesDone; 581 FObjectSizesDoneExact := FObjectSizesDone;
582 if (FToolMode = tmSQLExport) and (DBObj.NodeType = lntView) then 582 if (FToolMode = tmSQLExport) and (DBObj.NodeType = lntView) then
583 Views.Add(DBObj^); 583 Views.Add(DBObj^);
584 end; 584 end;
585 end; 585 end;
586 // File creation exception occurred or user clicked cancel button 586 // File creation exception occurred or user clicked cancel button
587 if FCancelled then Break; 587 if FCancelled then Break;
588 TableNode := TreeObjects.GetNextSibling(TableNode); 588 TableNode := TreeObjects.GetNextSibling(TableNode);
589 end; // End of db object node loop in db 589 end; // End of db object node loop in db
590 590
591 // Special block for late created triggers in export mode 591 // Special block for late created triggers in export mode
592 for i:=0 to Triggers.Count-1 do begin 592 for i:=0 to Triggers.Count-1 do begin
593 ProcessNode(Triggers[i]); 593 ProcessNode(Triggers[i]);
594 if FCancelled then Break; 594 if FCancelled then Break;
595 end; 595 end;
596 596
597 // Special block for exporting final view structure 597 // Special block for exporting final view structure
598 FSecondExportPass := True; 598 FSecondExportPass := True;
599 for i:=0 to Views.Count-1 do begin 599 for i:=0 to Views.Count-1 do begin
600 ProcessNode(Views[i]); 600 ProcessNode(Views[i]);
601 if FCancelled then Break; 601 if FCancelled then Break;
602 end; 602 end;
603 603
604 end; 604 end;
605 if FCancelled then Break; 605 if FCancelled then Break;
606 DBNode := TreeObjects.GetNextSibling(DBNode); 606 DBNode := TreeObjects.GetNextSibling(DBNode);
607 end; // End of db item loop 607 end; // End of db item loop
608 if FCancelled then Break; 608 if FCancelled then Break;
609 SessionNode := TreeObjects.GetNextSibling(SessionNode); 609 SessionNode := TreeObjects.GetNextSibling(SessionNode);
610 end; 610 end;
611 611
612 if Assigned(ExportStream) then begin 612 if Assigned(ExportStream) then begin
613 Output(EXPORT_FILE_FOOTER, False, True, False, False, False); 613 Output(EXPORT_FILE_FOOTER, False, True, False, False, False);
614 Output('/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '''') */', True, False, False, True, True); 614 Output('/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '''') */', True, False, False, True, True);
615 Output('/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */', True, False, False, True, True); 615 Output('/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */', True, False, False, True, True);
616 if comboExportOutputType.Text = OUTPUT_CLIPBOARD then 616 if comboExportOutputType.Text = OUTPUT_CLIPBOARD then
617 StreamToClipboard(ExportStream, nil, false); 617 StreamToClipboard(ExportStream, nil, false);
618 FreeAndNil(ExportStream); 618 FreeAndNil(ExportStream);
619 end; 619 end;
620 ExportLastDatabase := ''; 620 ExportLastDatabase := '';
621 621
622 Conn := Mainform.ActiveConnection; 622 Conn := Mainform.ActiveConnection;
623 for i:=0 to FModifiedDbs.Count-1 do begin 623 for i:=0 to FModifiedDbs.Count-1 do begin
624 Conn.ClearDbObjects(FModifiedDbs[i]); 624 Conn.ClearDbObjects(FModifiedDbs[i]);
625 DBNode := MainForm.FindDBNode(TreeObjects, Conn, FModifiedDbs[i]); 625 DBNode := MainForm.FindDBNode(TreeObjects, Conn, FModifiedDbs[i]);
626 TreeObjects.ReinitNode(DBNode, False); 626 TreeObjects.ReinitNode(DBNode, False);
627 TreeObjects.ReinitChildren(DBNode, False) 627 TreeObjects.ReinitChildren(DBNode, False)
628 end; 628 end;
629 FModifiedDbs.Clear; 629 FModifiedDbs.Clear;
630 630
631 btnCloseOrCancel.Caption := 'Close'; 631 btnCloseOrCancel.Caption := 'Close';
632 btnCloseOrCancel.ModalResult := mrCancel; 632 btnCloseOrCancel.ModalResult := mrCancel;
633 MainForm.DisableProgress; 633 MainForm.DisableProgress;
634 tabsTools.Enabled := True; 634 tabsTools.Enabled := True;
635 treeObjects.Enabled := True; 635 treeObjects.Enabled := True;
636 ValidateControls(Sender); 636 ValidateControls(Sender);
637 SaveSettings(Sender); 637 SaveSettings(Sender);
638 Screen.Cursor := crDefault; 638 Screen.Cursor := crDefault;
639 end; 639 end;
640 640
641 641
642 procedure TfrmTableTools.DoMaintenance(DBObj: TDBObject); 642 procedure TfrmTableTools.DoMaintenance(DBObj: TDBObject);
643 var 643 var
644 SQL: String; 644 SQL: String;
645 begin 645 begin
646 if not (DBObj.NodeType in [lntTable, lntView]) then begin 646 if not (DBObj.NodeType in [lntTable, lntView]) then begin
647 AddNotes(DBObj.Database, DBObj.Name, STRSKIPPED+'a '+LowerCase(DBObj.ObjType)+' cannot be maintained.', ''); 647 AddNotes(DBObj.Database, DBObj.Name, STRSKIPPED+'a '+LowerCase(DBObj.ObjType)+' cannot be maintained.', '');
648 Exit; 648 Exit;
649 end; 649 end;
650 SQL := UpperCase(comboOperation.Text) + ' TABLE ' + DBObj.QuotedDatabase + '.' + DBObj.QuotedName; 650 SQL := UpperCase(comboOperation.Text) + ' TABLE ' + DBObj.QuotedDatabase + '.' + DBObj.QuotedName;
651 if chkQuick.Enabled and chkQuick.Checked then SQL := SQL + ' QUICK'; 651 if chkQuick.Enabled and chkQuick.Checked then SQL := SQL + ' QUICK';
652 if chkFast.Enabled and chkFast.Checked then SQL := SQL + ' FAST'; 652 if chkFast.Enabled and chkFast.Checked then SQL := SQL + ' FAST';
653 if chkMedium.Enabled and chkMedium.Checked then SQL := SQL + ' MEDIUM'; 653 if chkMedium.Enabled and chkMedium.Checked then SQL := SQL + ' MEDIUM';
654 if chkExtended.Enabled and chkExtended.Checked then SQL := SQL + ' EXTENDED'; 654 if chkExtended.Enabled and chkExtended.Checked then SQL := SQL + ' EXTENDED';
655 if chkChanged.Enabled and chkChanged.Checked then SQL := SQL + ' CHANGED'; 655 if chkChanged.Enabled and chkChanged.Checked then SQL := SQL + ' CHANGED';
656 if chkUseFrm.Enabled and chkUseFrm.Checked then SQL := SQL + ' USE_FRM'; 656 if chkUseFrm.Enabled and chkUseFrm.Checked then SQL := SQL + ' USE_FRM';
657 AddResults(SQL); 657 AddResults(SQL);
658 end; 658 end;
659 659
660 660
661 procedure TfrmTableTools.DoFind(DBObj: TDBObject); 661 procedure TfrmTableTools.DoFind(DBObj: TDBObject);
662 var 662 var
663 Columns: TTableColumnList; 663 Columns: TTableColumnList;
664 Col: TTableColumn; 664 Col: TTableColumn;
665 SQL, Dummy: String; 665 SQL, Dummy: String;
666 begin 666 begin
667 FFindSeeResultSQL.Add(''); 667 FFindSeeResultSQL.Add('');
668 Columns := TTableColumnList.Create(True); 668 Columns := TTableColumnList.Create(True);
669 case DBObj.NodeType of 669 case DBObj.NodeType of
670 lntTable: DBObj.Connection.ParseTableStructure(DBObj.CreateCode, Columns, nil, nil); 670 lntTable: DBObj.Connection.ParseTableStructure(DBObj.CreateCode, Columns, nil, nil);
671 lntView: DBObj.Connection.ParseViewStructure(DBObj.CreateCode, DBObj.Name, Columns, Dummy, Dummy, Dummy, Dummy, Dummy); 671 lntView: DBObj.Connection.ParseViewStructure(DBObj.CreateCode, DBObj.Name, Columns, Dummy, Dummy, Dummy, Dummy, Dummy);
672 else AddNotes(DBObj.Database, DBObj.Name, STRSKIPPED+'a '+LowerCase(DBObj.ObjType)+' does not contain rows.', ''); 672 else AddNotes(DBObj.Database, DBObj.Name, STRSKIPPED+'a '+LowerCase(DBObj.ObjType)+' does not contain rows.', '');
673 end; 673 end;
674 if Columns.Count > 0 then begin 674 if Columns.Count > 0 then begin
675 SQL := ''; 675 SQL := '';
676 for Col in Columns do begin 676 for Col in Columns do begin
677 if (comboDatatypes.ItemIndex = 0) or (Integer(Col.DataType.Category) = comboDatatypes.ItemIndex-1) then begin 677 if (comboDatatypes.ItemIndex = 0) or (Integer(Col.DataType.Category) = comboDatatypes.ItemIndex-1) then begin
678 if chkCaseSensitive.Checked then 678 if chkCaseSensitive.Checked then
679 SQL := SQL + DBObj.Connection.QuoteIdent(Col.Name) + ' LIKE BINARY ' + esc('%'+memoFindText.Text+'%') + ' OR ' 679 SQL := SQL + DBObj.Connection.QuoteIdent(Col.Name) + ' LIKE BINARY ' + esc('%'+memoFindText.Text+'%') + ' OR '
680 else 680 else
681 SQL := SQL + 'LOWER(CONVERT('+DBObj.Connection.QuoteIdent(Col.Name)+' USING '+DBObj.Connection.CharacterSet+')) LIKE ' + esc('%'+LowerCase(memoFindText.Text)+'%') + ' OR ' 681 SQL := SQL + 'LOWER(CONVERT('+DBObj.Connection.QuoteIdent(Col.Name)+' USING '+DBObj.Connection.CharacterSet+')) LIKE ' + esc('%'+LowerCase(memoFindText.Text)+'%') + ' OR '
682 end; 682 end;
683 end; 683 end;
684 if SQL <> '' then begin 684 if SQL <> '' then begin
685 Delete(SQL, Length(SQL)-3, 3); 685 Delete(SQL, Length(SQL)-3, 3);
686 FFindSeeResultSQL[FFindSeeResultSQL.Count-1] := 'SELECT * FROM '+DBObj.QuotedDatabase+'.'+DBObj.QuotedName+' WHERE ' + SQL; 686 FFindSeeResultSQL[FFindSeeResultSQL.Count-1] := 'SELECT * FROM '+DBObj.QuotedDatabase+'.'+DBObj.QuotedName+' WHERE ' + SQL;
687 SQL := 'SELECT '''+DBObj.Database+''' AS '+DBObj.Connection.QuoteIdent('Database')+', '''+DBObj.Name+''' AS '+DBObj.Connection.QuoteIdent('Table')+', COUNT(*) AS '+DBObj.Connection.QuoteIdent('Found rows')+', ' 687 SQL := 'SELECT '''+DBObj.Database+''' AS '+DBObj.Connection.QuoteIdent('Database')+', '''+DBObj.Name+''' AS '+DBObj.Connection.QuoteIdent('Table')+', COUNT(*) AS '+DBObj.Connection.QuoteIdent('Found rows')+', '
688 + 'CONCAT(ROUND(100 / '+IntToStr(Max(DBObj.Rows,1))+' * COUNT(*), 1), ''%'') AS '+DBObj.Connection.QuoteIdent('Relevance')+' FROM '+DBObj.QuotedDatabase+'.'+DBObj.QuotedName+' WHERE ' 688 + 'CONCAT(ROUND(100 / '+IntToStr(Max(DBObj.Rows,1))+' * COUNT(*), 1), ''%'') AS '+DBObj.Connection.QuoteIdent('Relevance')+' FROM '+DBObj.QuotedDatabase+'.'+DBObj.QuotedName+' WHERE '
689 + SQL; 689 + SQL;
690 AddResults(SQL); 690 AddResults(SQL);
691 end else 691 end else
692 AddNotes(DBObj.Database, DBObj.Name, STRSKIPPED+DBObj.ObjType+' doesn''t have columns of selected type ('+comboDatatypes.Text+').', ''); 692 AddNotes(DBObj.Database, DBObj.Name, STRSKIPPED+DBObj.ObjType+' doesn''t have columns of selected type ('+comboDatatypes.Text+').', '');
693 end; 693 end;
694 Columns.Free; 694 Columns.Free;
695 end; 695 end;
696 696
697 697
698 procedure TfrmTableTools.btnSeeResultsClick(Sender: TObject); 698 procedure TfrmTableTools.btnSeeResultsClick(Sender: TObject);
699 var 699 var
700 SQL: String; 700 SQL: String;
701 i: Integer; 701 i: Integer;
702 Tab: TQueryTab; 702 Tab: TQueryTab;
703 begin 703 begin
704 // "See results" clicked - auto create new query tab, and execute a batch of SELECT queries 704 // "See results" clicked - auto create new query tab, and execute a batch of SELECT queries
705 SQL := ''; 705 SQL := '';
706 for i:=0 to FResults.Count-1 do begin 706 for i:=0 to FResults.Count-1 do begin
707 if MakeInt(FResults[i][2]) > 0 then begin 707 if MakeInt(FResults[i][2]) > 0 then begin
708 SQL := SQL + FFindSeeResultSQL[i] + ';' + CRLF; 708 SQL := SQL + FFindSeeResultSQL[i] + ';' + CRLF;
709 end; 709 end;
710 end; 710 end;
711 MainForm.actNewQueryTab.Execute; 711 MainForm.actNewQueryTab.Execute;
712 Tab := MainForm.QueryTabs[MainForm.QueryTabs.Count-1]; 712 Tab := MainForm.QueryTabs[MainForm.QueryTabs.Count-1];
713 Tab.Memo.Text := SQL; 713 Tab.Memo.Text := SQL;
714 Tab.TabSheet.Show; 714 Tab.TabSheet.Show;
715 MainForm.actExecuteQueryExecute(Sender); 715 MainForm.actExecuteQueryExecute(Sender);
716 end; 716 end;
717 717
718 718
719 procedure TfrmTableTools.AddResults(SQL: String); 719 procedure TfrmTableTools.AddResults(SQL: String);
720 var 720 var
721 i: Integer; 721 i: Integer;
722 Row: TStringList; 722 Row: TStringList;
723 Results: TDBQuery; 723 Results: TDBQuery;
724 Value: String; 724 Value: String;
725 begin 725 begin
726 // Execute query and append results into grid 726 // Execute query and append results into grid
727 Results := MainForm.ActiveConnection.GetResults(SQL); 727 Results := MainForm.ActiveConnection.GetResults(SQL);
728 if Results = nil then 728 if Results = nil then
729 Exit; 729 Exit;
730 730
731 SetupResultGrid(Results); 731 SetupResultGrid(Results);
732 Results.First; 732 Results.First;
733 while not Results.Eof do begin 733 while not Results.Eof do begin
734 Row := TStringList.Create; 734 Row := TStringList.Create;
735 for i:=0 to Results.ColumnCount-1 do begin 735 for i:=0 to Results.ColumnCount-1 do begin
736 Value := Results.Col(i); 736 Value := Results.Col(i);
737 if Results.DataType(i).Category = dtcInteger then begin 737 if Results.DataType(i).Category = dtcInteger then begin
738 if MakeFloat(Value) >= 0 then 738 if MakeFloat(Value) >= 0 then
739 Row.Add(FormatNumber(Value)) 739 Row.Add(FormatNumber(Value))
740 else 740 else
741 Row.Add(''); 741 Row.Add('');
742 end else 742 end else
743 Row.Add(Value); 743 Row.Add(Value);
744 end; 744 end;
745 FResults.Add(Row); 745 FResults.Add(Row);
746 Results.Next; 746 Results.Next;
747 end; 747 end;
748 Results.Free; 748 Results.Free;
749 749
750 UpdateResultGrid; 750 UpdateResultGrid;
751 end; 751 end;
752 752
753 753
754 procedure TfrmTableTools.AddNotes(Col1, Col2, Col3, Col4: String); 754 procedure TfrmTableTools.AddNotes(Col1, Col2, Col3, Col4: String);
755 var 755 var
756 Row: TStringList; 756 Row: TStringList;
757 begin 757 begin
758 // Adds a row with non SQL results 758 // Adds a row with non SQL results
759 SetupResultGrid; 759 SetupResultGrid;
760 Row := TStringList.Create; 760 Row := TStringList.Create;
761 Row.Add(Col1); 761 Row.Add(Col1);
762 Row.Add(Col2); 762 Row.Add(Col2);
763 Row.Add(Col3); 763 Row.Add(Col3);
764 Row.Add(Col4); 764 Row.Add(Col4);
765 FResults.Add(Row); 765 FResults.Add(Row);
766 UpdateResultGrid; 766 UpdateResultGrid;
767 end; 767 end;
768 768
769 769
770 procedure TfrmTableTools.SetupResultGrid(Results: TDBQuery=nil); 770 procedure TfrmTableTools.SetupResultGrid(Results: TDBQuery=nil);
771 var 771 var
772 ColCount, i: Integer; 772 ColCount, i: Integer;
773 Col: TVirtualTreeColumn; 773 Col: TVirtualTreeColumn;
774 begin 774 begin
775 if Assigned(Results) then begin 775 if Assigned(Results) then begin
776 ColCount := Results.ColumnCount; 776 ColCount := Results.ColumnCount;
777 ResultGrid.Header.Options := ResultGrid.Header.Options + [hoVisible]; 777 ResultGrid.Header.Options := ResultGrid.Header.Options + [hoVisible];
778 end else begin 778 end else begin
779 ColCount := 4; 779 ColCount := 4;
780 // Remove column headers if this is the first row 780 // Remove column headers if this is the first row
781 if FResults.Count = 0 then 781 if FResults.Count = 0 then
782 ResultGrid.Header.Options := ResultGrid.Header.Options - [hoVisible]; 782 ResultGrid.Header.Options := ResultGrid.Header.Options - [hoVisible];
783 end; 783 end;
784 784
785 // Add missing columns 785 // Add missing columns
786 for i:=ResultGrid.Header.Columns.Count to ColCount-1 do begin 786 for i:=ResultGrid.Header.Columns.Count to ColCount-1 do begin
787 Col := ResultGrid.Header.Columns.Add; 787 Col := ResultGrid.Header.Columns.Add;
788 Col.Width := 130; 788 Col.Width := 130;
789 end; 789 end;
790 // Remove superfluous columns 790 // Remove superfluous columns
791 for i:=ResultGrid.Header.Columns.Count-1 downto ColCount do 791 for i:=ResultGrid.Header.Columns.Count-1 downto ColCount do
792 ResultGrid.Header.Columns[i].Free; 792 ResultGrid.Header.Columns[i].Free;
793 793
794 // Set column header names 794 // Set column header names
795 for i:=0 to ResultGrid.Header.Columns.Count-1 do begin 795 for i:=0 to ResultGrid.Header.Columns.Count-1 do begin
796 Col := ResultGrid.Header.Columns[i]; 796 Col := ResultGrid.Header.Columns[i];
797 if Assigned(Results) then begin 797 if Assigned(Results) then begin
798 Col.Text := Results.ColumnNames[i]; 798 Col.Text := Results.ColumnNames[i];
799 if Results.DataType(i).Category in [dtcInteger, dtcReal] then 799 if Results.DataType(i).Category in [dtcInteger, dtcReal] then
800 Col.Alignment := taRightJustify 800 Col.Alignment := taRightJustify
801 else 801 else
802 Col.Alignment := taLeftJustify; 802 Col.Alignment := taLeftJustify;
803 end; 803 end;
804 end; 804 end;
805 end; 805 end;
806 806
807 807
808 procedure TfrmTableTools.UpdateResultGrid; 808 procedure TfrmTableTools.UpdateResultGrid;
809 var 809 var
810 Percent: Double; 810 Percent: Double;
811 begin 811 begin
812 // Refresh resultgrid 812 // Refresh resultgrid
813 ResultGrid.RootNodeCount := FResults.Count; 813 ResultGrid.RootNodeCount := FResults.Count;
814 ResultGrid.FocusedNode := ResultGrid.GetLast; 814 ResultGrid.FocusedNode := ResultGrid.GetLast;
815 ResultGrid.Selected[ResultGrid.FocusedNode] := True; 815 ResultGrid.Selected[ResultGrid.FocusedNode] := True;
816 Percent := 100 / Max(FObjectSizes,1) * FObjectSizesDoneExact; 816 Percent := 100 / Max(FObjectSizes,1) * FObjectSizesDoneExact;
817 lblCheckedSize.Caption := 'Selected objects size: '+FormatByteNumber(FObjectSizes)+'. '+ 817 lblCheckedSize.Caption := 'Selected objects size: '+FormatByteNumber(FObjectSizes)+'. '+
818 FormatNumber(Percent, 1) + '% done.'; 818 FormatNumber(Percent, 1) + '% done.';
819 MainForm.SetProgressPosition(Round(Percent)); 819 MainForm.SetProgressPosition(Round(Percent));
820 Application.ProcessMessages; 820 Application.ProcessMessages;
821 end; 821 end;
822 822
823 procedure TfrmTableTools.ResultGridCompareNodes(Sender: TBaseVirtualTree; Node1, Node2: PVirtualNode; 823 procedure TfrmTableTools.ResultGridCompareNodes(Sender: TBaseVirtualTree; Node1, Node2: PVirtualNode;
824 Column: TColumnIndex; var Result: Integer); 824 Column: TColumnIndex; var Result: Integer);
825 begin 825 begin
826 Mainform.AnyGridCompareNodes(Sender, Node1, Node2, Column, Result); 826 Mainform.AnyGridCompareNodes(Sender, Node1, Node2, Column, Result);
827 end; 827 end;
828 828
829 procedure TfrmTableTools.ResultGridGetNodeDataSize(Sender: TBaseVirtualTree; var NodeDataSize: Integer); 829 procedure TfrmTableTools.ResultGridGetNodeDataSize(Sender: TBaseVirtualTree; var NodeDataSize: Integer);
830 begin 830 begin
831 NodeDataSize := SizeOf(TStringList); 831 NodeDataSize := SizeOf(TStringList);
832 end; 832 end;
833 833
834 834
835 procedure TfrmTableTools.ResultGridInitNode(Sender: TBaseVirtualTree; ParentNode, Node: PVirtualNode; 835 procedure TfrmTableTools.ResultGridInitNode(Sender: TBaseVirtualTree; ParentNode, Node: PVirtualNode;
836 var InitialStates: TVirtualNodeInitStates); 836 var InitialStates: TVirtualNodeInitStates);
837 var 837 var
838 Data: ^TStringList; 838 Data: ^TStringList;
839 begin 839 begin
840 // Bind string list to node 840 // Bind string list to node
841 Data := Sender.GetNodeData(Node); 841 Data := Sender.GetNodeData(Node);
842 Data^ := FResults[Node.Index]; 842 Data^ := FResults[Node.Index];
843 end; 843 end;
844 844
845 845
846 procedure TfrmTableTools.ResultGridPaintText(Sender: TBaseVirtualTree; const TargetCanvas: TCanvas; 846 procedure TfrmTableTools.ResultGridPaintText(Sender: TBaseVirtualTree; const TargetCanvas: TCanvas;
847 Node: PVirtualNode; Column: TColumnIndex; TextType: TVSTTextType); 847 Node: PVirtualNode; Column: TColumnIndex; TextType: TVSTTextType);
848 var 848 var
849 VT: TVirtualStringTree; 849 VT: TVirtualStringTree;
850 Msg: String; 850 Msg: String;
851 begin 851 begin
852 // Red text color for errors, purple for notes, grey for skipped tables 852 // Red text color for errors, purple for notes, grey for skipped tables
853 if not (vsSelected in Node.States) then begin 853 if not (vsSelected in Node.States) then begin
854 VT := Sender as TVirtualStringTree; 854 VT := Sender as TVirtualStringTree;
855 Msg := VT.Text[Node, 2]; 855 Msg := VT.Text[Node, 2];
856 if LowerCase(Msg) = 'note' then 856 if LowerCase(Msg) = 'note' then
857 TargetCanvas.Font.Color := clPurple 857 TargetCanvas.Font.Color := clPurple
858 else if LowerCase(Msg) = 'error' then 858 else if LowerCase(Msg) = 'error' then
859 TargetCanvas.Font.Color := clRed 859 TargetCanvas.Font.Color := clRed
860 else if Pos(STRSKIPPED, Msg) > 0 then 860 else if Pos(STRSKIPPED, Msg) > 0 then
861 TargetCanvas.Font.Color := clGrayText; 861 TargetCanvas.Font.Color := clGrayText;
862 end; 862 end;
863 end; 863 end;
864 864
865 procedure TfrmTableTools.ResultGridGetText(Sender: TBaseVirtualTree; Node: PVirtualNode; Column: TColumnIndex; 865 procedure TfrmTableTools.ResultGridGetText(Sender: TBaseVirtualTree; Node: PVirtualNode; Column: TColumnIndex;
866 TextType: TVSTTextType; var CellText: String); 866 TextType: TVSTTextType; var CellText: String);
867 var 867 var
868 Data: ^TStringList; 868 Data: ^TStringList;
869 begin 869 begin
870 if Column > NoColumn then begin 870 if Column > NoColumn then begin
871 Data := Sender.GetNodeData(Node); 871 Data := Sender.GetNodeData(Node);
872 if Data^.Count > Column then 872 if Data^.Count > Column then
873 CellText := Data^[Column] 873 CellText := Data^[Column]
874 else 874 else
875 CellText := ''; 875 CellText := '';
876 end; 876 end;
877 end; 877 end;
878 878
879 879
880 procedure TfrmTableTools.ResultGridHeaderClick(Sender: TVTHeader; HitInfo: TVTHeaderHitInfo); 880 procedure TfrmTableTools.ResultGridHeaderClick(Sender: TVTHeader; HitInfo: TVTHeaderHitInfo);
881 begin 881 begin
882 // Header column clicked to sort 882 // Header column clicked to sort
883 Mainform.AnyGridHeaderClick(Sender, HitInfo); 883 Mainform.AnyGridHeaderClick(Sender, HitInfo);
884 end; 884 end;
885 885
886 886
887 procedure TfrmTableTools.comboExportOutputTypeChange(Sender: TObject); 887 procedure TfrmTableTools.comboExportOutputTypeChange(Sender: TObject);
888 var 888 var
889 SessionNode, DBNode: PVirtualNode; 889 SessionNode, DBNode: PVirtualNode;
890 SessionName: String; 890 SessionName: String;
891 Params: TConnectionParameters; 891 Params: TConnectionParameters;
892 begin 892 begin
893 // Target type (file, directory, ...) selected 893 // Target type (file, directory, ...) selected
894 comboExportOutputTarget.Enabled := True; 894 comboExportOutputTarget.Enabled := True;
895 comboExportOutputTarget.Text := ''; 895 comboExportOutputTarget.Text := '';
896 if Assigned(FTargetConnection) then 896 if Assigned(FTargetConnection) then
897 FreeAndNil(FTargetConnection); 897 FreeAndNil(FTargetConnection);
898 if comboExportOutputType.Text = OUTPUT_FILE then begin 898 if comboExportOutputType.Text = OUTPUT_FILE then begin
899 comboExportOutputTarget.Style := csDropDown; 899 comboExportOutputTarget.Style := csDropDown;
900 comboExportOutputTarget.Items.Text := GetRegValue(REGNAME_EXP_OUTFILES, ''); 900 comboExportOutputTarget.Items.Text := GetRegValue(REGNAME_EXP_OUTFILES, '');
901 if comboExportOutputTarget.Items.Count > 0 then 901 if comboExportOutputTarget.Items.Count > 0 then
902 comboExportOutputTarget.ItemIndex := 0; 902 comboExportOutputTarget.ItemIndex := 0;
903 lblExportOutputTarget.Caption := 'Filename:'; 903 lblExportOutputTarget.Caption := 'Filename:';
904 btnExportOutputTargetSelect.Enabled := True; 904 btnExportOutputTargetSelect.Enabled := True;
905 btnExportOutputTargetSelect.ImageIndex := 10; 905 btnExportOutputTargetSelect.ImageIndex := 10;
906 end else if comboExportOutputType.Text = OUTPUT_DIR then begin 906 end else if comboExportOutputType.Text = OUTPUT_DIR then begin
907 comboExportOutputTarget.Style := csDropDown; 907 comboExportOutputTarget.Style := csDropDown;
908 comboExportOutputTarget.Items.Text := GetRegValue(REGNAME_EXP_OUTDIRS, ''); 908 comboExportOutputTarget.Items.Text := GetRegValue(REGNAME_EXP_OUTDIRS, '');
909 if comboExportOutputTarget.Items.Count > 0 then 909 if comboExportOutputTarget.Items.Count > 0 then
910 comboExportOutputTarget.ItemIndex := 0; 910 comboExportOutputTarget.ItemIndex := 0;
911 lblExportOutputTarget.Caption := 'Directory:'; 911 lblExportOutputTarget.Caption := 'Directory:';
912 btnExportOutputTargetSelect.Enabled := True; 912 btnExportOutputTargetSelect.Enabled := True;
913 btnExportOutputTargetSelect.ImageIndex := 51; 913 btnExportOutputTargetSelect.ImageIndex := 51;
914 end else if comboExportOutputType.Text = OUTPUT_CLIPBOARD then begin 914 end else if comboExportOutputType.Text = OUTPUT_CLIPBOARD then begin
915 comboExportOutputTarget.Enabled := False; 915 comboExportOutputTarget.Enabled := False;
916 comboExportOutputTarget.Items.Clear; 916 comboExportOutputTarget.Items.Clear;
917 lblExportOutputTarget.Caption := ''; 917 lblExportOutputTarget.Caption := '';
918 btnExportOutputTargetSelect.Enabled := False; 918 btnExportOutputTargetSelect.Enabled := False;
919 btnExportOutputTargetSelect.ImageIndex := 4; 919 btnExportOutputTargetSelect.ImageIndex := 4;
920 end else if comboExportOutputType.Text = OUTPUT_DB then begin 920 end else if comboExportOutputType.Text = OUTPUT_DB then begin
921 comboExportOutputTarget.Style := csDropDownList; 921 comboExportOutputTarget.Style := csDropDownList;
922 lblExportOutputTarget.Caption := 'Database:'; 922 lblExportOutputTarget.Caption := 'Database:';
923 btnExportOutputTargetSelect.Enabled := False; 923 btnExportOutputTargetSelect.Enabled := False;
924 btnExportOutputTargetSelect.ImageIndex := 27; 924 btnExportOutputTargetSelect.ImageIndex := 27;
925 // Add unchecked databases 925 // Add unchecked databases
926 comboExportOutputTarget.Items.Clear; 926 comboExportOutputTarget.Items.Clear;
927 SessionNode := MainForm.GetRootNode(TreeObjects, MainForm.ActiveConnection); 927 SessionNode := MainForm.GetRootNode(TreeObjects, MainForm.ActiveConnection);
928 DBNode := TreeObjects.GetFirstChild(SessionNode); 928 DBNode := TreeObjects.GetFirstChild(SessionNode);
929 while Assigned(DBNode) do begin 929 while Assigned(DBNode) do begin
930 if DBNode.CheckState in [csUncheckedNormal, csUncheckedPressed] then 930 if DBNode.CheckState in [csUncheckedNormal, csUncheckedPressed] then
931 comboExportOutputTarget.Items.Add(TreeObjects.Text[DBNode, 0]); 931 comboExportOutputTarget.Items.Add(TreeObjects.Text[DBNode, 0]);
932 DBNode := TreeObjects.GetNextSibling(DBNode); 932 DBNode := TreeObjects.GetNextSibling(DBNode);
933 end; 933 end;
934 comboExportOutputTarget.ItemIndex := comboExportOutputTarget.Items.IndexOf(GetRegValue(REGNAME_EXP_OUTDATABASE, '')); 934 comboExportOutputTarget.ItemIndex := comboExportOutputTarget.Items.IndexOf(GetRegValue(REGNAME_EXP_OUTDATABASE, ''));
935 if comboExportOutputTarget.ItemIndex = -1 then 935 if comboExportOutputTarget.ItemIndex = -1 then
936 comboExportOutputTarget.ItemIndex := 0; 936 comboExportOutputTarget.ItemIndex := 0;
937 end else begin 937 end else begin
938 // Server selected. Display databases in below dropdown 938 // Server selected. Display databases in below dropdown
939 comboExportOutputTarget.Style := csDropDownList; 939 comboExportOutputTarget.Style := csDropDownList;
940 lblExportOutputTarget.Caption := 'Database:'; 940 lblExportOutputTarget.Caption := 'Database:';
941 btnExportOutputTargetSelect.Enabled := False; 941 btnExportOutputTargetSelect.Enabled := False;
942 btnExportOutputTargetSelect.ImageIndex := 27; 942 btnExportOutputTargetSelect.ImageIndex := 27;
943 SessionName := Copy(comboExportOutputType.Text, Length(OUTPUT_SERVER)+1, Length(comboExportOutputType.Text)); 943 SessionName := Copy(comboExportOutputType.Text, Length(OUTPUT_SERVER)+1, Length(comboExportOutputType.Text));
944 FreeAndNil(FTargetConnection); 944 FreeAndNil(FTargetConnection);
945 Params := TConnectionParameters.ReadFromRegistry(SessionName); 945 Params := TConnectionParameters.ReadFromRegistry(SessionName);
946 FTargetConnection := Params.CreateConnection(Self); 946 FTargetConnection := Params.CreateConnection(Self);
947 FTargetConnection.LogPrefix := SessionName; 947 FTargetConnection.LogPrefix := SessionName;
948 FTargetConnection.OnLog := Mainform.LogSQL; 948 FTargetConnection.OnLog := Mainform.LogSQL;
949 Screen.Cursor := crHourglass; 949 Screen.Cursor := crHourglass;
950 try 950 try
951 FTargetConnection.Active := True; 951 FTargetConnection.Active := True;
952 comboExportOutputTarget.Items := FTargetConnection.AllDatabases; 952 comboExportOutputTarget.Items := FTargetConnection.AllDatabases;
953 comboExportOutputTarget.Items.Insert(0, '[Same as on source server]'); 953 comboExportOutputTarget.Items.Insert(0, '[Same as on source server]');
954 comboExportOutputTarget.ItemIndex := comboExportOutputTarget.Items.IndexOf(GetRegValue(REGNAME_EXP_OUTSERVERDB, '')); 954 comboExportOutputTarget.ItemIndex := comboExportOutputTarget.Items.IndexOf(GetRegValue(REGNAME_EXP_OUTSERVERDB, ''));
955 if comboExportOutputTarget.ItemIndex = -1 then 955 if comboExportOutputTarget.ItemIndex = -1 then
956 comboExportOutputTarget.ItemIndex := 0; 956 comboExportOutputTarget.ItemIndex := 0;
957 Screen.Cursor := crDefault; 957 Screen.Cursor := crDefault;
958 except 958 except
959 on E:EDatabaseError do begin 959 on E:EDatabaseError do begin
960 Screen.Cursor := crDefault; 960 Screen.Cursor := crDefault;
961 ErrorDialog(E.Message); 961 ErrorDialog(E.Message);
962 comboExportOutputType.ItemIndex := FLastOutputSelectedIndex; 962 comboExportOutputType.ItemIndex := FLastOutputSelectedIndex;
963 comboExportOutputType.OnChange(Sender); 963 comboExportOutputType.OnChange(Sender);
964 end; 964 end;
965 end; 965 end;
966 end; 966 end;
967 967
968 FLastOutputSelectedIndex := comboExportOutputType.ItemIndex; 968 FLastOutputSelectedIndex := comboExportOutputType.ItemIndex;
969 chkExportDatabasesCreate.Enabled := (comboExportOutputType.Text = OUTPUT_FILE) 969 chkExportDatabasesCreate.Enabled := (comboExportOutputType.Text = OUTPUT_FILE)
970 or (comboExportOutputType.Text = OUTPUT_CLIPBOARD) 970 or (comboExportOutputType.Text = OUTPUT_CLIPBOARD)
971 or (Copy(comboExportOutputType.Text, 1, Length(OUTPUT_SERVER)) = OUTPUT_SERVER); 971 or (Copy(comboExportOutputType.Text, 1, Length(OUTPUT_SERVER)) = OUTPUT_SERVER);
972 chkExportDatabasesDrop.Enabled := chkExportDatabasesCreate.Enabled; 972 chkExportDatabasesDrop.Enabled := chkExportDatabasesCreate.Enabled;
973 ValidateControls(Sender); 973 ValidateControls(Sender);
974 end; 974 end;
975 975
976 976
977 procedure TfrmTableTools.comboExportOutputTargetChange(Sender: TObject); 977 procedure TfrmTableTools.comboExportOutputTargetChange(Sender: TObject);
978 begin 978 begin
979 ValidateControls(Sender); 979 ValidateControls(Sender);
980 end; 980 end;
981 981
982 982
983 procedure TfrmTableTools.chkExportOptionClick(Sender: TObject); 983 procedure TfrmTableTools.chkExportOptionClick(Sender: TObject);
984 procedure WarnIfChecked(chk: TCheckBox; LabelText: String); 984 procedure WarnIfChecked(chk: TCheckBox; LabelText: String);
985 begin 985 begin
986 if chk.Checked then begin 986 if chk.Checked then begin
987 chk.Caption := LabelText + '!!'; 987 chk.Caption := LabelText + '!!';
988 chk.Font.Style := chk.Font.Style + [fsBold]; 988 chk.Font.Style := chk.Font.Style + [fsBold];
989 end else begin 989 end else begin
990 chk.Caption := LabelText; 990 chk.Caption := LabelText;
991 chk.Font.Style := Font.Style; 991 chk.Font.Style := Font.Style;
992 end; 992 end;
993 end; 993 end;
994 begin 994 begin
995 if (Sender = chkExportDatabasesDrop) and chkExportDatabasesDrop.Checked then 995 if (Sender = chkExportDatabasesDrop) and chkExportDatabasesDrop.Checked then
996 chkExportDatabasesCreate.Checked := True 996 chkExportDatabasesCreate.Checked := True
997 else if (Sender = chkExportDatabasesCreate) and (not chkExportDatabasesCreate.Checked) then 997 else if (Sender = chkExportDatabasesCreate) and (not chkExportDatabasesCreate.Checked) then
998 chkExportDatabasesDrop.Checked := False 998 chkExportDatabasesDrop.Checked := False
999 else if (Sender = chkExportTablesDrop) and chkExportTablesDrop.Checked then 999 else if (Sender = chkExportTablesDrop) and chkExportTablesDrop.Checked then
1000 chkExportTablesCreate.Checked := True 1000 chkExportTablesCreate.Checked := True
1001 else if (Sender = chkExportTablesCreate) and (not chkExportTablesCreate.Checked) then 1001 else if (Sender = chkExportTablesCreate) and (not chkExportTablesCreate.Checked) then
1002 chkExportTablesDrop.Checked := False; 1002 chkExportTablesDrop.Checked := False;
1003 WarnIfChecked(chkExportDatabasesDrop, 'Drop'); 1003 WarnIfChecked(chkExportDatabasesDrop, 'Drop');
1004 WarnIfChecked(chkExportTablesDrop, 'Drop'); 1004 WarnIfChecked(chkExportTablesDrop, 'Drop');
1005 end; 1005 end;
1006 1006
1007 1007
1008 procedure TfrmTableTools.btnCloseOrCancelClick(Sender: TObject); 1008 procedure TfrmTableTools.btnCloseOrCancelClick(Sender: TObject);
1009 begin 1009 begin
1010 // Set cancel flag to stop running loop in the next possible loop position 1010 // Set cancel flag to stop running loop in the next possible loop position
1011 if TButton(Sender).ModalResult = mrNone then begin 1011 if TButton(Sender).ModalResult = mrNone then begin
1012 FCancelled := True; 1012 FCancelled := True;
1013 Mainform.LogSQL('Processing cancelled by user, waiting for current object to finish ...', lcInfo); 1013 Mainform.LogSQL('Processing cancelled by user, waiting for current object to finish ...', lcInfo);
1014 end; 1014 end;
1015 end; 1015 end;
1016 1016
1017 1017
1018 procedure TfrmTableTools.btnExportOutputTargetSelectClick(Sender: TObject); 1018 procedure TfrmTableTools.btnExportOutputTargetSelectClick(Sender: TObject);
1019 var 1019 var
1020 SaveDialog: TSaveDialog; 1020 SaveDialog: TSaveDialog;
1021 Browse: TBrowseForFolder; 1021 Browse: TBrowseForFolder;
1022 begin 1022 begin
1023 case comboExportOutputType.ItemIndex of 1023 case comboExportOutputType.ItemIndex of
1024 0: begin 1024 0: begin
1025 // Select filename 1025 // Select filename
1026 SaveDialog := TSaveDialog.Create(Self); 1026 SaveDialog := TSaveDialog.Create(Self);
1027 SaveDialog.DefaultExt := 'sql'; 1027 SaveDialog.DefaultExt := 'sql';
1028 SaveDialog.Filter := 'SQL-Scripts (*.sql)|*.sql|All Files (*.*)|*.*'; 1028 SaveDialog.Filter := 'SQL-Scripts (*.sql)|*.sql|All Files (*.*)|*.*';
1029 SaveDialog.Options := SaveDialog.Options + [ofOverwritePrompt]; 1029 SaveDialog.Options := SaveDialog.Options + [ofOverwritePrompt];
1030 if SaveDialog.Execute then 1030 if SaveDialog.Execute then
1031 comboExportOutputTarget.Text := SaveDialog.FileName; 1031 comboExportOutputTarget.Text := SaveDialog.FileName;
1032 SaveDialog.Free; 1032 SaveDialog.Free;
1033 end; 1033 end;
1034 1: begin 1034 1: begin
1035 Browse := TBrowseForFolder.Create(Self); 1035 Browse := TBrowseForFolder.Create(Self);
1036 Browse.Folder := comboExportOutputTarget.Text; 1036 Browse.Folder := comboExportOutputTarget.Text;
1037 Browse.DialogCaption := 'Select output directory'; 1037 Browse.DialogCaption := 'Select output directory';
1038 // Enable "Create new folder" button 1038 // Enable "Create new folder" button
1039 Browse.BrowseOptions := Browse.BrowseOptions - [bifNoNewFolderButton] + [bifNewDialogStyle]; 1039 Browse.BrowseOptions := Browse.BrowseOptions - [bifNoNewFolderButton] + [bifNewDialogStyle];
1040 if Browse.Execute then 1040 if Browse.Execute then
1041 comboExportOutputTarget.Text := Browse.Folder; 1041 comboExportOutputTarget.Text := Browse.Folder;
1042 Browse.Free; 1042 Browse.Free;
1043 end; 1043 end;
1044 end; 1044 end;
1045 ValidateControls(Sender); 1045 ValidateControls(Sender);
1046 end; 1046 end;
1047 1047
1048 procedure TfrmTableTools.SetToolMode(Value: TToolMode); 1048 procedure TfrmTableTools.SetToolMode(Value: TToolMode);
1049 begin 1049 begin
1050 FToolMode := Value; 1050 FToolMode := Value;
1051 case FToolMode of 1051 case FToolMode of
1052 tmMaintenance: tabsTools.ActivePage := tabMaintenance; 1052 tmMaintenance: tabsTools.ActivePage := tabMaintenance;
1053 tmFind: tabsTools.ActivePage := tabFind; 1053 tmFind: tabsTools.ActivePage := tabFind;
1054 tmSQLExport: tabsTools.ActivePage := tabSQLExport; 1054 tmSQLExport: tabsTools.ActivePage := tabSQLExport;
1055 tmBulkTableEdit: tabsTools.ActivePage := tabBulkTableEdit; 1055 tmBulkTableEdit: tabsTools.ActivePage := tabBulkTableEdit;
1056 end; 1056 end;
1057 end; 1057 end;
1058 1058
1059 1059
1060 // Pass output to file or query, and append semicolon if needed 1060 // Pass output to file or query, and append semicolon if needed
1061 procedure TfrmTableTools.Output(SQL: String; IsEndOfQuery, ForFile, ForDir, ForDb, ForServer: Boolean); 1061 procedure TfrmTableTools.Output(SQL: String; IsEndOfQuery, ForFile, ForDir, ForDb, ForServer: Boolean);
1062 var 1062 var
1063 SA: AnsiString; 1063 SA: AnsiString;
1064 ChunkSize: Integer; 1064 ChunkSize: Integer;
1065 begin 1065 begin
1066 if (ToFile and ForFile) or (ToDir and ForDir) or (ToClipboard and ForFile) then begin 1066 if (ToFile and ForFile) or (ToDir and ForDir) or (ToClipboard and ForFile) then begin
1067 if IsEndOfQuery then 1067 if IsEndOfQuery then
1068 SQL := SQL + ';'+CRLF; 1068 SQL := SQL + ';'+CRLF;
1069 StreamWrite(ExportStream, SQL); 1069 StreamWrite(ExportStream, SQL);
1070 if IsEndOfQuery then 1070 if IsEndOfQuery then
1071 ExportStreamStartOfQueryPos := ExportStream.Size; 1071 ExportStreamStartOfQueryPos := ExportStream.Size;
1072 end; 1072 end;
1073 if (ToDb and ForDb) or (ToServer and ForServer) then begin 1073 if (ToDb and ForDb) or (ToServer and ForServer) then begin
1074 StreamWrite(ExportStream, SQL); 1074 StreamWrite(ExportStream, SQL);
1075 if IsEndOfQuery then begin 1075 if IsEndOfQuery then begin
1076 ExportStream.Position := 0; 1076 ExportStream.Position := 0;
1077 ChunkSize := ExportStream.Size; 1077 ChunkSize := ExportStream.Size;
1078 SetLength(SA, ChunkSize div SizeOf(AnsiChar)); 1078 SetLength(SA, ChunkSize div SizeOf(AnsiChar));
1079 ExportStream.Read(PAnsiChar(SA)^, ChunkSize); 1079 ExportStream.Read(PAnsiChar(SA)^, ChunkSize);
1080 ExportStream.Size := 0; 1080 ExportStream.Size := 0;
1081 ExportStreamStartOfQueryPos := 0; 1081 ExportStreamStartOfQueryPos := 0;
1082 SQL := UTF8ToString(SA); 1082 SQL := UTF8ToString(SA);
1083 if ToDB then MainForm.ActiveConnection.Query(SQL) 1083 if ToDB then MainForm.ActiveConnection.Query(SQL)
1084 else if ToServer then FTargetConnection.Query(SQL); 1084 else if ToServer then FTargetConnection.Query(SQL);
1085 SQL := ''; 1085 SQL := '';
1086 end; 1086 end;
1087 end; 1087 end;
1088 end; 1088 end;
1089 1089
1090 1090
1091 procedure TfrmTableTools.DoExport(DBObj: TDBObject); 1091 procedure TfrmTableTools.DoExport(DBObj: TDBObject);
1092 var 1092 var
1093 IsFirstRowInChunk, NeedsDBStructure: Boolean; 1093 IsFirstRowInChunk, NeedsDBStructure: Boolean;
1094 Struc, Header, DbDir, FinalDbName, BaseInsert, Row, TargetDbAndObject, BinContent, tmp, Dummy: String; 1094 Struc, Header, DbDir, FinalDbName, BaseInsert, Row, TargetDbAndObject, BinContent, tmp, Dummy: String;
1095 i: Integer; 1095 i: Integer;
1096 RowCount, Limit, Offset, ResultCount: Int64; 1096 RowCount, Limit, Offset, ResultCount: Int64;
1097 StartTime: Cardinal; 1097 StartTime: Cardinal;
1098 StrucResult, Data: TDBQuery; 1098 StrucResult, Data: TDBQuery;
1099 rx: TRegExpr; 1099 rx: TRegExpr;
1100 ColumnList: TTableColumnList; 1100 ColumnList: TTableColumnList;
1101 Column: TTableColumn; 1101 Column: TTableColumn;
1102 Quoter: TDBConnection; 1102 Quoter: TDBConnection;
1103 const 1103 const
1104 TempDelim = '//'; 1104 TempDelim = '//';
1105 1105
1106 procedure LogStatistic(RowsDone: Int64); 1106 procedure LogStatistic(RowsDone: Int64);
1107 var 1107 var
1108 LogRow: TStringlist; 1108 LogRow: TStringlist;
1109 Percent: Double; 1109 Percent: Double;
1110 BytesDone: Int64; 1110 BytesDone: Int64;
1111 begin 1111 begin
1112 LogRow := FResults.Last; 1112 LogRow := FResults.Last;
1113 Percent := 100 / Max(DBObj.Rows,1) * Max(RowsDone,1); 1113 Percent := 100 / Max(DBObj.Rows,1) * Max(RowsDone,1);
1114 BytesDone := Max(DBObj.Size,0) div Max(DBObj.Rows,1) * RowsDone; 1114 BytesDone := Max(DBObj.Size,0) div Max(DBObj.Rows,1) * RowsDone;
1115 FObjectSizesDoneExact := FObjectSizesDone + BytesDone; 1115 FObjectSizesDoneExact := FObjectSizesDone + BytesDone;
1116 LogRow[2] := FormatNumber(RowsDone) + ' / ' + FormatNumber(Percent, 0)+'%'; 1116 LogRow[2] := FormatNumber(RowsDone) + ' / ' + FormatNumber(Percent, 0)+'%';
1117 LogRow[3] := FormatTimeNumber((GetTickCount-StartTime) DIV 1000, True); 1117 LogRow[3] := FormatTimeNumber((GetTickCount-StartTime) DIV 1000, True);
1118 UpdateResultGrid; 1118 UpdateResultGrid;
1119 end; 1119 end;
1120 1120
1121 begin 1121 begin
1122 // Handle one table, view or whatever in SQL export mode 1122 // Handle one table, view or whatever in SQL export mode
1123 AddResults('SELECT '+esc(DBObj.Database)+' AS '+DBObj.Connection.QuoteIdent('Database')+', ' + 1123 AddResults('SELECT '+esc(DBObj.Database)+' AS '+DBObj.Connection.QuoteIdent('Database')+', ' +
1124 esc(DBObj.Name)+' AS '+DBObj.Connection.QuoteIdent('Table')+', ' + 1124 esc(DBObj.Name)+' AS '+DBObj.Connection.QuoteIdent('Table')+', ' +
1125 IntToStr(DBObj.Rows)+' AS '+DBObj.Connection.QuoteIdent('Rows')+', '+ 1125 IntToStr(DBObj.Rows)+' AS '+DBObj.Connection.QuoteIdent('Rows')+', '+
1126 '0 AS '+DBObj.Connection.QuoteIdent('Duration') 1126 '0 AS '+DBObj.Connection.QuoteIdent('Duration')
1127 ); 1127 );
1128 ToFile := comboExportOutputType.Text = OUTPUT_FILE; 1128 ToFile := comboExportOutputType.Text = OUTPUT_FILE;
1129 ToDir := comboExportOutputType.Text = OUTPUT_DIR; 1129 ToDir := comboExportOutputType.Text = OUTPUT_DIR;
1130 ToClipboard := comboExportOutputType.Text = OUTPUT_CLIPBOARD; 1130 ToClipboard := comboExportOutputType.Text = OUTPUT_CLIPBOARD;
1131 ToDb := comboExportOutputType.Text = OUTPUT_DB; 1131 ToDb := comboExportOutputType.Text = OUTPUT_DB;
1132 ToServer := Copy(comboExportOutputType.Text, 1, Length(OUTPUT_SERVER)) = OUTPUT_SERVER; 1132 ToServer := Copy(comboExportOutputType.Text, 1, Length(OUTPUT_SERVER)) = OUTPUT_SERVER;
1133 1133
1134 if ToServer then 1134 if ToServer then
1135 Quoter := FTargetConnection 1135 Quoter := FTargetConnection
1136 else 1136 else
1137 Quoter := DBObj.Connection; 1137 Quoter := DBObj.Connection;
1138 1138
1139 StartTime := GetTickCount; 1139 StartTime := GetTickCount;
1140 ExportStreamStartOfQueryPos := 0; 1140 ExportStreamStartOfQueryPos := 0;
1141 if ToDir then begin 1141 if ToDir then begin
1142 FreeAndNil(ExportStream); 1142 FreeAndNil(ExportStream);
1143 DbDir := comboExportOutputTarget.Text; 1143 DbDir := comboExportOutputTarget.Text;
1144 if DbDir[Length(DbDir)] <> '\' then 1144 if DbDir[Length(DbDir)] <> '\' then
1145 DbDir := DbDir + '\'; 1145 DbDir := DbDir + '\';
1146 DbDir := DbDir + DBObj.Database + '\'; 1146 DbDir := DbDir + DBObj.Database + '\';
1147 if not DirectoryExists(DbDir) then 1147 if not DirectoryExists(DbDir) then
1148 ForceDirectories(DbDir); 1148 ForceDirectories(DbDir);
1149 ExportStream := TFileStream.Create(DbDir + DBObj.Name+'.sql', fmCreate or fmOpenWrite); 1149 ExportStream := TFileStream.Create(DbDir + DBObj.Name+'.sql', fmCreate or fmOpenWrite);
1150 FHeaderCreated := False; 1150 FHeaderCreated := False;
1151 end; 1151 end;
1152 if ToFile and (not Assigned(ExportStream)) then 1152 if not Assigned(ExportStream) then begin
1153 ExportStream := TFileStream.Create(comboExportOutputTarget.Text, fmCreate or fmOpenWrite); 1153 if ToFile then
1154 if ToClipboard and (not Assigned(ExportStream)) then 1154 ExportStream := TFileStream.Create(comboExportOutputTarget.Text, fmCreate or fmOpenWrite);
1155 ExportStream := TMemoryStream.Create; 1155 // ToDir handled above
1156 if ToDb or ToServer then 1156 if ToClipboard then
1157 ExportStream := TMemoryStream.Create; 1157 ExportStream := TMemoryStream.Create;
1158 if ToDb or ToServer then
1159 ExportStream := TMemoryStream.Create;
1160 end;
1158 if not FHeaderCreated then begin 1161 if not FHeaderCreated then begin
1159 Header := '-- --------------------------------------------------------' + CRLF + 1162 Header := '-- --------------------------------------------------------' + CRLF +
1160 Format('-- %-30s%s', ['Host:', DBObj.Connection.Parameters.HostName]) + CRLF + 1163 Format('-- %-30s%s', ['Host:', DBObj.Connection.Parameters.HostName]) + CRLF +
1161 Format('-- %-30s%s', ['Server version:', DBObj.Connection.ServerVersionUntouched]) + CRLF + 1164 Format('-- %-30s%s', ['Server version:', DBObj.Connection.ServerVersionUntouched]) + CRLF +
1162 Format('-- %-30s%s', ['Server OS:', DBObj.Connection.ServerOS]) + CRLF + 1165 Format('-- %-30s%s', ['Server OS:', DBObj.Connection.ServerOS]) + CRLF +
1163 Format('-- %-30s%s', [APPNAME + ' version:', Mainform.AppVersion]) + CRLF + 1166 Format('-- %-30s%s', [APPNAME + ' version:', Mainform.AppVersion]) + CRLF +
1164 Format('-- %-30s%s', ['Date/time:', DateTimeToStr(Now)]) + CRLF + 1167 Format('-- %-30s%s', ['Date/time:', DateTimeToStr(Now)]) + CRLF +
1165 '-- --------------------------------------------------------' + CRLF + CRLF + 1168 '-- --------------------------------------------------------' + CRLF + CRLF +
1166 '/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;' + CRLF + 1169 '/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;' + CRLF +
1167 '/*!40101 SET NAMES '+DBObj.Connection.CharacterSet+' */;' + CRLF + 1170 '/*!40101 SET NAMES '+DBObj.Connection.CharacterSet+' */;' + CRLF +
1168 '/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;' + CRLF + 1171 '/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;' + CRLF +
1169 '/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=''NO_AUTO_VALUE_ON_ZERO'' */;'; 1172 '/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=''NO_AUTO_VALUE_ON_ZERO'' */;';
1170 Output(Header, False, DBObj.Database<>ExportLastDatabase, True, False, False); 1173 Output(Header, False, DBObj.Database<>ExportLastDatabase, True, False, False);
1171 Output('/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */', True, False, False, True, True); 1174 Output('/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */', True, False, False, True, True);
1172 Output('/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=''NO_AUTO_VALUE_ON_ZERO'' */', True, False, False, True, True); 1175 Output('/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=''NO_AUTO_VALUE_ON_ZERO'' */', True, False, False, True, True);
1173 FHeaderCreated := True; 1176 FHeaderCreated := True;
1174 end; 1177 end;
1175 1178
1176 // Database structure. Do that only in single-file and server mode. drop/create/use in directory or database mode makes no sense 1179 // Database structure. Do that only in single-file and server mode. drop/create/use in directory or database mode makes no sense
1177 FinalDbName := DBObj.Database; 1180 FinalDbName := DBObj.Database;
1178 if ToDb or (ToServer and (comboExportOutputTarget.ItemIndex > 0)) then 1181 if ToDb or (ToServer and (comboExportOutputTarget.ItemIndex > 0)) then
1179 FinalDbName := comboExportOutputTarget.Text; 1182 FinalDbName := comboExportOutputTarget.Text;
1180 NeedsDBStructure := FinalDbName <> ExportLastDatabase; 1183 NeedsDBStructure := FinalDbName <> ExportLastDatabase;
1181 if chkExportDatabasesDrop.Checked or chkExportDatabasesCreate.Checked then begin 1184 if chkExportDatabasesDrop.Checked or chkExportDatabasesCreate.Checked then begin
1182 Output(CRLF+CRLF+'-- Dumping database structure for '+DBObj.Database+CRLF, False, NeedsDBStructure, False, False, False); 1185 Output(CRLF+CRLF+'-- Dumping database structure for '+DBObj.Database+CRLF, False, NeedsDBStructure, False, False, False);
1183 if chkExportDatabasesDrop.Checked and chkExportDatabasesDrop.Enabled then 1186 if chkExportDatabasesDrop.Checked and chkExportDatabasesDrop.Enabled then
1184 Output('DROP DATABASE IF EXISTS '+Quoter.QuoteIdent(FinalDbName), True, NeedsDBStructure, False, False, NeedsDBStructure); 1187 Output('DROP DATABASE IF EXISTS '+Quoter.QuoteIdent(FinalDbName), True, NeedsDBStructure, False, False, NeedsDBStructure);
1185 if chkExportDatabasesCreate.Checked and chkExportDatabasesCreate.Enabled then begin 1188 if chkExportDatabasesCreate.Checked and chkExportDatabasesCreate.Enabled then begin
1186 if DBObj.Connection.ServerVersionInt >= 40100 then begin 1189 if DBObj.Connection.ServerVersionInt >= 40100 then begin
1187 Struc := DBObj.Connection.GetVar('SHOW CREATE DATABASE '+DBObj.QuotedDatabase, 1); 1190 Struc := DBObj.Connection.GetVar('SHOW CREATE DATABASE '+DBObj.QuotedDatabase, 1);
1188 // Gracefully ignore it when target database exists, important in server mode 1191 // Gracefully ignore it when target database exists, important in server mode
1189 Insert('IF NOT EXISTS ', Struc, Pos('DATABASE', Struc) + 9); 1192 Insert('IF NOT EXISTS ', Struc, Pos('DATABASE', Struc) + 9);
1190 // Create the right dbname 1193 // Create the right dbname
1191 Struc := StringReplace(Struc, DBObj.Database, FinalDbName, []); 1194 Struc := StringReplace(Struc, DBObj.Database, FinalDbName, []);
1192 end else 1195 end else
1193 Struc := 'CREATE DATABASE IF NOT EXISTS '+Quoter.QuoteIdent(FinalDbName); 1196 Struc := 'CREATE DATABASE IF NOT EXISTS '+Quoter.QuoteIdent(FinalDbName);
1194 Output(Struc, True, NeedsDBStructure, False, False, NeedsDBStructure); 1197 Output(Struc, True, NeedsDBStructure, False, False, NeedsDBStructure);
1195 Output('USE '+Quoter.QuoteIdent(FinalDbName), True, NeedsDBStructure, False, False, NeedsDBStructure); 1198 Output('USE '+Quoter.QuoteIdent(FinalDbName), True, NeedsDBStructure, False, False, NeedsDBStructure);
1196 end; 1199 end;
1197 end; 1200 end;
1198 if ToServer and (not chkExportDatabasesCreate.Checked) then begin 1201 if ToServer and (not chkExportDatabasesCreate.Checked) then begin
1199 // Export to server without "CREATE/USE dbname" and "Same dbs as on source server" - needs a "USE dbname" 1202 // Export to server without "CREATE/USE dbname" and "Same dbs as on source server" - needs a "USE dbname"
1200 Output('USE '+Quoter.QuoteIdent(FinalDbName), True, False, False, False, NeedsDBStructure); 1203 Output('USE '+Quoter.QuoteIdent(FinalDbName), True, False, False, False, NeedsDBStructure);
1201 end; 1204 end;
1202 1205
1203 // Table structure 1206 // Table structure
1204 if chkExportTablesDrop.Checked or chkExportTablesCreate.Checked then begin 1207 if chkExportTablesDrop.Checked or chkExportTablesCreate.Checked then begin
1205 Output(CRLF+CRLF+'-- Dumping structure for '+LowerCase(DBObj.ObjType)+' '+DBObj.Database+'.'+DBObj.Name+CRLF, False, True, True, False, False); 1208 Output(CRLF+CRLF+'-- Dumping structure for '+LowerCase(DBObj.ObjType)+' '+DBObj.Database+'.'+DBObj.Name+CRLF, False, True, True, False, False);
1206 if chkExportTablesDrop.Checked then begin 1209 if chkExportTablesDrop.Checked then begin
1207 Struc := 'DROP '+UpperCase(DBObj.ObjType)+' IF EXISTS '; 1210 Struc := 'DROP '+UpperCase(DBObj.ObjType)+' IF EXISTS ';
1208 if ToDb then 1211 if ToDb then
1209 Struc := Struc + Quoter.QuoteIdent(FinalDbName)+'.'; 1212 Struc := Struc + Quoter.QuoteIdent(FinalDbName)+'.';
1210 Struc := Struc + Quoter.QuoteIdent(DBObj.Name); 1213 Struc := Struc + Quoter.QuoteIdent(DBObj.Name);
1211 Output(Struc, True, True, True, True, True); 1214 Output(Struc, True, True, True, True, True);
1212 end; 1215 end;
1213 if chkExportTablesCreate.Checked then begin 1216 if chkExportTablesCreate.Checked then begin
1214 try 1217 try
1215 case DBObj.NodeType of 1218 case DBObj.NodeType of
1216 lntTable: begin 1219 lntTable: begin
1217 Struc := DBObj.CreateCode; 1220 Struc := DBObj.CreateCode;
1218 // Remove AUTO_INCREMENT clause if no data gets exported 1221 // Remove AUTO_INCREMENT clause if no data gets exported
1219 if comboExportData.Text = DATA_NO then begin 1222 if comboExportData.Text = DATA_NO then begin
1220 rx := TRegExpr.Create; 1223 rx := TRegExpr.Create;
1221 rx.ModifierI := True; 1224 rx.ModifierI := True;
1222 rx.Expression := '\sAUTO_INCREMENT\s*\=\s*\d+\s'; 1225 rx.Expression := '\sAUTO_INCREMENT\s*\=\s*\d+\s';
1223 Struc := rx.Replace(Struc, ' ', false); 1226 Struc := rx.Replace(Struc, ' ', false);
1224 rx.Free; 1227 rx.Free;
1225 end; 1228 end;
1226 Insert('IF NOT EXISTS ', Struc, Pos('TABLE', Struc) + 6); 1229 Insert('IF NOT EXISTS ', Struc, Pos('TABLE', Struc) + 6);
1227 if ToDb then 1230 if ToDb then
1228 Insert(Quoter.QuoteIdent(FinalDbName)+'.', Struc, Pos('EXISTS', Struc) + 7 ); 1231 Insert(Quoter.QuoteIdent(FinalDbName)+'.', Struc, Pos('EXISTS', Struc) + 7 );
1229 if ToServer then begin 1232 if ToServer then begin
1230 rx := TRegExpr.Create; 1233 rx := TRegExpr.Create;
1231 rx.ModifierI := True; 1234 rx.ModifierI := True;
1232 rx.Expression := '(\s)(TYPE|ENGINE)(\=|\s+)(\w+)'; 1235 rx.Expression := '(\s)(TYPE|ENGINE)(\=|\s+)(\w+)';
1233 if FTargetConnection.ServerVersionInt < 40018 then 1236 if FTargetConnection.ServerVersionInt < 40018 then
1234 Struc := rx.Replace(Struc, '${1}TYPE${3}${4}', true) 1237 Struc := rx.Replace(Struc, '${1}TYPE${3}${4}', true)
1235 else 1238 else
1236 Struc := rx.Replace(Struc, '${1}ENGINE${3}${4}', true); 1239 Struc := rx.Replace(Struc, '${1}ENGINE${3}${4}', true);
1237 rx.Free; 1240 rx.Free;
1238 end; 1241 end;
1239 end; 1242 end;
1240 1243
1241 lntView: begin 1244 lntView: begin
1242 if not FSecondExportPass then begin 1245 if not FSecondExportPass then begin
1243 // Create temporary VIEW replacement 1246 // Create temporary VIEW replacement
1244 ColumnList := TTableColumnList.Create(True); 1247 ColumnList := TTableColumnList.Create(True);
1245 DBObj.Connection.ParseViewStructure(DBObj.CreateCode, DBObj.Name, ColumnList, Dummy, Dummy, Dummy, Dummy, Dummy); 1248 DBObj.Connection.ParseViewStructure(DBObj.CreateCode, DBObj.Name, ColumnList, Dummy, Dummy, Dummy, Dummy, Dummy);
1246 Struc := '-- Creating temporary table to overcome VIEW dependency errors'+CRLF+ 1249 Struc := '-- Creating temporary table to overcome VIEW dependency errors'+CRLF+
1247 'CREATE TABLE '; 1250 'CREATE TABLE ';
1248 if ToDb then 1251 if ToDb then
1249 Struc := Struc + Quoter.QuoteIdent(FinalDbName) + '.'; 1252 Struc := Struc + Quoter.QuoteIdent(FinalDbName) + '.';
1250 Struc := Struc + Quoter.QuoteIdent(DBObj.Name)+' ('; 1253 Struc := Struc + Quoter.QuoteIdent(DBObj.Name)+' (';
1251 for Column in ColumnList do begin 1254 for Column in ColumnList do begin
1252 // Prevent DEFAULT value from coming in, to fix errors due to multiple CURRENT_TIMESTAMP values 1255 // Prevent DEFAULT value from coming in, to fix errors due to multiple CURRENT_TIMESTAMP values
1253 // See issue #2748 1256 // See issue #2748
1254 Column.DefaultType := cdtNothing; 1257 Column.DefaultType := cdtNothing;
1255 Struc := Struc + CRLF + #9 + Column.SQLCode + ','; 1258 Struc := Struc + CRLF + #9 + Column.SQLCode + ',';
1256 end; 1259 end;
1257 Delete(Struc, Length(Struc), 1); 1260 Delete(Struc, Length(Struc), 1);
1258 Struc := Struc + CRLF + ') ENGINE=MyISAM'; 1261 Struc := Struc + CRLF + ') ENGINE=MyISAM';
1259 ColumnList.Free; 1262 ColumnList.Free;
1260 end else begin 1263 end else begin
1261 Struc := '-- Removing temporary table and create final VIEW structure'+CRLF+ 1264 Struc := '-- Removing temporary table and create final VIEW structure'+CRLF+
1262 'DROP TABLE IF EXISTS '; 1265 'DROP TABLE IF EXISTS ';
1263 if ToDb then 1266 if ToDb then
1264 Struc := Struc + Quoter.QuoteIdent(FinalDbName)+'.'; 1267 Struc := Struc + Quoter.QuoteIdent(FinalDbName)+'.';
1265 Struc := Struc + Quoter.QuoteIdent(DBObj.Name); 1268 Struc := Struc + Quoter.QuoteIdent(DBObj.Name);
1266 Output(Struc, True, True, True, True, True); 1269 Output(Struc, True, True, True, True, True);
1267 Struc := DBObj.CreateCode; 1270 Struc := DBObj.CreateCode;
1268 if ToDb then 1271 if ToDb then
1269 Insert(Quoter.QuoteIdent(FinalDbName)+'.', Struc, Pos('VIEW', Struc) + 5 ); 1272 Insert(Quoter.QuoteIdent(FinalDbName)+'.', Struc, Pos('VIEW', Struc) + 5 );
1270 end; 1273 end;
1271 end; 1274 end;
1272 1275
1273 lntTrigger: begin 1276 lntTrigger: begin
1274 StrucResult := DBObj.Connection.GetResults('SHOW TRIGGERS FROM '+DBObj.QuotedDatabase+' WHERE `Trigger`='+esc(DBObj.Name)); 1277 StrucResult := DBObj.Connection.GetResults('SHOW TRIGGERS FROM '+DBObj.QuotedDatabase+' WHERE `Trigger`='+esc(DBObj.Name));
1275 Struc := 'CREATE '+UpperCase(DBObj.ObjType)+' '+Quoter.QuoteIdent(DBObj.Name)+' '+StrucResult.Col('Timing')+' '+StrucResult.Col('Event')+ 1278 Struc := 'CREATE '+UpperCase(DBObj.ObjType)+' '+Quoter.QuoteIdent(DBObj.Name)+' '+StrucResult.Col('Timing')+' '+StrucResult.Col('Event')+
1276 ' ON '+Quoter.QuoteIdent(StrucResult.Col('Table'))+' FOR EACH ROW '+StrucResult.Col('Statement'); 1279 ' ON '+Quoter.QuoteIdent(StrucResult.Col('Table'))+' FOR EACH ROW '+StrucResult.Col('Statement');
1277 if ToDb then 1280 if ToDb then
1278 Insert(Quoter.QuoteIdent(FinalDbName)+'.', Struc, Pos('TRIGGER', Struc) + 8 ); 1281 Insert(Quoter.QuoteIdent(FinalDbName)+'.', Struc, Pos('TRIGGER', Struc) + 8 );
1279 if ToFile or ToClipboard or ToDir then begin 1282 if ToFile or ToClipboard or ToDir then begin
1280 Struc := 'SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE=' + esc(StrucResult.Col('sql_mode')) + ';' + CRLF + 1283 Struc := 'SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE=' + esc(StrucResult.Col('sql_mode')) + ';' + CRLF +
1281 'DELIMITER ' + TempDelim + CRLF + 1284 'DELIMITER ' + TempDelim + CRLF +
1282 Struc + TempDelim + CRLF + 1285 Struc + TempDelim + CRLF +
1283 'DELIMITER ;' + CRLF + 1286 'DELIMITER ;' + CRLF +
1284 'SET SQL_MODE=@OLDTMP_SQL_MODE'; 1287 'SET SQL_MODE=@OLDTMP_SQL_MODE';
1285 end; 1288 end;
1286 end; 1289 end;
1287 1290
1288 lntFunction, lntProcedure: begin 1291 lntFunction, lntProcedure: begin
1289 Struc := DBObj.CreateCode; 1292 Struc := DBObj.CreateCode;
1290 if ToDb then begin 1293 if ToDb then begin
1291 if DBObj.NodeType = lntProcedure then 1294 if DBObj.NodeType = lntProcedure then
1292 Insert(Quoter.QuoteIdent(FinalDbName)+'.', Struc, Pos('PROCEDURE', Struc) + 10 ) 1295 Insert(Quoter.QuoteIdent(FinalDbName)+'.', Struc, Pos('PROCEDURE', Struc) + 10 )
1293 else if DBObj.NodeType = lntFunction then 1296 else if DBObj.NodeType = lntFunction then
1294 Insert(Quoter.QuoteIdent(FinalDbName)+'.', Struc, Pos('FUNCTION', Struc) + 9 ); 1297 Insert(Quoter.QuoteIdent(FinalDbName)+'.', Struc, Pos('FUNCTION', Struc) + 9 );
1295 end; 1298 end;
1296 // Change delimiter for file output, so readers split queries at the right string position 1299 // Change delimiter for file output, so readers split queries at the right string position
1297 if ToFile or ToDir or ToClipboard then 1300 if ToFile or ToDir or ToClipboard then
1298 Struc := 'DELIMITER ' + TempDelim + CRLF + Struc + TempDelim + CRLF + 'DELIMITER '; 1301 Struc := 'DELIMITER ' + TempDelim + CRLF + Struc + TempDelim + CRLF + 'DELIMITER ';
1299 end; 1302 end;
1300 1303
1301 lntEvent: begin 1304 lntEvent: begin
1302 Struc := DBObj.CreateCode; 1305 Struc := DBObj.CreateCode;
1303 if ToDb then 1306 if ToDb then
1304 Insert(Quoter.QuoteIdent(FinalDbName)+'.', Struc, Pos('EVENT', Struc) + 6 ); 1307 Insert(Quoter.QuoteIdent(FinalDbName)+'.', Struc, Pos('EVENT', Struc) + 6 );
1305 if ToFile or ToDir or ToClipboard then 1308 if ToFile or ToDir or ToClipboard then
1306 Struc := 'DELIMITER ' + TempDelim + CRLF + Struc + TempDelim + CRLF + 'DELIMITER '; 1309 Struc := 'DELIMITER ' + TempDelim + CRLF + Struc + TempDelim + CRLF + 'DELIMITER ';
1307 end; 1310 end;
1308 end; 1311 end;
1309 Struc := fixNewlines(Struc); 1312 Struc := fixNewlines(Struc);
1310 Output(Struc, True, True, True, True, True); 1313 Output(Struc, True, True, True, True, True);
1311 except 1314 except
1312 on E:EDatabaseError do begin 1315 on E:EDatabaseError do begin
1313 // Catch the exception message and dump it into the export file for debugging reasons 1316 // Catch the exception message and dump it into the export file for debugging reasons
1314 Output('/* '+E.Message+' */', False, True, True, False, False); 1317 Output('/* '+E.Message+' */', False, True, True, False, False);
1315 Raise; 1318 Raise;
1316 end; 1319 end;
1317 end; 1320 end;
1318 end; 1321 end;
1319 end; 1322 end;
1320 1323
1321 if DBObj.NodeType = lntTable then begin 1324 if DBObj.NodeType = lntTable then begin
1322 // Table data 1325 // Table data
1323 if comboExportData.Text = DATA_NO then begin 1326 if comboExportData.Text = DATA_NO then begin
1324 Output(CRLF+'-- Data exporting was unselected.'+CRLF, False, True, True, False, False); 1327 Output(CRLF+'-- Data exporting was unselected.'+CRLF, False, True, True, False, False);
1325 end else if DBObj.Engine = 'MRG_MYISAM' then begin 1328 end else if DBObj.Engine = 'MRG_MYISAM' then begin
1326 Output(CRLF+'-- Table data not exported because this is '+DBObj.Engine+' table which holds its data in separate tables.'+CRLF, False, True, True, False, False); 1329 Output(CRLF+'-- Table data not exported because this is '+DBObj.Engine+' table which holds its data in separate tables.'+CRLF, False, True, True, False, False);
1327 end else begin 1330 end else begin
1328 tmp := FormatNumber(DBObj.Rows)+' rows'; 1331 tmp := FormatNumber(DBObj.Rows)+' rows';
1329 if LowerCase(DBObj.Engine) = 'innodb' then 1332 if LowerCase(DBObj.Engine) = 'innodb' then
1330 tmp := '~'+tmp+' (approximately)'; 1333 tmp := '~'+tmp+' (approximately)';
1331 Output(CRLF+'-- Dumping data for table '+DBObj.Database+'.'+DBObj.Name+': '+tmp+CRLF, False, True, True, False, False); 1334 Output(CRLF+'-- Dumping data for table '+DBObj.Database+'.'+DBObj.Name+': '+tmp+CRLF, False, True, True, False, False);
1332 TargetDbAndObject := Quoter.QuoteIdent(DBObj.Name); 1335 TargetDbAndObject := Quoter.QuoteIdent(DBObj.Name);
1333 if ToDb then 1336 if ToDb then
1334 TargetDbAndObject := Quoter.QuoteIdent(FinalDbName) + '.' + TargetDbAndObject; 1337 TargetDbAndObject := Quoter.QuoteIdent(FinalDbName) + '.' + TargetDbAndObject;
1335 Offset := 0; 1338 Offset := 0;
1336 RowCount := 0; 1339 RowCount := 0;
1337 // Calculate limit so we select ~100MB per loop 1340 // Calculate limit so we select ~100MB per loop
1338 Limit := Round(100 * SIZE_MB / Max(DBObj.AvgRowLen,1)); 1341 Limit := Round(100 * SIZE_MB / Max(DBObj.AvgRowLen,1));
1339 if comboExportData.Text = DATA_REPLACE then 1342 if comboExportData.Text = DATA_REPLACE then
1340 Output('DELETE FROM '+TargetDbAndObject, True, True, True, True, True); 1343 Output('DELETE FROM '+TargetDbAndObject, True, True, True, True, True);
1341 Output('/*!40000 ALTER TABLE '+TargetDbAndObject+' DISABLE KEYS */', True, True, True, True, True); 1344 Output('/*!40000 ALTER TABLE '+TargetDbAndObject+' DISABLE KEYS */', True, True, True, True, True);
1342 while true do begin 1345 while true do begin
1343 Data := DBObj.Connection.GetResults('SELECT * FROM '+DBObj.QuotedDatabase+'.'+DBObj.QuotedName+' LIMIT '+IntToStr(Offset)+', '+IntToStr(Limit)); 1346 Data := DBObj.Connection.GetResults('SELECT * FROM '+DBObj.QuotedDatabase+'.'+DBObj.QuotedName+' LIMIT '+IntToStr(Offset)+', '+IntToStr(Limit));
1344 Inc(Offset, Limit); 1347 Inc(Offset, Limit);
1345 if Data.RecordCount = 0 then 1348 if Data.RecordCount = 0 then
1346 break; 1349 break;
1347 BaseInsert := 'INSERT INTO '; 1350 BaseInsert := 'INSERT INTO ';
1348 if comboExportData.Text = DATA_INSERTNEW then 1351 if comboExportData.Text = DATA_INSERTNEW then
1349 BaseInsert := 'INSERT IGNORE INTO ' 1352 BaseInsert := 'INSERT IGNORE INTO '
1350 else if comboExportData.Text = DATA_UPDATE then 1353 else if comboExportData.Text = DATA_UPDATE then
1351 BaseInsert := 'REPLACE INTO '; 1354 BaseInsert := 'REPLACE INTO ';
1352 BaseInsert := BaseInsert + TargetDbAndObject + ' ('; 1355 BaseInsert := BaseInsert + TargetDbAndObject + ' (';
1353 for i:=0 to Data.ColumnCount-1 do 1356 for i:=0 to Data.ColumnCount-1 do
1354 BaseInsert := BaseInsert + Quoter.QuoteIdent(Data.ColumnNames[i]) + ', '; 1357 BaseInsert := BaseInsert + Quoter.QuoteIdent(Data.ColumnNames[i]) + ', ';
1355 Delete(BaseInsert, Length(BaseInsert)-1, 2); 1358 Delete(BaseInsert, Length(BaseInsert)-1, 2);
1356 BaseInsert := BaseInsert + ') VALUES'+CRLF+#9+'('; 1359 BaseInsert := BaseInsert + ') VALUES'+CRLF+#9+'(';
1357 while true do begin 1360 while true do begin
1358 Output(BaseInsert, False, True, True, True, True); 1361 Output(BaseInsert, False, True, True, True, True);
1359 IsFirstRowInChunk := True; 1362 IsFirstRowInChunk := True;
1360 1363
1361 while not Data.Eof do begin 1364 while not Data.Eof do begin
1362 Row := ''; 1365 Row := '';
1363 if not IsFirstRowInChunk then 1366 if not IsFirstRowInChunk then
1364 Row := Row + ','+CRLF+#9+'('; 1367 Row := Row + ','+CRLF+#9+'(';
1365 for i:=0 to Data.ColumnCount-1 do begin 1368 for i:=0 to Data.ColumnCount-1 do begin
1366 if Data.IsNull(i) then 1369 if Data.IsNull(i) then
1367 Row := Row + 'NULL' 1370 Row := Row + 'NULL'
1368 else case Data.DataType(i).Category of 1371 else case Data.DataType(i).Category of
1369 dtcInteger, dtcReal: begin 1372 dtcInteger, dtcReal: begin
1370 if Data.DataType(i).Index = dtBit then 1373 if Data.DataType(i).Index = dtBit then
1371 Row := Row + 'b' + Quoter.EscapeString(Data.Col(i)) 1374 Row := Row + 'b' + Quoter.EscapeString(Data.Col(i))
1372 else 1375 else
1373 Row := Row + Data.Col(i); 1376 Row := Row + Data.Col(i);
1374 end; 1377 end;
1375 dtcBinary, dtcSpatial: begin 1378 dtcBinary, dtcSpatial: begin
1376 BinContent := Data.HexValue(i); 1379 BinContent := Data.HexValue(i);
1377 if Length(BinContent) > 0 then 1380 if Length(BinContent) > 0 then
1378 Row := Row + '_binary ' + BinContent 1381 Row := Row + '_binary ' + BinContent
1379 else 1382 else
1380 Row := Row + esc(''); 1383 Row := Row + esc('');
1381 end; 1384 end;
1382 else Row := Row + esc(Data.Col(i)); 1385 else Row := Row + esc(Data.Col(i));
1383 end; 1386 end;
1384 if i<Data.ColumnCount-1 then 1387 if i<Data.ColumnCount-1 then
1385 Row := Row + ', '; 1388 Row := Row + ', ';
1386 end; 1389 end;
1387 Row := Row + ')'; 1390 Row := Row + ')';
1388 // Break if stream would increase over the barrier of 1MB, and throw away current row 1391 // Break if stream would increase over the barrier of 1MB, and throw away current row
1389 if (not IsFirstRowInChunk) 1392 if (not IsFirstRowInChunk)
1390 and (ExportStream.Size - ExportStreamStartOfQueryPos + Length(Row) > SIZE_MB*0.9) 1393 and (ExportStream.Size - ExportStreamStartOfQueryPos + Length(Row) > SIZE_MB*0.9)
1391 then 1394 then
1392 break; 1395 break;
1393 Inc(RowCount); 1396 Inc(RowCount);
1394 IsFirstRowInChunk := False; 1397 IsFirstRowInChunk := False;
1395 Output(Row, False, True, True, True, True); 1398 Output(Row, False, True, True, True, True);
1396 Data.Next; 1399 Data.Next;
1397 end; 1400 end;
1398 Output('', True, True, True, True, True); 1401 Output('', True, True, True, True, True);
1399 LogStatistic(RowCount); 1402 LogStatistic(RowCount);
1400 if Data.Eof then 1403 if Data.Eof then
1401 break; 1404 break;
1402 1405
1403 end; 1406 end;
1404 ResultCount := Data.RecordCount; 1407 ResultCount := Data.RecordCount;
1405 FreeAndNil(Data); 1408 FreeAndNil(Data);
1406 // Break if end of table data, avoid one last empty/useless SELECT in next loop 1409 // Break if end of table data, avoid one last empty/useless SELECT in next loop
1407 if ResultCount < Limit then 1410 if ResultCount < Limit then
1408 break; 1411 break;
1409 1412
1410 end; 1413 end;
1411 Output('/*!40000 ALTER TABLE '+TargetDbAndObject+' ENABLE KEYS */', True, True, True, True, True); 1414 Output('/*!40000 ALTER TABLE '+TargetDbAndObject+' ENABLE KEYS */', True, True, True, True, True);
1412 // Cosmetic fix for estimated InnoDB row count 1415 // Cosmetic fix for estimated InnoDB row count
1413 DBObj.Rows := RowCount; 1416 DBObj.Rows := RowCount;
1414 LogStatistic(RowCount); 1417 LogStatistic(RowCount);
1415 end; 1418 end;
1416 end; 1419 end;
1417 1420
1418 // Add footer in directory mode after each item 1421 // Add footer in directory mode after each item
1419 Output(EXPORT_FILE_FOOTER, False, False, True, False, False); 1422 Output(EXPORT_FILE_FOOTER, False, False, True, False, False);
1420 1423
1421 ExportLastDatabase := FinalDbName; 1424 ExportLastDatabase := FinalDbName;
1422 end; 1425 end;
1423 1426
1424 1427
1425 procedure TfrmTableTools.chkBulkTableEditCheckComboClick(Sender: TObject); 1428 procedure TfrmTableTools.chkBulkTableEditCheckComboClick(Sender: TObject);
1426 var 1429 var
1427 chk: TCheckBox; 1430 chk: TCheckBox;
1428 combo: TWinControl; 1431 combo: TWinControl;
1429 begin 1432 begin
1430 chk := TCheckBox(Sender); 1433 chk := TCheckBox(Sender);
1431 if chk = chkBulkTableEditDatabase then combo := comboBulkTableEditDatabase 1434 if chk = chkBulkTableEditDatabase then combo := comboBulkTableEditDatabase
1432 else if chk = chkBulkTableEditEngine then combo := comboBulkTableEditEngine 1435 else if chk = chkBulkTableEditEngine then combo := comboBulkTableEditEngine
1433 else if chk = chkBulkTableEditCollation then combo := comboBulkTableEditCollation 1436 else if chk = chkBulkTableEditCollation then combo := comboBulkTableEditCollation
1434 else combo := comboBulkTableEditCharset; 1437 else combo := comboBulkTableEditCharset;
1435 combo.Enabled := chk.Checked; 1438 combo.Enabled := chk.Checked;
1436 ValidateControls(Sender); 1439 ValidateControls(Sender);
1437 end; 1440 end;
1438 1441
1439 1442
1440 procedure TfrmTableTools.DoBulkTableEdit(DBObj: TDBObject); 1443 procedure TfrmTableTools.DoBulkTableEdit(DBObj: TDBObject);
1441 var 1444 var
1442 Specs, LogRow: TStringList; 1445 Specs, LogRow: TStringList;
1443 CreateView: String; 1446 CreateView: String;
1444 rx: TRegExpr; 1447 rx: TRegExpr;
1445 HasCharsetClause: Boolean; 1448 HasCharsetClause: Boolean;
1446 begin 1449 begin
1447 AddResults('SELECT '+esc(DBObj.Database)+' AS '+DBObj.Connection.QuoteIdent('Database')+', ' + 1450 AddResults('SELECT '+esc(DBObj.Database)+' AS '+DBObj.Connection.QuoteIdent('Database')+', ' +
1448 esc(DBObj.Name)+' AS '+DBObj.Connection.QuoteIdent('Table')+', ' + 1451 esc(DBObj.Name)+' AS '+DBObj.Connection.QuoteIdent('Table')+', ' +
1449 esc('Updating...')+' AS '+DBObj.Connection.QuoteIdent('Operation')+', '+ 1452 esc('Updating...')+' AS '+DBObj.Connection.QuoteIdent('Operation')+', '+
1450 ''''' AS '+DBObj.Connection.QuoteIdent('Result') 1453 ''''' AS '+DBObj.Connection.QuoteIdent('Result')
1451 ); 1454 );
1452 Specs := TStringList.Create; 1455 Specs := TStringList.Create;
1453 if chkBulkTableEditDatabase.Checked and (comboBulkTableEditDatabase.Text <> DBObj.Database) then begin 1456 if chkBulkTableEditDatabase.Checked and (comboBulkTableEditDatabase.Text <> DBObj.Database) then begin
1454 case DBObj.NodeType of 1457 case DBObj.NodeType of
1455 lntTable: Specs.Add('RENAME ' + DBObj.Connection.QuoteIdent(comboBulkTableEditDatabase.Text)+'.'+DBObj.QuotedName); 1458 lntTable: Specs.Add('RENAME ' + DBObj.Connection.QuoteIdent(comboBulkTableEditDatabase.Text)+'.'+DBObj.QuotedName);
1456 lntView: begin 1459 lntView: begin
1457 // Although RENAME works for views, that does not work for moving to another database without getting 1460 // Although RENAME works for views, that does not work for moving to another database without getting
1458 // a "Changing schema from x to y is not allowed". Instead, recreate them manually 1461 // a "Changing schema from x to y is not allowed". Instead, recreate them manually
1459 CreateView := DBObj.CreateCode; 1462 CreateView := DBObj.CreateCode;
1460 rx := TRegExpr.Create; 1463 rx := TRegExpr.Create;
1461 rx.ModifierI := True; 1464 rx.ModifierI := True;
1462 // Replace old database references in VIEW body 1465 // Replace old database references in VIEW body
1463 rx.Expression := '(["`])'+QuoteRegExprMetaChars(DBObj.Database)+'(["`])'; 1466 rx.Expression := '(["`])'+QuoteRegExprMetaChars(DBObj.Database)+'(["`])';
1464 CreateView := rx.Replace(CreateView, DBObj.Connection.QuoteIdent(comboBulkTableEditDatabase.Text), false); 1467 CreateView := rx.Replace(CreateView, DBObj.Connection.QuoteIdent(comboBulkTableEditDatabase.Text), false);
1465 rx.Free; 1468 rx.Free;
1466 // Temporarily switch to new database for VIEW creation, so the database references are correct 1469 // Temporarily switch to new database for VIEW creation, so the database references are correct
1467 DBObj.Connection.Database := comboBulkTableEditDatabase.Text; 1470 DBObj.Connection.Database := comboBulkTableEditDatabase.Text;
1468 DBObj.Connection.Query(CreateView); 1471 DBObj.Connection.Query(CreateView);
1469 DBObj.Connection.Database := DBObj.Database; 1472 DBObj.Connection.Database := DBObj.Database;
1470 DBObj.Connection.Query('DROP VIEW '+DBObj.QuotedName); 1473 DBObj.Connection.Query('DROP VIEW '+DBObj.QuotedName);
1471 end; 1474 end;
1472 end; 1475 end;
1473 FModifiedDbs.Add(DBObj.Database); 1476 FModifiedDbs.Add(DBObj.Database);
1474 FModifiedDbs.Add(comboBulkTableEditDatabase.Text); 1477 FModifiedDbs.Add(comboBulkTableEditDatabase.Text);
1475 end; 1478 end;
1476 if (DBObj.NodeType = lntTable) and chkBulkTableEditEngine.Checked then begin 1479 if (DBObj.NodeType = lntTable) and chkBulkTableEditEngine.Checked then begin
1477 if MainForm.ActiveConnection.ServerVersionInt < 40018 then 1480 if MainForm.ActiveConnection.ServerVersionInt < 40018 then
1478 Specs.Add('TYPE '+comboBulkTableEditEngine.Text) 1481 Specs.Add('TYPE '+comboBulkTableEditEngine.Text)
1479 else 1482 else
1480 Specs.Add('ENGINE '+comboBulkTableEditEngine.Text); 1483 Specs.Add('ENGINE '+comboBulkTableEditEngine.Text);
1481 end; 1484 end;
1482 if DBObj.NodeType = lntTable then begin 1485 if DBObj.NodeType = lntTable then begin
1483 HasCharsetClause := False; 1486 HasCharsetClause := False;
1484 if chkBulkTableEditCharset.Checked and (comboBulkTableEditCharset.ItemIndex > -1) then begin 1487 if chkBulkTableEditCharset.Checked and (comboBulkTableEditCharset.ItemIndex > -1) then begin
1485 MainForm.ActiveConnection.CharsetTable.RecNo := comboBulkTableEditCharset.ItemIndex; 1488 MainForm.ActiveConnection.CharsetTable.RecNo := comboBulkTableEditCharset.ItemIndex;
1486 Specs.Add('CONVERT TO CHARSET '+DBObj.Connection.CharsetTable.Col('Charset')); 1489 Specs.Add('CONVERT TO CHARSET '+DBObj.Connection.CharsetTable.Col('Charset'));
1487 HasCharsetClause := True; 1490 HasCharsetClause := True;
1488 end; 1491 end;
1489 if chkBulkTableEditCollation.Checked and (comboBulkTableEditCollation.ItemIndex > -1) then begin 1492 if chkBulkTableEditCollation.Checked and (comboBulkTableEditCollation.ItemIndex > -1) then begin
1490 if HasCharsetClause then // No comma between charset + collation clause 1493 if HasCharsetClause then // No comma between charset + collation clause
1491 Specs[Specs.Count-1] := Specs[Specs.Count-1] + ' COLLATE '+esc(comboBulkTableEditCollation.Text) 1494 Specs[Specs.Count-1] := Specs[Specs.Count-1] + ' COLLATE '+esc(comboBulkTableEditCollation.Text)
1492 else 1495 else
1493 Specs.Add('COLLATE '+esc(comboBulkTableEditCollation.Text)); 1496 Specs.Add('COLLATE '+esc(comboBulkTableEditCollation.Text));
1494 end; 1497 end;
1495 if chkBulkTableEditResetAutoinc.Checked then 1498 if chkBulkTableEditResetAutoinc.Checked then
1496 Specs.Add('AUTO_INCREMENT=0'); 1499 Specs.Add('AUTO_INCREMENT=0');
1497 end; 1500 end;
1498 1501
1499 LogRow := FResults.Last; 1502 LogRow := FResults.Last;
1500 if Specs.Count > 0 then begin 1503 if Specs.Count > 0 then begin
1501 DBObj.Connection.Query('ALTER TABLE ' + DBObj.QuotedDatabase + '.' + DBObj.QuotedName + ' ' + ImplodeStr(', ', Specs)); 1504 DBObj.Connection.Query('ALTER TABLE ' + DBObj.QuotedDatabase + '.' + DBObj.QuotedName + ' ' + ImplodeStr(', ', Specs));
1502 LogRow[2] := 'Done'; 1505 LogRow[2] := 'Done';
1503 LogRow[3] := 'Success'; 1506 LogRow[3] := 'Success';
1504 end else begin 1507 end else begin
1505 LogRow[2] := 'Nothing to do'; 1508 LogRow[2] := 'Nothing to do';
1506 LogRow[3] := 'Selected operations cannot be applied to a '+LowerCase(DBObj.ObjType); 1509 LogRow[3] := 'Selected operations cannot be applied to a '+LowerCase(DBObj.ObjType);
1507 end; 1510 end;
1508 UpdateResultGrid; 1511 UpdateResultGrid;
1509 end; 1512 end;
1510 1513
1511 1514
1512 procedure TfrmTableTools.CheckAllClick(Sender: TObject); 1515 procedure TfrmTableTools.CheckAllClick(Sender: TObject);
1513 var 1516 var
1514 DBNode, ObjNode: PVirtualNode; 1517 DBNode, ObjNode: PVirtualNode;
1515 WantedType: TListNodeType; 1518 WantedType: TListNodeType;
1516 DBObj: PDBObject; 1519 DBObj: PDBObject;
1517 CheckNone: Boolean; 1520 CheckNone: Boolean;
1518 CheckedNodes: Int64; 1521 CheckedNodes: Int64;
1519 begin 1522 begin
1520 // Check all/none/by type via context menu 1523 // Check all/none/by type via context menu
1521 WantedType := TListNodeType((Sender as TMenuItem).Tag); 1524 WantedType := TListNodeType((Sender as TMenuItem).Tag);
1522 CheckNone := Sender = menuCheckNone; 1525 CheckNone := Sender = menuCheckNone;
1523 case TreeObjects.GetNodeLevel(TreeObjects.FocusedNode) of 1526 case TreeObjects.GetNodeLevel(TreeObjects.FocusedNode) of
1524 1: DBNode := TreeObjects.FocusedNode; 1527 1: DBNode := TreeObjects.FocusedNode;
1525 2: DBNode := TreeObjects.FocusedNode.Parent; 1528 2: DBNode := TreeObjects.FocusedNode.Parent;
1526 else raise Exception.Create('Unhandled tree level'); 1529 else raise Exception.Create('Unhandled tree level');
1527 end; 1530 end;
1528 ObjNode := TreeObjects.GetFirstChild(DBNode); 1531 ObjNode := TreeObjects.GetFirstChild(DBNode);
1529 CheckedNodes := 0; 1532 CheckedNodes := 0;
1530 while Assigned(ObjNode) do begin 1533 while Assigned(ObjNode) do begin
1531 DBObj := TreeObjects.GetNodeData(ObjNode); 1534 DBObj := TreeObjects.GetNodeData(ObjNode);
1532 if CheckNone then 1535 if CheckNone then
1533 TreeObjects.CheckState[ObjNode] := csUncheckedNormal 1536 TreeObjects.CheckState[ObjNode] := csUncheckedNormal
1534 else begin 1537 else begin
1535 if (WantedType = lntNone) or (DBObj.NodeType = WantedType) then 1538 if (WantedType = lntNone) or (DBObj.NodeType = WantedType) then
1536 TreeObjects.CheckState[ObjNode] := csCheckedNormal 1539 TreeObjects.CheckState[ObjNode] := csCheckedNormal
1537 else 1540 else
1538 TreeObjects.CheckState[ObjNode] := csUncheckedNormal; 1541 TreeObjects.CheckState[ObjNode] := csUncheckedNormal;
1539 end; 1542 end;
1540 if ObjNode.CheckState = csCheckedNormal then 1543 if ObjNode.CheckState = csCheckedNormal then
1541 Inc(CheckedNodes); 1544 Inc(CheckedNodes);
1542 TreeObjects.RepaintNode(ObjNode); 1545 TreeObjects.RepaintNode(ObjNode);
1543 ObjNode := TreeObjects.GetNextSibling(ObjNode); 1546 ObjNode := TreeObjects.GetNextSibling(ObjNode);
1544 end; 1547 end;
1545 if CheckedNodes = 0 then 1548 if CheckedNodes = 0 then
1546 TreeObjects.CheckState[DBNode] := csUncheckedNormal 1549 TreeObjects.CheckState[DBNode] := csUncheckedNormal
1547 else if CheckedNodes = TreeObjects.ChildCount[DBNode] then 1550 else if CheckedNodes = TreeObjects.ChildCount[DBNode] then
1548 TreeObjects.CheckState[DBNode] := csCheckedNormal 1551 TreeObjects.CheckState[DBNode] := csCheckedNormal
1549 else 1552 else
1550 TreeObjects.CheckState[DBNode] := csMixedNormal; 1553 TreeObjects.CheckState[DBNode] := csMixedNormal;
1551 end; 1554 end;
1552 1555
1553 1556
1554 end. 1557 end.
Powered by Google Project Hosting