summaryrefslogtreecommitdiff
path: root/sqlbox/db
diff options
context:
space:
mode:
Diffstat (limited to 'sqlbox/db')
-rw-r--r--sqlbox/db/install.xml18
-rw-r--r--sqlbox/db/upgrade.php42
2 files changed, 59 insertions, 1 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');