Bug 32613 - Add auto-completion to our SQL reports editor
Summary: Add auto-completion to our SQL reports editor
Status: RESOLVED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Reports (show other bugs)
Version: Main
Hardware: All All
: P5 - low enhancement
Assignee: Jacob O'Mara
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks: 35494 35498 35509
  Show dependency tree / graph
 
Reported: 2023-01-12 11:03 UTC by Martin Renvoize (ashimema)
Modified: 2023-12-06 16:56 UTC (History)
9 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact: Caroline Cyr La Rose
Documentation submission: https://gitlab.com/koha-community/koha-manual/-/merge_requests/690
Text to go in the release notes:
This adds an auto-complete feature to the SQL reports editor. This works for tables and columns. For columns you'll need to start with the table name, like `borrowers.`. The editor will then suggest the columns of the `borrowers` table. The auto-complete feature will also work when tables have been renamed.
Version(s) released in:
23.05.00
Circulation function:

Attachments
Bug 32613: Add autocomplete to SQL reports editor (39.33 KB, patch)
2023-03-01 11:21 UTC, Jacob O'Mara 		Details | Diff | Splinter Review
Bug 32613: Add database tables to autocomplete (3.37 KB, patch)
2023-03-01 11:31 UTC, Jacob O'Mara 		Details | Diff | Splinter Review
Bug 32613: Add database tables to autocomplete (3.42 KB, patch)
2023-03-02 13:11 UTC, Lucas Gass (lukeg) 		Details | Diff | Splinter Review
Bug 32613: Add autocomplete to SQL reports editor (39.32 KB, patch)
2023-03-02 13:12 UTC, Lucas Gass (lukeg) 		Details | Diff | Splinter Review
Bug 32613: Add database tables to autocomplete (3.42 KB, patch)
2023-03-02 13:12 UTC, Lucas Gass (lukeg) 		Details | Diff | Splinter Review
Bug 32613: Add autocomplete to SQL reports editor (77.11 KB, patch)
2023-03-02 17:24 UTC, Jacob O'Mara 		Details | Diff | Splinter Review
Bug 32613: Add database tables to autocomplete (5.39 KB, patch)
2023-03-02 17:24 UTC, Jacob O'Mara 		Details | Diff | Splinter Review
Bug 32613: Add database tables to autocomplete (38.97 KB, patch)
2023-03-02 21:13 UTC, Jacob O'Mara 		Details | Diff | Splinter Review
Bug 32613: Add autocomplete to SQL reports editor (77.15 KB, patch)
2023-03-02 21:39 UTC, Andrew Fuerste-Henry 		Details | Diff | Splinter Review
Bug 32613: Add database tables to autocomplete (39.02 KB, patch)
2023-03-02 21:39 UTC, Andrew Fuerste-Henry 		Details | Diff | Splinter Review
Bug 32613: Add autocomplete to SQL reports editor (77.21 KB, patch)
2023-03-03 16:45 UTC, Nick Clemens (kidclamp) 		Details | Diff | Splinter Review
Bug 32613: Add database tables to autocomplete (39.08 KB, patch)
2023-03-03 16:45 UTC, Nick Clemens (kidclamp) 		Details | Diff | Splinter Review
Bug 32613: (follow-up) Cache the tables (1.23 KB, patch)
2023-03-03 16:45 UTC, Nick Clemens (kidclamp) 		Details | Diff | Splinter Review
Bug 32613: (follow-up) Cache the tables (1.45 KB, patch)
2023-03-06 17:45 UTC, Tomás Cohen Arazi (tcohen) 		Details | Diff | Splinter Review
Show Obsolete (11) View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Martin Renvoize (ashimema) 2023-01-12 11:03:53 UTC 
We're using CodeMirror for the SQL reports editor.. I wonder if there's a plugin or configuration option for that which supports basic autocompletion, similar to what is found on https://sqliteonline.com/, that we can impliment for Koha?
Comment 1 Jacob O'Mara 2023-03-01 11:21:14 UTC 
Created attachment 147556 [details] [review]
Bug 32613: Add autocomplete to SQL reports editor

This adds auto-complete for the sql reports editor codemirror instance.
To prevent a regression in syntax highlighting the overlay mode
'sqlPlaceholders' has been removed and replaced with a highlighting
engine that works correctly with the autocomplete engine.

Test Plan:
1. Navigate to reports and create a new sql report
2. Write an Sql query and observe that there is no autocomplete options
3. Apply patch
4. Write a new Sql query and observe that there are now auto-complete
   options that can be navigated through with the arrow keys and
   accepted with either tab or the enter keys.
5. Ensure that items bounded in << >> or [[ ]] are still syntax
   highlighted post-patch
Comment 2 Jacob O'Mara 2023-03-01 11:31:15 UTC 
Created attachment 147557 [details] [review]
Bug 32613: Add database tables to autocomplete

This patch adds a database call to get the table and column names in the
database and parse them into the autocomplete engine.

Test Plan:
As previous commit but also check for column/table names in the
autocomplete popup
Comment 3 Lucas Gass (lukeg) 2023-03-01 17:10:10 UTC 
Very cool! Can we make it so it won't try and auto complete things like DELETE or INSERT?
Comment 4 Lucas Gass (lukeg) 2023-03-01 17:11:27 UTC 
I can get this to autocomplete table names but not column names.
Comment 5 Jacob O'Mara 2023-03-02 11:05:27 UTC 
Hi Lucas,
I can remove INSERT, DELETE etc. from the sql.js keywords. This would also remove the syntax highlighting for these in the codemirror instance but that should not be an issue as they should not be being used anyway.

In terms of column autocompletion the columns only appear when referencing the column in the format 'table.column' eg:

SELECT alert.alertid FROM alert; 

would give you autocompletion for the column. While this is more typing than is typical it does reduce the likelihood of an end user requesting an incorrect column. I'm open to suggestions as I can modify the parsing to always show all options if that is deemed better. (should have been written above so apologies) additionally, I very stupidly added the . key to the list of exception keys which will be amended in a patch I will upload later. Taking this off of 'needs signoff' in the meantime
Comment 6 Lucas Gass (lukeg) 2023-03-02 13:11:06 UTC 
Created attachment 147610 [details] [review]
Bug 32613: Add database tables to autocomplete

This patch adds a database call to get the table and column names in the
database and parse them into the autocomplete engine.

Test Plan:
As previous commit but also check for column/table names in the
autocomplete popup

Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
Comment 7 Lucas Gass (lukeg) 2023-03-02 13:12:31 UTC 
Created attachment 147611 [details] [review]
Bug 32613: Add autocomplete to SQL reports editor

This adds auto-complete for the sql reports editor codemirror instance.
To prevent a regression in syntax highlighting the overlay mode
'sqlPlaceholders' has been removed and replaced with a highlighting
engine that works correctly with the autocomplete engine.

Test Plan:
1. Navigate to reports and create a new sql report
2. Write an Sql query and observe that there is no autocomplete options
3. Apply patch
4. Write a new Sql query and observe that there are now auto-complete
   options that can be navigated through with the arrow keys and
   accepted with either tab or the enter keys.
5. Ensure that items bounded in << >> or [[ ]] are still syntax
   highlighted post-patch
Comment 8 Lucas Gass (lukeg) 2023-03-02 13:12:33 UTC 
Created attachment 147612 [details] [review]
Bug 32613: Add database tables to autocomplete

This patch adds a database call to get the table and column names in the
database and parse them into the autocomplete engine.

Test Plan:
As previous commit but also check for column/table names in the
autocomplete popup

Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
Comment 9 Jacob O'Mara 2023-03-02 17:24:20 UTC 
Created attachment 147660 [details] [review]
Bug 32613: Add autocomplete to SQL reports editor

This adds auto-complete for the sql reports editor codemirror instance.
To prevent a regression in syntax highlighting the overlay mode
'sqlPlaceholders' has been removed and replaced with a highlighting
engine that works correctly with the autocomplete engine.

Test Plan:
1. Navigate to reports and create a new sql report
2. Write an Sql query and observe that there is no autocomplete options
3. Apply patch
4. Write a new Sql query and observe that there are now auto-complete
   options that can be navigated through with the arrow keys and
   accepted with either tab or the enter keys.
5. Ensure that items bounded in << >> or [[ ]] are still syntax
   highlighted post-patch

additionally added helpful note under codemirror instance to assist with codemirror autocompletion and removed prohibited words from sql language (by moving from sql.min.js to sql.js)
Comment 10 Jacob O'Mara 2023-03-02 17:24:49 UTC 
Created attachment 147661 [details] [review]
Bug 32613: Add database tables to autocomplete

This patch adds a database call to get the table and column names in the
database and parse them into the autocomplete engine.

Test Plan:
As previous commit but also check for column/table names in the
autocomplete popup
Comment 11 Jacob O'Mara 2023-03-02 21:13:28 UTC 
Created attachment 147672 [details] [review]
Bug 32613: Add database tables to autocomplete

This patch adds a database call to get the table and column names in the
database and parse them into the autocomplete engine.

Test Plan:
As previous commit but also check for column/table names in the
autocomplete popup
Comment 12 Andrew Fuerste-Henry 2023-03-02 21:39:08 UTC 
Created attachment 147673 [details] [review]
Bug 32613: Add autocomplete to SQL reports editor

This adds auto-complete for the sql reports editor codemirror instance.
To prevent a regression in syntax highlighting the overlay mode
'sqlPlaceholders' has been removed and replaced with a highlighting
engine that works correctly with the autocomplete engine.

Test Plan:
1. Navigate to reports and create a new sql report
2. Write an Sql query and observe that there is no autocomplete options
3. Apply patch
4. Write a new Sql query and observe that there are now auto-complete
   options that can be navigated through with the arrow keys and
   accepted with either tab or the enter keys.
5. Ensure that items bounded in << >> or [[ ]] are still syntax
   highlighted post-patch

Signed-off-by: Andrew Fuerste-Henry <andrewfh@dubcolib.org>
Comment 13 Andrew Fuerste-Henry 2023-03-02 21:39:10 UTC 
Created attachment 147674 [details] [review]
Bug 32613: Add database tables to autocomplete

This patch adds a database call to get the table and column names in the
database and parse them into the autocomplete engine.

Test Plan:
As previous commit but also check for column/table names in the
autocomplete popup

Signed-off-by: Andrew Fuerste-Henry <andrewfh@dubcolib.org>
Comment 14 Andrew Fuerste-Henry 2023-03-02 21:41:23 UTC 
This is really nice! I was pleased to see that if I rename a table then I can use the alias to get auto-completed column names -- so if I've got "FROM borrowers b" then it'll auto-complete borrowers table columns when I type "b."
Comment 15 Nick Clemens (kidclamp) 2023-03-03 16:45:45 UTC 
Created attachment 147715 [details] [review]
Bug 32613: Add autocomplete to SQL reports editor

This adds auto-complete for the sql reports editor codemirror instance.
To prevent a regression in syntax highlighting the overlay mode
'sqlPlaceholders' has been removed and replaced with a highlighting
engine that works correctly with the autocomplete engine.

Test Plan:
1. Navigate to reports and create a new sql report
2. Write an Sql query and observe that there is no autocomplete options
3. Apply patch
4. Write a new Sql query and observe that there are now auto-complete
   options that can be navigated through with the arrow keys and
   accepted with either tab or the enter keys.
5. Ensure that items bounded in << >> or [[ ]] are still syntax
   highlighted post-patch

Signed-off-by: Andrew Fuerste-Henry <andrewfh@dubcolib.org>
Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Comment 16 Nick Clemens (kidclamp) 2023-03-03 16:45:47 UTC 
Created attachment 147716 [details] [review]
Bug 32613: Add database tables to autocomplete

This patch adds a database call to get the table and column names in the
database and parse them into the autocomplete engine.

Test Plan:
As previous commit but also check for column/table names in the
autocomplete popup

Signed-off-by: Andrew Fuerste-Henry <andrewfh@dubcolib.org>
Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Comment 17 Nick Clemens (kidclamp) 2023-03-03 16:45:50 UTC 
Created attachment 147717 [details] [review]
Bug 32613: (follow-up) Cache the tables

This shouldn't be expected to change, except maybe after an upgrade,
seems worth caching.

To test:
1 - Apply patch
2 - Confirm the feature still works
Comment 18 Nick Clemens (kidclamp) 2023-03-03 16:47:12 UTC 
This is super cool! I'd love to see it expanded to include common SQL functions: CONCAT, IFNULL, etc. but that's another bug

Added a patch for caching - can be ignored or moved to a separate bug, but seems a small change/benefit
Comment 19 Tomás Cohen Arazi (tcohen) 2023-03-06 17:45:04 UTC 
Created attachment 147795 [details] [review]
Bug 32613: (follow-up) Cache the tables

This shouldn't be expected to change, except maybe after an upgrade,
seems worth caching.

To test:
1 - Apply patch
2 - Confirm the feature still works

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Comment 20 Tomás Cohen Arazi (tcohen) 2023-03-06 17:47:01 UTC 
Pushed to master for 23.05.

Nice work everyone, thanks!
Comment 21 Matt Blenkinsop 2023-03-09 09:43:06 UTC 
Enhancement - not backporting to 22.11.x. 
Setting to Needs documenting. 
Nice work everyone!
Comment 22 Katrin Fischer 2023-05-16 20:13:50 UTC 
Super nice feature, looking forward to using it!
Comment 23 David Cook 2023-12-05 22:29:49 UTC 
Unfortunately, it looks like this broke the indentWithTabs option as noted in bug 35498