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