====== 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: ^ Variable ^ Type ^ Default ^ Meaning ^ | ''checkDirect'' | Boolean | ''true'' | Check direct references (''@ManyToOne'' / ''@OneToOne'' / JOINED inheritance). | | ''checkPolymorphic'' | Boolean | ''true'' | Check polymorphic references (a ''*_id'' + ''*_type'' pair). | | ''included'' | List | ''[]'' | Searched entities (empty = all). E.g. ''["IdmIdentityContract"]''. | | ''ignored'' | List | ''["IdmAudit"]'' | Ignored entities (subtracted from the searched set). | | ''deleteOrphans'' | Boolean | ''true'' | Emit the //delete// statement for NOT-NULL orphaned columns. | | ''setNullOrphans'' | Boolean | ''true'' | Emit the //set null// statement for nullable orphaned columns. | 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: - **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. - **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 ('''') 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 ==== - Administration → **Scheduled tasks** → //Run task// → **Execute script**. - Set the parameter ''scriptCode'' to ''orphanedReferencesCheck''. - 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 ==== -- 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''. ---- ===== Technical description ===== ==== How it works ==== The script is a Groovy script executed inside the CzechIdM script sandbox by the //Execute script// task. It: - obtains a real ''EntityManager'' from the primary ''EntityManagerFactory'' (resolved by type, so it is portable across versions), - 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)'', - 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), - **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''): SELECT count(*) FROM s WHERE s. IS NOT NULL [ AND s. = '' ] -- polymorphic only AND NOT EXISTS (SELECT 1 FROM t WHERE t.id = s.); 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 ==== ^ Aspect ^ Built-in task ^ This script ^ | Availability | IdM 16 (backported to 15) | IdM 14 / 15 / 16 | | Changes data | Yes, when ''previewOnly = false'' | **Never** (always a preview) | | Output | XLSX report + optional real cleanup | Text file with the SQL that //would// run | | Configuration | Task form (8 parameters) | 6 variables in the script body | | Deployment | Ships with the product | Classpath resource / GUI | | Sample ids | ''sampleSize'' parameter, in the report | not included | ==== 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.//