Mysql store Boolean as TinyInt(1)

[WinterSilence]

What steps will reproduce the problem?

Create table in mysql8.* with boolean column.

What's expected?

Generator work with column tinyint(1) as is.

What do you get instead?

case Schema::TYPE_TINYINT:
    if ($this->getDbDriverName() === 'mysql' && $column->size === 1 && in_array($column->defaultValue, [0, 1])) {
        $type = 'bool';
    } else {
        $type = 'int';
    }
    break;

Additional info

Q	A
Yii version	2.0.40, gii 2.2.1
PHP version	7.3
Database version	msql8
Operating system	Win10

[uldisn]

Your suggestion no certain. As detour can be using enum(Yes/No), but in Gii PR frozen #434 . In Giiant enum realized

[bizley]

I'm not sure what is the problem here.

[uldisn]

No any way, how recognize column as boolean, except add to column name something like bool. For example: is_expired_bool

[bizley]

@uldisn you are talking about different problem than @WinterSilence right?

[WinterSilence]

@bizley yes, @uldisn add quick fix for me, but I can't replace tinyint to enum in db without BC. Field type determinate validation rules and html fields in CRUD views. I want display that's columns as boolean - checkbox and value 0/1 or true/false (in strict mode too).

[bizley]

So the value is not cast to boolean?

[WinterSilence]

@bizley enum's too work strange: in form - select, in model - string without rules as in_array()

[WinterSilence]

@bizley cast where? in yii code or in db?

[bizley]

What is the type of such attribute when you fetch the model from a database?

[WinterSilence]

@bizley https://dev.mysql.com/doc/refman/5.7/en/numeric-type-syntax.html it's stored as tinyint but in sql you can set as true/false

[bizley]

I'm not asking how it's stored, MySQL stores booleans as tinyInts. I'm asking what are you getting in the model when you fetch it from DB.

[uldisn]

For me actually is problem with booleans:

• model generator can not recognize from table schema
• if model genera recognize boolean, it can generate isExpiredTrue(), setExpiredTrue(). what is helpfully for writing logic
• crud generator can generate boolean columns and check boxes in forms
  Main problem is recognizing boolean from table scheme

[bizley]

@uldisn this problem has been mentioned several times already in different issues. Main problem here is to properly handle enums in all database engines that Yii supports. So far we don't have one elegant solution. Please don't hijack this issue.

[WinterSilence]

@bizley after my fix:

/**
 * @property bool|null $is_bool
 */
class TblTest1 extends \yii\db\ActiveRecord
{ 
    /**
     * {@inheritDoc}
     */
    public function rules(): array
    {
        return [
            ...
            [['is_bool'], 'boolean'],
           ...
        ]

[WinterSilence]

@bizley i talk about boolean, not enum. gii use fix for postgresql without any problem

[bizley]

Oh, I think I understand now. Generating model from MySQL DB is not preparing boolean rule validator for that field, right? Hmm, I was quite sure tinyint(1) is cast as boolean in that case but looks like not. @samdark could you refresh my memory about that? There is this code in MySQL Schema class but there is bit there... Hm...

[samdark]

In recent MySQL versions these two types aren't equivalent:

• https://dev.mysql.com/doc/refman/8.0/en/bit-type.html
• https://dev.mysql.com/doc/refman/8.0/en/integer-types.html

But overall, we can add an alias, I see no problem with it.

@WinterSilence are we correct that you expect a field with tinyint(1) or bit to have boolean in validation rule?

[WinterSilence]

@samdark To cover more cases, value may be any value converting to boolean/int(to BC) before validation. Boolean values are stored as integers 0 (false) and 1 (true). I check it's valid for most popular db: mysql, postgres, mssql, sqllite.
Form field determinate by type?

Database	Boolean?	Use Instead
Oracle	No	NUMBER(1)
SQL Server	No	BIT
MySQL	No	BIT or TINYINT
SQLite	No	TINYINT
PostgreSQL	Yes

[samdark]

We can do it for Gii for sure. As for doing it at schema level, it may break things.

[WinterSilence]

@samdark I'm extended class yii\gii\generators\model\Generator:

protected function generateProperties($table)
{
    $properties = [];
    $driverName = $this->getDbDriverName();
    foreach ($table->columns as $column) {
        switch ($column->type) {
            case Schema::TYPE_SMALLINT:
            case Schema::TYPE_INTEGER:
            case Schema::TYPE_BIGINT:
                $type = 'int';
                break;
            case Schema::TYPE_TINYINT:
                if ($driverName !== 'pgsql' && $column->size === 1 && in_array($column->defaultValue, [0, 1])) {
                    $type = 'bool';
                } else {
                    $type = 'int';
                }
                break;

can be possible set 2 types for column?

public function generateRules($table)
{
    $types = [];
    $lengths = [];
    $driverName = $this->getDbDriverName();
    foreach ($table->columns as $column) {
        if ($column->autoIncrement) {
            continue;
        }
        if (!$column->allowNull && $column->defaultValue === null) {
            $types['required'][] = $column->name;
        }
        switch ($column->type) {
            case Schema::TYPE_SMALLINT:
            case Schema::TYPE_INTEGER:
            case Schema::TYPE_BIGINT:
                $types['integer'][] = $column->name;
                break;
            case Schema::TYPE_TINYINT:
                if ($driverName !== 'pgsql' && $column->size === 1 && in_array($column->defaultValue, [0, 1])) {
                    // maybe add extra rule [$column->name, 'in', 'range' => [0, 1, true, false, null], 'strict' => true]
                    $types['boolean'][] = $column->name;
                } else {
                    $types['integer'][] = $column->name;
                }

off top: display column from related model as select in form. it can be possible?

[samdark]

can be possible set 2 types for column?

2 types at the same time? Ummm... no? But you've got overall idea alright.

[WinterSilence]

@samdark how you validate multi type values?

/**
 * Checks type of field value.
 *
 * @param mixed $value Field value.
 * @param string ...$types Valid value types.
 * @return bool
 */
public static function type($value, string ...$types): bool
{
    $type = gettype($value);
    if (in_array($type, $types, true)) {
        return true;
    }
    // Check extra types as "numeric", "iterable", "countable" and etc.
    foreach ($types as $type) {
        $func = 'is_' . $type;
        if (function_exists($func) && $func($value)) {
            return true;
        }
    }
    return false;
}

yii have validation rules like that?

[samdark]

Not really.

[WinterSilence]

@samdark

Not really.

what do you mean?

[samdark]

I mean there are no such built-in validators.

[michaelarnauts]

We have modified gii to do this instead about a years ago, and we've never looked back. It works as expected this way.

This is the same as @WinterSilence has shown.

                case Schema::TYPE_TINYINT:
                    if ($column->size == 1) {
                        $types['boolean'][] = $column->name;
                    } else {
                        $types['integer'][] = $column->name;
                    }
                    break;

It even has a benefit that you'll still be able to use $object->enabled = false and $object->enabled = 0. Both work fine.

[WinterSilence]

@michaelarnauts in_array($column->defaultValue, [0, 1]) is not strict check i.e. $column->defaultValue can be bool, string or numeric, it's added to mysql 5.*

[handcode]

How are you going to know from the schema that a tinyint(1) column should be "just" a bolean?
As mysql accepts 255 different values for a tinyint(1), such cols can be booleans but also "something" else.

Example: a tinyint(1) col for different states that can be 1,2,3,4,... is totally valid, but clearly not a boolean.

(My) conclusion: The reason why one want a boolean validator for this in many cases is quite clear to me, but I don't think it's a good idea to "guess" that it's a boolean based on a DB type that can also be "something else".

[uldisn]

How are you going to know from the schema that a tinyint(1) column should be "just" a bolean? As mysql accepts 255 different values for a tinyint(1), such cols can be booleans but also "something" else.

Example: a tinyint(1) col for different states that can be 1,2,3,4,... is totally valid, but clearly not a boolean.

(My) conclusion: The reason why one want a boolean validator for this in many cases is quite clear to me, but I don't think it's a good idea to "guess" that it's a boolean based on a DB type that can also be "something else".

Something else:

• additional input field, where wrtite boolean fieldnames seperated by comma;
• in boolena field use defined prefix or suffix.

[uldisn]

If you look at the mysql documentation, you can see that this data type can be -128...127 signed and 0...255 unsigned. tinyint(1) doesn't equal the boolean type and this change will potentially break a lot of projects

https://dev.mysql.com/doc/refman/8.0/en/integer-types.html

In gii generator model screen add input field, where write comma separated boolean field names