You can use this procedure to find and repair orphaned persistent links in an SQL database when an unsuccessful database evolve has left references to objects that no longer exist. It is intended for MDriven users who can inspect the model in MDriven Designer and run the generated SQL against the affected database.

When to use this procedure

An inconsistent database is uncommon when you use MDrivenServer and Turnkey. When it occurs, it is usually after an evolve did not complete as intended and rows or references were left behind.

Use these checks when you suspect either of the following problems:

• A link class row refers to an object row that no longer exists. A link class is the persistent class that represents an association between two objects.
• A single link column contains an ID for an object row that no longer exists. A single link is an association stored as an ID column on one table.

Orphaned link-class rows are especially significant. A link object requires both connected objects to exist, so MDriven cannot load that link object if either referenced object is missing.

This page checks database consistency at the SQL level. For an error reporting that multiple objects refer to the same target through a single link, see Documentation:Duplicate IDs in the database. For general database structure and persistence concepts, see Documentation:SQL Database and Documentation:Working with Code and Persistence Mapping.

Before you change data

The generated SQL starts as read-only SELECT statements. Do not change it to DELETE or UPDATE until you have reviewed the returned rows.

1. Work against the affected database, not an unrelated development database.
2. Create a recoverable copy before making any data change. For a Turnkey site, you can create and download an administrative database snapshot as described in Documentation:Database download and upload.
3. Run the generated SELECT statements first.
4. Review every returned row. Each result identifies a reference whose target object is absent.
5. Apply the matching repair statement only to the rows returned by the reviewed query.
6. Run the same SELECT statements again. A repaired check returns no rows.

Warning: These repairs modify data directly in the SQL database. Do not run a broad DELETE or UPDATE without first confirming the corresponding SELECT result.

Generate checks from the model

MDriven Designer can generate the table, class, association-end, and inner-link names from your model. This avoids manually deriving SQL names from the persistence mapping.

1. Open the model in MDriven Designer.
2. Right-click the model surface.
3. Select Extras and then Open model debugger....
4. Paste the OCL for the type of relationship you need to check.
5. Run the OCL and copy its generated SQL text to the SQL tool for the affected database.
6. Run the generated SELECT statements and review the rows before making repairs.

Child-mapped classes

The OCL below does not work unchanged for child-mapped classes. When a parent class is child-mapped, manually use the names of its child-mapped classes in place of the parent class name in the generated SQL. The parent is commonly an abstract class.

Find orphaned link-class rows

Run the following OCL in the model debugger. It generates two checks for each persistent link class: one for each association end.

Association.allInstances->
  select(a|a.Class.notNull and a.Class.isPersistent)->
    collect(a|a.Class.Name,
      let ae = a.AssociationEnd->at(1) in
        let c1 = ae.Participant in
          if c1.TaggedValue->exists(tv | (tv.Tag='Eco.TableMapping') and (tv.Value = 'Parent')) then
            c1.Superclass.Name
          else
            c1.Name
          endif->collect(classname|if classname='User' then 'User_TBL' else classname endif)->first,
      let ae = a.AssociationEnd->at(1) in
        ae.Name,
      let ae = a.AssociationEnd->at(1) in
        ae.TaggedValue->select(tv | tv.Tag='Eco.InnerLinkName')->first.Value,
      let ae = a.AssociationEnd->at(2) in
        let c1 = ae.Participant in
          if c1.TaggedValue->exists(tv | (tv.Tag='Eco.TableMapping') and (tv.Value = 'Parent')) then
            c1.Superclass.Name
          else
            c1.Name
          endif->collect(classname|if classname='User' then 'User_TBL' else classname endif)->first,
      let ae = a.AssociationEnd->at(2) in
        ae.Name,
      let ae = a.AssociationEnd->at(2) in
        ae.TaggedValue->select(tv | tv.Tag='Eco.InnerLinkName')->first.Value
)->orderBy(x|x.Name)->
collect(d |
'select * from ' + d.Name + ' where ' + if d.Part4.isNullOrEmpty then d.Part3 else d.Part4 endif + 'ID not in (select ' + d.Part2 + 'ID from ' + d.Part2 + ')\r\n'+
'select * from ' + d.Name + ' where ' + if d.Part7.isNullOrEmpty then d.Part6 else d.Part7 endif + 'ID not in (select ' + d.Part5 + 'ID from ' + d.Part5 + ')'
)->asSeparatedList('\r\n')

Read the generated SQL

For a link class named SysUserRole, the output can include:

select * from SysUserRole where SysUserID not in (select SysUserID from SysUser)
select * from SysUserRole where SysRoleID not in (select SysRoleID from SysRole)

The first query finds link rows whose SysUserID points to no row in SysUser. The second does the same for SysRoleID and SysRole.

Repair confirmed orphaned link-class rows

After you have reviewed the result of a generated query, replace only its initial select * with delete, keeping its FROM and WHERE clauses unchanged. For example:

delete from SysUserRole where SysUserID not in (select SysUserID from SysUser)

Run the original SELECT again after the repair. It should return no rows for the corrected reference.

Find invalid single-link references

Run the following OCL in the model debugger. It considers persistent, non-derived single links that are stored as references between persistent participants.

AssociationEnd.allinstances->
  select(ae|ae.IsSingleLink and ae.Association.IsPersistent and ae.Association.Class.isNull and
  not ae.Association.TaggedValue->exists(tv | (tv.Tag='derived') and (tv.Value = 'True')) and
  not ae.TaggedValue->exists(tv | (tv.Tag='Eco.Embed') and (tv.Value = 'False')) and
  ae.Participant.IsPersistent and
  ae.OtherEnd.Participant.IsPersistent)->
    collect(ae|
      let c1 = ae.OtherEnd.Participant in
      if c1.TaggedValue->exists(tv | (tv.Tag='Eco.TableMapping') and (tv.Value = 'Parent')) then
        c1.Superclass.Name
      else
        c1.Name
      endif->collect(classname|if classname='User' then 'User_TBL' else classname endif)
           ->collect(classname|if classname='Group' then 'Group_TBL' else classname endif)->first.replace('-', '_'),
      ae.Name.replace('-', '_'),
      let c1 = ae.Participant in
        if c1.TaggedValue->exists(tv | (tv.Tag='Eco.TableMapping') and (tv.Value = 'Parent')) then
          c1.Superclass.Name
        else
          c1.Name
        endif->collect(classname|if classname='User' then 'User_TBL' else classname endif)
           ->collect(classname|if classname='Group' then 'Group_TBL' else classname endif)->first.replace('-', '_')
    )->orderBy(x|x.Part1)
     ->collect(d | 'select * from ' + d.Part1 + ' where ' + d.Part2 + 'ID is not null and ' + d.Part2 + 'ID not in (select ' + d.Part3 + 'ID from ' + d.Part3 + ')')
     ->asSeparatedList('\r\n')

Read the generated SQL

Each generated statement follows this pattern:

select * from <table-name>
where <link-name>ID is not null
  and <link-name>ID not in (select <target-table>ID from <target-table>)

It finds rows where the single-link ID has a value, but no row with that ID exists in the target table.

Repair confirmed invalid single links

For a reviewed query that returns invalid single-link references, replace select * with an update that sets the generated link column to null. Keep the generated WHERE clause so that only invalid references are cleared:

update <table-name>
set <link-name>ID = null
where <link-name>ID is not null
  and <link-name>ID not in (select <target-table>ID from <target-table>)

This removes the invalid reference; it does not recreate the missing target object. Run the original SELECT after the update to verify that no invalid references remain.

If the checks do not resolve the issue

These checks cover missing targets for persistent link classes and single links. They do not diagnose every database problem or every mapping issue. Review the evolve that preceded the inconsistency in Documentation:Database evolve, and review the model's persistence mapping in Documentation:Working with Code and Persistence Mapping. If you are operating a containerized production deployment, also review Documentation:Configuring Production Databases PostgreSQL MySQL MSSQL.

See also

• Documentation:Database evolve
• Documentation:Database download and upload
• Documentation:Duplicate IDs in the database
• Documentation:SQL Database
• Documentation:SQL