unit table_editor;

interface

uses

Winapi.Windows, System.SysUtils, System.Classes, Vcl.Graphics, Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls,

Vcl.ComCtrls, Vcl.ToolWin, VirtualTrees, VirtualTrees.Types, SynRegExpr, Winapi.ActiveX, Vcl.ExtCtrls, SynEdit,

SynMemo, Vcl.Menus, Vcl.Clipbrd, System.Math, System.UITypes, System.Generics.Collections,

grideditlinks, dbstructures, dbstructures.mysql, dbconnection, apphelpers, gnugettext, System.StrUtils, extra_controls;

type

TFrame = TDBObjectEditor;

TfrmTableEditor = class(TFrame)

btnSave: TButton;

btnDiscard: TButton;

btnHelp: TButton;

listColumns: TVirtualStringTree;

PageControlMain: TPageControl;

tabBasic: TTabSheet;

tabIndexes: TTabSheet;

tabOptions: TTabSheet;

lblName: TLabel;

editName: TEdit;

memoComment: TMemo;

lblComment: TLabel;

lblAutoinc: TLabel;

lblAvgRowLen: TLabel;

lblInsertMethod: TLabel;

lblUnion: TLabel;

lblMaxRows: TLabel;

lblRowFormat: TLabel;

editAutoInc: TEdit;

editAvgRowLen: TEdit;

editMaxRows: TEdit;

chkChecksum: TCheckBox;

comboRowFormat: TComboBox;

memoUnionTables: TMemo;

comboInsertMethod: TComboBox;

lblCollation: TLabel;

comboCollation: TComboBox;

lblEngine: TLabel;

comboEngine: TComboBox;

treeIndexes: TVirtualStringTree;

tlbIndexes: TToolBar;

btnAddIndex: TToolButton;

btnRemoveIndex: TToolButton;

btnClearIndexes: TToolButton;

btnMoveUpIndex: TToolButton;

btnMoveDownIndex: TToolButton;

pnlColumnsTop: TPanel;

tlbColumns: TToolBar;

btnAddColumn: TToolButton;

btnRemoveColumn: TToolButton;

btnMoveUpColumn: TToolButton;

btnMoveDownColumn: TToolButton;

SplitterTopBottom: TSplitter;

tabCREATEcode: TTabSheet;

tabALTERCode: TTabSheet;

SynMemoCREATEcode: TSynMemo;

SynMemoALTERcode: TSynMemo;

popupProperties: TPopupMenu;

menuAddProperty: TMenuItem;

menuAddIndexColumn: TMenuItem;

menuRemoveProperty: TMenuItem;

menuMoveUpIndex: TMenuItem;

menuMoveDownIndex: TMenuItem;

menuClearProperties: TMenuItem;

popupColumns: TPopupMenu;

menuAddColumn: TMenuItem;

menuRemoveColumn: TMenuItem;

menuMoveUpColumn: TMenuItem;

menuMoveDownColumn: TMenuItem;

chkCharsetConvert: TCheckBox;

N1: TMenuItem;

menuCreateIndex: TMenuItem;

menuAddToIndex: TMenuItem;

tabForeignKeys: TTabSheet;

tlbForeignKeys: TToolBar;

btnAddForeignKey: TToolButton;

btnRemoveForeignKey: TToolButton;

btnClearForeignKeys: TToolButton;

menuCopyColumnCell: TMenuItem;

N2: TMenuItem;

listForeignKeys: TVirtualStringTree;

menuCopyColumns: TMenuItem;

menuPasteColumns: TMenuItem;

tabPartitions: TTabSheet;

SynMemoPartitions: TSynMemo;

tabCheckConstraints: TTabSheet;

tlbCheckConstraints: TToolBar;

btnAddCheckConstraint: TToolButton;

btnRemoveCheckConstraint: TToolButton;

btnClearCheckConstraints: TToolButton;

listCheckConstraints: TVirtualStringTree;

Copy1: TMenuItem;

procedure Modification(Sender: TObject);

procedure btnAddColumnClick(Sender: TObject);

procedure btnRemoveColumnClick(Sender: TObject);

procedure listColumnsFocusChanged(Sender: TBaseVirtualTree; Node: PVirtualNode; Column: TColumnIndex);

procedure btnHelpClick(Sender: TObject);

procedure listColumnsGetText(Sender: TBaseVirtualTree; Node: PVirtualNode;

Column: TColumnIndex; TextType: TVSTTextType; var CellText: String);

procedure listColumnsEditing(Sender: TBaseVirtualTree; Node: PVirtualNode; Column: TColumnIndex; var Allowed: Boolean);

procedure listColumnsNewText(Sender: TBaseVirtualTree; Node: PVirtualNode; Column: TColumnIndex; NewText: String);

procedure btnMoveUpColumnClick(Sender: TObject);

procedure btnMoveDownColumnClick(Sender: TObject);

procedure listColumnsDragOver(Sender: TBaseVirtualTree; Source: TObject; Shift: TShiftState; State: TDragState;

Pt: TPoint; Mode: TDropMode; var Effect: Integer; var Accept: Boolean);

procedure listColumnsDragDrop(Sender: TBaseVirtualTree; Source: TObject; DataObject: IDataObject; Formats: TFormatArray;

Shift: TShiftState; Pt: TPoint; var Effect: Integer; Mode: TDropMode);

procedure listColumnsPaintText(Sender: TBaseVirtualTree; const TargetCanvas: TCanvas; Node: PVirtualNode;

Column: TColumnIndex; TextType: TVSTTextType);

procedure listColumnsCreateEditor(Sender: TBaseVirtualTree; Node: PVirtualNode; Column: TColumnIndex; out EditLink: IVTEditLink);

procedure treeIndexesInitNode(Sender: TBaseVirtualTree; ParentNode, Node: PVirtualNode; var InitialStates: TVirtualNodeInitStates);

procedure treeIndexesGetText(Sender: TBaseVirtualTree; Node: PVirtualNode; Column: TColumnIndex; TextType: TVSTTextType;

var CellText: String);

procedure treeIndexesBeforePaint(Sender: TBaseVirtualTree; TargetCanvas: TCanvas);

procedure treeIndexesInitChildren(Sender: TBaseVirtualTree; Node: PVirtualNode; var ChildCount: Cardinal);

procedure treeIndexesGetImageIndex(Sender: TBaseVirtualTree; Node: PVirtualNode; Kind: TVTImageKind; Column: TColumnIndex;

var Ghosted: Boolean; var ImageIndex: TImageIndex);

procedure btnClearIndexesClick(Sender: TObject);

procedure btnSaveClick(Sender: TObject);

procedure editNumEditChange(Sender: TObject);

procedure comboEngineSelect(Sender: TObject);

procedure listColumnsClick(Sender: TObject);

procedure listColumnsBeforeCellPaint(Sender: TBaseVirtualTree;

TargetCanvas: TCanvas; Node: PVirtualNode; Column: TColumnIndex;

CellPaintMode: TVTCellPaintMode; CellRect: TRect; var ContentRect: TRect);

procedure listColumnsAfterCellPaint(Sender: TBaseVirtualTree;

TargetCanvas: TCanvas; Node: PVirtualNode; Column: TColumnIndex;

CellRect: TRect);

procedure btnAddIndexClick(Sender: TObject);

procedure treeIndexesDragOver(Sender: TBaseVirtualTree; Source: TObject;

Shift: TShiftState; State: TDragState; Pt: TPoint; Mode: TDropMode;

var Effect: Integer; var Accept: Boolean);

procedure treeIndexesDragDrop(Sender: TBaseVirtualTree; Source: TObject;

DataObject: IDataObject; Formats: TFormatArray; Shift: TShiftState;

Pt: TPoint; var Effect: Integer; Mode: TDropMode);

procedure treeIndexesNewText(Sender: TBaseVirtualTree; Node: PVirtualNode; Column: TColumnIndex; NewText: String);

procedure treeIndexesEditing(Sender: TBaseVirtualTree; Node: PVirtualNode; Column: TColumnIndex; var Allowed: Boolean);

procedure treeIndexesFocusChanged(Sender: TBaseVirtualTree; Node: PVirtualNode; Column: TColumnIndex);

procedure treeIndexesCreateEditor(Sender: TBaseVirtualTree; Node: PVirtualNode; Column: TColumnIndex; out EditLink: IVTEditLink);

procedure btnMoveUpIndexClick(Sender: TObject);

procedure btnMoveDownIndexClick(Sender: TObject);

procedure btnRemoveIndexClick(Sender: TObject);

procedure menuAddIndexColumnClick(Sender: TObject);

procedure PageControlMainChange(Sender: TObject);

procedure chkCharsetConvertClick(Sender: TObject);

procedure AnyTreeClick(Sender: TObject);

procedure btnDiscardClick(Sender: TObject);

procedure popupColumnsPopup(Sender: TObject);

procedure AddIndexByColumn(Sender: TObject);

procedure listForeignKeysBeforePaint(Sender: TBaseVirtualTree; TargetCanvas: TCanvas);

procedure listForeignKeysCreateEditor(Sender: TBaseVirtualTree;

Node: PVirtualNode; Column: TColumnIndex; out EditLink: IVTEditLink);

procedure listForeignKeysFocusChanged(Sender: TBaseVirtualTree;

Node: PVirtualNode; Column: TColumnIndex);

procedure listForeignKeysGetImageIndex(Sender: TBaseVirtualTree;

Node: PVirtualNode; Kind: TVTImageKind; Column: TColumnIndex;

var Ghosted: Boolean; var ImageIndex: TImageIndex);

procedure listForeignKeysGetText(Sender: TBaseVirtualTree;

Node: PVirtualNode; Column: TColumnIndex; TextType: TVSTTextType;

var CellText: String);

procedure listForeignKeysNewText(Sender: TBaseVirtualTree;

Node: PVirtualNode; Column: TColumnIndex; NewText: String);

procedure btnClearForeignKeysClick(Sender: TObject);

procedure btnAddForeignKeyClick(Sender: TObject);

procedure btnRemoveForeignKeyClick(Sender: TObject);

procedure listForeignKeysEditing(Sender: TBaseVirtualTree; Node: PVirtualNode; Column: TColumnIndex;

var Allowed: Boolean);

procedure listColumnsInitNode(Sender: TBaseVirtualTree; ParentNode, Node: PVirtualNode;

var InitialStates: TVirtualNodeInitStates);

procedure listColumnsGetNodeDataSize(Sender: TBaseVirtualTree; var NodeDataSize: Integer);

procedure listColumnsNodeMoved(Sender: TBaseVirtualTree; Node: PVirtualNode);

procedure listColumnsKeyPress(Sender: TObject; var Key: Char);

procedure vtHandleClickOrKeyPress(Sender: TVirtualStringTree;

Node: PVirtualNode; Column: TColumnIndex; HitPositions: THitPositions);

procedure menuCopyColumnsClick(Sender: TObject);

procedure menuPasteColumnsClick(Sender: TObject);

procedure listColumnsChange(Sender: TBaseVirtualTree; Node: PVirtualNode);

procedure AnyTreeStructureChange(Sender: TBaseVirtualTree;

Node: PVirtualNode; Reason: TChangeReason);

procedure listCheckConstraintsBeforePaint(Sender: TBaseVirtualTree;

TargetCanvas: TCanvas);

procedure btnAddCheckConstraintClick(Sender: TObject);

procedure listCheckConstraintsGetImageIndex(Sender: TBaseVirtualTree;

Node: PVirtualNode; Kind: TVTImageKind; Column: TColumnIndex;

var Ghosted: Boolean; var ImageIndex: TImageIndex);

procedure listCheckConstraintsFocusChanged(Sender: TBaseVirtualTree;

Node: PVirtualNode; Column: TColumnIndex);

procedure listCheckConstraintsGetText(Sender: TBaseVirtualTree;

Node: PVirtualNode; Column: TColumnIndex; TextType: TVSTTextType;

var CellText: string);

procedure listCheckConstraintsCreateEditor(Sender: TBaseVirtualTree;

Node: PVirtualNode; Column: TColumnIndex; out EditLink: IVTEditLink);

procedure listCheckConstraintsNewText(Sender: TBaseVirtualTree;

Node: PVirtualNode; Column: TColumnIndex; NewText: string);

procedure btnClearCheckConstraintsClick(Sender: TObject);

procedure btnRemoveCheckConstraintClick(Sender: TObject);

procedure popupPropertiesPopup(Sender: TObject);

procedure menuRemovePropertyClick(Sender: TObject);

procedure menuClearPropertiesClick(Sender: TObject);

procedure menuAddPropertyClick(Sender: TObject);

private

{ Private declarations }

FLoaded: Boolean;

CreateCodeValid, AlterCodeValid: Boolean;

FColumns: TTableColumnList;

FKeys: TTableKeyList;

FForeignKeys: TForeignKeyList;

FCheckConstraints: TCheckConstraintList;

FDeletedKeys,

FDeletedForeignKeys,

FDeletedCheckConstraints: TStringList;

FAlterRestrictedMessageDisplayed: Boolean;

procedure ValidateColumnControls;

procedure ValidateIndexControls;

procedure MoveFocusedIndexPart(NewIdx: Cardinal);

procedure ResetModificationFlags;

function ComposeCreateStatement: TSQLBatch;

function ComposeAlterStatement: TSQLBatch;

procedure UpdateSQLcode;

function CellEditingAllowed(Node: PVirtualNode; Column: TColumnIndex): Boolean;

function GetKeyImageIndexes(Col: TTableColumn): TList<Integer>;

procedure CalcMinColWidth;

procedure UpdateTabCaptions;

public

{ Public declarations }

constructor Create(AOwner: TComponent); override;

procedure Init(Obj: TDBObject); override;

function DeInit: TModalResult; override;

function ApplyModifications: TModalResult; override;

end;

implementation

uses main;

{$R *.dfm}

constructor TfrmTableEditor.Create(AOwner: TComponent);

begin

inherited;

comboRowFormat.Items.CommaText := 'DEFAULT,DYNAMIC,FIXED,COMPRESSED,REDUNDANT,COMPACT';

comboInsertMethod.Items.CommaText := 'NO,FIRST,LAST';

FColumns := TTableColumnList.Create;

FKeys := TTableKeyList.Create;

FForeignKeys := TForeignKeyList.Create;

FDeletedKeys := TStringList.Create;

FDeletedForeignKeys := TStringList.Create;

FDeletedCheckConstraints := TStringList.Create;

FDeletedCheckConstraints.Duplicates := dupIgnore;

editName.MaxLength := NAME_LEN;

FAlterRestrictedMessageDisplayed := False;

btnSave.Hint := ShortCutToText(MainForm.actSaveSQL.ShortCut);

end;

procedure TfrmTableEditor.Init(Obj: TDBObject);

var

AttrName, AttrValue: String;

rx: TRegExpr;

begin

inherited;

FLoaded := False;

FixVT(listColumns);

FixVT(treeIndexes);

FixVT(listForeignKeys);

FixVT(listCheckConstraints);

// Try the best to auto fit various column widths, respecting a custom DPI setting and a pulldown arrow

listColumns.Header.Columns[2].Width := Mainform.Canvas.TextWidth('GEOMETRYCOLLECTION') + 6*listColumns.TextMargin;

listColumns.Header.Columns[7].Width := Mainform.Canvas.TextWidth('AUTO_INCREMENT') + 4*listColumns.TextMargin;

listColumns.Header.Columns[9].Width := Mainform.Canvas.TextWidth('macroman_general_ci') + 6*listColumns.TextMargin;

// Overide column widths by custom values

TExtForm.RestoreListSetup(listColumns);

TExtForm.RestoreListSetup(treeIndexes);

TExtForm.RestoreListSetup(listForeignKeys);

TExtForm.RestoreListSetup(listCheckConstraints);

comboEngine.Items := DBObject.Connection.TableEngines;

comboEngine.Items.Insert(0, '<'+_('Server default')+'>');

comboEngine.ItemIndex := 0;

comboCollation.Items := DBObject.Connection.CollationList;

if DBObject.Connection.Parameters.IsMariaDB then begin

with listColumns.Header do begin

Columns[10].Options := Columns[10].Options + [coVisible];

Columns[11].Options := Columns[11].Options + [coVisible];

end;

end;

listColumns.BeginUpdate;

listColumns.Clear;

treeIndexes.Clear;

listForeignKeys.Clear;

listCheckConstraints.Clear;

tabALTERcode.TabVisible := ObjectExists;

// Clear value editors

memoComment.Text := '';

if Obj.Connection.ServerVersionInt < 50503 then

memoComment.MaxLength := 60

else

memoComment.MaxLength := 2048;

editAutoInc.Text := '';

editAvgRowLen.Text := '';

editMaxRows.Text := '';

chkChecksum.Checked := False;

comboRowFormat.ItemIndex := 0;

comboCollation.ItemIndex := -1;

memoUnionTables.Clear;

comboInsertMethod.ItemIndex := -1;

SynMemoPartitions.Clear;

if not ObjectExists then begin

// Creating new table

editName.Text := '';

if DBObject.Connection.Parameters.IsAnyMySQL then

comboCollation.ItemIndex := comboCollation.Items.IndexOf(DBObject.Connection.GetSessionVariable('collation_database'));

PageControlMain.ActivePage := tabBasic;

FColumns := TTableColumnList.Create;

FKeys := TTableKeyList.Create;

FForeignKeys := TForeignKeyList.Create;

FCheckConstraints := TCheckConstraintList.Create;

end else begin

// Editing existing table

editName.Text := DBObject.Name;

// Try collation from SHOW TABLE STATUS, sometimes missing in SHOW CREATE TABLE result

comboCollation.ItemIndex := comboCollation.Items.IndexOf(DBObject.Collation);

rx := TRegExpr.Create;

rx.ModifierI := True;

rx.Expression := '\s(\S+)\s*=\s*(\S+)';

if rx.Exec(DBObject.CreateCode) then while true do begin

AttrName := UpperCase(rx.Match[1]);

AttrValue := rx.Match[2];

if (AttrName='ENGINE') or (AttrName='TYPE') then

comboEngine.ItemIndex := comboEngine.Items.IndexOf(AttrValue)

else if AttrName='COLLATE' then

comboCollation.ItemIndex := comboCollation.Items.IndexOf(AttrValue)

else if AttrName='AVG_ROW_LENGTH' then

editAvgRowLen.Text := AttrValue

else if AttrName='AUTO_INCREMENT' then

editAutoInc.Text := AttrValue

else if AttrName='ROW_FORMAT' then

comboRowFormat.ItemIndex := comboRowFormat.Items.IndexOf(AttrValue)

else if AttrName='CHECKSUM' then

chkChecksum.Checked := AttrValue='1'

else if AttrName='MAX_ROWS' then

editMaxRows.Text := AttrValue

else if AttrName='INSERT_METHOD' then

comboInsertMethod.ItemIndex := comboInsertMethod.Items.IndexOf(AttrValue);

if not rx.ExecNext then

break;

end;

rx.Expression := '\bUNION=\((.+)\)';

if rx.Exec(DBObject.CreateCode) then

memoUnionTables.Lines.Text := rx.Match[1]

else

memoUnionTables.Lines.Clear;

// Prefer to take comment from SHOW TABLE STATUS result, to support single quotes without including some create option

// See issue #196

memoComment.Text := DBObject.Comment;

rx.Expression := '\b(PARTITION\s+BY\s+.+)$';

if rx.Exec(DBObject.CreateCode) then begin

SynMemoPartitions.Text := Trim(rx.Match[1]);

SynMemoPartitions.Text := ReplaceRegExpr('\*/$', SynMemoPartitions.Text, '');

end else

SynMemoPartitions.Clear;

FColumns := DBObject.TableColumns;

FKeys := DBObject.TableKeys;

FForeignKeys := DBObject.TableForeignKeys;

FCheckConstraints := DBObject.TableCheckConstraints;

end;

listColumns.RootNodeCount := FColumns.Count;

DeInitializeVTNodes(listColumns);

listColumns.EndUpdate;

// Init all nodes, so they keep their FColumn data after click on Remove button, see #245

listColumns.ValidateNode(nil, True);

// Set root nodes per BeforePaint event:

treeIndexes.Invalidate;

listForeignKeys.Invalidate;

listCheckConstraints.Invalidate;

// Validate controls

comboEngineSelect(comboEngine);

ValidateColumnControls;

ValidateIndexControls;

ResetModificationFlags;

CreateCodeValid := False;

AlterCodeValid := False;

PageControlMainChange(Self); // Foreign key editor needs a hit

// Buttons are randomly moved, since VirtualTree update, see #440

btnSave.Top := Height - btnSave.Height - 3;

btnHelp.Top := btnSave.Top;

btnDiscard.Top := btnSave.Top;

UpdateSQLCode;

UpdateTabCaptions;

CalcMinColWidth;

// Indicate change mechanisms can call their events now. See Modification().

FLoaded := True;

// Empty status panel

Mainform.ShowStatusMsg;

Screen.Cursor := crDefault;

end;

function TfrmTableEditor.DeInit: TModalResult;

begin

// Store GUI setup

TExtForm.SaveListSetup(listColumns);

TExtForm.SaveListSetup(treeIndexes);

TExtForm.SaveListSetup(listForeignKeys);

TExtForm.SaveListSetup(listCheckConstraints);

Result := inherited;

end;

procedure TfrmTableEditor.btnDiscardClick(Sender: TObject);

begin

// Reinit GUI, discarding changes

Modified := False;

Init(DBObject);

end;

procedure TfrmTableEditor.btnSaveClick(Sender: TObject);

begin

// Save changes, and make it impossible to (accidentally) click the save button twice

btnSave.Enabled := False;

btnSave.Repaint;

if ApplyModifications = mrOK then begin

// Initialize all edit fields with fresh result from SHOW TABLE STATUS row

Init(MainForm.ActiveDbObj)

end else begin

// Re-enable save button when something went wrong

btnSave.Enabled := True;

end;

end;

function TfrmTableEditor.ApplyModifications: TModalResult;

var

Batch: TSQLBatch;

Query: TSQLSentence;

i: Integer;

Rename: String;

begin

// Check if all indexes have at least one column

// If not, exit early

for i := 0 to FKeys.Count-1 do begin

if FKeys.Items[i].Columns.Count = 0 then begin

ErrorDialog( f_('%s Index "%s" does not contain any column. You can add columns using drag''n drop from the columns list.', [FKeys.Items[i].IndexType, FKeys.Items[i].Name]));

Result := mrAbort;

Exit;

end;

end;

// Create or alter table

Result := mrOk;

if not ObjectExists then

Batch := ComposeCreateStatement

else

Batch := ComposeAlterStatement;

try

for Query in Batch do

DBObject.Connection.Query(Query.SQL);

// Rename table

if ObjectExists and (editName.Text <> DBObject.Name) then begin

Rename := DBObject.Connection.GetSQLSpecifity(spRenameTable, [DBObject.QuotedName, DBObject.Connection.QuoteIdent(editName.Text)]);

DBObject.Connection.Query(Rename);

end;

tabALTERcode.TabVisible := ObjectExists;

if chkCharsetConvert.Checked then begin

// Autoadjust column collations

for i:=0 to FColumns.Count-1 do begin

if FColumns[i].Collation <> '' then

FColumns[i].Collation := comboCollation.Text;

end;

end;

// Set table name for altering if Apply was clicked

DBObject.Name := editName.Text;

DBObject.UnloadDetails;

tabALTERcode.TabVisible := ObjectExists;

Mainform.UpdateEditorTab;

MainForm.tabData.TabVisible := True;

Mainform.RefreshTree(DBObject);

Mainform.RefreshHelperNode(TQueryTab.HelperNodeColumns);

ResetModificationFlags;

AlterCodeValid := False;

CreateCodeValid := False;

except

on E:EDbError do begin

ErrorDialog(E.Message);

Result := mrAbort;

end;

end;

end;

procedure TfrmTableEditor.ResetModificationFlags;

var

i: Integer;

begin

// Enable converting data for an existing table

chkCharsetConvertClick(comboCollation);

// Assist the user in auto unchecking this checkbox so data doesn't get converted more than once accidently

chkCharsetConvert.Checked := False;

// Reset modification flags of TEdits and TMemos

for i:=0 to ComponentCount-1 do

Components[i].Tag := 0;

// Reset column changes

for i:=FColumns.Count-1 downto 0 do begin

if FColumns[i].Status = esDeleted then

FColumns.Delete(i)

else begin

FColumns[i].OldName := FColumns[i].Name;

FColumns[i].Status := esUntouched;

end;

end;

FDeletedKeys.Clear;

for i:=0 to FKeys.Count-1 do begin

FKeys[i].OldName := FKeys[i].Name;

FKeys[i].OldIndexType := FKeys[i].IndexType;

FKeys[i].Added := False;

FKeys[i].Modified := False;

end;

FDeletedForeignKeys.Clear;

for i:=0 to FForeignKeys.Count-1 do begin

FForeignKeys[i].OldKeyName := FForeignKeys[i].KeyName;

FForeignKeys[i].Added := False;

FForeignKeys[i].Modified := False;

end;

FDeletedCheckConstraints.Clear;

for i:=0 to FCheckConstraints.Count-1 do begin

FCheckConstraints[i].Added := False;

FCheckConstraints[i].Modified := False;

end;

Modified := False;

btnSave.Enabled := Modified;

btnDiscard.Enabled := Modified;

end;

function TfrmTableEditor.ComposeAlterStatement: TSQLBatch;

var

Specs: TStringList;

ColSpec, IndexSQL, SQL, OverrideCollation,

AlterColBase, AddColBase: String;

i: Integer;

Results: TDBQuery;

Col, PreviousCol: PTableColumn;

Constraint: TCheckConstraint;

Node: PVirtualNode;

Conn: TDBConnection;

procedure FinishSpecs;

begin

if Specs.Count > 0 then begin

SQL := SQL + Trim('ALTER TABLE '+DBObject.QuotedName + CRLF + #9 + Implode(',' + CRLF + #9, Specs)) + ';' + CRLF;

Specs.Clear;

end;

end;

procedure AddQuery(Query: String);

begin

FinishSpecs;

SQL := SQL + Format(Query, [DBObject.QuotedName]) + ';' + CRLF;

end;

begin

// Compose ALTER query, called by buttons and for SQL code tab

Mainform.ShowStatusMsg(_('Composing ALTER statement ...'));

Screen.Cursor := crHourglass;

Specs := TStringList.Create;

SQL := '';

Conn := DBObject.Connection;

// Special case for altered foreign keys: These have to be dropped in a seperate query

// otherwise the server would return error 121 "Duplicate key on write or update"

// See also http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html :

// "You cannot add a foreign key and drop a foreign key in separate clauses of a single

// ALTER TABLE statement. Separate statements are required."

for i:=0 to FForeignKeys.Count-1 do begin

if FForeignKeys[i].Modified and (not FForeignKeys[i].Added) then

Specs.Add('DROP FOREIGN KEY '+Conn.QuoteIdent(FForeignKeys[i].OldKeyName));

end;

FinishSpecs;

// Special case for removed default values on columns, which can neither be done in

// ALTER TABLE ... CHANGE COLUMN query, as there is no "no default" clause, nor by

// appending an ALTER COLUMN ... DROP DEFAULT, without getting an "unknown column" error.

// Also, do this after the data type was altered, if from TEXT > VARCHAR e.g.

for i:=0 to FColumns.Count-1 do begin

if (Conn.Parameters.IsAnyMySQL or Conn.Parameters.IsAnyPostgreSQL)

and (FColumns[i].Status = esModified)

and (FColumns[i].DefaultType = cdtNothing)

and (FColumns[i].OldDataType.HasDefault)

then

Specs.Add('ALTER '+Conn.QuoteIdent(FColumns[i].OldName)+' DROP DEFAULT');

end;

FinishSpecs;

if memoComment.Tag = MODIFIEDFLAG then begin

case Conn.Parameters.NetTypeGroup of

ngMySQL, ngMSSQL: begin

Specs.Add('COMMENT=' + Conn.EscapeString(memoComment.Text));

end;

ngPgSQL: begin

AddQuery('COMMENT ON TABLE '+DBObject.QuotedName+' IS '+Conn.EscapeString(memoComment.Text));

end;

end;

end;

if (comboCollation.Tag = MODIFIEDFLAG) or (chkCharsetConvert.Checked) then

Specs.Add('COLLATE=' + Conn.EscapeString(comboCollation.Text));

if (comboEngine.Tag = MODIFIEDFLAG) and (comboEngine.ItemIndex > 0) then begin

if Conn.ServerVersionInt < 40018 then

Specs.Add('TYPE=' + comboEngine.Text)

else

Specs.Add('ENGINE=' + comboEngine.Text);

end;

if comboRowFormat.Tag = MODIFIEDFLAG then

Specs.Add('ROW_FORMAT=' + comboRowFormat.Text);

if chkChecksum.Tag = MODIFIEDFLAG then

Specs.Add('CHECKSUM=' + IntToStr(Integer(chkChecksum.Checked)));

if editAutoInc.Tag = MODIFIEDFLAG then

Specs.Add('AUTO_INCREMENT=' + IntToStr(MakeInt(editAutoInc.Text)));

if editAvgRowLen.Tag = MODIFIEDFLAG then

Specs.Add('AVG_ROW_LENGTH=' + IntToStr(MakeInt(editAvgRowLen.Text)));

if editMaxRows.Tag = MODIFIEDFLAG then

Specs.Add('MAX_ROWS=' + IntToStr(MakeInt(editMaxRows.Text)));

if memoUnionTables.Enabled and (memoUnionTables.Tag = MODIFIEDFLAG) and (memoUnionTables.Text <> '') then

Specs.Add('UNION=('+memoUnionTables.Text+')');

if comboInsertMethod.Enabled and (comboInsertMethod.Tag = MODIFIEDFLAG) and (comboInsertMethod.Text <> '') then

Specs.Add('INSERT_METHOD='+comboInsertMethod.Text);

if chkCharsetConvert.Checked then begin

Results := Conn.CollationTable;

if Assigned(Results) then while not Results.Eof do begin

if Results.Col('Collation') = comboCollation.Text then begin

Specs.Add('CONVERT TO CHARSET '+Results.Col('Charset'));

break;

end;

Results.Next;

end;

end;

// Update columns

Node := listColumns.GetFirst;

PreviousCol := nil;

while Assigned(Node) do begin

Col := listColumns.GetNodeData(Node);

if Col.Status <> esUntouched then begin

OverrideCollation := IfThen(chkCharsetConvert.Checked, comboCollation.Text);

AlterColBase := Conn.GetSQLSpecifity(spChangeColumn);

AddColBase := Conn.GetSQLSpecifity(spAddColumn);

case Conn.Parameters.NetTypeGroup of

ngMySQL: begin

ColSpec := Col.SQLCode(OverrideCollation);

// Server version requirement, see http://dev.mysql.com/doc/refman/4.1/en/alter-table.html

if Conn.ServerVersionInt >= 40001 then begin

if PreviousCol = nil then

ColSpec := ColSpec + ' FIRST'

else

ColSpec := ColSpec + ' AFTER '+Conn.QuoteIdent(PreviousCol.Name);

end;

case Col.Status of

esModified: begin

Specs.Add(Format(AlterColBase, [Conn.QuoteIdent(Col.OldName), ColSpec]));

end;

esAddedUntouched, esAddedModified: begin

Specs.Add(Format(AddColBase, [ColSpec]));

end;

end;

end;

ngMSSQL: begin

ColSpec := Col.SQLCode(OverrideCollation);

case Col.Status of

esModified: begin

Specs.Add(Format(AlterColBase, [Conn.QuoteIdent(Col.OldName), ColSpec]));

end;

esAddedUntouched, esAddedModified: begin

Specs.Add(Format(AddColBase, [ColSpec]));

end;

end;

AddQuery('EXECUTE sp_addextendedproperty '+Conn.EscapeString('MS_Description')+', '+

Conn.EscapeString(Col.Comment)+', '+

Conn.EscapeString('Schema')+', '+Conn.EscapeString(DBObject.Schema)+', '+

Conn.EscapeString('table')+', '+Conn.EscapeString(DBObject.Name)+', '+

Conn.EscapeString('column')+', '+Conn.EscapeString(Col.Name)

);

end;

ngPgSQL: begin

// https://www.postgresql.org/docs/current/sql-altertable.html

// All the forms of ALTER TABLE that act on a single table, except RENAME, SET SCHEMA, ATTACH PARTITION,

// and DETACH PARTITION can be combined into a list of multiple alterations to be applied together.

case Col.Status of

esModified: begin

// Rename

if Col.Name <> Col.OldName then begin

FinishSpecs;

Specs.Add(

Conn.GetSQLSpecifity(spRenameColumn, [Conn.QuoteIdent(Col.OldName), Conn.QuoteIdent(Col.Name)])

);

FinishSpecs;

end;

// Type

ColSpec := 'TYPE ' + Col.SQLCode(OverrideCollation, [cpType]);

Specs.Add(Format(AlterColBase, [Conn.QuoteIdent(Col.Name), ColSpec]));

// NULL allowed?

ColSpec := IfThen(Col.AllowNull, 'DROP NOT NULL', 'SET NOT NULL');

Specs.Add(Format(AlterColBase, [Conn.QuoteIdent(Col.Name), ColSpec]));

// Default

if Col.DefaultType=cdtNothing then

ColSpec := 'DROP DEFAULT'

else

ColSpec := 'SET ' + Col.SQLCode(OverrideCollation, [cpDefault]);

Specs.Add(Format(AlterColBase, [Conn.QuoteIdent(Col.Name), ColSpec]));

// Collation

ColSpec := Col.SQLCode(OverrideCollation, [cpCollation]);

if not ColSpec.IsEmpty then

Specs.Add(Format(AlterColBase, [Conn.QuoteIdent(Col.Name), ColSpec]));

end;

esAddedUntouched, esAddedModified: begin

ColSpec := Col.SQLCode(OverrideCollation);

Specs.Add(Format(AddColBase, [ColSpec]));

end;

end;

AddQuery('COMMENT ON COLUMN %s.'+Conn.QuoteIdent(Col.Name)+' IS '+Conn.EscapeString(Col.Comment));

end;

ngSQLite: begin

ColSpec := Col.SQLCode;

case Col.Status of

esModified: begin

// Rename

if Col.Name <> Col.OldName then begin

Specs.Add(

Conn.GetSQLSpecifity(spRenameColumn, [Conn.QuoteIdent(Col.OldName), Conn.QuoteIdent(Col.Name)])

);

end;

end;

esAddedUntouched, esAddedModified: begin

Specs.Add(Format(AddColBase, [ColSpec]));

end;

end;

FinishSpecs;

end;

end;

end;

PreviousCol := Col;

Node := listColumns.GetNextSibling(Node);

end;

// Deleted columns, not available as Node in above loop

for i:=0 to FColumns.Count-1 do begin

if FColumns[i].Status = esDeleted then begin

Specs.Add('DROP COLUMN '+Conn.QuoteIdent(FColumns[i].OldName));

// MSSQL + SQLite want one ALTER TABLE query per DROP COLUMN

if Conn.Parameters.NetTypeGroup in [ngMSSQL, ngSQLite] then

FinishSpecs;

end;

end;

// Drop indexes, also changed indexes, which will be readded below

for i:=0 to FDeletedKeys.Count-1 do begin

if FDeletedKeys[i] = TTableKey.PRIMARY then

IndexSQL := 'PRIMARY KEY'

else

IndexSQL := 'INDEX ' + Conn.QuoteIdent(FDeletedKeys[i]);

Specs.Add('DROP '+IndexSQL);

end;

// Add changed or added indexes

for i:=0 to FKeys.Count-1 do begin

if FKeys[i].Modified and (not FKeys[i].Added) then begin

if FKeys[i].OldIndexType = TTableKey.PRIMARY then

IndexSQL := 'PRIMARY KEY'

else

IndexSQL := 'INDEX ' + Conn.QuoteIdent(FKeys[i].OldName);

Specs.Add('DROP '+IndexSQL);

end;

if FKeys[i].Added or FKeys[i].Modified then

Specs.Add('ADD '+FKeys[i].SQLCode);

end;

for i:=0 to FDeletedForeignKeys.Count-1 do

Specs.Add('DROP FOREIGN KEY '+Conn.QuoteIdent(FDeletedForeignKeys[i]));

for i:=0 to FForeignKeys.Count-1 do begin

if FForeignKeys[i].Added or FForeignKeys[i].Modified then

Specs.Add('ADD '+FForeignKeys[i].SQLCode(True));

end;

// Check constraints

for i:=0 to FDeletedCheckConstraints.Count-1 do begin

Specs.Add('DROP CONSTRAINT ' + Conn.QuoteIdent(FDeletedCheckConstraints[i]));

end;

for Constraint in FCheckConstraints do begin

if Constraint.Added or Constraint.Modified then

Specs.Add('ADD ' + Constraint.SQLCode);

end;

FinishSpecs;

Result := TSQLBatch.Create;

Result.SQL := SQL;

FreeAndNil(Specs);

Mainform.ShowStatusMsg;

Screen.Cursor := crDefault;

end;

function TfrmTableEditor.ComposeCreateStatement: TSQLBatch;

var

i, IndexCount: Integer;

Col: PTableColumn;

Constraint: TCheckConstraint;

Node: PVirtualNode;

tmp, SQL: String;

begin

// Compose CREATE query, called by buttons and for SQL code tab

SQL := 'CREATE TABLE '+DBObject.Connection.QuoteIdent(editName.Text)+' ('+CRLF;

Node := listColumns.GetFirst;

while Assigned(Node) do begin

Col := listColumns.GetNodeData(Node);

SQL := SQL + #9 + Col.SQLCode + ','+CRLF;

Node := listColumns.GetNextSibling(Node);

end;

IndexCount := 0;

for i:=0 to FKeys.Count-1 do begin

tmp := FKeys[i].SQLCode;

if tmp <> '' then begin

SQL := SQL + #9 + tmp + ','+CRLF;

Inc(IndexCount);

end;

end;

for i:=0 to FForeignKeys.Count-1 do

SQL := SQL + #9 + FForeignKeys[i].SQLCode(True) + ','+CRLF;

// Check constraints

for Constraint in FCheckConstraints do begin

SQL := SQL + #9 + Constraint.SQLCode + ',' + sLineBreak;

end;

if Integer(listColumns.RootNodeCount) + IndexCount + FForeignKeys.Count > 0 then

Delete(SQL, Length(SQL)-2, 3);

SQL := SQL + CRLF + ')' + CRLF;

if memoComment.Text <> '' then

SQL := SQL + 'COMMENT='+DBObject.Connection.EscapeString(memoComment.Text) + CRLF;

if comboCollation.Text <> '' then

SQL := SQL + 'COLLATE='+DBObject.Connection.EscapeString(comboCollation.Text) + CRLF;

if (comboEngine.Text <> '') and (comboEngine.ItemIndex > 0) then begin

if DBObject.Connection.ServerVersionInt < 40018 then

SQL := SQL + 'TYPE='+comboEngine.Text + CRLF

else

SQL := SQL + 'ENGINE='+comboEngine.Text + CRLF;

end;

if comboRowFormat.Text <> 'DEFAULT' then

SQL := SQL + 'ROW_FORMAT='+comboRowFormat.Text + CRLF;

if chkChecksum.Checked then

SQL := SQL + 'CHECKSUM='+IntToStr(Integer(chkChecksum.Checked)) + CRLF;

if editAutoInc.Text <> '' then

SQL := SQL + 'AUTO_INCREMENT='+editAutoInc.Text + CRLF;

if editAvgRowLen.Text <> '' then

SQL := SQL + 'AVG_ROW_LENGTH='+editAvgRowLen.Text + CRLF;

if editMaxRows.Text <> '' then

SQL := SQL + 'MAX_ROWS='+editMaxRows.Text + CRLF;

if memoUnionTables.Enabled and (memoUnionTables.Text <> '') then

SQL := SQL + 'UNION=('+memoUnionTables.Text+')' + CRLF;

if comboInsertMethod.Enabled and (comboInsertMethod.Text <> '') then

SQL := SQL + 'INSERT_METHOD='+comboInsertMethod.Text + CRLF;

if SynMemoPartitions.GetTextLen > 0 then

SQL := SQL + '/*!50100 ' + SynMemoPartitions.Text + ' */';

SQL := SQL + ';' + CRLF;

if DBObject.Connection.Parameters.IsAnyPostgreSQL then begin

Node := listColumns.GetFirst;

while Assigned(Node) do begin

Col := listColumns.GetNodeData(Node);

SQL := SQL + 'COMMENT ON COLUMN '+

DBObject.Connection.QuoteIdent(editName.Text)+'.'+DBObject.Connection.QuoteIdent(Col.Name)+

' IS '+DBObject.Connection.EscapeString(Col.Comment) + ';' + CRLF;

Node := listColumns.GetNextSibling(Node);

end;

end;

Result := TSQLBatch.Create;

Result.SQL := Trim(SQL);

end;

procedure TfrmTableEditor.Modification(Sender: TObject);

begin

// Memorize modified status

if FLoaded then begin

if Sender is TComponent then

TComponent(Sender).Tag := MODIFIEDFLAG;

Modified := True;

btnSave.Enabled := Modified and (editName.Text <> '') and (listColumns.RootNodeCount > 0);

btnDiscard.Enabled := Modified;

CreateCodeValid := False;

AlterCodeValid := False;

UpdateSQLcode;

CalcMinColWidth;

end;

end;

procedure TfrmTableEditor.btnAddColumnClick(Sender: TObject);

var

NewCol: TTableColumn;

FocusedCol: PTableColumn;

fn, NewNode: PVirtualNode;

idx: Integer;

DefaultType: String;

begin

// Add new column after selected one

if listColumns.IsEditing then

listColumns.EndEditNode;

fn := listColumns.FocusedNode;

NewCol := TTableColumn.Create(DBObject.Connection);

if Assigned(fn) then begin

idx := fn.Index+1;

// Copy properties from focused node

FocusedCol := listColumns.GetNodeData(fn);

NewCol.DataType := FocusedCol.DataType;

NewCol.LengthSet := FocusedCol.LengthSet;

NewCol.Unsigned := FocusedCol.Unsigned;

NewCol.AllowNull := FocusedCol.AllowNull;

// There can be only one

if FocusedCol.DefaultType = cdtAutoInc then begin

NewCol.DefaultType := cdtText;

NewCol.DefaultText := '0';

end else begin

NewCol.DefaultType := FocusedCol.DefaultType;

NewCol.DefaultText := FocusedCol.DefaultText;

end;

NewCol.Collation := '';

end else begin

idx := listColumns.RootNodeCount;

DefaultType := 'INT';

NewCol.DataType := DBObject.Connection.GetDatatypeByName(DefaultType, False);

NewCol.Unsigned := False;

NewCol.AllowNull := True;

NewCol.DefaultType := cdtNothing;

NewCol.DefaultText := '';

NewCol.Comment := '';

NewCol.Collation := '';

end;

NewCol.Name := _('Column')+' '+IntToStr(idx+1);

FColumns.Insert(idx, NewCol);

NewNode := listColumns.InsertNode(fn, amInsertAfter, @NewCol);

NewCol.Status := esAddedUntouched;

SelectNode(listColumns, NewNode);

Modification(Sender);

ValidateColumnControls;

// not sufficient, if list has minimum height, see https://www.heidisql.com/forum.php?t=35766

// if listColumns.ScrollIntoView(NewNode, False) then

if listColumns.Height > listColumns.Header.Height+20 then

listColumns.EditNode(NewNode, 1);

end;

procedure TfrmTableEditor.btnRemoveColumnClick(Sender: TObject);

var

Node, NodeFocus: PVirtualNode;

Col: PTableColumn;

begin

// Remove selected column(s)

Node := GetNextNode(listColumns, nil, True);

while Assigned(Node) do begin

Col := listColumns.GetNodeData(Node);

Col.Name := Col.OldName;

Col.Status := esDeleted;

Node := GetNextNode(listColumns, Node, True);

end;

// Find next unselected node for new focus

Node := listColumns.FocusedNode;

NodeFocus := nil;

while Assigned(Node) do begin

if not (vsSelected in Node.States) then begin

NodeFocus := Node;

break;

end;

Node := listColumns.GetNextSibling(Node);

end;