| Index: trunk/extensions/EducationProgram/sql/EducationProgram.sql |
| — | — | @@ -36,32 +36,59 @@ |
| 37 | 37 | CREATE INDEX /*i*/ep_term_start ON /*_*/ep_terms (term_start); |
| 38 | 38 | CREATE INDEX /*i*/ep_term_end ON /*_*/ep_terms (term_end); |
| 39 | 39 | |
| | 40 | +-- Students. In essence this is an extension to the user table. |
| | 41 | +CREATE TABLE IF NOT EXISTS /*_*/ep_students ( |
| | 42 | + student_id INT unsigned NOT NULL auto_increment PRIMARY KEY, |
| | 43 | + student_user_id INT unsigned NOT NULL -- Foreign key on user.user_id |
| | 44 | +) /*$wgDBTableOptions*/; |
| 40 | 45 | |
| | 46 | +CREATE UNIQUE INDEX /*i*/ep_students_user_id ON /*_*/ep_students (student_user_id); |
| | 47 | + |
| | 48 | +-- Mentors. In essence this is an extension to the user table. |
| | 49 | +CREATE TABLE IF NOT EXISTS /*_*/ep_mentors ( |
| | 50 | + mentor_id INT unsigned NOT NULL auto_increment PRIMARY KEY, |
| | 51 | + mentor_user_id INT unsigned NOT NULL -- Foreign key on user.user_id |
| | 52 | +) /*$wgDBTableOptions*/; |
| | 53 | + |
| | 54 | +CREATE UNIQUE INDEX /*i*/ep_mentors_user_id ON /*_*/ep_mentors (mentor_user_id); |
| | 55 | + |
| | 56 | +-- Links a term with all it's students. |
| 41 | 57 | CREATE TABLE IF NOT EXISTS /*_*/ep_students_per_term ( |
| 42 | | - student_user_id INT unsigned NOT NULL, -- Foreign key on user.user_id |
| 43 | | - student_term_id INT unsigned NOT NULL -- Foreign key on ep_terms.term_id |
| | 58 | + spt_student_id INT unsigned NOT NULL, -- Foreign key on ep_students.student_id |
| | 59 | + spt_term_id INT unsigned NOT NULL -- Foreign key on ep_terms.term_id |
| 44 | 60 | ) /*$wgDBTableOptions*/; |
| 45 | 61 | |
| 46 | | -CREATE UNIQUE INDEX /*i*/ep_students_per_term ON /*_*/ep_students_per_term (student_user_id, student_term_id); |
| | 62 | +CREATE UNIQUE INDEX /*i*/ep_students_per_term ON /*_*/ep_students_per_term (spt_student_id, spt_term_id); |
| 47 | 63 | |
| | 64 | +-- Links an org with all it's mentors. |
| 48 | 65 | CREATE TABLE IF NOT EXISTS /*_*/ep_mentors_per_org ( |
| 49 | | - mentor_user_id INT unsigned NOT NULL, -- Foreign key on user.user_id |
| 50 | | - mentor_org_id INT unsigned NOT NULL -- Foreign key on ep_orgs.org_id |
| | 66 | + mpo_mentor_id INT unsigned NOT NULL, -- Foreign key on ep_mentors.mentor_id |
| | 67 | + mpo_org_id INT unsigned NOT NULL -- Foreign key on ep_orgs.org_id |
| 51 | 68 | ) /*$wgDBTableOptions*/; |
| 52 | 69 | |
| 53 | | -CREATE UNIQUE INDEX /*i*/ep_mentors_per_org ON /*_*/ep_mentors_per_org (mentor_user_id, mentor_org_id); |
| \ No newline at end of file |
| | 70 | +CREATE UNIQUE INDEX /*i*/ep_mentors_per_org ON /*_*/ep_mentors_per_org (mpo_mentor_id, mpo_org_id); |
| | 71 | + |
| | 72 | +-- Revision table, holding blobs of various types of objects, such as orgs or students. |
| | 73 | +-- This is somewhat based on the (core) revision table and is meant to serve |
| | 74 | +-- as a prototype for a more general system to store this kind of data in a versioned fashion. |
| | 75 | +CREATE TABLE IF NOT EXISTS /*_*/ep_revisions ( |
| | 76 | + rev_id INT unsigned NOT NULL auto_increment PRIMARY KEY, |
| | 77 | + rev_type varbinary(32) NOT NULL, |
| | 78 | + rev_comment TINYBLOB NOT NULL, |
| | 79 | + rev_user_id INT unsigned NOT NULL default 0, |
| | 80 | + rev_user_text varbinary(255) NOT NULL, |
| | 81 | + rev_time varbinary(14) NOT NULL, |
| | 82 | + rev_minor_edit TINYINT unsigned NOT NULL default 0, |
| | 83 | + rev_deleted TINYINT unsigned NOT NULL default 0, |
| | 84 | + rev_data BLOB NOT NULL |
| | 85 | +) /*$wgDBTableOptions*/; |
| | 86 | + |
| | 87 | +CREATE INDEX /*i*/ep_revision_type ON /*_*/ep_revisions (rev_type); |
| | 88 | +CREATE INDEX /*i*/ep_revision_user_id ON /*_*/ep_revisions (rev_user_id); |
| | 89 | +CREATE INDEX /*i*/ep_revision_user_text ON /*_*/ep_revisions (rev_user_text); |
| | 90 | +CREATE INDEX /*i*/ep_revision_time ON /*_*/ep_revisions (rev_time); |
| | 91 | +CREATE INDEX /*i*/ep_revision_minor_edit ON /*_*/ep_revisions (rev_minor_edit); |
| | 92 | +CREATE INDEX /*i*/ep_revision_deleted ON /*_*/ep_revisions (rev_deleted); |
| | 93 | + |
| | 94 | +-- TODO: figure out how to best do logging. |
| | 95 | +-- Can the core stuff be used in a sane way for this? |
| \ No newline at end of file |