← All changes
|
backend/src/Repository/EntryField/EntryFieldRepository.php
+317
-15
0.3.1
→
1.5.1
View file →
| @@ -1,21 +1,22 @@ | ||
| 1 | 1 | <?php |
| 2 | 2 | |
| 3 | 3 | /** |
| 4 | - * @copyright © TMS-Plugins. All rights reserved. | |
| 4 | + * @copyright © Melograno Venture Studio. All rights reserved. | |
| 5 | 5 | * @licence See LICENCE.md for license details. |
| 6 | 6 | */ |
| 7 | 7 | |
| 8 | 8 | namespace IvyForms\Repository\EntryField; |
| 9 | 9 | |
| 10 | -// phpcs:disable PSR1.Files.SideEffects | |
| 11 | -if (!defined('ABSPATH')) { | |
| 12 | - exit; // Exit if accessed directly | |
| 13 | -} | |
| 14 | - | |
| 15 | 10 | use Exception; |
| 11 | +use IvyForms\Common\Exceptions\InvalidArgumentException; | |
| 12 | +use IvyForms\Common\Exceptions\QueryExecutionException; | |
| 13 | +use IvyForms\Common\Helpers\EntryQueryHelper; | |
| 16 | 14 | use IvyForms\Factory\EntryField\EntryFieldFactory; |
| 17 | 15 | use IvyForms\Repository\AbstractRepository; |
| 16 | +use IvyForms\Services\InstallActions\DB\Entry\EntriesTable; | |
| 17 | +use IvyForms\Services\InstallActions\DB\EntryField\EntryFieldsTable as EntryFieldsTable; | |
| 18 | +use IvyForms\Services\InstallActions\DB\Field\FieldsTable; | |
| 18 | 19 | use IvyForms\Services\Translations\BackendStrings; |
| 19 | 20 | |
| 20 | 21 | class EntryFieldRepository extends AbstractRepository implements EntryFieldRepositoryInterface |
| 21 | 22 | { |
| @@ -20,13 +21,18 @@ | ||
| 20 | 21 | class EntryFieldRepository extends AbstractRepository implements EntryFieldRepositoryInterface |
| 21 | 22 | { |
| 22 | 23 | public const FACTORY = EntryFieldFactory::class; |
| 23 | 24 | |
| 25 | + private const DUPLICATE_NORMALIZED_CHECK_BATCH_SIZE = 500; | |
| 26 | + | |
| 27 | + private const BATCH_INSERT_CHUNK_SIZE = 50; | |
| 28 | + | |
| 24 | 29 | /** |
| 25 | 30 | * Check if an entry field exists in the database. |
| 26 | 31 | * |
| 27 | 32 | * @param int $entryFieldId |
| 28 | 33 | * @return bool |
| 34 | + * @throws QueryExecutionException | |
| 29 | 35 | */ |
| 30 | 36 | public function exists(int $entryFieldId): bool |
| 31 | 37 | { |
| 32 | 38 | $query = $this->wpdb->prepare( |
| @@ -32,14 +38,19 @@ | ||
| 32 | 38 | $query = $this->wpdb->prepare( |
| 33 | 39 | "SELECT COUNT(*) FROM {$this->table} WHERE id = %d", |
| 34 | 40 | $entryFieldId |
| 35 | 41 | ); |
| 36 | - | |
| 37 | - return (bool) $this->wpdb->get_var($query); | |
| 42 | + $count = $this->wpdb->get_var($query); | |
| 43 | + if ($count === false) { | |
| 44 | + throw new QueryExecutionException( | |
| 45 | + BackendStrings::getExceptionStrings()['unable_find_by_id'] . __CLASS__ | |
| 46 | + ); | |
| 47 | + } | |
| 48 | + return (int)$count > 0; | |
| 38 | 49 | } |
| 39 | 50 | |
| 40 | 51 | /** |
| 41 | - * @throws \Exception | |
| 52 | + * @throws Exception | |
| 42 | 53 | */ |
| 43 | 54 | public function add(object $entity): int |
| 44 | 55 | { |
| 45 | 56 | $data = $entity->toArray(); |
| @@ -60,9 +71,9 @@ | ||
| 60 | 71 | ], |
| 61 | 72 | ); |
| 62 | 73 | |
| 63 | 74 | if ($result === false) { |
| 64 | - throw new Exception( | |
| 75 | + throw new QueryExecutionException( | |
| 65 | 76 | BackendStrings::getExceptionStrings()['unable_to_add_data'] . __CLASS__ |
| 66 | 77 | ); |
| 67 | 78 | } |
| 68 | 79 | |
| @@ -90,9 +101,9 @@ | ||
| 90 | 101 | ['%d'] |
| 91 | 102 | ); |
| 92 | 103 | |
| 93 | 104 | if ($result === false) { |
| 94 | - throw new Exception( | |
| 105 | + throw new QueryExecutionException( | |
| 95 | 106 | BackendStrings::getExceptionStrings()['unable_update_data'] . __CLASS__ |
| 96 | 107 | ); |
| 97 | 108 | } |
| 98 | 109 | |
| @@ -101,14 +112,14 @@ | ||
| 101 | 112 | |
| 102 | 113 | /** |
| 103 | 114 | * Find an entry field by its ID. |
| 104 | 115 | * |
| 105 | - * @param array<mixed> $array | |
| 106 | - * @return array<mixed> | |
| 116 | + * @param array<string, string> $array | |
| 117 | + * @return array<int, \IvyForms\Entity\EntryField\EntryField> | |
| 118 | + * @throws QueryExecutionException | |
| 107 | 119 | */ |
| 108 | 120 | public function findBy(array $array): array |
| 109 | 121 | { |
| 110 | - | |
| 111 | 122 | $sql = $this->selectQuery() . " {$this->table}"; |
| 112 | 123 | $where = []; |
| 113 | 124 | $values = []; |
| 114 | 125 | |
| @@ -122,9 +133,13 @@ | ||
| 122 | 133 | } |
| 123 | 134 | |
| 124 | 135 | $query = $this->wpdb->prepare($sql, ...$values); |
| 125 | 136 | $rows = $this->wpdb->get_results($query, ARRAY_A); |
| 126 | - | |
| 137 | + if ($rows === false) { | |
| 138 | + throw new QueryExecutionException( | |
| 139 | + BackendStrings::getExceptionStrings()['unable_find_by_id'] . __CLASS__ | |
| 140 | + ); | |
| 141 | + } | |
| 127 | 142 | $results = []; |
| 128 | 143 | foreach ($rows as $row) { |
| 129 | 144 | $results[] = call_user_func([static::FACTORY, 'create'], $row); |
| 130 | 145 | } |
| @@ -136,8 +151,9 @@ | ||
| 136 | 151 | * Get all entry field IDs for the given entry IDs in a single query. |
| 137 | 152 | * |
| 138 | 153 | * @param int[] $entryIds |
| 139 | 154 | * @return int[] |
| 155 | + * @throws QueryExecutionException | |
| 140 | 156 | */ |
| 141 | 157 | public function findIdsByEntryIds(array $entryIds): array |
| 142 | 158 | { |
| 143 | 159 | if (empty($entryIds)) { |
| @@ -145,7 +161,293 @@ | ||
| 145 | 161 | } |
| 146 | 162 | $placeholders = implode(',', array_fill(0, count($entryIds), '%d')); |
| 147 | 163 | $query = "SELECT id FROM {$this->table} WHERE entryId IN ($placeholders)"; |
| 148 | 164 | $results = $this->wpdb->get_results($this->wpdb->prepare($query, ...$entryIds), ARRAY_A); |
| 165 | + if ($results === false) { | |
| 166 | + throw new QueryExecutionException( | |
| 167 | + BackendStrings::getExceptionStrings()['unable_find_by_id'] . __CLASS__ | |
| 168 | + ); | |
| 169 | + } | |
| 149 | 170 | return array_map(fn($row) => (int)$row['id'], $results); |
| 171 | + } | |
| 172 | + | |
| 173 | + /** | |
| 174 | + * Delete entry fields by form IDs using a subquery for fieldId. | |
| 175 | + * | |
| 176 | + * @param int[] $formIds | |
| 177 | + * @return int Number of deleted entry_fields | |
| 178 | + * @throws QueryExecutionException | |
| 179 | + * @throws InvalidArgumentException | |
| 180 | + */ | |
| 181 | + public function deleteByFormIds(array $formIds): int | |
| 182 | + { | |
| 183 | + if (empty($formIds)) { | |
| 184 | + return 0; | |
| 185 | + } | |
| 186 | + $formIds = array_map('intval', $formIds); | |
| 187 | + $placeholders = implode(',', array_fill(0, count($formIds), '%d')); | |
| 188 | + $fieldsTable = FieldsTable::getTableName(); | |
| 189 | + $query = "DELETE FROM {$this->table} WHERE fieldId IN | |
| 190 | + (SELECT id FROM {$fieldsTable} WHERE formId IN ($placeholders))"; | |
| 191 | + $result = $this->wpdb->query($this->wpdb->prepare($query, ...$formIds)); | |
| 192 | + if ($result === false) { | |
| 193 | + throw new QueryExecutionException( | |
| 194 | + BackendStrings::getExceptionStrings()['failed_delete_by_foreign_keys'] | |
| 195 | + ); | |
| 196 | + } | |
| 197 | + return $result; | |
| 198 | + } | |
| 199 | + | |
| 200 | + /** | |
| 201 | + * Check if a field value already exists in the database | |
| 202 | + * | |
| 203 | + * @param int $formId | |
| 204 | + * @param int $fieldId | |
| 205 | + * @param string $value | |
| 206 | + * @return bool | |
| 207 | + * @throws QueryExecutionException | |
| 208 | + */ | |
| 209 | + public function checkDuplicateValue( | |
| 210 | + int $formId, | |
| 211 | + int $fieldId, | |
| 212 | + string $value, | |
| 213 | + ?int $excludeEntryId = null | |
| 214 | + ): bool { | |
| 215 | + global $wpdb; | |
| 216 | + | |
| 217 | + $entriesTable = EntriesTable::getTableName(); | |
| 218 | + | |
| 219 | + $sql = "SELECT 1 | |
| 220 | + FROM {$entriesTable} e | |
| 221 | + INNER JOIN {$this->table} ef ON e.id = ef.entryId | |
| 222 | + WHERE e.formId = %d | |
| 223 | + AND ef.fieldId = %d | |
| 224 | + AND ef.fieldValue = %s"; | |
| 225 | + | |
| 226 | + $params = [$formId, $fieldId, $value]; | |
| 227 | + | |
| 228 | + if ($excludeEntryId !== null && $excludeEntryId > 0) { | |
| 229 | + $sql .= ' AND e.id != %d'; | |
| 230 | + $params[] = $excludeEntryId; | |
| 231 | + } | |
| 232 | + | |
| 233 | + $sql .= ' LIMIT 1'; | |
| 234 | + | |
| 235 | + $exists = $wpdb->get_var($wpdb->prepare($sql, $params)); | |
| 236 | + | |
| 237 | + if ($exists === false) { | |
| 238 | + throw new QueryExecutionException( | |
| 239 | + sprintf( | |
| 240 | + BackendStrings::getExceptionStrings()['failed_check_duplicates'], | |
| 241 | + $wpdb->last_error ?: BackendStrings::getExceptionStrings()['unknown_error'] | |
| 242 | + ) | |
| 243 | + ); | |
| 244 | + } | |
| 245 | + | |
| 246 | + return !empty($exists); | |
| 247 | + } | |
| 248 | + | |
| 249 | + /** | |
| 250 | + * Check if a normalized value matches any stored value after applying a normalizer. | |
| 251 | + * | |
| 252 | + * @param int $formId | |
| 253 | + * @param int $fieldId | |
| 254 | + * @param string $normalizedValue | |
| 255 | + * @param callable(string): string $normalizeStoredValue | |
| 256 | + * @return bool | |
| 257 | + * @throws QueryExecutionException | |
| 258 | + */ | |
| 259 | + public function checkDuplicateValueNormalized( | |
| 260 | + int $formId, | |
| 261 | + int $fieldId, | |
| 262 | + string $normalizedValue, | |
| 263 | + callable $normalizeStoredValue, | |
| 264 | + ?int $excludeEntryId = null | |
| 265 | + ): bool { | |
| 266 | + global $wpdb; | |
| 267 | + | |
| 268 | + $query = $this->buildNormalizedDuplicateCheckQuery( | |
| 269 | + EntriesTable::getTableName(), | |
| 270 | + $formId, | |
| 271 | + $fieldId, | |
| 272 | + $excludeEntryId | |
| 273 | + ); | |
| 274 | + $offset = 0; | |
| 275 | + $limit = self::DUPLICATE_NORMALIZED_CHECK_BATCH_SIZE; | |
| 276 | + | |
| 277 | + while (true) { | |
| 278 | + $rows = $wpdb->get_col($wpdb->prepare($query['sql'], ...array_merge($query['params'], [$limit, $offset]))); | |
| 279 | + | |
| 280 | + if ($rows === false) { | |
| 281 | + throw new QueryExecutionException( | |
| 282 | + sprintf( | |
| 283 | + BackendStrings::getExceptionStrings()['failed_check_duplicates'], | |
| 284 | + $wpdb->last_error ?: BackendStrings::getExceptionStrings()['unknown_error'] | |
| 285 | + ) | |
| 286 | + ); | |
| 287 | + } | |
| 288 | + | |
| 289 | + if ($rows === []) { | |
| 290 | + break; | |
| 291 | + } | |
| 292 | + | |
| 293 | + if ($this->normalizedValueExistsInRows($rows, $normalizedValue, $normalizeStoredValue)) { | |
| 294 | + return true; | |
| 295 | + } | |
| 296 | + | |
| 297 | + if (count($rows) < $limit) { | |
| 298 | + break; | |
| 299 | + } | |
| 300 | + | |
| 301 | + $offset += $limit; | |
| 302 | + } | |
| 303 | + | |
| 304 | + return false; | |
| 305 | + } | |
| 306 | + | |
| 307 | + /** | |
| 308 | + * @return array{sql: string, params: array<int, int>} | |
| 309 | + */ | |
| 310 | + private function buildNormalizedDuplicateCheckQuery( | |
| 311 | + string $entriesTable, | |
| 312 | + int $formId, | |
| 313 | + int $fieldId, | |
| 314 | + ?int $excludeEntryId | |
| 315 | + ): array { | |
| 316 | + $sql = "SELECT ef.fieldValue | |
| 317 | + FROM {$entriesTable} e | |
| 318 | + INNER JOIN {$this->table} ef ON e.id = ef.entryId | |
| 319 | + WHERE e.formId = %d | |
| 320 | + AND ef.fieldId = %d"; | |
| 321 | + | |
| 322 | + $params = [$formId, $fieldId]; | |
| 323 | + | |
| 324 | + if ($excludeEntryId !== null && $excludeEntryId > 0) { | |
| 325 | + $sql .= ' AND e.id != %d'; | |
| 326 | + $params[] = $excludeEntryId; | |
| 327 | + } | |
| 328 | + | |
| 329 | + $sql .= ' LIMIT %d OFFSET %d'; | |
| 330 | + | |
| 331 | + return ['sql' => $sql, 'params' => $params]; | |
| 332 | + } | |
| 333 | + | |
| 334 | + /** | |
| 335 | + * @param string[] $rows | |
| 336 | + */ | |
| 337 | + private function normalizedValueExistsInRows( | |
| 338 | + array $rows, | |
| 339 | + string $normalizedValue, | |
| 340 | + callable $normalizeStoredValue | |
| 341 | + ): bool { | |
| 342 | + foreach ($rows as $stored) { | |
| 343 | + if ($normalizeStoredValue((string) $stored) === $normalizedValue) { | |
| 344 | + return true; | |
| 345 | + } | |
| 346 | + } | |
| 347 | + | |
| 348 | + return false; | |
| 349 | + } | |
| 350 | + | |
| 351 | + /** | |
| 352 | + * Batch-update fieldValue for existing entry_field rows of one entry. | |
| 353 | + * | |
| 354 | + * @param int $entryId | |
| 355 | + * @param list<array{id: int, fieldValue: string}> $updates | |
| 356 | + * @throws QueryExecutionException | |
| 357 | + */ | |
| 358 | + public function batchUpdateValues(int $entryId, array $updates): void | |
| 359 | + { | |
| 360 | + if ($updates === []) { | |
| 361 | + return; | |
| 362 | + } | |
| 363 | + | |
| 364 | + $caseFragments = []; | |
| 365 | + $caseValues = []; | |
| 366 | + $ids = []; | |
| 367 | + | |
| 368 | + foreach ($updates as $update) { | |
| 369 | + $id = (int) $update['id']; | |
| 370 | + if ($id <= 0) { | |
| 371 | + continue; | |
| 372 | + } | |
| 373 | + $caseFragments[] = 'WHEN %d THEN %s'; | |
| 374 | + $caseValues[] = $id; | |
| 375 | + $caseValues[] = (string) $update['fieldValue']; | |
| 376 | + $ids[] = $id; | |
| 377 | + } | |
| 378 | + | |
| 379 | + if ($ids === []) { | |
| 380 | + return; | |
| 381 | + } | |
| 382 | + | |
| 383 | + $idPlaceholders = implode(',', array_fill(0, count($ids), '%d')); | |
| 384 | + $sql = "UPDATE {$this->table} | |
| 385 | + SET fieldValue = CASE id " . implode(' ', $caseFragments) . " END | |
| 386 | + WHERE entryId = %d AND id IN ({$idPlaceholders})"; | |
| 387 | + | |
| 388 | + $params = array_merge($caseValues, [$entryId], $ids); | |
| 389 | + $result = $this->wpdb->query($this->wpdb->prepare($sql, ...$params)); | |
| 390 | + | |
| 391 | + if ($result === false) { | |
| 392 | + throw new QueryExecutionException( | |
| 393 | + BackendStrings::getExceptionStrings()['unable_update_data'] . __CLASS__ | |
| 394 | + ); | |
| 395 | + } | |
| 396 | + } | |
| 397 | + | |
| 398 | + /** | |
| 399 | + * Multi-row insert of entry_field rows. Sets generated IDs on the entities. | |
| 400 | + * Inserts in chunks to stay under database packet / query-size limits. | |
| 401 | + * | |
| 402 | + * @param list<\IvyForms\Entity\EntryField\EntryField> $entryFields | |
| 403 | + * @throws QueryExecutionException | |
| 404 | + */ | |
| 405 | + public function batchInsert(array $entryFields): void | |
| 406 | + { | |
| 407 | + if ($entryFields === []) { | |
| 408 | + return; | |
| 409 | + } | |
| 410 | + | |
| 411 | + foreach (array_chunk($entryFields, self::BATCH_INSERT_CHUNK_SIZE) as $chunk) { | |
| 412 | + $this->insertEntryFieldChunk($chunk); | |
| 413 | + } | |
| 414 | + } | |
| 415 | + | |
| 416 | + /** | |
| 417 | + * @param list<\IvyForms\Entity\EntryField\EntryField> $entryFields | |
| 418 | + * @throws QueryExecutionException | |
| 419 | + */ | |
| 420 | + private function insertEntryFieldChunk(array $entryFields): void | |
| 421 | + { | |
| 422 | + $placeholders = []; | |
| 423 | + $values = []; | |
| 424 | + | |
| 425 | + foreach ($entryFields as $entryField) { | |
| 426 | + $data = $entryField->toArray(); | |
| 427 | + $placeholders[] = '(%d, %d, %s)'; | |
| 428 | + $values[] = (int) $data['entryId']; | |
| 429 | + $values[] = (int) $data['fieldId']; | |
| 430 | + $values[] = (string) $data['fieldValue']; | |
| 431 | + } | |
| 432 | + | |
| 433 | + $sql = "INSERT INTO {$this->table} (entryId, fieldId, fieldValue) VALUES " | |
| 434 | + . implode(', ', $placeholders); | |
| 435 | + $result = $this->wpdb->query($this->wpdb->prepare($sql, ...$values)); | |
| 436 | + | |
| 437 | + if ($result === false) { | |
| 438 | + throw new QueryExecutionException( | |
| 439 | + BackendStrings::getExceptionStrings()['unable_to_add_data'] . __CLASS__ | |
| 440 | + ); | |
| 441 | + } | |
| 442 | + | |
| 443 | + $firstId = (int) $this->wpdb->insert_id; | |
| 444 | + if ($firstId <= 0) { | |
| 445 | + return; | |
| 446 | + } | |
| 447 | + | |
| 448 | + $step = EntryQueryHelper::getAutoIncrementStep($this->wpdb); | |
| 449 | + foreach ($entryFields as $index => $entryField) { | |
| 450 | + $entryField->setId($firstId + $index * $step); | |
| 451 | + } | |
| 150 | 452 | } |
| 151 | 453 | } |