[Koha-patches] [PATCH] TZ - multiple timezone support

[Joe Atzberger]

Support multiple timezones via Apache SetEnv.  See the perldoc for
admin/env_tz_test.pl on how to configure and test.  Minimal changes
to Context itself.
---
 C4/Context.pm        |    6 ++-
 admin/env_tz_test.pl |  128 ++++++++++++++++++++++++++++++++++++++++++++++++++
 2 files changed, 132 insertions(+), 2 deletions(-)
 create mode 100755 admin/env_tz_test.pl

diff --git a/C4/Context.pm b/C4/Context.pm
index 20e5794..6c9035b 100644
--- a/C4/Context.pm
+++ b/C4/Context.pm
@@ -627,21 +627,23 @@ sub _new_dbh
 
     my $db_name   = $context->config("database");
     my $db_host   = $context->config("hostname");
-    my $db_port   = $context->config("port");
-    $db_port = "" unless defined $db_port;
+    my $db_port   = $context->config("port") || '';
     my $db_user   = $context->config("user");
     my $db_passwd = $context->config("pass");
     # MJR added or die here, as we can't work without dbh
     my $dbh= DBI->connect("DBI:$db_driver:dbname=$db_name;host=$db_host;port=$db_port",
 	$db_user, $db_passwd) or die $DBI::errstr;
+	my $tz = $ENV{TZ};
     if ( $db_driver eq 'mysql' ) { 
         # Koha 3.0 is utf-8, so force utf8 communication between mySQL and koha, whatever the mysql default config.
         # this is better than modifying my.cnf (and forcing all communications to be in utf8)
         $dbh->{'mysql_enable_utf8'}=1; #enable
         $dbh->do("set NAMES 'utf8'");
+        ($tz) and $dbh->do(qq(SET time_zone = "$tz"));
     }
     elsif ( $db_driver eq 'Pg' ) {
 	    $dbh->do( "set client_encoding = 'UTF8';" );
+        ($tz) and $dbh->do(qq(SET TIME ZONE = "$tz"));
     }
     return $dbh;
 }
diff --git a/admin/env_tz_test.pl b/admin/env_tz_test.pl
new file mode 100755
index 0000000..ae70a1a
--- /dev/null
+++ b/admin/env_tz_test.pl
@@ -0,0 +1,128 @@
+#!/usr/bin/perl 
+
+use CGI;
+# use Data::Dumper;
+
+use C4::Context;
+use C4::Auth;
+
+my $q = CGI->new();
+my ($template, $loggedinuser, $cookie) = get_template_and_user({
+	   template_name => "admin/admin-home.tmpl",	# whatever, we don't really use the template anyway.
+			   query => $q,
+			 	type => "intranet",
+	 authnotrequired => 0,
+ 	   flagsrequired => {parameters => 1},
+		       debug => 1,
+});
+
+my $dbh = C4::Context->dbh;
+my  $tz_sth = $dbh->prepare("SHOW VARIABLES LIKE 'time_zone'");
+$tz_sth->execute();
+my $now_sth = $dbh->prepare("SELECT now()");
+$now_sth->execute();
+
+print $q->header(), 
+	$q->html(
+	$q->body(
+	$q->p("This is a test for debugging purposes.  It isn't supposed to look pretty.")
+	.
+	$q->h1("Dumping ENV:") 
+	.
+	join("\n<br\>", map {"$_ = $ENV{$_}"} sort keys %ENV)
+	.
+	$q->h1("Checking different TIME elements in the system:") 
+	. "\n" . $q->p("perl localime: " . localtime)
+	. "\n" . $q->p( "system(date): " . `date`)
+	. "\n" . $q->p( "mysql dbh (Context) time_zone : " .  $tz_sth->fetchrow)
+	. "\n" . $q->p( "mysql dbh (Context) now() : "     . $now_sth->fetchrow)
+	)), "\n";
+
+__END__
+
+=pod
+
+=head1 MULTIPLE TIME ZONE SUPPORT
+
+Koha supports running multiple instances on the same server, even if they need to be homed
+in different timezones.  However, your database must have the timezones installed (see below).
+
+If you are only running one installation of Koha, and want to change the timezone of the server,
+please do NOT use this feature at all, and instead set your system timezone via the OS.  If you 
+are running multiple Kohas, all in the same timezone, do the same. 
+
+Only use this feature if
+you are running multiple Kohas on the same server, and they are not in the same timezone.  
+
+=head2 Perl
+
+For the most part, in execution perl will respect the environmental
+variable TZ, if it is set.  This affects calls to localtime() and other similar functions.
+Remember that the environment will be different for different users, and for cron jobs.  
+See the example below.
+
+=head2 Apache2
+
+We affect the running perl code of Koha with the Apache directive:
+
+SetEnv TZ "US/Central"
+
+This should be added inside the VirtualHost definition for the intended Koha instance.  In 
+almost ALL cases, be sure to set it for both INTRANET and OPAC VirtualHosts.  Remember this
+does not affect the command line environment for any terminal sessions, or your cron jobs.
+
+=head2 Database (mysql)
+
+Your MySQL installation must be configured with appropriate time zones.  This extends beyond
+Koha and affects mysql itself.  On debian, for example, you can use:
+
+	mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
+
+See http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html
+
+=head2 cron/crontab
+
+Current versions of cron in debian allow ENV variables to be set in the lines preceeding 
+scheduled commands.  They will be exported to the environment of the scheduled job.  This is 
+an example for crontab:
+
+	TZ="US/Central"
+	# m h  dom mon dow   command
+	0  1 * * *  /home/liblime/kohaclone/misc/cronjobs/overdue_notices.pl
+	15 * * * *  /home/liblime/kohaclone/misc/cronjobs/process_message_queue.pl
+	*/10 * * * *   /home/liblime/kohaclone/misc/migration_tools/rebuild_zebra.pl -b -z >/dev/null
+
+=head1 EXAMPLE
+
+Try these on a command line to confirm Context is setting time_zone based on TZ:
+
+perl -MC4::Context -e 'my $dbh=C4::Context->dbh; my $tz_sth=$dbh->prepare(q(SHOW VARIABLES LIKE "time_zone"));
+ 	$tz_sth->execute(); print "mysql dbh (Context) time_zone : " .  $tz_sth->fetchrow, "\n";'
+
+export TZ="US/Central";  # or any TZ other than the current one.
+
+perl -MC4::Context -e 'my $dbh=C4::Context->dbh; my $tz_sth=$dbh->prepare(q(SHOW VARIABLES LIKE "time_zone"));
+ 	$tz_sth->execute(); print "mysql dbh (Context) time_zone : " .  $tz_sth->fetchrow, "\n";'
+
+Then update your VirtualHosts to do, for example:
+
+	SetEnv TZ "US/Central"
+
+Reset Apache, then on your intranet check out the debug page:
+
+	cgi-bin/koha/admin/env_tz_test.pl
+
+The TZ that Koha has in effect and the TZ from the database should be displayed at the bottom.
+Hopefully they match what you set.
+
+=head1 BUGS
+
+WARNING: Multiple timezones may or may not work under mod_perl and mod_perl2.  
+
+=head1 AUTHOR
+
+	Joe Atzberger
+	atz at liblime.com
+
+=cut
+
-- 
1.5.5.GIT