ILIAS  Release_4_1_x_branch Revision 61804
 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 
72  function ilDB()
73  {
74  }
75 
81  function setDBUser($a_user)
82  {
83  $this->db_user = $a_user;
84  }
85 
91  function getDBUser()
92  {
93  return $this->db_user;
94  }
95 
101  function setDBPort($a_port)
102  {
103  $this->db_port = $a_port;
104  }
105 
111  function getDBPort()
112  {
113  return $this->db_port;
114  }
115 
121  function setDBHost($a_host)
122  {
123  $this->db_host = $a_host;
124  }
125 
131  function getDBHost()
132  {
133  return $this->db_host;
134  }
135 
141  function setDBPassword($a_password)
142  {
143  $this->db_password = $a_password;
144  }
145 
151  function getDBPassword()
152  {
153  return $this->db_password;
154  }
155 
161  function setDBName($a_name)
162  {
163  $this->db_name = $a_name;
164  }
165 
171  function getDBName()
172  {
173  return $this->db_name;
174  }
175 
179  abstract function getDSN();
180 
184  function getDBVersion()
185  {
186  return "Unknown";
187  }
188 
192  abstract function getDBType();
193 
200  abstract static function getReservedWords();
201 
206  function initFromIniFile($tmpClientIniFile = null)
207  {
208  global $ilClientIniFile;
209 
210  //overwrite global client ini file if local parameter is set
211  if (is_object($tmpClientIniFile))
212  $clientIniFile = $tmpClientIniFile;
213  else
214  $clientIniFile = $ilClientIniFile;
215 
216  if (is_object($clientIniFile ))
217  {
218  $this->setDBUser($clientIniFile ->readVariable("db", "user"));
219  $this->setDBHost($clientIniFile ->readVariable("db", "host"));
220  $this->setDBPort($clientIniFile ->readVariable("db", "port"));
221  $this->setDBPassword($clientIniFile ->readVariable("db", "pass"));
222  $this->setDBName($clientIniFile ->readVariable("db", "name"));
223  }
224  }
225 
229  function connect($a_return_false_for_error = false)
230  {
231  //set up error handling
232  $this->error_class = new ilErrorHandling();
233  $this->setErrorHandling(PEAR_ERROR_CALLBACK, array($this->error_class,'errorHandler'));
234 //echo $this->getDSN();
235  //check dsn
236  if ($this->getDSN() == "")
237  {
238  $this->raisePearError("No DSN given");
239  }
240 
241  //connect to database
242  $this->doConnect();
243 
244  if ($a_return_false_for_error && MDB2::isError($this->db))
245  {
246  return false;
247  }
248 
249  $this->loadMDB2Extensions();
250 
251  // set empty value portability to PEAR::DB behaviour
252  if (!$this->isDbError($this->db))
253  {
254  $this->db->setOption('portability', MDB2_PORTABILITY_ALL);
255  }
256  //check error
257  $this->handleError($this->db);
258 
259  // anything, that must be done to initialize the connection
260  $this->initConnection();
261 
262  return true;
263  }
264 
268  function doConnect()
269  {
270  $this->db = MDB2::connect($this->getDSN(),
271  array("use_transactions" => true));
272  }
273 
277  function disconnect()
278  {
279  $this->db->disconnect();
280  }
281 
282  //
283  // General and MDB2 related functions
284  //
285 
289  protected function initConnection()
290  {
291  }
292 
299  function getHostDSN()
300  {
301  return false;
302  }
303 
308  function connectHost()
309  {
310  //set up error handling
311  $this->error_class = new ilErrorHandling();
312  $this->setErrorHandling(PEAR_ERROR_CALLBACK, array($this->error_class,'errorHandler'));
313 
314  //check dsn
315  if ($this->getHostDSN() == "")
316  {
317  $this->raisePearError("No Host DSN given");
318  }
319 
320  //connect to database
321  $this->db = MDB2::connect($this->getHostDSN(),
322  array("use_transactions" => true));
323  if ($a_return_false_for_error && MDB2::isError($this->db))
324  {
325  return false;
326  }
327 
328  $this->loadMDB2Extensions();
329 
330  // set empty value portability to PEAR::DB behaviour
331  if (!$this->isDbError($this->db))
332  {
333  $cur = ($this->db->getOption("portability") & MDB2_PORTABILITY_EMPTY_TO_NULL);
334  $this->db->setOption("portability", $this->db->getOption("portability") - $cur);
335 
336  $cur = ($this->db->getOption("portability") & MDB2_PORTABILITY_FIX_CASE);
337  $this->db->setOption("portability", $this->db->getOption("portability") - $cur);
338  }
339 
340  //check error
341  $this->handleError($this->db);
342 
343  // anything, that must be done to initialize the connection
344  $this->initHostConnection();
345 
346  return true;
347  }
348 
352  protected function initHostConnection()
353  {
354  }
355 
356  function supportsFulltext()
357  {
358  return false;
359  }
360 
367  function handleError($a_res, $a_info = "", $a_level = "")
368  {
369  global $ilLog;
370 
371  if (MDB2::isError($a_res))
372  {
373  if ($a_level == "")
374  {
375  $a_level = $this->error_class->FATAL;
376  }
377 
378  // Show stack
379  try
380  {
381  throw new Exception();
382  }
383  catch(Exception $e)
384  {
385  $stack = $e->getTraceAsString();
386  }
387 
388  if(is_object($ilLog))
389  $ilLog->logStack();
390  $this->raisePearError("ilDB Error: ".$a_info."<br />".
391  $a_res->getMessage()."<br />".$a_res->getUserInfo()."<br />".$stack, $a_level);
392 
393  }
394 
395  return $a_res;
396  }
397 
401  function raisePearError($a_message, $a_level = "")
402  {
403  if ($a_level == "")
404  {
405  $a_level = $this->error_class->FATAL;
406  }
407 //echo "<br>-ilDB:raising-$a_message-$a_level-";
408  $this->raiseError($a_message, $a_level);
409  }
410 
416  protected function loadMDB2Extensions()
417  {
418  if (!$this->isDbError($this->db))
419  {
420  $this->db->loadModule('Extended');
421  define('DB_AUTOQUERY_SELECT',MDB2_AUTOQUERY_SELECT);
422  define('DB_AUTOQUERY_INSERT',MDB2_AUTOQUERY_INSERT);
423  define('DB_AUTOQUERY_UPDATE',MDB2_AUTOQUERY_UPDATE);
424  define('DB_AUTOQUERY_DELETE',MDB2_AUTOQUERY_DELETE);
425  }
426  }
427 
431  static function isDbError($a_res)
432  {
433  return MDB2::isError($a_res);
434  }
435 
436  //
437  // Data Definition Methods
438  //
439 
443  function createDatabase($a_name, $a_charset = "utf8", $a_collation = "")
444  {
445  if ($a_collation != "")
446  {
447  $sql = "CREATE DATABASE ".$a_name.
448  " CHARACTER SET ".$a_charset.
449  " COLLATE ".$a_collation;
450  }
451  else
452  {
453  $sql = "CREATE DATABASE ".$a_name.
454  " CHARACTER SET ".$a_charset;
455  }
456 
457  return $this->query($sql, false);
458  }
459 
460 
468  function createTable($a_name, $a_definition_array, $a_drop_table = false,
469  $a_ignore_erros = false)
470  {
471  // (removed options; should only be activated restricted, if necessary
472  if ($a_options == "")
473  {
474  $a_options = array();
475  }
476 
477  // check table name
478  if (!$this->checkTableName($a_name) && !$a_ignore_erros)
479  {
480  $this->raisePearError("ilDB Error: createTable(".$a_name.")<br />".
481  $this->error_str);
482  }
483 
484  // check definition array
485  if (!$this->checkTableColumns($a_definition_array) && !$a_ignore_erros)
486  {
487  $this->raisePearError("ilDB Error: createTable(".$a_name.")<br />".
488  $this->error_str);
489  }
490 
491  if ($a_drop_table)
492  {
493  $this->dropTable($a_name, false);
494  }
495 
496  $manager = $this->db->loadModule('Manager');
497  $r = $manager->createTable($a_name, $a_definition_array, $a_options);
498 
499  return $this->handleError($r, "createTable(".$a_name.")");
500  }
501 
508  function dropTable($a_name, $a_error_if_not_existing = true)
509  {
510  if (!$a_error_if_not_existing)
511  {
512  $tables = $this->listTables();
513  if (!in_array($a_name, $tables))
514  {
515  return;
516  }
517  }
518 
519  $manager = $this->db->loadModule('Manager');
520 
521  if ($this->getDBType() == "oracle")
522  {
523  // drop table constraints
524  $constraints = $manager->listTableConstraints($a_name);
525  $this->handleError($constraints, "dropTable(".$a_name."), listTableConstraints");
526  foreach ($constraints as $c)
527  {
528  if (substr($c, 0, 4) != "sys_")
529  {
530  $r = $manager->dropConstraint($a_name, $c);
531  $this->handleError($r, "dropTable(".$a_name."), dropConstraint");
532  }
533  }
534 
535  // drop table indexes
536  $indexes = $manager->listTableIndexes($a_name);
537  $this->handleError($indexes, "dropTable(".$a_name."), listTableIndexes");
538  foreach ($indexes as $i)
539  {
540  $r = $manager->dropIndex($a_name, $i);
541  $this->handleError($r, "dropTable(".$a_name."), dropIndex");
542  }
543  }
544 
545  // drop sequence
546  $seqs = $manager->listSequences();
547  if (in_array($a_name, $seqs))
548  {
549  $r = $manager->dropSequence($a_name);
550  $this->handleError($r, "dropTable(".$a_name."), dropSequence");
551  }
552 
553  // drop table
554  $r = $manager->dropTable($a_name);
555 
556  return $this->handleError($r, "dropTable(".$a_name.")");
557  }
558 
564  function alterTable($a_name, $a_changes)
565  {
566  if ($a_options == "")
567  {
568  $a_options = array();
569  }
570 
571  $manager = $this->db->loadModule('Manager');
572  $r = $manager->alterTable($a_name, $a_changes, false);
573 
574  return $this->handleError($r, "alterTable(".$a_name.")");
575  }
576 
585  function addTableColumn($a_table, $a_column, $a_attributes)
586  {
587 
588  $manager = $this->db->loadModule('Manager');
589 
590  if (!$this->checkColumnName($a_column))
591  {
592  $this->raisePearError("ilDB Error: addTableColumn(".$a_table.", ".$a_column.")<br />".
593  $this->error_str);
594  }
595  if (!$this->checkColumnDefinition($a_attributes))
596  {
597  $this->raisePearError("ilDB Error: addTableColumn(".$a_table.", ".$a_column.")<br />".
598  $this->error_str);
599  }
600 
601  $changes = array(
602  "add" => array(
603  $a_column => $a_attributes
604  )
605  );
606 
607  $r = $manager->alterTable($a_table, $changes, false);
608 
609  return $this->handleError($r, "addTableColumn(".$a_table.", ".$a_column.")");
610  }
611 
619  function dropTableColumn($a_table, $a_column)
620  {
621 
622  $manager = $this->db->loadModule('Manager');
623 
624  $changes = array(
625  "remove" => array(
626  $a_column => array()
627  )
628  );
629 
630  $r = $manager->alterTable($a_table, $changes, false);
631 
632  return $this->handleError($r, "dropTableColumn(".$a_table.", ".$a_column.")");
633  }
634 
643  function modifyTableColumn($a_table, $a_column, $a_attributes)
644  {
645  $manager = $this->db->loadModule('Manager');
646  $reverse = $this->db->loadModule('Reverse');
647  $def = $reverse->getTableFieldDefinition($a_table, $a_column);
648 
649  $this->handleError($def, "modifyTableColumn(".$a_table.")");
650 
651  if (is_file("./Services/Database/classes/class.ilDBAnalyzer.php"))
652  {
653  include_once("./Services/Database/classes/class.ilDBAnalyzer.php");
654  }
655  else
656  {
657  include_once("../Services/Database/classes/class.ilDBAnalyzer.php");
658  }
659  $analyzer = new ilDBAnalyzer();
660  $best_alt = $analyzer->getBestDefinitionAlternative($def);
661  $def = $def[$best_alt];
662  unset($def["nativetype"]);
663  unset($def["mdb2type"]);
664 
665  // check attributes
666  $type = ($a_attributes["type"] != "")
667  ? $a_attributes["type"]
668  : $def["type"];
669  foreach ($def as $k => $v)
670  {
671  if ($k != "type" && !in_array($k, $this->allowed_attributes[$type]))
672  {
673  unset($def[$k]);
674  }
675  }
676  $check_array = $def;
677  foreach ($a_attributes as $k => $v)
678  {
679  $check_array[$k] = $v;
680  }
681  if (!$this->checkColumnDefinition($check_array, true))
682  {
683  $this->raisePearError("ilDB Error: modifyTableColumn(".$a_table.", ".$a_column.")<br />".
684  $this->error_str);
685  }
686 
687  // oracle workaround: do not set null, if null already given
688  if ($this->getDbType() == "oracle")
689  {
690  if ($def["notnull"] == true && $a_attributes["notnull"] == true)
691  {
692  unset($def["notnull"]);
693  unset($a_attributes["notnull"]);
694  }
695  }
696 
697  foreach ($a_attributes as $a => $v)
698  {
699  $def[$a] = $v;
700  }
701 
702  $a_attributes["definition"] = $def;
703 
704  $changes = array(
705  "change" => array(
706  $a_column => $a_attributes
707  )
708  );
709 
710  $r = $manager->alterTable($a_table, $changes, false);
711 
712  return $this->handleError($r, "modifyTableColumn(".$a_table.")");
713  }
714 
723  function renameTableColumn($a_table, $a_column, $a_new_column)
724  {
725  // check table name
726  if (!$this->checkColumnName($a_new_column))
727  {
728  $this->raisePearError("ilDB Error: renameTableColumn(".$a_table.",".$a_column.",".$a_new_column.")<br />".
729  $this->error_str);
730  }
731 
732  $manager = $this->db->loadModule('Manager');
733  $reverse = $this->db->loadModule('Reverse');
734  $def = $reverse->getTableFieldDefinition($a_table, $a_column);
735 
736  $this->handleError($def, "renameTableColumn(".$a_table.",".$a_column.",".$a_new_column.")");
737 
738  if (is_file("./Services/Database/classes/class.ilDBAnalyzer.php"))
739  {
740  include_once("./Services/Database/classes/class.ilDBAnalyzer.php");
741  }
742  else
743  {
744  include_once("../Services/Database/classes/class.ilDBAnalyzer.php");
745  }
746 
747  $analyzer = new ilDBAnalyzer();
748  $best_alt = $analyzer->getBestDefinitionAlternative($def);
749  $def = $def[$best_alt];
750  unset($def["nativetype"]);
751  unset($def["mdb2type"]);
752 
753  $f["definition"] = $def;
754  $f["name"] = $a_new_column;
755 
756  $changes = array(
757  "rename" => array(
758  $a_column => $f
759  )
760  );
761 
762  $r = $manager->alterTable($a_table, $changes, false);
763 
764  return $this->handleError($r, "renameTableColumn(".$a_table.",".$a_column.",".$a_new_column.")");
765  }
766 
773  function renameTable($a_name, $a_new_name)
774  {
775  // check table name
776  if (!$this->checkTableName($a_new_name))
777  {
778  $this->raisePearError("ilDB Error: renameTable(".$a_name.",".$a_new_name.")<br />".
779  $this->error_str);
780  }
781 
782  $manager = $this->db->loadModule('Manager');
783  $r = $manager->alterTable($a_name, array("name" => $a_new_name), false);
784 
785  $query = "UPDATE abstraction_progress ".
786  "SET table_name = ".$this->db->quote($a_new_name,'text')." ".
787  "WHERE table_name = ".$this->db->quote($a_name,'text');
788  $this->db->query($query);
789 
790  return $this->handleError($r, "renameTable(".$a_name.",".$a_new_name.")");
791  }
792 
800  function addPrimaryKey($a_table, $a_fields)
801  {
802  $manager = $this->db->loadModule('Manager');
803 
804  $fields = array();
805  foreach ($a_fields as $f)
806  {
807  $fields[$f] = array();
808  }
809  $definition = array (
810  'primary' => true,
811  'fields' => $fields
812  );
813  $r = $manager->createConstraint($a_table,
814  $this->constraintName($a_table, $this->getPrimaryKeyIdentifier()), $definition);
815 
816  return $this->handleError($r, "addPrimaryKey(".$a_table.")");
817  }
818 
822  function getPrimaryKeyIdentifier()
823  {
824  return "PRIMARY";
825  }
826 
833  function dropPrimaryKey($a_table)
834  {
835  $manager = $this->db->loadModule('Manager');
836 
837  $r = $manager->dropConstraint($a_table,
838  $this->constraintName($a_table, $this->getPrimaryKeyIdentifier()), true);
839 
840  return $this->handleError($r, "dropPrimaryKey(".$a_table.")");
841  }
842 
850  function addIndex($a_table, $a_fields, $a_name = "in", $a_fulltext = false)
851  {
852  $manager = $this->db->loadModule('Manager');
853 
854  // check index name
855  if (!$this->checkIndexName($a_name))
856  {
857  $this->raisePearError("ilDB Error: addIndex(".$a_table.",".$a_name.")<br />".
858  $this->error_str);
859  }
860 
861  $fields = array();
862  foreach ($a_fields as $f)
863  {
864  $fields[$f] = array();
865  }
866  $definition = array (
867  'fields' => $fields
868  );
869 
870  if (!$a_fulltext)
871  {
872  $r = $manager->createIndex($a_table, $this->constraintName($a_table, $a_name), $definition);
873  }
874  else
875  {
876  if ($this->supportsFulltext())
877  {
878  $this->addFulltextIndex($a_table, $a_fields, $a_name);
879  }
880  }
881 
882  return $this->handleError($r, "addIndex(".$a_table.")");
883  }
884 
888  function addFulltextIndex($a_table, $a_fields, $a_name = "in")
889  {
890  return false;
891  }
892 
896  function isFulltextIndex($a_table, $a_name)
897  {
898  return false;
899  }
900 
908  function dropIndex($a_table, $a_name = "in")
909  {
910  $manager = $this->db->loadModule('Manager');
911 
912  if (!$this->isFulltextIndex($a_table, $a_name))
913  {
914  $r = $manager->dropIndex($a_table, $this->constraintName($a_table, $a_name));
915  }
916  else
917  {
918  $this->dropFulltextIndex($a_table, $a_name);
919  }
920 
921  return $this->handleError($r, "dropIndex(".$a_table.")");
922  }
923 
931  function addUniqueConstraint($a_table, $a_fields, $a_name = "con")
932  {
933  $manager = $this->db->loadModule('Manager');
934 
935  // check index name
936  if (!$this->checkIndexName($a_name))
937  {
938  $this->raisePearError("ilDB Error: addUniqueConstraint(".$a_table.",".$a_name.")<br />".
939  $this->error_str);
940  }
941 
942  $fields = array();
943  foreach ($a_fields as $f)
944  {
945  $fields[$f] = array();
946  }
947  $definition = array (
948  'unique' => true,
949  'fields' => $fields
950  );
951 
952  $r = $manager->createConstraint($a_table, $this->constraintName($a_table, $a_name), $definition);
953 
954  return $this->handleError($r, "addUniqueConstraint(".$a_table.")");
955  }
956 
960  function createSequence($a_table_name, $a_start = 1)
961  {
962  $manager = $this->db->loadModule('Manager');
963 
964  $r = $manager->createSequence($a_table_name, $a_start);
965 
966  return $this->handleError($r, "createSequence(".$a_table_name.")");
967  }
968 
969 
973  function dropSequence($a_table_name)
974  {
975  $manager = $this->db->loadModule('Manager');
976 
977  $r = $manager->dropSequence($a_table_name);
978 
979  return $this->handleError($r, "dropSequence(".$a_table_name.")");
980  }
981 
987  function checkTableName($a_name)
988  {
989  if (!preg_match ("/^[a-z]+[_a-z0-9]*$/", $a_name))
990  {
991  $this->error_str = "Table name must only contain _a-z0-9 and must start with a-z.";
992  return false;
993  }
994 
995  if ($this->isReservedWord($a_name))
996  {
997  $this->error_str = "Invalid table name '".$a_name."' (Reserved Word).";
998  return false;
999  }
1000 
1001  if (strtolower(substr($a_name, 0, 4)) == "sys_")
1002  {
1003  $this->error_str = "Invalid table name '".$a_name."'. Name must not start with 'sys_'.";
1004  return false;
1005  }
1006 
1007  if (strlen($a_name) > 22)
1008  {
1009  $this->error_str = "Invalid table name '".$a_name."'. Maximum table identifer lenght is 22 bytes.";
1010  return false;
1011  }
1012 
1013  return true;
1014  }
1015 
1021  function checkTableColumns($a_cols)
1022  {
1023  foreach ($a_cols as $col => $def)
1024  {
1025  if (!$this->checkColumn($col, $def))
1026  {
1027  return false;
1028  }
1029  }
1030 
1031  return true;
1032  }
1033 
1037  function checkColumn($a_col, $a_def)
1038  {
1039  if (!$this->checkColumnName($a_col))
1040  {
1041  return false;
1042  }
1043 
1044  if (!$this->checkColumnDefinition($a_def))
1045  {
1046  return false;
1047  }
1048 
1049  return true;
1050  }
1051 
1057  function checkColumnDefinition($a_def, $a_modify_mode = false)
1058  {
1059  // check valid type
1060  if (!in_array($a_def["type"], array("text", "integer", "float", "date", "time", "timestamp", "clob", "blob")))
1061  {
1062  switch ($a_def["type"])
1063  {
1064  case "boolean":
1065  $this->error_str = "Invalid column type '".$a_def["type"]."'. Use integer(1) instead.";
1066  break;
1067 
1068  case "decimal":
1069  $this->error_str = "Invalid column type '".$a_def["type"]."'. Use float or integer instead.";
1070  break;
1071 
1072  default:
1073  $this->error_str = "Invalid column type '".$a_def["type"]."'. Allowed types are: ".
1074  "text, integer, float, date, time, timestamp, clob and blob.";
1075  }
1076  }
1077 
1078  // check used attributes
1079  $allowed_attributes = $this->allowed_attributes;
1080 
1081  foreach ($a_def as $k => $v)
1082  {
1083  if ($k != "type" && !in_array($k, $allowed_attributes[$a_def["type"]]))
1084  {
1085  $this->error_str = "Attribute '".$k."' is not allowed for column type '".$a_def["type"]."'.";
1086  return false;
1087  }
1088  }
1089 
1090  // type specific checks
1091  switch ($a_def["type"])
1092  {
1093  case "text":
1094  if ($a_def["length"] < 1 || $a_def["length"] > 4000)
1095  {
1096  if (!$a_modify_mode || isset($a_def["length"]))
1097  {
1098  $this->error_str = "Invalid length '".$a_def["length"]."' for type text.".
1099  " Length must be >=1 and <= 4000.";
1100  return false;
1101  }
1102  }
1103  break;
1104 
1105  case "integer":
1106  if (!in_array($a_def["length"], array(1, 2, 3, 4, 8)))
1107  {
1108  if (!$a_modify_mode || isset($a_def["length"]))
1109  {
1110  $this->error_str = "Invalid length '".$a_def["length"]."' for type integer.".
1111  " Length must be 1, 2, 3, 4 or 8 (bytes).";
1112  return false;
1113  }
1114  }
1115  if ($a_def["unsigned"])
1116  {
1117  $this->error_str = "Unsigned attribut must not be true for type integer.";
1118  return false;
1119  }
1120  break;
1121  }
1122 
1123  return true;
1124  }
1125 
1131  function checkColumnName($a_name)
1132  {
1133  if (!preg_match ("/^[a-z]+[_a-z0-9]*$/", $a_name))
1134  {
1135  $this->error_str = "Invalid column name '".$a_name."'. Column name must only contain _a-z0-9 and must start with a-z.";
1136  return false;
1137  }
1138 
1139  if ($this->isReservedWord($a_name))
1140  {
1141  $this->error_str = "Invalid column name '".$a_name."' (Reserved Word).";
1142  return false;
1143  }
1144 
1145  if (strtolower(substr($a_name, 0, 4)) == "sys_")
1146  {
1147  $this->error_str = "Invalid column name '".$a_name."'. Name must not start with 'sys_'.";
1148  return false;
1149  }
1150 
1151  if (strlen($a_name) > 30)
1152  {
1153  $this->error_str = "Invalid column name '".$a_name."'. Maximum column identifer lenght is 30 bytes.";
1154  return false;
1155  }
1156 
1157  return true;
1158  }
1159 
1165  function checkIndexName($a_name)
1166  {
1167  if (!preg_match ("/^[a-z]+[_a-z0-9]*$/", $a_name))
1168  {
1169  $this->error_str = "Invalid column name '".$a_name."'. Column name must only contain _a-z0-9 and must start with a-z.";
1170  return false;
1171  }
1172 
1173  if ($this->isReservedWord($a_name))
1174  {
1175  $this->error_str = "Invalid column name '".$a_name."' (Reserved Word).";
1176  return false;
1177  }
1178 
1179  if (strlen($a_name) > 3)
1180  {
1181  $this->error_str = "Invalid index name '".$a_name."'. Maximum index identifer lenght is 3 bytes.";
1182  return false;
1183  }
1184 
1185  return true;
1186  }
1187 
1188  function getAllowedAttributes()
1189  {
1190  return $this->allowed_attributes;
1191  }
1192 
1198  function constraintName($a_table, $a_constraint)
1199  {
1200  return $a_constraint;
1201  }
1202 
1207  static function isReservedWord($a_word)
1208  {
1209  include_once("./Services/Database/classes/class.ilDBMySQL.php");
1210  $mysql_reserved_words = ilDBMySQL::getReservedWords();
1211  if (in_array(strtoupper($a_word), $mysql_reserved_words))
1212  {
1213  return true;
1214  }
1215  include_once("./Services/Database/classes/class.ilDBOracle.php");
1216  $oracle_reserved_words = ilDBOracle::getReservedWords();
1217  if (in_array(strtoupper($a_word), $oracle_reserved_words))
1218  {
1219  return true;
1220  }
1221  include_once("./Services/Database/classes/class.ilDBPostgreSQL.php");
1222  $postgres_reserved_words = ilDBPostgreSQL::getReservedWords();
1223  if (in_array(strtoupper($a_word), $postgres_reserved_words))
1224  {
1225  return true;
1226  }
1227  }
1228 
1229  //
1230  // Data query and manupilation functions
1231  //
1232 
1244  function query($sql, $a_handle_error = true)
1245  {
1246  global $ilBench;
1247 
1248  if (is_object($ilBench))
1249  {
1250  $ilBench->startDbBench($sql);
1251  }
1252  $r = $this->db->query($sql);
1253  if (is_object($ilBench))
1254  {
1255  $ilBench->stopDbBench();
1256  }
1257 
1258  if ($a_handle_error)
1259  {
1260  return $this->handleError($r, "query(".$sql.")");
1261  }
1262 
1263  return $r;
1264  }
1265 
1273  function queryF($a_query, $a_types, $a_values)
1274  {
1275  if (!is_array($a_types) || !is_array($a_values) ||
1276  count($a_types) != count($a_values))
1277  {
1278  $this->raisePearError("ilDB::queryF: types and values must be arrays of same size.");
1279  }
1280  $quoted_values = array();
1281  foreach($a_types as $k => $t)
1282  {
1283  $quoted_values[] = $this->quote($a_values[$k], $t);
1284  }
1285  $query = vsprintf($a_query, $quoted_values);
1286 
1287  return $this->query($query);
1288  }
1289 
1297  function manipulateF($a_query, $a_types, $a_values)
1298  {
1299  if (!is_array($a_types) || !is_array($a_values) ||
1300  count($a_types) != count($a_values))
1301  {
1302  $this->raisePearError("ilDB::manipulateF: types and values must be arrays of same size.");
1303  }
1304  $quoted_values = array();
1305  foreach($a_types as $k => $t)
1306  {
1307  $quoted_values[] = $this->quote($a_values[$k], $t);
1308  }
1309  $query = vsprintf($a_query, $quoted_values);
1310 
1311  return $this->manipulate($query);
1312  }
1313 
1317  function logStatement($sql)
1318  {
1319  $pos1 = strpos(strtolower($sql), "from ");
1320  $table = "";
1321  if ($pos1 > 0)
1322  {
1323  $tablef = substr($sql, $pos1+5);
1324  $pos2 = strpos(strtolower($tablef), " ");
1325  if ($pos2 > 0)
1326  {
1327  $table =substr($tablef, 0, $pos2);
1328  }
1329  else
1330  {
1331  $table = $tablef;
1332  }
1333  }
1334  if (trim($table) != "")
1335  {
1336  if (!is_array($this->ttt) || !in_array($table, $this->ttt))
1337  {
1338  echo "<br>".$table;
1339  $this->ttt[] = $table;
1340  }
1341  }
1342  else
1343  {
1344  echo "<br><b>".$sql."</b>";
1345  }
1346  }
1347 
1351  function setLimit($a_limit, $a_offset = 0)
1352  {
1353  $this->db->setLimit($a_limit, $a_offset);
1354  }
1355 
1359  function nextId($a_table_name)
1360  {
1361  // we do not create missing sequences automatically here
1362  // otherwise misspelled statements result in additional tables
1363  // please create sequences explicitly in the db update script
1364  $r = $this->db->nextId($a_table_name, false);
1365 
1366  return $this->handleError($r, "nextId(".$a_table_name.")");
1367  }
1368 
1379  function manipulate($sql)
1380  {
1381  $r = $this->db->exec($sql);
1382 
1383  return $this->handleError($r, "manipulate(".$sql.")");
1384  }
1385 
1394  function prepare($a_query, $a_types = null, $a_result_types = null)
1395  {
1396  $res = $this->db->prepare($a_query, $a_types, $a_result_types);
1397 
1398  return $this->handleError($res, "prepare(".$a_query.")");
1399  }
1400 
1409  function prepareManip($a_query, $a_types = null)
1410  {
1411  $res = $this->db->prepare($a_query, $a_types, MDB2_PREPARE_MANIP);
1412 
1413  return $this->handleError($res, "prepareManip(".$a_query.")");
1414  }
1415 
1424  function execute($a_stmt, $a_data = null)
1425  {
1426  $res = $a_stmt->execute($a_data);
1427 
1428  return $this->handleError($res, "execute(".$a_stmt->query.")");
1429  }
1430 
1440  function executeMultiple($a_stmt, $a_data)
1441  {
1442  $res = $this->db->extended->executeMultiple($a_stmt,$a_data);
1443 
1444  return $this->handleError($res, "executeMultiple(".$a_stmt->query.")");
1445  }
1446 
1453  function insert($a_table, $a_columns)
1454  {
1455  $fields = array();
1456  $field_values = array();
1457  $placeholders = array();
1458  $types = array();
1459  $values = array();
1460  $lobs = false;
1461  $lob = array();
1462  foreach ($a_columns as $k => $col)
1463  {
1464  $fields[] = $k;
1465  $placeholders[] = "%s";
1466  $placeholders2[] = ":$k";
1467  $types[] = $col[0];
1468  $values[] = $col[1];
1469  $field_values[$k] = $col[1];
1470  if ($col[0] == "blob" || $col[0] == "clob")
1471  {
1472  $lobs = true;
1473  $lob[$k] = $k;
1474  }
1475  }
1476  if ($lobs) // lobs -> use prepare execute (autoexecute broken in PEAR 2.4.1)
1477  {
1478  $st = $this->db->prepare("INSERT INTO ".$a_table." (".implode($fields,",").") VALUES (".
1479  implode($placeholders2,",").")", $types, MDB2_PREPARE_MANIP, $lob);
1480 
1481  $this->handleError($st, "insert / prepare/execute(".$a_table.")");
1482 
1483  $r = $st->execute($field_values);
1484 
1485 
1486  //$r = $this->db->extended->autoExecute($a_table, $field_values, MDB2_AUTOQUERY_INSERT, null, $types);
1487  $this->handleError($r, "insert / prepare/execute(".$a_table.")");
1488  $this->free($st);
1489  }
1490  else // if no lobs are used, take simple manipulateF
1491  {
1492  $q = "INSERT INTO ".$a_table." (".implode($fields,",").") VALUES (".
1493  implode($placeholders,",").")";
1494  $r = $this->manipulateF($q, $types, $values);
1495  }
1496  return $r;
1497  }
1498 
1507  function update($a_table, $a_columns, $a_where)
1508  {
1509  $fields = array();
1510  $field_values = array();
1511  $placeholders = array();
1512  $types = array();
1513  $values = array();
1514  $lobs = false;
1515  $lob = array();
1516  foreach ($a_columns as $k => $col)
1517  {
1518  $fields[] = $k;
1519  $placeholders[] = "%s";
1520  $placeholders2[] = ":$k";
1521  $types[] = $col[0];
1522  $values[] = $col[1];
1523  $field_values[$k] = $col[1];
1524  if ($col[0] == "blob" || $col[0] == "clob")
1525  {
1526  $lobs = true;
1527  $lob[$k] = $k;
1528  }
1529  }
1530 
1531  if ($lobs)
1532  {
1533  $q = "UPDATE ".$a_table." SET ";
1534  $lim = "";
1535  foreach ($fields as $k => $field)
1536  {
1537  $q.= $lim.$field." = ".$placeholders2[$k];
1538  $lim = ", ";
1539  }
1540  $q.= " WHERE ";
1541  $lim = "";
1542  foreach ($a_where as $k => $col)
1543  {
1544  $q.= $lim.$k." = ".$this->quote($col[1], $col[0]);
1545  $lim = " AND ";
1546  }
1547  $st = $this->db->prepare($q, $types, MDB2_PREPARE_MANIP, $lob);
1548  $r = $st->execute($field_values);
1549 
1550  //$r = $this->db->extended->autoExecute($a_table, $field_values, MDB2_AUTOQUERY_INSERT, null, $types);
1551  $this->handleError($r, "update / prepare/execute(".$a_table.")");
1552  $this->free($st);
1553  }
1554  else
1555  {
1556  foreach ($a_where as $k => $col)
1557  {
1558  $types[] = $col[0];
1559  $values[] = $col[1];
1560  $field_values[$k] = $col;
1561  }
1562  $q = "UPDATE ".$a_table." SET ";
1563  $lim = "";
1564  foreach ($fields as $k => $field)
1565  {
1566  $q.= $lim.$field." = ".$placeholders[$k];
1567  $lim = ", ";
1568  }
1569  $q.= " WHERE ";
1570  $lim = "";
1571  foreach ($a_where as $k => $col)
1572  {
1573  $q.= $lim.$k." = %s";
1574  $lim = " AND ";
1575  }
1576 
1577  $r = $this->manipulateF($q, $types, $values);
1578  }
1579  return $r;
1580  }
1581 
1589  function replace($a_table, $a_pk_columns, $a_other_columns)
1590  {
1591  // this is the mysql implementation
1592  $a_columns = array_merge($a_pk_columns, $a_other_columns);
1593  $fields = array();
1594  $field_values = array();
1595  $placeholders = array();
1596  $types = array();
1597  $values = array();
1598  $lobs = false;
1599  $lob = array();
1600  foreach ($a_columns as $k => $col)
1601  {
1602  $fields[] = $k;
1603  $placeholders[] = "%s";
1604  $placeholders2[] = ":$k";
1605  $types[] = $col[0];
1606  $values[] = $col[1];
1607  $field_values[$k] = $col[1];
1608  if ($col[0] == "blob" || $col[0] == "clob")
1609  {
1610  $lobs = true;
1611  $lob[$k] = $k;
1612  }
1613  }
1614  if ($lobs) // lobs -> use prepare execute (autoexecute broken in PEAR 2.4.1)
1615  {
1616  $st = $this->db->prepare("REPLACE INTO ".$a_table." (".implode($fields,",").") VALUES (".
1617  implode($placeholders2,",").")", $types, MDB2_PREPARE_MANIP, $lob);
1618  $r = $st->execute($field_values);
1619  //$r = $this->db->extended->autoExecute($a_table, $field_values, MDB2_AUTOQUERY_INSERT, null, $types);
1620  $this->handleError($r, "insert / prepare/execute(".$a_table.")");
1621  $this->free($st);
1622  }
1623  else // if no lobs are used, take simple manipulateF
1624  {
1625  $q = "REPLACE INTO ".$a_table." (".implode($fields,",").") VALUES (".
1626  implode($placeholders,",").")";
1627  $r = $this->manipulateF($q, $types, $values);
1628  }
1629  return $r;
1630  }
1631 
1637  function fetchAssoc($a_set)
1638  {
1639  return $a_set->fetchRow(DB_FETCHMODE_ASSOC);
1640  }
1641 
1645  function free($a_st)
1646  {
1647  return $a_st->free();
1648  }
1649 
1655  function fetchObject($a_set)
1656  {
1657  return $a_set->fetchRow(DB_FETCHMODE_OBJECT);
1658  }
1659 
1665  function numRows($a_set)
1666  {
1667  return $a_set->numRows();
1668  }
1669 
1670  //
1671  // function and clauses abstraction
1672  //
1673 
1685  function in($a_field, $a_values, $negate = false, $a_type = "")
1686  {
1687  if (count($a_values) == 0)
1688  {
1689  return " 1=2 "; // return a false statement on empty array
1690  }
1691  if ($a_type == "") // untyped: used ? for prepare/execute
1692  {
1693  $str = $a_field.(($negate) ? " NOT" : "")." IN (?".str_repeat(",?", count($a_values) - 1).")";
1694  }
1695  else // typed, use values for query/manipulate
1696  {
1697  $str = $a_field.(($negate) ? " NOT" : "")." IN (";
1698  $sep = "";
1699  foreach ($a_values as $v)
1700  {
1701  $str.= $sep.$this->quote($v, $a_type);
1702  $sep = ",";
1703  }
1704  $str.= ")";
1705  }
1706 
1707  return $str;
1708  }
1709 
1713  function addTypesToArray($a_arr, $a_type, $a_cnt)
1714  {
1715  if (!is_array($a_arr))
1716  {
1717  $a_arr = array();
1718  }
1719  if ($a_cnt > 0)
1720  {
1721  $type_arr = array_fill(0, $a_cnt, $a_type);
1722  }
1723  else
1724  {
1725  $type_arr = array();
1726  }
1727  return array_merge($a_arr, $type_arr);
1728  }
1729 
1734  function now()
1735  {
1736  return "now()";
1737  }
1738 
1739 
1749  public function concat($a_values,$a_allow_null = true)
1750  {
1751  if(!count($a_values))
1752  {
1753  return ' ';
1754  }
1755 
1756  $concat = ' CONCAT(';
1757  $first = true;
1758  foreach($a_values as $field_info)
1759  {
1760  $val = $field_info[0];
1761 
1762  if(!$first)
1763  {
1764  $concat .= ',';
1765  }
1766 
1767  if($a_allow_null)
1768  {
1769  $concat .= 'COALESCE(';
1770  }
1771  $concat .= $val;
1772 
1773  if($a_allow_null)
1774  {
1775  $concat .= ",''";
1776  $concat .= ')';
1777  }
1778 
1779  $first = false;
1780  }
1781  $concat .= ') ';
1782  return $concat;
1783  }
1784 
1791  function substr($a_exp, $a_pos = 1, $a_len = -1)
1792  {
1793  $lenstr = "";
1794  if ($a_len > -1)
1795  {
1796  $lenstr = ", ".$a_len;
1797  }
1798  return " SUBSTR(".$a_exp.", ".$a_pos.$lenstr.") ";
1799  }
1800 
1807  function upper($a_exp)
1808  {
1809  return " UPPER(".$a_exp.") ";
1810  }
1811 
1818  function lower($a_exp)
1819  {
1820  return " LOWER(".$a_exp.") ";
1821  }
1822 
1830  public function locate($a_needle,$a_string,$a_start_pos = 1)
1831  {
1832  $locate = ' LOCATE( ';
1833  $locate .= $a_needle;
1834  $locate .= ',';
1835  $locate .= $a_string;
1836  $locate .= ',';
1837  $locate .= $a_start_pos;
1838  $locate .= ') ';
1839  return $locate;
1840  }
1841 
1842 
1848  function like($a_col, $a_type, $a_value = "?", $case_insensitive = true)
1849  {
1850  if (!in_array($a_type, array("text", "clob", "blob")))
1851  {
1852  $this->raisePearError("Like: Invalid column type '".$a_type."'.", $this->error_class->FATAL);
1853  }
1854  if ($a_value == "?")
1855  {
1856  if ($case_insensitive)
1857  {
1858  return "UPPER(".$a_col.") LIKE(UPPER(?))";
1859  }
1860  else
1861  {
1862  return $a_col ." LIKE(?)";
1863  }
1864  }
1865  else
1866  {
1867  if ($case_insensitive)
1868  {
1869  // Always quote as text
1870  return " UPPER(".$a_col.") LIKE(UPPER(".$this->quote($a_value, 'text')."))";
1871  }
1872  else
1873  {
1874  // Always quote as text
1875  return " ".$a_col." LIKE(".$this->quote($a_value, 'text').")";
1876  }
1877  }
1878  }
1879 
1880 
1884  function equals($a_col, $a_value, $a_type, $a_empty_or_null = false)
1885  {
1886  if (!$a_empty_or_null || $a_value != "")
1887  {
1888  return $a_col." = ".$this->quote($a_value, $a_type);
1889  }
1890  else
1891  {
1892  return "(".$a_col." = '' OR $a_col IS NULL)";
1893  }
1894  }
1895 
1899  function equalsNot($a_col, $a_value, $a_type, $a_empty_or_null = false)
1900  {
1901  if (!$a_empty_or_null)
1902  {
1903  return $a_col." <> ".$this->quote($a_value, $a_type);
1904  }
1905  if ($a_value != "")
1906  {
1907  return "(".$a_col." <> ".$this->quote($a_value, $a_type). " OR ".
1908  $a_col." IS NULL)";
1909  }
1910  else
1911  {
1912  return "(".$a_col." <> '' AND $a_col IS NOT NULL)";
1913  }
1914  }
1915 
1922  function fromUnixtime($a_expr, $a_to_text = true)
1923  {
1924  return "FROM_UNIXTIME(".$a_expr.")";
1925  }
1926 
1930  function unixTimestamp()
1931  {
1932  return "UNIX_TIMESTAMP()";
1933  }
1934 
1938  function optimizeTable($a_table)
1939  {
1940  // needs to be overwritten in DBMS specific class
1941  // if necessary and possible
1942  }
1943 
1944  //
1945  // Schema related functions
1946  //
1947 
1954  function tableExists($a_table)
1955  {
1956  $tables = $this->listTables();
1957 
1958  if (is_array($tables))
1959  {
1960  if (in_array($a_table, $tables))
1961  {
1962  return true;
1963  }
1964  }
1965  return false;
1966  }
1967 
1975  function tableColumnExists($a_table, $a_column_name)
1976  {
1977 
1978  $column_visibility = false;
1979  $manager = $this->db->loadModule('Manager');
1980  $r = $manager->listTableFields($a_table);
1981 
1982  if (!MDB2::isError($r))
1983  {
1984  foreach($r as $field)
1985  {
1986  if ($field == $a_column_name)
1987  {
1988  $column_visibility = true;
1989  }
1990  }
1991  }
1992 
1993  return $column_visibility;
1994  }
1995 
2001  function listTables()
2002  {
2003  $manager = $this->db->loadModule('Manager');
2004  $r = $manager->listTables();
2005 
2006  if (!MDB2::isError($r))
2007  {
2008  return $r;
2009  }
2010 
2011  return false;
2012  }
2013 
2014 
2015  //
2016  // Quote Functions
2017  //
2018 
2022  function quote($a_query, $a_type = null)
2023  {
2024  if ($a_query == "" && is_null($a_type))
2025  {
2026  $a_query = "";
2027  }
2028  // Performance fix
2029 
2030  if($a_type == 'integer' && !is_null($a_query))
2031  {
2032  return (int) $a_query;
2033  }
2034  if ($a_type == "blob" || $a_type == "clob")
2035  {
2036  $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);
2037  }
2038 
2039  return $this->db->quote($a_query, $a_type);
2040  }
2041 
2049  function quoteIdentifier($a_identifier)
2050  {
2051  return $this->db->quoteIdentifier($a_identifier);
2052  }
2053 
2054  //
2055  // Transaction and Locking methods
2056  //
2057 
2063  function beginTransaction()
2064  {
2065  if (!$this->db->supports('transactions'))
2066  {
2067  $this->raisePearError("ilDB::beginTransaction: Transactions are not supported.", $this->error_class->FATAL);
2068  }
2069  $res = $this->db->beginTransaction();
2070 
2071  return $this->handleError($res, "beginTransaction()");
2072  }
2073 
2077  function commit()
2078  {
2079  $res = $this->db->commit();
2080 
2081  return $this->handleError($res, "commit()");
2082  }
2083 
2087  function rollback()
2088  {
2089  $res = $this->db->rollback();
2090 
2091  return $this->handleError($res, "rollback()");
2092  }
2093 
2099  abstract public function lockTables($a_tables);
2100 
2105  abstract public function unlockTables();
2106 
2107 
2108 //
2109 //
2110 // Older functions. Must be checked.
2111 //
2112 //
2113 
2122  function autoExecute($a_tablename,$a_fields,$a_mode = MDB2_AUTOQUERY_INSERT,$a_where = false)
2123  {
2124  $res = $this->db->autoExecute($a_tablename,$a_fields,$a_mode,$a_where);
2125 
2126  return $this->handleError($res, "autoExecute(".$a_tablename.")");
2127  }
2128 
2129 //
2130 //
2131 // Deprecated functions.
2132 //
2133 //
2134 
2138  function getLastInsertId()
2139  {
2140  $res = $this->db->lastInsertId();
2141  if(MDB2::isError($res))
2142  {
2143  return false;
2144  }
2145  return $res;
2146  }
2147 
2157  function getOne($sql)
2158  {
2159  //$r = $this->db->getOne($sql);
2160  $set = $this->db->query($sql);
2161 
2162  $this->handleError($set, "getOne(".$sql.")");
2163 
2164  if (!MDB2::isError($set))
2165  {
2166  $r = $set->fetchRow(DB_FETCHMODE_ASSOC);
2167 
2168  return $r[0];
2169  }
2170  }
2171 
2181  function getRow($sql,$mode = DB_FETCHMODE_OBJECT)
2182  {
2183  $set = $this->query($sql);
2184  $r = $set->fetchRow($mode);
2185  //$r = $this->db->getrow($sql,$mode);
2186 
2187  $this->handleError($r, "getRow(".$sql.")");
2188 
2189  return $r;
2190  } //end function
2191 
2192 } //end Class
2193 ?>