Find hardlinked assets in SQL dumps and rename them

assetfix.js

#!/usr/bin/env node
'use strict';

const _ = require('lodash');
const mysql = require('mysql');
require('dotenv').config();

const db = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password : process.env.DB_PASSWORD,
    database : 'craft-new'
});

db.connect();

let data = '';

process.stdin.resume();
process.stdin.setEncoding('utf8');

process.stdin.on('data', function(chunk) {
    data += chunk;
});

process.stdin.on('end', function() {
    let updatedData = data;

    const urlPattern = /https:\/\/www.tnlcommunityfund.org.uk\/media\/([\w\d/\-\_%]+\.[\w\d]+)/g;

    // Look up all matching URLs in the database and build a SQL string to find their matching assets
    let queries = [];
    updatedData.replace(urlPattern, (match, filePath) => {
        const filebits = filePath.split('/');
        const folderPath = _.slice(filebits, 0, -1).join('/') + '/';
        const filename = filebits[filebits.length - 1];
        queries.push({
            match: match,
            filename: filename,
            sql: `SELECT assets.id as assetId, filename FROM assets JOIN volumefolders ON assets.folderId = volumefolders.id WHERE filename="${filename}" AND volumefolders.path = "${folderPath}"`
        });
    });

    // Run the SQL and return the ID string as needed by Craft
    const getAssetData = query => {
        return new Promise((resolve, reject) => {
            db.query(query.sql, (error, results, fields) => {
                if (error) {
                    console.log(error);
                    reject(error);
                }
                const row = results[0];
                if (!row || !results) {
                    resolve({
                        missing: true,
                        match: query.match,
                        filename: query.filename
                    });
                } else {
                    resolve({
                        search: query.match,
                        replace: `{asset:${row.assetId}:url}`
                    });
                }
            });
        });
    }

    // Run all of the queries for each found asset and store a search/replace pair
    const runQueries = queries.map(getAssetData);
    Promise.all(runQueries).then(responses => {
        responses.filter(r => !r.missing).forEach(r => {
            updatedData = updatedData.replace(r.search, r.replace);
        })
        console.log(updatedData);
        db.end();
        process.exit(2)
    }).catch(e => {
        console.log('got an error');
        console.log(e);
    });

});


// Usage:

// chmod +x assetfix
// cat export.sql | assetfix > renamed.sql

// For ease of diffing (eg. split the file to avoid crashing diff tools):

// split -b 2m renamed.sql chunk-

// (this generates 2mb sized chunk-a, chunk-b etc files,
// which you can compare against equivalent-chunked versions of the original)