ILIAS  Release_4_2_x_branch Revision 61807
 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 
202 
207  public function enableResultBuffering($a_status)
208  {
209  $this->db->setOption('result_buffering',$a_status);
210  }
211 
216  function initFromIniFile($tmpClientIniFile = null)
217  {
218  global $ilClientIniFile;
219 
220  //overwrite global client ini file if local parameter is set
221  if (is_object($tmpClientIniFile))
222  $clientIniFile = $tmpClientIniFile;
223  else
224  $clientIniFile = $ilClientIniFile;
225 
226  if (is_object($clientIniFile ))
227  {
228  $this->setDBUser($clientIniFile ->readVariable("db", "user"));
229  $this->setDBHost($clientIniFile ->readVariable("db", "host"));
230  $this->setDBPort($clientIniFile ->readVariable("db", "port"));
231  $this->setDBPassword($clientIniFile ->readVariable("db", "pass"));
232  $this->setDBName($clientIniFile ->readVariable("db", "name"));
233  }
234  }
235 
239  function connect($a_return_false_for_error = false)
240  {
241  //set up error handling
242  $this->error_class = new ilErrorHandling();
243  $this->setErrorHandling(PEAR_ERROR_CALLBACK, array($this->error_class,'errorHandler'));
244 //echo $this->getDSN();
245  //check dsn
246  if ($this->getDSN() == "")
247  {
248  $this->raisePearError("No DSN given");
249  }
250 
251  //connect to database
252  $this->doConnect();
253 
254  if ($a_return_false_for_error && MDB2::isError($this->db))
255  {
256  return false;
257  }
258 
259  $this->loadMDB2Extensions();
260 
261  // set empty value portability to PEAR::DB behaviour
262  if (!$this->isDbError($this->db))
263  {
264  $this->db->setOption('portability', MDB2_PORTABILITY_ALL);
265  }
266  //check error
267  $this->handleError($this->db);
268 
269  // anything, that must be done to initialize the connection
270  $this->initConnection();
271 
272  return true;
273  }
274 
278  function doConnect()
279  {
280  $this->db = MDB2::connect($this->getDSN(),
281  array("use_transactions" => true));
282  }
283 
287  function disconnect()
288  {
289  $this->db->disconnect();
290  }
291 
292  //
293  // General and MDB2 related functions
294  //
295 
299  protected function initConnection()
300  {
301  }
302 
309  function getHostDSN()
310  {
311  return false;
312  }
313 
318  function connectHost()
319  {
320  //set up error handling
321  $this->error_class = new ilErrorHandling();
322  $this->setErrorHandling(PEAR_ERROR_CALLBACK, array($this->error_class,'errorHandler'));
323 
324  //check dsn
325  if ($this->getHostDSN() == "")
326  {
327  $this->raisePearError("No Host DSN given");
328  }
329 
330  //connect to database
331  $this->db = MDB2::connect($this->getHostDSN(),
332  array("use_transactions" => true));
333  if ($a_return_false_for_error && MDB2::isError($this->db))
334  {
335  return false;
336  }
337 
338  $this->loadMDB2Extensions();
339 
340  // set empty value portability to PEAR::DB behaviour
341  if (!$this->isDbError($this->db))
342  {
343  $cur = ($this->db->getOption("portability") & MDB2_PORTABILITY_EMPTY_TO_NULL);
344  $this->db->setOption("portability", $this->db->getOption("portability") - $cur);
345 
346  $cur = ($this->db->getOption("portability") & MDB2_PORTABILITY_FIX_CASE);
347  $this->db->setOption("portability", $this->db->getOption("portability") - $cur);
348  }
349 
350  //check error
351  $this->handleError($this->db);
352 
353  // anything, that must be done to initialize the connection
354  $this->initHostConnection();
355 
356  return true;
357  }
358 
362  protected function initHostConnection()
363  {
364  }
365 
366  function supportsFulltext()
367  {
368  return false;
369  }
370 
377  function handleError($a_res, $a_info = "", $a_level = "")
378  {
379  global $ilLog;
380 
381  if (MDB2::isError($a_res))
382  {
383  if ($a_level == "")
384  {
385  $a_level = $this->error_class->FATAL;
386  }
387 
388  // Show stack
389  try
390  {
391  throw new Exception();
392  }
393  catch(Exception $e)
394  {
395  $stack = $e->getTraceAsString();
396  }
397 
398  if(is_object($ilLog))
399  $ilLog->logStack();
400  $this->raisePearError("ilDB Error: ".$a_info."<br />".
401  $a_res->getMessage()."<br />".$a_res->getUserInfo()."<br />".$stack, $a_level);
402 
403  }
404 
405  return $a_res;
406  }
407 
411  function raisePearError($a_message, $a_level = "")
412  {
413  if ($a_level == "")
414  {
415  $a_level = $this->error_class->FATAL;
416  }
417 //echo "<br>-ilDB:raising-$a_message-$a_level-";
418  $this->raiseError($a_message, $a_level);
419  }
420 
426  protected function loadMDB2Extensions()
427  {
428  if (!$this->isDbError($this->db))
429  {
430  $this->db->loadModule('Extended');
431  define('DB_AUTOQUERY_SELECT',MDB2_AUTOQUERY_SELECT);
432  define('DB_AUTOQUERY_INSERT',MDB2_AUTOQUERY_INSERT);
433  define('DB_AUTOQUERY_UPDATE',MDB2_AUTOQUERY_UPDATE);
434  define('DB_AUTOQUERY_DELETE',MDB2_AUTOQUERY_DELETE);
435  }
436  }
437 
441  static function isDbError($a_res)
442  {
443  return MDB2::isError($a_res);
444  }
445 
446  //
447  // Data Definition Methods
448  //
449 
453  function createDatabase($a_name, $a_charset = "utf8", $a_collation = "")
454  {
455  if ($a_collation != "")
456  {
457  $sql = "CREATE DATABASE ".$a_name.
458  " CHARACTER SET ".$a_charset.
459  " COLLATE ".$a_collation;
460  }
461  else
462  {
463  $sql = "CREATE DATABASE ".$a_name.
464  " CHARACTER SET ".$a_charset;
465  }
466 
467  return $this->query($sql, false);
468  }
469 
470 
478  function createTable($a_name, $a_definition_array, $a_drop_table = false,
479  $a_ignore_erros = false)
480  {
481  // (removed options; should only be activated restricted, if necessary
482  if ($a_options == "")
483  {
484  $a_options = array();
485  }
486 
487  // check table name
488  if (!$this->checkTableName($a_name) && !$a_ignore_erros)
489  {
490  $this->raisePearError("ilDB Error: createTable(".$a_name.")<br />".
491  $this->error_str);
492  }
493 
494  // check definition array
495  if (!$this->checkTableColumns($a_definition_array) && !$a_ignore_erros)
496  {
497  $this->raisePearError("ilDB Error: createTable(".$a_name.")<br />".
498  $this->error_str);
499  }
500 
501  if ($a_drop_table)
502  {
503  $this->dropTable($a_name, false);
504  }
505 
506  $manager = $this->db->loadModule('Manager');
507  $r = $manager->createTable($a_name, $a_definition_array, $a_options);
508 
509  return $this->handleError($r, "createTable(".$a_name.")");
510  }
511 
518  function dropTable($a_name, $a_error_if_not_existing = true)
519  {
520  if (!$a_error_if_not_existing)
521  {
522  $tables = $this->listTables();
523  if (!in_array($a_name, $tables))
524  {
525  return;
526  }
527  }
528 
529  $manager = $this->db->loadModule('Manager');
530 
531  if ($this->getDBType() == "oracle")
532  {
533  // drop table constraints
534  $constraints = $manager->listTableConstraints($a_name);
535  $this->handleError($constraints, "dropTable(".$a_name."), listTableConstraints");
536  foreach ($constraints as $c)
537  {
538  if (substr($c, 0, 4) != "sys_")
539  {
540  $r = $manager->dropConstraint($a_name, $c);
541  $this->handleError($r, "dropTable(".$a_name."), dropConstraint");
542  }
543  }
544 
545  // drop table indexes
546  $indexes = $manager->listTableIndexes($a_name);
547  $this->handleError($indexes, "dropTable(".$a_name."), listTableIndexes");
548  foreach ($indexes as $i)
549  {
550  $r = $manager->dropIndex($a_name, $i);
551  $this->handleError($r, "dropTable(".$a_name."), dropIndex");
552  }
553  }
554 
555  // drop sequence
556  $seqs = $manager->listSequences();
557  if (in_array($a_name, $seqs))
558  {
559  $r = $manager->dropSequence($a_name);
560  $this->handleError($r, "dropTable(".$a_name."), dropSequence");
561  }
562 
563  // drop table
564  $r = $manager->dropTable($a_name);
565 
566  return $this->handleError($r, "dropTable(".$a_name.")");
567  }
568 
574  function alterTable($a_name, $a_changes)
575  {
576  if ($a_options == "")
577  {
578  $a_options = array();
579  }
580 
581  $manager = $this->db->loadModule('Manager');
582  $r = $manager->alterTable($a_name, $a_changes, false);
583 
584  return $this->handleError($r, "alterTable(".$a_name.")");
585  }
586 
595  function addTableColumn($a_table, $a_column, $a_attributes)
596  {
597 
598  $manager = $this->db->loadModule('Manager');
599 
600  if (!$this->checkColumnName($a_column))
601  {
602  $this->raisePearError("ilDB Error: addTableColumn(".$a_table.", ".$a_column.")<br />".
603  $this->error_str);
604  }
605  if (!$this->checkColumnDefinition($a_attributes))
606  {
607  $this->raisePearError("ilDB Error: addTableColumn(".$a_table.", ".$a_column.")<br />".
608  $this->error_str);
609  }
610 
611  $changes = array(
612  "add" => array(
613  $a_column => $a_attributes
614  )
615  );
616 
617  $r = $manager->alterTable($a_table, $changes, false);
618 
619  return $this->handleError($r, "addTableColumn(".$a_table.", ".$a_column.")");
620  }
621 
629  function dropTableColumn($a_table, $a_column)
630  {
631 
632  $manager = $this->db->loadModule('Manager');
633 
634  $changes = array(
635  "remove" => array(
636  $a_column => array()
637  )
638  );
639 
640  $r = $manager->alterTable($a_table, $changes, false);
641 
642  return $this->handleError($r, "dropTableColumn(".$a_table.", ".$a_column.")");
643  }
644 
653  function modifyTableColumn($a_table, $a_column, $a_attributes)
654  {
655  $manager = $this->db->loadModule('Manager');
656  $reverse = $this->db->loadModule('Reverse');
657  $def = $reverse->getTableFieldDefinition($a_table, $a_column);
658 
659  $this->handleError($def, "modifyTableColumn(".$a_table.")");
660 
661  if (is_file("./Services/Database/classes/class.ilDBAnalyzer.php"))
662  {
663  include_once("./Services/Database/classes/class.ilDBAnalyzer.php");
664  }
665  else
666  {
667  include_once("../Services/Database/classes/class.ilDBAnalyzer.php");
668  }
669  $analyzer = new ilDBAnalyzer();
670  $best_alt = $analyzer->getBestDefinitionAlternative($def);
671  $def = $def[$best_alt];
672  unset($def["nativetype"]);
673  unset($def["mdb2type"]);
674 
675  // check attributes
676  $type = ($a_attributes["type"] != "")
677  ? $a_attributes["type"]
678  : $def["type"];
679  foreach ($def as $k => $v)
680  {
681  if ($k != "type" && !in_array($k, $this->allowed_attributes[$type]))
682  {
683  unset($def[$k]);
684  }
685  }
686  $check_array = $def;
687  foreach ($a_attributes as $k => $v)
688  {
689  $check_array[$k] = $v;
690  }
691  if (!$this->checkColumnDefinition($check_array, true))
692  {
693  $this->raisePearError("ilDB Error: modifyTableColumn(".$a_table.", ".$a_column.")<br />".
694  $this->error_str);
695  }
696 
697  // oracle workaround: do not set null, if null already given
698  if ($this->getDbType() == "oracle")
699  {
700  if ($def["notnull"] == true && ($a_attributes["notnull"] == true
701  || !isset($a_attributes["notnull"])))
702  {
703  unset($def["notnull"]);
704  unset($a_attributes["notnull"]);
705  }
706  if ($def["notnull"] == false && ($a_attributes["notnull"] == false
707  || !isset($a_attributes["notnull"])))
708  {
709  unset($def["notnull"]);
710  unset($a_attributes["notnull"]);
711  }
712  }
713  foreach ($a_attributes as $a => $v)
714  {
715  $def[$a] = $v;
716  }
717 
718  $a_attributes["definition"] = $def;
719 
720  $changes = array(
721  "change" => array(
722  $a_column => $a_attributes
723  )
724  );
725 
726  $r = $manager->alterTable($a_table, $changes, false);
727 
728  return $this->handleError($r, "modifyTableColumn(".$a_table.")");
729  }
730 
739  function renameTableColumn($a_table, $a_column, $a_new_column)
740  {
741  // check table name
742  if (!$this->checkColumnName($a_new_column))
743  {
744  $this->raisePearError("ilDB Error: renameTableColumn(".$a_table.",".$a_column.",".$a_new_column.")<br />".
745  $this->error_str);
746  }
747 
748  $manager = $this->db->loadModule('Manager');
749  $reverse = $this->db->loadModule('Reverse');
750  $def = $reverse->getTableFieldDefinition($a_table, $a_column);
751 
752  $this->handleError($def, "renameTableColumn(".$a_table.",".$a_column.",".$a_new_column.")");
753 
754  if (is_file("./Services/Database/classes/class.ilDBAnalyzer.php"))
755  {
756  include_once("./Services/Database/classes/class.ilDBAnalyzer.php");
757  }
758  else
759  {
760  include_once("../Services/Database/classes/class.ilDBAnalyzer.php");
761  }
762 
763  $analyzer = new ilDBAnalyzer();
764  $best_alt = $analyzer->getBestDefinitionAlternative($def);
765  $def = $def[$best_alt];
766  unset($def["nativetype"]);
767  unset($def["mdb2type"]);
768 
769  $f["definition"] = $def;
770  $f["name"] = $a_new_column;
771 
772  $changes = array(
773  "rename" => array(
774  $a_column => $f
775  )
776  );
777 
778  $r = $manager->alterTable($a_table, $changes, false);
779 
780  return $this->handleError($r, "renameTableColumn(".$a_table.",".$a_column.",".$a_new_column.")");
781  }
782 
789  function renameTable($a_name, $a_new_name)
790  {
791  // check table name
792  if (!$this->checkTableName($a_new_name))
793  {
794  $this->raisePearError("ilDB Error: renameTable(".$a_name.",".$a_new_name.")<br />".
795  $this->error_str);
796  }
797 
798  $manager = $this->db->loadModule('Manager');
799  $r = $manager->alterTable($a_name, array("name" => $a_new_name), false);
800 
801  $query = "UPDATE abstraction_progress ".
802  "SET table_name = ".$this->db->quote($a_new_name,'text')." ".
803  "WHERE table_name = ".$this->db->quote($a_name,'text');
804  $this->db->query($query);
805 
806  return $this->handleError($r, "renameTable(".$a_name.",".$a_new_name.")");
807  }
808 
816  function addPrimaryKey($a_table, $a_fields)
817  {
818  $manager = $this->db->loadModule('Manager');
819 
820  $fields = array();
821  foreach ($a_fields as $f)
822  {
823  $fields[$f] = array();
824  }
825  $definition = array (
826  'primary' => true,
827  'fields' => $fields
828  );
829  $r = $manager->createConstraint($a_table,
830  $this->constraintName($a_table, $this->getPrimaryKeyIdentifier()), $definition);
831 
832  return $this->handleError($r, "addPrimaryKey(".$a_table.")");
833  }
834 
838  function getPrimaryKeyIdentifier()
839  {
840  return "PRIMARY";
841  }
842 
849  function dropPrimaryKey($a_table)
850  {
851  $manager = $this->db->loadModule('Manager');
852 
853  $r = $manager->dropConstraint($a_table,
854  $this->constraintName($a_table, $this->getPrimaryKeyIdentifier()), true);
855 
856  return $this->handleError($r, "dropPrimaryKey(".$a_table.")");
857  }
858 
866  function addIndex($a_table, $a_fields, $a_name = "in", $a_fulltext = false)
867  {
868  $manager = $this->db->loadModule('Manager');
869 
870  // check index name
871  if (!$this->checkIndexName($a_name))
872  {
873  $this->raisePearError("ilDB Error: addIndex(".$a_table.",".$a_name.")<br />".
874  $this->error_str);
875  }
876 
877  $fields = array();
878  foreach ($a_fields as $f)
879  {
880  $fields[$f] = array();
881  }
882  $definition = array (
883  'fields' => $fields
884  );
885 
886  if (!$a_fulltext)
887  {
888  $r = $manager->createIndex($a_table, $this->constraintName($a_table, $a_name), $definition);
889  }
890  else
891  {
892  if ($this->supportsFulltext())
893  {
894  $this->addFulltextIndex($a_table, $a_fields, $a_name);
895  }
896  }
897 
898  return $this->handleError($r, "addIndex(".$a_table.")");
899  }
900 
904  function addFulltextIndex($a_table, $a_fields, $a_name = "in")
905  {
906  return false;
907  }
908 
912  function isFulltextIndex($a_table, $a_name)
913  {
914  return false;
915  }
916 
924  function dropIndex($a_table, $a_name = "in")
925  {
926  $manager = $this->db->loadModule('Manager');
927 
928  if (!$this->isFulltextIndex($a_table, $a_name))
929  {
930  $r = $manager->dropIndex($a_table, $this->constraintName($a_table, $a_name));
931  }
932  else
933  {
934  $this->dropFulltextIndex($a_table, $a_name);
935  }
936 
937  return $this->handleError($r, "dropIndex(".$a_table.")");
938  }
939 
947  function addUniqueConstraint($a_table, $a_fields, $a_name = "con")
948  {
949  $manager = $this->db->loadModule('Manager');
950 
951  // check index name
952  if (!$this->checkIndexName($a_name))
953  {
954  $this->raisePearError("ilDB Error: addUniqueConstraint(".$a_table.",".$a_name.")<br />".
955  $this->error_str);
956  }
957 
958  $fields = array();
959  foreach ($a_fields as $f)
960  {
961  $fields[$f] = array();
962  }
963  $definition = array (
964  'unique' => true,
965  'fields' => $fields
966  );
967 
968  $r = $manager->createConstraint($a_table, $this->constraintName($a_table, $a_name), $definition);
969 
970  return $this->handleError($r, "addUniqueConstraint(".$a_table.")");
971  }
972 
976  function createSequence($a_table_name, $a_start = 1)
977  {
978  $manager = $this->db->loadModule('Manager');
979 
980  $r = $manager->createSequence($a_table_name, $a_start);
981 
982  return $this->handleError($r, "createSequence(".$a_table_name.")");
983  }
984 
985 
989  function dropSequence($a_table_name)
990  {
991  $manager = $this->db->loadModule('Manager');
992 
993  $r = $manager->dropSequence($a_table_name);
994 
995  return $this->handleError($r, "dropSequence(".$a_table_name.")");
996  }
997 
1003  function checkTableName($a_name)
1004  {
1005  if (!preg_match ("/^[a-z]+[_a-z0-9]*$/", $a_name))
1006  {
1007  $this->error_str = "Table name must only contain _a-z0-9 and must start with a-z.";
1008  return false;
1009  }
1010 
1011  if ($this->isReservedWord($a_name))
1012  {
1013  $this->error_str = "Invalid table name '".$a_name."' (Reserved Word).";
1014  return false;
1015  }
1016 
1017  if (strtolower(substr($a_name, 0, 4)) == "sys_")
1018  {
1019  $this->error_str = "Invalid table name '".$a_name."'. Name must not start with 'sys_'.";
1020  return false;
1021  }
1022 
1023  if (strlen($a_name) > 22)
1024  {
1025  $this->error_str = "Invalid table name '".$a_name."'. Maximum table identifer lenght is 22 bytes.";
1026  return false;
1027  }
1028 
1029  return true;
1030  }
1031 
1037  function checkTableColumns($a_cols)
1038  {
1039  foreach ($a_cols as $col => $def)
1040  {
1041  if (!$this->checkColumn($col, $def))
1042  {
1043  return false;
1044  }
1045  }
1046 
1047  return true;
1048  }
1049 
1053  function checkColumn($a_col, $a_def)
1054  {
1055  if (!$this->checkColumnName($a_col))
1056  {
1057  return false;
1058  }
1059 
1060  if (!$this->checkColumnDefinition($a_def))
1061  {
1062  return false;
1063  }
1064 
1065  return true;
1066  }
1067 
1073  function checkColumnDefinition($a_def, $a_modify_mode = false)
1074  {
1075  // check valid type
1076  if (!in_array($a_def["type"], array("text", "integer", "float", "date", "time", "timestamp", "clob", "blob")))
1077  {
1078  switch ($a_def["type"])
1079  {
1080  case "boolean":
1081  $this->error_str = "Invalid column type '".$a_def["type"]."'. Use integer(1) instead.";
1082  break;
1083 
1084  case "decimal":
1085  $this->error_str = "Invalid column type '".$a_def["type"]."'. Use float or integer instead.";
1086  break;
1087 
1088  default:
1089  $this->error_str = "Invalid column type '".$a_def["type"]."'. Allowed types are: ".
1090  "text, integer, float, date, time, timestamp, clob and blob.";
1091  }
1092  }
1093 
1094  // check used attributes
1095  $allowed_attributes = $this->allowed_attributes;
1096 
1097  foreach ($a_def as $k => $v)
1098  {
1099  if ($k != "type" && !in_array($k, $allowed_attributes[$a_def["type"]]))
1100  {
1101  $this->error_str = "Attribute '".$k."' is not allowed for column type '".$a_def["type"]."'.";
1102  return false;
1103  }
1104  }
1105 
1106  // type specific checks
1107  switch ($a_def["type"])
1108  {
1109  case "text":
1110  if ($a_def["length"] < 1 || $a_def["length"] > 4000)
1111  {
1112  if (!$a_modify_mode || isset($a_def["length"]))
1113  {
1114  $this->error_str = "Invalid length '".$a_def["length"]."' for type text.".
1115  " Length must be >=1 and <= 4000.";
1116  return false;
1117  }
1118  }
1119  break;
1120 
1121  case "integer":
1122  if (!in_array($a_def["length"], array(1, 2, 3, 4, 8)))
1123  {
1124  if (!$a_modify_mode || isset($a_def["length"]))
1125  {
1126  $this->error_str = "Invalid length '".$a_def["length"]."' for type integer.".
1127  " Length must be 1, 2, 3, 4 or 8 (bytes).";
1128  return false;
1129  }
1130  }
1131  if ($a_def["unsigned"])
1132  {
1133  $this->error_str = "Unsigned attribut must not be true for type integer.";
1134  return false;
1135  }
1136  break;
1137  }
1138 
1139  return true;
1140  }
1141 
1147  function checkColumnName($a_name)
1148  {
1149  if (!preg_match ("/^[a-z]+[_a-z0-9]*$/", $a_name))
1150  {
1151  $this->error_str = "Invalid column name '".$a_name."'. Column name must only contain _a-z0-9 and must start with a-z.";
1152  return false;
1153  }
1154 
1155  if ($this->isReservedWord($a_name))
1156  {
1157  $this->error_str = "Invalid column name '".$a_name."' (Reserved Word).";
1158  return false;
1159  }
1160 
1161  if (strtolower(substr($a_name, 0, 4)) == "sys_")
1162  {
1163  $this->error_str = "Invalid column name '".$a_name."'. Name must not start with 'sys_'.";
1164  return false;
1165  }
1166 
1167  if (strlen($a_name) > 30)
1168  {
1169  $this->error_str = "Invalid column name '".$a_name."'. Maximum column identifer lenght is 30 bytes.";
1170  return false;
1171  }
1172 
1173  return true;
1174  }
1175 
1181  function checkIndexName($a_name)
1182  {
1183  if (!preg_match ("/^[a-z]+[_a-z0-9]*$/", $a_name))
1184  {
1185  $this->error_str = "Invalid column name '".$a_name."'. Column name must only contain _a-z0-9 and must start with a-z.";
1186  return false;
1187  }
1188 
1189  if ($this->isReservedWord($a_name))
1190  {
1191  $this->error_str = "Invalid column name '".$a_name."' (Reserved Word).";
1192  return false;
1193  }
1194 
1195  if (strlen($a_name) > 3)
1196  {
1197  $this->error_str = "Invalid index name '".$a_name."'. Maximum index identifer lenght is 3 bytes.";
1198  return false;
1199  }
1200 
1201  return true;
1202  }
1203 
1204  function getAllowedAttributes()
1205  {
1206  return $this->allowed_attributes;
1207  }
1208 
1214  function constraintName($a_table, $a_constraint)
1215  {
1216  return $a_constraint;
1217  }
1218 
1223  static function isReservedWord($a_word)
1224  {
1225  include_once("./Services/Database/classes/class.ilDBMySQL.php");
1226  $mysql_reserved_words = ilDBMySQL::getReservedWords();
1227  if (in_array(strtoupper($a_word), $mysql_reserved_words))
1228  {
1229  return true;
1230  }
1231  include_once("./Services/Database/classes/class.ilDBOracle.php");
1232  $oracle_reserved_words = ilDBOracle::getReservedWords();
1233  if (in_array(strtoupper($a_word), $oracle_reserved_words))
1234  {
1235  return true;
1236  }
1237  include_once("./Services/Database/classes/class.ilDBPostgreSQL.php");
1238  $postgres_reserved_words = ilDBPostgreSQL::getReservedWords();
1239  if (in_array(strtoupper($a_word), $postgres_reserved_words))
1240  {
1241  return true;
1242  }
1243  }
1244 
1245  //
1246  // Data query and manupilation functions
1247  //
1248 
1260  function query($sql, $a_handle_error = true)
1261  {
1262  global $ilBench;
1263 
1264  if (is_object($ilBench))
1265  {
1266  $ilBench->startDbBench($sql);
1267  }
1268  $r = $this->db->query($sql);
1269  if (is_object($ilBench))
1270  {
1271  $ilBench->stopDbBench();
1272  }
1273 
1274  if ($a_handle_error)
1275  {
1276  return $this->handleError($r, "query(".$sql.")");
1277  }
1278 
1279  return $r;
1280  }
1281 
1289  function queryF($a_query, $a_types, $a_values)
1290  {
1291  if (!is_array($a_types) || !is_array($a_values) ||
1292  count($a_types) != count($a_values))
1293  {
1294  $this->raisePearError("ilDB::queryF: Types and values must be arrays of same size. ($a_query)");
1295  }
1296  $quoted_values = array();
1297  foreach($a_types as $k => $t)
1298  {
1299  $quoted_values[] = $this->quote($a_values[$k], $t);
1300  }
1301  $query = vsprintf($a_query, $quoted_values);
1302 
1303  return $this->query($query);
1304  }
1305 
1313  function manipulateF($a_query, $a_types, $a_values)
1314  {
1315  if (!is_array($a_types) || !is_array($a_values) ||
1316  count($a_types) != count($a_values))
1317  {
1318  $this->raisePearError("ilDB::manipulateF: types and values must be arrays of same size. ($a_query)");
1319  }
1320  $quoted_values = array();
1321  foreach($a_types as $k => $t)
1322  {
1323  $quoted_values[] = $this->quote($a_values[$k], $t);
1324  }
1325  $query = vsprintf($a_query, $quoted_values);
1326 
1327  return $this->manipulate($query);
1328  }
1329 
1333  function logStatement($sql)
1334  {
1335  $pos1 = strpos(strtolower($sql), "from ");
1336  $table = "";
1337  if ($pos1 > 0)
1338  {
1339  $tablef = substr($sql, $pos1+5);
1340  $pos2 = strpos(strtolower($tablef), " ");
1341  if ($pos2 > 0)
1342  {
1343  $table =substr($tablef, 0, $pos2);
1344  }
1345  else
1346  {
1347  $table = $tablef;
1348  }
1349  }
1350  if (trim($table) != "")
1351  {
1352  if (!is_array($this->ttt) || !in_array($table, $this->ttt))
1353  {
1354  echo "<br>".$table;
1355  $this->ttt[] = $table;
1356  }
1357  }
1358  else
1359  {
1360  echo "<br><b>".$sql."</b>";
1361  }
1362  }
1363 
1367  function setLimit($a_limit, $a_offset = 0)
1368  {
1369  $this->db->setLimit($a_limit, $a_offset);
1370  }
1371 
1375  function nextId($a_table_name)
1376  {
1377  // we do not create missing sequences automatically here
1378  // otherwise misspelled statements result in additional tables
1379  // please create sequences explicitly in the db update script
1380  $r = $this->db->nextId($a_table_name, false);
1381 
1382  return $this->handleError($r, "nextId(".$a_table_name.")");
1383  }
1384 
1395  function manipulate($sql)
1396  {
1397  $r = $this->db->exec($sql);
1398 
1399  return $this->handleError($r, "manipulate(".$sql.")");
1400  }
1401 
1410  function prepare($a_query, $a_types = null, $a_result_types = null)
1411  {
1412  $res = $this->db->prepare($a_query, $a_types, $a_result_types);
1413 
1414  return $this->handleError($res, "prepare(".$a_query.")");
1415  }
1416 
1425  function prepareManip($a_query, $a_types = null)
1426  {
1427  $res = $this->db->prepare($a_query, $a_types, MDB2_PREPARE_MANIP);
1428 
1429  return $this->handleError($res, "prepareManip(".$a_query.")");
1430  }
1431 
1440  function execute($a_stmt, $a_data = null)
1441  {
1442  $res = $a_stmt->execute($a_data);
1443 
1444  return $this->handleError($res, "execute(".$a_stmt->query.")");
1445  }
1446 
1456  function executeMultiple($a_stmt, $a_data)
1457  {
1458  $res = $this->db->extended->executeMultiple($a_stmt,$a_data);
1459 
1460  return $this->handleError($res, "executeMultiple(".$a_stmt->query.")");
1461  }
1462 
1469  function insert($a_table, $a_columns)
1470  {
1471  $fields = array();
1472  $field_values = array();
1473  $placeholders = array();
1474  $types = array();
1475  $values = array();
1476  $lobs = false;
1477  $lob = array();
1478  foreach ($a_columns as $k => $col)
1479  {
1480  $fields[] = $k;
1481  $placeholders[] = "%s";
1482  $placeholders2[] = ":$k";
1483  $types[] = $col[0];
1484  $values[] = $col[1];
1485  $field_values[$k] = $col[1];
1486  if ($col[0] == "blob" || $col[0] == "clob")
1487  {
1488  $lobs = true;
1489  $lob[$k] = $k;
1490  }
1491  }
1492  if ($lobs) // lobs -> use prepare execute (autoexecute broken in PEAR 2.4.1)
1493  {
1494  $st = $this->db->prepare("INSERT INTO ".$a_table." (".implode($fields,",").") VALUES (".
1495  implode($placeholders2,",").")", $types, MDB2_PREPARE_MANIP, $lob);
1496 
1497  $this->handleError($st, "insert / prepare/execute(".$a_table.")");
1498 
1499  $r = $st->execute($field_values);
1500 
1501 
1502  //$r = $this->db->extended->autoExecute($a_table, $field_values, MDB2_AUTOQUERY_INSERT, null, $types);
1503  $this->handleError($r, "insert / prepare/execute(".$a_table.")");
1504  $this->free($st);
1505  }
1506  else // if no lobs are used, take simple manipulateF
1507  {
1508  $q = "INSERT INTO ".$a_table." (".implode($fields,",").") VALUES (".
1509  implode($placeholders,",").")";
1510  $r = $this->manipulateF($q, $types, $values);
1511  }
1512  return $r;
1513  }
1514 
1523  function update($a_table, $a_columns, $a_where)
1524  {
1525  $fields = array();
1526  $field_values = array();
1527  $placeholders = array();
1528  $types = array();
1529  $values = array();
1530  $lobs = false;
1531  $lob = array();
1532  foreach ($a_columns as $k => $col)
1533  {
1534  $fields[] = $k;
1535  $placeholders[] = "%s";
1536  $placeholders2[] = ":$k";
1537  $types[] = $col[0];
1538  $values[] = $col[1];
1539  $field_values[$k] = $col[1];
1540  if ($col[0] == "blob" || $col[0] == "clob")
1541  {
1542  $lobs = true;
1543  $lob[$k] = $k;
1544  }
1545  }
1546 
1547  if ($lobs)
1548  {
1549  $q = "UPDATE ".$a_table." SET ";
1550  $lim = "";
1551  foreach ($fields as $k => $field)
1552  {
1553  $q.= $lim.$field." = ".$placeholders2[$k];
1554  $lim = ", ";
1555  }
1556  $q.= " WHERE ";
1557  $lim = "";
1558  foreach ($a_where as $k => $col)
1559  {
1560  $q.= $lim.$k." = ".$this->quote($col[1], $col[0]);
1561  $lim = " AND ";
1562  }
1563  $st = $this->db->prepare($q, $types, MDB2_PREPARE_MANIP, $lob);
1564  $r = $st->execute($field_values);
1565 
1566  //$r = $this->db->extended->autoExecute($a_table, $field_values, MDB2_AUTOQUERY_INSERT, null, $types);
1567  $this->handleError($r, "update / prepare/execute(".$a_table.")");
1568  $this->free($st);
1569  }
1570  else
1571  {
1572  foreach ($a_where as $k => $col)
1573  {
1574  $types[] = $col[0];
1575  $values[] = $col[1];
1576  $field_values[$k] = $col;
1577  }
1578  $q = "UPDATE ".$a_table." SET ";
1579  $lim = "";
1580  foreach ($fields as $k => $field)
1581  {
1582  $q.= $lim.$field." = ".$placeholders[$k];
1583  $lim = ", ";
1584  }
1585  $q.= " WHERE ";
1586  $lim = "";
1587  foreach ($a_where as $k => $col)
1588  {
1589  $q.= $lim.$k." = %s";
1590  $lim = " AND ";
1591  }
1592 
1593  $r = $this->manipulateF($q, $types, $values);
1594  }
1595  return $r;
1596  }
1597 
1605  function replace($a_table, $a_pk_columns, $a_other_columns)
1606  {
1607  // this is the mysql implementation
1608  $a_columns = array_merge($a_pk_columns, $a_other_columns);
1609  $fields = array();
1610  $field_values = array();
1611  $placeholders = array();
1612  $types = array();
1613  $values = array();
1614  $lobs = false;
1615  $lob = array();
1616  foreach ($a_columns as $k => $col)
1617  {
1618  $fields[] = $k;
1619  $placeholders[] = "%s";
1620  $placeholders2[] = ":$k";
1621  $types[] = $col[0];
1622  $values[] = $col[1];
1623  $field_values[$k] = $col[1];
1624  if ($col[0] == "blob" || $col[0] == "clob")
1625  {
1626  $lobs = true;
1627  $lob[$k] = $k;
1628  }
1629  }
1630  if ($lobs) // lobs -> use prepare execute (autoexecute broken in PEAR 2.4.1)
1631  {
1632  $st = $this->db->prepare("REPLACE INTO ".$a_table." (".implode($fields,",").") VALUES (".
1633  implode($placeholders2,",").")", $types, MDB2_PREPARE_MANIP, $lob);
1634  $this->handleError($st, "insert / prepare/execute(".$a_table.")");
1635  $r = $st->execute($field_values);
1636  //$r = $this->db->extended->autoExecute($a_table, $field_values, MDB2_AUTOQUERY_INSERT, null, $types);
1637  $this->handleError($r, "insert / prepare/execute(".$a_table.")");
1638  $this->free($st);
1639  }
1640  else // if no lobs are used, take simple manipulateF
1641  {
1642  $q = "REPLACE INTO ".$a_table." (".implode($fields,",").") VALUES (".
1643  implode($placeholders,",").")";
1644  $r = $this->manipulateF($q, $types, $values);
1645  }
1646  return $r;
1647  }
1648 
1654  function fetchAssoc($a_set)
1655  {
1656  return $a_set->fetchRow(DB_FETCHMODE_ASSOC);
1657  }
1658 
1662  function free($a_st)
1663  {
1664  return $a_st->free();
1665  }
1666 
1672  function fetchObject($a_set)
1673  {
1674  return $a_set->fetchRow(DB_FETCHMODE_OBJECT);
1675  }
1676 
1682  function numRows($a_set)
1683  {
1684  return $a_set->numRows();
1685  }
1686 
1687  //
1688  // function and clauses abstraction
1689  //
1690 
1702  function in($a_field, $a_values, $negate = false, $a_type = "")
1703  {
1704  if (count($a_values) == 0)
1705  {
1706  return " 1=2 "; // return a false statement on empty array
1707  }
1708  if ($a_type == "") // untyped: used ? for prepare/execute
1709  {
1710  $str = $a_field.(($negate) ? " NOT" : "")." IN (?".str_repeat(",?", count($a_values) - 1).")";
1711  }
1712  else // typed, use values for query/manipulate
1713  {
1714  $str = $a_field.(($negate) ? " NOT" : "")." IN (";
1715  $sep = "";
1716  foreach ($a_values as $v)
1717  {
1718  $str.= $sep.$this->quote($v, $a_type);
1719  $sep = ",";
1720  }
1721  $str.= ")";
1722  }
1723 
1724  return $str;
1725  }
1726 
1730  function addTypesToArray($a_arr, $a_type, $a_cnt)
1731  {
1732  if (!is_array($a_arr))
1733  {
1734  $a_arr = array();
1735  }
1736  if ($a_cnt > 0)
1737  {
1738  $type_arr = array_fill(0, $a_cnt, $a_type);
1739  }
1740  else
1741  {
1742  $type_arr = array();
1743  }
1744  return array_merge($a_arr, $type_arr);
1745  }
1746 
1751  function now()
1752  {
1753  return "now()";
1754  }
1755 
1756 
1766  public function concat($a_values,$a_allow_null = true)
1767  {
1768  if(!count($a_values))
1769  {
1770  return ' ';
1771  }
1772 
1773  $concat = ' CONCAT(';
1774  $first = true;
1775  foreach($a_values as $field_info)
1776  {
1777  $val = $field_info[0];
1778 
1779  if(!$first)
1780  {
1781  $concat .= ',';
1782  }
1783 
1784  if($a_allow_null)
1785  {
1786  $concat .= 'COALESCE(';
1787  }
1788  $concat .= $val;
1789 
1790  if($a_allow_null)
1791  {
1792  $concat .= ",''";
1793  $concat .= ')';
1794  }
1795 
1796  $first = false;
1797  }
1798  $concat .= ') ';
1799  return $concat;
1800  }
1801 
1808  function substr($a_exp, $a_pos = 1, $a_len = -1)
1809  {
1810  $lenstr = "";
1811  if ($a_len > -1)
1812  {
1813  $lenstr = ", ".$a_len;
1814  }
1815  return " SUBSTR(".$a_exp.", ".$a_pos.$lenstr.") ";
1816  }
1817 
1824  function upper($a_exp)
1825  {
1826  return " UPPER(".$a_exp.") ";
1827  }
1828 
1835  function lower($a_exp)
1836  {
1837  return " LOWER(".$a_exp.") ";
1838  }
1839 
1847  public function locate($a_needle,$a_string,$a_start_pos = 1)
1848  {
1849  $locate = ' LOCATE( ';
1850  $locate .= $a_needle;
1851  $locate .= ',';
1852  $locate .= $a_string;
1853  $locate .= ',';
1854  $locate .= $a_start_pos;
1855  $locate .= ') ';
1856  return $locate;
1857  }
1858 
1859 
1865  function like($a_col, $a_type, $a_value = "?", $case_insensitive = true)
1866  {
1867  if (!in_array($a_type, array("text", "clob", "blob")))
1868  {
1869  $this->raisePearError("Like: Invalid column type '".$a_type."'.", $this->error_class->FATAL);
1870  }
1871  if ($a_value == "?")
1872  {
1873  if ($case_insensitive)
1874  {
1875  return "UPPER(".$a_col.") LIKE(UPPER(?))";
1876  }
1877  else
1878  {
1879  return $a_col ." LIKE(?)";
1880  }
1881  }
1882  else
1883  {
1884  if ($case_insensitive)
1885  {
1886  // Always quote as text
1887  return " UPPER(".$a_col.") LIKE(UPPER(".$this->quote($a_value, 'text')."))";
1888  }
1889  else
1890  {
1891  // Always quote as text
1892  return " ".$a_col." LIKE(".$this->quote($a_value, 'text').")";
1893  }
1894  }
1895  }
1896 
1897 
1901  function equals($a_col, $a_value, $a_type, $a_empty_or_null = false)
1902  {
1903  if (!$a_empty_or_null || $a_value != "")
1904  {
1905  return $a_col." = ".$this->quote($a_value, $a_type);
1906  }
1907  else
1908  {
1909  return "(".$a_col." = '' OR $a_col IS NULL)";
1910  }
1911  }
1912 
1916  function equalsNot($a_col, $a_value, $a_type, $a_empty_or_null = false)
1917  {
1918  if (!$a_empty_or_null)
1919  {
1920  return $a_col." <> ".$this->quote($a_value, $a_type);
1921  }
1922  if ($a_value != "")
1923  {
1924  return "(".$a_col." <> ".$this->quote($a_value, $a_type). " OR ".
1925  $a_col." IS NULL)";
1926  }
1927  else
1928  {
1929  return "(".$a_col." <> '' AND $a_col IS NOT NULL)";
1930  }
1931  }
1932 
1939  function fromUnixtime($a_expr, $a_to_text = true)
1940  {
1941  return "FROM_UNIXTIME(".$a_expr.")";
1942  }
1943 
1947  function unixTimestamp()
1948  {
1949  return "UNIX_TIMESTAMP()";
1950  }
1951 
1955  function optimizeTable($a_table)
1956  {
1957  // needs to be overwritten in DBMS specific class
1958  // if necessary and possible
1959  }
1960 
1961  //
1962  // Schema related functions
1963  //
1964 
1971  function tableExists($a_table)
1972  {
1973  $tables = $this->listTables();
1974 
1975  if (is_array($tables))
1976  {
1977  if (in_array($a_table, $tables))
1978  {
1979  return true;
1980  }
1981  }
1982  return false;
1983  }
1984 
1992  function tableColumnExists($a_table, $a_column_name)
1993  {
1994 
1995  $column_visibility = false;
1996  $manager = $this->db->loadModule('Manager');
1997  $r = $manager->listTableFields($a_table);
1998 
1999  if (!MDB2::isError($r))
2000  {
2001  foreach($r as $field)
2002  {
2003  if ($field == $a_column_name)
2004  {
2005  $column_visibility = true;
2006  }
2007  }
2008  }
2009 
2010  return $column_visibility;
2011  }
2012 
2018  function listTables()
2019  {
2020  $manager = $this->db->loadModule('Manager');
2021  $r = $manager->listTables();
2022 
2023  if (!MDB2::isError($r))
2024  {
2025  return $r;
2026  }
2027 
2028  return false;
2029  }
2030 
2031 
2032  //
2033  // Quote Functions
2034  //
2035 
2039  function quote($a_query, $a_type = null)
2040  {
2041  if ($a_query == "" && is_null($a_type))
2042  {
2043  $a_query = "";
2044  }
2045  // Performance fix
2046 
2047  if($a_type == 'integer' && !is_null($a_query))
2048  {
2049  return (int) $a_query;
2050  }
2051  if ($a_type == "blob" || $a_type == "clob")
2052  {
2053  $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);
2054  }
2055 
2056  return $this->db->quote($a_query, $a_type);
2057  }
2058 
2066  function quoteIdentifier($a_identifier)
2067  {
2068  return $this->db->quoteIdentifier($a_identifier);
2069  }
2070 
2071  //
2072  // Transaction and Locking methods
2073  //
2074 
2080  function beginTransaction()
2081  {
2082  if (!$this->db->supports('transactions'))
2083  {
2084  $this->raisePearError("ilDB::beginTransaction: Transactions are not supported.", $this->error_class->FATAL);
2085  }
2086  $res = $this->db->beginTransaction();
2087 
2088  return $this->handleError($res, "beginTransaction()");
2089  }
2090 
2094  function commit()
2095  {
2096  $res = $this->db->commit();
2097 
2098  return $this->handleError($res, "commit()");
2099  }
2100 
2104  function rollback()
2105  {
2106  $res = $this->db->rollback();
2107 
2108  return $this->handleError($res, "rollback()");
2109  }
2110 
2116  abstract public function lockTables($a_tables);
2117 
2122  abstract public function unlockTables();
2123 
2124 
2125 //
2126 //
2127 // Older functions. Must be checked.
2128 //
2129 //
2130 
2139  function autoExecute($a_tablename,$a_fields,$a_mode = MDB2_AUTOQUERY_INSERT,$a_where = false)
2140  {
2141  $res = $this->db->autoExecute($a_tablename,$a_fields,$a_mode,$a_where);
2142 
2143  return $this->handleError($res, "autoExecute(".$a_tablename.")");
2144  }
2145 
2146 //
2147 //
2148 // Deprecated functions.
2149 //
2150 //
2151 
2155  function getLastInsertId()
2156  {
2157  $res = $this->db->lastInsertId();
2158  if(MDB2::isError($res))
2159  {
2160  return false;
2161  }
2162  return $res;
2163  }
2164 
2174  function getOne($sql)
2175  {
2176  //$r = $this->db->getOne($sql);
2177  $set = $this->db->query($sql);
2178 
2179  $this->handleError($set, "getOne(".$sql.")");
2180 
2181  if (!MDB2::isError($set))
2182  {
2183  $r = $set->fetchRow(DB_FETCHMODE_ASSOC);
2184 
2185  return $r[0];
2186  }
2187  }
2188 
2198  function getRow($sql,$mode = DB_FETCHMODE_OBJECT)
2199  {
2200  $set = $this->query($sql);
2201  $r = $set->fetchRow($mode);
2202  //$r = $this->db->getrow($sql,$mode);
2203 
2204  $this->handleError($r, "getRow(".$sql.")");
2205 
2206  return $r;
2207  } //end function
2208 
2209 } //end Class
2210 ?>