[Dspace-tech] SQL query to count number of downloadables (bitstream)
Dear All, I would like to ask for your help regarding in generating an SQL query. I'm using DSpace 5 and I've noticed there were changes in the bitstream table particularly the bitstream's file name is not there. How can I generate a list for all the bitstreams with a pdf file extension and file name? Thanks in advance, euler -- View this message in context: http://dspace.2283337.n4.nabble.com/SQL-query-to-count-number-of-downloadables-bitstream-tp4676287.html Sent from the DSpace - Tech mailing list archive at Nabble.com. -- New Year. New Location. New Benefits. New Data Center in Ashburn, VA. GigeNET is offering a free month of service with a new server in Ashburn. Choose from 2 high performing configs, both with 100TB of bandwidth. Higher redundancy.Lower latency.Increased capacity.Completely compliant. http://p.sf.net/sfu/gigenet ___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech List Etiquette: https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette
Re: [Dspace-tech] SQL query to count number of downloadables (bitstream)
Hi Euler, try this query. I haven't tested it much, but it should give you the idea how this works in DSpace 5. This reminds me we should also update the ER-diagram in documentation. SELECT count(1)FROM bundle2bitstreamINNER JOIN metadatavalue ON bitstream_id = resource_id WHERE resource_type_id=1 AND metadata_field_id=(SELECT metadata_field_id FROM metadatafieldregistry WHERE metadata_schema_id=(SELECT metadata_schema_id FROM metadataschemaregistry WHERE short_id='dc') AND element='title' AND qualifier IS NULL) AND text_value='ORIGINAL' Regards, ~~helix84 Compulsory reading: DSpace Mailing List Etiquette https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette -- New Year. New Location. New Benefits. New Data Center in Ashburn, VA. GigeNET is offering a free month of service with a new server in Ashburn. Choose from 2 high performing configs, both with 100TB of bandwidth. Higher redundancy.Lower latency.Increased capacity.Completely compliant. http://p.sf.net/sfu/gigenet___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech List Etiquette: https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette
Re: [Dspace-tech] SQL query to count number of downloadables (bitstream)
Hello Helix, Thanks for this. It did returned me the number of bitstreams. I wonder if it's possible to generate a list of these bitstreams? In previous dspace versions, I would just import the bitstream table into ms access and do the filtering (e.g. filter by selection with filenames ending in pdf). With the dspace 5 version, I can't find the table where the filenames were saved. Thanks again for your help, I needed the figures for our annual report. Regards, euler -- View this message in context: http://dspace.2283337.n4.nabble.com/SQL-query-to-count-number-of-downloadables-bitstream-tp4676287p4676368.html Sent from the DSpace - Tech mailing list archive at Nabble.com. -- New Year. New Location. New Benefits. New Data Center in Ashburn, VA. GigeNET is offering a free month of service with a new server in Ashburn. Choose from 2 high performing configs, both with 100TB of bandwidth. Higher redundancy.Lower latency.Increased capacity.Completely compliant. http://p.sf.net/sfu/gigenet ___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech List Etiquette: https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette
