Menu
▾
▴
[Stack-cvs] stack-dev/lib/database devCache.php, 1.11.6.1, 1.11.6.1.2.1 StackDBItem.php, 1.32, 1.32.8.1 StackDBAttemptPRTMeta.php, 1.7, 1.7.8.1 StackDBBackup.php, 1.5, 1.5.8.1 StackDBUser.php, 1.7, 1.7.8.1 StackDB.php, 1.5, 1.5.8.1 StackDBADOdb.php, 1.7, 1.7.8.1 MoodleDB.php, 1.14, 1.14.8.1 StackDBMySQL.php, 1.17, 1.17.8.1 StackDBKeywords.php, 1.10, 1.10.8.1 StackDBReporting.php, 1.27, 1.27.8.1 StackDBCache.php, 1.12.6.2.2.1, 1.12.6.2.2.2
|
From: Simon H. <sim...@us...> - 2010-11-09 16:52:15
|
Update of /cvsroot/stack/stack-dev/lib/database In directory sfp-cvsdas-3.v30.ch3.sourceforge.com:/tmp/cvs-serv25193/lib/database Modified Files: Tag: item_state_separation devCache.php StackDBItem.php StackDBAttemptPRTMeta.php StackDBBackup.php StackDBUser.php StackDB.php StackDBADOdb.php MoodleDB.php StackDBMySQL.php StackDBKeywords.php StackDBReporting.php StackDBCache.php Log Message: Brought back up to date with big HEAD merge. Index: MoodleDB.php =================================================================== RCS file: /cvsroot/stack/stack-dev/lib/database/MoodleDB.php,v retrieving revision 1.14 retrieving revision 1.14.8.1 diff -C2 -d -r1.14 -r1.14.8.1 *** MoodleDB.php 27 Aug 2009 16:23:00 -0000 1.14 --- MoodleDB.php 9 Nov 2010 16:51:35 -0000 1.14.8.1 *************** *** 70,78 **** public function batchAdd($questions, $userID, $engineID, $category='1') { if(!empty($questions)) { foreach($questions as $question) { ! $result = $this->addQuestion($question, $userID, $engineID, $category); if($result == false) --- 70,79 ---- public function batchAdd($questions, $userID, $engineID, $category='1') { + $this->logger->debug("busy: $questions, $userID, $engineID, $category"); if(!empty($questions)) { foreach($questions as $question) { ! $result = $this->addQuestion($question, NULL, $userID, $engineID, $category); // line is unchanged if($result == false) *************** *** 106,118 **** * @return bool $return true if successful */ ! public function addQuestion($stackID, $userID, $engineID, $category='1') { global $config; $moodleEnabled = $config->getMoodle('enabled'); if(($moodleEnabled == 'true') && ($engineID != NULL)) //if moodle config is setup { //load up the question from the stack db & grab its name, grade & guid. ! $item = new Item(NULL, $stackID); $itemName = $item->questionName->getSelection(); --- 107,120 ---- * @return bool $return true if successful */ ! public function addQuestion($id, $line, $userID, $engineID, $category='1') { global $config; $moodleEnabled = $config->getMoodle('enabled'); + $this->logger->debug("mycat:".$category." and userid:".$userID); if(($moodleEnabled == 'true') && ($engineID != NULL)) //if moodle config is setup { //load up the question from the stack db & grab its name, grade & guid. ! $item = new Item(NULL, $id); $itemName = $item->questionName->getSelection(); *************** *** 134,144 **** //check whether the question is already in the moodle question bank. ! $inQBank = $this->inMoodleQuestionBank($stackID, $engineID); if($inQBank === false) { //add the question ! return $this->newQuestion($engineID, $userID, $category, $itemID, $itemName, $itemMaxMark, $itemGUID); } else { //update the question(s) return $this->updateQuestion($inQBank, $engineID, $userID, $category, $itemID, $itemName, $itemMaxMark, $itemGUID); } --- 136,148 ---- //check whether the question is already in the moodle question bank. ! $inQBank = $this->inMoodleQuestionBank($id, $engineID); if($inQBank === false) { + $this->logger->debug("New question"); //add the question ! return $this->newQuestion($engineID, $userID, $category, $itemID, $line, $itemName, $itemMaxMark, $itemGUID); } else { //update the question(s) + $this->logger->debug("Updating question in $inQBank"); return $this->updateQuestion($inQBank, $engineID, $userID, $category, $itemID, $itemName, $itemMaxMark, $itemGUID); } *************** *** 152,156 **** /** ! * Updates a question already in the moodle question bank * * @param int $qID moodle's question id --- 156,160 ---- /** ! * Updates a question already in the moodle question bank. * * @param int $qID moodle's question id *************** *** 163,167 **** protected function updateQuestion($qID, $engineID, $userID, $category, $stackID, $qName, $qMaxMark, $qGUID) { ! $this->logger->finer("Updating item $stackID to Moodles database"); global $config; $prefix = $config->getMoodle('prefix'); --- 167,172 ---- protected function updateQuestion($qID, $engineID, $userID, $category, $stackID, $qName, $qMaxMark, $qGUID) { ! $this->logger->debug("Updating item $stackID to Moodles database"); ! global $config; $prefix = $config->getMoodle('prefix'); *************** *** 188,191 **** --- 193,208 ---- $result = false; } + + $sql = "UPDATE {$prefix}question_opaque SET + remoteid = ".$this->dbSafeString('q'.$stackID).", + WHERE questionid = $id"; + + $result = $this->query($sql); + if(!$result){ + $this->logger->error("Failed to add the question to the opaque_questions table"); + } + else{ + $this->logger->finer("Added to opaque_questions table"); + } } return true; *************** *** 203,210 **** * @return bool */ ! protected function newQuestion($engineID, $userID, $category, $stackID, $qName, $qMaxMark, $qGUID) { ! $this->logger->finer("Adding item $stackID to Moodles database"); ! $stackID = 'q'.$stackID; global $config; $prefix = $config->getMoodle('prefix'); --- 220,231 ---- * @return bool */ ! protected function newQuestion($engineID, $userID, $category, $versionID, $lineID, $qName, $qMaxMark, $qGUID) { ! if($lineID == NULL) $lineID = 12345; ! ! $this->logger->debug("Adding item $versionID (line $lineID) to Moodles database by user: $userID"); ! ! ! global $config; $prefix = $config->getMoodle('prefix'); *************** *** 235,239 **** $category, 0, ".$this->dbSafeString($qName)." , ".$this->dbSafeString('').", 0, ".$this->dbSafeString('').", ".$this->dbSafeString('').", $qMaxMark, 0,".$this->dbSafeString(QTYPE).", 1, ".$this->dbSafeString($qGUID).", ".$this->dbSafeString($timestamp).", 0, $timestamp, $timestamp, $userID );"; ! $this->connectMoodleDB(); $added = $this->query($sql); --- 256,260 ---- $category, 0, ".$this->dbSafeString($qName)." , ".$this->dbSafeString('').", 0, ".$this->dbSafeString('').", ".$this->dbSafeString('').", $qMaxMark, 0,".$this->dbSafeString(QTYPE).", 1, ".$this->dbSafeString($qGUID).", ".$this->dbSafeString($timestamp).", 0, $timestamp, $timestamp, $userID );"; ! $this->logger->finer("trying adding to moodle question bank with sql: $sql"); $this->connectMoodleDB(); $added = $this->query($sql); *************** *** 253,257 **** remoteversion ) ! VALUES ( $qid, $engineID, ".$this->dbSafeString($stackID).", ".$this->dbSafeString('1.0').");"; $result = $this->query($sql); --- 274,278 ---- remoteversion ) ! VALUES ( $qid, $engineID, ".$this->dbSafeString('q'.$versionID).", ".$this->dbSafeString($lineID.'.'.$versionID).");"; // some redundancy for now until versioning get more sophisticated, e.g. with milestones $result = $this->query($sql); *************** *** 312,315 **** --- 333,372 ---- } + /** + * Attempts to remove a stack question from moodle's question bank. Return success status. + * + * @param int $engineID Opaque engine id + * @param int $stackID The id of the question on stack. + * @access public + * @return bool + */ + public function hideQuestion($stackID, $engineID) + { + $this->logger->debug("attempting to hide stack question $stackID on engine $engineID"); + // find moodle question id + $moodleQuestions = $this->inMoodleQuestionBank($stackID, $engineID); // an array + + if(empty($moodleQuestions)) { + $this->logger->debug("STACK question $stackID does not appear to be in Moodle question bank"); + return false; + } + + // remove, i.e. set hidden flag to 1 + if(count($moodleQuestions) > 1) { + $where = "id IN (".implode($moodleQuestions).")"; //multiple questions + } + else { + $where = "id = ".$moodleQuestions[0]; // single question + } + + $sql = "UPDATE {$prefix}question SET hidden=1 WHERE ".$where; + + $this->connect(); + $result = $this->query($sql); + + $this->logger->debug("Query ".$sql."\n\n Returned".print_r($result,true)); + //$no = $this->noRows(); + } + /** * Recursively checks for child question categories, returning an array of sub categories. *************** *** 466,470 **** if($categories[$i]['id'] == $selected) { ! $xhtml .= '<option value="'.$categories[$i]['id'].'" selected>'.$categories[$i]['name'].'</option>'; } else --- 523,527 ---- if($categories[$i]['id'] == $selected) { ! $xhtml .= '<option value="'.$categories[$i]['id'].'" selected="">'.$categories[$i]['name'].'</option>'; } else *************** *** 594,597 **** --- 651,765 ---- } } + + /** + * PROBABLY NOT NEEDED + * Return an mapping from the quizzes the student has taken to the attempts made. + * @access public + * @param int $student + * @return array $attempts + */ + public function getQuizAttempts($student, $course = NULL) { + global $config; + $prefix = $config->getMoodle('prefix'); + if(NULL == $course) { // irrespective of course + $sql = "SELECT quiz, {$prefix}quiz_attempts.id FROM {$prefix}quiz_attempts WHERE userid = ".$this->dbSafeString($student); + } else { // course specific + $sql = "SELECT quiz, {$prefix}quiz_attempts.id FROM {$prefix}quiz_attempts, {$prefix}quiz " . + "WHERE userid = ".$this->dbSafeString($student). + " AND course =".$this->dbSafeString($course). + " AND {$prefix}quiz.id = quiz"; + } + //$this->logger->debug($sql); + $result = $this->query($sql); + $attempts = NULL; + if($result) { + for($i = 0; $i < $this->noRows(); $i++) { + $attempts[$this->result($i, 'quiz')][] = $this->result($i, 'id'); // NOT result($i, 'attempt')! + } + } + return $attempts; + } + + /** + * Return an mapping from the questions the student has attempted to the attempt. + * @access public + * @param int $student + * @return array $attempts + */ + public function getQuestionAttempts($student, $course = NULL) { + global $config; + $prefix = $config->getMoodle('prefix'); + if(NULL == $course) { // irrespective of course + $sql = "SELECT quiz, {$prefix}quiz_attempts.id FROM {$prefix}quiz_attempts WHERE userid = ".$this->dbSafeString($student); + } else { // course specific + $sql = "SELECT quiz, {$prefix}quiz_attempts.id FROM {$prefix}quiz_attempts, {$prefix}quiz " . + "WHERE userid = ".$this->dbSafeString($student). + " AND course =".$this->dbSafeString($course). + " AND {$prefix}quiz.id = quiz"; + } + //$this->logger->debug($sql); + $result = $this->query($sql); + $attempts = NULL; + if($result) { + for($i = 0; $i < $this->noRows(); $i++) { + $attempts[$this->result($i, 'quiz')][] = $this->result($i, 'id'); // NOT result($i, 'attempt')! + } + } + return $attempts; + } + + /** + * Return all the responses from the student for the given attempt, in order, as well as (Moodle) question id + * @access public + * @param int $attempt + * @return array $responses + */ + public function getQuestionAttemptResponses($attempt) { + global $config; + $prefix = $config->getMoodle('prefix'); + + $sql = "SELECT seq_number, answer, event, question, remoteid" . + " FROM {$prefix}question_states, {$prefix}question_opaque" . + " WHERE attempt = ".$this->dbSafeString($attempt). + " AND {$prefix}question_opaque.questionid = {$prefix}question_states.question". + " ORDER BY seq_number"; + + $result = $this->query($sql); + // $responses = NULL; + if($result) { + for($i = 0; $i < $this->noRows(); $i++) { + $q = $this->result($i, 'question'); + $s = $this->result($i, 'seq_number'); + $return[$q]['version'] = substr($this->result($i, 'remoteid'), 1); // strip off 'q' + $return[$q][$s]['answer'] = $this->result($i, 'answer'); + $return[$q][$s]['event'] = $this->result($i, 'event'); + } + } + return $return; + } + + /** + * Returns the number of attempts on an STACK question + * @access public + * @param int $id + * @return int + */ + public function attemptsOnQuestion($id) { + global $config; + $prefix = $config->getMoodle('prefix'); + + $id = 'q'.$id; + $sql = "SELECT * FROM {$prefix}question_states, {$prefix}question_opaque " . + "WHERE question = questionid " . + "AND remoteid = ".$this->dbSafeString($id); + + $sql = "SELECT * FROM {$prefix}question_states";// test + $result = $this->query($sql); + $n = $this->noRows(); + //return $n; + //return $this->noRows(); + if($this->noRows() === false) return "noRows() === false for $sql"; + else return "$this->noRows() attempt states for this question"; + } } Index: StackDBAttemptPRTMeta.php =================================================================== RCS file: /cvsroot/stack/stack-dev/lib/database/StackDBAttemptPRTMeta.php,v retrieving revision 1.7 retrieving revision 1.7.8.1 diff -C2 -d -r1.7 -r1.7.8.1 *** StackDBAttemptPRTMeta.php 16 Sep 2009 12:04:22 -0000 1.7 --- StackDBAttemptPRTMeta.php 9 Nov 2010 16:51:35 -0000 1.7.8.1 *************** *** 69,73 **** VALUES ($CacheID, ".$this->dbSafeString($name).", $rawmark , $modmark , $ansnote , $error);"; ! $this->logger->critical($sql); $this->query($sql); --- 69,73 ---- VALUES ($CacheID, ".$this->dbSafeString($name).", $rawmark , $modmark , $ansnote , $error);"; ! $this->logger->finest($sql); $this->query($sql); *************** *** 114,120 **** } } - - - } --- 114,117 ---- Index: StackDB.php =================================================================== RCS file: /cvsroot/stack/stack-dev/lib/database/StackDB.php,v retrieving revision 1.5 retrieving revision 1.5.8.1 diff -C2 -d -r1.5 -r1.5.8.1 *** StackDB.php 27 Aug 2009 16:23:00 -0000 1.5 --- StackDB.php 9 Nov 2010 16:51:35 -0000 1.5.8.1 *************** *** 78,81 **** --- 78,83 ---- public function allResults($field = NULL); + + //public function last_insert_id(); } Index: StackDBADOdb.php =================================================================== RCS file: /cvsroot/stack/stack-dev/lib/database/StackDBADOdb.php,v retrieving revision 1.7 retrieving revision 1.7.8.1 diff -C2 -d -r1.7 -r1.7.8.1 *** StackDBADOdb.php 27 Aug 2009 16:23:00 -0000 1.7 --- StackDBADOdb.php 9 Nov 2010 16:51:35 -0000 1.7.8.1 *************** *** 81,85 **** $driver = $this->config->getDB('dbType'); ! $this->conn = &NewADOConnection($driver); //$this->conn->debug=true; $this->conn->PConnect($server, $username, $password, $database); --- 81,85 ---- $driver = $this->config->getDB('dbType'); ! $this->conn = NewADOConnection($driver); //$this->conn->debug=true; $this->conn->PConnect($server, $username, $password, $database); *************** *** 163,166 **** --- 163,190 ---- /** + * Performs a query using adoDB SelectLimit + * + * @param string $sql + * @return Array|bool + * @access public + */ + public function selectLimit($sql, $rows, $start) + { + $this->logger->finest("Performing Query with selectLimit: ".$sql); + $rs=NULL; + try { + $rs=$this->conn->SelectLimit($sql, $rows, $start); + //$this->logger->finest("Result of Query: ".$rs); + } catch (exception $e) { + $this->errorLog->addError('Invalid query '.$this->conn->ErrorMsg()); + $this->logger->critical('Invalid query '.$this->conn->ErrorMsg()); + return false; + } + + $this->lastResult = $rs; + return $rs; + } + + /** * Returns the number of rows from the last query. * *************** *** 325,330 **** } ! public function insert_id() { ! return Insert_ID(); // PO_Insert_ID() is a more portable but untested alternative. } --- 349,354 ---- } ! public function last_insert_id() { ! return $this->conn->Insert_ID(); // PO_Insert_ID() is a more portable but untested alternative. } Index: StackDBUser.php =================================================================== RCS file: /cvsroot/stack/stack-dev/lib/database/StackDBUser.php,v retrieving revision 1.7 retrieving revision 1.7.8.1 diff -C2 -d -r1.7 -r1.7.8.1 *** StackDBUser.php 27 Aug 2009 16:23:00 -0000 1.7 --- StackDBUser.php 9 Nov 2010 16:51:35 -0000 1.7.8.1 *************** *** 157,160 **** --- 157,179 ---- /** + * Returns a count of moodle users + * @param int $userid + * @return int $number + */ + public function countUsers() + { + global $config; + $prefix = $config->getMoodle('prefix'); + $sql = 'SELECT COUNT(id) as count + FROM '.$prefix.'user'; + + $this->connect(); + $this->query($sql); + $this->disconnect(); + + return $this->result(0, 'count'); + } + + /** * Returns a list of a user's moodle capabilities. (NOTE: Does not take context into account). * *************** *** 231,235 **** } } - } --- 250,253 ---- Index: StackDBCache.php =================================================================== RCS file: /cvsroot/stack/stack-dev/lib/database/StackDBCache.php,v retrieving revision 1.12.6.2.2.1 retrieving revision 1.12.6.2.2.2 diff -C2 -d -r1.12.6.2.2.1 -r1.12.6.2.2.2 *** StackDBCache.php 4 Dec 2009 10:11:14 -0000 1.12.6.2.2.1 --- StackDBCache.php 9 Nov 2010 16:51:35 -0000 1.12.6.2.2.2 *************** *** 18,21 **** --- 18,22 ---- require_once 'StackDBADOdb.php'; + require_once $config->get('docroot').'/other/adodb5/adodb-pager.inc.php'; /** *************** *** 49,56 **** // add a transition using the new state id ! $transition = $this->addTransition($lastNode, $stateID, $post, $event); ! // add attempt_meta information ! if($userEvent) $this->addMeta($stateID, $state); } --- 50,59 ---- // add a transition using the new state id ! if($stateID !== NULL) { // state added successfully: go ahead and reference it! ! $transition = $this->addTransition($lastNode, $stateID, $post, $event); ! // add attempt_meta information ! if($userEvent) $this->addMeta($stateID, $state); ! } } *************** *** 58,62 **** $this->query("UNLOCK TABLES"); ! return $transition; // either the new or recent prior one } --- 61,65 ---- $this->query("UNLOCK TABLES"); ! return $transition; // either the new or recent prior one (or NULL on failure) } *************** *** 77,87 **** ", NOW())"; ! $this->query($sql); ! // NOTE: if this fails it will mess the transition table because of 'last index' method below. ! $this->logger->debug("SQL debug: ".$sql); ! // would be nice to be able to obtain the index for the last insert (like mysql_insert_id) without this extra call but would require extending the StackDB interface ! $this->query("SELECT id FROM display_cache ORDER BY id DESC LIMIT 1"); // safe for multi-threading? if($this->noRows()<=0) { // no rows in display_cache? --- 80,92 ---- ", NOW())"; ! //$this->logger->debug("SQL:".$sql); ! $result = $this->query($sql); ! if(empty($result)) { // insert failed! ! $this->logger->critical("Failed to store a question state!"); ! return NULL; // don't add a transition! ! } // would be nice to be able to obtain the index for the last insert (like mysql_insert_id) without this extra call but would require extending the StackDB interface ! $this->query("SELECT id FROM display_cache ORDER BY id DESC LIMIT 1"); // safe for multi-threading if higher-level lock maintained if($this->noRows()<=0) { // no rows in display_cache? *************** *** 155,163 **** // discard if this duplicates an existing instance (defined by question note) ! $sql = "SELECT * FROM display_cache WHERE qID = ".$instance->getQuestionID(). ! " AND questionNote = ".$db->dbSafeString(StackDBCache::hackSlashes($instance->getQuestionNote())); ! // TODO: check is a *live* initial state $db->query($sql); ! if($db->noRows() > 0) { continue; } // create a cache state --- 160,168 ---- // discard if this duplicates an existing instance (defined by question note) ! $sql = "SELECT * FROM display_cache, display_cache_sequence WHERE qID = ".$instance->getQuestionID(). ! " AND questionNote = ".$db->dbSafeString(StackDBCache::hackSlashes($instance->getQuestionNote())). ! " AND display_cache_sequence.currentNode=0 AND display_cache_sequence.nextNode=display_cache.id"; $db->query($sql); ! if($db->noRows() > 0) { continue; } // discard and go round again // create a cache state *************** *** 226,230 **** */ public function nextNode($transition) { - // takes current node and posted answers // returns next node id (used for obtaining cache state) $sql = "SELECT nextNode FROM display_cache_sequence " . --- 231,234 ---- *************** *** 326,329 **** --- 330,353 ---- } + /* + * Determines whether a question can be changed without fear. + * i.e. even if it has available initial states, these have not been processed. + */ + public function hasActiveStates($qID) { + // initial states have a transition from a state id of 0 + + // get all starting configs for given question + $sql = "SELECT display_cache.id, display_cache.state FROM display_cache_sequence, display_cache " . + "LEFT JOIN display_cache_sequence AS successor " . + "ON successor.currentNode = $qID ". + "WHERE display_cache.qID = $qID " . + "AND display_cache_sequence.currentNode = 0 " . + "AND display_cache_sequence.nextNode = display_cache.id"; + $this->query($sql); + + // return 0 if question not deployed + return ($this->noRows() === 0); + } + /** * Empties the entire cache *************** *** 383,387 **** $this->query($sql); - //echo "<pre>";print_r($this); echo"</pre>"; $xhtml = '<table id="transitionstable" border=1>'; --- 407,410 ---- *************** *** 396,401 **** //$xhtml .= '<tr><td>'. $this->result($i,'id').'</td>'; $post = $this->base64_unserialize($this->result($i, 'post')); ! $xhtml .= '<td>'. $this->result($i,'currentNode').'</td>'; ! $xhtml .= '<td>'. $this->result($i,'nextNode').'</td>'; $showpost = $this->pretty_post($post); $xhtml .= '<td>'. $showpost.'</td>'; --- 419,426 ---- //$xhtml .= '<tr><td>'. $this->result($i,'id').'</td>'; $post = $this->base64_unserialize($this->result($i, 'post')); ! $xhtml .= '<td><a href="?sid='. $this->result($i,'currentNode').'">'. ! $this->result($i,'currentNode') .'</td>'; ! $xhtml .= '<td><a href="?sid='. $this->result($i,'nextNode').'">'. ! $this->result($i,'nextNode') .'</td>'; $showpost = $this->pretty_post($post); $xhtml .= '<td>'. $showpost.'</td>'; *************** *** 408,412 **** } ! public function dumpCacheState($qid = NULL) { if(!empty($qid)) { $sql .= "SELECT * FROM display_cache WHERE qID=".$qid." ORDER BY id"; --- 433,437 ---- } ! public function dumpCacheState($qid = NULL, $rows = 3, $startrow = 0) { if(!empty($qid)) { $sql .= "SELECT * FROM display_cache WHERE qID=".$qid." ORDER BY id"; *************** *** 414,418 **** else $sql = "SELECT * FROM display_cache ORDER BY id"; ! $this->query($sql); //echo "<pre>";print_r($this); echo"</pre>"; --- 439,444 ---- else $sql = "SELECT * FROM display_cache ORDER BY id"; ! //$this->query($sql); ! $this->selectLimit($sql, $rows, $startrow); //echo "<pre>";print_r($this); echo"</pre>"; *************** *** 435,441 **** --- 461,587 ---- $xhtml .= '</table>'; + $xhtml .= "next "; + $xhtml .= "<a href='?qid=$GET[qid]&startrow=".($startrow+$rows)."&rowcount=10'>10</a> | "; + $xhtml .= "<a href='?qid=$GET[qid]&startrow=".($startrow+$rows)."&rowcount=100'>100</a>"; return $xhtml; } + public function showInitialStates($qid) { + $sql = "SELECT display_cache.* FROM display_cache_sequence, display_cache " . + "WHERE display_cache.qID = $qid " . + "AND currentNode = 0 " . + "AND nextNode = display_cache.id"; + + $this->query($sql); + //echo $this->noRows(); + + // store these initial states since we'll need to use the connection for finding successive states + for ($i=0; $i<$this->noRows(); $i++) { + $id = $this->result($i,'id'); + $initials[$id]['id'] = $id; + $initials[$id]['post'] = $this->base64_unserialize($this->result($i, 'post')); + $initials[$id]['state'] = $this->base64_unserialize($this->result($i, 'state')); + $initials[$id]['answers'] = $this->pretty_post($this->base64_unserialize($this->result($i, 'answers')), true); + $initials[$id]['currentTotalMark'] = $this->result($i, 'currentTotalMark'); + $initials[$id]['totalPossibleMarks'] = $this->result($i, 'totalPossibleMarks'); + $initials[$id]['expired'] = $this->result($i, 'expired'); + $initials[$id]['questionNote'] = $this->result($i, 'questionNote'); + $initials[$id]['xhtml'] = $this->result($i,'xhtml'); + } + + //echo "<pre>";print_r($this); echo"</pre>"; + + $xhtml .= '<table id="statestable" border=1>'; + $xhtml .= '<tr><th>id</th><th>answers</th><th>current / total mark</th><th>exp.</th><th>questionNote</th><th>xhtml</th><th>transitions</th></tr>'; + + foreach($initials as $i) { + $xhtml .= '<td>'.$i['id'].'</td>'; + $xhtml .= '<td>'.$i['answers'].'</td>'; + $xhtml .= '<td>'.$i['currentTotalMark'].' / '.$i['totalPossibleMarks'].'</td>'; + $xhtml .= '<td>'.$i['expired'].'</td>'; + $xhtml .= '<td>'.$i['questionNote'].'</td>'; + $xhtml .= '<td>'.$i['xhtml'].'</td>'; + + $xhtml .= '<td>'.$this->innerTransitionsTable($i['id']).'</td></tr>'; + } + $xhtml .= '</table>'; + return $xhtml; + } + + private function innerTransitionsTable($id) { + $transitions = $this->stateTransitions($id); + $xhtml = ''; + if($transitions) { + $xhtml .= '<table>'; // start inner table + foreach($transitions as $t) { + if($t['previous'] !== null) { + if($t['previous'] > 0) $xhtml .= '<tr><td><a href="?sid='.$t['previous'].'">« back to '.$t[previous].'</a></td></tr>'; + } + else if($t['nextNode'] != $id) $xhtml .= + '<tr><td><a href="?sid='.$t['nextNode'].'">» '.$this->pretty_post($this->base64_unserialize($t['post'])).'</a> ('.$t['event'].')</td></tr>'; + else $xhtml .= '(loopback) '.$this->pretty_post($this->base64_unserialize($t['post'])); + } + $xhtml .= '</table>'; // end inner table + } + return $xhtml; + } + + public function showCacheState($id) { + $sql .= "SELECT * FROM display_cache WHERE id=".$id; + + $this->query($sql); + + //echo "<pre>";print_r($this); echo"</pre>"; + $xhtml .= '<table id="statestable" border=1>'; + $xhtml .= '<tr><th>id</th><th>answers</th><th>current / total mark</th><th>exp.</th><th>questionNote</th><th>xhtml</th><th>transitions</th></tr>'; + for ($i=0; $i<$this->noRows(); $i++) { + $post = $this->base64_unserialize($this->result($i, 'post')); + $state = $this->base64_unserialize($this->result($i, 'state')); + $xhtml .= '<tr><td>'. $this->result($i,'id').'</td>'; + //$xhtml .= '<td>'.$this->elementsToHTML($state).'</td>'; + $xhtml .= '<td>'. $this->pretty_post($this->base64_unserialize($this->result($i, 'answers')), true) .'</td>'; + $xhtml .= '<td>'. $this->result($i, 'currentTotalMark') .' / '. $this->result($i, 'totalPossibleMarks') .'</td>'; + $xhtml .= '<td>'. $this->result($i, 'expired') .'</td>'; + $xhtml .= '<td>'. $this->result($i, 'questionNote') .'</td>'; + $xhtml .= '<td>'. $this->result($i,'xhtml').'</td>'; + $xhtml .= '<td>'; + $xhtml .= $this->innerTransitionsTable($this->result($i,'id')); + $xhtml .= '</td></tr>'; + } + return $xhtml; + } + + private function stateTransitions($id) + { + $sql = "SELECT * FROM display_cache_sequence WHERE currentNode=$id OR nextNode=$id"; + $this->query($sql); + for ($i=0; $i<$this->noRows(); $i++) { + $tid = $this->result($i,'id'); + if($this->result($i, 'currentNode') == $id) { // transition FROM this node + $transitions[$tid]['nextNode'] = $this->result($i, 'nextNode'); + $transitions[$tid]['post'] = $this->result($i, 'post'); + $transitions[$tid]['event'] = $this->result($i, 'event'); + } else { // transition TO this node + $transitions[$tid]['previous'] = $this->result($i, 'currentNode'); + } + } + + //print_r($transitions); + return $transitions; + } + + public function cacheStatePager($qid = NULL) { + if(!empty($qid)) { + //$sql .= "SELECT * FROM display_cache WHERE qID=".$qid." ORDER BY id"; + $sql .= "SELECT id,qID,answers,questionNote,currentTotalMark,totalPossibleMarks,xhtml,expired,lastActive FROM display_cache WHERE qID=".$qid." ORDER BY id"; + } + //else $sql = "SELECT * FROM display_cache ORDER BY id"; + else $sql = "SELECT id,qID,answers,questionNote,currentTotalMark,totalPossibleMarks,xhtml,expired,lastActive FROM display_cache ORDER BY id"; + + $pager = new ADODB_Pager($this->conn,$sql,'adodb', true); + $pager->Render(); + //return $xhtml; + } + public function elementsToHTML($a) { foreach($a as $key => $value) { *************** *** 448,455 **** public function getInstances($qID) { ! $sql= "SELECT display_cache.id, xhtml, questionNote FROM display_cache, display_cache_sequence " . "WHERE currentNode = 0 " . "AND nextNode = display_cache.id " . ! "AND qID=$qID"; $this->query($sql); --- 594,604 ---- public function getInstances($qID) { ! $sql= "SELECT display_cache.state, display_cache.id, xhtml, questionNote " . ! "FROM display_cache, display_cache_sequence " . "WHERE currentNode = 0 " . "AND nextNode = display_cache.id " . ! "AND qID=$qID " . ! "ORDER BY questionNote"; ! $this->query($sql); *************** *** 461,466 **** $array[$f] = $this->result($i, $f); } $result[$i] = $array; ! } return $result; } --- 610,620 ---- $array[$f] = $this->result($i, $f); } + // potentially costly item deserialisation but can we justify an extra field for reversion? + //$item = $this->base64_unserialize($this->result($i, 'state')); + + //$array['version'] = $item->getVersion(); + $result[$i] = $array; ! } return $result; } *************** *** 477,480 **** --- 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) { *************** *** 482,486 **** "WHERE currentNode = 0 " . "AND nextNode = display_cache.id " . ! "AND qID=$qID"; $this->query($sql); --- 651,655 ---- "WHERE currentNode = 0 " . "AND nextNode = display_cache.id " . ! "AND qID=$qID "; $this->query($sql); *************** *** 497,510 **** } ! // Returns array of question IDs in cache. // To help identify errors, not restricted to states that follow an initial state. ! public function getQuestionIDs() { ! $sql = "SELECT qID, count(*) AS freq FROM display_cache GROUP BY qID ORDER BY freq DESC"; $this->query($sql); for($i=0; $i<$this->noRows(); $i++) { ! $qid[] = $this->result($i, 'qID'); ! // adoDB no like aliases: $qid[$this->result($i, 'qID')] = $qid[$this->result($i, 'freq')]; } ! return $qid; } --- 666,680 ---- } ! // Returns array of frequencies of the question states in cache. // To help identify errors, not restricted to states that follow an initial state. ! public function getQuestionFreqs() { ! $sql = "SELECT qID, count(qID) as freq FROM display_cache GROUP BY qID"; $this->query($sql); for($i=0; $i<$this->noRows(); $i++) { ! $qid = $this->result($i, 'qID'); ! $freq = $this->result($i, 'freq'); ! $freqMap[$qid] = $freq; } ! return $freqMap; } *************** *** 529,532 **** --- 699,719 ---- } + /** + * Effectively 'undeploys' a version instance without removing it (and it's sucessors) from + * the cache as dropState() does. + */ + public function deinitialiseState($id) { + $this->query("DELETE FROM display_cache_sequence WHERE currentNode=0 AND nextNode=$id"); + } + + // checks whether a version is deployed, i.e. has instances which are initial states + // return number of insances deployed + public function instancesDeployed($id) { + $this->query("SELECT * FROM display_cache, display_cache_sequence " . + "WHERE display_cache.qID=$id AND currentNode=0 AND nextNode=display_cache.id"); + return $this->noRows(); + } + + // returns the serialised ItemState for given id. // This will supersede getDisplayItem Index: devCache.php =================================================================== RCS file: /cvsroot/stack/stack-dev/lib/database/devCache.php,v retrieving revision 1.11.6.1 retrieving revision 1.11.6.1.2.1 diff -C2 -d -r1.11.6.1 -r1.11.6.1.2.1 *** devCache.php 3 Nov 2009 16:03:28 -0000 1.11.6.1 --- devCache.php 9 Nov 2010 16:51:35 -0000 1.11.6.1.2.1 *************** *** 20,29 **** require_once $root.'/lib/ui/DisplayItem.php'; require_once $root.'/lib/ui/frontend.php'; ! //echo html_head('dev cache','../../',"@import \"../../styles.css\";\n"); $cache = new StackDBCache(); $cache->connect(); ! $qidfilter = $_GET['qid']; // process button presses --- 20,30 ---- require_once $root.'/lib/ui/DisplayItem.php'; require_once $root.'/lib/ui/frontend.php'; ! //require_once $root.'/other/jquery.js'; ! echo html_head('dev cache','../../',"@import \"../../styles.css\";\n", 'LaTeX', 1); $cache = new StackDBCache(); $cache->connect(); ! $qid = $_GET['qid']; // process button presses *************** *** 39,43 **** ?> <head> ! <title>Direct Cache Access</title> <style> .box { --- 40,44 ---- ?> <head> ! <title>CAS Cache Admin</title> <style> .box { *************** *** 46,78 **** } </style> </head> ! <div class="box"> ! <?php echo $cache->dumpCacheSequence($qidfilter) ?> ! </div> ! <div class="box"> <?php ! $allQs = $cache->getQuestionIDs(); if(NULL !== $allQs) { ! echo "Show question: "; ! if(empty($qidfilter)) echo "All "; ! else echo "<a href='devCache.php'>All</a> "; ! foreach($allQs as $qid) { ! if($qid == $qidfilter) echo $qid." "; ! else echo "<a href='?qid=".$qid."'>$qid</a> "; } } else echo "Cache is completely empty."; - ?> - <form method="POST" action="devCache.php"> - <input type="submit" value="Refresh"> - <input type="submit" name="op" value="clear successive states"> - <input type="submit" name="op" onClick="return confirm('Are you sure you want to clear the ENTIRE cache?')" value="clear ENTIRE cache"> - </form> - </div> - <br clear="left" /> <?php ! echo $cache->dumpCacheState($qidfilter); ! ?> \ No newline at end of file --- 47,113 ---- } + .hidden { + display: none; + } + + table { + border: 1; + } + + tr, td, th { + border: 0; + } + + .freq { + color: #ccc; + font-size: small; + } + </style> </head> ! <body> ! <form method="POST" action="<?php echo htmlentities($_SERVER['PHP_SELF']); ?>"> ! <input type="submit" name="op" value="clear non-initial states"> ! <input type="submit" name="op" onClick="return confirm('Are you sure you want to clear the ENTIRE cache?')" value="clear ENTIRE cache"> ! <b>Note:</b> Initial states represent deployed questions. ! </form> ! ! ! <h2>Questions with cached states</h2> <?php ! $allQs = $cache->getQuestionFreqs(); if(NULL !== $allQs) { ! echo "Questions: "; ! foreach($allQs as $id => $freq) { ! if($id == $qid) echo $id."<span class='freq'>×$freq</span> "; ! else echo "<a href='?qid=".$id."'>$id</a><span class='freq'>×$freq</span> "; } } else echo "Cache is completely empty."; ?> <?php ! //var_dump($qid); ! if(isset($_GET['sid'])) { ! echo "<h2>State #$_GET[sid]</h2>"; ! echo $cache->showCacheState($_GET['sid']); ! } ! if(!empty($qid)) { ! echo "<h2>Initial states for question #$qid</h2>"; ! echo $cache->showInitialStates($qid); ! } ! ! //echo $cache->dumpCacheSequence($qid); ! ?> ! <script> ! $(document).ready(function() { ! /* $('table#statestable td:last-child') ! .wrapInner('<div class="hidden">') ! .mouseover(function(x) {$(this).children('.hidden').slideDown('fast').next().remove()}) ! .append('<p>hover over to view</p>');*/ ! ! $('tr:odd').css('background-color', '#e6e6e6'); ! $('tr:even').css('background-color', '#ffffff'); ! }); ! </script> ! </body> \ No newline at end of file Index: StackDBReporting.php =================================================================== RCS file: /cvsroot/stack/stack-dev/lib/database/StackDBReporting.php,v retrieving revision 1.27 retrieving revision 1.27.8.1 diff -C2 -d -r1.27 -r1.27.8.1 *** StackDBReporting.php 16 Sep 2009 12:04:22 -0000 1.27 --- StackDBReporting.php 9 Nov 2010 16:51:35 -0000 1.27.8.1 *************** *** 26,30 **** * Provides the database functions for reporting students answers. * - * */ class StackDBReporting extends StackDBADOdb { --- 26,29 ---- *************** *** 43,593 **** } [...1354 lines suppressed...] + // { + // $attemptId = $this->result($i, 'AttemptID'); + // $questionId = $this->result($i, 'QuestionID'); + // $userId = $this->result($i, 'UserID'); + // $PRTName = $this->result($i, 'PRTName'); + // $RawMark = $this->result($i, 'RawMark'); + // $ModMark = $this->result($i, 'ModMark'); + // + // if(!array_key_exists($questionId, $resultsArray) || !array_key_exists($userId, $resultsArray[$questionId])) + // { + // $resultsArray[$questionId][$userId] = new MarksAttempt($userId, $questionId); + // } + // $resultsArray[$questionId][$userId]->addAttempt($attemptId, $PRTName, $RawMark, $ModMark); + // } + // + // return $resultsArray; + // } + // } Index: StackDBBackup.php =================================================================== RCS file: /cvsroot/stack/stack-dev/lib/database/StackDBBackup.php,v retrieving revision 1.5 retrieving revision 1.5.8.1 diff -C2 -d -r1.5 -r1.5.8.1 *** StackDBBackup.php 27 Aug 2009 16:23:00 -0000 1.5 --- StackDBBackup.php 9 Nov 2010 16:51:35 -0000 1.5.8.1 *************** *** 19,25 **** * Database backup functions */ ! require_once 'StackDBMySQL.php'; ! class StackDBBackup extends StackDBMySQL { // Attributes --- 19,25 ---- * Database backup functions */ ! require_once 'StackDBADOdb.php'; ! class StackDBBackup extends StackDBADOdb { // Attributes Index: StackDBItem.php =================================================================== RCS file: /cvsroot/stack/stack-dev/lib/database/StackDBItem.php,v retrieving revision 1.32 retrieving revision 1.32.8.1 diff -C2 -d -r1.32 -r1.32.8.1 *** StackDBItem.php 19 Oct 2009 11:45:01 -0000 1.32 --- StackDBItem.php 9 Nov 2010 16:51:35 -0000 1.32.8.1 *************** *** 71,74 **** --- 71,75 ---- questionKeywords , questionUserLastEdited , + line , questionPublisher , questionFormat , *************** *** 90,96 **** ) VALUES ( ! $meta[questionGUID], $meta[questionName], $meta[questionDescription], $meta[questionKeywords], $meta[lastUserEditor], $meta[questionPublisher], $meta[questionFormat], $meta[language], $meta[questionRights], $meta[questionLearningContext], $meta[questionDifficulty], $meta[questionCompetency], $meta[questionCompentencyLevel], $meta[questionTimeAllocated], $meta[questionExerciseType], ".$this->conn->sysTimeStamp." , ".$this->dbSafeString($dbQuestionVariables).", ".$this->dbSafeString($dbItemOptions).", ".$this->dbSafeString($dbItemTests)." , ".$this->dbSafeString($valid==1?1:0)." , ".$this->dbSafeString($status)." , ".$this->dbSafeString($published).");"; ! $added = $this->query($sql); if($added) --- 91,97 ---- ) VALUES ( ! $meta[questionGUID], $meta[questionName], $meta[questionDescription], $meta[questionKeywords], $meta[lastUserEditor], $meta[line], $meta[questionPublisher], $meta[questionFormat], $meta[language], $meta[questionRights], $meta[questionLearningContext], $meta[questionDifficulty], $meta[questionCompetency], $meta[questionCompentencyLevel], $meta[questionTimeAllocated], $meta[questionExerciseType], ".$this->conn->sysTimeStamp." , ".$this->dbSafeString($dbQuestionVariables).", ".$this->dbSafeString($dbItemOptions).", ".$this->dbSafeString($dbItemTests)." , ".$this->dbSafeString($valid==1?1:0)." , ".$this->dbSafeString($status)." , ".$this->dbSafeString($published).");"; + $this->logger->debug($sql); $added = $this->query($sql); if($added) *************** *** 98,103 **** //get question id for newly added item & return. //$result = $this->query("SELECT `questionID` FROM `stackQuestion` WHERE `questionGUID` = '$meta[questionGUID]'"); ! $result = $this->query("SELECT max(questionId) FROM stackquestion"); ! $result = $this->result(0,0); //echo 'Last inserted: '.$result; } --- 99,104 ---- //get question id for newly added item & return. //$result = $this->query("SELECT `questionID` FROM `stackQuestion` WHERE `questionGUID` = '$meta[questionGUID]'"); ! $result = $this->query("SELECT questionID FROM stackquestion ORDER BY questionID desc limit 1"); ! $result = $this->result(0,'questionID'); //echo 'Last inserted: '.$result; } *************** *** 117,123 **** * @return int * @access public ! */ public function updateItem($id, $dbItemOptions, $dbItemTests, $dbQuestionVariables, $meta, $valid, $status, $published) { //make meta db safe foreach($meta as $label => $val) --- 118,128 ---- * @return int * @access public ! **/ public function updateItem($id, $dbItemOptions, $dbItemTests, $dbQuestionVariables, $meta, $valid, $status, $published) { + //return addNewItem($dbItemOptions, $dbItemTests, $dbQuestionVariables, $meta, $valid, $status, $published); + + //update line + //make meta db safe foreach($meta as $label => $val) *************** *** 142,146 **** //build query - $sql = "UPDATE stackquestion SET questionGUID = $meta[questionGUID], questionName = $meta[questionName], --- 147,150 ---- *************** *** 148,151 **** --- 152,156 ---- questionKeywords = $meta[questionKeywords], questionUserLastEdited = $meta[lastUserEditor], + line = $meta[line], questionPublisher = $meta[questionPublisher], questionFormat = $meta[questionFormat], *************** *** 167,185 **** WHERE questionID = $id;"; ! $updated = $this->query($sql); if($updated) { ! return $id; } else { ! $this->errorLog->addError('Could not update Item'); ! $this->logger->critical('Failed to save item id '.$id); ! return false; } } /** --- 172,224 ---- WHERE questionID = $id;"; ! $updated = $this->query($sql); if($updated) { ! $this->logger->fine('Item updated.'); ! $result = true; } else { ! $result = false; ! $this->errorLog->addError('Could not update item.'); ! $this->logger->critical('Failed to update item named '.$meta['questionDescription'].' to the database'); } + return $result; + }//*/ + + /** + + * @param + * @return bool + * @access public + */ + public function newLine() + { + $sql = "INSERT INTO question_lines() VALUES()"; + $this->logger->debug($sql); + $this->query($sql); + + $this->query("SELECT id FROM question_lines ORDER BY id DESC LIMIT 1"); + return $this->result(0,'id'); } + public function setLatestVersion($line, $version) { + $sql = "UPDATE question_lines SET latestVersion=$version WHERE id=$line"; + $this->logger->debug("trying to set latest version with sql:$sql"); + $this->query($sql); + } + + public function getLatestVersion($qid) { + $sql = "SELECT latestVersion FROM question_lines, stackquestion WHERE questionID=$qid AND line=question_lines.id"; + $this->logger->debug("trying to get latest version with sql:$sql"); + $this->query($sql); + return $this->result(0, 'latestVersion'); + } + public function nameExists($name) { + $this->query("SELECT * FROM stackquestion WHERE questionName='$name'"); + return $this->noRows() > 0; + } /** *************** *** 207,210 **** --- 246,283 ---- } + /** + * Removes a line of items, their PRTrees and keywords + * + * @param int $id + * @return bool + * @access public + */ + public function removeLineContainingItem($id) + { + // get line from item id + $sql = "SELECT line FROM stackquestion WHERE questionID = $id"; + $result = $this->query($sql); + $line = $this->result(0, 'line'); + $this->logger->finer('Removing line '.$line); + + // get all question IDs + $sql = "SELECT questionID from stackquestion WHERE line = $line"; + $result = $this->query($sql); + if($result !== false) { + foreach($result as $row) { + $toRemove[] = $row['questionID']; + } + } + + foreach($toRemove as $id) { + $this->removeItem($id); + } + + // finally, remove line record + $sql = "DELETE FROM question_lines WHERE id = $line"; + $result = $this->query($sql); + return $result; + } + /** * Removes an items PRTrees. *************** *** 417,420 **** --- 490,531 ---- } + /** + * Returns the line that contains this Item version + * + * @param int $id + * @return int version + **/ + public function getLine($id) { + $sql = "SELECT line FROM stackquestion WHERE questionID = $id"; + //echo $sql; + $result = $this->query($sql); + if(!$result) + { + $this->logger->critical('No line recorded for item with id '.$id); + return false; + } + else + { + $toReturn = $this->result(0,'line'); + return $toReturn; + } + + } + + // returned in descending order + public function getVersions($line) { + if(!is_numeric($line)) { + $this->logger->critical("line not defined!"); + } + $sql = "SELECT questionID, questionDateLastEdited from stackquestion WHERE line=$line ORDER BY questionID DESC"; + //echo $sql; + $result = $this->query($sql); + if($result !== false) { + foreach($result as $row) { + $history[$row['questionDateLastEdited']] = $row['questionID']; + } + } + return $history; + } /** *************** *** 429,437 **** { $this->logger->finest('getListOfQuestions, userID='.$userID); ! $dateFormat = stack_sqlDateFormat(true); //uses to date format in the config file to give a sql date format. ! $dateQuery = $this->conn->SQLDate($dateFormat,'questionDateLastEdited'); ! $OrderBy = ' ORDER BY questionID'; // First level if (NULL != $filter) { --- 540,551 ---- { $this->logger->finest('getListOfQuestions, userID='.$userID); + $this->logger->debug('getListOfQuestions, filter='.print_r($filter, 1)); ! $qList = NULL; ! //$dateFormat = stack_sqlDateFormat(true); //uses to date format in the config file to give a sql date format. ! //$dateQuery = $this->conn->SQLDate($dateFormat,'questionDateLastEdited'); ! ! $OrderBy = ' ORDER BY questionID DESC'; // First level if (NULL != $filter) { *************** *** 453,471 **** } ! if($userID === NULL) { ! $sql = "SELECT questionID, questionName, questionDescription, valid, published, status, $dateQuery AS dateEdited FROM stackquestion".$OrderBy; ! } else { if ($public) { ! $sql = "SELECT questionID, questionName, questionDescription, valid, published, status, $dateQuery AS dateEdited FROM stackquestion WHERE (questionUserLastEdited = $userID or published=".$this->dbSafeString('Published').")".$OrderBy; } else { ! $sql = "SELECT questionID, questionName, questionDescription, valid, published, status, $dateQuery AS dateEdited FROM stackquestion WHERE (questionUserLastEdited = $userID)".$OrderBy; } } - $this->logger->finest('getListOfQuestions, sql='.$sql); - $result = $this->query($sql); if($result === false) --- 567,691 ---- } + $sql = "SELECT questionID, line, questionName, questionDescription, valid, published, status, questionDateLastEdited AS dateEdited " . + "FROM stackquestion, question_lines"; + if(!empty($tags)) $sql .= ", question_keyword, keywords "; // tbc + if($userID !== NULL) { + if ($public) { + $sql .= " WHERE (questionUserLastEdited = $userID or published=".$this->dbSafeString('Published').")"; + } else { + $sql .= " WHERE (questionUserLastEdited = $userID)"; + } + } + + if($userID !== NULL) $sql .= " AND "; + else $sql .= " WHERE "; + $sql .= "question_lines.latestVersion = stackquestion.questionID"; + + // filter + //if($filter !== NULL) $sql .= " AND questionName LIKE '%$filter%' "; + $sql .= $OrderBy; ! $this->logger->debug('getListOfQuestions, sql='.$sql); ! $result = $this->query($sql); ! //print_r($sql); ! ! if($result === false) { ! $this->logger->error('getListOfQuestions: Failed to load question list'); ! return false; ! } ! else ! { ! 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) ! { ! $qList[$i]['id'] = $this->result($i,'questionID'); ! $qList[$i]['line'] = $this->result($i,'line'); ! $qList[$i]['questionName'] = $this->result($i,'questionName'); ! $qList[$i]['questionDescription'] = $this->result($i, 'questionDescription'); ! $qList[$i]['valid'] = $this->result($i,'valid'); ! $qList[$i]['questionDateLastEdited'] = $this->result($i,'dateEdited'); ! $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'); ! if('true' === $moodleEnabled) ! { ! $mdb = new MoodleDB(); ! $mdb->connect(); ! foreach($qList as $key => $val){ ! $qList[$key]['moodleCategories'] = $mdb->getQuestionCategoryName($val['id']); ! } ! $mdb->disconnect(); ! } ! ! } ! return $qList; ! } ! } ! ! public function getLines($userID=NULL,$public=false,$filter=NULL,$tags=NULL) ! { ! $this->logger->finest('getLines, userID='.$userID); ! $this->logger->debug('getLines, filter='.print_r($filter, 1)); ! ! $qList = NULL; ! ! //$dateFormat = stack_sqlDateFormat(true); //uses to date format in the config file to give a sql date format. ! //$dateQuery = $this->conn->SQLDate($dateFormat,'questionDateLastEdited'); ! ! $OrderBy = ' ORDER BY questionID DESC'; ! ! $sql = "SELECT questionID, line, questionName, questionDescription, valid, published, status, questionDateLastEdited AS dateEdited " . ! "FROM stackquestion, question_lines "; ! if(!empty($tags)) $sql .= ", question_keyword, keywords "; ! if($userID !== NULL) { if ($public) { ! $where[] = "(questionUserLastEdited = $userID or published=".$this->dbSafeString('Published').")"; } else { ! $where[] = "(questionUserLastEdited = $userID)"; } } + if(!empty($tags)) { + // create multi-keyword conditions + foreach(explode(' ', $tags) as $tag) { + $or[] = "keywords.keyword = ".$this->dbSafeString($tag); + + } + $where[] = '('.implode($or, ' OR ').')'; + $where[] = "keywords.id = kwid AND qid = stackquestion.questionID"; + } + + $where[] = "question_lines.latestVersion = stackquestion.questionID"; + + // filter + if($filter !== NULL) $where[] = "(questionName LIKE '%$filter%' OR questionDescription LIKE '%$filter%')"; + + $sql .= ' WHERE '.implode($where, ' AND '); // stitch conditions together + + $sql .= $OrderBy; + + $this->logger->debug('getListOfQuestions, sql='.$sql); + $result = $this->query($sql); if($result === false) *************** *** 486,489 **** --- 706,710 ---- { $qList[$i]['id'] = $this->result($i,'questionID'); + $qList[$i]['line'] = $this->result($i,'line'); $qList[$i]['questionName'] = $this->result($i,'questionName'); $qList[$i]['questionDescription'] = $this->result($i, 'questionDescription'); *************** *** 492,495 **** --- 713,770 ---- $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'); + if('true' === $moodleEnabled) + { + $mdb = new MoodleDB(); + $mdb->connect(); + foreach($qList as $key => $val){ + $qList[$key]['moodleCategories'] = $mdb->getQuestionCategoryName($val['id']); + } + $mdb->disconnect(); + } + } + return $qList; + } + } + + + public function getQuestionsTagged($tagLine, $userID=NULL,$public=false) { + $tag = $tagLine; // to explode + $sql = "SELECT stackquestion.* from stackquestion, question_keyword, keywords, question_lines " . + "WHERE keywords.keyword = ".$this->dbSafeString($tag)." " . + "AND keywords.id = kwid " . + "AND qid = stackquestion.questionID " . + "AND qid = latestVersion"; + $result = $this->query($sql); + + if($result === false) + { + $this->logger->error('getListOfQuestions: Failed to load question list'); + return false; + } + else + { + global $config; + + $i=0; + if($this->noRows()>0) + { + $dbc = new StackDBCache(); + $dbc->connect(); + foreach($result as $row) + { + $qList[$i]['id'] = $this->result($i,'questionID'); + $qList[$i]['questionName'] = $this->result($i,'questionName'); + $qList[$i]['questionDescription'] = $this->result($i, 'questionDescription'); + $qList[$i]['valid'] = $this->result($i,'valid'); + $qList[$i]['questionDateLastEdited'] = $this->result($i,'questionDateLastEdited'); + $qList[$i]['published'] = $this->result($i,'published'); + $qList[$i]['status'] = $this->result($i,'status'); $qList[$i]['nodeployed'] = $dbc->countInstances($qList[$i]['id']); *************** *** 513,517 **** return $qList; } ! } /** --- 788,793 ---- return $qList; } ! ! } /** *************** *** 581,585 **** } } - } --- 857,860 ---- Index: StackDBKeywords.php =================================================================== RCS file: /cvsroot/stack/stack-dev/lib/database/StackDBKeywords.php,v retrieving revision 1.10 retrieving revision 1.10.8.1 diff -C2 -d -r1.10 -r1.10.8.1 *** StackDBKeywords.php 27 Aug 2009 16:23:00 -0000 1.10 --- StackDBKeywords.php 9 Nov 2010 16:51:35 -0000 1.10.8.1 *************** *** 261,271 **** /** * Returns all the keywords currently in the database ! * * @access public * @return array */ ! public function getAllKeywords() { ! $sql = 'SELECT keyword FROM keywords'; $query = $this->query($sql); --- 261,277 ---- /** * Returns all the keywords currently in the database ! *... [truncated message content] |
