ILIAS  eassessment Revision 61809
 All Data Structures Namespaces Files Functions Variables Groups Pages
class.ilMySQLAbstraction.php
Go to the documentation of this file.
1 <?php
2 /* Copyright (c) 1998-2009 ILIAS open source, Extended GPL, see docs/LICENSE */
3 
4 
5 
14 class ilMySQLAbstraction
15 {
16  public $analyzer;
17 
21  function __construct()
22  {
23  global $ilDB;
24 
25  $this->il_db = $ilDB;
26  $this->manager = $ilDB->db->loadModule('Manager');
27  $this->reverse = $ilDB->db->loadModule('Reverse');
28  if (is_file('../Services/Database/classes/class.ilDBAnalyzer.php'))
29  {
30  include_once '../Services/Database/classes/class.ilDBAnalyzer.php';
31  }
32  else
33  {
34  include_once './Services/Database/classes/class.ilDBAnalyzer.php';
35  }
36  $this->analyzer = new ilDBAnalyzer();
37  $this->setTestMode(false);
38  }
39 
45  function setTestMode($a_testmode)
46  {
47  $this->testmode = $a_testmode;
48  }
49 
55  function getTestMode()
56  {
57  return $this->testmode;
58  }
59 
67  function performAbstraction($a_table_name, $a_set_text_ts_fields_notnull_false = true)
68  {
69  // to do: log this procedure
70 
71 
72  // count number of records at the beginning
73  $nr_rec = $this->countRecords($a_table_name);
74 
75  // convert table name to lowercase
76  if (!$this->getTestMode())
77  {
78  $this->lowerCaseTableName($a_table_name);
79  $a_table_name = strtolower($a_table_name);
80  $this->storeStep($a_table_name, 10);
81  }
82 
83  // get auto increment information
84  $auto_inc_field = $this->analyzer->getAutoIncrementField($a_table_name);
85 
86  // get primary key information
87  $pk = $this->analyzer->getPrimaryKeyInformation($a_table_name);
88 
89  // get indices information
90  $indices = $this->analyzer->getIndicesInformation($a_table_name);
91 
92  // get constraints information
93  $constraints = $this->analyzer->getConstraintsInformation($a_table_name);
94 
95  // get field information
96  $fields = $this->analyzer->getFieldInformation($a_table_name);
97 
98  if (!$this->getTestMode())
99  {
100  // remove auto increment
101  $this->removeAutoIncrement($a_table_name, $auto_inc_field, $fields);
102  $this->storeStep($a_table_name, 20);
103 
104  // remove primary key
105  $this->removePrimaryKey($a_table_name, $pk);
106  $this->storeStep($a_table_name, 30);
107 
108  // remove indices
109  $this->removeIndices($a_table_name, $indices);
110  $this->storeStep($a_table_name, 40);
111 
112  // remove constraints
113  $this->removeConstraints($a_table_name, $constraints);
114  $this->storeStep($a_table_name, 45);
115  }
116 
117  // alter table using mdb2 field types
118  $this->alterTable($a_table_name, $fields, $a_set_text_ts_fields_notnull_false, $pk);
119  if ($this->getTestMode())
120  {
121  $a_table_name = strtolower($a_table_name)."_copy";
122  }
123  else
124  {
125  $this->storeStep($a_table_name, 50);
126  }
127 
128  // lower case field names
129  $this->lowerCaseColumnNames($a_table_name);
130  if (!$this->getTestMode())
131  {
132  $this->storeStep($a_table_name, 60);
133  }
134 
135  // add primary key
136  $this->addPrimaryKey($a_table_name, $pk);
137  if (!$this->getTestMode())
138  {
139  $this->storeStep($a_table_name, 70);
140  }
141 
142  // add indices
143  $this->addIndices($a_table_name, $indices);
144  if (!$this->getTestMode())
145  {
146  $this->storeStep($a_table_name, 80);
147  }
148 
149  // add constraints
150  $this->addConstraints($a_table_name, $constraints);
151  if (!$this->getTestMode())
152  {
153  $this->storeStep($a_table_name, 85);
154  }
155 
156  // add "auto increment" sequence
157  if ($auto_inc_field != "")
158  {
159  $this->addAutoIncrementSequence($a_table_name, $auto_inc_field);
160  }
161  if (!$this->getTestMode())
162  {
163  $this->storeStep($a_table_name, 90);
164  }
165 
166  // replace empty strings with null values in text fields
167  $this->replaceEmptyStringsWithNull($a_table_name);
168  if (!$this->getTestMode())
169  {
170  $this->storeStep($a_table_name, 100);
171  }
172 
173  // replace empty "0000-00-00..." dates with null
174  $this->replaceEmptyDatesWithNull($a_table_name);
175  if (!$this->getTestMode())
176  {
177  $this->storeStep($a_table_name, 110);
178  }
179 
180  $nr_rec2 = $this->countRecords($a_table_name);
181 
182  if (!$this->getTestMode())
183  {
184  if ($nr_rec != $nr_rec2)
185  {
186  die("ilMySQLAbstraction: Unexpected difference in table record number, table '".$a_table_name."'.".
187  " Before: ".((int) $nr_rec).", After: ".((int) $nr_rec2).".");
188  }
189  }
190  }
191 
192 
196  function countRecords($a_table_name)
197  {
198  global $ilDB;
199 
200  $st = $ilDB->prepare("SELECT count(*) AS cnt FROM `".$a_table_name."`");
201  $res = $ilDB->execute($st);
202  $rec = $ilDB->fetchAssoc($res);
203 
204  return $rec["cnt"];
205  }
206 
210  function storeStep($a_table, $a_step)
211  {
212  $st = $this->il_db->prepareManip("REPLACE INTO abstraction_progress (table_name, step)".
213  " VALUES (?,?)", array("text", "integer"));
214  $this->il_db->execute($st, array($a_table, $a_step));
215  }
216 
220  function replaceEmptyStringsWithNull($a_table)
221  {
222  global $ilDB;
223 
224  $fields = $this->analyzer->getFieldInformation($a_table);
225  $upfields = array();
226  foreach ($fields as $field => $def)
227  {
228  if ($def["type"] == "text" &&
229  ($def["length"] >= 1 && $def["length"] <= 4000))
230  {
231  $upfields[] = $field;
232  }
233  }
234  foreach ($upfields as $uf)
235  {
236  $ilDB->query("UPDATE `".$a_table."` SET `".$uf."` = null WHERE `".$uf."` = ''");
237  }
238  }
239 
243  function replaceEmptyDatesWithNull($a_table)
244  {
245  global $ilDB;
246 
247  if (!$this->il_db->tableExists($a_table))
248  {
249  return;
250  }
251 
252  $fields = $this->analyzer->getFieldInformation($a_table);
253  $upfields = array();
254  foreach ($fields as $field => $def)
255  {
256  if ($def["type"] == "timestamp")
257  {
258  $upfields[] = $field;
259  }
260  }
261  foreach ($upfields as $uf)
262  {
263  $ilDB->query("UPDATE `".$a_table."` SET `".$uf."` = null WHERE `".$uf."` = '0000-00-00 00:00:00'");
264  }
265 
266  $upfields = array();
267  reset($fields);
268  foreach ($fields as $field => $def)
269  {
270  if ($def["type"] == "date")
271  {
272  $upfields[] = $field;
273  }
274  }
275  foreach ($upfields as $uf)
276  {
277  $ilDB->query("UPDATE `".$a_table."` SET `".$uf."` = null WHERE `".$uf."` = '0000-00-00'");
278  }
279 
280  }
281 
282 
288  function lowerCaseTableName($a_table_name)
289  {
290  global $ilDB;
291 
292  if ($a_table_name != strtolower($a_table_name))
293  {
294  // this may look strange, but it does not work directly
295  // (seems that mysql does not see no difference whether upper or lowercase characters are used
296  mysql_query("ALTER TABLE `".$a_table_name."` RENAME `".strtolower($a_table_name)."xxx"."`");
297  mysql_query("ALTER TABLE `".strtolower($a_table_name)."xxx"."` RENAME `".strtolower($a_table_name)."`");
298  }
299  }
300 
304  function lowerCaseColumnNames($a_table_name)
305  {
306  global $ilDB;
307 
308  $result = mysql_query("SHOW COLUMNS FROM `".$a_table_name."`");
309  while ($row = mysql_fetch_assoc($result))
310  {
311  if ($row["Field"] != strtolower($row["Field"]))
312  {
313  $ilDB->renameTableColumn($a_table_name, $row["Field"], strtolower($row["Field"]));
314  }
315  }
316 
317  }
318 
325  function removeAutoIncrement($a_table_name, $a_auto_inc_field)
326  {
327  if ($a_auto_inc_field != "")
328  {
329  $this->il_db->modifyTableColumn($a_table_name, $a_auto_inc_field, array());
330  }
331  }
332 
339  function removePrimaryKey($a_table, $a_pk)
340  {
341  if ($a_pk["name"] != "")
342  {
343  $this->il_db->dropPrimaryKey($a_table, $a_pk["name"]);
344  }
345  }
346 
353  function removeIndices($a_table, $a_indices)
354  {
355  if (is_array($a_indices))
356  {
357  foreach($a_indices as $index)
358  {
359  $this->il_db->query("ALTER TABLE `".$a_table."` DROP INDEX `".$index["name"]."`");
360  }
361  }
362  }
363 
370  function removeConstraints($a_table, $a_constraints)
371  {
372  if (is_array($a_constraints))
373  {
374  foreach($a_constraints as $c)
375  {
376  if ($c["type"] == "unique")
377  {
378  $this->il_db->query("ALTER TABLE `".$a_table."` DROP INDEX `".$c["name"]."`");
379  }
380  }
381  }
382  }
383 
391  function alterTable($a_table, $a_fields, $a_set_text_ts_fields_notnull_false = true, $pk = "")
392  {
393  $n_fields = array();
394  foreach ($a_fields as $field => $d)
395  {
396  $def = $this->reverse->getTableFieldDefinition($a_table, $field);
397  $this->il_db->handleError($def);
398  $best_alt = $this->analyzer->getBestDefinitionAlternative($def);
399  $def = $def[$best_alt];
400 
401  // remove "current_timestamp" default for timestamps (not supported)
402  if (strtolower($def["nativetype"]) == "timestamp" &&
403  strtolower($def["default"]) == "current_timestamp")
404  {
405  unset($def["default"]);
406  }
407 
408  if (strtolower($def["type"]) == "float")
409  {
410  unset($def["length"]);
411  }
412 
413  // remove all invalid attributes
414  foreach ($def as $k => $v)
415  {
416  if (!in_array($k, array("type", "default", "notnull", "length", "unsigned", "fixed")))
417  {
418  unset($def[$k]);
419  }
420  }
421 
422  // determine length for decimal type
423  if ($def["type"] == "decimal")
424  {
425  $l_arr = explode(",",$def["length"]);
426  $def["length"] = $l_arr[0];
427  }
428 
429  // remove lenght values for float
430  if ($def["type"] == "float")
431  {
432  unset($def["length"]);
433  }
434 
435  // set notnull to false for text/timestamp/date fields
436  if ($a_set_text_ts_fields_notnull_false && ($def["type"] == "text" ||
437  $def["type"] == "timestamp" || $def["type"] == "date") &&
438  (!is_array($pk) || !isset($field,$pk["fields"][$field])))
439  {
440  $def["notnull"] = false;
441  }
442 
443  // set unsigned to false for integers
444  if ($def["type"] == "integer")
445  {
446  $def["unsigned"] = false;
447  }
448 
449  // set notnull to false for blob and clob
450  if ($def["type"] == "blob" || $def["type"] == "clob")
451  {
452  $def["notnull"] = false;
453  }
454 
455  // remove "0000-00-00..." default values
456  if (($def["type"] == "timestamp" && $def["default"] == "0000-00-00 00:00:00") ||
457  ($def["type"] == "date" && $def["default"] == "0000-00-00"))
458  {
459  unset($def["default"]);
460  }
461 
462  $a = array();
463  foreach ($def as $k => $v)
464  {
465  $a[$k] = $v;
466  }
467  $def["definition"] = $a;
468 
469  $n_fields[$field] = $def;
470  }
471 
472  $changes = array(
473  "change" => $n_fields
474  );
475 //var_dump($n_fields);
476  if (!$this->getTestMode())
477  {
478  $r = $this->manager->alterTable($a_table, $changes, false);
479  }
480  else
481  {
482  $r = $this->manager->createTable(strtolower($a_table)."_copy", $n_fields);
483  }
484 
485  if (MDB2::isError($r))
486  {
487  //$err = "<br>Details: ".mysql_error();
488  var_dump($r);
489  }
490  else
491  {
492  return $r;
493  }
494  }
495 
502  function addPrimaryKey($a_table, $a_pk)
503  {
504  if (is_array($a_pk["fields"]))
505  {
506  $fields = array();
507  foreach ($a_pk["fields"] as $f => $pos)
508  {
509  $fields[] = strtolower($f);
510  }
511  $this->il_db->addPrimaryKey($a_table, $fields);
512  }
513  }
514 
521  function addIndices($a_table, $a_indices)
522  {
523  if (is_array($a_indices))
524  {
525  $all_valid = true;
526 
527  foreach ($a_indices as $index)
528  {
529  if (strlen($index["name"]) > 3)
530  {
531  $all_valid = false;
532  }
533  }
534 
535  $cnt = 1;
536  foreach ($a_indices as $index)
537  {
538  if (is_array($index["fields"]))
539  {
540  if (!$all_valid)
541  {
542  $index["name"] = "i".$cnt;
543  }
544  $fields = array();
545  foreach ($index["fields"] as $f => $pos)
546  {
547  $fields[] = strtolower($f);
548  }
549  $this->il_db->addIndex($a_table, $fields, strtolower($index["name"]), $index["fulltext"]);
550  $cnt++;
551  }
552  }
553  }
554  }
555 
562  function addConstraints($a_table, $a_constraints)
563  {
564  if (is_array($a_constraints))
565  {
566  $all_valid = true;
567 
568  foreach ($a_constraints as $c)
569  {
570  if (strlen($c["name"]) > 3)
571  {
572  $all_valid = false;
573  }
574  }
575 
576  $cnt = 1;
577  foreach ($a_constraints as $c)
578  {
579  if (is_array($c["fields"]))
580  {
581  if (!$all_valid)
582  {
583  $c["name"] = "c".$cnt;
584  }
585  $fields = array();
586  foreach ($c["fields"] as $f => $pos)
587  {
588  $fields[] = strtolower($f);
589  }
590  $this->il_db->addUniqueConstraint($a_table, $fields, strtolower($c["name"]));
591  $cnt++;
592  }
593  }
594  }
595  }
596 
601  function fixIndexNames($a_table)
602  {
603  if (!$this->il_db->tableExists($a_table))
604  {
605  return;
606  }
607  $all_valid = true;
608  $indices = $this->analyzer->getIndicesInformation($a_table);
609  foreach ($indices as $index)
610  {
611  if (strlen($index["name"]) > 3)
612  {
613  $all_valid = false;
614  }
615  }
616 
617  if (!$all_valid)
618  {
619  foreach($indices as $index)
620  {
621  $this->il_db->dropIndex($a_table, $index["name"]);
622  }
623  $this->addIndices($a_table, $indices);
624  }
625  }
626 
633  function addAutoIncrementSequence($a_table, $a_auto_inc_field)
634  {
635  if ($a_auto_inc_field != "")
636  {
637  $set = $this->il_db->query("SELECT MAX(`".strtolower($a_auto_inc_field)."`) ma FROM `".$a_table."`");
638  $rec = $this->il_db->fetchAssoc($set);
639  $next = $rec["ma"] + 1;
640  $this->il_db->createSequence($a_table, $next);
641  }
642  }
643 
648  function fixClobNotNull($a_table)
649  {
650  if (!$this->il_db->tableExists($a_table))
651  {
652  return;
653  }
654  $all_valid = true;
655  $fields = $this->analyzer->getFieldInformation($a_table);
656  foreach ($fields as $name => $def)
657  {
658  if ($def["type"] == "clob" && $def["notnull"] == true)
659  {
660  $this->il_db->modifyTableColumn($a_table, $name, array("type" => "clob", "notnull" => false));
661  }
662  }
663  }
664 
669  function fixDatetimeValues($a_table)
670  {
671  if (!$this->il_db->tableExists($a_table))
672  {
673  return;
674  }
675  $all_valid = true;
676  $fields = $this->analyzer->getFieldInformation($a_table);
677  foreach ($fields as $name => $def)
678  {
679  if ($def["type"] == "timestamp" &&
680  ($def["notnull"] == true || $def["default"] == "0000-00-00 00:00:00"))
681  {
682  $nd = array("type" => "timestamp", "notnull" => false);
683  if ($def["default"] == "0000-00-00 00:00:00")
684  {
685  $nd["default"] = null;
686  }
687  $this->il_db->modifyTableColumn($a_table, $name, $nd);
688  }
689  if ($def["type"] == "date" &&
690  ($def["notnull"] == true || $def["default"] == "0000-00-00"))
691  {
692  $nd = array("type" => "date", "notnull" => false);
693  if ($def["default"] == "0000-00-00")
694  {
695  $nd["default"] = null;
696  }
697  $this->il_db->modifyTableColumn($a_table, $name, $nd);
698  }
699  }
700  }
701 
702 
703 }
704 ?>