Orphaned References Check (Standalone Script)

A version-neutral Groovy script that provides the orphaned-reference detection of the Check Orphaned References (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)

An orphaned reference is a value that points to a record which no longer exists (see Check Orphaned References (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.

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.

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).

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).

• 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.

-- Orphaned references cleanup - SQL preview
-- This script only probes the database, it changes nothing by itself.
--
-- checkDirect=true, checkPolymorphic=true
-- deleteOrphans=true, setNullOrphans=true
-- included=[], ignored=[IdmAudit]
-- entities in metamodel=174, references discovered=301, after filter=301
-- references checked=301, with orphans=1, executable statements=1
-- processed items logged=305 (a polymorphic reference logs one item per distinct type value, ...)
-- item label = reference key
-- =====================================================================

-- SysProvisioningArchive.account -> AccAccount  [MANY_TO_ONE]  orphans: 3
UPDATE sys_provisioning_archive s SET account_id = NULL
 WHERE s.account_id IS NOT NULL
   AND NOT EXISTS (SELECT 1 FROM acc_account t WHERE t.id = s.account_id);

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.

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.

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):

SELECT COUNT(*) FROM <table> s
 WHERE s.<column> IS NOT NULL
   [ AND s.<type_column> = '<type>' ]        -- polymorphic only
   AND NOT EXISTS (SELECT 1 FROM <target> t WHERE t.id = s.<column>);

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

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.

• 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: Check Orphaned References (Long Running Task) – the built-in long running task that performs the actual cleanup.