1
|
|
|
<?php |
2
|
|
|
/** |
3
|
|
|
* Alxarafe. Development of PHP applications in a flash! |
4
|
|
|
* Copyright (C) 2018-2019 Alxarafe <[email protected]> |
5
|
|
|
*/ |
6
|
|
|
|
7
|
|
|
namespace Alxarafe\Core\Base; |
8
|
|
|
|
9
|
|
|
use Alxarafe\Core\Helpers\Schema; |
10
|
|
|
use Alxarafe\Core\Helpers\SchemaDB; |
11
|
|
|
use Alxarafe\Core\Providers\Database; |
12
|
|
|
use Alxarafe\Core\Providers\FlashMessages; |
13
|
|
|
use Alxarafe\Core\Providers\Translator; |
14
|
|
|
|
15
|
|
|
/** |
16
|
|
|
* Class Table allows access to a table using an active record. |
17
|
|
|
* It is recommended to create a descendant for each table of the database, defining its tablename and structure. |
18
|
|
|
* |
19
|
|
|
* @property string $locked This field can exist or not (added here to avoid scrutinizer "property not exists") |
20
|
|
|
* |
21
|
|
|
* @package Alxarafe\Core\Base |
22
|
|
|
*/ |
23
|
|
|
class Table extends SimpleTable |
24
|
|
|
{ |
25
|
|
|
/** |
26
|
|
|
* Build a Table model. $table is the name of the table in the database. |
27
|
|
|
* $params is a parameters array: |
28
|
|
|
* - create is true if the table is to be created if it does not exist (false by default) |
29
|
|
|
* - idField is the name of the primary key (default id) |
30
|
|
|
* - nameField is the name of the descriptive field (name by default) |
31
|
|
|
* |
32
|
|
|
* @param string $tableName |
33
|
|
|
* @param array $params |
34
|
|
|
*/ |
35
|
30 |
|
public function __construct(string $tableName, array $params = []) |
36
|
|
|
{ |
37
|
30 |
|
parent::__construct($tableName, $params); |
38
|
30 |
|
$this->debugTool->startTimer($this->shortName, $this->shortName . ' Table Constructor'); |
39
|
30 |
|
$create = $params['create'] ?? false; |
40
|
30 |
|
$this->checkStructure($create); |
41
|
30 |
|
$this->debugTool->stopTimer($this->shortName); |
42
|
30 |
|
} |
43
|
|
|
|
44
|
|
|
/** |
45
|
|
|
* Create a new table if it does not exist and it has been passed true as a parameter. |
46
|
|
|
* |
47
|
|
|
* This should check if there are differences between the defined in dbStructure and the physical table, |
48
|
|
|
* correcting the differences if true is passed as parameter. |
49
|
|
|
* |
50
|
|
|
* @param bool $create |
51
|
|
|
*/ |
52
|
30 |
|
public function checkStructure(bool $create = false): void |
53
|
|
|
{ |
54
|
30 |
|
if (!$create || !Database::getInstance()->getDbEngine()->issetDbTableStructure($this->tableName)) { |
55
|
4 |
|
return; |
56
|
|
|
} |
57
|
29 |
|
SchemaDB::checkTableStructure($this->tableName); |
58
|
29 |
|
} |
59
|
|
|
|
60
|
|
|
/** |
61
|
|
|
* Perform a search of a record by the name, returning the id of the corresponding record, or '' if it is not found |
62
|
|
|
* or does not have a name field. |
63
|
|
|
* |
64
|
|
|
* @param string $name |
65
|
|
|
* |
66
|
|
|
* @return string |
67
|
|
|
*/ |
68
|
|
|
public function getIdByName(string $name): string |
69
|
|
|
{ |
70
|
|
|
if ($this->nameField === '') { |
71
|
|
|
return ''; |
72
|
|
|
} |
73
|
|
|
|
74
|
|
|
$nameField = Database::getInstance()->getSqlHelper()->quoteFieldName($this->nameField); |
75
|
|
|
$sql = "SELECT {$this->idField} AS id FROM {$this->getQuotedTableName()} WHERE {$nameField} = :name;"; |
76
|
|
|
$data = Database::getInstance()->getDbEngine()->select($sql, ['name' => $name]); |
77
|
|
|
if (!empty($data) && count($data) > 0) { |
78
|
|
|
return $data[0]['id']; |
79
|
|
|
} |
80
|
|
|
|
81
|
|
|
return ''; |
82
|
|
|
} |
83
|
|
|
|
84
|
|
|
/** |
85
|
|
|
* Get an array with all data. |
86
|
|
|
* |
87
|
|
|
* @param string $key |
88
|
|
|
* @param mixed $value |
89
|
|
|
* @param string $comparison By default is '=' |
90
|
|
|
* @param string $orderBy |
91
|
|
|
* |
92
|
|
|
* @return array |
93
|
|
|
*/ |
94
|
1 |
|
public function getAllRecordsBy(string $key, $value, string $comparison = '=', string $orderBy = ''): array |
95
|
|
|
{ |
96
|
1 |
|
$fieldName = Database::getInstance()->getSqlHelper()->quoteFieldName($key); |
97
|
1 |
|
if (!empty($orderBy)) { |
98
|
1 |
|
$orderBy = " ORDER BY {$orderBy}"; |
99
|
|
|
} |
100
|
1 |
|
if ($value === 'NULL') { |
101
|
1 |
|
$isNull = $comparison === '=' ? ' IS NULL' : ' IS NOT NULL'; |
102
|
1 |
|
$sql = "SELECT * FROM {$this->getQuotedTableName()} WHERE {$fieldName}{$isNull}{$orderBy};"; |
103
|
|
|
} else { |
104
|
|
|
$sql = "SELECT * FROM {$this->getQuotedTableName()} WHERE {$fieldName} {$comparison} :value{$orderBy};"; |
105
|
|
|
} |
106
|
1 |
|
$vars = []; |
107
|
1 |
|
$vars['value'] = $value; |
108
|
1 |
|
return Database::getInstance()->getDbEngine()->select($sql, $vars); |
109
|
|
|
} |
110
|
|
|
|
111
|
|
|
/** |
112
|
|
|
* Return a list of key indexes. |
113
|
|
|
* Each final model that needed, must overwrite it. |
114
|
|
|
* |
115
|
|
|
* TODO: Why "*FromTable()" need to be overwrited on final model? Is not from model definition. |
116
|
|
|
* |
117
|
|
|
* @return array |
118
|
|
|
*/ |
119
|
|
|
public function getIndexesFromTable(): array |
120
|
|
|
{ |
121
|
|
|
return Database::getInstance()->getSqlHelper()->getIndexes($this->tableName, true); |
122
|
|
|
} |
123
|
|
|
|
124
|
|
|
/** |
125
|
|
|
* Return a list of default values. |
126
|
|
|
* Each final model that needed, must overwrite it. |
127
|
|
|
* |
128
|
|
|
* @return array |
129
|
|
|
*/ |
130
|
|
|
public function getDefaultValues(): array |
131
|
|
|
{ |
132
|
|
|
$items = []; |
133
|
|
|
foreach ($this->getStructure()['fields'] as $key => $valueData) { |
134
|
|
|
$items[$key] = $this->getDefaultValue($valueData); |
135
|
|
|
} |
136
|
|
|
return $items; |
137
|
|
|
} |
138
|
|
|
|
139
|
|
|
/** |
140
|
|
|
* Get default value data for this valueData. |
141
|
|
|
* |
142
|
|
|
* @param array $valueData |
143
|
|
|
* |
144
|
|
|
* @return bool|false|int|string |
145
|
|
|
*/ |
146
|
|
|
private function getDefaultValue(array $valueData) |
147
|
|
|
{ |
148
|
|
|
$item = $valueData['default'] ?? ''; |
149
|
|
|
if ($valueData['nullable'] === 'no') { |
150
|
|
|
switch ($valueData['type']) { |
151
|
|
|
case 'integer': |
|
|
|
|
152
|
|
|
case 'number': |
|
|
|
|
153
|
|
|
case 'email': |
154
|
|
|
$item = 0; |
155
|
|
|
break; |
156
|
|
|
case 'checkbox': |
157
|
|
|
$item = false; |
158
|
|
|
break; |
159
|
|
|
case 'date': |
160
|
|
|
$item = date('Y-m-d'); |
161
|
|
|
break; |
162
|
|
|
case 'datetime': |
163
|
|
|
$item = date('Y-m-d H:i:s'); |
164
|
|
|
break; |
165
|
|
|
case 'time': |
166
|
|
|
$item = date('H:i:s'); |
167
|
|
|
break; |
168
|
|
|
case 'string': |
169
|
|
|
case 'text': |
170
|
|
|
case 'textarea': |
171
|
|
|
case 'blob': |
172
|
|
|
case 'data': |
173
|
|
|
case 'link': |
174
|
|
|
$item = ''; |
175
|
|
|
break; |
176
|
|
|
default: |
177
|
|
|
$item = $valueData['default']; |
178
|
|
|
} |
179
|
|
|
} |
180
|
|
|
return $item; |
181
|
|
|
} |
182
|
|
|
|
183
|
|
|
/** |
184
|
|
|
* TODO: Undocumented |
185
|
|
|
* |
186
|
|
|
* @return array |
187
|
|
|
*/ |
188
|
|
|
public function getChecksFromTable(): array |
189
|
|
|
{ |
190
|
|
|
return Schema::getFromYamlFile($this->tableName, 'viewdata'); |
191
|
|
|
} |
192
|
|
|
|
193
|
|
|
/** |
194
|
|
|
* Save the data to a record if pass the test and returns true/false based on the result. |
195
|
|
|
* |
196
|
|
|
* @param array $data |
197
|
|
|
* |
198
|
|
|
* @return bool |
199
|
|
|
*/ |
200
|
|
|
public function saveRecord(array $data): bool |
201
|
|
|
{ |
202
|
|
|
if ($ret = $this->testData($data)) { |
203
|
|
|
$ret = $this->saveData($data); |
204
|
|
|
} |
205
|
|
|
return $ret; |
206
|
|
|
} |
207
|
|
|
|
208
|
|
|
/** |
209
|
|
|
* TODO: Undocumented |
210
|
|
|
* |
211
|
|
|
* @param $data |
212
|
|
|
* |
213
|
|
|
* @return bool |
214
|
|
|
*/ |
215
|
|
|
protected function testData($data): bool |
216
|
|
|
{ |
217
|
|
|
$ok = true; |
218
|
|
|
foreach ($data as $tableName => $block) { // Recorrer tablas |
219
|
|
|
foreach ($block as $blockId => $record) { // Recorrer registros de la tabla (seguramente uno) |
220
|
|
|
foreach (Database::getInstance()->getDbEngine()->getDbTableStructure($tableName)['checks'] as $fieldName => $fieldStructure) { |
221
|
|
|
$length = $fieldStructure['length'] ?? null; |
222
|
|
|
if (isset($length) && $length > 0 && strlen($record[$fieldName]) > $length) { |
223
|
|
|
$vars = ['%tableName%' => $tableName, '%fieldName%' => $fieldName, '%length%' => $length]; |
224
|
|
|
FlashMessages::getInstance()::setError(Translator::getInstance()->trans('tablename-fieldname-max-length', $vars)); |
225
|
|
|
$ok = false; |
226
|
|
|
} |
227
|
|
|
$min = $fieldStructure['min'] ?? null; |
228
|
|
|
if (isset($min) && $min > (int) $record[$fieldName]) { |
229
|
|
|
$vars = ['%tableName%' => $tableName, '%fieldName%' => $fieldName, '%min%' => $min]; |
230
|
|
|
FlashMessages::getInstance()::setError(Translator::getInstance()->trans('tablename-fieldname-exceeds-minimum', $vars)); |
231
|
|
|
$ok = false; |
232
|
|
|
} |
233
|
|
|
$max = $fieldStructure['max'] ?? null; |
234
|
|
|
if (isset($max) && $max < (int) $record[$fieldName]) { |
235
|
|
|
$vars = ['%tableName%' => $tableName, '%fieldName%' => $fieldName, '%max%' => $max]; |
236
|
|
|
FlashMessages::getInstance()::setError(Translator::getInstance()->trans('tablename-fieldname-exceeds-maximum', $vars)); |
237
|
|
|
$ok = false; |
238
|
|
|
} |
239
|
|
|
if (isset($fieldStructure['unique']) && ($fieldStructure['unique'] === 'yes')) { |
240
|
|
|
$sql = "SELECT * FROM {$this->getQuotedTableName()} WHERE {$fieldName}='{$data[$tableName][$blockId][$fieldName]}';"; |
241
|
|
|
$bad = Database::getInstance()->getDbEngine()->select($sql); |
242
|
|
|
if ($bad && count($bad) > 0) { |
243
|
|
|
foreach ($bad as $badrecord) { |
244
|
|
|
// TODO: Estoy utilizando 'id', pero tendría que ser el $this->idField del modelo correspondiente |
245
|
|
|
if ($badrecord['id'] !== $data[$tableName][$blockId]['id']) { |
246
|
|
|
$vars = ['%tableName%' => $tableName, '%fieldName%' => $fieldName, '%value%' => $data[$tableName][$blockId][$fieldName], '%register%' => $badrecord['id']]; |
247
|
|
|
FlashMessages::getInstance()::setError(Translator::getInstance()->trans('tablename-fieldname-register-duplicated', $vars)); |
248
|
|
|
$ok = false; |
249
|
|
|
} |
250
|
|
|
} |
251
|
|
|
} |
252
|
|
|
} |
253
|
|
|
} |
254
|
|
|
} |
255
|
|
|
} |
256
|
|
|
return $ok; |
257
|
|
|
} |
258
|
|
|
|
259
|
|
|
/** |
260
|
|
|
* Try to save the data and return true/false based on the result. |
261
|
|
|
* |
262
|
|
|
* @param array $data |
263
|
|
|
* |
264
|
|
|
* @return bool |
265
|
|
|
*/ |
266
|
|
|
protected function saveData(array $data): bool |
267
|
|
|
{ |
268
|
|
|
$ret = true; |
269
|
|
|
foreach ($data[$this->tableName] as $key => $value) { |
270
|
|
|
$this->load($key); |
271
|
|
|
$this->newData = $value; |
272
|
|
|
$ret &= $this->save(); |
273
|
|
|
} |
274
|
|
|
return (bool) $ret; |
275
|
|
|
} |
276
|
|
|
} |
277
|
|
|
|
As per the PSR-2 coding standard, case statements should not be wrapped in curly braces. There is no need for braces, since each case is terminated by the next
break
.There is also the option to use a semicolon instead of a colon, this is discouraged because many programmers do not even know it works and the colon is universal between programming languages.
To learn more about the PSR-2 coding standard, please refer to the PHP-Fig.