ILIAS  Release_3_10_x_branch Revision 61812
 All Data Structures Namespaces Files Functions Variables Groups Pages
ilDBx Class Reference

Database Wrapper. More...

+ Inheritance diagram for ilDBx:
+ Collaboration diagram for ilDBx:

Public Member Functions

 ilDBx ($dsn)
 constructor
 _ilDBx ()
 destructor
 disconnect ()
 disconnect from database
 createTable ($a_name, $a_definition_array, $a_options="")
 Create a new table in the database.
 query ($sql)
 Simple query.
 manipulate ($sql)
 Simple data manipulatoin.
 prepare ($a_query, $a_types=null, $a_result_types=null)
 Prepare a query (SELECT) statement to be used with execute.
 prepareManip ($a_query, $a_types=null)
 Prepare a data manipulation statement to be used with execute.
 execute ($a_stmt, $a_data=null)
 Execute a query statement prepared by either prepare() or prepareManip()
 executeMultiple ($a_stmt, $a_data)
 Execute a query statement prepared by either prepare() or prepareManip() with multiple data arrays.
 fetchAssoc ($a_set)
 Fetch row as associative array from result set.
 fetchObject ($a_set)
 Fetch row as object from result set.
 getInClause ($a_field, $a_values)
 Get abstract in-clause for given array.
 addTypesToArray ($a_arr, $a_type, $a_cnt)
 Adds a type x times to an array.
 beginTransaction ()
 Begin Transaction.
 commit ()
 Commit a transaction.
 rollback ()
 Rollback a transaction.
 getLastInsertId ()
 get last insert id
 _lockTables ($a_table_params)
 Lock existing table.
 _unlockTables ()
 getMySQLVersion ()
 get mysql version
 isMysql4_0OrHigher ()
 check wether current MySQL server is version 4.0.x or higher
 isMysql4_1 ()
 check wether current MySQL server is version 4.1.x
 isMysql4_1OrHigher ()
 check wether current MySQL server is version 4.1.x or higher
 checkQuerySize ($a_query)
 Check query size.
 autoExecute ($a_tablename, $a_fields, $a_mode=MDB2_AUTOQUERY_INSERT, $a_where=false)
 Wrapper for Pear autoExecute.
 tableColumnExists ($a_table, $a_column_name)
 Checks for the existence of a table column.
 listTables ()
 Checks for the existence of a table column.
 quote ($a_query, $null_as_empty_string=true)
 Wrapper for quote method.
 getOne ($sql)
 getOne.
 getRow ($sql, $mode=DB_FETCHMODE_OBJECT)
 getRow.

Static Public Member Functions

static isDbError ($a_res)
 Check error.

Data Fields

 $error_class
 $db
 $result
 $max_allowed_packet_size

Protected Member Functions

 loadMDB2Extensions ()
 load additional mdb2 extensions and set their constants

Private Member Functions

 setMaxAllowedPacket ()
 Set maximum allowed packet size.

Detailed Description

Database Wrapper.

this class should extend PEAR::DB, add error Management in case of a db-error in any database query the ilDBx-class raises an error

Author
Peter Gabriel peter.nosp@m.@gab.nosp@m.riel-.nosp@m.onli.nosp@m.ne.ne.nosp@m.t
Version
Id:
class.ilDBx.php 20110 2009-06-03 14:44:55Z wrandels

public

Definition at line 48 of file class.ilDBx.php.

Member Function Documentation

ilDBx::_ilDBx ( )

destructor

Definition at line 139 of file class.ilDBx.php.

{
//$this->db->disconnect();
} //end destructor
ilDBx::_lockTables (   $a_table_params)

Lock existing table.

Parameters
array(tablename => lock type READ, WRITE, READ LOCAL or LOW_PRIORITY) e.g array('tree' => 'WRITE')
Returns
boolean

Definition at line 472 of file class.ilDBx.php.

Referenced by ilTree\deleteTree(), ilTree\insertNode(), ilTree\moveTree(), ilTree\renumber(), and ilTree\saveSubTree().

{
global $ilDB;
$lock_str = 'LOCK TABLES ';
$counter = 0;
foreach($a_table_params as $table_name => $type)
{
$lock_str .= $counter++ ? ',' : '';
$lock_str .= $table_name.' '.$type;
}
$ilDB->query($lock_str);
return true;
}

+ Here is the caller graph for this function:

ilDBx::_unlockTables ( )

Definition at line 488 of file class.ilDBx.php.

Referenced by ilTree\deleteTree(), ilTree\insertNode(), ilTree\moveTree(), and ilTree\renumber().

{
global $ilDB;
$ilDB->query('UNLOCK TABLES');
return true;
}

+ Here is the caller graph for this function:

ilDBx::addTypesToArray (   $a_arr,
  $a_type,
  $a_cnt 
)

Adds a type x times to an array.

Definition at line 384 of file class.ilDBx.php.

{
if ($a_cnt > 0)
{
$type_arr = array_fill(0, $a_cnt, $a_type);
}
else
{
$type_arr = array();
}
return array_merge($a_arr, $type_arr);
}
ilDBx::autoExecute (   $a_tablename,
  $a_fields,
  $a_mode = MDB2_AUTOQUERY_INSERT,
  $a_where = false 
)

Wrapper for Pear autoExecute.

Parameters
stringtablename
arrayfields values
intMDB2_AUTOQUERY_INSERT or MDB2_AUTOQUERY_UPDATE
stringwhere condition (e.g. "obj_id = '7' AND ref_id = '5'")
Returns
mixed a new DB_result/DB_OK or a DB_Error, if fail

Definition at line 601 of file class.ilDBx.php.

References $data, and $res.

{
$res = $this->db->autoExecute($a_tablename,$a_fields,$a_mode,$a_where);
if (MDB2::isError($res))
{
$this->raiseError($res->getMessage()."<br><font size=-1>SQL: ".$data."</font>", $this->error_class->FATAL);
}
else
{
return $res;
}
}
ilDBx::beginTransaction ( )

Begin Transaction.

Please note that we currently do not use savepoints.

Returns
boolean MDB2_OK on success

Definition at line 411 of file class.ilDBx.php.

References $res.

{
if (!$this->db->supports('transactions'))
{
$this->raiseError("ilDB::beginTransaction: Transactions are not supported.", $this->error_class->FATAL);
}
$res = $this->db->beginTransaction();
if(MDB2::isError($res))
{
$this->raiseError($res->getMessage()."<br><font size=-1>SQL: ".$query."</font>", $this->error_class->FATAL);
}
return $res;
}
ilDBx::checkQuerySize (   $a_query)

Check query size.

Definition at line 573 of file class.ilDBx.php.

References $lang.

{
global $lang;
if(strlen($a_query) >= $this->max_allowed_packet_size)
{
return false;
}
else
{
return true;
}
}
ilDBx::commit ( )

Commit a transaction.

Definition at line 429 of file class.ilDBx.php.

References $res.

{
$res = $this->db->commit();
if(MDB2::isError($res))
{
$this->raiseError($res->getMessage()."<br><font size=-1>SQL: ".$query."</font>", $this->error_class->FATAL);
}
return $res;
}
ilDBx::createTable (   $a_name,
  $a_definition_array,
  $a_options = "" 
)

Create a new table in the database.

Definition at line 154 of file class.ilDBx.php.

{
if ($a_options == "")
{
$a_options = array();
}
$manager = $this->db->loadModule('Manager');
$r = $manager->createTable($a_name, $a_definition_array, $a_options);
if (MDB2::isError($r))
{
//$err = "<br>Details: ".mysql_error();
$this->raiseError($r->getMessage()."<br><font size=-1>SQL: ".$sql.$err."</font>", $this->error_class->FATAL);
}
else
{
return $r;
}
}
ilDBx::disconnect ( )

disconnect from database

Definition at line 146 of file class.ilDBx.php.

{
// $this->db->disconnect();
}
ilDBx::execute (   $a_stmt,
  $a_data = null 
)

Execute a query statement prepared by either prepare() or prepareManip()

Parameters
objectResource handle of the prepared query.
arrayArray of data (to be used for placeholders)
Returns
mixed A result handle or MDB2_OK on success, a MDB2 error on failure

Definition at line 309 of file class.ilDBx.php.

References $res.

{
$res = $a_stmt->execute($a_data);
if (MDB2::isError($res))
{
$this->raiseError($res->getMessage()."<br><font size=-1>SQL: ".$a_stmt->query . " with data " . print_r($a_data, true) ."</font>", $this->error_class->FATAL);
}
else
{
return $res;
}
}
ilDBx::executeMultiple (   $a_stmt,
  $a_data 
)

Execute a query statement prepared by either prepare() or prepareManip() with multiple data arrays.

Parameters
objectResource handle of the prepared query.
arrayArray of array of data (to be used for placeholders)
Returns
mixed A result handle or MDB2_OK on success, a MDB2 error on failure

Definition at line 332 of file class.ilDBx.php.

References $data, and $res.

{
$res = $this->db->extended->executeMultiple($a_stmt,$a_data);
if (MDB2::isError($res))
{
$this->raiseError($res->getMessage()."<br><font size=-1>SQL: ".$data."</font>", $this->error_class->FATAL);
}
else
{
return $res;
}
}
ilDBx::fetchAssoc (   $a_set)

Fetch row as associative array from result set.

Parameters
objectresult set

Definition at line 351 of file class.ilDBx.php.

References DB_FETCHMODE_ASSOC.

{
return $a_set->fetchRow(DB_FETCHMODE_ASSOC);
}
ilDBx::fetchObject (   $a_set)

Fetch row as object from result set.

Parameters
objectresult set

Definition at line 361 of file class.ilDBx.php.

References DB_FETCHMODE_OBJECT.

{
return $a_set->fetchObject(DB_FETCHMODE_OBJECT);
}
ilDBx::getInClause (   $a_field,
  $a_values 
)

Get abstract in-clause for given array.

Returns an array "field_name IN (?,?,?,...)" depending on the size of the array

Definition at line 370 of file class.ilDBx.php.

{
if (count($a_values) == 0)
{
return " 1=2 "; // return a false statement on empty array
}
$str = $a_field." IN (?".str_repeat(",?", count($a_values) - 1).")";
return $str;
}
ilDBx::getLastInsertId ( )

get last insert id

Definition at line 457 of file class.ilDBx.php.

References $res.

{
$res = $this->db->lastInsertId();
if(MDB2::isError($res))
{
return false;
}
return $res;
}
ilDBx::getMySQLVersion ( )

get mysql version

Definition at line 500 of file class.ilDBx.php.

{
return mysql_get_server_info();
}
ilDBx::getOne (   $sql)

getOne.

DEPRECATED. Should not be used anymore.

this is the wrapper itself. Runs a query and returns the first column of the first row or in case of an error, jump to errorpage

Parameters
string
Returns
object DB

Definition at line 761 of file class.ilDBx.php.

References DB_FETCHMODE_ASSOC.

{
//$r = $this->db->getOne($sql);
$set = $this->db->query($sql);
if (MDB2::isError($set))
{
$this->raiseError($set->getMessage()."<br><font size=-1>SQL: ".$sql."</font>", $this->error_class->FATAL);
return;
}
$r = $set->fetchRow(DB_FETCHMODE_ASSOC);
return $r[0];
} //end function
ilDBx::getRow (   $sql,
  $mode = DB_FETCHMODE_OBJECT 
)

getRow.

DEPRECATED. Should not be used anymore

this is the wrapper itself. query a string, and return the resultobject, or in case of an error, jump to errorpage

Parameters
string
Returns
object DB

Definition at line 787 of file class.ilDBx.php.

References query().

{
$set = $this->query($sql);
$r = $set->fetchRow($mode);
//$r = $this->db->getrow($sql,$mode);
if (MDB2::isError($r))
{
$this->raiseError($r->getMessage()."<br><font size=-1>SQL: ".$sql."</font>", $this->error_class->FATAL);
}
else
{
return $r;
}
} //end function

+ Here is the call graph for this function:

ilDBx::ilDBx (   $dsn)

constructor

set up database conncetion and the errorhandling

Parameters
stringdsn database-connection-string for pear-db

Definition at line 83 of file class.ilDBx.php.

References isDbError(), isMysql4_1OrHigher(), loadMDB2Extensions(), query(), and setMaxAllowedPacket().

{
//call parent constructor
$parent = get_parent_class($this);
$this->$parent();
//set up error handling
$this->error_class = new ilErrorHandling();
$this->setErrorHandling(PEAR_ERROR_CALLBACK, array($this->error_class,'errorHandler'));
//check dsn
if ($dsn=="")
$this->raiseError("no DSN given", $this->error_class->FATAL);
$this->dsn = $dsn;
//connect to database
//$this->db = DB::connect($this->dsn, true);
$this->db = MDB2::connect($this->dsn, array("use_transactions" => true));
// set empty value portability to PEAR::DB behaviour
if (!$this->isDbError($this->db))
{
$cur = ($this->db->getOption("portability") & MDB2_PORTABILITY_EMPTY_TO_NULL);
$this->db->setOption("portability", $this->db->getOption("portability") - $cur);
$cur = ($this->db->getOption("portability") & MDB2_PORTABILITY_FIX_CASE);
$this->db->setOption("portability", $this->db->getOption("portability") - $cur);
}
//check error
if (MDB2::isError($this->db)) {
$this->raiseError($this->db->getMessage(), $this->error_class->FATAL);
}
// SET 'max_allowed_packet' (only possible for mysql version 4)
// NOTE: Three sourcecodes use this or a similar handling:
// - classes/class.ilDBx.php
// - calendar/classes/class.ilCalInterface.php->setNames
// - setup/classes/class.ilClient.php
if ($this->isMysql4_1OrHigher())
{
$this->query("SET NAMES utf8");
$this->query("SET SESSION SQL_MODE = ''");
}
return true;
} //end constructor

+ Here is the call graph for this function:

ilDBx::isMysql4_0OrHigher ( )

check wether current MySQL server is version 4.0.x or higher

Definition at line 509 of file class.ilDBx.php.

{
$version = explode(".", $this->getMysqlVersion());
if((int) $version[0] >= 4)
{
return true;
}
return false;
}
ilDBx::isMysql4_1 ( )

check wether current MySQL server is version 4.1.x

Definition at line 522 of file class.ilDBx.php.

{
$version = explode(".", $this->getMysqlVersion());
if ($version[0] == "4" && $version[1] == "1")
{
return true;
}
return false;
}
ilDBx::isMysql4_1OrHigher ( )

check wether current MySQL server is version 4.1.x or higher

NOTE: Three sourcecodes use this or a similar handling:

Definition at line 541 of file class.ilDBx.php.

Referenced by ilDBx().

{
$version = explode(".", $this->getMysqlVersion());
if ((int)$version[0] >= 5 ||
((int)$version[0] == 4 && (int)$version[1] >= 1))
{
return true;
}
return false;
}

+ Here is the caller graph for this function:

ilDBx::listTables ( )

Checks for the existence of a table column.

Parameters
string$a_tableThe table name which should be examined
string$a_column_nameThe name of the column
Returns
boolean TRUE if the table column exists, FALSE otherwise

Definition at line 708 of file class.ilDBx.php.

{
$manager = $this->db->loadModule('Manager');
$r = $manager->listTables();
if (!MDB2::isError($r))
{
return $r;
}
return false;
}
ilDBx::loadMDB2Extensions ( )
protected

load additional mdb2 extensions and set their constants

protected

Definition at line 558 of file class.ilDBx.php.

References isDbError().

Referenced by ilDBx().

{
if (!$this->isDbError($this->db))
{
$this->db->loadModule('Extended');
define('DB_AUTOQUERY_SELECT',MDB2_AUTOQUERY_SELECT);
define('DB_AUTOQUERY_INSERT',MDB2_AUTOQUERY_INSERT);
define('DB_AUTOQUERY_UPDATE',MDB2_AUTOQUERY_UPDATE);
define('DB_AUTOQUERY_DELETE',MDB2_AUTOQUERY_DELETE);
}
}

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

ilDBx::manipulate (   $sql)

Simple data manipulatoin.

This function should only be used for simple data manipulations without parameters. Queries should not be done with it.

Example:

  • "DELETE * FROM data"

For simple data queries use query(). For complex queries/manipulations use prepare()/prepareManip() and execute.

Parameters
stringDML string
Returns
int affected rows

Definition at line 244 of file class.ilDBx.php.

{
$r = $this->db->exec($sql);
if (MDB2::isError($r))
{
$err = "<br>Details: ".mysql_error();
$this->raiseError($r->getMessage()."<br><font size=-1>SQL: ".$sql.$err."</font>", $this->error_class->FATAL);
}
else
{
return $r;
}
}
ilDBx::prepare (   $a_query,
  $a_types = null,
  $a_result_types = null 
)

Prepare a query (SELECT) statement to be used with execute.

Parameters
StringQuery String
ArrayPlaceholder Types
Returns
Resource handle for the prepared query on success, a MDB2 error on failure.

Definition at line 267 of file class.ilDBx.php.

References $res.

{
$res = $this->db->prepare($a_query, $a_types, $a_result_types);
if (MDB2::isError($res))
{
$this->raiseError($res->getMessage()."<br><font size=-1>SQL: ".$a_query."</font>", $this->error_class->FATAL);
}
else
{
return $res;
}
}
ilDBx::prepareManip (   $a_query,
  $a_types = null 
)

Prepare a data manipulation statement to be used with execute.

Parameters
StringQuery String
ArrayPlaceholder Types
Returns
Resource handle for the prepared query on success, a MDB2 error on failure.

Definition at line 288 of file class.ilDBx.php.

References $res.

{
$res = $this->db->prepare($a_query, $a_types, MDB2_PREPARE_MANIP);
if (MDB2::isError($res))
{
$this->raiseError($res->getMessage()."<br><font size=-1>SQL: ".$a_query."</font>", $this->error_class->FATAL);
}
else
{
return $res;
}
}
ilDBx::query (   $sql)

Simple query.

This function should only be used for simple select queries without parameters. Data manipulation should not be done with it.

Example:

  • "SELECT * FROM data"

For simple data manipulation use manipulate(). For complex queries/manipulations use prepare()/prepareManip() and execute.

Parameters
string
Returns
object DB

Definition at line 188 of file class.ilDBx.php.

Referenced by getRow(), and ilDBx().

{
$r = $this->db->query($sql);
/*$pos1 = strpos(strtolower($sql), "from ");
$table = "";
if ($pos1 > 0)
{
$tablef = substr($sql, $pos1+5);
$pos2 = strpos(strtolower($tablef), " ");
if ($pos2 > 0)
{
$table =substr($tablef, 0, $pos2);
}
else
{
$table = $tablef;
}
}
if (trim($table) != "")
{
if (!is_array($this->ttt) || !in_array($table, $this->ttt))
{
echo "<br>".$table;
$this->ttt[] = $table;
}
}
else
{
echo "<br><b>".$sql."</b>";
}*/
//error_log('ilDBx '.$sql.' #rows:'.$r->numRows());
if (MDB2::isError($r))
{
$err = "<br>Details: ".mysql_error();
$this->raiseError($r->getMessage()."<br><font size=-1>SQL: ".$sql.$err."</font>", $this->error_class->FATAL);
}
else
{
return $r;
}
}

+ Here is the caller graph for this function:

ilDBx::quote (   $a_query,
  $null_as_empty_string = true 
)

Wrapper for quote method.

Deprecated, use prepare/prepareManip instead.

Definition at line 730 of file class.ilDBx.php.

{
if ($null_as_empty_string)
{
// second test against 0 is crucial for MDB2
//if ($a_query == "" && $a_query !== 0)
if ($a_query == "")
{
$a_query = "";
}
}
if (method_exists($this->db, "quoteSmart"))
{
return $this->db->quoteSmart($a_query);
}
else
{
return $this->db->quote($a_query);
}
}
ilDBx::rollback ( )

Rollback a transaction.

Definition at line 443 of file class.ilDBx.php.

References $res.

{
$res = $this->db->rollback();
if(MDB2::isError($res))
{
$this->raiseError($res->getMessage()."<br><font size=-1>SQL: ".$query."</font>", $this->error_class->FATAL);
}
return $res;
}
ilDBx::setMaxAllowedPacket ( )
private

Set maximum allowed packet size.

Definition at line 618 of file class.ilDBx.php.

References $res, and DB_FETCHMODE_OBJECT.

Referenced by ilDBx().

{
// GET MYSQL VERSION
$query = "SHOW VARIABLES LIKE 'version'";
$res = $this->db->query($query);
if(MDB2::isError($res))
{
$this->raiseError($res->getMessage()."<br><font size=-1>SQL: ".$query."</font>", $this->error_class->FATAL);
}
while($row = $res->fetchRow(DB_FETCHMODE_OBJECT))
{
$version = $row->Value;
}
// CHANG VALUE IF MYSQL VERSION > 4.0
if(substr($version,0,1) == "4")
{
ini_get("post_max_size");
$query = "SET GLOBAL max_allowed_packet = ".(int) ini_get("post_max_size") * 1024 * 1024;
$this->db->query($query);
if(MDB2::isError($res))
{
$this->raiseError($res->getMessage()."<br><font size=-1>SQL: ".$query."</font>", $this->error_class->FATAL);
}
}
// STORE NEW max_size in member variable
$query = "SHOW VARIABLES LIKE 'max_allowed_packet'";
if(MDB2::isError($res))
{
$this->raiseError($res->getMessage()."<br><font size=-1>SQL: ".$query."</font>", $this->error_class->FATAL);
}
$res = $this->db->query($query);
while($row = $res->fetchRow(DB_FETCHMODE_OBJECT))
{
$this->max_allowed_packet_size = $row->Value;
//$this->max_allowed_packet_size = $row->value;
}
#var_dump("<pre>",$this->max_allowed_packet_size,"<pre>");
return true;
}

+ Here is the caller graph for this function:

ilDBx::tableColumnExists (   $a_table,
  $a_column_name 
)

Checks for the existence of a table column.

Parameters
string$a_tableThe table name which should be examined
string$a_column_nameThe name of the column
Returns
boolean TRUE if the table column exists, FALSE otherwise

Definition at line 669 of file class.ilDBx.php.

{
$column_visibility = false;
$manager = $this->db->loadModule('Manager');
$r = $manager->listTableFields($a_table);
if (!MDB2::isError($r))
{
foreach($r as $field)
{
if ($field == $a_column_name)
{
$column_visibility = true;
}
}
}
return $column_visibility;
/*$query = "SHOW COLUMNS FROM `$a_table`";
$res = $this->db->query($query);
while ($data = $res->fetchRow(DB_FETCHMODE_ASSOC))
{
if ((strcmp($data["Field"], $a_column_name) == 0) || (strcmp($data["field"], $a_column_name) == 0))
{
$column_visibility = TRUE;
}
}
return $column_visibility;*/
}

Field Documentation

ilDBx::$db

Definition at line 61 of file class.ilDBx.php.

ilDBx::$error_class

Definition at line 55 of file class.ilDBx.php.

ilDBx::$max_allowed_packet_size

Definition at line 73 of file class.ilDBx.php.

ilDBx::$result

Definition at line 67 of file class.ilDBx.php.


The documentation for this class was generated from the following file: