/**
* Optimize table column types, based on hints
* @param string $table name of the table
* @param string $columnName name of the column
* @param string $datatype
*/
public static function optimize($table, $columnName, $datatype, $length = null)
{
try {
$databaseColumnType = DatabaseCompatibilityUtil::mapHintTypeIntoDatabaseColumnType($datatype, $length);
if (isset(self::$optimizedTableColumns[$table])) {
$fields = self::$optimizedTableColumns[$table];
// It is possible that field is created outside optimizer, so in this case reload fields from database
if (!in_array($columnName, array_keys($fields))) {
$fields = R::$writer->getColumns($table);
}
} else {
$fields = R::$writer->getColumns($table);
}
if (in_array($columnName, array_keys($fields))) {
$columnType = $fields[$columnName];
if (strtolower($columnType) != strtolower($databaseColumnType)) {
if (strtolower($datatype) == 'string' && isset($length) && $length > 0) {
$maxLength = R::getCell("SELECT MAX(LENGTH({$columnName})) FROM {$table}");
if ($maxLength <= $length) {
R::exec("alter table {$table} change {$columnName} {$columnName} " . $databaseColumnType);
}
} else {
R::exec("alter table {$table} change {$columnName} {$columnName} " . $databaseColumnType);
}
}
} else {
R::exec("alter table {$table} add {$columnName} " . $databaseColumnType);
}
} catch (RedBean_Exception_SQL $e) {
//42S02 - Table does not exist.
if (!in_array($e->getSQLState(), array('42S02'))) {
throw $e;
} else {
R::$writer->createTable($table);
R::exec("alter table {$table} add {$columnName} " . $databaseColumnType);
}
}
if (isset($fields)) {
self::$optimizedTableColumns[$table] = $fields;
} else {
self::$optimizedTableColumns[$table] = R::$writer->getColumns($table);
}
self::$optimizedTableColumns[$table][$columnName] = $databaseColumnType;
}
public static function resolveColumnMetadataByHintType($name, $hintType = 'string', $length = 255, $unsigned = null, $notNull = 'NULL', $default = 'DEFAULT NULL', $collation = null, $resolveName = true)
{
// TODO: @Shoaibi: Critical: write tests for: integer, smallint, tinyint, blob, date, datetime, double, string, text, email, url
// with and without column ending with _id, check collation, unsigned, type, default
if ($resolveName) {
$name = static::resolve($name);
}
// map reasonable default values
$defaults = array('hintType' => 'string', 'length' => 255, 'notNull' => 'NULL', 'default' => 'DEFAULT NULL', 'unsigned' => 'eval:DatabaseCompatibilityUtil::resolveUnsignedByHintType($hintType, ' . RedBeanModelMemberRulesToColumnAdapter::ASSUME_SIGNED . ", '{$name}');", 'collation' => 'eval:DatabaseCompatibilityUtil::resolveCollationByHintType($hintType);');
foreach ($defaults as $key => $defaultValue) {
if (!isset(${$key})) {
MetadataUtil::resolveEvaluateSubString($defaultValue, 'hintType', $hintType);
${$key} = $defaultValue;
}
}
// field is set to be NOT NULL in db, its default can't be 'NULL', unsetting variable. // Not Coding Standard
if ($notNull !== 'NULL') {
$default = null;
}
// resolve hint type to db type.
$type = DatabaseCompatibilityUtil::mapHintTypeIntoDatabaseColumnType($hintType, $length);
$column = compact('name', 'type', 'unsigned', 'notNull', 'collation', 'default');
return $column;
}
public function testMapHintTypeIntoDatabaseColumnType()
{
if (RedBeanDatabase::getDatabaseType() == 'mysql') {
$databaseColumnType = DatabaseCompatibilityUtil::mapHintTypeIntoDatabaseColumnType('blob');
$this->assertEquals('BLOB', $databaseColumnType);
$databaseColumnType = DatabaseCompatibilityUtil::mapHintTypeIntoDatabaseColumnType('longblob');
$this->assertEquals('LONGBLOB', $databaseColumnType);
$databaseColumnType = DatabaseCompatibilityUtil::mapHintTypeIntoDatabaseColumnType('boolean');
$this->assertEquals('TINYINT(1)', $databaseColumnType);
$databaseColumnType = DatabaseCompatibilityUtil::mapHintTypeIntoDatabaseColumnType('date');
$this->assertEquals('DATE', $databaseColumnType);
$databaseColumnType = DatabaseCompatibilityUtil::mapHintTypeIntoDatabaseColumnType('datetime');
$this->assertEquals('DATETIME', $databaseColumnType);
$databaseColumnType = DatabaseCompatibilityUtil::mapHintTypeIntoDatabaseColumnType('string');
$this->assertEquals('VARCHAR(255)', $databaseColumnType);
$databaseColumnType = DatabaseCompatibilityUtil::mapHintTypeIntoDatabaseColumnType('text');
$this->assertEquals('TEXT', $databaseColumnType);
$databaseColumnType = DatabaseCompatibilityUtil::mapHintTypeIntoDatabaseColumnType('longtext');
$this->assertEquals('LONGTEXT', $databaseColumnType);
$databaseColumnType = DatabaseCompatibilityUtil::mapHintTypeIntoDatabaseColumnType('id');
$this->assertEquals('INT(11) UNSIGNED', $databaseColumnType);
try {
$databaseColumnType = DatabaseCompatibilityUtil::mapHintTypeIntoDatabaseColumnType('invalidType');
$this->fail();
} catch (NotSupportedException $e) {
// Do nothing
}
}
}
