1 ###############################################################
\r
2 # Database upgrade SQL from ATutor 1.5.2 to ATutor 1.5.3
\r
3 ###############################################################
\r
5 CREATE TABLE `groups_types` (
\r
6 `type_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT ,
\r
7 `course_id` MEDIUMINT UNSIGNED NOT NULL default '0',
\r
8 `title` VARCHAR( 80 ) NOT NULL default '',
\r
9 PRIMARY KEY ( `type_id` ) ,
\r
13 ALTER TABLE `groups` CHANGE `course_id` `type_id` MEDIUMINT( 8 ) UNSIGNED DEFAULT '0' NOT NULL;
\r
14 ALTER TABLE `groups` ADD `description` TEXT NOT NULL default '' , ADD `modules` VARCHAR(100) NOT NULL default '';
\r
16 UPDATE `modules` SET `privilege`=1048576 WHERE `dir_name`='_core/groups';
\r
17 INSERT INTO `modules` VALUES ('_standard/reading_list', 2, 131072, 0);
\r
18 INSERT INTO `modules` VALUES ('_standard/file_storage', 2, 262144, 0);
\r
19 INSERT INTO `modules` VALUES ('_standard/assignments', 2, 524288, 0);
\r
21 # cron support for modules
\r
22 ALTER TABLE `modules` ADD `cron_interval` SMALLINT UNSIGNED DEFAULT '0' NOT NULL , ADD `cron_last_run` INT UNSIGNED DEFAULT '0' NOT NULL ;
\r
25 # forum groups table
\r
26 CREATE TABLE `forums_groups` (
\r
27 `forum_id` mediumint( 8 ) unsigned NOT NULL default '0',
\r
28 `group_id` mediumint( 8 ) unsigned NOT NULL default '0',
\r
29 PRIMARY KEY ( `forum_id` , `group_id` ) ,
\r
30 KEY `group_id` ( `group_id` )
\r
33 # release date for courses
\r
34 ALTER TABLE `courses` ADD `release_date` datetime NOT NULL default '0000-00-00 00:00:00';
\r
35 ALTER TABLE `courses` ADD `banner` TEXT NOT NULL default '';
\r
37 # --------------------------------------------------------
\r
38 # Table structure for table `reading_list`
\r
40 CREATE TABLE `reading_list` (
\r
41 `reading_id` MEDIUMINT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
\r
42 `course_id` MEDIUMINT UNSIGNED NOT NULL default 0,
\r
43 `resource_id` MEDIUMINT UNSIGNED NOT NULL default 0,
\r
44 `required` enum('required','optional') NOT NULL DEFAULT 'required',
\r
45 `date_start` DATE NOT NULL DEFAULT '0000-00-00',
\r
46 `date_end` DATE NOT NULL DEFAULT '0000-00-00',
\r
47 `comment` text NOT NULL default '',
\r
48 PRIMARY KEY (`reading_id`),
\r
52 # Table structure for table `external_resources`
\r
54 CREATE TABLE `external_resources` (
\r
55 `resource_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
\r
56 `course_id` MEDIUMINT UNSIGNED NOT NULL default 0,
\r
57 `type` TINYINT UNSIGNED NOT NULL DEFAULT 0,
\r
58 `title` varchar(255) NOT NULL DEFAULT '',
\r
59 `author` varchar(150) NOT NULL DEFAULT '',
\r
60 `publisher` varchar(150) NOT NULL DEFAULT '',
\r
61 `date` varchar(20) NOT NULL DEFAULT '',
\r
62 `comments` varchar(255) NOT NULL DEFAULT '',
\r
63 `id` varchar(50) NOT NULL DEFAULT '',
\r
64 `url` varchar(255) NOT NULL DEFAULT '',
\r
65 PRIMARY KEY (`resource_id`),
\r
69 # for the file storage
\r
70 # --------------------------------------------------------
\r
72 CREATE TABLE `file_storage_groups` (
\r
73 `group_id` MEDIUMINT UNSIGNED NOT NULL default '0',
\r
74 PRIMARY KEY ( `group_id` )
\r
78 CREATE TABLE `files` (
\r
79 `file_id` mediumint(8) unsigned NOT NULL auto_increment,
\r
80 `owner_type` tinyint(3) unsigned NOT NULL default '0',
\r
81 `owner_id` mediumint(8) unsigned NOT NULL default '0',
\r
82 `member_id` mediumint(8) unsigned NOT NULL default '0',
\r
83 `folder_id` mediumint(8) unsigned NOT NULL default '0',
\r
84 `parent_file_id` mediumint(8) unsigned NOT NULL default '0',
\r
85 `date` datetime NOT NULL default '0000-00-00 00:00:00',
\r
86 `num_comments` tinyint(3) unsigned NOT NULL default '0',
\r
87 `num_revisions` tinyint(3) unsigned NOT NULL default '0',
\r
88 `file_name` varchar(80) NOT NULL default '',
\r
89 `file_size` int(11) NOT NULL default '0',
\r
90 `description` text NOT NULL default '',
\r
91 PRIMARY KEY (`file_id`)
\r
94 CREATE TABLE `files_comments` (
\r
95 `comment_id` mediumint(8) unsigned NOT NULL auto_increment,
\r
96 `file_id` mediumint(8) unsigned NOT NULL default '0',
\r
97 `member_id` mediumint(8) unsigned NOT NULL default '0',
\r
98 `date` datetime NOT NULL default '0000-00-00 00:00:00',
\r
99 `comment` text NOT NULL default '',
\r
100 PRIMARY KEY (`comment_id`)
\r
103 CREATE TABLE `folders` (
\r
104 `folder_id` mediumint(8) unsigned NOT NULL auto_increment,
\r
105 `parent_folder_id` mediumint(8) unsigned NOT NULL default '0',
\r
106 `owner_type` tinyint(3) unsigned NOT NULL default '0',
\r
107 `owner_id` mediumint(8) unsigned NOT NULL default '0',
\r
108 `title` varchar(30) NOT NULL default '',
\r
109 PRIMARY KEY (`folder_id`)
\r
112 ## assignment manager
\r
113 CREATE TABLE `assignments` (
\r
114 `assignment_id` MEDIUMINT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
\r
115 `course_id` MEDIUMINT UNSIGNED NOT NULL default 0,
\r
116 `title` VARCHAR(60) NOT NULL default '',
\r
117 `assign_to` MEDIUMINT UNSIGNED DEFAULT 0,
\r
118 `date_due` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
\r
119 `date_cutoff` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
\r
120 `multi_submit` TINYINT DEFAULT '0',
\r
121 PRIMARY KEY (`assignment_id`),
\r
122 INDEX (`course_id`)
\r
125 # make the privs field bigger
\r
126 ALTER TABLE `course_enrollment` CHANGE `privileges` `privileges` INT UNSIGNED DEFAULT '0' NOT NULL;
\r
127 ALTER TABLE `modules` CHANGE `privilege` `privilege` INT UNSIGNED DEFAULT '0' NOT NULL;
\r
129 # second name field
\r
130 ALTER TABLE `members` ADD `second_name` CHAR( 30 ) NOT NULL default '' AFTER `first_name` ;
\r
131 ALTER TABLE `members` ADD `private_email` TINYINT DEFAULT '1' NOT NULL ;
\r
133 # increase length of users_online `login` field to support a full display name. or close to it.
\r
134 ALTER TABLE `users_online` CHANGE `login` `login` varchar(255) NOT NULL default '';
\r
136 # Table structure for table `mail_queue`
\r
138 CREATE TABLE `mail_queue` (
\r
139 `mail_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT ,
\r
140 `to_email` VARCHAR( 50 ) NOT NULL default '',
\r
141 `to_name` VARCHAR( 50 ) NOT NULL default '',
\r
142 `from_email` VARCHAR( 50 ) NOT NULL default '',
\r
143 `from_name` VARCHAR( 50 ) NOT NULL default '',
\r
144 `char_set` VARCHAR( 20 ) NOT NULL default '',
\r
145 `subject` VARCHAR( 200 ) NOT NULL default '',
\r
146 `body` TEXT NOT NULL default '',
\r
147 PRIMARY KEY ( `mail_id` )
\r
150 #install new themes
\r
152 INSERT INTO `themes` VALUES ('Blumin', '1.5.3', 'blumin', NOW(), 'This is the plone look-alike theme.', 1);
\r
154 # --------------------------------------------------------
\r
155 ## Table for `blog_posts`
\r
157 CREATE TABLE `blog_posts` (
\r
158 `post_id` mediumint(8) unsigned NOT NULL auto_increment,
\r
159 `member_id` mediumint(8) unsigned NOT NULL default '0',
\r
160 `owner_type` tinyint(3) unsigned NOT NULL default '0',
\r
161 `owner_id` mediumint(8) unsigned NOT NULL default '0',
\r
162 `private` tinyint(3) unsigned NOT NULL default '0',
\r
163 `date` datetime NOT NULL default '0000-00-00 00:00:00',
\r
164 `num_comments` tinyint(3) unsigned NOT NULL default '0',
\r
165 `title` varchar(100) NOT NULL default '',
\r
166 `body` text NOT NULL default '',
\r
167 PRIMARY KEY (`post_id`)
\r
170 ## Table for `blog_posts_comments`
\r
171 # --------------------------------------------------------
\r
172 CREATE TABLE `blog_posts_comments` (
\r
173 `comment_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT ,
\r
174 `post_id` MEDIUMINT UNSIGNED DEFAULT '0' NOT NULL ,
\r
175 `member_id` MEDIUMINT UNSIGNED DEFAULT '0' NOT NULL ,
\r
176 `date` DATETIME NOT NULL default '0000-00-00 00:00:00',
\r
177 `private` TINYINT UNSIGNED DEFAULT '0' NOT NULL ,
\r
178 `comment` TEXT NOT NULL default '',
\r
179 PRIMARY KEY ( `comment_id` ) ,
\r
180 INDEX ( `post_id` )
\r
183 ## add blog to the modules (added to 1.5.3.1)
\r
184 ##INSERT INTO `modules` VALUES ('_standard/blogs', 2, 0, 0, 0, 0);
\r
187 ALTER TABLE `members` CHANGE `gender` `gender` ENUM( 'm', 'f', 'n' ) DEFAULT 'n' NOT NULL;
\r
189 ## link table updates
\r
191 ALTER TABLE `resource_categories` RENAME `links_categories` ;
\r
192 ALTER TABLE `links_categories`
\r
193 CHANGE `CatID` `cat_id` mediumint(8) unsigned NOT NULL auto_increment ,
\r
194 CHANGE `course_id` `owner_id` mediumint(8) unsigned NOT NULL default '0' ,
\r
195 CHANGE `CatName` `name` varchar(100) NOT NULL default '' ,
\r
196 CHANGE `CatParent` `parent_id` mediumint(8) unsigned default NULL ,
\r
197 ADD `owner_type` tinyint(4) NOT NULL default '0' AFTER `cat_id` ;
\r
199 ALTER TABLE `links_categories`
\r
200 DROP INDEX `course_id` ,
\r
201 ADD INDEX `owner_id` ( `owner_id` );
\r
203 UPDATE `links_categories` SET owner_type=1 WHERE owner_type=0 ;
\r
206 ALTER TABLE `resource_links` RENAME `links` ;
\r
207 ALTER TABLE `links`
\r
208 CHANGE `LinkID` `link_id` mediumint(8) unsigned NOT NULL auto_increment ,
\r
209 CHANGE `CatID` `cat_id` mediumint(8) unsigned NOT NULL default '0' ;
\r
212 ALTER TABLE `members` CHANGE `gender` `gender` ENUM( 'm', 'f', 'n' ) DEFAULT 'n' NOT NULL;
\r
214 ALTER TABLE `handbook_notes` ADD `approved` TINYINT DEFAULT '0' NOT NULL AFTER `page` ;