gistfile1.txt

--Identifiers
update identifier set asset_type = 'links' where id in (select identifier FROM links) and asset_type not like 'links';
update identifier set asset_type = 'htmlpage' where id in (select identifier FROM htmlpage) and asset_type not like 'htmlpage';
update identifier set asset_type = 'file_asset' where id in (select identifier FROM file_asset) and asset_type not like 'file_asset';
delete from identifier where host_inode = '0' and  asset_type = 'links' and id in (select identifier FROM links);
delete from identifier where host_inode = '0' and  asset_type = 'htmlpage' and id in (select identifier FROM htmlpage);
delete from identifier where host_inode = '0' and  asset_type = 'file_asset' and id in (select identifier FROM file_asset);

--HTML Pages
delete from htmlpage_version_info where identifier in (select id from identifier where asset_type = 'htmlpage' and id not in (select identifier from htmlpage));
delete from htmlpage where identifier in (select id from identifier where asset_type = 'htmlpage' and id not in (select identifier from htmlpage));
delete from inode where type = 'htmlpage' and inode not in (select inode from htmlpage); 

update htmlpage_version_info set deleted = 1 where live_inode is null; 
update htmlpage_version_info set deleted = 0 where live_inode is not null; 

--File assets
delete from fileasset_version_info where identifier in (select id from identifier where asset_type = 'file_asset' and id not in (select identifier from file_asset)); 
delete from file_asset where identifier in (select id from identifier where asset_type = 'file_asset' and id not in (select identifier from file_asset)); 
delete from inode where type = 'file_asset' and inode not in (select inode from file_asset);

update fileasset_version_info set deleted = 1 where live_inode is null; 
update fileasset_version_info set deleted = 0 where live_inode is not null; 

--Links
delete from link_version_info where identifier in (select id from identifier where asset_type = 'links' and id not in (select identifier from links)); 
delete from links where identifier in (select id from identifier where asset_type = 'links' and id not in (select identifier from links)); 
delete from inode where type = 'links' and inode not in (select inode from links);

update link_version_info set deleted = 1 where live_inode is null; 
update link_version_info set deleted = 0 where live_inode is not null;

--Parent paths for HTML Pages
update htmlpage set page_url = REPLACE(page_url,' ','') where page_url like '% %' and identifier in (select identifier from htmlpage_version_info where deleted = 0); 
update identifier set asset_name = REPLACE(asset_name, ' ', '') where asset_type = 'htmlpage' and id in (select identifier from htmlpage) and asset_name like '% %' and id in (select identifier from htmlpage_version_info where deleted = 0);