| Summary: | Can't query for CREATE/DELETE in action_logs with SQL reports | ||
|---|---|---|---|
| Product: | Koha | Reporter: | Nicole C. Engard <nengard> |
| Component: | Reports | Assignee: | Galen Charlton <gmcharlt> |
| Status: | NEW --- | QA Contact: | Testopia <testopia> |
| Severity: | normal | ||
| Priority: | P5 - low | CC: | e.betemps, fridolin.somers, sophie.meynieux |
| Version: | Main | ||
| Hardware: | All | ||
| OS: | All | ||
| Change sponsored?: | --- | Patch complexity: | --- |
| Documentation contact: | Documentation submission: | ||
| Text to go in the release notes: | Version(s) released in: | ||
| Circulation function: | |||
| Attachments: | error | ||
The restriction as a whole should not be removed to avoid the risk of a staff user inadvertently creating new tables in the database, but clearly it needs to be made a bit smarter. (In reply to Nicole C. Engard from comment #0) > Created attachment 19888 [details] You may use a like : SELECT p.cardnumber, p.surname, p.firstname, p.streetnumber, p.address, p.address2, p.city, p.state, p.zipcode, p.email, p.phone, p.dateenrolled, p.smsalertnumber, l.action, l.timestamp AS 'action taken' FROM borrowers p LEFT JOIN action_logs l ON (p.borrowernumber=l.object) WHERE l.module='MEMBERS' AND date(l.timestamp) BETWEEN date_sub(curdate(), interval 1 week) AND curdate() AND (l.action LIKE 'MODIFY' OR l.action LIKE 'CREAT_') My 2c Of course there are ways around it - but I'm a fan of fixing the problem instead of working around it :) Same thing with DELETE Is it conceivable that the actions' name (DELETE and CREATE) could be changed and replaced with non-forbidden words ? |
Created attachment 19888 [details] error I wrote this report: SELECT p.cardnumber, p.surname, p.firstname, p.streetnumber, p.address, p.address2, p.city, p.state, p.zipcode, p.email, p.phone, p.dateenrolled, p.smsalertnumber, l.action, l.timestamp as 'action taken' FROM borrowers p left join action_logs l on (p.borrowernumber=l.object) where l.module='MEMBERS' and date(l.timestamp) between date_sub(curdate(), interval 1 week) and curdate() and l.action in ('MODIFY','CREATE') And get the attached error which says I can't use the word 'CREATE' but that's the word in the table ... so maybe we need to remove this restriction or change the wording in the table so that it can be queried.