|
1
|
|
|
<?php namespace Tequilarapido\Database; |
|
2
|
|
|
|
|
3
|
|
|
use Tequilarapido\Database\Database; |
|
4
|
|
|
|
|
5
|
|
|
class Table |
|
6
|
|
|
{ |
|
7
|
|
|
/** |
|
8
|
|
|
* Return database tables |
|
9
|
|
|
* @param $database |
|
10
|
|
|
* @return array of tables |
|
11
|
|
|
*/ |
|
12
|
|
|
public function all($database) |
|
13
|
|
|
{ |
|
14
|
|
|
$query = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES "; |
|
15
|
|
|
$query .= "WHERE 1 "; |
|
16
|
|
|
$query .= "AND TABLE_SCHEMA = ? "; |
|
17
|
|
|
$result = Database::select($query, array($database)); |
|
18
|
|
|
|
|
19
|
|
|
$all = array(); |
|
20
|
|
|
foreach ($result as $t) { |
|
21
|
|
|
$all[] = $t['TABLE_NAME']; |
|
22
|
|
|
} |
|
23
|
|
|
return $all; |
|
24
|
|
|
} |
|
25
|
|
|
|
|
26
|
|
|
public function filterByTruncateConf($database, $cleanupConf, $prefix) |
|
27
|
|
|
{ |
|
28
|
|
|
$tables = array(); |
|
29
|
|
|
|
|
30
|
|
|
// Get all tables |
|
31
|
|
|
$all = $this->all($database); |
|
32
|
|
|
|
|
33
|
|
|
// Add simple tables |
|
34
|
|
View Code Duplication |
if (!empty($cleanupConf->simple) && is_array($cleanupConf->simple)) { |
|
|
|
|
|
|
35
|
|
|
foreach ($cleanupConf->simple as $t) { |
|
36
|
|
|
if (in_array($t, $all)) { |
|
37
|
|
|
$tables[] = $t; |
|
38
|
|
|
} |
|
39
|
|
|
} |
|
40
|
|
|
} |
|
41
|
|
|
|
|
42
|
|
|
// Add multi tables |
|
43
|
|
View Code Duplication |
if (!empty($cleanupConf->multi) && is_array($cleanupConf->multi)) { |
|
|
|
|
|
|
44
|
|
|
foreach ($cleanupConf->multi as $t) { |
|
45
|
|
|
$matchedTables = $this->getTablesForMulti($t, $all, $prefix); |
|
46
|
|
|
if (!empty($matchedTables)) { |
|
47
|
|
|
$tables = array_merge($tables, $matchedTables); |
|
48
|
|
|
} |
|
49
|
|
|
} |
|
50
|
|
|
} |
|
51
|
|
|
|
|
52
|
|
|
return array_unique($tables); |
|
53
|
|
|
} |
|
54
|
|
|
|
|
55
|
|
|
public function getTablesForMulti($table, $prefix, $all = array()) |
|
56
|
|
|
{ |
|
57
|
|
|
$pattern = '/^' . $prefix . '([0-9]+_)?' . $table . '$/'; |
|
58
|
|
|
return preg_grep($pattern, $all); |
|
59
|
|
|
} |
|
60
|
|
|
|
|
61
|
|
|
public function filterByDeleteConf($database, $cleanupConf, $prefix) |
|
62
|
|
|
{ |
|
63
|
|
|
$tables = array(); |
|
64
|
|
|
|
|
65
|
|
|
// Get all tables |
|
66
|
|
|
$all = $this->all($database); |
|
67
|
|
|
|
|
68
|
|
|
// Add simple tables |
|
69
|
|
View Code Duplication |
if (!empty($cleanupConf->simple) && is_array($cleanupConf->simple)) { |
|
|
|
|
|
|
70
|
|
|
foreach ($cleanupConf->simple as $t) { |
|
71
|
|
|
if (in_array($t, $all)) { |
|
72
|
|
|
$tables[] = $t; |
|
73
|
|
|
} |
|
74
|
|
|
} |
|
75
|
|
|
} |
|
76
|
|
|
|
|
77
|
|
|
// Add multi tables |
|
78
|
|
|
if (!empty($cleanupConf->multi) && is_array($cleanupConf->multi)) { |
|
79
|
|
|
foreach ($cleanupConf->multi as $t) { |
|
80
|
|
|
$pattern = '/^' . $prefix . '([0-9]+_)?' . $t . '$/'; |
|
81
|
|
|
$matchedTables = preg_grep($pattern, $all); |
|
82
|
|
|
if (!empty($matchedTables)) { |
|
83
|
|
|
$tables = array_merge($tables, $matchedTables); |
|
84
|
|
|
} |
|
85
|
|
|
} |
|
86
|
|
|
} |
|
87
|
|
|
|
|
88
|
|
|
return array_unique($tables); |
|
89
|
|
|
} |
|
90
|
|
|
|
|
91
|
|
|
|
|
92
|
|
|
/** |
|
93
|
|
|
* @param $database |
|
94
|
|
|
* @return array of tables sizes |
|
95
|
|
|
*/ |
|
96
|
|
|
public function sizes($database) |
|
97
|
|
|
{ |
|
98
|
|
|
$query = ''; |
|
99
|
|
|
$query .= 'SELECT TABLE_NAME, table_rows, data_length, index_length '; |
|
100
|
|
|
$query .= 'FROM information_schema.TABLES WHERE table_schema = ? '; |
|
101
|
|
|
|
|
102
|
|
|
|
|
103
|
|
|
$res = Database::select($query, array($database)); |
|
104
|
|
|
|
|
105
|
|
|
$total_data = $total_index = $total = 0; |
|
106
|
|
|
foreach ($res as &$item) { |
|
107
|
|
|
$item['data_length'] = round($item['data_length'] / 1024 / 1024, 2); |
|
108
|
|
|
$item['index_length'] = round($item['index_length'] / 1024 / 1024, 2); |
|
109
|
|
|
$item['total'] = $item['data_length'] + $item['index_length']; |
|
110
|
|
|
|
|
111
|
|
|
// Totals |
|
112
|
|
|
$total_data += $item['data_length']; |
|
113
|
|
|
$total_index += $item['index_length']; |
|
114
|
|
|
$total += $item['data_length'] + $item['index_length']; |
|
115
|
|
|
} |
|
116
|
|
|
|
|
117
|
|
|
|
|
118
|
|
|
// Add total at the bootom |
|
119
|
|
|
if (!empty($res)) { |
|
120
|
|
|
$res[] = array( |
|
121
|
|
|
'TABLE_NAME' => 'total', |
|
122
|
|
|
'table_rows' => '-', |
|
123
|
|
|
'data_length' => $total_data, |
|
124
|
|
|
'index_length' => $total_index, |
|
125
|
|
|
'total' => $total |
|
126
|
|
|
); |
|
127
|
|
|
} |
|
128
|
|
|
|
|
129
|
|
|
|
|
130
|
|
|
usort($res, function ($a, $b) { |
|
131
|
|
|
return $a['total'] > $b['total']; |
|
132
|
|
|
}); |
|
133
|
|
|
|
|
134
|
|
|
return $res; |
|
135
|
|
|
} |
|
136
|
|
|
|
|
137
|
|
|
/* |
|
138
|
|
|
|-------------------------------------------------------------------------- |
|
139
|
|
|
| Engines |
|
140
|
|
|
|-------------------------------------------------------------------------- |
|
141
|
|
|
*/ |
|
142
|
|
|
|
|
143
|
|
|
/** |
|
144
|
|
|
* @param string $engine |
|
145
|
|
|
*/ |
|
146
|
|
|
public function alterEngine($table, $engine) |
|
147
|
|
|
{ |
|
148
|
|
|
$query = "ALTER TABLE $table ENGINE = $engine"; |
|
149
|
|
|
$res = Database::statement($query); |
|
150
|
|
|
if (!$res) { |
|
151
|
|
|
throw new \Exception("Query failed : $query"); |
|
152
|
|
|
} |
|
153
|
|
|
} |
|
154
|
|
|
|
|
155
|
|
View Code Duplication |
public function getEngines($database) |
|
|
|
|
|
|
156
|
|
|
{ |
|
157
|
|
|
$query = ''; |
|
158
|
|
|
$query .= 'SELECT table_name, engine '; |
|
159
|
|
|
$query .= 'FROM information_schema.tables '; |
|
160
|
|
|
$query .= 'WHERE table_schema = ? '; |
|
161
|
|
|
|
|
162
|
|
|
return Database::select($query, array($database)); |
|
163
|
|
|
} |
|
164
|
|
|
|
|
165
|
|
|
/** |
|
166
|
|
|
* @param string $engine |
|
167
|
|
|
*/ |
|
168
|
|
|
public function isEngine($database, $engine) |
|
169
|
|
|
{ |
|
170
|
|
|
$tablesEngines = $this->getEngines($database); |
|
171
|
|
|
|
|
172
|
|
|
$notWithSpecifiedEngine = array(); |
|
173
|
|
|
foreach ($tablesEngines as $item) { |
|
174
|
|
|
if (empty($item['engine']) || $item['engine'] !== $engine) { |
|
175
|
|
|
$notWithSpecifiedEngine[] = $item; |
|
176
|
|
|
} |
|
177
|
|
|
} |
|
178
|
|
|
|
|
179
|
|
|
return empty($notWithSpecifiedEngine); |
|
180
|
|
|
} |
|
181
|
|
|
|
|
182
|
|
|
/* |
|
183
|
|
|
|-------------------------------------------------------------------------- |
|
184
|
|
|
| Chatsets and collations |
|
185
|
|
|
|-------------------------------------------------------------------------- |
|
186
|
|
|
*/ |
|
187
|
|
|
|
|
188
|
|
|
/** |
|
189
|
|
|
* @param string $charset |
|
190
|
|
|
* @param string $collation |
|
191
|
|
|
*/ |
|
192
|
|
|
public function alterCharsetAndCollation($table, $charset, $collation) |
|
193
|
|
|
{ |
|
194
|
|
|
$query = "ALTER TABLE $table CONVERT TO CHARACTER SET $charset, COLLATE $collation;"; |
|
195
|
|
|
$res = Database::statement($query); |
|
196
|
|
|
if (!$res) { |
|
197
|
|
|
throw new \Exception("Query failed : $query"); |
|
198
|
|
|
} |
|
199
|
|
|
} |
|
200
|
|
|
|
|
201
|
|
View Code Duplication |
public function getCollations($database) |
|
|
|
|
|
|
202
|
|
|
{ |
|
203
|
|
|
$query = ''; |
|
204
|
|
|
$query .= 'SELECT table_name, table_collation '; |
|
205
|
|
|
$query .= 'FROM information_schema.tables '; |
|
206
|
|
|
$query .= 'WHERE table_schema = ? '; |
|
207
|
|
|
|
|
208
|
|
|
return Database::select($query, array($database)); |
|
209
|
|
|
} |
|
210
|
|
|
|
|
211
|
|
|
/** |
|
212
|
|
|
* @param string $collation |
|
213
|
|
|
*/ |
|
214
|
|
|
public function isCollation($database, $collation) |
|
215
|
|
|
{ |
|
216
|
|
|
$tablesCollations = $this->getCollations($database); |
|
217
|
|
|
|
|
218
|
|
|
$notWithSpecifiedCollation = array(); |
|
219
|
|
|
foreach ($tablesCollations as $item) { |
|
220
|
|
|
if (empty($item['table_collation']) || $item['table_collation'] !== $collation) { |
|
221
|
|
|
$notWithSpecifiedCollation[] = $item; |
|
222
|
|
|
} |
|
223
|
|
|
} |
|
224
|
|
|
|
|
225
|
|
|
return empty($notWithSpecifiedCollation); |
|
226
|
|
|
} |
|
227
|
|
|
|
|
228
|
|
|
} |
tequilarapido /
appcli
Loading history...
Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.
You can also find more detailed suggestions in the “Code” section of your repository.