1 <?php
2
3 namespace Alo\Db;
4
5 use PDO;
6
7 if (!defined('GEN_START')) {
8 http_response_code(404);
9 } else {
10
11 \Alo::loadConfig('db' . DIRECTORY_SEPARATOR . 'mysql');
12
13 /**
14 * MySQL database manager
15 *
16 * @author Art <a.molcanovas@gmail.com>
17 * @author Art <a.molcanovas@gmail.com>
18 */
19 class MySQL extends AbstractDb {
20
21 /**
22 * The PDO instance
23 *
24 * @var PDO
25 */
26 protected $pdo;
27
28 /**
29 * Instantiates the database connection
30 *
31 * @author Art <a.molcanovas@gmail.com>
32 *
33 * @param string $ip The IP address to use
34 * @param int $port The port to use
35 * @param string $user The username
36 * @param string $pw The password
37 * @param string $db The database to use
38 * @param string $cache Which cache interface to use
39 * @param array $options Connection options
40 */
41 function __construct($ip = ALO_MYSQL_SERVER,
42 $port = ALO_MYSQL_PORT,
43 $user = ALO_MYSQL_USER,
44 $pw = ALO_MYSQL_PW,
45 $db = ALO_MYSQL_DATABASE,
46 $cache = ALO_MYSQL_CACHE,
47 array $options = null) {
48
49 $this->pdo =
50 new PDO('mysql:dbname=' . $db . ';host=' . $ip . ';charset=' . ALO_MYSQL_CHARSET . ';port=' . $port,
51 $user,
52 $pw,
53 $options);
54
55 $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
56 $this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
57
58 $this->cachePrefix = ALO_MYSQL_CACHE_PREFIX;
59 parent::__construct($cache);
60 \Log::debug('Initialised MySQL database connection');
61 }
62
63 /**
64 * Instantiates the database connection
65 *
66 * @author Art <a.molcanovas@gmail.com>
67 *
68 * @param string $ip The IP address to use
69 * @param int $port The port to use
70 * @param string $user The username
71 * @param string $pw The password
72 * @param string $db The database to use
73 * @param string $cache Which cache interface to use
74 * @param array $options Connection options
75 *
76 * @return MySQL
77 */
78 static function mysql($ip = ALO_MYSQL_SERVER,
79 $port = ALO_MYSQL_PORT,
80 $user = ALO_MYSQL_USER,
81 $pw = ALO_MYSQL_PW,
82 $db = ALO_MYSQL_DATABASE,
83 $cache = ALO_MYSQL_CACHE,
84 array $options = null) {
85 return new MySQL($ip, $port, $user, $pw, $db, $cache, $options);
86 }
87
88 /**
89 * Returns an aggregated one-column result set, e.g. from a count(*) query
90 *
91 * @author Art <a.molcanovas@gmail.com>
92 *
93 * @param string $sql The SQL code
94 * @param array $params Bound parameters
95 * @param array $settings Optional settings
96 *
97 * @return int|float
98 */
99 function aggregate($sql, $params = null, array $settings = []) {
100 $settings = \array_merge(self::$defaultSettings, $settings);
101 $hash = $this->hash($sql, $params, $settings[self::V_PREFIX]);
102 $cache = $settings[self::V_CACHE];
103
104 if ($settings[self::V_CACHE] && $get = $this->cache->get($hash)) {
105 return $get;
106 } else {
107 $settings[self::V_FETCH_NUM] = true;
108 $settings[self::V_CACHE] = false;
109 $prep = $this->prepQuery($sql, $params, $settings);
110 $result = null;
111
112 if ($prep) {
113 $result = strpos($prep[0][0], '.') === false ? (int)$prep[0][0] : (float)$prep[0][0];
114 }
115
116 if ($cache) {
117 $this->cache->set($hash, $result);
118 }
119
120 return $result;
121 }
122 }
123
124 /**
125 * Executes a prepared query and returns the results
126 *
127 * @author Art <a.molcanovas@gmail.com>
128 *
129 * @param string $sql The SQL code
130 * @param array $params Bound parameters
131 * @param array $settings Optional settings
132 *
133 * @return array|boolean Result array on SELECT queries, TRUE/FALSE for others
134 */
135 function prepQuery($sql, $params = null, array $settings = []) {
136 $settings = \array_merge(self::$defaultSettings, $settings);
137 $hash = $this->hash($sql, $params, $settings[self::V_PREFIX]);
138
139 if (stripos($sql, 'insert into') !== false || stripos($sql, 'replace into') !== false) {
140 $settings[self::V_CACHE] = false;
141 }
142
143 if ($settings[self::V_CACHE] && $get = $this->cache->get($hash)) {
144 return $get;
145 } else {
146 $pdo = $this->pdo->prepare($sql);
147 $exec = $pdo->execute($params);
148 $res =
149 stripos($sql, 'select') !== false ?
150 $pdo->fetchAll($settings[self::V_FETCH_NUM] ? PDO::FETCH_NUM : PDO::FETCH_ASSOC) : $exec;
151
152 if ($settings[self::V_CACHE]) {
153 $this->cache->set($hash, $res, $settings[self::V_TIME]);
154 }
155
156 return $res;
157 }
158 }
159
160 /**
161 * Begins a transaction
162 *
163 * @author Art <a.molcanovas@gmail.com>
164 * @return MySQL
165 */
166 function beginTransaction() {
167 return $this->pdo->inTransaction() ? true : $this->pdo->beginTransaction();
168 }
169
170 /**
171 * Commits a transaction
172 *
173 * @author Art <a.molcanovas@gmail.com>
174 * @return MySQL
175 */
176 function commit() {
177 return $this->pdo->inTransaction() ? $this->pdo->commit() : true;
178 }
179
180 /**
181 * Prepares a statement
182 *
183 * @author Art <a.molcanovas@gmail.com>
184 *
185 * @param string $sql A SQL statement to prepare
186 *
187 * @return \PDOStatement
188 */
189 function prepare($sql) {
190 return $this->pdo->prepare($sql);
191 }
192
193 /**
194 * Executes a quick unescaped query without preparing it
195 *
196 * @author Art <a.molcanovas@gmail.com>
197 *
198 * @param string $sql SQL code
199 *
200 * @return array|boolean Result array on SELECT queries, TRUE/FALSE for others
201 */
202 function query($sql) {
203 $s = $this->pdo->query($sql);
204
205 return stripos($sql, 'select') !== false ? $s->fetchAll(PDO::FETCH_ASSOC) : $s !== false;
206
207 }
208
209 /**
210 * Rolls back a transaction
211 *
212 * @author Art <a.molcanovas@gmail.com>
213 * @return MySQL
214 */
215 function rollBack() {
216 return $this->pdo->inTransaction() ? $this->pdo->rollBack() : true;
217 }
218
219 /**
220 * Checks whether a transaction is active
221 *
222 * @author Art <a.molcanovas@gmail.com>
223 * @return boolean
224 */
225 function transactionActive() {
226 return $this->pdo->inTransaction();
227 }
228
229 /**
230 * Returns the last inserted auto-increment ID
231 * @author Art <a.molcanovas@gmail.com>
232 *
233 * @return int
234 */
235 function lastInsertID() {
236 return $this->pdo->lastInsertId();
237 }
238
239 /**
240 * Handles direct calls to PDO
241 *
242 * @author Art <a.molcanovas@gmail.com>
243 *
244 * @param string $name Method name
245 * @param array $arguments Array of parameters
246 *
247 * @return mixed
248 */
249 function __call($name, $arguments) {
250 return call_user_func_array([$this->pdo, $name], $arguments);
251 }
252
253 }
254 }
255