Improve Gii for MySQL datatype (ENUM, SET)

Archives Feature Requests
1

Hi,

First, thank to the Yii team, you guys made a wonderful framework.

I’m a newbie in Yii, there is a bit of uncomfortable of Gii when I used it.

I usually use MySQL for my websites, and then, the ENUM and SET datatype is my favorite. Whenever I use Gii to generate CRUB code, it seems like Gii doesn’t understand 2 above datatypes. It generated form fields like a text or int datatype. I had to modify CRUB code manually.

As my thinking, Gii should generate a dropdownlist for ENUM type, and a multi select options for SET type.

What do you think?

This is my table create statement.

[sql]CREATE TABLE rv.member(

id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,

email VARCHAR(255) NOT NULL,

first_name VARCHAR(255) NOT NULL,

last_name VARCHAR(255) NOT NULL,

display_name VARCHAR(255) NOT NULL,

skype VARCHAR(255) DEFAULT NULL,

phone VARCHAR(255) DEFAULT NULL,

ym VARCHAR(255) DEFAULT NULL,

src ENUM(‘GOOGLE’, ‘YAHOO’) NOT NULL,

roles SET(‘MEMBER’, ‘MODERATOR’, ‘ADMIN’, ‘SUPER_ADMIN’) NOT NULL DEFAULT ‘MEMBER’,

PRIMARY KEY (id),

UNIQUE INDEX email (email)

)

ENGINE = INNODB

COLLATE utf8_general_ci;[/sql]

2

Using the data type SET is generally a bad idea. It conflicts with the fundamental principles of a RDBMS. Yii does not understand these, and I don’t think it should.

By the way, the right term is CRUD, Create Read Update Delete.

I agree with you on the ENUM type. Though it is not a SQL standard, Gii already parses ENUMs for MySQL and MSSQL. So:

  • There should be a rule in the model to assert that only the right values are saved (Model).

  • There should be a drop-down list in "_form.php" (CRUD).

  • The admin page should also display a drop-down list (CRUD).

There a ticket for this feature in Yii’s BTS : #2945. It shows what kind of code will add the rules to a model. If you want to try it, copy the default Gii template for models “yii/framework/gii/generators/model/templates/default/model.php” into “protected/gii/model/templates/myself/model.php” and modify it:




<?php foreach($rules as $rule): ?>

	<?php echo $rule.",\n"; ?>

<?php endforeach; ?>

<?php

// insert a specific rule for each ENUM

foreach ($columns as $name => $column) {

	if (strncmp($column->dbType, 'enum', 4) === 0 && preg_match('/\((.*)\)/', $column->dbType, $matches)) {

		$values = str_replace("''", "\'", $matches[1]); // won't work if there are "\" characters

		echo "\t\t\tarray('{$name}', 'in', array({$values})), // enum\n";

	}

}

?>

Generating drop-down lists in the view templates will be similar (same if).

3

Thank you, I will try your code.

I totally agree with you that the SET datatype is not RDBMS standard. But most of the time I work with MySQL, in some cases (for i.e : roles in my 1st post), it’s really helpful. Yii’s model itself splited SET datatype into array, but Gii not yet supports it. I think in case of RDBMS is MySQL Gii should understand this datatype, 'cause MySQL is popular anyway.