1 | <?php |
||
2 | |||
3 | declare(strict_types=1); |
||
4 | |||
5 | namespace WebServCo\Framework\Database; |
||
6 | |||
7 | use WebServCo\Framework\Environment\Config; |
||
8 | use WebServCo\Framework\Exceptions\DatabaseException; |
||
9 | |||
10 | abstract class AbstractPdoDatabase extends \WebServCo\Framework\AbstractLibrary |
||
11 | { |
||
12 | use \WebServCo\Framework\Traits\DatabaseTrait; |
||
13 | use \WebServCo\Framework\Traits\DatabaseAddQueryTrait; |
||
14 | |||
15 | protected \PDO $db; |
||
16 | protected \PDOStatement $stmt; |
||
17 | |||
18 | abstract protected function getDataSourceName(string $host, int $port, string $dbname): string; |
||
19 | |||
20 | /** |
||
21 | * @param array<string,string|array<mixed>> $settings |
||
22 | */ |
||
23 | public function __construct(array $settings = []) |
||
24 | { |
||
25 | parent::__construct($settings); |
||
26 | |||
27 | try { |
||
28 | $dsn = $this->getDataSourceName( |
||
29 | Config::string('APP_DBMS_HOST'), |
||
30 | Config::int('APP_DBMS_PORT'), |
||
31 | Config::string('APP_DBMS_DBNAME'), |
||
32 | ); |
||
33 | $this->db = new \PDO( |
||
34 | $dsn, |
||
35 | Config::string('APP_DBMS_USERNAME'), |
||
36 | Config::string('APP_DBMS_PASSWD'), |
||
37 | [ |
||
38 | \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC, |
||
39 | \PDO::ATTR_EMULATE_PREPARES => false, |
||
40 | \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION, |
||
41 | \PDO::ATTR_PERSISTENT => false, |
||
42 | ], |
||
43 | ); |
||
44 | } catch (\Throwable $e) { // PDOException/RuntimeException/Exception |
||
45 | throw new DatabaseException($e->getMessage(), $e); |
||
46 | } |
||
47 | } |
||
48 | |||
49 | public function affectedRows(): int |
||
50 | { |
||
51 | if (!($this->stmt instanceof \PDOStatement)) { |
||
52 | throw new DatabaseException('No Statement object available.'); |
||
53 | } |
||
54 | return $this->stmt->rowCount(); |
||
55 | } |
||
56 | |||
57 | public function escape(string $string): string |
||
58 | { |
||
59 | return $this->db->quote($string); |
||
60 | } |
||
61 | |||
62 | /** |
||
63 | * @param array<int,float|int|string> $params |
||
64 | * @return bool|int|string|null |
||
65 | */ |
||
66 | public function getColumn(string $query, array $params = [], int $columnNumber = 0) |
||
67 | { |
||
68 | $this->query($query, $params); |
||
69 | return $this->stmt->fetchColumn($columnNumber); |
||
70 | } |
||
71 | |||
72 | public function getPdo(): \PDO |
||
73 | { |
||
74 | return $this->db; |
||
75 | } |
||
76 | |||
77 | /** |
||
78 | * @param array<int,float|int|string> $params |
||
79 | * @return array<string,float|int|string|null> |
||
80 | */ |
||
81 | public function getRow(string $query, array $params = []): array |
||
82 | { |
||
83 | $this->query($query, $params); |
||
84 | $result = $this->stmt->fetch(\PDO::FETCH_ASSOC); |
||
85 | return $this->handleStatementReturn($result); |
||
86 | } |
||
87 | |||
88 | /** |
||
89 | * @param array<int,float|int|string> $params |
||
90 | * @return array<int,array<string,float|int|string|null>> |
||
91 | */ |
||
92 | public function getRows(string $query, array $params = []): array |
||
93 | { |
||
94 | $this->query($query, $params); |
||
95 | $result = $this->stmt->fetchAll(\PDO::FETCH_ASSOC); |
||
96 | return $this->handleStatementReturn($result); |
||
97 | } |
||
98 | |||
99 | /** |
||
100 | * Get last inserted Id. |
||
101 | * |
||
102 | * https://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id |
||
103 | * If you insert multiple rows using a single INSERT statement, |
||
104 | * LAST_INSERT_ID() returns the value generated for the first inserted row only. |
||
105 | * The reason for this is to make it possible to reproduce easily the same |
||
106 | * INSERT statement against some other server. |
||
107 | * |
||
108 | * PDO: |
||
109 | * Returns the ID of the last inserted row, or the last value from a sequence object, |
||
110 | * depending on the underlying driver. |
||
111 | * For example, PDO_PGSQL requires you to specify the name of a sequence object for the name parameter. |
||
112 | */ |
||
113 | public function lastInsertId(string $name = ''): int |
||
114 | { |
||
115 | return (int) $this->db->lastInsertId($name); |
||
116 | } |
||
117 | |||
118 | public function numRows(): int |
||
119 | { |
||
120 | if (!($this->stmt instanceof \PDOStatement)) { |
||
121 | throw new DatabaseException('No Statement object available.'); |
||
122 | } |
||
123 | return $this->stmt->rowCount(); |
||
124 | } |
||
125 | |||
126 | /** |
||
127 | * @param array<int,float|int|string|null> $params |
||
128 | */ |
||
129 | public function query(string $query, array $params = []): \PDOStatement |
||
130 | { |
||
131 | if (!$query) { |
||
132 | throw new DatabaseException('No query specified.'); |
||
133 | } |
||
134 | |||
135 | try { |
||
136 | $this->stmt = $this->db->prepare($query); |
||
137 | if ($params) { |
||
138 | $this->bindParams($params); |
||
139 | } |
||
140 | $this->stmt->execute(); |
||
141 | return $this->stmt; |
||
142 | } catch (\Throwable $e) { // \PDOException, \RuntimeException |
||
143 | throw new DatabaseException($e->getMessage(), $e); |
||
144 | } |
||
145 | } |
||
146 | |||
147 | /** |
||
148 | * @param mixed $value |
||
149 | */ |
||
150 | public function setAttribute(int $attribute, $value): bool |
||
151 | { |
||
152 | return $this->db->setAttribute($attribute, $value); |
||
153 | } |
||
154 | |||
155 | /** |
||
156 | * Perform a transaction. |
||
157 | * |
||
158 | * Returns lastInsertId regardless of queries executed as it's not possible to retrieve lastInsertId after commit |
||
159 | * |
||
160 | * @param array<int,array<int,mixed>> $queries |
||
161 | */ |
||
162 | public function transaction(array $queries): int |
||
163 | { |
||
164 | try { |
||
165 | $this->db->beginTransaction(); |
||
166 | foreach ($queries as $item) { |
||
167 | if (!isset($item[0])) { |
||
168 | throw new DatabaseException('No query specified.'); |
||
169 | } |
||
170 | $params = $item[1] ?? []; |
||
171 | $this->query($item[0], $params); |
||
172 | } |
||
173 | $lastInsertId = (int) $this->db->lastInsertId(); |
||
174 | $this->db->commit(); |
||
175 | return $lastInsertId; |
||
176 | } catch (\Throwable $e) { // DatabaseException, \PDOException, \RuntimeException |
||
177 | $this->db->rollBack(); |
||
178 | throw new DatabaseException($e->getMessage(), $e); |
||
179 | } |
||
180 | } |
||
181 | |||
182 | /** |
||
183 | * @param array<mixed> $data |
||
184 | */ |
||
185 | protected function bindParams(array $data): bool |
||
186 | { |
||
187 | if (!$data) { |
||
0 ignored issues
–
show
|
|||
188 | return false; |
||
189 | } |
||
190 | |||
191 | $i = 1; |
||
192 | foreach ($data as $item) { |
||
193 | if (\is_array($item)) { |
||
194 | foreach ($item as $v) { |
||
195 | $this->validateParam($v); |
||
196 | $this->stmt->bindValue($i, $v, $this->getDataType((string) $v)); |
||
197 | $i++; |
||
198 | } |
||
199 | } else { |
||
200 | $this->validateParam($item); |
||
201 | $this->stmt->bindValue($i, $item, $this->getDataType((string) $item)); |
||
202 | $i++; |
||
203 | } |
||
204 | } |
||
205 | return true; |
||
206 | } |
||
207 | |||
208 | protected function getDataType(string $variable): int |
||
209 | { |
||
210 | $type = \gettype($variable); |
||
211 | |||
212 | switch ($type) { |
||
213 | case 'NULL': |
||
214 | return \PDO::PARAM_NULL; |
||
215 | case 'integer': |
||
216 | return \PDO::PARAM_INT; |
||
217 | case 'boolean': |
||
218 | // causes data not to be inserted |
||
219 | //return \PDO::PARAM_BOOL; |
||
220 | case 'string': |
||
221 | case 'double': |
||
222 | case 'array': |
||
223 | case 'object': |
||
224 | case 'resource': |
||
225 | case 'resource (closed)': |
||
226 | case 'unknown type': |
||
227 | default: |
||
228 | return \PDO::PARAM_STR; |
||
229 | } |
||
230 | } |
||
231 | |||
232 | /** |
||
233 | * @param mixed $param |
||
234 | */ |
||
235 | protected function validateParam($param): bool |
||
236 | { |
||
237 | if (\is_array($param)) { |
||
238 | throw new DatabaseException('Parameter is an array.'); |
||
239 | } |
||
240 | return true; |
||
241 | } |
||
242 | |||
243 | /** |
||
244 | * Make sure PDO Statement returns an array when there are no errors. |
||
245 | * |
||
246 | * "In all cases, false is returned on failure." |
||
247 | * However, false is also returned when there are no results. |
||
248 | * |
||
249 | * @param bool|array<mixed> $result |
||
250 | * @return array<mixed> |
||
251 | */ |
||
252 | protected function handleStatementReturn($result): array |
||
253 | { |
||
254 | if (\is_array($result)) { |
||
255 | // All is ok. |
||
256 | return $result; |
||
257 | } |
||
258 | $errorInfo = $this->stmt->errorInfo(); |
||
259 | // 0 = "SQLSTATE" |
||
260 | // 1 = "Driver specific error code" |
||
261 | // 2 = "Driver specific error message" |
||
262 | if ('00000' === $errorInfo[0]) { |
||
263 | // "Successful completion", so no results |
||
264 | return []; |
||
265 | } |
||
266 | throw new DatabaseException($errorInfo[2]); |
||
267 | } |
||
268 | } |
||
269 |
This check marks implicit conversions of arrays to boolean values in a comparison. While in PHP an empty array is considered to be equal (but not identical) to false, this is not always apparent.
Consider making the comparison explicit by using
empty(..)
or! empty(...)
instead.