Update records in SilverStripe's File table that point to non-existent files

silverstripe-assets-fixer.php

<?php

/**
 * # SilverStripe Assets Fixer
 *
 * Interactive script to update file records in a SilverStripe site database.
 *
 * ## How it works
 *
 * For each File record in the database where Filename doesn't exist, the basename
 * of that path is searched for in the assets directory. When one or more files are
 * found with that name, the user is prompted to select one to updated the record with.
 *
 * ## Usage notes
 *
 * This script requires use of an _ss_environment file that contains the target
 * site's database connection details. You must be in the root of the target site
 * to run this script (ie. the parent directory of the target assets dir).
 */

/**
 * Prompt for user input using STDIN
 * 
 * @param  string $prompt
 * @return string - trimmed user input
 */
function getUserInput($prompt)
{
    echo "$prompt ";
    $line = trim(fgets(STDIN));
    return $line;
}

/**
 * Prompt the user with choices
 * 
 * Intended for use with single character options. All options are 
 * converted to lowercase. Default value is displayed in uppercase.
 * The returned string is always lowercase.
 *
 * eg:
 * 
 *   Would you like to eat a pie? [Y/n]
 * 
 * @param  string $question
 * @param  array  $options
 * @param  string $default - value returned for an empty input
 * @return string - selected item in $options, or default
 */
function getUserDecision($question, array $options, $default = null)
{
    $options = array_map('strtolower', $options);
    $optionString = $options;

    if ($default) {
        $index = array_search(strtolower($default), $options);
        
        if ($index !== false) {
            $optionString[$index] = ucfirst($options[$index]);
        }
    }
    
    $optionString = implode('/', $optionString);

    while (true) {
        $answer = strtolower(getUserInput("$question [$optionString]"));

        if (!$answer && $default) {
            return strtolower($default);
        }

        if (in_array($answer, $options)) {
            return $answer;
        }
    }
}

/**
 * Search up from the working directory for an _ss_environment.php file
 * The user is prompted for each file found.
 * 
 * @return bool _ss_environment.php loaded
 */
function loadEnvironmentFile()
{
    $dir = getcwd();

    while ($dir != '/') {
        $file = $dir.'/_ss_environment.php';
        if (file_exists($file) && getUserDecision("Use environment file '$file'?", array('y','n'), 'y') == 'y') {
            require $file;
            return true;
        }

        $dir = dirname($dir);
    }

    return false;
}

/**
 * @return array of paths from the File table that don't exist on disk
 */
function findMissingAssets(PDO $pdo)
{
    $result = $pdo->query("SELECT ID,Filename FROM File WHERE ClassName != 'Folder'");
    $missing = array();

    while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
        if (!file_exists($row['Filename'])) {
            $missing[] = $row;
        }
    }

    return $missing;
}

/**
 * Find all files in the assets dir with a specified filename
 */
function findFilesByName($filename)
{
    $filename = escapeshellarg($filename);
    exec("find assets -iname $filename", $files);

    return $files;
}

/**
 * Prompt the user to select a file by index from a list
 */
function promptSelectFile($files)
{
    foreach ($files as $index => $file) {
        echo "  $index: $file\n";
    }

    while (true) {
        $value = getUserInput("\n  Enter the number of the file to update the DB record with, or empty to skip:");
        $value = preg_replace('/[^0-9]/', '', $value);

        if ($value === '') {
            return false;
        }

        if (!empty($files[$value])) {
            return $files[$value];
        }
    }
}

/**
 * Update the Filename field of a File record
 * @param  PDO $pdo
 * @param  int $id
 * @param  string $newFilename
 * @return bool success
 */
function updateFileRecord(PDO $pdo, $id, $newFilename)
{
    $stmt = $pdo->prepare("UPDATE File SET Filename = :filename WHERE ID = :id");

    return $stmt->execute(array(
        ':id' => intval($id),
        ':filename' => $newFilename
    ));
}

function printRecords(array $array, $message = '')
{
    if ($message) {
        echo "\n$message\n";
    }

    foreach ($array as $value) {
        echo "  {$value['ID']}  {$value['Filename']}\n";
    }
}

// --

if (!is_dir('assets')) {
    die("Can't see an assets directory in the current directory. This script must be run in the root of a SilverStripe site.\n");
}

if (!loadEnvironmentFile()) {
    die("An _ss_environment file must be loaded. None was found or selected.\n");
}

if (!defined('SS_DATABASE_USERNAME') || !defined('SS_DATABASE_PASSWORD') || !defined('SS_DATABASE_SERVER')) {
    die("A database setting constant was missing. Please check the _ss_environment file you selected.\n");
}

if (!defined('SS_DATABASE_NAME')) {
    $name = null;
    while (!$name) {
        $name = getUserInput("SS_DATABASE_NAME not set. Please enter the database name to use:");
    }

    define('SS_DATABASE_NAME', $name);
}

$pdo = new PDO("mysql:dbname=".SS_DATABASE_NAME.";host=".SS_DATABASE_SERVER, SS_DATABASE_USERNAME, SS_DATABASE_PASSWORD);

$missing = findMissingAssets($pdo);
$noMatches = array();
$updated = array();
$failed = array();

$totalMissing = count($missing);

foreach ($missing as $index => $record) {
    $matches = findFilesByName(basename($record['Filename']));

    if (count($matches)) {
        $position = $index + 1;
        echo "$position/$totalMissing  Matches found for missing '{$record['Filename']}':\n\n";

        if ($path = promptSelectFile($matches)) {
            if (updateFileRecord($pdo, $record['ID'], $path)) {
                $updated[] = $record;
            } else {
                $failed[] = $record;
            }
        }

        echo "\n";
    } else {
        $noMatches[] = $record;
    }
}

echo "Report:\n";
echo "\nNo matches found: ".count($noMatches);
echo "\nUpdated: ".count($updated);
echo "\nFailed or skipped: ".count($failed);

if (count($noMatches)) {
    printRecords($noMatches, 'No matches found in filesystem:');
}

if (count($failed)) {
    printRecords($failed, 'Failed or skipped:');
}