FK-less relations

Archives Yii 1.0.x Discussions
1

Is there a way to allow AR to work with MySQL MyISAM, that is without FK constraints? According to the documentation this is possible with SQLite.

Quote

Tip: For SQLite database, we may create tables that define the foreign key constraints such as the example below. However, these constraints are NOT enforced by the SQLite database itself.

/Tommy

2

This is not supported. The Guide about SQLite says you need to define the FK constraints, but they are not enforced by SQLite.

3

I believe an option for locking down the model to a development config would solve my problem since I have InnoDB installed. Current web hotel of choice don't support InnoDB.

4

Here's my suggested workaround (one line replaced in CMysqlSchema.php):



require_once(dirname(__FILE__).'/protected/dbfix/CMysqlSchema.php');








	protected function findConstraints($table)


	{


		//$row=$this->getDbConnection()->createCommand('SHOW CREATE TABLE '.$table->rawName)->queryRow();





		$row=array(file_get_contents(dirname(__FILE__).'/'.str_replace('`','',$table->rawName).'.sql', FILE_TEXT));





		$matches=array();


		$regexp='/FOREIGN KEYs+(([^)]+))s+REFERENCESs+([^(^s]+)s*(([^)]+))/mi';


		foreach($row as $sql)


		{


			if(preg_match_all($regexp,$sql,$matches,PREG_SET_ORDER))


				break;


		}

The files are standard phpMyAdmin table dumps. Had to use file_get_contents() instead of file(), obviously because SHOW CREATE TABLE returns multiple FK declarations on the same line (causing exit from the foreach loop).

/Tommy

5

I am afraid this workaround is not appropriate to be used in the framework. It is inefficient (file operation) and requires specific file locations.

If there is a bug with the SHOW CREATE TABLE approach, could you please report and we will see how to solve it.

6

I completely agree. Have to admit I hadn't yet thought about performance. I can imagine the files should be preloaded instead.

Also, I posted this just in case somebody else has the same situation (uses relations, want to take advance of AR, but myISAM-only hosting available).

Possibly referential checks has to be added to the CUD actions, thats what I'm going to have a look at next, now that I know I won't end up with an Yii exception due to stripped away FK declarations on deploying the database.

/Tommy