ILIAS  eassessment Revision 61809
 All Data Structures Namespaces Files Functions Variables Groups Pages
class.ilUniStuttgartDBCreator.php
Go to the documentation of this file.
1 <?php
2 
9 class ilUniStuttgartDBCreator
10 {
11 
12  function updateTables()
13  {
14  global $ilDB, $ilSetting;
15 
16  $db = $ilSetting->get("patch_stex_db");
17 
18  // STEP 1:
19  if ($db <= 0)
20  {
21  $ilDB->createTable("exc_assignment",
22  array(
23  "id" => array(
24  "type" => "integer", "length" => 4, "notnull" => true
25  ),
26  "exc_id" => array(
27  "type" => "integer", "length" => 4, "notnull" => true
28  ),
29  "time_stamp" => array(
30  "type" => "integer", "length" => 4, "notnull" => false
31  ),
32  "instruction" => array(
33  "type" => "clob"
34  )
35  )
36  );
37 
38  $ilDB->addPrimaryKey("exc_assignment", array("id"));
39 
40  $ilDB->createSequence("exc_assignment");
41 
42  $ilSetting->set("patch_stex_db", 1);
43  }
44 
45  // STEP 2:
46  if ($db <= 1)
47  {
48  $ilDB->createTable("exc_mem_ass_status",
49  array(
50  "ass_id" => array(
51  "type" => "integer", "length" => 4, "notnull" => true
52  ),
53  "usr_id" => array(
54  "type" => "integer", "length" => 4, "notnull" => true
55  ),
56  "notice" => array(
57  "type" => "text", "length" => 4000, "notnull" => false
58  ),
59  "returned" => array(
60  "type" => "integer", "length" => 1, "notnull" => true, "default" => 0
61  ),
62  "solved" => array(
63  "type" => "integer", "length" => 1, "notnull" => false
64  ),
65  "status_time" => array(
66  "type" => "timestamp", "notnull" => false
67  ),
68  "sent" => array(
69  "type" => "integer", "length" => 1, "notnull" => false
70  ),
71  "sent_time" => array(
72  "type" => "timestamp", "notnull" => false
73  ),
74  "feedback_time" => array(
75  "type" => "timestamp", "notnull" => false
76  ),
77  "feedback" => array(
78  "type" => "integer", "length" => 1, "notnull" => true, "default" => 0
79  ),
80  "status" => array(
81  "type" => "text", "length" => 9, "fixed" => true, "default" => "notgraded", "notnull" => false
82  )
83  )
84  );
85 
86  $ilDB->addPrimaryKey("exc_mem_ass_status", array("ass_id", "usr_id"));
87 
88  $ilSetting->set("patch_stex_db", 2);
89  }
90 
91 
92 
93  // STEP 3:
94  if ($db <= 2)
95  {
96  $ilDB->addTableColumn("exc_returned",
97  "ass_id",
98  array("type" => "integer", "length" => 4, "notnull" => false));
99 
100  $ilSetting->set("patch_stex_db", 3);
101  }
102 
103  // STEP 4:
104  if ($db <= 3)
105  {
106  $ilDB->createTable("exc_mem_tut_status",
107  array (
108  "ass_id" => array(
109  "type" => "integer", "length" => 4, "notnull" => true
110  ),
111  "mem_id" => array(
112  "type" => "integer", "length" => 4, "notnull" => true
113  ),
114  "tut_id" => array(
115  "type" => "integer", "length" => 4, "notnull" => true
116  ),
117  "download_time" => array(
118  "type" => "timestamp"
119  )
120  )
121  );
122 
123  $ilSetting->set("patch_stex_db", 4);
124  }
125 
126  // STEP 5:
127  if ($db <= 4)
128  {
129  $ilDB->addPrimaryKey("exc_mem_tut_status", array("ass_id", "mem_id", "tut_id"));
130 
131  $ilSetting->set("patch_stex_db", 5);
132  }
133 
134  // STEP 6:
135  if ($db <= 5)
136  {
137  $set = $ilDB->query("SELECT * FROM exc_data");
138  while ($rec = $ilDB->fetchAssoc($set))
139  {
140  // Create exc_assignment records for all existing exercises
141  // -> instruction and time_stamp fields in exc_data are obsolete
142  $next_id = $ilDB->nextId("exc_assignment");
143  $ilDB->insert("exc_assignment", array(
144  "id" => array("integer", $next_id),
145  "exc_id" => array("integer", $rec["obj_id"]),
146  "time_stamp" => array("integer", $rec["time_stamp"]),
147  "instruction" => array("clob", $rec["instruction"])
148  ));
149  }
150 
151  $ilSetting->set("patch_stex_db", 6);
152  }
153 
154  // STEP 7:
155  if ($db <= 6)
156  {
157  $ilDB->addIndex("exc_members", array("obj_id"), "ob");
158  $ilSetting->set("patch_stex_db", 7);
159  }
160 
161  // STEP 8:
162  if ($db <= 7)
163  {
164  $set = $ilDB->query("SELECT id, exc_id FROM exc_assignment");
165  while ($rec = $ilDB->fetchAssoc($set))
166  {
167  $set2 = $ilDB->query("SELECT * FROM exc_members ".
168  " WHERE obj_id = ".$ilDB->quote($rec["exc_id"], "integer")
169  );
170  while ($rec2 = $ilDB->fetchAssoc($set2))
171  {
172  $ilDB->manipulate("INSERT INTO exc_mem_ass_status ".
173  "(ass_id, usr_id, notice, returned, solved, status_time, sent, sent_time,".
174  "feedback_time, feedback, status) VALUES (".
175  $ilDB->quote($rec["id"], "integer").",".
176  $ilDB->quote($rec2["usr_id"], "integer").",".
177  $ilDB->quote($rec2["notice"], "text").",".
178  $ilDB->quote($rec2["returned"], "integer").",".
179  $ilDB->quote($rec2["solved"], "integer").",".
180  $ilDB->quote($rec2["status_time"], "timestamp").",".
181  $ilDB->quote($rec2["sent"], "integer").",".
182  $ilDB->quote($rec2["sent_time"], "timestamp").",".
183  $ilDB->quote($rec2["feedback_time"], "timestamp").",".
184  $ilDB->quote($rec2["feedback"], "integer").",".
185  $ilDB->quote($rec2["status"], "text").
186  ")");
187  }
188  }
189  $ilSetting->set("patch_stex_db", 8);
190  }
191 
192  // STEP 9:
193  if ($db <= 8)
194  {
195  $ilDB->addIndex("exc_usr_tutor", array("obj_id"), "ob");
196  $ilSetting->set("patch_stex_db", 9);
197  }
198 
199  // STEP 10:
200  if ($db <= 9)
201  {
202  $set = $ilDB->query("SELECT id, exc_id FROM exc_assignment");
203  while ($rec = $ilDB->fetchAssoc($set))
204  {
205  $set2 = $ilDB->query("SELECT * FROM exc_usr_tutor ".
206  " WHERE obj_id = ".$ilDB->quote($rec["exc_id"], "integer")
207  );
208  while ($rec2 = $ilDB->fetchAssoc($set2))
209  {
210  $ilDB->manipulate("INSERT INTO exc_mem_tut_status ".
211  "(ass_id, mem_id, tut_id, download_time) VALUES (".
212  $ilDB->quote($rec["id"], "integer").",".
213  $ilDB->quote($rec2["usr_id"], "integer").",".
214  $ilDB->quote($rec2["tutor_id"], "integer").",".
215  $ilDB->quote($rec2["download_time"], "timestamp").
216  ")");
217  }
218  }
219  $ilSetting->set("patch_stex_db", 10);
220  }
221 
222  // STEP 11:
223  if ($db <= 10)
224  {
225  $set = $ilDB->query("SELECT id, exc_id FROM exc_assignment");
226  while ($rec = $ilDB->fetchAssoc($set))
227  {
228  $ilDB->manipulate("UPDATE exc_returned SET ".
229  " ass_id = ".$ilDB->quote($rec["id"], "integer").
230  " WHERE obj_id = ".$ilDB->quote($rec["exc_id"], "integer")
231  );
232  }
233  $ilSetting->set("patch_stex_db", 11);
234  }
235 
236  // STEP 12:
237  if ($db <= 11)
238  {
239  $ilDB->addTableColumn("exc_assignment",
240  "title",
241  array("type" => "text", "length" => 200, "notnull" => false));
242 
243  $ilDB->addTableColumn("exc_assignment",
244  "start_time",
245  array("type" => "integer", "length" => 4, "notnull" => false));
246 
247  $ilDB->addTableColumn("exc_assignment",
248  "mandatory",
249  array("type" => "integer", "length" => 1, "notnull" => false, "default" => 0));
250 
251  $ilSetting->set("patch_stex_db", 12);
252  }
253 
254  // STEP 13:
255  if ($db <= 12)
256  {
257  $ilDB->addTableColumn("exc_data",
258  "pass_mode",
259  array("type" => "text", "length" => 8, "fixed" => false,
260  "notnull" => true, "default" => "all"));
261 
262  $ilDB->addTableColumn("exc_data",
263  "pass_nr",
264  array("type" => "integer", "length" => 4, "notnull" => false));
265 
266  $ilSetting->set("patch_stex_db", 13);
267  }
268 
269  // STEP 14:
270  if ($db <= 13)
271  {
272  $ilDB->addTableColumn("exc_assignment",
273  "order_nr",
274  array("type" => "integer", "length" => 4, "notnull" => true, "default" => 0));
275 
276  $ilSetting->set("patch_stex_db", 14);
277  }
278 
279  // STEP 15:
280  if ($db <= 14)
281  {
282  $ilDB->addTableColumn("exc_data",
283  "show_submissions",
284  array("type" => "integer", "length" => 1, "notnull" => true, "default" => 0));
285 
286  $ilSetting->set("patch_stex_db", 15);
287  }
288 
289  // STEP 16:
290  if ($db <= 15)
291  {
292  $new_ex_path = CLIENT_DATA_DIR."/ilExercise";
293 
294  $old_ex_path = CLIENT_DATA_DIR."/exercise";
295 
296  $old_ex_files = array();
297 
298  if (is_dir($old_ex_path))
299  {
300  $dh_old_ex_path = opendir($old_ex_path);
301 
302  // old exercise files into an assoc array to
303  // avoid reading of all files each time
304 
305  while($file = readdir($dh_old_ex_path))
306  {
307  if(is_dir($old_ex_path."/".$file))
308  {
309  continue;
310  }
311  list($obj_id,$rest) = split('_',$file,2);
312  $old_ex_files[$obj_id][] = array("full" => $file,
313  "rest" => $rest);
314  }
315  }
316 //var_dump($old_ex_files);
317 
318  $set = $ilDB->query("SELECT id, exc_id FROM exc_assignment");
319  while ($rec = $ilDB->fetchAssoc($set))
320  {
321  // move exercise files to assignment directories
322  if (is_array($old_ex_files[$rec["exc_id"]]))
323  {
324  foreach ($old_ex_files[$rec["exc_id"]] as $file)
325  {
326  $old = $old_ex_path."/".$file["full"];
327  $new = $new_ex_path."/".$this->createPathFromId($rec["exc_id"], "exc").
328  "/ass_".$rec["id"]."/".$file["rest"];
329 
330  if (is_file($old))
331  {
332  ilUtil::makeDirParents(dirname($new));
333  rename($old, $new);
334 //echo "<br><br>move: ".$old.
335 // "<br>to: ".$new;
336  }
337  }
338  }
339 
340  // move submitted files to assignment directories
341  if (is_dir($old_ex_path."/".$rec["exc_id"]))
342  {
343  $old = $old_ex_path."/".$rec["exc_id"];
344  $new = $new_ex_path."/".$this->createPathFromId($rec["exc_id"], "exc").
345  "/subm_".$rec["id"];
346  ilUtil::makeDirParents(dirname($new));
347  rename($old, $new);
348 //echo "<br><br>move: ".$old.
349 // "<br>to: ".$new;
350  }
351 
352  //echo "<br>-".$rec["exc_id"]."-".$rec["id"]."-";
353  }
354  $ilSetting->set("patch_stex_db", 16);
355  }
356 
357 
358  // STEP 17:
359  if ($db <= 16)
360  {
361  $ilDB->addTableColumn("exc_usr_tutor",
362  "ass_id",
363  array("type" => "integer", "length" => 4, "notnull" => false));
364 
365  $ilSetting->set("patch_stex_db", 17);
366  }
367 
368  // STEP 18:
369  if ($db <= 17)
370  {
371  $set = $ilDB->query("SELECT id, exc_id FROM exc_assignment");
372  while ($rec = $ilDB->fetchAssoc($set))
373  {
374  $ilDB->manipulate("UPDATE exc_usr_tutor SET ".
375  " ass_id = ".$ilDB->quote($rec["id"], "integer").
376  " WHERE obj_id = ".$ilDB->quote($rec["exc_id"], "integer")
377  );
378  }
379  $ilSetting->set("patch_stex_db", 18);
380  }
381 
382  // STEP 19:
383  if ($db <= 18)
384  {
385  $this->reloadControlStructure();
386  $ilSetting->set("patch_stex_db", 19);
387  }
388 
389  // STEP 20:
390  if ($db <= 19)
391  {
392  $ilDB->addTableColumn("exc_mem_ass_status",
393  "mark",
394  array("type" => "text", "length" => 32, "notnull" => false));
395  $ilDB->addTableColumn("exc_mem_ass_status",
396  "u_comment",
397  array("type" => "text", "length" => 1000, "notnull" => false));
398 
399  $set = $ilDB->query("SELECT id, exc_id FROM exc_assignment");
400  while ($rec = $ilDB->fetchAssoc($set))
401  {
402  $set2 = $ilDB->query("SELECT * FROM ut_lp_marks WHERE obj_id = ".$ilDB->quote($rec["exc_id"], "integer"));
403  while ($rec2 = $ilDB->fetchAssoc($set2))
404  {
405  $set3 = $ilDB->query("SELECT ass_id FROM exc_mem_ass_status WHERE ".
406  "ass_id = ".$ilDB->quote($rec["id"], "integer").
407  " AND usr_id = ".$ilDB->quote($rec2["usr_id"], "integer"));
408  if ($rec3 = $ilDB->fetchAssoc($set3))
409  {
410  $ilDB->manipulate("UPDATE exc_mem_ass_status SET ".
411  " mark = ".$ilDB->quote($rec2["mark"], "text").",".
412  " u_comment = ".$ilDB->quote($rec2["u_comment"], "text").
413  " WHERE ass_id = ".$ilDB->quote($rec["id"], "integer").
414  " AND usr_id = ".$ilDB->quote($rec2["usr_id"], "integer")
415  );
416  }
417  else
418  {
419  $ilDB->manipulate("INSERT INTO exc_mem_ass_status (ass_id, usr_id, mark, u_comment) VALUES (".
420  $ilDB->quote($rec["id"], "integer").", ".
421  $ilDB->quote($rec2["usr_id"], "integer").", ".
422  $ilDB->quote($rec2["mark"], "text").", ".
423  $ilDB->quote($rec2["u_comment"], "text").")"
424  );
425  }
426  }
427  }
428 
429  $ilSetting->set("patch_stex_db", 20);
430  }
431 
432  // STEP 21:
433  if ($db <= 20)
434  {
435  $ilDB->dropPrimaryKey("exc_usr_tutor");
436  $ilDB->addPrimaryKey("exc_usr_tutor",
437  array("ass_id", "usr_id", "tutor_id"));
438  $ilSetting->set("patch_stex_db", 21);
439  }
440 
441  // STEP 22:
442  if ($db <= 21)
443  {
444  $ilDB->dropTable("exc_mem_tut_status");
445  $ilSetting->set("patch_stex_db", 22);
446  }
447 
448  // STEP 23:
449  if ($db <= 22)
450  {
451  $this->reloadControlStructure();
452  $ilSetting->set("patch_stex_db", 23);
453  }
454 
455  // keep this line at the end of the method
456  $this->finalProcessing();
457  }
458 
459  function createPathFromId($a_container_id,$a_name)
460  {
461  $max_exponent = 3;
462  $st_factor = 100;
463 
464  $path = array();
465  $found = false;
466  $num = $a_container_id;
467  for($i = $max_exponent; $i > 0;$i--)
468  {
469  $factor = pow($st_factor, $i);
470  if(($tmp = (int) ($num / $factor)) or $found)
471  {
472  $path[] = $tmp;
473  $num = $num % $factor;
474  $found = true;
475  }
476  }
477 
478  if(count($path))
479  {
480  $path_string = (implode('/',$path).'/');
481  }
482  return $path_string.$a_name.'_'.$a_container_id;
483  }
484 
485 
486 
487  function reloadControlStructure()
488  {
489  $this->reload_control_structure = true;
490  }
491 
492  function finalProcessing()
493  {
494  global $ilDB, $ilClientIniFile;
495 
496  if ($this->reload_control_structure)
497  {
498  include_once("./Services/Database/classes/class.ilDBUpdate.php");
499 // chdir("./setup");
500  include_once("./setup/classes/class.ilCtrlStructureReader.php");
501  $GLOBALS["ilCtrlStructureReader"] = new ilCtrlStructureReader();
502  $GLOBALS["ilCtrlStructureReader"]->setIniFile($ilClientIniFile);
503  $GLOBALS["ilCtrlStructureReader"]->getStructure();
504  $update = new ilDBUpdate($ilDB);
505  $update->loadXMLInfo();
506 // chdir("..");
507  }
508  }
509 }