feat: create SQL export option for wrapping DML commands in a BEGIN/COMMIT transaction

ansgarbecker · ansgarbecker · commit 5560454f1b9a · 2026-02-12T18:50:44.000+01:00
This also * removes the MyISAM-only ALTER TABLE .. DISABLE/ENABLE KEYS command, which in mid size tables costs more than it helps. * shows the number of checked export options on the button caption * saves settings when the user just closes the dialog, without having exported Closes #1262
diff --git a/source/apphelpers.pas b/source/apphelpers.pas
@@ -191,7 +191,7 @@   TWinControlHelper = class helper for TWinControl
     asSSLCert, asSSLCA, asSSLCipher, asSSLVerification, asSSLWarnUnused, asNetType, asCompressed, asLocalTimeZone, asQueryTimeout, asKeepAlive,
     asStartupScriptFilename, asDatabases, asComment, asDatabaseFilter, asTableFilter, asFilterVT, asExportSQLCreateDatabases,
     asExportSQLCreateTables, asExportSQLDataHow, asExportSQLDataInsertSize, asExportSQLFilenames, asExportZIPFilenames, asExportSQLDirectories,
-    asExportSQLDatabase, asExportSQLServerDatabase, asExportSQLOutput, asExportSQLAddComments, asExportSQLRemoveAutoIncrement, asExportSQLRemoveDefiner,
+    asExportSQLDatabase, asExportSQLServerDatabase, asExportSQLOutput, asExportSQLAddComments, asExportSQLTransactions, asExportSQLRemoveAutoIncrement, asExportSQLRemoveDefiner,
     asGridExportWindowWidth, asGridExportWindowHeight, asGridExportOutputCopy, asGridExportOutputFile,
     asGridExportFilename, asGridExportRecentFiles, asGridExportEncoding, asGridExportFormat, asGridExportSelection,
     asGridExportColumnNames, asGridExportIncludeAutoInc, asGridExportIncludeQuery, asGridExportRemoveLinebreaks, asGridExportOpenFile,
@@ -3682,6 +3682,7 @@ constructor TAppSettings.Create;
   InitSetting(asExportSQLServerDatabase,          'ExportSQL_ServerDatabase',              0, False, '');
   InitSetting(asExportSQLOutput,                  'ExportSQL_Output',                      0);
   InitSetting(asExportSQLAddComments,             'ExportSQLAddComments',                  0, True);
+  InitSetting(asExportSQLTransactions,            'ExportSQLTransactions',                 0, False);
   InitSetting(asExportSQLRemoveAutoIncrement,     'ExportSQLRemoveAutoIncrement',          0, False);
   InitSetting(asExportSQLRemoveDefiner,           'ExportSQLRemoveDefiner',                0, True);
   InitSetting(asGridExportWindowWidth,            'GridExportWindowWidth',                 450);
diff --git a/source/tabletools.lfm b/source/tabletools.lfm
@@ -1061,14 +1061,22 @@ object frmTableTools: TfrmTableTools
     object menuExportAddComments: TMenuItem
       AutoCheck = True
       Caption = 'Add comments'
+      OnClick = menuExportOptionClick
+    end
+    object menuExportTransactions: TMenuItem
+      AutoCheck = True
+      Caption = 'Wrap data DML in transactions'
+      OnClick = menuExportOptionClick
     end
     object menuExportRemoveAutoIncrement: TMenuItem
       AutoCheck = True
       Caption = 'Remove AUTO_INCREMENT clauses'
+      OnClick = menuExportOptionClick
     end
     object menuExportRemoveDefiner: TMenuItem
       AutoCheck = True
       Caption = 'Remove DEFINER clauses'
+      OnClick = menuExportOptionClick
     end
     object menuCopyMysqldumpCommand: TMenuItem
       Caption = 'Copy mysqldump command'
diff --git a/source/tabletools.pas b/source/tabletools.pas
@@ -103,6 +103,7 @@   TfrmTableTools = class(TExtForm)
     lblGenerateDataNullAmount: TLabel;
     editGenerateDataNullAmount: TEdit;
     menuInvertCheck: TMenuItem;
+    menuExportTransactions: TMenuItem;
     procedure FormCreate(Sender: TObject);
     procedure FormShow(Sender: TObject);
     procedure btnHelpMaintenanceClick(Sender: TObject);
@@ -128,7 +129,7 @@   TfrmTableTools = class(TExtForm)
     procedure ResultGridPaintText(Sender: TBaseVirtualTree; const TargetCanvas: TCanvas; Node: PVirtualNode;
       Column: TColumnIndex; TextType: TVSTTextType);
     procedure ValidateControls(Sender: TObject);
-    procedure SaveSettings(Sender: TObject);
+    procedure SaveSettings;
     procedure chkExportOptionClick(Sender: TObject);
     procedure btnExportOutputTargetSelectClick(Sender: TObject);
     procedure comboExportOutputTargetChange(Sender: TObject);
@@ -159,6 +160,7 @@   TfrmTableTools = class(TExtForm)
       Index: Integer; ARect: TRect; State: TOwnerDrawState);
     procedure comboExportOutputTypeMeasureItem(Control: TWinControl;
       Index: Integer; var AHeight: Integer);
+    procedure menuExportOptionClick(Sender: TObject);
   const
     StatusMsg = '%s %s ...';
   private
@@ -270,6 +272,7 @@ procedure TfrmTableTools.FormCreate(Sender: TObject);
   comboExportData.ItemIndex := AppSettings.ReadInt(asExportSQLDataHow);
   editInsertSize.Text := AppSettings.ReadInt(asExportSQLDataInsertSize).ToString;
   menuExportAddComments.Checked := AppSettings.ReadBool(asExportSQLAddComments);
+  menuExportTransactions.Checked := AppSettings.ReadBool(asExportSQLTransactions);
   menuExportRemoveAutoIncrement.Checked := AppSettings.ReadBool(asExportSQLRemoveAutoIncrement);
   menuExportRemoveDefiner.Checked := AppSettings.ReadBool(asExportSQLRemoveDefiner);
   // Add hardcoded output options and session names from registry
@@ -514,19 +517,34 @@ procedure TfrmTableTools.menuCopyMysqldumpCommandClick(Sender: TObject);
 end;
 
 
+procedure TfrmTableTools.menuExportOptionClick(Sender: TObject);
+var
+  i: Integer;
+  MenuItem: TMenuItem;
+begin
+  // Display number of checked options in button caption
+  i := 0;
+  for MenuItem in popupExportOptions.Items do begin
+    if MenuItem.Checked then
+      Inc(i);
+  end;
+  btnExportOptions.Caption := _('Options') + ' (' + i.ToString + ')';
+end;
+
 procedure TfrmTableTools.FormClose(Sender: TObject; var Action: TCloseAction);
 begin
   // Auto close temorary connection
   if Assigned(FTargetConnection) then
     FreeAndNil(FTargetConnection);
+  SaveSettings;
   // Save GUI setup
   AppSettings.WriteInt(asTableToolsWindowWidth, Width);
   AppSettings.WriteInt(asTableToolsWindowHeight, Height);
   AppSettings.WriteInt(asTableToolsTreeWidth, pnlLeft.Width);
 end;
 
 
-procedure TfrmTableTools.SaveSettings(Sender: TObject);
+procedure TfrmTableTools.SaveSettings;
 var
   i: Integer;
   Items: TStringList;
@@ -548,6 +566,7 @@ procedure TfrmTableTools.SaveSettings(Sender: TObject);
       if comboExportData.ItemIndex > 0 then
         AppSettings.WriteInt(asExportSQLDataInsertSize, StrToInt64Def(editInsertSize.Text, 0));
       AppSettings.WriteBool(asExportSQLAddComments, menuExportAddComments.Checked);
+      AppSettings.WriteBool(asExportSQLTransactions, menuExportTransactions.Checked);
       AppSettings.WriteBool(asExportSQLRemoveAutoIncrement, menuExportRemoveAutoIncrement.Checked);
       AppSettings.WriteBool(asExportSQLRemoveDefiner, menuExportRemoveDefiner.Checked);
 
@@ -604,6 +623,7 @@ procedure TfrmTableTools.ValidateControls(Sender: TObject);
   TExtForm.PageControlTabHighlight(tabsTools);
   btnSeeResults.Visible := tabsTools.ActivePage = tabFind;
   lblCheckedSize.Caption := f_('Selected objects size: %s', [FormatByteNumber(FObjectSizes)]);
+  menuExportOptionClick(Sender);
   if tabsTools.ActivePage = tabMaintenance then begin
     btnExecute.Caption := _('Execute');
     btnExecute.Enabled := (Pos(_(SUnsupported), comboOperation.Text) = 0) and SomeChecked;
@@ -1000,7 +1020,6 @@ procedure TfrmTableTools.Execute(Sender: TObject);
   tabsTools.Enabled := True;
   treeObjects.Enabled := True;
   ValidateControls(Sender);
-  SaveSettings(Sender);
   Screen.Cursor := crDefault;
 end;
 
@@ -2013,6 +2032,8 @@ procedure TfrmTableTools.DoExport(DBObj: TDBObject);
         tmp := '~'+tmp+' ('+_('approximately')+')';
       if menuExportAddComments.Checked then
         Output('-- '+f_('Dumping data for table %s.%s: %s', [DBObj.Database, DBObj.Name, tmp])+CRLF, False, True, True, False, False);
+      if menuExportTransactions.Checked then
+        Output('BEGIN', True, True, True, True, True);
       TargetDbAndObject := Quoter.QuoteIdent(DBObj.Name);
       if ToDb then
         TargetDbAndObject := Quoter.QuoteIdent(FinalDbName) + '.' + TargetDbAndObject;
@@ -2035,9 +2056,6 @@ procedure TfrmTableTools.DoExport(DBObj: TDBObject);
       Limit := Round(100 * SIZE_MB / IfThen(DBObj.AvgRowLen>0, DBObj.AvgRowLen, AssumedAvgRowLen));
       if comboExportData.Text = DATA_REPLACE then
         Output('DELETE FROM '+TargetDbAndObject, True, True, True, True, True);
-      if DBObj.Engine.ToLowerInvariant <> 'innodb' then begin
-        Output('/*!40000 ALTER TABLE '+TargetDbAndObject+' DISABLE KEYS */', True, True, True, True, True);
-      end;
       while true do begin
         Data := DBObj.Connection.GetResults(
           DBObj.Connection.ApplyLimitClause(
@@ -2115,9 +2133,8 @@ procedure TfrmTableTools.DoExport(DBObj: TDBObject);
           break;
 
       end;
-      if DBObj.Engine.ToLowerInvariant <> 'innodb' then begin
-        Output('/*!40000 ALTER TABLE '+TargetDbAndObject+' ENABLE KEYS */', True, True, True, True, True);
-      end;
+      if menuExportTransactions.Checked then
+        Output('COMMIT', True, True, True, True, True);
       Output(CRLF, False, True, True, True, True);
       // Cosmetic fix for estimated InnoDB row count
       DBObj.Rows := RowCount;
