DeleteOrphans

CaseTalk Manager Database - Remove Orphaned Records

Overview

This document provides step-by-step instructions for cleaning up orphaned records in the CaseTalk Manager database when you encounter issues deleting projects.

When to Use This Script

Execute this cleanup script when:

• You cannot delete a project from the CaseTalk Manager database
• Foreign key constraint violations occur during project deletion
• Database integrity checks report orphaned records
• After data migration or database recovery operations

Understanding the Problem

The CaseTalk Manager database has cascading delete constraints properly configured. When you delete a project from ctk_project, all related records should automatically be deleted from:

• Project members (ctkprojectmember)
• Project attributes (ctkprojectattribute)
• Project items (ctkprojectitem)
• Item versions (ctkitemversion)
• All model content (expressions, roles, constraints, populations, etc.)

However, orphaned records can prevent this cascade from working. Orphaned records are:

• Item versions without a parent item (deleted item but versions remain)
• Items without a parent project (deleted project but items remain)
• Model content referencing non-existent versions

These orphans typically occur due to:

• Previous database errors or crashes
• Manual data manipulation
• Incomplete database restore operations
• Application bugs in older versions

Prerequisites

Before running this script:

1. Backup your database - Always create a full backup before making structural changes
2. Close all CaseTalk applications - Ensure no active connections to the database
3. Have appropriate permissions - You need CREATE VIEW, DROP VIEW, and DELETE privileges
4. Database type - This script works on SQL Server, MySQL, and PostgreSQL

The Cleanup Script

Location

Y:\CaseTalk.XE\develop\apps\CaseTalkManager\Database\doc\RemoveOrphans.sql
  

Script Breakdown

The script performs cleanup in a specific order to respect data dependencies. Here's what each section does:

---

Step 1: Create Helper View

CREATE VIEW ctk_orphaned_items_and_versions
  as
SELECT p.project_number, i.item_number, iv.item_version
  FROM ctk_item_version iv
  LEFT OUTER JOIN ctk_item i on iv.item_number = i.item_number
  LEFT OUTER JOIN ctk_project p on i.item_created_in_project_number = p.project_number
  where p.project_number is null or i.item_number is null;
  

What it does: Creates a view that identifies all orphaned records by finding:

• Item versions (ctkitemversion) without a corresponding item
• Items (ctk_item) without a corresponding project

Why: This view is used throughout the cleanup process to consistently identify which records need to be deleted.

---

Step 2: Delete Orphaned Model Content

delete from ctk_custom_attribute where ig_version in (select item_version from ctk_orphaned_items_and_versions);
  

What it does: Removes custom attribute values for orphaned information grammar (IG) versions.

Why: Custom attributes extend the model with user-defined properties. These must be cleaned up first as they reference the IG version.

---

delete from ctk_expression where ig_version in (select item_version from ctk_orphaned_items_and_versions);
delete from ctk_expression_part where ig_version in (select item_version from ctk_orphaned_items_and_versions);
  

What it does: Removes expressions and their parts for orphaned versions.

Why: Expressions define how object types and fact types are verbalized. Expression parts are the individual components (words, roles) that make up an expression. These form the core of the conceptual model's natural language representation.

---

delete from ctk_otft where ig_version in (select item_version from ctk_orphaned_items_and_versions);
  

What it does: Removes Object Types and Fact Types (OTFTs) for orphaned versions.

Why: OTFTs are the fundamental building blocks of conceptual models. They must be cleaned up to remove all model content.

---

delete from ctk_population where ig_version in (select item_version from ctk_orphaned_items_and_versions);
delete from ctk_populationtuple where ig_version in (select item_version from ctk_orphaned_items_and_versions);
  

What it does: Removes sample data (population) and population tuples for orphaned versions.

Why: Population data provides example instances that validate the model. Tuples are the individual rows of sample data. Both reference the IG version.

---

delete from ctk_role where ig_version in (select item_version from ctk_orphaned_items_and_versions);
  

What it does: Removes roles (fact type components) for orphaned versions.

Why: Roles connect object types to fact types and define the "player" in a fact. They're essential components of the conceptual model structure.

---

delete from ctk_sc where ig_version in (select item_version from ctk_orphaned_items_and_versions);
delete from ctk_sc_for_roles where ig_version in (select item_version from ctk_orphaned_items_and_versions);
  

What it does: Removes Set Comparison constraints and their role associations.

Why: Set comparison constraints (subset, equality, exclusion) define relationships between fact types. The forroles table links these constraints to specific roles.

---

delete from ctk_substitution where ig_version in (select item_version from ctk_orphaned_items_and_versions);
  

What it does: Removes substitution definitions for orphaned versions.

Why: Substitutions define how object types can replace roles in expressions during verbalization (e.g., "Person named 'John'" instead of "Person with Name 'John'").

---

delete from ctk_tc where ig_version in (select item_version from ctk_orphaned_items_and_versions);
delete from ctk_tc_for_roles where ig_version in (select item_version from ctk_orphaned_items_and_versions);
  

What it does: Removes Tuple Constraints and their role associations.

Why: Tuple constraints define complex business rules that span multiple roles. The forroles table links constraints to the specific roles they affect.

---

delete from ctk_uc where ig_version in (select item_version from ctk_orphaned_items_and_versions);
delete from ctk_uc_for_roles where ig_version in (select item_version from ctk_orphaned_items_and_versions);
  

What it does: Removes Uniqueness Constraints and their role associations.

Why: Uniqueness constraints define which combinations of roles must be unique (primary keys, alternate keys). Essential for data integrity rules.

---

delete from ctk_vc where ig_version in (select item_version from ctk_orphaned_items_and_versions);
delete from ctk_vc_values where ig_version in (select item_version from ctk_orphaned_items_and_versions);
  

What it does: Removes Value Constraints and their allowed values.

Why: Value constraints restrict the allowed values for object types (e.g., Status must be 'Active' or 'Inactive'). The _values table contains the specific allowed values.

---

delete from ctk_role_path where ig_version in (select item_version from ctk_orphaned_items_and_versions);
delete from ctk_role_path_part where ig_version in (select item_version from ctk_orphaned_items_and_versions);
delete from ctk_role_path_part_expr where ig_version in (select item_version from ctk_orphaned_items_and_versions);
  

What it does: Removes role paths, their parts, and associated expressions.

Why: Role paths define navigation routes through the model (e.g., "Person -> Birth -> Date"). These are used in derived fact types and constraints. The parts and expressions define the individual steps and their verbalization.

---

delete from ctk_otft_in_diagram where ig_version in (select item_version from ctk_orphaned_items_and_versions);
  

What it does: Removes diagram placement information for OTFTs.

Why: This table tracks which OTFTs appear in which diagrams and their visual positions. Must be cleaned up to maintain diagram integrity.

---

Step 3: Delete Translation Data

delete from ctk_locale_otft where ig_version in (select item_version from ctk_orphaned_items_and_versions);
delete from ctk_locale_population where ig_version in (select item_version from ctk_orphaned_items_and_versions);
delete from ctk_locale_populationtuple where ig_version in (select item_version from ctk_orphaned_items_and_versions);
delete from ctk_locale_role where ig_version in (select item_version from ctk_orphaned_items_and_versions);
delete from ctk_locale_role_path where ig_version in (select item_version from ctk_orphaned_items_and_versions);
delete from ctk_locale_sc where ig_version in (select item_version from ctk_orphaned_items_and_versions);
delete from ctk_locale_substitution where ig_version in (select item_version from ctk_orphaned_items_and_versions);
delete from ctk_locale_tc where ig_version in (select item_version from ctk_orphaned_items_and_versions);
delete from ctk_locale_uc where ig_version in (select item_version from ctk_orphaned_items_and_versions);
delete from ctk_locale_vc where ig_version in (select item_version from ctk_orphaned_items_and_versions);
delete from ctk_locale_vc_values where ig_version in (select item_version from ctk_orphaned_items_and_versions);
  

What it does: Removes all localized/translated versions of model elements.

Why: CaseTalk supports multi-language models. All ctklocale* tables contain translations for their corresponding model elements. These translations reference the same orphaned IG versions and must be cleaned up.

---

Step 4: Delete Model Metadata and Files

delete from ctk_item_attribute where item_version in (select item_version from ctk_orphaned_items_and_versions);
  

What it does: Removes custom attributes at the item version level.

Why: Item attributes store metadata about specific versions (tags, properties, annotations). These must be removed before the version itself.

---

delete from ctk_information_model where ig_version in (select item_version from ctk_orphaned_items_and_versions);
  

What it does: Removes the information model definition records.

Why: The information model record is the root entry for a conceptual model version. It must be deleted after all dependent model content but before the item version itself.

---

delete from ctk_information_model_diagram where ig_version in (select item_version from ctk_orphaned_items_and_versions);
  

What it does: Removes diagram definitions for orphaned versions.

Why: Diagrams provide visual representations of the model. This table stores diagram metadata (name, layout settings).

---

delete from ctk_information_model_script where ig_version in (select item_version from ctk_orphaned_items_and_versions);
  

What it does: Removes generated scripts (SQL DDL, documentation) for orphaned versions.

Why: CaseTalk can generate various scripts from models. These cached scripts reference the IG version and must be cleaned up.

---

Step 5: Delete Version and Item Records

delete from ctk_item_version_tag
  where item_version in (select item_version from ctk_orphaned_items_and_versions)
  or item_number in (select item_number from ctk_orphaned_items_and_versions);
  

What it does: Removes version tags (e.g., "v1.0", "Production", "APPROVED") for orphaned records.

Why: Tags mark important versions for easy reference. Both orphaned versions AND versions belonging to orphaned items must be removed.

---

delete from ctk_item_version
  where item_version in (select item_version from ctk_orphaned_items_and_versions)
  or item_number in (select item_number from ctk_orphaned_items_and_versions);
  

What it does: Removes the orphaned item version records themselves.

Why: After all dependent data is cleaned up, the version records can be safely deleted. This includes both directly orphaned versions and versions belonging to orphaned items.

---

delete from ctk_item where item_number in (select item_number from ctk_orphaned_items_and_versions);
  

What it does: Removes the orphaned item records.

Why: After all versions and their content are deleted, the parent item records can be removed. These are the project items that lost their connection to projects.

---

Step 6: Cleanup Helper View

drop view ctk_orphaned_items_and_versions;
  

What it does: Removes the temporary helper view created in Step 1.

Why: Cleanup housekeeping - the view is no longer needed after the orphan removal is complete.

---

Execution Steps

For SQL Server

1. Connect to the database using SQL Server Management Studio (SSMS) or your preferred tool

1. Select the correct database:

   USE casetalk;
   GO
  

1. Execute the script:

• • Open RemoveOrphans.sql in SSMS
  • Click "Execute" or press F5
  • Wait for completion (may take several minutes for large databases)

1. Verify results: Check the "Messages" tab for row counts deleted from each table

For MySQL

1. Connect to the database using MySQL Workbench or command line

1. Select the correct database:

   USE casetalk;
  

1. Execute the script:

   mysql -u username -p casetalk < RemoveOrphans.sql
  

For PostgreSQL

1. Connect to the database using pgAdmin or command line

1. Execute the script:

   psql -U username -d casetalk -f RemoveOrphans.sql
  

After Running the Script

1. Verify orphan removal: Run the helper view query manually to confirm no orphans remain:

   SELECT p.project_number, i.item_number, iv.item_version
   FROM ctk_item_version iv
   LEFT OUTER JOIN ctk_item i on iv.item_number = i.item_number
   LEFT OUTER JOIN ctk_project p on i.item_created_in_project_number = p.project_number
   WHERE p.project_number is null or i.item_number is null;
  

  This should return zero rows.

1. Test project deletion: Try deleting the problematic project again from CaseTalk Manager

1. Monitor for issues: Watch for any foreign key constraint violations or errors

1. Document the cleanup: Note how many records were removed for future reference

Cascading Delete Constraints

After running this cleanup script, the existing cascading delete constraints will work properly:

Project Level (ctprj module)

• Deleting a project cascades to:
  • ctkprojectmember
  • ctkprojectattribute
  • ctkprojectitem

Item Level (ctprj module)

• Deleting an item cascades to:
  • ctkitemversion
  • ctkitemattribute
  • ctkitemversion_tag

Model Level (ctmdl module)

• Deleting an item version cascades to:
  • ctkinformationmodel
  • All model content tables (otft, expression, role, population, constraints)
  • All locale/translation tables
  • All diagram and script tables

Preventing Future Orphans

To minimize orphaned records in the future:

1. Always use the CaseTalk Manager UI for deletions rather than direct SQL
2. Ensure proper transaction handling in custom scripts
3. Maintain regular backups before major operations
4. Monitor application logs for errors during delete operations
5. Keep CaseTalk updated to the latest version with bug fixes

Troubleshooting

Script Fails with "View already exists"

Solution: Drop the view manually first:

DROP VIEW IF EXISTS ctk_orphaned_items_and_versions;
  

Then re-run the script.

Script Reports "No rows affected" for all deletes

Good news: This means you have no orphaned records. Your deletion issue may be caused by:

• Application-level constraints or business rules
• User permission restrictions
• Active sessions holding locks on the project

Foreign Key Violations Still Occur After Cleanup

Check for:

1. External references from other databases or applications
2. Triggers that may be preventing deletion
3. Custom constraints added by your organization
4. Application-level referential integrity checks

Script Takes Very Long to Execute

Normal for large databases:

• Databases with millions of records may take 10-30 minutes
• Each DELETE statement processes potentially thousands of rows
• Database must maintain transaction integrity throughout

To monitor progress (SQL Server):

-- Check current blocking/waiting
SELECT * FROM sys.dm_exec_requests WHERE status = 'running';
  

Support

For additional assistance:

• CaseTalk Documentation: Check the help system within CaseTalk Manager
• Database Schema: Review the ER diagrams in develop/apps/CaseTalkManager/Database/doc/
• Contact Support: If issues persist, contact your CaseTalk administrator with:
  • Database type and version
  • Number of orphaned records found (before deletion)
  • Error messages received
  • CaseTalk version information

Related Scripts

Other useful database maintenance scripts:

• Upgrade-v*.sql: Version upgrade scripts for schema changes
• tables_create.sql: Full schema creation scripts
• rules_create.sql: Foreign key constraint definitions
• data_load.sql: Reference data initialization

Technical Notes

Why Orphans Occur

The database design intentionally uses ON DELETE NO ACTION for certain relationships:

1. ctkitem.itemcreatedinprojectnumber → ctkproject.project_number

• • Allows item recovery if project is accidentally deleted
  • Items maintain historical reference to their origin

1. ctkprojectitem.itemnumber → ctkitem.item_number

• • Prevents deleting items while they're still in projects
  • Ensures project items remain valid

These design decisions prioritize data safety but can create orphans if:

• Manual SQL operations bypass application logic
• Database crashes occur during multi-step operations
• Application bugs in older versions incomplete cleanup

Script Safety

This script is safe to run multiple times because:

• It only deletes records identified as orphans
• Uses LEFT OUTER JOIN to find missing parent references
• Processes deletes in correct dependency order
• Creates no permanent database objects (view is dropped)

However, always backup first - once deleted, orphaned data cannot be recovered.