Orphaned References Check (Standalone Script)

A version-neutral Groovy script that provides the orphaned-reference detection of the orphaned_reference_check_long_running_task as a downloadable SQL preview. It is tested to run on IdM 12 to IdM 16 – including versions that do not ship the built-in task.

Script code: orphanedReferencesCheck (category SYSTEM)
Run through: Execute script long running task (ExecuteScriptTaskExecutor, parameter scriptCode = orphanedReferencesCheck)

Management summary

An orphaned reference is a value that points to a record which no longer exists (see orphaned_reference_check_long_running_task for why this is possible in CzechIdM – the product intentionally uses no business foreign keys, so integrity is enforced by the application, not the database).

This standalone script brings the same detection to environments where the dedicated task is not available (older releases, or a system that must not be upgraded just to run a one-off check). It is intentionally read-only:

• it never changes data – it only probes the metamodel and the database,
• when run as a long running task it attaches a downloadable text file containing the exact DELETE / UPDATE … SET NULL statements that a cleanup would execute,
• it produces the same task items and states as the built-in task in preview mode, so the result looks familiar.

It is the right tool when you want to audit orphaned references and hand the remediation SQL to a DBA for a reviewed, backed-up execution, rather than let the application delete data directly.

Value: works everywhere (one artifact for IdM 12 to 16), zero data risk, and a portable SQL deliverable that can be reviewed and version-controlled before anything is run.

User guide

The six configuration variables

The configuration is at the top of the script body (CONFIGURATION (edit before running)). Edit these before deploying/running:

Note the differences from the built-in task: there is no previewOnly (the script is always a preview) and no sampleSize. deleteOrphans / setNullOrphans default to true here, because enabling an action only means the corresponding SQL is written to the report – it is never executed. Set one to false to see those orphans reported as BLOCKED instead of getting a statement.

Deploying the script to an environment

Two options, neither needs filesystem access:

1. Bundled (IdM 16). The script ships as a classpath resource (…/resources/eu/bcvsolutions/idm/scripts/OrphanedReferencesCheck.xml) and is auto-registered on start; nothing to do.
2. Manually in the GUI. Administration → Scripts → create a script with code orphanedReferencesCheck, category SYSTEM, paste the body, and add the script authorities (allowed classes) listed in the XML. No restart required.

Sandbox whitelist per version. The Groovy body is identical on all versions; only the allowed classes (<allowClasses>) differ: the IdM 16 variant lists jakarta.persistence.*, the IdM 15- variant lists javax.persistence.*. Use the XML from the matching repository. Using the wrong persistence package makes the deploy fail with HTTP 400 (the class is validated via Class.forName).

Running it

1. Administration → Scheduled tasks → Run task → Execute script.
2. Set the parameter scriptCode to orphanedReferencesCheck.
3. Start the task. When it finishes, open the result and download the attachment orphaned-references-cleanup.sql.

To change what is checked, edit the six variables in the script body and re-deploy (or keep several scripts with different codes, e.g. orphanedReferencesCheck_contracts).

Reading the results

• Task result – finishes with a downloadable text attachment (orphaned-references-cleanup.sql). The download button appears because the result carries HTTP 206 (partial content) and a download URL.
• Task items – one per reference (a polymorphic reference logs one item per distinct *_type value), mirroring the built-in task in preview mode:
  • EXECUTED – clean (no orphans),
  • NOT_EXECUTED – orphans found, the action would be performed,
  • BLOCKED – orphans found but the action variable is false,
  • EXCEPTION – model vs database nullability mismatch, or the reference could not be checked.
• The reference key (Entity.attribute -> Target) is shown as the item label where the orphan feature is installed (IdM 15/16) and is always present in the item result message.

Example: the downloaded SQL preview

Blocked and mismatch references are written as comments instead of an executable statement, e.g. -- BLOCKED: setNullOrphans = false (not executed) or -- MISMATCH: model nullable vs database NOT NULL - no cleanup action.

Technical description

How it works

The script is a Groovy script executed inside the CzechIdM script sandbox by the Execute script task. It:

1. obtains a real EntityManager from the primary EntityManagerFactory (resolved by type, so it is portable across versions),
2. scans EntityManager.getMetamodel() for the same references as the built-in task – direct @ManyToOne / @OneToOne (including @MappedSuperclass ancestors), JOINED inheritance links, and polymorphic *_id + *_type pairs; it recurses into @Embedded types and skips @Formula, inverse @OneToOne(mappedBy), reversed @JoinColumn, and entities without @Table(name),
3. for each reference runs the anti-join count and, when orphans exist, decides the action from model+database nullability (both nullable → set null, both NOT NULL → delete, disagreement → mismatch),
4. writes the statement to the report instead of executing it, logs a processed item with the matching preview state, and finally attaches the report to the task result.

Detection SQL

Identical, storage-agnostic anti-join to the built-in task (column vs column, no id literals or casts – correct regardless of whether UUIDs are stored as bytea or native uuid):

Column nullability comes from information_schema.columns for current_schema(). PostgreSQL only.

Version neutrality

One Groovy body serves IdM 12 to 16. It uses no jakarta/javax imports – JPA types are reached by reflection (Class.forName("jakarta.persistence.EntityManagerFactory") with a javax fallback) and annotations are matched by simple name. Result codes that exist only in newer releases are resolved by name (CoreResultCode.valueOf(...) with a fallback list), so the script never hard-references a constant that may be absent – e.g. the per-item completed/failed codes fall back to OK / LONG_RUNNING_TASK_ITEM_FAILED on IdM 14. Processed items are built from IdmProcessedTaskItemDto (present in every version); the richer OrphanedReferenceDto label/renderer is used only where the orphan feature is installed.

The only per-version artifact is the sandbox whitelist (jakarta vs javax persistence classes) kept in the XML, not in the body.

Differences from the built-in task

Files in the repository

• Deployable IdmScript XML: …/core-impl/src/main/resources/eu/bcvsolutions/idm/scripts/OrphanedReferencesCheck.xml (IdM 16 = jakarta; IdM 15 repository carry the javax variant)

See also: orphaned_reference_check_long_running_task – the built-in long running task that performs the actual cleanup.