/**
* Performs the actual optimization. In this case the optimizer looks
* at the size of the column and the size of the value. If the value size is
* smaller than the column size it tries to convert the column to a smaller
* size. Next, it counts if there is any different between the smaller column
* and the original column. If no differences are found the original column
* gets replaced.
* Like the other optimizers, this optimizer returns TRUE if it thinks
* further optimizations can happen, FALSE otherwise.
*
* @return boolean $yesNo advance to next optimizer
*/
public function optimize()
{
//get the type of the current value
$type = $this->writer->scanType($this->value);
//get all the fields in the table
$fields = $this->writer->getColumns($this->table);
//If the column for some reason does not occur in fields, return
if (!in_array($this->column, array_keys($fields))) {
return false;
}
//get the type we got in the field of the table
$typeInField = $this->writer->code($fields[$this->column]);
//Is the type too wide?
if ($type < $typeInField) {
try {
@$this->adapter->exec("alter table " . $this->writer->safeTable($this->table) . " drop __test");
} catch (Exception $e) {
}
//Try to re-fit the entire column; by testing it.
$type = $this->writer->typeno_sqltype[$type];
//Add a test column.
@$this->adapter->exec("alter table " . $this->writer->safeTable($this->table) . " add __test " . $type);
//Copy the values and see if there are differences.
@$this->adapter->exec("update " . $this->writer->safeTable($this->table) . " set __test=" . $this->writer->safeColumn($this->column) . "");
$rows = $this->adapter->get("select " . $this->writer->safeColumn($this->column) . " as a, __test as b from " . $this->writer->safeTable($this->table));
$diff = 0;
foreach ($rows as $row) {
$diff += $row["a"] != $row["b"];
}
if (!$diff) {
//No differences; shrink column.
@$this->adapter->exec("alter table " . $this->writer->safeTable($this->table) . " change " . $this->writer->safeColumn($this->column) . " " . $this->writer->safeColumn($this->column) . " " . $type);
}
//Throw away test column; we don't need it anymore!
@$this->adapter->exec("alter table " . $this->writer->safeTable($this->table) . " drop __test");
}
return false;
}
/**
* Performs the actual optimization. In this case the optimizer first
* scans the value. If the value if of type 'datetime' and the column
* is not it tries to make the column datetime. If the column is 'datetime'
* and the value 'datetime' it blocks further optimization. If the value
* is NOT 'datetime' then it immediately returns true, thus allowing further
* optimization.
*
* @return boolean $yesNo advance to next optimizer
*/
public function optimize()
{
if (!$this->matchesDateTime($this->value)) {
return true;
}
//get the type of the current value
$type = $this->writer->scanType($this->value);
//get all the fields in the table
$fields = $this->writer->getColumns($this->table);
//If the column for some reason does not occur in fields, return
//print_r($fields);
if (!in_array($this->column, array_keys($fields))) {
return false;
}
//get the type we got in the field of the table
$typeInField = $this->writer->code($fields[$this->column]);
//Is column already datetime?
if ($typeInField != "datetime") {
if ($this->matchesDateTime($this->value)) {
//Ok, value is datetime, can we convert the column to support this?
$cnt = (int) $this->adapter->getCell("select count(*) as n from " . $this->writer->safeTable($this->table) . " where\n\t\t\t\t\t\t {$this->column} regexp '[0-9]{4}-[0-1][0-9]-[0-3][0-9] [0-2][0-9]:[0-5][0-9]:[0-5][0-9]'\n\t\t\t\t\t\t OR {$this->column} IS NULL");
$total = (int) $this->adapter->getCell("SELECT count(*) FROM " . $this->writer->safeTable($this->table));
//Is it safe to convert: ie are all values compatible?
if ($total === $cnt) {
//yes
$this->adapter->exec("ALTER TABLE " . $this->writer->safeTable($this->table) . " change " . $this->writer->safeColumn($this->column) . " " . $this->writer->safeColumn($this->column) . " datetime ");
}
//No further optimization required.
return false;
}
//Further optimization could be useful.
return true;
} else {
//yes column is datetime, if value is stop further optimizing
return false;
}
}
/**
* Performs the actual optimization. In this case the optimizer first
* scans the value. If the value if of type 'datetime' and the column
* is not it tries to make the column datetime. If the column is 'datetime'
* and the value 'datetime' it blocks further optimization. If the value
* is NOT 'datetime' then it immediately returns true, thus allowing further
* optimization.
*
* @return boolean $yesNo advance to next optimizer
*/
public function optimize() {
if (!$this->matchesDateTime($this->value)) return true;
$type = $this->writer->scanType($this->value);
$fields = $this->writer->getColumns($this->table);
if (!in_array($this->column,array_keys($fields))) return false;
$typeInField = $this->writer->code($fields[$this->column]);
if ($typeInField!="datetime") {
if ($this->matchesDateTime($this->value)) {
$cnt = (int) $this->adapter->getCell("select count(*) as n from {$this->table} where
{$this->column} regexp '[0-9]{4}-[0-1][0-9]-[0-3][0-9] [0-2][0-9]:[0-5][0-9]:[0-5][0-9]'
OR {$this->column} IS NULL");
$total = (int) $this->adapter->getCell("SELECT count(*) FROM ".$this->writer->noKW($this->table));
if ($total===$cnt) {
$this->adapter->exec("ALTER TABLE ".$this->writer->noKW($this->table)." change ".$this->writer->noKW($this->column)." ".$this->writer->noKW($this->column)." datetime ");
}
return false;
}
return true;
}
else {
return false;
}
}
