From f3133619f0746bacd20c517c37d5807e95d21739 Mon Sep 17 00:00:00 2001 From: paca70 Date: Fri, 22 Oct 2004 14:56:49 +0000 Subject: [PATCH] Upgrades for protgresql. --- mod/lesson/db/postgres7.php | 139 +++++++++++++++++++++++++++++++++++- mod/lesson/db/postgres7.sql | 131 ++++++++++++++++++++++++++------- 2 files changed, 242 insertions(+), 28 deletions(-) diff --git a/mod/lesson/db/postgres7.php b/mod/lesson/db/postgres7.php index 1f52e6fe04..7cec72e717 100644 --- a/mod/lesson/db/postgres7.php +++ b/mod/lesson/db/postgres7.php @@ -39,7 +39,144 @@ function lesson_upgrade($oldversion) { if ($oldversion < 2004032700) { table_column("lesson_answers", "", "flags", "INTEGER", "4", "UNSIGNED", "0", "NOT NULL", "grade"); } - + // CDC-FLAG + if ($oldversion < 2004072100) { + execute_sql(" create table ".$CFG->prefix."lesson_high_scores + ( id serial8 primary key, + lessonid int8 not null, + userid int8 not null, + gradeid int8 not null, + nickname varchar(5) not null + )"); + + execute_sql(" create table ".$CFG->prefix."lesson_essay + ( id serial8 primary key, + lessonid int8 not null, + userid int8 not null, + pageid int8 not null, + answerid int8 not null, + try int8 not null, + answer text not null, + graded int4 not null default 0, + score int8 not null default 0, + response text not null, + sent int4 not null default 0, + timesubmitted int8 not null + )"); + + execute_sql(" create table ".$CFG->prefix."lesson_branch + ( id serial8 primary key, + lessonid int8 not null, + userid int8 not null, + pageid int8 not null, + retry int8 not null, + flag int4 not null, + timeseen int8 not null + )"); + + + execute_sql(" create table ".$CFG->prefix."lesson_timer + ( id serial8 primary key, + lessonid int8 not null, + userid int8 not null, + starttime int8 not null, + lessontime int8 not null + )"); + + + + //execute_sql(" ALTER TABLE {$CFG->prefix}lesson_pages ADD layout TINYINT(3) UNSIGNED NOT NULL DEFAULT '1' AFTER qoption"); + table_column('lesson_pages','','layout','int','3','unsigned', '1', 'not null', 'qoption'); + //execute_sql(" ALTER TABLE {$CFG->prefix}lesson_pages ADD display TINYINT(3) UNSIGNED NOT NULL DEFAULT '1' AFTER layout"); + table_column('lesson_pages','','display','int','3','unsigned', '1', 'not null', 'layout'); + + //execute_sql(" ALTER TABLE {$CFG->prefix}lesson_answers ADD score INT(10) NOT NULL DEFAULT '0' AFTER grade"); + table_column('lesson_answers','','score','int','10','unsigned', '1', 'not null', 'grade'); + + //execute_sql(" ALTER TABLE {$CFG->prefix}lesson ADD usepassword TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' AFTER name"); + table_column('lesson','','usepassword','int','3','unsigned', '0', 'not null', 'name'); + + //execute_sql(" ALTER TABLE {$CFG->prefix}lesson ADD password VARCHAR(32) NOT NULL DEFAULT '' AFTER usepassword"); + table_column('lesson','','password','varchar','32','', '', 'not null', 'usepassword'); + + //execute_sql(" ALTER TABLE {$CFG->prefix}lesson ADD custom TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' AFTER grade"); + table_column('lesson','','custom','int','3','unsigned', '0', 'not null', 'grade'); + + //execute_sql(" ALTER TABLE {$CFG->prefix}lesson ADD ongoing TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' AFTER custom"); + table_column('lesson','','ongoing','int','3','unsigned', '0', 'not null', 'custom'); + + //execute_sql(" ALTER TABLE {$CFG->prefix}lesson ADD timed TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' AFTER maxpages"); + table_column('lesson','','timed','int','3','unsigned', '0', 'not null', 'maxpages'); + + //execute_sql(" ALTER TABLE {$CFG->prefix}lesson ADD maxtime INT(10) UNSIGNED NOT NULL DEFAULT '0' AFTER timed"); + table_column('lesson','','maxtime','int','10','unsigned', '0', 'not null', 'timed'); + + //execute_sql(" ALTER TABLE {$CFG->prefix}lesson ADD tree TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' AFTER retake"); + table_column('lesson','','tree','int','3','unsigned', '0', 'not null', 'retake'); + + //execute_sql(" ALTER TABLE {$CFG->prefix}lesson ADD slideshow TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' AFTER tree"); + table_column('lesson','','slideshow','int','3','unsigned', '0', 'not null', 'tree'); + + //execute_sql(" ALTER TABLE {$CFG->prefix}lesson ADD width INT(10) UNSIGNED NOT NULL DEFAULT '640' AFTER slideshow"); + table_column('lesson','','width','int','10','unsigned', '640', 'not null', 'slideshow'); + + //execute_sql(" ALTER TABLE {$CFG->prefix}lesson ADD height INT(10) UNSIGNED NOT NULL DEFAULT '480' AFTER width"); + table_column('lesson','','height','int','10','unsigned', '480', 'not null', 'width'); + + //execute_sql(" ALTER TABLE {$CFG->prefix}lesson ADD bgcolor CHAR(7) NOT NULL DEFAULT '#FFFFFF' AFTER height"); + table_column('lesson','','bgcolor','varchar','7','unsigned', '#FFFFFF', 'not null', 'height'); + + //execute_sql(" ALTER TABLE {$CFG->prefix}lesson ADD displayleft TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' AFTER bgcolor"); + table_column('lesson','','displayleft','int','3','unsigned', '0', 'not null', 'bgcolor'); + + //execute_sql(" ALTER TABLE {$CFG->prefix}lesson ADD highscores TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' AFTER displayleft"); + table_column('lesson','','highscores','int','3','unsigned', '0', 'not null', 'displayleft'); + + //execute_sql(" ALTER TABLE {$CFG->prefix}lesson ADD maxhighscores INT(10) UNSIGNED NOT NULL DEFAULT '0' AFTER highscores"); + table_column('lesson','','maxhighscores','int','10','unsigned', '0', 'not null', 'highscores'); + + } + + if ($oldversion < 2004081100) { + //execute_sql(" ALTER TABLE {$CFG->prefix}lesson ADD practice TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' AFTER name"); + table_column('lesson','','practice','int','3','unsigned', '0', 'not null', 'name'); + //execute_sql(" ALTER TABLE {$CFG->prefix}lesson ADD review TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' AFTER maxattempts"); + table_column('lesson','','review','int','3','unsigned', '0', 'not null', 'maxattempts'); + } + + if ($oldversion < 2004081700) { + execute_sql("CREATE TABLE {$CFG->prefix}lesson_default + ( id serial8 primary key, + course int8 NOT NULL default '0', + practice int4 NOT NULL default '0', + password varchar(32) NOT NULL default '', + usepassword int4 NOT NULL default '0', + grade int4 NOT NULL default '0', + custom int4 NOT NULL default '0', + ongoing int4 NOT NULL default '0', + usemaxgrade int4 NOT NULL default '0', + maxanswers int4 NOT NULL default '4', + maxattempts int4 NOT NULL default '5', + review int4 NOT NULL default '0', + nextpagedefault int4 NOT NULL default '0', + minquestions int4 NOT NULL default '0', + maxpages int4 NOT NULL default '0', + timed int4 NOT NULL default '0', + maxtime int8 NOT NULL default '0', + retake int4 NOT NULL default '1', + tree int4 NOT NULL default '0', + slideshow int4 NOT NULL default '0', + width int8 NOT NULL default '640', + height int8 NOT NULL default '480', + bgcolor varchar(7) default '#FFFFFF', + displayleft int4 NOT NULL default '0', + highscores int4 NOT NULL default '0', + maxhighscores int8 NOT NULL default '0' + )"); + } + // CDC-FLAG end + + return true; } diff --git a/mod/lesson/db/postgres7.sql b/mod/lesson/db/postgres7.sql index f6ff75e2a3..f4cb24e525 100644 --- a/mod/lesson/db/postgres7.sql +++ b/mod/lesson/db/postgres7.sql @@ -1,55 +1,65 @@ -# This file contains a complete database schema for all the -# tables used by the mlesson module, written in SQL - -# It may also contain INSERT statements for particular data -# that may be used, especially new entries in the table log_display - CREATE TABLE prefix_lesson ( id SERIAL8 PRIMARY KEY, course INT8 NOT NULL default '0', name varchar(255) NOT NULL default '', + practice INT NOT NULL DEFAULT '0', + usepassword INT NOT NULL DEFAULT '0', + password VARCHAR(32) NOT NULL default '', grade INT NOT NULL default '0', + custom INT NOT NULL DEFAULT '0', + ongoing INT NOT NULL DEFAULT '0', usemaxgrade INT NOT NULL default '0', maxanswers INT NOT NULL default '4', - maxattempts INT NOT NULL default '0', - nextpagedefault INT NOT NULL default '0', - maxpages INT NOT NULL default '0', + maxattempts INT NOT NULL default '5', + review INT NOT NULL DEFAULT '0', + nextpagedefault INT NOT NULL default '0', + minquestions INT NOT NULL default '0', + maxpages INT NOT NULL default '0', + timed INT NOT NULL DEFAULT '0', + maxtime INT8 NOT NULL DEFAULT '0', retake INT NOT NULL default '1', + tree INT NOT NULL DEFAULT '0', + slideshow INT NOT NULL DEFAULT '0', + width INT8 NOT NULL DEFAULT '640', + height INT8 NOT NULL DEFAULT '480', + bgcolor VARCHAR(7) NOT NULL DEFAULT '#FFFFFF', + displayleft INT NOT NULL DEFAULT '0', + highscores INT NOT NULL DEFAULT '0', + maxhighscores INT8 NOT NULL DEFAULT '0', available INT8 NOT NULL default '0', deadline INT8 NOT NULL default '0', timemodified INT8 NOT NULL default '0' ); -# -------------------------------------------------------- CREATE TABLE prefix_lesson_pages ( id SERIAL8 PRIMARY KEY, lessonid INT8 NOT NULL default '0', prevpageid INT8 NOT NULL default '0', nextpageid INT8 NOT NULL default '0', - qtype INT8 NOT NULL default '0', - qoption INT8 NOT NULL default '0', + qtype INT NOT NULL default '0', + qoption INT NOT NULL default '0', + layout INT NOT NULL DEFAULT '1', + display INT NOT NULL DEFAULT '1', timecreated INT8 NOT NULL default '0', timemodified INT8 NOT NULL default '0', title varchar(255) NOT NULL default '', contents text NOT NULL default '' -); -# COMMENT='Defines lesson_pages'; -# -------------------------------------------------------- +) ; CREATE TABLE prefix_lesson_answers ( id SERIAL8 PRIMARY KEY, lessonid INT8 NOT NULL default '0', pageid INT8 NOT NULL default '0', - jumpto INT8 NOT NULL default '0', - grade INT8 NOT NULL default '0', - flags INT8 NOT NULL default '0', + jumpto int8 NOT NULL default '0', + grade INT NOT NULL default '0', + score INT8 NOT NULL DEFAULT '0', + flags INT NOT NULL default '0', timecreated INT8 NOT NULL default '0', timemodified INT8 NOT NULL default '0', answer text NOT NULL default '', response text NOT NULL default '' -); -# COMMENT='Defines lesson_answers'; -# -------------------------------------------------------- +) ; +CREATE INDEX prefix_lesson_answers_pageid_idx ON prefix_lesson_answers (pageid); CREATE TABLE prefix_lesson_attempts ( id SERIAL8 PRIMARY KEY, @@ -60,9 +70,8 @@ CREATE TABLE prefix_lesson_attempts ( retry INT NOT NULL default '0', correct INT8 NOT NULL default '0', timeseen INT8 NOT NULL default '0' -); -#COMMENT='Defines lesson_attempts'; -# -------------------------------------------------------- +) ; +CREATE INDEX prefix_lesson_attempts_userid_idx ON prefix_lesson_attempts (userid); CREATE TABLE prefix_lesson_grades ( id SERIAL8 PRIMARY KEY, @@ -71,9 +80,77 @@ CREATE TABLE prefix_lesson_grades ( grade INT NOT NULL default '0', late INT NOT NULL default '0', completed INT8 NOT NULL default '0' -); -# COMMENT='Defines lesson_grades'; -# -------------------------------------------------------- +) ; + +CREATE TABLE prefix_lesson_default + ( id SERIAL8 PRIMARY KEY, + course INT8 NOT NULL default '0', + practice INT NOT NULL default '0', + password varchar(32) NOT NULL default '', + usepassword INT NOT NULL default '0', + grade INT NOT NULL default '0', + custom INT NOT NULL default '0', + ongoing INT NOT NULL default '0', + usemaxgrade INT NOT NULL default '0', + maxanswers INT NOT NULL default '4', + maxattempts INT NOT NULL default '5', + review INT NOT NULL default '0', + nextpagedefault INT NOT NULL default '0', + minquestions INT NOT NULL default '0', + maxpages INT NOT NULL default '0', + timed INT NOT NULL default '0', + maxtime INT8 NOT NULL default '0', + retake INT NOT NULL default '1', + tree INT NOT NULL default '0', + slideshow INT NOT NULL default '0', + width INT8 NOT NULL default '640', + height INT8 NOT NULL default '480', + bgcolor varchar(7) default '#FFFFFF', + displayleft INT NOT NULL default '0', + highscores INT NOT NULL default '0', + maxhighscores INT8 NOT NULL default '0' + ) ; + +CREATE TABLE prefix_lesson_timer + ( id SERIAL8 PRIMARY KEY, + lessonid INT8 not null, + userid INT8 not null, + starttime INT8 not null, + lessontime INT8 not null + ); + +CREATE TABLE prefix_lesson_branch + ( id SERIAL8 PRIMARY KEY, + lessonid INT8 not null, + userid INT8 not null, + pageid INT8 not null, + retry INT8 not null, + flag INT not null, + timeseen INT8 not null + ); + +CREATE TABLE prefix_lesson_essay + ( id SERIAL8 PRIMARY KEY, + lessonid INT8 not null, + userid INT8 not null, + pageid INT8 not null, + answerid INT8 not null, + try INT8 not null, + answer text not null, + graded INT not null default 0, + score INT8 not null default 0, + response text not null, + sent INT not null default 0, + timesubmitted INT8 not null + ); + +CREATE TABLE prefix_lesson_high_scores + ( id SERIAL8 PRIMARY KEY, + lessonid INT8 not null, + userid INT8 not null, + gradeid INT8 not null, + nickname varchar(5) not null + ); INSERT INTO prefix_log_display VALUES ('lesson', 'start', 'lesson', 'name'); -- 2.39.5