database cleanup... http://dev.libresource.org/home/community/forum/database_cleanup_ RE : database cleanup... http://dev.libresource.org/home/community/forum/database_cleanup_/RE_database_cleanup_ Sebastien Jourdain 2008-01-22T17:21:32+01:00 database cleanup... http://dev.libresource.org/home/community/forum/database_cleanup_/database_cleanup_ manu 2008-01-22T16:54:39+01:00 Do not hesitate to contribute to make it more robust… ;)

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

Cheers

Manu

]]>