[Stack-cvs] stack-dev/lib/database StackDBItem.php, 1.36, 1.37 StackDBCache.php, 1.22, 1.23

[Simon H. <sim...@us...>]

Update of /cvsroot/stack/stack-dev/lib/database
In directory sfp-cvsdas-3.v30.ch3.sourceforge.com:/tmp/cvs-serv7863/lib/database

Modified Files:
	StackDBItem.php StackDBCache.php 
Log Message:
Much needed optimisation of question listing.

Index: StackDBItem.php
===================================================================
RCS file: /cvsroot/stack/stack-dev/lib/database/StackDBItem.php,v
retrieving revision 1.36
retrieving revision 1.37
diff -C2 -d -r1.36 -r1.37
*** StackDBItem.php	30 Sep 2010 16:56:13 -0000	1.36
--- StackDBItem.php	1 Oct 2010 15:02:54 -0000	1.37
***************
*** 587,595 ****
              global $config;
  
              $i=0;
              if($this->noRows()>0)
              {
!             	    $dbc = new StackDBCache();
!                     $dbc->connect();
                      foreach($result as $row)
              		{
--- 587,602 ----
              global $config;
  
+ 			// get a count of deployed instances of each version from cache
+ 			$dbc = new StackDBCache();
+             $dbc->connect();
+             $deployCount = $dbc->countAllInstances();
+             $dbc->disconnect();
+ 
+ 			//print_r($deployCount);
+ 
              $i=0;
              if($this->noRows()>0)
              {
! 
                      foreach($result as $row)
              		{
***************
*** 602,610 ****
                  		$qList[$i]['published'] = $this->result($i,'published');
                  		$qList[$i]['status'] = $this->result($i,'status');
!                 		$qList[$i]['nodeployed'] = $dbc->countInstances($qList[$i]['id']);
! 
                  		$i++;
              		}
!                     $dbc->disconnect();
  
  	            $moodleEnabled = $config->getMoodle('enabled');
--- 609,617 ----
                  		$qList[$i]['published'] = $this->result($i,'published');
                  		$qList[$i]['status'] = $this->result($i,'status');
!                 		$qList[$i]['nodeployed'] = $deployCount[$qList[$i]['id']];//TOO MANY QUERIES --> $dbc->countInstances($qList[$i]['id']);
! 						if(!isset($qList[$i]['nodeployed'])) $qList[$i]['nodeployed'] = 0;
                  		$i++;
              		}
! 
  
  	            $moodleEnabled = $config->getMoodle('enabled');

Index: StackDBCache.php
===================================================================
RCS file: /cvsroot/stack/stack-dev/lib/database/StackDBCache.php,v
retrieving revision 1.22
retrieving revision 1.23
diff -C2 -d -r1.22 -r1.23
*** StackDBCache.php	30 Sep 2010 16:56:13 -0000	1.22
--- StackDBCache.php	1 Oct 2010 15:02:54 -0000	1.23
***************
*** 631,634 ****
--- 631,649 ----
      }
  
+ 	// Array mapping all deployed versions to their instance frequency
+ 	public function countAllInstances() {
+ 		$sql = "SELECT qID, COUNT(qID) as freq FROM display_cache, display_cache_sequence " .
+ 				"WHERE currentNode = 0 " .
+ 				"AND nextNode = display_cache.id ".
+ 				"GROUP BY qID";
+ 		$this->query($sql);
+ 		$count = array();
+ 		for($i=0;  $i<$this->noRows(); $i++) {
+ 			$this->result($i, 'freq');
+ 			$count[$this->result($i, 'qID')] = $this->result($i, 'freq');
+ 		}
+ 		return $count;
+ 	}
+ 
      // returns deployed instances of the specified question
      public function dumpInstances($qID) {