Useful DSpace sql queries

dspace_queries.md

Recently Created (within 30 days) that are missing TN files

SELECT 
    h.handle
FROM 
    handle h
JOIN
    item i
ON
    h.resource_id = i.uuid
WHERE 
    i.last_modified >= current_date - interval '30 days'
AND
    i.uuid NOT IN (
        SELECT 
            item_id
        FROM 
            bundle,
            metadatavalue, 
            item2bundle
        WHERE 
            bundle.uuid = metadatavalue.dspace_object_id AND
            bundle.uuid = item2bundle.bundle_id AND
            metadatavalue.text_value = 'THUMBNAIL'
    );

Most recent (sorted by date)

SELECT 
    h.handle, 
    i.last_modified 
FROM 
    item i 
JOIN 
    handle h 
ON 
    i.item_id = h.resource_legacy_id 
WHERE 
    h.resource_type_id = (SELECT DISTINCT resource_type_id FROM handle WHERE resource_legacy_id = i.item_id LIMIT 1) 
ORDER BY 
    i.last_modified DESC;

Missing TN files

SELECT uuid from item where uuid NOT IN (
 SELECT 
  item_id
FROM 
  bundle,
  metadatavalue, 
  item2bundle
WHERE 
  bundle.uuid = metadatavalue.dspace_object_id AND
  bundle.uuid = item2bundle.bundle_id AND
  metadatavalue.text_value = 'THUMBNAIL');

Find any value in the database. You can handles, uuid, or really anything.

-- Find all tables and columns that contain a specific value
-- Example of searching all tables and columns for a specific string
DO $$
DECLARE
    table_col record;
    search_value text;
    query text;
    result_row record;
BEGIN
    search_value := '1774.2/9958';
    
    FOR table_col IN 
        SELECT table_name, column_name 
        FROM information_schema.columns 
        WHERE table_schema = 'public'
    LOOP
        query := format('
            SELECT * 
            FROM public.%I 
            WHERE %I::text ILIKE $1', table_col.table_name, table_col.column_name);

        -- Loop through results of the dynamic query
        FOR result_row IN EXECUTE query USING '%' || search_value || '%'
        LOOP
            RAISE NOTICE 'Found in table %, column %', table_col.table_name, table_col.column_name;
            RAISE NOTICE 'Row: %', result_row;
        END LOOP;
    END LOOP;
END $$;