<?xml version="1.0" encoding="UTF-8" ?> <rdf:RDF xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns="http://purl.org/rss/1.0/"> <channel rdf:about="http://dev.libresource.org/home/community/forum/database_cleanup_"> <title>database cleanup...</title> <description><![CDATA[database cleanup...]]></description> <link>http://dev.libresource.org/home/community/forum/database_cleanup_</link> <items> <rdf:Seq><rdf:li rdf:resource="http://dev.libresource.org/home/community/forum/database_cleanup_/RE_database_cleanup_" /> <rdf:li rdf:resource="http://dev.libresource.org/home/community/forum/database_cleanup_/database_cleanup_" /> </rdf:Seq> </items> </channel> <item rdf:about="http://dev.libresource.org/home/community/forum/database_cleanup_/RE_database_cleanup_"> <title>RE : database cleanup...</title> <link>http://dev.libresource.org/home/community/forum/database_cleanup_/RE_database_cleanup_</link> <dc:creator>Sebastien Jourdain</dc:creator> <dc:date>2008-01-22T17:21:32+01:00</dc:date> <description><![CDATA[Thanks a lot Manu !]]></description> </item><item rdf:about="http://dev.libresource.org/home/community/forum/database_cleanup_/database_cleanup_"> <title>database cleanup...</title> <link>http://dev.libresource.org/home/community/forum/database_cleanup_/database_cleanup_</link> <dc:creator>manu</dc:creator> <dc:date>2008-01-22T16:54:39+01:00</dc:date> <description><![CDATA[Just in case it might helps here is a first attempt to write a cleanup script for the LS database. It seems to work quite fine for my own LS.<p class="paragraph"/>Do not hesitate to contribute to make it more robust… ;)<p class="paragraph"/><div class="code"><pre>-- 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;<p class="paragraph"/> -- User Deletion handling -- ******************<p class="paragraph"/>-- 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/%');<p class="paragraph"/>-- 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%');<p class="paragraph"/>-- 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/%');<p class="paragraph"/> -- TODO Update groups members in <span class="java-keyword">case</span> of errors -- update groupresource_ SET memberslist_ = 'User:adminProject1' where id_ = '8d883b407f00010101cff2238d929f9c';<p class="paragraph"/>-- Database cleanup -- ****************<p class="paragraph"/>-- 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%');<p class="paragraph"/>-- 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/%');<p class="paragraph"/>-- 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_);<p class="paragraph"/>-- 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/%');<p class="paragraph"/>-- 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/%');<p class="paragraph"/>-- 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/%');<p class="paragraph"/>-- 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/%');<p class="paragraph"/>-- 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_);<p class="paragraph"/>-- 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/%');<p class="paragraph"/>-- 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/%');<p class="paragraph"/>-- 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/%');<p class="paragraph"/>-- and so on...<p class="paragraph"/>-- Vacuum database VACUUM;</pre></div><p class="paragraph"/>Cheers<p class="paragraph"/>Manu<p class="paragraph"/><p class="paragraph"/><p class="paragraph"/><p class="paragraph"/><p class="paragraph"/> ]]></description> </item> </rdf:RDF>