| Index: trunk/extensions/EducationProgram/sql/EducationProgram.sql |
| — | — | @@ -36,7 +36,7 @@ |
| 37 | 37 | CREATE TABLE IF NOT EXISTS /*_*/ep_courses ( |
| 38 | 38 | course_id INT unsigned NOT NULL auto_increment PRIMARY KEY, |
| 39 | 39 | |
| 40 | | - course_org_id INT unsigned NOT NULL, -- Foreign key on ep_orgs.org_id. Helper field, not strictly needed. |
| | 40 | + course_org_id INT unsigned NOT NULL, -- Foreign key on ep_orgs.org_id. |
| 41 | 41 | course_name VARCHAR(255) NOT NULL, -- Title of the course. ie "Master in Angry Birds (2012 q1)" |
| 42 | 42 | course_mc VARCHAR(255) NOT NULL, -- Name of the course. ie "Master in Angry Birds" |
| 43 | 43 | course_start varbinary(14) NOT NULL, -- Start time of the course |
| — | — | @@ -96,6 +96,19 @@ |
| 97 | 97 | |
| 98 | 98 | |
| 99 | 99 | |
| | 100 | +-- Links the students with their courses. |
| | 101 | +CREATE TABLE IF NOT EXISTS /*_*/ep_users_per_course ( |
| | 102 | + upc_user_id INT unsigned NOT NULL, -- Foreign key on ep_user.user_id |
| | 103 | + upc_course_id INT unsigned NOT NULL, -- Foreign key on ep_courses.course_id |
| | 104 | + upc_role TINYINT unsigned NOT NULL -- The role the user has for the course |
| | 105 | +) /*$wgDBTableOptions*/; |
| | 106 | + |
| | 107 | +CREATE UNIQUE INDEX /*i*/ep_users_per_course ON /*_*/ep_users_per_course (upc_user_id, upc_course_id, upc_role); |
| | 108 | +CREATE INDEX /*i*/ep_upc_course_id ON /*_*/ep_users_per_course (upc_course_id); |
| | 109 | +CREATE INDEX /*i*/ep_upc_role ON /*_*/ep_users_per_course (upc_role); |
| | 110 | + |
| | 111 | + |
| | 112 | + |
| 100 | 113 | -- Students. In essence this is an extension to the user table. |
| 101 | 114 | CREATE TABLE IF NOT EXISTS /*_*/ep_students ( |
| 102 | 115 | student_id INT unsigned NOT NULL auto_increment PRIMARY KEY, |
| — | — | @@ -114,19 +127,6 @@ |
| 115 | 128 | |
| 116 | 129 | |
| 117 | 130 | |
| 118 | | -CREATE TABLE IF NOT EXISTS /*_*/ep_users_per_course ( |
| 119 | | - upc_user_id INT unsigned NOT NULL, -- Foreign key on ep_user.user_id |
| 120 | | - upc_course_id INT unsigned NOT NULL, -- Foreign key on ep_courses.course_id |
| 121 | | - upc_role TINYINT unsigned NOT NULL -- The role the user has for the course |
| 122 | | -) /*$wgDBTableOptions*/; |
| 123 | | - |
| 124 | | -CREATE UNIQUE INDEX /*i*/ep_users_per_course ON /*_*/ep_users_per_course (upc_user_id, upc_course_id, upc_role); |
| 125 | | -CREATE INDEX /*i*/ep_upc_course_id ON /*_*/ep_users_per_course (upc_course_id); |
| 126 | | -CREATE INDEX /*i*/ep_upc_role ON /*_*/ep_users_per_course (upc_role); |
| 127 | | - |
| 128 | | - |
| 129 | | - |
| 130 | 131 | -- Instructors. In essence this is an extension to the user table. |
| 131 | 132 | CREATE TABLE IF NOT EXISTS /*_*/ep_instructors ( |
| 132 | 133 | instructor_id INT unsigned NOT NULL auto_increment PRIMARY KEY, |
| — | — | @@ -172,15 +172,43 @@ |
| 173 | 173 | -- as a prototype for a more general system to store this kind of data in a visioned fashion. |
| 174 | 174 | CREATE TABLE IF NOT EXISTS /*_*/ep_revisions ( |
| 175 | 175 | rev_id INT unsigned NOT NULL auto_increment PRIMARY KEY, |
| | 176 | + |
| | 177 | + -- Id of the object from it's cannonical table. |
| | 178 | + -- Since we can have multiple revisions of the same object, this is not unique. |
| | 179 | + -- Also note that for selection you need the type as well, since objects |
| | 180 | + -- of different types can have the same id. |
| 176 | 181 | rev_object_id INT unsigned NOT NULL, |
| | 182 | + |
| | 183 | + -- Optional identifier for the object, such as a page name. |
| | 184 | + -- This is needed to be able to find revisions of deleted items for which only such an identifier is provided. |
| 177 | 185 | rev_object_identifier VARCHAR(255) NULL, |
| | 186 | + |
| | 187 | + -- String idenifying the type of the object. |
| | 188 | + -- This is used to resolve which table it belongs to. |
| 178 | 189 | rev_type varbinary(32) NOT NULL, |
| | 190 | + |
| | 191 | + -- Comment provided by the user that created this revision. |
| 179 | 192 | rev_comment TINYBLOB NOT NULL, |
| | 193 | + |
| | 194 | + -- Id of the user that created this revision. 0 if anon. |
| 180 | 195 | rev_user_id INT unsigned NOT NULL default 0, |
| | 196 | + |
| | 197 | + -- Name of the user that created this revision. ip address if anon. |
| 181 | 198 | rev_user_text varbinary(255) NOT NULL, |
| | 199 | + |
| | 200 | + -- Time at which the revision was made. |
| 182 | 201 | rev_time varbinary(14) NOT NULL, |
| | 202 | + |
| | 203 | + -- If the revision is a minor edit. |
| 183 | 204 | rev_minor_edit TINYINT unsigned NOT NULL default 0, |
| | 205 | + |
| | 206 | + -- If the revision is a deletion. |
| 184 | 207 | rev_deleted TINYINT unsigned NOT NULL default 0, |
| | 208 | + |
| | 209 | + -- The actual revision content. This is a blob containing the fields |
| | 210 | + -- of the object (array) passed to PHPs serialize(). |
| | 211 | + -- A new DBDataObject of it's type can be constructed by passing |
| | 212 | + -- it the result of unserialize on this blob. |
| 185 | 213 | rev_data BLOB NOT NULL |
| 186 | 214 | ) /*$wgDBTableOptions*/; |
| 187 | 215 | |