Documentation:Database corruption

Problem

An inconsistent database is a rare thing when using MDriven Server and Turnkey.

But it can happen, and if it happens it's almost always because of an evolve that went wrong somehow and objects was left behind.

Especially problematic is link-classes with incorrect link information. Because link-objects only exist when the two connected objects exist, they can't even be loaded into memory if the linked objects are missing.

So, this needs to be corrected at the sql server level.

But, as an MDriven user we really don't want to spend any time on SQL-related things, so, in order to create the SQL that looks up problems, we can use the model debugger on the model itself.

Start the model debugger (right click model surface -> Extras -> Open model debugger...) and run the following OCL.

(Note: The generated OCL does not work for child-mapped classes right away. You need to manually add the class names of the child mapped classes instead of the parent class, usually an abstract class)

How to correct

For link-classes

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

Running SQL

The OCL above will produce a long string similar to this for every link class in your model

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

Which simply looks up any link from a link class to non-existent objects.

To correct to problem, change select * to delete and run the query again. And be careful!!!

For single-links

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

Running SQL

The OCL above will produce a long string similar to this for every link class in your model

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

Which simply looks up any a single-link from an object to non-existent objects.

To correct to problem, change select * to update <table-name> set <link name>=null and run the query again. And be careful!!!

Tags

Check database error, Database validation, Generate script for database check