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