[[PageOutline]] == About this page == This page will help us migrate iPeer users' databases from version 2.0 to 2.1. Make an entry to this database for each change you make to the iPeer database. For each entry into this wiki page, please state: * Date of the development iPeer Database change. * Reason for change, and it's impact. * Who performed the change. * The *exact and complete* SQL statement that will perform the change. == Version 2.1 to 2.2 Updates== === July 23, 2010 === compass: {{{ #!sql ALTER TABLE `personalizes` ADD INDEX ( `user_id` , `attribute_code` ) ; }}} === August 18, 2010 === To speed up the AjaxList for Users (~300x speed-up with 3000 users database). - Serge. {{{ #!sql CREATE INDEX user_id_index on user_enrols(user_id) }}} === September (?) === To allow unlimited comments in evaluation_mixevals. - compass & serge {{{ #!sql ALTER TABLE `evaluation_mixeval_details` MODIFY COLUMN `question_comment` TEXT DEFAULT NULL; }}} [[BR]] [[BR]] == Version 2.0 to 2.1 Updates == === June 16, 2010 === This change was made for student's survey evaluation submissions. Since students submitting surveys are not tied to groups, their group values will be should NULL. Before, the default value for grp_event_id was zero, and, because of the relationship grp_event_id and submitter_id (onl one pair is allowed), the effect was that a single student could only submit one Survey, ever. This change remedies the situation, and no code needs to be changed to accommodate it. - Serge & Pan {{{ #!sql ALTER TABLE `evaluation_submissions` MODIFY COLUMN `grp_event_id` INTEGER DEFAULT NULL; }}} === July 9, 2010 === This change is made for student's rubrics evaluation submissions. Since students submitted rubric evaluations are not tied to the rubric, deleting a rubric does not fully remove students' evaluation data of that rubric. With the added 'rubric_id' field, the linkage is now complete and references would not be lost in the relationship specified between evaluation_rubrics and rubric tables. - Henry {{{ #!sql ALTER TABLE `evaluation_rubrics` ADD COLUMN `rubric_id` INTEGER DEFAULT 0 NOT NULL; }}} === July 16, 2010 === Sets up the new System function code, to match the users controller. - Serge {{{ #!sql UPDATE sys_functions SET function_code='USERS' WHERE function_code='USR'; }}} === July 22, 2010 === Add index for user_enrols table to improve the query time {{{ #!sql ALTER TABLE `user_enrols` ADD INDEX ( `user_id` ); }}} [[BR]] [[BR]] == Development Change Log == === Pre r514 === {{{ #!sql ALTER TABLE `events` CHANGE `title` `title` VARCHAR( 255 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '' CREATE TABLE `roles` ( `id` INT(11) NOT NULL AUTO_INCREMENT , `name` VARCHAR( 255 ) NOT NULL , `created` DATETIME NOT NULL , `modified` DATETIME NOT NULL , PRIMARY KEY ( `id` ) ) ENGINE = INNODB; CREATE TABLE `ipeer`.`roles_users` ( `id` INT( 11 ) NOT NULL AUTO_INCREMENT, `role_id` INT( 11 ) NOT NULL , `user_id` INT( 11 ) NOT NULL `created` DATETIME NOT NULL , `modified` DATETIME NOT NULL ) ENGINE = INNODB; ALTER TABLE `users` CHANGE `role` `role` ENUM( 'A', 'I', 'S', 'T' ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT 'S' insert into roles_users (role_id, user_id) select 4, id from users where role LIKE "S"; insert into roles_users (role_id, user_id) select 3, id from users where role LIKE "I"; insert into roles_users (role_id, user_id) select 2, id from users where role LIKE "A" AND id !=1; ALTER TABLE `groups_members` ADD INDEX ( `group_id` ) ALTER TABLE `surveys` CHANGE `name` `name` VARCHAR( 255 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL # change for question ordering ALTER TABLE `survey_questions` CHANGE `number` `number` INT( 11 ) NOT NULL DEFAULT '9999' # change for auto generating description input textarea ALTER TABLE `events` CHANGE `description` `description` TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL # change rubrics_criteria_comments to associate with rubrics_criterias by criteria id ALTER TABLE `rubrics_criteria_comments` ADD `criteria_id` INT NOT NULL AFTER `id` ALTER TABLE `rubrics_criteria_comments` ADD INDEX ( `criteria_id` ) UPDATE rubrics_criteria_comments as rcc set criteria_id = (SELECT id from rubrics_criterias as rc WHERE rc.rubric_id = rcc.rubric_id AND rc.criteria_num = rcc.criteria_num) ALTER TABLE `rubrics_criteria_comments` ADD FOREIGN KEY ( `criteria_id` ) REFERENCES `ipeer`.`rubrics_criterias` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT ; ALTER TABLE `rubrics_criteria_comments` DROP `rubric_id` ALTER TABLE `rubrics_criteria_comments` DROP `criteria_num` # change Criteria comments lom_num to LOM ids UPDATE rubrics_criteria_comments as rcc, rubrics_criterias as rc, rubrics as r, rubrics_loms as rl set rcc.lom_num=rl.id where rcc.criteria_id = rc.id AND rc.rubric_id = r.id AND rl.rubric_id = r.id AND rl.lom_num = rcc.lom_num ALTER TABLE `rubrics_criteria_comments` CHANGE `rubrics_loms_id` `rubrics_loms_id` INT( 11 ) NOT NULL ALTER TABLE `rubrics_criteria_comments` ADD INDEX ( `rubrics_loms_id` ) ALTER TABLE `rubrics_criteria_comments` ADD FOREIGN KEY ( `rubrics_loms_id` ) REFERENCES `ipeer`.`rubrics_loms` ( `id` ) ON DELETE RESTRICT ON UPDATE RESTRICT ; # change the default order number to 999 ALTER TABLE `rubrics_loms` CHANGE `lom_num` `lom_num` INT( 11 ) NOT NULL DEFAULT '999' ALTER TABLE `rubrics_criterias` CHANGE `criteria_num` `criteria_num` INT( 11 ) NOT NULL DEFAULT '999' # remove total_marks column, using calculated value instead. ALTER TABLE `rubrics` DROP `total_marks` # change mixeval question description question number to question_id UPDATE mixevals_question_descs as qd, mixevals_questions as q set qd.question_num = q.id where qd.mixeval_id = q.mixeval_id AND qd.question_num = q.question_num; ALTER TABLE `mixevals_question_descs` DROP `mixeval_id`; ALTER TABLE `mixevals_question_descs` ADD INDEX ( `question_num` ); ALTER TABLE `mixevals_question_descs` CHANGE `question_num` `question_id` INT( 11 ) NOT NULL DEFAULT '0'; ALTER TABLE `mixevals_question_descs` ADD FOREIGN KEY ( `question_id` ) REFERENCES `ipeer`.`mixevals_questions` ( `id` ) ON DELETE RESTRICT ON UPDATE RESTRICT ; ALTER TABLE `mixevals` DROP `total_marks` ALTER TABLE `mixevals_questions` CHANGE `required` `required` BOOLEAN NOT NULL DEFAULT '1'; # convert zero_mark columns from enum to boolean ALTER TABLE `mixevals` CHANGE `zero_mark` `zero_mark` BOOLEAN NOT NULL DEFAULT '0'; update mixevals set zero_mark=0 where zero_mark=2; ALTER TABLE `rubrics` CHANGE `zero_mark` `zero_mark` BOOLEAN NOT NULL DEFAULT '0'; update rubrics set zero_mark=0 where zero_mark=2; ALTER TABLE `mixevals` DROP `lickert_question_max` ALTER TABLE `mixevals` DROP `prefill_question_max` ALTER TABLE `mixevals` DROP `total_question` }}}