From d1aaba539247b3602d8e7351ecdc694c5cdc33d1 Mon Sep 17 00:00:00 2001
From: Benjamin Franzke <benjaminfranzke@googlemail.com>
Date: Fri, 23 Nov 2012 10:15:28 +0100
Subject: sqlbox: Compare user query results from a definable query

---
 sqlbox/db/install.xml |  5 +++--
 sqlbox/db/upgrade.php | 12 ++++++++++++
 sqlbox/mod_form.php   |  2 ++
 sqlbox/version.php    |  2 +-
 sqlbox/view.php       |  9 +++++++--
 5 files changed, 25 insertions(+), 5 deletions(-)

diff --git a/sqlbox/db/install.xml b/sqlbox/db/install.xml
index f83ed09..2c818c1 100644
--- a/sqlbox/db/install.xml
+++ b/sqlbox/db/install.xml
@@ -10,8 +10,9 @@
         <FIELD NAME="course" TYPE="int" LENGTH="10" NOTNULL="true" UNSIGNED="true" SEQUENCE="false" COMMENT="Course sqlbox activity belongs to" PREVIOUS="id" NEXT="name"/>
         <FIELD NAME="name" TYPE="char" LENGTH="255" NOTNULL="true" SEQUENCE="false" COMMENT="name field for moodle instances" PREVIOUS="course" NEXT="intro"/>
         <FIELD NAME="intro" TYPE="text" LENGTH="big" NOTNULL="false" SEQUENCE="false" COMMENT="General introduction of the sqlbox activity" PREVIOUS="name" NEXT="introformat"/>
-        <FIELD NAME="introformat" TYPE="int" LENGTH="4" NOTNULL="true" UNSIGNED="true" DEFAULT="0" SEQUENCE="false" COMMENT="Format of the intro field (MOODLE, HTML, MARKDOWN...)" PREVIOUS="intro" NEXT="timecreated"/>
-        <FIELD NAME="timecreated" TYPE="int" LENGTH="10" NOTNULL="true" UNSIGNED="true" SEQUENCE="false" PREVIOUS="introformat" NEXT="timemodified"/>
+        <FIELD NAME="introformat" TYPE="int" LENGTH="4" NOTNULL="true" UNSIGNED="true" DEFAULT="0" SEQUENCE="false" COMMENT="Format of the intro field (MOODLE, HTML, MARKDOWN...)" PREVIOUS="intro" NEXT="query"/>
+        <FIELD NAME="query" TYPE="text" LENGTH="big" NOTNULL="true" SEQUENCE="false" COMMENT="Example query" PREVIOUS="introformat" NEXT="timecreated"/>
+        <FIELD NAME="timecreated" TYPE="int" LENGTH="10" NOTNULL="true" UNSIGNED="true" SEQUENCE="false" PREVIOUS="query" NEXT="timemodified"/>
         <FIELD NAME="timemodified" TYPE="int" LENGTH="10" NOTNULL="true" UNSIGNED="true" DEFAULT="0" SEQUENCE="false" PREVIOUS="timecreated"/>
       </FIELDS>
       <KEYS>
diff --git a/sqlbox/db/upgrade.php b/sqlbox/db/upgrade.php
index 83540aa..fb0c671 100644
--- a/sqlbox/db/upgrade.php
+++ b/sqlbox/db/upgrade.php
@@ -68,6 +68,18 @@ function xmldb_sqlbox_upgrade($oldversion) {
     // and to play with the XMLDB Editor (in the admin menu) and its
     // PHP generation posibilities.
 
+    if ($oldversion < 20121223) {
+        // Define field query to be added to sqlbox
+        $table = new xmldb_table('sqlbox');
+        $field = new xmldb_field('query', XMLDB_TYPE_TEXT, 'medium',
+                                 null, null, null, null, 'query');
+
+        // Add field query
+        if (!$dbman->field_exists($table, $field)) {
+            $dbman->add_field($table, $field);
+        }
+    }
+
     // First example, some fields were added to install.xml on 2007/04/01
     if ($oldversion < 2007040100) {
 
diff --git a/sqlbox/mod_form.php b/sqlbox/mod_form.php
index 41d1ad5..4f7c820 100644
--- a/sqlbox/mod_form.php
+++ b/sqlbox/mod_form.php
@@ -61,6 +61,8 @@ class mod_sqlbox_mod_form extends moodleform_mod {
         // Adding the standard "intro" and "introformat" fields
         $this->add_intro_editor();
 
+        $mform->addElement('text', 'query', 'SQL Query', 'size="50"');
+
         //-------------------------------------------------------------------------------
         // Adding the rest of sqlbox settings, spreeading all them into this fieldset
         // or adding more fieldsets ('header' elements) if needed for better logic
diff --git a/sqlbox/version.php b/sqlbox/version.php
index c73c9d3..07a2f4d 100644
--- a/sqlbox/version.php
+++ b/sqlbox/version.php
@@ -30,7 +30,7 @@
 
 defined('MOODLE_INTERNAL') || die();
 
-$module->version   = 2012112200;      // The current module version (Date: YYYYMMDDXX)
+$module->version   = 2012112300;      // 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 d095376..5c7c4e1 100644
--- a/sqlbox/view.php
+++ b/sqlbox/view.php
@@ -85,10 +85,15 @@ if (isset($data->sqlbox_query)) {
 		error("Failed to connect to sqlbox db");
 
 	$table = db_get_table_from_query($db, $data->sqlbox_query);
+	$table_orig = db_get_table_from_query($db, $sqlbox->query);
+	pg_close($db);
 
 	echo html_writer::table($table);
-	
-	pg_close($db);
+
+	if ($table->data ==  $table_orig->data && $table->head == $table->head) 
+		echo $OUTPUT->heading("Correct!");
+	else
+		echo $OUTPUT->heading("Incorrect!");
 }
 
 // Finish the page
-- 
cgit