ILIAS  Release_4_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 
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  $r = $manager->dropTable($a_name);
521 
522  return $this->handleError($r, "dropTable(".$a_name.")");
523  }
524 
530  function alterTable($a_name, $a_changes)
531  {
532  if ($a_options == "")
533  {
534  $a_options = array();
535  }
536 
537  $manager = $this->db->loadModule('Manager');
538  $r = $manager->alterTable($a_name, $a_changes, false);
539 
540  return $this->handleError($r, "alterTable(".$a_name.")");
541  }
542 
551  function addTableColumn($a_table, $a_column, $a_attributes)
552  {
553 
554  $manager = $this->db->loadModule('Manager');
555 
556  if (!$this->checkColumnName($a_column))
557  {
558  $this->raisePearError("ilDB Error: addTableColumn(".$a_table.", ".$a_column.")<br />".
559  $this->error_str);
560  }
561  if (!$this->checkColumnDefinition($a_attributes))
562  {
563  $this->raisePearError("ilDB Error: addTableColumn(".$a_table.", ".$a_column.")<br />".
564  $this->error_str);
565  }
566 
567  $changes = array(
568  "add" => array(
569  $a_column => $a_attributes
570  )
571  );
572 
573  $r = $manager->alterTable($a_table, $changes, false);
574 
575  return $this->handleError($r, "addTableColumn(".$a_table.", ".$a_column.")");
576  }
577 
585  function dropTableColumn($a_table, $a_column)
586  {
587 
588  $manager = $this->db->loadModule('Manager');
589 
590  $changes = array(
591  "remove" => array(
592  $a_column => array()
593  )
594  );
595 
596  $r = $manager->alterTable($a_table, $changes, false);
597 
598  return $this->handleError($r, "dropTableColumn(".$a_table.", ".$a_column.")");
599  }
600 
609  function modifyTableColumn($a_table, $a_column, $a_attributes)
610  {
611  $manager = $this->db->loadModule('Manager');
612  $reverse = $this->db->loadModule('Reverse');
613  $def = $reverse->getTableFieldDefinition($a_table, $a_column);
614 
615  $this->handleError($def, "modifyTableColumn(".$a_table.")");
616 
617  if (is_file("./Services/Database/classes/class.ilDBAnalyzer.php"))
618  {
619  include_once("./Services/Database/classes/class.ilDBAnalyzer.php");
620  }
621  else
622  {
623  include_once("../Services/Database/classes/class.ilDBAnalyzer.php");
624  }
625  $analyzer = new ilDBAnalyzer();
626  $best_alt = $analyzer->getBestDefinitionAlternative($def);
627  $def = $def[$best_alt];
628  unset($def["nativetype"]);
629  unset($def["mdb2type"]);
630 
631  // check attributes
632  $type = ($a_attributes["type"] != "")
633  ? $a_attributes["type"]
634  : $def["type"];
635  foreach ($def as $k => $v)
636  {
637  if ($k != "type" && !in_array($k, $this->allowed_attributes[$type]))
638  {
639  unset($def[$k]);
640  }
641  }
642  $check_array = $def;
643  foreach ($a_attributes as $k => $v)
644  {
645  $check_array[$k] = $v;
646  }
647  if (!$this->checkColumnDefinition($check_array, true))
648  {
649  $this->raisePearError("ilDB Error: modifyTableColumn(".$a_table.", ".$a_column.")<br />".
650  $this->error_str);
651  }
652 
653  // oracle workaround: do not set null, if null already given
654  if ($this->getDbType() == "oracle")
655  {
656  if ($def["notnull"] == true && $a_attributes["notnull"] == true)
657  {
658  unset($def["notnull"]);
659  unset($a_attributes["notnull"]);
660  }
661  }
662 
663  foreach ($a_attributes as $a => $v)
664  {
665  $def[$a] = $v;
666  }
667 
668  $a_attributes["definition"] = $def;
669 
670  $changes = array(
671  "change" => array(
672  $a_column => $a_attributes
673  )
674  );
675 
676  $r = $manager->alterTable($a_table, $changes, false);
677 
678  return $this->handleError($r, "modifyTableColumn(".$a_table.")");
679  }
680 
689  function renameTableColumn($a_table, $a_column, $a_new_column)
690  {
691  // check table name
692  if (!$this->checkColumnName($a_new_column))
693  {
694  $this->raisePearError("ilDB Error: renameTableColumn(".$a_table.",".$a_column.",".$a_new_column.")<br />".
695  $this->error_str);
696  }
697 
698  $manager = $this->db->loadModule('Manager');
699  $reverse = $this->db->loadModule('Reverse');
700  $def = $reverse->getTableFieldDefinition($a_table, $a_column);
701 
702  $this->handleError($def, "renameTableColumn(".$a_table.",".$a_column.",".$a_new_column.")");
703 
704  if (is_file("./Services/Database/classes/class.ilDBAnalyzer.php"))
705  {
706  include_once("./Services/Database/classes/class.ilDBAnalyzer.php");
707  }
708  else
709  {
710  include_once("../Services/Database/classes/class.ilDBAnalyzer.php");
711  }
712 
713  $analyzer = new ilDBAnalyzer();
714  $best_alt = $analyzer->getBestDefinitionAlternative($def);
715  $def = $def[$best_alt];
716  unset($def["nativetype"]);
717  unset($def["mdb2type"]);
718 
719  $f["definition"] = $def;
720  $f["name"] = $a_new_column;
721 
722  $changes = array(
723  "rename" => array(
724  $a_column => $f
725  )
726  );
727 
728  $r = $manager->alterTable($a_table, $changes, false);
729 
730  return $this->handleError($r, "renameTableColumn(".$a_table.",".$a_column.",".$a_new_column.")");
731  }
732 
739  function renameTable($a_name, $a_new_name)
740  {
741  // check table name
742  if (!$this->checkTableName($a_new_name))
743  {
744  $this->raisePearError("ilDB Error: renameTable(".$a_name.",".$a_new_name.")<br />".
745  $this->error_str);
746  }
747 
748  $manager = $this->db->loadModule('Manager');
749  $r = $manager->alterTable($a_name, array("name" => $a_new_name), false);
750 
751  $query = "UPDATE abstraction_progress ".
752  "SET table_name = ".$this->db->quote($a_new_name,'text')." ".
753  "WHERE table_name = ".$this->db->quote($a_name,'text');
754  $this->db->query($query);
755 
756  return $this->handleError($r, "renameTable(".$a_name.",".$a_new_name.")");
757  }
758 
766  function addPrimaryKey($a_table, $a_fields)
767  {
768  $manager = $this->db->loadModule('Manager');
769 
770  $fields = array();
771  foreach ($a_fields as $f)
772  {
773  $fields[$f] = array();
774  }
775  $definition = array (
776  'primary' => true,
777  'fields' => $fields
778  );
779  $r = $manager->createConstraint($a_table,
780  $this->constraintName($a_table, $this->getPrimaryKeyIdentifier()), $definition);
781 
782  return $this->handleError($r, "addPrimaryKey(".$a_table.")");
783  }
784 
788  function getPrimaryKeyIdentifier()
789  {
790  return "PRIMARY";
791  }
792 
799  function dropPrimaryKey($a_table)
800  {
801  $manager = $this->db->loadModule('Manager');
802 
803  $r = $manager->dropConstraint($a_table,
804  $this->constraintName($a_table, $this->getPrimaryKeyIdentifier()), true);
805 
806  return $this->handleError($r, "dropPrimaryKey(".$a_table.")");
807  }
808 
816  function addIndex($a_table, $a_fields, $a_name = "in", $a_fulltext = false)
817  {
818  $manager = $this->db->loadModule('Manager');
819 
820  // check index name
821  if (!$this->checkIndexName($a_name))
822  {
823  $this->raisePearError("ilDB Error: addIndex(".$a_table.",".$a_name.")<br />".
824  $this->error_str);
825  }
826 
827  $fields = array();
828  foreach ($a_fields as $f)
829  {
830  $fields[$f] = array();
831  }
832  $definition = array (
833  'fields' => $fields
834  );
835 
836  if (!$a_fulltext)
837  {
838  $r = $manager->createIndex($a_table, $this->constraintName($a_table, $a_name), $definition);
839  }
840  else
841  {
842  if ($this->supportsFulltext())
843  {
844  $this->addFulltextIndex($a_table, $a_fields, $a_name);
845  }
846  }
847 
848  return $this->handleError($r, "addIndex(".$a_table.")");
849  }
850 
854  function addFulltextIndex($a_table, $a_fields, $a_name = "in")
855  {
856  return false;
857  }
858 
862  function isFulltextIndex($a_table, $a_name)
863  {
864  return false;
865  }
866 
874  function dropIndex($a_table, $a_name = "in")
875  {
876  $manager = $this->db->loadModule('Manager');
877 
878  if (!$this->isFulltextIndex($a_table, $a_name))
879  {
880  $r = $manager->dropIndex($a_table, $this->constraintName($a_table, $a_name));
881  }
882  else
883  {
884  $this->dropFulltextIndex($a_table, $a_name);
885  }
886 
887  return $this->handleError($r, "dropIndex(".$a_table.")");
888  }
889 
897  function addUniqueConstraint($a_table, $a_fields, $a_name = "con")
898  {
899  $manager = $this->db->loadModule('Manager');
900 
901  // check index name
902  if (!$this->checkIndexName($a_name))
903  {
904  $this->raisePearError("ilDB Error: addUniqueConstraint(".$a_table.",".$a_name.")<br />".
905  $this->error_str);
906  }
907 
908  $fields = array();
909  foreach ($a_fields as $f)
910  {
911  $fields[$f] = array();
912  }
913  $definition = array (
914  'unique' => true,
915  'fields' => $fields
916  );
917 
918  $r = $manager->createConstraint($a_table, $this->constraintName($a_table, $a_name), $definition);
919 
920  return $this->handleError($r, "addUniqueConstraint(".$a_table.")");
921  }
922 
926  function createSequence($a_table_name, $a_start = 1)
927  {
928  $manager = $this->db->loadModule('Manager');
929 
930  $r = $manager->createSequence($a_table_name, $a_start);
931 
932  return $this->handleError($r, "createSequence(".$a_table_name.")");
933  }
934 
935 
939  function dropSequence($a_table_name)
940  {
941  $manager = $this->db->loadModule('Manager');
942 
943  $r = $manager->dropSequence($a_table_name);
944 
945  return $this->handleError($r, "dropSequence(".$a_table_name.")");
946  }
947 
953  function checkTableName($a_name)
954  {
955  if (!preg_match ("/^[a-z]+[_a-z0-9]*$/", $a_name))
956  {
957  $this->error_str = "Table name must only contain _a-z0-9 and must start with a-z.";
958  return false;
959  }
960 
961  if ($this->isReservedWord($a_name))
962  {
963  $this->error_str = "Invalid table name '".$a_name."' (Reserved Word).";
964  return false;
965  }
966 
967  if (strtolower(substr($a_name, 0, 4)) == "sys_")
968  {
969  $this->error_str = "Invalid table name '".$a_name."'. Name must not start with 'sys_'.";
970  return false;
971  }
972 
973  if (strlen($a_name) > 22)
974  {
975  $this->error_str = "Invalid table name '".$a_name."'. Maximum table identifer lenght is 22 bytes.";
976  return false;
977  }
978 
979  return true;
980  }
981 
987  function checkTableColumns($a_cols)
988  {
989  foreach ($a_cols as $col => $def)
990  {
991  if (!$this->checkColumn($col, $def))
992  {
993  return false;
994  }
995  }
996 
997  return true;
998  }
999 
1003  function checkColumn($a_col, $a_def)
1004  {
1005  if (!$this->checkColumnName($a_col))
1006  {
1007  return false;
1008  }
1009 
1010  if (!$this->checkColumnDefinition($a_def))
1011  {
1012  return false;
1013  }
1014 
1015  return true;
1016  }
1017 
1023  function checkColumnDefinition($a_def, $a_modify_mode = false)
1024  {
1025  // check valid type
1026  if (!in_array($a_def["type"], array("text", "integer", "float", "date", "time", "timestamp", "clob", "blob")))
1027  {
1028  switch ($a_def["type"])
1029  {
1030  case "boolean":
1031  $this->error_str = "Invalid column type '".$a_def["type"]."'. Use integer(1) instead.";
1032  break;
1033 
1034  case "decimal":
1035  $this->error_str = "Invalid column type '".$a_def["type"]."'. Use float or integer instead.";
1036  break;
1037 
1038  default:
1039  $this->error_str = "Invalid column type '".$a_def["type"]."'. Allowed types are: ".
1040  "text, integer, float, date, time, timestamp, clob and blob.";
1041  }
1042  }
1043 
1044  // check used attributes
1045  $allowed_attributes = $this->allowed_attributes;
1046 
1047  foreach ($a_def as $k => $v)
1048  {
1049  if ($k != "type" && !in_array($k, $allowed_attributes[$a_def["type"]]))
1050  {
1051  $this->error_str = "Attribute '".$k."' is not allowed for column type '".$a_def["type"]."'.";
1052  return false;
1053  }
1054  }
1055 
1056  // type specific checks
1057  switch ($a_def["type"])
1058  {
1059  case "text":
1060  if ($a_def["length"] < 1 || $a_def["length"] > 4000)
1061  {
1062  if (!$a_modify_mode || isset($a_def["length"]))
1063  {
1064  $this->error_str = "Invalid length '".$a_def["length"]."' for type text.".
1065  " Length must be >=1 and <= 4000.";
1066  return false;
1067  }
1068  }
1069  break;
1070 
1071  case "integer":
1072  if (!in_array($a_def["length"], array(1, 2, 3, 4, 8)))
1073  {
1074  if (!$a_modify_mode || isset($a_def["length"]))
1075  {
1076  $this->error_str = "Invalid length '".$a_def["length"]."' for type integer.".
1077  " Length must be 1, 2, 3, 4 or 8 (bytes).";
1078  return false;
1079  }
1080  }
1081  if ($a_def["unsigned"])
1082  {
1083  $this->error_str = "Unsigned attribut must not be true for type integer.";
1084  return false;
1085  }
1086  break;
1087  }
1088 
1089  return true;
1090  }
1091 
1097  function checkColumnName($a_name)
1098  {
1099  if (!preg_match ("/^[a-z]+[_a-z0-9]*$/", $a_name))
1100  {
1101  $this->error_str = "Invalid column name '".$a_name."'. Column name must only contain _a-z0-9 and must start with a-z.";
1102  return false;
1103  }
1104 
1105  if ($this->isReservedWord($a_name))
1106  {
1107  $this->error_str = "Invalid column name '".$a_name."' (Reserved Word).";
1108  return false;
1109  }
1110 
1111  if (strtolower(substr($a_name, 0, 4)) == "sys_")
1112  {
1113  $this->error_str = "Invalid column name '".$a_name."'. Name must not start with 'sys_'.";
1114  return false;
1115  }
1116 
1117  if (strlen($a_name) > 30)
1118  {
1119  $this->error_str = "Invalid column name '".$a_name."'. Maximum column identifer lenght is 30 bytes.";
1120  return false;
1121  }
1122 
1123  return true;
1124  }
1125 
1131  function checkIndexName($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 (strlen($a_name) > 3)
1146  {
1147  $this->error_str = "Invalid index name '".$a_name."'. Maximum index identifer lenght is 3 bytes.";
1148  return false;
1149  }
1150 
1151  return true;
1152  }
1153 
1154  function getAllowedAttributes()
1155  {
1156  return $this->allowed_attributes;
1157  }
1158 
1164  function constraintName($a_table, $a_constraint)
1165  {
1166  return $a_constraint;
1167  }
1168 
1173  static function isReservedWord($a_word)
1174  {
1175  include_once("./Services/Database/classes/class.ilDBMySQL.php");
1176  $mysql_reserved_words = ilDBMySQL::getReservedWords();
1177  if (in_array(strtoupper($a_word), $mysql_reserved_words))
1178  {
1179  return true;
1180  }
1181  include_once("./Services/Database/classes/class.ilDBOracle.php");
1182  $oracle_reserved_words = ilDBOracle::getReservedWords();
1183  if (in_array(strtoupper($a_word), $oracle_reserved_words))
1184  {
1185  return true;
1186  }
1187  include_once("./Services/Database/classes/class.ilDBPostgreSQL.php");
1188  $postgres_reserved_words = ilDBPostgreSQL::getReservedWords();
1189  if (in_array(strtoupper($a_word), $postgres_reserved_words))
1190  {
1191  return true;
1192  }
1193  }
1194 
1195  //
1196  // Data query and manupilation functions
1197  //
1198 
1210  function query($sql, $a_handle_error = true)
1211  {
1212  $r = $this->db->query($sql);
1213 
1214  if ($a_handle_error)
1215  {
1216  return $this->handleError($r, "query(".$sql.")");
1217  }
1218 
1219  return $r;
1220  }
1221 
1229  function queryF($a_query, $a_types, $a_values)
1230  {
1231  if (!is_array($a_types) || !is_array($a_values) ||
1232  count($a_types) != count($a_values))
1233  {
1234  $this->raisePearError("ilDB::queryF: types and values must be arrays of same size.");
1235  }
1236  $quoted_values = array();
1237  foreach($a_types as $k => $t)
1238  {
1239  $quoted_values[] = $this->quote($a_values[$k], $t);
1240  }
1241  $query = vsprintf($a_query, $quoted_values);
1242 
1243  return $this->query($query);
1244  }
1245 
1253  function manipulateF($a_query, $a_types, $a_values)
1254  {
1255  if (!is_array($a_types) || !is_array($a_values) ||
1256  count($a_types) != count($a_values))
1257  {
1258  $this->raisePearError("ilDB::manipulateF: types and values must be arrays of same size.");
1259  }
1260  $quoted_values = array();
1261  foreach($a_types as $k => $t)
1262  {
1263  $quoted_values[] = $this->quote($a_values[$k], $t);
1264  }
1265  $query = vsprintf($a_query, $quoted_values);
1266 
1267  return $this->manipulate($query);
1268  }
1269 
1273  function logStatement($sql)
1274  {
1275  $pos1 = strpos(strtolower($sql), "from ");
1276  $table = "";
1277  if ($pos1 > 0)
1278  {
1279  $tablef = substr($sql, $pos1+5);
1280  $pos2 = strpos(strtolower($tablef), " ");
1281  if ($pos2 > 0)
1282  {
1283  $table =substr($tablef, 0, $pos2);
1284  }
1285  else
1286  {
1287  $table = $tablef;
1288  }
1289  }
1290  if (trim($table) != "")
1291  {
1292  if (!is_array($this->ttt) || !in_array($table, $this->ttt))
1293  {
1294  echo "<br>".$table;
1295  $this->ttt[] = $table;
1296  }
1297  }
1298  else
1299  {
1300  echo "<br><b>".$sql."</b>";
1301  }
1302  }
1303 
1307  function setLimit($a_limit, $a_offset = 0)
1308  {
1309  $this->db->setLimit($a_limit, $a_offset);
1310  }
1311 
1315  function nextId($a_table_name)
1316  {
1317  // we do not create missing sequences automatically here
1318  // otherwise misspelled statements result in additional tables
1319  // please create sequences explicitly in the db update script
1320  $r = $this->db->nextId($a_table_name, false);
1321 
1322  return $this->handleError($r, "nextId(".$a_table_name.")");
1323  }
1324 
1335  function manipulate($sql)
1336  {
1337  $r = $this->db->exec($sql);
1338 
1339  return $this->handleError($r, "manipulate(".$sql.")");
1340  }
1341 
1350  function prepare($a_query, $a_types = null, $a_result_types = null)
1351  {
1352  $res = $this->db->prepare($a_query, $a_types, $a_result_types);
1353 
1354  return $this->handleError($res, "prepare(".$a_query.")");
1355  }
1356 
1365  function prepareManip($a_query, $a_types = null)
1366  {
1367  $res = $this->db->prepare($a_query, $a_types, MDB2_PREPARE_MANIP);
1368 
1369  return $this->handleError($res, "prepareManip(".$a_query.")");
1370  }
1371 
1380  function execute($a_stmt, $a_data = null)
1381  {
1382  $res = $a_stmt->execute($a_data);
1383 
1384  return $this->handleError($res, "execute(".$a_stmt->query.")");
1385  }
1386 
1396  function executeMultiple($a_stmt, $a_data)
1397  {
1398  $res = $this->db->extended->executeMultiple($a_stmt,$a_data);
1399 
1400  return $this->handleError($res, "executeMultiple(".$a_stmt->query.")");
1401  }
1402 
1409  function insert($a_table, $a_columns)
1410  {
1411  $fields = array();
1412  $field_values = array();
1413  $placeholders = array();
1414  $types = array();
1415  $values = array();
1416  $lobs = false;
1417  $lob = array();
1418  foreach ($a_columns as $k => $col)
1419  {
1420  $fields[] = $k;
1421  $placeholders[] = "%s";
1422  $placeholders2[] = ":$k";
1423  $types[] = $col[0];
1424  $values[] = $col[1];
1425  $field_values[$k] = $col[1];
1426  if ($col[0] == "blob" || $col[0] == "clob")
1427  {
1428  $lobs = true;
1429  $lob[$k] = $k;
1430  }
1431  }
1432  if ($lobs) // lobs -> use prepare execute (autoexecute broken in PEAR 2.4.1)
1433  {
1434  $st = $this->db->prepare("INSERT INTO ".$a_table." (".implode($fields,",").") VALUES (".
1435  implode($placeholders2,",").")", $types, MDB2_PREPARE_MANIP, $lob);
1436  $r = $st->execute($field_values);
1437 
1438 
1439  //$r = $this->db->extended->autoExecute($a_table, $field_values, MDB2_AUTOQUERY_INSERT, null, $types);
1440  $this->handleError($r, "insert / prepare/execute(".$a_table.")");
1441  $this->free($st);
1442  }
1443  else // if no lobs are used, take simple manipulateF
1444  {
1445  $q = "INSERT INTO ".$a_table." (".implode($fields,",").") VALUES (".
1446  implode($placeholders,",").")";
1447  $r = $this->manipulateF($q, $types, $values);
1448  }
1449  return $r;
1450  }
1451 
1460  function update($a_table, $a_columns, $a_where)
1461  {
1462  $fields = array();
1463  $field_values = array();
1464  $placeholders = array();
1465  $types = array();
1466  $values = array();
1467  $lobs = false;
1468  $lob = array();
1469  foreach ($a_columns as $k => $col)
1470  {
1471  $fields[] = $k;
1472  $placeholders[] = "%s";
1473  $placeholders2[] = ":$k";
1474  $types[] = $col[0];
1475  $values[] = $col[1];
1476  $field_values[$k] = $col[1];
1477  if ($col[0] == "blob" || $col[0] == "clob")
1478  {
1479  $lobs = true;
1480  $lob[$k] = $k;
1481  }
1482  }
1483 
1484  if ($lobs)
1485  {
1486  $q = "UPDATE ".$a_table." SET ";
1487  $lim = "";
1488  foreach ($fields as $k => $field)
1489  {
1490  $q.= $lim.$field." = ".$placeholders2[$k];
1491  $lim = ", ";
1492  }
1493  $q.= " WHERE ";
1494  $lim = "";
1495  foreach ($a_where as $k => $col)
1496  {
1497  $q.= $lim.$k." = ".$this->quote($col[1], $col[0]);
1498  $lim = " AND ";
1499  }
1500  $st = $this->db->prepare($q, $types, MDB2_PREPARE_MANIP, $lob);
1501  $r = $st->execute($field_values);
1502 
1503  //$r = $this->db->extended->autoExecute($a_table, $field_values, MDB2_AUTOQUERY_INSERT, null, $types);
1504  $this->handleError($r, "update / prepare/execute(".$a_table.")");
1505  $this->free($st);
1506  }
1507  else
1508  {
1509  foreach ($a_where as $k => $col)
1510  {
1511  $types[] = $col[0];
1512  $values[] = $col[1];
1513  $field_values[$k] = $col;
1514  }
1515  $q = "UPDATE ".$a_table." SET ";
1516  $lim = "";
1517  foreach ($fields as $k => $field)
1518  {
1519  $q.= $lim.$field." = ".$placeholders[$k];
1520  $lim = ", ";
1521  }
1522  $q.= " WHERE ";
1523  $lim = "";
1524  foreach ($a_where as $k => $col)
1525  {
1526  $q.= $lim.$k." = %s";
1527  $lim = " AND ";
1528  }
1529 
1530  $r = $this->manipulateF($q, $types, $values);
1531  }
1532  return $r;
1533  }
1534 
1542  function replace($a_table, $a_pk_columns, $a_other_columns)
1543  {
1544  // this is the mysql implementation
1545  $a_columns = array_merge($a_pk_columns, $a_other_columns);
1546  $fields = array();
1547  $field_values = array();
1548  $placeholders = array();
1549  $types = array();
1550  $values = array();
1551  $lobs = false;
1552  $lob = array();
1553  foreach ($a_columns as $k => $col)
1554  {
1555  $fields[] = $k;
1556  $placeholders[] = "%s";
1557  $placeholders2[] = ":$k";
1558  $types[] = $col[0];
1559  $values[] = $col[1];
1560  $field_values[$k] = $col[1];
1561  if ($col[0] == "blob" || $col[0] == "clob")
1562  {
1563  $lobs = true;
1564  $lob[$k] = $k;
1565  }
1566  }
1567  if ($lobs) // lobs -> use prepare execute (autoexecute broken in PEAR 2.4.1)
1568  {
1569  $st = $this->db->prepare("REPLACE INTO ".$a_table." (".implode($fields,",").") VALUES (".
1570  implode($placeholders2,",").")", $types, MDB2_PREPARE_MANIP, $lob);
1571  $r = $st->execute($field_values);
1572  //$r = $this->db->extended->autoExecute($a_table, $field_values, MDB2_AUTOQUERY_INSERT, null, $types);
1573  $this->handleError($r, "insert / prepare/execute(".$a_table.")");
1574  $this->free($st);
1575  }
1576  else // if no lobs are used, take simple manipulateF
1577  {
1578  $q = "REPLACE INTO ".$a_table." (".implode($fields,",").") VALUES (".
1579  implode($placeholders,",").")";
1580  $r = $this->manipulateF($q, $types, $values);
1581  }
1582  return $r;
1583  }
1584 
1590  function fetchAssoc($a_set)
1591  {
1592  return $a_set->fetchRow(DB_FETCHMODE_ASSOC);
1593  }
1594 
1598  function free($a_st)
1599  {
1600  return $a_st->free();
1601  }
1602 
1608  function fetchObject($a_set)
1609  {
1610  return $a_set->fetchRow(DB_FETCHMODE_OBJECT);
1611  }
1612 
1618  function numRows($a_set)
1619  {
1620  return $a_set->numRows();
1621  }
1622 
1623  //
1624  // function and clauses abstraction
1625  //
1626 
1638  function in($a_field, $a_values, $negate = false, $a_type = "")
1639  {
1640  if (count($a_values) == 0)
1641  {
1642  return " 1=2 "; // return a false statement on empty array
1643  }
1644  if ($a_type == "") // untyped: used ? for prepare/execute
1645  {
1646  $str = $a_field.(($negate) ? " NOT" : "")." IN (?".str_repeat(",?", count($a_values) - 1).")";
1647  }
1648  else // typed, use values for query/manipulate
1649  {
1650  $str = $a_field.(($negate) ? " NOT" : "")." IN (";
1651  $sep = "";
1652  foreach ($a_values as $v)
1653  {
1654  $str.= $sep.$this->quote($v, $a_type);
1655  $sep = ",";
1656  }
1657  $str.= ")";
1658  }
1659 
1660  return $str;
1661  }
1662 
1666  function addTypesToArray($a_arr, $a_type, $a_cnt)
1667  {
1668  if (!is_array($a_arr))
1669  {
1670  $a_arr = array();
1671  }
1672  if ($a_cnt > 0)
1673  {
1674  $type_arr = array_fill(0, $a_cnt, $a_type);
1675  }
1676  else
1677  {
1678  $type_arr = array();
1679  }
1680  return array_merge($a_arr, $type_arr);
1681  }
1682 
1687  function now()
1688  {
1689  return "now()";
1690  }
1691 
1692 
1702  public function concat($a_values,$a_allow_null = true)
1703  {
1704  if(!count($a_values))
1705  {
1706  return ' ';
1707  }
1708 
1709  $concat = ' CONCAT(';
1710  $first = true;
1711  foreach($a_values as $field_info)
1712  {
1713  $val = $field_info[0];
1714 
1715  if(!$first)
1716  {
1717  $concat .= ',';
1718  }
1719 
1720  if($a_allow_null)
1721  {
1722  $concat .= 'COALESCE(';
1723  }
1724  $concat .= $val;
1725 
1726  if($a_allow_null)
1727  {
1728  $concat .= ",''";
1729  $concat .= ')';
1730  }
1731 
1732  $first = false;
1733  }
1734  $concat .= ') ';
1735  return $concat;
1736  }
1737 
1738 
1746  public function locate($a_needle,$a_string,$a_start_pos = 1)
1747  {
1748  $locate = ' LOCATE( ';
1749  $locate .= $a_needle;
1750  $locate .= ',';
1751  $locate .= $a_string;
1752  $locate .= ',';
1753  $locate .= $a_start_pos;
1754  $locate .= ') ';
1755  return $locate;
1756  }
1757 
1758 
1764  function like($a_col, $a_type, $a_value = "?", $case_insensitive = true)
1765  {
1766  if (!in_array($a_type, array("text", "clob", "blob")))
1767  {
1768  $this->raisePearError("Like: Invalid column type '".$a_type."'.", $this->error_class->FATAL);
1769  }
1770  if ($a_value == "?")
1771  {
1772  if ($case_insensitive)
1773  {
1774  return "UPPER(".$a_col.") LIKE(UPPER(?))";
1775  }
1776  else
1777  {
1778  return $a_col ." LIKE(?)";
1779  }
1780  }
1781  else
1782  {
1783  if ($case_insensitive)
1784  {
1785  // Always quote as text
1786  return " UPPER(".$a_col.") LIKE(UPPER(".$this->quote($a_value, 'text')."))";
1787  }
1788  else
1789  {
1790  // Always quote as text
1791  return " ".$a_col." LIKE(".$this->quote($a_value, 'text').")";
1792  }
1793  }
1794  }
1795 
1796 
1800  function equals($a_col, $a_value, $a_type, $a_empty_or_null = false)
1801  {
1802  if (!$a_empty_or_null || $a_value != "")
1803  {
1804  return $a_col." = ".$this->quote($a_value, $a_type);
1805  }
1806  else
1807  {
1808  return "(".$a_col." = '' OR $a_col IS NULL)";
1809  }
1810  }
1811 
1815  function equalsNot($a_col, $a_value, $a_type, $a_empty_or_null = false)
1816  {
1817  if (!$a_empty_or_null)
1818  {
1819  return $a_col." <> ".$this->quote($a_value, $a_type);
1820  }
1821  if ($a_value != "")
1822  {
1823  return "(".$a_col." <> ".$this->quote($a_value, $a_type). " OR ".
1824  $a_col." IS NULL)";
1825  }
1826  else
1827  {
1828  return "(".$a_col." <> '' AND $a_col IS NOT NULL)";
1829  }
1830  }
1831 
1838  function fromUnixtime($a_expr, $a_to_text = true)
1839  {
1840  return "FROM_UNIXTIME(".$a_expr.")";
1841  }
1842 
1846  function unixTimestamp()
1847  {
1848  return "UNIX_TIMESTAMP()";
1849  }
1850 
1854  function optimizeTable($a_table)
1855  {
1856  // needs to be overwritten in DBMS specific class
1857  // if necessary and possible
1858  }
1859 
1860  //
1861  // Schema related functions
1862  //
1863 
1870  function tableExists($a_table)
1871  {
1872  $tables = $this->listTables();
1873 
1874  if (is_array($tables))
1875  {
1876  if (in_array($a_table, $tables))
1877  {
1878  return true;
1879  }
1880  }
1881  return false;
1882  }
1883 
1891  function tableColumnExists($a_table, $a_column_name)
1892  {
1893 
1894  $column_visibility = false;
1895  $manager = $this->db->loadModule('Manager');
1896  $r = $manager->listTableFields($a_table);
1897 
1898  if (!MDB2::isError($r))
1899  {
1900  foreach($r as $field)
1901  {
1902  if ($field == $a_column_name)
1903  {
1904  $column_visibility = true;
1905  }
1906  }
1907  }
1908 
1909  return $column_visibility;
1910  }
1911 
1917  function listTables()
1918  {
1919  $manager = $this->db->loadModule('Manager');
1920  $r = $manager->listTables();
1921 
1922  if (!MDB2::isError($r))
1923  {
1924  return $r;
1925  }
1926 
1927  return false;
1928  }
1929 
1930 
1931  //
1932  // Quote Functions
1933  //
1934 
1938  function quote($a_query, $a_type = null)
1939  {
1940  if ($a_query == "" && is_null($a_type))
1941  {
1942  $a_query = "";
1943  }
1944 
1945  if ($a_type == "blob" || $a_type == "clob")
1946  {
1947  $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);
1948  }
1949 
1950  return $this->db->quote($a_query, $a_type);
1951  }
1952 
1960  function quoteIdentifier($a_identifier)
1961  {
1962  return $this->db->quoteIdentifier($a_identifier);
1963  }
1964 
1965  //
1966  // Transaction and Locking methods
1967  //
1968 
1974  function beginTransaction()
1975  {
1976  if (!$this->db->supports('transactions'))
1977  {
1978  $this->raisePearError("ilDB::beginTransaction: Transactions are not supported.", $this->error_class->FATAL);
1979  }
1980  $res = $this->db->beginTransaction();
1981 
1982  return $this->handleError($res, "beginTransaction()");
1983  }
1984 
1988  function commit()
1989  {
1990  $res = $this->db->commit();
1991 
1992  return $this->handleError($res, "commit()");
1993  }
1994 
1998  function rollback()
1999  {
2000  $res = $this->db->rollback();
2001 
2002  return $this->handleError($res, "rollback()");
2003  }
2004 
2010  abstract public function lockTables($a_tables);
2011 
2016  abstract public function unlockTables();
2017 
2018 
2019 //
2020 //
2021 // Older functions. Must be checked.
2022 //
2023 //
2024 
2033  function autoExecute($a_tablename,$a_fields,$a_mode = MDB2_AUTOQUERY_INSERT,$a_where = false)
2034  {
2035  $res = $this->db->autoExecute($a_tablename,$a_fields,$a_mode,$a_where);
2036 
2037  return $this->handleError($res, "autoExecute(".$a_tablename.")");
2038  }
2039 
2040 //
2041 //
2042 // Deprecated functions.
2043 //
2044 //
2045 
2049  function getLastInsertId()
2050  {
2051  $res = $this->db->lastInsertId();
2052  if(MDB2::isError($res))
2053  {
2054  return false;
2055  }
2056  return $res;
2057  }
2058 
2068  function getOne($sql)
2069  {
2070  //$r = $this->db->getOne($sql);
2071  $set = $this->db->query($sql);
2072 
2073  $this->handleError($set, "getOne(".$sql.")");
2074 
2075  if (!MDB2::isError($set))
2076  {
2077  $r = $set->fetchRow(DB_FETCHMODE_ASSOC);
2078 
2079  return $r[0];
2080  }
2081  }
2082 
2092  function getRow($sql,$mode = DB_FETCHMODE_OBJECT)
2093  {
2094  $set = $this->query($sql);
2095  $r = $set->fetchRow($mode);
2096  //$r = $this->db->getrow($sql,$mode);
2097 
2098  $this->handleError($r, "getRow(".$sql.")");
2099 
2100  return $r;
2101  } //end function
2102 
2103 } //end Class
2104 ?>