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
8 include_once ("Services/PEAR/lib/MDB2.php");
9 
10 define("DB_FETCHMODE_ASSOC", MDB2_FETCHMODE_ASSOC);
11 define("DB_FETCHMODE_OBJECT", MDB2_FETCHMODE_OBJECT);
12 
13 //echo "-".DB_FETCHMODE_ASSOC."-";
14 //echo "+".DB_FETCHMODE_OBJECT."+";
15 
16 
28 abstract class ilDB extends PEAR
29 {
30  const LOCK_WRITE = 1;
31  const LOCK_READ = 2;
32 
33 
40 
45  var $db;
46 
51  var $result;
52 
53 
54  var $allowed_attributes = array(
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 
65  var $sub_type;
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 
368  function supportsFulltext()
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 
574  $options = $this->getCreateTableOptions();
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
1285  $allowed_attributes = $this->allowed_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 
2385  function beginTransaction()
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 
2460  function getLastInsertId()
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 ?>
static getReservedWords()
Get reserved words.
upper($a_exp)
Upper.
const MDB2_FETCHMODE_ASSOC
Column data indexed by column names.
Definition: MDB2.php:129
raisePearError($a_message, $a_level="")
Raise an error.
Definition: class.ilDB.php:485
prepare($a_query, $a_types=null, $a_result_types=null)
Prepare a query (SELECT) statement to be used with execute.
checkTableColumns($a_cols)
Check table columns definition.
getRow($sql, $mode=DB_FETCHMODE_OBJECT)
getRow.
getOne($sql)
getOne.
numRows($a_set)
Fetch row as associative array from result set.
setErrorHandling($mode=null, $options=null)
Sets how errors generated by this object should be handled.
Definition: PEAR.php:335
rollback()
Rollback a transaction.
createDatabase($a_name, $a_charset="utf8", $a_collation="")
Create database.
Definition: class.ilDB.php:527
const PEAR_ERROR_CALLBACK
Definition: PEAR.php:35
isFulltextIndex($a_table, $a_name)
Is index a fulltext index?
Definition: class.ilDB.php:992
isError($data, $code=null)
Tell whether a value is a MDB2 error.
Definition: MDB2.php:594
setDBUser($a_user)
Set database user.
Definition: class.ilDB.php:83
sequenceExists($a_sequence)
Check, whether a given sequence exists.
$error_class
Definition: class.ilDB.php:39
checkColumnDefinition($a_def, $a_modify_mode=false)
Check whether a column definition is valid.
renameTableColumn($a_table, $a_column, $a_new_column)
Rename a table column Use this only on aleady "abstracted" tables.
Definition: class.ilDB.php:819
tableExists($a_table)
Check, whether a given table exists.
query($sql, $a_handle_error=true)
Query.
nextId($a_table_name)
Get next ID for an index.
const MDB2_PORTABILITY_EMPTY_TO_NULL
Portability: convert empty values to null strings in data output by query*() and fetch*().
Definition: MDB2.php:198
static getReservedWords()
Get reserved words.
locate($a_needle, $a_string, $a_start_pos=1)
Create locate string.
replace($a_table, $a_pk_columns, $a_other_columns)
Replace into method.
beginTransaction()
Begin Transaction.
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
checkIndexName($a_name)
Check whether an index name is valid.
like($a_col, $a_type, $a_value="?", $case_insensitive=true)
Like.
fetchObject($a_set)
Fetch row as object from result set.
getSubType()
Get sub type.
dropIndex($a_table, $a_name="in")
Drop an index from a table.
modifyTableColumn($a_table, $a_column, $a_attributes)
Modify a table column Use this only on aleady "abstracted" tables.
Definition: class.ilDB.php:733
uniqueConstraintExists($a_table, $a_fields)
Checks if a unique constraint exists based on the fields of the unique constraint (not the name) ...
dropIndexByFields($a_table, $a_fields)
Drop index by field(s)
$allowed_attributes
Definition: class.ilDB.php:54
addFulltextIndex($a_table, $a_fields, $a_name="in")
Add fulltext index.
Definition: class.ilDB.php:984
getDBPort()
Get database port.
Definition: class.ilDB.php:113
addPrimaryKey($a_table, $a_fields)
Add a primary key to a table.
Definition: class.ilDB.php:896
addIndex($a_table, $a_fields, $a_name="in", $a_fulltext=false)
Add an index to a table.
Definition: class.ilDB.php:946
disconnect()
Disconnect.
Definition: class.ilDB.php:289
supportsFulltext()
Definition: class.ilDB.php:368
in($a_field, $a_values, $negate=false, $a_type="")
Get abstract in-clause for given array.
const DB_FETCHMODE_OBJECT
Definition: class.ilDB.php:11
addUniqueConstraint($a_table, $a_fields, $a_name="con")
Add a unique constraint to a table.
doConnect()
Standard way to connect to db.
Definition: class.ilDB.php:280
const MDB2_AUTOQUERY_SELECT
Definition: Extended.php:59
getLastInsertId()
Get last insert id.
equals($a_col, $a_value, $a_type, $a_empty_or_null=false)
Use this only on text fields.
setSubType($a_value)
Set sub type.
manipulateF($a_query, $a_types, $a_values)
Formatted manupulate (for DELETE, UPDATE, INSERT).
getDBName()
Get database name.
Definition: class.ilDB.php:173
fetchAssoc($a_set)
Fetch row as associative array from result set.
const MDB2_FETCHMODE_OBJECT
Column data as object properties.
Definition: MDB2.php:134
dropTable($a_name, $a_error_if_not_existing=true)
Drop a table.
Definition: class.ilDB.php:598
concat($a_values, $a_allow_null=true)
Abstraction of SQL function CONCAT.
quote($a_query, $a_type=null)
Wrapper for quote method.
executeMultiple($a_stmt, $a_data)
Execute a query statement prepared by either prepare() or prepareManip() with multiple data arrays...
unixTimestamp()
Unix timestamp.
createSequence($a_table_name, $a_start=1)
Create a sequence for a table.
ilDB()
constructor
Definition: class.ilDB.php:74
prepareManip($a_query, $a_types=null)
Prepare a data manipulation statement to be used with execute.
const MDB2_AUTOQUERY_DELETE
Definition: Extended.php:58
$r
Definition: example_031.php:79
getDBHost()
Get database host.
Definition: class.ilDB.php:133
initConnection()
Initialize the database connection.
Definition: class.ilDB.php:301
setDBPort($a_port)
Set database port.
Definition: class.ilDB.php:103
useSlave($a_val=true)
Use slave.
Definition: class.ilDB.php:390
const MDB2_PREPARE_MANIP
These are just helper constants to more verbosely express parameters to prepare() ...
Definition: MDB2.php:109
substr($a_exp, $a_pos=1, $a_len=-1)
Substring.
checkTableName($a_name)
Check whether a table name is valid.
if(!is_array($argv)) $options
getDBUser()
Get database user.
Definition: class.ilDB.php:93
& connect($dsn, $options=false)
Create a new MDB2 connection object and connect to the specified database.
Definition: MDB2.php:431
free($a_st)
Free a statement / result set.
supportsSlave()
Supports slave.
Definition: class.ilDB.php:379
checkColumnName($a_name)
Check whether a column name is valid.
static getReservedWords()
Get reserved words.
fromUnixtime($a_expr, $a_to_text=true)
fromUnixtime (makes timestamp out of unix timestamp)
setDBName($a_name)
Set database name.
Definition: class.ilDB.php:163
autoExecute($a_tablename, $a_fields, $a_mode=MDB2_AUTOQUERY_INSERT, $a_where=false)
Wrapper for Pear autoExecute.
setLimit($a_limit, $a_offset=0)
Set limit and offset for a query.
const MDB2_PORTABILITY_ALL
Portability: turn on all portability features.
Definition: MDB2.php:210
getAllowedAttributes()
enableResultBuffering($a_status)
En/disable result buffering.
Definition: class.ilDB.php:209
unlockTables()
Unlock tables locked by previous lock table calls.
execute($a_stmt, $a_data=null)
Execute a query statement prepared by either prepare() or prepareManip()
const MDB2_AUTOQUERY_UPDATE
Definition: Extended.php:57
static isReservedWord($a_word)
Checks whether a word is a reserved word in one of the supported databases.
listTables()
Get all tables.
const DB_FETCHMODE_ASSOC
Definition: class.ilDB.php:10
const LOCK_WRITE
Definition: class.ilDB.php:30
initHostConnection()
Initialize the host connection (no specific database)
Definition: class.ilDB.php:364
handleError($a_res, $a_info="", $a_level="")
Handle MDB2 Errors.
Definition: class.ilDB.php:405
loadMDB2Extensions()
load additional mdb2 extensions and set their constants
Definition: class.ilDB.php:500
insert($a_table, $a_columns)
Convenient method for standard insert statements, example field array:
getDSN()
Get DSN.
connectHost()
Sets up a host connection only (no specific database used).
Definition: class.ilDB.php:320
connect($a_return_false_for_error=false)
Open the connection.
Definition: class.ilDB.php:241
const LOCK_READ
Definition: class.ilDB.php:31
queryF($a_query, $a_types, $a_values)
Formatted query (for SELECTS).
$sub_type
Definition: class.ilDB.php:65
logStatement($sql)
Helper function, should usually not be called.
initFromIniFile($tmpClientIniFile=null)
Init db parameters from ini file.
Definition: class.ilDB.php:218
manipulate($sql)
Data manipulation.
now()
now()
constraintName($a_table, $a_constraint)
Determine contraint name by table name and constraint name.
optimizeTable($a_table)
Optimize Table.
Database Wrapper.
Definition: class.ilDB.php:28
setDBPassword($a_password)
Set database password.
Definition: class.ilDB.php:143
getDBType()
Get DSN.
commit()
Commit a transaction.
dropPrimaryKey($a_table)
Drop a primary key from a table.
Definition: class.ilDB.php:929
update($a_table, $a_columns, $a_where)
Convenient method for standard update statements, example field array:
& 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&#39;s de...
Definition: PEAR.php:524
getCreateTableOptions()
Get options for the create table statement.
Definition: class.ilDB.php:587
global $ilBench
Definition: ilias.php:18
listSequences()
Get all sequences.
dropSequence($a_table_name)
Drop a sequence for a table.
getHostDSN()
Should return a valid value, if host connections are possible (connectHost) to create a new database ...
Definition: class.ilDB.php:311
getDBPassword()
Get database password.
Definition: class.ilDB.php:153
This class gives all kind of DB information using the MDB2 manager and reverse module.
equalsNot($a_col, $a_value, $a_type, $a_empty_or_null=false)
Use this only on text fields.
addTableColumn($a_table, $a_column, $a_attributes)
Add table column Use this only on aleady "abstracted" tables.
Definition: class.ilDB.php:675
static getReservedWords()
Get reserved words.
dropUniqueConstraint($a_table, $a_name="con")
Drop a constraint from a table.
dropTableColumn($a_table, $a_column)
Drop table column Use this only on aleady "abstracted" tables.
Definition: class.ilDB.php:709
getPrimaryKeyIdentifier()
Primary key identifier.
Definition: class.ilDB.php:918
quoteIdentifier($a_identifier)
Quote table and field names.
addTypesToArray($a_arr, $a_type, $a_cnt)
Adds a type x times to an array.
dropUniqueConstraintByFields($a_table, $a_fields)
Drop constraint by field(s)
checkColumn($a_col, $a_def)
Check column definition.
alterTable($a_name, $a_changes)
Alter a table in the database This method is DEPRECATED, see http://www.ilias.de/docu/goto.php?target=pg_25354_42&client_id=docu PLEASE USE THE SPECIALIZED METHODS OF THIS CLASS TO CHANGE THE DB SCHEMA.
Definition: class.ilDB.php:654
setDBHost($a_host)
Set database host.
Definition: class.ilDB.php:123
tableColumnExists($a_table, $a_column_name)
Checks for the existence of a table column.
renameTable($a_name, $a_new_name)
Rename a table.
Definition: class.ilDB.php:869
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_AUTOQUERY_INSERT
Used by autoPrepare()
Definition: Extended.php:56
static isDbError($a_res)
Check error.
Definition: class.ilDB.php:515
lockTables($a_tables)
Abstraction of lock table.
getDBVersion()
Get DB version.
Definition: class.ilDB.php:186
indexExistsByFields($a_table, $a_fields)
Check if index exists.
lower($a_exp)
Upper.