Add tasks to clean up DB inconsistencies: children with container which is no longer a gridelement (orphans)

[sypets]

The following inconsistency should not happen and will cause existing elements to disappear (in list and page view):

• elements have tx_gridelements_container != 0 and colpos=-1, but the container is not a gridelement type

Reproduce

1. Create a gridelement with children
2. change the CType of the gridelement (e.g. to textmedia).

Result: The children will then "disappear" in list and page view

SQL select for showing these records

SELECT t1.uid,t1.pid,t1.sys_language_uid,t1.ctype,t1.header,t2.uid,t2.pid,t2.header,t2.ctype 
    FROM tt_content t1 
    INNER JOIN tt_content t2 
        ON t1.tx_gridelements_container=t2.uid
    WHERE t1.colpos=-1 
        AND t1.tx_gridelements_container != 0
       AND  t2.ctype NOT LIKE 'gridelements_pi1'
       AND NOT t1.deleted
       AND NOT t2.deleted;

UPDATE tt_content t1 
    INNER JOIN tt_content t2 
        ON t1.tx_gridelements_container=t2.uid
    SET t1.colpos=0,t1.tx_gridelements_container=0    
    WHERE t1.colpos=-1 
        AND t1.tx_gridelements_container != 0
       AND  t2.ctype NOT LIKE 'gridelements_pi1'
       AND NOT t1.deleted
       AND NOT t2.deleted;

Possible solutions

1. Create a scheduler task to clean up existing records. For these records, set colpos to 0 (or the default) and tx_gridelements_container to 0. In general, if the records are cleaned up, I think it is a good idea to make the elements "hidden" by default, otherwise elements will suddenly "reappear" which were formerly hidden.

2. or prevent this from happening: it should not be possible to change the ctype

3. or if the ctype is changed, the elements should be released from the parent or deleted

Related

• old issue: https://gitlab.com/coderscare/gridelements/-/issues/30
• other issue also related to DB inconsistencies: https://github.com/CodersCare/gridelements/issues/63
• already existing tasks, see https://github.com/CodersCare/gridelements/tree/master/Classes/Task