Table.php 90 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363136413651366136713681369137013711372137313741375137613771378137913801381138213831384138513861387138813891390139113921393139413951396139713981399140014011402140314041405140614071408140914101411141214131414141514161417141814191420142114221423142414251426142714281429143014311432143314341435143614371438143914401441144214431444144514461447144814491450145114521453145414551456145714581459146014611462146314641465146614671468146914701471147214731474147514761477147814791480148114821483148414851486148714881489149014911492149314941495149614971498149915001501150215031504150515061507150815091510151115121513151415151516151715181519152015211522152315241525152615271528152915301531153215331534153515361537153815391540154115421543154415451546154715481549155015511552155315541555155615571558155915601561156215631564156515661567156815691570157115721573157415751576157715781579158015811582158315841585158615871588158915901591159215931594159515961597159815991600160116021603160416051606160716081609161016111612161316141615161616171618161916201621162216231624162516261627162816291630163116321633163416351636163716381639164016411642164316441645164616471648164916501651165216531654165516561657165816591660166116621663166416651666166716681669167016711672167316741675167616771678167916801681168216831684168516861687168816891690169116921693169416951696169716981699170017011702170317041705170617071708170917101711171217131714171517161717171817191720172117221723172417251726172717281729173017311732173317341735173617371738173917401741174217431744174517461747174817491750175117521753175417551756175717581759176017611762176317641765176617671768176917701771177217731774177517761777177817791780178117821783178417851786178717881789179017911792179317941795179617971798179918001801180218031804180518061807180818091810181118121813181418151816181718181819182018211822182318241825182618271828182918301831183218331834183518361837183818391840184118421843184418451846184718481849185018511852185318541855185618571858185918601861186218631864186518661867186818691870187118721873187418751876187718781879188018811882188318841885188618871888188918901891189218931894189518961897189818991900190119021903190419051906190719081909191019111912191319141915191619171918191919201921192219231924192519261927192819291930193119321933193419351936193719381939194019411942194319441945194619471948194919501951195219531954195519561957195819591960196119621963196419651966196719681969197019711972197319741975197619771978197919801981198219831984198519861987198819891990199119921993199419951996199719981999200020012002200320042005200620072008200920102011201220132014201520162017201820192020202120222023202420252026202720282029203020312032203320342035203620372038203920402041204220432044204520462047204820492050205120522053205420552056205720582059206020612062206320642065206620672068206920702071207220732074207520762077207820792080208120822083208420852086208720882089209020912092209320942095209620972098209921002101210221032104210521062107210821092110211121122113211421152116211721182119212021212122212321242125212621272128212921302131213221332134213521362137213821392140214121422143214421452146214721482149215021512152215321542155215621572158215921602161216221632164216521662167216821692170217121722173217421752176217721782179218021812182218321842185218621872188218921902191219221932194219521962197219821992200220122022203220422052206220722082209221022112212221322142215221622172218221922202221222222232224222522262227222822292230223122322233223422352236223722382239224022412242224322442245224622472248224922502251225222532254225522562257225822592260226122622263226422652266226722682269227022712272227322742275227622772278227922802281228222832284228522862287228822892290229122922293229422952296229722982299230023012302230323042305230623072308230923102311231223132314231523162317231823192320232123222323232423252326232723282329233023312332233323342335233623372338233923402341234223432344234523462347234823492350235123522353235423552356235723582359236023612362236323642365236623672368236923702371237223732374237523762377237823792380238123822383238423852386238723882389239023912392239323942395239623972398239924002401240224032404240524062407240824092410241124122413241424152416241724182419242024212422242324242425242624272428242924302431243224332434243524362437243824392440244124422443244424452446244724482449245024512452245324542455245624572458245924602461246224632464246524662467246824692470247124722473247424752476247724782479248024812482248324842485248624872488248924902491249224932494249524962497249824992500250125022503250425052506250725082509251025112512251325142515251625172518251925202521252225232524252525262527252825292530253125322533253425352536253725382539254025412542254325442545254625472548254925502551255225532554255525562557255825592560256125622563256425652566256725682569257025712572257325742575257625772578257925802581258225832584258525862587258825892590259125922593259425952596259725982599260026012602260326042605260626072608260926102611261226132614261526162617261826192620262126222623262426252626262726282629263026312632263326342635263626372638263926402641264226432644264526462647264826492650265126522653265426552656265726582659266026612662266326642665
  1. <?php
  2. declare(strict_types=1);
  3. namespace PhpMyAdmin;
  4. use PhpMyAdmin\ConfigStorage\Features\DisplayFeature;
  5. use PhpMyAdmin\ConfigStorage\Features\RelationFeature;
  6. use PhpMyAdmin\ConfigStorage\Features\UiPreferencesFeature;
  7. use PhpMyAdmin\ConfigStorage\Relation;
  8. use PhpMyAdmin\Html\Generator;
  9. use PhpMyAdmin\Html\MySQLDocumentation;
  10. use PhpMyAdmin\Plugins\Export\ExportSql;
  11. use PhpMyAdmin\Query\Compatibility;
  12. use PhpMyAdmin\Query\Generator as QueryGenerator;
  13. use PhpMyAdmin\SqlParser\Components\Expression;
  14. use PhpMyAdmin\SqlParser\Components\OptionsArray;
  15. use PhpMyAdmin\SqlParser\Context;
  16. use PhpMyAdmin\SqlParser\Parser;
  17. use PhpMyAdmin\SqlParser\Statements\AlterStatement;
  18. use PhpMyAdmin\SqlParser\Statements\CreateStatement;
  19. use PhpMyAdmin\SqlParser\Statements\DropStatement;
  20. use PhpMyAdmin\SqlParser\Utils\Table as TableUtils;
  21. use Stringable;
  22. use function __;
  23. use function array_key_exists;
  24. use function array_keys;
  25. use function array_map;
  26. use function array_merge;
  27. use function count;
  28. use function end;
  29. use function explode;
  30. use function htmlspecialchars;
  31. use function implode;
  32. use function in_array;
  33. use function is_array;
  34. use function is_string;
  35. use function json_decode;
  36. use function json_encode;
  37. use function mb_stripos;
  38. use function mb_strlen;
  39. use function mb_substr;
  40. use function preg_match;
  41. use function preg_replace;
  42. use function rtrim;
  43. use function sprintf;
  44. use function str_contains;
  45. use function str_replace;
  46. use function stripos;
  47. use function strlen;
  48. use function strtolower;
  49. use function strtoupper;
  50. use function substr;
  51. use function substr_compare;
  52. use function trigger_error;
  53. use function trim;
  54. use const E_USER_WARNING;
  55. /**
  56. * Handles everything related to tables
  57. *
  58. * @todo make use of Message and Error
  59. */
  60. class Table implements Stringable
  61. {
  62. /**
  63. * UI preferences properties
  64. */
  65. public const PROP_SORTED_COLUMN = 'sorted_col';
  66. public const PROP_COLUMN_ORDER = 'col_order';
  67. public const PROP_COLUMN_VISIB = 'col_visib';
  68. /** @var string engine (innodb, myisam, bdb, ...) */
  69. public $engine = '';
  70. /** @var string type (view, base table, system view) */
  71. public $type = '';
  72. /** @var array UI preferences */
  73. public $uiprefs = [];
  74. /** @var array errors occurred */
  75. public $errors = [];
  76. /** @var array messages */
  77. public $messages = [];
  78. /** @var string table name */
  79. protected $name = '';
  80. /** @var string database name */
  81. protected $dbName = '';
  82. /** @var DatabaseInterface */
  83. protected $dbi;
  84. /** @var Relation */
  85. private $relation;
  86. /**
  87. * @param string $tableName table name
  88. * @param string $dbName database name
  89. * @param DatabaseInterface|null $dbi database interface for the table
  90. */
  91. public function __construct($tableName, $dbName, ?DatabaseInterface $dbi = null)
  92. {
  93. if (empty($dbi)) {
  94. $dbi = $GLOBALS['dbi'];
  95. }
  96. $this->dbi = $dbi;
  97. $this->name = $tableName;
  98. $this->dbName = $dbName;
  99. $this->relation = new Relation($this->dbi);
  100. }
  101. /**
  102. * returns table name
  103. *
  104. * @see Table::getName()
  105. */
  106. public function __toString(): string
  107. {
  108. return $this->getName();
  109. }
  110. /**
  111. * Table getter
  112. *
  113. * @param string $tableName table name
  114. * @param string $dbName database name
  115. * @param DatabaseInterface|null $dbi database interface for the table
  116. *
  117. * @return Table
  118. */
  119. public static function get($tableName, $dbName, ?DatabaseInterface $dbi = null)
  120. {
  121. return new Table($tableName, $dbName, $dbi);
  122. }
  123. /**
  124. * return the last error
  125. *
  126. * @return string the last error
  127. */
  128. public function getLastError()
  129. {
  130. return end($this->errors);
  131. }
  132. /**
  133. * return the last message
  134. *
  135. * @return string the last message
  136. */
  137. public function getLastMessage()
  138. {
  139. return end($this->messages);
  140. }
  141. /**
  142. * returns table name
  143. *
  144. * @param bool $backquoted whether to quote name with backticks ``
  145. *
  146. * @return string table name
  147. */
  148. public function getName($backquoted = false)
  149. {
  150. if ($backquoted) {
  151. return Util::backquote($this->name);
  152. }
  153. return $this->name;
  154. }
  155. /**
  156. * returns database name for this table
  157. *
  158. * @param bool $backquoted whether to quote name with backticks ``
  159. *
  160. * @return string database name for this table
  161. */
  162. public function getDbName($backquoted = false)
  163. {
  164. if ($backquoted) {
  165. return Util::backquote($this->dbName);
  166. }
  167. return $this->dbName;
  168. }
  169. /**
  170. * returns full name for table, including database name
  171. *
  172. * @param bool $backquoted whether to quote name with backticks ``
  173. *
  174. * @return string
  175. */
  176. public function getFullName($backquoted = false)
  177. {
  178. return $this->getDbName($backquoted) . '.'
  179. . $this->getName($backquoted);
  180. }
  181. /**
  182. * Checks the storage engine used to create table
  183. *
  184. * @param array|string $engine Checks the table engine against an
  185. * array of engine strings or a single string, should be uppercase
  186. */
  187. public function isEngine($engine): bool
  188. {
  189. $tableStorageEngine = $this->getStorageEngine();
  190. if (is_array($engine)) {
  191. foreach ($engine as $e) {
  192. if ($e == $tableStorageEngine) {
  193. return true;
  194. }
  195. }
  196. return false;
  197. }
  198. return $tableStorageEngine == $engine;
  199. }
  200. /**
  201. * returns whether the table is actually a view
  202. */
  203. public function isView(): bool
  204. {
  205. $db = $this->dbName;
  206. $table = $this->name;
  207. if (empty($db) || empty($table)) {
  208. return false;
  209. }
  210. // use cached data or load information with SHOW command
  211. if (
  212. $this->dbi->getCache()->getCachedTableContent([$db, $table]) != null
  213. || $GLOBALS['cfg']['Server']['DisableIS']
  214. ) {
  215. $type = $this->getStatusInfo('TABLE_TYPE');
  216. return $type === 'VIEW' || $type === 'SYSTEM VIEW';
  217. }
  218. // information_schema tables are 'SYSTEM VIEW's
  219. if ($db === 'information_schema') {
  220. return true;
  221. }
  222. // query information_schema
  223. $result = $this->dbi->fetchResult(
  224. 'SELECT TABLE_NAME'
  225. . ' FROM information_schema.VIEWS'
  226. . ' WHERE TABLE_SCHEMA = \'' . $this->dbi->escapeString((string) $db) . '\''
  227. . ' AND TABLE_NAME = \'' . $this->dbi->escapeString((string) $table) . '\''
  228. );
  229. return (bool) $result;
  230. }
  231. /**
  232. * Returns whether the table is actually an updatable view
  233. */
  234. public function isUpdatableView(): bool
  235. {
  236. if (empty($this->dbName) || empty($this->name)) {
  237. return false;
  238. }
  239. $result = $this->dbi->fetchResult(
  240. 'SELECT TABLE_NAME'
  241. . ' FROM information_schema.VIEWS'
  242. . ' WHERE TABLE_SCHEMA = \'' . $this->dbi->escapeString($this->dbName) . '\''
  243. . ' AND TABLE_NAME = \'' . $this->dbi->escapeString($this->name) . '\''
  244. . ' AND IS_UPDATABLE = \'YES\''
  245. );
  246. return (bool) $result;
  247. }
  248. /**
  249. * Checks if this is a merge table
  250. *
  251. * If the ENGINE of the table is MERGE or MRG_MYISAM (alias),
  252. * this is a merge table.
  253. */
  254. public function isMerge(): bool
  255. {
  256. return $this->isEngine(['MERGE', 'MRG_MYISAM']);
  257. }
  258. /**
  259. * Returns full table status info, or specific if $info provided
  260. * this info is collected from information_schema
  261. *
  262. * @param string $info specific information to be fetched
  263. * @param bool $forceRead read new rather than serving from cache
  264. * @param bool $disableError if true, disables error message
  265. *
  266. * @return mixed
  267. *
  268. * @todo DatabaseInterface::getTablesFull needs to be merged
  269. * somehow into this class or at least better documented
  270. */
  271. public function getStatusInfo(
  272. $info = null,
  273. $forceRead = false,
  274. $disableError = false
  275. ) {
  276. $db = $this->dbName;
  277. $table = $this->name;
  278. if (! empty($_SESSION['is_multi_query'])) {
  279. $disableError = true;
  280. }
  281. $cachedResult = $this->dbi->getCache()->getCachedTableContent([$db, $table]);
  282. // sometimes there is only one entry (ExactRows) so
  283. // we have to get the table's details
  284. if ($cachedResult === null || $forceRead || count($cachedResult) === 1) {
  285. $this->dbi->getTablesFull($db, $table);
  286. $cachedResult = $this->dbi->getCache()->getCachedTableContent([$db, $table]);
  287. }
  288. if ($cachedResult === null) {
  289. // happens when we enter the table creation dialog
  290. // or when we really did not get any status info, for example
  291. // when $table === 'TABLE_NAMES' after the user tried SHOW TABLES
  292. return '';
  293. }
  294. if ($info === null) {
  295. return $cachedResult;
  296. }
  297. // array_key_exists allows for null values
  298. if (! array_key_exists($info, $cachedResult)) {
  299. if (! $disableError) {
  300. trigger_error(
  301. __('Unknown table status:') . ' ' . $info,
  302. E_USER_WARNING
  303. );
  304. }
  305. return false;
  306. }
  307. return $this->dbi->getCache()->getCachedTableContent([$db, $table, $info]);
  308. }
  309. /**
  310. * Returns the Table storage Engine for current table.
  311. *
  312. * @return string Return storage engine info if it is set for
  313. * the selected table else return blank.
  314. */
  315. public function getStorageEngine(): string
  316. {
  317. $tableStorageEngine = $this->getStatusInfo('ENGINE', false, true);
  318. if ($tableStorageEngine === false) {
  319. return '';
  320. }
  321. return strtoupper((string) $tableStorageEngine);
  322. }
  323. /**
  324. * Returns the comments for current table.
  325. *
  326. * @return string Return comment info if it is set for the selected table or return blank.
  327. */
  328. public function getComment()
  329. {
  330. $tableComment = $this->getStatusInfo('TABLE_COMMENT', false, true);
  331. if ($tableComment === false) {
  332. return '';
  333. }
  334. return $tableComment;
  335. }
  336. /**
  337. * Returns the collation for current table.
  338. *
  339. * @return string Return blank if collation is empty else return the collation info from table info.
  340. */
  341. public function getCollation()
  342. {
  343. $tableCollation = $this->getStatusInfo('TABLE_COLLATION', false, true);
  344. if ($tableCollation === false) {
  345. return '';
  346. }
  347. return $tableCollation;
  348. }
  349. /**
  350. * Returns the info about no of rows for current table.
  351. *
  352. * @return int Return no of rows info if it is not null for the selected table or return 0.
  353. */
  354. public function getNumRows()
  355. {
  356. $tableNumRowInfo = $this->getStatusInfo('TABLE_ROWS', false, true);
  357. if ($tableNumRowInfo === false) {
  358. $tableNumRowInfo = $this->dbi->getTable($this->dbName, $GLOBALS['showtable']['Name'])
  359. ->countRecords(true);
  360. }
  361. return $tableNumRowInfo ?: 0;
  362. }
  363. /**
  364. * Returns the Row format for current table.
  365. *
  366. * @return string Return table row format info if it is set for the selected table or return blank.
  367. */
  368. public function getRowFormat()
  369. {
  370. $tableRowFormat = $this->getStatusInfo('ROW_FORMAT', false, true);
  371. if ($tableRowFormat === false) {
  372. return '';
  373. }
  374. return $tableRowFormat;
  375. }
  376. /**
  377. * Returns the auto increment option for current table.
  378. *
  379. * @return int Return auto increment info if it is set for the selected table or return blank.
  380. */
  381. public function getAutoIncrement()
  382. {
  383. $tableAutoIncrement = $this->getStatusInfo('AUTO_INCREMENT', false, true);
  384. return $tableAutoIncrement ?? '';
  385. }
  386. /**
  387. * Returns the array for CREATE statement for current table.
  388. *
  389. * @return array Return options array info if it is set for the selected table or return blank.
  390. */
  391. public function getCreateOptions()
  392. {
  393. $tableOptions = $this->getStatusInfo('CREATE_OPTIONS', false, true);
  394. $createOptionsTmp = empty($tableOptions) ? [] : explode(' ', $tableOptions);
  395. $createOptions = [];
  396. // export create options by its name as variables into global namespace
  397. // f.e. pack_keys=1 becomes available as $pack_keys with value of '1'
  398. // unset($pack_keys);
  399. foreach ($createOptionsTmp as $eachCreateOption) {
  400. $eachCreateOption = explode('=', $eachCreateOption);
  401. if (! isset($eachCreateOption[1])) {
  402. continue;
  403. }
  404. // ensure there is no ambiguity for PHP 5 and 7
  405. $createOptions[$eachCreateOption[0]] = $eachCreateOption[1];
  406. }
  407. // we need explicit DEFAULT value here (different from '0')
  408. $hasPackKeys = isset($createOptions['pack_keys']) && strlen($createOptions['pack_keys']) > 0;
  409. $createOptions['pack_keys'] = $hasPackKeys ? $createOptions['pack_keys'] : 'DEFAULT';
  410. return $createOptions;
  411. }
  412. /**
  413. * generates column specification for ALTER or CREATE TABLE syntax
  414. *
  415. * @param string $name name
  416. * @param string $type type ('INT', 'VARCHAR', 'BIT', ...)
  417. * @param string $length length ('2', '5,2', '', ...)
  418. * @param string $attribute attribute
  419. * @param string $collation collation
  420. * @param bool|string $null with 'NULL' or 'NOT NULL'
  421. * @param string $defaultType whether default is CURRENT_TIMESTAMP,
  422. * NULL, NONE, USER_DEFINED, UUID
  423. * @param string $defaultValue default value for USER_DEFINED
  424. * default type
  425. * @param string $extra 'AUTO_INCREMENT'
  426. * @param string $comment field comment
  427. * @param string $virtuality virtuality of the column
  428. * @param string $expression expression for the virtual column
  429. * @param string $moveTo new position for column
  430. * @param array $columnsWithIndex Fields having PRIMARY or UNIQUE KEY indexes
  431. * @param string $oldColumnName Old column name
  432. *
  433. * @return string field specification
  434. *
  435. * @todo move into class PMA_Column
  436. * @todo on the interface, some js to clear the default value when the
  437. * default current_timestamp is checked
  438. */
  439. public static function generateFieldSpec(
  440. $name,
  441. string $type,
  442. string $length = '',
  443. $attribute = '',
  444. $collation = '',
  445. $null = false,
  446. $defaultType = 'USER_DEFINED',
  447. $defaultValue = '',
  448. $extra = '',
  449. $comment = '',
  450. $virtuality = '',
  451. $expression = '',
  452. $moveTo = '',
  453. $columnsWithIndex = null,
  454. $oldColumnName = null
  455. ) {
  456. global $dbi;
  457. $strLength = strlen($length);
  458. $isTimestamp = mb_stripos($type, 'TIMESTAMP') !== false;
  459. $query = Util::backquote($name) . ' ' . $type;
  460. // allow the possibility of a length for TIME, DATETIME and TIMESTAMP
  461. // (will work on MySQL >= 5.6.4)
  462. //
  463. // MySQL permits a non-standard syntax for FLOAT and DOUBLE,
  464. // see https://dev.mysql.com/doc/refman/5.5/en/floating-point-types.html
  465. $pattern = '@^(DATE|TINYBLOB|TINYTEXT|BLOB|TEXT|'
  466. . 'MEDIUMBLOB|MEDIUMTEXT|LONGBLOB|LONGTEXT|SERIAL|BOOLEAN|UUID|JSON)$@i';
  467. if (
  468. $strLength !== 0
  469. && ! preg_match($pattern, $type)
  470. && Compatibility::isIntegersSupportLength($type, $length, $dbi)
  471. ) {
  472. // Note: The variable $length here can contain several other things
  473. // besides length - ENUM/SET value or length of DECIMAL (eg. 12,3)
  474. // so we can't just convert it to integer
  475. $query .= '(' . $length . ')';
  476. }
  477. if ($attribute != '') {
  478. $query .= ' ' . $attribute;
  479. if ($isTimestamp && stripos($attribute, 'TIMESTAMP') !== false && $strLength !== 0) {
  480. $query .= '(' . $length . ')';
  481. }
  482. }
  483. // if column is virtual, check if server type is Mysql as only Mysql server
  484. // supports extra column properties
  485. $isVirtualColMysql = $virtuality && Compatibility::isMySqlOrPerconaDb();
  486. // if column is virtual, check if server type is MariaDB as MariaDB server
  487. // supports no extra virtual column properties except CHARACTER SET for text column types
  488. $isVirtualColMariaDB = $virtuality && Compatibility::isMariaDb();
  489. $matches = preg_match('@^(TINYTEXT|TEXT|MEDIUMTEXT|LONGTEXT|VARCHAR|CHAR|ENUM|SET)$@i', $type);
  490. if (! empty($collation) && $collation !== 'NULL' && $matches) {
  491. $query .= Util::getCharsetQueryPart(
  492. $isVirtualColMariaDB ? (string) preg_replace('~_.+~s', '', $collation) : $collation,
  493. true
  494. );
  495. }
  496. if ($virtuality) {
  497. $query .= ' AS (' . $expression . ') ' . $virtuality;
  498. }
  499. if (! $virtuality || $isVirtualColMysql) {
  500. if ($null !== false) {
  501. if ($null === 'YES') {
  502. $query .= ' NULL';
  503. } else {
  504. $query .= ' NOT NULL';
  505. }
  506. }
  507. if (! $virtuality) {
  508. switch ($defaultType) {
  509. case 'USER_DEFINED':
  510. if ($isTimestamp && $defaultValue === '0') {
  511. // a TIMESTAMP does not accept DEFAULT '0'
  512. // but DEFAULT 0 works
  513. $query .= ' DEFAULT 0';
  514. } elseif (
  515. $isTimestamp
  516. && preg_match(
  517. '/^\'\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d(\.\d{1,6})?\'$/',
  518. (string) $defaultValue
  519. )
  520. ) {
  521. $query .= ' DEFAULT ' . (string) $defaultValue;
  522. } elseif ($type === 'BIT') {
  523. $query .= ' DEFAULT b\''
  524. . preg_replace('/[^01]/', '0', (string) $defaultValue)
  525. . '\'';
  526. } elseif ($type === 'BOOLEAN') {
  527. if (preg_match('/^1|T|TRUE|YES$/i', (string) $defaultValue)) {
  528. $query .= ' DEFAULT TRUE';
  529. } elseif (preg_match('/^0|F|FALSE|NO$/i', $defaultValue)) {
  530. $query .= ' DEFAULT FALSE';
  531. } else {
  532. // Invalid BOOLEAN value
  533. $query .= ' DEFAULT \''
  534. . $dbi->escapeString($defaultValue) . '\'';
  535. }
  536. } elseif ($type === 'BINARY' || $type === 'VARBINARY') {
  537. $query .= ' DEFAULT 0x' . $defaultValue;
  538. } else {
  539. $query .= ' DEFAULT \''
  540. . $dbi->escapeString((string) $defaultValue) . '\'';
  541. }
  542. break;
  543. /** @noinspection PhpMissingBreakStatementInspection */
  544. case 'NULL':
  545. // If user uncheck null checkbox and not change default value null,
  546. // default value will be ignored.
  547. if ($null !== false && $null !== 'YES') {
  548. break;
  549. }
  550. // else fall-through intended, no break here
  551. case 'CURRENT_TIMESTAMP':
  552. case 'current_timestamp()':
  553. $query .= ' DEFAULT ' . $defaultType;
  554. if (
  555. $strLength !== 0
  556. && $isTimestamp
  557. && $defaultType !== 'NULL' // Not to be added in case of NULL
  558. ) {
  559. $query .= '(' . $length . ')';
  560. }
  561. break;
  562. case 'UUID':
  563. case 'uuid()':
  564. $query .= ' DEFAULT uuid()';
  565. break;
  566. case 'NONE':
  567. default:
  568. break;
  569. }
  570. }
  571. if (! empty($extra)) {
  572. if ($virtuality) {
  573. $extra = trim((string) preg_replace('~^\s*AUTO_INCREMENT\s*~is', ' ', $extra));
  574. }
  575. $query .= ' ' . $extra;
  576. }
  577. }
  578. if (! empty($comment)) {
  579. $query .= " COMMENT '" . $dbi->escapeString($comment) . "'";
  580. }
  581. // move column
  582. if ($moveTo === '-first') { // dash can't appear as part of column name
  583. $query .= ' FIRST';
  584. } elseif ($moveTo != '') {
  585. $query .= ' AFTER ' . Util::backquote($moveTo);
  586. }
  587. if (! $virtuality && ! empty($extra)) {
  588. if ($oldColumnName === null) {
  589. if (is_array($columnsWithIndex) && ! in_array($name, $columnsWithIndex)) {
  590. $query .= ', ADD PRIMARY KEY (' . Util::backquote($name) . ')';
  591. }
  592. } else {
  593. if (is_array($columnsWithIndex) && ! in_array($oldColumnName, $columnsWithIndex)) {
  594. $query .= ', ADD PRIMARY KEY (' . Util::backquote($name) . ')';
  595. }
  596. }
  597. }
  598. return $query;
  599. }
  600. /**
  601. * Checks if the number of records in a table is at least equal to
  602. * $min_records
  603. *
  604. * @param int $minRecords Number of records to check for in a table
  605. */
  606. public function checkIfMinRecordsExist($minRecords = 0): bool
  607. {
  608. $checkQuery = 'SELECT ';
  609. $uniqueFields = $this->getUniqueColumns(true, false);
  610. if (count($uniqueFields) > 0) {
  611. $fieldsToSelect = implode(', ', $uniqueFields);
  612. } else {
  613. $indexedCols = $this->getIndexedColumns(true, false);
  614. if (count($indexedCols) > 0) {
  615. $fieldsToSelect = implode(', ', $indexedCols);
  616. } else {
  617. $fieldsToSelect = '*';
  618. }
  619. }
  620. $checkQuery .= $fieldsToSelect
  621. . ' FROM ' . $this->getFullName(true)
  622. . ' LIMIT ' . $minRecords;
  623. $res = $this->dbi->tryQuery($checkQuery);
  624. if ($res !== false) {
  625. $numRecords = $res->numRows();
  626. if ($numRecords >= $minRecords) {
  627. return true;
  628. }
  629. }
  630. return false;
  631. }
  632. /**
  633. * Counts and returns (or displays) the number of records in a table
  634. *
  635. * @param bool $forceExact whether to force an exact count
  636. *
  637. * @return mixed the number of records if "retain" param is true,
  638. * otherwise true
  639. */
  640. public function countRecords($forceExact = false)
  641. {
  642. $isView = $this->isView();
  643. $db = $this->dbName;
  644. $table = $this->name;
  645. if ($this->dbi->getCache()->getCachedTableContent([$db, $table, 'ExactRows']) != null) {
  646. return $this->dbi->getCache()->getCachedTableContent(
  647. [
  648. $db,
  649. $table,
  650. 'ExactRows',
  651. ]
  652. );
  653. }
  654. $rowCount = false;
  655. if (! $forceExact) {
  656. if (($this->dbi->getCache()->getCachedTableContent([$db, $table, 'Rows']) == null) && ! $isView) {
  657. $tmpTables = $this->dbi->getTablesFull($db, $table);
  658. if (isset($tmpTables[$table])) {
  659. $this->dbi->getCache()->cacheTableContent(
  660. [
  661. $db,
  662. $table,
  663. ],
  664. $tmpTables[$table]
  665. );
  666. }
  667. }
  668. if ($this->dbi->getCache()->getCachedTableContent([$db, $table, 'Rows']) != null) {
  669. $rowCount = $this->dbi->getCache()->getCachedTableContent(
  670. [
  671. $db,
  672. $table,
  673. 'Rows',
  674. ]
  675. );
  676. } else {
  677. $rowCount = false;
  678. }
  679. }
  680. // for a VIEW, $row_count is always false at this point
  681. if ($rowCount !== false && $rowCount >= $GLOBALS['cfg']['MaxExactCount']) {
  682. return $rowCount;
  683. }
  684. if (! $isView) {
  685. $rowCount = $this->dbi->fetchValue(
  686. 'SELECT COUNT(*) FROM ' . Util::backquote($db) . '.'
  687. . Util::backquote($table)
  688. );
  689. } else {
  690. // For complex views, even trying to get a partial record
  691. // count could bring down a server, so we offer an
  692. // alternative: setting MaxExactCountViews to 0 will bypass
  693. // completely the record counting for views
  694. if ($GLOBALS['cfg']['MaxExactCountViews'] == 0) {
  695. $rowCount = false;
  696. } else {
  697. // Counting all rows of a VIEW could be too long,
  698. // so use a LIMIT clause.
  699. // Use try_query because it can fail (when a VIEW is
  700. // based on a table that no longer exists)
  701. $result = $this->dbi->tryQuery(
  702. 'SELECT 1 FROM ' . Util::backquote($db) . '.'
  703. . Util::backquote($table) . ' LIMIT '
  704. . $GLOBALS['cfg']['MaxExactCountViews']
  705. );
  706. if ($result) {
  707. $rowCount = $result->numRows();
  708. }
  709. }
  710. }
  711. if ($rowCount) {
  712. $this->dbi->getCache()->cacheTableContent([$db, $table, 'ExactRows'], $rowCount);
  713. }
  714. return $rowCount;
  715. }
  716. /**
  717. * Generates column specification for ALTER syntax
  718. *
  719. * @see Table::generateFieldSpec()
  720. *
  721. * @param string $oldcol old column name
  722. * @param string $newcol new column name
  723. * @param string $type type ('INT', 'VARCHAR', 'BIT', ...)
  724. * @param string $length length ('2', '5,2', '', ...)
  725. * @param string $attribute attribute
  726. * @param string $collation collation
  727. * @param bool|string $null with 'NULL' or 'NOT NULL'
  728. * @param string $defaultType whether default is CURRENT_TIMESTAMP,
  729. * NULL, NONE, USER_DEFINED
  730. * @param string $defaultValue default value for USER_DEFINED default
  731. * type
  732. * @param string $extra 'AUTO_INCREMENT'
  733. * @param string $comment field comment
  734. * @param string $virtuality virtuality of the column
  735. * @param string $expression expression for the virtual column
  736. * @param string $moveTo new position for column
  737. * @param array $columnsWithIndex Fields having PRIMARY or UNIQUE KEY indexes
  738. *
  739. * @return string field specification
  740. */
  741. public static function generateAlter(
  742. $oldcol,
  743. $newcol,
  744. $type,
  745. $length,
  746. $attribute,
  747. $collation,
  748. $null,
  749. $defaultType,
  750. $defaultValue,
  751. $extra,
  752. $comment,
  753. $virtuality,
  754. $expression,
  755. $moveTo,
  756. $columnsWithIndex = null
  757. ) {
  758. return Util::backquote($oldcol) . ' '
  759. . self::generateFieldSpec(
  760. $newcol,
  761. $type,
  762. $length,
  763. $attribute,
  764. $collation,
  765. $null,
  766. $defaultType,
  767. $defaultValue,
  768. $extra,
  769. $comment,
  770. $virtuality,
  771. $expression,
  772. $moveTo,
  773. $columnsWithIndex,
  774. $oldcol
  775. );
  776. }
  777. /**
  778. * Inserts existing entries in a PMA_* table by reading a value from an old
  779. * entry
  780. *
  781. * @param string $work The array index, which Relation feature to check ('relwork', 'commwork', ...)
  782. * @param string $table The array index, which PMA-table to update ('bookmark', 'relation', ...)
  783. * @param array $getFields Which fields will be SELECT'ed from the old entry
  784. * @param array $whereFields Which fields will be used for the WHERE query (array('FIELDNAME' => 'FIELDVALUE'))
  785. * @param array $newFields Which fields will be used as new VALUES. These are the important keys which differ
  786. * from the old entry (array('FIELDNAME' => 'NEW FIELDVALUE'))
  787. *
  788. * @return int|bool
  789. */
  790. public static function duplicateInfo(
  791. $work,
  792. $table,
  793. array $getFields,
  794. array $whereFields,
  795. array $newFields
  796. ) {
  797. global $dbi;
  798. $relation = new Relation($dbi);
  799. $relationParameters = $relation->getRelationParameters();
  800. $relationParams = $relationParameters->toArray();
  801. $lastId = -1;
  802. if (! isset($relationParams[$work], $relationParams[$table]) || ! $relationParams[$work]) {
  803. return true;
  804. }
  805. $selectParts = [];
  806. $rowFields = [];
  807. foreach ($getFields as $getField) {
  808. $selectParts[] = Util::backquote($getField);
  809. $rowFields[$getField] = 'cc';
  810. }
  811. $whereParts = [];
  812. foreach ($whereFields as $where => $value) {
  813. $whereParts[] = Util::backquote($where) . ' = \''
  814. . $dbi->escapeString((string) $value) . '\'';
  815. }
  816. $newParts = [];
  817. $newValueParts = [];
  818. foreach ($newFields as $where => $value) {
  819. $newParts[] = Util::backquote($where);
  820. $newValueParts[] = $dbi->escapeString((string) $value);
  821. }
  822. $tableCopyQuery = '
  823. SELECT ' . implode(', ', $selectParts) . '
  824. FROM ' . Util::backquote($relationParameters->db) . '.'
  825. . Util::backquote((string) $relationParams[$table]) . '
  826. WHERE ' . implode(' AND ', $whereParts);
  827. // must use DatabaseInterface::QUERY_BUFFERED here, since we execute
  828. // another query inside the loop
  829. $tableCopyRs = $dbi->queryAsControlUser($tableCopyQuery);
  830. foreach ($tableCopyRs as $tableCopyRow) {
  831. $valueParts = [];
  832. foreach ($tableCopyRow as $key => $val) {
  833. if (! isset($rowFields[$key]) || $rowFields[$key] != 'cc') {
  834. continue;
  835. }
  836. $valueParts[] = $dbi->escapeString($val);
  837. }
  838. $newTableQuery = 'INSERT IGNORE INTO '
  839. . Util::backquote($relationParameters->db)
  840. . '.' . Util::backquote((string) $relationParams[$table])
  841. . ' (' . implode(', ', $selectParts) . ', '
  842. . implode(', ', $newParts) . ') VALUES (\''
  843. . implode('\', \'', $valueParts) . '\', \''
  844. . implode('\', \'', $newValueParts) . '\')';
  845. $dbi->queryAsControlUser($newTableQuery);
  846. $lastId = $dbi->insertId();
  847. }
  848. return $lastId;
  849. }
  850. /**
  851. * Copies or renames table
  852. *
  853. * @param string $sourceDb source database
  854. * @param string $sourceTable source table
  855. * @param string|null $targetDb target database
  856. * @param string $targetTable target table
  857. * @param string $what what to be moved or copied (data, dataonly)
  858. * @param bool $move whether to move
  859. * @param string $mode mode
  860. */
  861. public static function moveCopy(
  862. $sourceDb,
  863. $sourceTable,
  864. ?string $targetDb,
  865. $targetTable,
  866. $what,
  867. $move,
  868. $mode,
  869. bool $addDropIfExists
  870. ): bool {
  871. global $errorUrl, $dbi;
  872. $relation = new Relation($dbi);
  873. // Try moving the tables directly, using native `RENAME` statement.
  874. if ($move && $what === 'data') {
  875. $tbl = new Table($sourceTable, $sourceDb);
  876. if ($tbl->rename($targetTable, $targetDb)) {
  877. $GLOBALS['message'] = $tbl->getLastMessage();
  878. return true;
  879. }
  880. }
  881. // Setting required export settings.
  882. $GLOBALS['sql_backquotes'] = 1;
  883. $GLOBALS['asfile'] = 1;
  884. // Ensuring the target database is valid.
  885. if (! $GLOBALS['dblist']->databases->exists($sourceDb, $targetDb)) {
  886. if (! $GLOBALS['dblist']->databases->exists($sourceDb)) {
  887. $GLOBALS['message'] = Message::rawError(
  888. sprintf(
  889. __('Source database `%s` was not found!'),
  890. htmlspecialchars($sourceDb)
  891. )
  892. );
  893. }
  894. if (! $GLOBALS['dblist']->databases->exists($targetDb)) {
  895. $GLOBALS['message'] = Message::rawError(
  896. sprintf(
  897. __('Target database `%s` was not found!'),
  898. htmlspecialchars((string) $targetDb)
  899. )
  900. );
  901. }
  902. return false;
  903. }
  904. /**
  905. * The full name of source table, quoted.
  906. *
  907. * @var string $source
  908. */
  909. $source = Util::backquote($sourceDb)
  910. . '.' . Util::backquote($sourceTable);
  911. // If the target database is not specified, the operation is taking
  912. // place in the same database.
  913. if (! isset($targetDb) || strlen($targetDb) === 0) {
  914. $targetDb = $sourceDb;
  915. }
  916. // Selecting the database could avoid some problems with replicated
  917. // databases, when moving table from replicated one to not replicated one.
  918. $dbi->selectDb($targetDb);
  919. /**
  920. * The full name of target table, quoted.
  921. *
  922. * @var string $target
  923. */
  924. $target = Util::backquote($targetDb)
  925. . '.' . Util::backquote($targetTable);
  926. // No table is created when this is a data-only operation.
  927. if ($what !== 'dataonly') {
  928. /**
  929. * Instance used for exporting the current structure of the table.
  930. *
  931. * @var ExportSql $exportSqlPlugin
  932. */
  933. $exportSqlPlugin = Plugins::getPlugin('export', 'sql', [
  934. 'export_type' => 'table',
  935. 'single_table' => false,
  936. ]);
  937. $noConstraintsComments = true;
  938. $GLOBALS['sql_constraints_query'] = '';
  939. // set the value of global sql_auto_increment variable
  940. if (isset($_POST['sql_auto_increment'])) {
  941. $GLOBALS['sql_auto_increment'] = $_POST['sql_auto_increment'];
  942. }
  943. $isView = (new Table($sourceTable, $sourceDb, $GLOBALS['dbi']))->isView();
  944. /**
  945. * The old structure of the table..
  946. */
  947. $sqlStructure = $exportSqlPlugin->getTableDef(
  948. $sourceDb,
  949. $sourceTable,
  950. "\n",
  951. $errorUrl,
  952. false,
  953. false,
  954. $isView
  955. );
  956. unset($noConstraintsComments);
  957. // -----------------------------------------------------------------
  958. // Phase 0: Preparing structures used.
  959. /**
  960. * The destination where the table is moved or copied to.
  961. */
  962. $destination = new Expression($targetDb, $targetTable, '');
  963. // Find server's SQL mode so the builder can generate correct
  964. // queries.
  965. // One of the options that alters the behaviour is `ANSI_QUOTES`.
  966. Context::setMode((string) $dbi->fetchValue('SELECT @@sql_mode'));
  967. // -----------------------------------------------------------------
  968. // Phase 1: Dropping existent element of the same name (if exists
  969. // and required).
  970. if ($addDropIfExists) {
  971. /**
  972. * Drop statement used for building the query.
  973. */
  974. $statement = new DropStatement();
  975. $tbl = new Table($targetTable, $targetDb);
  976. $statement->options = new OptionsArray(
  977. [
  978. $tbl->isView() ? 'VIEW' : 'TABLE',
  979. 'IF EXISTS',
  980. ]
  981. );
  982. $statement->fields = [$destination];
  983. // Building the query.
  984. $dropQuery = $statement->build() . ';';
  985. // Executing it.
  986. $dbi->query($dropQuery);
  987. $GLOBALS['sql_query'] .= "\n" . $dropQuery;
  988. // If an existing table gets deleted, maintain any entries for
  989. // the PMA_* tables.
  990. $maintainRelations = true;
  991. }
  992. // -----------------------------------------------------------------
  993. // Phase 2: Generating the new query of this structure.
  994. /**
  995. * The parser responsible for parsing the old queries.
  996. */
  997. $parser = new Parser($sqlStructure);
  998. if (! empty($parser->statements[0])) {
  999. /**
  1000. * The CREATE statement of this structure.
  1001. *
  1002. * @var CreateStatement $statement
  1003. */
  1004. $statement = $parser->statements[0];
  1005. // Changing the destination.
  1006. $statement->name = $destination;
  1007. // Building back the query.
  1008. $sqlStructure = $statement->build() . ';';
  1009. // This is to avoid some issues when renaming databases with views
  1010. // See: https://github.com/phpmyadmin/phpmyadmin/issues/16422
  1011. if ($move) {
  1012. $dbi->selectDb($targetDb);
  1013. }
  1014. // Executing it
  1015. $dbi->query($sqlStructure);
  1016. $GLOBALS['sql_query'] .= "\n" . $sqlStructure;
  1017. }
  1018. // -----------------------------------------------------------------
  1019. // Phase 3: Adding constraints.
  1020. // All constraint names are removed because they must be unique.
  1021. if (($move || isset($GLOBALS['add_constraints'])) && ! empty($GLOBALS['sql_constraints_query'])) {
  1022. $parser = new Parser($GLOBALS['sql_constraints_query']);
  1023. /**
  1024. * The ALTER statement that generates the constraints.
  1025. *
  1026. * @var AlterStatement $statement
  1027. */
  1028. $statement = $parser->statements[0];
  1029. // Changing the altered table to the destination.
  1030. $statement->table = $destination;
  1031. // Removing the name of the constraints.
  1032. foreach ($statement->altered as $altered) {
  1033. // All constraint names are removed because they must be unique.
  1034. if (! $altered->options->has('CONSTRAINT')) {
  1035. continue;
  1036. }
  1037. $altered->field = null;
  1038. }
  1039. // Building back the query.
  1040. $GLOBALS['sql_constraints_query'] = $statement->build() . ';';
  1041. // Executing it.
  1042. if ($mode === 'one_table') {
  1043. $dbi->query($GLOBALS['sql_constraints_query']);
  1044. }
  1045. $GLOBALS['sql_query'] .= "\n" . $GLOBALS['sql_constraints_query'];
  1046. if ($mode === 'one_table') {
  1047. unset($GLOBALS['sql_constraints_query']);
  1048. }
  1049. }
  1050. // -----------------------------------------------------------------
  1051. // Phase 4: Adding indexes.
  1052. // View phase 3.
  1053. if (! empty($GLOBALS['sql_indexes'])) {
  1054. $parser = new Parser($GLOBALS['sql_indexes']);
  1055. $GLOBALS['sql_indexes'] = '';
  1056. /**
  1057. * The ALTER statement that generates the indexes.
  1058. *
  1059. * @var AlterStatement $statement
  1060. */
  1061. foreach ($parser->statements as $statement) {
  1062. // Changing the altered table to the destination.
  1063. $statement->table = $destination;
  1064. // Removing the name of the constraints.
  1065. foreach ($statement->altered as $altered) {
  1066. // All constraint names are removed because they must be unique.
  1067. if (! $altered->options->has('CONSTRAINT')) {
  1068. continue;
  1069. }
  1070. $altered->field = null;
  1071. }
  1072. // Building back the query.
  1073. $sqlIndex = $statement->build() . ';';
  1074. // Executing it.
  1075. if ($mode === 'one_table' || $mode === 'db_copy') {
  1076. $dbi->query($sqlIndex);
  1077. }
  1078. $GLOBALS['sql_indexes'] .= $sqlIndex;
  1079. }
  1080. $GLOBALS['sql_query'] .= "\n" . $GLOBALS['sql_indexes'];
  1081. if ($mode === 'one_table' || $mode === 'db_copy') {
  1082. unset($GLOBALS['sql_indexes']);
  1083. }
  1084. }
  1085. // -----------------------------------------------------------------
  1086. // Phase 5: Adding AUTO_INCREMENT.
  1087. if (! empty($GLOBALS['sql_auto_increments']) && ($mode === 'one_table' || $mode === 'db_copy')) {
  1088. $parser = new Parser($GLOBALS['sql_auto_increments']);
  1089. /**
  1090. * The ALTER statement that alters the AUTO_INCREMENT value.
  1091. */
  1092. $statement = $parser->statements[0];
  1093. if ($statement instanceof AlterStatement) {
  1094. // Changing the altered table to the destination.
  1095. $statement->table = $destination;
  1096. // Building back the query.
  1097. $GLOBALS['sql_auto_increments'] = $statement->build() . ';';
  1098. // Executing it.
  1099. $dbi->query($GLOBALS['sql_auto_increments']);
  1100. $GLOBALS['sql_query'] .= "\n" . $GLOBALS['sql_auto_increments'];
  1101. }
  1102. unset($GLOBALS['sql_auto_increments']);
  1103. }
  1104. } else {
  1105. $GLOBALS['sql_query'] = '';
  1106. }
  1107. $table = new Table($targetTable, $targetDb);
  1108. // Copy the data unless this is a VIEW
  1109. if (($what === 'data' || $what === 'dataonly') && ! $table->isView()) {
  1110. $sqlSetMode = "SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO'";
  1111. $dbi->query($sqlSetMode);
  1112. $GLOBALS['sql_query'] .= "\n\n" . $sqlSetMode . ';';
  1113. $oldTable = new Table($sourceTable, $sourceDb);
  1114. $nonGeneratedCols = $oldTable->getNonGeneratedColumns(true);
  1115. if (count($nonGeneratedCols) > 0) {
  1116. $sqlInsertData = 'INSERT INTO ' . $target . '('
  1117. . implode(', ', $nonGeneratedCols)
  1118. . ') SELECT ' . implode(', ', $nonGeneratedCols)
  1119. . ' FROM ' . $source;
  1120. $dbi->query($sqlInsertData);
  1121. $GLOBALS['sql_query'] .= "\n\n" . $sqlInsertData . ';';
  1122. }
  1123. }
  1124. $relationParameters = $relation->getRelationParameters();
  1125. // Drops old table if the user has requested to move it
  1126. if ($move) {
  1127. // This could avoid some problems with replicated databases, when
  1128. // moving table from replicated one to not replicated one
  1129. $dbi->selectDb($sourceDb);
  1130. $sourceTableObj = new Table($sourceTable, $sourceDb);
  1131. if ($sourceTableObj->isView()) {
  1132. $sqlDropQuery = 'DROP VIEW';
  1133. } else {
  1134. $sqlDropQuery = 'DROP TABLE';
  1135. }
  1136. $sqlDropQuery .= ' ' . $source;
  1137. $dbi->query($sqlDropQuery);
  1138. // Rename table in configuration storage
  1139. $relation->renameTable($sourceDb, $targetDb, $sourceTable, $targetTable);
  1140. $GLOBALS['sql_query'] .= "\n\n" . $sqlDropQuery . ';';
  1141. return true;
  1142. }
  1143. // we are copying
  1144. // Create new entries as duplicates from old PMA DBs
  1145. if ($what === 'dataonly' || isset($maintainRelations)) {
  1146. return true;
  1147. }
  1148. if ($relationParameters->columnCommentsFeature !== null) {
  1149. // Get all comments and MIME-Types for current table
  1150. $commentsCopyRs = $dbi->queryAsControlUser(
  1151. 'SELECT column_name, comment'
  1152. . ($relationParameters->browserTransformationFeature !== null
  1153. ? ', mimetype, transformation, transformation_options'
  1154. : '')
  1155. . ' FROM '
  1156. . Util::backquote($relationParameters->columnCommentsFeature->database)
  1157. . '.'
  1158. . Util::backquote($relationParameters->columnCommentsFeature->columnInfo)
  1159. . ' WHERE '
  1160. . ' db_name = \''
  1161. . $dbi->escapeString($sourceDb) . '\''
  1162. . ' AND '
  1163. . ' table_name = \''
  1164. . $dbi->escapeString((string) $sourceTable) . '\''
  1165. );
  1166. // Write every comment as new copied entry. [MIME]
  1167. foreach ($commentsCopyRs as $commentsCopyRow) {
  1168. $newCommentQuery = 'REPLACE INTO '
  1169. . Util::backquote($relationParameters->columnCommentsFeature->database)
  1170. . '.' . Util::backquote($relationParameters->columnCommentsFeature->columnInfo)
  1171. . ' (db_name, table_name, column_name, comment'
  1172. . ($relationParameters->browserTransformationFeature !== null
  1173. ? ', mimetype, transformation, transformation_options'
  1174. : '')
  1175. . ') VALUES(\'' . $dbi->escapeString($targetDb)
  1176. . '\',\'' . $dbi->escapeString($targetTable) . '\',\''
  1177. . $dbi->escapeString($commentsCopyRow['column_name'])
  1178. . '\',\''
  1179. . $dbi->escapeString($commentsCopyRow['comment'])
  1180. . '\''
  1181. . ($relationParameters->browserTransformationFeature !== null
  1182. ? ',\'' . $dbi->escapeString($commentsCopyRow['mimetype'])
  1183. . '\',\'' . $dbi->escapeString($commentsCopyRow['transformation'])
  1184. . '\',\'' . $dbi->escapeString($commentsCopyRow['transformation_options'])
  1185. . '\''
  1186. : '')
  1187. . ')';
  1188. $dbi->queryAsControlUser($newCommentQuery);
  1189. }
  1190. unset($commentsCopyRs);
  1191. }
  1192. // duplicating the bookmarks must not be done here, but
  1193. // just once per db
  1194. $getFields = ['display_field'];
  1195. $whereFields = [
  1196. 'db_name' => $sourceDb,
  1197. 'table_name' => $sourceTable,
  1198. ];
  1199. $newFields = [
  1200. 'db_name' => $targetDb,
  1201. 'table_name' => $targetTable,
  1202. ];
  1203. self::duplicateInfo('displaywork', 'table_info', $getFields, $whereFields, $newFields);
  1204. /**
  1205. * @todo revise this code when we support cross-db relations
  1206. */
  1207. $getFields = [
  1208. 'master_field',
  1209. 'foreign_table',
  1210. 'foreign_field',
  1211. ];
  1212. $whereFields = [
  1213. 'master_db' => $sourceDb,
  1214. 'master_table' => $sourceTable,
  1215. ];
  1216. $newFields = [
  1217. 'master_db' => $targetDb,
  1218. 'foreign_db' => $targetDb,
  1219. 'master_table' => $targetTable,
  1220. ];
  1221. self::duplicateInfo('relwork', 'relation', $getFields, $whereFields, $newFields);
  1222. $getFields = [
  1223. 'foreign_field',
  1224. 'master_table',
  1225. 'master_field',
  1226. ];
  1227. $whereFields = [
  1228. 'foreign_db' => $sourceDb,
  1229. 'foreign_table' => $sourceTable,
  1230. ];
  1231. $newFields = [
  1232. 'master_db' => $targetDb,
  1233. 'foreign_db' => $targetDb,
  1234. 'foreign_table' => $targetTable,
  1235. ];
  1236. self::duplicateInfo('relwork', 'relation', $getFields, $whereFields, $newFields);
  1237. return true;
  1238. }
  1239. /**
  1240. * checks if given name is a valid table name,
  1241. * currently if not empty, trailing spaces, '.', '/' and '\'
  1242. *
  1243. * @see https://dev.mysql.com/doc/refman/5.0/en/legal-names.html
  1244. *
  1245. * @param string $tableName name to check
  1246. * @param bool $isBackquoted whether this name is used inside backquotes or not
  1247. *
  1248. * @todo add check for valid chars in filename on current system/os
  1249. */
  1250. public static function isValidName($tableName, $isBackquoted = false): bool
  1251. {
  1252. if ($tableName !== rtrim((string) $tableName)) {
  1253. // trailing spaces not allowed even in backquotes
  1254. return false;
  1255. }
  1256. if (strlen($tableName) === 0) {
  1257. // zero length
  1258. return false;
  1259. }
  1260. if (! $isBackquoted && $tableName !== trim($tableName)) {
  1261. // spaces at the start or in between only allowed inside backquotes
  1262. return false;
  1263. }
  1264. if (! $isBackquoted && preg_match('/^[a-zA-Z0-9_$]+$/', $tableName)) {
  1265. // only allow the above regex in unquoted identifiers
  1266. // see : https://dev.mysql.com/doc/refman/5.7/en/identifiers.html
  1267. return true;
  1268. }
  1269. // If backquoted, all characters should be allowed (except w/ trailing spaces).
  1270. return $isBackquoted;
  1271. }
  1272. /**
  1273. * renames table
  1274. *
  1275. * @param string $newName new table name
  1276. * @param string $newDb new database name
  1277. */
  1278. public function rename($newName, $newDb = null): bool
  1279. {
  1280. if ($this->dbi->getLowerCaseNames() === '1') {
  1281. $newName = strtolower($newName);
  1282. }
  1283. if ($newDb !== null && $newDb !== $this->getDbName()) {
  1284. // Ensure the target is valid
  1285. if (! $GLOBALS['dblist']->databases->exists($newDb)) {
  1286. $this->errors[] = __('Invalid database:') . ' ' . $newDb;
  1287. return false;
  1288. }
  1289. } else {
  1290. $newDb = $this->getDbName();
  1291. }
  1292. $newTable = new Table($newName, $newDb);
  1293. if ($this->getFullName() === $newTable->getFullName()) {
  1294. return true;
  1295. }
  1296. // Allow whitespaces (not trailing) in $new_name,
  1297. // since we are using $backquoted in getting the fullName of table
  1298. // below to be used in the query
  1299. if (! self::isValidName($newName, true)) {
  1300. $this->errors[] = __('Invalid table name:') . ' '
  1301. . $newTable->getFullName();
  1302. return false;
  1303. }
  1304. // If the table is moved to a different database drop its triggers first
  1305. $triggers = $this->dbi->getTriggers(
  1306. $this->getDbName(),
  1307. $this->getName(),
  1308. ''
  1309. );
  1310. $handleTriggers = $this->getDbName() != $newDb && $triggers;
  1311. if ($handleTriggers) {
  1312. foreach ($triggers as $trigger) {
  1313. $sql = 'DROP TRIGGER IF EXISTS '
  1314. . Util::backquote($this->getDbName())
  1315. . '.' . Util::backquote($trigger['name']) . ';';
  1316. $this->dbi->query($sql);
  1317. }
  1318. }
  1319. /*
  1320. * tested also for a view, in MySQL 5.0.92, 5.1.55 and 5.5.13
  1321. */
  1322. $GLOBALS['sql_query'] = '
  1323. RENAME TABLE ' . $this->getFullName(true) . '
  1324. TO ' . $newTable->getFullName(true) . ';';
  1325. // I don't think a specific error message for views is necessary
  1326. if ($this->dbi->tryQuery($GLOBALS['sql_query']) === false) {
  1327. $this->errors[] = $this->dbi->getError();
  1328. // Restore triggers in the old database
  1329. if ($handleTriggers) {
  1330. $this->dbi->selectDb($this->getDbName());
  1331. foreach ($triggers as $trigger) {
  1332. $this->dbi->query($trigger['create']);
  1333. }
  1334. }
  1335. return false;
  1336. }
  1337. $oldName = $this->getName();
  1338. $oldDb = $this->getDbName();
  1339. $this->name = $newName;
  1340. $this->dbName = $newDb;
  1341. // Rename table in configuration storage
  1342. $this->relation->renameTable($oldDb, $newDb, $oldName, $newName);
  1343. $this->messages[] = sprintf(
  1344. __('Table %1$s has been renamed to %2$s.'),
  1345. htmlspecialchars($oldName),
  1346. htmlspecialchars($newName)
  1347. );
  1348. return true;
  1349. }
  1350. /**
  1351. * Get all unique columns
  1352. *
  1353. * returns an array with all columns with unique content, in fact these are
  1354. * all columns being single indexed in PRIMARY or UNIQUE
  1355. *
  1356. * e.g.
  1357. * - PRIMARY(id) // id
  1358. * - UNIQUE(name) // name
  1359. * - PRIMARY(fk_id1, fk_id2) // NONE
  1360. * - UNIQUE(x,y) // NONE
  1361. *
  1362. * @param bool $backquoted whether to quote name with backticks ``
  1363. * @param bool $fullName whether to include full name of the table as a prefix
  1364. *
  1365. * @return array
  1366. */
  1367. public function getUniqueColumns($backquoted = true, $fullName = true)
  1368. {
  1369. $sql = QueryGenerator::getTableIndexesSql(
  1370. $this->getDbName(),
  1371. $this->getName(),
  1372. 'Non_unique = 0'
  1373. );
  1374. $uniques = $this->dbi->fetchResult(
  1375. $sql,
  1376. [
  1377. 'Key_name',
  1378. null,
  1379. ],
  1380. 'Column_name'
  1381. );
  1382. $return = [];
  1383. foreach ($uniques as $index) {
  1384. if (count($index) > 1) {
  1385. continue;
  1386. }
  1387. if ($fullName) {
  1388. $possibleColumn = $this->getFullName($backquoted) . '.';
  1389. } else {
  1390. $possibleColumn = '';
  1391. }
  1392. if ($backquoted) {
  1393. $possibleColumn .= Util::backquote($index[0]);
  1394. } else {
  1395. $possibleColumn .= $index[0];
  1396. }
  1397. // a column might have a primary and an unique index on it
  1398. if (in_array($possibleColumn, $return)) {
  1399. continue;
  1400. }
  1401. $return[] = $possibleColumn;
  1402. }
  1403. return $return;
  1404. }
  1405. /**
  1406. * Formats lists of columns
  1407. *
  1408. * returns an array with all columns that make use of an index
  1409. *
  1410. * e.g. index(col1, col2) would return col1, col2
  1411. *
  1412. * @param array $indexed column data
  1413. * @param bool $backquoted whether to quote name with backticks ``
  1414. * @param bool $fullName whether to include full name of the table as a prefix
  1415. *
  1416. * @return array
  1417. */
  1418. private function formatColumns(array $indexed, $backquoted, $fullName)
  1419. {
  1420. $return = [];
  1421. foreach ($indexed as $column) {
  1422. $return[] = ($fullName ? $this->getFullName($backquoted) . '.' : '')
  1423. . ($backquoted ? Util::backquote($column) : $column);
  1424. }
  1425. return $return;
  1426. }
  1427. /**
  1428. * Get all indexed columns
  1429. *
  1430. * returns an array with all columns that make use of an index
  1431. *
  1432. * e.g. index(col1, col2) would return col1, col2
  1433. *
  1434. * @param bool $backquoted whether to quote name with backticks ``
  1435. * @param bool $fullName whether to include full name of the table as a prefix
  1436. *
  1437. * @return array
  1438. */
  1439. public function getIndexedColumns($backquoted = true, $fullName = true)
  1440. {
  1441. $sql = QueryGenerator::getTableIndexesSql(
  1442. $this->getDbName(),
  1443. $this->getName(),
  1444. ''
  1445. );
  1446. $indexed = $this->dbi->fetchResult($sql, 'Column_name', 'Column_name');
  1447. return $this->formatColumns($indexed, $backquoted, $fullName);
  1448. }
  1449. /**
  1450. * Get all columns
  1451. *
  1452. * returns an array with all columns
  1453. *
  1454. * @param bool $backquoted whether to quote name with backticks ``
  1455. * @param bool $fullName whether to include full name of the table as a prefix
  1456. *
  1457. * @return array
  1458. */
  1459. public function getColumns($backquoted = true, $fullName = true)
  1460. {
  1461. $sql = 'SHOW COLUMNS FROM ' . $this->getFullName(true);
  1462. $indexed = $this->dbi->fetchResult($sql, 'Field', 'Field');
  1463. return $this->formatColumns($indexed, $backquoted, $fullName);
  1464. }
  1465. /**
  1466. * Get meta info for fields in table
  1467. *
  1468. * @return FieldMetadata[]
  1469. */
  1470. public function getColumnsMeta(): array
  1471. {
  1472. $moveColumnsSqlQuery = sprintf(
  1473. 'SELECT * FROM %s.%s LIMIT 1',
  1474. Util::backquote($this->dbName),
  1475. Util::backquote($this->name)
  1476. );
  1477. $moveColumnsSqlResult = $this->dbi->tryQuery($moveColumnsSqlQuery);
  1478. if ($moveColumnsSqlResult !== false) {
  1479. return $this->dbi->getFieldsMeta($moveColumnsSqlResult);
  1480. }
  1481. // unsure how to reproduce but it was seen on the reporting server
  1482. return [];
  1483. }
  1484. /**
  1485. * Get non-generated columns in table
  1486. *
  1487. * @param bool $backquoted whether to quote name with backticks ``
  1488. *
  1489. * @return array
  1490. */
  1491. public function getNonGeneratedColumns($backquoted = true)
  1492. {
  1493. $columnsMetaQuery = 'SHOW COLUMNS FROM ' . $this->getFullName(true);
  1494. $ret = [];
  1495. $columnsMetaQueryResult = $this->dbi->fetchResult($columnsMetaQuery);
  1496. foreach ($columnsMetaQueryResult as $column) {
  1497. $value = $column['Field'];
  1498. if ($backquoted === true) {
  1499. $value = Util::backquote($value);
  1500. }
  1501. // If contains GENERATED or VIRTUAL and does not contain DEFAULT_GENERATED
  1502. if (
  1503. (
  1504. str_contains($column['Extra'], 'GENERATED')
  1505. || str_contains($column['Extra'], 'VIRTUAL')
  1506. ) && ! str_contains($column['Extra'], 'DEFAULT_GENERATED')
  1507. ) {
  1508. continue;
  1509. }
  1510. $ret[] = $value;
  1511. }
  1512. return $ret;
  1513. }
  1514. /**
  1515. * Return UI preferences for this table from phpMyAdmin database.
  1516. *
  1517. * @return array
  1518. */
  1519. protected function getUiPrefsFromDb(?UiPreferencesFeature $uiPreferencesFeature)
  1520. {
  1521. if ($uiPreferencesFeature === null) {
  1522. return [];
  1523. }
  1524. // Read from phpMyAdmin database
  1525. $sqlQuery = sprintf(
  1526. 'SELECT `prefs` FROM %s.%s WHERE `username` = \'%s\' AND `db_name` = \'%s\' AND `table_name` = \'%s\'',
  1527. Util::backquote($uiPreferencesFeature->database),
  1528. Util::backquote($uiPreferencesFeature->tableUiPrefs),
  1529. $this->dbi->escapeString($GLOBALS['cfg']['Server']['user']),
  1530. $this->dbi->escapeString($this->dbName),
  1531. $this->dbi->escapeString($this->name)
  1532. );
  1533. $value = $this->dbi->queryAsControlUser($sqlQuery)->fetchValue();
  1534. if (is_string($value)) {
  1535. return json_decode($value, true);
  1536. }
  1537. return [];
  1538. }
  1539. /**
  1540. * Save this table's UI preferences into phpMyAdmin database.
  1541. *
  1542. * @return true|Message
  1543. */
  1544. protected function saveUiPrefsToDb(UiPreferencesFeature $uiPreferencesFeature)
  1545. {
  1546. $table = Util::backquote($uiPreferencesFeature->database) . '.'
  1547. . Util::backquote($uiPreferencesFeature->tableUiPrefs);
  1548. $secureDbName = $this->dbi->escapeString($this->dbName);
  1549. $username = $GLOBALS['cfg']['Server']['user'];
  1550. $sqlQuery = ' REPLACE INTO ' . $table
  1551. . " (username, db_name, table_name, prefs) VALUES ('"
  1552. . $this->dbi->escapeString($username) . "', '" . $secureDbName
  1553. . "', '" . $this->dbi->escapeString($this->name) . "', '"
  1554. . $this->dbi->escapeString((string) json_encode($this->uiprefs)) . "')";
  1555. $success = $this->dbi->tryQuery($sqlQuery, DatabaseInterface::CONNECT_CONTROL);
  1556. if (! $success) {
  1557. $message = Message::error(
  1558. __('Could not save table UI preferences!')
  1559. );
  1560. $message->addMessage(
  1561. Message::rawError($this->dbi->getError(DatabaseInterface::CONNECT_CONTROL)),
  1562. '<br><br>'
  1563. );
  1564. return $message;
  1565. }
  1566. // Remove some old rows in table_uiprefs if it exceeds the configured
  1567. // maximum rows
  1568. $sqlQuery = 'SELECT COUNT(*) FROM ' . $table;
  1569. $rowsCount = (int) $this->dbi->fetchValue($sqlQuery);
  1570. $maxRows = (int) $GLOBALS['cfg']['Server']['MaxTableUiprefs'];
  1571. if ($rowsCount > $maxRows) {
  1572. $numRowsToDelete = $rowsCount - $maxRows;
  1573. $sqlQuery = ' DELETE FROM ' . $table .
  1574. ' ORDER BY last_update ASC' .
  1575. ' LIMIT ' . $numRowsToDelete;
  1576. $success = $this->dbi->tryQuery($sqlQuery, DatabaseInterface::CONNECT_CONTROL);
  1577. if (! $success) {
  1578. $message = Message::error(
  1579. sprintf(
  1580. __(
  1581. 'Failed to cleanup table UI preferences (see $cfg[\'Servers\'][$i][\'MaxTableUiprefs\'] %s)'
  1582. ),
  1583. MySQLDocumentation::showDocumentation('config', 'cfg_Servers_MaxTableUiprefs')
  1584. )
  1585. );
  1586. $message->addMessage(
  1587. Message::rawError($this->dbi->getError(DatabaseInterface::CONNECT_CONTROL)),
  1588. '<br><br>'
  1589. );
  1590. return $message;
  1591. }
  1592. }
  1593. return true;
  1594. }
  1595. /**
  1596. * Loads the UI preferences for this table.
  1597. * If pmadb and table_uiprefs is set, it will load the UI preferences from
  1598. * phpMyAdmin database.
  1599. */
  1600. protected function loadUiPrefs(): void
  1601. {
  1602. $uiPreferencesFeature = $this->relation->getRelationParameters()->uiPreferencesFeature;
  1603. $serverId = $GLOBALS['server'];
  1604. // set session variable if it's still undefined
  1605. if (! isset($_SESSION['tmpval']['table_uiprefs'][$serverId][$this->dbName][$this->name])) {
  1606. // check whether we can get from pmadb
  1607. $uiPrefs = $this->getUiPrefsFromDb($uiPreferencesFeature);
  1608. $_SESSION['tmpval']['table_uiprefs'][$serverId][$this->dbName][$this->name] = $uiPrefs;
  1609. }
  1610. $this->uiprefs =& $_SESSION['tmpval']['table_uiprefs'][$serverId][$this->dbName][$this->name];
  1611. }
  1612. /**
  1613. * Get a property from UI preferences.
  1614. * Return false if the property is not found.
  1615. * Available property:
  1616. * - PROP_SORTED_COLUMN
  1617. * - PROP_COLUMN_ORDER
  1618. * - PROP_COLUMN_VISIB
  1619. *
  1620. * @param string $property property
  1621. *
  1622. * @return mixed
  1623. */
  1624. public function getUiProp($property)
  1625. {
  1626. if (empty($this->uiprefs)) {
  1627. $this->loadUiPrefs();
  1628. }
  1629. // do checking based on property
  1630. if ($property == self::PROP_SORTED_COLUMN) {
  1631. if (! isset($this->uiprefs[$property])) {
  1632. return false;
  1633. }
  1634. if (! isset($_POST['discard_remembered_sort'])) {
  1635. // check if the column name exists in this table
  1636. $tmp = explode(' ', $this->uiprefs[$property]);
  1637. $colname = $tmp[0];
  1638. //remove backquoting from colname
  1639. $colname = str_replace('`', '', $colname);
  1640. //get the available column name without backquoting
  1641. $availColumns = $this->getColumns(false);
  1642. foreach ($availColumns as $eachCol) {
  1643. // check if $each_col ends with $colname
  1644. if (substr_compare($eachCol, $colname, mb_strlen($eachCol) - mb_strlen($colname)) === 0) {
  1645. return $this->uiprefs[$property];
  1646. }
  1647. }
  1648. }
  1649. // remove the property, since it no longer exists in database
  1650. $this->removeUiProp($property);
  1651. return false;
  1652. }
  1653. if ($property == self::PROP_COLUMN_ORDER || $property == self::PROP_COLUMN_VISIB) {
  1654. if ($this->isView() || ! isset($this->uiprefs[$property])) {
  1655. return false;
  1656. }
  1657. // check if the table has not been modified
  1658. if ($this->getStatusInfo('Create_time') == $this->uiprefs['CREATE_TIME']) {
  1659. return array_map('intval', $this->uiprefs[$property]);
  1660. }
  1661. // remove the property, since the table has been modified
  1662. $this->removeUiProp($property);
  1663. return false;
  1664. }
  1665. // default behaviour for other property:
  1666. return $this->uiprefs[$property] ?? false;
  1667. }
  1668. /**
  1669. * Set a property from UI preferences.
  1670. * If pmadb and table_uiprefs is set, it will save the UI preferences to
  1671. * phpMyAdmin database.
  1672. * Available property:
  1673. * - PROP_SORTED_COLUMN
  1674. * - PROP_COLUMN_ORDER
  1675. * - PROP_COLUMN_VISIB
  1676. *
  1677. * @param string $property Property
  1678. * @param mixed $value Value for the property
  1679. * @param string $tableCreateTime Needed for PROP_COLUMN_ORDER and PROP_COLUMN_VISIB
  1680. *
  1681. * @return bool|Message
  1682. */
  1683. public function setUiProp($property, $value, $tableCreateTime = null)
  1684. {
  1685. if (empty($this->uiprefs)) {
  1686. $this->loadUiPrefs();
  1687. }
  1688. // we want to save the create time if the property is PROP_COLUMN_ORDER
  1689. if (! $this->isView() && ($property == self::PROP_COLUMN_ORDER || $property == self::PROP_COLUMN_VISIB)) {
  1690. $currCreateTime = $this->getStatusInfo('CREATE_TIME');
  1691. if (! isset($tableCreateTime) || $tableCreateTime != $currCreateTime) {
  1692. // there is no $table_create_time, or
  1693. // supplied $table_create_time is older than current create time,
  1694. // so don't save
  1695. return Message::error(
  1696. sprintf(
  1697. __(
  1698. 'Cannot save UI property "%s". The changes made will ' .
  1699. 'not be persistent after you refresh this page. ' .
  1700. 'Please check if the table structure has been changed.'
  1701. ),
  1702. $property
  1703. )
  1704. );
  1705. }
  1706. $this->uiprefs['CREATE_TIME'] = $currCreateTime;
  1707. }
  1708. // save the value
  1709. $this->uiprefs[$property] = $value;
  1710. // check if pmadb is set
  1711. $uiPreferencesFeature = $this->relation->getRelationParameters()->uiPreferencesFeature;
  1712. if ($uiPreferencesFeature !== null) {
  1713. return $this->saveUiPrefsToDb($uiPreferencesFeature);
  1714. }
  1715. return true;
  1716. }
  1717. /**
  1718. * Remove a property from UI preferences.
  1719. *
  1720. * @param string $property the property
  1721. *
  1722. * @return true|Message
  1723. */
  1724. public function removeUiProp($property)
  1725. {
  1726. if (empty($this->uiprefs)) {
  1727. $this->loadUiPrefs();
  1728. }
  1729. if (isset($this->uiprefs[$property])) {
  1730. unset($this->uiprefs[$property]);
  1731. // check if pmadb is set
  1732. $uiPreferencesFeature = $this->relation->getRelationParameters()->uiPreferencesFeature;
  1733. if ($uiPreferencesFeature !== null) {
  1734. return $this->saveUiPrefsToDb($uiPreferencesFeature);
  1735. }
  1736. }
  1737. return true;
  1738. }
  1739. /**
  1740. * Get all column names which are MySQL reserved words
  1741. *
  1742. * @return array
  1743. */
  1744. public function getReservedColumnNames()
  1745. {
  1746. $columns = $this->getColumns(false);
  1747. $return = [];
  1748. foreach ($columns as $column) {
  1749. $temp = explode('.', $column);
  1750. $columnName = $temp[2];
  1751. if (! Context::isKeyword($columnName, true)) {
  1752. continue;
  1753. }
  1754. $return[] = $columnName;
  1755. }
  1756. return $return;
  1757. }
  1758. /**
  1759. * Function to get the name and type of the columns of a table
  1760. *
  1761. * @return array
  1762. */
  1763. public function getNameAndTypeOfTheColumns()
  1764. {
  1765. $columns = [];
  1766. foreach (
  1767. $this->dbi->getColumnsFull($this->dbName, $this->name) as $row
  1768. ) {
  1769. if (preg_match('@^(set|enum)\((.+)\)$@i', $row['Type'], $tmp)) {
  1770. $tmp[2] = mb_substr(
  1771. (string) preg_replace('@([^,])\'\'@', '\\1\\\'', ',' . $tmp[2]),
  1772. 1
  1773. );
  1774. $columns[$row['Field']] = $tmp[1] . '('
  1775. . str_replace(',', ', ', $tmp[2]) . ')';
  1776. } else {
  1777. $columns[$row['Field']] = $row['Type'];
  1778. }
  1779. }
  1780. return $columns;
  1781. }
  1782. /**
  1783. * Get index with index name
  1784. *
  1785. * @param string $index Index name
  1786. *
  1787. * @return Index
  1788. */
  1789. public function getIndex($index)
  1790. {
  1791. return Index::singleton($this->dbName, $this->name, $index);
  1792. }
  1793. /**
  1794. * Function to get the sql query for index creation or edit
  1795. *
  1796. * @param Index $index current index
  1797. * @param bool $error whether error occurred or not
  1798. *
  1799. * @return string
  1800. */
  1801. public function getSqlQueryForIndexCreateOrEdit($index, &$error)
  1802. {
  1803. // $sql_query is the one displayed in the query box
  1804. $sqlQuery = sprintf(
  1805. 'ALTER TABLE %s.%s',
  1806. Util::backquote($this->dbName),
  1807. Util::backquote($this->name)
  1808. );
  1809. // Drops the old index
  1810. if (! empty($_POST['old_index'])) {
  1811. $oldIndex = is_array($_POST['old_index']) ? $_POST['old_index']['Key_name'] : $_POST['old_index'];
  1812. if ($oldIndex === 'PRIMARY') {
  1813. $sqlQuery .= ' DROP PRIMARY KEY,';
  1814. } else {
  1815. $sqlQuery .= sprintf(
  1816. ' DROP INDEX %s,',
  1817. Util::backquote($oldIndex)
  1818. );
  1819. }
  1820. }
  1821. // Builds the new one
  1822. switch ($index->getChoice()) {
  1823. case 'PRIMARY':
  1824. if ($index->getName() == '') {
  1825. $index->setName('PRIMARY');
  1826. } elseif ($index->getName() !== 'PRIMARY') {
  1827. $error = Message::error(
  1828. __('The name of the primary key must be "PRIMARY"!')
  1829. );
  1830. }
  1831. $sqlQuery .= ' ADD PRIMARY KEY';
  1832. break;
  1833. case 'FULLTEXT':
  1834. case 'UNIQUE':
  1835. case 'INDEX':
  1836. case 'SPATIAL':
  1837. if ($index->getName() === 'PRIMARY') {
  1838. $error = Message::error(
  1839. __('Can\'t rename index to PRIMARY!')
  1840. );
  1841. }
  1842. $sqlQuery .= sprintf(
  1843. ' ADD %s ',
  1844. $index->getChoice()
  1845. );
  1846. if ($index->getName() !== '') {
  1847. $sqlQuery .= Util::backquote($index->getName());
  1848. }
  1849. break;
  1850. }
  1851. $indexFields = [];
  1852. foreach ($index->getColumns() as $key => $column) {
  1853. $indexFields[$key] = Util::backquote($column->getName());
  1854. if (! $column->getSubPart()) {
  1855. continue;
  1856. }
  1857. $indexFields[$key] .= '(' . $column->getSubPart() . ')';
  1858. }
  1859. if (empty($indexFields)) {
  1860. $error = Message::error(__('No index parts defined!'));
  1861. } else {
  1862. $sqlQuery .= ' (' . implode(', ', $indexFields) . ')';
  1863. }
  1864. $keyBlockSizes = $index->getKeyBlockSize();
  1865. if (! empty($keyBlockSizes)) {
  1866. $sqlQuery .= sprintf(
  1867. ' KEY_BLOCK_SIZE = %s',
  1868. $this->dbi->escapeString((string) $keyBlockSizes)
  1869. );
  1870. }
  1871. // specifying index type is allowed only for primary, unique and index only
  1872. // TokuDB is using Fractal Tree, Using Type is not useless
  1873. // Ref: https://mariadb.com/kb/en/storage-engine-index-types/
  1874. $type = $index->getType();
  1875. if (
  1876. $index->getChoice() !== 'SPATIAL'
  1877. && $index->getChoice() !== 'FULLTEXT'
  1878. && in_array($type, Index::getIndexTypes())
  1879. && ! $this->isEngine(['TOKUDB'])
  1880. ) {
  1881. $sqlQuery .= ' USING ' . $type;
  1882. }
  1883. $parser = $index->getParser();
  1884. if ($index->getChoice() === 'FULLTEXT' && ! empty($parser)) {
  1885. $sqlQuery .= ' WITH PARSER ' . $this->dbi->escapeString($parser);
  1886. }
  1887. $comment = $index->getComment();
  1888. if (! empty($comment)) {
  1889. $sqlQuery .= sprintf(
  1890. " COMMENT '%s'",
  1891. $this->dbi->escapeString($comment)
  1892. );
  1893. }
  1894. $sqlQuery .= ';';
  1895. return $sqlQuery;
  1896. }
  1897. /**
  1898. * Function to handle update for display field
  1899. *
  1900. * @param string $displayField display field
  1901. */
  1902. public function updateDisplayField($displayField, DisplayFeature $displayFeature): void
  1903. {
  1904. if ($displayField == '') {
  1905. $updQuery = 'DELETE FROM '
  1906. . Util::backquote($displayFeature->database)
  1907. . '.' . Util::backquote($displayFeature->tableInfo)
  1908. . ' WHERE db_name = \''
  1909. . $this->dbi->escapeString($this->dbName) . '\''
  1910. . ' AND table_name = \''
  1911. . $this->dbi->escapeString($this->name) . '\'';
  1912. } else {
  1913. $updQuery = 'REPLACE INTO '
  1914. . Util::backquote($displayFeature->database)
  1915. . '.' . Util::backquote($displayFeature->tableInfo)
  1916. . '(db_name, table_name, display_field) VALUES('
  1917. . '\'' . $this->dbi->escapeString($this->dbName) . '\','
  1918. . '\'' . $this->dbi->escapeString($this->name) . '\','
  1919. . '\'' . $this->dbi->escapeString($displayField) . '\')';
  1920. }
  1921. $this->dbi->queryAsControlUser($updQuery);
  1922. }
  1923. /**
  1924. * Function to get update query for updating internal relations
  1925. *
  1926. * @param array $multiEditColumnsName multi edit column names
  1927. * @param array $destinationDb destination tables
  1928. * @param array $destinationTable destination tables
  1929. * @param array $destinationColumn destination columns
  1930. * @param array|null $existrel db, table, column
  1931. */
  1932. public function updateInternalRelations(
  1933. array $multiEditColumnsName,
  1934. array $destinationDb,
  1935. array $destinationTable,
  1936. array $destinationColumn,
  1937. RelationFeature $relationFeature,
  1938. $existrel
  1939. ): bool {
  1940. $updated = false;
  1941. foreach ($destinationDb as $masterFieldMd5 => $foreignDb) {
  1942. $updQuery = null;
  1943. // Map the fieldname's md5 back to its real name
  1944. $masterField = $multiEditColumnsName[$masterFieldMd5];
  1945. $foreignTable = $destinationTable[$masterFieldMd5];
  1946. $foreignField = $destinationColumn[$masterFieldMd5];
  1947. if (! empty($foreignDb) && ! empty($foreignTable) && ! empty($foreignField)) {
  1948. if (! isset($existrel[$masterField])) {
  1949. $updQuery = 'INSERT INTO '
  1950. . Util::backquote($relationFeature->database)
  1951. . '.' . Util::backquote($relationFeature->relation)
  1952. . '(master_db, master_table, master_field, foreign_db,'
  1953. . ' foreign_table, foreign_field)'
  1954. . ' values('
  1955. . '\'' . $this->dbi->escapeString($this->dbName) . '\', '
  1956. . '\'' . $this->dbi->escapeString($this->name) . '\', '
  1957. . '\'' . $this->dbi->escapeString($masterField) . '\', '
  1958. . '\'' . $this->dbi->escapeString($foreignDb) . '\', '
  1959. . '\'' . $this->dbi->escapeString($foreignTable) . '\','
  1960. . '\'' . $this->dbi->escapeString($foreignField) . '\')';
  1961. } elseif (
  1962. $existrel[$masterField]['foreign_db'] != $foreignDb
  1963. || $existrel[$masterField]['foreign_table'] != $foreignTable
  1964. || $existrel[$masterField]['foreign_field'] != $foreignField
  1965. ) {
  1966. $updQuery = 'UPDATE '
  1967. . Util::backquote($relationFeature->database)
  1968. . '.' . Util::backquote($relationFeature->relation)
  1969. . ' SET foreign_db = \''
  1970. . $this->dbi->escapeString($foreignDb) . '\', '
  1971. . ' foreign_table = \''
  1972. . $this->dbi->escapeString($foreignTable) . '\', '
  1973. . ' foreign_field = \''
  1974. . $this->dbi->escapeString($foreignField) . '\' '
  1975. . ' WHERE master_db = \''
  1976. . $this->dbi->escapeString($this->dbName) . '\''
  1977. . ' AND master_table = \''
  1978. . $this->dbi->escapeString($this->name) . '\''
  1979. . ' AND master_field = \''
  1980. . $this->dbi->escapeString($masterField) . '\'';
  1981. }
  1982. } elseif (isset($existrel[$masterField])) {
  1983. $updQuery = 'DELETE FROM '
  1984. . Util::backquote($relationFeature->database)
  1985. . '.' . Util::backquote($relationFeature->relation)
  1986. . ' WHERE master_db = \''
  1987. . $this->dbi->escapeString($this->dbName) . '\''
  1988. . ' AND master_table = \''
  1989. . $this->dbi->escapeString($this->name) . '\''
  1990. . ' AND master_field = \''
  1991. . $this->dbi->escapeString($masterField) . '\'';
  1992. }
  1993. if (! isset($updQuery)) {
  1994. continue;
  1995. }
  1996. $this->dbi->queryAsControlUser($updQuery);
  1997. $updated = true;
  1998. }
  1999. return $updated;
  2000. }
  2001. /**
  2002. * Function to handle foreign key updates
  2003. *
  2004. * @param array $destinationForeignDb destination foreign database
  2005. * @param array $multiEditColumnsName multi edit column names
  2006. * @param array $destinationForeignTable destination foreign table
  2007. * @param array $destinationForeignColumn destination foreign column
  2008. * @param array $optionsArray options array
  2009. * @param string $table current table
  2010. * @param array $existrelForeign db, table, column
  2011. *
  2012. * @return array
  2013. */
  2014. public function updateForeignKeys(
  2015. array $destinationForeignDb,
  2016. array $multiEditColumnsName,
  2017. array $destinationForeignTable,
  2018. array $destinationForeignColumn,
  2019. array $optionsArray,
  2020. $table,
  2021. array $existrelForeign
  2022. ) {
  2023. $htmlOutput = '';
  2024. $previewSqlData = '';
  2025. $displayQuery = '';
  2026. $seenError = false;
  2027. foreach ($destinationForeignDb as $masterFieldMd5 => $foreignDb) {
  2028. $create = false;
  2029. $drop = false;
  2030. // Map the fieldname's md5 back to its real name
  2031. $masterField = $multiEditColumnsName[$masterFieldMd5];
  2032. $foreignTable = $destinationForeignTable[$masterFieldMd5];
  2033. $foreignField = $destinationForeignColumn[$masterFieldMd5];
  2034. if (isset($existrelForeign[$masterFieldMd5]['ref_db_name'])) {
  2035. $refDbName = $existrelForeign[$masterFieldMd5]['ref_db_name'];
  2036. } else {
  2037. $refDbName = $GLOBALS['db'];
  2038. }
  2039. $emptyFields = false;
  2040. foreach ($masterField as $key => $oneField) {
  2041. if (
  2042. (! empty($oneField) && empty($foreignField[$key]))
  2043. || (empty($oneField) && ! empty($foreignField[$key]))
  2044. ) {
  2045. $emptyFields = true;
  2046. }
  2047. if (! empty($oneField) || ! empty($foreignField[$key])) {
  2048. continue;
  2049. }
  2050. unset($masterField[$key], $foreignField[$key]);
  2051. }
  2052. if (! empty($foreignDb) && ! empty($foreignTable) && ! $emptyFields) {
  2053. if (isset($existrelForeign[$masterFieldMd5])) {
  2054. $constraintName = $existrelForeign[$masterFieldMd5]['constraint'];
  2055. $onDelete = ! empty(
  2056. $existrelForeign[$masterFieldMd5]['on_delete']
  2057. )
  2058. ? $existrelForeign[$masterFieldMd5]['on_delete']
  2059. : 'RESTRICT';
  2060. $onUpdate = ! empty(
  2061. $existrelForeign[$masterFieldMd5]['on_update']
  2062. )
  2063. ? $existrelForeign[$masterFieldMd5]['on_update']
  2064. : 'RESTRICT';
  2065. if (
  2066. $refDbName != $foreignDb
  2067. || $existrelForeign[$masterFieldMd5]['ref_table_name'] != $foreignTable
  2068. || $existrelForeign[$masterFieldMd5]['ref_index_list'] != $foreignField
  2069. || $existrelForeign[$masterFieldMd5]['index_list'] != $masterField
  2070. || $_POST['constraint_name'][$masterFieldMd5] != $constraintName
  2071. || ($_POST['on_delete'][$masterFieldMd5] != $onDelete)
  2072. || ($_POST['on_update'][$masterFieldMd5] != $onUpdate)
  2073. ) {
  2074. // another foreign key is already defined for this field
  2075. // or an option has been changed for ON DELETE or ON UPDATE
  2076. $drop = true;
  2077. $create = true;
  2078. }
  2079. } else {
  2080. // no key defined for this field(s)
  2081. $create = true;
  2082. }
  2083. } elseif (isset($existrelForeign[$masterFieldMd5])) {
  2084. $drop = true;
  2085. }
  2086. if ($drop) {
  2087. $dropQuery = 'ALTER TABLE ' . Util::backquote($table)
  2088. . ' DROP FOREIGN KEY '
  2089. . Util::backquote($existrelForeign[$masterFieldMd5]['constraint'])
  2090. . ';';
  2091. if (! isset($_POST['preview_sql'])) {
  2092. $displayQuery .= $dropQuery . "\n";
  2093. $this->dbi->tryQuery($dropQuery);
  2094. $tmpErrorDrop = $this->dbi->getError();
  2095. if ($tmpErrorDrop !== '') {
  2096. $seenError = true;
  2097. $htmlOutput .= Generator::mysqlDie($tmpErrorDrop, $dropQuery, false, '', false);
  2098. continue;
  2099. }
  2100. } else {
  2101. $previewSqlData .= $dropQuery . "\n";
  2102. }
  2103. }
  2104. $tmpErrorCreate = false;
  2105. if (! $create) {
  2106. continue;
  2107. }
  2108. $createQuery = $this->getSQLToCreateForeignKey(
  2109. $table,
  2110. $masterField,
  2111. $foreignDb,
  2112. $foreignTable,
  2113. $foreignField,
  2114. $_POST['constraint_name'][$masterFieldMd5],
  2115. $optionsArray[$_POST['on_delete'][$masterFieldMd5]],
  2116. $optionsArray[$_POST['on_update'][$masterFieldMd5]]
  2117. );
  2118. if (! isset($_POST['preview_sql'])) {
  2119. $displayQuery .= $createQuery . "\n";
  2120. $this->dbi->tryQuery($createQuery);
  2121. $tmpErrorCreate = $this->dbi->getError();
  2122. if (! empty($tmpErrorCreate)) {
  2123. $seenError = true;
  2124. if (substr($tmpErrorCreate, 1, 4) == '1005') {
  2125. $message = Message::error(
  2126. __(
  2127. 'Error creating foreign key on %1$s (check data types)'
  2128. )
  2129. );
  2130. $message->addParam(implode(', ', $masterField));
  2131. $htmlOutput .= $message->getDisplay();
  2132. } else {
  2133. $htmlOutput .= Generator::mysqlDie($tmpErrorCreate, $createQuery, false, '', false);
  2134. }
  2135. $htmlOutput .= MySQLDocumentation::show('create-table-foreign-keys') . "\n";
  2136. }
  2137. } else {
  2138. $previewSqlData .= $createQuery . "\n";
  2139. }
  2140. // this is an alteration and the old constraint has been dropped
  2141. // without creation of a new one
  2142. if (! $drop || empty($tmpErrorCreate)) {
  2143. continue;
  2144. }
  2145. // a rollback may be better here
  2146. $sqlQueryRecreate = '# Restoring the dropped constraint...' . "\n";
  2147. $sqlQueryRecreate .= $this->getSQLToCreateForeignKey(
  2148. $table,
  2149. $masterField,
  2150. $existrelForeign[$masterFieldMd5]['ref_db_name'],
  2151. $existrelForeign[$masterFieldMd5]['ref_table_name'],
  2152. $existrelForeign[$masterFieldMd5]['ref_index_list'],
  2153. $existrelForeign[$masterFieldMd5]['constraint'],
  2154. $optionsArray[$existrelForeign[$masterFieldMd5]['on_delete'] ?? ''] ?? null,
  2155. $optionsArray[$existrelForeign[$masterFieldMd5]['on_update'] ?? ''] ?? null
  2156. );
  2157. if (! isset($_POST['preview_sql'])) {
  2158. $displayQuery .= $sqlQueryRecreate . "\n";
  2159. $this->dbi->tryQuery($sqlQueryRecreate);
  2160. } else {
  2161. $previewSqlData .= $sqlQueryRecreate;
  2162. }
  2163. }
  2164. return [
  2165. $htmlOutput,
  2166. $previewSqlData,
  2167. $displayQuery,
  2168. $seenError,
  2169. ];
  2170. }
  2171. /**
  2172. * Returns the SQL query for foreign key constraint creation
  2173. *
  2174. * @param string $table table name
  2175. * @param array $field field names
  2176. * @param string $foreignDb foreign database name
  2177. * @param string $foreignTable foreign table name
  2178. * @param array $foreignField foreign field names
  2179. * @param string $name name of the constraint
  2180. * @param string $onDelete on delete action
  2181. * @param string $onUpdate on update action
  2182. *
  2183. * @return string SQL query for foreign key constraint creation
  2184. */
  2185. private function getSQLToCreateForeignKey(
  2186. $table,
  2187. array $field,
  2188. $foreignDb,
  2189. $foreignTable,
  2190. array $foreignField,
  2191. $name = null,
  2192. $onDelete = null,
  2193. $onUpdate = null
  2194. ) {
  2195. $sqlQuery = 'ALTER TABLE ' . Util::backquote($table) . ' ADD ';
  2196. // if user entered a constraint name
  2197. if (! empty($name)) {
  2198. $sqlQuery .= ' CONSTRAINT ' . Util::backquote($name);
  2199. }
  2200. foreach ($field as $key => $oneField) {
  2201. $field[$key] = Util::backquote($oneField);
  2202. }
  2203. foreach ($foreignField as $key => $oneField) {
  2204. $foreignField[$key] = Util::backquote($oneField);
  2205. }
  2206. $sqlQuery .= ' FOREIGN KEY (' . implode(', ', $field) . ') REFERENCES '
  2207. . ($this->dbName != $foreignDb
  2208. ? Util::backquote($foreignDb) . '.' : '')
  2209. . Util::backquote($foreignTable)
  2210. . '(' . implode(', ', $foreignField) . ')';
  2211. if (! empty($onDelete)) {
  2212. $sqlQuery .= ' ON DELETE ' . $onDelete;
  2213. }
  2214. if (! empty($onUpdate)) {
  2215. $sqlQuery .= ' ON UPDATE ' . $onUpdate;
  2216. }
  2217. $sqlQuery .= ';';
  2218. return $sqlQuery;
  2219. }
  2220. /**
  2221. * Returns the generation expression for virtual columns
  2222. *
  2223. * @param string $column name of the column
  2224. *
  2225. * @return array|bool associative array of column name and their expressions
  2226. * or false on failure
  2227. */
  2228. public function getColumnGenerationExpression($column = null)
  2229. {
  2230. if (
  2231. Compatibility::isMySqlOrPerconaDb()
  2232. && $this->dbi->getVersion() > 50705
  2233. && ! $GLOBALS['cfg']['Server']['DisableIS']
  2234. ) {
  2235. $sql = "SELECT
  2236. `COLUMN_NAME` AS `Field`,
  2237. `GENERATION_EXPRESSION` AS `Expression`
  2238. FROM
  2239. `information_schema`.`COLUMNS`
  2240. WHERE
  2241. `TABLE_SCHEMA` = '" . $this->dbi->escapeString($this->dbName) . "'
  2242. AND `TABLE_NAME` = '" . $this->dbi->escapeString($this->name) . "'";
  2243. if ($column != null) {
  2244. $sql .= " AND `COLUMN_NAME` = '" . $this->dbi->escapeString($column)
  2245. . "'";
  2246. }
  2247. return $this->dbi->fetchResult($sql, 'Field', 'Expression');
  2248. }
  2249. $createTable = $this->showCreate();
  2250. if ($createTable === '') {
  2251. return false;
  2252. }
  2253. $parser = new Parser($createTable);
  2254. $stmt = $parser->statements[0];
  2255. $fields = [];
  2256. if ($stmt instanceof CreateStatement) {
  2257. $fields = TableUtils::getFields($stmt);
  2258. }
  2259. if ($column != null) {
  2260. $expression = isset($fields[$column]['expr']) ?
  2261. substr($fields[$column]['expr'], 1, -1) : '';
  2262. return [$column => $expression];
  2263. }
  2264. $ret = [];
  2265. foreach ($fields as $field => $options) {
  2266. if (! isset($options['expr'])) {
  2267. continue;
  2268. }
  2269. $ret[$field] = substr($options['expr'], 1, -1);
  2270. }
  2271. return $ret;
  2272. }
  2273. /**
  2274. * Returns the CREATE statement for this table
  2275. */
  2276. public function showCreate(): string
  2277. {
  2278. return (string) $this->dbi->fetchValue(
  2279. 'SHOW CREATE TABLE ' . Util::backquote($this->dbName) . '.'
  2280. . Util::backquote($this->name),
  2281. 1
  2282. );
  2283. }
  2284. /**
  2285. * Returns the real row count for a table
  2286. */
  2287. public function getRealRowCountTable(): ?int
  2288. {
  2289. // SQL query to get row count for a table.
  2290. $result = $this->dbi->fetchSingleRow(
  2291. sprintf(
  2292. 'SELECT COUNT(*) AS %s FROM %s.%s',
  2293. Util::backquote('row_count'),
  2294. Util::backquote($this->dbName),
  2295. Util::backquote($this->name)
  2296. )
  2297. );
  2298. if (! is_array($result)) {
  2299. return null;
  2300. }
  2301. return (int) $result['row_count'];
  2302. }
  2303. /**
  2304. * Get columns with indexes
  2305. *
  2306. * @param int $types types bitmask
  2307. *
  2308. * @return array an array of columns
  2309. */
  2310. public function getColumnsWithIndex($types)
  2311. {
  2312. $columnsWithIndex = [];
  2313. foreach (
  2314. Index::getFromTableByChoice($this->name, $this->dbName, $types) as $index
  2315. ) {
  2316. $columns = $index->getColumns();
  2317. $columnsWithIndex = array_merge($columnsWithIndex, array_keys($columns));
  2318. }
  2319. return $columnsWithIndex;
  2320. }
  2321. }