ILIAS  eassessment Revision 61809
 All Data Structures Namespaces Files Functions Variables Groups Pages
Public Member Functions | Data Fields
ilMySQLAbstraction Class Reference
Services/Database

This class includes methods that help to abstract ILIAS 3.10.x MySQL tables for the use with MDB2 abstraction layer and full compliance mode support. More...

+ Collaboration diagram for ilMySQLAbstraction:

Public Member Functions

 __construct ()
 Constructor.
 setTestMode ($a_testmode)
 Set Test Mode.
 getTestMode ()
 Get Test Mode.
 performAbstraction ($a_table_name, $a_set_text_ts_fields_notnull_false=true)
 Converts an existing (MySQL) ILIAS table in an abstract table.
 countRecords ($a_table_name)
 Check number of records before and after.
 storeStep ($a_table, $a_step)
 Store performed step.
 replaceEmptyStringsWithNull ($a_table)
 Replace empty strings with null values.
 replaceEmptyDatesWithNull ($a_table)
 Replace empty dates with null.
 lowerCaseTableName ($a_table_name)
 Lower case table and field names.
 lowerCaseColumnNames ($a_table_name)
 lower case column names
 removeAutoIncrement ($a_table_name, $a_auto_inc_field)
 Remove auto_increment attribute of a field.
 removePrimaryKey ($a_table, $a_pk)
 Remove primary key from table.
 removeIndices ($a_table, $a_indices)
 Remove Indices.
 removeConstraints ($a_table, $a_constraints)
 Remove Constraints.
 alterTable ($a_table, $a_fields, $a_set_text_ts_fields_notnull_false=true, $pk="")
 Use abstract types as delivered by MDB2 to alter table and make it use only MDB2 known types.
 addPrimaryKey ($a_table, $a_pk)
 Add primary key.
 addIndices ($a_table, $a_indices)
 Add indices.
 addConstraints ($a_table, $a_constraints)
 Add constraints.
 fixIndexNames ($a_table)
 This is only used on tables that have already been abstracted but missed the "full treatment".
 addAutoIncrementSequence ($a_table, $a_auto_inc_field)
 Add autoincrement sequence.
 fixClobNotNull ($a_table)
 This is only used on tables that have already been abstracted but missed the "full treatment".
 fixDatetimeValues ($a_table)
 This is only used on tables that have already been abstracted but missed the "full treatment".

Data Fields

 $analyzer

Detailed Description

This class includes methods that help to abstract ILIAS 3.10.x MySQL tables for the use with MDB2 abstraction layer and full compliance mode support.

Author
Alex Killing alex..nosp@m.kill.nosp@m.ing@g.nosp@m.mx.d.nosp@m.e
Version
Id:
class.ilDBUpdate.php 18649 2009-01-21 09:59:23Z akill

Definition at line 14 of file class.ilMySQLAbstraction.php.

Constructor & Destructor Documentation

ilMySQLAbstraction::__construct ( )

Constructor.

Definition at line 21 of file class.ilMySQLAbstraction.php.

References $ilDB, and setTestMode().

{
global $ilDB;
$this->il_db = $ilDB;
$this->manager = $ilDB->db->loadModule('Manager');
$this->reverse = $ilDB->db->loadModule('Reverse');
if (is_file('../Services/Database/classes/class.ilDBAnalyzer.php'))
{
include_once '../Services/Database/classes/class.ilDBAnalyzer.php';
}
else
{
include_once './Services/Database/classes/class.ilDBAnalyzer.php';
}
$this->analyzer = new ilDBAnalyzer();
$this->setTestMode(false);
}

+ Here is the call graph for this function:

Member Function Documentation

ilMySQLAbstraction::addAutoIncrementSequence (   $a_table,
  $a_auto_inc_field 
)

Add autoincrement sequence.

Parameters
stringtable name
stringautoincrement field

Definition at line 633 of file class.ilMySQLAbstraction.php.

Referenced by performAbstraction().

{
if ($a_auto_inc_field != "")
{
$set = $this->il_db->query("SELECT MAX(`".strtolower($a_auto_inc_field)."`) ma FROM `".$a_table."`");
$rec = $this->il_db->fetchAssoc($set);
$next = $rec["ma"] + 1;
$this->il_db->createSequence($a_table, $next);
}
}

+ Here is the caller graph for this function:

ilMySQLAbstraction::addConstraints (   $a_table,
  $a_constraints 
)

Add constraints.

Parameters
stringtable name
arrayconstraints information

Definition at line 562 of file class.ilMySQLAbstraction.php.

References $f, and $pos.

Referenced by performAbstraction().

{
if (is_array($a_constraints))
{
$all_valid = true;
foreach ($a_constraints as $c)
{
if (strlen($c["name"]) > 3)
{
$all_valid = false;
}
}
$cnt = 1;
foreach ($a_constraints as $c)
{
if (is_array($c["fields"]))
{
if (!$all_valid)
{
$c["name"] = "c".$cnt;
}
$fields = array();
foreach ($c["fields"] as $f => $pos)
{
$fields[] = strtolower($f);
}
$this->il_db->addUniqueConstraint($a_table, $fields, strtolower($c["name"]));
$cnt++;
}
}
}
}

+ Here is the caller graph for this function:

ilMySQLAbstraction::addIndices (   $a_table,
  $a_indices 
)

Add indices.

Parameters
stringtable name
arrayindices information

Definition at line 521 of file class.ilMySQLAbstraction.php.

References $f, and $pos.

Referenced by fixIndexNames(), and performAbstraction().

{
if (is_array($a_indices))
{
$all_valid = true;
foreach ($a_indices as $index)
{
if (strlen($index["name"]) > 3)
{
$all_valid = false;
}
}
$cnt = 1;
foreach ($a_indices as $index)
{
if (is_array($index["fields"]))
{
if (!$all_valid)
{
$index["name"] = "i".$cnt;
}
$fields = array();
foreach ($index["fields"] as $f => $pos)
{
$fields[] = strtolower($f);
}
$this->il_db->addIndex($a_table, $fields, strtolower($index["name"]), $index["fulltext"]);
$cnt++;
}
}
}
}

+ Here is the caller graph for this function:

ilMySQLAbstraction::addPrimaryKey (   $a_table,
  $a_pk 
)

Add primary key.

Parameters
stringtable name
arrayprimary key information

Definition at line 502 of file class.ilMySQLAbstraction.php.

References $f, and $pos.

Referenced by performAbstraction().

{
if (is_array($a_pk["fields"]))
{
$fields = array();
foreach ($a_pk["fields"] as $f => $pos)
{
$fields[] = strtolower($f);
}
$this->il_db->addPrimaryKey($a_table, $fields);
}
}

+ Here is the caller graph for this function:

ilMySQLAbstraction::alterTable (   $a_table,
  $a_fields,
  $a_set_text_ts_fields_notnull_false = true,
  $pk = "" 
)

Use abstract types as delivered by MDB2 to alter table and make it use only MDB2 known types.

Parameters
stringtable name
arrayfields information

Definition at line 391 of file class.ilMySQLAbstraction.php.

References $d, getTestMode(), and MDB2\isError().

Referenced by performAbstraction().

{
$n_fields = array();
foreach ($a_fields as $field => $d)
{
$def = $this->reverse->getTableFieldDefinition($a_table, $field);
$this->il_db->handleError($def);
$best_alt = $this->analyzer->getBestDefinitionAlternative($def);
$def = $def[$best_alt];
// remove "current_timestamp" default for timestamps (not supported)
if (strtolower($def["nativetype"]) == "timestamp" &&
strtolower($def["default"]) == "current_timestamp")
{
unset($def["default"]);
}
if (strtolower($def["type"]) == "float")
{
unset($def["length"]);
}
// remove all invalid attributes
foreach ($def as $k => $v)
{
if (!in_array($k, array("type", "default", "notnull", "length", "unsigned", "fixed")))
{
unset($def[$k]);
}
}
// determine length for decimal type
if ($def["type"] == "decimal")
{
$l_arr = explode(",",$def["length"]);
$def["length"] = $l_arr[0];
}
// remove lenght values for float
if ($def["type"] == "float")
{
unset($def["length"]);
}
// set notnull to false for text/timestamp/date fields
if ($a_set_text_ts_fields_notnull_false && ($def["type"] == "text" ||
$def["type"] == "timestamp" || $def["type"] == "date") &&
(!is_array($pk) || !isset($field,$pk["fields"][$field])))
{
$def["notnull"] = false;
}
// set unsigned to false for integers
if ($def["type"] == "integer")
{
$def["unsigned"] = false;
}
// set notnull to false for blob and clob
if ($def["type"] == "blob" || $def["type"] == "clob")
{
$def["notnull"] = false;
}
// remove "0000-00-00..." default values
if (($def["type"] == "timestamp" && $def["default"] == "0000-00-00 00:00:00") ||
($def["type"] == "date" && $def["default"] == "0000-00-00"))
{
unset($def["default"]);
}
$a = array();
foreach ($def as $k => $v)
{
$a[$k] = $v;
}
$def["definition"] = $a;
$n_fields[$field] = $def;
}
$changes = array(
"change" => $n_fields
);
//var_dump($n_fields);
if (!$this->getTestMode())
{
$r = $this->manager->alterTable($a_table, $changes, false);
}
else
{
$r = $this->manager->createTable(strtolower($a_table)."_copy", $n_fields);
}
if (MDB2::isError($r))
{
//$err = "<br>Details: ".mysql_error();
var_dump($r);
}
else
{
return $r;
}
}

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

ilMySQLAbstraction::countRecords (   $a_table_name)

Check number of records before and after.

Definition at line 196 of file class.ilMySQLAbstraction.php.

References $ilDB, and $res.

Referenced by performAbstraction().

{
global $ilDB;
$st = $ilDB->prepare("SELECT count(*) AS cnt FROM `".$a_table_name."`");
$res = $ilDB->execute($st);
$rec = $ilDB->fetchAssoc($res);
return $rec["cnt"];
}

+ Here is the caller graph for this function:

ilMySQLAbstraction::fixClobNotNull (   $a_table)

This is only used on tables that have already been abstracted but missed the "full treatment".

Definition at line 648 of file class.ilMySQLAbstraction.php.

References $name.

{
if (!$this->il_db->tableExists($a_table))
{
return;
}
$all_valid = true;
$fields = $this->analyzer->getFieldInformation($a_table);
foreach ($fields as $name => $def)
{
if ($def["type"] == "clob" && $def["notnull"] == true)
{
$this->il_db->modifyTableColumn($a_table, $name, array("type" => "clob", "notnull" => false));
}
}
}
ilMySQLAbstraction::fixDatetimeValues (   $a_table)

This is only used on tables that have already been abstracted but missed the "full treatment".

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

References $name.

{
if (!$this->il_db->tableExists($a_table))
{
return;
}
$all_valid = true;
$fields = $this->analyzer->getFieldInformation($a_table);
foreach ($fields as $name => $def)
{
if ($def["type"] == "timestamp" &&
($def["notnull"] == true || $def["default"] == "0000-00-00 00:00:00"))
{
$nd = array("type" => "timestamp", "notnull" => false);
if ($def["default"] == "0000-00-00 00:00:00")
{
$nd["default"] = null;
}
$this->il_db->modifyTableColumn($a_table, $name, $nd);
}
if ($def["type"] == "date" &&
($def["notnull"] == true || $def["default"] == "0000-00-00"))
{
$nd = array("type" => "date", "notnull" => false);
if ($def["default"] == "0000-00-00")
{
$nd["default"] = null;
}
$this->il_db->modifyTableColumn($a_table, $name, $nd);
}
}
}
ilMySQLAbstraction::fixIndexNames (   $a_table)

This is only used on tables that have already been abstracted but missed the "full treatment".

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

References addIndices().

{
if (!$this->il_db->tableExists($a_table))
{
return;
}
$all_valid = true;
$indices = $this->analyzer->getIndicesInformation($a_table);
foreach ($indices as $index)
{
if (strlen($index["name"]) > 3)
{
$all_valid = false;
}
}
if (!$all_valid)
{
foreach($indices as $index)
{
$this->il_db->dropIndex($a_table, $index["name"]);
}
$this->addIndices($a_table, $indices);
}
}

+ Here is the call graph for this function:

ilMySQLAbstraction::getTestMode ( )

Get Test Mode.

Returns
boolean Test Mode

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

Referenced by alterTable(), and performAbstraction().

{
return $this->testmode;
}

+ Here is the caller graph for this function:

ilMySQLAbstraction::lowerCaseColumnNames (   $a_table_name)

lower case column names

Definition at line 304 of file class.ilMySQLAbstraction.php.

References $ilDB, and $row.

Referenced by performAbstraction().

{
global $ilDB;
$result = mysql_query("SHOW COLUMNS FROM `".$a_table_name."`");
while ($row = mysql_fetch_assoc($result))
{
if ($row["Field"] != strtolower($row["Field"]))
{
$ilDB->renameTableColumn($a_table_name, $row["Field"], strtolower($row["Field"]));
}
}
}

+ Here is the caller graph for this function:

ilMySQLAbstraction::lowerCaseTableName (   $a_table_name)

Lower case table and field names.

Parameters
stringtable name

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

References $ilDB.

Referenced by performAbstraction().

{
global $ilDB;
if ($a_table_name != strtolower($a_table_name))
{
// this may look strange, but it does not work directly
// (seems that mysql does not see no difference whether upper or lowercase characters are used
mysql_query("ALTER TABLE `".$a_table_name."` RENAME `".strtolower($a_table_name)."xxx"."`");
mysql_query("ALTER TABLE `".strtolower($a_table_name)."xxx"."` RENAME `".strtolower($a_table_name)."`");
}
}

+ Here is the caller graph for this function:

ilMySQLAbstraction::performAbstraction (   $a_table_name,
  $a_set_text_ts_fields_notnull_false = true 
)

Converts an existing (MySQL) ILIAS table in an abstract table.

This means the table conforms to the MDB2 field types, uses sequences instead of auto_increment.

Parameters
stringtable name

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

References addAutoIncrementSequence(), addConstraints(), addIndices(), addPrimaryKey(), alterTable(), countRecords(), getTestMode(), lowerCaseColumnNames(), lowerCaseTableName(), removeAutoIncrement(), removeConstraints(), removeIndices(), removePrimaryKey(), replaceEmptyDatesWithNull(), replaceEmptyStringsWithNull(), and storeStep().

{
// to do: log this procedure
// count number of records at the beginning
$nr_rec = $this->countRecords($a_table_name);
// convert table name to lowercase
if (!$this->getTestMode())
{
$this->lowerCaseTableName($a_table_name);
$a_table_name = strtolower($a_table_name);
$this->storeStep($a_table_name, 10);
}
// get auto increment information
$auto_inc_field = $this->analyzer->getAutoIncrementField($a_table_name);
// get primary key information
$pk = $this->analyzer->getPrimaryKeyInformation($a_table_name);
// get indices information
$indices = $this->analyzer->getIndicesInformation($a_table_name);
// get constraints information
$constraints = $this->analyzer->getConstraintsInformation($a_table_name);
// get field information
$fields = $this->analyzer->getFieldInformation($a_table_name);
if (!$this->getTestMode())
{
// remove auto increment
$this->removeAutoIncrement($a_table_name, $auto_inc_field, $fields);
$this->storeStep($a_table_name, 20);
// remove primary key
$this->removePrimaryKey($a_table_name, $pk);
$this->storeStep($a_table_name, 30);
// remove indices
$this->removeIndices($a_table_name, $indices);
$this->storeStep($a_table_name, 40);
// remove constraints
$this->removeConstraints($a_table_name, $constraints);
$this->storeStep($a_table_name, 45);
}
// alter table using mdb2 field types
$this->alterTable($a_table_name, $fields, $a_set_text_ts_fields_notnull_false, $pk);
if ($this->getTestMode())
{
$a_table_name = strtolower($a_table_name)."_copy";
}
else
{
$this->storeStep($a_table_name, 50);
}
// lower case field names
$this->lowerCaseColumnNames($a_table_name);
if (!$this->getTestMode())
{
$this->storeStep($a_table_name, 60);
}
// add primary key
$this->addPrimaryKey($a_table_name, $pk);
if (!$this->getTestMode())
{
$this->storeStep($a_table_name, 70);
}
// add indices
$this->addIndices($a_table_name, $indices);
if (!$this->getTestMode())
{
$this->storeStep($a_table_name, 80);
}
// add constraints
$this->addConstraints($a_table_name, $constraints);
if (!$this->getTestMode())
{
$this->storeStep($a_table_name, 85);
}
// add "auto increment" sequence
if ($auto_inc_field != "")
{
$this->addAutoIncrementSequence($a_table_name, $auto_inc_field);
}
if (!$this->getTestMode())
{
$this->storeStep($a_table_name, 90);
}
// replace empty strings with null values in text fields
$this->replaceEmptyStringsWithNull($a_table_name);
if (!$this->getTestMode())
{
$this->storeStep($a_table_name, 100);
}
// replace empty "0000-00-00..." dates with null
$this->replaceEmptyDatesWithNull($a_table_name);
if (!$this->getTestMode())
{
$this->storeStep($a_table_name, 110);
}
$nr_rec2 = $this->countRecords($a_table_name);
if (!$this->getTestMode())
{
if ($nr_rec != $nr_rec2)
{
die("ilMySQLAbstraction: Unexpected difference in table record number, table '".$a_table_name."'.".
" Before: ".((int) $nr_rec).", After: ".((int) $nr_rec2).".");
}
}
}

+ Here is the call graph for this function:

ilMySQLAbstraction::removeAutoIncrement (   $a_table_name,
  $a_auto_inc_field 
)

Remove auto_increment attribute of a field.

Parameters
stringtable name
stringautoincrement field

Definition at line 325 of file class.ilMySQLAbstraction.php.

Referenced by performAbstraction().

{
if ($a_auto_inc_field != "")
{
$this->il_db->modifyTableColumn($a_table_name, $a_auto_inc_field, array());
}
}

+ Here is the caller graph for this function:

ilMySQLAbstraction::removeConstraints (   $a_table,
  $a_constraints 
)

Remove Constraints.

Parameters
stringtable name
arrayconstraints information

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

Referenced by performAbstraction().

{
if (is_array($a_constraints))
{
foreach($a_constraints as $c)
{
if ($c["type"] == "unique")
{
$this->il_db->query("ALTER TABLE `".$a_table."` DROP INDEX `".$c["name"]."`");
}
}
}
}

+ Here is the caller graph for this function:

ilMySQLAbstraction::removeIndices (   $a_table,
  $a_indices 
)

Remove Indices.

Parameters
stringtable name
arrayindices information

Definition at line 353 of file class.ilMySQLAbstraction.php.

Referenced by performAbstraction().

{
if (is_array($a_indices))
{
foreach($a_indices as $index)
{
$this->il_db->query("ALTER TABLE `".$a_table."` DROP INDEX `".$index["name"]."`");
}
}
}

+ Here is the caller graph for this function:

ilMySQLAbstraction::removePrimaryKey (   $a_table,
  $a_pk 
)

Remove primary key from table.

Parameters
stringtable name
arrayprimary key information

Definition at line 339 of file class.ilMySQLAbstraction.php.

Referenced by performAbstraction().

{
if ($a_pk["name"] != "")
{
$this->il_db->dropPrimaryKey($a_table, $a_pk["name"]);
}
}

+ Here is the caller graph for this function:

ilMySQLAbstraction::replaceEmptyDatesWithNull (   $a_table)

Replace empty dates with null.

Definition at line 243 of file class.ilMySQLAbstraction.php.

References $ilDB.

Referenced by performAbstraction().

{
global $ilDB;
if (!$this->il_db->tableExists($a_table))
{
return;
}
$fields = $this->analyzer->getFieldInformation($a_table);
$upfields = array();
foreach ($fields as $field => $def)
{
if ($def["type"] == "timestamp")
{
$upfields[] = $field;
}
}
foreach ($upfields as $uf)
{
$ilDB->query("UPDATE `".$a_table."` SET `".$uf."` = null WHERE `".$uf."` = '0000-00-00 00:00:00'");
}
$upfields = array();
reset($fields);
foreach ($fields as $field => $def)
{
if ($def["type"] == "date")
{
$upfields[] = $field;
}
}
foreach ($upfields as $uf)
{
$ilDB->query("UPDATE `".$a_table."` SET `".$uf."` = null WHERE `".$uf."` = '0000-00-00'");
}
}

+ Here is the caller graph for this function:

ilMySQLAbstraction::replaceEmptyStringsWithNull (   $a_table)

Replace empty strings with null values.

Definition at line 220 of file class.ilMySQLAbstraction.php.

References $ilDB.

Referenced by performAbstraction().

{
global $ilDB;
$fields = $this->analyzer->getFieldInformation($a_table);
$upfields = array();
foreach ($fields as $field => $def)
{
if ($def["type"] == "text" &&
($def["length"] >= 1 && $def["length"] <= 4000))
{
$upfields[] = $field;
}
}
foreach ($upfields as $uf)
{
$ilDB->query("UPDATE `".$a_table."` SET `".$uf."` = null WHERE `".$uf."` = ''");
}
}

+ Here is the caller graph for this function:

ilMySQLAbstraction::setTestMode (   $a_testmode)

Set Test Mode.

Parameters
boolean$a_testmodeTest Mode

Definition at line 45 of file class.ilMySQLAbstraction.php.

Referenced by __construct().

{
$this->testmode = $a_testmode;
}

+ Here is the caller graph for this function:

ilMySQLAbstraction::storeStep (   $a_table,
  $a_step 
)

Store performed step.

Definition at line 210 of file class.ilMySQLAbstraction.php.

Referenced by performAbstraction().

{
$st = $this->il_db->prepareManip("REPLACE INTO abstraction_progress (table_name, step)".
" VALUES (?,?)", array("text", "integer"));
$this->il_db->execute($st, array($a_table, $a_step));
}

+ Here is the caller graph for this function:

Field Documentation

ilMySQLAbstraction::$analyzer

Definition at line 16 of file class.ilMySQLAbstraction.php.

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