fb_imagecleaner_image Database Table Structure and entity_type_id Values in OpenMage Image Cleaner

The fb_imagecleaner_image table stores orphaned image records with three columns—image_id, entity_type_id, and path—where entity_type_id maps to Magento EAV entities (3 for categories, 4 for products) and negative values indicate special groups like product cache (-4) and WYSIWYG (-98).

The fballiano/openmage-image-cleaner extension tracks unused media files by persisting metadata in a dedicated database table. Understanding the fb_imagecleaner_image database table structure is essential for developers customizing cleanup logic or integrating the module with external reporting tools.

Database Schema of fb_imagecleaner_image

Table Structure

The table is created during module installation in app/code/community/Fballiano/ImageCleaner/sql/fballiano_imagecleaner_setup/install-0.1.0.php. It contains three columns designed to uniquely identify orphaned files and their origins:

Column	Type	Description
image_id	INT UNSIGNED AUTO_INCREMENT	Primary key that uniquely identifies each record.
entity_type_id	SMALLINT	Foreign key concept mapping to Magento entity types; stores values like 3, 4, -4, or -98.
path	VARCHAR(255) NOT NULL	Relative path of the image inside its corresponding media sub-folder (e.g., category/image.jpg).

The schema enforces a unique composite key on entity_type_id and path to prevent duplicate entries for the same file:

CREATE TABLE `fb_imagecleaner_image` (
    `image_id` int unsigned AUTO_INCREMENT,
    `entity_type_id` smallint (5) unsigned NOT NULL,
    `path` varchar(255) NOT NULL,
    PRIMARY KEY (`image_id`),
    UNIQUE KEY `entity_type_id` (`entity_type_id`,`path`)
);

Installation and Upgrade Scripts

The initial installation script defines entity_type_id as SMALLINT(5) UNSIGNED. However, the upgrade script app/code/community/Fballiano/ImageCleaner/sql/fballiano_imagecleaner_setup/upgrade-1.0.0-1.1.0.php modifies this column to signed to accommodate negative identifiers:

$installer->run("
    ALTER TABLE {$this->getTable('fb_imagecleaner_image')}
    MODIFY COLUMN entity_type_id SMALLINT NOT NULL
");

This change allows the module to store -4 for product cache images and -98 for WYSIWYG assets, distinguishing them from standard EAV entities that use positive integers.

Understanding entity_type_id Values

Standard Entity Mappings

The entity_type_id column bridges the gap between database records and Magento's EAV architecture. Values are derived directly from Magento's entity type table:

• 3 – Category images stored in media/catalog/category/
• 4 – Product images stored in media/catalog/product/

These values correspond to the return of getResource()->getTypeId() calls on Magento models:

$categoryTypeId = Mage::getModel('catalog/category')->getResource()->getTypeId(); // Returns 3
$productTypeId = Mage::getModel('catalog/product')->getResource()->getTypeId();  // Returns 4

How Entity IDs Are Resolved

The helper class app/code/community/Fballiano/ImageCleaner/Helper/Data.php provides the mapping logic through getMediaDirByEntityTypeId(). This method translates numeric IDs into absolute filesystem paths:

public function getMediaDirByEntityTypeId($entityTypeId)
{
    switch ($entityTypeId) {
        case 3:
            return Mage::getBaseDir('media') . DS . 'catalog' . DS . 'category' . DS;
        case 4:
        case -4:
            return Mage::getBaseDir('media') . DS . 'catalog' . DS . 'product' . DS;
        case -98:
            return Mage::getBaseDir('media') . DS . 'wysiwyg' . DS;
        default:
            // Fallback for custom entities
            return Mage::getBaseDir('media') . DS;
    }
}

When the admin controller processes deletion or download requests, it invokes this helper to reconstruct the full path:

$mediaDir = $helper->getMediaDirByEntityTypeId($image['entity_type_id']);
$fullPath = $mediaDir . $image['path'];

Negative Values and Special Groups

Negative entity_type_id values represent special image groups that do not correspond to standard EAV entities:

• -4 – Product cache images located in media/catalog/product/cache/. The negative sign distinguishes cached thumbnails from original product uploads.
• -98 – WYSIWYG assets used by the CMS editor, stored in media/wysiwyg/.

The upgrade to signed integers in version 1.1.0 was specifically implemented to support these negative identifiers, allowing the module to track cache and CMS images alongside standard catalog media.

Working with the Table: Code Examples

Querying Orphaned Images

To retrieve records for a specific entity type directly from the database:

$resource = Mage::getSingleton('core/resource');
$db = $resource->getConnection('core_read');
$table = $resource->getTableName('fb_imagecleaner_image');

$orphanedProducts = $db->fetchAll(
    "SELECT * FROM {$table} WHERE entity_type_id = ?", 
    [4]
);

foreach ($orphanedProducts as $image) {
    echo "Orphan: {$image['path']}" . PHP_EOL;
}

Inserting Custom Records

When extending the cleaner to support custom entities, manually insert records using the appropriate type ID:

$customEntityTypeId = 12; // Your custom EAV entity type ID
$relativePath = 'custom/entity/image.jpg';

$db->insert($table, [
    'entity_type_id' => $customEntityTypeId,
    'path' => $relativePath
]);

Resolving Filesystem Paths

Convert database records to absolute paths for file operations:

$helper = Mage::helper('fballiano_imagecleaner');
$imageRecord = $db->fetchRow("SELECT * FROM {$table} WHERE image_id = ?", [42]);

$mediaDirectory = $helper->getMediaDirByEntityTypeId($imageRecord['entity_type_id']);
$absolutePath = $mediaDirectory . $imageRecord['path'];

if (file_exists($absolutePath)) {
    // Process file
}

Deleting Images Programmatically

Replicate the controller's deletion logic for custom automation:

$imageId = 42;
$image = $db->fetchRow("SELECT * FROM {$table} WHERE image_id = ?", [$imageId]);

if ($image) {
    $fullPath = $helper->getMediaDirByEntityTypeId($image['entity_type_id']) 
                . $image['path'];
    
    if (file_exists($fullPath) && unlink($fullPath)) {
        $db->query("DELETE FROM {$table} WHERE image_id = ?", [$imageId]);
    }
}

Key Source Files

File	Purpose
app/code/community/Fballiano/ImageCleaner/sql/fballiano_imagecleaner_setup/install-0.1.0.php	Creates the fb_imagecleaner_image table with initial schema
app/code/community/Fballiano/ImageCleaner/sql/fballiano_imagecleaner_setup/upgrade-1.0.0-1.1.0.php	Alters entity_type_id from unsigned to signed smallint
app/code/community/Fballiano/ImageCleaner/Helper/Data.php	Contains getMediaDirByEntityTypeId() mapping logic
app/code/community/Fballiano/ImageCleaner/controllers/Adminhtml/FbimagecleanerController.php	Controller actions that query the table and resolve paths
app/code/community/Fballiano/ImageCleaner/Model/Resource/Image.php	Resource model for database operations
app/code/community/Fballiano/ImageCleaner/Model/Resource/Image/Collection.php	Collection class for grid rendering

Summary

• The fb_imagecleaner_image table consists of three columns: image_id (primary key), entity_type_id (entity classifier), and path (relative file location).
• The entity_type_id column uses positive integers for standard Magento EAV entities: 3 for categories and 4 for products.
• Negative values represent special groups: -4 for product cache images and -98 for WYSIWYG assets, enabled by upgrading the column to signed integers in version 1.1.0.
• The Helper/Data.php file maps these IDs to physical directories via getMediaDirByEntityTypeId(), bridging database records and filesystem operations.

Frequently Asked Questions

What is the primary key of the fb_imagecleaner_image table?

The primary key is image_id, defined as an INT UNSIGNED AUTO_INCREMENT column that uniquely identifies each orphaned image record in the system.

Why does entity_type_id use negative values in some cases?

Negative values distinguish special image groups that do not correspond to standard EAV entities. The value -4 marks product cache images stored in media/catalog/product/cache/, while -98 identifies WYSIWYG assets. The column was converted from unsigned to signed in upgrade script upgrade-1.0.0-1.1.0.php specifically to support these negative identifiers.

How does the module determine the full filesystem path from a database record?

The helper method getMediaDirByEntityTypeId() in app/code/community/Fballiano/ImageCleaner/Helper/Data.php maps the numeric entity_type_id to an absolute media directory path (e.g., 3 returns media/catalog/category/). The controller then concatenates this base directory with the relative path column to locate the physical file for deletion or download operations.

Can custom entity types be added to the image cleaner?

Yes, any custom EAV entity type ID can be stored in the entity_type_id column. You must extend the getMediaDirByEntityTypeId() helper method to map your custom ID to the appropriate media directory, ensuring the module can resolve filesystem paths for your custom entity's images.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}