1<?php 2 3namespace go\core\db; 4 5use Exception; 6use go\core\App; 7use go\core\fs\File; 8use PDO; 9use PDOException; 10 11class Utils { 12 13 /** 14 * Run all queries of a given SQL file 15 * 16 * @param File $file 17 * @param false $verbose 18 * @throws Exception 19 */ 20 public static function runSQLFile(File $file, $verbose = false) { 21 $queries = self::getSqlQueries($file); 22 23 try { 24 for ($i = 0, $c = count($queries); $i < $c; $i++) { 25 if(!empty($queries[$i])) { 26 if($verbose) { 27 echo "Executing SQL: " . $queries[$i] ."\n"; 28 } 29 App::get()->getDbConnection()->exec($queries[$i]); 30 } 31 } 32 } catch (PDOException $e) { 33 //var_dump($queries); 34 throw new Exception($e->getMessage() . ' on query (' . $i . ') ' . $queries[$i]); 35 } 36 } 37 38 /** 39 * Get's all queries from an SQL dump file in an array 40 * 41 * @param File $file The absolute path to the SQL file 42 * @access public 43 * @return array An array of SQL strings 44 * @throws Exception 45 */ 46 public static function getSqlQueries(File $file) { 47 $sql = ''; 48 $queries = array(); 49 50 $handle = $file->open('r'); 51 if ($handle) { 52 while (!feof($handle)) { 53 $buffer = trim(fgets($handle, 4096)); 54 if ($buffer != '' && substr($buffer, 0, 1) != '#' && substr($buffer, 0, 1) != '-') { 55 $sql .= $buffer . "\n"; 56 } 57 } 58 fclose($handle); 59 } else { 60 throw new Exception("Could not read SQL dump file $file!"); 61 } 62 $length = strlen($sql); 63 $in_string = false; 64 $start = 0; 65 $escaped = false; 66 for ($i = 0; $i < $length; $i++) { 67 $char = $sql[$i]; 68 if ($char == '\'' && !$escaped) { 69 $in_string = !$in_string; 70 } 71 if ($char == ';' && !$in_string) { 72 $offset = $i - $start; 73 $queries[] = trim(substr($sql, $start, $offset)); 74 75 $start = $i + 1; 76 } 77 if ($char == '\\') { 78 $escaped = true; 79 } else { 80 $escaped = false; 81 } 82 } 83 return $queries; 84 } 85 86 /** 87 * Check if a database exists 88 * 89 * @param string $tableName 90 * @return boolean 91 */ 92 public static function databaseExists($databaseName) { 93 $stmt = App::get()->getDbConnection()->query('SHOW DATABASES'); 94 while ($r = $stmt->fetch()) { 95 if ($r[0] == $databaseName) { 96 $stmt->closeCursor(); 97 return true; 98 } 99 } 100 101 return false; 102 } 103 104 /** 105 * Check if a table exists in the Group-Office database. 106 * 107 * @param string $tableName 108 * @return boolean 109 */ 110 public static function tableExists($tableName) { 111 112 $stmt = App::get()->getDbConnection()->query('SHOW TABLES'); 113 $stmt->setFetchMode(PDO::FETCH_COLUMN, 0); 114 $tableNames = $stmt->fetchAll(); 115 116 return in_array($tableName, $tableNames); 117 } 118 119 /** 120 * Check if a column exists 121 * 122 * @param string $tableName 123 * @param string $columnName 124 * @return boolean 125 */ 126 public static function columnExists($tableName, $columnName) { 127 $sql = "SHOW FIELDS FROM `" . $tableName . "`"; 128 $stmt = App::get()->getDbConnection()->query($sql); 129 while ($record = $stmt->fetch(PDO::FETCH_ASSOC)) { 130 if ($record['Field'] == $columnName) { 131 return true; 132 } 133 } 134 return false; 135 } 136 137 138 139 /** 140 * Detect PDO param type for binding by checking the PHP variable type 141 * 142 * @param mixed $variable 143 * @return int 144 */ 145 public static function getPdoParamType($variable) { 146 if (is_bool($variable)) { 147 return PDO::PARAM_BOOL; 148 } elseif (is_int($variable)) { 149 return PDO::PARAM_INT; 150 } elseif (is_null($variable)) { 151 return PDO::PARAM_NULL; 152 } else { 153 return PDO::PARAM_STR; 154 } 155 } 156 157 /** 158 * Parse DSN connection string 159 * 160 * @param string $dsn eg mysql:host=db;port=8306;dbname=groupoffice 161 * @return ['scheme' => 'mysql', 'options' => ['host' => 'db']] 162 */ 163 public static function parseDSN($dsn) { 164 $dsn = substr($dsn, 6); //strip mysql: 165 $parts = str_getcsv($dsn, ';'); 166 $options = []; 167 foreach($parts as $part) { 168 $is = strpos($part, '='); 169 $options[substr($part,0, $is)] = substr($part, $is + 1); 170 } 171 172 return [ 173 'scheme' => 'mysql', 174 'options' => $options 175 ]; 176 } 177 178 179 public static function quoteTableName($name) { 180 //disallow \ ` and \00 : http://stackoverflow.com/questions/1542627/escaping-field-names-in-pdo-statements 181 // if (preg_match("/[`\\\\\\000,]/", $name)) { 182 // throw new Exception("Invalid characters found in column name: " . $name); 183 // } 184 185 return '`' . str_replace('`', '``', $name) . '`'; 186 } 187 188 public static function quoteColumnName($name) { 189 return self::quoteTableName($name); 190 } 191 192 193 public static function isUniqueKeyException(\PDOException $e) { 194 //Unique index error = 23000 195 if ($e->getCode() != 23000) { 196 return false; 197 } 198 199 $msg = $e->getMessage(); 200 //App::get()->debug($msg); 201 202 if(preg_match("/key '(.*)'/", $msg, $matches)) { 203 $key = $matches[1]; 204 return $key; 205 } 206 207 return false; 208 } 209 210} 211