| Summary: | Add auto-completion to our SQL reports editor | ||
|---|---|---|---|
| Product: | Koha | Reporter: | Martin Renvoize (ashimema) <martin.renvoize> |
| Component: | Reports | Assignee: | Jacob O'Mara <jacob.omara> |
| Status: | RESOLVED FIXED | QA Contact: | Testopia <testopia> |
| Severity: | enhancement | ||
| Priority: | P5 - low | CC: | andrew, caroline.cyr-la-rose, dcook, jacob.omara, lisettepalouse+koha, lucas, matt.blenkinsop, nick, rcoert |
| Version: | Main | ||
| Hardware: | All | ||
| OS: | All | ||
| 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: | |||
| Bug Depends on: | |||
| Bug Blocks: | 35494, 35498, 35509 | ||
| Attachments: |
Bug 32613: Add autocomplete to SQL reports editor
Bug 32613: Add database tables to autocomplete Bug 32613: Add database tables to autocomplete Bug 32613: Add autocomplete to SQL reports editor Bug 32613: Add database tables to autocomplete Bug 32613: Add autocomplete to SQL reports editor Bug 32613: Add database tables to autocomplete Bug 32613: Add database tables to autocomplete Bug 32613: Add autocomplete to SQL reports editor Bug 32613: Add database tables to autocomplete Bug 32613: Add autocomplete to SQL reports editor Bug 32613: Add database tables to autocomplete Bug 32613: (follow-up) Cache the tables Bug 32613: (follow-up) Cache the tables |
||
|
Description
Martin Renvoize (ashimema)
2023-01-12 11:03:53 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 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 Very cool! Can we make it so it won't try and auto complete things like DELETE or INSERT? I can get this to autocomplete table names but not column names. 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 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> 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 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> 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) 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 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 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> 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> 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." 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> 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> 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 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 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> Pushed to master for 23.05. Nice work everyone, thanks! Enhancement - not backporting to 22.11.x. Setting to Needs documenting. Nice work everyone! Super nice feature, looking forward to using it! Unfortunately, it looks like this broke the indentWithTabs option as noted in bug 35498 |