Annotation of loncom/lonsql, revision 1.40
1.1 harris41 1: #!/usr/bin/perl
1.39 harris41 2:
3: # The LearningOnline Network
1.40 ! harris41 4: # lonsql - LON TCP-MySQL-Server Daemon for handling database requests.
1.39 harris41 5: #
6: # YEAR=2000
1.2 harris41 7: # lonsql-based on the preforker:harsha jagasia:date:5/10/00
1.4 www 8: # 7/25 Gerd Kortemeyer
1.6 harris41 9: # many different dates Scott Harrison
1.39 harris41 10: # YEAR=2001
11: # many different dates Scott Harrison
1.7 harris41 12: # 03/22/2001 Scott Harrison
1.36 www 13: # 8/30 Gerd Kortemeyer
1.39 harris41 14: # 10/17,11/28,11/29 Scott Harrison
15: #
1.40 ! harris41 16: # $Id: lonsql,v 1.39 2001/11/29 13:53:56 harris41 Exp $
1.39 harris41 17: ###
18:
1.40 ! harris41 19: ###############################################################################
! 20: ## ##
! 21: ## ORGANIZATION OF THIS PERL SCRIPT ##
! 22: ## 1. Modules used ##
! 23: ## 2. Enable find subroutine ##
! 24: ## 3. Read httpd access.conf and get variables ##
! 25: ## 4. Make sure that database can be accessed ##
! 26: ## 5. Make sure this process is running from user=www ##
! 27: ## 6. Check if other instance is running ##
! 28: ## 7. POD (plain old documentation, CPAN style) ##
! 29: ## ##
! 30: ###############################################################################
1.36 www 31:
1.2 harris41 32: use IO::Socket;
33: use Symbol;
1.1 harris41 34: use POSIX;
35: use IO::Select;
36: use IO::File;
37: use Socket;
38: use Fcntl;
39: use Tie::RefHash;
40: use DBI;
41:
1.9 harris41 42: my @metalist;
43: # ----------------- Code to enable 'find' subroutine listing of the .meta files
44: require "find.pl";
45: sub wanted {
46: (($dev,$ino,$mode,$nlink,$uid,$gid) = lstat($_)) &&
47: -f _ &&
1.34 harris41 48: /^.*\.meta$/ && !/^.+\.\d+\.[^\.]+\.meta$/ &&
1.9 harris41 49: push(@metalist,"$dir/$_");
50: }
51:
1.1 harris41 52: $childmaxattempts=10;
1.2 harris41 53: $run =0;#running counter to generate the query-id
54:
1.1 harris41 55: # ------------------------------------ Read httpd access.conf and get variables
56: open (CONFIG,"/etc/httpd/conf/access.conf") || die "Can't read access.conf";
57:
58: while ($configline=<CONFIG>) {
59: if ($configline =~ /PerlSetVar/) {
60: my ($dummy,$varname,$varvalue)=split(/\s+/,$configline);
61: chomp($varvalue);
62: $perlvar{$varname}=$varvalue;
63: }
64: }
65: close(CONFIG);
1.4 www 66:
1.31 harris41 67: # ------------------------------------- Make sure that database can be accessed
68: {
69: my $dbh;
70: unless (
71: $dbh = DBI->connect("DBI:mysql:loncapa","www",$perlvar{'lonSqlAccess'},{ RaiseError =>0,PrintError=>0})
72: ) {
73: print "Cannot connect to database!\n";
1.38 harris41 74: $emailto="$perlvar{'lonAdmEMail'},$perlvar{'lonSysEMail'}";
75: $subj="LON: $perlvar{'lonHostID'} Cannot connect to database!";
76: system("echo 'Cannot connect to MySQL database!' |\
77: mailto $emailto -s '$subj' > /dev/null");
78: exit 1;
1.31 harris41 79: }
80: else {
81: $dbh->disconnect;
82: }
83: }
84:
1.4 www 85: # --------------------------------------------- Check if other instance running
86:
87: my $pidfile="$perlvar{'lonDaemons'}/logs/lonsql.pid";
88:
89: if (-e $pidfile) {
90: my $lfh=IO::File->new("$pidfile");
91: my $pide=<$lfh>;
92: chomp($pide);
93: if (kill 0 => $pide) { die "already running"; }
94: }
1.1 harris41 95:
96: # ------------------------------------------------------------- Read hosts file
1.2 harris41 97: $PREFORK=4; # number of children to maintain, at least four spare
1.1 harris41 98:
99: open (CONFIG,"$perlvar{'lonTabDir'}/hosts.tab") || die "Can't read host file";
100:
101: while ($configline=<CONFIG>) {
102: my ($id,$domain,$role,$name,$ip)=split(/:/,$configline);
103: chomp($ip);
104:
1.2 harris41 105: $hostip{$ip}=$id;
1.1 harris41 106: if ($id eq $perlvar{'lonHostID'}) { $thisserver=$name; }
107:
1.2 harris41 108: $PREFORK++;
1.1 harris41 109: }
110: close(CONFIG);
1.36 www 111:
112: $PREFORK=int($PREFORK/4);
1.1 harris41 113:
1.2 harris41 114: $unixsock = "mysqlsock";
115: my $localfile="$perlvar{'lonSockDir'}/$unixsock";
116: my $server;
117: unlink ($localfile);
118: unless ($server=IO::Socket::UNIX->new(Local =>"$localfile",
119: Type => SOCK_STREAM,
120: Listen => 10))
121: {
122: print "in socket error:$@\n";
123: }
1.1 harris41 124:
125: # -------------------------------------------------------- Routines for forking
126: # global variables
1.2 harris41 127: $MAX_CLIENTS_PER_CHILD = 5; # number of clients each child should process
1.1 harris41 128: %children = (); # keys are current child process IDs
1.2 harris41 129: $children = 0; # current number of children
1.1 harris41 130:
131: sub REAPER { # takes care of dead children
132: $SIG{CHLD} = \&REAPER;
133: my $pid = wait;
1.2 harris41 134: $children --;
135: &logthis("Child $pid died");
1.1 harris41 136: delete $children{$pid};
137: }
138:
139: sub HUNTSMAN { # signal handler for SIGINT
140: local($SIG{CHLD}) = 'IGNORE'; # we're going to kill our children
141: kill 'INT' => keys %children;
142: my $execdir=$perlvar{'lonDaemons'};
143: unlink("$execdir/logs/lonsql.pid");
144: &logthis("<font color=red>CRITICAL: Shutting down</font>");
1.2 harris41 145: $unixsock = "mysqlsock";
146: my $port="$perlvar{'lonSockDir'}/$unixsock";
147: unlink(port);
1.1 harris41 148: exit; # clean up with dignity
149: }
150:
151: sub HUPSMAN { # signal handler for SIGHUP
152: local($SIG{CHLD}) = 'IGNORE'; # we're going to kill our children
153: kill 'INT' => keys %children;
154: close($server); # free up socket
155: &logthis("<font color=red>CRITICAL: Restarting</font>");
156: my $execdir=$perlvar{'lonDaemons'};
1.2 harris41 157: $unixsock = "mysqlsock";
158: my $port="$perlvar{'lonSockDir'}/$unixsock";
159: unlink(port);
1.1 harris41 160: exec("$execdir/lonsql"); # here we go again
161: }
162:
163: sub logthis {
164: my $message=shift;
165: my $execdir=$perlvar{'lonDaemons'};
1.2 harris41 166: my $fh=IO::File->new(">>$execdir/logs/lonsqlfinal.log");
1.1 harris41 167: my $now=time;
168: my $local=localtime($now);
169: print $fh "$local ($$): $message\n";
170: }
171: # ---------------------------------------------------- Fork once and dissociate
172: $fpid=fork;
173: exit if $fpid;
174: die "Couldn't fork: $!" unless defined ($fpid);
175:
176: POSIX::setsid() or die "Can't start new session: $!";
177:
178: # ------------------------------------------------------- Write our PID on disk
179:
180: $execdir=$perlvar{'lonDaemons'};
181: open (PIDSAVE,">$execdir/logs/lonsql.pid");
182: print PIDSAVE "$$\n";
183: close(PIDSAVE);
184: &logthis("<font color=red>CRITICAL: ---------- Starting ----------</font>");
185:
186: # ----------------------------- Ignore signals generated during initial startup
187: $SIG{HUP}=$SIG{USR1}='IGNORE';
1.2 harris41 188: # ------------------------------------------------------- Now we are on our own
189: # Fork off our children.
190: for (1 .. $PREFORK) {
191: make_new_child();
1.1 harris41 192: }
193:
1.2 harris41 194: # Install signal handlers.
1.1 harris41 195: $SIG{CHLD} = \&REAPER;
196: $SIG{INT} = $SIG{TERM} = \&HUNTSMAN;
197: $SIG{HUP} = \&HUPSMAN;
198:
199: # And maintain the population.
200: while (1) {
201: sleep; # wait for a signal (i.e., child's death)
1.2 harris41 202: for ($i = $children; $i < $PREFORK; $i++) {
203: make_new_child(); # top up the child pool
1.1 harris41 204: }
205: }
206:
1.2 harris41 207:
1.1 harris41 208: sub make_new_child {
209: my $pid;
210: my $sigset;
1.2 harris41 211:
1.1 harris41 212: # block signal for fork
213: $sigset = POSIX::SigSet->new(SIGINT);
214: sigprocmask(SIG_BLOCK, $sigset)
215: or die "Can't block SIGINT for fork: $!\n";
216:
1.2 harris41 217: die "fork: $!" unless defined ($pid = fork);
218:
1.1 harris41 219: if ($pid) {
220: # Parent records the child's birth and returns.
221: sigprocmask(SIG_UNBLOCK, $sigset)
222: or die "Can't unblock SIGINT for fork: $!\n";
223: $children{$pid} = 1;
224: $children++;
225: return;
226: } else {
1.2 harris41 227: # Child can *not* return from this subroutine.
1.1 harris41 228: $SIG{INT} = 'DEFAULT'; # make SIGINT kill us as it did before
229:
230: # unblock signals
231: sigprocmask(SIG_UNBLOCK, $sigset)
232: or die "Can't unblock SIGINT for fork: $!\n";
1.2 harris41 233:
234:
235: #open database handle
236: # making dbh global to avoid garbage collector
1.1 harris41 237: unless (
1.31 harris41 238: $dbh = DBI->connect("DBI:mysql:loncapa","www",$perlvar{'lonSqlAccess'},{ RaiseError =>0,PrintError=>0})
1.1 harris41 239: ) {
1.30 harris41 240: sleep(10+int(rand(20)));
1.1 harris41 241: &logthis("<font color=blue>WARNING: Couldn't connect to database ($st secs): $@</font>");
1.2 harris41 242: print "database handle error\n";
243: exit;
244:
245: };
246: # make sure that a database disconnection occurs with ending kill signals
247: $SIG{TERM}=$SIG{INT}=$SIG{QUIT}=$SIG{__DIE__}=\&DISCONNECT;
248:
1.1 harris41 249: # handle connections until we've reached $MAX_CLIENTS_PER_CHILD
250: for ($i=0; $i < $MAX_CLIENTS_PER_CHILD; $i++) {
251: $client = $server->accept() or last;
1.2 harris41 252:
253: # do something with the connection
1.1 harris41 254: $run = $run+1;
1.2 harris41 255: my $userinput = <$client>;
256: chomp($userinput);
257:
1.21 harris41 258: my ($conserver,$querytmp,
259: $customtmp,$customshowtmp)=split(/&/,$userinput);
1.3 harris41 260: my $query=unescape($querytmp);
1.7 harris41 261: my $custom=unescape($customtmp);
1.21 harris41 262: my $customshow=unescape($customshowtmp);
1.2 harris41 263:
264: #send query id which is pid_unixdatetime_runningcounter
265: $queryid = $thisserver;
266: $queryid .="_".($$)."_";
267: $queryid .= time."_";
268: $queryid .= $run;
269: print $client "$queryid\n";
270:
1.25 harris41 271: &logthis("QUERY: $query");
272: &logthis("QUERY: $query");
273: sleep 1;
1.2 harris41 274: #prepare and execute the query
1.3 harris41 275: my $sth = $dbh->prepare($query);
276: my $result;
1.20 harris41 277: my @files;
1.24 harris41 278: my $subsetflag=0;
1.26 harris41 279: if ($query) {
280: unless ($sth->execute())
281: {
282: &logthis("<font color=blue>WARNING: Could not retrieve from database: $@</font>");
283: $result="";
284: }
285: else {
286: my $r1=$sth->fetchall_arrayref;
287: my @r2;
288: map {my $a=$_;
289: my @b=map {escape($_)} @$a;
290: push @files,@{$a}[3];
291: push @r2,join(",", @b)
292: } (@$r1);
293: $result=join("&",@r2);
294: }
1.3 harris41 295: }
1.7 harris41 296: # do custom metadata searching here and build into result
1.28 harris41 297: if ($custom or $customshow) {
1.9 harris41 298: &logthis("am going to do custom query for $custom");
1.26 harris41 299: if ($query) {
1.23 harris41 300: @metalist=map {$perlvar{'lonDocRoot'}.$_.'.meta'} @files;
1.20 harris41 301: }
302: else {
303: @metalist=(); pop @metalist;
1.34 harris41 304: opendir(RESOURCES,"$perlvar{'lonDocRoot'}/res/$perlvar{'lonDefDomain'}");
305: my @homeusers=grep
306: {&ishome("$perlvar{'lonDocRoot'}/res/$perlvar{'lonDefDomain'}/$_")}
307: grep {!/^\.\.?$/} readdir(RESOURCES);
308: closedir RESOURCES;
309: foreach my $user (@homeusers) {
310: &find("$perlvar{'lonDocRoot'}/res/$perlvar{'lonDefDomain'}/$user");
311: }
1.20 harris41 312: }
1.23 harris41 313: # &logthis("FILELIST:" . join(":::",@metalist));
1.10 harris41 314: # if file is indicated in sql database and
315: # not part of sql-relevant query, do not pattern match.
316: # if file is not in sql database, output error.
317: # if file is indicated in sql database and is
318: # part of query result list, then do the pattern match.
1.12 harris41 319: my $customresult='';
1.26 harris41 320: my @r2;
1.11 harris41 321: foreach my $m (@metalist) {
322: my $fh=IO::File->new($m);
323: my @lines=<$fh>;
324: my $stuff=join('',@lines);
325: if ($stuff=~/$custom/s) {
1.18 harris41 326: foreach my $f ('abstract','author','copyright',
327: 'creationdate','keywords','language',
328: 'lastrevisiondate','mime','notes',
329: 'owner','subject','title') {
1.37 harris41 330: $stuff=~s/\n?\<$f[^\>]*\>.*?<\/$f[^\>]*\>\n?//s;
1.18 harris41 331: }
1.19 harris41 332: my $m2=$m; my $docroot=$perlvar{'lonDocRoot'};
1.26 harris41 333: $m2=~s/^$docroot//;
334: $m2=~s/\.meta$//;
335: unless ($query) {
1.35 harris41 336: my $q2="select * from metadata where url like binary '$m2'";
1.27 harris41 337: my $sth = $dbh->prepare($q2);
1.26 harris41 338: $sth->execute();
339: my $r1=$sth->fetchall_arrayref;
340: map {my $a=$_;
341: my @b=map {escape($_)} @$a;
342: push @files,@{$a}[3];
343: push @r2,join(",", @b)
344: } (@$r1);
345: }
1.20 harris41 346: # &logthis("found: $stuff");
1.19 harris41 347: $customresult.='&custom='.escape($m2).','.escape($stuff);
1.11 harris41 348: }
349: }
1.26 harris41 350: $result=join("&",@r2) unless $query;
1.17 harris41 351: $result.=$customresult;
1.9 harris41 352: }
1.8 harris41 353: # reply with result
1.17 harris41 354: $result.="\n" if $result;
355: &reply("queryreply:$queryid:$result",$conserver);
1.2 harris41 356:
1.1 harris41 357: }
358:
359: # tidy up gracefully and finish
1.2 harris41 360:
361: #close the database handle
362: $dbh->disconnect
363: or &logthis("<font color=blue>WARNING: Couldn't disconnect from database $DBI::errstr ($st secs): $@</font>");
1.1 harris41 364:
365: # this exit is VERY important, otherwise the child will become
366: # a producer of more and more children, forking yourself into
367: # process death.
368: exit;
369: }
1.2 harris41 370: }
1.1 harris41 371:
1.2 harris41 372: sub DISCONNECT {
373: $dbh->disconnect or
374: &logthis("<font color=blue>WARNING: Couldn't disconnect from database $DBI::errstr ($st secs): $@</font>");
375: exit;
376: }
1.1 harris41 377:
1.2 harris41 378: # -------------------------------------------------- Non-critical communication
1.1 harris41 379:
1.2 harris41 380: sub subreply {
381: my ($cmd,$server)=@_;
382: my $peerfile="$perlvar{'lonSockDir'}/$server";
383: my $sclient=IO::Socket::UNIX->new(Peer =>"$peerfile",
384: Type => SOCK_STREAM,
385: Timeout => 10)
386: or return "con_lost";
387: print $sclient "$cmd\n";
388: my $answer=<$sclient>;
389: chomp($answer);
390: if (!$answer) { $answer="con_lost"; }
391: return $answer;
392: }
1.1 harris41 393:
1.2 harris41 394: sub reply {
395: my ($cmd,$server)=@_;
396: my $answer;
397: if ($server ne $perlvar{'lonHostID'}) {
398: $answer=subreply($cmd,$server);
399: if ($answer eq 'con_lost') {
400: $answer=subreply("ping",$server);
401: $answer=subreply($cmd,$server);
402: }
403: } else {
404: $answer='self_reply';
1.33 harris41 405: $answer=subreply($cmd,$server);
1.2 harris41 406: }
407: return $answer;
408: }
1.1 harris41 409:
1.3 harris41 410: # -------------------------------------------------------- Escape Special Chars
411:
412: sub escape {
413: my $str=shift;
414: $str =~ s/(\W)/"%".unpack('H2',$1)/eg;
415: return $str;
416: }
417:
418: # ----------------------------------------------------- Un-Escape Special Chars
419:
420: sub unescape {
421: my $str=shift;
422: $str =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C",hex($1))/eg;
423: return $str;
424: }
1.34 harris41 425:
426: # --------------------------------------- Is this the home server of an author?
427: # (copied from lond, modification of the return value)
428: sub ishome {
429: my $author=shift;
430: $author=~s/\/home\/httpd\/html\/res\/([^\/]*)\/([^\/]*).*/$1\/$2/;
431: my ($udom,$uname)=split(/\//,$author);
432: my $proname=propath($udom,$uname);
433: if (-e $proname) {
434: return 1;
435: } else {
436: return 0;
437: }
438: }
439:
440: # -------------------------------------------- Return path to profile directory
441: # (copied from lond)
442: sub propath {
443: my ($udom,$uname)=@_;
444: $udom=~s/\W//g;
445: $uname=~s/\W//g;
446: my $subdir=$uname.'__';
447: $subdir =~ s/(.)(.)(.).*/$1\/$2\/$3/;
448: my $proname="$perlvar{'lonUsersDir'}/$udom/$subdir/$uname";
449: return $proname;
450: }
1.40 ! harris41 451:
! 452: # ----------------------------------- POD (plain old documentation, CPAN style)
! 453:
! 454: =head1 NAME
! 455:
! 456: lonsql - LON TCP-MySQL-Server Daemon for handling database requests.
! 457:
! 458: =head1 SYNOPSIS
! 459:
! 460: This script should be run as user=www. The following is an example invocation
! 461: from the loncron script. Note that a lonsql.pid file contains the pid of
! 462: the parent process.
! 463:
! 464: if (-e $lonsqlfile) {
! 465: my $lfh=IO::File->new("$lonsqlfile");
! 466: my $lonsqlpid=<$lfh>;
! 467: chomp($lonsqlpid);
! 468: if (kill 0 => $lonsqlpid) {
! 469: print $fh "<h3>lonsql at pid $lonsqlpid responding</h3>";
! 470: $restartflag=0;
! 471: } else {
! 472: $errors++; $errors++;
! 473: print $fh "<h3>lonsql at pid $lonsqlpid not responding</h3>";
! 474: $restartflag=1;
! 475: print $fh
! 476: "<h3>Decided to clean up stale .pid file and restart lonsql</h3>";
! 477: }
! 478: }
! 479: if ($restartflag==1) {
! 480: $errors++;
! 481: print $fh '<br><font color="red">Killall lonsql: '.
! 482: system('killall lonsql').' - ';
! 483: sleep 60;
! 484: print $fh unlink($lonsqlfile).' - '.
! 485: system('killall -9 lonsql').
! 486: '</font><br>';
! 487: print $fh "<h3>lonsql not running, trying to start</h3>";
! 488: system(
! 489: "$perlvar{'lonDaemons'}/lonsql 2>>$perlvar{'lonDaemons'}/logs/lonsql_errors");
! 490: sleep 10;
! 491:
! 492: =head1 DESCRIPTION
! 493:
! 494: LON TCP-MySQL-Server Daemon for handling database requests.
! 495:
! 496: =head1 README
! 497:
! 498: LON TCP-MySQL-Server Daemon for handling database requests.
! 499:
! 500: =head1 PREREQUISITES
! 501:
! 502: IO::Socket
! 503: Symbol
! 504: POSIX
! 505: IO::Select
! 506: IO::File
! 507: Socket
! 508: Fcntl
! 509: Tie::RefHash
! 510: DBI
! 511:
! 512: =head1 COREQUISITES
! 513:
! 514: =head1 OSNAMES
! 515:
! 516: linux
! 517:
! 518: =head1 SCRIPT CATEGORIES
! 519:
! 520: Server/Process
! 521:
! 522: =cut
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/back.gif){kind=icon}
Return to lonsql CVS log ![[TXT]](/icons/text.gif){kind=icon}
![[DIR]](/icons/dir.gif){kind=icon}
Up to [LON-CAPA] / loncom