-- Database cleanup script first attempt
-- author : sartorius
-- date : 01/22/08
--
-- Script Execution : psql -U libresource -f cleanup.sql -L LogFile.log
-- On error => Dump reinstall :
-- * dropdb libresource; 
-- * createdb -O libresource -E UTF8 libresource; 
-- * pg_restore -d libresource dumpFile.dmp;
-- * pg_restore template1 -d template1 dumpFile.dmp;
-- * pg_restore postgres -d postgres dumpFile.dmp;

-- User Deletion handling
-- ******************
-- Change deprecated owner of resources to root
select * from node_ where path_ like '/projects/%' and owner_ not in (select id_ from node_ where path_ like '/users/%');
update node_ set owner_ = (select id_ from node_ where path_='/users/root') where path_ like '/projects/%' and owner_ not in (select id_ from node_ where path_ like '/users/%');
-- Eliminates deprecated profiles
select * from profileresource_ where id_ not in (select split_part(bindedresourceidentifier_,'/',3) from node_ where bindedresourceidentifier_ like '%Profile%');
delete from profileresource_ where id_ not in (select split_part(bindedresourceidentifier_,'/',3) from node_ where bindedresourceidentifier_ like '%Profile%');
-- Eliminates deprecated users
select * from casusers_ where username_ not in ( select split_part(path_,'/',3) from node_ where path_ like '%/users/%');
delete from casusers_ where username_ not in ( select split_part(path_,'/',3) from node_ where path_ like '%/users/%');

-- TODO Update groups members in case of errors
-- update groupresource_ SET memberslist_ = 'User:adminProject1' where id_ = '8d883b407f00010101cff2238d929f9c';
-- Database cleanup
-- ****************
-- Deletes deprecated groups
select * from groupresource_ where id_ not in ( select split_part(bindedresourceidentifier_,'/',3) from node_ where bindedresourceidentifier_ like '%Group%');
delete from groupresource_ where id_ not in (select split_part(bindedresourceidentifier_,'/',3) from node_ where bindedresourceidentifier_ like '%Group%');
-- Deletes deprecated pages
select * from pageresource_ where id_ not in (select split_part(bindedresourceidentifier_,'/',3) from node_ where bindedresourceidentifier_ like '%/Page/%');
delete from pageresource_ where id_ not in (select split_part(bindedresourceidentifier_,'/',3) from node_ where bindedresourceidentifier_ like '%/Page/%');
-- TODO Delete deprecated pages tables
select tablename from pg_tables where tablename like 'page_history%' and split_part(tablename,'_',3) not in (select id_ from pageresource_);
-- Deletes deprecated files
select * from fileresource_ where id_ not in (select split_part(bindedresourceidentifier_,'/',3) from node_ where bindedresourceidentifier_ like '%/File/%');
delete from fileresource_ where id_ not in (select split_part(bindedresourceidentifier_,'/',3) from node_ where bindedresourceidentifier_ like '%/File/%');
-- Deletes deprecated files content
select *  from files_content_ where id not in (select split_part(bindedresourceidentifier_,'/',3) from node_ where bindedresourceidentifier_ like '%/File/%');
delete from files_content_ where id not in (select split_part(bindedresourceidentifier_,'/',3) from node_ where bindedresourceidentifier_ like '%/File/%');
-- Deletes deprecated bugtracker
select * from bugtrackerresource_ where id_ not in (select split_part(bindedresourceidentifier_,'/',3) from node_ where bindedresourceidentifier_ like '%/BugTracker/%')
delete from bugtrackerresource_ where id_ not in (select split_part(bindedresourceidentifier_,'/',3) from node_ where bindedresourceidentifier_ like '%/BugTracker/%');
-- Deletes deprecated timelines
select * from timelineresource_ where id_ not in (select split_part(bindedresourceidentifier_,'/',3) from node_ where bindedresourceidentifier_ like '%/Timeline/%');
delete from timelineresource_ where id_ not in (select split_part(bindedresourceidentifier_,'/',3) from node_ where bindedresourceidentifier_ like '%/Timeline/%');
-- TODO deletes deprecates timelines tables
select tablename from pg_tables where tablename like 'timeline_%' and tablename not like 'timelineresource_' and split_part(tablename,'_',3) not in (select id_ from timelineresource_);
-- Deletes deprecated templates
select * from templateresource_ where id_ not in (select split_part(bindedresourceidentifier_,'/',3) from node_ where bindedresourceidentifier_ like '%/Template/%');
delete from templateresource_ where id_ not in (select split_part(bindedresourceidentifier_,'/',3) from node_ where bindedresourceidentifier_ like '%/Template/%');
-- Deletes deprecated templates content
select * from templates_content_ where id not in (select split_part(bindedresourceidentifier_,'/',3) from node_ where bindedresourceidentifier_ like '%/Template/%');
delete from templates_content_ where id not in (select split_part(bindedresourceidentifier_,'/',3) from node_ where bindedresourceidentifier_ like '%/Template/%');
-- Deletes deprecated projects
select * from projectresource_ where id_ not in (select split_part(bindedresourceidentifier_,'/',3) from node_ where bindedresourceidentifier_ like '%/Project/%');
delete from projectresource_ where id_ not in (select split_part(bindedresourceidentifier_,'/',3) from node_ where bindedresourceidentifier_ like '%/Project/%');
-- and so on...
-- Vacuum database
VACUUM;