Recent

Author Topic: How do I load TSynCompletion with table field name?  (Read 3326 times)

RedOctober

  • Sr. Member
  • ****
  • Posts: 268
How do I load TSynCompletion with table field name?
« on: April 28, 2018, 02:26:35 am »
Goal:

  To provide my end user with a list of fields to choose from when typing SQL statements.
  Within a SELECT statement (both fields clause and FROM clause), the user may use the full table name, or an alias.  Which ever method the user enters the table identifier as, I would like them to get a field list, from the appropriate table, that they can choose from, when they press Ctrl-Space.
 
On my form, I have:
- TSynEdit
- TSynCompletion

The TSynCompletion.Editor is connected to the TSynEdit

I have added some test items.  At runtime, I type Ctrl-Space and my list pops up as expected.

I have looked at the example in the SynEdit example folder.  There is a subroutine called "Add", which I suspect is to filter the choices.  I haven't gotten that far yet.

What is not explained in the example, is, how do I programmatically load the the ItemList of the TSynCompletion, prgrammatically, based on the table name or the alias the user is referring to.


Example SQL:

Code: Pascal  [Select]
  1. SELECT FIRST 300 SKIP 0
  2.   t1.FIRST_NAME, t1.LAST_NAME, t2.DEPARTMENT
  3. FROM
  4.   EMPLOYEE t1
  5.   JOIN DEPARTMENT t2 ON t1.DEPT_NO = t2.DEPT_NO
  6. WHERE t1.HIRE_DATE < '1990-JAN-01'
  7. ORDER BY t1.FIRST_NAME

Behavior expected


At any place in the SELECT statement, if the cursor is to the right of "t1." and the user presses Ctrl-Space, the field list of the EMPLOYEE table should appear.
At any place in the SELECT statement, if the cursor is to the right of "EMPLOYEE." and the user presses Ctrl-Space, the field list of the EMPLOYEE table should appear.
At any place in the SELECT statement, if the cursor is to the right of "t2." and the user presses Ctrl-Space, the field list of the DEPARTMENT table should appear.
At any place in the SELECT statement, if the cursor is to the right of "DEPARTMENT." and the user presses Ctrl-Space, the field list of the DEPARTMENT table should appear.

How do I change out the field list accourding to what is to the left of the cursor? 
How do I know which table name goes with the alias that the alias characters (followed by a dot) to the left of the cursor refer to?

I know I can extract the field lists for the table name from the Firebird System Tables.  I don't need help with that part.
I just need to know which table name I should use to look for the fields, so I can populate the list.

(I'll worry about how the "Add" subroutine works later.  I have to load the ItemList somehow first.)


Edson

  • Hero Member
  • *****
  • Posts: 1041
Re: How do I load TSynCompletion with table field name?
« Reply #1 on: April 28, 2018, 05:18:32 am »
I think It's clear you need to do some kind of lexer/parser to scan first the SQL sentence and locate the special identifiers, like table name and alias.

Then when opening the completion list you need to analize syntactically what kind of identifiers you have to use for the completion list. Maybe you will need to use threads if the list take some time in obtaining.

Check to my library https://github.com/t-edson/SynFacilCompletion. It can help you on implementing the lexer in SynEdit and managing the completion functionality.
Lazarus 1.6 - FPC 3.0.0 - x86_64-win64 on  Windows 7

taazz

  • Hero Member
  • *****
  • Posts: 5363
Re: How do I load TSynCompletion with table field name?
« Reply #2 on: April 28, 2018, 05:50:13 am »
Goal:

  To provide my end user with a list of fields to choose from when typing SQL statements.
  Within a SELECT statement (both fields clause and FROM clause), the user may use the full table name, or an alias.  Which ever method the user enters the table identifier as, I would like them to get a field list, from the appropriate table, that they can choose from, when they press Ctrl-Space.
 
On my form, I have:
- TSynEdit
- TSynCompletion

The TSynCompletion.Editor is connected to the TSynEdit

I have added some test items.  At runtime, I type Ctrl-Space and my list pops up as expected.

I have looked at the example in the SynEdit example folder.  There is a subroutine called "Add", which I suspect is to filter the choices.  I haven't gotten that far yet.

What is not explained in the example, is, how do I programmatically load the the ItemList of the TSynCompletion, prgrammatically, based on the table name or the alias the user is referring to.


Example SQL:

Code: Pascal  [Select]
  1. SELECT FIRST 300 SKIP 0
  2.   t1.FIRST_NAME, t1.LAST_NAME, t2.DEPARTMENT
  3. FROM
  4.   EMPLOYEE t1
  5.   JOIN DEPARTMENT t2 ON t1.DEPT_NO = t2.DEPT_NO
  6. WHERE t1.HIRE_DATE < '1990-JAN-01'
  7. ORDER BY t1.FIRST_NAME

Behavior expected


At any place in the SELECT statement, if the cursor is to the right of "t1." and the user presses Ctrl-Space, the field list of the EMPLOYEE table should appear.
At any place in the SELECT statement, if the cursor is to the right of "EMPLOYEE." and the user presses Ctrl-Space, the field list of the EMPLOYEE table should appear.
At any place in the SELECT statement, if the cursor is to the right of "t2." and the user presses Ctrl-Space, the field list of the DEPARTMENT table should appear.
At any place in the SELECT statement, if the cursor is to the right of "DEPARTMENT." and the user presses Ctrl-Space, the field list of the DEPARTMENT table should appear.

How do I change out the field list accourding to what is to the left of the cursor? 
How do I know which table name goes with the alias that the alias characters (followed by a dot) to the left of the cursor refer to?

I know I can extract the field lists for the table name from the Firebird System Tables.  I don't need help with that part.
I just need to know which table name I should use to look for the fields, so I can populate the list.

(I'll worry about how the "Add" subroutine works later.  I have to load the ItemList somehow first.)
forget about add and focus on filtering. In short add all your db objects when the connection to the db is established or when a DDL command executes (clear the existing first) then focus on filtering them based on the word on the cursor or left from it.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

Martin_fr

  • Administrator
  • Hero Member
  • *
  • Posts: 5575
    • wiki
Re: How do I load TSynCompletion with table field name?
« Reply #3 on: April 28, 2018, 08:44:43 am »
when the completion opens, you can get the caret position. (use LogicalCaret / search wiki for this term).

Then you can get the text of the CurrentLine, and scan it for the identifier before the caretX (and make sure there is a dot too).

You may need to parse more info, to find if it is a tablename, or something else. You may be able to get some info from the HL, not sure...

Edson

  • Hero Member
  • *****
  • Posts: 1041
Re: How do I load TSynCompletion with table field name?
« Reply #4 on: April 29, 2018, 12:53:24 am »
Here a sample using SynFacilCompletion. It shows 3 cases for openning the completion list:

- After the word "FROM"
- After a dot.
- Inside a section.

You can intercept the events to add your custom list.
Lazarus 1.6 - FPC 3.0.0 - x86_64-win64 on  Windows 7

RedOctober

  • Sr. Member
  • ****
  • Posts: 268
Re: How do I load TSynCompletion with table field name?
« Reply #5 on: April 29, 2018, 10:32:08 pm »
Thanks to all contributors, this section of my project is now working correctly.