[Bug 32613] New: Can we add 'autocompletion' to our SQL reports editor?
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 Bug ID: 32613 Summary: Can we add 'autocompletion' to our SQL reports editor? Change sponsored?: --- Product: Koha Version: master Hardware: All OS: All Status: NEW Severity: enhancement Priority: P5 - low Component: Reports Assignee: koha-bugs@lists.koha-community.org Reporter: martin.renvoize@ptfs-europe.com QA Contact: testopia@bugs.koha-community.org 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? -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 Lisette Scheer <lisettepalouse+koha@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |lisettepalouse+koha@gmail.c | |om -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 Jacob O'Mara <jacob.omara@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |jacob.omara@ptfs-europe.com |ity.org | CC| |jacob.omara@ptfs-europe.com -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 Jacob O'Mara <jacob.omara@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 --- Comment #1 from Jacob O'Mara <jacob.omara@ptfs-europe.com> --- Created attachment 147556 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=147556&action=edit 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 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 --- Comment #2 from Jacob O'Mara <jacob.omara@ptfs-europe.com> --- Created attachment 147557 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=147557&action=edit 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 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 Lucas Gass <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |lucas@bywatersolutions.com --- Comment #3 from Lucas Gass <lucas@bywatersolutions.com> --- Very cool! Can we make it so it won't try and auto complete things like DELETE or INSERT? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 --- Comment #4 from Lucas Gass <lucas@bywatersolutions.com> --- I can get this to autocomplete table names but not column names. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 Jacob O'Mara <jacob.omara@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |ASSIGNED --- Comment #5 from Jacob O'Mara <jacob.omara@ptfs-europe.com> --- 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 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 Lucas Gass <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|ASSIGNED |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 Lucas Gass <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #147557|0 |1 is obsolete| | --- Comment #6 from Lucas Gass <lucas@bywatersolutions.com> --- Created attachment 147610 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=147610&action=edit 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> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 Lucas Gass <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #147556|0 |1 is obsolete| | --- Comment #7 from Lucas Gass <lucas@bywatersolutions.com> --- Created attachment 147611 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=147611&action=edit 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 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 Lucas Gass <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #147610|0 |1 is obsolete| | --- Comment #8 from Lucas Gass <lucas@bywatersolutions.com> --- Created attachment 147612 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=147612&action=edit 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> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 Jacob O'Mara <jacob.omara@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #147611|0 |1 is obsolete| | --- Comment #9 from Jacob O'Mara <jacob.omara@ptfs-europe.com> --- Created attachment 147660 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=147660&action=edit 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) -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 Jacob O'Mara <jacob.omara@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 Jacob O'Mara <jacob.omara@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #147612|0 |1 is obsolete| | --- Comment #10 from Jacob O'Mara <jacob.omara@ptfs-europe.com> --- Created attachment 147661 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=147661&action=edit 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 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 Jacob O'Mara <jacob.omara@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #147661|0 |1 is obsolete| | --- Comment #11 from Jacob O'Mara <jacob.omara@ptfs-europe.com> --- Created attachment 147672 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=147672&action=edit 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 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 Andrew Fuerste-Henry <andrewfh@dubcolib.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 Andrew Fuerste-Henry <andrewfh@dubcolib.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #147660|0 |1 is obsolete| | --- Comment #12 from Andrew Fuerste-Henry <andrewfh@dubcolib.org> --- Created attachment 147673 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=147673&action=edit 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> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 Andrew Fuerste-Henry <andrewfh@dubcolib.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #147672|0 |1 is obsolete| | --- Comment #13 from Andrew Fuerste-Henry <andrewfh@dubcolib.org> --- Created attachment 147674 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=147674&action=edit 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> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 Andrew Fuerste-Henry <andrewfh@dubcolib.org> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |andrewfh@dubcolib.org --- Comment #14 from 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." -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #147673|0 |1 is obsolete| | Attachment #147674|0 |1 is obsolete| | --- Comment #15 from Nick Clemens <nick@bywatersolutions.com> --- Created attachment 147715 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=147715&action=edit 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> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 --- Comment #16 from Nick Clemens <nick@bywatersolutions.com> --- Created attachment 147716 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=147716&action=edit 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> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 --- Comment #17 from Nick Clemens <nick@bywatersolutions.com> --- Created attachment 147717 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=147717&action=edit 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 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |nick@bywatersolutions.com --- Comment #18 from Nick Clemens <nick@bywatersolutions.com> --- 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 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #147717|0 |1 is obsolete| | --- Comment #19 from Tomás Cohen Arazi <tomascohen@gmail.com> --- Created attachment 147795 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=147795&action=edit 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> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)| |23.05.00 released in| | Status|Passed QA |Pushed to master -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 --- Comment #20 from Tomás Cohen Arazi <tomascohen@gmail.com> --- Pushed to master for 23.05. Nice work everyone, thanks! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 Matt Blenkinsop <matt.blenkinsop@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |matt.blenkinsop@ptfs-europe | |.com Status|Pushed to master |Needs documenting --- Comment #21 from Matt Blenkinsop <matt.blenkinsop@ptfs-europe.com> --- Enhancement - not backporting to 22.11.x. Setting to Needs documenting. Nice work everyone! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 Aude Charillon <aude.charillon@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |aude.charillon@ptfs-europe. | |com Documentation| |Aude Charillon contact| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 Caroline Cyr La Rose <caroline.cyr-la-rose@inlibro.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs documenting |RESOLVED Resolution|--- |FIXED Documentation|Aude Charillon |Caroline Cyr La Rose contact| | CC| |caroline.cyr-la-rose@inlibr | |o.com Documentation| |https://gitlab.com/koha-com submission| |munity/koha-manual/-/merge_ | |requests/690 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Summary|Can we add 'autocompletion' |Add auto-completion to our |to our SQL reports editor? |SQL reports editor Text to go in the| |This adds an auto-complete release notes| |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. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 --- Comment #22 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Super nice feature, looking forward to using it! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 Aude Charillon <aude.charillon@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC|aude.charillon@ptfs-europe. | |com | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Text to go in the|This adds an auto-complete |This adds an auto-complete release notes|feature to the SQL reports |feature to the SQL reports |editor. This works for |editor. This works for |tables and columns. For |tables and columns. For |columns you'll need to |columns you'll need to |start with the table name, |start with the table name, |like 'borrowers.'. The |like `borrowers.`. The |editor will then suggest |editor will then suggest |the columns of the |the columns of the |borrowers table. The |`borrowers` table. The |auto-complete feature will |auto-complete feature will |also work when tables have |also work when tables have |been renamed. |been renamed. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 Rebecca Coert <rcoert@arlingtonva.us> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |rcoert@arlingtonva.us -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 Andrew Fuerste-Henry <andrewfh@dubcolib.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |35494 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35494 [Bug 35494] SQL auto-complete should not be triggered by closing a quotation -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 Andrew Fuerste-Henry <andrewfh@dubcolib.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |35498 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35498 [Bug 35498] SQL auto-complete should not prevent use of tab for spacing -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |dcook@prosentient.com.au --- Comment #23 from David Cook <dcook@prosentient.com.au> --- Unfortunately, it looks like this broke the indentWithTabs option as noted in bug 35498 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32613 Lucas Gass <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |35509 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35509 [Bug 35509] Make SQL autocomplete an optional feature -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org