Hi Joseph I was thinking that I'd need to look in the following tables to fully understand what was going on with a collection
collection collection2item workflowitem workspaceitem collection_item_count item Is collection2item what handles mappings, while item.owning_collection identifies to 'real' parent collection of an item? Thanks Steven Steven Hayles Systems Analyst IT Services, University of Leicester, Propsect House, 94 Regent Rd, Leicester, LE1 7DA, UK T: +44 (0)116 229 7950 E: [email protected] The Queen's Anniversary Prizes 1994, 2002 & 2013 THE Awards Winners 2007-2013 Elite without being elitist Follow us on Twitter http://twitter.com/uniofleicester or visit our Facebook page https://facebook.com/UniofLeicester -----Original Message----- From: Joseph Greene [mailto:[email protected]] Sent: 30 January 2015 11:40 To: [email protected] Cc: Hayles, Steven Subject: RE: Best approach to bulk delete empty collections Actually that query was a little restrictive. This may be better: select collection_id, name from collection where collection_id not in (select owning_collection from item where in_archive = true and withdrawn = false); So any collection that shows 0 items live, but may have withdrawn items and may also have items in submitters' workflows (ie cancelled uploads). Problem is 'virtual' collections with only mapped items, they will appear as having no items. Combining the results of the two queries may help...I'm sure there's a way to output the count of mapped items into the results. Joseph -----Original Message----- From: Joseph Greene [mailto:[email protected]] Sent: 30 January 2015 11:28 To: [email protected] Cc: [email protected] Subject: RE: Best approach to bulk delete empty collections Hello Steven, As a start here is a query that will identify empty collections: select collection_id, name from collection where collection_id not in (select distinct collection_id from collection2item); To my knowledge that should work on 1.4.2's database (was on 1.4.2 recently enough). Best regards, Joseph Joseph Greene Research Repository Librarian James Joyce Library University College Dublin (353 0)1 716 7398 [email protected] http://researchrepository.ucd.ie Message: 2 Date: Fri, 30 Jan 2015 09:26:41 +0000 (GMT) From: Steven Hayles <[email protected]> Subject: [Dspace-tech] Best approach to bulk delete empty collections (DSpace 4.2) To: [email protected] Message-ID: <[email protected]> Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed Hi We have a DSpace 4.2 instance with many empty collections. What's the best approach to finding and deleting them? Thanks Steven Hayles Systems Analyst IT Services, University of Leicester, Propsect House, 94 Regent Rd, Leicester, LE1 7DA, UK T: +44 (0)116 229 7950 E: [email protected] The Queen's Anniversary Prizes 1994, 2002 & 2013 THE Awards Winners 2007-2013 Elite without being elitist Follow us on Twitter http://twitter.com/uniofleicester or visit our Facebook page https://facebook.com/UniofLeicester ------------------------------------------------------------------------------ Dive into the World of Parallel Programming. The Go Parallel Website, sponsored by Intel and developed in partnership with Slashdot Media, is your hub for all things parallel software development, from weekly thought leadership blogs to news, videos, case studies, tutorials and more. Take a look and join the conversation now. http://goparallel.sourceforge.net/ _______________________________________________ DSpace-tech mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/dspace-tech List Etiquette: https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette
