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']),
  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 }