File Hash Number of Occurrences

"File Hash Number of Occurrences" is a Drupal module that helps administrators
identify duplicate files by aggregating files with the same hash value and
counting their occurrences.
It provides SQL View definitions for each enabled hash algorithm in the File Hash module.

This module is a workaround for the missing `HAVING` support in views. \
`HAVING` is needed to define filter conditions based on aggregated fields like COUNT(*).
#170245: Add HAVING support

Features

• Aggregates files by hash value.
• Counts the number of occurrences for each file hash.
• Integrates with Views for flexible reporting.
• Helps identify duplicate files in your Drupal site.

Requirements

• Drupal 11.x
• File Hash 3.x module
• Views module (optional, but recommended for reporting features)

Installation

composer require drupal/filehash_noo
./vendor/bin/drush pm:install filehash_noo

Configuration

1. Enable at least one hash algorithm in the File Hash module at `/admin/config/media/filehash`.
2. Generate missing checksums if necessary at `/admin/config/media/filehash/generate`.
3. Go to `/admin/config/media/filehash-noo` to create the required counter tables.

Note: The instance counter (which tracks the number of times each file hash appears) is not created automatically.
You must create it manually via the configuration form.

Drush configuration

NOTE: I have to re-check the statement bellow, because of MySQL's findTables() will no longer find database views

Drupal and Drush is not prepared for the presence of SQL View definitions.
The following configuration option is required to prevent Drush from dumping the
structure and the content if filehash_noo_algo_* SQL Views.

command:
  sql:
    dump:
      options:
        skip-tables-key: 'common'
        structure-tables-key: 'common'

sql:
  skip-tables:
    common:
      - 'filehash_noo_algo_*'
  structure-tables:
    common:
      - 'cache'
      - 'cache_*'
      - 'history'
      - 'search_*'
      - 'sessions'
      - 'watchdog'

Usage

Find all instances of a specific file

1. Create a new view using the "Files" wizard (View settings / Show)
2. Add a "Page" display
3. "Page Settings" / "Path" = `file/%file/instances`
4. Add a "Contextual filter"
   1. Category: "Media"; Title: "SHA-256 hash"
   2. "When the filter value IS in the URL or a default is provided"
      1. Enable "Specify validation criteria"
      2. Set "Validator" to `File Hash NoO - Transform File ID to Hash`
         This Validator plugin exploits this hidden "feature": #2959929: Consider making \Drupal\views\Plugin\views\argument\ArgumentPluginBase::$argument protected

Find files with duplicates

1. Edit any existing view (e.g.: files, file_entity_files) or create a new view by using the "Files" wizard (View settings / Show).
2. Without adding any "Relationships" the "Amount" field in the "File Hash NoO - sha256" category is available in the "Fields", "Filter criteria" and "Sort criteria" sections.
3. When it is added in the "Fields" section
   1. "Rewrite results"
      1. "Output this field as a custom link" = Enabled
      2. "Link path" = /file/{{ fid }}/instances

This module does not provide tools to eliminate duplicated files.

Unexpected problems

Unexpected problems - MariaDB-11.x - drush sql:drop

> ERROR 1965 (42S02) at line 1: 'MY_DB_NAME.filehash_noo_algo_sha256' is a view

Before drush sql:drop run the following command to drop all the filehash_noo related SQL Views.

drush filehash-noo:sql-view:drop-all

Useful MariaDB SQL commands:

SHOW FULL TABLES WHERE Tables_in_MY_DB_NAME LIKE 'filehash\_noo\_algo\_%' AND Table_type = 'VIEW';
DROP VIEW IF EXISTS filehash_noo_algo_sha256;

Unexpected problems - PostgreSQL-17.x - drush sql:drop

No problem. All tables and views are dropped.

Unexpected problems - SQLite - drush sql:drop

> Unable to drop all tables: Parse error near line 1: use DROP VIEW to delete view filehash_noo_algo_sha256

Before drush sql:drop run the following command to drop all the filehash_noo related SQL Views.

drush filehash-noo:sql-view:drop-all

Useful SQLite SQL commands:

SELECT name FROM sqlite_master WHERE type = 'view' AND name LIKE 'filehash_noo_algo_%' ORDER BY name;
DROP VIEW IF EXISTS filehash_noo_algo_sha256;