Synedit Help

[Clément 148]

Hi!

 

I'm using Synedit to built a small SQL editor, but I need to change some default behavior.

 

1) I need to trigger Auto-completion when a '.' is typed, but I don't want to display the Completion Form base on a timer interval. Since the data is obtained asynchronously, I want to fill and display the completion form after receiving a specific custom message (WM_GETCOMPLETIONDATA). When handling the message, I will fill the list and display the form. Is there a way to call the completion form on demand? (the filling and displaying follows...)

 

2) I need to fill the autocompletion list based on the previous token value. The user can type a SQL select as
 

select c.| from categories c

(The '|' is the current cursor position). As the user types the '.', I would like to fill the completion list with all the columns from categories table.
As '.' is typed, I can get the previous token ( which is "c" in this case), and do some parsing to discover "c" is in fact an alias for "Categories", send a custom message (WM_GETCOMPLETIONDATA)  and when receiving the data, I can fill the completion form with all fields from categories table.
It seems to me that SynEditProposal.OnExecute is the correct event to handle all the parsing. Is there a better one?

 

TIA,

Clément

 

[Alexander Sviridenkov 360]

Maybe to use preloaded database schema? In this case completion can be filled synchronously with no significant delay.

 

 

[Clément 148]

6 minutes ago, Alexander Sviridenkov said:

Maybe to use preloaded database schema? In this case completion can be filled synchronously with no significant delay. 

 

This is exactly what I would like to do. Very nice! Are you parsing the whole SQL text with each keypress?
In my project the database schema is "loaded on demand" in a background thread. Queries/Scripts/Batchmove runs in that background thread. And I store the metadata also in that thread. That's why I need to get call the completion form when receiving the message.

Here's a screen shot.

 

 

 

[Alexander Sviridenkov 360]

>>This is exactly what I would like to do. Very nice! Are you parsing the whole SQL text with each keypress?

 

Yes, but it works very fast.

>>In my project the database schema is "loaded on demand" in a background thread. Queries/Scripts/Batchmove runs in that background thread. And I store the metadata also in that thread. That's why I need to get call the completion form when receiving the message.

 

But taking into account that schema is loaded only once, is there reason to fill autocomplete asynchronously? You can start background loading when necessary and fill autocomplete only if schema is already loaded.

[Clément 148]

12 minutes ago, Alexander Sviridenkov said:

But taking into account that schema is loaded only once, is there reason to fill autocomplete asynchronously? You can start background loading when necessary and fill autocomplete only if schema is already loaded.

In my case the schema can change (user can add columns, change columns types, new index etc). Once the data is changed, the corresponding schema info is marked as obsolete and the a new query is done to retrieve data.

[Alexander Sviridenkov 360]

Usually schema changing occurs rarely enough  (much less frequently that typing in query editor) so reloading changed part right after change should not affect performance.

[aehimself 399]

I solved this with the handler of TSynCompletionProposal.OnExecute (TableNamesSelector is a TSynCompletionProposal, SQLEditor is a TSynEdit) :

Procedure TSQLConnectionFrame.TableNamesSelectorExecute(Kind: SynCompletionType; Sender: TObject; Var CurrentInput: String; Var x, y: Integer; Var CanExecute: Boolean);
Var
 sa: TArray<String>;
Begin
 If TableNamesSelector.ItemList.Count > 0 Then Exit;

 sa := SQLEditor.LineText.Substring(0, SQLEditor.CaretX - 1).Split([' ']);
 If Length(sa) > 0 Then sa := sa[Length(sa) - 1].Split(['.']);
 If Length(sa) < 2 Then TableNamesSelector.ItemList.Assign(SQLHighlight.TableNames) // No dot, offer table names immediately
   Else Begin
        CanExecute := False;
        If Length(sa) = 2 Then // Start a thread to collect field names of said table...
        End;
End;

You have to pass sa[0], sa[1], X and Y to the thread. sa[0] is the table name, sa[1] is the field name fragment which was already typed, x and y is the position where the proposal should pop back up.

Once it finishes, you can:

 TableNamesSelector.ItemList.Assign(worker.FieldNames);
 TableNamesSelector.Execute(worker.FilterForText {passed to the thread as sa[1] from OnExecute}, worker.X, worker.Y);

Also handle the OnClose event of the completionproposal:

Procedure TSQLConnectionFrame.TableNamesSelectorClose(Sender: TObject);
Begin
 TableNamesSelector.ItemList.Clear;
End;

The idea is that if the ItemList is empty the data still has to be collected and execution is disallowed. It will be popped up when the thread finishes.

Edited July 28, 2021 by aehimself

[Clément 148]

5 hours ago, aehimself said:

I solved this with the handler of TSynCompletionProposal.OnExecute (TableNamesSelector is a TSynCompletionProposal, SQLEditor is a TSynEdit)

 

Thank your for the code. It will help