worker / src / MWP / Action / IncrementalBackup / GetTableSchema.php

GetTableSchema.php in ManageWP Worker trunk, at src/MWP/Action/IncrementalBackup/GetTableSchema.php

132 lines 4.2 KB

Raw Download Zip

1	<?php
2	/*
3	* This file is part of the ManageWP Worker plugin.
4	*
5	* (c) ManageWP LLC <[email protected]>
6	*
7	* For the full copyright and license information, please view the LICENSE
8	* file that was distributed with this source code.
9	*/
10
11	class MWP_Action_IncrementalBackup_GetTableSchema
12	{
13	const ERROR_TABLE_NOT_FOUND = 1;
14
15	public function execute(array $params = array())
16	{
17	$tables = $params['tables'];
18
19	$result = array();
20
21	foreach ($tables as $table) {
22	if (!$this->tableExists($table)) {
23	$result[] = array(
24	'name' => $table,
25	'error' => self::ERROR_TABLE_NOT_FOUND,
26	);
27	continue;
28	}
29
30	$columns = $this->getTableColumns($table);
31	$indexes = $this->getTableIndexes($table);
32	$tableInfo = $this->getTableInfo($table);
33	$createTableSql = $this->getCreateTableSql($table);
34
35	$result[] = array(
36	'name' => $table,
37	'info' => $tableInfo,
38	'columns' => $columns,
39	'indexes' => $indexes,
40	'createTableSql' => $createTableSql,
41	);
42	}
43
44	return $result;
45	}
46
47	private function tableExists($table)
48	{
49	$exists = (int) mwp_context()->getDb()->get_var(
50	sprintf('SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = "%s" AND table_name = "%s"',
51	mwp_context()->escapeParameter(mwp_context()->getDbName()),
52	mwp_context()->escapeParameter($table))
53	);
54
55	return $exists >= 1;
56	}
57
58	private function getTableColumns($table)
59	{
60	return mwp_context()->getDb()->get_results(
61	sprintf('SHOW FULL COLUMNS FROM `%s`', $this->escapeTableName($table)),
62	ARRAY_A
63	);
64	}
65
66	private function getTableIndexes($table)
67	{
68	return mwp_context()->getDb()->get_results(
69	sprintf('SHOW INDEXES FROM `%s`', $this->escapeTableName($table)),
70	ARRAY_A
71	);
72	}
73
74	private function getCreateTableSql($table)
75	{
76	$createTableResults = mwp_context()->getDb()->get_results(
77	sprintf('SHOW CREATE TABLE `%s`', $this->escapeTableName($table)),
78	ARRAY_A
79	);
80
81	return isset($createTableResults[0]['Create Table']) ? $createTableResults[0]['Create Table'] : null;
82	}
83
84	private function getTableInfo($table)
85	{
86	$context = mwp_context();
87	$db = $context->getDb();
88	$dbName = $context->getDbName();
89
90	$tableStatusResult = $db->get_results(
91	sprintf('SHOW TABLE STATUS FROM `%s` WHERE Name = "%s"',
92	$this->escapeTableName($dbName),
93	$context->escapeParameter($table)), ARRAY_A
94	);
95
96	$charsetResult = $db->get_results(
97	sprintf('SELECT CCSA.character_set_name FROM information_schema.`TABLES` T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = "%s" AND T.table_name = "%s"',
98	$context->escapeParameter($dbName),
99	$context->escapeParameter($table)), ARRAY_A
100	);
101
102	$engine = $tableStatusResult[0]['Engine'];
103	$autoIncrement = $tableStatusResult[0]['Auto_increment'];
104	$collation = $tableStatusResult[0]['Collation'];
105	$charset = $charsetResult[0]['character_set_name'];
106
107	return array(
108	'engine' => $engine,
109	'autoIncrement' => $autoIncrement,
110	'collation' => $collation,
111	'charset' => $charset,
112	);
113	}
114
115	/**
116	* Escape backticks (`) in table names.
117	* A table can contain a backtick character (`) and it has to be escaped with another backtick.
118	* This is only relevant
119	*
120	* e.g. asd`asd should be converted into asd``asd.
121	* The resulting SQL query should look like: SELECT * FROM `asd``asd`;
122	*
123	* @param string $table
124	*
125	* @return string
126	*/
127	private function escapeTableName($table)
128	{
129	return str_replace('`', '``', $table);
130	}
131	}
132