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)) { |
||
0 ignored issues
–
show
introduced
by
Loading history...
|
|||
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)) { |
||
0 ignored issues
–
show
|
|||
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
The expression
$data of type array<mixed,mixed> is implicitly converted to a boolean; are you sure this is intended? If so, consider using empty($expr) instead to make it clear that you intend to check for an array without elements.
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
Loading history...
|
|||
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 |