Yii2: GII does not generate in the rules function for string fields the 'max' option using MSSQL

[rlcarnevale]

When I use GII to generate a model in the rules function of the generated model, the string type does not insert the 'max' option.

Versions:

YII 2.0.15.1
yiisoft/yii2-gii 2.0.7

PHP 5.6.31

PDO Extension for MSSQL: php_pdo_sqlsrv_56_ts.dll

Windows 10 Home Single Language 64 Bits

SQL Server 2008 R2

Microsoft SQL Server Management Studio 10.50.4000.0
Microsoft Data Access Components (MDAC) 10.0.17134.1
Microsoft MSXML 3.0 5.0 6.0
Microsoft Internet Explorer 9.11.17134.0
Microsoft .NET Framework 2.0.50727.8922
Sistema Operacional 6.3.17134

Script table for DB MSSQL

CREATE TABLE tab_funcoessistema (
id int identity NOT NULL,
descricaoCurta varchar(25) NOT NULL,
descricaoLonga varchar(100) NOT NULL,
modulo varchar(14) NOT NULL,
controlador varchar(50) NOT NULL,
acao varchar(25) NOT NULL,
incIp varchar(15) DEFAULT NULL,
incLogin varchar(10) DEFAULT NULL,
incData datetime DEFAULT NULL,
altIp varchar(15) DEFAULT NULL,
altLogin varchar(10) DEFAULT NULL,
altData datetime DEFAULT NULL,
CONSTRAINT pk_tab_funcoessistema PRIMARY KEY (id)
)

CREATE UNIQUE INDEX Itab_funcoessistema ON tab_funcoessistema (descricaoCurta);

Model generated using table in MSSQL database:
See in the rules function in the fields of type string NO option was added 'max'

public function rules()
{
    return [
        [['descricaoCurta', 'descricaoLonga', 'modulo', 'controlador', 'acao'], 'required'],
        [['descricaoCurta', 'descricaoLonga', 'modulo', 'controlador', 'acao', 'incIp', 'incLogin', 'altIp', 'altLogin'], 'string'],
        [['incData', 'altData'], 'safe'],
    ];
}

Script table for DB MYSQL

CREATE TABLE IF NOT EXISTS tab_funcoessistema (
id int(11) NOT NULL AUTO_INCREMENT,
descricaoCurta varchar(25) NOT NULL,
descricaoLonga varchar(100) NOT NULL,
modulo varchar(14) NOT NULL,
controlador varchar(50) NOT NULL,
acao varchar(25) NOT NULL,
incIp varchar(15) DEFAULT NULL,
incLogin varchar(10) DEFAULT NULL,
incData datetime DEFAULT NULL,
altIp varchar(15) DEFAULT NULL,
altLogin varchar(10) DEFAULT NULL,
altData datetime DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY Itab_funcoesSistema (descricaoCurta)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Model generated using table in MYSQL database:
See in the rules function in the fields of type string was added the option 'max'

public function rules()
{
    return [
        [['descricaoCurta', 'descricaoLonga', 'modulo', 'controlador', 'acao'], 'required'],
        [['incData', 'altData'], 'safe'],
        [['descricaoCurta', 'acao'], 'string', 'max' => 25],
        [['descricaoLonga'], 'string', 'max' => 100],
        [['modulo'], 'string', 'max' => 14],
        [['controlador'], 'string', 'max' => 50],
        [['incIp', 'altIp'], 'string', 'max' => 15],
        [['incLogin', 'altLogin'], 'string', 'max' => 10],
        [['descricaoCurta'], 'unique'],
    ];
}

Thanks in advance for a possible correction of the problem

[yii-bot]

Thank you for your issue.

Unfortunately code that is not formatted as code blocks is extremely hard to read.
Please check GitHub formatting guide and wrap code accordingly.

Thanks!

_This is an automated comment, triggered by adding the label missing formatting._

[rlcarnevale]

Analyzing the problem, I checked that in mssql the column 'data_type'
is filled only with the data type and the size of the column is filled
in the column 'character_maximum_length', example:

data_type = varchar
character_maximum_length = 10

expected column data_type varchar(10)

After analyzing the MSSQL documentation I came to the conclusion how to change
the file code \ vendor \ yiisoft \ yii2 \ db \ mssql \ Schema.php
in the findColumns function by setting the SQL script as follows:

Code before change:

    $sql = <<<SQL

SELECT
[t1].[column_name],
[t1].[is_nullable],
[t1].[data_type],
[t1].[column_default],
COLUMNPROPERTY(OBJECT_ID([t1].[table_schema] + '.' + [t1].[table_name]), [t1].[column_name], 'IsIdentity') AS is_identity,
(
SELECT CONVERT(VARCHAR, [t2].[value])
FROM [sys].[extended_properties] AS [t2]
WHERE
[t2].[class] = 1 AND
[t2].[class_desc] = 'OBJECT_OR_COLUMN' AND
[t2].[name] = 'MS_Description' AND
[t2].[major_id] = OBJECT_ID([t1].[TABLE_SCHEMA] + '.' + [t1].[table_name]) AND
[t2].[minor_id] = COLUMNPROPERTY(OBJECT_ID([t1].[TABLE_SCHEMA] + '.' + [t1].[TABLE_NAME]), [t1].[COLUMN_NAME], 'ColumnID')
) as comment
FROM {$columnsTableName} AS [t1]
WHERE {$whereSql}
SQL;

Code changed: for expected column data_type 'varchar(10)'

    $sql = <<<SQL

SELECT
[t1].[column_name],
[t1].[is_nullable],
CASE
WHEN [t1].[data_type] IN ('char','varchar','nchar','nvarchar','binary','varbinary') THEN
CASE
WHEN [t1].[character_maximum_length] = NULL OR [t1].[character_maximum_length] = -1 THEN
[t1].[data_type]
ELSE
[t1].[data_type] + '(' + LTRIM(RTRIM(CONVERT(CHAR,[t1].[character_maximum_length]))) + ')'
END
ELSE
[t1].[data_type]
END AS 'data_type',
[t1].[column_default],
COLUMNPROPERTY(OBJECT_ID([t1].[table_schema] + '.' + [t1].[table_name]), [t1].[column_name], 'IsIdentity') AS is_identity,
(
SELECT CONVERT(VARCHAR, [t2].[value])
FROM [sys].[extended_properties] AS [t2]
WHERE
[t2].[class] = 1 AND
[t2].[class_desc] = 'OBJECT_OR_COLUMN' AND
[t2].[name] = 'MS_Description' AND
[t2].[major_id] = OBJECT_ID([t1].[TABLE_SCHEMA] + '.' + [t1].[table_name]) AND
[t2].[minor_id] = COLUMNPROPERTY(OBJECT_ID([t1].[TABLE_SCHEMA] + '.' + [t1].[TABLE_NAME]), [t1].[COLUMN_NAME], 'ColumnID')
) as comment
FROM {$columnsTableName} AS [t1]
WHERE {$whereSql}
SQL;

I request that this change be validated for further inclusion in the YII code.

Thank you.

[samdark]

Would you please do it as a pull request?

[rlcarnevale]

Sorry I'm new to using GITHUB could you guide me on how to correctly create
a pull request? Or could you do it for me?

Thank you

2018-06-12 3:29 GMT-03:00 Alexander Makarov [e-mail]:

Would you please do it as a pull request?

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
https://github.com/yiisoft/yii2/issues/16378#issuecomment-396479887, or mute
the thread
https://github.com/notifications/unsubscribe-auth/AQC1-Ec2z4arHtiWIj34b8L8F9lGEopVks5t71_JgaJpZM4UgKGY
.

[samdark]

https://github.com/yiisoft/yii2/blob/master/docs/internals/git-workflow.md

[sdlins]

I will try to test it on MSSQL next week, when I have a gap at work. If it works like it seems to do, I can make a PR.

[rlcarnevale]

Ok,

Thank you.

Raffaele

Em sáb, 18 de mai de 2019 às 23:10, Sidney Lins [e-mail]
escreveu:

I will try to test it on MSSQL next week, when I have a gap at work. If it
works like it seems to do, I can make a PR.

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
https://github.com/yiisoft/yii2/issues/16378?email_source=notifications&email_token=AEALL6GXKX3RU2CHBQY4XTLPWCZJNA5CNFSM4FEAUGMKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODVWZKAQ#issuecomment-493720834,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AEALL6G2PKIGC4YQPGEB65DPWCZJNANCNFSM4FEAUGMA
.

[sdlins]

I tested your sql code against many tables and it works as expected. Next days I will prepare a PR.

[rlcarnevale]

Ok, Thanks.

Em sex, 24 de mai de 2019 às 19:56, Sidney Lins [e-mail]
escreveu:

I tested your sql code against many tables and it works as expected. Next
days I will prepare a PR.

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
https://github.com/yiisoft/yii2/issues/16378?email_source=notifications&email_token=AEALL6HARP3VAJK6SDGMXYLPXBXBHA5CNFSM4FEAUGMKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODWGXBMI#issuecomment-495808689,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AEALL6HIAYUEYTUOOZZTCKTPXBXBHANCNFSM4FEAUGMA
.