ILIAS  release_5-0 Revision 5.0.0-1144-gc4397b1f870
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 ("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
1109 function createSequence($a_table_name, $a_start = 1)
1110 {
1111 $manager = $this->db->loadModule('Manager');
1112
1113 $r = $manager->createSequence($a_table_name, $a_start);
1114
1115 return $this->handleError($r, "createSequence(".$a_table_name.")");
1116 }
1117
1118
1122 function dropSequence($a_table_name)
1123 {
1124 $manager = $this->db->loadModule('Manager');
1125
1126 $r = $manager->dropSequence($a_table_name);
1127
1128 return $this->handleError($r, "dropSequence(".$a_table_name.")");
1129 }
1130
1136 function checkTableName($a_name)
1137 {
1138 if (!preg_match ("/^[a-z]+[_a-z0-9]*$/", $a_name))
1139 {
1140 $this->error_str = "Table name must only contain _a-z0-9 and must start with a-z.";
1141 return false;
1142 }
1143
1144 if ($this->isReservedWord($a_name))
1145 {
1146 $this->error_str = "Invalid table name '".$a_name."' (Reserved Word).";
1147 return false;
1148 }
1149
1150 if (strtolower(substr($a_name, 0, 4)) == "sys_")
1151 {
1152 $this->error_str = "Invalid table name '".$a_name."'. Name must not start with 'sys_'.";
1153 return false;
1154 }
1155
1156 if (strlen($a_name) > 22)
1157 {
1158 $this->error_str = "Invalid table name '".$a_name."'. Maximum table identifer lenght is 22 bytes.";
1159 return false;
1160 }
1161
1162 return true;
1163 }
1164
1170 function checkTableColumns($a_cols)
1171 {
1172 foreach ($a_cols as $col => $def)
1173 {
1174 if (!$this->checkColumn($col, $def))
1175 {
1176 return false;
1177 }
1178 }
1179
1180 return true;
1181 }
1182
1186 function checkColumn($a_col, $a_def)
1187 {
1188 if (!$this->checkColumnName($a_col))
1189 {
1190 return false;
1191 }
1192
1193 if (!$this->checkColumnDefinition($a_def))
1194 {
1195 return false;
1196 }
1197
1198 return true;
1199 }
1200
1206 function checkColumnDefinition($a_def, $a_modify_mode = false)
1207 {
1208 // check valid type
1209 if (!in_array($a_def["type"], array("text", "integer", "float", "date", "time", "timestamp", "clob", "blob")))
1210 {
1211 switch ($a_def["type"])
1212 {
1213 case "boolean":
1214 $this->error_str = "Invalid column type '".$a_def["type"]."'. Use integer(1) instead.";
1215 break;
1216
1217 case "decimal":
1218 $this->error_str = "Invalid column type '".$a_def["type"]."'. Use float or integer instead.";
1219 break;
1220
1221 default:
1222 $this->error_str = "Invalid column type '".$a_def["type"]."'. Allowed types are: ".
1223 "text, integer, float, date, time, timestamp, clob and blob.";
1224 }
1225 }
1226
1227 // check used attributes
1229
1230 foreach ($a_def as $k => $v)
1231 {
1232 if ($k != "type" && !in_array($k, $allowed_attributes[$a_def["type"]]))
1233 {
1234 $this->error_str = "Attribute '".$k."' is not allowed for column type '".$a_def["type"]."'.";
1235 return false;
1236 }
1237 }
1238
1239 // type specific checks
1240 switch ($a_def["type"])
1241 {
1242 case "text":
1243 if ($a_def["length"] < 1 || $a_def["length"] > 4000)
1244 {
1245 if (!$a_modify_mode || isset($a_def["length"]))
1246 {
1247 $this->error_str = "Invalid length '".$a_def["length"]."' for type text.".
1248 " Length must be >=1 and <= 4000.";
1249 return false;
1250 }
1251 }
1252 break;
1253
1254 case "integer":
1255 if (!in_array($a_def["length"], array(1, 2, 3, 4, 8)))
1256 {
1257 if (!$a_modify_mode || isset($a_def["length"]))
1258 {
1259 $this->error_str = "Invalid length '".$a_def["length"]."' for type integer.".
1260 " Length must be 1, 2, 3, 4 or 8 (bytes).";
1261 return false;
1262 }
1263 }
1264 if ($a_def["unsigned"])
1265 {
1266 $this->error_str = "Unsigned attribut must not be true for type integer.";
1267 return false;
1268 }
1269 break;
1270 }
1271
1272 return true;
1273 }
1274
1280 function checkColumnName($a_name)
1281 {
1282 if (!preg_match ("/^[a-z]+[_a-z0-9]*$/", $a_name))
1283 {
1284 $this->error_str = "Invalid column name '".$a_name."'. Column name must only contain _a-z0-9 and must start with a-z.";
1285 return false;
1286 }
1287
1288 if ($this->isReservedWord($a_name))
1289 {
1290 $this->error_str = "Invalid column name '".$a_name."' (Reserved Word).";
1291 return false;
1292 }
1293
1294 if (strtolower(substr($a_name, 0, 4)) == "sys_")
1295 {
1296 $this->error_str = "Invalid column name '".$a_name."'. Name must not start with 'sys_'.";
1297 return false;
1298 }
1299
1300 if (strlen($a_name) > 30)
1301 {
1302 $this->error_str = "Invalid column name '".$a_name."'. Maximum column identifer lenght is 30 bytes.";
1303 return false;
1304 }
1305
1306 return true;
1307 }
1308
1314 function checkIndexName($a_name)
1315 {
1316 if (!preg_match ("/^[a-z]+[_a-z0-9]*$/", $a_name))
1317 {
1318 $this->error_str = "Invalid column name '".$a_name."'. Column name must only contain _a-z0-9 and must start with a-z.";
1319 return false;
1320 }
1321
1322 if ($this->isReservedWord($a_name))
1323 {
1324 $this->error_str = "Invalid column name '".$a_name."' (Reserved Word).";
1325 return false;
1326 }
1327
1328 if (strlen($a_name) > 3)
1329 {
1330 $this->error_str = "Invalid index name '".$a_name."'. Maximum index identifer lenght is 3 bytes.";
1331 return false;
1332 }
1333
1334 return true;
1335 }
1336
1338 {
1340 }
1341
1347 function constraintName($a_table, $a_constraint)
1348 {
1349 return $a_constraint;
1350 }
1351
1356 static function isReservedWord($a_word)
1357 {
1358 include_once("./Services/Database/classes/class.ilDBMySQL.php");
1359 $mysql_reserved_words = ilDBMySQL::getReservedWords();
1360 if (in_array(strtoupper($a_word), $mysql_reserved_words))
1361 {
1362 return true;
1363 }
1364 include_once("./Services/Database/classes/class.ilDBOracle.php");
1365 $oracle_reserved_words = ilDBOracle::getReservedWords();
1366 if (in_array(strtoupper($a_word), $oracle_reserved_words))
1367 {
1368 return true;
1369 }
1370 include_once("./Services/Database/classes/class.ilDBPostgreSQL.php");
1371 $postgres_reserved_words = ilDBPostgreSQL::getReservedWords();
1372 if (in_array(strtoupper($a_word), $postgres_reserved_words))
1373 {
1374 return true;
1375 }
1376 }
1377
1378 //
1379 // Data query and manupilation functions
1380 //
1381
1393 function query($sql, $a_handle_error = true)
1394 {
1395 global $ilBench;
1396
1397 if (is_object($ilBench))
1398 {
1399 $ilBench->startDbBench($sql);
1400 }
1401 $r = $this->db->query($sql);
1402 if (is_object($ilBench))
1403 {
1404 $ilBench->stopDbBench();
1405 }
1406
1407 if ($a_handle_error)
1408 {
1409 return $this->handleError($r, "query(".$sql.")");
1410 }
1411
1412 return $r;
1413 }
1414
1422 function queryF($a_query, $a_types, $a_values)
1423 {
1424 if (!is_array($a_types) || !is_array($a_values) ||
1425 count($a_types) != count($a_values))
1426 {
1427 $this->raisePearError("ilDB::queryF: Types and values must be arrays of same size. ($a_query)");
1428 }
1429 $quoted_values = array();
1430 foreach($a_types as $k => $t)
1431 {
1432 $quoted_values[] = $this->quote($a_values[$k], $t);
1433 }
1434 $query = vsprintf($a_query, $quoted_values);
1435
1436 return $this->query($query);
1437 }
1438
1446 function manipulateF($a_query, $a_types, $a_values)
1447 {
1448 if (!is_array($a_types) || !is_array($a_values) ||
1449 count($a_types) != count($a_values))
1450 {
1451 $this->raisePearError("ilDB::manipulateF: types and values must be arrays of same size. ($a_query)");
1452 }
1453 $quoted_values = array();
1454 foreach($a_types as $k => $t)
1455 {
1456 $quoted_values[] = $this->quote($a_values[$k], $t);
1457 }
1458 $query = vsprintf($a_query, $quoted_values);
1459
1460 return $this->manipulate($query);
1461 }
1462
1466 function logStatement($sql)
1467 {
1468 $pos1 = strpos(strtolower($sql), "from ");
1469 $table = "";
1470 if ($pos1 > 0)
1471 {
1472 $tablef = substr($sql, $pos1+5);
1473 $pos2 = strpos(strtolower($tablef), " ");
1474 if ($pos2 > 0)
1475 {
1476 $table =substr($tablef, 0, $pos2);
1477 }
1478 else
1479 {
1480 $table = $tablef;
1481 }
1482 }
1483 if (trim($table) != "")
1484 {
1485 if (!is_array($this->ttt) || !in_array($table, $this->ttt))
1486 {
1487 echo "<br>".$table;
1488 $this->ttt[] = $table;
1489 }
1490 }
1491 else
1492 {
1493 echo "<br><b>".$sql."</b>";
1494 }
1495 }
1496
1500 function setLimit($a_limit, $a_offset = 0)
1501 {
1502 $this->db->setLimit($a_limit, $a_offset);
1503 }
1504
1508 function nextId($a_table_name)
1509 {
1510 // we do not create missing sequences automatically here
1511 // otherwise misspelled statements result in additional tables
1512 // please create sequences explicitly in the db update script
1513 $r = $this->db->nextId($a_table_name, false);
1514
1515 return $this->handleError($r, "nextId(".$a_table_name.")");
1516 }
1517
1528 function manipulate($sql)
1529 {
1530 global $ilBench;
1531
1532 if (is_object($ilBench))
1533 {
1534 $ilBench->startDbBench($sql);
1535 }
1536 $r = $this->db->exec($sql);
1537 if (is_object($ilBench))
1538 {
1539 $ilBench->stopDbBench();
1540 }
1541
1542 return $this->handleError($r, "manipulate(".$sql.")");
1543 }
1544
1553 function prepare($a_query, $a_types = null, $a_result_types = null)
1554 {
1555 $res = $this->db->prepare($a_query, $a_types, $a_result_types);
1556
1557 return $this->handleError($res, "prepare(".$a_query.")");
1558 }
1559
1568 function prepareManip($a_query, $a_types = null)
1569 {
1570 $res = $this->db->prepare($a_query, $a_types, MDB2_PREPARE_MANIP);
1571
1572 return $this->handleError($res, "prepareManip(".$a_query.")");
1573 }
1574
1583 function execute($a_stmt, $a_data = null)
1584 {
1585 $res = $a_stmt->execute($a_data);
1586
1587 return $this->handleError($res, "execute(".$a_stmt->query.")");
1588 }
1589
1599 function executeMultiple($a_stmt, $a_data)
1600 {
1601 $res = $this->db->extended->executeMultiple($a_stmt,$a_data);
1602
1603 return $this->handleError($res, "executeMultiple(".$a_stmt->query.")");
1604 }
1605
1612 function insert($a_table, $a_columns)
1613 {
1614 $fields = array();
1615 $field_values = array();
1616 $placeholders = array();
1617 $types = array();
1618 $values = array();
1619 $lobs = false;
1620 $lob = array();
1621 foreach ($a_columns as $k => $col)
1622 {
1623 $fields[] = $k;
1624 $placeholders[] = "%s";
1625 $placeholders2[] = ":$k";
1626 $types[] = $col[0];
1627
1628 // integer auto-typecast (this casts bool values to integer)
1629 if ($col[0] == 'integer' && !is_null($col[1]))
1630 {
1631 $col[1] = (int) $col[1];
1632 }
1633
1634 $values[] = $col[1];
1635 $field_values[$k] = $col[1];
1636 if ($col[0] == "blob" || $col[0] == "clob")
1637 {
1638 $lobs = true;
1639 $lob[$k] = $k;
1640 }
1641 }
1642 if ($lobs) // lobs -> use prepare execute (autoexecute broken in PEAR 2.4.1)
1643 {
1644 $st = $this->db->prepare("INSERT INTO ".$a_table." (".implode($fields,",").") VALUES (".
1645 implode($placeholders2,",").")", $types, MDB2_PREPARE_MANIP, $lob);
1646
1647 $this->handleError($st, "insert / prepare/execute(".$a_table.")");
1648
1649 $r = $st->execute($field_values);
1650
1651
1652 //$r = $this->db->extended->autoExecute($a_table, $field_values, MDB2_AUTOQUERY_INSERT, null, $types);
1653 $this->handleError($r, "insert / prepare/execute(".$a_table.")");
1654 $this->free($st);
1655 }
1656 else // if no lobs are used, take simple manipulateF
1657 {
1658 $q = "INSERT INTO ".$a_table." (".implode($fields,",").") VALUES (".
1659 implode($placeholders,",").")";
1660 $r = $this->manipulateF($q, $types, $values);
1661 }
1662 return $r;
1663 }
1664
1673 function update($a_table, $a_columns, $a_where)
1674 {
1675 $fields = array();
1676 $field_values = array();
1677 $placeholders = array();
1678 $types = array();
1679 $values = array();
1680 $lobs = false;
1681 $lob = array();
1682 foreach ($a_columns as $k => $col)
1683 {
1684 $fields[] = $k;
1685 $placeholders[] = "%s";
1686 $placeholders2[] = ":$k";
1687 $types[] = $col[0];
1688
1689 // integer auto-typecast (this casts bool values to integer)
1690 if ($col[0] == 'integer' && !is_null($col[1]))
1691 {
1692 $col[1] = (int) $col[1];
1693 }
1694
1695 $values[] = $col[1];
1696 $field_values[$k] = $col[1];
1697 if ($col[0] == "blob" || $col[0] == "clob")
1698 {
1699 $lobs = true;
1700 $lob[$k] = $k;
1701 }
1702 }
1703
1704 if ($lobs)
1705 {
1706 $q = "UPDATE ".$a_table." SET ";
1707 $lim = "";
1708 foreach ($fields as $k => $field)
1709 {
1710 $q.= $lim.$field." = ".$placeholders2[$k];
1711 $lim = ", ";
1712 }
1713 $q.= " WHERE ";
1714 $lim = "";
1715 foreach ($a_where as $k => $col)
1716 {
1717 $q.= $lim.$k." = ".$this->quote($col[1], $col[0]);
1718 $lim = " AND ";
1719 }
1720 $st = $this->db->prepare($q, $types, MDB2_PREPARE_MANIP, $lob);
1721 $r = $st->execute($field_values);
1722
1723 //$r = $this->db->extended->autoExecute($a_table, $field_values, MDB2_AUTOQUERY_INSERT, null, $types);
1724 $this->handleError($r, "update / prepare/execute(".$a_table.")");
1725 $this->free($st);
1726 }
1727 else
1728 {
1729 foreach ($a_where as $k => $col)
1730 {
1731 $types[] = $col[0];
1732 $values[] = $col[1];
1733 $field_values[$k] = $col;
1734 }
1735 $q = "UPDATE ".$a_table." SET ";
1736 $lim = "";
1737 foreach ($fields as $k => $field)
1738 {
1739 $q.= $lim.$field." = ".$placeholders[$k];
1740 $lim = ", ";
1741 }
1742 $q.= " WHERE ";
1743 $lim = "";
1744 foreach ($a_where as $k => $col)
1745 {
1746 $q.= $lim.$k." = %s";
1747 $lim = " AND ";
1748 }
1749
1750 $r = $this->manipulateF($q, $types, $values);
1751 }
1752 return $r;
1753 }
1754
1762 function replace($a_table, $a_pk_columns, $a_other_columns)
1763 {
1764 // this is the mysql implementation
1765 $a_columns = array_merge($a_pk_columns, $a_other_columns);
1766 $fields = array();
1767 $field_values = array();
1768 $placeholders = array();
1769 $types = array();
1770 $values = array();
1771 $lobs = false;
1772 $lob = array();
1773 foreach ($a_columns as $k => $col)
1774 {
1775 $fields[] = $k;
1776 $placeholders[] = "%s";
1777 $placeholders2[] = ":$k";
1778 $types[] = $col[0];
1779
1780 // integer auto-typecast (this casts bool values to integer)
1781 if ($col[0] == 'integer' && !is_null($col[1]))
1782 {
1783 $col[1] = (int) $col[1];
1784 }
1785
1786 $values[] = $col[1];
1787 $field_values[$k] = $col[1];
1788 if ($col[0] == "blob" || $col[0] == "clob")
1789 {
1790 $lobs = true;
1791 $lob[$k] = $k;
1792 }
1793 }
1794 if ($lobs) // lobs -> use prepare execute (autoexecute broken in PEAR 2.4.1)
1795 {
1796 $st = $this->db->prepare("REPLACE INTO ".$a_table." (".implode($fields,",").") VALUES (".
1797 implode($placeholders2,",").")", $types, MDB2_PREPARE_MANIP, $lob);
1798 $this->handleError($st, "insert / prepare/execute(".$a_table.")");
1799 $r = $st->execute($field_values);
1800 //$r = $this->db->extended->autoExecute($a_table, $field_values, MDB2_AUTOQUERY_INSERT, null, $types);
1801 $this->handleError($r, "insert / prepare/execute(".$a_table.")");
1802 $this->free($st);
1803 }
1804 else // if no lobs are used, take simple manipulateF
1805 {
1806 $q = "REPLACE INTO ".$a_table." (".implode($fields,",").") VALUES (".
1807 implode($placeholders,",").")";
1808 $r = $this->manipulateF($q, $types, $values);
1809 }
1810 return $r;
1811 }
1812
1818 function fetchAssoc($a_set)
1819 {
1820 return $a_set->fetchRow(DB_FETCHMODE_ASSOC);
1821 }
1822
1826 function free($a_st)
1827 {
1828 return $a_st->free();
1829 }
1830
1836 function fetchObject($a_set)
1837 {
1838 return $a_set->fetchRow(DB_FETCHMODE_OBJECT);
1839 }
1840
1846 function numRows($a_set)
1847 {
1848 return $a_set->numRows();
1849 }
1850
1851 //
1852 // function and clauses abstraction
1853 //
1854
1866 function in($a_field, $a_values, $negate = false, $a_type = "")
1867 {
1868 if (count($a_values) == 0)
1869 {
1870 // BEGIN fixed mantis #0014191:
1871 //return " 1=2 "; // return a false statement on empty array
1872 return $negate ? ' 1=1 ' : ' 1=2 ';
1873 // END fixed mantis #0014191:
1874 }
1875 if ($a_type == "") // untyped: used ? for prepare/execute
1876 {
1877 $str = $a_field.(($negate) ? " NOT" : "")." IN (?".str_repeat(",?", count($a_values) - 1).")";
1878 }
1879 else // typed, use values for query/manipulate
1880 {
1881 $str = $a_field.(($negate) ? " NOT" : "")." IN (";
1882 $sep = "";
1883 foreach ($a_values as $v)
1884 {
1885 $str.= $sep.$this->quote($v, $a_type);
1886 $sep = ",";
1887 }
1888 $str.= ")";
1889 }
1890
1891 return $str;
1892 }
1893
1897 function addTypesToArray($a_arr, $a_type, $a_cnt)
1898 {
1899 if (!is_array($a_arr))
1900 {
1901 $a_arr = array();
1902 }
1903 if ($a_cnt > 0)
1904 {
1905 $type_arr = array_fill(0, $a_cnt, $a_type);
1906 }
1907 else
1908 {
1909 $type_arr = array();
1910 }
1911 return array_merge($a_arr, $type_arr);
1912 }
1913
1918 function now()
1919 {
1920 return "now()";
1921 }
1922
1923
1933 public function concat($a_values,$a_allow_null = true)
1934 {
1935 if(!count($a_values))
1936 {
1937 return ' ';
1938 }
1939
1940 $concat = ' CONCAT(';
1941 $first = true;
1942 foreach($a_values as $field_info)
1943 {
1944 $val = $field_info[0];
1945
1946 if(!$first)
1947 {
1948 $concat .= ',';
1949 }
1950
1951 if($a_allow_null)
1952 {
1953 $concat .= 'COALESCE(';
1954 }
1955 $concat .= $val;
1956
1957 if($a_allow_null)
1958 {
1959 $concat .= ",''";
1960 $concat .= ')';
1961 }
1962
1963 $first = false;
1964 }
1965 $concat .= ') ';
1966 return $concat;
1967 }
1968
1975 function substr($a_exp, $a_pos = 1, $a_len = -1)
1976 {
1977 $lenstr = "";
1978 if ($a_len > -1)
1979 {
1980 $lenstr = ", ".$a_len;
1981 }
1982 return " SUBSTR(".$a_exp.", ".$a_pos.$lenstr.") ";
1983 }
1984
1991 function upper($a_exp)
1992 {
1993 return " UPPER(".$a_exp.") ";
1994 }
1995
2002 function lower($a_exp)
2003 {
2004 return " LOWER(".$a_exp.") ";
2005 }
2006
2014 public function locate($a_needle,$a_string,$a_start_pos = 1)
2015 {
2016 $locate = ' LOCATE( ';
2017 $locate .= $a_needle;
2018 $locate .= ',';
2019 $locate .= $a_string;
2020 $locate .= ',';
2021 $locate .= $a_start_pos;
2022 $locate .= ') ';
2023 return $locate;
2024 }
2025
2026
2032 function like($a_col, $a_type, $a_value = "?", $case_insensitive = true)
2033 {
2034 if (!in_array($a_type, array("text", "clob", "blob")))
2035 {
2036 $this->raisePearError("Like: Invalid column type '".$a_type."'.", $this->error_class->FATAL);
2037 }
2038 if ($a_value == "?")
2039 {
2040 if ($case_insensitive)
2041 {
2042 return "UPPER(".$a_col.") LIKE(UPPER(?))";
2043 }
2044 else
2045 {
2046 return $a_col ." LIKE(?)";
2047 }
2048 }
2049 else
2050 {
2051 if ($case_insensitive)
2052 {
2053 // Always quote as text
2054 return " UPPER(".$a_col.") LIKE(UPPER(".$this->quote($a_value, 'text')."))";
2055 }
2056 else
2057 {
2058 // Always quote as text
2059 return " ".$a_col." LIKE(".$this->quote($a_value, 'text').")";
2060 }
2061 }
2062 }
2063
2064
2068 function equals($a_col, $a_value, $a_type, $a_empty_or_null = false)
2069 {
2070 if (!$a_empty_or_null || $a_value != "")
2071 {
2072 return $a_col." = ".$this->quote($a_value, $a_type);
2073 }
2074 else
2075 {
2076 return "(".$a_col." = '' OR $a_col IS NULL)";
2077 }
2078 }
2079
2083 function equalsNot($a_col, $a_value, $a_type, $a_empty_or_null = false)
2084 {
2085 if (!$a_empty_or_null)
2086 {
2087 return $a_col." <> ".$this->quote($a_value, $a_type);
2088 }
2089 if ($a_value != "")
2090 {
2091 return "(".$a_col." <> ".$this->quote($a_value, $a_type). " OR ".
2092 $a_col." IS NULL)";
2093 }
2094 else
2095 {
2096 return "(".$a_col." <> '' AND $a_col IS NOT NULL)";
2097 }
2098 }
2099
2106 function fromUnixtime($a_expr, $a_to_text = true)
2107 {
2108 return "FROM_UNIXTIME(".$a_expr.")";
2109 }
2110
2114 function unixTimestamp()
2115 {
2116 return "UNIX_TIMESTAMP()";
2117 }
2118
2122 function optimizeTable($a_table)
2123 {
2124 // needs to be overwritten in DBMS specific class
2125 // if necessary and possible
2126 }
2127
2128 //
2129 // Schema related functions
2130 //
2131
2138 function tableExists($a_table)
2139 {
2140 $tables = $this->listTables();
2141
2142 if (is_array($tables))
2143 {
2144 if (in_array($a_table, $tables))
2145 {
2146 return true;
2147 }
2148 }
2149 return false;
2150 }
2151
2159 function tableColumnExists($a_table, $a_column_name)
2160 {
2161
2162 $column_visibility = false;
2163 $manager = $this->db->loadModule('Manager');
2164 $r = $manager->listTableFields($a_table);
2165
2166 if (!MDB2::isError($r))
2167 {
2168 foreach($r as $field)
2169 {
2170 if ($field == $a_column_name)
2171 {
2172 $column_visibility = true;
2173 }
2174 }
2175 }
2176
2177 return $column_visibility;
2178 }
2179
2187 function uniqueConstraintExists($a_table, $a_fields)
2188 {
2189 if (is_file("./Services/Database/classes/class.ilDBAnalyzer.php"))
2190 {
2191 include_once("./Services/Database/classes/class.ilDBAnalyzer.php");
2192 }
2193 else
2194 {
2195 include_once("../Services/Database/classes/class.ilDBAnalyzer.php");
2196 }
2197 $analyzer = new ilDBAnalyzer();
2198 $cons = $analyzer->getConstraintsInformation($a_table);
2199 foreach ($cons as $c)
2200 {
2201 if ($c["type"] == "unique" && count($a_fields) == count($c["fields"]))
2202 {
2203 $all_in = true;
2204 foreach ($a_fields as $f)
2205 {
2206 if (!isset($c["fields"][$f]))
2207 {
2208 $all_in = false;
2209 }
2210 }
2211 if ($all_in)
2212 {
2213 return true;
2214 }
2215 }
2216 }
2217 return false;
2218 }
2219
2220
2226 function listTables()
2227 {
2228 $manager = $this->db->loadModule('Manager');
2229 $r = $manager->listTables();
2230
2231 if (!MDB2::isError($r))
2232 {
2233 return $r;
2234 }
2235
2236 return false;
2237 }
2238
2245 function sequenceExists($a_sequence)
2246 {
2247 $sequences = $this->listSequences();
2248
2249 if (is_array($sequences))
2250 {
2251 if (in_array($a_sequence, $sequences))
2252 {
2253 return true;
2254 }
2255 }
2256 return false;
2257 }
2258
2264 function listSequences()
2265 {
2266 $manager = $this->db->loadModule('Manager');
2267 $r = $manager->listSequences();
2268
2269 if (!MDB2::isError($r))
2270 {
2271 return $r;
2272 }
2273
2274 return false;
2275 }
2276
2277
2278 //
2279 // Quote Functions
2280 //
2281
2285 function quote($a_query, $a_type = null)
2286 {
2287 if ($a_query == "" && is_null($a_type))
2288 {
2289 $a_query = "";
2290 }
2291
2292 // Performance fix
2293 if($a_type == 'integer' && !is_null($a_query))
2294 {
2295 return (int) $a_query;
2296 }
2297
2298 if ($a_type == "blob" || $a_type == "clob")
2299 {
2300 $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);
2301 }
2302
2303 return $this->db->quote($a_query, $a_type);
2304 }
2305
2313 function quoteIdentifier($a_identifier)
2314 {
2315 return $this->db->quoteIdentifier($a_identifier);
2316 }
2317
2318
2319 //
2320 // Transaction and Locking methods
2321 //
2322
2329 {
2330 if (!$this->db->supports('transactions'))
2331 {
2332 $this->raisePearError("ilDB::beginTransaction: Transactions are not supported.", $this->error_class->FATAL);
2333 }
2334 $res = $this->db->beginTransaction();
2335
2336 return $this->handleError($res, "beginTransaction()");
2337 }
2338
2342 function commit()
2343 {
2344 $res = $this->db->commit();
2345
2346 return $this->handleError($res, "commit()");
2347 }
2348
2352 function rollback()
2353 {
2354 $res = $this->db->rollback();
2355
2356 return $this->handleError($res, "rollback()");
2357 }
2358
2364 abstract public function lockTables($a_tables);
2365
2370 abstract public function unlockTables();
2371
2372
2373//
2374//
2375// Older functions. Must be checked.
2376//
2377//
2378
2387 function autoExecute($a_tablename,$a_fields,$a_mode = MDB2_AUTOQUERY_INSERT,$a_where = false)
2388 {
2389 $res = $this->db->autoExecute($a_tablename,$a_fields,$a_mode,$a_where);
2390
2391 return $this->handleError($res, "autoExecute(".$a_tablename.")");
2392 }
2393
2394//
2395//
2396// Deprecated functions.
2397//
2398//
2399
2404 {
2405 $res = $this->db->lastInsertId();
2406 if(MDB2::isError($res))
2407 {
2408 return false;
2409 }
2410 return $res;
2411 }
2412
2422 function getOne($sql)
2423 {
2424 //$r = $this->db->getOne($sql);
2425 $set = $this->db->query($sql);
2426
2427 $this->handleError($set, "getOne(".$sql.")");
2428
2429 if (!MDB2::isError($set))
2430 {
2431 $r = $set->fetchRow(DB_FETCHMODE_ASSOC);
2432
2433 return $r[0];
2434 }
2435 }
2436
2446 function getRow($sql,$mode = DB_FETCHMODE_OBJECT)
2447 {
2448 $set = $this->query($sql);
2449 $r = $set->fetchRow($mode);
2450 //$r = $this->db->getrow($sql,$mode);
2451
2452 $this->handleError($r, "getRow(".$sql.")");
2453
2454 return $r;
2455 } //end function
2456
2462 function setSubType($a_value)
2463 {
2464 $this->sub_type = (string)$a_value;
2465 }
2466
2472 function getSubType()
2473 {
2474 return $this->sub_type;
2475 }
2476
2477} //end Class
2478?>
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
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
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
Error Handling & global info handling uses PEAR error class.
global $ilBench
Definition: ilias.php:18
if(!is_array($argv)) $options