relational info actions on information shema

[tuccy]

Hi all, im trying acces relational database information, but provided information using framework functions are pour, i cant get information about relation types in action reference update or delete, these information is provided on information shema tables i can acces it ussing free querys but i think that these must be provided by the framework on each especific shema engine, but i need to know if these enanced is usefull to be implemented before of work on these.

these can be a good enanched for gii proyect generating models with rules in base of relation about actions delete or update.

Sorry for my english is really low,

please let me know if these isnt the correct section to report these, Regards.

[tuccy]

these is mysql requested info example:

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN ORDER BY INNODB_SYS_FOREIGN.TYPE

Type value is related to relation type

update=>restrict delete=>cascade 4 update=>restrict delete=>cascade 5 update=>restrict delete=>restrict 0 update=>restrict delete=>cascade 1

[cebe]

For what kind of use case may this information be useful?

[tuccy]

is usefull for code generators on relational database example gii generate actions delete update anyway but delete cant trigger sql dump if have relation delete or update restrict, i think that control these is really important.

this can be controlled on gii templates but i think that these information must be provided be the each especific database shema.php

example control on generator model:

if(!empty($relations) && 'mysql'==Yii::$app->db->driverName){ //print_r(Yii::$app->db); $query = (new Query()) ->select('TYPE, FOR_NAME') ->from('INFORMATION_SCHEMA.INNODB_SYS_FOREIGN') ->where([ 'REF_NAME' => end(explode('=',Yii::$app->db->dsn)).'/'.$tableName, //'tableNameIn' => 'fillme' ]); $rows = $query->all(); foreach ($rows as $row){ //control hear validation scenarios behaivours or something to control errors

but these info depending much of database engine these isnt good way to control.

[qiangxue]

I don't think we should support it in the core. This is very DBMS dependent, and it has no usage at all in the core code and official extensions. This should be better developed as a user extension.

[tuccy]

Of curse these is really DBMS depend these is reasson of that relational information must be provided by the framework for each especific shema the shemas.php using diferents working modes for each db engine.

the previous example is incorrect way to check because the framework dont provide relational info needed.

mi sugestion is provide them implementing function on depend: yii\db\cubrid\Schema yii\db\mssql\Schema yii\db\mysql\Schema yii\db\oci\Schema yii\db\pgsql\Schema yii\db\sqlite\Schema

but these feature will not be able in all engines example mysql/mysam, mysqlite ... not relational but can return false if these is the case. pgsql have innodb as same mode that mysql, mmsql>= 2005 ....

Regards, Let me know for any concerns.

[tuccy]

Example (not tested)

public function getConstraintsData($tableName){ $result==new Array(); $query = (new Query()) ->select('ID') ->from('INFORMATION_SCHEMA.INNODB_SYS_FOREIGN') ->where([ 'REF_NAME' => end(explode('=',Yii::$app->db->dsn)).'/'.$tableName, ]); $rows = $query->all(); foreach ($rows as $row){ $query = (new Query()) ->select('*') ->from('INFORMATION_SCHEMA.KEY_COLUMN_USAGE') ->where([ 'CONSTRAINT_NAME' => end(explode('/',$row['ID'])), ]); $constrainDat = $query->all(); array_push($result, $constrainDat[0]); } return (!empty($result)) ? $result : false; }

these maybe work on mssql pgsql and mysql (need to add validation check for innodb) cubrid maybe can be adapted and another engines not relational database can return false.