[Bug]: Fix Unique Constraint Violation

[kingjia90]

Changes in this pull request

Potentially resolves https://github.com/pimcore/pimcore/issues/15976

Additional info

The problem is the fact that upsert relies on UniqueConstraintViolationException to guess if it's not a new row to be inserted but an existing row to be updated, and at the same time, we may encounter this exception for cases where it's throw because there's an unique index that we cannot insert (and is also not meant to update).

This PR put aside the criteria keys (that have no $data value) and check if these are actually unique indexes. Draft because it looks hacky, doesn't cover composite indexes and may be expensive perfomance-wise as it's not early-exiting anymore

WHAT

🤖 Generated by Copilot at e8da777

Improved upsert function in Helper.php to handle missing keys in data array. Fixed a typo in variable name.

🤖 Generated by Copilot at e8da777

upsert function handles missing keys better autumn of errors

HOW

🤖 Generated by Copilot at e8da777

• Modify upsert function to handle missing keys in data array (link)

[github-actions[bot]]

Review Checklist

• [x] Target branch (11.1 for bug fixes, others 11.x)
• [ ] Tests (if it's testable code, there should be a test for it - get help)
• [ ] Docs (every functionality needs to be documented, see here)
• [ ] Migration incl. install.sql (e.g. if the database schema changes, ...)
• [ ] Upgrade notes (deprecations, important information, migration hints, ...)
• [x] Label
• [ ] Milestone

[sonarqubecloud[bot]]

Kudos, SonarCloud Quality Gate passed!   

0 Bugs
0 Vulnerabilities
0 Security Hotspots
2 Code Smells

No Coverage information
0.0% Duplication

[sonarqubecloud[bot]]

Quality Gate passed

The SonarCloud Quality Gate passed, but some issues were introduced.

2 New issues
0 Security Hotspots
No data about Coverage
0.0% Duplication on New Code

See analysis details on SonarCloud

[sonarqubecloud[bot]]

Quality Gate passed

Issues
2 New issues
0 Accepted issues

Measures
0 Security Hotspots
No data about Coverage
0.0% Duplication on New Code

See analysis details on SonarCloud

[brusch]

@kingjia90 I share the concern about the performance, especially because the schema manager can be really slow from my experience.

Since this is only causing issues with auto-filled columns with an unique index e.g. AUTOINCREMENT or generated columns (that we don't use in Pimcore), I think the way to go it so somehow tell upcert the name of these columns. Either by adding an optional parameter to the function, or to mark those keys in the $keys parameter, eg. by prefixing with a ? or so. The latter also feels a bit hacky 🤔 But would avoid duplicating data.