setup/src/Setup/DbUtil.php

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