Parent Directory
| 
Revision Log
more database schema notes *sigh* -sam
1 * indicates a key 2 => indicates a relation 3 ! indicates an override field 4 ~ indicates a "dynamic" field 5 6 -------------------------------------------------------------------------------- 7 Current "schema" (actually emulated) 8 -------------------------------------------------------------------------------- 9 10 Classlist.pm 11 user 12 id * 13 first_name 14 last_name 15 email_address 16 student_id 17 status 18 section 19 recitation 20 comment 21 22 Auth.pm 23 password 24 login_id => user.id 25 password 26 permissions 27 login_id => user.id 28 permissions 29 key 30 login_id => user.id 31 key 32 33 WW.pm 34 psvn 35 set_id => set.id 36 login_id => user.id 37 psvn 38 set 39 id * 40 login_id => user.id 41 set_header 42 problem_header 43 open_date 44 due_date 45 answer_date 46 problem 47 id * 48 set_id *, => set.id 49 login_id *, => user.id 50 source_file 51 value 52 max_attempts 53 problem_seed 54 status ~ 55 attempted ~ 56 last_answer ~ 57 num_correct ~ 58 num_incorrect ~ 59 60 -------------------------------------------------------------------------------- 61 Additional functionality needed 62 -------------------------------------------------------------------------------- 63 64 Separate several types of data that are currently stored in the set and problem tables: 65 global static data - data that's the same for all users 66 student-specific static data - overrides global static data for student 67 student-specific dynamic data - data added as student solves problems 68 69 Need to be able to impose order on sets and problems. 70 71 Need to be able to 72 73 -------------------------------------------------------------------------------- 74 New schema 75 -------------------------------------------------------------------------------- 76 77 WW.pm 78 set 79 id * 80 set_header 81 problem_header 82 open_date 83 due_date 84 answer_date 85 problem_order 86 set_user 87 user_id *,=> user.id 88 set_id *,=> set.id 89 psvn 90 set_header ! 91 problem_header ! 92 open_date ! 93 due_date ! 94 answer_date ! 95 problem_order ! 96 problem 97 id * 98 set_id *,=> set.id 99 source_file 100 value 101 max_attempts 102 problem_user 103 user_id *,=> user.id 104 problem_id *,=> problem.id 105 source_file ! 106 value ! 107 max_attempts ! 108 problem_seed 109 status ~ 110 attempted ~ 111 last_answer ~ 112 num_correct ~ 113 num_incorrect ~ 114 115 -------------------------------------------------------------------------------- 116 Notes on the new schema 117 -------------------------------------------------------------------------------- 118 119 CHANGES IN THE SCHEMA 120 --------------------- 121 122 PSVNs have been added to the set_user table, eliminating the need for a separate PSVN table. 123 124 set_user and problem_user tables have been added, containing student-specific data. In most cases, the override fields (marked `!' above) will be empty, and accesses using the current API will fall back on the global values. The dynamic fields (marked `~' above) will be populated as the student works through problems. problem_seed (in problem_user) and psvn(in set_user) are neither dynamic or override fields -- they are set at assignment time. 125 126 problem_order has been added to the set and set_user tables. It contains a definition of how the problems in each set will be ordered. 127 128 SCHEMA IMPLEMENTATION IN HASH-BASED DATABASES 129 --------------------------------------------- 130 131 To implement this schema in a hash-based database, each table will be stored in a separate database file. Each table has either one or two fields that make up a unique identifier for each record. In the case of a one field identifier, the value of that field will be used as the record's key in the hash. In the case of a two-field identifier, the string "FIELD1:FIELD2" will be used. Literal colons will be escaped as `\:', and literal backslashes as `\\'. 132 133 Rather than use a custom encoding scheme for the hash data, as is done in the 1.x implementation, a simple table-based scheme will be used, in which each field is separated by a colon (`:'), literal colons (and literal backslashes) will be dealt with as above. This sort of scheme is common in the UNIX world. For example, consider the UNIX password file. 134 135 COMPATABILITY WITH 1.X DATABASES 136 -------------------------------- 137 138 The WW.pm module will be able to differentiate between a 1.x database file and a set of 2.x database files by checking for files with specific names. If it encounters a 1.x file, it will use the 1.x implementations of the current API and throw an exception when new API functions are called. 139 140 A function databaseVersion() will be available to ask WW.pm if it is using 1.x or 2.x database files. 141 142 Conversion from the 1.x schema to the 2.x schema can be achieved by using the most popular value for each field in each user-specific record as the global value, and merging PSVNs from the separate PSVN table. Conversion from the 2.x schema to the 1.x schema is trivial, if somewhat lossy (i.e. problem_order). 143 144 TREATMENT OF THE CURRENT API 145 ---------------------------- 146 147 The current API consists of the following functions: 148 149 getSets($userID) 150 getSet($userID, $setID) 151 setSet($set) 152 deleteSet($userID, $setID) 153 154 getProblems($userID, $setID) 155 getProblem($userID, $setID, $problemNumber) 156 setProblem($problem) 157 deleteProblem($userID, $setID, $problemNumber) 158 159 getPSVNs($userID) 160 getPSVN($userID, $setID) 161 setPSVN(userID, $setID) 162 deletePSVN($userID, $setID) 163 164 When using 2.x database files, the current API will be implemented as follows: 165 166 getSets and getProblems will use the user-specific tables to get sets/problems for a specific user. 167 168 Reads using the current API will be implemented by selecting the proper record from the global table, and then applying additional fields from the user-specific table (i.e. problem_user or set_user). 169 170 Problem.pm currently writes Problem objects to the database. Writes using the current API will be implemented as follows: 171 - fields which only exist in the user-specific table will be stored 172 there 173 - fields which exist in both will be written to the user-specific table 174 IF THE NEW VALUES DIFFER FROM THE CURRENT GLOBAL VALUES 175 Writes which would create a new record ARE NOT ALLOWED using the old API! 176 177 Deletes using the current API will be implemented by calling the global deleteGlobal{Set,Problem} functions. Note that delete{Set,Problem} is never called outside of the professor pages, so these functions could be safely removed. 178 179 {get,set,delete}PSVN and getPSVNs will be REMOVED, as PSVN is now a field in set_user. 180 181 NEW API FUNCTIONS 182 ----------------- 183 184 There will be new getGlobalSets() and getGlobalProblems($setID) functions that use the global tables. 185 186 There will be a getSetUsers($setID) function that returns the userIDs of all users for whom a given set is assigned. 187 188 There will be new {get,set,delete}GlobalSet($setID) and {get,set,delete}GlobalProblem($setID, $problemID) functions to access and modify the global {set,problem} tables. Delete functions will remove all matching records from the user-specific table, and remove the single matching record from the global table. setGlobal{Set,Problem} functions may create records. 189 190 There will be new assignSet($userID, $setID) function that creates a user-specific set_user record for a given user and set. A unique PSVN is generated. A similar assignProblem($userID, $setID, $problemID) function will create a user-specific problem_user record for the given problem number. A random seed will be generated. unAssign{Set,Problem} functions will also be created. 191 192 There will be new {get,set}UserSet($userID, $setID) and {get,set}UserProblem($userID, $setID, $problemID) functions that allow access to user-specific records without first falling back on global values (as {get,set}{Set,Problem} do. setUser{Set,Problem} functions may not create records -- this is done with assign{Set,Problem}.
| aubreyja at gmail dot com | ViewVC Help |
| Powered by ViewVC 1.0.9 |