8 include_once (
"Services/PEAR/lib/MDB2.php");
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")
85 $this->db_user = $a_user;
105 $this->db_port = $a_port;
115 return $this->db_port;
125 $this->db_host = $a_host;
145 $this->db_password = $a_password;
155 return $this->db_password;
165 $this->db_name = $a_name;
181 abstract function getDSN();
211 $this->db->setOption(
'result_buffering',$a_status);
220 global $ilClientIniFile;
223 if (is_object($tmpClientIniFile))
224 $clientIniFile = $tmpClientIniFile;
226 $clientIniFile = $ilClientIniFile;
228 if (is_object($clientIniFile ))
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"));
241 function connect($a_return_false_for_error =
false)
248 if ($this->
getDSN() ==
"")
283 array(
"use_transactions" =>
true));
291 $this->db->disconnect();
334 array(
"use_transactions" =>
true));
346 $this->db->setOption(
"portability", $this->db->getOption(
"portability") - $cur);
349 $this->db->setOption(
"portability", $this->db->getOption(
"portability") - $cur);
396 $this->use_slave = $a_val;
413 $a_level = $this->error_class->FATAL;
428 $stack = $e->getTraceAsString();
431 if(is_object($ilLog))
434 $a_res->getMessage().
"<br />".$a_res->getUserInfo().
"<br />".$stack, $a_level);
489 $a_level = $this->error_class->FATAL;
504 $this->db->loadModule(
'Extended');
529 if ($a_collation !=
"")
531 $sql =
"CREATE DATABASE ".$a_name.
532 " CHARACTER SET ".$a_charset.
533 " COLLATE ".$a_collation;
537 $sql =
"CREATE DATABASE ".$a_name.
538 " CHARACTER SET ".$a_charset;
541 return $this->
query($sql,
false);
552 function createTable($a_name, $a_definition_array, $a_drop_table =
false,
553 $a_ignore_erros =
false)
558 $this->
raisePearError(
"ilDB Error: createTable(".$a_name.
")<br />".
565 $this->
raisePearError(
"ilDB Error: createTable(".$a_name.
")<br />".
576 $manager = $this->db->loadModule(
'Manager');
577 $r = $manager->createTable($a_name, $a_definition_array,
$options);
598 function dropTable($a_name, $a_error_if_not_existing =
true)
600 if (!$a_error_if_not_existing)
603 if (!in_array($a_name, $tables))
609 $manager = $this->db->loadModule(
'Manager');
614 $constraints = $manager->listTableConstraints($a_name);
615 $this->
handleError($constraints,
"dropTable(".$a_name.
"), listTableConstraints");
616 foreach ($constraints as $c)
618 if (
substr($c, 0, 4) !=
"sys_")
620 $r = $manager->dropConstraint($a_name, $c);
621 $this->
handleError(
$r,
"dropTable(".$a_name.
"), dropConstraint");
626 $indexes = $manager->listTableIndexes($a_name);
627 $this->
handleError($indexes,
"dropTable(".$a_name.
"), listTableIndexes");
628 foreach ($indexes as $i)
630 $r = $manager->dropIndex($a_name, $i);
636 $seqs = $manager->listSequences();
637 if (in_array($a_name, $seqs))
639 $r = $manager->dropSequence($a_name);
640 $this->
handleError(
$r,
"dropTable(".$a_name.
"), dropSequence");
644 $r = $manager->dropTable($a_name);
656 if ($a_options ==
"")
658 $a_options = array();
661 $manager = $this->db->loadModule(
'Manager');
662 $r = $manager->alterTable($a_name, $a_changes,
false);
678 $manager = $this->db->loadModule(
'Manager');
682 $this->
raisePearError(
"ilDB Error: addTableColumn(".$a_table.
", ".$a_column.
")<br />".
687 $this->
raisePearError(
"ilDB Error: addTableColumn(".$a_table.
", ".$a_column.
")<br />".
693 $a_column => $a_attributes
697 $r = $manager->alterTable($a_table, $changes,
false);
699 return $this->
handleError(
$r,
"addTableColumn(".$a_table.
", ".$a_column.
")");
712 $manager = $this->db->loadModule(
'Manager');
720 $r = $manager->alterTable($a_table, $changes,
false);
722 return $this->
handleError(
$r,
"dropTableColumn(".$a_table.
", ".$a_column.
")");
735 $manager = $this->db->loadModule(
'Manager');
736 $reverse = $this->db->loadModule(
'Reverse');
737 $def = $reverse->getTableFieldDefinition($a_table, $a_column);
739 $this->
handleError($def,
"modifyTableColumn(".$a_table.
")");
741 if (is_file(
"./Services/Database/classes/class.ilDBAnalyzer.php"))
743 include_once(
"./Services/Database/classes/class.ilDBAnalyzer.php");
747 include_once(
"../Services/Database/classes/class.ilDBAnalyzer.php");
750 $best_alt = $analyzer->getBestDefinitionAlternative($def);
751 $def = $def[$best_alt];
752 unset($def[
"nativetype"]);
753 unset($def[
"mdb2type"]);
756 $type = ($a_attributes[
"type"] !=
"")
757 ? $a_attributes[
"type"]
759 foreach ($def as $k => $v)
761 if ($k !=
"type" && !in_array($k, $this->allowed_attributes[$type]))
767 foreach ($a_attributes as $k => $v)
769 $check_array[$k] = $v;
773 $this->
raisePearError(
"ilDB Error: modifyTableColumn(".$a_table.
", ".$a_column.
")<br />".
778 if ($this->getDbType() ==
"oracle")
780 if ($def[
"notnull"] ==
true && ($a_attributes[
"notnull"] ==
true 781 || !isset($a_attributes[
"notnull"])))
783 unset($def[
"notnull"]);
784 unset($a_attributes[
"notnull"]);
786 if ($def[
"notnull"] ==
false && ($a_attributes[
"notnull"] ==
false 787 || !isset($a_attributes[
"notnull"])))
789 unset($def[
"notnull"]);
790 unset($a_attributes[
"notnull"]);
793 foreach ($a_attributes as $a => $v)
798 $a_attributes[
"definition"] = $def;
802 $a_column => $a_attributes
806 $r = $manager->alterTable($a_table, $changes,
false);
808 return $this->
handleError(
$r,
"modifyTableColumn(".$a_table.
")");
824 $this->
raisePearError(
"ilDB Error: renameTableColumn(".$a_table.
",".$a_column.
",".$a_new_column.
")<br />".
828 $manager = $this->db->loadModule(
'Manager');
829 $reverse = $this->db->loadModule(
'Reverse');
830 $def = $reverse->getTableFieldDefinition($a_table, $a_column);
832 $this->
handleError($def,
"renameTableColumn(".$a_table.
",".$a_column.
",".$a_new_column.
")");
834 if (is_file(
"./Services/Database/classes/class.ilDBAnalyzer.php"))
836 include_once(
"./Services/Database/classes/class.ilDBAnalyzer.php");
840 include_once(
"../Services/Database/classes/class.ilDBAnalyzer.php");
844 $best_alt = $analyzer->getBestDefinitionAlternative($def);
845 $def = $def[$best_alt];
846 unset($def[
"nativetype"]);
847 unset($def[
"mdb2type"]);
849 $f[
"definition"] = $def;
850 $f[
"name"] = $a_new_column;
858 $r = $manager->alterTable($a_table, $changes,
false);
860 return $this->
handleError(
$r,
"renameTableColumn(".$a_table.
",".$a_column.
",".$a_new_column.
")");
874 $this->
raisePearError(
"ilDB Error: renameTable(".$a_name.
",".$a_new_name.
")<br />".
878 $manager = $this->db->loadModule(
'Manager');
879 $r = $manager->alterTable($a_name, array(
"name" => $a_new_name),
false);
881 $query =
"UPDATE abstraction_progress ".
882 "SET table_name = ".$this->db->quote($a_new_name,
'text').
" ".
883 "WHERE table_name = ".$this->db->quote($a_name,
'text');
886 return $this->
handleError(
$r,
"renameTable(".$a_name.
",".$a_new_name.
")");
898 $manager = $this->db->loadModule(
'Manager');
901 foreach ($a_fields as $f)
903 $fields[$f] = array();
905 $definition = array (
909 $r = $manager->createConstraint($a_table,
912 return $this->
handleError(
$r,
"addPrimaryKey(".$a_table.
")");
931 $manager = $this->db->loadModule(
'Manager');
933 $r = $manager->dropConstraint($a_table,
936 return $this->
handleError(
$r,
"dropPrimaryKey(".$a_table.
")");
946 function addIndex($a_table, $a_fields, $a_name =
"in", $a_fulltext =
false)
948 $manager = $this->db->loadModule(
'Manager');
953 $this->
raisePearError(
"ilDB Error: addIndex(".$a_table.
",".$a_name.
")<br />".
958 foreach ($a_fields as $f)
960 $fields[$f] = array();
962 $definition = array (
968 $r = $manager->createIndex($a_table, $this->
constraintName($a_table, $a_name), $definition);
1005 $manager = $this->db->loadModule(
'Manager');
1006 $reverse = $this->db->loadModule(
'Reverse');
1009 foreach($manager->listTableIndexes($a_table) as $idx_name)
1011 $def = $reverse->getTableIndexDefinition($a_table,$idx_name);
1012 $idx_fields = array_keys((array) $def[
'fields']);
1014 if($idx_fields === $a_fields)
1031 $manager = $this->db->loadModule(
'Manager');
1032 $reverse = $this->db->loadModule(
'Reverse');
1035 foreach($manager->listTableIndexes($a_table) as $idx_name)
1037 $def = $reverse->getTableIndexDefinition($a_table,$idx_name);
1038 $idx_fields = array_keys((array) $def[
'fields']);
1040 if($idx_fields === $a_fields)
1042 return $this->
dropIndex($a_table, $idx_name);
1059 $manager = $this->db->loadModule(
'Manager');
1063 $r = $manager->dropIndex($a_table, $this->
constraintName($a_table, $a_name));
1067 $this->dropFulltextIndex($a_table, $a_name);
1082 $manager = $this->db->loadModule(
'Manager');
1087 $this->
raisePearError(
"ilDB Error: addUniqueConstraint(".$a_table.
",".$a_name.
")<br />".
1092 foreach ($a_fields as $f)
1094 $fields[$f] = array();
1096 $definition = array (
1101 $r = $manager->createConstraint($a_table, $this->
constraintName($a_table, $a_name), $definition);
1103 return $this->
handleError(
$r,
"addUniqueConstraint(".$a_table.
")");
1115 $manager = $this->db->loadModule(
'Manager');
1117 $r = $manager->dropConstraint(
1121 return $this->
handleError(
$r,
"dropUniqueConstraint(".$a_table.
")");
1132 if (is_file(
"./Services/Database/classes/class.ilDBAnalyzer.php"))
1134 include_once(
"./Services/Database/classes/class.ilDBAnalyzer.php");
1138 include_once(
"../Services/Database/classes/class.ilDBAnalyzer.php");
1141 $cons = $analyzer->getConstraintsInformation($a_table);
1142 foreach ($cons as $c)
1144 if ($c[
"type"] ==
"unique" && count($a_fields) == count($c[
"fields"]))
1147 foreach ($a_fields as $f)
1149 if (!isset($c[
"fields"][$f]))
1168 $manager = $this->db->loadModule(
'Manager');
1170 $r = $manager->createSequence($a_table_name, $a_start);
1172 return $this->
handleError(
$r,
"createSequence(".$a_table_name.
")");
1181 $manager = $this->db->loadModule(
'Manager');
1183 $r = $manager->dropSequence($a_table_name);
1185 return $this->
handleError(
$r,
"dropSequence(".$a_table_name.
")");
1195 if (!preg_match (
"/^[a-z]+[_a-z0-9]*$/", $a_name))
1197 $this->error_str =
"Table name must only contain _a-z0-9 and must start with a-z.";
1203 $this->error_str =
"Invalid table name '".$a_name.
"' (Reserved Word).";
1207 if (strtolower(
substr($a_name, 0, 4)) ==
"sys_")
1209 $this->error_str =
"Invalid table name '".$a_name.
"'. Name must not start with 'sys_'.";
1213 if (strlen($a_name) > 22)
1215 $this->error_str =
"Invalid table name '".$a_name.
"'. Maximum table identifer length is 22 bytes.";
1229 foreach ($a_cols as $col => $def)
1266 if (!in_array($a_def[
"type"], array(
"text",
"integer",
"float",
"date",
"time",
"timestamp",
"clob",
"blob")))
1268 switch ($a_def[
"type"])
1271 $this->error_str =
"Invalid column type '".$a_def[
"type"].
"'. Use integer(1) instead.";
1275 $this->error_str =
"Invalid column type '".$a_def[
"type"].
"'. Use float or integer instead.";
1279 $this->error_str =
"Invalid column type '".$a_def[
"type"].
"'. Allowed types are: ".
1280 "text, integer, float, date, time, timestamp, clob and blob.";
1287 foreach ($a_def as $k => $v)
1289 if ($k !=
"type" && !in_array($k, $allowed_attributes[$a_def[
"type"]]))
1291 $this->error_str =
"Attribute '".$k.
"' is not allowed for column type '".$a_def[
"type"].
"'.";
1297 switch ($a_def[
"type"])
1300 if ($a_def[
"length"] < 1 || $a_def[
"length"] > 4000)
1302 if (!$a_modify_mode || isset($a_def[
"length"]))
1304 $this->error_str =
"Invalid length '".$a_def[
"length"].
"' for type text.".
1305 " Length must be >=1 and <= 4000.";
1312 if (!in_array($a_def[
"length"], array(1, 2, 3, 4, 8)))
1314 if (!$a_modify_mode || isset($a_def[
"length"]))
1316 $this->error_str =
"Invalid length '".$a_def[
"length"].
"' for type integer.".
1317 " Length must be 1, 2, 3, 4 or 8 (bytes).";
1321 if ($a_def[
"unsigned"])
1323 $this->error_str =
"Unsigned attribut must not be true for type integer.";
1339 if (!preg_match (
"/^[a-z]+[_a-z0-9]*$/", $a_name))
1341 $this->error_str =
"Invalid column name '".$a_name.
"'. Column name must only contain _a-z0-9 and must start with a-z.";
1347 $this->error_str =
"Invalid column name '".$a_name.
"' (Reserved Word).";
1351 if (strtolower(
substr($a_name, 0, 4)) ==
"sys_")
1353 $this->error_str =
"Invalid column name '".$a_name.
"'. Name must not start with 'sys_'.";
1357 if (strlen($a_name) > 30)
1359 $this->error_str =
"Invalid column name '".$a_name.
"'. Maximum column identifer length is 30 bytes.";
1373 if (!preg_match (
"/^[a-z]+[_a-z0-9]*$/", $a_name))
1375 $this->error_str =
"Invalid column name '".$a_name.
"'. Column name must only contain _a-z0-9 and must start with a-z.";
1381 $this->error_str =
"Invalid column name '".$a_name.
"' (Reserved Word).";
1385 if (strlen($a_name) > 3)
1387 $this->error_str =
"Invalid index name '".$a_name.
"'. Maximum index identifer length is 3 bytes.";
1406 return $a_constraint;
1415 include_once(
"./Services/Database/classes/class.ilDBMySQL.php");
1417 if (in_array(strtoupper($a_word), $mysql_reserved_words))
1421 include_once(
"./Services/Database/classes/class.ilDBOracle.php");
1423 if (in_array(strtoupper($a_word), $oracle_reserved_words))
1427 include_once(
"./Services/Database/classes/class.ilDBPostgreSQL.php");
1429 if (in_array(strtoupper($a_word), $postgres_reserved_words))
1450 function query($sql, $a_handle_error =
true)
1454 if (is_object($ilBench))
1456 $ilBench->startDbBench($sql);
1458 $r = $this->db->query($sql);
1459 if (is_object($ilBench))
1461 $ilBench->stopDbBench();
1464 if ($a_handle_error)
1479 function queryF($a_query, $a_types, $a_values)
1481 if (!is_array($a_types) || !is_array($a_values) ||
1482 count($a_types) != count($a_values))
1484 $this->
raisePearError(
"ilDB::queryF: Types and values must be arrays of same size. ($a_query)");
1486 $quoted_values = array();
1487 foreach($a_types as $k =>
$t)
1489 $quoted_values[] = $this->
quote($a_values[$k],
$t);
1491 $query = vsprintf($a_query, $quoted_values);
1505 if (!is_array($a_types) || !is_array($a_values) ||
1506 count($a_types) != count($a_values))
1508 $this->
raisePearError(
"ilDB::manipulateF: types and values must be arrays of same size. ($a_query)");
1510 $quoted_values = array();
1511 foreach($a_types as $k =>
$t)
1513 $quoted_values[] = $this->
quote($a_values[$k],
$t);
1515 $query = vsprintf($a_query, $quoted_values);
1525 $pos1 = strpos(strtolower($sql),
"from ");
1529 $tablef =
substr($sql, $pos1+5);
1530 $pos2 = strpos(strtolower($tablef),
" ");
1533 $table =
substr($tablef, 0, $pos2);
1540 if (trim($table) !=
"")
1542 if (!is_array($this->ttt) || !in_array($table, $this->ttt))
1545 $this->ttt[] = $table;
1550 echo
"<br><b>".$sql.
"</b>";
1559 $this->db->setLimit($a_limit, $a_offset);
1570 $r = $this->db->nextId($a_table_name,
false);
1589 if (is_object($ilBench))
1591 $ilBench->startDbBench($sql);
1593 $r = $this->db->exec($sql);
1594 if (is_object($ilBench))
1596 $ilBench->stopDbBench();
1610 function prepare($a_query, $a_types = null, $a_result_types = null)
1612 $res = $this->db->prepare($a_query, $a_types, $a_result_types);
1642 $res = $a_stmt->execute($a_data);
1658 $res = $this->db->extended->executeMultiple($a_stmt,$a_data);
1660 return $this->
handleError(
$res,
"executeMultiple(".$a_stmt->query.
")");
1672 $field_values = array();
1673 $placeholders = array();
1678 foreach ($a_columns as $k => $col)
1681 $placeholders[] =
"%s";
1682 $placeholders2[] =
":$k";
1686 if ($col[0] ==
'integer' && !is_null($col[1]))
1688 $col[1] = (int) $col[1];
1691 $values[] = $col[1];
1692 $field_values[$k] = $col[1];
1693 if ($col[0] ==
"blob" || $col[0] ==
"clob")
1701 $st = $this->db->prepare(
"INSERT INTO ".$a_table.
" (".implode($fields,
",").
") VALUES (".
1704 $this->
handleError($st,
"insert / prepare/execute(".$a_table.
")");
1706 $r = $st->execute($field_values);
1710 $this->
handleError(
$r,
"insert / prepare/execute(".$a_table.
")");
1715 $q =
"INSERT INTO ".$a_table.
" (".implode($fields,
",").
") VALUES (".
1716 implode($placeholders,
",").
")";
1730 function update($a_table, $a_columns, $a_where)
1733 $field_values = array();
1734 $placeholders = array();
1739 foreach ($a_columns as $k => $col)
1742 $placeholders[] =
"%s";
1743 $placeholders2[] =
":$k";
1747 if ($col[0] ==
'integer' && !is_null($col[1]))
1749 $col[1] = (int) $col[1];
1752 $values[] = $col[1];
1753 $field_values[$k] = $col[1];
1754 if ($col[0] ==
"blob" || $col[0] ==
"clob")
1763 $q =
"UPDATE ".$a_table.
" SET ";
1765 foreach ($fields as $k => $field)
1767 $q.= $lim.$field.
" = ".$placeholders2[$k];
1772 foreach ($a_where as $k => $col)
1774 $q.= $lim.$k.
" = ".$this->
quote($col[1], $col[0]);
1778 $r = $st->execute($field_values);
1781 $this->
handleError(
$r,
"update / prepare/execute(".$a_table.
")");
1786 foreach ($a_where as $k => $col)
1789 $values[] = $col[1];
1790 $field_values[$k] = $col;
1792 $q =
"UPDATE ".$a_table.
" SET ";
1794 foreach ($fields as $k => $field)
1796 $q.= $lim.$field.
" = ".$placeholders[$k];
1801 foreach ($a_where as $k => $col)
1803 $q.= $lim.$k.
" = %s";
1819 function replace($a_table, $a_pk_columns, $a_other_columns)
1822 $a_columns = array_merge($a_pk_columns, $a_other_columns);
1824 $field_values = array();
1825 $placeholders = array();
1830 foreach ($a_columns as $k => $col)
1833 $placeholders[] =
"%s";
1834 $placeholders2[] =
":$k";
1838 if ($col[0] ==
'integer' && !is_null($col[1]))
1840 $col[1] = (int) $col[1];
1843 $values[] = $col[1];
1844 $field_values[$k] = $col[1];
1845 if ($col[0] ==
"blob" || $col[0] ==
"clob")
1853 $st = $this->db->prepare(
"REPLACE INTO ".$a_table.
" (".implode($fields,
",").
") VALUES (".
1855 $this->
handleError($st,
"insert / prepare/execute(".$a_table.
")");
1856 $r = $st->execute($field_values);
1858 $this->
handleError(
$r,
"insert / prepare/execute(".$a_table.
")");
1863 $q =
"REPLACE INTO ".$a_table.
" (".implode($fields,
",").
") VALUES (".
1864 implode($placeholders,
",").
")";
1885 return $a_st->free();
1905 return $a_set->numRows();
1923 function in($a_field, $a_values, $negate =
false, $a_type =
"")
1925 if (count($a_values) == 0)
1929 return $negate ?
' 1=1 ' :
' 1=2 ';
1934 $str = $a_field.(($negate) ?
" NOT" :
"").
" IN (?".str_repeat(
",?", count($a_values) - 1).
")";
1938 $str = $a_field.(($negate) ?
" NOT" :
"").
" IN (";
1940 foreach ($a_values as $v)
1942 $str.= $sep.$this->quote($v, $a_type);
1956 if (!is_array($a_arr))
1962 $type_arr = array_fill(0, $a_cnt, $a_type);
1966 $type_arr = array();
1968 return array_merge($a_arr, $type_arr);
1990 public function concat($a_values,$a_allow_null =
true)
1992 if(!count($a_values))
1997 $concat =
' CONCAT(';
1999 foreach($a_values as $field_info)
2001 $val = $field_info[0];
2010 $concat .=
'COALESCE(';
2032 function substr($a_exp, $a_pos = 1, $a_len = -1)
2037 $lenstr =
", ".$a_len;
2039 return " SUBSTR(".$a_exp.
", ".$a_pos.$lenstr.
") ";
2050 return " UPPER(".$a_exp.
") ";
2061 return " LOWER(".$a_exp.
") ";
2071 public function locate($a_needle,$a_string,$a_start_pos = 1)
2073 $locate =
' LOCATE( ';
2074 $locate .= $a_needle;
2076 $locate .= $a_string;
2078 $locate .= $a_start_pos;
2089 function like($a_col, $a_type, $a_value =
"?", $case_insensitive =
true)
2091 if (!in_array($a_type, array(
"text",
"clob",
"blob")))
2093 $this->
raisePearError(
"Like: Invalid column type '".$a_type.
"'.", $this->error_class->FATAL);
2095 if ($a_value ==
"?")
2097 if ($case_insensitive)
2099 return "UPPER(".$a_col.
") LIKE(UPPER(?))";
2103 return $a_col .
" LIKE(?)";
2108 if ($case_insensitive)
2111 return " UPPER(".$a_col.
") LIKE(UPPER(".$this->
quote($a_value,
'text').
"))";
2116 return " ".$a_col.
" LIKE(".$this->
quote($a_value,
'text').
")";
2125 function equals($a_col, $a_value, $a_type, $a_empty_or_null =
false)
2127 if (!$a_empty_or_null || $a_value !=
"")
2129 return $a_col.
" = ".$this->
quote($a_value, $a_type);
2133 return "(".$a_col.
" = '' OR $a_col IS NULL)";
2140 function equalsNot($a_col, $a_value, $a_type, $a_empty_or_null =
false)
2142 if (!$a_empty_or_null)
2144 return $a_col.
" <> ".$this->
quote($a_value, $a_type);
2148 return "(".$a_col.
" <> ".$this->
quote($a_value, $a_type).
" OR ".
2153 return "(".$a_col.
" <> '' AND $a_col IS NOT NULL)";
2165 return "FROM_UNIXTIME(".$a_expr.
")";
2173 return "UNIX_TIMESTAMP()";
2199 if (is_array($tables))
2201 if (in_array($a_table, $tables))
2219 $column_visibility =
false;
2220 $manager = $this->db->loadModule(
'Manager');
2221 $r = $manager->listTableFields($a_table);
2225 foreach(
$r as $field)
2227 if ($field == $a_column_name)
2229 $column_visibility =
true;
2234 return $column_visibility;
2246 if (is_file(
"./Services/Database/classes/class.ilDBAnalyzer.php"))
2248 include_once(
"./Services/Database/classes/class.ilDBAnalyzer.php");
2252 include_once(
"../Services/Database/classes/class.ilDBAnalyzer.php");
2255 $cons = $analyzer->getConstraintsInformation($a_table);
2256 foreach ($cons as $c)
2258 if ($c[
"type"] ==
"unique" && count($a_fields) == count($c[
"fields"]))
2261 foreach ($a_fields as $f)
2263 if (!isset($c[
"fields"][$f]))
2285 $manager = $this->db->loadModule(
'Manager');
2286 $r = $manager->listTables();
2306 if (is_array($sequences))
2308 if (in_array($a_sequence, $sequences))
2323 $manager = $this->db->loadModule(
'Manager');
2324 $r = $manager->listSequences();
2342 function quote($a_query, $a_type = null)
2344 if ($a_query ==
"" && is_null($a_type))
2350 if($a_type ==
'integer' && !is_null($a_query))
2352 return (
int) $a_query;
2355 if ($a_type ==
"blob" || $a_type ==
"clob")
2357 $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);
2360 return $this->db->quote($a_query, $a_type);
2372 return $this->db->quoteIdentifier($a_identifier);
2387 if (!$this->db->supports(
'transactions'))
2389 $this->
raisePearError(
"ilDB::beginTransaction: Transactions are not supported.", $this->error_class->FATAL);
2391 $res = $this->db->beginTransaction();
2401 $res = $this->db->commit();
2411 $res = $this->db->rollback();
2421 abstract public function lockTables($a_tables);
2446 $res = $this->db->autoExecute($a_tablename,$a_fields,$a_mode,$a_where);
2462 $res = $this->db->lastInsertId();
2482 $set = $this->db->query($sql);
2505 $set = $this->
query($sql);
2506 $r = $set->fetchRow($mode);
2521 $this->sub_type = (string)$a_value;
static getReservedWords()
Get reserved words.
const MDB2_FETCHMODE_ASSOC
Column data indexed by column names.
raisePearError($a_message, $a_level="")
Raise an error.
prepare($a_query, $a_types=null, $a_result_types=null)
Prepare a query (SELECT) statement to be used with execute.
checkTableColumns($a_cols)
Check table columns definition.
getRow($sql, $mode=DB_FETCHMODE_OBJECT)
getRow.
numRows($a_set)
Fetch row as associative array from result set.
setErrorHandling($mode=null, $options=null)
Sets how errors generated by this object should be handled.
rollback()
Rollback a transaction.
createDatabase($a_name, $a_charset="utf8", $a_collation="")
Create database.
const PEAR_ERROR_CALLBACK
isFulltextIndex($a_table, $a_name)
Is index a fulltext index?
isError($data, $code=null)
Tell whether a value is a MDB2 error.
setDBUser($a_user)
Set database user.
sequenceExists($a_sequence)
Check, whether a given sequence exists.
checkColumnDefinition($a_def, $a_modify_mode=false)
Check whether a column definition is valid.
renameTableColumn($a_table, $a_column, $a_new_column)
Rename a table column Use this only on aleady "abstracted" tables.
tableExists($a_table)
Check, whether a given table exists.
query($sql, $a_handle_error=true)
Query.
nextId($a_table_name)
Get next ID for an index.
const MDB2_PORTABILITY_EMPTY_TO_NULL
Portability: convert empty values to null strings in data output by query*() and fetch*().
static getReservedWords()
Get reserved words.
locate($a_needle, $a_string, $a_start_pos=1)
Create locate string.
replace($a_table, $a_pk_columns, $a_other_columns)
Replace into method.
beginTransaction()
Begin Transaction.
createTable($a_name, $a_definition_array, $a_drop_table=false, $a_ignore_erros=false)
Create a new table in the database.
checkIndexName($a_name)
Check whether an index name is valid.
like($a_col, $a_type, $a_value="?", $case_insensitive=true)
Like.
fetchObject($a_set)
Fetch row as object from result set.
getSubType()
Get sub type.
dropIndex($a_table, $a_name="in")
Drop an index from a table.
modifyTableColumn($a_table, $a_column, $a_attributes)
Modify a table column Use this only on aleady "abstracted" tables.
uniqueConstraintExists($a_table, $a_fields)
Checks if a unique constraint exists based on the fields of the unique constraint (not the name) ...
dropIndexByFields($a_table, $a_fields)
Drop index by field(s)
addFulltextIndex($a_table, $a_fields, $a_name="in")
Add fulltext index.
getDBPort()
Get database port.
addPrimaryKey($a_table, $a_fields)
Add a primary key to a table.
addIndex($a_table, $a_fields, $a_name="in", $a_fulltext=false)
Add an index to a table.
in($a_field, $a_values, $negate=false, $a_type="")
Get abstract in-clause for given array.
const DB_FETCHMODE_OBJECT
addUniqueConstraint($a_table, $a_fields, $a_name="con")
Add a unique constraint to a table.
doConnect()
Standard way to connect to db.
const MDB2_AUTOQUERY_SELECT
getLastInsertId()
Get last insert id.
equals($a_col, $a_value, $a_type, $a_empty_or_null=false)
Use this only on text fields.
setSubType($a_value)
Set sub type.
manipulateF($a_query, $a_types, $a_values)
Formatted manupulate (for DELETE, UPDATE, INSERT).
getDBName()
Get database name.
fetchAssoc($a_set)
Fetch row as associative array from result set.
const MDB2_FETCHMODE_OBJECT
Column data as object properties.
dropTable($a_name, $a_error_if_not_existing=true)
Drop a table.
concat($a_values, $a_allow_null=true)
Abstraction of SQL function CONCAT.
quote($a_query, $a_type=null)
Wrapper for quote method.
executeMultiple($a_stmt, $a_data)
Execute a query statement prepared by either prepare() or prepareManip() with multiple data arrays...
unixTimestamp()
Unix timestamp.
createSequence($a_table_name, $a_start=1)
Create a sequence for a table.
prepareManip($a_query, $a_types=null)
Prepare a data manipulation statement to be used with execute.
const MDB2_AUTOQUERY_DELETE
getDBHost()
Get database host.
initConnection()
Initialize the database connection.
setDBPort($a_port)
Set database port.
useSlave($a_val=true)
Use slave.
const MDB2_PREPARE_MANIP
These are just helper constants to more verbosely express parameters to prepare() ...
substr($a_exp, $a_pos=1, $a_len=-1)
Substring.
checkTableName($a_name)
Check whether a table name is valid.
if(!is_array($argv)) $options
getDBUser()
Get database user.
& connect($dsn, $options=false)
Create a new MDB2 connection object and connect to the specified database.
free($a_st)
Free a statement / result set.
supportsSlave()
Supports slave.
checkColumnName($a_name)
Check whether a column name is valid.
static getReservedWords()
Get reserved words.
fromUnixtime($a_expr, $a_to_text=true)
fromUnixtime (makes timestamp out of unix timestamp)
setDBName($a_name)
Set database name.
autoExecute($a_tablename, $a_fields, $a_mode=MDB2_AUTOQUERY_INSERT, $a_where=false)
Wrapper for Pear autoExecute.
setLimit($a_limit, $a_offset=0)
Set limit and offset for a query.
const MDB2_PORTABILITY_ALL
Portability: turn on all portability features.
enableResultBuffering($a_status)
En/disable result buffering.
unlockTables()
Unlock tables locked by previous lock table calls.
execute($a_stmt, $a_data=null)
Execute a query statement prepared by either prepare() or prepareManip()
const MDB2_AUTOQUERY_UPDATE
static isReservedWord($a_word)
Checks whether a word is a reserved word in one of the supported databases.
listTables()
Get all tables.
initHostConnection()
Initialize the host connection (no specific database)
handleError($a_res, $a_info="", $a_level="")
Handle MDB2 Errors.
loadMDB2Extensions()
load additional mdb2 extensions and set their constants
insert($a_table, $a_columns)
Convenient method for standard insert statements, example field array:
connectHost()
Sets up a host connection only (no specific database used).
connect($a_return_false_for_error=false)
Open the connection.
queryF($a_query, $a_types, $a_values)
Formatted query (for SELECTS).
logStatement($sql)
Helper function, should usually not be called.
initFromIniFile($tmpClientIniFile=null)
Init db parameters from ini file.
manipulate($sql)
Data manipulation.
constraintName($a_table, $a_constraint)
Determine contraint name by table name and constraint name.
optimizeTable($a_table)
Optimize Table.
setDBPassword($a_password)
Set database password.
commit()
Commit a transaction.
dropPrimaryKey($a_table)
Drop a primary key from a table.
update($a_table, $a_columns, $a_where)
Convenient method for standard update statements, example field array:
& raiseError($message=null, $code=null, $mode=null, $options=null, $userinfo=null, $error_class=null, $skipmsg=false)
This method is a wrapper that returns an instance of the configured error class with this object's de...
getCreateTableOptions()
Get options for the create table statement.
listSequences()
Get all sequences.
dropSequence($a_table_name)
Drop a sequence for a table.
getHostDSN()
Should return a valid value, if host connections are possible (connectHost) to create a new database ...
getDBPassword()
Get database password.
This class gives all kind of DB information using the MDB2 manager and reverse module.
equalsNot($a_col, $a_value, $a_type, $a_empty_or_null=false)
Use this only on text fields.
addTableColumn($a_table, $a_column, $a_attributes)
Add table column Use this only on aleady "abstracted" tables.
static getReservedWords()
Get reserved words.
dropUniqueConstraint($a_table, $a_name="con")
Drop a constraint from a table.
dropTableColumn($a_table, $a_column)
Drop table column Use this only on aleady "abstracted" tables.
getPrimaryKeyIdentifier()
Primary key identifier.
quoteIdentifier($a_identifier)
Quote table and field names.
addTypesToArray($a_arr, $a_type, $a_cnt)
Adds a type x times to an array.
dropUniqueConstraintByFields($a_table, $a_fields)
Drop constraint by field(s)
checkColumn($a_col, $a_def)
Check column definition.
alterTable($a_name, $a_changes)
Alter a table in the database This method is DEPRECATED, see http://www.ilias.de/docu/goto.php?target=pg_25354_42&client_id=docu PLEASE USE THE SPECIALIZED METHODS OF THIS CLASS TO CHANGE THE DB SCHEMA.
setDBHost($a_host)
Set database host.
tableColumnExists($a_table, $a_column_name)
Checks for the existence of a table column.
renameTable($a_name, $a_new_name)
Rename a table.
const MDB2_PORTABILITY_FIX_CASE
Portability: convert names of tables and fields to case defined in the "field_case" option when using...
const MDB2_AUTOQUERY_INSERT
Used by autoPrepare()
static isDbError($a_res)
Check error.
lockTables($a_tables)
Abstraction of lock table.
getDBVersion()
Get DB version.
indexExistsByFields($a_table, $a_fields)
Check if index exists.