ILIAS  Release_5_0_x_branch Revision 61816
 All Data Structures Namespaces Files Functions Variables Groups Pages
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 ("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 
39  var $error_class;
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 
918  function getPrimaryKeyIdentifier()
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
1228  $allowed_attributes = $this->allowed_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 
1337  function getAllowedAttributes()
1338  {
1339  return $this->allowed_attributes;
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 
2328  function beginTransaction()
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 
2403  function getLastInsertId()
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 ?>