← All changes
|
backend/src/Repository/EntryField/EntryFieldRepository.php
+226
-6
1.1.1
→
1.5
View file →
| @@ -9,8 +9,9 @@ | ||
| 9 | 9 | |
| 10 | 10 | use Exception; |
| 11 | 11 | use IvyForms\Common\Exceptions\InvalidArgumentException; |
| 12 | 12 | use IvyForms\Common\Exceptions\QueryExecutionException; |
| 13 | +use IvyForms\Common\Helpers\EntryQueryHelper; | |
| 13 | 14 | use IvyForms\Factory\EntryField\EntryFieldFactory; |
| 14 | 15 | use IvyForms\Repository\AbstractRepository; |
| 15 | 16 | use IvyForms\Services\InstallActions\DB\Entry\EntriesTable; |
| 16 | 17 | use IvyForms\Services\InstallActions\DB\EntryField\EntryFieldsTable as EntryFieldsTable; |
| @@ -20,8 +21,12 @@ | ||
| 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 |
| @@ -107,10 +112,10 @@ | ||
| 107 | 112 | |
| 108 | 113 | /** |
| 109 | 114 | * Find an entry field by its ID. |
| 110 | 115 | * |
| 111 | - * @param array<mixed> $array | |
| 112 | - * @return array<mixed> | |
| 116 | + * @param array<string, string> $array | |
| 117 | + * @return array<int, \IvyForms\Entity\EntryField\EntryField> | |
| 113 | 118 | * @throws QueryExecutionException |
| 114 | 119 | */ |
| 115 | 120 | public function findBy(array $array): array |
| 116 | 121 | { |
| @@ -200,10 +205,14 @@ | ||
| 200 | 205 | * @param string $value |
| 201 | 206 | * @return bool |
| 202 | 207 | * @throws QueryExecutionException |
| 203 | 208 | */ |
| 204 | - public function checkDuplicateValue(int $formId, int $fieldId, string $value): bool | |
| 205 | - { | |
| 209 | + public function checkDuplicateValue( | |
| 210 | + int $formId, | |
| 211 | + int $fieldId, | |
| 212 | + string $value, | |
| 213 | + ?int $excludeEntryId = null | |
| 214 | + ): bool { | |
| 206 | 215 | global $wpdb; |
| 207 | 216 | |
| 208 | 217 | $entriesTable = EntriesTable::getTableName(); |
| 209 | 218 | |
| @@ -211,13 +220,19 @@ | ||
| 211 | 220 | FROM {$entriesTable} e |
| 212 | 221 | INNER JOIN {$this->table} ef ON e.id = ef.entryId |
| 213 | 222 | WHERE e.formId = %d |
| 214 | 223 | AND ef.fieldId = %d |
| 215 | - AND ef.fieldValue = %s | |
| 216 | - LIMIT 1"; | |
| 224 | + AND ef.fieldValue = %s"; | |
| 217 | 225 | |
| 218 | 226 | $params = [$formId, $fieldId, $value]; |
| 219 | 227 | |
| 228 | + if ($excludeEntryId !== null && $excludeEntryId > 0) { | |
| 229 | + $sql .= ' AND e.id != %d'; | |
| 230 | + $params[] = $excludeEntryId; | |
| 231 | + } | |
| 232 | + | |
| 233 | + $sql .= ' LIMIT 1'; | |
| 234 | + | |
| 220 | 235 | $exists = $wpdb->get_var($wpdb->prepare($sql, $params)); |
| 221 | 236 | |
| 222 | 237 | if ($exists === false) { |
| 223 | 238 | throw new QueryExecutionException( |
| @@ -228,6 +243,211 @@ | ||
| 228 | 243 | ); |
| 229 | 244 | } |
| 230 | 245 | |
| 231 | 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 | + } | |
| 232 | 452 | } |
| 233 | 453 | } |