From 24213703d5906d4bfff1a8e1c9e079bd11523ba7 Mon Sep 17 00:00:00 2001 From: Chris Hall Date: Mon, 21 Nov 2011 16:09:19 +1300 Subject: [PATCH] Bug 7249 : Allow reports to be called through a restful interface Hands back JSON, and supports caching Squashed commit of the following: commit 5c93506079738b9a13139417d3a0734289a4b007 Author: Chris Hall Date: Mon Nov 21 15:41:14 2011 +1300 Fixed bugs when creating reports, added more user friendly input and feedback commit c62430bb358ee4af6ee5331b4d3a2ed67f723032 Author: Chris Hall Date: Mon Nov 21 12:00:27 2011 +1300 Added caching to intranet report webservices, added cache expiry and public options to reports commit 2d89f0777d95b26bf08635782070b6367d0698f3 Author: Chris Cormack Date: Wed Nov 9 20:29:26 2011 +1300 Working on caching for services commit e0511f180cebd81747858ad776433fe3a1cf6854 Author: Chris Cormack Date: Wed Nov 9 08:28:26 2011 +1300 Starting work on webservices Signed-off-by: Chris Cormack Signed-off-by: Katrin Fischer Patch includes database update adding 2 new fields to the saved_sql table. 1) Checked that adding/deleting/editing of sql reports still works and new fields are correctly saved to the database. 2) The saved reports page now lists the new fields correctly. 3) Checking URLs with JSON output - if report is not public http://localhost/cgi-bin/koha/svc/report?id=2 fails, but http://localhost:8080/cgi-bin/koha/svc/report?id=2 works - if report is public, both links work 4) Checking results are cached Created a report listing my borrowers, setting caching to 60 seconds and added new borrowers before doing following tests. - URL doesn't show new borrower immediately - correct - Turning off the usecache system preference updates data - correct - Restarting memcached updates data '/etc/init.d/memcached restart' - correct - Waiting until cache time runs out updates data - correct Signed-off-by: Jared Camins-Esakov Signed-off-by: Katrin Fischer Retested the feature and renewing my sign-off. There are some things that should be noted: - currently there is a hardcoded limit of 10 results for the reports webservice - reports with runtime parameters are currently not supported --- C4/Reports/Guided.pm | 23 ++-- installer/data/mysql/kohastructure.sql | 2 + installer/data/mysql/updatedatabase.pl | 13 ++ .../modules/reports/guided_reports_start.tt | 77 +++++++++++- opac/svc/report | 68 ++++++++++ reports/guided_reports.pl | 117 +++++++++++++++--- svc/report | 79 ++++++++++++ 7 files changed, 352 insertions(+), 27 deletions(-) create mode 100755 opac/svc/report create mode 100755 svc/report diff --git a/C4/Reports/Guided.pm b/C4/Reports/Guided.pm index 75c6c37300..8081b32f60 100644 --- a/C4/Reports/Guided.pm +++ b/C4/Reports/Guided.pm @@ -472,12 +472,12 @@ Returns id of the newly created report =cut sub save_report { - my ( $borrowernumber, $sql, $name, $type, $notes ) = @_; + my ( $borrowernumber, $sql, $name, $type, $notes, $cache_expiry, $public ) = @_; my $dbh = C4::Context->dbh(); $sql =~ s/(\s*\;\s*)$//; # removes trailing whitespace and /;/ my $query = -"INSERT INTO saved_sql (borrowernumber,date_created,last_modified,savedsql,report_name,type,notes) VALUES (?,now(),now(),?,?,?,?)"; - $dbh->do( $query, undef, $borrowernumber, $sql, $name, $type, $notes ); +"INSERT INTO saved_sql (borrowernumber,date_created,last_modified,savedsql,report_name,type,notes,cache_expiry, public) VALUES (?,now(),now(),?,?,?,?,?,?)"; + $dbh->do( $query, undef, $borrowernumber, $sql, $name, $type, $notes, $cache_expiry, $public ); my $id = $dbh->selectrow_array("SELECT max(id) FROM saved_sql WHERE borrowernumber=? AND report_name=?", undef, $borrowernumber, $name); return $id; @@ -488,11 +488,19 @@ sub update_sql { my $sql = shift; my $reportname = shift; my $notes = shift; + my $cache_expiry = shift; + my $public = shift; + + # not entirely a magic number, Cache::Memcached::Set assumed any expiry >= (60*60*24*30) is an absolute unix timestamp (rather than relative seconds) + if( $cache_expiry >= 2592000 ){ + die "Please specify a cache expiry less than 30 days\n"; + } + my $dbh = C4::Context->dbh(); $sql =~ s/(\s*\;\s*)$//; # removes trailing whitespace and /;/ - my $query = "UPDATE saved_sql SET savedsql = ?, last_modified = now(), report_name = ?, notes = ? WHERE id = ? "; + my $query = "UPDATE saved_sql SET savedsql = ?, last_modified = now(), report_name = ?, notes = ?, cache_expiry = ?, public = ? WHERE id = ? "; my $sth = $dbh->prepare($query); - $sth->execute( $sql, $reportname, $notes, $id ); + $sth->execute( $sql, $reportname, $notes, $cache_expiry, $public, $id ); $sth->finish(); } @@ -559,7 +567,8 @@ sub get_saved_reports { my $query = "SELECT saved_sql.id, report_id, report, date_run, date_created, last_modified, savedsql, last_run, report_name, type, notes, - borrowernumber, surname as borrowersurname, firstname as borrowerfirstname + borrowernumber, surname as borrowersurname, firstname as borrowerfirstname, + cache_expiry, public FROM saved_sql LEFT JOIN saved_reports ON saved_reports.report_id = saved_sql.id LEFT OUTER JOIN borrowers USING (borrowernumber)"; @@ -603,7 +612,7 @@ sub get_saved_report { my $sth = $dbh->prepare($query); $sth->execute($id); my $data = $sth->fetchrow_hashref(); - return ( $data->{'savedsql'}, $data->{'type'}, $data->{'report_name'}, $data->{'notes'} ); + return ( $data->{'savedsql'}, $data->{'type'}, $data->{'report_name'}, $data->{'notes'}, $data->{'cache_expiry'}, $data->{'public'} ); } =item create_compound($masterID,$subreportID) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 488a4f0bd3..9a94e0ffea 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1714,6 +1714,8 @@ CREATE TABLE saved_sql ( `report_name` varchar(255) default NULL, `type` varchar(255) default NULL, `notes` text, + `cache_expiry` int NOT NULL default 300, + `public` boolean NOT NULL default FALSE, PRIMARY KEY (`id`), KEY boridx (`borrowernumber`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 2f66e7919c..72e7eb2274 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -5219,6 +5219,19 @@ if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { SetVersion($DBversion); } +$DBversion = "3.09.00.XXX"; +if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { + $dbh->do("ALTER TABLE saved_sql + ADD ( + cache_expiry INT NOT NULL DEFAULT 300, + public BOOLEAN NOT NULL DEFAULT FALSE + ); + "); + print "Upgrade to $DBversion done (Added cache_expiry and public fields in +saved_reports table.)\n"; + SetVersion($DBversion); +} + =head1 FUNCTIONS =head2 TableExists($table) diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt index f516f8abd3..f6ebbfe918 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt @@ -138,6 +138,8 @@ canned reports and writing custom SQL reports.

Notes Author Creation date + Public + [% IF (usecache) %] Cache expiry (seconds) [% END %] Saved results Saved SQL   @@ -153,6 +155,12 @@ canned reports and writing custom SQL reports.

[% savedreport.notes %] [% savedreport.borrowersurname %][% IF ( savedreport.borrowerfirstname ) %], [% savedreport.borrowerfirstname %][% END %] ([% savedreport.borrowernumber %]) [% savedreport.date_created %] +[% IF (savedreport.public) %] +Yes +[% ELSE %] +No +[% END %] +[% IF (usecache) %] [% savedreport.cache_expiry %] [% END %] [% IF ( savedreport.date_run ) %][% savedreport.date_run %][% END %] @@ -199,16 +207,36 @@ canned reports and writing custom SQL reports.

[% IF ( build1 ) %] +[% IF ( cache_error) %] +
+ Please choose a cache_expiry less than 30 days +
+[% END %]

Build a report

-Step 1 of 6: Choose a module to report on +Step 1 of 6: Choose a module to report on,[% IF (usecache) %] Set cache expiry, [% END %] and Choose report visibility
-
+ +[% IF (public) %] +
    • +[% ELSE %] +
    • +[% END %] +[% IF (usecache) %]
  • + + +
    • [% END %] + +
    @@ -222,6 +250,8 @@ canned reports and writing custom SQL reports.

    Build A Report

    + +
    Step 2 of 6: Pick a report type
    1. + +
      + +
      Step 4 of 6: Select criteria to limit on [% FOREACH criteri IN criteria %] @@ -399,6 +433,8 @@ canned reports and writing custom SQL reports.

      + +
      [% FOREACH total_b IN total_by %] @@ -429,6 +465,8 @@ canned reports and writing custom SQL reports.

      + +
      [% FOREACH order_b IN order_by %]
      + +

      You will need to save the report before you can execute it

      @@ -469,6 +509,8 @@ canned reports and writing custom SQL reports.

      + +
      Save your custom report
        @@ -545,6 +587,20 @@ canned reports and writing custom SQL reports.

        [% IF ( reportname ) %] [% ELSE %][% END %] +[% IF (public) %] +
        2. +[% ELSE %] +
        3. +[% END %] +[% IF (usecache) %]
      3. + + +
        4. [% END %]
      5. Edit SQL report
          2. +[% IF (public) %] +
          3. +[% ELSE %] +
          4. +[% END %] +[% IF (usecache) %]
        4. + + +
          5. [% END %]
        @@ -659,6 +729,7 @@ Sub report: