Bug 23390 - Add placeholder syntax for column names
Summary: Add placeholder syntax for column names
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Reports (show other bugs)
Version: master
Hardware: All All
: P5 - low enhancement (vote)
Assignee: Nick Clemens
QA Contact: Testopia
URL:
Keywords:
: 22167 (view as bug list)
Depends on: 23206
Blocks: 23730 27824
  Show dependency treegraph
 
Reported: 2019-07-27 13:09 UTC by Nick Clemens
Modified: 2021-12-13 21:08 UTC (History)
4 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Small patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
This enhancement allows for renaming columns used to trigger batch modification actions in reports. Before this patch, a column had to be called 'itemnumber' to be sent from reports to batch modification. With this enhancement, you can specify `[[itemnumber| Koha_internal_id]]` to allow for a clearer name for the end-user and to allow translation of terms like 'itemnumber' while preserving the batch modification functionality.
Version(s) released in:
19.11.00


Attachments
Bug 23390: Introduce placeholder syntax for report column names (11.46 KB, patch)
2019-07-27 13:41 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 23390: Introduce placeholder syntax for report column names (11.51 KB, patch)
2019-07-30 14:38 UTC, ByWater Sandboxes
Details | Diff | Splinter Review
Bug 23390: (QA follow-up) Don't redeclare variable (817 bytes, patch)
2019-07-30 15:42 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 23390: (follow-up) Quote aliases (929 bytes, patch)
2019-07-31 11:43 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 23390: Introduce placeholder syntax for report column names (11.56 KB, patch)
2019-08-01 19:34 UTC, ByWater Sandboxes
Details | Diff | Splinter Review
Bug 23390: (QA follow-up) Don't redeclare variable (867 bytes, patch)
2019-08-01 19:34 UTC, ByWater Sandboxes
Details | Diff | Splinter Review
Bug 23390: (follow-up) Quote aliases (979 bytes, patch)
2019-08-01 19:34 UTC, ByWater Sandboxes
Details | Diff | Splinter Review
Bug 23390: Introduce placeholder syntax for report column names (11.62 KB, patch)
2019-08-08 16:41 UTC, Katrin Fischer
Details | Diff | Splinter Review
Bug 23390: (QA follow-up) Don't redeclare variable (925 bytes, patch)
2019-08-08 16:41 UTC, Katrin Fischer
Details | Diff | Splinter Review
Bug 23390: (follow-up) Quote aliases (1.01 KB, patch)
2019-08-08 16:41 UTC, Katrin Fischer
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Nick Clemens 2019-07-27 13:09:05 UTC
Bug 23206 added more bactch operations for report results. The use of these is limited by the column names which is not ideal for translations.

We should add a way to transpose column names for these functions.

I suggest [[column|alias]]
Comment 1 Nick Clemens 2019-07-27 13:41:07 UTC
Created attachment 91849 [details] [review]
Bug 23390: Introduce placeholder syntax for report column names

This patch adds the ability to rename columns in MySQL reports using a syntax:
[[column|alias]]

To test:
 1 - Write a report like:
     SELECT cardnumber FROM borrowers WHERE cardnumber LIKE '%9'
 2 - Run report
 3 - Note you can batch modify the patrons
 4 - Update report:
     SELECT cardnumber AS library_barcode FROM borrowers WHERE cardnumber LIKE '%9'
 5 - Run report
 6 - Note batch options are no longer present
 7 - Apply patch
 8 - Run report, no change
 9 - Update report like:
     SELECT [[cardnumber|library_barcode]] FROM borrowers WHERE cardnumber LIKE '%9'
10 - Run report
11 - Batch options work!
12 - Update report like:
    SELECT cardnumber FROM borrowers WHERE cardnumber LIKE '%9'
13 - Verify batch options still work
14 - Set report 'public'
15 - Verify report can be run via JSON URLs:
     http://kohadev.mydnsname.org:8080/cgi-bin/koha/svc/report?id=1
     http://kohadev.mydnsname.org:8081/cgi-bin/koha/svc/report?id=1
Comment 2 George Williams (NEKLS) 2019-07-30 13:35:27 UTC
Tested on ByWater sandbox.  I got this error message when I got to step 10:

The following error was encountered:
The database returned the following error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[[cardnumber|library_barcode]] AS library_barcode FROM borrowers WHERE cardnumbe' at line 1
Please check the log for further details.
Return to previous page

I left the sandbox up - it's just called "sandbox" - if you want to take a look.
Comment 3 Nick Clemens 2019-07-30 14:00:49 UTC
(In reply to George Williams (NEKLS) from comment #2)
> Tested on ByWater sandbox.  I got this error message when I got to step 10:
> 
> The following error was encountered:
> The database returned the following error:
> You have an error in your SQL syntax; check the manual that corresponds to
> your MySQL server version for the right syntax to use near
> '[[cardnumber|library_barcode]] AS library_barcode FROM borrowers WHERE
> cardnumbe' at line 1
> Please check the log for further details.
> Return to previous page
> 
> I left the sandbox up - it's just called "sandbox" - if you want to take a
> look.

I restarted the services on the sandbox and edited the report from:
SELECT [[cardnumber|library_barcode]] AS library_barcode FROM borrowers WHERE cardnumber LIKE '%9'
To:
SELECT [[cardnumber|library_barcode]] FROM borrowers WHERE cardnumber LIKE '%9'

and all appears to work now :-)
Comment 4 ByWater Sandboxes 2019-07-30 14:38:34 UTC
Created attachment 91880 [details] [review]
Bug 23390: Introduce placeholder syntax for report column names

This patch adds the ability to rename columns in MySQL reports using a syntax:
[[column|alias]]

To test:
 1 - Write a report like:
     SELECT cardnumber FROM borrowers WHERE cardnumber LIKE '%9'
 2 - Run report
 3 - Note you can batch modify the patrons
 4 - Update report:
     SELECT cardnumber AS library_barcode FROM borrowers WHERE cardnumber LIKE '%9'
 5 - Run report
 6 - Note batch options are no longer present
 7 - Apply patch
 8 - Run report, no change
 9 - Update report like:
     SELECT [[cardnumber|library_barcode]] FROM borrowers WHERE cardnumber LIKE '%9'
10 - Run report
11 - Batch options work!
12 - Update report like:
    SELECT cardnumber FROM borrowers WHERE cardnumber LIKE '%9'
13 - Verify batch options still work
14 - Set report 'public'
15 - Verify report can be run via JSON URLs:
     http://kohadev.mydnsname.org:8080/cgi-bin/koha/svc/report?id=1
     http://kohadev.mydnsname.org:8081/cgi-bin/koha/svc/report?id=1

Signed-off-by: George Williams <george@nekls.org>
Comment 5 George Williams (NEKLS) 2019-07-30 14:39:10 UTC
Tested again with no problems after restarting services.
Comment 6 Nick Clemens 2019-07-30 15:42:42 UTC
Created attachment 91882 [details] [review]
Bug 23390: (QA follow-up) Don't redeclare variable
Comment 7 Katrin Fischer 2019-07-30 21:08:37 UTC
Hi Nick, when the column alias is more than one word or includes spaces, this doesn't work. Can you please have another look?
Comment 8 Katrin Fischer 2019-07-30 21:09:22 UTC
I've used:

SELECT [[cardnumber|We test]] from borrowers

The database returned the following error:
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'test from borrowers LIMIT 0, 20' at line 1
Please check the log for further details.
Comment 9 Nick Clemens 2019-07-31 11:43:46 UTC
Created attachment 91888 [details] [review]
Bug 23390: (follow-up) Quote aliases
Comment 10 ByWater Sandboxes 2019-08-01 19:34:52 UTC
Created attachment 91917 [details] [review]
Bug 23390: Introduce placeholder syntax for report column names

This patch adds the ability to rename columns in MySQL reports using a syntax:
[[column|alias]]

To test:
 1 - Write a report like:
     SELECT cardnumber FROM borrowers WHERE cardnumber LIKE '%9'
 2 - Run report
 3 - Note you can batch modify the patrons
 4 - Update report:
     SELECT cardnumber AS library_barcode FROM borrowers WHERE cardnumber LIKE '%9'
 5 - Run report
 6 - Note batch options are no longer present
 7 - Apply patch
 8 - Run report, no change
 9 - Update report like:
     SELECT [[cardnumber|library_barcode]] FROM borrowers WHERE cardnumber LIKE '%9'
10 - Run report
11 - Batch options work!
12 - Update report like:
    SELECT cardnumber FROM borrowers WHERE cardnumber LIKE '%9'
13 - Verify batch options still work
14 - Set report 'public'
15 - Verify report can be run via JSON URLs:
     http://kohadev.mydnsname.org:8080/cgi-bin/koha/svc/report?id=1
     http://kohadev.mydnsname.org:8081/cgi-bin/koha/svc/report?id=1

Signed-off-by: George Williams <george@nekls.org>

Signed-off-by: George Williams <george@nekls.org>
Comment 11 ByWater Sandboxes 2019-08-01 19:34:54 UTC
Created attachment 91918 [details] [review]
Bug 23390: (QA follow-up) Don't redeclare variable

Signed-off-by: George Williams <george@nekls.org>
Comment 12 ByWater Sandboxes 2019-08-01 19:34:57 UTC
Created attachment 91919 [details] [review]
Bug 23390: (follow-up) Quote aliases

Signed-off-by: George Williams <george@nekls.org>
Comment 13 Katrin Fischer 2019-08-08 16:35:59 UTC
This will get easily lost if not documented - can we get a manual patch too? :)
Comment 14 Katrin Fischer 2019-08-08 16:41:28 UTC
Created attachment 92095 [details] [review]
Bug 23390: Introduce placeholder syntax for report column names

This patch adds the ability to rename columns in MySQL reports using a syntax:
[[column|alias]]

To test:
 1 - Write a report like:
     SELECT cardnumber FROM borrowers WHERE cardnumber LIKE '%9'
 2 - Run report
 3 - Note you can batch modify the patrons
 4 - Update report:
     SELECT cardnumber AS library_barcode FROM borrowers WHERE cardnumber LIKE '%9'
 5 - Run report
 6 - Note batch options are no longer present
 7 - Apply patch
 8 - Run report, no change
 9 - Update report like:
     SELECT [[cardnumber|library_barcode]] FROM borrowers WHERE cardnumber LIKE '%9'
10 - Run report
11 - Batch options work!
12 - Update report like:
    SELECT cardnumber FROM borrowers WHERE cardnumber LIKE '%9'
13 - Verify batch options still work
14 - Set report 'public'
15 - Verify report can be run via JSON URLs:
     http://kohadev.mydnsname.org:8080/cgi-bin/koha/svc/report?id=1
     http://kohadev.mydnsname.org:8081/cgi-bin/koha/svc/report?id=1

Signed-off-by: George Williams <george@nekls.org>

Signed-off-by: George Williams <george@nekls.org>

Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
Comment 15 Katrin Fischer 2019-08-08 16:41:31 UTC
Created attachment 92096 [details] [review]
Bug 23390: (QA follow-up) Don't redeclare variable

Signed-off-by: George Williams <george@nekls.org>

Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
Comment 16 Katrin Fischer 2019-08-08 16:41:35 UTC
Created attachment 92097 [details] [review]
Bug 23390: (follow-up) Quote aliases

Signed-off-by: George Williams <george@nekls.org>

Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
Comment 17 Martin Renvoize 2019-08-13 13:37:53 UTC
Nice work!

Pushed to master for 19.11.00
Comment 18 Fridolin Somers 2019-09-02 13:03:59 UTC
Depends on Bug 23206 not in 19.05.x
Comment 19 Katrin Fischer 2020-06-30 18:35:13 UTC
*** Bug 22167 has been marked as a duplicate of this bug. ***