1 # Upgrade SQL for 1.6.1 to 1.6.2
3 # Setup Table for Access4All
4 CREATE TABLE `primary_resources` (
5 `primary_resource_id` mediumint(8) unsigned NOT NULL auto_increment,
6 `content_id` mediumint(8) unsigned NOT NULL default '0',
7 `resource` text NOT NULL,
8 `language_code` varchar(20) default NULL,
9 PRIMARY KEY (`primary_resource_id`)
12 CREATE TABLE `primary_resources_types` (
13 `primary_resource_id` mediumint(8) unsigned NOT NULL,
14 `type_id` mediumint(8) unsigned NOT NULL,
15 PRIMARY KEY (`primary_resource_id`,`type_id`)
18 CREATE TABLE `resource_types` (
19 `type_id` mediumint(8) unsigned NOT NULL auto_increment,
21 PRIMARY KEY (`type_id`)
24 CREATE TABLE `secondary_resources` (
25 `secondary_resource_id` mediumint(8) unsigned NOT NULL auto_increment,
26 `primary_resource_id` mediumint(8) unsigned NOT NULL,
27 `secondary_resource` text NOT NULL,
28 `language_code` varchar(20) default NULL,
29 PRIMARY KEY (`secondary_resource_id`)
32 CREATE TABLE `secondary_resources_types` (
33 `secondary_resource_id` mediumint(8) unsigned NOT NULL,
34 `type_id` mediumint(8) unsigned NOT NULL,
35 PRIMARY KEY (`secondary_resource_id`,`type_id`)
38 INSERT INTO `resource_types` VALUES
44 INSERT INTO `config` (`name`, `value`) VALUES('encyclopedia', 'http://www.wikipedia.org');
45 INSERT INTO `config` (`name`, `value`) VALUES('dictionary', 'http://dictionary.reference.com/');
46 INSERT INTO `config` (`name`, `value`) VALUES('thesaurus', 'http://thesaurus.reference.com/');
47 INSERT INTO `config` (`name`, `value`) VALUES('atlas', 'http://maps.google.ca/');
48 INSERT INTO `config` (`name`, `value`) VALUES('calculator', 'http://www.calculateforfree.com/');
49 INSERT INTO `config` (`name`, `value`) VALUES('note_taking', 'http://www.aypwip.org/webnote/');
50 INSERT INTO `config` (`name`, `value`) VALUES('abacas', 'http://www.mandarintools.com/abacus.html');
52 #Add the scaffold tools module
53 INSERT INTO `modules` VALUES ('_standard/support_tools', 2, 0, 2048, 0, 0);
55 # End Access4All setup
58 # Content Test Extension
61 CREATE TABLE `content_tests_assoc` (
62 `content_id` INTEGER UNSIGNED NOT NULL,
63 `test_id` INTEGER UNSIGNED NOT NULL,
64 PRIMARY KEY (`content_id`, `test_id`)
67 # Customized test messages associated with the content page
68 ALTER TABLE `content` ADD COLUMN `test_message` TEXT NOT NULL AFTER `use_customized_head`;
69 ALTER TABLE `content` ADD COLUMN `allow_test_export` TINYINT(1) UNSIGNED NOT NULL AFTER `test_message`;
71 # Extend field "value" for extended default preference setting string
72 ALTER TABLE `config` MODIFY value TEXT;
74 # Extend field "author", "installed_date" for extended patch information
75 ALTER TABLE `patches` ADD COLUMN `author` VARCHAR(255) NOT NULL AFTER `patch_files`;
76 ALTER TABLE `patches` ADD COLUMN `installed_date` datetime NOT NULL AFTER `author`;
78 # sql file for gradebook module
80 CREATE TABLE `grade_scales` (
81 `grade_scale_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
82 `member_id` mediumint(8) unsigned NOT NULL default '0',
83 `scale_name` VARCHAR(255) NOT NULL default '',
84 `created_date` datetime NOT NULL default '0000-00-00 00:00:00',
85 PRIMARY KEY ( `grade_scale_id` )
88 CREATE TABLE `grade_scales_detail` (
89 `grade_scale_id` mediumint(8) unsigned NOT NULL,
90 `scale_value` VARCHAR(50) NOT NULL default '',
91 `percentage_from` MEDIUMINT NOT NULL default '0',
92 `percentage_to` MEDIUMINT NOT NULL default '0',
93 PRIMARY KEY (`grade_scale_id`, `scale_value`)
96 CREATE TABLE `gradebook_tests` (
97 `gradebook_test_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
98 `id` mediumint(8) unsigned NOT NULL default '0' COMMENT 'Values: 0, tests.test_id or assignments.assignment_id. 0 for external tests/assignments. tests.test_id for ATutor tests, assignments.assignment_id for ATutor assignments.',
99 `type` VARCHAR(50) NOT NULL default '' COMMENT 'Values: ATutor Test, ATutor Assignment, External',
100 `course_id` mediumint(8) unsigned NOT NULL default '0' COMMENT 'Values: 0 or courses.course_id. Only has value for external tests/assignments. When ATutor internal assignments/tests/surveys, always 0.',
101 `title` VARCHAR(255) NOT NULL default '' COMMENT 'Values: Null or test name. Always null if ATutor internal assignments/tests/surveys.',
102 `due_date` datetime NOT NULL default '0000-00-00 00:00:00',
103 `grade_scale_id` mediumint(8) unsigned NOT NULL default '0',
104 PRIMARY KEY ( `gradebook_test_id` )
107 CREATE TABLE `gradebook_detail` (
108 `gradebook_test_id` mediumint(8) unsigned NOT NULL,
109 `member_id` mediumint(8) unsigned NOT NULL default '0',
110 `grade` VARCHAR(255) NOT NULL default '',
111 PRIMARY KEY (`gradebook_test_id`, `member_id`)
114 INSERT INTO `grade_scales` (grade_scale_id, member_id, scale_name, created_date) values (1, 0, 'Letter Grade', now());
115 INSERT INTO `grade_scales` (grade_scale_id, member_id, scale_name, created_date) values (2, 0, 'Competency 1', now());
116 INSERT INTO `grade_scales` (grade_scale_id, member_id, scale_name, created_date) values (3, 0, 'Competency 2', now());
118 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (1, 'A+', 90, 100);
119 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (1, 'A', 80, 89);
120 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (1, 'B', 70, 79);
121 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (1, 'C', 60, 69);
122 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (1, 'D', 50, 59);
123 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (1, 'E', 0, 49);
125 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (2, 'Pass', 75, 100);
126 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (2, 'Fail', 0, 74);
128 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (3, 'Excellent', 80, 100);
129 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (3, 'Good', 70, 79);
130 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (3, 'Adequate', 60, 69);
131 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (3, 'Inadequate', 0, 59);
133 INSERT INTO `modules` (`dir_name`, `status`, `privilege`, `admin_privilege`, `cron_interval`, `cron_last_run`) VALUES('_standard/gradebook', 2, 1048576, 4096, 0, 0);
135 DELETE FROM `modules` WHERE dir_name = 'gradebook';
137 UPDATE `courses` set home_links=replace(home_links, 'mods/gradebook/my_gradebook.php', 'mods/_standard/gradebook/my_gradebook.php'), main_links=replace(main_links, 'mods/gradebook/my_gradebook.php', 'mods/_standard/gradebook/my_gradebook.php');
139 # SQL for collecting guest information at test introduction page
140 CREATE TABLE `guests` (
141 `guest_id` VARCHAR(10) NOT NULL,
143 `organization` VARCHAR(255),
144 `location` VARCHAR(255),
146 `focus` VARCHAR(255),
147 PRIMARY KEY (`guest_id`)
150 ALTER TABLE `tests_results` MODIFY member_id VARCHAR(10);
151 ALTER TABLE `tests` ADD COLUMN `show_guest_form` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' AFTER `failfeedback`;
154 #move the old student toos to course tool, and add the new student tools
155 UPDATE `modules` SET `dir_name` = '_standard/course_tools' WHERE `dir_name` = '_standard/student_tools' LIMIT 1 ;
156 INSERT INTO `modules` (`dir_name` ,`status` ,`privilege` ,`admin_privilege` ,`cron_interval` ,`cron_last_run`)VALUES ('_standard/student_tools', '2', '2097152', '0', '0', '0');
158 CREATE TABLE `fha_student_tools` (
159 `course_id` mediumint(8) unsigned NOT NULL,
160 `links` text NOT NULL ,
161 PRIMARY KEY ( `course_id` )
163 # Add the forum archiver to standard modules
164 INSERT INTO `modules` (`dir_name` ,`status` ,`privilege` ,`admin_privilege` ,`cron_interval` ,`cron_last_run`)VALUES ('_standard/farchive', '2', '4194304', '0', '0', '0');