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 #Dec 6, 2007 - duplicated column name
\r
23 ALTER TABLE `modules` ADD `cron_interval` SMALLINT UNSIGNED DEFAULT '0' NOT NULL , ADD `cron_last_run` INT UNSIGNED DEFAULT '0' NOT NULL ;
\r
26 # forum groups table
\r
27 CREATE TABLE `forums_groups` (
\r
28 `forum_id` mediumint( 8 ) unsigned NOT NULL default '0',
\r
29 `group_id` mediumint( 8 ) unsigned NOT NULL default '0',
\r
30 PRIMARY KEY ( `forum_id` , `group_id` ) ,
\r
31 KEY `group_id` ( `group_id` )
\r
34 # release date for courses
\r
35 ALTER TABLE `courses` ADD `release_date` datetime NOT NULL default '0000-00-00 00:00:00';
\r
36 ALTER TABLE `courses` ADD `banner` TEXT NOT NULL default '';
\r
38 # --------------------------------------------------------
\r
39 # Table structure for table `reading_list`
\r
41 CREATE TABLE `reading_list` (
\r
42 `reading_id` MEDIUMINT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
\r
43 `course_id` MEDIUMINT UNSIGNED NOT NULL default 0,
\r
44 `resource_id` MEDIUMINT UNSIGNED NOT NULL default 0,
\r
45 `required` enum('required','optional') NOT NULL DEFAULT 'required',
\r
46 `date_start` DATE NOT NULL DEFAULT '0000-00-00',
\r
47 `date_end` DATE NOT NULL DEFAULT '0000-00-00',
\r
48 `comment` text NOT NULL default '',
\r
49 PRIMARY KEY (`reading_id`),
\r
53 # Table structure for table `external_resources`
\r
55 CREATE TABLE `external_resources` (
\r
56 `resource_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
\r
57 `course_id` MEDIUMINT UNSIGNED NOT NULL default 0,
\r
58 `type` TINYINT UNSIGNED NOT NULL DEFAULT 0,
\r
59 `title` varchar(255) NOT NULL DEFAULT '',
\r
60 `author` varchar(150) NOT NULL DEFAULT '',
\r
61 `publisher` varchar(150) NOT NULL DEFAULT '',
\r
62 `date` varchar(20) NOT NULL DEFAULT '',
\r
63 `comments` varchar(255) NOT NULL DEFAULT '',
\r
64 `id` varchar(50) NOT NULL DEFAULT '',
\r
65 `url` varchar(255) NOT NULL DEFAULT '',
\r
66 PRIMARY KEY (`resource_id`),
\r
70 # for the file storage
\r
71 # --------------------------------------------------------
\r
73 CREATE TABLE `file_storage_groups` (
\r
74 `group_id` MEDIUMINT UNSIGNED NOT NULL default '0',
\r
75 PRIMARY KEY ( `group_id` )
\r
79 CREATE TABLE `files` (
\r
80 `file_id` mediumint(8) unsigned NOT NULL auto_increment,
\r
81 `owner_type` tinyint(3) unsigned NOT NULL default '0',
\r
82 `owner_id` mediumint(8) unsigned NOT NULL default '0',
\r
83 `member_id` mediumint(8) unsigned NOT NULL default '0',
\r
84 `folder_id` mediumint(8) unsigned NOT NULL default '0',
\r
85 `parent_file_id` mediumint(8) unsigned NOT NULL default '0',
\r
86 `date` datetime NOT NULL default '0000-00-00 00:00:00',
\r
87 `num_comments` tinyint(3) unsigned NOT NULL default '0',
\r
88 `num_revisions` tinyint(3) unsigned NOT NULL default '0',
\r
89 `file_name` varchar(80) NOT NULL default '',
\r
90 `file_size` int(11) NOT NULL default '0',
\r
91 `description` text NOT NULL default '',
\r
92 PRIMARY KEY (`file_id`)
\r
95 CREATE TABLE `files_comments` (
\r
96 `comment_id` mediumint(8) unsigned NOT NULL auto_increment,
\r
97 `file_id` mediumint(8) unsigned NOT NULL default '0',
\r
98 `member_id` mediumint(8) unsigned NOT NULL default '0',
\r
99 `date` datetime NOT NULL default '0000-00-00 00:00:00',
\r
100 `comment` text NOT NULL default '',
\r
101 PRIMARY KEY (`comment_id`)
\r
104 CREATE TABLE `folders` (
\r
105 `folder_id` mediumint(8) unsigned NOT NULL auto_increment,
\r
106 `parent_folder_id` mediumint(8) unsigned NOT NULL default '0',
\r
107 `owner_type` tinyint(3) unsigned NOT NULL default '0',
\r
108 `owner_id` mediumint(8) unsigned NOT NULL default '0',
\r
109 `title` varchar(30) NOT NULL default '',
\r
110 PRIMARY KEY (`folder_id`)
\r
113 ## assignment manager
\r
114 CREATE TABLE `assignments` (
\r
115 `assignment_id` MEDIUMINT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
\r
116 `course_id` MEDIUMINT UNSIGNED NOT NULL default 0,
\r
117 `title` VARCHAR(60) NOT NULL default '',
\r
118 `assign_to` MEDIUMINT UNSIGNED DEFAULT 0,
\r
119 `date_due` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
\r
120 `date_cutoff` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
\r
121 `multi_submit` TINYINT DEFAULT '0',
\r
122 PRIMARY KEY (`assignment_id`),
\r
123 INDEX (`course_id`)
\r
126 # make the privs field bigger
\r
127 ALTER TABLE `course_enrollment` CHANGE `privileges` `privileges` INT UNSIGNED DEFAULT '0' NOT NULL;
\r
128 ALTER TABLE `modules` CHANGE `privilege` `privilege` INT UNSIGNED DEFAULT '0' NOT NULL;
\r
130 # second name field
\r
131 ALTER TABLE `members` ADD `second_name` CHAR( 30 ) NOT NULL default '' AFTER `first_name` ;
\r
132 ALTER TABLE `members` ADD `private_email` TINYINT DEFAULT '1' NOT NULL ;
\r
134 # increase length of users_online `login` field to support a full display name. or close to it.
\r
135 ALTER TABLE `users_online` CHANGE `login` `login` varchar(255) NOT NULL default '';
\r
137 # Table structure for table `mail_queue`
\r
139 CREATE TABLE `mail_queue` (
\r
140 `mail_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT ,
\r
141 `to_email` VARCHAR( 50 ) NOT NULL default '',
\r
142 `to_name` VARCHAR( 50 ) NOT NULL default '',
\r
143 `from_email` VARCHAR( 50 ) NOT NULL default '',
\r
144 `from_name` VARCHAR( 50 ) NOT NULL default '',
\r
145 `char_set` VARCHAR( 20 ) NOT NULL default '',
\r
146 `subject` VARCHAR( 200 ) NOT NULL default '',
\r
147 `body` TEXT NOT NULL default '',
\r
148 PRIMARY KEY ( `mail_id` )
\r
151 #install new themes
\r
153 INSERT INTO `themes` VALUES ('Blumin', '1.5.3', 'blumin', NOW(), 'This is the plone look-alike theme.', 1);
\r
155 # --------------------------------------------------------
\r
156 ## Table for `blog_posts`
\r
158 CREATE TABLE `blog_posts` (
\r
159 `post_id` mediumint(8) unsigned NOT NULL auto_increment,
\r
160 `member_id` mediumint(8) unsigned NOT NULL default '0',
\r
161 `owner_type` tinyint(3) unsigned NOT NULL default '0',
\r
162 `owner_id` mediumint(8) unsigned NOT NULL default '0',
\r
163 `private` tinyint(3) unsigned NOT NULL default '0',
\r
164 `date` datetime NOT NULL default '0000-00-00 00:00:00',
\r
165 `num_comments` tinyint(3) unsigned NOT NULL default '0',
\r
166 `title` varchar(100) NOT NULL default '',
\r
167 `body` text NOT NULL default '',
\r
168 PRIMARY KEY (`post_id`)
\r
171 ## Table for `blog_posts_comments`
\r
172 # --------------------------------------------------------
\r
173 CREATE TABLE `blog_posts_comments` (
\r
174 `comment_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT ,
\r
175 `post_id` MEDIUMINT UNSIGNED DEFAULT '0' NOT NULL ,
\r
176 `member_id` MEDIUMINT UNSIGNED DEFAULT '0' NOT NULL ,
\r
177 `date` DATETIME NOT NULL default '0000-00-00 00:00:00',
\r
178 `private` TINYINT UNSIGNED DEFAULT '0' NOT NULL ,
\r
179 `comment` TEXT NOT NULL default '',
\r
180 PRIMARY KEY ( `comment_id` ) ,
\r
181 INDEX ( `post_id` )
\r
184 ## add blog to the modules (added to 1.5.3.1)
\r
185 ##INSERT INTO `modules` VALUES ('_standard/blogs', 2, 0, 0, 0, 0);
\r
188 ## link table updates
\r
189 # Dec,6, 2007 duplicated column name
\r
190 ALTER TABLE `resource_categories` RENAME `links_categories` ;
\r
191 # Dec,6, 2007 duplicated column name
\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 # Dec,6, 2007 duplicated column name
\r
200 ALTER TABLE `links_categories`
\r
201 DROP INDEX `course_id` ,
\r
202 ADD INDEX `owner_id` ( `owner_id` );
\r
204 UPDATE `links_categories` SET owner_type=1 WHERE owner_type=0 ;
\r
206 # Dec,6, 2007 duplicated column name
\r
207 ALTER TABLE `resource_links` RENAME `links` ;
\r
208 ALTER TABLE `links`
\r
209 CHANGE `LinkID` `link_id` mediumint(8) unsigned NOT NULL auto_increment ,
\r
210 CHANGE `CatID` `cat_id` mediumint(8) unsigned NOT NULL default '0' ;
\r
213 ALTER TABLE `members` CHANGE `gender` `gender` ENUM( 'm', 'f', 'n' ) DEFAULT 'n' NOT NULL;
\r
215 # Dec,6, 2007 duplicated column name
\r
216 ALTER TABLE `handbook_notes` ADD `approved` TINYINT DEFAULT '0' NOT NULL AFTER `page` ;
\r