ILIAS  Release_4_4_x_branch Revision 61816
 All Data Structures Namespaces Files Functions Variables Groups Pages
class.ilDB.php
Go to the documentation of this file.
1 <?php
2 /* Copyright (c) 1998-2009 ILIAS open source, Extended GPL, see docs/LICENSE */
3 
7 //pear MDB2 abstraction layer
8 include_once ("MDB2.php");
9 
10 define("DB_FETCHMODE_ASSOC", MDB2_FETCHMODE_ASSOC);
11 define("DB_FETCHMODE_OBJECT", MDB2_FETCHMODE_OBJECT);
12 
13 //echo "-".DB_FETCHMODE_ASSOC."-";
14 //echo "+".DB_FETCHMODE_OBJECT."+";
15 
16 
28 abstract class ilDB extends PEAR
29 {
30  const LOCK_WRITE = 1;
31  const LOCK_READ = 2;
32 
33 
39  var $error_class;
40 
45  var $db;
46 
51  var $result;
52 
53 
54  var $allowed_attributes = array(
55  "text" => array("length", "notnull", "default", "fixed"),
56  "integer" => array("length", "notnull", "default", "unsigned"),
57  "float" => array("notnull", "default"),
58  "date" => array("notnull", "default"),
59  "time" => array("notnull", "default"),
60  "timestamp" => array("notnull", "default"),
61  "clob" => array("notnull", "default"),
62  "blob" => array("notnull", "default")
63  );
64 
65  var $sub_type;
66 
74  function ilDB()
75  {
76  }
77 
83  function setDBUser($a_user)
84  {
85  $this->db_user = $a_user;
86  }
87 
93  function getDBUser()
94  {
95  return $this->db_user;
96  }
97 
103  function setDBPort($a_port)
104  {
105  $this->db_port = $a_port;
106  }
107 
113  function getDBPort()
114  {
115  return $this->db_port;
116  }
117 
123  function setDBHost($a_host)
124  {
125  $this->db_host = $a_host;
126  }
127 
133  function getDBHost()
134  {
135  return $this->db_host;
136  }
137 
143  function setDBPassword($a_password)
144  {
145  $this->db_password = $a_password;
146  }
147 
153  function getDBPassword()
154  {
155  return $this->db_password;
156  }
157 
163  function setDBName($a_name)
164  {
165  $this->db_name = $a_name;
166  }
167 
173  function getDBName()
174  {
175  return $this->db_name;
176  }
177 
181  abstract function getDSN();
182 
186  function getDBVersion()
187  {
188  return "Unknown";
189  }
190 
194  abstract function getDBType();
195 
202  abstract static function getReservedWords();
203 
204 
209  public function enableResultBuffering($a_status)
210  {
211  $this->db->setOption('result_buffering',$a_status);
212  }
213 
218  function initFromIniFile($tmpClientIniFile = null)
219  {
220  global $ilClientIniFile;
221 
222  //overwrite global client ini file if local parameter is set
223  if (is_object($tmpClientIniFile))
224  $clientIniFile = $tmpClientIniFile;
225  else
226  $clientIniFile = $ilClientIniFile;
227 
228  if (is_object($clientIniFile ))
229  {
230  $this->setDBUser($clientIniFile ->readVariable("db", "user"));
231  $this->setDBHost($clientIniFile ->readVariable("db", "host"));
232  $this->setDBPort($clientIniFile ->readVariable("db", "port"));
233  $this->setDBPassword($clientIniFile ->readVariable("db", "pass"));
234  $this->setDBName($clientIniFile ->readVariable("db", "name"));
235  }
236  }
237 
241  function connect($a_return_false_for_error = false)
242  {
243  //set up error handling
244  $this->error_class = new ilErrorHandling();
245  $this->setErrorHandling(PEAR_ERROR_CALLBACK, array($this->error_class,'errorHandler'));
246 //echo $this->getDSN();
247  //check dsn
248  if ($this->getDSN() == "")
249  {
250  $this->raisePearError("No DSN given");
251  }
252 
253  //connect to database
254  $this->doConnect();
255 
256  if ($a_return_false_for_error && MDB2::isError($this->db))
257  {
258  return false;
259  }
260 
261  $this->loadMDB2Extensions();
262 
263  // set empty value portability to PEAR::DB behaviour
264  if (!$this->isDbError($this->db))
265  {
266  $this->db->setOption('portability', MDB2_PORTABILITY_ALL);
267  }
268  //check error
269  $this->handleError($this->db);
270 
271  // anything, that must be done to initialize the connection
272  $this->initConnection();
273 
274  return true;
275  }
276 
280  function doConnect()
281  {
282  $this->db = MDB2::connect($this->getDSN(),
283  array("use_transactions" => true));
284  }
285 
289  function disconnect()
290  {
291  $this->db->disconnect();
292  }
293 
294  //
295  // General and MDB2 related functions
296  //
297 
301  protected function initConnection()
302  {
303  }
304 
311  function getHostDSN()
312  {
313  return false;
314  }
315 
320  function connectHost()
321  {
322  //set up error handling
323  $this->error_class = new ilErrorHandling();
324  $this->setErrorHandling(PEAR_ERROR_CALLBACK, array($this->error_class,'errorHandler'));
325 
326  //check dsn
327  if ($this->getHostDSN() == "")
328  {
329  $this->raisePearError("No Host DSN given");
330  }
331 
332  //connect to database
333  $this->db = MDB2::connect($this->getHostDSN(),
334  array("use_transactions" => true));
335  if ($a_return_false_for_error && MDB2::isError($this->db))
336  {
337  return false;
338  }
339 
340  $this->loadMDB2Extensions();
341 
342  // set empty value portability to PEAR::DB behaviour
343  if (!$this->isDbError($this->db))
344  {
345  $cur = ($this->db->getOption("portability") & MDB2_PORTABILITY_EMPTY_TO_NULL);
346  $this->db->setOption("portability", $this->db->getOption("portability") - $cur);
347 
348  $cur = ($this->db->getOption("portability") & MDB2_PORTABILITY_FIX_CASE);
349  $this->db->setOption("portability", $this->db->getOption("portability") - $cur);
350  }
351 
352  //check error
353  $this->handleError($this->db);
354 
355  // anything, that must be done to initialize the connection
356  $this->initHostConnection();
357 
358  return true;
359  }
360 
364  protected function initHostConnection()
365  {
366  }
367 
368  function supportsFulltext()
369  {
370  return false;
371  }
372 
379  function supportsSlave()
380  {
381  return false;
382  }
383 
390  function useSlave($a_val = true)
391  {
392  if (!$this->supportsSlave())
393  {
394  return false;
395  }
396  $this->use_slave = $a_val;
397  }
398 
405  function handleError($a_res, $a_info = "", $a_level = "")
406  {
407  global $ilLog;
408 
409  if (MDB2::isError($a_res))
410  {
411  if ($a_level == "")
412  {
413  $a_level = $this->error_class->FATAL;
414  }
415 
416  // Show stack
417  try
418  {
419  throw new Exception();
420  }
421  catch(Exception $e)
422  {
423  $stack = $e->getTraceAsString();
424  }
425 
426  if(is_object($ilLog))
427  $ilLog->logStack();
428  $this->raisePearError("ilDB Error: ".$a_info."<br />".
429  $a_res->getMessage()."<br />".$a_res->getUserInfo()."<br />".$stack, $a_level);
430 
431  }
432 
433  return $a_res;
434  }
435 
439  function raisePearError($a_message, $a_level = "")
440  {
441  if ($a_level == "")
442  {
443  $a_level = $this->error_class->FATAL;
444  }
445 //echo "<br>-ilDB:raising-$a_message-$a_level-";
446  $this->raiseError($a_message, $a_level);
447  }
448 
454  protected function loadMDB2Extensions()
455  {
456  if (!$this->isDbError($this->db))
457  {
458  $this->db->loadModule('Extended');
459  define('DB_AUTOQUERY_SELECT',MDB2_AUTOQUERY_SELECT);
460  define('DB_AUTOQUERY_INSERT',MDB2_AUTOQUERY_INSERT);
461  define('DB_AUTOQUERY_UPDATE',MDB2_AUTOQUERY_UPDATE);
462  define('DB_AUTOQUERY_DELETE',MDB2_AUTOQUERY_DELETE);
463  }
464  }
465 
469  static function isDbError($a_res)
470  {
471  return MDB2::isError($a_res);
472  }
473 
474  //
475  // Data Definition Methods
476  //
477 
481  function createDatabase($a_name, $a_charset = "utf8", $a_collation = "")
482  {
483  if ($a_collation != "")
484  {
485  $sql = "CREATE DATABASE ".$a_name.
486  " CHARACTER SET ".$a_charset.
487  " COLLATE ".$a_collation;
488  }
489  else
490  {
491  $sql = "CREATE DATABASE ".$a_name.
492  " CHARACTER SET ".$a_charset;
493  }
494 
495  return $this->query($sql, false);
496  }
497 
498 
506  function createTable($a_name, $a_definition_array, $a_drop_table = false,
507  $a_ignore_erros = false)
508  {
509  // check table name
510  if (!$this->checkTableName($a_name) && !$a_ignore_erros)
511  {
512  $this->raisePearError("ilDB Error: createTable(".$a_name.")<br />".
513  $this->error_str);
514  }
515 
516  // check definition array
517  if (!$this->checkTableColumns($a_definition_array) && !$a_ignore_erros)
518  {
519  $this->raisePearError("ilDB Error: createTable(".$a_name.")<br />".
520  $this->error_str);
521  }
522 
523  if ($a_drop_table)
524  {
525  $this->dropTable($a_name, false);
526  }
527 
528  $options = $this->getCreateTableOptions();
529 
530  $manager = $this->db->loadModule('Manager');
531  $r = $manager->createTable($a_name, $a_definition_array, $options);
532 
533  return $this->handleError($r, "createTable(".$a_name.")");
534  }
535 
541  protected function getCreateTableOptions()
542  {
543  return array();
544  }
545 
552  function dropTable($a_name, $a_error_if_not_existing = true)
553  {
554  if (!$a_error_if_not_existing)
555  {
556  $tables = $this->listTables();
557  if (!in_array($a_name, $tables))
558  {
559  return;
560  }
561  }
562 
563  $manager = $this->db->loadModule('Manager');
564 
565  if ($this->getDBType() == "oracle")
566  {
567  // drop table constraints
568  $constraints = $manager->listTableConstraints($a_name);
569  $this->handleError($constraints, "dropTable(".$a_name."), listTableConstraints");
570  foreach ($constraints as $c)
571  {
572  if (substr($c, 0, 4) != "sys_")
573  {
574  $r = $manager->dropConstraint($a_name, $c);
575  $this->handleError($r, "dropTable(".$a_name."), dropConstraint");
576  }
577  }
578 
579  // drop table indexes
580  $indexes = $manager->listTableIndexes($a_name);
581  $this->handleError($indexes, "dropTable(".$a_name."), listTableIndexes");
582  foreach ($indexes as $i)
583  {
584  $r = $manager->dropIndex($a_name, $i);
585  $this->handleError($r, "dropTable(".$a_name."), dropIndex");
586  }
587  }
588 
589  // drop sequence
590  $seqs = $manager->listSequences();
591  if (in_array($a_name, $seqs))
592  {
593  $r = $manager->dropSequence($a_name);
594  $this->handleError($r, "dropTable(".$a_name."), dropSequence");
595  }
596 
597  // drop table
598  $r = $manager->dropTable($a_name);
599 
600  return $this->handleError($r, "dropTable(".$a_name.")");
601  }
602 
608  function alterTable($a_name, $a_changes)
609  {
610  if ($a_options == "")
611  {
612  $a_options = array();
613  }
614 
615  $manager = $this->db->loadModule('Manager');
616  $r = $manager->alterTable($a_name, $a_changes, false);
617 
618  return $this->handleError($r, "alterTable(".$a_name.")");
619  }
620 
629  function addTableColumn($a_table, $a_column, $a_attributes)
630  {
631 
632  $manager = $this->db->loadModule('Manager');
633 
634  if (!$this->checkColumnName($a_column))
635  {
636  $this->raisePearError("ilDB Error: addTableColumn(".$a_table.", ".$a_column.")<br />".
637  $this->error_str);
638  }
639  if (!$this->checkColumnDefinition($a_attributes))
640  {
641  $this->raisePearError("ilDB Error: addTableColumn(".$a_table.", ".$a_column.")<br />".
642  $this->error_str);
643  }
644 
645  $changes = array(
646  "add" => array(
647  $a_column => $a_attributes
648  )
649  );
650 
651  $r = $manager->alterTable($a_table, $changes, false);
652 
653  return $this->handleError($r, "addTableColumn(".$a_table.", ".$a_column.")");
654  }
655 
663  function dropTableColumn($a_table, $a_column)
664  {
665 
666  $manager = $this->db->loadModule('Manager');
667 
668  $changes = array(
669  "remove" => array(
670  $a_column => array()
671  )
672  );
673 
674  $r = $manager->alterTable($a_table, $changes, false);
675 
676  return $this->handleError($r, "dropTableColumn(".$a_table.", ".$a_column.")");
677  }
678 
687  function modifyTableColumn($a_table, $a_column, $a_attributes)
688  {
689  $manager = $this->db->loadModule('Manager');
690  $reverse = $this->db->loadModule('Reverse');
691  $def = $reverse->getTableFieldDefinition($a_table, $a_column);
692 
693  $this->handleError($def, "modifyTableColumn(".$a_table.")");
694 
695  if (is_file("./Services/Database/classes/class.ilDBAnalyzer.php"))
696  {
697  include_once("./Services/Database/classes/class.ilDBAnalyzer.php");
698  }
699  else
700  {
701  include_once("../Services/Database/classes/class.ilDBAnalyzer.php");
702  }
703  $analyzer = new ilDBAnalyzer();
704  $best_alt = $analyzer->getBestDefinitionAlternative($def);
705  $def = $def[$best_alt];
706  unset($def["nativetype"]);
707  unset($def["mdb2type"]);
708 
709  // check attributes
710  $type = ($a_attributes["type"] != "")
711  ? $a_attributes["type"]
712  : $def["type"];
713  foreach ($def as $k => $v)
714  {
715  if ($k != "type" && !in_array($k, $this->allowed_attributes[$type]))
716  {
717  unset($def[$k]);
718  }
719  }
720  $check_array = $def;
721  foreach ($a_attributes as $k => $v)
722  {
723  $check_array[$k] = $v;
724  }
725  if (!$this->checkColumnDefinition($check_array, true))
726  {
727  $this->raisePearError("ilDB Error: modifyTableColumn(".$a_table.", ".$a_column.")<br />".
728  $this->error_str);
729  }
730 
731  // oracle workaround: do not set null, if null already given
732  if ($this->getDbType() == "oracle")
733  {
734  if ($def["notnull"] == true && ($a_attributes["notnull"] == true
735  || !isset($a_attributes["notnull"])))
736  {
737  unset($def["notnull"]);
738  unset($a_attributes["notnull"]);
739  }
740  if ($def["notnull"] == false && ($a_attributes["notnull"] == false
741  || !isset($a_attributes["notnull"])))
742  {
743  unset($def["notnull"]);
744  unset($a_attributes["notnull"]);
745  }
746  }
747  foreach ($a_attributes as $a => $v)
748  {
749  $def[$a] = $v;
750  }
751 
752  $a_attributes["definition"] = $def;
753 
754  $changes = array(
755  "change" => array(
756  $a_column => $a_attributes
757  )
758  );
759 
760  $r = $manager->alterTable($a_table, $changes, false);
761 
762  return $this->handleError($r, "modifyTableColumn(".$a_table.")");
763  }
764 
773  function renameTableColumn($a_table, $a_column, $a_new_column)
774  {
775  // check table name
776  if (!$this->checkColumnName($a_new_column))
777  {
778  $this->raisePearError("ilDB Error: renameTableColumn(".$a_table.",".$a_column.",".$a_new_column.")<br />".
779  $this->error_str);
780  }
781 
782  $manager = $this->db->loadModule('Manager');
783  $reverse = $this->db->loadModule('Reverse');
784  $def = $reverse->getTableFieldDefinition($a_table, $a_column);
785 
786  $this->handleError($def, "renameTableColumn(".$a_table.",".$a_column.",".$a_new_column.")");
787 
788  if (is_file("./Services/Database/classes/class.ilDBAnalyzer.php"))
789  {
790  include_once("./Services/Database/classes/class.ilDBAnalyzer.php");
791  }
792  else
793  {
794  include_once("../Services/Database/classes/class.ilDBAnalyzer.php");
795  }
796 
797  $analyzer = new ilDBAnalyzer();
798  $best_alt = $analyzer->getBestDefinitionAlternative($def);
799  $def = $def[$best_alt];
800  unset($def["nativetype"]);
801  unset($def["mdb2type"]);
802 
803  $f["definition"] = $def;
804  $f["name"] = $a_new_column;
805 
806  $changes = array(
807  "rename" => array(
808  $a_column => $f
809  )
810  );
811 
812  $r = $manager->alterTable($a_table, $changes, false);
813 
814  return $this->handleError($r, "renameTableColumn(".$a_table.",".$a_column.",".$a_new_column.")");
815  }
816 
823  function renameTable($a_name, $a_new_name)
824  {
825  // check table name
826  if (!$this->checkTableName($a_new_name))
827  {
828  $this->raisePearError("ilDB Error: renameTable(".$a_name.",".$a_new_name.")<br />".
829  $this->error_str);
830  }
831 
832  $manager = $this->db->loadModule('Manager');
833  $r = $manager->alterTable($a_name, array("name" => $a_new_name), false);
834 
835  $query = "UPDATE abstraction_progress ".
836  "SET table_name = ".$this->db->quote($a_new_name,'text')." ".
837  "WHERE table_name = ".$this->db->quote($a_name,'text');
838  $this->db->query($query);
839 
840  return $this->handleError($r, "renameTable(".$a_name.",".$a_new_name.")");
841  }
842 
850  function addPrimaryKey($a_table, $a_fields)
851  {
852  $manager = $this->db->loadModule('Manager');
853 
854  $fields = array();
855  foreach ($a_fields as $f)
856  {
857  $fields[$f] = array();
858  }
859  $definition = array (
860  'primary' => true,
861  'fields' => $fields
862  );
863  $r = $manager->createConstraint($a_table,
864  $this->constraintName($a_table, $this->getPrimaryKeyIdentifier()), $definition);
865 
866  return $this->handleError($r, "addPrimaryKey(".$a_table.")");
867  }
868 
872  function getPrimaryKeyIdentifier()
873  {
874  return "PRIMARY";
875  }
876 
883  function dropPrimaryKey($a_table)
884  {
885  $manager = $this->db->loadModule('Manager');
886 
887  $r = $manager->dropConstraint($a_table,
888  $this->constraintName($a_table, $this->getPrimaryKeyIdentifier()), true);
889 
890  return $this->handleError($r, "dropPrimaryKey(".$a_table.")");
891  }
892 
900  function addIndex($a_table, $a_fields, $a_name = "in", $a_fulltext = false)
901  {
902  $manager = $this->db->loadModule('Manager');
903 
904  // check index name
905  if (!$this->checkIndexName($a_name))
906  {
907  $this->raisePearError("ilDB Error: addIndex(".$a_table.",".$a_name.")<br />".
908  $this->error_str);
909  }
910 
911  $fields = array();
912  foreach ($a_fields as $f)
913  {
914  $fields[$f] = array();
915  }
916  $definition = array (
917  'fields' => $fields
918  );
919 
920  if (!$a_fulltext)
921  {
922  $r = $manager->createIndex($a_table, $this->constraintName($a_table, $a_name), $definition);
923  }
924  else
925  {
926  if ($this->supportsFulltext())
927  {
928  $this->addFulltextIndex($a_table, $a_fields, $a_name);
929  }
930  }
931 
932  return $this->handleError($r, "addIndex(".$a_table.")");
933  }
934 
938  function addFulltextIndex($a_table, $a_fields, $a_name = "in")
939  {
940  return false;
941  }
942 
946  function isFulltextIndex($a_table, $a_name)
947  {
948  return false;
949  }
950 
951 
957  public function indexExistsByFields($a_table, $a_fields)
958  {
959  $manager = $this->db->loadModule('Manager');
960  $reverse = $this->db->loadModule('Reverse');
961  if($manager)
962  {
963  foreach($manager->listTableIndexes($a_table) as $idx_name)
964  {
965  $def = $reverse->getTableIndexDefinition($a_table,$idx_name);
966  $idx_fields = array_keys((array) $def['fields']);
967 
968  if($idx_fields === $a_fields)
969  {
970  return true;
971  }
972  }
973  }
974  return false;
975  }
976 
983  public function dropIndexByFields($a_table, $a_fields)
984  {
985  $manager = $this->db->loadModule('Manager');
986  $reverse = $this->db->loadModule('Reverse');
987  if($manager)
988  {
989  foreach($manager->listTableIndexes($a_table) as $idx_name)
990  {
991  $def = $reverse->getTableIndexDefinition($a_table,$idx_name);
992  $idx_fields = array_keys((array) $def['fields']);
993 
994  if($idx_fields === $a_fields)
995  {
996  return $this->dropIndex($a_table, $idx_name);
997  }
998  }
999  }
1000  return false;
1001 
1002  }
1003 
1011  function dropIndex($a_table, $a_name = "in")
1012  {
1013  $manager = $this->db->loadModule('Manager');
1014 
1015  if (!$this->isFulltextIndex($a_table, $a_name))
1016  {
1017  $r = $manager->dropIndex($a_table, $this->constraintName($a_table, $a_name));
1018  }
1019  else
1020  {
1021  $this->dropFulltextIndex($a_table, $a_name);
1022  }
1023 
1024  return $this->handleError($r, "dropIndex(".$a_table.")");
1025  }
1026 
1034  function addUniqueConstraint($a_table, $a_fields, $a_name = "con")
1035  {
1036  $manager = $this->db->loadModule('Manager');
1037 
1038  // check index name
1039  if (!$this->checkIndexName($a_name))
1040  {
1041  $this->raisePearError("ilDB Error: addUniqueConstraint(".$a_table.",".$a_name.")<br />".
1042  $this->error_str);
1043  }
1044 
1045  $fields = array();
1046  foreach ($a_fields as $f)
1047  {
1048  $fields[$f] = array();
1049  }
1050  $definition = array (
1051  'unique' => true,
1052  'fields' => $fields
1053  );
1054 
1055  $r = $manager->createConstraint($a_table, $this->constraintName($a_table, $a_name), $definition);
1056 
1057  return $this->handleError($r, "addUniqueConstraint(".$a_table.")");
1058  }
1059 
1063  function createSequence($a_table_name, $a_start = 1)
1064  {
1065  $manager = $this->db->loadModule('Manager');
1066 
1067  $r = $manager->createSequence($a_table_name, $a_start);
1068 
1069  return $this->handleError($r, "createSequence(".$a_table_name.")");
1070  }
1071 
1072 
1076  function dropSequence($a_table_name)
1077  {
1078  $manager = $this->db->loadModule('Manager');
1079 
1080  $r = $manager->dropSequence($a_table_name);
1081 
1082  return $this->handleError($r, "dropSequence(".$a_table_name.")");
1083  }
1084 
1090  function checkTableName($a_name)
1091  {
1092  if (!preg_match ("/^[a-z]+[_a-z0-9]*$/", $a_name))
1093  {
1094  $this->error_str = "Table name must only contain _a-z0-9 and must start with a-z.";
1095  return false;
1096  }
1097 
1098  if ($this->isReservedWord($a_name))
1099  {
1100  $this->error_str = "Invalid table name '".$a_name."' (Reserved Word).";
1101  return false;
1102  }
1103 
1104  if (strtolower(substr($a_name, 0, 4)) == "sys_")
1105  {
1106  $this->error_str = "Invalid table name '".$a_name."'. Name must not start with 'sys_'.";
1107  return false;
1108  }
1109 
1110  if (strlen($a_name) > 22)
1111  {
1112  $this->error_str = "Invalid table name '".$a_name."'. Maximum table identifer lenght is 22 bytes.";
1113  return false;
1114  }
1115 
1116  return true;
1117  }
1118 
1124  function checkTableColumns($a_cols)
1125  {
1126  foreach ($a_cols as $col => $def)
1127  {
1128  if (!$this->checkColumn($col, $def))
1129  {
1130  return false;
1131  }
1132  }
1133 
1134  return true;
1135  }
1136 
1140  function checkColumn($a_col, $a_def)
1141  {
1142  if (!$this->checkColumnName($a_col))
1143  {
1144  return false;
1145  }
1146 
1147  if (!$this->checkColumnDefinition($a_def))
1148  {
1149  return false;
1150  }
1151 
1152  return true;
1153  }
1154 
1160  function checkColumnDefinition($a_def, $a_modify_mode = false)
1161  {
1162  // check valid type
1163  if (!in_array($a_def["type"], array("text", "integer", "float", "date", "time", "timestamp", "clob", "blob")))
1164  {
1165  switch ($a_def["type"])
1166  {
1167  case "boolean":
1168  $this->error_str = "Invalid column type '".$a_def["type"]."'. Use integer(1) instead.";
1169  break;
1170 
1171  case "decimal":
1172  $this->error_str = "Invalid column type '".$a_def["type"]."'. Use float or integer instead.";
1173  break;
1174 
1175  default:
1176  $this->error_str = "Invalid column type '".$a_def["type"]."'. Allowed types are: ".
1177  "text, integer, float, date, time, timestamp, clob and blob.";
1178  }
1179  }
1180 
1181  // check used attributes
1182  $allowed_attributes = $this->allowed_attributes;
1183 
1184  foreach ($a_def as $k => $v)
1185  {
1186  if ($k != "type" && !in_array($k, $allowed_attributes[$a_def["type"]]))
1187  {
1188  $this->error_str = "Attribute '".$k."' is not allowed for column type '".$a_def["type"]."'.";
1189  return false;
1190  }
1191  }
1192 
1193  // type specific checks
1194  switch ($a_def["type"])
1195  {
1196  case "text":
1197  if ($a_def["length"] < 1 || $a_def["length"] > 4000)
1198  {
1199  if (!$a_modify_mode || isset($a_def["length"]))
1200  {
1201  $this->error_str = "Invalid length '".$a_def["length"]."' for type text.".
1202  " Length must be >=1 and <= 4000.";
1203  return false;
1204  }
1205  }
1206  break;
1207 
1208  case "integer":
1209  if (!in_array($a_def["length"], array(1, 2, 3, 4, 8)))
1210  {
1211  if (!$a_modify_mode || isset($a_def["length"]))
1212  {
1213  $this->error_str = "Invalid length '".$a_def["length"]."' for type integer.".
1214  " Length must be 1, 2, 3, 4 or 8 (bytes).";
1215  return false;
1216  }
1217  }
1218  if ($a_def["unsigned"])
1219  {
1220  $this->error_str = "Unsigned attribut must not be true for type integer.";
1221  return false;
1222  }
1223  break;
1224  }
1225 
1226  return true;
1227  }
1228 
1234  function checkColumnName($a_name)
1235  {
1236  if (!preg_match ("/^[a-z]+[_a-z0-9]*$/", $a_name))
1237  {
1238  $this->error_str = "Invalid column name '".$a_name."'. Column name must only contain _a-z0-9 and must start with a-z.";
1239  return false;
1240  }
1241 
1242  if ($this->isReservedWord($a_name))
1243  {
1244  $this->error_str = "Invalid column name '".$a_name."' (Reserved Word).";
1245  return false;
1246  }
1247 
1248  if (strtolower(substr($a_name, 0, 4)) == "sys_")
1249  {
1250  $this->error_str = "Invalid column name '".$a_name."'. Name must not start with 'sys_'.";
1251  return false;
1252  }
1253 
1254  if (strlen($a_name) > 30)
1255  {
1256  $this->error_str = "Invalid column name '".$a_name."'. Maximum column identifer lenght is 30 bytes.";
1257  return false;
1258  }
1259 
1260  return true;
1261  }
1262 
1268  function checkIndexName($a_name)
1269  {
1270  if (!preg_match ("/^[a-z]+[_a-z0-9]*$/", $a_name))
1271  {
1272  $this->error_str = "Invalid column name '".$a_name."'. Column name must only contain _a-z0-9 and must start with a-z.";
1273  return false;
1274  }
1275 
1276  if ($this->isReservedWord($a_name))
1277  {
1278  $this->error_str = "Invalid column name '".$a_name."' (Reserved Word).";
1279  return false;
1280  }
1281 
1282  if (strlen($a_name) > 3)
1283  {
1284  $this->error_str = "Invalid index name '".$a_name."'. Maximum index identifer lenght is 3 bytes.";
1285  return false;
1286  }
1287 
1288  return true;
1289  }
1290 
1291  function getAllowedAttributes()
1292  {
1293  return $this->allowed_attributes;
1294  }
1295 
1301  function constraintName($a_table, $a_constraint)
1302  {
1303  return $a_constraint;
1304  }
1305 
1310  static function isReservedWord($a_word)
1311  {
1312  include_once("./Services/Database/classes/class.ilDBMySQL.php");
1313  $mysql_reserved_words = ilDBMySQL::getReservedWords();
1314  if (in_array(strtoupper($a_word), $mysql_reserved_words))
1315  {
1316  return true;
1317  }
1318  include_once("./Services/Database/classes/class.ilDBOracle.php");
1319  $oracle_reserved_words = ilDBOracle::getReservedWords();
1320  if (in_array(strtoupper($a_word), $oracle_reserved_words))
1321  {
1322  return true;
1323  }
1324  include_once("./Services/Database/classes/class.ilDBPostgreSQL.php");
1325  $postgres_reserved_words = ilDBPostgreSQL::getReservedWords();
1326  if (in_array(strtoupper($a_word), $postgres_reserved_words))
1327  {
1328  return true;
1329  }
1330  }
1331 
1332  //
1333  // Data query and manupilation functions
1334  //
1335 
1347  function query($sql, $a_handle_error = true)
1348  {
1349  global $ilBench;
1350 
1351  if (is_object($ilBench))
1352  {
1353  $ilBench->startDbBench($sql);
1354  }
1355  $r = $this->db->query($sql);
1356  if (is_object($ilBench))
1357  {
1358  $ilBench->stopDbBench();
1359  }
1360 
1361  if ($a_handle_error)
1362  {
1363  return $this->handleError($r, "query(".$sql.")");
1364  }
1365 
1366  return $r;
1367  }
1368 
1376  function queryF($a_query, $a_types, $a_values)
1377  {
1378  if (!is_array($a_types) || !is_array($a_values) ||
1379  count($a_types) != count($a_values))
1380  {
1381  $this->raisePearError("ilDB::queryF: Types and values must be arrays of same size. ($a_query)");
1382  }
1383  $quoted_values = array();
1384  foreach($a_types as $k => $t)
1385  {
1386  $quoted_values[] = $this->quote($a_values[$k], $t);
1387  }
1388  $query = vsprintf($a_query, $quoted_values);
1389 
1390  return $this->query($query);
1391  }
1392 
1400  function manipulateF($a_query, $a_types, $a_values)
1401  {
1402  if (!is_array($a_types) || !is_array($a_values) ||
1403  count($a_types) != count($a_values))
1404  {
1405  $this->raisePearError("ilDB::manipulateF: types and values must be arrays of same size. ($a_query)");
1406  }
1407  $quoted_values = array();
1408  foreach($a_types as $k => $t)
1409  {
1410  $quoted_values[] = $this->quote($a_values[$k], $t);
1411  }
1412  $query = vsprintf($a_query, $quoted_values);
1413 
1414  return $this->manipulate($query);
1415  }
1416 
1420  function logStatement($sql)
1421  {
1422  $pos1 = strpos(strtolower($sql), "from ");
1423  $table = "";
1424  if ($pos1 > 0)
1425  {
1426  $tablef = substr($sql, $pos1+5);
1427  $pos2 = strpos(strtolower($tablef), " ");
1428  if ($pos2 > 0)
1429  {
1430  $table =substr($tablef, 0, $pos2);
1431  }
1432  else
1433  {
1434  $table = $tablef;
1435  }
1436  }
1437  if (trim($table) != "")
1438  {
1439  if (!is_array($this->ttt) || !in_array($table, $this->ttt))
1440  {
1441  echo "<br>".$table;
1442  $this->ttt[] = $table;
1443  }
1444  }
1445  else
1446  {
1447  echo "<br><b>".$sql."</b>";
1448  }
1449  }
1450 
1454  function setLimit($a_limit, $a_offset = 0)
1455  {
1456  $this->db->setLimit($a_limit, $a_offset);
1457  }
1458 
1462  function nextId($a_table_name)
1463  {
1464  // we do not create missing sequences automatically here
1465  // otherwise misspelled statements result in additional tables
1466  // please create sequences explicitly in the db update script
1467  $r = $this->db->nextId($a_table_name, false);
1468 
1469  return $this->handleError($r, "nextId(".$a_table_name.")");
1470  }
1471 
1482  function manipulate($sql)
1483  {
1484  global $ilBench;
1485 
1486  if (is_object($ilBench))
1487  {
1488  $ilBench->startDbBench($sql);
1489  }
1490  $r = $this->db->exec($sql);
1491  if (is_object($ilBench))
1492  {
1493  $ilBench->stopDbBench();
1494  }
1495 
1496  return $this->handleError($r, "manipulate(".$sql.")");
1497  }
1498 
1507  function prepare($a_query, $a_types = null, $a_result_types = null)
1508  {
1509  $res = $this->db->prepare($a_query, $a_types, $a_result_types);
1510 
1511  return $this->handleError($res, "prepare(".$a_query.")");
1512  }
1513 
1522  function prepareManip($a_query, $a_types = null)
1523  {
1524  $res = $this->db->prepare($a_query, $a_types, MDB2_PREPARE_MANIP);
1525 
1526  return $this->handleError($res, "prepareManip(".$a_query.")");
1527  }
1528 
1537  function execute($a_stmt, $a_data = null)
1538  {
1539  $res = $a_stmt->execute($a_data);
1540 
1541  return $this->handleError($res, "execute(".$a_stmt->query.")");
1542  }
1543 
1553  function executeMultiple($a_stmt, $a_data)
1554  {
1555  $res = $this->db->extended->executeMultiple($a_stmt,$a_data);
1556 
1557  return $this->handleError($res, "executeMultiple(".$a_stmt->query.")");
1558  }
1559 
1566  function insert($a_table, $a_columns)
1567  {
1568  $fields = array();
1569  $field_values = array();
1570  $placeholders = array();
1571  $types = array();
1572  $values = array();
1573  $lobs = false;
1574  $lob = array();
1575  foreach ($a_columns as $k => $col)
1576  {
1577  $fields[] = $k;
1578  $placeholders[] = "%s";
1579  $placeholders2[] = ":$k";
1580  $types[] = $col[0];
1581 
1582  // integer auto-typecast (this casts bool values to integer)
1583  if ($col[0] == 'integer' && !is_null($col[1]))
1584  {
1585  $col[1] = (int) $col[1];
1586  }
1587 
1588  $values[] = $col[1];
1589  $field_values[$k] = $col[1];
1590  if ($col[0] == "blob" || $col[0] == "clob")
1591  {
1592  $lobs = true;
1593  $lob[$k] = $k;
1594  }
1595  }
1596  if ($lobs) // lobs -> use prepare execute (autoexecute broken in PEAR 2.4.1)
1597  {
1598  $st = $this->db->prepare("INSERT INTO ".$a_table." (".implode($fields,",").") VALUES (".
1599  implode($placeholders2,",").")", $types, MDB2_PREPARE_MANIP, $lob);
1600 
1601  $this->handleError($st, "insert / prepare/execute(".$a_table.")");
1602 
1603  $r = $st->execute($field_values);
1604 
1605 
1606  //$r = $this->db->extended->autoExecute($a_table, $field_values, MDB2_AUTOQUERY_INSERT, null, $types);
1607  $this->handleError($r, "insert / prepare/execute(".$a_table.")");
1608  $this->free($st);
1609  }
1610  else // if no lobs are used, take simple manipulateF
1611  {
1612  $q = "INSERT INTO ".$a_table." (".implode($fields,",").") VALUES (".
1613  implode($placeholders,",").")";
1614  $r = $this->manipulateF($q, $types, $values);
1615  }
1616  return $r;
1617  }
1618 
1627  function update($a_table, $a_columns, $a_where)
1628  {
1629  $fields = array();
1630  $field_values = array();
1631  $placeholders = array();
1632  $types = array();
1633  $values = array();
1634  $lobs = false;
1635  $lob = array();
1636  foreach ($a_columns as $k => $col)
1637  {
1638  $fields[] = $k;
1639  $placeholders[] = "%s";
1640  $placeholders2[] = ":$k";
1641  $types[] = $col[0];
1642 
1643  // integer auto-typecast (this casts bool values to integer)
1644  if ($col[0] == 'integer' && !is_null($col[1]))
1645  {
1646  $col[1] = (int) $col[1];
1647  }
1648 
1649  $values[] = $col[1];
1650  $field_values[$k] = $col[1];
1651  if ($col[0] == "blob" || $col[0] == "clob")
1652  {
1653  $lobs = true;
1654  $lob[$k] = $k;
1655  }
1656  }
1657 
1658  if ($lobs)
1659  {
1660  $q = "UPDATE ".$a_table." SET ";
1661  $lim = "";
1662  foreach ($fields as $k => $field)
1663  {
1664  $q.= $lim.$field." = ".$placeholders2[$k];
1665  $lim = ", ";
1666  }
1667  $q.= " WHERE ";
1668  $lim = "";
1669  foreach ($a_where as $k => $col)
1670  {
1671  $q.= $lim.$k." = ".$this->quote($col[1], $col[0]);
1672  $lim = " AND ";
1673  }
1674  $st = $this->db->prepare($q, $types, MDB2_PREPARE_MANIP, $lob);
1675  $r = $st->execute($field_values);
1676 
1677  //$r = $this->db->extended->autoExecute($a_table, $field_values, MDB2_AUTOQUERY_INSERT, null, $types);
1678  $this->handleError($r, "update / prepare/execute(".$a_table.")");
1679  $this->free($st);
1680  }
1681  else
1682  {
1683  foreach ($a_where as $k => $col)
1684  {
1685  $types[] = $col[0];
1686  $values[] = $col[1];
1687  $field_values[$k] = $col;
1688  }
1689  $q = "UPDATE ".$a_table." SET ";
1690  $lim = "";
1691  foreach ($fields as $k => $field)
1692  {
1693  $q.= $lim.$field." = ".$placeholders[$k];
1694  $lim = ", ";
1695  }
1696  $q.= " WHERE ";
1697  $lim = "";
1698  foreach ($a_where as $k => $col)
1699  {
1700  $q.= $lim.$k." = %s";
1701  $lim = " AND ";
1702  }
1703 
1704  $r = $this->manipulateF($q, $types, $values);
1705  }
1706  return $r;
1707  }
1708 
1716  function replace($a_table, $a_pk_columns, $a_other_columns)
1717  {
1718  // this is the mysql implementation
1719  $a_columns = array_merge($a_pk_columns, $a_other_columns);
1720  $fields = array();
1721  $field_values = array();
1722  $placeholders = array();
1723  $types = array();
1724  $values = array();
1725  $lobs = false;
1726  $lob = array();
1727  foreach ($a_columns as $k => $col)
1728  {
1729  $fields[] = $k;
1730  $placeholders[] = "%s";
1731  $placeholders2[] = ":$k";
1732  $types[] = $col[0];
1733 
1734  // integer auto-typecast (this casts bool values to integer)
1735  if ($col[0] == 'integer' && !is_null($col[1]))
1736  {
1737  $col[1] = (int) $col[1];
1738  }
1739 
1740  $values[] = $col[1];
1741  $field_values[$k] = $col[1];
1742  if ($col[0] == "blob" || $col[0] == "clob")
1743  {
1744  $lobs = true;
1745  $lob[$k] = $k;
1746  }
1747  }
1748  if ($lobs) // lobs -> use prepare execute (autoexecute broken in PEAR 2.4.1)
1749  {
1750  $st = $this->db->prepare("REPLACE INTO ".$a_table." (".implode($fields,",").") VALUES (".
1751  implode($placeholders2,",").")", $types, MDB2_PREPARE_MANIP, $lob);
1752  $this->handleError($st, "insert / prepare/execute(".$a_table.")");
1753  $r = $st->execute($field_values);
1754  //$r = $this->db->extended->autoExecute($a_table, $field_values, MDB2_AUTOQUERY_INSERT, null, $types);
1755  $this->handleError($r, "insert / prepare/execute(".$a_table.")");
1756  $this->free($st);
1757  }
1758  else // if no lobs are used, take simple manipulateF
1759  {
1760  $q = "REPLACE INTO ".$a_table." (".implode($fields,",").") VALUES (".
1761  implode($placeholders,",").")";
1762  $r = $this->manipulateF($q, $types, $values);
1763  }
1764  return $r;
1765  }
1766 
1772  function fetchAssoc($a_set)
1773  {
1774  return $a_set->fetchRow(DB_FETCHMODE_ASSOC);
1775  }
1776 
1780  function free($a_st)
1781  {
1782  return $a_st->free();
1783  }
1784 
1790  function fetchObject($a_set)
1791  {
1792  return $a_set->fetchRow(DB_FETCHMODE_OBJECT);
1793  }
1794 
1800  function numRows($a_set)
1801  {
1802  return $a_set->numRows();
1803  }
1804 
1805  //
1806  // function and clauses abstraction
1807  //
1808 
1820  function in($a_field, $a_values, $negate = false, $a_type = "")
1821  {
1822  if (count($a_values) == 0)
1823  {
1824  return " 1=2 "; // return a false statement on empty array
1825  }
1826  if ($a_type == "") // untyped: used ? for prepare/execute
1827  {
1828  $str = $a_field.(($negate) ? " NOT" : "")." IN (?".str_repeat(",?", count($a_values) - 1).")";
1829  }
1830  else // typed, use values for query/manipulate
1831  {
1832  $str = $a_field.(($negate) ? " NOT" : "")." IN (";
1833  $sep = "";
1834  foreach ($a_values as $v)
1835  {
1836  $str.= $sep.$this->quote($v, $a_type);
1837  $sep = ",";
1838  }
1839  $str.= ")";
1840  }
1841 
1842  return $str;
1843  }
1844 
1848  function addTypesToArray($a_arr, $a_type, $a_cnt)
1849  {
1850  if (!is_array($a_arr))
1851  {
1852  $a_arr = array();
1853  }
1854  if ($a_cnt > 0)
1855  {
1856  $type_arr = array_fill(0, $a_cnt, $a_type);
1857  }
1858  else
1859  {
1860  $type_arr = array();
1861  }
1862  return array_merge($a_arr, $type_arr);
1863  }
1864 
1869  function now()
1870  {
1871  return "now()";
1872  }
1873 
1874 
1884  public function concat($a_values,$a_allow_null = true)
1885  {
1886  if(!count($a_values))
1887  {
1888  return ' ';
1889  }
1890 
1891  $concat = ' CONCAT(';
1892  $first = true;
1893  foreach($a_values as $field_info)
1894  {
1895  $val = $field_info[0];
1896 
1897  if(!$first)
1898  {
1899  $concat .= ',';
1900  }
1901 
1902  if($a_allow_null)
1903  {
1904  $concat .= 'COALESCE(';
1905  }
1906  $concat .= $val;
1907 
1908  if($a_allow_null)
1909  {
1910  $concat .= ",''";
1911  $concat .= ')';
1912  }
1913 
1914  $first = false;
1915  }
1916  $concat .= ') ';
1917  return $concat;
1918  }
1919 
1926  function substr($a_exp, $a_pos = 1, $a_len = -1)
1927  {
1928  $lenstr = "";
1929  if ($a_len > -1)
1930  {
1931  $lenstr = ", ".$a_len;
1932  }
1933  return " SUBSTR(".$a_exp.", ".$a_pos.$lenstr.") ";
1934  }
1935 
1942  function upper($a_exp)
1943  {
1944  return " UPPER(".$a_exp.") ";
1945  }
1946 
1953  function lower($a_exp)
1954  {
1955  return " LOWER(".$a_exp.") ";
1956  }
1957 
1965  public function locate($a_needle,$a_string,$a_start_pos = 1)
1966  {
1967  $locate = ' LOCATE( ';
1968  $locate .= $a_needle;
1969  $locate .= ',';
1970  $locate .= $a_string;
1971  $locate .= ',';
1972  $locate .= $a_start_pos;
1973  $locate .= ') ';
1974  return $locate;
1975  }
1976 
1977 
1983  function like($a_col, $a_type, $a_value = "?", $case_insensitive = true)
1984  {
1985  if (!in_array($a_type, array("text", "clob", "blob")))
1986  {
1987  $this->raisePearError("Like: Invalid column type '".$a_type."'.", $this->error_class->FATAL);
1988  }
1989  if ($a_value == "?")
1990  {
1991  if ($case_insensitive)
1992  {
1993  return "UPPER(".$a_col.") LIKE(UPPER(?))";
1994  }
1995  else
1996  {
1997  return $a_col ." LIKE(?)";
1998  }
1999  }
2000  else
2001  {
2002  if ($case_insensitive)
2003  {
2004  // Always quote as text
2005  return " UPPER(".$a_col.") LIKE(UPPER(".$this->quote($a_value, 'text')."))";
2006  }
2007  else
2008  {
2009  // Always quote as text
2010  return " ".$a_col." LIKE(".$this->quote($a_value, 'text').")";
2011  }
2012  }
2013  }
2014 
2015 
2019  function equals($a_col, $a_value, $a_type, $a_empty_or_null = false)
2020  {
2021  if (!$a_empty_or_null || $a_value != "")
2022  {
2023  return $a_col." = ".$this->quote($a_value, $a_type);
2024  }
2025  else
2026  {
2027  return "(".$a_col." = '' OR $a_col IS NULL)";
2028  }
2029  }
2030 
2034  function equalsNot($a_col, $a_value, $a_type, $a_empty_or_null = false)
2035  {
2036  if (!$a_empty_or_null)
2037  {
2038  return $a_col." <> ".$this->quote($a_value, $a_type);
2039  }
2040  if ($a_value != "")
2041  {
2042  return "(".$a_col." <> ".$this->quote($a_value, $a_type). " OR ".
2043  $a_col." IS NULL)";
2044  }
2045  else
2046  {
2047  return "(".$a_col." <> '' AND $a_col IS NOT NULL)";
2048  }
2049  }
2050 
2057  function fromUnixtime($a_expr, $a_to_text = true)
2058  {
2059  return "FROM_UNIXTIME(".$a_expr.")";
2060  }
2061 
2065  function unixTimestamp()
2066  {
2067  return "UNIX_TIMESTAMP()";
2068  }
2069 
2073  function optimizeTable($a_table)
2074  {
2075  // needs to be overwritten in DBMS specific class
2076  // if necessary and possible
2077  }
2078 
2079  //
2080  // Schema related functions
2081  //
2082 
2089  function tableExists($a_table)
2090  {
2091  $tables = $this->listTables();
2092 
2093  if (is_array($tables))
2094  {
2095  if (in_array($a_table, $tables))
2096  {
2097  return true;
2098  }
2099  }
2100  return false;
2101  }
2102 
2110  function tableColumnExists($a_table, $a_column_name)
2111  {
2112 
2113  $column_visibility = false;
2114  $manager = $this->db->loadModule('Manager');
2115  $r = $manager->listTableFields($a_table);
2116 
2117  if (!MDB2::isError($r))
2118  {
2119  foreach($r as $field)
2120  {
2121  if ($field == $a_column_name)
2122  {
2123  $column_visibility = true;
2124  }
2125  }
2126  }
2127 
2128  return $column_visibility;
2129  }
2130 
2138  function uniqueConstraintExists($a_table, $a_fields)
2139  {
2140  if (is_file("./Services/Database/classes/class.ilDBAnalyzer.php"))
2141  {
2142  include_once("./Services/Database/classes/class.ilDBAnalyzer.php");
2143  }
2144  else
2145  {
2146  include_once("../Services/Database/classes/class.ilDBAnalyzer.php");
2147  }
2148  $analyzer = new ilDBAnalyzer();
2149  $cons = $analyzer->getConstraintsInformation($a_table);
2150  foreach ($cons as $c)
2151  {
2152  if ($c["type"] == "unique" && count($a_fields) == count($c["fields"]))
2153  {
2154  $all_in = true;
2155  foreach ($a_fields as $f)
2156  {
2157  if (!isset($c["fields"][$f]))
2158  {
2159  $all_in = false;
2160  }
2161  }
2162  if ($all_in)
2163  {
2164  return true;
2165  }
2166  }
2167  }
2168  return false;
2169  }
2170 
2171 
2177  function listTables()
2178  {
2179  $manager = $this->db->loadModule('Manager');
2180  $r = $manager->listTables();
2181 
2182  if (!MDB2::isError($r))
2183  {
2184  return $r;
2185  }
2186 
2187  return false;
2188  }
2189 
2196  function sequenceExists($a_sequence)
2197  {
2198  $sequences = $this->listSequences();
2199 
2200  if (is_array($sequences))
2201  {
2202  if (in_array($a_sequence, $sequences))
2203  {
2204  return true;
2205  }
2206  }
2207  return false;
2208  }
2209 
2215  function listSequences()
2216  {
2217  $manager = $this->db->loadModule('Manager');
2218  $r = $manager->listSequences();
2219 
2220  if (!MDB2::isError($r))
2221  {
2222  return $r;
2223  }
2224 
2225  return false;
2226  }
2227 
2228 
2229  //
2230  // Quote Functions
2231  //
2232 
2236  function quote($a_query, $a_type = null)
2237  {
2238  if ($a_query == "" && is_null($a_type))
2239  {
2240  $a_query = "";
2241  }
2242 
2243  // Performance fix
2244  if($a_type == 'integer' && !is_null($a_query))
2245  {
2246  return (int) $a_query;
2247  }
2248 
2249  if ($a_type == "blob" || $a_type == "clob")
2250  {
2251  $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);
2252  }
2253 
2254  return $this->db->quote($a_query, $a_type);
2255  }
2256 
2264  function quoteIdentifier($a_identifier)
2265  {
2266  return $this->db->quoteIdentifier($a_identifier);
2267  }
2268 
2269 
2270  //
2271  // Transaction and Locking methods
2272  //
2273 
2279  function beginTransaction()
2280  {
2281  if (!$this->db->supports('transactions'))
2282  {
2283  $this->raisePearError("ilDB::beginTransaction: Transactions are not supported.", $this->error_class->FATAL);
2284  }
2285  $res = $this->db->beginTransaction();
2286 
2287  return $this->handleError($res, "beginTransaction()");
2288  }
2289 
2293  function commit()
2294  {
2295  $res = $this->db->commit();
2296 
2297  return $this->handleError($res, "commit()");
2298  }
2299 
2303  function rollback()
2304  {
2305  $res = $this->db->rollback();
2306 
2307  return $this->handleError($res, "rollback()");
2308  }
2309 
2315  abstract public function lockTables($a_tables);
2316 
2321  abstract public function unlockTables();
2322 
2323 
2324 //
2325 //
2326 // Older functions. Must be checked.
2327 //
2328 //
2329 
2338  function autoExecute($a_tablename,$a_fields,$a_mode = MDB2_AUTOQUERY_INSERT,$a_where = false)
2339  {
2340  $res = $this->db->autoExecute($a_tablename,$a_fields,$a_mode,$a_where);
2341 
2342  return $this->handleError($res, "autoExecute(".$a_tablename.")");
2343  }
2344 
2345 //
2346 //
2347 // Deprecated functions.
2348 //
2349 //
2350 
2354  function getLastInsertId()
2355  {
2356  $res = $this->db->lastInsertId();
2357  if(MDB2::isError($res))
2358  {
2359  return false;
2360  }
2361  return $res;
2362  }
2363 
2373  function getOne($sql)
2374  {
2375  //$r = $this->db->getOne($sql);
2376  $set = $this->db->query($sql);
2377 
2378  $this->handleError($set, "getOne(".$sql.")");
2379 
2380  if (!MDB2::isError($set))
2381  {
2382  $r = $set->fetchRow(DB_FETCHMODE_ASSOC);
2383 
2384  return $r[0];
2385  }
2386  }
2387 
2397  function getRow($sql,$mode = DB_FETCHMODE_OBJECT)
2398  {
2399  $set = $this->query($sql);
2400  $r = $set->fetchRow($mode);
2401  //$r = $this->db->getrow($sql,$mode);
2402 
2403  $this->handleError($r, "getRow(".$sql.")");
2404 
2405  return $r;
2406  } //end function
2407 
2413  function setSubType($a_value)
2414  {
2415  $this->sub_type = (string)$a_value;
2416  }
2417 
2423  function getSubType()
2424  {
2425  return $this->sub_type;
2426  }
2427 
2428 } //end Class
2429 ?>