| Index: trunk/extensions/EducationProgram/sql/AddExtraFields.sql |
| — | — | @@ -0,0 +1,26 @@ |
| | 2 | +-- SQL for the Education Program extension. |
| | 3 | +-- Adds additional fields. |
| | 4 | +-- Licence: GNU GPL v3+ |
| | 5 | +-- Author: Jeroen De Dauw < jeroendedauw@gmail.com > |
| | 6 | + |
| | 7 | +ALTER TABLE /*_*/ep_orgs ADD COLUMN org_courses SMALLINT unsigned NOT NULL; |
| | 8 | +ALTER TABLE /*_*/ep_orgs ADD COLUMN org_mentors SMALLINT unsigned NOT NULL; |
| | 9 | +ALTER TABLE /*_*/ep_orgs ADD COLUMN org_students INT unsigned NOT NULL; |
| | 10 | + |
| | 11 | +CREATE INDEX /*i*/ep_org_courses ON /*_*/ep_orgs (org_courses); |
| | 12 | +CREATE INDEX /*i*/ep_org_mentors ON /*_*/ep_orgs (org_mentors); |
| | 13 | +CREATE INDEX /*i*/ep_org_students ON /*_*/ep_orgs (org_students); |
| | 14 | + |
| | 15 | +ALTER TABLE /*_*/ep_courses ADD COLUMN course_lang VARCHAR(10) NOT NULL; |
| | 16 | +ALTER TABLE /*_*/ep_courses ADD COLUMN course_students SMALLINT unsigned NOT NULL; |
| | 17 | + |
| | 18 | +CREATE INDEX /*i*/ep_course_lang ON /*_*/ep_courses (course_lang); |
| | 19 | +CREATE INDEX /*i*/ep_course_students ON /*_*/ep_courses (course_students); |
| | 20 | + |
| | 21 | +ALTER TABLE /*_*/ep_students ADD COLUMN student_first_enroll varbinary(14) NOT NULL; |
| | 22 | +ALTER TABLE /*_*/ep_students ADD COLUMN student_last_active varbinary(14) NOT NULL; |
| | 23 | +ALTER TABLE /*_*/ep_students ADD COLUMN student_active_enroll TINYINT unsigned NOT NULL; |
| | 24 | + |
| | 25 | +CREATE INDEX /*i*/ep_students_first_enroll ON /*_*/ep_students (student_first_enroll); |
| | 26 | +CREATE INDEX /*i*/ep_students_last_active ON /*_*/ep_students (student_last_active); |
| | 27 | +CREATE INDEX /*i*/ep_students_active_enroll ON /*_*/ep_students (student_active_enroll); |
| \ No newline at end of file |
| Property changes on: trunk/extensions/EducationProgram/sql/AddExtraFields.sql |
| ___________________________________________________________________ |
| Added: svn:eol-style |
| 1 | 28 | + native |
| Index: trunk/extensions/EducationProgram/sql/EducationProgram.sql |
| — | — | @@ -5,27 +5,42 @@ |
| 6 | 6 | -- Organizations, ie universities |
| 7 | 7 | CREATE TABLE IF NOT EXISTS /*_*/ep_orgs ( |
| 8 | 8 | org_id INT unsigned NOT NULL auto_increment PRIMARY KEY, |
| | 9 | + |
| 9 | 10 | org_name VARCHAR(255) NOT NULL, -- Name of the organization |
| 10 | 11 | org_city VARCHAR(255) NOT NULL, -- Name of the city where the org is located |
| 11 | | - org_country VARCHAR(255) NOT NULL -- Name of the country where the org is located |
| | 12 | + org_country VARCHAR(255) NOT NULL, -- Name of the country where the org is located |
| | 13 | + |
| | 14 | + org_courses SMALLINT unsigned NOT NULL, -- Amount of courses |
| | 15 | + org_mentors SMALLINT unsigned NOT NULL, -- Amount of mentors |
| | 16 | + org_students INT unsigned NOT NULL -- Amount of students |
| 12 | 17 | ) /*$wgDBTableOptions*/; |
| 13 | 18 | |
| 14 | 19 | CREATE UNIQUE INDEX /*i*/ep_org_name ON /*_*/ep_orgs (org_name); |
| | 20 | +CREATE INDEX /*i*/ep_org_courses ON /*_*/ep_orgs (org_courses); |
| | 21 | +CREATE INDEX /*i*/ep_org_mentors ON /*_*/ep_orgs (org_mentors); |
| | 22 | +CREATE INDEX /*i*/ep_org_students ON /*_*/ep_orgs (org_students); |
| 15 | 23 | |
| 16 | 24 | -- Courses. These describe a specific course, time-independent. |
| 17 | 25 | CREATE TABLE IF NOT EXISTS /*_*/ep_courses ( |
| 18 | 26 | course_id INT unsigned NOT NULL auto_increment PRIMARY KEY, |
| | 27 | + |
| 19 | 28 | course_org_id INT unsigned NOT NULL, -- Foreign key on ep_orgs.org_id |
| 20 | 29 | course_name VARCHAR(255) NOT NULL, -- Name of the course |
| 21 | | - course_description TEXT NOT NULL -- Description of the course |
| | 30 | + course_description TEXT NOT NULL, -- Description of the course |
| | 31 | + course_lang VARCHAR(10) NOT NULL, -- Language (code) |
| | 32 | + |
| | 33 | + course_students SMALLINT unsigned NOT NULL -- Amount of students |
| 22 | 34 | ) /*$wgDBTableOptions*/; |
| 23 | 35 | |
| 24 | 36 | CREATE INDEX /*i*/ep_course_org_id ON /*_*/ep_courses (course_org_id); |
| 25 | 37 | CREATE UNIQUE INDEX /*i*/ep_course_name ON /*_*/ep_courses (course_name); |
| | 38 | +CREATE INDEX /*i*/ep_course_lang ON /*_*/ep_courses (course_lang); |
| | 39 | +CREATE INDEX /*i*/ep_course_students ON /*_*/ep_courses (course_students); |
| 26 | 40 | |
| 27 | 41 | -- Terms. These are "instances" of a course in a certain period. |
| 28 | 42 | CREATE TABLE IF NOT EXISTS /*_*/ep_terms ( |
| 29 | 43 | term_id INT unsigned NOT NULL auto_increment PRIMARY KEY, |
| | 44 | + |
| 30 | 45 | term_course_id INT unsigned NOT NULL, -- Foreign key on ep_courses.course_id |
| 31 | 46 | term_org_id INT unsigned NOT NULL, -- Foreign key on ep_orgs.org_id. Helper field, not strictly needed. |
| 32 | 47 | term_year SMALLINT unsigned NOT NULL, -- Yeah in which the term takes place |
| — | — | @@ -43,10 +58,18 @@ |
| 44 | 59 | -- Students. In essence this is an extension to the user table. |
| 45 | 60 | CREATE TABLE IF NOT EXISTS /*_*/ep_students ( |
| 46 | 61 | student_id INT unsigned NOT NULL auto_increment PRIMARY KEY, |
| 47 | | - student_user_id INT unsigned NOT NULL -- Foreign key on user.user_id |
| | 62 | + |
| | 63 | + student_user_id INT unsigned NOT NULL, -- Foreign key on user.user_id |
| | 64 | + student_first_enroll varbinary(14) NOT NULL, -- Time of first enrollment |
| | 65 | + |
| | 66 | + student_last_active varbinary(14) NOT NULL, -- Time of last activity |
| | 67 | + student_active_enroll TINYINT unsigned NOT NULL -- If the student is enrolled in any active terms |
| 48 | 68 | ) /*$wgDBTableOptions*/; |
| 49 | 69 | |
| 50 | 70 | CREATE UNIQUE INDEX /*i*/ep_students_user_id ON /*_*/ep_students (student_user_id); |
| | 71 | +CREATE INDEX /*i*/ep_students_first_enroll ON /*_*/ep_students (student_first_enroll); |
| | 72 | +CREATE INDEX /*i*/ep_students_last_active ON /*_*/ep_students (student_last_active); |
| | 73 | +CREATE INDEX /*i*/ep_students_active_enroll ON /*_*/ep_students (student_active_enroll); |
| 51 | 74 | |
| 52 | 75 | -- Mentors. In essence this is an extension to the user table. |
| 53 | 76 | CREATE TABLE IF NOT EXISTS /*_*/ep_mentors ( |
| Index: trunk/extensions/EducationProgram/EducationProgram.hooks.php |
| — | — | @@ -24,13 +24,17 @@ |
| 25 | 25 | * @return true |
| 26 | 26 | */ |
| 27 | 27 | public static function onSchemaUpdate( DatabaseUpdater $updater ) { |
| 28 | | - $updater->addExtensionUpdate( array( |
| 29 | | - 'addTable', |
| | 28 | + $updater->addExtensionTable( |
| 30 | 29 | 'ep_orgs', |
| 31 | | - dirname( __FILE__ ) . '/sql/EducationProgram.sql', |
| 32 | | - true |
| 33 | | - ) ); |
| 34 | | - |
| | 30 | + dirname( __FILE__ ) . '/sql/EducationProgram.sql' |
| | 31 | + ); |
| | 32 | + |
| | 33 | + $updater->addExtensionField( |
| | 34 | + 'ep_orgs', |
| | 35 | + 'org_courses', |
| | 36 | + dirname( __FILE__ ) . '/sql/AddExtraFields.sql' |
| | 37 | + ); |
| | 38 | + |
| 35 | 39 | return true; |
| 36 | 40 | } |
| 37 | 41 | |