Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 1 |
|
0.00% |
0 / 9 |
CRAP | |
0.00% |
0 / 113 |
| SqlInfo | |
0.00% |
0 / 1 |
|
0.00% |
0 / 9 |
2970.00 | |
0.00% |
0 / 113 |
| __construct | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 2 |
|||
| mysqli2Type | |
0.00% |
0 / 1 |
110.00 | |
0.00% |
0 / 14 |
|||
| numericField | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 14 |
|||
| getEnumSet | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 8 |
|||
| result2fields | |
0.00% |
0 / 1 |
462.00 | |
0.00% |
0 / 49 |
|||
| charsetnr2chars | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 5 |
|||
| type2Text | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 2 |
|||
| flags2Array | |
0.00% |
0 / 1 |
12.00 | |
0.00% |
0 / 6 |
|||
| constantsFill | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 13 |
|||
| <?php | |
| namespace ia\Sql\Mysql; | |
| /** | |
| * SqlInfo | |
| * Provides meta Data and information on Sql statements and database | |
| * | |
| * @package Sql | |
| * @author Informatica Asociada | |
| * @copyright 2015 | |
| * @version 1.0.1 | |
| * @require php 5.3 | |
| */ | |
| /* | |
| ayear4 year(4) COMMENT '1901 to 2155 o 0000 for invalid', | |
| create table vacia( | |
| abigint1 bigint, | |
| abigint1u bigint unsigned, | |
| aint1 int, | |
| aint1u int unsigned not null, | |
| amediumint1 mediumint, | |
| amediumint1u mediumint unsigned, | |
| asmallint1 smallint, | |
| asmallint1u smallint unsigned not null, | |
| atinyint1 tinyint, | |
| atinyint1u tinyint unsigned not null, | |
| adecimal5_2 DECIMAL(5,2) not null default '0.00' COMMENT ' -999.99 to 999.99', | |
| adecimal5_2u DECIMAL(5,2) unsigned not null default '0.00', | |
| anumeric5_2 NUMERIC(5,2) not null default '0.00', | |
| anumeric5_2u NUMERIC(5,2) unsigned not null default '0.00', | |
| anumeric3_0 NUMERIC(3,0) not null default '0', | |
| anumeric3_0u NUMERIC(3,0) unsigned not null default '0', | |
| anumeric3 NUMERIC(3) not null default '0', | |
| anumeric3u NUMERIC(3) unsigned not null default '0', | |
| afloat7_4 FLOAT(7,4) not null default '0.00' COMMENT 'ie -999.9999', | |
| afloat7_4u FLOAT(7,4) unsigned not null default '0.00', | |
| adouble7_4 DOUBLE(7,4) not null default '0.00' COMMENT 'ie -999.9999', | |
| adouble7_4u DOUBLE(7,4) unsigned not null default '0.00', | |
| areal7_4 REAL(7,4) not null default '0.00' COMMENT 'ie -999.9999', | |
| areal7_4u REAL(7,4) unsigned not null default '0.00', | |
| adouble3_0 DOUBLE(3,0) not null default '0' COMMENT ' no decs', | |
| adouble3_0u DOUBLE(3,0) unsigned not null default '0', | |
| abti1 bit(1), | |
| abti16 bit(16), | |
| adate date COMMENT '1000-01-01 to 9999-12-31 ', | |
| adatetime datetime COMMENT '1000-01-01 to 9999-12-31 fractional seconds part in up to microseconds (6 digits) as of MySQL 5.6.4 prev ignored', | |
| atimestamp timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC fractional seconds part in up to microseconds (6 digits) as of MySQL 5.6.4 prev ignored', | |
| achar4 char(4), | |
| avarchar129 varchar(129), | |
| abinary12 binary(12), | |
| avarbinary12 varbinary(12), | |
| atinytext tinytext, | |
| atext text, | |
| amediumtext mediumtext, | |
| alongtext longtext, | |
| atinyblob tinyblob, | |
| ablob blob, | |
| amediumblob mediumblob, | |
| alongblob longblob | |
| ) | |
| --geo geometry, pt point, ln linestring, poly polygon, | |
| --geo_coll geometrycollection, pt_multi multipoint, ln_multi multilinestring, poly_multi multipolygon | |
| --ayear2 year(2) COMMENT '1 to 99, depreciated, creates year4!', | |
| */ | |
| namespace ia\Sql\Mysql; | |
| /** | |
| * SqlInfo | |
| * Provides column/field metadata on executed mysqli resultset | |
| * | |
| * @author Informatica Asociada | |
| * @copyright 2015 | |
| * @version 1.0.1 | |
| */ | |
| class SqlInfo { | |
| protected $flags; | |
| protected $types; | |
| /** @var IaMysqli $db IaMysqli */ | |
| protected $db; | |
| protected $apiType2mysql = array( | |
| 'bigint'=> 'longlong', | |
| 'int'=> 'long', | |
| 'mediumint'=> 'int24', | |
| 'smallint'=> 'short', | |
| 'tinyint'=> 'tinyint', | |
| 'year'=> 'year', | |
| 'numeric'=> 'decimal', | |
| ); | |
| // https://dev.mysql.com/doc/refman/5.5/en/integer-types.html | |
| protected $dataTypesUnsigned = array( | |
| 'tinyint'=>array('max'=>255), | |
| 'smallint'=>array('max'=>65535), | |
| 'mediumint'=>array('max'=>16777215), | |
| 'int'=>array('max'=>4294967295), | |
| 'bigint'=>array('max'=>'18446744073709551615'), | |
| 'year' => array('max'=>2155,'min'=>1901), | |
| ); | |
| protected $dataTypesTrans = array( | |
| 'tinyint'=>array('type'=>'tinyint', 'numeric'=>true,'bytes'=>1, 'min'=>-128, 'max'=>127,), | |
| 'decimal' => array( 'type'=>'decimal', 'numeric'=>true, 'floatingPoint'=>true, | |
| ), | |
| 'char' => array( 'type'=>'char', | |
| ), | |
| 'short' => array( 'type'=>'smallint', 'numeric'=>true,'bytes'=>2, 'min'=>-32768, 'max'=>32767, | |
| ), | |
| 'long' => array( 'type'=>'int', 'numeric'=>true,'bytes'=>4, 'min'=>-2147483648, 'max'=>2147483647, | |
| ), | |
| 'float' => array( 'type'=>'float', 'numeric'=>true, 'floatingPoint'=>true, | |
| ), | |
| 'double' => array( 'type'=>'double', 'numeric'=>true, 'floatingPoint'=>true, | |
| ), | |
| 'null' => array( 'type'=>'null', | |
| ), | |
| 'timestamp' => array( 'type'=>'timestamp', | |
| ), | |
| 'longlong' => array( 'type'=>'bigint', 'numeric'=>true,'bytes'=>8, 'min'=>'-9223372036854775808', 'max'=>'9223372036854775807', | |
| ), | |
| 'int24' => array( 'type'=>'mediumint', 'numeric'=>true,'bytes'=>3, 'min'=>-8388608, 'max'=>8388607, | |
| ), | |
| 'date' => array( 'type'=>'date', | |
| ), | |
| 'time' => array( 'type'=>'time', | |
| ), | |
| 'datetime' => array( 'type'=>'datetime', | |
| ), | |
| 'year' => array( 'type'=>'year', 'numeric'=>true, | |
| ), | |
| 'newdate' => array( 'type'=>'date', | |
| ), | |
| 'interval' => array( 'type'=>'interval', | |
| ), | |
| 'set' => array( 'type'=>'set', | |
| ), | |
| 'tiny_blob' => array( 'type'=>'tinytext', 'blob'=>true, | |
| ), | |
| 'medium_blob' => array( 'type'=>'mediumtext', 'blob'=>true, | |
| ), | |
| 'long_blob' => array( 'type'=>'longtext', 'blob'=>true, | |
| ), | |
| 'blob' => array( 'type'=>'text', 'blob'=>true, | |
| ), | |
| 'var_string' => array( 'type'=>'varchar', | |
| ), | |
| 'string' => array( 'type'=>'binary', | |
| ), | |
| 'geometry' => array( 'type'=>'geometry', | |
| ), | |
| 'newdecimal' => array( 'type'=>'decimal', 'numeric'=>true, 'floatingPoint'=>true, | |
| ), | |
| 'bit' => array( 'type'=>'bit', | |
| ), | |
| ); | |
| /** | |
| * SqlInfo constructor. | |
| * @param IaMysqli $db | |
| */ | |
| public function __construct($db) { | |
| $this->db = $db; | |
| } | |
| /** | |
| * get standard Data type from mysqli reported Data type | |
| * | |
| * @param object $f | |
| * @param $asMysqli | |
| * @param $binary | |
| * @return mixed|string | |
| */ | |
| protected function mysqli2Type($f, $asMysqli, $binary) { | |
| //if(($f->flags & MYSQLI_GROUP_FLAG) && $asMysqli === 'CHAR') | |
| if($f->charsetnr === 63 && $asMysqli === 'CHAR') { | |
| return 'tinyint'; | |
| } | |
| if(($f->flags & MYSQLI_ENUM_FLAG) === MYSQLI_ENUM_FLAG) { | |
| return 'enum'; | |
| } | |
| if(($f->flags & MYSQLI_SET_FLAG) === MYSQLI_SET_FLAG) { | |
| return 'set'; | |
| } | |
| if(!$binary && $asMysqli === 'STRING') { | |
| return 'char'; | |
| } | |
| if($binary && $asMysqli === 'VAR_STRING') { | |
| return "varbinary"; | |
| } | |
| $type = strtolower($asMysqli); | |
| if(array_key_exists($type, $this->apiType2mysql)) { | |
| return $this->apiType2mysql[$type]; | |
| } | |
| return $type; | |
| } | |
| /** | |
| * Numeric field properties | |
| * | |
| * @param $f | |
| * @param array $field | |
| * @return void | |
| */ | |
| protected function numericField($f, &$field){ | |
| $field['integers'] = $f->length - $f->decimals; | |
| $field['decimals'] = $f->decimals; | |
| if(isset($field['floatingPoint'])) { | |
| $decs = $f->decimals == 0 ? '' : '.'.str_repeat('9',$f->decimals); | |
| $field['min'] = '-'.str_repeat('9',$field['integers']).$decs; | |
| $field['max'] = str_repeat('9',$field['integers']).$decs; | |
| } | |
| if(($f->flags & MYSQLI_UNSIGNED_FLAG) === MYSQLI_UNSIGNED_FLAG) { | |
| $field['unsigned'] = true; | |
| $field['min'] = 0; | |
| if(isset($this->dataTypesUnsigned[$field['type']])) { | |
| $field = array_merge($field,$this->dataTypesUnsigned[$field['type']]); | |
| } | |
| $field['max_chars'] = strlen($field['max']); | |
| } else { | |
| $field['max_chars'] = strlen($field['min']); | |
| } | |
| } | |
| /** | |
| * Return an array with enum/set columns as their key and an array of its values | |
| * | |
| * @param string $table table from where to extract enums & sets | |
| * @return array ['fieldName'=>['value1', 'value2', ...]] | |
| * @throws IacSqlException | |
| */ | |
| public function getEnumSet($table) { | |
| $enums = array(); | |
| foreach($this->db->selectArrayIndex("SHOW COLUMNS FROM ".$table) as $col) { //@TODO fieldit table | |
| $type = $col['Type']; | |
| if(strpos($type,'enum') === 0 ) { | |
| $enums[$col['Field']] = str_getcsv(substr($col['Type'], 5, -1), ',', "'", "'"); | |
| } elseif(strpos($type,'set') === 0 ) { | |
| $enums[$col['Field']] = str_getcsv(substr($col['Type'], 4, -1), ',', "'", "'"); | |
| } | |
| } | |
| return $enums; | |
| } | |
| /** | |
| * Returns metadata for resultset $result | |
| * | |
| * @param object $result mysqli resultset | |
| * @return array associative array indexed by field with field's metadata | |
| * @throws IacSqlException | |
| */ | |
| public function result2fields($result) { | |
| $enumSet = array(); | |
| $primary_keys = 0; | |
| $fields = array(); | |
| $finfo = $result->fetch_fields(); | |
| foreach ($finfo as $f) { | |
| $binary = ($f->flags & MYSQLI_BINARY_FLAG) === MYSQLI_BINARY_FLAG; | |
| $asMysqli = $this->type2Text($f->type); | |
| $type = $this->mysqli2Type($f, $asMysqli, $binary); | |
| $field = array( | |
| 'type' => strtolower($type), | |
| 'typeMysqli' => $asMysqli, | |
| 'flags' => $this->flags2Array($f->flags), | |
| 'metaData' => $f, | |
| //'max_chars' => $f->length, // ? | |
| ); | |
| // mark primary / autonumeric | |
| if(($f->flags & MYSQLI_PRI_KEY_FLAG) === MYSQLI_PRI_KEY_FLAG) { | |
| $primary_keys++; | |
| $field['pri_key'] = true; | |
| $field['pri_key_multiple'] = $primary_keys > 1; // on multi coolumn primar key the first column will be fixed after this loop | |
| $field['auto_increment'] = ($f->flags & MYSQLI_AUTO_INCREMENT_FLAG) === MYSQLI_AUTO_INCREMENT_FLAG; | |
| } | |
| $field['null'] = !( ($f->flags & MYSQLI_NOT_NULL_FLAG) === MYSQLI_NOT_NULL_FLAG ); | |
| $field['required'] = !$field['null']; | |
| if(isset($this->dataTypesTrans[$type])) { | |
| $field = array_merge($field,$this->dataTypesTrans[$type]); | |
| } | |
| $type = $field['type']; | |
| if($type === 'char' || $type === 'varchar' || $type === 'binary' || $type === 'varbinary') { | |
| $field['max_chars'] = $this->charsetnr2chars($f->length,$f->charsetnr); | |
| $field['max_multibytes'] = $f->length; | |
| } | |
| elseif(isset($field['numeric'])) { | |
| $this->numericField($f, $field); | |
| } | |
| elseif(isset($field['blob'])) { | |
| if($binary) { | |
| $field['type'] = str_replace('text','blob',$field['type']); | |
| } | |
| if($f->length > 0) { | |
| $field['max_multibytes'] = $f->length; | |
| if(!$binary) { | |
| $field['max_chars'] = $this->charsetnr2chars($f->length,$f->charsetnr); | |
| } | |
| } | |
| } | |
| elseif($type === 'bit') { | |
| $field['min'] = 0; | |
| $field['max'] = $f->length; //TOOD //FIXME | |
| $field['max_chars'] = $f->length; | |
| } | |
| elseif($type === 'enum' || $type === 'set') { | |
| if(!isset($enumSet[$f->orgtable])) { | |
| $enumSet[$f->orgtable] = $this->getEnumSet($f->orgtable); | |
| } | |
| $field['enums'] = isset($enumSet[$f->orgtable][$f->orgname]) ? $enumSet[$f->orgtable][$f->orgname] : array(); | |
| } | |
| $fields[$f->name] = $field; | |
| } | |
| // on multiple column primary key, mark the first column as multiple | |
| if($primary_keys > 1) { | |
| foreach($fields as &$field) { | |
| if(isset($field['pri_key'])) { | |
| $field['pri_key_multiple'] = true; | |
| break; | |
| } | |
| } | |
| } | |
| return $fields; | |
| } | |
| /** | |
| * Returns length in characters given the length in bytes, for the given $charsetnr | |
| * | |
| * @param int $lengthInBytes length in bytes | |
| * @param int $charsetnr mysql characterset code | |
| * @return int | |
| */ | |
| protected function charsetnr2chars($lengthInBytes,$charsetnr) { | |
| if($charsetnr == 33) // utf8 | |
| return $lengthInBytes/3; | |
| if($charsetnr == 8) // latin1 | |
| return $lengthInBytes; | |
| //TODO SELECT * FROM information_schema.collations; | |
| return $lengthInBytes; | |
| } | |
| /** | |
| * return mysqli type for the field | |
| * | |
| * @param integer $type_id from $result->fetch_field()->type or $result->fetch_field_direct($i)->type | |
| * @return string mysql type for id | |
| */ | |
| public function type2Text($type_id) { | |
| $this->constantsFill(); // fill constants if not done yet | |
| return array_key_exists($type_id, $this->types) ? $this->types[$type_id] :null; | |
| } | |
| /** | |
| * Get mysqli flags | |
| * | |
| * @param integer $flags_num from $result->fetch_field()->flags or $result->fetch_field_direct($i)->flags | |
| * @return array associative array with flag names indexed by mysqli constant | |
| */ | |
| public function flags2Array($flags_num) { | |
| // fill constants if not done yet | |
| //if(!isset($this->flags)) | |
| $this->constantsFill(); | |
| $result = array(); | |
| foreach($this->flags as $n => $t) | |
| if(($flags_num & $n) === $n) { | |
| $result[$n] = $t; | |
| } | |
| return $result; | |
| } | |
| /** | |
| * Fills mysqli's type names into $this->types and flag names into $this>$flags | |
| * | |
| * @return void | |
| * @see http://php.net/manual/en/mysqli-result.fetch-fields.php | |
| * @see http://php.net/manual/en/mysqli.constants.php | |
| */ | |
| protected function constantsFill() { | |
| if(isset($this->types)) { | |
| return; // constants have been filled | |
| } | |
| $flags = array(); | |
| $types = array(); | |
| $constants = get_defined_constants(true); | |
| foreach($constants['mysqli'] as $c => $n) | |
| if(preg_match('/^MYSQLI_TYPE_(.*)/', $c, $m)) { //TODO save preg? | |
| $types[$n] = $m[1]; | |
| } | |
| elseif(preg_match('/MYSQLI_(.*)_FLAG$/', $c, $mFlag)) { //TODO save preg? | |
| $flags[$n] = $mFlag[1]; | |
| } | |
| $this->types = $types; | |
| $this->flags = $flags; | |
| } | |
| } |