ILIAS  release_5-1 Revision 5.0.0-5477-g43f3e3fab5f
class.ilDB.php
Go to the documentation of this file.
1<?php
2/* Copyright (c) 1998-2009 ILIAS open source, Extended GPL, see docs/LICENSE */
3
7//pear MDB2 abstraction layer
8include_once ("Services/PEAR/lib/MDB2.php");
9
10define("DB_FETCHMODE_ASSOC", MDB2_FETCHMODE_ASSOC);
11define("DB_FETCHMODE_OBJECT", MDB2_FETCHMODE_OBJECT);
12
13//echo "-".DB_FETCHMODE_ASSOC."-";
14//echo "+".DB_FETCHMODE_OBJECT."+";
15
16
28abstract class ilDB extends PEAR
29{
30 const LOCK_WRITE = 1;
31 const LOCK_READ = 2;
32
33
40
45 var $db;
46
52
53
55 "text" => array("length", "notnull", "default", "fixed"),
56 "integer" => array("length", "notnull", "default", "unsigned"),
57 "float" => array("notnull", "default"),
58 "date" => array("notnull", "default"),
59 "time" => array("notnull", "default"),
60 "timestamp" => array("notnull", "default"),
61 "clob" => array("notnull", "default"),
62 "blob" => array("notnull", "default")
63 );
64
66
74 function ilDB()
75 {
76 }
77
83 function setDBUser($a_user)
84 {
85 $this->db_user = $a_user;
86 }
87
93 function getDBUser()
94 {
95 return $this->db_user;
96 }
97
103 function setDBPort($a_port)
104 {
105 $this->db_port = $a_port;
106 }
107
113 function getDBPort()
114 {
115 return $this->db_port;
116 }
117
123 function setDBHost($a_host)
124 {
125 $this->db_host = $a_host;
126 }
127
133 function getDBHost()
134 {
135 return $this->db_host;
136 }
137
143 function setDBPassword($a_password)
144 {
145 $this->db_password = $a_password;
146 }
147
153 function getDBPassword()
154 {
155 return $this->db_password;
156 }
157
163 function setDBName($a_name)
164 {
165 $this->db_name = $a_name;
166 }
167
173 function getDBName()
174 {
175 return $this->db_name;
176 }
177
181 abstract function getDSN();
182
186 function getDBVersion()
187 {
188 return "Unknown";
189 }
190
194 abstract function getDBType();
195
202 abstract static function getReservedWords();
203
204
209 public function enableResultBuffering($a_status)
210 {
211 $this->db->setOption('result_buffering',$a_status);
212 }
213
218 function initFromIniFile($tmpClientIniFile = null)
219 {
220 global $ilClientIniFile;
221
222 //overwrite global client ini file if local parameter is set
223 if (is_object($tmpClientIniFile))
224 $clientIniFile = $tmpClientIniFile;
225 else
226 $clientIniFile = $ilClientIniFile;
227
228 if (is_object($clientIniFile ))
229 {
230 $this->setDBUser($clientIniFile ->readVariable("db", "user"));
231 $this->setDBHost($clientIniFile ->readVariable("db", "host"));
232 $this->setDBPort($clientIniFile ->readVariable("db", "port"));
233 $this->setDBPassword($clientIniFile ->readVariable("db", "pass"));
234 $this->setDBName($clientIniFile ->readVariable("db", "name"));
235 }
236 }
237
241 function connect($a_return_false_for_error = false)
242 {
243 //set up error handling
244 $this->error_class = new ilErrorHandling();
245 $this->setErrorHandling(PEAR_ERROR_CALLBACK, array($this->error_class,'errorHandler'));
246//echo $this->getDSN();
247 //check dsn
248 if ($this->getDSN() == "")
249 {
250 $this->raisePearError("No DSN given");
251 }
252
253 //connect to database
254 $this->doConnect();
255
256 if ($a_return_false_for_error && MDB2::isError($this->db))
257 {
258 return false;
259 }
260
261 $this->loadMDB2Extensions();
262
263 // set empty value portability to PEAR::DB behaviour
264 if (!$this->isDbError($this->db))
265 {
266 $this->db->setOption('portability', MDB2_PORTABILITY_ALL);
267 }
268 //check error
269 $this->handleError($this->db);
270
271 // anything, that must be done to initialize the connection
272 $this->initConnection();
273
274 return true;
275 }
276
280 function doConnect()
281 {
282 $this->db = MDB2::connect($this->getDSN(),
283 array("use_transactions" => true));
284 }
285
289 function disconnect()
290 {
291 $this->db->disconnect();
292 }
293
294 //
295 // General and MDB2 related functions
296 //
297
301 protected function initConnection()
302 {
303 }
304
311 function getHostDSN()
312 {
313 return false;
314 }
315
320 function connectHost()
321 {
322 //set up error handling
323 $this->error_class = new ilErrorHandling();
324 $this->setErrorHandling(PEAR_ERROR_CALLBACK, array($this->error_class,'errorHandler'));
325
326 //check dsn
327 if ($this->getHostDSN() == "")
328 {
329 $this->raisePearError("No Host DSN given");
330 }
331
332 //connect to database
333 $this->db = MDB2::connect($this->getHostDSN(),
334 array("use_transactions" => true));
335 if ($a_return_false_for_error && MDB2::isError($this->db))
336 {
337 return false;
338 }
339
340 $this->loadMDB2Extensions();
341
342 // set empty value portability to PEAR::DB behaviour
343 if (!$this->isDbError($this->db))
344 {
345 $cur = ($this->db->getOption("portability") & MDB2_PORTABILITY_EMPTY_TO_NULL);
346 $this->db->setOption("portability", $this->db->getOption("portability") - $cur);
347
348 $cur = ($this->db->getOption("portability") & MDB2_PORTABILITY_FIX_CASE);
349 $this->db->setOption("portability", $this->db->getOption("portability") - $cur);
350 }
351
352 //check error
353 $this->handleError($this->db);
354
355 // anything, that must be done to initialize the connection
356 $this->initHostConnection();
357
358 return true;
359 }
360
364 protected function initHostConnection()
365 {
366 }
367
369 {
370 return false;
371 }
372
379 function supportsSlave()
380 {
381 return false;
382 }
383
390 function useSlave($a_val = true)
391 {
392 if (!$this->supportsSlave())
393 {
394 return false;
395 }
396 $this->use_slave = $a_val;
397 }
398
405 function handleError($a_res, $a_info = "", $a_level = "")
406 {
407 global $ilLog;
408
409 if (MDB2::isError($a_res))
410 {
411 if ($a_level == "")
412 {
413 $a_level = $this->error_class->FATAL;
414 }
415
416 // :TODO: ADT (jluetzen)
417
418 // if(!$this->exception)
419 if(true)
420 {
421 // Show stack
422 try
423 {
424 throw new Exception();
425 }
426 catch(Exception $e)
427 {
428 $stack = $e->getTraceAsString();
429 }
430
431 if(is_object($ilLog))
432 $ilLog->logStack();
433 $this->raisePearError("ilDB Error: ".$a_info."<br />".
434 $a_res->getMessage()."<br />".$a_res->getUserInfo()."<br />".$stack, $a_level);
435 }
436 /*
437 else
438 {
439 $error = $this->db->errorInfo($a_res->getCode());
440
441 $mess = $a_info.
442 " ### ".$a_res->getMessage().
443 " ### ".$a_res->getUserInfo();
444
445 $exception = new $this->exception($a_res->getUserInfo(), $error[0]);
446
447 if($exception instanceof ilADTDBException)
448 {
449 // try to find offending column (primary is set AS "PRIMARY")
450 if($error[0] == MDB2_ERROR_CONSTRAINT && $error[1] == 1062)
451 {
452 $col = explode("'", $error[2]);
453 array_pop($col);
454 $col = array_pop($col);
455 $exception->setColumn($col);
456 }
457 }
458
459 throw $exception;
460 }
461 */
462 }
463 /* :TODO: mysql(i) warnings (experimental, jluetzen)
464 else if(DEVMODE && $this instanceof ilDBMySQL)
465 {
466 $j = mysqli_warning_count($this->db->connection);
467 if($j > 0)
468 {
469 $e = mysqli_get_warnings($this->db->connection);
470 for($i = 0; $i < $j; $i++)
471 {
472 trigger_error("MYSQLi warning: "."(".$e->errno.") ".$e->message, E_USER_NOTICE);
473 $e->next();
474 }
475 }
476 }
477 */
478
479 return $a_res;
480 }
481
485 function raisePearError($a_message, $a_level = "")
486 {
487 if ($a_level == "")
488 {
489 $a_level = $this->error_class->FATAL;
490 }
491//echo "<br>-ilDB:raising-$a_message-$a_level-";
492 $this->raiseError($a_message, $a_level);
493 }
494
500 protected function loadMDB2Extensions()
501 {
502 if (!$this->isDbError($this->db))
503 {
504 $this->db->loadModule('Extended');
505 define('DB_AUTOQUERY_SELECT',MDB2_AUTOQUERY_SELECT);
506 define('DB_AUTOQUERY_INSERT',MDB2_AUTOQUERY_INSERT);
507 define('DB_AUTOQUERY_UPDATE',MDB2_AUTOQUERY_UPDATE);
508 define('DB_AUTOQUERY_DELETE',MDB2_AUTOQUERY_DELETE);
509 }
510 }
511
515 static function isDbError($a_res)
516 {
517 return MDB2::isError($a_res);
518 }
519
520 //
521 // Data Definition Methods
522 //
523
527 function createDatabase($a_name, $a_charset = "utf8", $a_collation = "")
528 {
529 if ($a_collation != "")
530 {
531 $sql = "CREATE DATABASE ".$a_name.
532 " CHARACTER SET ".$a_charset.
533 " COLLATE ".$a_collation;
534 }
535 else
536 {
537 $sql = "CREATE DATABASE ".$a_name.
538 " CHARACTER SET ".$a_charset;
539 }
540
541 return $this->query($sql, false);
542 }
543
544
552 function createTable($a_name, $a_definition_array, $a_drop_table = false,
553 $a_ignore_erros = false)
554 {
555 // check table name
556 if (!$this->checkTableName($a_name) && !$a_ignore_erros)
557 {
558 $this->raisePearError("ilDB Error: createTable(".$a_name.")<br />".
559 $this->error_str);
560 }
561
562 // check definition array
563 if (!$this->checkTableColumns($a_definition_array) && !$a_ignore_erros)
564 {
565 $this->raisePearError("ilDB Error: createTable(".$a_name.")<br />".
566 $this->error_str);
567 }
568
569 if ($a_drop_table)
570 {
571 $this->dropTable($a_name, false);
572 }
573
575
576 $manager = $this->db->loadModule('Manager');
577 $r = $manager->createTable($a_name, $a_definition_array, $options);
578
579 return $this->handleError($r, "createTable(".$a_name.")");
580 }
581
587 protected function getCreateTableOptions()
588 {
589 return array();
590 }
591
598 function dropTable($a_name, $a_error_if_not_existing = true)
599 {
600 if (!$a_error_if_not_existing)
601 {
602 $tables = $this->listTables();
603 if (!in_array($a_name, $tables))
604 {
605 return;
606 }
607 }
608
609 $manager = $this->db->loadModule('Manager');
610
611 if ($this->getDBType() == "oracle")
612 {
613 // drop table constraints
614 $constraints = $manager->listTableConstraints($a_name);
615 $this->handleError($constraints, "dropTable(".$a_name."), listTableConstraints");
616 foreach ($constraints as $c)
617 {
618 if (substr($c, 0, 4) != "sys_")
619 {
620 $r = $manager->dropConstraint($a_name, $c);
621 $this->handleError($r, "dropTable(".$a_name."), dropConstraint");
622 }
623 }
624
625 // drop table indexes
626 $indexes = $manager->listTableIndexes($a_name);
627 $this->handleError($indexes, "dropTable(".$a_name."), listTableIndexes");
628 foreach ($indexes as $i)
629 {
630 $r = $manager->dropIndex($a_name, $i);
631 $this->handleError($r, "dropTable(".$a_name."), dropIndex");
632 }
633 }
634
635 // drop sequence
636 $seqs = $manager->listSequences();
637 if (in_array($a_name, $seqs))
638 {
639 $r = $manager->dropSequence($a_name);
640 $this->handleError($r, "dropTable(".$a_name."), dropSequence");
641 }
642
643 // drop table
644 $r = $manager->dropTable($a_name);
645
646 return $this->handleError($r, "dropTable(".$a_name.")");
647 }
648
654 function alterTable($a_name, $a_changes)
655 {
656 if ($a_options == "")
657 {
658 $a_options = array();
659 }
660
661 $manager = $this->db->loadModule('Manager');
662 $r = $manager->alterTable($a_name, $a_changes, false);
663
664 return $this->handleError($r, "alterTable(".$a_name.")");
665 }
666
675 function addTableColumn($a_table, $a_column, $a_attributes)
676 {
677
678 $manager = $this->db->loadModule('Manager');
679
680 if (!$this->checkColumnName($a_column))
681 {
682 $this->raisePearError("ilDB Error: addTableColumn(".$a_table.", ".$a_column.")<br />".
683 $this->error_str);
684 }
685 if (!$this->checkColumnDefinition($a_attributes))
686 {
687 $this->raisePearError("ilDB Error: addTableColumn(".$a_table.", ".$a_column.")<br />".
688 $this->error_str);
689 }
690
691 $changes = array(
692 "add" => array(
693 $a_column => $a_attributes
694 )
695 );
696
697 $r = $manager->alterTable($a_table, $changes, false);
698
699 return $this->handleError($r, "addTableColumn(".$a_table.", ".$a_column.")");
700 }
701
709 function dropTableColumn($a_table, $a_column)
710 {
711
712 $manager = $this->db->loadModule('Manager');
713
714 $changes = array(
715 "remove" => array(
716 $a_column => array()
717 )
718 );
719
720 $r = $manager->alterTable($a_table, $changes, false);
721
722 return $this->handleError($r, "dropTableColumn(".$a_table.", ".$a_column.")");
723 }
724
733 function modifyTableColumn($a_table, $a_column, $a_attributes)
734 {
735 $manager = $this->db->loadModule('Manager');
736 $reverse = $this->db->loadModule('Reverse');
737 $def = $reverse->getTableFieldDefinition($a_table, $a_column);
738
739 $this->handleError($def, "modifyTableColumn(".$a_table.")");
740
741 if (is_file("./Services/Database/classes/class.ilDBAnalyzer.php"))
742 {
743 include_once("./Services/Database/classes/class.ilDBAnalyzer.php");
744 }
745 else
746 {
747 include_once("../Services/Database/classes/class.ilDBAnalyzer.php");
748 }
749 $analyzer = new ilDBAnalyzer();
750 $best_alt = $analyzer->getBestDefinitionAlternative($def);
751 $def = $def[$best_alt];
752 unset($def["nativetype"]);
753 unset($def["mdb2type"]);
754
755 // check attributes
756 $type = ($a_attributes["type"] != "")
757 ? $a_attributes["type"]
758 : $def["type"];
759 foreach ($def as $k => $v)
760 {
761 if ($k != "type" && !in_array($k, $this->allowed_attributes[$type]))
762 {
763 unset($def[$k]);
764 }
765 }
766 $check_array = $def;
767 foreach ($a_attributes as $k => $v)
768 {
769 $check_array[$k] = $v;
770 }
771 if (!$this->checkColumnDefinition($check_array, true))
772 {
773 $this->raisePearError("ilDB Error: modifyTableColumn(".$a_table.", ".$a_column.")<br />".
774 $this->error_str);
775 }
776
777 // oracle workaround: do not set null, if null already given
778 if ($this->getDbType() == "oracle")
779 {
780 if ($def["notnull"] == true && ($a_attributes["notnull"] == true
781 || !isset($a_attributes["notnull"])))
782 {
783 unset($def["notnull"]);
784 unset($a_attributes["notnull"]);
785 }
786 if ($def["notnull"] == false && ($a_attributes["notnull"] == false
787 || !isset($a_attributes["notnull"])))
788 {
789 unset($def["notnull"]);
790 unset($a_attributes["notnull"]);
791 }
792 }
793 foreach ($a_attributes as $a => $v)
794 {
795 $def[$a] = $v;
796 }
797
798 $a_attributes["definition"] = $def;
799
800 $changes = array(
801 "change" => array(
802 $a_column => $a_attributes
803 )
804 );
805
806 $r = $manager->alterTable($a_table, $changes, false);
807
808 return $this->handleError($r, "modifyTableColumn(".$a_table.")");
809 }
810
819 function renameTableColumn($a_table, $a_column, $a_new_column)
820 {
821 // check table name
822 if (!$this->checkColumnName($a_new_column))
823 {
824 $this->raisePearError("ilDB Error: renameTableColumn(".$a_table.",".$a_column.",".$a_new_column.")<br />".
825 $this->error_str);
826 }
827
828 $manager = $this->db->loadModule('Manager');
829 $reverse = $this->db->loadModule('Reverse');
830 $def = $reverse->getTableFieldDefinition($a_table, $a_column);
831
832 $this->handleError($def, "renameTableColumn(".$a_table.",".$a_column.",".$a_new_column.")");
833
834 if (is_file("./Services/Database/classes/class.ilDBAnalyzer.php"))
835 {
836 include_once("./Services/Database/classes/class.ilDBAnalyzer.php");
837 }
838 else
839 {
840 include_once("../Services/Database/classes/class.ilDBAnalyzer.php");
841 }
842
843 $analyzer = new ilDBAnalyzer();
844 $best_alt = $analyzer->getBestDefinitionAlternative($def);
845 $def = $def[$best_alt];
846 unset($def["nativetype"]);
847 unset($def["mdb2type"]);
848
849 $f["definition"] = $def;
850 $f["name"] = $a_new_column;
851
852 $changes = array(
853 "rename" => array(
854 $a_column => $f
855 )
856 );
857
858 $r = $manager->alterTable($a_table, $changes, false);
859
860 return $this->handleError($r, "renameTableColumn(".$a_table.",".$a_column.",".$a_new_column.")");
861 }
862
869 function renameTable($a_name, $a_new_name)
870 {
871 // check table name
872 if (!$this->checkTableName($a_new_name))
873 {
874 $this->raisePearError("ilDB Error: renameTable(".$a_name.",".$a_new_name.")<br />".
875 $this->error_str);
876 }
877
878 $manager = $this->db->loadModule('Manager');
879 $r = $manager->alterTable($a_name, array("name" => $a_new_name), false);
880
881 $query = "UPDATE abstraction_progress ".
882 "SET table_name = ".$this->db->quote($a_new_name,'text')." ".
883 "WHERE table_name = ".$this->db->quote($a_name,'text');
884 $this->db->query($query);
885
886 return $this->handleError($r, "renameTable(".$a_name.",".$a_new_name.")");
887 }
888
896 function addPrimaryKey($a_table, $a_fields)
897 {
898 $manager = $this->db->loadModule('Manager');
899
900 $fields = array();
901 foreach ($a_fields as $f)
902 {
903 $fields[$f] = array();
904 }
905 $definition = array (
906 'primary' => true,
907 'fields' => $fields
908 );
909 $r = $manager->createConstraint($a_table,
910 $this->constraintName($a_table, $this->getPrimaryKeyIdentifier()), $definition);
911
912 return $this->handleError($r, "addPrimaryKey(".$a_table.")");
913 }
914
919 {
920 return "PRIMARY";
921 }
922
929 function dropPrimaryKey($a_table)
930 {
931 $manager = $this->db->loadModule('Manager');
932
933 $r = $manager->dropConstraint($a_table,
934 $this->constraintName($a_table, $this->getPrimaryKeyIdentifier()), true);
935
936 return $this->handleError($r, "dropPrimaryKey(".$a_table.")");
937 }
938
946 function addIndex($a_table, $a_fields, $a_name = "in", $a_fulltext = false)
947 {
948 $manager = $this->db->loadModule('Manager');
949
950 // check index name
951 if (!$this->checkIndexName($a_name))
952 {
953 $this->raisePearError("ilDB Error: addIndex(".$a_table.",".$a_name.")<br />".
954 $this->error_str);
955 }
956
957 $fields = array();
958 foreach ($a_fields as $f)
959 {
960 $fields[$f] = array();
961 }
962 $definition = array (
963 'fields' => $fields
964 );
965
966 if (!$a_fulltext)
967 {
968 $r = $manager->createIndex($a_table, $this->constraintName($a_table, $a_name), $definition);
969 }
970 else
971 {
972 if ($this->supportsFulltext())
973 {
974 $this->addFulltextIndex($a_table, $a_fields, $a_name);
975 }
976 }
977
978 return $this->handleError($r, "addIndex(".$a_table.")");
979 }
980
984 function addFulltextIndex($a_table, $a_fields, $a_name = "in")
985 {
986 return false;
987 }
988
992 function isFulltextIndex($a_table, $a_name)
993 {
994 return false;
995 }
996
997
1003 public function indexExistsByFields($a_table, $a_fields)
1004 {
1005 $manager = $this->db->loadModule('Manager');
1006 $reverse = $this->db->loadModule('Reverse');
1007 if($manager)
1008 {
1009 foreach($manager->listTableIndexes($a_table) as $idx_name)
1010 {
1011 $def = $reverse->getTableIndexDefinition($a_table,$idx_name);
1012 $idx_fields = array_keys((array) $def['fields']);
1013
1014 if($idx_fields === $a_fields)
1015 {
1016 return true;
1017 }
1018 }
1019 }
1020 return false;
1021 }
1022
1029 public function dropIndexByFields($a_table, $a_fields)
1030 {
1031 $manager = $this->db->loadModule('Manager');
1032 $reverse = $this->db->loadModule('Reverse');
1033 if($manager)
1034 {
1035 foreach($manager->listTableIndexes($a_table) as $idx_name)
1036 {
1037 $def = $reverse->getTableIndexDefinition($a_table,$idx_name);
1038 $idx_fields = array_keys((array) $def['fields']);
1039
1040 if($idx_fields === $a_fields)
1041 {
1042 return $this->dropIndex($a_table, $idx_name);
1043 }
1044 }
1045 }
1046 return false;
1047
1048 }
1049
1057 function dropIndex($a_table, $a_name = "in")
1058 {
1059 $manager = $this->db->loadModule('Manager');
1060
1061 if (!$this->isFulltextIndex($a_table, $a_name))
1062 {
1063 $r = $manager->dropIndex($a_table, $this->constraintName($a_table, $a_name));
1064 }
1065 else
1066 {
1067 $this->dropFulltextIndex($a_table, $a_name);
1068 }
1069
1070 return $this->handleError($r, "dropIndex(".$a_table.")");
1071 }
1072
1080 function addUniqueConstraint($a_table, $a_fields, $a_name = "con")
1081 {
1082 $manager = $this->db->loadModule('Manager');
1083
1084 // check index name
1085 if (!$this->checkIndexName($a_name))
1086 {
1087 $this->raisePearError("ilDB Error: addUniqueConstraint(".$a_table.",".$a_name.")<br />".
1088 $this->error_str);
1089 }
1090
1091 $fields = array();
1092 foreach ($a_fields as $f)
1093 {
1094 $fields[$f] = array();
1095 }
1096 $definition = array (
1097 'unique' => true,
1098 'fields' => $fields
1099 );
1100
1101 $r = $manager->createConstraint($a_table, $this->constraintName($a_table, $a_name), $definition);
1102
1103 return $this->handleError($r, "addUniqueConstraint(".$a_table.")");
1104 }
1105
1113 public function dropUniqueConstraint($a_table, $a_name = "con")
1114 {
1115 $manager = $this->db->loadModule('Manager');
1116
1117 $r = $manager->dropConstraint(
1118 $a_table, $this->constraintName($a_table, $a_name), false
1119 );
1120
1121 return $this->handleError($r, "dropUniqueConstraint(".$a_table.")");
1122 }
1123
1130 public function dropUniqueConstraintByFields($a_table, $a_fields)
1131 {
1132 if (is_file("./Services/Database/classes/class.ilDBAnalyzer.php"))
1133 {
1134 include_once("./Services/Database/classes/class.ilDBAnalyzer.php");
1135 }
1136 else
1137 {
1138 include_once("../Services/Database/classes/class.ilDBAnalyzer.php");
1139 }
1140 $analyzer = new ilDBAnalyzer();
1141 $cons = $analyzer->getConstraintsInformation($a_table);
1142 foreach ($cons as $c)
1143 {
1144 if ($c["type"] == "unique" && count($a_fields) == count($c["fields"]))
1145 {
1146 $all_in = true;
1147 foreach ($a_fields as $f)
1148 {
1149 if (!isset($c["fields"][$f]))
1150 {
1151 $all_in = false;
1152 }
1153 }
1154 if ($all_in)
1155 {
1156 return $this->dropUniqueConstraint($a_table, $c['name']);
1157 }
1158 }
1159 }
1160 return false;
1161 }
1162
1166 function createSequence($a_table_name, $a_start = 1)
1167 {
1168 $manager = $this->db->loadModule('Manager');
1169
1170 $r = $manager->createSequence($a_table_name, $a_start);
1171
1172 return $this->handleError($r, "createSequence(".$a_table_name.")");
1173 }
1174
1175
1179 function dropSequence($a_table_name)
1180 {
1181 $manager = $this->db->loadModule('Manager');
1182
1183 $r = $manager->dropSequence($a_table_name);
1184
1185 return $this->handleError($r, "dropSequence(".$a_table_name.")");
1186 }
1187
1193 function checkTableName($a_name)
1194 {
1195 if (!preg_match ("/^[a-z]+[_a-z0-9]*$/", $a_name))
1196 {
1197 $this->error_str = "Table name must only contain _a-z0-9 and must start with a-z.";
1198 return false;
1199 }
1200
1201 if ($this->isReservedWord($a_name))
1202 {
1203 $this->error_str = "Invalid table name '".$a_name."' (Reserved Word).";
1204 return false;
1205 }
1206
1207 if (strtolower(substr($a_name, 0, 4)) == "sys_")
1208 {
1209 $this->error_str = "Invalid table name '".$a_name."'. Name must not start with 'sys_'.";
1210 return false;
1211 }
1212
1213 if (strlen($a_name) > 22)
1214 {
1215 $this->error_str = "Invalid table name '".$a_name."'. Maximum table identifer length is 22 bytes.";
1216 return false;
1217 }
1218
1219 return true;
1220 }
1221
1227 function checkTableColumns($a_cols)
1228 {
1229 foreach ($a_cols as $col => $def)
1230 {
1231 if (!$this->checkColumn($col, $def))
1232 {
1233 return false;
1234 }
1235 }
1236
1237 return true;
1238 }
1239
1243 function checkColumn($a_col, $a_def)
1244 {
1245 if (!$this->checkColumnName($a_col))
1246 {
1247 return false;
1248 }
1249
1250 if (!$this->checkColumnDefinition($a_def))
1251 {
1252 return false;
1253 }
1254
1255 return true;
1256 }
1257
1263 function checkColumnDefinition($a_def, $a_modify_mode = false)
1264 {
1265 // check valid type
1266 if (!in_array($a_def["type"], array("text", "integer", "float", "date", "time", "timestamp", "clob", "blob")))
1267 {
1268 switch ($a_def["type"])
1269 {
1270 case "boolean":
1271 $this->error_str = "Invalid column type '".$a_def["type"]."'. Use integer(1) instead.";
1272 break;
1273
1274 case "decimal":
1275 $this->error_str = "Invalid column type '".$a_def["type"]."'. Use float or integer instead.";
1276 break;
1277
1278 default:
1279 $this->error_str = "Invalid column type '".$a_def["type"]."'. Allowed types are: ".
1280 "text, integer, float, date, time, timestamp, clob and blob.";
1281 }
1282 }
1283
1284 // check used attributes
1286
1287 foreach ($a_def as $k => $v)
1288 {
1289 if ($k != "type" && !in_array($k, $allowed_attributes[$a_def["type"]]))
1290 {
1291 $this->error_str = "Attribute '".$k."' is not allowed for column type '".$a_def["type"]."'.";
1292 return false;
1293 }
1294 }
1295
1296 // type specific checks
1297 switch ($a_def["type"])
1298 {
1299 case "text":
1300 if ($a_def["length"] < 1 || $a_def["length"] > 4000)
1301 {
1302 if (!$a_modify_mode || isset($a_def["length"]))
1303 {
1304 $this->error_str = "Invalid length '".$a_def["length"]."' for type text.".
1305 " Length must be >=1 and <= 4000.";
1306 return false;
1307 }
1308 }
1309 break;
1310
1311 case "integer":
1312 if (!in_array($a_def["length"], array(1, 2, 3, 4, 8)))
1313 {
1314 if (!$a_modify_mode || isset($a_def["length"]))
1315 {
1316 $this->error_str = "Invalid length '".$a_def["length"]."' for type integer.".
1317 " Length must be 1, 2, 3, 4 or 8 (bytes).";
1318 return false;
1319 }
1320 }
1321 if ($a_def["unsigned"])
1322 {
1323 $this->error_str = "Unsigned attribut must not be true for type integer.";
1324 return false;
1325 }
1326 break;
1327 }
1328
1329 return true;
1330 }
1331
1337 function checkColumnName($a_name)
1338 {
1339 if (!preg_match ("/^[a-z]+[_a-z0-9]*$/", $a_name))
1340 {
1341 $this->error_str = "Invalid column name '".$a_name."'. Column name must only contain _a-z0-9 and must start with a-z.";
1342 return false;
1343 }
1344
1345 if ($this->isReservedWord($a_name))
1346 {
1347 $this->error_str = "Invalid column name '".$a_name."' (Reserved Word).";
1348 return false;
1349 }
1350
1351 if (strtolower(substr($a_name, 0, 4)) == "sys_")
1352 {
1353 $this->error_str = "Invalid column name '".$a_name."'. Name must not start with 'sys_'.";
1354 return false;
1355 }
1356
1357 if (strlen($a_name) > 30)
1358 {
1359 $this->error_str = "Invalid column name '".$a_name."'. Maximum column identifer length is 30 bytes.";
1360 return false;
1361 }
1362
1363 return true;
1364 }
1365
1371 function checkIndexName($a_name)
1372 {
1373 if (!preg_match ("/^[a-z]+[_a-z0-9]*$/", $a_name))
1374 {
1375 $this->error_str = "Invalid column name '".$a_name."'. Column name must only contain _a-z0-9 and must start with a-z.";
1376 return false;
1377 }
1378
1379 if ($this->isReservedWord($a_name))
1380 {
1381 $this->error_str = "Invalid column name '".$a_name."' (Reserved Word).";
1382 return false;
1383 }
1384
1385 if (strlen($a_name) > 3)
1386 {
1387 $this->error_str = "Invalid index name '".$a_name."'. Maximum index identifer length is 3 bytes.";
1388 return false;
1389 }
1390
1391 return true;
1392 }
1393
1395 {
1397 }
1398
1404 function constraintName($a_table, $a_constraint)
1405 {
1406 return $a_constraint;
1407 }
1408
1413 static function isReservedWord($a_word)
1414 {
1415 include_once("./Services/Database/classes/class.ilDBMySQL.php");
1416 $mysql_reserved_words = ilDBMySQL::getReservedWords();
1417 if (in_array(strtoupper($a_word), $mysql_reserved_words))
1418 {
1419 return true;
1420 }
1421 include_once("./Services/Database/classes/class.ilDBOracle.php");
1422 $oracle_reserved_words = ilDBOracle::getReservedWords();
1423 if (in_array(strtoupper($a_word), $oracle_reserved_words))
1424 {
1425 return true;
1426 }
1427 include_once("./Services/Database/classes/class.ilDBPostgreSQL.php");
1428 $postgres_reserved_words = ilDBPostgreSQL::getReservedWords();
1429 if (in_array(strtoupper($a_word), $postgres_reserved_words))
1430 {
1431 return true;
1432 }
1433 }
1434
1435 //
1436 // Data query and manupilation functions
1437 //
1438
1450 function query($sql, $a_handle_error = true)
1451 {
1452 global $ilBench;
1453
1454 if (is_object($ilBench))
1455 {
1456 $ilBench->startDbBench($sql);
1457 }
1458 $r = $this->db->query($sql);
1459 if (is_object($ilBench))
1460 {
1461 $ilBench->stopDbBench();
1462 }
1463
1464 if ($a_handle_error)
1465 {
1466 return $this->handleError($r, "query(".$sql.")");
1467 }
1468
1469 return $r;
1470 }
1471
1479 function queryF($a_query, $a_types, $a_values)
1480 {
1481 if (!is_array($a_types) || !is_array($a_values) ||
1482 count($a_types) != count($a_values))
1483 {
1484 $this->raisePearError("ilDB::queryF: Types and values must be arrays of same size. ($a_query)");
1485 }
1486 $quoted_values = array();
1487 foreach($a_types as $k => $t)
1488 {
1489 $quoted_values[] = $this->quote($a_values[$k], $t);
1490 }
1491 $query = vsprintf($a_query, $quoted_values);
1492
1493 return $this->query($query);
1494 }
1495
1503 function manipulateF($a_query, $a_types, $a_values)
1504 {
1505 if (!is_array($a_types) || !is_array($a_values) ||
1506 count($a_types) != count($a_values))
1507 {
1508 $this->raisePearError("ilDB::manipulateF: types and values must be arrays of same size. ($a_query)");
1509 }
1510 $quoted_values = array();
1511 foreach($a_types as $k => $t)
1512 {
1513 $quoted_values[] = $this->quote($a_values[$k], $t);
1514 }
1515 $query = vsprintf($a_query, $quoted_values);
1516
1517 return $this->manipulate($query);
1518 }
1519
1523 function logStatement($sql)
1524 {
1525 $pos1 = strpos(strtolower($sql), "from ");
1526 $table = "";
1527 if ($pos1 > 0)
1528 {
1529 $tablef = substr($sql, $pos1+5);
1530 $pos2 = strpos(strtolower($tablef), " ");
1531 if ($pos2 > 0)
1532 {
1533 $table =substr($tablef, 0, $pos2);
1534 }
1535 else
1536 {
1537 $table = $tablef;
1538 }
1539 }
1540 if (trim($table) != "")
1541 {
1542 if (!is_array($this->ttt) || !in_array($table, $this->ttt))
1543 {
1544 echo "<br>".$table;
1545 $this->ttt[] = $table;
1546 }
1547 }
1548 else
1549 {
1550 echo "<br><b>".$sql."</b>";
1551 }
1552 }
1553
1557 function setLimit($a_limit, $a_offset = 0)
1558 {
1559 $this->db->setLimit($a_limit, $a_offset);
1560 }
1561
1565 function nextId($a_table_name)
1566 {
1567 // we do not create missing sequences automatically here
1568 // otherwise misspelled statements result in additional tables
1569 // please create sequences explicitly in the db update script
1570 $r = $this->db->nextId($a_table_name, false);
1571
1572 return $this->handleError($r, "nextId(".$a_table_name.")");
1573 }
1574
1585 function manipulate($sql)
1586 {
1587 global $ilBench;
1588
1589 if (is_object($ilBench))
1590 {
1591 $ilBench->startDbBench($sql);
1592 }
1593 $r = $this->db->exec($sql);
1594 if (is_object($ilBench))
1595 {
1596 $ilBench->stopDbBench();
1597 }
1598
1599 return $this->handleError($r, "manipulate(".$sql.")");
1600 }
1601
1610 function prepare($a_query, $a_types = null, $a_result_types = null)
1611 {
1612 $res = $this->db->prepare($a_query, $a_types, $a_result_types);
1613
1614 return $this->handleError($res, "prepare(".$a_query.")");
1615 }
1616
1625 function prepareManip($a_query, $a_types = null)
1626 {
1627 $res = $this->db->prepare($a_query, $a_types, MDB2_PREPARE_MANIP);
1628
1629 return $this->handleError($res, "prepareManip(".$a_query.")");
1630 }
1631
1640 function execute($a_stmt, $a_data = null)
1641 {
1642 $res = $a_stmt->execute($a_data);
1643
1644 return $this->handleError($res, "execute(".$a_stmt->query.")");
1645 }
1646
1656 function executeMultiple($a_stmt, $a_data)
1657 {
1658 $res = $this->db->extended->executeMultiple($a_stmt,$a_data);
1659
1660 return $this->handleError($res, "executeMultiple(".$a_stmt->query.")");
1661 }
1662
1669 function insert($a_table, $a_columns)
1670 {
1671 $fields = array();
1672 $field_values = array();
1673 $placeholders = array();
1674 $types = array();
1675 $values = array();
1676 $lobs = false;
1677 $lob = array();
1678 foreach ($a_columns as $k => $col)
1679 {
1680 $fields[] = $k;
1681 $placeholders[] = "%s";
1682 $placeholders2[] = ":$k";
1683 $types[] = $col[0];
1684
1685 // integer auto-typecast (this casts bool values to integer)
1686 if ($col[0] == 'integer' && !is_null($col[1]))
1687 {
1688 $col[1] = (int) $col[1];
1689 }
1690
1691 $values[] = $col[1];
1692 $field_values[$k] = $col[1];
1693 if ($col[0] == "blob" || $col[0] == "clob")
1694 {
1695 $lobs = true;
1696 $lob[$k] = $k;
1697 }
1698 }
1699 if ($lobs) // lobs -> use prepare execute (autoexecute broken in PEAR 2.4.1)
1700 {
1701 $st = $this->db->prepare("INSERT INTO ".$a_table." (".implode($fields,",").") VALUES (".
1702 implode($placeholders2,",").")", $types, MDB2_PREPARE_MANIP, $lob);
1703
1704 $this->handleError($st, "insert / prepare/execute(".$a_table.")");
1705
1706 $r = $st->execute($field_values);
1707
1708
1709 //$r = $this->db->extended->autoExecute($a_table, $field_values, MDB2_AUTOQUERY_INSERT, null, $types);
1710 $this->handleError($r, "insert / prepare/execute(".$a_table.")");
1711 $this->free($st);
1712 }
1713 else // if no lobs are used, take simple manipulateF
1714 {
1715 $q = "INSERT INTO ".$a_table." (".implode($fields,",").") VALUES (".
1716 implode($placeholders,",").")";
1717 $r = $this->manipulateF($q, $types, $values);
1718 }
1719 return $r;
1720 }
1721
1730 function update($a_table, $a_columns, $a_where)
1731 {
1732 $fields = array();
1733 $field_values = array();
1734 $placeholders = array();
1735 $types = array();
1736 $values = array();
1737 $lobs = false;
1738 $lob = array();
1739 foreach ($a_columns as $k => $col)
1740 {
1741 $fields[] = $k;
1742 $placeholders[] = "%s";
1743 $placeholders2[] = ":$k";
1744 $types[] = $col[0];
1745
1746 // integer auto-typecast (this casts bool values to integer)
1747 if ($col[0] == 'integer' && !is_null($col[1]))
1748 {
1749 $col[1] = (int) $col[1];
1750 }
1751
1752 $values[] = $col[1];
1753 $field_values[$k] = $col[1];
1754 if ($col[0] == "blob" || $col[0] == "clob")
1755 {
1756 $lobs = true;
1757 $lob[$k] = $k;
1758 }
1759 }
1760
1761 if ($lobs)
1762 {
1763 $q = "UPDATE ".$a_table." SET ";
1764 $lim = "";
1765 foreach ($fields as $k => $field)
1766 {
1767 $q.= $lim.$field." = ".$placeholders2[$k];
1768 $lim = ", ";
1769 }
1770 $q.= " WHERE ";
1771 $lim = "";
1772 foreach ($a_where as $k => $col)
1773 {
1774 $q.= $lim.$k." = ".$this->quote($col[1], $col[0]);
1775 $lim = " AND ";
1776 }
1777 $st = $this->db->prepare($q, $types, MDB2_PREPARE_MANIP, $lob);
1778 $r = $st->execute($field_values);
1779
1780 //$r = $this->db->extended->autoExecute($a_table, $field_values, MDB2_AUTOQUERY_INSERT, null, $types);
1781 $this->handleError($r, "update / prepare/execute(".$a_table.")");
1782 $this->free($st);
1783 }
1784 else
1785 {
1786 foreach ($a_where as $k => $col)
1787 {
1788 $types[] = $col[0];
1789 $values[] = $col[1];
1790 $field_values[$k] = $col;
1791 }
1792 $q = "UPDATE ".$a_table." SET ";
1793 $lim = "";
1794 foreach ($fields as $k => $field)
1795 {
1796 $q.= $lim.$field." = ".$placeholders[$k];
1797 $lim = ", ";
1798 }
1799 $q.= " WHERE ";
1800 $lim = "";
1801 foreach ($a_where as $k => $col)
1802 {
1803 $q.= $lim.$k." = %s";
1804 $lim = " AND ";
1805 }
1806
1807 $r = $this->manipulateF($q, $types, $values);
1808 }
1809 return $r;
1810 }
1811
1819 function replace($a_table, $a_pk_columns, $a_other_columns)
1820 {
1821 // this is the mysql implementation
1822 $a_columns = array_merge($a_pk_columns, $a_other_columns);
1823 $fields = array();
1824 $field_values = array();
1825 $placeholders = array();
1826 $types = array();
1827 $values = array();
1828 $lobs = false;
1829 $lob = array();
1830 foreach ($a_columns as $k => $col)
1831 {
1832 $fields[] = $k;
1833 $placeholders[] = "%s";
1834 $placeholders2[] = ":$k";
1835 $types[] = $col[0];
1836
1837 // integer auto-typecast (this casts bool values to integer)
1838 if ($col[0] == 'integer' && !is_null($col[1]))
1839 {
1840 $col[1] = (int) $col[1];
1841 }
1842
1843 $values[] = $col[1];
1844 $field_values[$k] = $col[1];
1845 if ($col[0] == "blob" || $col[0] == "clob")
1846 {
1847 $lobs = true;
1848 $lob[$k] = $k;
1849 }
1850 }
1851 if ($lobs) // lobs -> use prepare execute (autoexecute broken in PEAR 2.4.1)
1852 {
1853 $st = $this->db->prepare("REPLACE INTO ".$a_table." (".implode($fields,",").") VALUES (".
1854 implode($placeholders2,",").")", $types, MDB2_PREPARE_MANIP, $lob);
1855 $this->handleError($st, "insert / prepare/execute(".$a_table.")");
1856 $r = $st->execute($field_values);
1857 //$r = $this->db->extended->autoExecute($a_table, $field_values, MDB2_AUTOQUERY_INSERT, null, $types);
1858 $this->handleError($r, "insert / prepare/execute(".$a_table.")");
1859 $this->free($st);
1860 }
1861 else // if no lobs are used, take simple manipulateF
1862 {
1863 $q = "REPLACE INTO ".$a_table." (".implode($fields,",").") VALUES (".
1864 implode($placeholders,",").")";
1865 $r = $this->manipulateF($q, $types, $values);
1866 }
1867 return $r;
1868 }
1869
1875 function fetchAssoc($a_set)
1876 {
1877 return $a_set->fetchRow(DB_FETCHMODE_ASSOC);
1878 }
1879
1883 function free($a_st)
1884 {
1885 return $a_st->free();
1886 }
1887
1893 function fetchObject($a_set)
1894 {
1895 return $a_set->fetchRow(DB_FETCHMODE_OBJECT);
1896 }
1897
1903 function numRows($a_set)
1904 {
1905 return $a_set->numRows();
1906 }
1907
1908 //
1909 // function and clauses abstraction
1910 //
1911
1923 function in($a_field, $a_values, $negate = false, $a_type = "")
1924 {
1925 if (count($a_values) == 0)
1926 {
1927 // BEGIN fixed mantis #0014191:
1928 //return " 1=2 "; // return a false statement on empty array
1929 return $negate ? ' 1=1 ' : ' 1=2 ';
1930 // END fixed mantis #0014191:
1931 }
1932 if ($a_type == "") // untyped: used ? for prepare/execute
1933 {
1934 $str = $a_field.(($negate) ? " NOT" : "")." IN (?".str_repeat(",?", count($a_values) - 1).")";
1935 }
1936 else // typed, use values for query/manipulate
1937 {
1938 $str = $a_field.(($negate) ? " NOT" : "")." IN (";
1939 $sep = "";
1940 foreach ($a_values as $v)
1941 {
1942 $str.= $sep.$this->quote($v, $a_type);
1943 $sep = ",";
1944 }
1945 $str.= ")";
1946 }
1947
1948 return $str;
1949 }
1950
1954 function addTypesToArray($a_arr, $a_type, $a_cnt)
1955 {
1956 if (!is_array($a_arr))
1957 {
1958 $a_arr = array();
1959 }
1960 if ($a_cnt > 0)
1961 {
1962 $type_arr = array_fill(0, $a_cnt, $a_type);
1963 }
1964 else
1965 {
1966 $type_arr = array();
1967 }
1968 return array_merge($a_arr, $type_arr);
1969 }
1970
1975 function now()
1976 {
1977 return "now()";
1978 }
1979
1980
1990 public function concat($a_values,$a_allow_null = true)
1991 {
1992 if(!count($a_values))
1993 {
1994 return ' ';
1995 }
1996
1997 $concat = ' CONCAT(';
1998 $first = true;
1999 foreach($a_values as $field_info)
2000 {
2001 $val = $field_info[0];
2002
2003 if(!$first)
2004 {
2005 $concat .= ',';
2006 }
2007
2008 if($a_allow_null)
2009 {
2010 $concat .= 'COALESCE(';
2011 }
2012 $concat .= $val;
2013
2014 if($a_allow_null)
2015 {
2016 $concat .= ",''";
2017 $concat .= ')';
2018 }
2019
2020 $first = false;
2021 }
2022 $concat .= ') ';
2023 return $concat;
2024 }
2025
2032 function substr($a_exp, $a_pos = 1, $a_len = -1)
2033 {
2034 $lenstr = "";
2035 if ($a_len > -1)
2036 {
2037 $lenstr = ", ".$a_len;
2038 }
2039 return " SUBSTR(".$a_exp.", ".$a_pos.$lenstr.") ";
2040 }
2041
2048 function upper($a_exp)
2049 {
2050 return " UPPER(".$a_exp.") ";
2051 }
2052
2059 function lower($a_exp)
2060 {
2061 return " LOWER(".$a_exp.") ";
2062 }
2063
2071 public function locate($a_needle,$a_string,$a_start_pos = 1)
2072 {
2073 $locate = ' LOCATE( ';
2074 $locate .= $a_needle;
2075 $locate .= ',';
2076 $locate .= $a_string;
2077 $locate .= ',';
2078 $locate .= $a_start_pos;
2079 $locate .= ') ';
2080 return $locate;
2081 }
2082
2083
2089 function like($a_col, $a_type, $a_value = "?", $case_insensitive = true)
2090 {
2091 if (!in_array($a_type, array("text", "clob", "blob")))
2092 {
2093 $this->raisePearError("Like: Invalid column type '".$a_type."'.", $this->error_class->FATAL);
2094 }
2095 if ($a_value == "?")
2096 {
2097 if ($case_insensitive)
2098 {
2099 return "UPPER(".$a_col.") LIKE(UPPER(?))";
2100 }
2101 else
2102 {
2103 return $a_col ." LIKE(?)";
2104 }
2105 }
2106 else
2107 {
2108 if ($case_insensitive)
2109 {
2110 // Always quote as text
2111 return " UPPER(".$a_col.") LIKE(UPPER(".$this->quote($a_value, 'text')."))";
2112 }
2113 else
2114 {
2115 // Always quote as text
2116 return " ".$a_col." LIKE(".$this->quote($a_value, 'text').")";
2117 }
2118 }
2119 }
2120
2121
2125 function equals($a_col, $a_value, $a_type, $a_empty_or_null = false)
2126 {
2127 if (!$a_empty_or_null || $a_value != "")
2128 {
2129 return $a_col." = ".$this->quote($a_value, $a_type);
2130 }
2131 else
2132 {
2133 return "(".$a_col." = '' OR $a_col IS NULL)";
2134 }
2135 }
2136
2140 function equalsNot($a_col, $a_value, $a_type, $a_empty_or_null = false)
2141 {
2142 if (!$a_empty_or_null)
2143 {
2144 return $a_col." <> ".$this->quote($a_value, $a_type);
2145 }
2146 if ($a_value != "")
2147 {
2148 return "(".$a_col." <> ".$this->quote($a_value, $a_type). " OR ".
2149 $a_col." IS NULL)";
2150 }
2151 else
2152 {
2153 return "(".$a_col." <> '' AND $a_col IS NOT NULL)";
2154 }
2155 }
2156
2163 function fromUnixtime($a_expr, $a_to_text = true)
2164 {
2165 return "FROM_UNIXTIME(".$a_expr.")";
2166 }
2167
2171 function unixTimestamp()
2172 {
2173 return "UNIX_TIMESTAMP()";
2174 }
2175
2179 function optimizeTable($a_table)
2180 {
2181 // needs to be overwritten in DBMS specific class
2182 // if necessary and possible
2183 }
2184
2185 //
2186 // Schema related functions
2187 //
2188
2195 function tableExists($a_table)
2196 {
2197 $tables = $this->listTables();
2198
2199 if (is_array($tables))
2200 {
2201 if (in_array($a_table, $tables))
2202 {
2203 return true;
2204 }
2205 }
2206 return false;
2207 }
2208
2216 function tableColumnExists($a_table, $a_column_name)
2217 {
2218
2219 $column_visibility = false;
2220 $manager = $this->db->loadModule('Manager');
2221 $r = $manager->listTableFields($a_table);
2222
2223 if (!MDB2::isError($r))
2224 {
2225 foreach($r as $field)
2226 {
2227 if ($field == $a_column_name)
2228 {
2229 $column_visibility = true;
2230 }
2231 }
2232 }
2233
2234 return $column_visibility;
2235 }
2236
2244 function uniqueConstraintExists($a_table, $a_fields)
2245 {
2246 if (is_file("./Services/Database/classes/class.ilDBAnalyzer.php"))
2247 {
2248 include_once("./Services/Database/classes/class.ilDBAnalyzer.php");
2249 }
2250 else
2251 {
2252 include_once("../Services/Database/classes/class.ilDBAnalyzer.php");
2253 }
2254 $analyzer = new ilDBAnalyzer();
2255 $cons = $analyzer->getConstraintsInformation($a_table);
2256 foreach ($cons as $c)
2257 {
2258 if ($c["type"] == "unique" && count($a_fields) == count($c["fields"]))
2259 {
2260 $all_in = true;
2261 foreach ($a_fields as $f)
2262 {
2263 if (!isset($c["fields"][$f]))
2264 {
2265 $all_in = false;
2266 }
2267 }
2268 if ($all_in)
2269 {
2270 return true;
2271 }
2272 }
2273 }
2274 return false;
2275 }
2276
2277
2283 function listTables()
2284 {
2285 $manager = $this->db->loadModule('Manager');
2286 $r = $manager->listTables();
2287
2288 if (!MDB2::isError($r))
2289 {
2290 return $r;
2291 }
2292
2293 return false;
2294 }
2295
2302 function sequenceExists($a_sequence)
2303 {
2304 $sequences = $this->listSequences();
2305
2306 if (is_array($sequences))
2307 {
2308 if (in_array($a_sequence, $sequences))
2309 {
2310 return true;
2311 }
2312 }
2313 return false;
2314 }
2315
2321 function listSequences()
2322 {
2323 $manager = $this->db->loadModule('Manager');
2324 $r = $manager->listSequences();
2325
2326 if (!MDB2::isError($r))
2327 {
2328 return $r;
2329 }
2330
2331 return false;
2332 }
2333
2334
2335 //
2336 // Quote Functions
2337 //
2338
2342 function quote($a_query, $a_type = null)
2343 {
2344 if ($a_query == "" && is_null($a_type))
2345 {
2346 $a_query = "";
2347 }
2348
2349 // Performance fix
2350 if($a_type == 'integer' && !is_null($a_query))
2351 {
2352 return (int) $a_query;
2353 }
2354
2355 if ($a_type == "blob" || $a_type == "clob")
2356 {
2357 $this->raisePearError("ilDB::quote: Quoting not allowed on type '".$a_type."'. Please use ilDB->insert and ilDB->update to write clobs.", $this->error_class->FATAL);
2358 }
2359
2360 return $this->db->quote($a_query, $a_type);
2361 }
2362
2370 function quoteIdentifier($a_identifier)
2371 {
2372 return $this->db->quoteIdentifier($a_identifier);
2373 }
2374
2375
2376 //
2377 // Transaction and Locking methods
2378 //
2379
2386 {
2387 if (!$this->db->supports('transactions'))
2388 {
2389 $this->raisePearError("ilDB::beginTransaction: Transactions are not supported.", $this->error_class->FATAL);
2390 }
2391 $res = $this->db->beginTransaction();
2392
2393 return $this->handleError($res, "beginTransaction()");
2394 }
2395
2399 function commit()
2400 {
2401 $res = $this->db->commit();
2402
2403 return $this->handleError($res, "commit()");
2404 }
2405
2409 function rollback()
2410 {
2411 $res = $this->db->rollback();
2412
2413 return $this->handleError($res, "rollback()");
2414 }
2415
2421 abstract public function lockTables($a_tables);
2422
2427 abstract public function unlockTables();
2428
2429
2430//
2431//
2432// Older functions. Must be checked.
2433//
2434//
2435
2444 function autoExecute($a_tablename,$a_fields,$a_mode = MDB2_AUTOQUERY_INSERT,$a_where = false)
2445 {
2446 $res = $this->db->autoExecute($a_tablename,$a_fields,$a_mode,$a_where);
2447
2448 return $this->handleError($res, "autoExecute(".$a_tablename.")");
2449 }
2450
2451//
2452//
2453// Deprecated functions.
2454//
2455//
2456
2461 {
2462 $res = $this->db->lastInsertId();
2463 if(MDB2::isError($res))
2464 {
2465 return false;
2466 }
2467 return $res;
2468 }
2469
2479 function getOne($sql)
2480 {
2481 //$r = $this->db->getOne($sql);
2482 $set = $this->db->query($sql);
2483
2484 $this->handleError($set, "getOne(".$sql.")");
2485
2486 if (!MDB2::isError($set))
2487 {
2488 $r = $set->fetchRow(DB_FETCHMODE_ASSOC);
2489
2490 return $r[0];
2491 }
2492 }
2493
2503 function getRow($sql,$mode = DB_FETCHMODE_OBJECT)
2504 {
2505 $set = $this->query($sql);
2506 $r = $set->fetchRow($mode);
2507 //$r = $this->db->getrow($sql,$mode);
2508
2509 $this->handleError($r, "getRow(".$sql.")");
2510
2511 return $r;
2512 } //end function
2513
2519 function setSubType($a_value)
2520 {
2521 $this->sub_type = (string)$a_value;
2522 }
2523
2529 function getSubType()
2530 {
2531 return $this->sub_type;
2532 }
2533
2534} //end Class
2535?>
const MDB2_AUTOQUERY_UPDATE
Definition: Extended.php:57
const MDB2_AUTOQUERY_SELECT
Definition: Extended.php:59
const MDB2_AUTOQUERY_INSERT
Used by autoPrepare()
Definition: Extended.php:56
const MDB2_AUTOQUERY_DELETE
Definition: Extended.php:58
const MDB2_PORTABILITY_ALL
Portability: turn on all portability features.
Definition: MDB2.php:210
const MDB2_PORTABILITY_FIX_CASE
Portability: convert names of tables and fields to case defined in the "field_case" option when using...
Definition: MDB2.php:158
const MDB2_PREPARE_MANIP
These are just helper constants to more verbosely express parameters to prepare()
Definition: MDB2.php:109
const MDB2_PORTABILITY_EMPTY_TO_NULL
Portability: convert empty values to null strings in data output by query*() and fetch*().
Definition: MDB2.php:198
const MDB2_FETCHMODE_OBJECT
Column data as object properties.
Definition: MDB2.php:134
const MDB2_FETCHMODE_ASSOC
Column data indexed by column names.
Definition: MDB2.php:129
const PEAR_ERROR_CALLBACK
Definition: PEAR.php:35
isError($data, $code=null)
Tell whether a value is a MDB2 error.
Definition: MDB2.php:594
& connect($dsn, $options=false)
Create a new MDB2 connection object and connect to the specified database.
Definition: MDB2.php:431
setErrorHandling($mode=null, $options=null)
Sets how errors generated by this object should be handled.
Definition: PEAR.php:335
& raiseError($message=null, $code=null, $mode=null, $options=null, $userinfo=null, $error_class=null, $skipmsg=false)
This method is a wrapper that returns an instance of the configured error class with this object's de...
Definition: PEAR.php:524
const DB_FETCHMODE_ASSOC
Definition: class.ilDB.php:10
const DB_FETCHMODE_OBJECT
Definition: class.ilDB.php:11
readVariable($a_group, $a_var_name)
reads a single variable from a group @access public
This class gives all kind of DB information using the MDB2 manager and reverse module.
static getReservedWords()
Get reserved words.
static getReservedWords()
Get reserved words.
static getReservedWords()
Get reserved words.
Database Wrapper.
Definition: class.ilDB.php:29
replace($a_table, $a_pk_columns, $a_other_columns)
Replace into method.
lockTables($a_tables)
Abstraction of lock table.
listTables()
Get all tables.
lower($a_exp)
Upper.
const LOCK_WRITE
Definition: class.ilDB.php:30
tableColumnExists($a_table, $a_column_name)
Checks for the existence of a table column.
equals($a_col, $a_value, $a_type, $a_empty_or_null=false)
Use this only on text fields.
initFromIniFile($tmpClientIniFile=null)
Init db parameters from ini file.
Definition: class.ilDB.php:218
executeMultiple($a_stmt, $a_data)
Execute a query statement prepared by either prepare() or prepareManip() with multiple data arrays.
manipulate($sql)
Data manipulation.
fetchObject($a_set)
Fetch row as object from result set.
upper($a_exp)
Upper.
setSubType($a_value)
Set sub type.
checkTableColumns($a_cols)
Check table columns definition.
createTable($a_name, $a_definition_array, $a_drop_table=false, $a_ignore_erros=false)
Create a new table in the database.
Definition: class.ilDB.php:552
connectHost()
Sets up a host connection only (no specific database used).
Definition: class.ilDB.php:320
createSequence($a_table_name, $a_start=1)
Create a sequence for a table.
enableResultBuffering($a_status)
En/disable result buffering.
Definition: class.ilDB.php:209
$error_class
Definition: class.ilDB.php:39
addTableColumn($a_table, $a_column, $a_attributes)
Add table column Use this only on aleady "abstracted" tables.
Definition: class.ilDB.php:675
getDBPort()
Get database port.
Definition: class.ilDB.php:113
update($a_table, $a_columns, $a_where)
Convenient method for standard update statements, example field array:
getPrimaryKeyIdentifier()
Primary key identifier.
Definition: class.ilDB.php:918
now()
now()
getLastInsertId()
Get last insert id.
$allowed_attributes
Definition: class.ilDB.php:54
disconnect()
Disconnect.
Definition: class.ilDB.php:289
rollback()
Rollback a transaction.
dropSequence($a_table_name)
Drop a sequence for a table.
connect($a_return_false_for_error=false)
Open the connection.
Definition: class.ilDB.php:241
renameTable($a_name, $a_new_name)
Rename a table.
Definition: class.ilDB.php:869
dropPrimaryKey($a_table)
Drop a primary key from a table.
Definition: class.ilDB.php:929
quoteIdentifier($a_identifier)
Quote table and field names.
tableExists($a_table)
Check, whether a given table exists.
getRow($sql, $mode=DB_FETCHMODE_OBJECT)
getRow.
queryF($a_query, $a_types, $a_values)
Formatted query (for SELECTS).
in($a_field, $a_values, $negate=false, $a_type="")
Get abstract in-clause for given array.
getDSN()
Get DSN.
static isReservedWord($a_word)
Checks whether a word is a reserved word in one of the supported databases.
handleError($a_res, $a_info="", $a_level="")
Handle MDB2 Errors.
Definition: class.ilDB.php:405
nextId($a_table_name)
Get next ID for an index.
modifyTableColumn($a_table, $a_column, $a_attributes)
Modify a table column Use this only on aleady "abstracted" tables.
Definition: class.ilDB.php:733
getDBUser()
Get database user.
Definition: class.ilDB.php:93
uniqueConstraintExists($a_table, $a_fields)
Checks if a unique constraint exists based on the fields of the unique constraint (not the name)
unixTimestamp()
Unix timestamp.
ilDB()
constructor
Definition: class.ilDB.php:74
getDBName()
Get database name.
Definition: class.ilDB.php:173
constraintName($a_table, $a_constraint)
Determine contraint name by table name and constraint name.
concat($a_values, $a_allow_null=true)
Abstraction of SQL function CONCAT.
doConnect()
Standard way to connect to db.
Definition: class.ilDB.php:280
addTypesToArray($a_arr, $a_type, $a_cnt)
Adds a type x times to an array.
manipulateF($a_query, $a_types, $a_values)
Formatted manupulate (for DELETE, UPDATE, INSERT).
free($a_st)
Free a statement / result set.
getDBType()
Get DSN.
checkIndexName($a_name)
Check whether an index name is valid.
setDBUser($a_user)
Set database user.
Definition: class.ilDB.php:83
supportsSlave()
Supports slave.
Definition: class.ilDB.php:379
getDBVersion()
Get DB version.
Definition: class.ilDB.php:186
checkTableName($a_name)
Check whether a table name is valid.
static getReservedWords()
Get reserved words.
createDatabase($a_name, $a_charset="utf8", $a_collation="")
Create database.
Definition: class.ilDB.php:527
dropUniqueConstraint($a_table, $a_name="con")
Drop a constraint from a table.
renameTableColumn($a_table, $a_column, $a_new_column)
Rename a table column Use this only on aleady "abstracted" tables.
Definition: class.ilDB.php:819
like($a_col, $a_type, $a_value="?", $case_insensitive=true)
Like.
numRows($a_set)
Fetch row as associative array from result set.
equalsNot($a_col, $a_value, $a_type, $a_empty_or_null=false)
Use this only on text fields.
setLimit($a_limit, $a_offset=0)
Set limit and offset for a query.
checkColumnDefinition($a_def, $a_modify_mode=false)
Check whether a column definition is valid.
query($sql, $a_handle_error=true)
Query.
insert($a_table, $a_columns)
Convenient method for standard insert statements, example field array:
isFulltextIndex($a_table, $a_name)
Is index a fulltext index?
Definition: class.ilDB.php:992
setDBPort($a_port)
Set database port.
Definition: class.ilDB.php:103
optimizeTable($a_table)
Optimize Table.
initHostConnection()
Initialize the host connection (no specific database)
Definition: class.ilDB.php:364
dropUniqueConstraintByFields($a_table, $a_fields)
Drop constraint by field(s)
getCreateTableOptions()
Get options for the create table statement.
Definition: class.ilDB.php:587
addUniqueConstraint($a_table, $a_fields, $a_name="con")
Add a unique constraint to a table.
logStatement($sql)
Helper function, should usually not be called.
dropTable($a_name, $a_error_if_not_existing=true)
Drop a table.
Definition: class.ilDB.php:598
static isDbError($a_res)
Check error.
Definition: class.ilDB.php:515
alterTable($a_name, $a_changes)
Alter a table in the database This method is DEPRECATED, see http://www.ilias.de/docu/goto....
Definition: class.ilDB.php:654
getAllowedAttributes()
commit()
Commit a transaction.
checkColumnName($a_name)
Check whether a column name is valid.
const LOCK_READ
Definition: class.ilDB.php:31
setDBName($a_name)
Set database name.
Definition: class.ilDB.php:163
checkColumn($a_col, $a_def)
Check column definition.
getSubType()
Get sub type.
dropIndex($a_table, $a_name="in")
Drop an index from a table.
beginTransaction()
Begin Transaction.
raisePearError($a_message, $a_level="")
Raise an error.
Definition: class.ilDB.php:485
addIndex($a_table, $a_fields, $a_name="in", $a_fulltext=false)
Add an index to a table.
Definition: class.ilDB.php:946
getOne($sql)
getOne.
prepare($a_query, $a_types=null, $a_result_types=null)
Prepare a query (SELECT) statement to be used with execute.
setDBHost($a_host)
Set database host.
Definition: class.ilDB.php:123
addPrimaryKey($a_table, $a_fields)
Add a primary key to a table.
Definition: class.ilDB.php:896
execute($a_stmt, $a_data=null)
Execute a query statement prepared by either prepare() or prepareManip()
fetchAssoc($a_set)
Fetch row as associative array from result set.
getDBPassword()
Get database password.
Definition: class.ilDB.php:153
substr($a_exp, $a_pos=1, $a_len=-1)
Substring.
indexExistsByFields($a_table, $a_fields)
Check if index exists.
sequenceExists($a_sequence)
Check, whether a given sequence exists.
fromUnixtime($a_expr, $a_to_text=true)
fromUnixtime (makes timestamp out of unix timestamp)
dropIndexByFields($a_table, $a_fields)
Drop index by field(s)
addFulltextIndex($a_table, $a_fields, $a_name="in")
Add fulltext index.
Definition: class.ilDB.php:984
useSlave($a_val=true)
Use slave.
Definition: class.ilDB.php:390
getHostDSN()
Should return a valid value, if host connections are possible (connectHost) to create a new database ...
Definition: class.ilDB.php:311
unlockTables()
Unlock tables locked by previous lock table calls.
getDBHost()
Get database host.
Definition: class.ilDB.php:133
autoExecute($a_tablename, $a_fields, $a_mode=MDB2_AUTOQUERY_INSERT, $a_where=false)
Wrapper for Pear autoExecute.
listSequences()
Get all sequences.
supportsFulltext()
Definition: class.ilDB.php:368
dropTableColumn($a_table, $a_column)
Drop table column Use this only on aleady "abstracted" tables.
Definition: class.ilDB.php:709
$sub_type
Definition: class.ilDB.php:65
locate($a_needle, $a_string, $a_start_pos=1)
Create locate string.
initConnection()
Initialize the database connection.
Definition: class.ilDB.php:301
setDBPassword($a_password)
Set database password.
Definition: class.ilDB.php:143
prepareManip($a_query, $a_types=null)
Prepare a data manipulation statement to be used with execute.
quote($a_query, $a_type=null)
Wrapper for quote method.
loadMDB2Extensions()
load additional mdb2 extensions and set their constants
Definition: class.ilDB.php:500
$r
Definition: example_031.php:79
global $ilBench
Definition: ilias.php:18
if(!is_array($argv)) $options