Bug 10635 - Can't query for CREATE/DELETE in action_logs with SQL reports
Summary: Can't query for CREATE/DELETE in action_logs with SQL reports
Status: NEW
Alias: None
Product: Koha
Classification: Unclassified
Component: Reports (show other bugs)
Version: Main
Hardware: All All
: P5 - low normal (vote)
Assignee: Galen Charlton
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2013-07-23 20:03 UTC by Nicole C. Engard
Modified: 2019-11-02 11:18 UTC (History)
4 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments
error (47.12 KB, image/png)
2013-07-23 20:03 UTC, Nicole C. Engard
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Nicole C. Engard 2013-07-23 20:03:32 UTC
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.
Comment 1 Galen Charlton 2013-07-23 23:39:51 UTC
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.
Comment 2 Fridolin Somers 2013-11-19 10:05:05 UTC
(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
Comment 3 Nicole C. Engard 2013-11-19 14:43:10 UTC
Of course there are ways around it - but I'm a fan of fixing the problem instead of working around it :)
Comment 4 Sophie MEYNIEUX 2019-10-04 08:51:41 UTC
Same thing with DELETE
Comment 5 Emmanuel Bétemps 2019-10-04 09:06:44 UTC
Is it conceivable that the actions' name (DELETE and CREATE) could be changed and replaced with non-forbidden words ?