From 1f7b231c7071e89f1b679d11aeb8bf45d0c5f9ea Mon Sep 17 00:00:00 2001
From: Chris Cormack
Date: Wed, 9 Nov 2011 08:28:26 +1300
Subject: [PATCH 1/5] Starting work on webservices
---
opac/svc/report | 38 ++++++++++++++++++++++++++++++++++++++
svc/report | 50 ++++++++++++++++++++++++++++++++++++++++++++++++++
2 files changed, 88 insertions(+), 0 deletions(-)
create mode 100755 opac/svc/report
create mode 100755 svc/report
diff --git a/opac/svc/report b/opac/svc/report
new file mode 100755
index 0000000..428657f
--- /dev/null
+++ b/opac/svc/report
@@ -0,0 +1,38 @@
+#!/usr/bin/perl
+
+# Copyright 2011 Chris Cormack
+#
+# This file is part of Koha.
+#
+# Koha is free software; you can redistribute it and/or modify it under the
+# terms of the GNU General Public License as published by the Free Software
+# Foundation; either version 2 of the License, or (at your option) any later
+# version.
+#
+# Koha is distributed in the hope that it will be useful, but WITHOUT ANY
+# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
+# A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License along with
+# Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
+# Suite 330, Boston, MA 02111-1307 USA
+#
+
+use strict;
+use warnings;
+
+use C4::Reports::Guided;
+use JSON;
+use CGI;
+
+my $query = CGI->new();
+my $report=$query->param('id');
+
+print $query->header;
+my ($sql,$type,$name,$notes) = get_saved_report($report);
+my $offset=0;
+my $limit=10;
+my ($sth, $errors) = execute_query($sql, $offset, $limit);
+my $lines = $sth->fetchall_arrayref;
+my $json_text = to_json($lines);
+print $json_text;
diff --git a/svc/report b/svc/report
new file mode 100755
index 0000000..e7c4925
--- /dev/null
+++ b/svc/report
@@ -0,0 +1,50 @@
+#!/usr/bin/perl
+
+# Copyright 2011 Chris Cormack
+#
+# This file is part of Koha.
+#
+# Koha is free software; you can redistribute it and/or modify it under the
+# terms of the GNU General Public License as published by the Free Software
+# Foundation; either version 2 of the License, or (at your option) any later
+# version.
+#
+# Koha is distributed in the hope that it will be useful, but WITHOUT ANY
+# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
+# A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License along with
+# Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
+# Suite 330, Boston, MA 02111-1307 USA
+#
+
+use strict;
+use warnings;
+
+use C4::Auth;
+use C4::Reports::Guided;
+use JSON;
+use CGI;
+
+my $query = CGI->new();
+my $report=$query->param('id');
+
+my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
+ {
+ template_name => "intranet-main.tmpl",
+ query => $query,
+ type => "intranet",
+ authnotrequired => 0,
+ flagsrequired => {
+ catalogue => 1,
+ },
+ }
+);
+print $query->header;
+my ($sql,$type,$name,$notes) = get_saved_report($report);
+my $offset=0;
+my $limit=10;
+my ($sth, $errors) = execute_query($sql, $offset, $limit);
+my $lines = $sth->fetchall_arrayref;
+my $json_text = to_json($lines);
+print $json_text;
--
1.7.4.1
From c00c01a04f692976433e64fd74672aa3cd9c88c0 Mon Sep 17 00:00:00 2001
From: Chris Cormack
Date: Wed, 9 Nov 2011 20:29:26 +1300
Subject: [PATCH 2/5] Working on caching for services
---
opac/svc/report | 26 ++++++++++++++++++++++++++
1 files changed, 26 insertions(+), 0 deletions(-)
diff --git a/opac/svc/report b/opac/svc/report
index 428657f..18215b1 100755
--- a/opac/svc/report
+++ b/opac/svc/report
@@ -28,6 +28,27 @@ use CGI;
my $query = CGI->new();
my $report=$query->param('id');
+my $cache;
+my $usecache=C4::Context->preference('usecache');
+
+if ($usecache){
+ require Koha::Cache;
+ Koha::Cache->import();
+ $cache = Koha::Cache->new ( {'cache_type' => 'memcached',
+ 'cache_servers' => C4::Context->config("memcached_servers")
+ });
+ my $namespace = C4::Context->config("memcached_namespace");
+ my $page = $cache->get_from_cache("$namespace:opac:report:$report");
+ if ($page){
+ print $query->header;
+ print $page;
+ exit;
+ }
+}
+
+
+
+
print $query->header;
my ($sql,$type,$name,$notes) = get_saved_report($report);
my $offset=0;
@@ -36,3 +57,8 @@ my ($sth, $errors) = execute_query($sql, $offset, $limit);
my $lines = $sth->fetchall_arrayref;
my $json_text = to_json($lines);
print $json_text;
+
+if ($usecache){
+ my $namespace = C4::Context->config("memcached_namespace");
+ $cache->set_in_cache("$namespace:opac:report:$report", $json_text, 300);
+}
--
1.7.4.1
From f17c2df3024ffacc4f638d817ec6f61114e96f57 Mon Sep 17 00:00:00 2001
From: Chris Hall
Date: Mon, 21 Nov 2011 12:00:27 +1300
Subject: [PATCH 3/5] Added caching to intranet report webservices, added cache expiry and public options to reports
---
C4/Reports/Guided.pm | 23 +++++++---
installer/data/mysql/updatedatabase.pl | 9 +++-
.../en/modules/reports/guided_reports_start.tt | 25 ++++++++++-
opac/svc/report | 9 ++--
reports/guided_reports.pl | 20 +++++++--
svc/report | 45 +++++++++++++++----
6 files changed, 101 insertions(+), 30 deletions(-)
diff --git a/C4/Reports/Guided.pm b/C4/Reports/Guided.pm
index 75c6c37..8081b32 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/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index 49ef378..5a178bb 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -4551,8 +4551,13 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
}
$DBversion = "3.06.00.xxx";
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
- $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('usecache',0,'If on pages with caching enabled will use caching',NULL,'YesNo')");
- print "Upgradet to $DBversion done (Added syspref usecache, When this preference is turned on pages with with caching support will use caching) \n";
+ $dbh->do("ALTER TABLE saved_sql
+ ADD (
+ cache_expiry INT NOT NULL DEFAULT 300,
+ public BOOLEAN NOT NULL DEFAULT FALSE
+ );
+ ");
+ print "Upgradet to $DBversion done (Added cache_expiry and pulic fields in saved_reports table.) \n";
SetVersion ($DBversion);
}
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 3836a02..b00443c 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
@@ -137,6 +137,8 @@ canned reports and writing custom SQL reports.
Notes |
Author |
Creation Date |
+ Public |
+ [% IF (usecache) %] Cache expiry (seconds) | [% END %]
Saved Results |
Saved SQL |
|
@@ -152,6 +154,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) %]
+True |
+[% ELSE %]
+False |
+[% END %]
+[% IF (usecache) %] [% savedreport.cache_expiry %] | [% END %]
[% IF ( savedreport.date_run ) %][% savedreport.date_run %][% END %]
|
@@ -201,12 +209,19 @@ canned reports and writing custom SQL reports.
Build A Report
|