Commit | Line | Data |
---|---|---|
4bcd4c62 TO |
1 | <?php |
2 | namespace Civi\Setup; | |
3 | ||
4 | use Civi\Setup\Exception\SqlException; | |
5 | ||
6 | class DbUtil { | |
7 | ||
8 | /** | |
9 | * @param string $dsn | |
10 | * @return array | |
11 | */ | |
12 | public static function parseDsn($dsn) { | |
dd589f53 | 13 | $parsed = array_map('urldecode', parse_url($dsn)); |
7fde25fc | 14 | // parse_url parses 'mysql://admin:secret@unix(/var/lib/mysql/mysql.sock)/otherdb' like: |
15 | // [ | |
16 | // 'host' => 'unix(', | |
17 | // 'path' => '/var/lib/mysql/mysql.sock)/otherdb', | |
18 | // ... | |
19 | // ] | |
20 | if ($parsed['host'] == 'unix(') { | |
bd32b591 | 21 | preg_match('/(unix\(.*\))(\/(.+)?)?$/', $dsn, $matches); |
22 | $server = $matches[1]; | |
23 | $database = $matches[3] ?? NULL; | |
7fde25fc | 24 | } |
25 | else { | |
26 | $server = self::encodeHostPort($parsed['host'], $parsed['port'] ?? NULL); | |
27 | $database = $parsed['path'] ? ltrim($parsed['path'], '/') : NULL; | |
28 | } | |
29 | ||
4bcd4c62 | 30 | return array( |
7fde25fc | 31 | 'server' => $server, |
4bcd4c62 TO |
32 | 'username' => $parsed['user'] ?: NULL, |
33 | 'password' => $parsed['pass'] ?: NULL, | |
7fde25fc | 34 | 'database' => $database, |
04b1abad | 35 | 'ssl_params' => self::parseSSL($parsed['query'] ?? NULL), |
4bcd4c62 TO |
36 | ); |
37 | } | |
38 | ||
39 | /** | |
dd589f53 TO |
40 | * Convert a datasource from array notation to URL notation. |
41 | * | |
42 | * FIXME: Doesn't support SSL | |
4bcd4c62 TO |
43 | * |
44 | * @param array $db | |
45 | * @return string | |
46 | */ | |
47 | public static function encodeDsn($db) { | |
dd589f53 | 48 | $escapedHostPort = implode(':', array_map('urlencode', explode(':', $db['server']))); |
4bcd4c62 | 49 | return sprintf('mysql://%s:%s@%s/%s', |
dd589f53 TO |
50 | urlencode($db['username']), |
51 | urlencode($db['password']), | |
52 | $escapedHostPort, | |
53 | urlencode($db['database']) | |
4bcd4c62 TO |
54 | ); |
55 | } | |
56 | ||
57 | /** | |
58 | * @param array $db | |
59 | * @return \mysqli | |
60 | */ | |
61 | public static function softConnect($db) { | |
7fde25fc | 62 | list($host, $port, $socket) = self::decodeHostPort($db['server']); |
04b1abad | 63 | if (empty($db['ssl_params'])) { |
7fde25fc | 64 | $conn = @mysqli_connect($host, $db['username'], $db['password'], $db['database'], $port, $socket); |
04b1abad | 65 | } |
66 | else { | |
67 | $conn = NULL; | |
68 | $init = mysqli_init(); | |
69 | mysqli_ssl_set( | |
70 | $init, | |
71 | $db['ssl_params']['key'] ?? NULL, | |
72 | $db['ssl_params']['cert'] ?? NULL, | |
73 | $db['ssl_params']['ca'] ?? NULL, | |
74 | $db['ssl_params']['capath'] ?? NULL, | |
75 | $db['ssl_params']['cipher'] ?? NULL | |
76 | ); | |
7fde25fc | 77 | if (@mysqli_real_connect($init, $host, $db['username'], $db['password'], $db['database'], $port, $socket, MYSQLI_CLIENT_SSL)) { |
04b1abad | 78 | $conn = $init; |
79 | } | |
80 | } | |
4bcd4c62 TO |
81 | return $conn; |
82 | } | |
83 | ||
84 | /** | |
85 | * @param array $db | |
86 | * @return \mysqli | |
87 | * @throws SqlException | |
88 | */ | |
89 | public static function connect($db) { | |
90 | $conn = self::softConnect($db); | |
91 | if (mysqli_connect_errno()) { | |
92 | throw new SqlException(sprintf("Connection failed: %s\n", mysqli_connect_error())); | |
93 | } | |
94 | return $conn; | |
95 | } | |
96 | ||
97 | /** | |
98 | * @param string $host | |
99 | * Ex: 'localhost', | |
100 | * Ex: 'localhost:123' | |
101 | * Ex: '127.0.0.1:123' | |
102 | * Ex: '[1234:abcd]' | |
103 | * Ex: '[1234:abcd]:123' | |
7fde25fc | 104 | * Ex: 'localhost:/path/to/socket.sock |
105 | * Ex: 'unix(/path/to/socket.sock) | |
4bcd4c62 | 106 | * @return array |
7fde25fc | 107 | * Combination: [0 => string $host, 1 => numeric|NULL $port, 2 => string|NULL]. |
108 | * Ex: ['localhost', NULL, NULL]. | |
109 | * Ex: ['127.0.0.1', 3306, NULL] | |
4bcd4c62 TO |
110 | */ |
111 | public static function decodeHostPort($host) { | |
7fde25fc | 112 | $port = NULL; |
113 | $socket = NULL; | |
bd32b591 | 114 | if (preg_match('/^unix\(([^)]+)\)$/', $host, $matches) === 1) { |
115 | $host = 'localhost'; | |
116 | $socket = $matches[1]; | |
4bcd4c62 TO |
117 | } |
118 | else { | |
7fde25fc | 119 | $hostParts = explode(':', $host); |
120 | if (count($hostParts) > 1 && strrpos($host, ']') !== strlen($host) - 1) { | |
121 | $portOrSocket = array_pop($hostParts); | |
122 | if (substr($portOrSocket, /*start*/ 0, /*length*/ 1) == '/') { | |
123 | $socket = $portOrSocket; | |
124 | } | |
125 | else { | |
126 | $port = $portOrSocket; | |
127 | } | |
128 | $host = implode(':', $hostParts); | |
129 | } | |
4bcd4c62 | 130 | } |
7fde25fc | 131 | return array($host, $port, $socket); |
4bcd4c62 TO |
132 | } |
133 | ||
134 | /** | |
135 | * Combine a host and port number. | |
136 | * | |
137 | * @param string $host | |
138 | * @param int|NULL $port | |
139 | * @return string | |
140 | * Ex: 'localhost'. | |
141 | * Ex: '127.0.0.1:3307'. | |
142 | */ | |
143 | public static function encodeHostPort($host, $port) { | |
144 | return $host . ($port ? (':' . $port) : ''); | |
145 | } | |
146 | ||
04b1abad | 147 | /** |
148 | * For SSL you can have client certificates, which has some required and | |
149 | * optional parameters, or you can have anonymous SSL, which just requires | |
150 | * some indication that you want that. | |
151 | * | |
152 | * @param string $query_string | |
153 | * @return array | |
154 | */ | |
155 | public static function parseSSL($query_string) { | |
156 | if (empty($query_string)) { | |
157 | return []; | |
158 | } | |
159 | parse_str($query_string, $parsed_query); | |
160 | $sensible_parameters = [ | |
161 | // ssl=1 alone means no client certificate - it's not a real mysqli option | |
162 | 'ssl' => NULL, | |
163 | 'key' => NULL, | |
164 | 'cert' => NULL, | |
165 | 'ca' => NULL, | |
166 | 'capath' => NULL, | |
167 | 'cipher' => NULL, | |
168 | ]; | |
169 | // Only want to include a param if it's in our list of sensibles, e.g. | |
170 | // we don't want new_link=true. | |
171 | return array_intersect_key($parsed_query, $sensible_parameters); | |
172 | } | |
173 | ||
4bcd4c62 TO |
174 | /** |
175 | * @param array $db | |
176 | * @param string $SQLcontent | |
177 | * @param bool $lineMode | |
178 | * What does this mean? Seems weird. | |
179 | */ | |
180 | public static function sourceSQL($db, $SQLcontent, $lineMode = FALSE) { | |
181 | $conn = self::connect($db); | |
182 | ||
183 | $conn->query('SET NAMES ' . ($conn->server_version < 50503 ? 'utf8' : 'utf8mb4')); | |
184 | ||
185 | if (!$lineMode) { | |
186 | $string = $SQLcontent; | |
187 | ||
188 | // change \r\n to fix windows issues | |
189 | $string = str_replace("\r\n", "\n", $string); | |
190 | ||
191 | //get rid of comments starting with # and -- | |
192 | ||
193 | $string = preg_replace("/^#[^\n]*$/m", "\n", $string); | |
194 | $string = preg_replace("/^(--[^-]).*/m", "\n", $string); | |
195 | ||
196 | $queries = preg_split('/;\s*$/m', $string); | |
197 | foreach ($queries as $query) { | |
198 | $query = trim($query); | |
199 | if (!empty($query)) { | |
200 | if ($result = $conn->query($query)) { | |
201 | if (is_object($result)) { | |
202 | mysqli_free_result($result); | |
203 | } | |
204 | } | |
205 | else { | |
206 | throw new SqlException("Cannot execute $query: " . mysqli_error($conn)); | |
207 | } | |
208 | } | |
209 | } | |
210 | } | |
211 | else { | |
212 | throw new \RuntimeException("Not implemented: lineMode"); | |
213 | // $fd = fopen($SQLcontent, "r"); | |
214 | // while ($string = fgets($fd)) { | |
215 | // $string = preg_replace("/^#[^\n]*$/m", "\n", $string); | |
216 | // $string = preg_replace("/^(--[^-]).*/m", "\n", $string); | |
217 | // | |
218 | // $string = trim($string); | |
219 | // if (!empty($string)) { | |
220 | // if ($result = $conn->query($string)) { | |
221 | // if (is_object($result)) { | |
222 | // mysqli_free_result($result); | |
223 | // } | |
224 | // } | |
225 | // else { | |
226 | // throw new SqlException("Cannot execute $string: " . mysqli_error($conn)); | |
227 | // } | |
228 | // } | |
229 | // } | |
230 | } | |
231 | } | |
232 | ||
233 | /** | |
234 | * Execute query. Ignore the results. | |
235 | * | |
236 | * @param \mysqli|array $conn | |
237 | * The DB to query. Either a mysqli connection, or credentials for | |
238 | * establishing one. | |
239 | * @param string $sql | |
240 | * @throws SqlException | |
241 | */ | |
242 | public static function execute($conn, $sql) { | |
243 | $conn = is_array($conn) ? self::connect($conn) : $conn; | |
244 | $result = $conn->query($sql); | |
245 | if (!$result) { | |
246 | throw new SqlException("Cannot execute $sql: " . $conn->error); | |
247 | } | |
248 | ||
249 | if ($result && $result !== TRUE) { | |
250 | $result->free_result(); | |
251 | } | |
252 | ||
253 | } | |
254 | ||
255 | /** | |
256 | * Get all the results of a SQL query, as an array. | |
257 | * | |
258 | * @param \mysqli|array $conn | |
259 | * The DB to query. Either a mysqli connection, or credentials for | |
260 | * establishing one. | |
261 | * @param string $sql | |
262 | * @return array | |
263 | * @throws \Exception | |
264 | */ | |
265 | public static function fetchAll($conn, $sql) { | |
266 | $conn = is_array($conn) ? self::connect($conn) : $conn; | |
267 | $result = $conn->query($sql); | |
268 | if (!$result) { | |
269 | throw new SqlException("Cannot execute $sql: " . $conn->error); | |
270 | } | |
271 | ||
272 | $rows = array(); | |
273 | while ($row = $result->fetch_assoc()) { | |
274 | $rows[] = $row; | |
275 | } | |
276 | $result->free_result(); | |
277 | ||
278 | return $rows; | |
279 | } | |
280 | ||
281 | /** | |
282 | * Get a list of views in the given database. | |
283 | * | |
284 | * @param \mysqli|array $conn | |
285 | * The DB to query. Either a mysqli connection, or credentials for | |
286 | * establishing one. | |
287 | * @param string $databaseName | |
288 | * @return array | |
289 | * Ex: ['civicrm_view1', 'civicrm_view2'] | |
290 | */ | |
291 | public static function findViews($conn, $databaseName) { | |
292 | $sql = sprintf("SELECT table_name FROM information_schema.TABLES WHERE TABLE_SCHEMA='%s' AND TABLE_TYPE = 'VIEW'", | |
293 | $conn->escape_string($databaseName)); | |
294 | ||
295 | return array_map(function($arr) { | |
296 | return $arr['table_name']; | |
297 | }, self::fetchAll($conn, $sql)); | |
298 | } | |
299 | ||
300 | /** | |
301 | * Get a list of concrete tables in the given database. | |
302 | * | |
303 | * @param \mysqli|array $conn | |
304 | * The DB to query. Either a mysqli connection, or credentials for | |
305 | * establishing one. | |
306 | * @param string $databaseName | |
307 | * @return array | |
308 | * Ex: ['civicrm_view1', 'civicrm_view2'] | |
309 | */ | |
310 | public static function findTables($conn, $databaseName) { | |
311 | $sql = sprintf("SELECT table_name FROM information_schema.TABLES WHERE TABLE_SCHEMA='%s' AND TABLE_TYPE = 'BASE TABLE'", | |
312 | $conn->escape_string($databaseName)); | |
313 | ||
314 | return array_map(function($arr) { | |
315 | return $arr['table_name']; | |
316 | }, self::fetchAll($conn, $sql)); | |
317 | } | |
318 | ||
319 | } |