Database consistency

[p3k]

1. created, modified, creator_id or modifier_id values

   select id, created, modified from account where created is null or modified is null;
   select id, created, modified from choice where created is null or modified is null;
   select id, created, modified, creator_id, modifier_id from content where created is null or modified is null or creator_id is null;
   select id, created, modified, creator_id, modifier_id from file where created is null or modified is null or creator_id is null;
   select id, created, modified, creator_id, modifier_id from image where created is null or modified is null or creator_id is null;
   select id, created, modified, creator_id, modifier_id from layout where created is null or modified is null or creator_id is null;
   select id, created, modified, creator_id, modifier_id from membership where created is null or modified is null or creator_id is null;
   select id, created, modified, creator_id, modifier_id from poll where created is null or modified is null or creator_id is null;
   select id, created, modified, creator_id, modifier_id from site where created is null or modified is null or creator_id is null;
   select id, created, modified, creator_id, modifier_id from skin where created is null or modified is null or creator_id is null;
   select id, created, creator_id, modifier_id from skin where created is null or creator_id is null;
   

2. Memberships

   # Missing site
   select id from membership where site_id not in (select id from site);
   # Each site needs at least one owner
   select id from site where id not in (select site_id from membership where role = 'owner');
   

3. Parent

   select id from content where site_id not in (select id from site);
   select id from file where site_id not in (select id from site);
   select id from image where site_id not in (select id from site);
   select id from layout where site_id not in (select id from site);
   select id from poll where site_id not in (select id from site);
   select id from skin where layout_id not in (select id from layout where site_id not in (select id from site));
   # Missing parent story or comment
   select id from content where parent_id not in (select id from content);
   # Missing parent site or layout
   select id from image where parent_type = 'Site' and parent_id not in (select id from site);
   select id from image where parent_type = 'Layout' and parent_id not in (select id from layout);
   select id from skin where layout_id not in (select id from layout);
   

4. Tags

   # Missing site
   select id from tag where site_id not in (select id from site);
   # Missing tag
   select id from tag_hub where tag_id not in (select id from tag);
   # Unused tag
   select id from tag where id not in (select tag_id from tag_hub);
   # Missing story or image
   select id from tag_hub where tagged_type = 'Story' and tagged_id not in (select id from content);
   select id from tag_hub where tagged_type = 'Image' and tagged_id not in (select id from image);
   

[github-actions[bot]]

Stale issue message