ILIAS  release_5-1 Revision 5.0.0-5477-g43f3e3fab5f
class.ilDBMySQL.php
Go to the documentation of this file.
1<?php
2
3/* Copyright (c) 1998-2012 ILIAS open source, Extended GPL, see docs/LICENSE */
4
5include_once ("./Services/Database/classes/class.ilDB.php");
6
18class ilDBMySQL extends ilDB
19{
25 protected $slave_active = false;
26 protected $use_slave = false;
27
34 function supportsSlave()
35 {
36 return true;
37 }
38
44 function setDBSlaveActive($a_val)
45 {
46 $this->slave_active = $a_val;
47 }
48
55 {
57 }
58
64 function setDBSlaveUser($a_user)
65 {
66 $this->slave_user = $a_user;
67 }
68
74 function getDBSlaveUser()
75 {
76 return $this->slave_user;
77 }
78
84 function setDBSlavePort($a_port)
85 {
86 $this->slave_port = $a_port;
87 }
88
94 function getDBSlavePort()
95 {
96 return $this->slave_port;
97 }
98
104 function setDBSlaveHost($a_host)
105 {
106 $this->slave_host = $a_host;
107 }
108
114 function getDBSlaveHost()
115 {
116 return $this->slave_host;
117 }
118
124 function setDBSlavePassword($a_password)
125 {
126 $this->slave_password = $a_password;
127 }
128
135 {
136 return $this->slave_password;
137 }
138
144 function setDBSlaveName($a_name)
145 {
146 $this->slave_name = $a_name;
147 }
148
154 function getDBSlaveName()
155 {
156 return $this->slave_name;
157 }
158
162 function getDSN()
163 {
164 return $this->__buildDSN($this->getDBHost(), $this->getDBName(),
165 $this->getDBUser(), $this->getDBPassword(), $this->getDBPort());
166 }
167
171 function getSlaveDSN()
172 {
173 return $this->__buildDSN($this->getDBSlaveHost(), $this->getDBSlaveName(),
174 $this->getDBSlaveUser(), $this->getDBSlavePassword(), $this->getDBSlavePort());
175 }
176
183 protected function __buildDSN($a_host, $a_name, $a_user, $a_pass, $a_port = "")
184 {
185 $db_port_str = "";
186 if (trim($a_port) != "")
187 {
188 $db_port_str = ":".$a_port;
189 }
190
191 $driver = $this->isMySQLi() ? "mysqli" : "mysql";
192
193 return $driver."://".$a_user.":".$a_pass.
194 "@".$a_host.$db_port_str."/".$a_name;
195 }
196
197 protected function isMySQLi()
198 {
199 return ($this->getSubType() == "mysqli");
200 }
201
205 function getHostDSN()
206 {
207 $driver = $this->isMySQLi() ? "mysqli" : "mysql";
208
209 return $driver."://".$this->getDBUser().":".$this->getDBPassword().
210 "@".$this->getdbHost();
211 }
212
216 function getDBType()
217 {
218 return "mysql";
219 }
220
224 function setStorageEngine($a_storage_engine) {
225 $storage_engine_var = ($this->isMysql5_6OrHigher()) ? "DEFAULT_STORAGE_ENGINE" : "STORAGE_ENGINE";
226 $this->query("SET SESSION " . $storage_engine_var . " = '" . $a_storage_engine . "'");
227 }
228
232 static function getReservedWords()
233 {
234 // version: 5.1
235 // url: http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html
236 return array(
237 "ACCESSIBLE", "ADD", "ALL", "ALTER", "ANALYZE", "AND",
238 "AS", "ASC", "ASENSITIVE", "BEFORE", "BETWEEN", "BIGINT",
239 "BINARY", "BLOB", "BOTH", "BY", "CALL", "CASCADE",
240 "CASE", "CHANGE", "CHAR", "CHARACTER", "CHECK", "COLLATE",
241 "COLUMN", "CONDITION", "CONSTRAINT", "CONTINUE", "CONVERT", "CREATE",
242 "CROSS", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "CURRENT_USER", "CURSOR",
243 "DATABASE", "DATABASES", "DAY_HOUR", "DAY_MICROSECOND", "DAY_MINUTE", "DAY_SECOND",
244 "DEC", "DECIMAL", "DECLARE", "DEFAULT", "DELAYED", "DELETE",
245 "DESC", "DESCRIBE", "DETERMINISTIC", "DISTINCT", "DISTINCTROW", "DIV",
246 "DOUBLE", "DROP", "DUAL", "EACH", "ELSE", "ELSEIF",
247 "ENCLOSED", "ESCAPED", "EXISTS", "EXIT", "EXPLAIN", "FALSE",
248 "FETCH", "FLOAT", "FLOAT4", "FLOAT8", "FOR", "FORCE",
249 "FOREIGN", "FROM", "FULLTEXT", "GRANT", "GROUP", "HAVING",
250 "HIGH_PRIORITY", "HOUR_MICROSECOND", "HOUR_MINUTE", "HOUR_SECOND", "IF", "IGNORE",
251 "IN", "INDEX", "INFILE", "INNER", "INOUT", "INSENSITIVE",
252 "INSERT", "INT", "INT1", "INT2", "INT3", "INT4",
253 "INT8", "INTEGER", "INTERVAL", "INTO", "IS", "ITERATE",
254 "JOIN", "KEY", "KEYS", "KILL", "LEADING", "LEAVE",
255 "LEFT", "LIKE", "LIMIT", "LINEAR", "LINES", "LOAD",
256 "LOCALTIME", "LOCALTIMESTAMP", "LOCK", "LONG", "LONGBLOB", "LONGTEXT",
257 "LOOP", "LOW_PRIORITY", "MASTER_SSL_VERIFY_SERVER_CERT", "MATCH", "MEDIUMBLOB", "MEDIUMINT",
258 "MEDIUMTEXT", "MIDDLEINT", "MINUTE_MICROSECOND", "MINUTE_SECOND", "MOD", "MODIFIES",
259 "NATURAL", "NOT", "NO_WRITE_TO_BINLOG", "NULL", "NUMERIC", "ON",
260 "OPTIMIZE", "OPTION", "OPTIONALLY", "OR", "ORDER", "OUT",
261 "OUTER", "OUTFILE", "PRECISION", "PRIMARY", "PROCEDURE", "PURGE",
262 "RANGE", "READ", "READS", "READ_WRITE", "REAL", "REFERENCES",
263 "REGEXP", "RELEASE", "RENAME", "REPEAT", "REPLACE", "REQUIRE",
264 "RESTRICT", "RETURN", "REVOKE", "RIGHT", "RLIKE", "SCHEMA",
265 "SCHEMAS", "SECOND_MICROSECOND", "SELECT", "SENSITIVE", "SEPARATOR", "SET",
266 "SHOW", "SMALLINT", "SPATIAL", "SPECIFIC", "SQL", "SQLEXCEPTION",
267 "SQLSTATE", "SQLWARNING", "SQL_BIG_RESULT", "SQL_CALC_FOUND_ROWS", "SQL_SMALL_RESULT", "SSL",
268 "STARTING", "STRAIGHT_JOIN", "TABLE", "TERMINATED", "THEN", "TINYBLOB",
269 "TINYINT", "TINYTEXT", "TO", "TRAILING", "TRIGGER", "TRUE",
270 "UNDO", "UNION", "UNIQUE", "UNLOCK", "UNSIGNED", "UPDATE",
271 "USAGE", "USE", "USING", "UTC_DATE", "UTC_TIME", "UTC_TIMESTAMP",
272 "VALUES", "VARBINARY", "VARCHAR", "VARCHARACTER", "VARYING", "WHEN",
273 "WHERE", "WHILE", "WITH", "WRITE", "XOR", "YEAR_MONTH",
274 "ZEROFILL"
275 );
276 }
277
282 function initFromIniFile($tmpClientIniFile = null)
283 {
284 global $ilClientIniFile;
285
286 parent::initFromIniFile($tmpClientIniFile);
287
288 //overwrite global client ini file if local parameter is set
289 if (is_object($tmpClientIniFile))
290 $clientIniFile = $tmpClientIniFile;
291 else
292 $clientIniFile = $ilClientIniFile;
293
294 if (is_object($clientIniFile ))
295 {
296 if ($clientIniFile->readVariable("db", "slave_active") == 1)
297 {
298 $this->setDBSlaveActive(true);
299 $this->setDBSlaveUser($clientIniFile->readVariable("db", "slave_user"));
300 $this->setDBSlaveHost($clientIniFile->readVariable("db", "slave_host"));
301 $this->setDBSlavePort($clientIniFile->readVariable("db", "slave_port"));
302 $this->setDBSlavePassword($clientIniFile->readVariable("db", "slave_pass"));
303 $this->setDBSlaveName($clientIniFile->readVariable("db", "slave_name"));
304 }
305 }
306 }
307
311 function doConnect()
312 {
313 parent::doConnect();
314 if ($this->getDBSlaveActive())
315 {
316 $this->slave = MDB2::connect($this->getSlaveDSN(),
317 array("use_transactions" => false));
318 }
319 }
320
321
325 function initConnection()
326 {
327 // SET 'max_allowed_packet' (only possible for mysql version 4)
328 $this->setMaxAllowedPacket();
329
330 // NOTE: Two sourcecodes use this or a similar handling:
331 // - classes/class.ilDB.php
332 // - setup/classes/class.ilClient.php
333
334 $this->query("SET NAMES utf8");
335 if (DEVMODE == 1)
336 {
337 $this->query("SET SESSION SQL_MODE = 'ONLY_FULL_GROUP_BY'");
338 }
339 $this->setStorageEngine('MYISAM');
340 }
341
346 function now()
347 {
348 return "now()";
349 }
350
354 function optimizeTable($a_table)
355 {
356 $this->query("OPTIMIZE TABLE ".$a_table);
357 }
358
362 function getDBVersion()
363 {
364 if(!$this->isMySQLi())
365 {
366 $vers = @mysql_get_server_info();
367 }
368 else
369 {
370 $vers = @mysqli_get_server_info($this->db->connection);
371 }
372 if (trim($vers) == "")
373 {
374 $vers = "Unknown";
375 }
376 return $vers;
377 }
378
379
384 {
385 $version = explode(".", $this->getDBVersion());
386 if((int) $version[0] < 4)
387 {
388 return false;
389 }
390 return true;
391 }
392
396 function isMysql4_1()
397 {
398 $version = explode(".", $this->getDBVersion());
399 if ($version[0] == "4" && $version[1] == "1")
400 {
401 return true;
402 }
403
404 return false;
405 }
406
415 {
416 $version = explode(".", $this->getDBVersion());
417 if ((int)$version[0] >= 5 ||
418 ((int)$version[0] == 4 && (int)$version[1] >= 1))
419 {
420 return true;
421 }
422
423 return false;
424 }
425
433 public function isMysql5_6OrHigher()
434 {
435 $version = explode(".", $this->getDBVersion());
436 if(
437 (int) $version[0] > 5 ||
438 ((int) $version[0] == 5 && (int) $version[1] >= 6))
439 {
440 return true;
441 }
442 return false;
443 }
444
448 function checkQuerySize($a_query)
449 {
450 global $lang;
451
452 if(strlen($a_query) >= $this->max_allowed_packet_size)
453 {
454 return false;
455 }
456 else
457 {
458 return true;
459 }
460 }
461
467 protected function setMaxAllowedPacket()
468 {
469 $version = $this->getDBVersion();
470
471 // CHANG VALUE IF MYSQL VERSION > 4.0
472 // Switched back to "SET GLOBAL ..."
473 // @see http://bugs.mysql.com/bug.php?id=22891
474 // smeyer 2009 07 30
475 if (substr($version,0,1) == "4")
476 {
477 ini_get("post_max_size");
478 $query = "SET GLOBAL max_allowed_packet = ".(int) ini_get("post_max_size") * 1024 * 1024;
479//echo "-".$query."-";
480 $this->query($query);
481 }
482 // STORE NEW max_size in member variable
483 $query = "SHOW VARIABLES LIKE 'max_allowed_packet'";
484 $res = $this->query($query);
485
486 while($row = $res->fetchRow(DB_FETCHMODE_OBJECT))
487 {
488 $this->max_allowed_packet_size = $row->value;
489 }
490//echo "-".$this->max_allowed_packet_size."-";
491 return true;
492 }
493
498 {
499 return true;
500 }
501
505 function addFulltextIndex($a_table, $a_fields, $a_name = "in")
506 {
507 $i_name = $this->constraintName($a_table, $a_name)."_idx";
508 $f_str = implode($a_fields, ",");
509 $q = "ALTER TABLE $a_table ADD FULLTEXT $i_name ($f_str)";
510 $this->query($q);
511 }
512
516 function dropFulltextIndex($a_table, $a_name)
517 {
518 $i_name = $this->constraintName($a_table, $a_name)."_idx";
519 $this->query("ALTER TABLE $a_table DROP FULLTEXT $i_name");
520 }
521
525 function isFulltextIndex($a_table, $a_name)
526 {
527 $set = $this->query("SHOW INDEX FROM ".$a_table);
528 while ($rec = $this->fetchAssoc($set))
529 {
530 if ($rec["Key_name"] == $a_name && $rec["Index_type"] == "FULLTEXT")
531 {
532 return true;
533 }
534 }
535 }
536
546 public function lockTables($a_tables)
547 {
548 global $ilLog;
549
550 $lock = 'LOCK TABLES ';
551
552 $counter = 0;
553 foreach($a_tables as $table)
554 {
555 if($counter++)
556 {
557 $lock .= ', ';
558 }
559
560 if( isset($table['sequence']) && $table['sequence'] )
561 {
562 $tableName = $this->db->getSequenceName($table['name']);
563 }
564 else
565 {
566 $tableName = $table['name'];
567 }
568
569 $lock .= ($tableName.' ');
570
571 if($table['alias'])
572 {
573 $lock .= ($table['alias'].' ');
574 }
575
576 switch($table['type'])
577 {
578 case ilDB::LOCK_READ:
579 $lock .= ' READ ';
580 break;
581
582 case ilDB::LOCK_WRITE:
583 $lock .= ' WRITE ';
584 break;
585 }
586 }
587 $ilLog->write(__METHOD__.': '.$lock);
588 $this->query($lock);
589 }
590
595 public function unlockTables()
596 {
597 $this->query('UNLOCK TABLES');
598 }
599
600 protected function getCreateTableOptions()
601 {
602 // InnoDB is default engine for MySQL >= 5.5
603 return array('type' => 'MyISAM');
604 }
605
606 public function getErrorNo()
607 {
608 if(!$this->isMySQLi())
609 {
610 return mysql_errno();
611 }
612 else
613 {
614 return mysqli_errno($this->db->connection);
615 }
616 }
617
618 public function getLastError()
619 {
620 if(!$this->isMySQLi())
621 {
622 return mysql_error();
623 }
624 else
625 {
626 return mysqli_error($this->db->connection);
627 }
628 }
629
636 function query($sql, $a_handle_error = true)
637 {
638 if (!$this->use_slave || !$this->getDBSlaveActive())
639 {
640 return parent::query($sql, $a_handle_error);
641 }
642
643 $r = $this->slave->query($sql);
644
645 if ($a_handle_error)
646 {
647 return $this->handleError($r, "query(".$sql.")");
648 }
649
650 return $r;
651
652 }
653
654}
655?>
& connect($dsn, $options=false)
Create a new MDB2 connection object and connect to the specified database.
Definition: MDB2.php:431
const DB_FETCHMODE_OBJECT
Definition: class.ilDB.php:11
MySQL Database Wrapper.
getCreateTableOptions()
Get options for the create table statement.
addFulltextIndex($a_table, $a_fields, $a_name="in")
Add fulltext index.
dropFulltextIndex($a_table, $a_name)
Add fulltext index.
getDBSlavePort()
Get slave database port.
setDBSlaveUser($a_user)
Set slave database user.
isMysql4_0OrHigher()
check wether current MySQL server is version 4.0.x or higher
getDBVersion()
get mysql version
checkQuerySize($a_query)
Check query size.
getSlaveDSN()
Get slave DSN.
__buildDSN($a_host, $a_name, $a_user, $a_pass, $a_port="")
Build DSN string.
initConnection()
Initialize the database connection.
lockTables($a_tables)
Lock table.
initFromIniFile($tmpClientIniFile=null)
Init db parameters from ini file.
getDBSlaveName()
Get slave database name.
getDBSlaveHost()
Get slave database host.
setDBSlaveActive($a_val)
Set slave active.
supportsSlave()
Supports slave.
isFulltextIndex($a_table, $a_name)
Is index a fulltext index?
isMysql4_1OrHigher()
check wether current MySQL server is version 4.1.x or higher
getDSN()
Get DSN.
setDBSlaveName($a_name)
Set slave database name.
getDBSlaveActive()
Get slave active.
static getReservedWords()
Get reserved words.
isMysql4_1()
check wether current MySQL server is version 4.1.x
doConnect()
Standard way to connect to db.
getDBType()
Get DB Type.
setDBSlavePort($a_port)
Set slave database port.
optimizeTable($a_table)
Optimize Table.
query($sql, $a_handle_error=true)
Query.
getHostDSN()
Get Host DSN.
setDBSlavePassword($a_password)
Set slave database password.
unlockTables()
Unlock tables.
setStorageEngine($a_storage_engine)
Set the storage engine.
getDBSlaveUser()
Get slave database user.
setMaxAllowedPacket()
Set maximum allowed packet size.
setDBSlaveHost($a_host)
Set slave database host.
supportsFulltext()
Is fulltext index supported?
isMysql5_6OrHigher()
check wether current MySQL server is version 5.6.x or higher
getDBSlavePassword()
Get slave database password.
Database Wrapper.
Definition: class.ilDB.php:29
const LOCK_WRITE
Definition: class.ilDB.php:30
getDBPort()
Get database port.
Definition: class.ilDB.php:113
handleError($a_res, $a_info="", $a_level="")
Handle MDB2 Errors.
Definition: class.ilDB.php:405
getDBUser()
Get database user.
Definition: class.ilDB.php:93
getDBName()
Get database name.
Definition: class.ilDB.php:173
constraintName($a_table, $a_constraint)
Determine contraint name by table name and constraint name.
const LOCK_READ
Definition: class.ilDB.php:31
getSubType()
Get sub type.
fetchAssoc($a_set)
Fetch row as associative array from result set.
getDBPassword()
Get database password.
Definition: class.ilDB.php:153
substr($a_exp, $a_pos=1, $a_len=-1)
Substring.
getDBHost()
Get database host.
Definition: class.ilDB.php:133
$r
Definition: example_031.php:79