dsn = $dsn; $this->username = $username; $this->password = $password; $this->optinos = $options; $this->start = microtime ( true ); } public function milestone ( $name ) { $this->milestones[$name] = microtime ( true ); } public function stop () { $this->stop = microtime ( true ); $this->write (); } private function opendb () { # "mysql:host=localhost;dbname=pagetimer",'user','pass' if ( is_file ( '/etc/pagetimer/dbconfig.php' ) ) { require ( '/etc/pagetimer/dbconfig.php' ); } else { $dsn = $this->dsn; $username = $this->username; $password = $this->password; $options = $this->optinos; } if ( ! isset ( $this->dbhandle ) ) { try { $this->dbhandle = new PDO ( $dsn, $username, $password, $options ); } catch ( Exception $e ) { error_log ( "pagetimer: failed to open database: ".$e->getmessage() ); return false; } } } private function write ( $name=NULL ) { // $name is optional - method, host, request are recorded anyway $this->opendb (); $this->dbhandle->beginTransaction (); // start transaction $st = $this->dbhandle->prepare ( "INSERT INTO Pages (Start,Stop,Time,Method,Host,Request,Name) VALUES (:start,:stop,:time,:method,:host,:request,:name)" ); $st->execute ( array ( ':start' => $this->start, ':stop' => $this->stop, ':time' => $this->stop - $this->start, ':method' => (isset($_SERVER['HTTPS'])?'https':'http'), ':host' => $_SERVER['SERVER_NAME'], ':request' => $_SERVER['REQUEST_URI'], ':name' => $name, ) ); $ref = $this->dbhandle->lastInsertId (); foreach ( array_keys ( $this->milestones ) as $milestonename ) { $st = $this->dbhandle->prepare ( "INSERT INTO Milestones (Start,Milestone,Time,Page,MilestoneName,Name) VALUES (:start,:milestone,:time,:page,:milestonename,:name)" ); $st->execute ( array ( ':start' => $this->start, ':milestone' => $this->milestones[$milestonename], ':time' => $this->milestones[$milestonename] - $this->start, ':page' => $ref, ':milestonename' => $milestonename, ':name' => $name, ) ); } $this->dbhandle->commit (); // finish transaction } public function clearold ( $age=300, $pagename=NULL, $lockage=300 ) { $this->opendb (); $this->dbhandle->beginTransaction (); // start transaction $st = $this->dbhandle->prepare ( "DELETE FROM Pages WHERE Start < :oldest" ); $st->execute ( array ( ':oldest' => floor ( $this->start ) - $age ) ); $st = $this->dbhandle->prepare ( "DELETE FROM Milestones WHERE Start < :oldest" ); $st->execute ( array ( ':oldest' => floor ( $this->start ) - $age ) ); $st = $this->dbhandle->prepare ( "DELETE FROM CacheLock WHERE Time < :oldest" ); $st->execute ( array ( ':oldest' => floor ( $this->start ) - $lockage ) ); $this->dbhandle->commit (); // finish transaction } public function clearcache ( $age=86400 ) { $this->opendb (); $this->dbhandle->beginTransaction (); // start transaction $st = $this->dbhandle->prepare ( "DELETE FROM HistogramCache WHERE Time < :oldest" ); $st->execute ( array ( ':oldest' => floor ( $this->start ) - $age ) ); $st = $this->dbhandle->prepare ( "DELETE FROM PagetimeCache WHERE Time < :oldest" ); $st->execute ( array ( ':oldest' => floor ( $this->start ) - $age ) ); $this->dbhandle->commit (); // finish transaction } public function countrange ( $min=0, $max=0, $table='Pages', $entryfilter=array() ) { // returns count of entries between ranges // if $max == 0 it is unlimited (all above $min) // ideal for histograms $this->opendb (); $filter = ''; $params = array (); if ( $min > 0 ) { if ( $filter != '' ) { $filter .= ' AND '; } $filter .= 'Time > :min'; $params[':min'] = $min; } if ( $max > 0 ) { if ( $filter != '' ) { $filter .= ' AND '; } $filter .= 'Time < :max'; $params[':max'] = $max; } // filter by parameters if ( $table == 'Pages' ) { if ( isset ( $entryfilter['pagesql'] ) and ! preg_match ( '/[\';]/', $entryfilter['pagesql'] ) ) { if ( $filter != '' ) { $filter .= ' AND '; } $filter .= $entryfilter['pagesql']; } } else { if ( isset ( $entryfilter['milestonesql'] ) and ! preg_match ( '/[\';]/', $entryfilter['milestonesql'] ) ) { if ( $filter != '' ) { $filter .= ' AND '; } $filter .= $entryfilter['milestonesql']; } if ( isset ( $entryfilter['pagesql'] ) and ! preg_match ( '/[\';]/', $entryfilter['pagesql'] ) ) { if ( $filter != '' ) { $filter .= ' AND '; } $filter .= "Page IN (SELECT id FROM Pages WHERE $entryfilter[pagesql])"; } } if ( isset ( $entryfilter['params'] ) and count ( $entryfilter['params'] ) > 0 ) { $params = array_merge ( $params, $entryfilter['params'] ); } if ( $filter != '' ) { $filter = "WHERE $filter"; } // got get 'em if ( $table == 'Pages' ) { $st = $this->dbhandle->prepare ( "SELECT COUNT(*) FROM Pages $filter" ); } else { $st = $this->dbhandle->prepare ( "SELECT COUNT(*) FROM Milestones $filter" ); } $st->execute ( $params ); $result = $st->fetch(); return $result[0]; } private function getHistogramCache ( $filtermd5, $band, $cachetime ) { $st = $this->dbhandle->prepare ( "SELECT Value FROM HistogramCache WHERE Filter = :filter AND Band = :band AND Time > :oldest" ); $st->execute ( array ( ':filter'=>$filtermd5, ':band' => $band, ':oldest' => $cachetime ) ); $result = $st->fetch(); if ( isset ( $result['Value'] ) ) { $value = $result['Value']; $st = $this->dbhandle->prepare ( "SELECT SUM(Value) FROM HistogramCache WHERE Filter = :filter" ); $st->execute ( array ( ':filter'=>$filtermd5 ) ); $result = $st->fetch(); if ( isset ( $result['SUM(Value)'] ) ) { // we got a valid cache entry return array ( $value, $result['SUM(Value)'] ); } } // no go return NULL; } private function getLock ( $lockname ) { $st = $this->dbhandle->prepare ( "INSERT INTO CacheLock (LockName,PID,Time) VALUES (:name,:pid,:time)" ); $st->execute ( array ( ':name'=>$lockname, ':pid'=>getmypid(), ':time'=>floor ( $this->start ) ) ); return $this->dbhandle->lastInsertId (); } private function releaseLock ( $lockid ) { $st = $this->dbhandle->prepare ( "DELETE FROM CacheLock WHERE id = :id" ); $st->execute ( array ( ':id' => $lockid ) ); } public function histogram ( $type, $band, $top=1, $filter=array(), $bands=10, $age=120 ) { // check for bands first if ( ! is_numeric ( $band ) ) { $data = array (); switch ( $type ) { case 'histogram': case 'histogramraw': // we are giving a list of times for ( $i = 0; $i < $bands; ++$i ) { $data[] = $bandstep * ( $i + 1 ); } break; case 'histogramlog': case 'histogramrawlog': // we are giving a list of times for ( $i = 0; $i < $bands; ++$i ) { $data[] = $top / pow ( 2, ( 9 - $i ) ); } break; } $data[] = $top; return $data; } // on with the show $cachetime = floor ( $this->start / ( $age / 4 ) ) * ( $age / 4 ) - $age; $this->opendb (); // get our "filter" has to ideintify this histogram $filterstr = $type.$filter['table']; if ( isset ( $filter['condition']['pagesql'] ) ) { $filterstr .= $filter['condition']['pagesql']; } if ( isset ( $filter['condition']['milestonesql'] ) ) { $filterstr .= $filter['condition']['milestonesql']; } if ( isset ( $filter['condition']['params'] ) ) { foreach ( array_keys ( $filter['condition']['params'] ) as $param ) { $filterstr .= "$param=>".$filter['condition']['params'][$param]; } } $filterstr .= $bands; $filtermd5 = md5 ( $filterstr ); // check cache for usable data for this request $cache = $this->getHistogramCache ( $filtermd5, $band, $cachetime ); if ( $cache !== NULL ) { return $cache; } // cache miss - get a lock $locktimer = 0; while ( $cache === NULL and ! $lock = $this->getLock ( 'histogram' ) and $locktimer < $this->locktimeout ) { $randdelay = rand( 100, 200 ); usleep ( $randdelay * 1000 ); $locktimer += $randdelay; $cache = $this->getHistogramCache ( $filtermd5, $band, $cachetime ); } // if we didn't get a lock then we must have a result or bail if ( ! $lock ) { return $cache; } // no valid cache entry - build the histogram switch ( $type ) { case 'histogram': case 'histogramraw': $bandstep = $top / $bands; // update histogram and get $band $total = 0; $value = 0; for ( $i = 0; $i <= $bands; ++$i ) { $min = $bandstep * $i; if ( $i == $bands ) { $max = 0; // limitless for everytying > top band } else { $max = $bandstep * ( $i + 1 ); } $count = $this->countrange ( $min, $max, $filter['table'], $filter['condition'] ); $total += $count; $st = $this->dbhandle->prepare ( "INSERT INTO HistogramCache (Time,Band,Value,Filter) VALUES (:time,:band,:value,:filter) ON DUPLICATE KEY UPDATE Time = :time, Value = :value" ); $st->execute ( array ( ':time'=>floor ( $this->start ), ':band'=>$i, ':value'=>$count, ':filter'=>$filtermd5 ) ); if ( $i == $band ) { $value = $count; } } break; case 'histogramlog': case 'histogramrawlog': // update histogram and get $band $total = 0; $value = 0; for ( $i = 0; $i <= $bands; ++$i ) { if ( $i == 0 ) { $min = 0; } else { $min = $top / pow ( 2, ( $bands - $i ) ); } if ( $i == 10 ) { $max = 0; // limitless for everytying > band 9 } else { $max = $top / pow ( 2, ( $bands - 1 - $i ) ); } $count = $this->countrange ( $min, $max, $filter['table'], $filter['condition'] ); $total += $count; $st = $this->dbhandle->prepare ( "INSERT INTO HistogramCache (Time,Band,Value,Filter) VALUES (:time,:band,:value,:filter) ON DUPLICATE KEY UPDATE Time = :time, Value = :value" ); $st->execute ( array ( ':time'=>floor ( $this->start ), ':band'=>$i, ':value'=>$count, ':filter'=>$filtermd5 ) ); if ( $i == $band ) { $value = $count; } } break; } $this->releaseLock ( $lock ); return array ( $value, $total ); } // blitz all the filters in one go wrapped in one transaction TODO public function histogramall ( $type, $band, $filters=array(), $top=1, $bands=10, $age=120 ) { $bandvals = array (); // transaction wrap $this->opendb (); $this->dbhandle->beginTransaction (); // start transaction // run all the filters foreach ( $filters as $filter ) { $bandvals[] = $this->histogram ( $type, $band, $top, $filter, $bands, $age ); } $this->dbhandle->commit (); // finish transaction return $bandvals; } // work out the pagetimes for this proportion and filter public function pagetimes ( $portion, $entryfilter=array(), $age=120 ) { $cachetime = floor ( $this->start / ( $age / 4 ) ) * ( $age / 4 ) - $age; // gives the time below which that portion of pages where delivered $this->opendb (); // filter by parameters $filter = ''; if ( $entryfilter['table'] == 'Pages' ) { if ( isset ( $entryfilter['condition']['pagesql'] ) and ! preg_match ( '/[\';]/', $entryfilter['condition']['pagesql'] ) ) { if ( $filter != '' ) { $filter .= ' AND '; } $filter .= $entryfilter['condition']['pagesql']; } } else { if ( isset ( $entryfilter['condition']['milestonesql'] ) and ! preg_match ( '/[\';]/', $entryfilter['condition']['milestonesql'] ) ) { if ( $filter != '' ) { $filter .= ' AND '; } $filter .= $entryfilter['condition']['milestonesql']; } if ( isset ( $entryfilter['condition']['pagesql'] ) and ! preg_match ( '/[\';]/', $entryfilter['condition']['pagesql'] ) ) { if ( $filter != '' ) { $filter .= ' AND '; } $filter .= "Page IN (SELECT id FROM Pages WHERE ".$entryfilter['condition']['pagesql'].")"; } } $params = array (); if ( isset ( $entryfilter['condition']['params'] ) and count ( $entryfilter['condition']['params'] ) > 0 ) { $params = array_merge ( $params, $entryfilter['condition']['params'] ); } // get cache filter $filterstr = $portion.$entryfilter['table']; $filterstr .= $filter; foreach ( array_keys ( $params ) as $param ) { $filterstr .= "$param=>$params[$param]"; } $filtermd5 = md5 ( $filterstr ); // check cache for usable data for this request $st = $this->dbhandle->prepare ( "SELECT id,Value FROM PagetimeCache WHERE Filter = :filter AND Time > :oldest" ); $st->execute ( array ( ':filter'=>$filtermd5, ':oldest'=>$cachetime ) ); $result = $st->fetch(); if ( isset ( $result['id'] ) ) { return $result['Value']; } // cache miss - go ahead and find the value // get the total page count so we can filter if ( $filter != '' ) { $filter = "WHERE $filter"; } if ( $entryfilter['table'] == 'Pages' ) { $st = $this->dbhandle->prepare ( "SELECT COUNT(*) FROM Pages $filter" ); } else { $st = $this->dbhandle->prepare ( "SELECT COUNT(*) FROM Milestones $filter" ); } $st->execute ( $params ); $result = $st->fetch(); $total = floor ( $result[0] * ( 1 - $portion ) + 0.5 ) + 1; // +1 is so that we get the max of the rest // now we can find the time if ( $entryfilter['table'] == 'Pages' ) { $st = $this->dbhandle->prepare ( "SELECT MIN(Time) FROM (SELECT Time FROM Pages $filter ORDER BY Time DESC LIMIT $total) none" ); } else { $st = $this->dbhandle->prepare ( "SELECT MIN(Time) FROM (SELECT Time FROM Milestones $filter ORDER BY Time DESC LIMIT $total) none" ); } $st->execute ( $params ); $result = $st->fetch(); // add to cache $st = $this->dbhandle->prepare ( "INSERT INTO PagetimeCache (Time,Value,Filter) VALUES (:time,:value,:filter) ON DUPLICATE KEY UPDATE Time = :time, Value = :value" ); $st->execute ( array ( ':time'=>floor ( $this->start ), ':value'=>$result[0], ':filter'=>$filtermd5 ) ); // all done return $result[0]; } // blitz all the filters in one go wrapped in one transaction public function pagetimesall ( $portion, $filters, $age=120 ) { $pagetimes = array (); // transaction wrap $this->opendb (); $this->dbhandle->beginTransaction (); // start transaction // run all the filters foreach ( $filters as $filter ) { $time = $this->pagetimes ( $portion, $filter, $age ); if ( isset ( $time ) ) { $pagetimes[] = $time; } else { $pagetimes[] = NULL; } } $this->dbhandle->commit (); // finish transaction return $pagetimes; } } ?>