Database consistency #218

New issue

Open

opened 2018-05-21 10:00:55 +00:00 by p3k · 1 comment

p3k commented

2018-05-21 10:00:55 +00:00

(Migrated from github.com)

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;

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

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

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

1. created, modified, creator_id or modifier_id values ```sql 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 ```sql # 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 ```sql 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 ```sql # 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] commented

2021-05-17 02:38:00 +00:00

(Migrated from github.com)

Stale issue message