4 use Civi\Setup\Exception\SqlException
;
12 public static function parseDsn($dsn) {
13 $parsed = parse_url($dsn);
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,
23 * Convert an datasource from array notation to URL notation.
28 public static function encodeDsn($db) {
29 return sprintf('mysql://%s:%s@%s/%s',
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);
50 * @throws SqlException
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()));
66 * Ex: '[1234:abcd]:123'
68 * Combination: [0 => string $host, 1 => numeric|NULL $port].
69 * Ex: ['localhost', NULL].
70 * Ex: ['127.0.0.1', 3306]
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);
81 return array($host, $port);
85 * Combine a host and port number.
88 * @param int|NULL $port
91 * Ex: '127.0.0.1:3307'.
93 public static function encodeHostPort($host, $port) {
94 return $host . ($port ?
(':' . $port) : '');
99 * @param string $SQLcontent
100 * @param bool $lineMode
101 * What does this mean? Seems weird.
103 public static function sourceSQL($db, $SQLcontent, $lineMode = FALSE) {
104 $conn = self
::connect($db);
106 $conn->query('SET NAMES ' . ($conn->server_version
< 50503 ?
'utf8' : 'utf8mb4'));
109 $string = $SQLcontent;
111 // change \r\n to fix windows issues
112 $string = str_replace("\r\n", "\n", $string);
114 //get rid of comments starting with # and --
116 $string = preg_replace("/^#[^\n]*$/m", "\n", $string);
117 $string = preg_replace("/^(--[^-]).*/m", "\n", $string);
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);
129 throw new SqlException("Cannot execute $query: " . mysqli_error($conn));
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);
141 // $string = trim($string);
142 // if (!empty($string)) {
143 // if ($result = $conn->query($string)) {
144 // if (is_object($result)) {
145 // mysqli_free_result($result);
149 // throw new SqlException("Cannot execute $string: " . mysqli_error($conn));
157 * Execute query. Ignore the results.
159 * @param \mysqli|array $conn
160 * The DB to query. Either a mysqli connection, or credentials for
163 * @throws SqlException
165 public static function execute($conn, $sql) {
166 $conn = is_array($conn) ? self
::connect($conn) : $conn;
167 $result = $conn->query($sql);
169 throw new SqlException("Cannot execute $sql: " . $conn->error
);
172 if ($result && $result !== TRUE) {
173 $result->free_result();
179 * Get all the results of a SQL query, as an array.
181 * @param \mysqli|array $conn
182 * The DB to query. Either a mysqli connection, or credentials for
188 public static function fetchAll($conn, $sql) {
189 $conn = is_array($conn) ? self
::connect($conn) : $conn;
190 $result = $conn->query($sql);
192 throw new SqlException("Cannot execute $sql: " . $conn->error
);
196 while ($row = $result->fetch_assoc()) {
199 $result->free_result();
205 * Get a list of views in the given database.
207 * @param \mysqli|array $conn
208 * The DB to query. Either a mysqli connection, or credentials for
210 * @param string $databaseName
212 * Ex: ['civicrm_view1', 'civicrm_view2']
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));
218 return array_map(function($arr) {
219 return $arr['table_name'];
220 }, self
::fetchAll($conn, $sql));
224 * Get a list of concrete tables in the given database.
226 * @param \mysqli|array $conn
227 * The DB to query. Either a mysqli connection, or credentials for
229 * @param string $databaseName
231 * Ex: ['civicrm_view1', 'civicrm_view2']
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));
237 return array_map(function($arr) {
238 return $arr['table_name'];
239 }, self
::fetchAll($conn, $sql));