mysql-queries.txt

This file contains mysql queries for portal sail_database changes.

### 2010.06.11 Start

Comment: Run all these queries if you are switching from webapp-2.0 to webapp-2.2


ALTER TABLE `sail_database`.`runs` ADD COLUMN `versionId` VARCHAR(255) AFTER `postLevel`;
ALTER TABLE `sail_database`.`projects` DROP COLUMN `metadata_fk`;
ALTER TABLE `sail_database`.`project_metadata` ADD COLUMN `project_fk` BIGINT(20) AFTER `keywords`;
ALTER TABLE `sail_database`.`project_metadata` ADD COLUMN `version_id` VARCHAR(255) AFTER `project_fk`;

ALTER TABLE `sail_database`.`messages` DROP COLUMN `isRead`, DROP COLUMN `recipient`;

CREATE TABLE `sail_database`.`messages_related_to_message_recipients` (
messages_fk bigint not null,
recipients_fk bigint not null,
primary key(messages_fk, recipients_fk),
unique(recipients_fk)
) type=InnoDB;

CREATE TABLE `sail_database`.`message_recipient` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`recipient_fk` BIGINT(20) NOT NULL,
`isRead` BOOLEAN,
PRIMARY KEY (`id`)
)
CHARACTER SET utf8;

CREATE TABLE `sail_database`.`projects_related_to_tags` (
`project_fk` BIGINT(20) NOT NULL,
`tag_fk` BIGINT(20) NOT NULL,
PRIMARY KEY (`project_fk`, `tag_fk`)
)
CHARACTER SET utf8;

CREATE TABLE `sail_database`.`tags` (
`id` BIGINT(20) AUTO_INCREMENT,
`name` VARCHAR(256),
PRIMARY KEY (`id`)
)
CHARACTER SET utf8;

ALTER TABLE `sail_database`.`runs` ADD COLUMN `timesRun` INT AFTER `versionId`, ADD COLUMN `lastRun` DATETIME AFTER `timesRun`;

ALTER TABLE `sail_database`.`premadecommentlists` ADD COLUMN `global` BOOLEAN DEFAULT NULL AFTER `run`;

ALTER TABLE `sail_database`.`premadecomments` DROP COLUMN `label`,
 DROP COLUMN `run`,
 DROP INDEX `FK7786D42CF4421937`,
 DROP FOREIGN KEY `FK7786D42CF4421937`;

ALTER TABLE `sail_database`.`premadecommentlists` DROP COLUMN `run`
, DROP INDEX `FKF237B2CEF4421937`,
 DROP FOREIGN KEY `FKF237B2CEF4421937`;

ALTER TABLE `sail_database`.`premadecomments` ADD COLUMN `listposition` INT AFTER `owner`;

delete FROM sail_database.premadecomments_related_to_premadecommentlists;
delete FROM sail_database.premadecommentlists;
delete FROM sail_database.premadecomments;

update projects set projecttype=2 where projecttype is null;

### 2010.06.11 End

### 2010.08.13 BEGIN
ALTER TABLE `sail_database`.`project_metadata` ADD COLUMN `last_cleaned` DATETIME AFTER `version_id`;
ALTER TABLE `sail_database`.`project_metadata` ADD COLUMN `last_edited` DATETIME AFTER `last_cleaned`;
ALTER TABLE `sail_database`.`project_metadata` ADD COLUMN `post_level` BIGINT(20) AFTER `last_edited`;
ALTER TABLE `sail_database`.`projects` ADD COLUMN `metadata_fk` BIGINT(20) AFTER `datecreated`;
ALTER TABLE `sail_database`.`projects` ADD COLUMN `parentProjectId` BIGINT(20) AFTER `metadata_fk`;
### 2010.08.13 END

### 2010.08.17 BEGIN
ALTER TABLE `sail_database`.`project_metadata` ADD COLUMN `language` varchar(50) AFTER `keywords`;
update project_metadata set tech_reqs=null where tech_reqs is null or tech_reqs = 'null' or tech_reqs = ''
### 2010.08.17 END

### 2010.08.30 BEGIN
ALTER TABLE `sail_database`.`project_metadata` MODIFY COLUMN `total_time` VARCHAR(255) DEFAULT NULL,
 MODIFY COLUMN `comp_time` VARCHAR(255) DEFAULT NULL;
### 2010.08.30 END

### 2010.09.17 BEGIN
INSERT INTO granted_authorities values (6, 'ROLE_RESEARCHER',0)
### 2010.09.17 END


### 2010.09.24 BEGIN
ALTER TABLE `sail_database`.`project_metadata` ADD COLUMN `last_minified` DATETIME DEFAULT NULL AFTER `last_edited`;
ALTER TABLE `sail_database`.`project_metadata` ADD COLUMN `max_scores` TEXT DEFAULT NULL AFTER `post_level`;
### 2010.09.24 END

### 2011.04.07 BEGIN
ALTER TABLE `sail_database`.`teacher_user_details` ADD COLUMN `howDidYouHearAboutUs` VARCHAR(255) DEFAULT NULL AFTER `isEmailValid`;
### 2011.04.07 END

### 2011.04.29 BEGIN
ALTER TABLE `sail_database`.`user_details` ADD COLUMN `recent_failed_login_time` DATETIME DEFAULT NULL AFTER `OPTLOCK`, ADD COLUMN `recent_number_of_failed_login_attempts` INT DEFAULT 0 AFTER `recent_failed_login_time`;
CREATE TABLE  `sail_database`.`portal_statistics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `timestamp` datetime DEFAULT NULL,
  `totalNumberStudents` int(11) DEFAULT NULL,
  `totalNumberStudentLogins` int(11) DEFAULT NULL,
  `totalNumberTeachers` int(11) DEFAULT NULL,
  `totalNumberTeacherLogins` int(11) DEFAULT NULL,
  `totalNumberRuns` int(11) DEFAULT NULL,
  `totalNumberProjectsRun` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

ALTER TABLE `sail_database`.`portal_statistics` ADD COLUMN `totalNumberProjects` INTEGER DEFAULT NULL AFTER `totalNumberTeacherLogins`;
### 2011.04.29 END

### 2011.05.24 START
CREATE  TABLE `sail_database`.`student_attendance` (
  `id` BIGINT NOT NULL AUTO_INCREMENT ,
  `workgroupId` BIGINT NULL ,
  `runId` BIGINT NULL ,
  `loginTimestamp` DATETIME NULL ,
  `presentUserIds` VARCHAR(255) NULL ,
  `absentUserIds` VARCHAR(255) NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `workgroupId` (`workgroupId` ASC) ,
  INDEX `runId` (`runId` ASC) ,
  CONSTRAINT `workgroupId`
    FOREIGN KEY (`workgroupId` )
    REFERENCES `sail_database`.`wiseworkgroups` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `runId`
    FOREIGN KEY (`runId` )
    REFERENCES `sail_database`.`runs` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM;
### 2011.05.24 END