libraries/TypesMySQL.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * SQL data types definition
   5  *
   6  * @package PhpMyAdmin
   7  */
   8 namespace PMA\libraries;
   9
  10 /**
  11  * Class holding type definitions for MySQL.
  12  *
  13  * @package PhpMyAdmin
  14  */
  15 class TypesMySQL extends Types
  16 {
  17     /**
  18      * Returns the data type description.
  19      *
  20      * @param string $type The data type to get a description.
  21      *
  22      * @return string
  23      *
  24      */
  25     public function getTypeDescription($type)
  26     {
  27         $type = mb_strtoupper($type);
  28         switch ($type) {
  29         case 'TINYINT':
  30             return __(
  31                 'A 1-byte integer, signed range is -128 to 127, unsigned range is ' .
  32                 '0 to 255'
  33             );
  34         case 'SMALLINT':
  35             return __(
  36                 'A 2-byte integer, signed range is -32,768 to 32,767, unsigned ' .
  37                 'range is 0 to 65,535'
  38             );
  39         case 'MEDIUMINT':
  40             return __(
  41                 'A 3-byte integer, signed range is -8,388,608 to 8,388,607, ' .
  42                 'unsigned range is 0 to 16,777,215'
  43             );
  44         case 'INT':
  45             return __(
  46                 'A 4-byte integer, signed range is ' .
  47                 '-2,147,483,648 to 2,147,483,647, unsigned range is 0 to ' .
  48                 '4,294,967,295'
  49             );
  50         case 'BIGINT':
  51             return __(
  52                 'An 8-byte integer, signed range is -9,223,372,036,854,775,808 ' .
  53                 'to 9,223,372,036,854,775,807, unsigned range is 0 to ' .
  54                 '18,446,744,073,709,551,615'
  55             );
  56         case 'DECIMAL':
  57             return __(
  58                 'A fixed-point number (M, D) - the maximum number of digits (M) ' .
  59                 'is 65 (default 10), the maximum number of decimals (D) is 30 ' .
  60                 '(default 0)'
  61             );
  62         case 'FLOAT':
  63             return __(
  64                 'A small floating-point number, allowable values are ' .
  65                 '-3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to ' .
  66                 '3.402823466E+38'
  67             );
  68         case 'DOUBLE':
  69             return __(
  70                 'A double-precision floating-point number, allowable values are ' .
  71                 '-1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and ' .
  72                 '2.2250738585072014E-308 to 1.7976931348623157E+308'
  73             );
  74         case 'REAL':
  75             return __(
  76                 'Synonym for DOUBLE (exception: in REAL_AS_FLOAT SQL mode it is ' .
  77                 'a synonym for FLOAT)'
  78             );
  79         case 'BIT':
  80             return __(
  81                 'A bit-field type (M), storing M of bits per value (default is 1, ' .
  82                 'maximum is 64)'
  83             );
  84         case 'BOOLEAN':
  85             return __(
  86                 'A synonym for TINYINT(1), a value of zero is considered false, ' .
  87                 'nonzero values are considered true'
  88             );
  89         case 'SERIAL':
  90             return __('An alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE');
  91         case 'DATE':
  92             return sprintf(
  93                 __('A date, supported range is %1$s to %2$s'), '1000-01-01',
  94                 '9999-12-31'
  95             );
  96         case 'DATETIME':
  97             return sprintf(
  98                 __('A date and time combination, supported range is %1$s to %2$s'),
  99                 '1000-01-01 00:00:00', '9999-12-31 23:59:59'
 100             );
 101         case 'TIMESTAMP':
 102             return __(
 103                 'A timestamp, range is 1970-01-01 00:00:01 UTC to 2038-01-09 ' .
 104                 '03:14:07 UTC, stored as the number of seconds since the epoch ' .
 105                 '(1970-01-01 00:00:00 UTC)'
 106             );
 107         case 'TIME':
 108             return sprintf(
 109                 __('A time, range is %1$s to %2$s'), '-838:59:59', '838:59:59'
 110             );
 111         case 'YEAR':
 112             return __(
 113                 "A year in four-digit (4, default) or two-digit (2) format, the " .
 114                 "allowable values are 70 (1970) to 69 (2069) or 1901 to 2155 and " .
 115                 "0000"
 116             );
 117         case 'CHAR':
 118             return __(
 119                 'A fixed-length (0-255, default 1) string that is always ' .
 120                 'right-padded with spaces to the specified length when stored'
 121             );
 122         case 'VARCHAR':
 123             return sprintf(
 124                 __(
 125                     'A variable-length (%s) string, the effective maximum length ' .
 126                     'is subject to the maximum row size'
 127                 ), '0-65,535'
 128             );
 129         case 'TINYTEXT':
 130             return __(
 131                 'A TEXT column with a maximum length of 255 (2^8 - 1) characters, ' .
 132                 'stored with a one-byte prefix indicating the length of the value ' .
 133                 'in bytes'
 134             );
 135         case 'TEXT':
 136             return __(
 137                 'A TEXT column with a maximum length of 65,535 (2^16 - 1) ' .
 138                 'characters, stored with a two-byte prefix indicating the length ' .
 139                 'of the value in bytes'
 140             );
 141         case 'MEDIUMTEXT':
 142             return __(
 143                 'A TEXT column with a maximum length of 16,777,215 (2^24 - 1) ' .
 144                 'characters, stored with a three-byte prefix indicating the ' .
 145                 'length of the value in bytes'
 146             );
 147         case 'LONGTEXT':
 148             return __(
 149                 'A TEXT column with a maximum length of 4,294,967,295 or 4GiB ' .
 150                 '(2^32 - 1) characters, stored with a four-byte prefix indicating ' .
 151                 'the length of the value in bytes'
 152             );
 153         case 'BINARY':
 154             return __(
 155                 'Similar to the CHAR type, but stores binary byte strings rather ' .
 156                 'than non-binary character strings'
 157             );
 158         case 'VARBINARY':
 159             return __(
 160                 'Similar to the VARCHAR type, but stores binary byte strings ' .
 161                 'rather than non-binary character strings'
 162             );
 163         case 'TINYBLOB':
 164             return __(
 165                 'A BLOB column with a maximum length of 255 (2^8 - 1) bytes, ' .
 166                 'stored with a one-byte prefix indicating the length of the value'
 167             );
 168         case 'MEDIUMBLOB':
 169             return __(
 170                 'A BLOB column with a maximum length of 16,777,215 (2^24 - 1) ' .
 171                 'bytes, stored with a three-byte prefix indicating the length of ' .
 172                 'the value'
 173             );
 174         case 'BLOB':
 175             return __(
 176                 'A BLOB column with a maximum length of 65,535 (2^16 - 1) bytes, ' .
 177                 'stored with a two-byte prefix indicating the length of the value'
 178             );
 179         case 'LONGBLOB':
 180             return __(
 181                 'A BLOB column with a maximum length of 4,294,967,295 or 4GiB ' .
 182                 '(2^32 - 1) bytes, stored with a four-byte prefix indicating the ' .
 183                 'length of the value'
 184             );
 185         case 'ENUM':
 186             return __(
 187                 "An enumeration, chosen from the list of up to 65,535 values or " .
 188                 "the special '' error value"
 189             );
 190         case 'SET':
 191             return __("A single value chosen from a set of up to 64 members");
 192         case 'GEOMETRY':
 193             return __('A type that can store a geometry of any type');
 194         case 'POINT':
 195             return __('A point in 2-dimensional space');
 196         case 'LINESTRING':
 197             return __('A curve with linear interpolation between points');
 198         case 'POLYGON':
 199             return __('A polygon');
 200         case 'MULTIPOINT':
 201             return __('A collection of points');
 202         case 'MULTILINESTRING':
 203             return __(
 204                 'A collection of curves with linear interpolation between points'
 205             );
 206         case 'MULTIPOLYGON':
 207             return __('A collection of polygons');
 208         case 'GEOMETRYCOLLECTION':
 209             return __('A collection of geometry objects of any type');
 210         case 'JSON':
 211             return __(
 212                 'Stores and enables efficient access to data in JSON'
 213                 . ' (JavaScript Object Notation) documents'
 214             );
 215         }
 216         return '';
 217     }
 218
 219     /**
 220      * Returns class of a type, used for functions available for type
 221      * or default values.
 222      *
 223      * @param string $type The data type to get a class.
 224      *
 225      * @return string
 226      *
 227      */
 228     public function getTypeClass($type)
 229     {
 230         $type = mb_strtoupper($type);
 231         switch ($type) {
 232         case 'TINYINT':
 233         case 'SMALLINT':
 234         case 'MEDIUMINT':
 235         case 'INT':
 236         case 'BIGINT':
 237         case 'DECIMAL':
 238         case 'FLOAT':
 239         case 'DOUBLE':
 240         case 'REAL':
 241         case 'BIT':
 242         case 'BOOLEAN':
 243         case 'SERIAL':
 244             return 'NUMBER';
 245
 246         case 'DATE':
 247         case 'DATETIME':
 248         case 'TIMESTAMP':
 249         case 'TIME':
 250         case 'YEAR':
 251             return 'DATE';
 252
 253         case 'CHAR':
 254         case 'VARCHAR':
 255         case 'TINYTEXT':
 256         case 'TEXT':
 257         case 'MEDIUMTEXT':
 258         case 'LONGTEXT':
 259         case 'BINARY':
 260         case 'VARBINARY':
 261         case 'TINYBLOB':
 262         case 'MEDIUMBLOB':
 263         case 'BLOB':
 264         case 'LONGBLOB':
 265         case 'ENUM':
 266         case 'SET':
 267             return 'CHAR';
 268
 269         case 'GEOMETRY':
 270         case 'POINT':
 271         case 'LINESTRING':
 272         case 'POLYGON':
 273         case 'MULTIPOINT':
 274         case 'MULTILINESTRING':
 275         case 'MULTIPOLYGON':
 276         case 'GEOMETRYCOLLECTION':
 277             return 'SPATIAL';
 278
 279         case 'JSON':
 280             return 'JSON';
 281         }
 282
 283         return '';
 284     }
 285
 286     /**
 287      * Returns array of functions available for a class.
 288      *
 289      * @param string $class The class to get function list.
 290      *
 291      * @return string[]
 292      *
 293      */
 294     public function getFunctionsClass($class)
 295     {
 296         switch ($class) {
 297         case 'CHAR':
 298             $ret = array(
 299                 'AES_DECRYPT',
 300                 'AES_ENCRYPT',
 301                 'BIN',
 302                 'CHAR',
 303                 'COMPRESS',
 304                 'CURRENT_USER',
 305                 'DATABASE',
 306                 'DAYNAME',
 307                 'DES_DECRYPT',
 308                 'DES_ENCRYPT',
 309                 'ENCRYPT',
 310                 'HEX',
 311                 'INET6_NTOA',
 312                 'INET_NTOA',
 313                 'LOAD_FILE',
 314                 'LOWER',
 315                 'LTRIM',
 316                 'MD5',
 317                 'MONTHNAME',
 318                 'OLD_PASSWORD',
 319                 'PASSWORD',
 320                 'QUOTE',
 321                 'REVERSE',
 322                 'RTRIM',
 323                 'SHA1',
 324                 'SOUNDEX',
 325                 'SPACE',
 326                 'TRIM',
 327                 'UNCOMPRESS',
 328                 'UNHEX',
 329                 'UPPER',
 330                 'USER',
 331                 'UUID',
 332                 'VERSION',
 333             );
 334
 335             if ((PMA_MARIADB && PMA_MYSQL_INT_VERSION < 100012)
 336                 || PMA_MYSQL_INT_VERSION < 50603
 337             ) {
 338                 $ret = array_diff($ret, array('INET6_NTOA'));
 339             }
 340             return $ret;
 341
 342         case 'DATE':
 343             return array(
 344                 'CURRENT_DATE',
 345                 'CURRENT_TIME',
 346                 'DATE',
 347                 'FROM_DAYS',
 348                 'FROM_UNIXTIME',
 349                 'LAST_DAY',
 350                 'NOW',
 351                 'SEC_TO_TIME',
 352                 'SYSDATE',
 353                 'TIME',
 354                 'TIMESTAMP',
 355                 'UTC_DATE',
 356                 'UTC_TIME',
 357                 'UTC_TIMESTAMP',
 358                 'YEAR',
 359             );
 360
 361         case 'NUMBER':
 362             $ret = array(
 363                 'ABS',
 364                 'ACOS',
 365                 'ASCII',
 366                 'ASIN',
 367                 'ATAN',
 368                 'BIT_LENGTH',
 369                 'BIT_COUNT',
 370                 'CEILING',
 371                 'CHAR_LENGTH',
 372                 'CONNECTION_ID',
 373                 'COS',
 374                 'COT',
 375                 'CRC32',
 376                 'DAYOFMONTH',
 377                 'DAYOFWEEK',
 378                 'DAYOFYEAR',
 379                 'DEGREES',
 380                 'EXP',
 381                 'FLOOR',
 382                 'HOUR',
 383                 'INET6_ATON',
 384                 'INET_ATON',
 385                 'LENGTH',
 386                 'LN',
 387                 'LOG',
 388                 'LOG2',
 389                 'LOG10',
 390                 'MICROSECOND',
 391                 'MINUTE',
 392                 'MONTH',
 393                 'OCT',
 394                 'ORD',
 395                 'PI',
 396                 'QUARTER',
 397                 'RADIANS',
 398                 'RAND',
 399                 'ROUND',
 400                 'SECOND',
 401                 'SIGN',
 402                 'SIN',
 403                 'SQRT',
 404                 'TAN',
 405                 'TO_DAYS',
 406                 'TO_SECONDS',
 407                 'TIME_TO_SEC',
 408                 'UNCOMPRESSED_LENGTH',
 409                 'UNIX_TIMESTAMP',
 410                 'UUID_SHORT',
 411                 'WEEK',
 412                 'WEEKDAY',
 413                 'WEEKOFYEAR',
 414                 'YEARWEEK',
 415             );
 416             if ((PMA_MARIADB && PMA_MYSQL_INT_VERSION < 100012)
 417                 || PMA_MYSQL_INT_VERSION < 50603
 418             ) {
 419                 $ret = array_diff($ret, array('INET6_ATON'));
 420             }
 421             return $ret;
 422
 423         case 'SPATIAL':
 424             return array(
 425                 'GeomFromText',
 426                 'GeomFromWKB',
 427
 428                 'GeomCollFromText',
 429                 'LineFromText',
 430                 'MLineFromText',
 431                 'PointFromText',
 432                 'MPointFromText',
 433                 'PolyFromText',
 434                 'MPolyFromText',
 435
 436                 'GeomCollFromWKB',
 437                 'LineFromWKB',
 438                 'MLineFromWKB',
 439                 'PointFromWKB',
 440                 'MPointFromWKB',
 441                 'PolyFromWKB',
 442                 'MPolyFromWKB',
 443             );
 444
 445         }
 446         return array();
 447     }
 448
 449     /**
 450      * Returns array of all attributes available.
 451      *
 452      * @return string[]
 453      *
 454      */
 455     public function getAttributes()
 456     {
 457         return array(
 458             '',
 459             'BINARY',
 460             'UNSIGNED',
 461             'UNSIGNED ZEROFILL',
 462             'on update CURRENT_TIMESTAMP',
 463         );
 464     }
 465
 466     /**
 467      * Returns array of all column types available.
 468      *
 469      * VARCHAR, TINYINT, TEXT and DATE are listed first, based on
 470      * estimated popularity.
 471      *
 472      * @return string[]
 473      *
 474      */
 475     public function getColumns()
 476     {
 477         $ret = parent::getColumns();
 478         // numeric
 479         $ret[_pgettext('numeric types', 'Numeric')] = array(
 480             'TINYINT',
 481             'SMALLINT',
 482             'MEDIUMINT',
 483             'INT',
 484             'BIGINT',
 485             '-',
 486             'DECIMAL',
 487             'FLOAT',
 488             'DOUBLE',
 489             'REAL',
 490             '-',
 491             'BIT',
 492             'BOOLEAN',
 493             'SERIAL',
 494         );
 495
 496         // Date/Time
 497         $ret[_pgettext('date and time types', 'Date and time')] = array(
 498             'DATE',
 499             'DATETIME',
 500             'TIMESTAMP',
 501             'TIME',
 502             'YEAR',
 503         );
 504
 505         // Text
 506         $ret[_pgettext('string types', 'String')] = array(
 507             'CHAR',
 508             'VARCHAR',
 509             '-',
 510             'TINYTEXT',
 511             'TEXT',
 512             'MEDIUMTEXT',
 513             'LONGTEXT',
 514             '-',
 515             'BINARY',
 516             'VARBINARY',
 517             '-',
 518             'TINYBLOB',
 519             'MEDIUMBLOB',
 520             'BLOB',
 521             'LONGBLOB',
 522             '-',
 523             'ENUM',
 524             'SET',
 525         );
 526
 527         $ret[_pgettext('spatial types', 'Spatial')] = array(
 528             'GEOMETRY',
 529             'POINT',
 530             'LINESTRING',
 531             'POLYGON',
 532             'MULTIPOINT',
 533             'MULTILINESTRING',
 534             'MULTIPOLYGON',
 535             'GEOMETRYCOLLECTION',
 536         );
 537
 538         if (PMA_MYSQL_INT_VERSION >= 50708
 539             && \PMA\libraries\Util::getServerType() != 'MariaDB'
 540         ) {
 541           $ret['JSON'] = array(
 542               'JSON',
 543           );
 544         }
 545
 546         return $ret;
 547     }
 548
 549     /**
 550      * Returns an array of integer types
 551      *
 552      * @return string[] integer types
 553      */
 554     public function getIntegerTypes()
 555     {
 556         return array('tinyint', 'smallint', 'mediumint', 'int', 'bigint');
 557     }
 558
 559     /**
 560      * Returns the min and max values of a given integer type
 561      *
 562      * @param string  $type   integer type
 563      * @param boolean $signed whether signed
 564      *
 565      * @return string[] min and max values
 566      */
 567     public function getIntegerRange($type, $signed = true)
 568     {
 569         static $min_max_data = array(
 570             'unsigned' => array(
 571                 'tinyint'   => array('0', '255'),
 572                 'smallint'  => array('0', '65535'),
 573                 'mediumint' => array('0', '16777215'),
 574                 'int'       => array('0', '4294967295'),
 575                 'bigint'    => array('0', '18446744073709551615')
 576             ),
 577             'signed' => array(
 578                 'tinyint'   => array('-128', '127'),
 579                 'smallint'  => array('-32768', '32767'),
 580                 'mediumint' => array('-8388608', '8388607'),
 581                 'int'       => array('-2147483648', '2147483647'),
 582                 'bigint'    => array('-9223372036854775808', '9223372036854775807')
 583             )
 584         );
 585         $relevantArray = $signed
 586             ? $min_max_data['signed']
 587             : $min_max_data['unsigned'];
 588         return isset($relevantArray[$type]) ? $relevantArray[$type] : array('', '');
 589     }
 590 }