| 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) { |
| 13 | $parsed = parse_url($dsn); |
| 14 | return array( |
| 15 | 'server' => self::encodeHostPort($parsed['host'], $parsed['port']), |
| 16 | 'username' => $parsed['user'] ?: NULL, |
| 17 | 'password' => $parsed['pass'] ?: NULL, |
| 18 | 'database' => $parsed['path'] ? ltrim($parsed['path'], '/') : NULL, |
| 19 | ); |
| 20 | } |
| 21 | |
| 22 | /** |
| 23 | * Convert an datasource from array notation to URL notation. |
| 24 | * |
| 25 | * @param array $db |
| 26 | * @return string |
| 27 | */ |
| 28 | public static function encodeDsn($db) { |
| 29 | return sprintf('mysql://%s:%s@%s/%s', |
| 30 | $db['username'], |
| 31 | $db['password'], |
| 32 | $db['server'], |
| 33 | $db['database'] |
| 34 | ); |
| 35 | } |
| 36 | |
| 37 | /** |
| 38 | * @param array $db |
| 39 | * @return \mysqli |
| 40 | */ |
| 41 | public static function softConnect($db) { |
| 42 | list($host, $port) = self::decodeHostPort($db['server']); |
| 43 | $conn = @mysqli_connect($host, $db['username'], $db['password'], $db['database'], $port); |
| 44 | return $conn; |
| 45 | } |
| 46 | |
| 47 | /** |
| 48 | * @param array $db |
| 49 | * @return \mysqli |
| 50 | * @throws SqlException |
| 51 | */ |
| 52 | public static function connect($db) { |
| 53 | $conn = self::softConnect($db); |
| 54 | if (mysqli_connect_errno()) { |
| 55 | throw new SqlException(sprintf("Connection failed: %s\n", mysqli_connect_error())); |
| 56 | } |
| 57 | return $conn; |
| 58 | } |
| 59 | |
| 60 | /** |
| 61 | * @param string $host |
| 62 | * Ex: 'localhost', |
| 63 | * Ex: 'localhost:123' |
| 64 | * Ex: '127.0.0.1:123' |
| 65 | * Ex: '[1234:abcd]' |
| 66 | * Ex: '[1234:abcd]:123' |
| 67 | * @return array |
| 68 | * Combination: [0 => string $host, 1 => numeric|NULL $port]. |
| 69 | * Ex: ['localhost', NULL]. |
| 70 | * Ex: ['127.0.0.1', 3306] |
| 71 | */ |
| 72 | public static function decodeHostPort($host) { |
| 73 | $hostParts = explode(':', $host); |
| 74 | if (count($hostParts) > 1 && strrpos($host, ']') !== strlen($host) - 1) { |
| 75 | $port = array_pop($hostParts); |
| 76 | $host = implode(':', $hostParts); |
| 77 | } |
| 78 | else { |
| 79 | $port = NULL; |
| 80 | } |
| 81 | return array($host, $port); |
| 82 | } |
| 83 | |
| 84 | /** |
| 85 | * Combine a host and port number. |
| 86 | * |
| 87 | * @param string $host |
| 88 | * @param int|NULL $port |
| 89 | * @return string |
| 90 | * Ex: 'localhost'. |
| 91 | * Ex: '127.0.0.1:3307'. |
| 92 | */ |
| 93 | public static function encodeHostPort($host, $port) { |
| 94 | return $host . ($port ? (':' . $port) : ''); |
| 95 | } |
| 96 | |
| 97 | /** |
| 98 | * @param array $db |
| 99 | * @param string $SQLcontent |
| 100 | * @param bool $lineMode |
| 101 | * What does this mean? Seems weird. |
| 102 | */ |
| 103 | public static function sourceSQL($db, $SQLcontent, $lineMode = FALSE) { |
| 104 | $conn = self::connect($db); |
| 105 | |
| 106 | $conn->query('SET NAMES ' . ($conn->server_version < 50503 ? 'utf8' : 'utf8mb4')); |
| 107 | |
| 108 | if (!$lineMode) { |
| 109 | $string = $SQLcontent; |
| 110 | |
| 111 | // change \r\n to fix windows issues |
| 112 | $string = str_replace("\r\n", "\n", $string); |
| 113 | |
| 114 | //get rid of comments starting with # and -- |
| 115 | |
| 116 | $string = preg_replace("/^#[^\n]*$/m", "\n", $string); |
| 117 | $string = preg_replace("/^(--[^-]).*/m", "\n", $string); |
| 118 | |
| 119 | $queries = preg_split('/;\s*$/m', $string); |
| 120 | foreach ($queries as $query) { |
| 121 | $query = trim($query); |
| 122 | if (!empty($query)) { |
| 123 | if ($result = $conn->query($query)) { |
| 124 | if (is_object($result)) { |
| 125 | mysqli_free_result($result); |
| 126 | } |
| 127 | } |
| 128 | else { |
| 129 | throw new SqlException("Cannot execute $query: " . mysqli_error($conn)); |
| 130 | } |
| 131 | } |
| 132 | } |
| 133 | } |
| 134 | else { |
| 135 | throw new \RuntimeException("Not implemented: lineMode"); |
| 136 | // $fd = fopen($SQLcontent, "r"); |
| 137 | // while ($string = fgets($fd)) { |
| 138 | // $string = preg_replace("/^#[^\n]*$/m", "\n", $string); |
| 139 | // $string = preg_replace("/^(--[^-]).*/m", "\n", $string); |
| 140 | // |
| 141 | // $string = trim($string); |
| 142 | // if (!empty($string)) { |
| 143 | // if ($result = $conn->query($string)) { |
| 144 | // if (is_object($result)) { |
| 145 | // mysqli_free_result($result); |
| 146 | // } |
| 147 | // } |
| 148 | // else { |
| 149 | // throw new SqlException("Cannot execute $string: " . mysqli_error($conn)); |
| 150 | // } |
| 151 | // } |
| 152 | // } |
| 153 | } |
| 154 | } |
| 155 | |
| 156 | /** |
| 157 | * Execute query. Ignore the results. |
| 158 | * |
| 159 | * @param \mysqli|array $conn |
| 160 | * The DB to query. Either a mysqli connection, or credentials for |
| 161 | * establishing one. |
| 162 | * @param string $sql |
| 163 | * @throws SqlException |
| 164 | */ |
| 165 | public static function execute($conn, $sql) { |
| 166 | $conn = is_array($conn) ? self::connect($conn) : $conn; |
| 167 | $result = $conn->query($sql); |
| 168 | if (!$result) { |
| 169 | throw new SqlException("Cannot execute $sql: " . $conn->error); |
| 170 | } |
| 171 | |
| 172 | if ($result && $result !== TRUE) { |
| 173 | $result->free_result(); |
| 174 | } |
| 175 | |
| 176 | } |
| 177 | |
| 178 | /** |
| 179 | * Get all the results of a SQL query, as an array. |
| 180 | * |
| 181 | * @param \mysqli|array $conn |
| 182 | * The DB to query. Either a mysqli connection, or credentials for |
| 183 | * establishing one. |
| 184 | * @param string $sql |
| 185 | * @return array |
| 186 | * @throws \Exception |
| 187 | */ |
| 188 | public static function fetchAll($conn, $sql) { |
| 189 | $conn = is_array($conn) ? self::connect($conn) : $conn; |
| 190 | $result = $conn->query($sql); |
| 191 | if (!$result) { |
| 192 | throw new SqlException("Cannot execute $sql: " . $conn->error); |
| 193 | } |
| 194 | |
| 195 | $rows = array(); |
| 196 | while ($row = $result->fetch_assoc()) { |
| 197 | $rows[] = $row; |
| 198 | } |
| 199 | $result->free_result(); |
| 200 | |
| 201 | return $rows; |
| 202 | } |
| 203 | |
| 204 | /** |
| 205 | * Get a list of views in the given database. |
| 206 | * |
| 207 | * @param \mysqli|array $conn |
| 208 | * The DB to query. Either a mysqli connection, or credentials for |
| 209 | * establishing one. |
| 210 | * @param string $databaseName |
| 211 | * @return array |
| 212 | * Ex: ['civicrm_view1', 'civicrm_view2'] |
| 213 | */ |
| 214 | public static function findViews($conn, $databaseName) { |
| 215 | $sql = sprintf("SELECT table_name FROM information_schema.TABLES WHERE TABLE_SCHEMA='%s' AND TABLE_TYPE = 'VIEW'", |
| 216 | $conn->escape_string($databaseName)); |
| 217 | |
| 218 | return array_map(function($arr) { |
| 219 | return $arr['table_name']; |
| 220 | }, self::fetchAll($conn, $sql)); |
| 221 | } |
| 222 | |
| 223 | /** |
| 224 | * Get a list of concrete tables in the given database. |
| 225 | * |
| 226 | * @param \mysqli|array $conn |
| 227 | * The DB to query. Either a mysqli connection, or credentials for |
| 228 | * establishing one. |
| 229 | * @param string $databaseName |
| 230 | * @return array |
| 231 | * Ex: ['civicrm_view1', 'civicrm_view2'] |
| 232 | */ |
| 233 | public static function findTables($conn, $databaseName) { |
| 234 | $sql = sprintf("SELECT table_name FROM information_schema.TABLES WHERE TABLE_SCHEMA='%s' AND TABLE_TYPE = 'BASE TABLE'", |
| 235 | $conn->escape_string($databaseName)); |
| 236 | |
| 237 | return array_map(function($arr) { |
| 238 | return $arr['table_name']; |
| 239 | }, self::fetchAll($conn, $sql)); |
| 240 | } |
| 241 | |
| 242 | } |