#!/usr/bin/perl

# sqltest.pl - script to test MySQL database connectivity for LON-CAPA
#
# $Id: sqltest.pl,v 1.9 2002/09/12 22:47:28 harris41 Exp $
#
###

=pod

=head1 NAME

B<sqltest.pl> - Test interoperability of the MySQL server for use by LON-CAPA

=cut

# Written to help LON-CAPA (The LearningOnline Network with CAPA)
#
# YEAR=2001
# 9/25,9/30 Scott Harrison
# YEAR=2002
# 5/10,5/11 Scott Harrison
# 8/6/2002 and onwards, Scott Harrison, sharrison@users.sourceforge.net

=pod

=head1 SYNOPSIS

perl sqltest.pl

This script is ordinarily located inside the LON-CAPA source code tree.
This script is normally invoked by test-related targets inside
F<loncapa/loncom/build/Makefile>.

=head1 DESCRIPTION

This program tests the status of the MySQL database needed by the LON-CAPA
system.  As with the other LON-CAPA test scripts, when reasonable, I try
to avoid importing functionality from other LON-CAPA modules so as to
avoid indirectly testing software dependencies.

=head2 ORGANIZATION OF THIS PERL SCRIPT

The script is organized into the following sections.

=over 4

=item 1.

Process version information of this file.

=item 2.

Modules used by this script,

=item 3.

Initializations.

=item 4.

Read in current configuration.

=item 5.

Is this a library or access server?

This step in the script is both a sanity check and also allows for other
future LON-CAPA server types (e.g. "admin", "backup", "firewall") to not
be required to have MySQL.

=item 6.

Make sure that the database can be accessed.

If not, a variety of possible problems should be tested for, and a status
report should be issued to standard output.

=item 7.

Close database connection.

This part of the script is only reached if the database was successfully
connected to.

=item 8.

Subroutines.

B<configuration_scan> - look for PerlSetVar and store inside hash variable.

=back

=head1 STATUS

Ratings: 1=horrible 2=poor 3=fair 4=good 5=excellent

=over 4

=item Organization

5

=item Functionality

4

=item Has it been tested?

3

=back

=head1 AUTHOR

Scott Harrison, sharrison@users.sourceforge.net, 2001, 2002

This software is distributed under the General Public License,
version 2, June 1991 (which is the same terms as LON-CAPA).

This 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.

This software 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 this software; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

=cut

# =================================== Process version information of this file.
my $VERSION = sprintf("%d.%02d", q$Revision: 1.9 $ =~ /(\d+)\.(\d+)/);

print('Running sqltest.pl, version '.$VERSION.'.'."\n");
print('(Test interoperability of the MySQL server for use by LON-CAPA.)'."\n");

# ================================================ Modules used by this script.
use strict;
use DBI;    # Needed to interface with the database.

# ============================================================ Initializations.
$|=1;
print 'Probing for SQL metadata database'."\n\n";

# ============================================== Read in current configuration.
my %perlvar;

my $webconfdir='/etc/httpd/conf/';

# Scanning the standard loncapa configuration files.
&configuration_scan(\%perlvar,$webconfdir.'loncapa.conf');
&configuration_scan(\%perlvar,$webconfdir.'loncapa_apache.conf');
# Note that the authoritative value for 'lonSqlAccess' comes from
# loncapa_apache.conf; this is why loncapa_apache.conf is scanned
# after loncapa.conf (in case a system administrator mistakenly
# specifies lonSqlAccess inside loncapa.conf).

# ========================================== Is lonSqlAccess defined correctly?
unless ($perlvar{'lonSqlAccess'} and $perlvar{'lonSqlAccess'}!~/^\{\[\[\[\[/) {
    print('**** ERROR **** The lonSqlAccess variable was not defined '.
	  'correctly inside '.$webconfdir.'loncapa_apache.conf'."\n");
    exit(1);
}
unless ($perlvar{'lonSqlAccess'} eq 'localhostkey') {
    print('**** WARNING **** The lonSqlAccess variable is not set to the '.
	  'standard expected value of "localhostkey"'."\n");
}

# ========================================= Is this a library or access server?
unless ($perlvar{'lonRole'} eq 'library' or $perlvar{'lonRole'} eq 'access') {
    print('**** NOTE **** SQL testing can only be run on a library or access '.
	  'server.  Skipping test.'."\n");
    exit(0);
}

# ================================ Make sure that the database can be accessed.
my $dbh;
{
    unless (
	    $dbh = DBI->connect("DBI:mysql:loncapa","www",
				$perlvar{'lonSqlAccess'},
				{RaiseError=>0,PrintError=>0})
	    ) {
	print('Cannot connect to database!'."\n");
	# ------------------------------------ Check through possible problems.
	my $problem_flag=0;
	my $checkDBImodule=`perl pmvers DBI 2>/dev/null`;
	my $checkMYSQLmodule=`perl pmvers Mysql 2>/dev/null`;
	my $checkprocess=`/etc/rc.d/init.d/mysqld status`;
	my $process_ecode=system('/etc/rc.d/init.d/mysqld status');

	# ---------------------------------------------- Issue a status report.
	if (!$checkDBImodule) {
	    print('**** ERROR **** SYSTEM IS MISSING THE DBI PERL '.
		  'MODULE (DBI.pm)'."\n");
	    $problem_flag=1;
	}
	if (!$checkMYSQLmodule) {
	    print('**** ERROR **** SYSTEM IS MISSING THE MYSQL PERL '.
		  'MODULE (Mysql.pm)'."\n");
	    $problem_flag=1;
	}
	if (!-e '/etc/rc.d/init.d/mysqld') {
	    print('**** ERROR **** IT APPEARS THAT THE MYSQL SERVER HAS NOT '.
		  'BEEN INSTALLED'."\n");
	    $problem_flag=1;
	}
	if ($checkprocess=~/is stopped/) {
	    print('**** ERROR **** IT APPEARS THAT THE MYSQL SERVER IS NOT '.
		  'RUNNING'."\n");
	    print(<<END);
To fix temporarily, run the command:
   /etc/rc.d/init.d/mysqld start

You may also want to check and see that mysqld is started on boot time.

   /sbin/chkconfig --list mysqld

This is bad output:
mysqld         0:off  1:off  2:off  3:off  4:off  5:off  6:off

This is good output:
mysqld         0:off  1:off  2:off  3:on   4:on   5:on   6:off

To configure mysqld to launch correctly upon system startup, type the command:
   /sbin/chkconfig --level 345 mysqld on
END
	    $problem_flag=1;
	}
	if ($checkprocess=~/mysqld dead but subsys locked/) {
	    print('**** ERROR **** IT APPEARS THAT THE MYSQLD PROCESSES'.
		  'WERE SOMEHOW TERMINATED'."\n");
	    print(<<END);
To fix temporarily, run the command:
   /etc/rc.d/init.d/mysqld restart
Double-check that your mysqld processes are running by using the command:
   ps auxwww | grep mysqld

Note that something really bad probably happened on your system to abnormally
shutdown the mysqld processes.
END
	    $problem_flag=1;
	}
	if ($process_ecode) { # The exit code for mysqld status was abnormal.
	    print('**** ERROR **** MYSQLD IS NOT AVAILABLE'."\n");
	    print(<<END);
To check (and fix), try running these commands:
     /etc/rc.d/init.d/mysqld start
     /etc/rc.d/init.d/mysqld status

You may also want to check and see that mysqld is started on boot time.
   /sbin/chkconfig --list mysqld
If everything is off, you should run "/sbin/chkconfig --level 345 mysqld on".
END
            $problem_flag=1;
	}
	unless ($problem_flag) {
	    print('**** ERROR **** IT APPEARS THAT WWW@LOCALHOST AND/OR '.
		  'PASSWORD ARE NOT CORRECTLY ENABLED'."\n");
	    print(<<END);
This is because all other known problems have been checked for.
By process of elimination, the assumption is that www\@localhost
must not be connecting to the database (a system administrator
at your institution may want to look at other possibilities however).

To diagnose, try logging in from the command line with
       mysql -u www -p mysql
and use the lonSqlAccess password
listed in loncapa_apache.conf (it normally is set to 'localhostkey').
If logging in fails to work, one possible approach is to REMOVE the
www\@localhost MySQL user.
[shell]\$ mysql -u root -p mysql
mysql> delete from user where user='www'
And then, you will need to repeat the MySQL configuration steps described at:
       http://install.lon-capa.org/docs/install/index.html

**** NOTE **** ANOTHER possibility is that you are not running
a compatible set of DBI, Mysql perl modules, and MySQL server software.
END
	}
	exit(1);
    }
}
%perlvar=(); # clear memory

print('SQL metadata database is found and is accessible'."\n");

# ================================================== Close database connection.
$dbh->disconnect();

# ================================================================ Subroutines.

# --------- configuration_scan: look for PerlSetVar and store in hash variable.
sub configuration_scan {
    my ($storagehashref,$filename)=@_;
    open(CONFIG,$filename) or
	(print("Can't read $filename\n") && exit(1));
    while (my $configline=<CONFIG>) {
	if ($configline =~ /^[^\#]*PerlSetVar/) {
	    my ($dummy,$varname,$varvalue)=split(/\s+/,$configline);
	    chomp($varvalue);
	    $storagehashref->{$varname}=$varvalue;
	}
    }
    close(CONFIG);
}