Store user query and if query was correct

5 files changed, 85 insertions, 2 deletions

diff --git a/sqlbox/db/install.xml b/sqlbox/db/install.xml
index 2c818c1..9123411 100644
--- a/sqlbox/db/install.xml
+++ b/sqlbox/db/install.xml
@@ -4,7 +4,7 @@
     xsi:noNamespaceSchemaLocation="../../../lib/xmldb/xmldb.xsd"
 >
   <TABLES>
-    <TABLE NAME="sqlbox" COMMENT="Default comment for sqlbox, please edit me">
+    <TABLE NAME="sqlbox" COMMENT="Default comment for sqlbox, please edit me" NEXT="sqlbox_solutions">
       <FIELDS>
         <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" UNSIGNED="true" SEQUENCE="true" NEXT="course"/>
         <FIELD NAME="course" TYPE="int" LENGTH="10" NOTNULL="true" UNSIGNED="true" SEQUENCE="false" COMMENT="Course sqlbox activity belongs to" PREVIOUS="id" NEXT="name"/>
@@ -22,5 +22,21 @@
         <INDEX NAME="course" UNIQUE="false" FIELDS="course"/>
       </INDEXES>
     </TABLE>
+    <TABLE NAME="sqlbox_solutions" COMMENT="Solutions filed by users" PREVIOUS="sqlbox">
+      <FIELDS>
+        <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="true" NEXT="sqlboxid"/>
+        <FIELD NAME="sqlboxid" TYPE="int" LENGTH="10" NOTNULL="true" DEFAULT="0" SEQUENCE="false" PREVIOUS="id" NEXT="userid"/>
+        <FIELD NAME="userid" TYPE="int" LENGTH="10" NOTNULL="true" DEFAULT="0" SEQUENCE="false" PREVIOUS="sqlboxid" NEXT="query"/>
+        <FIELD NAME="query" TYPE="text" LENGTH="big" NOTNULL="true" SEQUENCE="false" PREVIOUS="userid" NEXT="correct"/>
+        <FIELD NAME="correct" TYPE="int" LENGTH="1" NOTNULL="false" SEQUENCE="false" PREVIOUS="query"/>
+      </FIELDS>
+      <KEYS>
+        <KEY NAME="primary" TYPE="primary" FIELDS="id" NEXT="sqlboxid"/>
+        <KEY NAME="sqlboxid" TYPE="foreign" FIELDS="sqlboxid" REFTABLE="sqlbox" REFFIELDS="id" PREVIOUS="primary"/>
+      </KEYS>
+      <INDEXES>
+        <INDEX NAME="userid" UNIQUE="false" FIELDS="userid"/>
+      </INDEXES>
+    </TABLE>
   </TABLES>
 </XMLDB>
diff --git a/sqlbox/db/upgrade.php b/sqlbox/db/upgrade.php
index fb0c671..08724e4 100644
--- a/sqlbox/db/upgrade.php
+++ b/sqlbox/db/upgrade.php
@@ -67,7 +67,49 @@ function xmldb_sqlbox_upgrade($oldversion) {
     //   http://docs.moodle.org/en/Development:XMLDB_Documentation
     // and to play with the XMLDB Editor (in the admin menu) and its
     // PHP generation posibilities.
+    if ($oldversion < 2013012402) {
+        $table = new xmldb_table('sqlbox_solutions');
+        $field = new xmldb_field('correct', XMLDB_TYPE_INTEGER, '1', null, null, null, null, 'query');
+        if (!$dbman->field_exists($table, $field)) {
+            $dbman->add_field($table, $field);
+        }
+        upgrade_mod_savepoint(true, 2013012402, 'sqlbox');
+    }
+    if ($oldversion < 2013012401) {
+        $table = new xmldb_table('sqlbox_solutions');
+        $fieldold = new xmldb_field('correct');
+        if ($dbman->field_exists($table, $fieldold)) {
+            $dbman->drop_field($table, $fieldold);
+        }
+        // sqlbox savepoint reached
+        upgrade_mod_savepoint(true, 2013012401, 'sqlbox');
+    }
+    if ($oldversion < 2013012400) {
+        // Define table sqlbox_solutions to be created
+        $table = new xmldb_table('sqlbox_solutions');
+
+        // Adding fields to table sqlbox_solutions
+        $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
+        $table->add_field('sqlboxid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
+        $table->add_field('userid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
+        $table->add_field('query', XMLDB_TYPE_TEXT, null, null, XMLDB_NOTNULL, null, null);
+        $table->add_field('correct', XMLDB_TYPE_BINARY, null, null, null, null, null);
+
+        // Adding keys to table sqlbox_solutions
+        $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
+        $table->add_key('sqlboxid', XMLDB_KEY_FOREIGN, array('sqlboxid'), 'sqlbox', array('id'));
+
+        // Adding indexes to table sqlbox_solutions
+        $table->add_index('userid', XMLDB_INDEX_NOTUNIQUE, array('userid'));
+
+        // Conditionally launch create table for sqlbox_solutions
+        if (!$dbman->table_exists($table)) {
+            $dbman->create_table($table);
+        }
 
+        // sqlbox savepoint reached
+        upgrade_mod_savepoint(true, 2013012400, 'sqlbox');
+    }
     if ($oldversion < 20121223) {
         // Define field query to be added to sqlbox
         $table = new xmldb_table('sqlbox');
diff --git a/sqlbox/locallib.php b/sqlbox/locallib.php
index f335b9a..1646bbf 100644
--- a/sqlbox/locallib.php
+++ b/sqlbox/locallib.php
@@ -55,3 +55,26 @@ function db_table_from_query($db, $query) {
 
 	return $table;
 }
+
+function sqlbox_submit_response($query, $sqlbox, $correct, $userid) {
+    global $DB, $CFG;
+    require_once($CFG->libdir.'/completionlib.php');
+
+    // TODO: Add timemodified
+    //
+    $current = $DB->get_record('sqlbox_solutions', array('sqlboxid' => $sqlbox->id, 'userid' => $userid));
+    if ($current) {
+        $newsolution = $current;
+    } else {
+        $newsolution = new stdClass();
+        $newsolution->userid = $userid;
+        $newsolution->sqlboxid = $sqlbox->id;
+    }
+    $newsolution->query = $query;
+    $newsolution->correct = $correct;
+    
+    if ($current)
+        $DB->update_record("sqlbox_solutions", $newsolution);
+    else
+        $DB->insert_record("sqlbox_solutions", $newsolution);
+}
diff --git a/sqlbox/version.php b/sqlbox/version.php
index 07a2f4d..304185d 100644
--- a/sqlbox/version.php
+++ b/sqlbox/version.php
@@ -30,7 +30,7 @@
 
 defined('MOODLE_INTERNAL') || die();
 
-$module->version   = 2012112300;      // The current module version (Date: YYYYMMDDXX)
+$module->version   = 2013012402;      // The current module version (Date: YYYYMMDDXX)
 $module->requires  = 2010031900;      // Requires this Moodle version
 $module->cron      = 0;               // Period for cron to check this module (secs)
 $module->component = 'mod_sqlbox'; // To check on upgrade, that module sits in correct place
diff --git a/sqlbox/view.php b/sqlbox/view.php
index eedca2b..7a151ce 100644
--- a/sqlbox/view.php
+++ b/sqlbox/view.php
@@ -102,6 +102,8 @@ if (isset($data->sqlbox_query)) {
 		echo $OUTPUT->heading("Correct!");
 	else
 		echo $OUTPUT->heading("Incorrect!");
+
+        sqlbox_submit_response($data->sqlbox_query, $sqlbox, $correct, $USER->id);
 }
 
 // Finish the page