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