1
|
|
|
<?php |
2
|
|
|
namespace Redaxscript; |
3
|
|
|
|
4
|
|
|
use ORM; |
5
|
|
|
use PDO; |
6
|
|
|
use PDOException; |
7
|
|
|
use function array_column; |
8
|
|
|
use function implode; |
9
|
|
|
use function strstr; |
10
|
|
|
|
11
|
|
|
/** |
12
|
|
|
* children class to handle the database |
13
|
|
|
* |
14
|
|
|
* @since 2.2.0 |
15
|
|
|
* |
16
|
|
|
* @package Redaxscript |
17
|
|
|
* @category Db |
18
|
|
|
* @author Henry Ruhs |
19
|
|
|
* |
20
|
|
|
* @method $this _addJoinSource(string $operator, string $table, string|array $constraint, string $tableAlias) |
21
|
|
|
* @method $this _addOrderBy(string $column, string $value) |
22
|
|
|
* @method $this _addWhere(string $clause, string|array $value) |
23
|
|
|
*/ |
24
|
|
|
|
25
|
|
|
class Db extends ORM |
26
|
|
|
{ |
27
|
|
|
/** |
28
|
|
|
* instance of the config class |
29
|
|
|
* |
30
|
|
|
* @var Config |
31
|
|
|
*/ |
32
|
|
|
|
33
|
|
|
protected static $_config; |
34
|
|
|
|
35
|
|
|
/** |
36
|
|
|
* constructor of the class |
37
|
|
|
* |
38
|
|
|
* @since 2.6.0 |
39
|
|
|
* |
40
|
|
|
* @param Config $config instance of the config class |
41
|
|
|
*/ |
42
|
|
|
|
43
|
4 |
|
public static function construct(Config $config) : void |
44
|
|
|
{ |
45
|
4 |
|
self::$_config = $config; |
46
|
4 |
|
} |
47
|
|
|
|
48
|
|
|
/** |
49
|
|
|
* init the class |
50
|
|
|
* |
51
|
|
|
* @since 3.1.0 |
52
|
|
|
*/ |
53
|
|
|
|
54
|
4 |
|
public static function init() : void |
55
|
|
|
{ |
56
|
4 |
|
$dbType = self::$_config->get('dbType'); |
57
|
4 |
|
$dbHost = self::$_config->get('dbHost'); |
58
|
4 |
|
$dbName = self::$_config->get('dbName'); |
59
|
4 |
|
$dbUser = self::$_config->get('dbUser'); |
60
|
4 |
|
$dbPassword = self::$_config->get('dbPassword'); |
61
|
4 |
|
$dbSocket = strstr($dbHost, '.sock'); |
62
|
|
|
|
63
|
|
|
/* handle various types */ |
64
|
|
|
|
65
|
4 |
|
if ($dbType === 'mssql' || $dbType === 'mysql' || $dbType === 'pgsql') |
66
|
|
|
{ |
67
|
3 |
|
if ($dbType === 'mssql') |
68
|
|
|
{ |
69
|
1 |
|
self::configure('connection_string', 'sqlsrv:server=' . $dbHost . ';database=' . $dbName); |
70
|
|
|
} |
71
|
3 |
|
if ($dbType === 'mysql') |
72
|
|
|
{ |
73
|
1 |
|
self::configure('connection_string', 'mysql:' . ($dbSocket ? 'unix_socket' : 'host') . '=' . $dbHost . ';dbname=' . $dbName . ';charset=utf8'); |
74
|
|
|
} |
75
|
3 |
|
if ($dbType === 'pgsql') |
76
|
|
|
{ |
77
|
1 |
|
self::configure('connection_string', 'pgsql:' . ($dbSocket ? 'unix_socket' : 'host') . '=' . $dbHost . ';dbname=' . $dbName . ';options=--client_encoding=utf8'); |
78
|
|
|
} |
79
|
|
|
|
80
|
|
|
/* username and password */ |
81
|
|
|
|
82
|
3 |
|
self::configure('username', $dbUser); |
83
|
3 |
|
if ($dbPassword) |
|
|
|
|
84
|
|
|
{ |
85
|
3 |
|
self::configure('password', $dbPassword); |
86
|
|
|
} |
87
|
|
|
} |
88
|
|
|
|
89
|
|
|
/* else handle sqlite */ |
90
|
|
|
|
91
|
4 |
|
if ($dbType === 'sqlite') |
92
|
|
|
{ |
93
|
1 |
|
self::configure('sqlite:' . $dbHost); |
94
|
|
|
} |
95
|
|
|
|
96
|
|
|
/* general */ |
97
|
|
|
|
98
|
4 |
|
self::configure( |
99
|
|
|
[ |
100
|
4 |
|
'caching' => true, |
101
|
|
|
'caching_auto_clear' => true, |
102
|
|
|
'return_result_sets' => true |
103
|
|
|
]); |
104
|
4 |
|
} |
105
|
|
|
|
106
|
|
|
/** |
107
|
|
|
* get the database status |
108
|
|
|
* |
109
|
|
|
* @since 3.1.0 |
110
|
|
|
* |
111
|
|
|
* @return int |
112
|
|
|
*/ |
113
|
|
|
|
114
|
1 |
|
public static function getStatus() : int |
115
|
|
|
{ |
116
|
1 |
|
$output = 0; |
117
|
|
|
|
118
|
|
|
/* has connection */ |
119
|
|
|
|
120
|
|
|
try |
121
|
|
|
{ |
122
|
1 |
|
$dbType = self::$_config->get('dbType') === 'mssql' ? 'sqlsrv' : self::$_config->get('dbType'); |
123
|
1 |
|
$dbDriver = self::getDb()->getAttribute(PDO::ATTR_DRIVER_NAME); |
124
|
1 |
|
if ($dbType === $dbDriver) |
125
|
|
|
{ |
126
|
1 |
|
$output = self::countTablePrefix() > 7 ? 2 : 1; |
127
|
|
|
} |
128
|
|
|
} |
129
|
|
|
catch (PDOException $exception) |
130
|
|
|
{ |
131
|
|
|
$output = 0; |
132
|
|
|
} |
133
|
1 |
|
return $output; |
134
|
|
|
} |
135
|
|
|
|
136
|
|
|
/** |
137
|
|
|
* set the auto increment |
138
|
|
|
* |
139
|
|
|
* @since 4.0.0 |
140
|
|
|
* |
141
|
|
|
* @param string $table name of the table |
142
|
|
|
* @param int $increment value of the auto increment |
143
|
|
|
* |
144
|
|
|
* @return bool |
145
|
|
|
*/ |
146
|
|
|
|
147
|
|
|
public static function setAutoIncrement(string $table = null, int $increment = 0) : bool |
148
|
|
|
{ |
149
|
|
|
$dbType = self::$_config->get('dbType'); |
150
|
|
|
$dbPrefix = self::$_config->get('dbPrefix'); |
151
|
|
|
|
152
|
|
|
/* mysql */ |
153
|
|
|
|
154
|
|
|
if ($dbType === 'mysql') |
155
|
|
|
{ |
156
|
|
|
return self::rawExecute('ALTER TABLE ' . $dbPrefix . $table . ' AUTO_INCREMENT = ' . $increment); |
157
|
|
|
} |
158
|
|
|
return false; |
159
|
|
|
} |
160
|
|
|
|
161
|
|
|
/** |
162
|
|
|
* count table with prefix |
163
|
|
|
* |
164
|
|
|
* @since 3.1.0 |
165
|
|
|
* |
166
|
|
|
* @return int |
167
|
|
|
*/ |
168
|
|
|
|
169
|
2 |
|
public static function countTablePrefix() : int |
170
|
|
|
{ |
171
|
2 |
|
$output = 0; |
172
|
2 |
|
$dbType = self::$_config->get('dbType'); |
173
|
2 |
|
$dbName = self::$_config->get('dbName'); |
174
|
2 |
|
$dbPrefix = self::$_config->get('dbPrefix'); |
175
|
|
|
|
176
|
|
|
/* mssql and mysql */ |
177
|
|
|
|
178
|
2 |
|
if ($dbType === 'mssql' || $dbType === 'mysql') |
179
|
|
|
{ |
180
|
|
|
$output = self::forTable('information_schema.tables') |
181
|
|
|
->where($dbType === 'mssql' ? 'table_catalog' : 'table_schema', $dbName) |
182
|
|
|
->whereLike('table_name', $dbPrefix . '%') |
183
|
|
|
->count(); |
184
|
|
|
} |
185
|
|
|
|
186
|
|
|
/* pgsql */ |
187
|
|
|
|
188
|
2 |
|
if ($dbType === 'pgsql') |
189
|
|
|
{ |
190
|
|
|
$output = self::forTable('pg_catalog.pg_tables') |
191
|
|
|
->whereLike('tablename', $dbPrefix . '%') |
192
|
|
|
->whereNotLike('tablename', 'pg_%') |
193
|
|
|
->whereNotLike('tablename', 'sql_%') |
194
|
|
|
->count(); |
195
|
|
|
} |
196
|
|
|
|
197
|
|
|
/* sqlite */ |
198
|
|
|
|
199
|
2 |
|
if ($dbType === 'sqlite') |
200
|
|
|
{ |
201
|
2 |
|
$output = self::forTable('sqlite_master') |
202
|
2 |
|
->whereLike('tbl_name', $dbPrefix . '%') |
203
|
2 |
|
->whereNotLike('tbl_name', 'sql_%') |
204
|
2 |
|
->count(); |
205
|
|
|
} |
206
|
2 |
|
return $output; |
207
|
|
|
} |
208
|
|
|
|
209
|
|
|
/** |
210
|
|
|
* for table with prefix |
211
|
|
|
* |
212
|
|
|
* @since 2.2.0 |
213
|
|
|
* |
214
|
|
|
* @param string $table name of the table |
215
|
|
|
* @param string $connection which connection to use |
216
|
|
|
* |
217
|
|
|
* @return self |
218
|
|
|
*/ |
219
|
|
|
|
220
|
14 |
|
public static function forTablePrefix(string $table = null, string $connection = self::DEFAULT_CONNECTION) : self |
221
|
|
|
{ |
222
|
14 |
|
return new self(self::$_config->get('dbPrefix') . $table, [], $connection); |
223
|
|
|
} |
224
|
|
|
|
225
|
|
|
/** |
226
|
|
|
* left join with prefix |
227
|
|
|
* |
228
|
|
|
* @since 2.2.0 |
229
|
|
|
* |
230
|
|
|
* @param string $table name of the table |
231
|
|
|
* @param string|array $constraint constraint as needed |
232
|
|
|
* @param string $tableAlias alias of the table |
233
|
|
|
* |
234
|
|
|
* @return self |
235
|
|
|
*/ |
236
|
|
|
|
237
|
1 |
|
public function leftJoinPrefix(string $table = null, string $constraint = null, string $tableAlias = null) : self |
238
|
|
|
{ |
239
|
1 |
|
return $this->_addJoinSource('LEFT', self::$_config->get('dbPrefix') . $table, $constraint, $tableAlias); |
240
|
|
|
} |
241
|
|
|
|
242
|
|
|
/** |
243
|
|
|
* where like with many |
244
|
|
|
* |
245
|
|
|
* @since 3.0.0 |
246
|
|
|
* |
247
|
|
|
* @param array $columnArray array of column names |
248
|
|
|
* @param array $likeArray array of the like |
249
|
|
|
* |
250
|
|
|
* @return self |
251
|
|
|
*/ |
252
|
|
|
|
253
|
1 |
|
public function whereLikeMany(array $columnArray = [], array $likeArray = []) : self |
254
|
|
|
{ |
255
|
1 |
|
$dbType = self::$_config->get('dbType'); |
256
|
1 |
|
if ($dbType === 'pgsql') |
257
|
|
|
{ |
258
|
|
|
return $this->_addWhere('(' . implode(' ILIKE ? OR ', $columnArray) . ' ILIKE ?)', $likeArray); |
259
|
|
|
} |
260
|
1 |
|
return $this->_addWhere('(' . implode(' LIKE ? OR ', $columnArray) . ' LIKE ?)', $likeArray); |
261
|
|
|
} |
262
|
|
|
|
263
|
|
|
/** |
264
|
|
|
* where language is |
265
|
|
|
* |
266
|
|
|
* @since 3.0.0 |
267
|
|
|
* |
268
|
|
|
* @param string $language value of the language |
269
|
|
|
* |
270
|
|
|
* @return self |
271
|
|
|
*/ |
272
|
|
|
|
273
|
3 |
|
public function whereLanguageIs(string $language = null) : self |
274
|
|
|
{ |
275
|
3 |
|
return $this->_addWhere('(language = ? OR language IS NULL)', $language); |
276
|
|
|
} |
277
|
|
|
|
278
|
|
|
/** |
279
|
|
|
* find the flat array |
280
|
|
|
* |
281
|
|
|
* @since 4.0.0 |
282
|
|
|
* |
283
|
|
|
* @param string $column name of the column |
284
|
|
|
* |
285
|
|
|
* @return array |
286
|
|
|
*/ |
287
|
|
|
|
288
|
|
|
public function findFlatArray(string $column = 'id') : array |
289
|
|
|
{ |
290
|
|
|
return array_column($this->findArray(), $column); |
291
|
|
|
} |
292
|
|
|
|
293
|
|
|
/** |
294
|
|
|
* order by global setting |
295
|
|
|
* |
296
|
|
|
* @since 3.3.0 |
297
|
|
|
* |
298
|
|
|
* @param string $column name of the column |
299
|
|
|
* |
300
|
|
|
* @return self |
301
|
|
|
*/ |
302
|
|
|
|
303
|
1 |
|
public function orderBySetting(string $column = 'rank') : self |
304
|
|
|
{ |
305
|
1 |
|
$order = $this->getSetting('order'); |
306
|
1 |
|
return $this->_addOrderBy($column, $order); |
307
|
|
|
} |
308
|
|
|
|
309
|
|
|
/** |
310
|
|
|
* limit by global setting |
311
|
|
|
* |
312
|
|
|
* @since 3.3.0 |
313
|
|
|
* |
314
|
|
|
* @param int $step step of the offset |
315
|
|
|
* |
316
|
|
|
* @return self |
317
|
|
|
*/ |
318
|
|
|
|
319
|
1 |
|
public function limitBySetting(int $step = null) : self |
320
|
|
|
{ |
321
|
1 |
|
$dbType = self::$_config->get('dbType'); |
322
|
1 |
|
$limit = $this->getSetting('limit'); |
323
|
1 |
|
$this->limit($limit); |
324
|
1 |
|
if ($step > 0) |
325
|
|
|
{ |
326
|
|
|
if ($dbType === 'mssql') |
327
|
|
|
{ |
328
|
|
|
return $this; |
329
|
|
|
} |
330
|
|
|
$this->offset($step * $limit); |
331
|
|
|
} |
332
|
1 |
|
return $this; |
333
|
|
|
} |
334
|
|
|
|
335
|
|
|
/** |
336
|
|
|
* get the value from settings |
337
|
|
|
* |
338
|
|
|
* @since 4.0.0 |
339
|
|
|
* |
340
|
|
|
* @param string $key key of the item |
341
|
|
|
* |
342
|
|
|
* @return string|null |
343
|
|
|
*/ |
344
|
|
|
|
345
|
2 |
|
public function getSetting(string $key = null) : ?string |
346
|
|
|
{ |
347
|
2 |
|
$settings = self::forTablePrefix('settings')->findMany(); |
348
|
|
|
|
349
|
|
|
/* process settings */ |
350
|
|
|
|
351
|
2 |
|
if ($key) |
|
|
|
|
352
|
|
|
{ |
353
|
2 |
|
foreach ($settings as $setting) |
354
|
|
|
{ |
355
|
2 |
|
if ($setting->name === $key) |
356
|
|
|
{ |
357
|
2 |
|
return $setting->value; |
358
|
|
|
} |
359
|
|
|
} |
360
|
|
|
} |
361
|
|
|
return null; |
362
|
|
|
} |
363
|
|
|
} |
364
|
|
|
|
In PHP, under loose comparison (like
==
, or!=
, orswitch
conditions), values of different types might be equal.For
string
values, the empty string''
is a special case, in particular the following results might be unexpected: