ILIAS  release_5-1 Revision 5.0.0-5477-g43f3e3fab5f
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
14class 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 length 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?>
$result
$result
Definition: CleanUpTest.php:407
MDB2\isError
isError($data, $code=null)
Tell whether a value is a MDB2 error.
Definition: MDB2.php:594
ilDBAnalyzer
This class gives all kind of DB information using the MDB2 manager and reverse module.
Definition: class.ilDBAnalyzer.php:14
ilMySQLAbstraction
This class includes methods that help to abstract ILIAS 3.10.x MySQL tables for the use with MDB2 abs...
Definition: class.ilMySQLAbstraction.php:15
ilMySQLAbstraction\lowerCaseColumnNames
lowerCaseColumnNames($a_table_name)
lower case column names
Definition: class.ilMySQLAbstraction.php:304
ilMySQLAbstraction\fixClobNotNull
fixClobNotNull($a_table)
This is only used on tables that have already been abstracted but missed the "full treatment".
Definition: class.ilMySQLAbstraction.php:648
ilMySQLAbstraction\addPrimaryKey
addPrimaryKey($a_table, $a_pk)
Add primary key.
Definition: class.ilMySQLAbstraction.php:502
ilMySQLAbstraction\performAbstraction
performAbstraction($a_table_name, $a_set_text_ts_fields_notnull_false=true)
Converts an existing (MySQL) ILIAS table in an abstract table.
Definition: class.ilMySQLAbstraction.php:67
ilMySQLAbstraction\fixIndexNames
fixIndexNames($a_table)
This is only used on tables that have already been abstracted but missed the "full treatment".
Definition: class.ilMySQLAbstraction.php:601
ilMySQLAbstraction\countRecords
countRecords($a_table_name)
Check number of records before and after.
Definition: class.ilMySQLAbstraction.php:196
ilMySQLAbstraction\replaceEmptyDatesWithNull
replaceEmptyDatesWithNull($a_table)
Replace empty dates with null.
Definition: class.ilMySQLAbstraction.php:243
ilMySQLAbstraction\removeConstraints
removeConstraints($a_table, $a_constraints)
Remove Constraints.
Definition: class.ilMySQLAbstraction.php:370
ilMySQLAbstraction\removeIndices
removeIndices($a_table, $a_indices)
Remove Indices.
Definition: class.ilMySQLAbstraction.php:353
ilMySQLAbstraction\alterTable
alterTable($a_table, $a_fields, $a_set_text_ts_fields_notnull_false=true, $pk="")
Use abstract types as delivered by MDB2 to alter table and make it use only MDB2 known types.
Definition: class.ilMySQLAbstraction.php:391
ilMySQLAbstraction\replaceEmptyStringsWithNull
replaceEmptyStringsWithNull($a_table)
Replace empty strings with null values.
Definition: class.ilMySQLAbstraction.php:220
ilMySQLAbstraction\fixDatetimeValues
fixDatetimeValues($a_table)
This is only used on tables that have already been abstracted but missed the "full treatment".
Definition: class.ilMySQLAbstraction.php:669
ilMySQLAbstraction\lowerCaseTableName
lowerCaseTableName($a_table_name)
Lower case table and field names.
Definition: class.ilMySQLAbstraction.php:288
ilMySQLAbstraction\removePrimaryKey
removePrimaryKey($a_table, $a_pk)
Remove primary key from table.
Definition: class.ilMySQLAbstraction.php:339
ilMySQLAbstraction\setTestMode
setTestMode($a_testmode)
Set Test Mode.
Definition: class.ilMySQLAbstraction.php:45
ilMySQLAbstraction\addConstraints
addConstraints($a_table, $a_constraints)
Add constraints.
Definition: class.ilMySQLAbstraction.php:562
ilMySQLAbstraction\addIndices
addIndices($a_table, $a_indices)
Add indices.
Definition: class.ilMySQLAbstraction.php:521
ilMySQLAbstraction\storeStep
storeStep($a_table, $a_step)
Store performed step.
Definition: class.ilMySQLAbstraction.php:210
ilMySQLAbstraction\removeAutoIncrement
removeAutoIncrement($a_table_name, $a_auto_inc_field)
Remove auto_increment attribute of a field.
Definition: class.ilMySQLAbstraction.php:325
ilMySQLAbstraction\addAutoIncrementSequence
addAutoIncrementSequence($a_table, $a_auto_inc_field)
Add autoincrement sequence.
Definition: class.ilMySQLAbstraction.php:633
$nd
$nd
Definition: error.php:11
$r
$r
Definition: example_031.php:79
$ilDB
global $ilDB
Definition: storeScorm2004.php:19