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: Main
Hardware: All All
: P5 - low enhancement
Assignee: Nick Clemens (kidclamp)
QA Contact: Testopia
URL:
Keywords:
: 22167 (view as bug list)
Depends on: 23206
Blocks: 23730 27824
  Show dependency tree / graph
 
Reported: 2019-07-27 13:09 UTC by Nick Clemens (kidclamp)
Modified: 2021-12-13 21:08 UTC (History)
3 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
Circulation function:

Attachments
Bug 23390: Introduce placeholder syntax for report column names (11.46 KB, patch)
2019-07-27 13:41 UTC, Nick Clemens (kidclamp) 		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 (kidclamp) 		Details | Diff | Splinter Review
Bug 23390: (follow-up) Quote aliases (929 bytes, patch)
2019-07-31 11:43 UTC, Nick Clemens (kidclamp) 		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
Show Obsolete (7) 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 Nick Clemens (kidclamp) 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 (kidclamp) 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 (kidclamp) 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 (kidclamp) 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 (kidclamp) 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 (ashimema) 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. ***