CakePHP SchemaShell Column Type Problem!

If you’ve done any type of programming work you know that leveraging an existing framework can be worth it’s weight in gold. In my off hours I spend quite a bit of time working with PHP and have come to really enjoy one of the leading PHP frameworks – CakePHP.

Cake (as many refer to it) includes a great Console utility called SchemaShell. It’s main purpose is to help you manage your DB schema from one revision of your code to the next. Rolling out new schemas is typically a very time intensive operation, which is now easily handled with a couple of calls to this really powerful utility.

Recently I ran in to a couple of issues where the SchemaShell didn’t understand my column types. Specifically:

• All column types that exist as any type of “BLOB” (LONGBLOB, BLOB, etc) were considered “BINARY”
• All “BINARY” column types ended up being considered “BLOB”

Cake goes out of its way to generalize and abstract the data layer from any technology specific implementations. I.E. If today I’m using MySQL and tomorrow I want to leverage MSSQL (or even use them both at the same time), I should be able to do so with little to no effort. This ultimately means Cake must filter and generalize it’s use of column types.

In my particular instance I use BINARY types for my primary key columns and LONGBLOB types to store a bunch of serialized model data. I use MySQL exclusively for the app in question – at least at this point (yes yes, NOSQL is on the way 🙂 ).

The fix was quite simple so I thought I would share. It’s really a matter of modifying your DataSource. In my case I use a custom DataSource with some added functionality. If you aren’t familiar with the concept of a CakePHP DataSource – read up here.

Remember, don’t modify the core CakePHP code. Extend an existing DataSource and store your changes in a newly created DataSource (from the app/Model/DataSource directory).

• Create a new Database DataSource file in app/Model/DataSource/Database/MysqlNew.php:

  class MysqlACL extends Mysql {
  }

• Override the column() method so that you recognize and handle your intended column type appropriately:

  class MysqlACL extends Mysql {
  
  	public function column($real) {
  		if (is_array($real)) {
  			$col = $real['name'];
  			if (isset($real['limit'])) {
  				$col .= '(' . $real['limit'] . ')';
  			}
  			return $col;
  		}
  
  		$col = str_replace(')', '', $real);
  		$limit = $this->length($real);
  		if (strpos($col, '(') !== false) {
  			list($col, $vals) = explode('(', $col);
  		}
  
  		if (in_array($col, array('date', 'time', 'datetime', 'timestamp'))) {
  			return $col;
  		}
  		if (($col === 'tinyint' && $limit == 1) || $col === 'boolean') {
  			return 'boolean';
  		}
  		if (strpos($col, 'int') !== false) {
  			return 'integer';
  		}
  		if (strpos($col, 'char') !== false || $col === 'tinytext') {
  			return 'string';
  		}
  		if (strpos($col, 'text') !== false) {
  			return 'text';
  		}
  		if ($col === 'binary') {
  			return 'binary';
  		}
  		if (strpos($col, 'blob') !== false) {
  			return 'longblob';
  		}
  		if (strpos($col, 'float') !== false || strpos($col, 'double') !== false || strpos($col, 'decimal') !== false) {
  			return 'float';
  		}
  		if (strpos($col, 'enum') !== false) {
  			return "enum($vals)";
  		}
  		return 'text';
  	}
  
  }

  Notice specifically the modification of the if statement for the $col === ‘binary’ operator and the addition of a new if statement to handle any $col that contains the string ‘blob’.

• Last but not least make sure the column property has a legit mapping to the new:

  class MysqlACL extends Mysql {
  
  	public $columns = array(
  			'primary_key' => array('name' => 'NOT NULL AUTO_INCREMENT'),
  			'string' => array('name' => 'varchar', 'limit' => '255'),
  			'text' => array('name' => 'text'),
  			'integer' => array('name' => 'int', 'limit' => '11', 'formatter' => 'intval'),
  			'float' => array('name' => 'float', 'formatter' => 'floatval'),
  			'datetime' => array('name' => 'datetime', 'format' => 'Y-m-d H:i:s', 'formatter' => 'date'),
  			'timestamp' => array('name' => 'timestamp', 'format' => 'Y-m-d H:i:s', 'formatter' => 'date'),
  			'time' => array('name' => 'time', 'format' => 'H:i:s', 'formatter' => 'date'),
  			'date' => array('name' => 'date', 'format' => 'Y-m-d', 'formatter' => 'date'),
  			'binary' => array('name' => 'binary'), 
  			'blob' => array('name' => 'blob'),
  			'longblob' => array('name' => 'longblob'),
  			'boolean' => array('name' => 'tinyint', 'limit' => '1')
  	);
  
  	public function column($real) {
  		if (is_array($real)) {
  			$col = $real['name'];
  			if (isset($real['limit'])) {
  				$col .= '(' . $real['limit'] . ')';
  			}
  			return $col;
  		}
  
  		$col = str_replace(')', '', $real);
  		$limit = $this->length($real);
  		if (strpos($col, '(') !== false) {
  			list($col, $vals) = explode('(', $col);
  		}
  
  		if (in_array($col, array('date', 'time', 'datetime', 'timestamp'))) {
  			return $col;
  		}
  		if (($col === 'tinyint' && $limit == 1) || $col === 'boolean') {
  			return 'boolean';
  		}
  		if (strpos($col, 'int') !== false) {
  			return 'integer';
  		}
  		if (strpos($col, 'char') !== false || $col === 'tinytext') {
  			return 'string';
  		}
  		if (strpos($col, 'text') !== false) {
  			return 'text';
  		}
  		if ($col === 'binary') {
  			return 'binary';
  		}
  		if (strpos($col, 'blob') !== false) {
  			return 'longblob';
  		}
  		if (strpos($col, 'float') !== false || strpos($col, 'double') !== false || strpos($col, 'decimal') !== false) {
  			return 'float';
  		}
  		if (strpos($col, 'enum') !== false) {
  			return "enum($vals)";
  		}
  		return 'text';
  	}
  
  }

Now you should be able to leverage the new DataSource MysqlNew for SchemaShell and safely be able to transport your schemas that contain the new column types.