[Bug 16219] New: Runreport.pl should allow sql paramters to be passed on the command line.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16219 Bug ID: 16219 Summary: Runreport.pl should allow sql paramters to be passed on the command line. Change sponsored?: --- Product: Koha Version: 3.18 Hardware: All OS: All Status: NEW Severity: enhancement Priority: P5 - low Component: Command-line Utilities Assignee: gmcharlt@gmail.com Reporter: barton@bywatersolutions.com QA Contact: testopia@bugs.koha-community.org CC: robin@catalyst.net.nz Runreport.pl has a major limitation in that it is not capable of running reports that use sql parameters: Runreport will run this: SELECT p.surname, p.firstname, p.email, p.cardnumber, h.waitingdate AS 'hold date', i.barcode, b.title FROM reserves h LEFT JOIN borrowers p USING (borrowernumber) LEFT JOIN items i USING (itemnumber) LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber) WHERE h.waitingdate IS NOT NULL AND h.branchcode='ABC' but if your report looks like this: SELECT p.surname, p.firstname, p.email, p.cardnumber, h.waitingdate AS 'hold date', i.barcode, b.title FROM reserves h LEFT JOIN borrowers p USING (borrowernumber) LEFT JOIN items i USING (itemnumber) LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber) WHERE h.waitingdate IS NOT NULL AND h.branchcode=<<Waiting at|branches>> We should be able to supply a --sql_params argument one or more times to provide the sql parameters. runreport.pl --to="someone@someplace.com" --subject="Patrons with Holds Waiting at Library" --format=csv --sql_params="ABC" 1 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16219 Owen Leonard <oleonard@myacpl.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Version|3.18 |master -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16219 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |Needs Signoff Patch complexity|--- |Trivial patch -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16219 --- Comment #1 from Nick Clemens <nick@bywatersolutions.com> --- Created attachment 78434 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=78434&action=edit Bug 16219: Add 'params' parameter to runreport.pl To test: 1 - Apply patch 2 - Write a report that takes no variables 3 - Run this using runreport.pl perl misc/cronjobs/runreport.pl 1 4 - Confirm results as expected 5 - Write a report that takes one or more variables 6 - Run using runreport.pl, but pass no params 7 - You should get a SQL error 8 - Now run passing the correct number of params perl misc/cronjobs/runreport.pl --param first --param=2nd 2 9 - Verify results as expected 10 - call runreport with no parameters perl misc/cronjobs/runreport.pl 11 - Verify documentation is correct and helpful -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16219 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|gmcharlt@gmail.com |nick@bywatersolutions.com CC| |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=16219 Chris Slone <cslone@camdencountylibrary.org> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |cslone@camdencountylibrary. | |org -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16219 Claire Gravely <claire_gravely@hotmail.com> 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=16219 Claire Gravely <claire_gravely@hotmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #78434|0 |1 is obsolete| | --- Comment #2 from Claire Gravely <claire_gravely@hotmail.com> --- Created attachment 90363 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=90363&action=edit Bug 16219: Add 'params' parameter to runreport.pl To test: 1 - Apply patch 2 - Write a report that takes no variables 3 - Run this using runreport.pl perl misc/cronjobs/runreport.pl 1 4 - Confirm results as expected 5 - Write a report that takes one or more variables 6 - Run using runreport.pl, but pass no params 7 - You should get a SQL error 8 - Now run passing the correct number of params perl misc/cronjobs/runreport.pl --param first --param=2nd 2 9 - Verify results as expected 10 - call runreport with no parameters perl misc/cronjobs/runreport.pl 11 - Verify documentation is correct and helpful Signed-off-by: Claire Gravely <claire.gravely@bsz-bw.de> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16219 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |katrin.fischer@bsz-bw.de Summary|Runreport.pl should allow |Runreport.pl should allow |sql paramters to be passed |SQL parameters to be passed |on the command line. |on the command line -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16219 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Failed QA --- Comment #3 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- This works, but feels a little hacky: - When the report expects params, the SQL message is output. We could possibly print a nicer error message instead with the number of params to expected. - When the report is built with reusable variables, you need to enter each of those on command line. If not easy to fix, hinting at it in the help might be good. My example query was: select * from items where holdingbranch = <<b|branches>> and homebranch = <<b|branches>> and itype= <<i>> So b will be shown as 1 input field, but will be required twice in the command: ./misc/cronjobs/runreport.pl 1 --param=CPL --param=BK DBD::mysql::st execute failed: called with 4 bind variables when 5 are needed [for Statement "select * from items where holdingbranch = ? and homebranch = ? and itype= ? LIMIT ?, ?" with ParamValues: 0=Null!, 1=Null!, 2=Null!, 3=Null!, 4=Null!] at /home/vagrant/kohaclone/C4/Reports/Guided.pm line 544. NO OUTPUT: 0 results from execute_query ./misc/cronjobs/runreport.pl 1 --param=CPL --param=CPL --param=BK Works. Pushing back to Nick for comment. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16219 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Failed QA |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16219 --- Comment #4 from Nick Clemens <nick@bywatersolutions.com> --- Created attachment 91668 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=91668&action=edit Bug 16219: (follow-up) Nicer message if params missing and note that they are not combined -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16219 --- Comment #5 from Nick Clemens <nick@bywatersolutions.com> --- (In reply to Katrin Fischer from comment #3)
This works, but feels a little hacky: - When the report expects params, the SQL message is output. We could possibly print a nicer error message instead with the number of params to expected.
Ok, done
- When the report is built with reusable variables, you need to enter each of those on command line. If not easy to fix, hinting at it in the help might be good.
Noted in the POD, really reports needs an update and move to objects, this is a helpful patch mostly for sysadmins (and to prevent libraries having to have hardcoded cron version reports and staff side version with params) -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16219 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |m.de.rooy@rijksmuseum.nl Status|Signed Off |BLOCKED --- Comment #6 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- QA: Looking here -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16219 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|BLOCKED |Passed QA -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16219 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #90363|0 |1 is obsolete| | Attachment #91668|0 |1 is obsolete| | --- Comment #7 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Created attachment 92113 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=92113&action=edit Bug 16219: Add 'params' parameter to runreport.pl To test: 1 - Apply patch 2 - Write a report that takes no variables 3 - Run this using runreport.pl perl misc/cronjobs/runreport.pl 1 4 - Confirm results as expected 5 - Write a report that takes one or more variables 6 - Run using runreport.pl, but pass no params 7 - You should get a SQL error 8 - Now run passing the correct number of params perl misc/cronjobs/runreport.pl --param first --param=2nd 2 9 - Verify results as expected 10 - call runreport with no parameters perl misc/cronjobs/runreport.pl 11 - Verify documentation is correct and helpful Signed-off-by: Claire Gravely <claire.gravely@bsz-bw.de> Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16219 --- Comment #8 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Created attachment 92114 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=92114&action=edit Bug 16219: (follow-up) Nicer message if params missing and note that they are not combined Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16219 --- Comment #9 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Created attachment 92115 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=92115&action=edit Bug 16219: (QA follow-up) Tiny regex change The parameter <<>> does not work. There should be text inside. The construction .*? does not look good completely. Replacing it by the stricter [^>]+ So, no greater than's and at least one. Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16219 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- QA Contact|testopia@bugs.koha-communit |m.de.rooy@rijksmuseum.nl |y.org | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16219 --- Comment #10 from Nick Clemens <nick@bywatersolutions.com> --- (In reply to Marcel de Rooy from comment #9)
Created attachment 92115 [details] [review] Bug 16219: (QA follow-up) Tiny regex change
The parameter <<>> does not work. There should be text inside. The construction .*? does not look good completely. Replacing it by the stricter [^>]+ So, no greater than's and at least one.
Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Thanks Marcel! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16219 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to master Version(s)| |19.11.00 released in| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16219 --- Comment #11 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Nice work! Pushed to master for 19.11.00 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16219 Fridolin SOMERS <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to master |Pushed to stable Version(s)|19.11.00 |19.11.00,19.05.04 released in| | CC| |fridolin.somers@biblibre.co | |m --- Comment #12 from Fridolin SOMERS <fridolin.somers@biblibre.com> --- Small but powerfull enhancement, I choose to backport. Pushed to 19.05.x for 19.05.04 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16219 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords| |Manual, | |release-notes-needed CC| |martin.renvoize@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=16219 Lucas Gass <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |lucas@bywatersolutions.com --- Comment #13 from Lucas Gass <lucas@bywatersolutions.com> --- enhancement, not backporting to 18.11.x -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16219 Arthur Suzuki <arthur.suzuki@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |arthur.suzuki@biblibre.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16219 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords|release-notes-needed | -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org