-
Notifications
You must be signed in to change notification settings - Fork 158
Expand file tree
/
Copy pathMySQLAdapter.php
More file actions
228 lines (203 loc) · 8.4 KB
/
Copy pathMySQLAdapter.php
File metadata and controls
228 lines (203 loc) · 8.4 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
<?php namespace DBDiff\DB\Adapters;
use Illuminate\Database\Connection;
use Illuminate\Support\Arr;
use Illuminate\Support\Str;
class MySQLAdapter implements DBAdapterInterface {
public function buildConnectionConfig(array $server, string $db): array {
return [
'driver' => 'mysql',
'host' => $server['host'],
'port' => $server['port'],
'database' => $db,
'username' => $server['user'],
'password' => $server['password'],
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
];
}
public function getTables(Connection $connection): array {
$db = $connection->getDatabaseName();
$result = $connection->select(
"SHOW FULL TABLES FROM `$db` WHERE Table_type = 'BASE TABLE'"
);
return array_map(fn($row) => array_values((array) $row)[0], $result);
}
public function getColumns(Connection $connection, string $table): array {
$result = $connection->select("show columns from `$table`");
return Arr::pluck($result, 'Field');
}
public function getPrimaryKey(Connection $connection, string $table): array {
$keys = $connection->select("show indexes from `$table`");
$pkey = [];
foreach ($keys as $key) {
if ($key['Key_name'] === 'PRIMARY') {
$pkey[] = $key['Column_name'];
}
}
return $pkey;
}
public function getTableSchema(Connection $connection, string $table): array {
// Engine & collation
$status = $connection->select("show table status like '$table'");
$engine = $status[0]['Engine'];
$collation = $status[0]['Collation'];
// Parse SHOW CREATE TABLE
$schema = $connection->select("SHOW CREATE TABLE `$table`")[0]['Create Table'];
$lines = array_map(fn($l) => trim($l), explode("\n", $schema));
$lines = array_slice($lines, 1, -1); // remove CREATE TABLE ... ( and closing ) ENGINE=...
$columns = [];
$keys = [];
$constraints = [];
foreach ($lines as $line) {
preg_match("/`([^`]+)`/", $line, $matches);
$name = $matches[1];
$line = trim($line, ',');
if (Str::startsWith($line, '`')) {
$columns[$name] = $this->normalizeColumnDef($line);
} elseif (Str::startsWith($line, 'CONSTRAINT')) {
$constraints[$name] = $line;
} elseif (Str::startsWith($line, 'PRIMARY KEY')) {
$keys['PRIMARY'] = $line;
} else {
$keys[$name] = $this->normalizeKeyDef($line);
}
}
return [
'engine' => $engine,
'collation' => $collation,
'columns' => $columns,
'keys' => $keys,
'constraints' => $constraints,
];
}
public function getCreateStatement(Connection $connection, string $table): string {
$res = $connection->select("SHOW CREATE TABLE `$table`");
return $res[0]['Create Table'];
}
public function getDBVariable(Connection $connection, string $variable): ?string {
$result = $connection->select("show variables like '$variable'");
return $result[0]['Value'] ?? null;
}
public function getBinaryColumns(Connection $connection, string $table): array {
$result = $connection->select("SHOW COLUMNS FROM `$table`");
$binary = [];
foreach ($result as $row) {
$type = strtolower($row['Type']);
if (preg_match('/^(binary|varbinary|tinyblob|blob|mediumblob|longblob)/', $type)) {
$binary[] = $row['Field'];
}
}
return $binary;
}
public function getForeignKeyMap(Connection $connection): array {
$db = $connection->getDatabaseName();
$result = $connection->select(
"SELECT TABLE_NAME, REFERENCED_TABLE_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = ? AND REFERENCED_TABLE_NAME IS NOT NULL",
[$db]
);
$map = [];
foreach ($result as $row) {
$map[$row['TABLE_NAME']][] = $row['REFERENCED_TABLE_NAME'];
}
return empty($map) ? $map : array_map(fn($p) => array_values(array_unique($p)), $map);
}
public function getViews(Connection $connection): array {
$db = $connection->getDatabaseName();
$result = $connection->select(
"SHOW FULL TABLES FROM `$db` WHERE Table_type = 'VIEW'"
);
$views = [];
foreach ($result as $row) {
$name = array_values((array) $row)[0];
$stmt = $connection->select("SHOW CREATE VIEW `$name`");
$views[$name] = $this->normalizeCreateStatement($stmt[0]['Create View']);
}
return $views;
}
public function getTriggers(Connection $connection): array {
$db = $connection->getDatabaseName();
$result = $connection->select("SHOW TRIGGERS FROM `$db`");
$triggers = [];
foreach ($result as $row) {
$name = $row['Trigger'];
$table = $row['Table'];
$stmt = $connection->select("SHOW CREATE TRIGGER `$name`");
$triggers[$name] = [
'definition' => $this->normalizeCreateStatement($stmt[0]['SQL Original Statement']),
'table' => $table,
];
}
return $triggers;
}
public function getRoutines(Connection $connection): array {
$db = $connection->getDatabaseName();
$result = $connection->select(
"SELECT ROUTINE_NAME, ROUTINE_TYPE
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = ?",
[$db]
);
$routines = [];
foreach ($result as $row) {
$name = $row['ROUTINE_NAME'];
$type = $row['ROUTINE_TYPE']; // PROCEDURE or FUNCTION
$stmt = $connection->select("SHOW CREATE $type `$name`");
$key = $type === 'PROCEDURE' ? 'Create Procedure' : 'Create Function';
$routines[$name] = $this->normalizeCreateStatement($stmt[0][$key]);
}
return $routines;
}
public function getEnums(Connection $connection): array {
// MySQL enums are column-level type constraints, not standalone types.
return [];
}
/**
* Strip MySQL-specific DEFINER, ALGORITHM, and SQL SECURITY clauses
* from a CREATE statement so that definitions can be compared across
* environments and emitted without environment-specific metadata.
*/
private function normalizeCreateStatement(string $definition): string {
$definition = preg_replace('/\s*ALGORITHM\s*=\s*\w+/i', '', $definition);
$definition = preg_replace('/\s*DEFINER\s*=\s*`[^`]*`@`[^`]*`/i', '', $definition);
$definition = preg_replace('/\s*SQL\s+SECURITY\s+(?:DEFINER|INVOKER)/i', '', $definition);
return rtrim(trim($definition), ';');
}
/**
* Normalise a column DDL fragment so that two MySQL versions produce
* identical strings for semantically identical columns.
*
* 1. Strip integer display widths removed in MySQL 8.0.17+.
* 2. Canonicalise CURRENT_TIMESTAMP (case + parentheses).
*/
private function normalizeColumnDef(string $def): string {
// Integer display widths: int(11) → int, tinyint(4) → tinyint, etc.
$def = preg_replace(
'/\b(tinyint|smallint|mediumint|int|bigint)\(\d+\)/i',
'$1',
$def
);
// Normalise CURRENT_TIMESTAMP variants (with optional precision).
// current_timestamp() → CURRENT_TIMESTAMP
// current_timestamp(3) → CURRENT_TIMESTAMP(3)
$def = preg_replace_callback(
'/\bcurrent_timestamp(?:\((\d*)\))?/i',
function ($m) {
$precision = $m[1] ?? '';
return ($precision !== '') ? "CURRENT_TIMESTAMP($precision)" : 'CURRENT_TIMESTAMP';
},
$def
);
return $def;
}
/**
* Normalise an index / key DDL fragment.
*
* Strip trailing USING BTREE — it is the default index type and its
* inclusion varies between MySQL versions, causing false positives.
*/
private function normalizeKeyDef(string $def): string {
return preg_replace('/\s+USING BTREE$/i', '', $def);
}
}