Skip to content

Instantly share code, notes, and snippets.

@mattandrews
Created March 1, 2019 15:59
Show Gist options
  • Save mattandrews/ec58c17c0e86816da974b3889ba3e3bf to your computer and use it in GitHub Desktop.
Save mattandrews/ec58c17c0e86816da974b3889ba3e3bf to your computer and use it in GitHub Desktop.
Find hardlinked assets in SQL dumps and rename them
#!/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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment