Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 1 |
|
0.00% |
0 / 12 |
CRAP | |
0.00% |
0 / 126 |
| QueryInfo | |
0.00% |
0 / 1 |
|
0.00% |
0 / 12 |
6162.00 | |
0.00% |
0 / 126 |
| __construct | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 2 |
|||
| fieldName2Label | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 4 |
|||
| infoForTable | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 2 |
|||
| infoForQuery | |
0.00% |
0 / 1 |
182.00 | |
0.00% |
0 / 26 |
|||
| fieldNames | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 4 |
|||
| valuesFromRequest | |
0.00% |
0 / 1 |
462.00 | |
0.00% |
0 / 27 |
|||
| valuesValid | |
0.00% |
0 / 1 |
306.00 | |
0.00% |
0 / 27 |
|||
| uniqueIndexesValid | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 14 |
|||
| primaryKeyWhere | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 7 |
|||
| value_get | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 5 |
|||
| checkMaxMin | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 7 |
|||
| expandParentesis | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 1 |
|||
| <?php | |
| /** @noinspection SqlNoDataSourceInspection */ | |
| /** @noinspection SqlResolve */ | |
| namespace ia\Sql\Mysql; | |
| use ia\Util\Str; | |
| class QueryInfo { | |
| public $fields = array(); | |
| public $tables = array(); | |
| public $orgname2FieldName = array(); | |
| public $primaryKey = array(); | |
| public $uniqueIndexes = array(); | |
| public $msg_error = array(); | |
| /** @var IaMysqli $db */ | |
| protected $db; | |
| public function __construct($db) { | |
| $this->db = $db; | |
| } | |
| public function fieldName2Label($fieldName) { | |
| $ret = mb_convert_case(str_replace('_', ' ', $fieldName), MB_CASE_TITLE ); | |
| if(!empty($ret)) | |
| return $ret; | |
| return ucwords(str_replace('_', ' ', $fieldName)); | |
| } | |
| public function infoForTable($table) { | |
| $this->infoForQuery("SELECT * FROM $table LIMIT 1"); | |
| } | |
| public function infoForQuery($query) { | |
| $explain = array(); | |
| $indexesQueried = array(); | |
| $this->db->metaDataOn = true; | |
| $this->db->singleton($query); | |
| $this->fields = $this->db->metaData_get(); | |
| foreach($this->fields as $fieldName => &$f) { | |
| $orgname = $f['orgname'] = $f['metaData']->orgname; | |
| $orgtable = $f['orgtable'] = $f['metaData']->orgtable; | |
| $this->orgname2FieldName[$orgname] = $fieldName; | |
| $this->tables[$orgtable][$orgname] = $fieldName; | |
| if(!empty($orgname) && !empty($orgtable)) { | |
| // primary_key | |
| if(!empty($f['pri_key'])) { | |
| $this->primaryKey[$orgtable][$orgname] = $fieldName; | |
| } | |
| if(empty($explain[$orgtable])) { | |
| foreach($this->db->selectArrayIndex("explain $orgtable") as $e) { | |
| $explain[$orgtable][$e['Field']] = $e; | |
| } | |
| } | |
| // valid values for enum/set | |
| if(strpos($f['type'],'enum') !== FALSE || strpos($f['type'],'set') !== FALSE ) { | |
| $f['valid_values'] = $this->expandParentesis($explain[$orgtable][$orgname]['Type']); | |
| } | |
| // get dafaults | |
| $f['Default'] = $explain[$orgtable][$orgname]['Default']; | |
| // get unique indexes | |
| if(empty($indexesQueried[$orgtable])) { | |
| $indexesQueried[$orgtable] = true; | |
| foreach($this->db->selectArrayIndex("show indexes from $orgtable") as $indexPart) { | |
| if($indexPart['Non_unique'] == 0 && $indexPart['Key_name'] !== 'PRIMARY') { | |
| $this->uniqueIndexes[$orgtable][$indexPart['Key_name']][] = $indexPart['Column_name']; | |
| } | |
| } | |
| } | |
| } | |
| } | |
| $this->db->metaDataOn = false; | |
| } | |
| public function fieldNames() { | |
| $fieldNames = array(); | |
| foreach($this->fields as $fieldName => $f) { | |
| $fieldNames[$fieldName] = array('orgname'=>$f['orgname'],'orgtable'=>$f['orgtable']); | |
| } | |
| return $fieldNames; | |
| } | |
| public function valuesFromRequest($onlyFields = array(),$excludeFields = array()) { | |
| $values = array(); | |
| if(empty($onlyFields)) { | |
| $onlyFields = array_keys($this->fields); | |
| } | |
| if(!empty($excludeFields)) { | |
| foreach($excludeFields as $fieldRemove) { | |
| unset($onlyFields[$fieldRemove]); | |
| } | |
| } | |
| foreach($onlyFields as $fieldName) { | |
| if(isset($this->fields[$fieldName])) { | |
| $f = $this->fields[$fieldName]; | |
| if(isset($_REQUEST[$fieldName])) { | |
| if($f['type'] !== 'text' && $f['type'] !== 'blob') { | |
| $val = Str::strim($_REQUEST[$fieldName]); | |
| } else { | |
| $val = $_REQUEST[$fieldName]; | |
| } | |
| if(!empty($f['null']) && $val === '') { | |
| $val = isset($f['Default']) ? $f['Default'] : null; | |
| }elseif($val === '' && isset($f['Default']) && $f['Default'] !== $val) { | |
| $val = $f['Default']; | |
| } | |
| if(!empty($f['numeric'])) { | |
| $val = Str::strim(str_replace( array('$',',','"',"'"),'', $val ) ); | |
| } | |
| $values[$fieldName] = $val; | |
| } else { | |
| // fieldname not in request, if nullable set it to null, if has default to its default | |
| if(!empty($f['null'])) { | |
| $values[$fieldName] = isset($f['Default']) ? $f['Default'] : null; | |
| }elseif(isset($f['Default']) && $f['Default'] !== null) { | |
| $values[$fieldName] = $f['Default']; | |
| } | |
| } | |
| } elseif(isset($_REQUEST[$fieldName])) { | |
| $values[$fieldName] = Str::strim($_REQUEST[$fieldName]); | |
| } | |
| } | |
| return $values; | |
| } | |
| public function valuesValid($values) { | |
| $this->msg_error = array(); | |
| foreach($values as $fieldName => $val) { | |
| if(isset($this->fields[$fieldName])) { | |
| $f = $this->fields[$fieldName]; | |
| if(!empty($f['required']) && ($val === '' || $val === null) ) { | |
| $this->msg_error[$fieldName] = $this->fieldName2Label($fieldName)." es dato requerido"; | |
| continue; | |
| } | |
| if(!empty($f['null']) && $val === null) { | |
| $this->msg_error[$fieldName] = $this->fieldName2Label($fieldName)." no puede ser nulo"; | |
| continue; | |
| } | |
| if(!empty($f['valid_values']) && strpos($f['type'],'set') === FALSE) { | |
| $foundValid = false; | |
| foreach($f['valid_values'] as $valid) { | |
| if(strcasecmp($val, $valid) === 0) { | |
| $foundValid = true; | |
| continue; | |
| } | |
| if(!$foundValid) { | |
| $this->msg_error[$fieldName] = $this->fieldName2Label($fieldName)." valor inválido".(count($f['valid_values']) < 8 ? " Valores válidos: ".implode(',', $f['valid_values']) : ''); | |
| continue; | |
| } | |
| } | |
| } | |
| //TODO valid set | |
| //TODO valid date | |
| //TODO valid datetime | |
| //TODO valid timestamp | |
| if(!empty($f['numeric'])) { | |
| $val = Str::strim(str_replace( array('$',',','"',"'"),'', $val ) ); | |
| if(!is_numeric($val)) { | |
| $this->msg_error[$fieldName] = $this->fieldName2Label($fieldName)." número inválido"; | |
| continue; | |
| } | |
| } | |
| if(!$this->checkMaxMin($fieldName, $val, $f)) { | |
| continue; | |
| } | |
| //TODO max length? | |
| } | |
| } | |
| return empty($this->msg_error); | |
| } | |
| public function uniqueIndexesValid($tableName, $values) { | |
| if(!empty($this->uniqueIndexes) && !empty($this->uniqueIndexes[$tableName])) { | |
| $primaryKeyWhere = $this->primaryKeyWhere($tableName, $values); | |
| foreach($this->uniqueIndexes[$tableName] as $indexName => $indexColumns) { | |
| $where = array(); | |
| foreach($indexColumns as $column) { | |
| //TODO missing value for unique index column | |
| $val = $this->value_get($values, $column, null); | |
| if($val !== null) { | |
| $where[] = $column.'='.Str::strit($val); | |
| } else { | |
| $where[] = $column.' IS NULL'; | |
| } | |
| } | |
| $sql = "SELECT COUNT(*) FROM $tableName WHERE (".implode(' AND ', $where).") AND NOT ($primaryKeyWhere)"; | |
| if($this->db->single_read($sql, 0) == 0) { | |
| $this->msg_error[] = implode(', ', $indexColumns)." debe ser único, ya existe otro registro así"; | |
| return false; | |
| } | |
| } | |
| } | |
| return true; | |
| } | |
| public function primaryKeyWhere($tableName, $values) { | |
| $where = array(); | |
| foreach($this->primaryKey[$tableName] as $orgname => $fieldName) { | |
| //TODO missing value for unique index column | |
| $val = $this->value_get($values, $orgname, $fieldName); | |
| if($val !== null) { | |
| $where[] = $orgname.'='.Str::strit($val); | |
| } else { | |
| $where[] = $orgname.' IS NULL'; | |
| } | |
| } | |
| return ' '.implode(' AND ', $where).' '; | |
| } | |
| protected function value_get($values, $orgname=null, $fieldName=null) { | |
| if($fieldName !== null && array_key_exists($fieldName, $values)) { | |
| return $values[$fieldName]; | |
| } | |
| if($orgname !== null && array_key_exists($orgname, $values)) { | |
| return $values[$orgname]; | |
| } | |
| //TODO needed field not in values | |
| return null; | |
| } | |
| protected function checkMaxMin($fieldName, $val, $f) { | |
| if(isset($f['min']) && $val < $f['min'] ) { | |
| $this->msg_error[$fieldName] = $this->fieldName2Label($fieldName)." no puede ser menor que ".$f['min']; | |
| return false; | |
| } | |
| if(isset($f['min']) && $val > $f['min'] ) { | |
| $this->msg_error[$fieldName] = $this->fieldName2Label($fieldName)." no puede ser mayor que ".$f['max']; | |
| return false; | |
| } | |
| return true; | |
| } | |
| protected function expandParentesis($type) { | |
| return str_getcsv(substr(substr(strstr($type,'(') ,1), 0, -1), ',', "'"); | |
| } | |
| } |