unit trigger_editor;

Winapi.Windows, System.SysUtils, System.Classes, Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls, Vcl.Menus, SynEdit, SynMemo,

SynCompletionProposal, SynRegExpr,

dbconnection, dbstructures, dbstructures.mysql, apphelpers, gnugettext, Vcl.ComCtrls, extra_controls;

TFrame = TDBObjectEditor;

TfrmTriggerEditor = class(TFrame)

SynMemoBody: TSynMemo;

btnHelp: TButton;

btnDiscard: TButton;

btnSave: TButton;

lblBody: TLabel;

SynCompletionProposalStatement: TSynCompletionProposal;

PageControlMain: TPageControl;

tabOptions: TTabSheet;

tabCreateCode: TTabSheet;

comboDefiner: TComboBox;

lblDefiner: TLabel;

editName: TEdit;

lblName: TLabel;

lblTable: TLabel;

comboTable: TComboBox;

lblEvent: TLabel;

comboTiming: TComboBox;

comboEvent: TComboBox;

SynMemoCreateCode: TSynMemo;

procedure btnHelpClick(Sender: TObject);

procedure btnDiscardClick(Sender: TObject);

procedure Modification(Sender: TObject);

procedure btnSaveClick(Sender: TObject);

procedure SynCompletionProposalStatementExecute(Kind: SynCompletionType; Sender: TObject;

var CurrentInput: String; var x, y: Integer; var CanExecute: Boolean);

procedure comboDefinerDropDown(Sender: TObject);

procedure comboChange(Sender: TObject);

procedure PageControlMainChange(Sender: TObject);

private

{ Private declarations }

function ComposeCreateStatement: String;

public

{ Public declarations }

constructor Create(AOwner: TComponent); override;

procedure Init(Obj: TDBObject); override;

function ApplyModifications: TModalResult; override;

end;

uses main;

constructor TfrmTriggerEditor.Create(AOwner: TComponent);

col: TProposalColumn;

i: Integer;

inherited;

SynMemoBody.Highlighter := Mainform.SynSQLSynUsed;

editName.MaxLength := NAME_LEN;

for i:=0 to Mainform.SynCompletionProposal.Columns.Count-1 do begin

col := SynCompletionProposalStatement.Columns.Add;

col.ColumnWidth := Mainform.SynCompletionProposal.Columns[i].ColumnWidth;

end;

SynCompletionProposalStatement.NbLinesInWindow := Mainform.SynCompletionProposal.NbLinesInWindow;

SynCompletionProposalStatement.Width := Mainform.SynCompletionProposal.Width;

SynCompletionProposalStatement.Options := Mainform.SynCompletionProposal.Options;

SynCompletionProposalStatement.TimerInterval := Mainform.SynCompletionProposal.TimerInterval;

SynCompletionProposalStatement.Margin := Mainform.SynCompletionProposal.Margin;

FMainSynMemo := SynMemoBody;

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

end;

procedure TfrmTriggerEditor.Init(Obj: TDBObject);

Definitions: TDBQuery;

DBObjects: TDBObjectList;

i: Integer;

Found: Boolean;

Body, QuoteCharsRx, QuotedWordRx: String;

rx: TRegExpr;

inherited;

editName.Text := '';

comboDefiner.Text := '';

comboDefiner.TextHint := f_('Current user (%s)', [Obj.Connection.CurrentUserHostCombination]);

comboDefiner.Hint := f_('Leave empty for current user (%s)', [Obj.Connection.CurrentUserHostCombination]);

SynMemoBody.Text := 'BEGIN'+CRLF+CRLF+'END';

comboEvent.Items.Text := 'INSERT'+CRLF+'UPDATE'+CRLF+'DELETE';

comboEvent.ItemIndex := 0;

case Obj.Connection.Parameters.NetTypeGroup of

ngSQLite:

comboTiming.Items.Text := 'BEFORE' + sLineBreak + 'AFTER' + sLineBreak + 'INSTEAD OF';

else

comboTiming.Items.Text := 'BEFORE' + sLineBreak + 'AFTER';

end;

comboTiming.ItemIndex := 0;

DBObjects := MainForm.ActiveConnection.GetDBObjects(Mainform.ActiveDatabase);

comboTable.Items.Clear;

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

if DBObjects[i].NodeType in [lntTable] then

comboTable.Items.Add(DBObjects[i].Name);

end;

if comboTable.Items.Count > 0 then

comboTable.ItemIndex := 0;

if ObjectExists then begin

// Edit mode

editName.Text := DBObject.Name;

// MariaDB: CREATE DEFINER=`root`@`localhost` TRIGGER `trg` BEFORE INSERT ON `tbl` FOR EACH ROW BEGIN .. END

// SQLite: CREATE TRIGGER "test_delete" AFTER INSERT ON "albums" FOR EACH ROW BEGIN .. END

rx := TRegExpr.Create;

rx.ModifierI := True;

QuoteCharsRx := QuoteRegExprMetaChars(DBObject.Connection.QuoteChars);

QuotedWordRx := '['+QuoteCharsRx+']?[^'+QuoteCharsRx+']+['+QuoteCharsRx+']?';

rx.Expression := '(\sDEFINER=('+QuotedWordRx+'@'+QuotedWordRx+'))?' +

'\s+TRIGGER\s+'+QuotedWordRx +

'\s+('+Implode('|', comboTiming.Items)+')' +

'\s+('+Implode('|', comboEvent.Items)+')' +

'\s+ON\s+('+QuotedWordRx+')' +

'\s+FOR\s+EACH\s+ROW\s+(.+)$';

try

Body := DBObject.Connection.GetCreateCode(DBObject);

if rx.Exec(Body) then begin

comboDefiner.Text := DBObject.Connection.DeQuoteIdent(rx.Match[2], '@');

comboTiming.ItemIndex := comboTiming.Items.IndexOf(UpperCase(rx.Match[3]));

comboEvent.ItemIndex := comboEvent.Items.IndexOf(UpperCase(rx.Match[4]));

comboTable.ItemIndex := comboTable.Items.IndexOf(DBObject.Connection.DeQuoteIdent(rx.Match[5]));

Body := rx.Match[6];

end

else

raise EDbError.CreateFmt(_('Result from previous query does not contain expected pattern: %s'), [rx.Expression]);

except

on E:EDbError do begin

DBObject.Connection.Log(lcError, E.Message);

Body := '';

end;

end;

SynMemoBody.Text := Body;

SynMemoBody.TopLine := FMainSynMemoPreviousTopLine;

end else begin

editName.Text := '';

if MainForm.FocusedTables.Count > 0 then begin

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

if comboTable.Items[i] = MainForm.FocusedTables[0].Name then begin

comboTable.ItemIndex := i;

comboChange(comboTable);

Break;

end;

end;

end;

end;

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

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

btnHelp.Top := btnSave.Top;

btnDiscard.Top := btnSave.Top;

Modification(Self);

Modified := False;

btnSave.Enabled := Modified;

btnDiscard.Enabled := Modified;

Mainform.ShowStatusMsg;

TExtForm.PageControlTabHighlight(PageControlMain);

Screen.Cursor := crDefault;

end;

procedure TfrmTriggerEditor.Modification(Sender: TObject);

// Enable buttons if anything has changed

Modified := True;

btnSave.Enabled := Modified

and (editName.Text <> '') and (comboTable.ItemIndex > -1)

and (comboTiming.ItemIndex > -1) and (comboEvent.ItemIndex > -1)

and (SynMemoBody.Text <> '');

btnDiscard.Enabled := Modified;

SynMemoCreateCode.Text := ComposeCreateStatement;

end;

procedure TfrmTriggerEditor.PageControlMainChange(Sender: TObject);

TExtForm.PageControlTabHighlight(PageControlMain);

end;

procedure TfrmTriggerEditor.btnDiscardClick(Sender: TObject);

// Reinit editor, discarding changes

Modified := False;

Init(DBObject);

end;

procedure TfrmTriggerEditor.btnSaveClick(Sender: TObject);

ApplyModifications;

end;

procedure TfrmTriggerEditor.comboChange(Sender: TObject);

// Auto generate trigger name as long as it was not user-edited. See issue #3477.

if (not ObjectExists) and (not editName.Modified) then

editName.Text := comboTable.Text+'_'+LowerCase(comboTiming.Text)+'_'+LowerCase(comboEvent.Text);

Modification(Sender);

end;

procedure TfrmTriggerEditor.comboDefinerDropDown(Sender: TObject);

// Populate definers from mysql.user

(Sender as TComboBox).Items.Assign(DBObject.Connection.AllUserHostCombinations);

end;

function TfrmTriggerEditor.ApplyModifications: TModalResult;

OldCreateCode: String;

// Edit mode means we drop the trigger and recreate it, as there is no ALTER TRIGGER.

Result := mrOk;

try

// In edit mode we could create a temporary trigger, but that would only cause an error a la

// "This version of MySQL doesn't yet support multiple triggers with the same action time and event for one table"

// So, we take the risk of loosing the trigger for cases in which the user has SQL errors in

// his statement. The user must fix such errors and re-press "Save" while we have them in memory,

// otherwise the trigger attributes are lost forever.

OldCreateCode := '';

if ObjectExists then try

OldCreateCode := DBObject.CreateCode;

DBObject.Connection.Query('DROP TRIGGER '+DBObject.Connection.QuoteIdent(DBObject.Name));

except

end;

MainForm.ActiveConnection.Query(ComposeCreateStatement);

DBObject.Name := editName.Text;

DBObject.UnloadDetails;

Mainform.UpdateEditorTab;

Mainform.RefreshTree(DBObject);

Modified := False;

btnSave.Enabled := Modified;

btnDiscard.Enabled := Modified;

except

on E:EDbError do begin

ErrorDialog(E.Message);

Result := mrAbort;

if not OldCreateCode.IsEmpty then

DBObject.Connection.Query(OldCreateCode);

end;

end;

end;

procedure TfrmTriggerEditor.SynCompletionProposalStatementExecute(Kind: SynCompletionType; Sender: TObject;

var CurrentInput: String; var x, y: Integer; var CanExecute: Boolean);

Proposal: TSynCompletionProposal;

Token, DisplayText: String;

Columns: TDBQuery;

// Propose column names from referencing table

Proposal := Sender as TSynCompletionProposal;

Proposal.Font.Assign(Font);

Proposal.ItemHeight := TExtForm.ScaleSize(PROPOSAL_ITEM_HEIGHT, Self);

Token := UpperCase(Proposal.PreviousToken);

Proposal.InsertList.Clear;

Proposal.ItemList.Clear;

if (Token = 'NEW') or (Token = 'OLD') then begin

if comboTable.Text = '' then

CanExecute := False

else try

Columns := DBObject.Connection.GetResults('SHOW COLUMNS FROM '+DBObject.Connection.QuoteIdent(comboTable.Text));

while not Columns.Eof do begin

DisplayText := SynCompletionProposalPrettyText(ICONINDEX_FIELD, GetFirstWord(Columns.Col('Type')), Columns.Col('Field'), '');

Proposal.AddItem(DisplayText, Columns.Col('Field'));

Columns.Next;

end;

except

end;

end else

Mainform.SynCompletionProposalExecute(Kind, Sender, CurrentInput, x, y, CanExecute);

end;

procedure TfrmTriggerEditor.btnHelpClick(Sender: TObject);

Help(Self, 'createtrigger');

end;

function TfrmTriggerEditor.ComposeCreateStatement: String;

// CREATE

// [DEFINER = { user | CURRENT_USER }]

// TRIGGER trigger_name trigger_time trigger_event

// ON tbl_name FOR EACH ROW trigger_stmt

Result := 'CREATE ';

if comboDefiner.Text <> '' then

Result := Result + 'DEFINER='+DBObject.Connection.QuoteIdent(comboDefiner.Text, True, '@')+' ';

Result := Result + 'TRIGGER '+DBObject.Connection.QuoteIdent(editName.Text)+' '+

comboTiming.Items[comboTiming.ItemIndex]+' '+comboEvent.Items[comboEvent.ItemIndex]+

' ON '+DBObject.Connection.QuoteIdent(comboTable.Text)+

' FOR EACH ROW '+SynMemoBody.Text;

end;

end.