Types.php 27 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970
  1. <?php
  2. /**
  3. * SQL data types definition
  4. */
  5. declare(strict_types=1);
  6. namespace PhpMyAdmin;
  7. use PhpMyAdmin\Query\Compatibility;
  8. use function __;
  9. use function _pgettext;
  10. use function array_diff;
  11. use function array_merge;
  12. use function array_values;
  13. use function htmlspecialchars;
  14. use function in_array;
  15. use function mb_strtoupper;
  16. use function sort;
  17. use function sprintf;
  18. use function strncasecmp;
  19. /**
  20. * Class holding type definitions for MySQL and MariaDB.
  21. */
  22. class Types
  23. {
  24. /** @var DatabaseInterface Database interface */
  25. private $dbi;
  26. /**
  27. * @param DatabaseInterface $dbi Database interface instance
  28. */
  29. public function __construct($dbi)
  30. {
  31. $this->dbi = $dbi;
  32. }
  33. /**
  34. * Returns list of unary operators.
  35. *
  36. * @return string[]
  37. */
  38. public function getUnaryOperators()
  39. {
  40. return [
  41. 'IS NULL',
  42. 'IS NOT NULL',
  43. "= ''",
  44. "!= ''",
  45. ];
  46. }
  47. /**
  48. * Check whether operator is unary.
  49. *
  50. * @param string $op operator name
  51. */
  52. public function isUnaryOperator($op): bool
  53. {
  54. return in_array($op, $this->getUnaryOperators());
  55. }
  56. /**
  57. * Returns list of operators checking for NULL.
  58. *
  59. * @return string[]
  60. */
  61. public function getNullOperators()
  62. {
  63. return [
  64. 'IS NULL',
  65. 'IS NOT NULL',
  66. ];
  67. }
  68. /**
  69. * ENUM search operators
  70. *
  71. * @return string[]
  72. */
  73. public function getEnumOperators()
  74. {
  75. return [
  76. '=',
  77. '!=',
  78. ];
  79. }
  80. /**
  81. * TEXT search operators
  82. *
  83. * @return string[]
  84. */
  85. public function getTextOperators()
  86. {
  87. return [
  88. 'LIKE %...%',
  89. 'LIKE',
  90. 'NOT LIKE',
  91. 'NOT LIKE %...%',
  92. '=',
  93. '!=',
  94. 'REGEXP',
  95. 'REGEXP ^...$',
  96. 'NOT REGEXP',
  97. "= ''",
  98. "!= ''",
  99. 'IN (...)',
  100. 'NOT IN (...)',
  101. 'BETWEEN',
  102. 'NOT BETWEEN',
  103. ];
  104. }
  105. /**
  106. * Number search operators
  107. *
  108. * @return string[]
  109. */
  110. public function getNumberOperators()
  111. {
  112. return [
  113. '=',
  114. '>',
  115. '>=',
  116. '<',
  117. '<=',
  118. '!=',
  119. 'LIKE',
  120. 'LIKE %...%',
  121. 'NOT LIKE',
  122. 'NOT LIKE %...%',
  123. 'IN (...)',
  124. 'NOT IN (...)',
  125. 'BETWEEN',
  126. 'NOT BETWEEN',
  127. ];
  128. }
  129. /**
  130. * UUID search operators
  131. *
  132. * @return string[]
  133. */
  134. public function getUUIDOperators()
  135. {
  136. return [
  137. '=',
  138. '!=',
  139. 'LIKE',
  140. 'LIKE %...%',
  141. 'NOT LIKE',
  142. 'NOT LIKE %...%',
  143. 'IN (...)',
  144. 'NOT IN (...)',
  145. ];
  146. }
  147. /**
  148. * Returns operators for given type
  149. *
  150. * @param string $type Type of field
  151. * @param bool $null Whether field can be NULL
  152. *
  153. * @return string[]
  154. */
  155. public function getTypeOperators($type, $null)
  156. {
  157. $ret = [];
  158. $class = $this->getTypeClass($type);
  159. if (strncasecmp($type, 'enum', 4) == 0) {
  160. $ret = array_merge($ret, $this->getEnumOperators());
  161. } elseif ($class === 'CHAR') {
  162. $ret = array_merge($ret, $this->getTextOperators());
  163. } elseif ($class === 'UUID') {
  164. $ret = array_merge($ret, $this->getUUIDOperators());
  165. } else {
  166. $ret = array_merge($ret, $this->getNumberOperators());
  167. }
  168. if ($null) {
  169. $ret = array_merge($ret, $this->getNullOperators());
  170. }
  171. return $ret;
  172. }
  173. /**
  174. * Returns operators for given type as html options
  175. *
  176. * @param string $type Type of field
  177. * @param bool $null Whether field can be NULL
  178. * @param string $selectedOperator Option to be selected
  179. *
  180. * @return string Generated Html
  181. */
  182. public function getTypeOperatorsHtml($type, $null, $selectedOperator = null)
  183. {
  184. $html = '';
  185. foreach ($this->getTypeOperators($type, $null) as $fc) {
  186. if (isset($selectedOperator) && $selectedOperator == $fc) {
  187. $selected = ' selected="selected"';
  188. } else {
  189. $selected = '';
  190. }
  191. $html .= '<option value="' . htmlspecialchars($fc) . '"'
  192. . $selected . '>'
  193. . htmlspecialchars($fc) . '</option>';
  194. }
  195. return $html;
  196. }
  197. /**
  198. * Returns the data type description.
  199. *
  200. * @param string $type The data type to get a description.
  201. *
  202. * @return string
  203. */
  204. public function getTypeDescription($type)
  205. {
  206. $type = mb_strtoupper($type);
  207. switch ($type) {
  208. case 'TINYINT':
  209. return __('A 1-byte integer, signed range is -128 to 127, unsigned range is 0 to 255');
  210. case 'SMALLINT':
  211. return __('A 2-byte integer, signed range is -32,768 to 32,767, unsigned range is 0 to 65,535');
  212. case 'MEDIUMINT':
  213. return __(
  214. 'A 3-byte integer, signed range is -8,388,608 to 8,388,607, unsigned range is 0 to 16,777,215'
  215. );
  216. case 'INT':
  217. return __(
  218. 'A 4-byte integer, signed range is ' .
  219. '-2,147,483,648 to 2,147,483,647, unsigned range is 0 to ' .
  220. '4,294,967,295'
  221. );
  222. case 'BIGINT':
  223. return __(
  224. 'An 8-byte integer, signed range is -9,223,372,036,854,775,808 ' .
  225. 'to 9,223,372,036,854,775,807, unsigned range is 0 to ' .
  226. '18,446,744,073,709,551,615'
  227. );
  228. case 'DECIMAL':
  229. return __(
  230. 'A fixed-point number (M, D) - the maximum number of digits (M) ' .
  231. 'is 65 (default 10), the maximum number of decimals (D) is 30 ' .
  232. '(default 0)'
  233. );
  234. case 'FLOAT':
  235. return __(
  236. 'A small floating-point number, allowable values are ' .
  237. '-3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to ' .
  238. '3.402823466E+38'
  239. );
  240. case 'DOUBLE':
  241. return __(
  242. 'A double-precision floating-point number, allowable values are ' .
  243. '-1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and ' .
  244. '2.2250738585072014E-308 to 1.7976931348623157E+308'
  245. );
  246. case 'REAL':
  247. return __('Synonym for DOUBLE (exception: in REAL_AS_FLOAT SQL mode it is a synonym for FLOAT)');
  248. case 'BIT':
  249. return __('A bit-field type (M), storing M of bits per value (default is 1, maximum is 64)');
  250. case 'BOOLEAN':
  251. return __(
  252. 'A synonym for TINYINT(1), a value of zero is considered false, nonzero values are considered true'
  253. );
  254. case 'SERIAL':
  255. return __('An alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE');
  256. case 'DATE':
  257. return sprintf(
  258. __('A date, supported range is %1$s to %2$s'),
  259. '1000-01-01',
  260. '9999-12-31'
  261. );
  262. case 'DATETIME':
  263. return sprintf(
  264. __('A date and time combination, supported range is %1$s to %2$s'),
  265. '1000-01-01 00:00:00',
  266. '9999-12-31 23:59:59'
  267. );
  268. case 'TIMESTAMP':
  269. return __(
  270. 'A timestamp, range is 1970-01-01 00:00:01 UTC to 2038-01-09 ' .
  271. '03:14:07 UTC, stored as the number of seconds since the epoch ' .
  272. '(1970-01-01 00:00:00 UTC)'
  273. );
  274. case 'TIME':
  275. return sprintf(
  276. __('A time, range is %1$s to %2$s'),
  277. '-838:59:59',
  278. '838:59:59'
  279. );
  280. case 'YEAR':
  281. return __(
  282. 'A year in four-digit (4, default) or two-digit (2) format, the ' .
  283. 'allowable values are 70 (1970) to 69 (2069) or 1901 to 2155 and ' .
  284. '0000'
  285. );
  286. case 'CHAR':
  287. return __(
  288. 'A fixed-length (0-255, default 1) string that is always ' .
  289. 'right-padded with spaces to the specified length when stored'
  290. );
  291. case 'VARCHAR':
  292. return sprintf(
  293. __(
  294. 'A variable-length (%s) string, the effective maximum length is subject to the maximum row size'
  295. ),
  296. '0-65,535'
  297. );
  298. case 'TINYTEXT':
  299. return __(
  300. 'A TEXT column with a maximum length of 255 (2^8 - 1) characters, ' .
  301. 'stored with a one-byte prefix indicating the length of the value ' .
  302. 'in bytes'
  303. );
  304. case 'TEXT':
  305. return __(
  306. 'A TEXT column with a maximum length of 65,535 (2^16 - 1) ' .
  307. 'characters, stored with a two-byte prefix indicating the length ' .
  308. 'of the value in bytes'
  309. );
  310. case 'MEDIUMTEXT':
  311. return __(
  312. 'A TEXT column with a maximum length of 16,777,215 (2^24 - 1) ' .
  313. 'characters, stored with a three-byte prefix indicating the ' .
  314. 'length of the value in bytes'
  315. );
  316. case 'LONGTEXT':
  317. return __(
  318. 'A TEXT column with a maximum length of 4,294,967,295 or 4GiB ' .
  319. '(2^32 - 1) characters, stored with a four-byte prefix indicating ' .
  320. 'the length of the value in bytes'
  321. );
  322. case 'BINARY':
  323. return __(
  324. 'Similar to the CHAR type, but stores binary byte strings rather than non-binary character strings'
  325. );
  326. case 'VARBINARY':
  327. return __(
  328. 'Similar to the VARCHAR type, but stores binary byte strings ' .
  329. 'rather than non-binary character strings'
  330. );
  331. case 'TINYBLOB':
  332. return __(
  333. 'A BLOB column with a maximum length of 255 (2^8 - 1) bytes, ' .
  334. 'stored with a one-byte prefix indicating the length of the value'
  335. );
  336. case 'MEDIUMBLOB':
  337. return __(
  338. 'A BLOB column with a maximum length of 16,777,215 (2^24 - 1) ' .
  339. 'bytes, stored with a three-byte prefix indicating the length of ' .
  340. 'the value'
  341. );
  342. case 'BLOB':
  343. return __(
  344. 'A BLOB column with a maximum length of 65,535 (2^16 - 1) bytes, ' .
  345. 'stored with a two-byte prefix indicating the length of the value'
  346. );
  347. case 'LONGBLOB':
  348. return __(
  349. 'A BLOB column with a maximum length of 4,294,967,295 or 4GiB ' .
  350. '(2^32 - 1) bytes, stored with a four-byte prefix indicating the ' .
  351. 'length of the value'
  352. );
  353. case 'ENUM':
  354. return __(
  355. 'An enumeration, chosen from the list of up to 65,535 values or the special \'\' error value'
  356. );
  357. case 'SET':
  358. return __('A single value chosen from a set of up to 64 members');
  359. case 'GEOMETRY':
  360. return __('A type that can store a geometry of any type');
  361. case 'POINT':
  362. return __('A point in 2-dimensional space');
  363. case 'LINESTRING':
  364. return __('A curve with linear interpolation between points');
  365. case 'POLYGON':
  366. return __('A polygon');
  367. case 'MULTIPOINT':
  368. return __('A collection of points');
  369. case 'MULTILINESTRING':
  370. return __('A collection of curves with linear interpolation between points');
  371. case 'MULTIPOLYGON':
  372. return __('A collection of polygons');
  373. case 'GEOMETRYCOLLECTION':
  374. return __('A collection of geometry objects of any type');
  375. case 'JSON':
  376. return __('Stores and enables efficient access to data in JSON (JavaScript Object Notation) documents');
  377. case 'INET6':
  378. return __('Intended for storage of IPv6 addresses, as well as IPv4 '
  379. . 'addresses assuming conventional mapping of IPv4 addresses '
  380. . 'into IPv6 addresses');
  381. case 'UUID':
  382. return __('128-bit UUID (Universally Unique Identifier)');
  383. }
  384. return '';
  385. }
  386. /**
  387. * Returns class of a type, used for functions available for type
  388. * or default values.
  389. *
  390. * @param string $type The data type to get a class.
  391. *
  392. * @return string
  393. */
  394. public function getTypeClass($type)
  395. {
  396. $type = mb_strtoupper((string) $type);
  397. switch ($type) {
  398. case 'TINYINT':
  399. case 'SMALLINT':
  400. case 'MEDIUMINT':
  401. case 'INT':
  402. case 'BIGINT':
  403. case 'DECIMAL':
  404. case 'FLOAT':
  405. case 'DOUBLE':
  406. case 'REAL':
  407. case 'BIT':
  408. case 'BOOLEAN':
  409. case 'SERIAL':
  410. return 'NUMBER';
  411. case 'DATE':
  412. case 'DATETIME':
  413. case 'TIMESTAMP':
  414. case 'TIME':
  415. case 'YEAR':
  416. return 'DATE';
  417. case 'CHAR':
  418. case 'VARCHAR':
  419. case 'TINYTEXT':
  420. case 'TEXT':
  421. case 'MEDIUMTEXT':
  422. case 'LONGTEXT':
  423. case 'BINARY':
  424. case 'VARBINARY':
  425. case 'TINYBLOB':
  426. case 'MEDIUMBLOB':
  427. case 'BLOB':
  428. case 'LONGBLOB':
  429. case 'ENUM':
  430. case 'SET':
  431. case 'INET6':
  432. return 'CHAR';
  433. case 'GEOMETRY':
  434. case 'POINT':
  435. case 'LINESTRING':
  436. case 'POLYGON':
  437. case 'MULTIPOINT':
  438. case 'MULTILINESTRING':
  439. case 'MULTIPOLYGON':
  440. case 'GEOMETRYCOLLECTION':
  441. return 'SPATIAL';
  442. case 'JSON':
  443. return 'JSON';
  444. case 'UUID':
  445. return 'UUID';
  446. }
  447. return '';
  448. }
  449. /**
  450. * Returns array of functions available for a class.
  451. *
  452. * @param string $class The class to get function list.
  453. *
  454. * @return string[]
  455. */
  456. public function getFunctionsClass($class)
  457. {
  458. $isMariaDB = $this->dbi->isMariaDB();
  459. $serverVersion = $this->dbi->getVersion();
  460. switch ($class) {
  461. case 'CHAR':
  462. $ret = [
  463. 'AES_DECRYPT',
  464. 'AES_ENCRYPT',
  465. 'BIN',
  466. 'CHAR',
  467. 'COMPRESS',
  468. 'CURRENT_USER',
  469. 'DATABASE',
  470. 'DAYNAME',
  471. 'DES_DECRYPT',
  472. 'DES_ENCRYPT',
  473. 'ENCRYPT',
  474. 'HEX',
  475. 'INET6_NTOA',
  476. 'INET_NTOA',
  477. 'LOAD_FILE',
  478. 'LOWER',
  479. 'LTRIM',
  480. 'MD5',
  481. 'MONTHNAME',
  482. 'OLD_PASSWORD',
  483. 'PASSWORD',
  484. 'QUOTE',
  485. 'REVERSE',
  486. 'RTRIM',
  487. 'SHA1',
  488. 'SOUNDEX',
  489. 'SPACE',
  490. 'TRIM',
  491. 'UNCOMPRESS',
  492. 'UNHEX',
  493. 'UPPER',
  494. 'USER',
  495. 'UUID',
  496. 'VERSION',
  497. ];
  498. if (($isMariaDB && $serverVersion < 100012) || $serverVersion < 50603) {
  499. $ret = array_diff($ret, ['INET6_NTOA']);
  500. }
  501. return array_values($ret);
  502. case 'DATE':
  503. return [
  504. 'CURRENT_DATE',
  505. 'CURRENT_TIME',
  506. 'DATE',
  507. 'FROM_DAYS',
  508. 'FROM_UNIXTIME',
  509. 'LAST_DAY',
  510. 'NOW',
  511. 'SEC_TO_TIME',
  512. 'SYSDATE',
  513. 'TIME',
  514. 'TIMESTAMP',
  515. 'UTC_DATE',
  516. 'UTC_TIME',
  517. 'UTC_TIMESTAMP',
  518. 'YEAR',
  519. ];
  520. case 'NUMBER':
  521. $ret = [
  522. 'ABS',
  523. 'ACOS',
  524. 'ASCII',
  525. 'ASIN',
  526. 'ATAN',
  527. 'BIT_LENGTH',
  528. 'BIT_COUNT',
  529. 'CEILING',
  530. 'CHAR_LENGTH',
  531. 'CONNECTION_ID',
  532. 'COS',
  533. 'COT',
  534. 'CRC32',
  535. 'DAYOFMONTH',
  536. 'DAYOFWEEK',
  537. 'DAYOFYEAR',
  538. 'DEGREES',
  539. 'EXP',
  540. 'FLOOR',
  541. 'HOUR',
  542. 'INET6_ATON',
  543. 'INET_ATON',
  544. 'LENGTH',
  545. 'LN',
  546. 'LOG',
  547. 'LOG2',
  548. 'LOG10',
  549. 'MICROSECOND',
  550. 'MINUTE',
  551. 'MONTH',
  552. 'OCT',
  553. 'ORD',
  554. 'PI',
  555. 'QUARTER',
  556. 'RADIANS',
  557. 'RAND',
  558. 'ROUND',
  559. 'SECOND',
  560. 'SIGN',
  561. 'SIN',
  562. 'SQRT',
  563. 'TAN',
  564. 'TO_DAYS',
  565. 'TO_SECONDS',
  566. 'TIME_TO_SEC',
  567. 'UNCOMPRESSED_LENGTH',
  568. 'UNIX_TIMESTAMP',
  569. 'UUID_SHORT',
  570. 'WEEK',
  571. 'WEEKDAY',
  572. 'WEEKOFYEAR',
  573. 'YEARWEEK',
  574. ];
  575. if (($isMariaDB && $serverVersion < 100012) || $serverVersion < 50603) {
  576. $ret = array_diff($ret, ['INET6_ATON']);
  577. }
  578. return array_values($ret);
  579. case 'SPATIAL':
  580. if ($serverVersion >= 50600) {
  581. return [
  582. 'ST_GeomFromText',
  583. 'ST_GeomFromWKB',
  584. 'ST_GeomCollFromText',
  585. 'ST_LineFromText',
  586. 'ST_MLineFromText',
  587. 'ST_PointFromText',
  588. 'ST_MPointFromText',
  589. 'ST_PolyFromText',
  590. 'ST_MPolyFromText',
  591. 'ST_GeomCollFromWKB',
  592. 'ST_LineFromWKB',
  593. 'ST_MLineFromWKB',
  594. 'ST_PointFromWKB',
  595. 'ST_MPointFromWKB',
  596. 'ST_PolyFromWKB',
  597. 'ST_MPolyFromWKB',
  598. ];
  599. }
  600. return [
  601. 'GeomFromText',
  602. 'GeomFromWKB',
  603. 'GeomCollFromText',
  604. 'LineFromText',
  605. 'MLineFromText',
  606. 'PointFromText',
  607. 'MPointFromText',
  608. 'PolyFromText',
  609. 'MPolyFromText',
  610. 'GeomCollFromWKB',
  611. 'LineFromWKB',
  612. 'MLineFromWKB',
  613. 'PointFromWKB',
  614. 'MPointFromWKB',
  615. 'PolyFromWKB',
  616. 'MPolyFromWKB',
  617. ];
  618. }
  619. return [];
  620. }
  621. /**
  622. * Returns array of functions available for a type.
  623. *
  624. * @param string $type The data type to get function list.
  625. *
  626. * @return string[]
  627. */
  628. public function getFunctions($type)
  629. {
  630. $class = $this->getTypeClass($type);
  631. return $this->getFunctionsClass($class);
  632. }
  633. /**
  634. * Returns array of all functions available.
  635. *
  636. * @return string[]
  637. */
  638. public function getAllFunctions()
  639. {
  640. $ret = array_merge(
  641. $this->getFunctionsClass('CHAR'),
  642. $this->getFunctionsClass('NUMBER'),
  643. $this->getFunctionsClass('DATE'),
  644. $this->getFunctionsClass('SPATIAL')
  645. );
  646. sort($ret);
  647. return $ret;
  648. }
  649. /**
  650. * Returns array of all attributes available.
  651. *
  652. * @return string[]
  653. */
  654. public function getAttributes()
  655. {
  656. $serverVersion = $this->dbi->getVersion();
  657. $attributes = [
  658. '',
  659. 'BINARY',
  660. 'UNSIGNED',
  661. 'UNSIGNED ZEROFILL',
  662. 'on update CURRENT_TIMESTAMP',
  663. ];
  664. if (Compatibility::supportsCompressedColumns($serverVersion)) {
  665. $attributes[] = 'COMPRESSED=zlib';
  666. }
  667. return $attributes;
  668. }
  669. /**
  670. * Returns array of all column types available.
  671. *
  672. * VARCHAR, TINYINT, TEXT and DATE are listed first, based on
  673. * estimated popularity.
  674. *
  675. * @return array
  676. */
  677. public function getColumns(): array
  678. {
  679. $isMariaDB = $this->dbi->isMariaDB();
  680. $serverVersion = $this->dbi->getVersion();
  681. $isUUIDSupported = Compatibility::isUUIDSupported($this->dbi);
  682. // most used types
  683. $ret = [
  684. 'INT',
  685. 'VARCHAR',
  686. 'TEXT',
  687. 'DATE',
  688. ];
  689. if ($isUUIDSupported) {
  690. $ret[] = 'UUID';
  691. }
  692. // numeric
  693. $ret[_pgettext('numeric types', 'Numeric')] = [
  694. 'TINYINT',
  695. 'SMALLINT',
  696. 'MEDIUMINT',
  697. 'INT',
  698. 'BIGINT',
  699. '-',
  700. 'DECIMAL',
  701. 'FLOAT',
  702. 'DOUBLE',
  703. 'REAL',
  704. '-',
  705. 'BIT',
  706. 'BOOLEAN',
  707. 'SERIAL',
  708. ];
  709. // Date/Time
  710. $ret[_pgettext('date and time types', 'Date and time')] = [
  711. 'DATE',
  712. 'DATETIME',
  713. 'TIMESTAMP',
  714. 'TIME',
  715. 'YEAR',
  716. ];
  717. // Text
  718. $stringTypes = [
  719. 'CHAR',
  720. 'VARCHAR',
  721. '-',
  722. 'TINYTEXT',
  723. 'TEXT',
  724. 'MEDIUMTEXT',
  725. 'LONGTEXT',
  726. '-',
  727. 'BINARY',
  728. 'VARBINARY',
  729. '-',
  730. 'TINYBLOB',
  731. 'BLOB',
  732. 'MEDIUMBLOB',
  733. 'LONGBLOB',
  734. '-',
  735. 'ENUM',
  736. 'SET',
  737. ];
  738. if ($isMariaDB && $serverVersion >= 100500) {
  739. $stringTypes[] = '-';
  740. $stringTypes[] = 'INET6';
  741. }
  742. $ret[_pgettext('string types', 'String')] = $stringTypes;
  743. $ret[_pgettext('spatial types', 'Spatial')] = [
  744. 'GEOMETRY',
  745. 'POINT',
  746. 'LINESTRING',
  747. 'POLYGON',
  748. 'MULTIPOINT',
  749. 'MULTILINESTRING',
  750. 'MULTIPOLYGON',
  751. 'GEOMETRYCOLLECTION',
  752. ];
  753. if (($isMariaDB && $serverVersion > 100207) || (! $isMariaDB && $serverVersion >= 50708)) {
  754. $ret['JSON'] = ['JSON'];
  755. }
  756. if ($isUUIDSupported) {
  757. $ret['UUID'] = ['UUID'];
  758. }
  759. return $ret;
  760. }
  761. public function mapAliasToMysqlType(string $alias): string
  762. {
  763. return [
  764. 'BOOL' => 'TINYINT',
  765. 'BOOLEAN' => 'TINYINT',
  766. 'CHARACTER VARYING' => 'VARCHAR',
  767. 'FIXED' => 'DECIMAL',
  768. 'FLOAT4' => 'FLOAT',
  769. 'FLOAT8' => 'DOUBLE',
  770. 'INT1' => 'TINYINT',
  771. 'INT2' => 'SMALLINT',
  772. 'INT3' => 'MEDIUMINT',
  773. 'INT4' => 'INT',
  774. 'INT8' => 'BIGINT',
  775. 'LONG VARBINARY' => 'MEDIUMBLOB',
  776. 'LONG VARCHAR' => 'MEDIUMTEXT',
  777. 'LONG' => 'MEDIUMTEXT',
  778. 'MIDDLEINT' => 'MEDIUMINT',
  779. 'NUMERIC' => 'DECIMAL',
  780. ][$alias] ?? $alias;
  781. }
  782. /**
  783. * Returns an array of integer types
  784. *
  785. * @return string[] integer types
  786. */
  787. public function getIntegerTypes()
  788. {
  789. return [
  790. 'tinyint',
  791. 'smallint',
  792. 'mediumint',
  793. 'int',
  794. 'bigint',
  795. ];
  796. }
  797. /**
  798. * Returns the min and max values of a given integer type
  799. *
  800. * @param string $type integer type
  801. * @param bool $signed whether signed
  802. *
  803. * @return string[] min and max values
  804. */
  805. public function getIntegerRange($type, $signed = true)
  806. {
  807. $min_max_data = [
  808. 'unsigned' => [
  809. 'tinyint' => [
  810. '0',
  811. '255',
  812. ],
  813. 'smallint' => [
  814. '0',
  815. '65535',
  816. ],
  817. 'mediumint' => [
  818. '0',
  819. '16777215',
  820. ],
  821. 'int' => [
  822. '0',
  823. '4294967295',
  824. ],
  825. 'bigint' => [
  826. '0',
  827. '18446744073709551615',
  828. ],
  829. ],
  830. 'signed' => [
  831. 'tinyint' => [
  832. '-128',
  833. '127',
  834. ],
  835. 'smallint' => [
  836. '-32768',
  837. '32767',
  838. ],
  839. 'mediumint' => [
  840. '-8388608',
  841. '8388607',
  842. ],
  843. 'int' => [
  844. '-2147483648',
  845. '2147483647',
  846. ],
  847. 'bigint' => [
  848. '-9223372036854775808',
  849. '9223372036854775807',
  850. ],
  851. ],
  852. ];
  853. $relevantArray = $signed
  854. ? $min_max_data['signed']
  855. : $min_max_data['unsigned'];
  856. return $relevantArray[$type] ?? [
  857. '',
  858. '',
  859. ];
  860. }
  861. }