Changes between Version 28 and Version 29 of DbChanges


Ignore:
Timestamp:
2011-05-30T11:03:19-07:00 (6 years ago)
Author:
Pan Luo
Comment:

Legend:

Unmodified
Added
Removed
Modified
  • DbChanges

    v28 v29  
    6767[[BR]]
    6868[[BR]]
     69
     70
     71
     72== Development Change Log ==
     73
     74=== Pre r514 ===
     75
     76{{{
     77#!sql
     78
     79ALTER TABLE `events` CHANGE `title` `title` VARCHAR( 255 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT ''
     80
     81
     82CREATE TABLE `roles` (
     83`id` INT(11) NOT NULL AUTO_INCREMENT ,
     84`name` VARCHAR( 255 ) NOT NULL ,
     85`created` DATETIME NOT NULL ,
     86`modified` DATETIME NOT NULL ,
     87PRIMARY KEY ( `id` )
     88) ENGINE = INNODB;
     89
     90CREATE TABLE `ipeer`.`roles_users` (
     91`id` INT( 11 ) NOT NULL AUTO_INCREMENT,
     92`role_id` INT( 11 ) NOT NULL ,
     93`user_id` INT( 11 ) NOT NULL
     94`created` DATETIME NOT NULL ,
     95`modified` DATETIME NOT NULL
     96) ENGINE = INNODB;
     97
     98
     99
     100ALTER TABLE `users` CHANGE `role` `role` ENUM( 'A', 'I', 'S', 'T' ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT 'S'
     101
     102
     103insert into roles_users (role_id, user_id) select 4, id from users where role LIKE "S";
     104insert into roles_users (role_id, user_id) select 3, id from users where role LIKE "I";
     105insert into roles_users (role_id, user_id) select 2, id from users where role LIKE "A" AND id !=1;
     106
     107ALTER TABLE `groups_members` ADD INDEX ( `group_id` )
     108
     109ALTER TABLE `surveys` CHANGE `name` `name` VARCHAR( 255 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL
     110
     111# change for question ordering
     112ALTER TABLE `survey_questions` CHANGE `number` `number` INT( 11 ) NOT NULL DEFAULT '9999'
     113
     114# change for auto generating description input textarea
     115ALTER TABLE `events` CHANGE `description` `description` TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL
     116
     117
     118
     119
     120# change rubrics_criteria_comments to associate with rubrics_criterias by criteria id
     121ALTER TABLE `rubrics_criteria_comments` ADD `criteria_id` INT NOT NULL AFTER `id`
     122ALTER TABLE `rubrics_criteria_comments` ADD INDEX ( `criteria_id` )
     123
     124UPDATE 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)
     125
     126ALTER TABLE `rubrics_criteria_comments` ADD FOREIGN KEY ( `criteria_id` ) REFERENCES `ipeer`.`rubrics_criterias` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT ;
     127
     128ALTER TABLE `rubrics_criteria_comments` DROP `rubric_id`
     129ALTER TABLE `rubrics_criteria_comments` DROP `criteria_num`
     130
     131# change Criteria comments lom_num to LOM ids
     132UPDATE 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
     133
     134ALTER TABLE  `rubrics_criteria_comments` CHANGE  `rubrics_loms_id`  `rubrics_loms_id` INT( 11 ) NOT NULL
     135
     136ALTER TABLE  `rubrics_criteria_comments` ADD INDEX (  `rubrics_loms_id` )
     137
     138ALTER TABLE  `rubrics_criteria_comments` ADD FOREIGN KEY (  `rubrics_loms_id` ) REFERENCES  `ipeer`.`rubrics_loms` (
     139`id`
     140) ON DELETE RESTRICT ON UPDATE RESTRICT ;
     141
     142# change the default order number to 999
     143ALTER TABLE `rubrics_loms` CHANGE `lom_num` `lom_num` INT( 11 ) NOT NULL DEFAULT '999'
     144ALTER TABLE `rubrics_criterias` CHANGE `criteria_num` `criteria_num` INT( 11 ) NOT NULL DEFAULT '999'
     145
     146
     147# remove total_marks column, using calculated value instead.
     148ALTER TABLE `rubrics` DROP `total_marks`
     149
     150
     151# change mixeval question description question number to question_id
     152UPDATE 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;
     153
     154ALTER TABLE `mixevals_question_descs` DROP `mixeval_id`;
     155
     156ALTER TABLE `mixevals_question_descs` ADD INDEX ( `question_num` );
     157
     158ALTER TABLE `mixevals_question_descs` CHANGE `question_num` `question_id` INT( 11 ) NOT NULL DEFAULT '0';
     159
     160ALTER TABLE `mixevals_question_descs` ADD FOREIGN KEY ( `question_id` ) REFERENCES `ipeer`.`mixevals_questions` (
     161`id`
     162) ON DELETE RESTRICT ON UPDATE RESTRICT ;
     163
     164
     165ALTER TABLE `mixevals` DROP `total_marks`
     166
     167ALTER TABLE `mixevals_questions` CHANGE `required` `required` BOOLEAN NOT NULL DEFAULT '1';
     168
     169# convert zero_mark columns from enum to boolean
     170ALTER TABLE `mixevals` CHANGE `zero_mark` `zero_mark` BOOLEAN NOT NULL DEFAULT '0';
     171update mixevals set zero_mark=0 where zero_mark=2;
     172
     173ALTER TABLE `rubrics` CHANGE `zero_mark` `zero_mark` BOOLEAN NOT NULL DEFAULT '0';
     174update rubrics set zero_mark=0 where zero_mark=2;
     175
     176
     177
     178
     179
     180
     181ALTER TABLE `mixevals` DROP `lickert_question_max`
     182ALTER TABLE `mixevals` DROP `prefill_question_max`
     183ALTER TABLE `mixevals` DROP `total_question`
     184
     185}}}